<?php
declare(strict_types=1);

class DashboardController
{
    public static function index(): void
    {
        Auth::requireLogin();
        $db     = Database::getInstance();
        $userId = Auth::id();
        $isAdmin = Auth::isAdmin();

        // Stats
        if ($isAdmin) {
            $projectCount = $db->fetch('SELECT COUNT(*) as c FROM projects WHERE status = ?', ['active'])['c'];
        } else {
            $projectCount = $db->fetch(
                'SELECT COUNT(*) as c FROM project_members pm
                 JOIN projects p ON p.id = pm.project_id
                 WHERE pm.user_id = ? AND p.status = ?',
                [$userId, 'active']
            )['c'];
        }

        // Counter for the "My open tasks" stat card. Mirrors the WHERE/JOIN
        // shape of MyTasksController exactly (status=open, scope=mine) so the
        // number on the card equals the list rendered after clicking it.
        $openTasks = $db->fetch(
            'SELECT COUNT(*) as c FROM tasks t
             JOIN task_statuses ts ON ts.id = t.status_id
             JOIN projects p ON p.id = t.project_id
             WHERE ts.is_done = 0
               AND EXISTS (SELECT 1 FROM task_assignees ta WHERE ta.task_id = t.id AND ta.user_id = ?)',
            [$userId]
        )['c'] ?? 0;

        // Both due-date counters are scoped to the user's own assigned tasks,
        // mirroring MyTasksController(scope=mine) so the number matches the
        // list rendered after clicking the card.
        $dueToday = $db->fetch(
            'SELECT COUNT(*) as c FROM tasks t
             JOIN task_statuses ts ON ts.id = t.status_id
             JOIN projects p ON p.id = t.project_id
             WHERE ts.is_done = 0 AND t.due_date = date(\'now\')
               AND EXISTS (SELECT 1 FROM task_assignees ta WHERE ta.task_id = t.id AND ta.user_id = ?)',
            [$userId]
        )['c'] ?? 0;

        $overdue = $db->fetch(
            'SELECT COUNT(*) as c FROM tasks t
             JOIN task_statuses ts ON ts.id = t.status_id
             JOIN projects p ON p.id = t.project_id
             WHERE ts.is_done = 0 AND t.due_date < date(\'now\')
               AND EXISTS (SELECT 1 FROM task_assignees ta WHERE ta.task_id = t.id AND ta.user_id = ?)',
            [$userId]
        )['c'] ?? 0;

        // My assigned open tasks
        $myTasks = $db->fetchAll(
            'SELECT t.*, ts.name as status_name, ts.color as status_color,
                    p.name as project_name, p.color as project_color, p.id as project_id
             FROM tasks t
             JOIN task_statuses ts ON ts.id = t.status_id
             JOIN projects p ON p.id = t.project_id
             WHERE ts.is_done = 0
             AND EXISTS (SELECT 1 FROM task_assignees ta WHERE ta.task_id = t.id AND ta.user_id = ?)
             ORDER BY CASE WHEN t.due_date IS NULL THEN 1 ELSE 0 END,
                      t.due_date ASC, t.priority DESC
             LIMIT 10',
            [$userId]
        );

        // Recent activity
        $activity = $db->fetchAll(
            'SELECT al.*, u.username, u.avatar,
                    t.title as task_title, p.name as project_name
             FROM activity_log al
             JOIN users u ON u.id = al.user_id
             LEFT JOIN tasks t ON t.id = al.task_id
             LEFT JOIN projects p ON p.id = al.project_id
             WHERE al.project_id IN (
                 SELECT project_id FROM project_members WHERE user_id = ?
             )
             ORDER BY al.created_at DESC LIMIT 15',
            [$userId]
        );

        // Projects overview
        if ($isAdmin) {
            $projects = $db->fetchAll(
                'SELECT p.*, u.username as owner_name,
                        (SELECT COUNT(*) FROM tasks WHERE project_id = p.id) as task_count,
                        (SELECT COUNT(*) FROM project_members WHERE project_id = p.id) as member_count
                 FROM projects p JOIN users u ON u.id = p.owner_id
                 WHERE p.status = ? ORDER BY p.updated_at DESC LIMIT 6',
                ['active']
            );
        } else {
            $projects = $db->fetchAll(
                'SELECT p.*, u.username as owner_name, pm.role as my_role,
                        (SELECT COUNT(*) FROM tasks WHERE project_id = p.id) as task_count,
                        (SELECT COUNT(*) FROM project_members WHERE project_id = p.id) as member_count
                 FROM projects p
                 JOIN project_members pm ON pm.project_id = p.id AND pm.user_id = ?
                 JOIN users u ON u.id = p.owner_id
                 WHERE p.status = ? ORDER BY p.updated_at DESC LIMIT 6',
                [$userId, 'active']
            );
        }

        view('dashboard/index', compact('projectCount', 'openTasks', 'dueToday', 'overdue', 'myTasks', 'activity', 'projects'));
    }
}
