<?php

namespace App\Models;

use CodeIgniter\Model;

class PaymentModel extends Model
{
    protected $table            = 'tbl_payment';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'object';
    protected $useSoftDeletes   = false;
    protected $protectFields    = false;
    protected $allowedFields    = [];

    protected bool $allowEmptyInserts = false;
    protected bool $updateOnlyChanged = true;

    protected array $casts = [];
    protected array $castHandlers = [];

    // Dates
    protected $useTimestamps = false;
    protected $dateFormat    = 'datetime';
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';
    protected $deletedField  = 'deleted_at';

    // Validation
    protected $validationRules      = [];
    protected $validationMessages   = [];
    protected $skipValidation       = false;
    protected $cleanValidationRules = true;

    // Callbacks
    protected $allowCallbacks = true;
    protected $beforeInsert   = [];
    protected $afterInsert    = [];
    protected $beforeUpdate   = [];
    protected $afterUpdate    = [];
    protected $beforeFind     = [];
    protected $afterFind      = [];
    protected $beforeDelete   = [];
    protected $afterDelete    = [];



    public function _set_query()
    {

        $this->join('tbl_customer','tbl_customer.id=tbl_payment.customer_id');
        $this->join('tbl_area','tbl_area.id=tbl_customer.village_id');
        $this->join('tbl_user','tbl_user.id=tbl_payment.created_by');
        if(!isAdmin(session()->get('role_id'))){
            $this->where('tbl_payment.created_by',session()->get('id'));
        }
        if(isset($this->customer)&&!empty($this->customer)){
            $this->where('tbl_payment.customer_id',$this->customer);
        }
        if(isset($this->village)&&!empty($this->village)){
            $this->where('tbl_customer.village_id',$this->village);
        }
        if(isset($this->month)&&!empty($this->month)){
            $this->where('MONTH(tbl_payment.date)',date('m',strtotime($this->month)));
            $this->where('YEAR(tbl_payment.date)',date('Y',strtotime($this->month)));
        }
        if(!empty($this->search)){
            $this->groupStart();
            $this->like('UPPER(tbl_customer.name)',strtoupper($this->search));
            $this->orLike('tbl_payment.date',$this->search);
            $this->orLike('tbl_payment.total',$this->search);
            $this->groupEnd();
        }
    }

    public function getAllData()
    {
        $data = [];

        if ($this->columns[$this->order[0]['column']]['data'] == 'no') {
            $this->columns[$this->order[0]['column']]['data'] = $this->table . '.id';
        }

        //all data
        $this->select($this->table . '.id');
        $this->countData = $this->countAllResults();

        //filtered
        $this->select($this->table . '.id');
        $this->_set_query();
        $this->filterData = $this->countAllResults();

        //view data
        $this->select('tbl_payment.id, tbl_payment.total, tbl_payment.date, tbl_payment.created_at, tbl_customer.name AS customer_name, tbl_area.name AS village_name, tbl_user.name AS pic');
        $this->_set_query();
        $this->orderBy($this->columns[$this->order[0]['column']]['data'], $this->order[0]['dir']);
        $this->limit( intval($this->limit), intval($this->start));
        $getData = $this->findAll();
        $no      = $this->start;
        
        if ( count((array) $getData) > 0) {
            foreach ($getData as $row) {
                $row->no = ++$no;
                $row->created_at = date('H:i d-m-Y',strtotime($row->created_at));
                $row->total = 'Rp. '.number_format(intval($row->total), 0, ',', '.');;
                $row->village_name = '<span class="text-warning"> '.$row->village_name.'</span>';
                $row->date = date('M Y', strtotime($row->date));

                $row->option = '<p class="m-0 text-nowrap">';

                if(getAccessMenu(5,"is_delete", session()->get('role_id'))){

                    $row->option = $row->option . ' <a href="javascript:void(0)" data-id="' . $row->id . '" title="Hapus data" data-link="' . base_url() . 'payment/delete" class=" datatable-delete"><i class="bx bx-trash text-danger" style="font-size: 20px;"></i></a>';

                }


                if(getAccessMenu(5,"is_update", session()->get('role_id'))){

                    // $row->option = $row->option . '<a href="javascript:void(0)" title="Ubah data" data-link="' . base_url() . 'customer/edit/' . $row->id . '" class=" datatable-edit me-2"><i class="bx bx-edit text-warning" style="font-size: 20px;"></i></a>';
                }

                $row->option    = $row->option . '</p>';


                $data[]      = $row;
            }
        }

        $result = array(
            "draw"            => $this->draw,
            "recordsTotal"    => $this->countData,
            "recordsFiltered" => $this->filterData,
            "data"            => $data,
        );

        return json_encode($result);
    }


    public function getAllDataPayCustomer()
    {

        $data = [];

        $customerModel = new \App\Models\CustomerModel();
        $countFilter = 0;


        $query = $customerModel->select('tbl_customer.name, tbl_customer.id, tbl_customer.phone, tbl_package.name AS package_name, tbl_package.price AS price, tbl_area.name AS village_name')->join('tbl_package','tbl_package.id=tbl_customer.package_id')->join('tbl_area','tbl_area.id=tbl_customer.village_id');


        // Filter berdasarkan packages
        if (!empty((array)$this->packages)) {
            $query->groupStart(); // Start grouping package filters
            foreach ($this->packages as $i => $package) {
                if ($i === 0) {
                    $query->where('package_id', $package['id']);
                } else {
                    $query->orWhere('package_id', $package['id']);
                }
            }
            $query->groupEnd(); // End grouping
        }

        // Filter berdasarkan subdistricts
        if (!empty((array)$this->subdistricts)) {
            $query->groupStart(); // Start grouping subdistrict filters
            foreach ($this->subdistricts as $i => $subdistrict) {
                if ($i === 0) {
                    $query->where('subdistrict_id', $subdistrict['id']);
                } else {
                    $query->orWhere('subdistrict_id', $subdistrict['id']);
                }
            }
            $query->groupEnd(); // End grouping
        }

        // Filter berdasarkan villages
        if (!empty((array)$this->villages)) {
            $query->groupStart();
            foreach ($this->villages as $i => $village) {
                if ($i === 0) {
                    $query->where('village_id', $village['id']);
                } else {
                    $query->orWhere('village_id', $village['id']);
                }
            }
            $query->groupEnd();
        }


        if(!empty($this->keyword)){

            $query->groupStart();
            $query->where('UPPER(tbl_customer.name) LIKE', '%' . strtoupper($this->keyword) . '%');
            $query->groupEnd();
        }


        $customers = $query->findAll();
        if(count($customers)>0){

            $data = $this->_getPayment($customers,$this->startDate);

        }

        return json_encode([
            'code'      => 200,
            'success'   => true,
            'data'      => $data
        ]);

    }


    private function _getPayment($customers,$startDate)
    {
        
        if(count($customers)>0){
            $rangeMonth = $this->_getMonthRange($startDate,$startDate);
            $result = [];

            foreach($customers AS $customer){

                $this->select('id');
                $this->where('customer_id',$customer->id);
                $this->where('MONTH(date)',date('m',strtotime($startDate)));
                $this->where('YEAR(date)',date('Y',strtotime($startDate)));
                $this->groupBy("DATE_FORMAT(date, '%Y-%m')");
                $countPay = $this->findAll();
                if(count($countPay)<count($rangeMonth)){
                    $result[] = [
                        'id'   => $customer->id,
                        'name' => $customer->name,
                        'phone' => $customer->phone,
                        'village' => $customer->village_name,
                        'price' => $customer->price,
                        'countHasntPay' => count($rangeMonth)-count($countPay),
                    ];

                }

            }

            return $result;

        }

        return [];

    }


    private function _getMonthRange($start, $end) {
        $months = [];
        $current = strtotime($start);
        $end = strtotime($end);
        
        while ($current <= $end) {
            $months[] = date('Y-m', $current);
            $current = strtotime('+1 month', $current);
        }
        
        return $months;
    }


public function _getAllDataPayCustomer()
{
    $db = db_connect();
    
    // Hitung total bulan dalam rentang
    $start = $this->startDate.'-01';
    $end = $this->endDate.'-31';
    $totalMonths = $db->query("SELECT TIMESTAMPDIFF(MONTH, ?, ?) + 1 AS months", [$start, $end])
                     ->getRow()->months;

    // Bangun subquery untuk pembayaran
    $subquery = $db->table('tbl_payment AS payments')
        ->select("customer_id, DATE_FORMAT(date, '%Y-%m') AS month")
        ->where("DATE(date) >= ", $start)
        ->where("DATE(date) <= ", $end)
        ->groupBy("customer_id, DATE_FORMAT(date, '%Y-%m')")
        ->getCompiledSelect();

    // Query utama
    $builder = $db->table('tbl_customer')
        ->select("tbl_customer.name, tbl_customer.id, tbl_customer.phone, 
                tbl_package.name AS package_name, tbl_package.price AS price, 
                tbl_area.name AS village_name,
                COUNT(DISTINCT payments.month) AS paid_months,
                ($totalMonths - COUNT(DISTINCT payments.month)) AS countHasntPay")
        ->join('tbl_package', 'tbl_package.id = tbl_customer.package_id')
        ->join('tbl_area', 'tbl_area.id = tbl_customer.village_id')
        ->join("($subquery) AS payments", 'payments.customer_id = tbl_customer.id', 'left')
        ->groupBy('tbl_customer.id, tbl_customer.name, tbl_customer.phone, 
                 tbl_package.name, tbl_package.price, tbl_area.name')
        ->having('countHasntPay > 0');

    // Filter packages
    if (!empty((array)$this->packages)) {
        $packageIds = array_column($this->packages, 'id');
        $builder->whereIn('tbl_customer.package_id', $packageIds);
    }

    // Filter subdistricts
    if (!empty((array)$this->subdistricts)) {
        $subdistrictIds = array_column($this->subdistricts, 'id');
        $builder->whereIn('tbl_customer.subdistrict_id', $subdistrictIds);
    }

    // Filter villages
    if (!empty((array)$this->villages)) {
        $villageIds = array_column($this->villages, 'id');
        $builder->whereIn('tbl_customer.village_id', $villageIds);
    }

    // Keyword search
    if (!empty($this->keyword)) {
        $keyword = strtoupper($this->keyword);
        $builder->groupStart()
            ->like('UPPER(tbl_customer.name)', $keyword)
            ->orLike('UPPER(tbl_package.name)', $keyword)
            ->orLike('UPPER(tbl_area.name)', $keyword)
            ->groupEnd();
    }

    $result = $builder->get()->getResult();

    return json_encode([
        'code' => 200,
        'success' => true,
        'data' => $result
    ]);
}


}
