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

class HasilTKA_model extends CI_Model
{

    public function __construct()
    {
        parent::__construct();
    }

    // Get all results
    public function get_all_results()
    {
        return $this->db->get('tbl_nilai_tka')->result();
    }

    // Get result by NISN
    public function get_result_by_nisn($nisn)
    {
        $this->db->where('NISN', $nisn);
        return $this->db->get('tbl_nilai_tka')->row();
    }

    // Get result by NISN
    public function get_result_by_id($id)
    {
        $this->db->where('ID', $id);
        return $this->db->get('tbl_nilai_tka')->row();
    }

    // Check if NISN exists
    public function nisn_exists($nisn)
    {
        $this->db->where('NISN', $nisn);
        return $this->db->get('tbl_nilai_tka')->num_rows() > 0;
    }

    // Add result
    public function add_result($data)
    {
        return $this->db->insert('tbl_nilai_tka', $data);
    }

    // Update result
    public function update_result($id, $data)
    {
        $this->db->where('ID', $id);
        return $this->db->update('tbl_nilai_tka', $data);
    }

    // Delete result
    public function delete_result($id)
    {
        $this->db->where('ID', $id);
        return $this->db->delete('tbl_nilai_tka');
    }



    // Search results
    public function search_results($keyword)
    {
        $this->db->like('NISN', $keyword);
        $this->db->or_like('NO_PESERTA', $keyword);
        $this->db->or_like('NAMA_PESERTA', $keyword);
        return $this->db->get('tbl_nilai_tka')->result();
    }

    // Import from Excel - tanpa validasi numerik
    public function import_excel($data)
    {
        $success_count = 0;
        $error_count = 0;

        foreach ($data as $row) {
            // Clean and format data
            $clean_data = array(
                'NISN' => $this->clean_string($row['NISN']),
                'NO_PESERTA' => $this->clean_string($row['NO_PESERTA']),
                'NAMA_PESERTA' => $this->clean_string($row['NAMA_PESERTA']),
                'TEMPAT_TGL_LAHIR' => $this->clean_string($row['TEMPAT_TGL_LAHIR']),
                'BHS_INDONESIA' => $this->clean_string($row['BHS_INDONESIA']),
                'MATEMATIKA' => $this->clean_string($row['MATEMATIKA']),
                'BHS_INGGRIS' => $this->clean_string($row['BHS_INGGRIS']),
                'NAMA_MATPEL_PIL_1' => $this->clean_string($row['NAMA_MATPEL_PIL_1']),
                'NILAI_MATPEL_PIL_1' => $this->clean_string($row['NILAI_MATPEL_PIL_1']),
                'NAMA_MATPEL_PIL_2' => $this->clean_string($row['NAMA_MATPEL_PIL_2']),
                'NILAI_MATPEL_PIL_2' => $this->clean_string($row['NILAI_MATPEL_PIL_2']),
                'LAMPIRAN' => $this->clean_string($row['LAMPIRAN'])
            );

            // Check if NISN already exists
            $this->db->where('NISN', $clean_data['NISN']);
            $exists = $this->db->get('tbl_nilai_tka')->row();

            try {
                if ($exists) {
                    // Update existing record
                    $this->db->where('NISN', $clean_data['NISN']);
                    if ($this->db->update('tbl_nilai_tka', $clean_data)) {
                        $success_count++;
                    } else {
                        $error_count++;
                        log_message('error', 'Failed to update NISN: ' . $clean_data['NISN']);
                    }
                } else {
                    // Insert new record
                    $clean_data['CREATED_AT'] = date('Y-m-d H:i:s');
                    if ($this->db->insert('tbl_nilai_tka', $clean_data)) {
                        $success_count++;
                    } else {
                        $error_count++;
                        log_message('error', 'Failed to insert NISN: ' . $clean_data['NISN']);
                    }
                }
            } catch (Exception $e) {
                $error_count++;
                log_message('error', 'Import error for NISN ' . $clean_data['NISN'] . ': ' . $e->getMessage());
            }
        }

        return array(
            'success' => $success_count,
            'error' => $error_count,
            'total' => count($data)
        );
    }

    /**
     * Clean string value
     */
    private function clean_string($value)
    {
        if (is_null($value) || $value === '') {
            return '';
        }

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

        // Trim whitespace
        $value = trim($value);

        // Remove non-printable characters but keep all text
        $value = preg_replace('/[\x00-\x1F\x7F]/u', '', $value);

        return $value;
    }

    /**
     * Import with batch insert (lebih cepat)
     */
    public function import_excel_batch($data)
    {
        $insert_batch = array();
        $update_batch = array();

        foreach ($data as $row) {
            $clean_data = array(
                'NISN' => $this->clean_string($row['NISN']),
                'NO_PESERTA' => $this->clean_string($row['NO_PESERTA']),
                'NAMA_PESERTA' => $this->clean_string($row['NAMA_PESERTA']),
                'TEMPAT_TGL_LAHIR' => $this->clean_string($row['TEMPAT_TGL_LAHIR']),
                'BHS_INDONESIA' => $this->clean_string($row['BHS_INDONESIA']),
                'MATEMATIKA' => $this->clean_string($row['MATEMATIKA']),
                'BHS_INGGRIS' => $this->clean_string($row['BHS_INGGRIS']),
                'NAMA_MATPEL_PIL_1' => $this->clean_string($row['NAMA_MATPEL_PIL_1']),
                'NILAI_MATPEL_PIL_1' => $this->clean_string($row['NILAI_MATPEL_PIL_1']),
                'NAMA_MATPEL_PIL_2' => $this->clean_string($row['NAMA_MATPEL_PIL_2']),
                'NILAI_MATPEL_PIL_2' => $this->clean_string($row['NILAI_MATPEL_PIL_2']),
                'LAMPIRAN' => $this->clean_string($row['LAMPIRAN']),
                'UPDATE_AT' => date('Y-m-d H:i:s')
            );

            $this->db->where('NISN', $clean_data['NISN']);
            $exists = $this->db->get('tbl_nilai_tka')->row();

            if ($exists) {
                $clean_data['id'] = $exists->id;
                $update_batch[] = $clean_data;
            } else {
                $clean_data['CREATED_AT'] = date('Y-m-d H:i:s');
                $insert_batch[] = $clean_data;
            }
        }

        // Execute batch operations
        $success_count = 0;

        if (!empty($insert_batch)) {
            if ($this->db->insert_batch('tbl_nilai_tka', $insert_batch)) {
                $success_count += count($insert_batch);
            }
        }

        if (!empty($update_batch)) {
            foreach ($update_batch as $row) {
                $id = $row['id'];
                unset($row['id']);

                $this->db->where('id', $id);
                if ($this->db->update('tbl_nilai_tka', $row)) {
                    $success_count++;
                }
            }
        }

        return $success_count;
    }
}
