<?php

declare(strict_types=1);

namespace App\Modules\Timeline;

use App\Core\BaseModel;

/**
 * TimelineModel — SQL and business logic for Couple Wedding Timeline Checklist.
 *
 * @package Inveetaire
 * @ref     RC-018
 */
class TimelineModel extends BaseModel
{
    protected string $table = 'timeline_tasks';

    private array $defaultPhases = [
        ['name' => '12+ Months',      'sort' => 10,  'offset_days' => 365],
        ['name' => '9–12 Months',     'sort' => 20,  'offset_days' => 270],
        ['name' => '6–9 Months',      'sort' => 30,  'offset_days' => 180],
        ['name' => '3–6 Months',      'sort' => 40,  'offset_days' => 90],
        ['name' => '2 Months',        'sort' => 50,  'offset_days' => 60],
        ['name' => '1 Month',         'sort' => 60,  'offset_days' => 30],
        ['name' => '2 Weeks',         'sort' => 70,  'offset_days' => 14],
        ['name' => '1 Week',          'sort' => 80,  'offset_days' => 7],
        ['name' => 'Wedding Day',     'sort' => 90,  'offset_days' => 0],
        ['name' => 'After Wedding',   'sort' => 100, 'offset_days' => -15]
    ];

    private array $defaultTasks = [
        '12+ Months' => [
            ['title' => 'Estimate guest count', 'cat' => 'Guest', 'pri' => 'Normal', 'dur' => 7, 'desc' => 'Gather initial list and draft numbers.'],
            ['title' => 'Define total wedding budget limit', 'cat' => 'Budget', 'pri' => 'High', 'dur' => 14, 'desc' => 'Align with parents and planner on budget constraints.'],
            ['title' => 'Search and book wedding venue', 'cat' => 'Venue', 'pri' => 'Critical', 'dur' => 30, 'desc' => 'Visit venues and secure the date.']
        ],
        '9–12 Months' => [
            ['title' => 'Hire a Wedding Organizer', 'cat' => 'Entertainment', 'pri' => 'Normal', 'dur' => 15, 'desc' => 'Appoint lead organizer for coordination.'],
            ['title' => 'Select and book photographer & videographer', 'cat' => 'Photography', 'pri' => 'High', 'dur' => 20, 'desc' => 'Secure creative documenters.']
        ],
        '6–9 Months' => [
            ['title' => 'Book decorators and select theme options', 'cat' => 'Decoration', 'pri' => 'Normal', 'dur' => 10, 'desc' => 'Finalize theme color sets.'],
            ['title' => 'Finalize guest list draft', 'cat' => 'Guest', 'pri' => 'Normal', 'dur' => 10, 'desc' => 'Input guest details in excel or app.'],
            ['title' => 'Design wedding dress and suit options', 'cat' => 'Dress', 'pri' => 'High', 'dur' => 40, 'desc' => 'Meet tailors and designer.']
        ],
        '3–6 Months' => [
            ['title' => 'Confirm Church / ceremony arrangements', 'cat' => 'Church', 'pri' => 'Critical', 'dur' => 15, 'desc' => 'Submit marriage course docs.'],
            ['title' => 'Plan Tea Pai ceremony details', 'cat' => 'Tea Pai', 'pri' => 'Low', 'dur' => 10, 'desc' => 'Purchase cups, set schedule.'],
            ['title' => 'Complete marriage legal registration files', 'cat' => 'Legal', 'pri' => 'High', 'dur' => 30, 'desc' => 'Prepare certificates and civil office forms.']
        ],
        '2 Months' => [
            ['title' => 'Design wedding invitations and templates', 'cat' => 'Invitation', 'pri' => 'High', 'dur' => 14, 'desc' => 'Format theme, verify map barcodes.'],
            ['title' => 'Order wedding cake', 'cat' => 'Cake', 'pri' => 'Low', 'dur' => 7, 'desc' => 'Confirm flavors and height.']
        ],
        '1 Month' => [
            ['title' => 'Publish and send digital wedding invitations', 'cat' => 'Invitation', 'pri' => 'Critical', 'dur' => 10, 'desc' => 'Send links to guests via WhatsApp.'],
            ['title' => 'Order souvenir and guest gifts', 'cat' => 'Souvenir', 'pri' => 'Normal', 'dur' => 14, 'desc' => 'Confirm souvenir counts.']
        ],
        '2 Weeks' => [
            ['title' => 'Import Guest Excel list into system', 'cat' => 'Guest', 'pri' => 'High', 'dur' => 5, 'desc' => 'Upload formatted spreadsheet.'],
            ['title' => 'Confirm food tasting and menu selections', 'cat' => 'Food', 'pri' => 'Normal', 'dur' => 3, 'desc' => 'Finalize buffet portions.']
        ],
        '1 Week' => [
            ['title' => 'Complete dress and suit final fitting', 'cat' => 'Dress', 'pri' => 'Critical', 'dur' => 1, 'desc' => 'Adjust alignments.'],
            ['title' => 'Confirm vendor rehearsal schedule', 'cat' => 'Miscellaneous', 'pri' => 'High', 'dur' => 1, 'desc' => 'Set timing for technical rehearsal.']
        ],
        'Wedding Day' => [
            ['title' => 'Celebrate your beautiful Wedding Day!', 'cat' => 'Miscellaneous', 'pri' => 'Critical', 'dur' => 1, 'desc' => 'Enjoy the coordination planner flow.']
        ],
        'After Wedding' => [
            ['title' => 'Send thank-you notes to guests', 'cat' => 'Miscellaneous', 'pri' => 'Low', 'dur' => 7, 'desc' => 'Appreciation messages via WhatsApp.']
        ]
    ];

    // ─────────────────────────────────────────────────────────────────────────
    // AUTO CHECKLIST SEEDING
    // ─────────────────────────────────────────────────────────────────────────

    public function ensureDefaultChecklistExists(int $workspaceId): void
    {
        $stmt = $this->query(
            'SELECT COUNT(*) as cnt FROM timeline_phases WHERE workspace_id = ?',
            [$workspaceId]
        );
        $res = $stmt->fetch();
        
        if ((int)$res['cnt'] === 0) {
            // Get wedding date
            $db = $this->db();
            $stmtW = $db->prepare('SELECT wedding_date FROM workspaces WHERE id = ?');
            $stmtW->execute([$workspaceId]);
            $wObj = $stmtW->fetch();
            $weddingDateStr = $wObj ? $wObj['wedding_date'] : date('Y-m-d', strtotime('+365 days'));
            $weddingTimestamp = strtotime($weddingDateStr);

            $db->beginTransaction();
            try {
                // 1. Seed phases
                $stmtPhase = $db->prepare('INSERT INTO timeline_phases (workspace_id, name, sort_order, created_at) VALUES (?, ?, ?, NOW())');
                $phaseIds = [];
                foreach ($this->defaultPhases as $ph) {
                    $stmtPhase->execute([$workspaceId, $ph['name'], $ph['sort']]);
                    $phaseIds[$ph['name']] = (int) $db->lastInsertId();
                }

                // 2. Seed default tasks
                $stmtTask = $db->prepare(
                    'INSERT INTO timeline_tasks (
                        workspace_id, phase_id, category, title, description, due_date, 
                        priority, status, estimated_duration, sort_order, created_at
                    ) VALUES (
                        ?, ?, ?, ?, ?, ?, ?, "Not Started", ?, ?, NOW()
                    )'
                );

                foreach ($this->defaultPhases as $ph) {
                    $phName = $ph['name'];
                    $phId = $phaseIds[$phName];
                    $offsetSecs = $ph['offset_days'] * 86400;
                    $taskDueDate = date('Y-m-d', $weddingTimestamp - $offsetSecs);

                    if (isset($this->defaultTasks[$phName])) {
                        foreach ($this->defaultTasks[$phName] as $idx => $t) {
                            $stmtTask->execute([
                                $workspaceId,
                                $phId,
                                $t['cat'],
                                $t['title'],
                                $t['desc'],
                                $taskDueDate,
                                $t['pri'],
                                $t['dur'],
                                $idx * 10
                            ]);
                        }
                    }
                }

                // 3. Seed initial default milestones
                $stmtMilestone = $db->prepare(
                    'INSERT INTO timeline_milestones (workspace_id, title, target_date, notes, created_at) VALUES (?, ?, ?, ?, NOW())'
                );
                
                $milestonesSeed = [
                    ['title' => 'Wedding Venue Booked', 'offset' => 300],
                    ['title' => 'Church arrangements Confirmed', 'offset' => 120],
                    ['title' => 'Digital Invitation Published', 'offset' => 30],
                    ['title' => 'Wedding Day', 'offset' => 0]
                ];

                foreach ($milestonesSeed as $m) {
                    $targetDate = date('Y-m-d', $weddingTimestamp - ($m['offset'] * 86400));
                    $stmtMilestone->execute([
                        $workspaceId,
                        $m['title'],
                        $targetDate,
                        "Dynamic checkpoint based on wedding date schedule."
                    ]);
                }

                $db->commit();
            } catch (\Exception $e) {
                $db->rollBack();
                throw $e;
            }
        }
    }

    // ─────────────────────────────────────────────────────────────────────────
    // PHASES
    // ─────────────────────────────────────────────────────────────────────────

    public function getPhases(int $workspaceId): array
    {
        $this->ensureDefaultChecklistExists($workspaceId);
        return $this->query(
            'SELECT * FROM timeline_phases WHERE workspace_id = ? ORDER BY sort_order ASC, id ASC',
            [$workspaceId]
        )->fetchAll();
    }

    // ─────────────────────────────────────────────────────────────────────────
    // TASKS CRUD
    // ─────────────────────────────────────────────────────────────────────────

    public function getTasks(int $workspaceId, array $filters = [], int $limit = 100, int $offset = 0): array
    {
        $sql = 'SELECT t.*, p.name as phase_name 
                FROM timeline_tasks t
                JOIN timeline_phases p ON p.id = t.phase_id
                WHERE t.workspace_id = :workspace_id AND t.is_deleted = 0';
        
        $params = ['workspace_id' => $workspaceId];

        if (!empty($filters['phase_id'])) {
            $sql .= ' AND t.phase_id = :phase_id';
            $params['phase_id'] = (int) $filters['phase_id'];
        }

        if (!empty($filters['category'])) {
            $sql .= ' AND t.category = :category';
            $params['category'] = trim($filters['category']);
        }

        if (!empty($filters['status'])) {
            $sql .= ' AND t.status = :status';
            $params['status'] = trim($filters['status']);
        }

        if (!empty($filters['priority'])) {
            $sql .= ' AND t.priority = :priority';
            $params['priority'] = trim($filters['priority']);
        }

        if (!empty($filters['search'])) {
            $sql .= ' AND (t.title LIKE :search OR t.description LIKE :search OR t.notes LIKE :search)';
            $params['search'] = '%' . trim($filters['search']) . '%';
        }

        $sql .= ' ORDER BY p.sort_order ASC, t.sort_order ASC, t.id DESC LIMIT :limit OFFSET :offset';

        $db = $this->db();
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':workspace_id', $workspaceId, \PDO::PARAM_INT);
        if (isset($params['phase_id'])) $stmt->bindValue(':phase_id', $params['phase_id'], \PDO::PARAM_INT);
        if (isset($params['category'])) $stmt->bindValue(':category', $params['category'], \PDO::PARAM_STR);
        if (isset($params['status'])) $stmt->bindValue(':status', $params['status'], \PDO::PARAM_STR);
        if (isset($params['priority'])) $stmt->bindValue(':priority', $params['priority'], \PDO::PARAM_STR);
        if (isset($params['search'])) $stmt->bindValue(':search', $params['search'], \PDO::PARAM_STR);
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll();
    }

    public function getTasksCount(int $workspaceId, array $filters = []): int
    {
        $sql = 'SELECT COUNT(*) as total 
                FROM timeline_tasks t
                JOIN timeline_phases p ON p.id = t.phase_id
                WHERE t.workspace_id = :workspace_id AND t.is_deleted = 0';
        
        $params = ['workspace_id' => $workspaceId];

        if (!empty($filters['phase_id'])) {
            $sql .= ' AND t.phase_id = :phase_id';
            $params['phase_id'] = (int) $filters['phase_id'];
        }

        if (!empty($filters['category'])) {
            $sql .= ' AND t.category = :category';
            $params['category'] = trim($filters['category']);
        }

        if (!empty($filters['status'])) {
            $sql .= ' AND t.status = :status';
            $params['status'] = trim($filters['status']);
        }

        if (!empty($filters['priority'])) {
            $sql .= ' AND t.priority = :priority';
            $params['priority'] = trim($filters['priority']);
        }

        if (!empty($filters['search'])) {
            $sql .= ' AND (t.title LIKE :search OR t.description LIKE :search)';
            $params['search'] = '%' . trim($filters['search']) . '%';
        }

        $db = $this->db();
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':workspace_id', $workspaceId, \PDO::PARAM_INT);
        if (isset($params['phase_id'])) $stmt->bindValue(':phase_id', $params['phase_id'], \PDO::PARAM_INT);
        if (isset($params['category'])) $stmt->bindValue(':category', $params['category'], \PDO::PARAM_STR);
        if (isset($params['status'])) $stmt->bindValue(':status', $params['status'], \PDO::PARAM_STR);
        if (isset($params['priority'])) $stmt->bindValue(':priority', $params['priority'], \PDO::PARAM_STR);
        if (isset($params['search'])) $stmt->bindValue(':search', $params['search'], \PDO::PARAM_STR);
        $stmt->execute();
        $res = $stmt->fetch();
        return (int) ($res['total'] ?? 0);
    }

    public function getTask(int $workspaceId, int $id): ?array
    {
        $stmt = $this->query(
            'SELECT t.*, p.name as phase_name 
             FROM timeline_tasks t
             JOIN timeline_phases p ON p.id = t.phase_id
             WHERE t.id = ? AND t.workspace_id = ? AND t.is_deleted = 0 LIMIT 1',
            [$id, $workspaceId]
        );
        $res = $stmt->fetch();
        return $res ? $res : null;
    }

    public function createTask(int $workspaceId, array $data): int
    {
        // Get next sort order under phase
        $phaseId = (int) $data['phase_id'];
        $stmt = $this->query(
            'SELECT MAX(sort_order) as max_sort FROM timeline_tasks WHERE workspace_id = ? AND phase_id = ? AND is_deleted = 0',
            [$workspaceId, $phaseId]
        );
        $res = $stmt->fetch();
        $nextSort = $res ? ((int)$res['max_sort'] + 10) : 0;

        $db = $this->db();
        $stmt = $db->prepare(
            'INSERT INTO timeline_tasks (
                workspace_id, phase_id, category, title, description, due_date, 
                priority, status, owner, estimated_duration, notes, sort_order, 
                reminder_enabled, reminder_date, reminder_notes, created_at
            ) VALUES (
                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW()
            )'
        );

        $stmt->execute([
            $workspaceId,
            $phaseId,
            trim($data['category']),
            trim($data['title']),
            !empty($data['description']) ? trim($data['description']) : null,
            !empty($data['due_date']) ? $data['due_date'] : null,
            $data['priority'] ?? 'Normal',
            $data['status'] ?? 'Not Started',
            !empty($data['owner']) ? trim($data['owner']) : null,
            !empty($data['estimated_duration']) ? (int) $data['estimated_duration'] : null,
            !empty($data['notes']) ? trim($data['notes']) : null,
            $nextSort,
            (int) ($data['reminder_enabled'] ?? 0),
            (!empty($data['reminder_enabled']) && !empty($data['reminder_date'])) ? $data['reminder_date'] : null,
            !empty($data['reminder_notes']) ? trim($data['reminder_notes']) : null
        ]);

        return (int)$db->lastInsertId();
    }

    public function updateTask(int $workspaceId, int $id, array $data): bool
    {
        $db = $this->db();
        $stmt = $db->prepare(
            'UPDATE timeline_tasks SET 
                phase_id = ?, 
                category = ?, 
                title = ?, 
                description = ?, 
                due_date = ?, 
                priority = ?, 
                status = ?, 
                owner = ?, 
                estimated_duration = ?, 
                notes = ?, 
                reminder_enabled = ?, 
                reminder_date = ?, 
                reminder_notes = ?, 
                updated_at = NOW() 
             WHERE id = ? AND workspace_id = ? AND is_deleted = 0'
        );

        $stmt->execute([
            (int) $data['phase_id'],
            trim($data['category']),
            trim($data['title']),
            !empty($data['description']) ? trim($data['description']) : null,
            !empty($data['due_date']) ? $data['due_date'] : null,
            $data['priority'] ?? 'Normal',
            $data['status'] ?? 'Not Started',
            !empty($data['owner']) ? trim($data['owner']) : null,
            !empty($data['estimated_duration']) ? (int) $data['estimated_duration'] : null,
            !empty($data['notes']) ? trim($data['notes']) : null,
            (int) ($data['reminder_enabled'] ?? 0),
            (!empty($data['reminder_enabled']) && !empty($data['reminder_date'])) ? $data['reminder_date'] : null,
            !empty($data['reminder_notes']) ? trim($data['reminder_notes']) : null,
            $id,
            $workspaceId
        ]);

        return true;
    }

    public function deleteTask(int $workspaceId, int $id): bool
    {
        $this->query(
            'UPDATE timeline_tasks SET is_deleted = 1, deleted_at = NOW() WHERE id = ? AND workspace_id = ?',
            [$id, $workspaceId]
        );
        return true;
    }

    public function toggleStatus(int $workspaceId, int $id): string
    {
        $task = $this->getTask($workspaceId, $id);
        if (!$task) {
            return 'Not Started';
        }

        $newStatus = ($task['status'] === 'Completed') ? 'Not Started' : 'Completed';
        
        $this->query(
            'UPDATE timeline_tasks SET status = ?, updated_at = NOW() WHERE id = ? AND workspace_id = ?',
            [$newStatus, $id, $workspaceId]
        );

        return $newStatus;
    }

    // ─────────────────────────────────────────────────────────────────────────
    // MILESTONES CRUD
    // ─────────────────────────────────────────────────────────────────────────

    public function getMilestones(int $workspaceId): array
    {
        $this->ensureDefaultChecklistExists($workspaceId);
        return $this->query(
            'SELECT * FROM timeline_milestones WHERE workspace_id = ? AND is_deleted = 0 ORDER BY target_date ASC, id DESC',
            [$workspaceId]
        )->fetchAll();
    }

    public function createMilestone(int $workspaceId, string $title, ?string $targetDate = null, ?string $notes = null): int
    {
        $db = $this->db();
        $stmt = $db->prepare(
            'INSERT INTO timeline_milestones (workspace_id, title, target_date, notes, created_at) VALUES (?, ?, ?, ?, NOW())'
        );
        $stmt->execute([
            $workspaceId,
            trim($title),
            !empty($targetDate) ? $targetDate : null,
            !empty($notes) ? trim($notes) : null
        ]);
        return (int)$db->lastInsertId();
    }

    public function updateMilestone(int $workspaceId, int $id, string $title, ?string $targetDate = null, bool $isCompleted = false, ?string $notes = null): bool
    {
        $completedAt = $isCompleted ? date('Y-m-d H:i:s') : null;
        $db = $this->db();
        $stmt = $db->prepare(
            'UPDATE timeline_milestones SET 
                title = ?, 
                target_date = ?, 
                is_completed = ?, 
                completed_at = ?,
                notes = ?, 
                updated_at = NOW() 
             WHERE id = ? AND workspace_id = ? AND is_deleted = 0'
        );
        $stmt->execute([
            trim($title),
            !empty($targetDate) ? $targetDate : null,
            (int)$isCompleted,
            $completedAt,
            !empty($notes) ? trim($notes) : null,
            $id,
            $workspaceId
        ]);
        return true;
    }

    public function deleteMilestone(int $workspaceId, int $id): bool
    {
        $this->query(
            'UPDATE timeline_milestones SET is_deleted = 1 WHERE id = ? AND workspace_id = ?',
            [$id, $workspaceId]
        );
        return true;
    }

    public function toggleMilestoneComplete(int $workspaceId, int $id): bool
    {
        $stmt = $this->query(
            'SELECT is_completed FROM timeline_milestones WHERE id = ? AND workspace_id = ? AND is_deleted = 0 LIMIT 1',
            [$id, $workspaceId]
        );
        $m = $stmt->fetch();
        if (!$m) return false;

        $newVal = (int)$m['is_completed'] === 1 ? 0 : 1;
        $completedAt = $newVal === 1 ? date('Y-m-d H:i:s') : null;

        $this->query(
            'UPDATE timeline_milestones SET is_completed = ?, completed_at = ?, updated_at = NOW() WHERE id = ? AND workspace_id = ?',
            [$newVal, $completedAt, $id, $workspaceId]
        );
        return true;
    }

    // ─────────────────────────────────────────────────────────────────────────
    // STATISTICS & AGGREGATIONS
    // ─────────────────────────────────────────────────────────────────────────

    public function getDashboardStats(int $workspaceId): array
    {
        // 1. Fetch wedding date & countdown
        $db = $this->db();
        $stmt = $db->prepare('SELECT wedding_date, groom_name, bride_name FROM workspaces WHERE id = ?');
        $stmt->execute([$workspaceId]);
        $wObj = $stmt->fetch();
        
        $weddingDateStr = $wObj ? $wObj['wedding_date'] : date('Y-m-d', strtotime('+365 days'));
        $daysCountdown = (int) ceil((strtotime($weddingDateStr) - time()) / 86400);

        // 2. Count tasks
        $stmtStats = $this->query(
            'SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN status = "Completed" THEN 1 ELSE 0 END) as completed,
                SUM(CASE WHEN status = "In Progress" THEN 1 ELSE 0 END) as in_progress,
                SUM(CASE WHEN status NOT IN ("Completed", "Cancelled") AND (due_date < CURDATE()) THEN 1 ELSE 0 END) as overdue,
                SUM(CASE WHEN status NOT IN ("Completed", "Cancelled") THEN 1 ELSE 0 END) as pending
             FROM timeline_tasks 
             WHERE workspace_id = ? AND is_deleted = 0',
            [$workspaceId]
        );
        $stats = $stmtStats->fetch();

        $total = (int) ($stats['total'] ?? 0);
        $completed = (int) ($stats['completed'] ?? 0);
        $inProgress = (int) ($stats['in_progress'] ?? 0);
        $overdue = (int) ($stats['overdue'] ?? 0);
        $pending = (int) ($stats['pending'] ?? 0);

        $completionPercent = 0.0;
        if ($total > 0) {
            $completionPercent = min(100.0, ($completed / $total) * 100);
        }

        // 3. Due counts (this week & this month)
        $stmtWeek = $this->query(
            'SELECT COUNT(*) as week_count FROM timeline_tasks 
             WHERE workspace_id = ? AND is_deleted = 0 AND status NOT IN ("Completed", "Cancelled")
               AND due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)',
            [$workspaceId]
        );
        $weekRes = $stmtWeek->fetch();
        $dueThisWeek = (int) ($weekRes['week_count'] ?? 0);

        $stmtMonth = $this->query(
            'SELECT COUNT(*) as month_count FROM timeline_tasks 
             WHERE workspace_id = ? AND is_deleted = 0 AND status NOT IN ("Completed", "Cancelled")
               AND due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)',
            [$workspaceId]
        );
        $monthRes = $stmtMonth->fetch();
        $dueThisMonth = (int) ($monthRes['month_count'] ?? 0);

        // 4. Recently completed tasks
        $recentCompleted = $this->query(
            'SELECT t.*, p.name as phase_name 
             FROM timeline_tasks t
             JOIN timeline_phases p ON p.id = t.phase_id
             WHERE t.workspace_id = ? AND t.is_deleted = 0 AND t.status = "Completed"
             ORDER BY t.updated_at DESC, t.id DESC
             LIMIT 5',
            [$workspaceId]
        )->fetchAll();

        // 5. Overdue list
        $overdueList = $this->query(
            'SELECT t.*, p.name as phase_name 
             FROM timeline_tasks t
             JOIN timeline_phases p ON p.id = t.phase_id
             WHERE t.workspace_id = ? AND t.is_deleted = 0 
               AND t.status NOT IN ("Completed", "Cancelled")
               AND t.due_date < CURDATE()
             ORDER BY t.due_date ASC, t.id DESC
             LIMIT 5',
            [$workspaceId]
        )->fetchAll();

        // 6. Today's checklist
        $todayTasks = $this->query(
            'SELECT t.*, p.name as phase_name 
             FROM timeline_tasks t
             JOIN timeline_phases p ON p.id = t.phase_id
             WHERE t.workspace_id = ? AND t.is_deleted = 0 
               AND t.status NOT IN ("Completed", "Cancelled")
               AND t.due_date = CURDATE()
             ORDER BY t.priority DESC, t.id DESC',
            [$workspaceId]
        )->fetchAll();

        // 7. This week upcoming checklist
        $thisWeekTasks = $this->query(
            'SELECT t.*, p.name as phase_name 
             FROM timeline_tasks t
             JOIN timeline_phases p ON p.id = t.phase_id
             WHERE t.workspace_id = ? AND t.is_deleted = 0 
               AND t.status NOT IN ("Completed", "Cancelled")
               AND t.due_date BETWEEN DATE_ADD(CURDATE(), INTERVAL 1 DAY) AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
             ORDER BY t.due_date ASC, t.priority DESC',
            [$workspaceId]
        )->fetchAll();

        return [
            'wedding_date' => $weddingDateStr,
            'groom_name' => $wObj ? $wObj['groom_name'] : 'Groom',
            'bride_name' => $wObj ? $wObj['bride_name'] : 'Bride',
            'countdown_days' => $daysCountdown,
            'total_tasks' => $total,
            'completed_tasks' => $completed,
            'in_progress_tasks' => $inProgress,
            'overdue_tasks' => $overdue,
            'pending_tasks' => $pending,
            'completion_percent' => $completionPercent,
            'due_this_week' => $dueThisWeek,
            'due_this_month' => $dueThisMonth,
            'recent_completed' => $recentCompleted,
            'overdue_list' => $overdueList,
            'today_tasks' => $todayTasks,
            'this_week_tasks' => $thisWeekTasks
        ];
    }

    public function getCalendarTasks(int $workspaceId, int $year, int $month): array
    {
        $startDate = sprintf('%04d-%02d-01', $year, $month);
        $endDate = date('Y-m-t', strtotime($startDate));

        return $this->query(
            'SELECT t.*, p.name as phase_name 
             FROM timeline_tasks t
             JOIN timeline_phases p ON p.id = t.phase_id
             WHERE t.workspace_id = ? AND t.is_deleted = 0
               AND t.due_date BETWEEN ? AND ?
             ORDER BY t.due_date ASC, t.priority DESC, t.id DESC',
            [$workspaceId, $startDate, $endDate]
        )->fetchAll();
    }
}
