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

class Reporting_model extends CI_Model
{
    /**
     * Get Department + Shift + Sponsor stats by date range
     * Returns structured data ready for reporting
     */
    
public function departmentStats($from, $to)
{
    // Step 1: Aggregate token amounts to avoid duplicates from multiple services
    $query = $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, '') 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,
            SUM(CASE WHEN t.balance > 0 THEN t.balance ELSE 0 END) AS due_amount,
            SUM(
                t.total_fee 
                - t.discount_amount 
                - t.refund_amount 
                - CASE WHEN t.balance > 0 THEN t.balance ELSE 0 END
            ) AS net_amount
        FROM kns1_tokens t
        JOIN kns1_shifts s ON s.id = t.shift_id
        JOIN (
            SELECT ts.token_id, MAX(srv.department_id) AS department_id
            FROM kns1_token_services ts
            JOIN kns1_services srv ON srv.id = ts.service_id
            GROUP BY ts.token_id
        ) token_dept ON token_dept.token_id = t.id
        JOIN kns1_departments d ON d.id = token_dept.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
    ", [$from, $to]);

    $rows = $query->result();

    // -------------------------
    // Build structured stats array
    // -------------------------
    $stats = [];

    foreach ($rows as $r) {
        $deptId = $r->department_id;
        $funded = $r->funded_by ?: '';

        if (!isset($stats[$deptId])) {
            $stats[$deptId] = [
                'department_name' => $r->department_name,
                'sponsors' => []
            ];
        }

        if (!isset($stats[$deptId]['sponsors'][$funded])) {
            $stats[$deptId]['sponsors'][$funded] = [
                'funded_by'      => $funded,
                'shifts'         => [],
                'total_tokens'   => 0,
                'total_amount'   => 0,
                'total_discount' => 0,
                'total_refund'   => 0,
                'due_amount'     => 0,
                'net_total'      => 0,

                'AllDay_tokens'      => 0,
                'gross_amount'      => 0,
            ];
        }

        $sp =& $stats[$deptId]['sponsors'][$funded];

        // Shift token count
        $sp['shifts'][$r->shift_id] = $r->token_count;

        // Totals
        $sp['AllDay_tokens']   += $r->token_count;
        $sp['gross_amount']   += $r->total_amount;
        

        $sp['total_tokens']   += $r->token_count;
        $sp['total_amount']   += $r->total_amount;
        $sp['total_discount'] += $r->total_discount;
        $sp['total_refund']   += $r->total_refund;
        $sp['due_amount']     += $r->due_amount;
        $sp['net_total']      += $r->net_amount;
    }

    // Convert sponsor arrays to numeric
    foreach ($stats as $deptId => $dept) {
        $stats[$deptId]['sponsors'] = array_values($dept['sponsors']);
    }

    return array_values($stats);
}

}
