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

class Support_dashboard extends AdminController
{
    public function __construct()
    {
        parent::__construct();
        $this->load->model('tickets_model');
        $this->load->model('clients_model');
    }

    public function index()
    {
        $isAdmin  = is_admin();
        $staffId  = get_staff_user_id();
        $today    = date('Y-m-d 23:59:59');
        $range30  = date('Y-m-d 00:00:00', strtotime('-30 days'));

        // ---- Ticket scope ----
        $ticketWhere = '';
        if (!$isAdmin) {
            $ticketWhere = ' AND assigned = ' . (int)$staffId;
        }

        // ---- Customer scope: staff can only see customers they are admin on ----
        $customerJoin = '';
        if (!$isAdmin) {
            $customerJoin = ' AND userid IN (SELECT customer_id FROM ' . db_prefix() . 'customer_admins WHERE staff_id = ' . (int)$staffId . ')';
        }

        // ============ ROW 1: Top stat cards ============
        $data['total_customers']  = $this->db->query('SELECT COUNT(*) c FROM ' . db_prefix() . 'clients WHERE 1=1' . $customerJoin)->row()->c;
        $data['new_customers_30'] = $this->db->query('SELECT COUNT(*) c FROM ' . db_prefix() . 'clients WHERE datecreated >= ' . $this->db->escape($range30) . $customerJoin)->row()->c;

        $data['dormant_customers'] = $this->db->query('
            SELECT COUNT(*) c FROM ' . db_prefix() . 'clients cl
            WHERE 1=1' . str_replace('userid', 'cl.userid', $customerJoin) . '
            AND NOT EXISTS (
                SELECT 1 FROM ' . db_prefix() . 'tickets t
                WHERE t.userid = cl.userid AND t.date >= ' . $this->db->escape($range30) . '
            )')->row()->c;

        $data['open_tickets']    = $this->db->query('SELECT COUNT(*) c FROM ' . db_prefix() . 'tickets WHERE status IN (1,2)' . $ticketWhere)->row()->c;
        $data['overdue_tickets'] = $this->db->query('SELECT COUNT(*) c FROM ' . db_prefix() . 'tickets WHERE status IN (1,2) AND date < DATE_SUB(NOW(), INTERVAL 3 DAY)' . $ticketWhere)->row()->c;
        $data['closed_30']       = $this->db->query('SELECT COUNT(*) c FROM ' . db_prefix() . 'tickets WHERE status = 5 AND lastreply >= ' . $this->db->escape($range30) . $ticketWhere)->row()->c;

        // ============ ROW 2: SLA cards ============
        $frt = $this->db->query('
            SELECT AVG(TIMESTAMPDIFF(MINUTE, t.date, r.date)) avg_minutes
            FROM ' . db_prefix() . 'tickets t
            JOIN ' . db_prefix() . 'ticket_replies r ON r.ticketid = t.ticketid
            WHERE r.admin IS NOT NULL AND t.date >= ' . $this->db->escape($range30) .
            str_replace('assigned', 't.assigned', $ticketWhere) . '
            AND r.date = (
                SELECT MIN(r2.date) FROM ' . db_prefix() . 'ticket_replies r2
                WHERE r2.ticketid = t.ticketid AND r2.admin IS NOT NULL
            )')->row();
        $data['avg_first_response_min'] = $frt && $frt->avg_minutes ? (int)$frt->avg_minutes : 0;

        $art = $this->db->query('
            SELECT AVG(TIMESTAMPDIFF(HOUR, date, lastreply)) avg_hours
            FROM ' . db_prefix() . 'tickets
            WHERE status = 5 AND lastreply >= ' . $this->db->escape($range30) . $ticketWhere)->row();
        $data['avg_resolution_hours'] = $art && $art->avg_hours ? (int)$art->avg_hours : 0;

        // ============ ROW 3: Distribution charts ============
        $data['by_status'] = $this->db->query('
            SELECT s.name, COUNT(t.ticketid) c
            FROM ' . db_prefix() . 'tickets_status s
            LEFT JOIN ' . db_prefix() . 'tickets t ON t.status = s.ticketstatusid
                AND t.date >= ' . $this->db->escape($range30) . str_replace('assigned', 't.assigned', $ticketWhere) . '
            GROUP BY s.ticketstatusid, s.name ORDER BY s.statusorder')->result_array();

        $data['by_priority'] = $this->db->query('
            SELECT p.name, COUNT(t.ticketid) c
            FROM ' . db_prefix() . 'tickets_priorities p
            LEFT JOIN ' . db_prefix() . 'tickets t ON t.priority = p.priorityid
                AND t.date >= ' . $this->db->escape($range30) . str_replace('assigned', 't.assigned', $ticketWhere) . '
            GROUP BY p.priorityid, p.name')->result_array();

        $data['by_department'] = $this->db->query('
            SELECT d.name, COUNT(t.ticketid) c
            FROM ' . db_prefix() . 'departments d
            LEFT JOIN ' . db_prefix() . 'tickets t ON t.department = d.departmentid
                AND t.date >= ' . $this->db->escape($range30) . str_replace('assigned', 't.assigned', $ticketWhere) . '
            GROUP BY d.departmentid, d.name')->result_array();

        // ============ ROW 4: Trend series (last 30 days) ============
        $data['tickets_trend']   = $this->buildDailySeries('tickets', 'date', $ticketWhere);
        $data['customers_trend'] = $this->buildDailySeries('clients', 'datecreated', $customerJoin);

        // ============ ROW 5: Tables ============
        $data['recent_open_tickets'] = $this->db->query('
            SELECT t.ticketid, t.subject, t.date, t.priority, t.status,
                c.company, CONCAT(ct.firstname," ",ct.lastname) contact_name,
                s.firstname AS staff_first, s.lastname AS staff_last
            FROM ' . db_prefix() . 'tickets t
            LEFT JOIN ' . db_prefix() . 'clients c ON c.userid = t.userid
            LEFT JOIN ' . db_prefix() . 'contacts ct ON ct.id = t.contactid
            LEFT JOIN ' . db_prefix() . 'staff s ON s.staffid = t.assigned
            WHERE t.status IN (1,2)' . $ticketWhere . '
            ORDER BY t.date DESC LIMIT 10')->result_array();

        $data['top_customers'] = $this->db->query('
            SELECT c.userid, c.company, COUNT(t.ticketid) tickets
            FROM ' . db_prefix() . 'clients c
            JOIN ' . db_prefix() . 'tickets t ON t.userid = c.userid
                ' . ($ticketWhere ? 'AND ' . substr($ticketWhere, 5) : '') . '
            WHERE 1=1' . str_replace('userid', 'c.userid', $customerJoin) . '
            GROUP BY c.userid, c.company
            ORDER BY tickets DESC LIMIT 10')->result_array();

        $data['dormant_list'] = $this->db->query('
            SELECT c.userid, c.company, c.datecreated
            FROM ' . db_prefix() . 'clients c
            WHERE NOT EXISTS (
                SELECT 1 FROM ' . db_prefix() . 'tickets t
                WHERE t.userid = c.userid AND t.date >= ' . $this->db->escape($range30) . '
            )' . str_replace('userid', 'c.userid', $customerJoin) . '
            ORDER BY c.datecreated DESC LIMIT 10')->result_array();

        $data['title'] = 'Support Overview';
        $this->load->view('admin/support_dashboard/index', $data);
    }

    private function buildDailySeries($table, $dateCol, $extraWhere)
    {
        $rows = $this->db->query('
            SELECT DATE(' . $dateCol . ') d, COUNT(*) c
            FROM ' . db_prefix() . $table . '
            WHERE ' . $dateCol . ' >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)' . $extraWhere . '
            GROUP BY DATE(' . $dateCol . ')')->result_array();

        $lookup = [];
        foreach ($rows as $r) {
            $lookup[$r['d']] = (int)$r['c'];
        }
        $series = [];
        for ($i = 29; $i >= 0; $i--) {
            $d = date('Y-m-d', strtotime("-$i days"));
            $series[] = ['date' => $d, 'count' => $lookup[$d] ?? 0];
        }
        return $series;
    }
}
