<?php
defined('BASEPATH') or exit('No direct script access allowed');

class Spreadsheet
{

    private $spreadsheet;
    private $CI;

    public function __construct()
    {
        $this->CI = &get_instance();

        // Include Composer autoload
        require_once APPPATH . '../vendor/autoload.php';
    }

    /**
     * Load Excel file
     */
    public function load($filepath)
    {
        $this->spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filepath);
        return $this;
    }

    /**
     * Create new spreadsheet
     */
    public function create()
    {
        $this->spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        return $this;
    }

    /**
     * Get active sheet
     */
    public function getActiveSheet()
    {
        return $this->spreadsheet->getActiveSheet();
    }

    /**
     * Read Excel file to array
     */
    public function readToArray($filepath, $startRow = 1)
    {
        $this->load($filepath);
        $sheet = $this->spreadsheet->getActiveSheet();
        return $sheet->toArray(null, true, true, true);
    }

    /**
     * Read Excel untuk data hasil TKA
     */
    public function readHasilTKA($filepath, $startRow = 2)
    {
        try {
            // Load file
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filepath);
            $sheet = $spreadsheet->getActiveSheet();

            // Get highest row and column
            $highestRow = $sheet->getHighestRow();
            $highestColumn = $sheet->getHighestColumn();
            $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

            $data = array();

            // Read data starting from startRow
            for ($row = $startRow; $row <= $highestRow; $row++) {
                $rowData = array();

                // Read each column
                for ($col = 1; $col <= $highestColumnIndex; $col++) {
                    $cellValue = $sheet->getCellByColumnAndRow($col, $row)->getValue();
                    $rowData[] = $cellValue;
                }

                // Check if NISN exists
                if (!empty($rowData[0])) {
                    $data[] = array(
                        'NISN' => $rowData[0] ?? '',
                        'NO_PESERTA' => $rowData[1] ?? '',
                        'NAMA_PESERTA' => $rowData[2] ?? '',
                        'TEMPAT_TGL_LAHIR' => $rowData[3] ?? '',
                        'BHS_INDONESIA' => $rowData[4] ?? '0',
                        'MATEMATIKA' => $rowData[5] ?? '0',
                        'BHS_INGGRIS' => $rowData[6] ?? '0',
                        'NAMA_MATPEL_PIL_1' => $rowData[7] ?? '',
                        'NILAI_MATPEL_PIL_1' => $rowData[8] ?? '',
                        'NAMA_MATPEL_PIL_2' => $rowData[9] ?? '',
                        'NILAI_MATPEL_PIL_2' => $rowData[10] ?? '',
                        'LAMPIRAN' => $rowData[11] ?? ''
                    );
                }
            }

            return $data;
        } catch (\Exception $e) {
            log_message('error', 'Spreadsheet Error: ' . $e->getMessage());
            return array();
        }
    }

    /**
     * Read Excel dengan validasi kolom
     */
    public function readHasilTKAV2($filepath)
    {
        try {
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filepath);
            $worksheet = $spreadsheet->getActiveSheet();

            // Get all data as array
            $dataArray = $worksheet->toArray(null, true, true, true);

            // Remove header row
            array_shift($dataArray);

            $formattedData = array();
            $rowNumber = 2;

            foreach ($dataArray as $row) {
                // Skip empty rows (NISN kosong)
                if (empty($row['A'])) {
                    $rowNumber++;
                    continue;
                }

                // Format data - semua nilai sebagai string
                $formattedData[] = array(
                    'NISN' => $this->toString($row['A']),
                    'NO_PESERTA' => $this->toString($row['B']),
                    'NAMA_PESERTA' => $this->toString($row['C']),
                    'TEMPAT_TGL_LAHIR' => $this->toString($row['D']),
                    'BHS_INDONESIA' => $this->toString($row['E']),
                    'MATEMATIKA' => $this->toString($row['F']),
                    'BHS_INGGRIS' => $this->toString($row['G']),
                    'NAMA_MATPEL_PIL_1' => $this->toString($row['H']),
                    'NILAI_MATPEL_PIL_1' => $this->toString($row['I']),
                    'NAMA_MATPEL_PIL_2' => $this->toString($row['J']),
                    'NILAI_MATPEL_PIL_2' => $this->toString($row['K']),
                    'LAMPIRAN' => $this->toString($row['L']),
                    'row_number' => $rowNumber
                );

                $rowNumber++;
            }

            return $formattedData;
        } catch (\Exception $e) {
            throw new \Exception('Error membaca file Excel: ' . $e->getMessage());
        }
    }

    /**
     * Convert any value to string
     */
    private function toString($value)
    {
        if ($value === null || $value === '') {
            return '';
        }

        // Handle PhpSpreadsheet RichText objects
        if (is_object($value) && method_exists($value, '__toString')) {
            return $value->__toString();
        }

        // Convert to string
        return (string) $value;
    }

    /**
     * Clean value - simplified version
     */
    private function cleanValue($value)
    {
        if ($value === null) {
            return '';
        }

        // Convert to string
        $value = trim((string) $value);

        return $value;
    }


    /**
     * Export data to Excel
     */
    public function export($data, $filename = 'export.xlsx', $headers = array())
    {
        // Create new Spreadsheet
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Set headers
        $row = 1;
        if (!empty($headers)) {
            $col = 1;
            foreach ($headers as $header) {
                $sheet->setCellValueByColumnAndRow($col, $row, $header);
                $sheet->getStyleByColumnAndRow($col, $row)->getFont()->setBold(true);
                $col++;
            }
            $row++;
        }

        // Add data
        foreach ($data as $dataRow) {
            $col = 1;
            foreach ($dataRow as $value) {
                $sheet->setCellValueByColumnAndRow($col, $row, $value);
                $col++;
            }
            $row++;
        }

        // Auto size columns
        for ($col = 1; $col <= count($headers ?: $data[0] ?? array()); $col++) {
            $sheet->getColumnDimension(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col))->setAutoSize(true);
        }

        // Set title
        $sheet->setTitle('Data Export');

        // Save to browser
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    /**
     * Export data hasil TKA
     */
    public function exportHasilTKA($data, $filename = 'hasil_tka.xlsx')
    {
        $headers = array(
            'NISN',
            'NO_PESERTA',
            'NAMA_PESERTA',
            'TEMPAT_TGL_LAHIR',
            'BHS_INDONESIA',
            'MATEMATIKA',
            'BHS_INGGRIS',
            'NAMA_MATPEL_PIL_1',
            'NILAI_MATPEL_PIL_1',
            'NAMA_MATPEL_PIL_2',
            'NILAI_MATPEL_PIL_2',
            'LAMPIRAN'
        );

        $exportData = array();
        foreach ($data as $row) {
            $exportData[] = array(
                $row['NISN'] ?? '',
                $row['NO_PESERTA'] ?? '',
                $row['NAMA_PESERTA'] ?? '',
                $row['TEMPAT_TGL_LAHIR'] ?? '',
                $row['BHS_INDONESIA'] ?? '0',
                $row['MATEMATIKA'] ?? '0',
                $row['BHS_INGGRIS'] ?? '0',
                $row['NAMA_MATPEL_PIL_1'] ?? '',
                $row['NILAI_MATPEL_PIL_1'] ?? '',
                $row['NAMA_MATPEL_PIL_2'] ?? '',
                $row['NILAI_MATPEL_PIL_2'] ?? '',
                $row['LAMPIRAN'] ?? ''
            );
        }

        return $this->export($exportData, $filename, $headers);
    }

    /**
     * Download template untuk import
     */
    public function downloadTemplate()
    {
        $templateData = array(
            array(
                '1234567890',
                'TKA2023001',
                'Contoh Nama Siswa',
                'Jakarta, 01-01-2005',
                '85',
                '90',
                '88',
                'Fisika',
                '87',
                'Kimia',
                '82',
                'Keterangan tambahan'
            )
        );

        $headers = array(
            'NISN (10 digit) *',
            'NO_PESERTA *',
            'NAMA_PESERTA *',
            'TEMPAT_TGL_LAHIR',
            'BHS_INDONESIA',
            'MATEMATIKA',
            'BHS_INGGRIS',
            'NAMA_MATPEL_PIL_1',
            'NILAI_MATPEL_PIL_1',
            'NAMA_MATPEL_PIL_2',
            'NILAI_MATPEL_PIL_2',
            'LAMPIRAN'
        );

        $this->export($templateData, 'template_import_tka.xlsx', $headers);
    }

    /**
     * Validate Excel file
     */
    public function validateFile($filepath)
    {
        try {
            $fileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($filepath);
            $validTypes = ['Xlsx', 'Xls', 'Xml', 'Ods', 'Csv'];

            return in_array($fileType, $validTypes);
        } catch (\Exception $e) {
            return false;
        }
    }

    /**
     * Get sheet names
     */
    public function getSheetNames($filepath)
    {
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filepath);
        return $spreadsheet->getSheetNames();
    }

    /**
     * Get sheet count
     */
    public function getSheetCount($filepath)
    {
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filepath);
        return $spreadsheet->getSheetCount();
    }
}
