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

class Payment_model extends CI_Model {

    /**
     * Create a new payment record
     */
    public function create($options)
    {
        $this->db->insert('kns1_payment', $options);
        return $this->db->insert_id();
    }

    /**
     * Get all payments (with optional limit/offset)
     */
    public function get_all($limit = null, $offset = null)
    {
        $this->db->order_by('id', 'DESC');
        $query = $this->db->get('kns1_payment', $limit, $offset);
        return $query->result();
    }

    /**
     * Get single payment by ID
     */
    public function get_by($id)
    {
        $this->db->where('id', $id);
        $query = $this->db->get('kns1_payment');
        return $query->row();
    }

    /**
     * Get payments by module ID
     */
    public function get_by_module($id)
    {
        $this->db->where('module_id', $id);
        $query = $this->db->get('kns1_payment');
        return $query->result();
    }

    /**
     * Get total debit/credit for a module (optionally for today)
     */
    public function get_totals_by_module($module, $today = false)
    {
        $this->db->select('SUM(debit) AS debit, SUM(credit) AS credit');
        $this->db->where('payment_module', $module);

        if($today) {
            $this->db->where('created_at >=', date('Y-m-d 04:00:00'));
            $this->db->where('created_at <=', date('Y-m-d 04:00:00', strtotime('tomorrow')));
        }

        $query = $this->db->get('kns1_payment');
        return $query->row();
    }

    /**
     * Delete payments by module and module_id
     */
    public function delete_by_module($module, $module_id)
    {
        $this->db->where('payment_module', $module);
        $this->db->where('module_id', $module_id);
        $this->db->delete('kns1_payment');
        return $this->db->affected_rows();
    }

    /**
     * Get total cash collected today
     */
    public function get_all_today()
    {
        $this->db->select('SUM(debit) AS debit, SUM(credit) AS credit');
        $this->db->where('DATE(created_at)', date('Y-m-d'));
        $query = $this->db->get('kns1_payment');
        return $query->row();
    }

    /**
     * Get total cash collected today by a specific user
     */
    public function get_total_cash_by_user_today($user_id)
    {
        $this->db->select('SUM(debit) AS debit, SUM(credit) AS credit');
        $this->db->where('user_id', $user_id);
        $this->db->where('DATE(created_at)', date('Y-m-d'));
        $query = $this->db->get('kns1_payment');
        $row = $query->row();
        return ($row->debit ?? 0) - ($row->credit ?? 0);
    }

    /**
     * Get shift-wise cash collected today
     * $shifts = array of shift objects (id, start_time, end_time)
     */
    public function get_shift_cash_totals_today($shifts)
    {
        $shift_cash_totals = [];
        $today = date('Y-m-d');

        foreach ($shifts as $shift) {
            $this->db->select('SUM(debit) AS total_debit, SUM(credit) AS total_credit');
            $this->db->where('TIME(created_at) >=', $shift->start_time);
            $this->db->where('TIME(created_at) <=', $shift->end_time);
            $this->db->where('DATE(created_at)', $today);
            $query = $this->db->get('kns1_payment')->row();
            $shift_cash_totals[$shift->id] = ($query->total_debit ?? 0) - ($query->total_credit ?? 0);
        }

        return $shift_cash_totals;
    }

    /**
     * Get total cash collected by user (optionally today)
     */
    public function get_total_cash_by_user($user_id, $today = false)
    {
        $this->db->select('SUM(debit) AS debit, SUM(credit) AS credit');
        $this->db->where('user_id', $user_id);

        if($today) {
            $this->db->where('DATE(created_at)', date('Y-m-d'));
        }

        $query = $this->db->get('kns1_payment');
        $row = $query->row();
        return ($row->debit ?? 0) - ($row->credit ?? 0);
    }



public function get_user_cash_in_hand($user_id, $shift = null)
{
    $this->db->select('SUM(debit) as debit, SUM(credit) as credit');
    $this->db->where('user_id', $user_id);
    $this->db->where('DATE(created_at)', date('Y-m-d'));

    // optional: limit by shift timing
    if ($shift && isset($shift->start_time) && isset($shift->end_time)) {
        $this->db->where('TIME(created_at) >=', $shift->start_time);
        $this->db->where('TIME(created_at) <', $shift->end_time);
    }

    $row = $this->db->get('kns1_payment')->row();
    return ($row->debit ?? 0) - ($row->credit ?? 0);
}


// ہر دن کا total debit-credit (ایک مہینے کیلئے)
public function get_daily_totals_by_month($year, $month)
{
    $this->db->select('DATE(created_at) as day, SUM(debit) as debit, SUM(credit) as credit');
    $this->db->where('YEAR(created_at)', $year);
    $this->db->where('MONTH(created_at)', $month);
    $this->db->group_by('DATE(created_at)');
    $this->db->order_by('DATE(created_at)', 'ASC');
    return $this->db->get('kns1_payment')->result();
}

// ہر مہینے کا total debit-credit (ایک سال کیلئے)
public function get_monthly_totals_by_year($year)
{
    $this->db->select('MONTH(created_at) as month, SUM(debit) as debit, SUM(credit) as credit');
    $this->db->where('YEAR(created_at)', $year);
    $this->db->group_by('MONTH(created_at)');
    $this->db->order_by('MONTH(created_at)', 'ASC');
    return $this->db->get('kns1_payment')->result();
}


    // AJAX for chart filters
    public function chart_data()
    {
        $year  = $this->input->get('year')  ?? date('Y');
        $month = $this->input->get('month') ?? date('m');

        $daily   = $this->payment->get_daily_totals_by_month($year, $month);
        $monthly = $this->payment->get_monthly_totals_by_year($year);

        echo json_encode([
            'daily'   => $daily,
            'monthly' => $monthly
        ]);
    }



//SALARY WITHDRAWAL BY YEAR      -----------      FUNCTION START     -----------     SALARY WITHDRAWAL BY YEAR//

public function get_salary_withdrawals_by_year($year)
{
    $this->db->select("MONTH(staff_withdrawal_date) as month, SUM(staff_withdrawal_amount) as total");
    $this->db->from("kns1_staff_withdrawal");
    $this->db->where("YEAR(staff_withdrawal_date)", $year);
    $this->db->group_by("MONTH(staff_withdrawal_date)");
    $query = $this->db->get();
    
    $result = array_fill(1, 12, 0); // Default all months to 0

    foreach ($query->result() as $row) {
        $result[(int)$row->month] = (float)$row->total;
    }

    return $result;
}

    //SALARY WITHDRAWAL BY YEAR     -----------      FUNCTION END     -----------     SALARY WITHDRAWAL BY YEAR//

    //SALARY WITHDRAWAL BY MODUE GROUP BY YEAR ---  FUNCTION START     -----------     SALARY WITHDRAWAL BY MODUE GROUP BYY YEAR//

public function get_salary_withdrawals_grouped($year)
{
    $this->db->select("MONTH(staff_withdrawal_date) as month, staff_withdrawal_type, SUM(staff_withdrawal_amount) as total");
    $this->db->from("kns1_staff_withdrawal");
    $this->db->where("YEAR(staff_withdrawal_date)", $year);
    $this->db->group_by(["MONTH(staff_withdrawal_date)", "staff_withdrawal_type"]);
    return $this->db->get()->result();
}

    //SALARY WITHDRAWAL BY MODUE GROUP BY YEAR ---  FUNCTION END     -----------     SALARY WITHDRAWAL BY MODUE GROUP BYY YEAR//

    //PAYMENTS BY STAFF     -----------     FUNCTION START     -----------     PAYMENTS BY STAFF//

public function get_payments_by_staff($staff_id)
{
    $this->db->select('p.*, s.staff_name as staff_name');
    $this->db->from('kns1_payment as p');
    $this->db->join('kns1_staff as s', 's.id = p.pay_customer_id', 'left');
    $this->db->where('p.payment_module', 'Staff');
    $this->db->where('p.pay_customer_id', $staff_id);
    $this->db->order_by('p.pay_date', 'asc'); // ✅ Recent first
    return $this->db->get()->result();
}



    //PAYMENTS BY STAFF     -----------     FUNCTION END     -----------     PAYMENTS BY STAFF//


}
