<?php
namespace App\Controllers;

use App\Core\Auth;
use App\Core\Controller;
use App\Core\Database;

class DashboardController extends Controller
{
    public function index(): void
    {
        $openTickets = (int)Database::scalar("SELECT COUNT(*) FROM tickets WHERE status NOT IN ('CLOSED','REJECTED')");
        $breached = (int)Database::scalar("SELECT COUNT(*) FROM tickets WHERE (is_response_breached = 1 OR is_resolution_breached = 1) AND status NOT IN ('CLOSED','REJECTED')");
        $dueSoon = (int)Database::scalar("SELECT COUNT(*) FROM tickets WHERE status NOT IN ('CLOSED','REJECTED','RESOLVED') AND resolution_due_at BETWEEN NOW() AND (NOW() + INTERVAL 4 HOUR)");
        $resolvedMtd = (int)Database::scalar("SELECT COUNT(*) FROM tickets WHERE resolved_at >= DATE_FORMAT(NOW(), '%Y-%m-01')");
        $csatAvg = Database::scalar("SELECT AVG(rating) FROM csat_responses WHERE created_at >= (NOW() - INTERVAL 90 DAY)");
        $csatCount = (int)Database::scalar("SELECT COUNT(*) FROM csat_responses WHERE created_at >= (NOW() - INTERVAL 90 DAY)");
        $fcr = Database::scalar("SELECT (SUM(CASE WHEN reassignment_count = 0 THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0)) * 100 FROM tickets WHERE status IN ('RESOLVED','CLOSED')");

        // Week-over-week created volume, for a small honest trend indicator (not a fabricated backlog delta).
        $createdThisWeek = (int)Database::scalar("SELECT COUNT(*) FROM tickets WHERE created_at >= (NOW() - INTERVAL 7 DAY)");
        $createdPriorWeek = (int)Database::scalar("SELECT COUNT(*) FROM tickets WHERE created_at >= (NOW() - INTERVAL 14 DAY) AND created_at < (NOW() - INTERVAL 7 DAY)");
        // Same elapsed-days window, one month back — a fair "on pace vs last month" comparison
        // rather than partial-month-vs-full-month, which would always read as a decline.
        $resolvedSamePaceLastMonth = (int)Database::scalar(
            "SELECT COUNT(*) FROM tickets
             WHERE resolved_at >= (DATE_FORMAT(NOW(), '%Y-%m-01') - INTERVAL 1 MONTH)
             AND resolved_at < (NOW() - INTERVAL 1 MONTH)"
        );

        $attention = Database::all(
            "SELECT t.*, c.name AS customer_name, p.code AS p_code, p.name AS p_name, p.color AS p_color,
                    u.name AS assignee_name
             FROM tickets t
             JOIN customers c ON c.id = t.customer_id
             JOIN priorities p ON p.id = t.priority_id
             LEFT JOIN users u ON u.id = t.assignee_id
             WHERE t.status NOT IN ('CLOSED','REJECTED')
             ORDER BY (t.is_resolution_breached = 1) DESC, t.resolution_due_at ASC
             LIMIT 8"
        );

        $volumeByDay = Database::all(
            "SELECT DATE(created_at) AS d, COUNT(*) AS c FROM tickets
             WHERE created_at >= (NOW() - INTERVAL 7 DAY) GROUP BY DATE(created_at) ORDER BY d"
        );

        $totalTickets = (int)Database::scalar('SELECT COUNT(*) FROM tickets');
        $firstRespMet = (int)Database::scalar("SELECT COUNT(*) FROM tickets WHERE first_response_at IS NOT NULL AND is_response_breached = 0");
        $firstRespTotal = (int)Database::scalar("SELECT COUNT(*) FROM tickets WHERE first_response_at IS NOT NULL");
        $resMet = (int)Database::scalar("SELECT COUNT(*) FROM tickets WHERE resolved_at IS NOT NULL AND is_resolution_breached = 0");
        $resTotal = (int)Database::scalar("SELECT COUNT(*) FROM tickets WHERE resolved_at IS NOT NULL");
        $p1ResTotal = (int)Database::scalar("SELECT COUNT(*) FROM tickets t JOIN priorities p ON p.id=t.priority_id WHERE p.code='P1' AND t.resolved_at IS NOT NULL");
        $p1ResMet = (int)Database::scalar("SELECT COUNT(*) FROM tickets t JOIN priorities p ON p.id=t.priority_id WHERE p.code='P1' AND t.resolved_at IS NOT NULL AND t.is_resolution_breached=0");

        $byCategory = Database::all(
            "SELECT COALESCE(cat.name, sub.name) AS name, COUNT(*) AS c FROM tickets t
             JOIN categories cat ON cat.id = t.category_id
             LEFT JOIN categories sub ON sub.id = t.sub_category_id
             WHERE t.status NOT IN ('CLOSED','REJECTED')
             GROUP BY t.category_id ORDER BY c DESC LIMIT 6"
        );

        $hour = (int)date('G');
        $greeting = $hour < 12 ? 'Good morning' : ($hour < 17 ? 'Good afternoon' : 'Good evening');
        $firstName = explode(' ', (Auth::user()['name'] ?? ''))[0] ?? '';

        $this->render('dashboard/index', [
            'pageTitle' => 'Support Dashboard',
            'pageSubtitle' => date('l, d M Y · H:i'),
            'greeting' => trim("{$greeting}, {$firstName}"),
            'openTickets' => $openTickets,
            'breached' => $breached,
            'dueSoon' => $dueSoon,
            'resolvedMtd' => $resolvedMtd,
            'csatAvg' => $csatAvg ? round((float)$csatAvg, 1) : null,
            'csatCount' => $csatCount,
            'fcr' => $fcr ? round((float)$fcr) : null,
            'attention' => $attention,
            'volumeByDay' => $volumeByDay,
            'firstRespPct' => $firstRespTotal ? round(($firstRespMet / $firstRespTotal) * 100, 1) : null,
            'resPct' => $resTotal ? round(($resMet / $resTotal) * 100, 1) : null,
            'p1ResPct' => $p1ResTotal ? round(($p1ResMet / $p1ResTotal) * 100, 1) : null,
            'byCategory' => $byCategory,
            'totalTickets' => $totalTickets,
            'createdThisWeek' => $createdThisWeek,
            'createdPriorWeek' => $createdPriorWeek,
            'resolvedSamePaceLastMonth' => $resolvedSamePaceLastMonth,
        ]);
    }

    public function markAllRead(): void
    {
        Database::run('UPDATE in_app_notifications SET is_read = 1 WHERE user_id = :u', ['u' => Auth::id()]);
        $this->redirect('/');
    }
}
