<?php
namespace App\Controllers;

use App\Core\Controller;
use App\Core\Database;
use App\Core\Request;
use App\Services\SimplePdf;
use App\Services\SimpleXlsx;

class ReportController extends Controller
{
    public function index(): void
    {
        $this->render('reports/index', ['pageTitle' => 'Report Catalogue', 'pageSubtitle' => 'FR-SUP-088 to FR-SUP-098']);
    }

    public function slaCompliance(): void
    {
        [$where, $params, $summary] = $this->buildFilters(['date' => 't.created_at', 'customer' => true, 'priority' => true]);
        $rows = Database::all(
            "SELECT c.name AS customer_name, p.code AS priority,
                    COUNT(*) AS total,
                    SUM(CASE WHEN t.first_response_at IS NOT NULL AND t.is_response_breached = 0 THEN 1 ELSE 0 END) AS resp_met,
                    SUM(CASE WHEN t.resolved_at IS NOT NULL AND t.is_resolution_breached = 0 THEN 1 ELSE 0 END) AS res_met,
                    SUM(CASE WHEN t.resolved_at IS NOT NULL THEN 1 ELSE 0 END) AS res_total
             FROM tickets t JOIN customers c ON c.id = t.customer_id JOIN priorities p ON p.id = t.priority_id
             WHERE {$where}
             GROUP BY t.customer_id, t.priority_id ORDER BY c.name, p.sort_order",
            $params
        );
        $columns = ['customer_name' => 'Customer', 'priority' => 'Priority', 'total' => 'Tickets', 'resp_met' => 'Response met', 'res_met' => 'Resolution met', 'res_total' => 'Resolved'];
        $this->respond('reports/sla_compliance', 'sla_compliance', 'SLA Compliance', $rows, $columns, $summary, ['rows' => $rows]);
    }

    public function ageing(): void
    {
        [$where, $params, $summary] = $this->buildFilters(['date' => 't.created_at', 'priority' => true, 'queue' => true], "t.status NOT IN ('CLOSED','REJECTED')");
        $rows = Database::all(
            "SELECT t.id, t.ticket_no, c.name AS customer_name, t.subject, p.code AS priority, t.status,
                    TIMESTAMPDIFF(HOUR, t.created_at, NOW()) AS age_hours
             FROM tickets t JOIN customers c ON c.id = t.customer_id JOIN priorities p ON p.id = t.priority_id
             LEFT JOIN queues q ON q.id = t.queue_id
             WHERE {$where} ORDER BY age_hours DESC",
            $params
        );
        foreach ($rows as &$r) {
            $h = (int)$r['age_hours'];
            $r['band'] = $h < 24 ? '< 1 day' : ($h < 72 ? '1-3 days' : ($h < 168 ? '3-7 days' : '7+ days'));
        }
        unset($r);
        $columns = ['ticket_no' => 'Ticket', 'customer_name' => 'Customer', 'subject' => 'Subject', 'priority' => 'Priority', 'status' => 'Status', 'age_hours' => 'Age (hrs)', 'band' => 'Band'];
        $this->respond('reports/ageing', 'ticket_ageing', 'Ticket Ageing', $rows, $columns, $summary, ['rows' => $rows]);
    }

    public function breachRegister(): void
    {
        [$where, $params, $summary] = $this->buildFilters(['date' => 't.resolution_due_at', 'customer' => true, 'priority' => true, 'queue' => true], '(t.is_response_breached = 1 OR t.is_resolution_breached = 1)');
        $rows = Database::all(
            "SELECT t.ticket_no, c.name AS customer_name, q.name AS queue_name, p.code AS priority,
                    t.is_response_breached, t.is_resolution_breached, t.breach_justification, t.resolution_due_at, t.status
             FROM tickets t JOIN customers c ON c.id = t.customer_id JOIN priorities p ON p.id = t.priority_id
             LEFT JOIN queues q ON q.id = t.queue_id
             WHERE {$where}
             ORDER BY t.resolution_due_at DESC",
            $params
        );
        $columns = ['ticket_no' => 'Ticket', 'customer_name' => 'Customer', 'queue_name' => 'Queue', 'priority' => 'Priority', 'is_response_breached' => 'Response breached', 'is_resolution_breached' => 'Resolution breached', 'breach_justification' => 'Justification', 'resolution_due_at' => 'Resolution due', 'status' => 'Status'];
        $this->respond('reports/breach_register', 'breach_register', 'Breach Register', $rows, $columns, $summary, ['rows' => $rows]);
    }

    public function agentProductivity(): void
    {
        // Date range must live in the JOIN's ON clause, not WHERE — this starts from users so an
        // agent with zero tickets in the period still shows a zero row instead of disappearing.
        [, , $summary] = $this->buildFilters(['date' => 't.created_at', 'agent' => true]);

        $joinCond = ['t.assignee_id = u.id'];
        $params = [];
        $from = Request::query('from', '');
        $to = Request::query('to', '');
        if ($from !== '') { $joinCond[] = 't.created_at >= :date_from'; $params['date_from'] = $from . ' 00:00:00'; }
        if ($to !== '') { $joinCond[] = 't.created_at <= :date_to'; $params['date_to'] = $to . ' 23:59:59'; }
        $joinSql = implode(' AND ', $joinCond);

        $agentFilter = '';
        if (Request::query('agent_id')) { $agentFilter = ' AND u.id = :agent_id'; $params['agent_id'] = Request::query('agent_id'); }

        $rows = Database::all(
            "SELECT u.name AS agent_name,
                    COUNT(t.id) AS handled,
                    SUM(CASE WHEN t.status IN ('RESOLVED','CLOSED') THEN 1 ELSE 0 END) AS resolved,
                    SUM(t.reassignment_count) AS reassignments,
                    ROUND(AVG(TIMESTAMPDIFF(MINUTE, t.created_at, t.resolved_at))) AS avg_handle_minutes,
                    ROUND(AVG(cs.rating),2) AS avg_csat
             FROM users u
             LEFT JOIN tickets t ON {$joinSql}
             LEFT JOIN csat_responses cs ON cs.ticket_id = t.id
             WHERE u.role_id IN (SELECT id FROM roles WHERE code IN ('L1','L2','L3')){$agentFilter}
             GROUP BY u.id ORDER BY handled DESC",
            $params
        );
        $columns = ['agent_name' => 'Agent', 'handled' => 'Handled', 'resolved' => 'Resolved', 'reassignments' => 'Reassignments', 'avg_handle_minutes' => 'Avg. handling (min)', 'avg_csat' => 'Avg. CSAT'];
        $this->respond('reports/agent_productivity', 'agent_productivity', 'Agent Productivity', $rows, $columns, $summary, ['rows' => $rows]);
    }

    public function csat(): void
    {
        [$where, $params, $summary] = $this->buildFilters(['date' => 'cs.created_at', 'customer' => true, 'minRating' => true]);
        $rows = Database::all(
            "SELECT t.ticket_no, c.name AS customer_name, cs.rating, cs.comment, cs.created_at
             FROM csat_responses cs JOIN tickets t ON t.id = cs.ticket_id JOIN customers c ON c.id = t.customer_id
             WHERE {$where}
             ORDER BY cs.created_at DESC",
            $params
        );
        $distribution = Database::all('SELECT rating, COUNT(*) AS c FROM csat_responses GROUP BY rating ORDER BY rating DESC');
        $columns = ['ticket_no' => 'Ticket', 'customer_name' => 'Customer', 'rating' => 'Rating', 'comment' => 'Comment', 'created_at' => 'Date'];
        $this->respond('reports/csat', 'csat', 'CSAT', $rows, $columns, $summary, ['rows' => $rows, 'distribution' => $distribution]);
    }

    public function entitlement(): void
    {
        [$where, $params, $summary] = $this->buildFilters(['customer' => true, 'tier' => true], 'ct.is_active = 1');
        $rows = Database::all(
            "SELECT c.name AS customer_name, ct.contract_no, ct.tier, ct.support_hours_total, ct.support_hours_used, ct.end_date,
                    ROUND((ct.support_hours_used / NULLIF(ct.support_hours_total,0)) * 100) AS pct_used
             FROM contracts ct JOIN customers c ON c.id = ct.customer_id
             WHERE {$where}
             ORDER BY pct_used DESC",
            $params
        );
        $columns = ['customer_name' => 'Customer', 'contract_no' => 'Contract', 'tier' => 'Tier', 'support_hours_total' => 'Hours total', 'support_hours_used' => 'Hours used', 'end_date' => 'Expiry', 'pct_used' => '% used'];
        $this->respond('reports/entitlement', 'entitlement', 'Entitlement Consumption', $rows, $columns, $summary, ['rows' => $rows]);
    }

    // ------------------------------------------------------------------
    // Shared filter machinery
    // ------------------------------------------------------------------

    /**
     * Parses whichever of from/to/customer_id/priority_id/queue_id/agent_id/tier/min_rating
     * are relevant for this report (per $enabled) into a WHERE clause + bound params, plus a
     * human-readable summary string ("Filters: ... to ... · Customer: ... ") reused by the
     * page heading, the PDF subtitle and the print view.
     *
     * @param array<string,bool|string> $enabled which filters this report accepts, and which column 'date' binds to
     */
    private function buildFilters(array $enabled, ?string $baseWhere = null): array
    {
        $where = [$baseWhere ?: '1=1'];
        $params = [];
        $summary = [];

        if (!empty($enabled['date'])) {
            $from = Request::query('from', '');
            $to = Request::query('to', '');
            if ($from !== '') {
                $where[] = "{$enabled['date']} >= :date_from"; $params['date_from'] = $from . ' 00:00:00';
                $summary[] = 'From ' . $from;
            }
            if ($to !== '') {
                $where[] = "{$enabled['date']} <= :date_to"; $params['date_to'] = $to . ' 23:59:59';
                $summary[] = 'to ' . $to;
            }
        }
        if (!empty($enabled['customer']) && Request::query('customer_id')) {
            $where[] = 't.customer_id = :customer_id';
            $params['customer_id'] = Request::query('customer_id');
            $name = Database::scalar('SELECT name FROM customers WHERE id = :id', ['id' => Request::query('customer_id')]);
            if ($name) $summary[] = "Customer: {$name}";
        }
        if (!empty($enabled['priority']) && Request::query('priority_id')) {
            $where[] = 't.priority_id = :priority_id';
            $params['priority_id'] = Request::query('priority_id');
            $code = Database::scalar('SELECT code FROM priorities WHERE id = :id', ['id' => Request::query('priority_id')]);
            if ($code) $summary[] = "Priority: {$code}";
        }
        if (!empty($enabled['queue']) && Request::query('queue_id')) {
            $where[] = 't.queue_id = :queue_id';
            $params['queue_id'] = Request::query('queue_id');
            $name = Database::scalar('SELECT name FROM queues WHERE id = :id', ['id' => Request::query('queue_id')]);
            if ($name) $summary[] = "Queue: {$name}";
        }
        if (!empty($enabled['agent']) && Request::query('agent_id')) {
            $name = Database::scalar('SELECT name FROM users WHERE id = :id', ['id' => Request::query('agent_id')]);
            if ($name) $summary[] = "Agent: {$name}";
            // NOT added to $where here — agentProductivity applies it against the join condition instead.
        }
        if (!empty($enabled['tier']) && Request::query('tier')) {
            $where[] = 'ct.tier = :tier';
            $params['tier'] = Request::query('tier');
            $summary[] = 'Tier: ' . Request::query('tier');
        }
        if (!empty($enabled['minRating']) && Request::query('min_rating')) {
            $where[] = 'cs.rating >= :min_rating';
            $params['min_rating'] = Request::query('min_rating');
            $summary[] = 'Rating ≥ ' . Request::query('min_rating');
        }

        return [implode(' AND ', $where), $params, $summary ? ('Filters: ' . implode(' ', $summary)) : 'No filters applied'];
    }

    /** Dropdown option lists shared by every report's filter bar. */
    private function filterOptions(): array
    {
        return [
            'customers' => Database::all('SELECT id, name FROM customers WHERE is_active = 1 ORDER BY name'),
            'priorities' => Database::all('SELECT id, code, name FROM priorities WHERE is_active = 1 ORDER BY sort_order'),
            'queues' => Database::all('SELECT id, name FROM queues WHERE is_active = 1 ORDER BY name'),
            'agents' => Database::all("SELECT u.id, u.name FROM users u JOIN roles r ON r.id = u.role_id WHERE r.code IN ('L1','L2','L3') AND u.is_active = 1 ORDER BY u.name"),
        ];
    }

    private function respond(string $view, string $filename, string $title, array $rows, array $columns, string $filterSummary, array $extraData): void
    {
        $format = Request::query('export', '');
        if ($format === 'csv') { $this->downloadCsv($filename, $rows, $columns); return; }
        if ($format === 'xlsx') { $this->downloadXlsx($filename, $title, $rows, $columns); return; }
        if ($format === 'pdf') { $this->downloadPdf($filename, $title, $rows, $columns, $filterSummary); return; }

        $data = array_merge($extraData, [
            'pageTitle' => $title,
            'pageSubtitle' => count($rows) . ' row(s) · ' . $filterSummary,
            'filterSummary' => $filterSummary,
            'filters' => $this->filterOptions(),
            'query' => Request::all(),
        ]);

        if (Request::query('view') === 'print') {
            $data['columns'] = $columns;
            $data['title'] = $title;
            $this->render('reports/_print', $data, null);
            return;
        }

        $this->render($view, $data);
    }

    private function downloadCsv(string $filename, array $rows, array $columns): void
    {
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '_' . date('Ymd_His') . '.csv"');
        $out = fopen('php://output', 'w');
        fputcsv($out, array_values($columns), ',', '"', '\\');
        foreach ($rows as $row) {
            fputcsv($out, $this->projectColumns($row, $columns), ',', '"', '\\');
        }
        fclose($out);
        exit;
    }

    private function downloadXlsx(string $filename, string $title, array $rows, array $columns): void
    {
        $data = array_map(fn($row) => $this->projectColumns($row, $columns), $rows);
        $bytes = SimpleXlsx::build(array_values($columns), $data, $title);

        if ($bytes === null) {
            // ext-zip unavailable on this host — degrade to CSV rather than fail outright.
            $this->downloadCsv($filename, $rows, $columns);
            return;
        }

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '_' . date('Ymd_His') . '.xlsx"');
        header('Content-Length: ' . strlen($bytes));
        echo $bytes;
        exit;
    }

    private function downloadPdf(string $filename, string $title, array $rows, array $columns, string $filterSummary): void
    {
        $pdf = new SimplePdf($title, $filterSummary);
        $pdf->setColumns(array_map(fn($label) => ['label' => $label], array_values($columns)));
        $data = array_map(fn($row) => $this->projectColumns($row, $columns), $rows);
        $pdf->render($data, 'Generated ' . date('d M Y, H:i'));
        $bytes = $pdf->output();

        header('Content-Type: application/pdf');
        header('Content-Disposition: attachment; filename="' . $filename . '_' . date('Ymd_His') . '.pdf"');
        header('Content-Length: ' . strlen($bytes));
        echo $bytes;
        exit;
    }

    /** Reduce a full row to just the exported columns, in export order, formatted for display. */
    private function projectColumns(array $row, array $columns): array
    {
        $out = [];
        foreach ($columns as $key => $label) {
            $val = $row[$key] ?? '';
            if ($key === 'is_response_breached' || $key === 'is_resolution_breached') {
                $val = $val ? 'Yes' : 'No';
            }
            $out[] = $val;
        }
        return $out;
    }
}
