<?php
class Loan_Payment_Received_Model extends CI_Model
{
    private $tableName = 'loan';
    function __construct()
    {
        /* Call the Model constructor */
        parent::__construct();
        $this->load->database();
        $this->load->helper('url');
        $this->load->library(array(
            'email'
        ));
    }

    public function insert_payment($data)
    {
        $this->db->insert('loan_payment', $data);
        return $this->db->insert_id();
    }


    public function mark_term_not_collected($loan_id, $loan_term_id, $reason, $payment_amount = 0)
    {
        // Get the loan term
        $this->db->where('term_id', $loan_term_id);
        $this->db->where('loan_id', $loan_id);
        $term = $this->db->get('loan_terms')->row();

        if (!$term) {
            return false; // No matching record
        }

        // Calculate delay charge
        $delay_charge = ((($term->term_amount - $payment_amount) * 0.1) / 30) * $term->different_days;

        // Update the loan term
        $update_data = array(
            'status' => 'Not Collected',
            'not_collected_reason'         => $reason,
            'delay_charge'   => $delay_charge,
            'updated_at'     => date('Y-m-d H:i:s')
        );

        $this->db->where('term_id', $loan_term_id);
        $this->db->where('loan_id', $loan_id);
        $this->db->update('loan_terms', $update_data);

        if ($this->db->affected_rows() > 0) {
            return $delay_charge; // return calculated value
        }

        return false;
    }


    function update_loan_issue_status($loan_id, $data)
    {
        $this->db->where('loan_id', $loan_id);
        return $this->db->update('loan', $data);
    }
    function get_loan_paid_total_by_loan_id($loan_id)
    {
        $this->db->select_sum('pymnt_amount');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.pymnt_returned', 0);
        $this->db->where('loan_payment.loan_id', $loan_id);
        $query = $this->db->get();
        return $query->row_array();
    }

    /* function save_loan_payment($data)
    {
        if ($data) {
            return $this->db->insert('loan_payment', $data);
        } else {
            return false;
        }
    } */

    function save_loan_payment($data, $loan_term_id = 0)
    {
        if (!$data) return ['status' => false, 'message' => 'No data provided'];

        $this->db->trans_start(); // Start transaction

        try {
            // Insert payment record
            $this->db->insert('loan_payment', $data);
            if ($this->db->affected_rows() == 0) {
                throw new Exception('Failed to insert loan payment');
            }

            $payment_id    = $this->db->insert_id();
            $payment_date  = date('Y-m-d', strtotime($data['pymnt_date_time']));
            $payment_amount = $data['pymnt_amount'];

            // ---------------------------
            // Get the correct loan term
            // ---------------------------
            if ($loan_term_id != 0) {
                // Use the given term_id
                $term = $this->db->where('term_id', $loan_term_id)
                    ->where('loan_id', $data['loan_id'])
                    ->get('loan_terms')
                    ->row();
            } else {
                // Auto find next pending term
                $term = $this->db->where('loan_id', $data['loan_id'])
                    ->where('status !=', 'Paid')
                    ->order_by('term_no', 'ASC')
                    ->limit(1)
                    ->get('loan_terms')
                    ->row();
            }

            if (!$term) {
                throw new Exception('No loan term found to update');
            }

            // ---------------------------
            // Update the term
            // ---------------------------
            $new_paid = $term->paid_amount + $payment_amount;
            //$status   = ($new_paid >= $term->term_amount) ? 'Paid' : 'Pending';
            $status   = 'Paid';
            $delay_charge   = ((($term->term_amount - $payment_amount) * 0.1) / 30) * $term->different_days;


            $this->db->where('term_id', $term->term_id)
                ->update('loan_terms', [
                    'paid_amount'  => $new_paid,
                    'status'       => $status,
                    'payment_date' => $payment_date,
                    'paymnt_id'    => $payment_id,
                    'delay_charge'    => $delay_charge,
                    'updated_at'   => date('Y-m-d H:i:s')
                ]);

            if ($this->db->affected_rows() == 0) {
                throw new Exception('Failed to update loan term');
            }

            $this->db->trans_complete();

            if ($this->db->trans_status() === false) {
                return ['status' => false, 'message' => 'Transaction failed'];
            }

            return ['status' => true, 'payment_id' => $payment_id, 'term_id' => $term->term_id];
        } catch (Exception $e) {
            $this->db->trans_rollback();
            return ['status' => false, 'message' => $e->getMessage()];
        }
    }




    function getLoan_approved_issued($warehouse_id = '')
    {
        $this->db->select('b.*,c.cus_name');
        $this->db->select('mpt.payment_term_name');
        $this->db->from('loan b');
        $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        $this->db->join('customer c', 'c.cus_id = b.cus_id', 'left');
        $this->db->join('mstr_payment_terms mpt', 'mpt.id = b.loan_payment_term', 'left');
        $this->db->where("b.approval_status", 1);
        $this->db->where("b.loan_issue_status", 1);
        if ($warehouse_id) {
            $this->db->where("b.warehouse_id", $warehouse_id); //
        }
        $this->db->order_by("b.loan_id", "desc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function get_last_panalty_by_loan_id($loan_id)
    {
        $this->db->select('loan_panalty.payment_date,loan_panalty.panalty_amount');
        $this->db->from('loan_panalty');
        $this->db->where("loan_panalty.loan_id", $loan_id);
        $this->db->where("loan_panalty.is_removed", 0);
        $this->db->order_by("loan_panalty.id", "desc");
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row_array();
        } else {
            return false;
        }
    }
    function getLoan_paid_list($loan_id)
    {
        $this->db->select('user.user_first_name');
        $this->db->select('loan_payment.*');
        $this->db->from('loan_payment');
        $this->db->join('user', 'user.user_id=loan_payment.user_id', 'left');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $query = $this->db->get();
        return $query->result();
    }
    function get_total_realize_cheque_for_today_by_loan_id($loan_id, $today)
    {
        $this->db->select_sum('loan_received');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_deposited', 1);
        //$this->db->where('loan_payment.pymnt_realize',1);
        $this->db->where('loan_payment.pymnt_paying_by', "Cheque");
        $this->db->where('loan_payment.pymnt_returned', 0);
        $this->db->where("loan_payment.pymnt_cheque_date <=", $today);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_cheque_for_today_by_loan_id($loan_id, $today)
    {
        $this->db->select_sum('loan_received');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.is_initial_cheque', 1);
        $this->db->where('loan_payment.pymnt_paying_by', "Cheque");
        $this->db->where("loan_payment.pymnt_cheque_date <=", $today);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_panalty_by_loan_id($loan_id)
    {
        $this->db->select_sum('panalty_amount');
        $this->db->from('loan_panalty');
        $this->db->where('loan_panalty.loan_id', $loan_id);
        $this->db->where('loan_panalty.is_removed', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_due_by_loan_id($loan_id)
    {
        $this->db->select_sum('due_amount');
        $this->db->from('loan_due_amount');
        $this->db->where('loan_due_amount.loan_id', $loan_id);
        $this->db->where('loan_due_amount.is_removed', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_paid_panalty_by_loan_id($loan_id)
    {
        $this->db->select_sum('panalty_received');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_paid_due_by_loan_id($loan_id)
    {
        $this->db->select_sum('due_received');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_paid_loan_by_loan_id($loan_id)
    {
        $this->db->select_sum('loan_received');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_paid_capital_by_loan_id($loan_id)
    {
        $this->db->select_sum('capital_received');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_paid_interest_by_loan_id($loan_id)
    {
        $this->db->select_sum('loan_interest_recived');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function update_loan_close_status($loan_id, $data)
    {
        $this->db->where('loan_id', $loan_id);
        return $this->db->update('loan', $data);
    }
    function getLoan_approved_issued_cash_collect($warehouse_id = '', $route, $payment_t)
    {
        $this->db->select("b.*,c.cus_mobile,c.cus_nic,c.location_latitude,c.location_long,c.location_status,c.cus_address,IF(bi.business_name='', c.full_name, bi.business_name) AS cus_name",false);
        $this->db->select('mpt.payment_term_name');
        $this->db->from('loan b');
        $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        $this->db->join('customer c', 'c.cus_id = b.cus_id', 'left');
        $this->db->join('business_information bi', 'c.cus_id = bi.customer_id', 'left');
        $this->db->join('mstr_payment_terms mpt', 'mpt.id = b.loan_payment_term', 'left');
        $this->db->where("b.approval_status", 1);
        $this->db->where("b.loan_issue_status", 1);
        $this->db->where("b.is_closed", 0);
        if ($warehouse_id) {
            $this->db->where("b.warehouse_id", $warehouse_id); //
        }
        if ($route) {
            $this->db->where("b.route_id", $route); //
        }
        if ($payment_t) {
            $this->db->where("b.loan_payment_term", $payment_t); //
        }
        $this->db->order_by("b.loan_id", "desc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    //----------------------------------------------------------------------------------
    //loan report 
    function get_loan_payment_total($loan_id)
    {
        $this->db->select_sum('pymnt_amount');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_deposited', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    //-------------------------------------------------------------------------------

    function get_last_paid_loan_by_loan_id($loan_id)
    {
        $this->db->select('pymnt_amount');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_deposited', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
        $this->db->limit(1);
        $this->db->order_by("loan_payment.pymnt_date_time", "desc");
        $this->db->order_by("loan_payment.paymnt_id", "desc");

        $query = $this->db->get();

        //print_r($query);
        return $query->row_array();
    }
    
     function save_loan_payment_excel($data, $loan_term_id = 0)
    {
        if (!$data) return ['status' => false, 'message' => 'No data provided'];

        $this->db->trans_start(); // Start transaction

        try {
            if ($data['pymnt_amount'] > 0) {
                // First, define your condition to check existing row
                $condition = array(
                    'loan_id' => $data['loan_id'],
                    'term_id' => $data['term_id'],
                );
                $this->db->where($condition);
                $existing = $this->db->get('loan_payment')->row();
                if ($existing) {
                    $payment_id    = $existing->paymnt_id;
                    $this->db->where('paymnt_id', $payment_id);
                    unset($data['pymnt_ref_no']);
                    $this->db->update('loan_payment', $data);

                    if ($this->db->affected_rows() == 0) {
                        throw new Exception('Failed to update existing loan payment');
                    }
                } else {
                    $this->db->insert('loan_payment', $data);
                    if ($this->db->affected_rows() == 0) {
                        throw new Exception('Failed to insert loan payment');
                    }
                    $payment_id    = $this->db->insert_id();
                }
            } else {
                $payment_id = 0;
            }

            $payment_date  = date('Y-m-d', strtotime($data['pymnt_date_time']));
            $payment_amount = $data['pymnt_amount'];

            // ---------------------------
            // Get the correct loan term
            // ---------------------------
            if ($loan_term_id != 0) {
                // Use the given term_id
                $term = $this->db->where('term_no', $loan_term_id)
                    ->where('loan_id', $data['loan_id'])
                    ->get('loan_terms')
                    ->row();
            } else {
                // Auto find next pending term
                $term = $this->db->where('loan_id', $data['loan_id'])
                    ->where('status !=', 'Paid')
                    ->order_by('term_no', 'ASC')
                    ->limit(1)
                    ->get('loan_terms')
                    ->row();
            }

            if (!$term) {
                throw new Exception('No loan term found to update');
            }

            // ---------------------------
            // Update the term
            // ---------------------------
            $new_paid = $payment_amount;
            $status   = ($payment_amount >0) ? 'Paid' : 'Not Collected';
            $delay_charge   = ((($term->term_amount - $payment_amount) * 0.1) / 30) * $term->different_days;


            $this->db->where('term_id', $term->term_id)
                ->update('loan_terms', [
                    'paid_amount'  => $new_paid,
                    'status'       => $status,
                    'payment_date' => $payment_date,
                    'paymnt_id'    => $payment_id,
                    'delay_charge'    => $delay_charge,
                    'updated_at'   => date('Y-m-d H:i:s')
                ]);

            if ($this->db->affected_rows() == 0) {
                throw new Exception('Failed to update loan term');
            }

            $this->db->trans_complete();

            if ($this->db->trans_status() === false) {
                return ['status' => false, 'message' => 'Transaction failed'];
            }

            return ['status' => true, 'payment_id' => $payment_id, 'term_id' => $term->term_id];
        } catch (Exception $e) {
            $this->db->trans_rollback();
            return ['status' => false, 'message' => $e->getMessage()];
        }
    }
}
