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

require_once APPPATH . 'third_party/PHPExcel/Classes/PHPExcel.php';
require_once APPPATH . 'third_party/PHPExcel/Classes/PHPExcel/IOFactory.php';
class Excel_read extends CI_Controller
{
    var $main_menu_name = "loan_payment_received";
    var $sub_menu_name = "";
    public function __construct()
    {
        parent::__construct();
        $this->load->model('Report_Loan_Model');
        $this->load->model('loan_model');
        $this->load->model('Common_Model');
        $this->load->model('Loan_Payment_Received_Model');
        date_default_timezone_set('asia/colombo');
        $this->load->library('form_validation');
        $this->load->helper(array('url', 'file'));
    }

    public function read_file()
    {
        // Load PHPExcel
        require_once APPPATH . 'third_party/PHPExcel/Classes/PHPExcel.php';
        require_once APPPATH . 'third_party/PHPExcel/Classes/PHPExcel/IOFactory.php';
        $file = FCPATH . 'uploads/excel/collection software.xlsx';
        $objPHPExcel = PHPExcel_IOFactory::load($file);
        $sheet = $objPHPExcel->getActiveSheet();
        $highestRow = $sheet->getHighestRow();

        $data = array();

        // Table starts from row 16 (as per your Excel)
        for ($row = 16; $row <= $highestRow; $row++) {

            $no     = $sheet->getCell('A' . $row)->getValue();
            $date   = $sheet->getCell('B' . $row)->getValue();
            $amount = $sheet->getCell('C' . $row)->getValue();

            if ($no != '') {

                // Convert Excel date to PHP date
                if (is_numeric($date)) {
                    $date = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($date));
                }

                $data[] = array(
                    'no'     => $no,
                    'date'   => $date,
                    'amount' => $amount
                );
            }
        }

        echo "<pre>";
        print_r($data);
    }

    public function add_payment()
    {

        $response = array('status' => 'error', 'message' => 'Unknown error');

        if ($this->input->is_ajax_request()) {

            $loan_id = $this->input->post('loan_id', TRUE);

            if (empty($loan_id)) {
                $response['message'] = 'Please select a Loan No.';
                echo json_encode($response);
                return;
            }

            // ───────────────────────────────────────────────
            // Handle Excel upload (if provided)
            // ───────────────────────────────────────────────
            $uploaded_payments = array();

            if (!empty($_FILES['collection_excel']['name'])) {

                $config['upload_path']   = FCPATH . 'uploads/excel/excel_temp/';
                $config['allowed_types'] = 'xlsx|xls';
                $config['max_size']      = 5120; // 5MB
                $config['encrypt_name']  = TRUE;

                // Create folder if not exists
                if (!is_dir($config['upload_path'])) {
                    mkdir($config['upload_path'], 0755, TRUE);
                }

                $this->load->library('upload', $config);

                if (!$this->upload->do_upload('collection_excel')) {
                    $response['message'] = $this->upload->display_errors('', '');
                    echo json_encode($response);
                    return;
                }

                $upload_data = $this->upload->data();
                $file_path   = $upload_data['full_path'];

                try {
                    $objPHPExcel = PHPExcel_IOFactory::load($file_path);
                    $sheet       = $objPHPExcel->getActiveSheet();
                    $highestRow  = $sheet->getHighestRow();

                    for ($row = 18; $row <= $highestRow; $row++) {

                        $no     = trim($sheet->getCell('A' . $row)->getValue());
                        $date   = $sheet->getCell('B' . $row)->getValue();
                        $amount = $sheet->getCell('C' . $row)->getValue();

                        // Remove comma formatting
                        $amount = str_replace(',', '', $amount);

                        // STOP if the row is completely empty
                        if ($amount === '' || $amount === null) {
                            break;
                        }

                        // Run process for all valid rows (even if amount = 0)
                        if (!empty($no)) {
                            
                            
                            // Handle Excel date
                            if (is_numeric($date)) {
                                $php_date = PHPExcel_Shared_Date::ExcelToPHP($date);
                                $date_str = date('Y-m-d', $php_date);
                            } else {
                                $date_str = date('Y-m-d', strtotime($date));
                                if ($date_str === '1970-01-01') {
                                    $date_str = null;
                                }
                            }

                            $loan_details = $this->loan_model->get_loan_details_by_id($loan_id);
                            $tot_loan_amount = $loan_details->loan_tot_amount;

                            $data = array(
                                'loan_id' => $loan_id,
                                'term_id' => $no,
                                'cus_id' => $loan_details->cus_id,
                                'warehouse_id' => $loan_details->warehouse_id,
                                'route_id' => $loan_details->route_id,
                                'pymnt_paying_by' => 'Cash',
                                'pymnt_date_time' => $date_str,
                                'pymnt_amount' => floatval($amount),
                                'pymnt_added_date_time' => date("Y-m-d H:i:s"),
                                'user_id' => $this->session->userdata('ss_user_id'),
                                'pymnt_collected' => 1,
                                'pymnt_deposited' => 1,
                                'panalty_received' => 0,
                                'due_received' => 0,
                                'loan_received' => $amount,
                                'excess_received' => 0,
                                'capital_received' => 0,
                                'loan_interest_recived' => 0,
                                'pymnt_ref_no' => $this->Common_Model->gen_ref_number('paymnt_id', 'loan_payment', 'RCPT/'),
                            );

                            $result1 = $this->Loan_Payment_Received_Model->save_loan_payment_excel($data, $no);
                            // Loan close check
                            $paid = $this->Loan_Payment_Received_Model->get_loan_paid_total_by_loan_id($loan_id);
                            $paid_amount = ($paid['pymnt_amount'] > 0) ? $paid['pymnt_amount'] : 0;

                            $tot_to_be_payable = $tot_loan_amount;
                            $balance = $tot_to_be_payable - $paid_amount;

                            if ($balance <= 0) {
                                $data = array(
                                    'is_closed' => 1,
                                    'system_close_date' => date("Y-m-d")
                                );

                                $this->Loan_Payment_Received_Model->update_loan_close_status($loan_id, $data);
                            }
                        }
                    }
                    // Optional: remove uploaded file after processing
                    @unlink($file_path);
                } catch (Exception $e) {
                    $response['message'] = 'Excel reading error: ' . $e->getMessage();
                    echo json_encode($response);
                    return;
                }
            }

            // ───────────────────────────────────────────────
            // Now decide what to do:
            //   A) Only Excel → bulk insert
            //   B) No Excel → single payment (your original logic)
            //   C) Both → maybe merge or give priority to Excel
            // ───────────────────────────────────────────────

            if (!empty($uploaded_payments)) {
                // Bulk insert example
                //$this->db->insert_batch('loan_payments', $uploaded_payments);
                $response = array(
                    'status'  => 'success',
                    'message' => count($uploaded_payments) . ' payment(s) imported from Excel successfully.',
                    'count'   => count($uploaded_payments)
                );
            } else {
                // Your original single payment logic here...
                // Example:
                $payment_data = array(
                    'loan_id'      => $loan_id,
                    'amount'       => $this->input->post('amount'),
                    'payment_date' => $this->input->post('payment_date'),
                    // ... other fields
                );
                //$this->db->insert('loan_payments', $payment_data);

                $response = array(
                    'status'  => 'success',
                    'message' => 'Single payment added successfully.'
                );
            }
        } else {
            $response['message'] = 'Invalid request.';
        }

        echo json_encode($response);
    }

    public function add_loan_payment()
    {    //not updated	 
        $data['main_menu_name'] = $this->main_menu_name;
        $data['sub_menu_name'] = "excel_payment";
        $data['loan_details'] = $this->loan_model->get_all_loans();
        $loan_id = $this->uri->segment('3');

        $this->load->view('loan/excel_payment', $data);
    }
}
