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

class token_model extends CI_Model {


    // Get the last token number for a specific shift
    public function getLastTokenForShift($shift_id)
    {
        $this->db->select('MAX(token_number) as last_token'); // Get the maximum token number
        $this->db->from('kns1_tokens');
        $this->db->where('shift_id', $shift_id);  // Filter by the shift_id
        $query = $this->db->get();

        // Return the last token or 0 if no tokens exist
        $result = $query->row();
        return isset($result->last_token) ? $result->last_token : 0;
    }

public function get_shift_tokens($shift_id, $date)
{
    $this->db->select('MAX(token_number) as max_token');
    $this->db->from('kns1_tokens');
    $this->db->where('shift_id', $shift_id);
    $this->db->where('DATE(created_at)', $date);
    $query = $this->db->get();
    $result = $query->row();

    return ($result->max_token) ? $result->max_token : 0;
}










// public function get_by_phone($phone) {
//     $this->db->where('phone', $phone);
//     $query = $this->db->get('tokens');  // <-- 'patients' ko apne table name se replace karein
//     return $query->row();  // Returns single row object or null
// }



    // Add Data Function
public function create_token(
    $shift_id,
    $token_number,
    $name,
    $phone,
    $gender = null,
    $age = null,
    $age_unit = null,
    $funded_by = null,
    $discount_percent = null,
    $discount_amount = null
) {
    $data = [
        'token_number'     => $token_number,
        'shift_id'         => $shift_id,
        'created_at'       => date('Y-m-d H:i:s'),
        'name'             => $name,
        'phone'            => $phone,
        'gender'           => $gender,
        'age'              => $age,
        'age_unit'         => $age_unit,
        'funded_by'        => $funded_by,
        'discount_percent' => $discount_percent,
        'discount_amount'  => $discount_amount 
    ];
    $this->db->insert('kns1_tokens', $data);
    return $this->db->insert_id();
}

    // public function create($options)
    // {
    //     $this->db->insert('kns1_tokens', $options);
    //     return $this->db->insert_id();
    // }

    // Get All Function
    public function get_all($limit = NULL, $offset = NULL)
    {
        $this->db->order_by('id', 'ASC');
        $query = $this->db->get('kns1_tokens', $limit, $offset);
        return $query->result();
    }

    // Delete Function
    public function destroy($id)
    {
        $this->db->where('id', $id);
        $this->db->delete('kns1_tokens');
        return $this->db->affected_rows();
    }

    // Update Function
    public function update($id, $options)
    {
        $this->db->where('id', $id);
        $this->db->update('kns1_tokens', $options);
        return $id;
    }

    // Get ID by Name Funcion
    public function getNameBy($id)
    {
        $this->db->select('name');
        $this->db->where('id', $id);
        $query = $this->db->get('kns1_tokens');
        return $query->row()->name;
    }

    // Get ID Funcion
    // public function get_by($categoryId)
    // {
    //     $this->db->where('id', $categoryId);
    //     $query = $this->db->get('kns1_tokens');
    //     return $query->row();
    // }


public function get_by($id)
{
    $this->db->select('kns1_tokens.*, kns1_shifts.name as shift_name');  // Select the shift name along with token data
    $this->db->from('kns1_tokens');
    $this->db->join('kns1_shifts', 'kns1_shifts.id = kns1_tokens.shift_id');  // Join to get the shift name
    $this->db->where('kns1_tokens.id', $id);  // Use the provided ID to fetch the token
    $query = $this->db->get();
    return $query->row();  // Fetch a single row (token with shift name)
}
        


// Get services with fee for a token
public function get_services($token_id)
{
    $this->db->select('
        ts.id,
        ts.fee,
        s.name  AS service_name,
        ss.name AS sub_service_name,
        d.name  AS department_name
    ');
    $this->db->from('token_services ts');
    $this->db->join('services s', 's.id = ts.service_id', 'left');
    $this->db->join('sub_services ss', 'ss.id = ts.sub_service_id', 'left');
    $this->db->join('departments d', 'd.id = s.department_id', 'left');
    $this->db->where('ts.token_id', $token_id);

    return $this->db->get()->result_array();
}



// Get total fee for a token
public function get_token_total($token_id)
{
    $this->db->select('SUM(fee) as total_amount');
    $this->db->from('token_services');
    $this->db->where('token_id', $token_id);
    return $this->db->get()->row_array();
}



public function count_today_tokens() {
        $today = date('Y-m-d');
        $this->db->from('kns1_tokens');
        $this->db->where('DATE(created_at)', $today);
        return $this->db->count_all_results();
    }

    // Shift-wise tokens for today
    public function count_today_tokens_by_shift() {
        $today = date('Y-m-d');

        $this->db->select('s.id as shift_id, s.name as shift_name, COUNT(t.id) as token_count');
        $this->db->from('kns1_shifts s');
        $this->db->join('kns1_tokens t', 't.shift_id = s.id AND DATE(t.created_at) = "'.$today.'"', 'left');
        $this->db->group_by('s.id, s.name');
        $this->db->order_by('s.start_time', 'ASC');

        return $this->db->get()->result();
    }


public function getAllTokens()
{
    $this->db->select('
        kns1_tokens.*,
        kns1_shifts.name as shift_name,
        ts.id as token_service_id,
        ts.fee,
        s.name as service_name,
        ss.name as sub_service_name,
        d.name as department_name
    ');
    $this->db->from('kns1_tokens');
    $this->db->join('kns1_shifts', 'kns1_shifts.id = kns1_tokens.shift_id', 'left');
    $this->db->join('kns1_token_services ts', 'ts.token_id = kns1_tokens.id', 'left');
    $this->db->join('kns1_services s', 's.id = ts.service_id', 'left');
    $this->db->join('kns1_sub_services ss', 'ss.id = ts.sub_service_id', 'left');
    $this->db->join('kns1_departments d', 'd.id = s.department_id', 'left');
    $this->db->order_by('kns1_tokens.id', 'desc');

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

    // Group services under their tokens
    $tokens = [];
    foreach ($results as $row) {
        $token_id = $row->id;
        if (!isset($tokens[$token_id])) {
            // Clone the token data without services first
            $tokens[$token_id] = $row;
            $tokens[$token_id]->services = [];
        }
        if ($row->token_service_id) {
            $tokens[$token_id]->services[] = [
                'token_service_id' => $row->token_service_id,
                'fee' => $row->fee,
                'service_name' => $row->service_name,
                'sub_service_name' => $row->sub_service_name,
                'department_name' => $row->department_name
            ];
        }
    }

    // Return tokens as a numerically indexed array
    return array_values($tokens);
}


















public function get_filtered_tokens($filters = [])
{
    // $this->db->select('t.*, s.name as shift_name, d.name as department_name');
    // $this->db->from('kns1_tokens t');
    // $this->db->join('kns1_shifts s', 's.id = t.shift_id','left');
    // $this->db->join('kns1_token_services ts','ts.token_id=t.id','left');
    // $this->db->join('kns1_services srv','srv.id=ts.service_id','left');
    // $this->db->join('kns1_departments d','d.id=srv.department_id','left');

    $this->db->select('
        t.*,
        s.name as shift_name,
        d.name as department_name,
        ts.service_id,
        srv.name as service_name
    ');
    $this->db->from('kns1_tokens t');
    $this->db->join('kns1_shifts s', 's.id = t.shift_id','left');
    $this->db->join('kns1_token_services ts','ts.token_id=t.id','left');
    $this->db->join('kns1_services srv','srv.id=ts.service_id','left');
    $this->db->join('kns1_departments d','d.id=srv.department_id','left');


    if (!empty($filters['date_from'])) {
        $this->db->where('DATE(t.created_at) >=', $filters['date_from']);
    }
    if (!empty($filters['date_to'])) {
        $this->db->where('DATE(t.created_at) <=', $filters['date_to']);
    }
    if (!empty($filters['shift_id'])) {
        $this->db->where('t.shift_id', $filters['shift_id']);
    }
    if (!empty($filters['department_id'])) {
        $this->db->where('d.id', $filters['department_id']);
    }
    if (!empty($filters['service_id'])) {
        $this->db->where('srv.id', $filters['service_id']);
    }
    if (!empty($filters['sub_service_id'])) {
        $this->db->where('ts.sub_service_id', $filters['sub_service_id']);
    }
    if (!empty($filters['gender'])) {
        $this->db->where('t.gender', $filters['gender']);
    }
    if (!empty($filters['funded_by'])) {
        $this->db->where('t.funded_by', $filters['funded_by']);
    }

    $this->db->group_by('t.id');
    $this->db->order_by('t.created_at','DESC');
    return $this->db->get()->result();
}


public function get_by_phone($phone)
{
    $cleanPhone = preg_replace('/\D/', '', trim($phone));

    $sql = "SELECT * FROM kns1_tokens 
            WHERE REPLACE(REPLACE(TRIM(phone), '-', ''), ' ', '') = ? 
            ORDER BY created_at DESC 
            LIMIT 1";

    $query = $this->db->query($sql, [$cleanPhone]);

    if ($query->num_rows() > 0) {
        return $query->row();
    } else {
        return null;
    }
}










        public function todayDepartmentStatsDynamic()
{
    return $this->db->query("
        SELECT 
            d.id   AS department_id,
            d.name AS department_name,

            s.id   AS shift_id,
            s.name AS shift_name,

            IFNULL(t.funded_by, 'Self / Walk-in') AS funded_by,

            COUNT(DISTINCT t.id) AS token_count,
            SUM(t.total_fee) AS total_amount,
            SUM(t.discount_amount) AS total_discount,
            SUM(t.refund_amount) AS total_refund

        FROM kns1_tokens t
        JOIN kns1_shifts s ON s.id = t.shift_id
        JOIN kns1_token_services ts ON ts.token_id = t.id
        JOIN kns1_services srv ON srv.id = ts.service_id
        JOIN kns1_departments d ON d.id = srv.department_id

        WHERE DATE(t.created_at) = CURDATE()

        GROUP BY d.id, s.id, funded_by
        ORDER BY d.name, s.name, funded_by
    ")->result();
}



public function departmentStatsByDateRange($fromDate, $toDate)
{
    return $this->db->query("
        SELECT 
            d.id   AS department_id,
            d.name AS department_name,

            s.id   AS shift_id,
            s.name AS shift_name,

            IFNULL(t.funded_by, 'Self / Walk-in') AS funded_by,

            COUNT(DISTINCT t.id) AS token_count,
            SUM(t.total_fee) AS total_amount,
            SUM(t.discount_amount) AS total_discount,
            SUM(t.refund_amount) AS total_refund

        FROM kns1_tokens t
        JOIN kns1_shifts s ON s.id = t.shift_id
        JOIN kns1_token_services ts ON ts.token_id = t.id
        JOIN kns1_services srv ON srv.id = ts.service_id
        JOIN kns1_departments d ON d.id = srv.department_id

        WHERE DATE(t.created_at) BETWEEN ? AND ?

        GROUP BY d.id, s.id, funded_by
        ORDER BY d.name, s.name, funded_by
    ", [$fromDate, $toDate])->result();
}





public function todayDepartmentStats()
{
    return $this->db->query("
        SELECT 
            d.id AS department_id,
            d.name AS department_name,

            COUNT(DISTINCT CASE WHEN s.name = 'Shift A' THEN t.id END) AS shift_a_tokens,
            COUNT(DISTINCT CASE WHEN s.name = 'Shift B' THEN t.id END) AS shift_b_tokens,

            COUNT(DISTINCT t.id) AS total_tokens,
            AVG(t.total_fee) AS avg_token_amount,
            SUM(t.total_fee) AS total_token_amount,
            SUM(t.discount_amount) AS total_discount,
            SUM(t.refund_amount) AS total_refund,

            (
                SUM(t.total_fee)
                - SUM(t.discount_amount)
                - SUM(t.refund_amount)
            ) AS net_total

        FROM kns1_tokens t
        JOIN kns1_shifts s ON s.id = t.shift_id
        JOIN kns1_token_services ts ON ts.token_id = t.id
        JOIN kns1_services srv ON srv.id = ts.service_id
        JOIN kns1_departments d ON d.id = srv.department_id
        WHERE DATE(t.created_at) = CURDATE()
        GROUP BY d.id
        ORDER BY d.name ASC
    ")->result();
}





public function count_tokens_today_by_shift()
{
    $today = date('Y-m-d');

    $query = $this->db
        ->select('shift_id, COUNT(*) as token_count')
        ->where('DATE(created_at)', $today)
        ->group_by('shift_id')
        ->get('kns1_tokens');

    return $query->result();
}
public function count_tokens_today_grouped_by_shift()
{
    $today = date('Y-m-d');

    $query = $this->db
        ->select('s.id as shift_id, s.name as shift_name, COUNT(t.id) as token_count')
        ->from('kns1_tokens t')
        ->join('kns1_shifts s', 't.shift_id = s.id')
        ->where('DATE(t.created_at)', $today)
        ->group_by('t.shift_id')
        ->get();

    return $query->result();
}
public function count_tokens_today_grouped_by_department()
{
    $today = date('Y-m-d');

    $query = $this->db
        ->select('d.id as department_id, d.name as department_name, COUNT(DISTINCT ts.token_id) as token_count')
        ->from('kns1_token_services ts')
        ->join('kns1_departments d', 'ts.department_id = d.id')
        ->join('kns1_tokens t', 'ts.token_id = t.id')
        ->where('DATE(t.created_at)', $today)
        ->group_by('d.id')
        ->get();

    return $query->result();
}














}