<?php

declare(strict_types=1);

namespace App\Modules\Invitation;

use App\Core\BaseController;
use App\Core\Request;
use App\Core\Session;
use App\Modules\Operation\OperationModel;
use App\Modules\Timeline\TimelineModel;

/**
 * CoupleDashboardController — Wedding Command Center Dashboard HTTP Handler.
 *
 * Scopes statistics, budget differences, countdowns, checklists, RSVPs,
 * upcoming payments, and recent activities.
 *
 * @package Inveetaire
 * @ref     RC-020
 */
class CoupleDashboardController extends BaseController
{
    protected string $layout = 'couple';
    private InvitationService $invitationService;
    private OperationModel $operationModel;
    private TimelineModel $timelineModel;

    public function __construct()
    {
        parent::__construct();
        $this->invitationService = new InvitationService();
        $this->operationModel = new OperationModel();
        $this->timelineModel = new TimelineModel();
    }

    /**
     * Display the Wedding Management summary dashboard.
     *
     * @param Request $request
     * @return void
     */
    public function index(Request $request): void
    {
        $workspaceId = (int) Session::get('active_workspace_id');

        try {
            $db = \App\Core\Database::getConnection();

            // 1. Fetch Header Details (Workspace & Plan details)
            $stmtHeader = $db->prepare('
                SELECT w.id, w.slug, w.groom_name, w.bride_name, w.wedding_date, p.name as plan_name,
                       COALESCE(i.invitation_status, \'draft\') as invitation_status
                FROM workspaces w
                JOIN commercial_plans p ON w.commercial_plan_id = p.id
                LEFT JOIN invitations i ON i.workspace_id = w.id
                WHERE w.id = :workspace_id
                LIMIT 1
            ');
            $stmtHeader->execute(['workspace_id' => $workspaceId]);
            $workspace = $stmtHeader->fetch(\PDO::FETCH_ASSOC);

            if (!$workspace) {
                throw new \RuntimeException("Workspace details not found.");
            }

            // Calculate Countdown Days
            $weddingDateTime = new \DateTime($workspace['wedding_date']);
            $today = new \DateTime(date('Y-m-d'));
            $diff = $today->diff($weddingDateTime);
            $countdownDays = (int) $diff->format('%r%a');
            if ($countdownDays < 0) {
                $countdownDays = 0; // Already occurred
            }

            // 2. Budget Command Center Summary Calculations
            // Get budget settings (total budget and currency)
            $stmtBgtSet = $db->prepare('SELECT total_budget, currency FROM budget_settings WHERE workspace_id = :workspace_id LIMIT 1');
            $stmtBgtSet->execute(['workspace_id' => $workspaceId]);
            $bgtSet = $stmtBgtSet->fetch(\PDO::FETCH_ASSOC);

            $totalBudget = (float) ($bgtSet['total_budget'] ?? 150000000.00); // Default fallback Rp 150jt
            $currency = $bgtSet['currency'] ?? 'IDR';

            // Query planned (estimated) and actual cost of all expenses
            $stmtBgtAgg = $db->prepare('
                SELECT 
                    SUM(estimated_cost) as total_planned,
                    SUM(actual_cost) as total_actual,
                    SUM(CASE WHEN actual_cost > 0 THEN actual_cost ELSE estimated_cost END) as target_allocated
                FROM budget_expenses 
                WHERE workspace_id = :workspace_id 
                  AND is_deleted = 0 
                  AND status != "Cancelled"
            ');
            $stmtBgtAgg->execute(['workspace_id' => $workspaceId]);
            $bgtAgg = $stmtBgtAgg->fetch(\PDO::FETCH_ASSOC);

            $allocatedBudget = (float) ($bgtAgg['total_planned'] ?? 0.00);
            $actualDealValue = (float) ($bgtAgg['total_actual'] ?? 0.00);
            $targetAllocated = (float) ($bgtAgg['target_allocated'] ?? 0.00);

            // Total paid installments
            $stmtPaid = $db->prepare('
                SELECT SUM(amount) as total_paid 
                FROM budget_payments 
                WHERE workspace_id = :workspace_id AND is_deleted = 0
            ');
            $stmtPaid->execute(['workspace_id' => $workspaceId]);
            $paidRes = $stmtPaid->fetch(\PDO::FETCH_ASSOC);
            $paid = (float) ($paidRes['total_paid'] ?? 0.00);

            // Remaining Cash = Total Budget - Paid
            $remainingCash = max(0.00, $totalBudget - $paid);

            // Difference = Total Budget - target_allocated
            $budgetDifference = $totalBudget - $targetAllocated;

            // 3. Budget breakdown (per category)
            $stmtBreakdown = $db->prepare('
                SELECT c.id, c.name, 
                       COALESCE(SUM(e.estimated_cost), 0.00) as planned, 
                       COALESCE(SUM(e.actual_cost), 0.00) as actual,
                       COALESCE((
                           SELECT SUM(p.amount) FROM budget_payments p 
                           JOIN budget_expenses ex ON ex.id = p.expense_id
                           WHERE ex.category_id = c.id AND ex.is_deleted = 0 AND p.is_deleted = 0
                       ), 0.00) as paid
                FROM budget_categories c
                LEFT JOIN budget_expenses e ON e.category_id = c.id AND e.is_deleted = 0 AND e.status != "Cancelled"
                WHERE c.workspace_id = :workspace_id AND c.is_deleted = 0
                GROUP BY c.id, c.name
                ORDER BY c.sort_order ASC, c.name ASC
            ');
            $stmtBreakdown->execute(['workspace_id' => $workspaceId]);
            $budgetBreakdown = $stmtBreakdown->fetchAll(\PDO::FETCH_ASSOC);

            // Calculate differences for categories
            foreach ($budgetBreakdown as &$cat) {
                $cat['planned'] = (float) $cat['planned'];
                $cat['actual'] = (float) $cat['actual'];
                $cat['paid'] = (float) $cat['paid'];
                
                $targetVal = $cat['actual'] > 0 ? $cat['actual'] : $cat['planned'];
                $cat['remaining'] = max(0.00, $targetVal - $cat['paid']);
                
                // If category has actual deal cost, difference is planned - actual. Else 0.
                if ($cat['actual'] > 0.00) {
                    $cat['difference'] = $cat['planned'] - $cat['actual'];
                } else {
                    $cat['difference'] = 0.00; // No deal signed yet
                }
            }
            unset($cat);

            // 4. Upcoming Payments
            $stmtUpcomingPay = $db->prepare('
                SELECT e.id as expense_id, e.name as expense_name, e.due_date, e.actual_cost, e.estimated_cost, bc.name as category_name,
                       v.name as vendor_name, v.id as vendor_id,
                       COALESCE((SELECT SUM(amount) FROM budget_payments p WHERE p.expense_id = e.id AND p.is_deleted = 0), 0.00) as paid
                FROM budget_expenses e
                JOIN budget_categories bc ON bc.id = e.category_id
                LEFT JOIN vendors v ON v.id = e.vendor_id AND v.is_deleted = 0
                WHERE e.workspace_id = :workspace_id 
                  AND e.is_deleted = 0 
                  AND e.due_date >= CURDATE() 
                  AND e.status != "Paid" 
                  AND e.status != "Cancelled"
                ORDER BY e.due_date ASC
                LIMIT 5
            ');
            $stmtUpcomingPay->execute(['workspace_id' => $workspaceId]);
            $upcomingPayments = $stmtUpcomingPay->fetchAll(\PDO::FETCH_ASSOC);

            foreach ($upcomingPayments as &$p) {
                $p['target_cost'] = (float) ($p['actual_cost'] > 0 ? $p['actual_cost'] : $p['estimated_cost']);
                $p['remaining_balance'] = max(0.00, $p['target_cost'] - (float)$p['paid']);
            }
            unset($p);

            // 5. Timeline summary
            $timelineModelStats = $this->timelineModel->getDashboardStats($workspaceId);
            $timelineSummary = [
                'completed' => $timelineModelStats['completed_tasks'] ?? 0,
                'remaining' => $timelineModelStats['pending_tasks'] ?? 0,
                'due_this_week' => $timelineModelStats['due_this_week_tasks'] ?? 0,
                'overdue' => $timelineModelStats['overdue_tasks'] ?? 0,
                'countdown' => $countdownDays
            ];

            // 6. Guest stats & RSVP summaries
            $guestStats = $this->operationModel->getAttendanceStats($workspaceId);

            // Invitation Sent count
            $stmtSent = $db->prepare('
                SELECT COUNT(*) FROM guests 
                WHERE workspace_id = :workspace_id 
                  AND is_deleted = 0 
                  AND invitation_status IN ("sent", "opened")
            ');
            $stmtSent->execute(['workspace_id' => $workspaceId]);
            $invitationsSent = (int) $stmtSent->fetchColumn();

            // Need follow up count
            $stmtFollowUp = $db->prepare('
                SELECT COUNT(*) FROM guests 
                WHERE workspace_id = :workspace_id 
                  AND is_deleted = 0 
                  AND rsvp_status = "pending" 
                  AND invitation_status IN ("sent", "opened")
            ');
            $stmtFollowUp->execute(['workspace_id' => $workspaceId]);
            $needFollowUp = (int) $stmtFollowUp->fetchColumn();

            $guestSummary = [
                'total'          => $guestStats['total_guests'] ?? 0,
                'sent'           => $invitationsSent,
                'confirmed'      => $guestStats['rsvp_yes_count'] ?? 0,
                'confirmed_pax'  => $guestStats['rsvp_yes_pax'] ?? 0,
                'declined'       => $guestStats['rsvp_no_count'] ?? 0,
                'pending'        => $guestStats['rsvp_pending_count'] ?? 0,
                'need_follow_up' => $needFollowUp
            ];

            // 7. Today's Focus (Actionable items due today)
            $focusItems = [];

            // Tasks due today
            $stmtFocusTasks = $db->prepare('
                SELECT id, title, "task" as type 
                FROM timeline_tasks 
                WHERE workspace_id = :workspace_id 
                  AND is_deleted = 0 
                  AND status NOT IN ("Completed", "Cancelled") 
                  AND due_date = CURDATE()
            ');
            $stmtFocusTasks->execute(['workspace_id' => $workspaceId]);
            $focusTasks = $stmtFocusTasks->fetchAll(\PDO::FETCH_ASSOC);
            foreach ($focusTasks as $t) {
                $focusItems[] = [
                    'title' => $t['title'],
                    'type' => 'Task',
                    'icon' => 'fa-solid fa-list-check text-indigo-500',
                    'link' => url('/app/couple/timeline/tasks/edit/' . $t['id'])
                ];
            }

            // Payments due today
            $stmtFocusPay = $db->prepare('
                SELECT id, name, "payment" as type 
                FROM budget_expenses 
                WHERE workspace_id = :workspace_id 
                  AND is_deleted = 0 
                  AND status NOT IN ("Paid", "Cancelled") 
                  AND due_date = CURDATE()
            ');
            $stmtFocusPay->execute(['workspace_id' => $workspaceId]);
            $focusPay = $stmtFocusPay->fetchAll(\PDO::FETCH_ASSOC);
            foreach ($focusPay as $p) {
                $focusItems[] = [
                    'title' => 'Pay for ' . $p['name'],
                    'type' => 'Payment Due',
                    'icon' => 'fa-solid fa-credit-card text-amber-500',
                    'link' => url('/app/couple/budget/expense/edit/' . $p['id'])
                ];
            }

            // Meetings today
            $stmtFocusMeetings = $db->prepare('
                SELECT c.id, c.notes, v.name as vendor_name, c.channel
                FROM vendor_communications c
                JOIN vendors v ON v.id = c.vendor_id
                WHERE v.workspace_id = :workspace_id 
                  AND c.is_deleted = 0 
                  AND c.next_follow_up = CURDATE()
            ');
            $stmtFocusMeetings->execute(['workspace_id' => $workspaceId]);
            $focusMeetings = $stmtFocusMeetings->fetchAll(\PDO::FETCH_ASSOC);
            foreach ($focusMeetings as $m) {
                $focusItems[] = [
                    'title' => 'Meeting: ' . $m['vendor_name'] . ' (' . $m['channel'] . ') - ' . $m['notes'],
                    'type' => 'Meeting Today',
                    'icon' => 'fa-solid fa-handshake text-emerald-500',
                    'link' => url('/app/couple/vendor/detail/' . $m['id'] . '?tab=communication')
                ];
            }

            // 8. Recent Activities timeline (Merged Guest, Budget, Timeline, RSVP, Wedding Day)
            $activities = [];

            // A. RSVPs
            $stmtActRsvp = $db->prepare('
                SELECT full_name, rsvp_status, rsvp_submitted_at as act_date 
                FROM guests 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 AND rsvp_status != "pending"
                ORDER BY rsvp_submitted_at DESC LIMIT 10
            ');
            $stmtActRsvp->execute(['workspace_id' => $workspaceId]);
            foreach ($stmtActRsvp->fetchAll(\PDO::FETCH_ASSOC) as $act) {
                $activities[] = [
                    'text' => 'RSVP: ' . escape_html($act['full_name']) . ' responded ' . strtoupper($act['rsvp_status']),
                    'date' => $act['act_date'],
                    'icon' => 'fa-solid fa-envelope-open-text text-sky-500'
                ];
            }

            // B. Payments
            $stmtActPay = $db->prepare('
                SELECT p.amount, e.name as expense_name, p.payment_date as act_date 
                FROM budget_payments p 
                JOIN budget_expenses e ON e.id = p.expense_id 
                WHERE e.workspace_id = :workspace_id AND e.is_deleted = 0 AND p.is_deleted = 0 
                ORDER BY p.payment_date DESC, p.id DESC LIMIT 10
            ');
            $stmtActPay->execute(['workspace_id' => $workspaceId]);
            foreach ($stmtActPay->fetchAll(\PDO::FETCH_ASSOC) as $act) {
                $activities[] = [
                    'text' => 'Budget Payment: Registered installment for ' . escape_html($act['expense_name']),
                    'date' => $act['act_date'],
                    'icon' => 'fa-solid fa-file-invoice-dollar text-emerald-550'
                ];
            }

            // C. Vendor Updates
            $stmtActVendor = $db->prepare('
                SELECT name, status, COALESCE(updated_at, created_at) as act_date 
                FROM vendors 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 
                ORDER BY COALESCE(updated_at, created_at) DESC LIMIT 10
            ');
            $stmtActVendor->execute(['workspace_id' => $workspaceId]);
            foreach ($stmtActVendor->fetchAll(\PDO::FETCH_ASSOC) as $act) {
                $activities[] = [
                    'text' => 'Vendor Profile: Updated ' . escape_html($act['name']) . ' status to ' . $act['status'],
                    'date' => $act['act_date'],
                    'icon' => 'fa-solid fa-store text-indigo-500'
                ];
            }

            // F. Invitation Opens (G012)
            $stmtActOpen = $db->prepare('
                SELECT full_name, view_count, last_opened_at as act_date 
                FROM guests 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 AND last_opened_at IS NOT NULL 
                ORDER BY last_opened_at DESC LIMIT 10
            ');
            $stmtActOpen->execute(['workspace_id' => $workspaceId]);
            foreach ($stmtActOpen->fetchAll(\PDO::FETCH_ASSOC) as $act) {
                $activities[] = [
                    'text' => 'Invitation Opened: ' . escape_html($act['full_name']) . ' opened invitation (' . (int)$act['view_count'] . ' views)',
                    'date' => $act['act_date'],
                    'icon' => 'fa-solid fa-envelope-open text-[#D4A28C]'
                ];
            }

            // G. Wishes Submitted (G012)
            $stmtActWish = $db->prepare('
                SELECT sender_name, message, submitted_at as act_date 
                FROM wishes 
                WHERE workspace_id = :workspace_id 
                ORDER BY submitted_at DESC LIMIT 10
            ');
            $stmtActWish->execute(['workspace_id' => $workspaceId]);
            foreach ($stmtActWish->fetchAll(\PDO::FETCH_ASSOC) as $act) {
                $activities[] = [
                    'text' => 'Guest Book: ' . escape_html($act['sender_name']) . ' left a congratulatory wish',
                    'date' => $act['act_date'],
                    'icon' => 'fa-solid fa-heart text-amber-500'
                ];
            }

            // Sort activities by date descending and slice to top 15
            usort($activities, function ($a, $b) {
                return strtotime((string)($b['date'] ?? '1970-01-01')) <=> strtotime((string)($a['date'] ?? '1970-01-01'));
            });
            $activities = array_slice($activities, 0, 15);

            // 9. G012 Analytics & Guest Progress Table
            $guestModel = new \App\Modules\Guest\GuestModel();
            $deliveryStats = $guestModel->getInvitationDeliveryStats($workspaceId);

            $progressFilters = [
                'search' => trim((string) $request->get('search')),
                'filter' => trim((string) $request->get('filter', 'all'))
            ];
            $progressPage = max(1, (int) $request->get('page', 1));
            $progressLimit = 10;
            $progressOffset = ($progressPage - 1) * $progressLimit;

            $guestProgress = $guestModel->getGuestProgressList($workspaceId, $progressFilters, $progressLimit, $progressOffset);
            $progressTotal = $guestModel->getGuestProgressCount($workspaceId, $progressFilters);
            $progressPages = (int) ceil($progressTotal / $progressLimit);

            if ($request->get('ajax') === '1') {
                $this->layout = 'ajax';
            }
            $perCeremonyStats = $this->operationModel->getPerCeremonyAttendanceStats($workspaceId);

            // Render view
            $this->render('invitation/dashboard', [
                'title'               => 'Wedding Command Center',
                'workspace'           => $workspace,
                'countdown_days'      => $countdownDays,
                'total_budget'        => $totalBudget,
                'allocated_budget'    => $allocatedBudget,
                'actual_deal_value'   => $actualDealValue,
                'paid'                => $paid,
                'remaining_cash'      => $remainingCash,
                'budget_difference'   => $budgetDifference,
                'currency'            => $currency,
                'budget_breakdown'    => $budgetBreakdown,
                'upcoming_payments'   => $upcomingPayments,
                'timeline_summary'    => $timelineSummary,
                'guest_summary'       => $guestSummary,
                'per_ceremony_stats'  => $perCeremonyStats,
                'delivery_stats'      => $deliveryStats,
                'focus_items'         => $focusItems,
                'activities'          => $activities,
                'guest_progress'      => $guestProgress,
                'progress_filters'    => $progressFilters,
                'progress_page'       => $progressPage,
                'progress_pages'      => $progressPages,
                'progress_total'      => $progressTotal
            ]);

        } catch (\Throwable $e) {
            $this->flash('error', 'Dashboard failed to load: ' . $e->getMessage());
            $this->redirect('/app/couple/invitation/edit');
        }
    }

    /**
     * Display the Wedding Insights workspace.
     * Route: GET /app/couple/insights
     */
    public function insights(Request $request): void
    {
        $workspaceId = (int) Session::get('active_workspace_id');

        try {
            $db = \App\Core\Database::getConnection();

            // ── 1. HEADER & COUNTDOWN ────────────────────────────────────────
            $stmtHeader = $db->prepare('
                SELECT w.id, w.groom_name, w.bride_name, w.wedding_date, p.name as plan_name 
                FROM workspaces w
                JOIN commercial_plans p ON w.commercial_plan_id = p.id
                WHERE w.id = :workspace_id LIMIT 1
            ');
            $stmtHeader->execute(['workspace_id' => $workspaceId]);
            $workspace = $stmtHeader->fetch(\PDO::FETCH_ASSOC);

            if (!$workspace) {
                throw new \RuntimeException("Workspace details not found.");
            }

            // Calculate Countdown Days, Weeks, Months
            $weddingDateTime = new \DateTime($workspace['wedding_date']);
            $today = new \DateTime(date('Y-m-d'));
            $diff = $today->diff($weddingDateTime);
            $countdownDays = (int) $diff->format('%r%a');
            if ($countdownDays < 0) {
                $countdownDays = 0;
            }
            $weeksRemaining = (int) ceil($countdownDays / 7);
            $monthsRemaining = (int) ceil($countdownDays / 30.43);

            // ── 2. BUDGET INSIGHTS ──────────────────────────────────────────
            $stmtBgtSet = $db->prepare('SELECT total_budget, currency FROM budget_settings WHERE workspace_id = :workspace_id LIMIT 1');
            $stmtBgtSet->execute(['workspace_id' => $workspaceId]);
            $bgtSet = $stmtBgtSet->fetch(\PDO::FETCH_ASSOC);
            $totalBudget = (float) ($bgtSet['total_budget'] ?? 150000000.00);
            $currency = $bgtSet['currency'] ?? 'IDR';

            // Query planned (estimated) and actual cost of all expenses
            $stmtBgtAgg = $db->prepare('
                SELECT 
                    SUM(estimated_cost) as total_planned,
                    SUM(actual_cost) as total_actual,
                    SUM(CASE WHEN actual_cost > 0 THEN actual_cost ELSE estimated_cost END) as target_allocated
                FROM budget_expenses 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 AND status != "Cancelled"
            ');
            $stmtBgtAgg->execute(['workspace_id' => $workspaceId]);
            $bgtAgg = $stmtBgtAgg->fetch(\PDO::FETCH_ASSOC);

            $allocatedBudget = (float) ($bgtAgg['total_planned'] ?? 0.00);
            $actualDealValue = (float) ($bgtAgg['total_actual'] ?? 0.00);
            $targetAllocated = (float) ($bgtAgg['target_allocated'] ?? 0.00);

            // Total paid installments
            $stmtPaid = $db->prepare('
                SELECT SUM(amount) as total_paid 
                FROM budget_payments 
                WHERE workspace_id = :workspace_id AND is_deleted = 0
            ');
            $stmtPaid->execute(['workspace_id' => $workspaceId]);
            $paidRes = $stmtPaid->fetch(\PDO::FETCH_ASSOC);
            $paid = (float) ($paidRes['total_paid'] ?? 0.00);

            $remainingCash = max(0.00, $totalBudget - $paid);
            $budgetDifference = $totalBudget - $targetAllocated;

            // Fetch categories list with planned, actual, and difference
            $stmtCats = $db->prepare('
                SELECT c.id, c.name, 
                       COALESCE(SUM(e.estimated_cost), 0.00) as planned, 
                       COALESCE(SUM(e.actual_cost), 0.00) as actual
                FROM budget_categories c
                LEFT JOIN budget_expenses e ON e.category_id = c.id AND e.is_deleted = 0 AND e.status != "Cancelled"
                WHERE c.workspace_id = :workspace_id AND c.is_deleted = 0
                GROUP BY c.id, c.name
            ');
            $stmtCats->execute(['workspace_id' => $workspaceId]);
            $categoriesBreakdown = $stmtCats->fetchAll(\PDO::FETCH_ASSOC);

            foreach ($categoriesBreakdown as &$cat) {
                $cat['planned'] = (float) $cat['planned'];
                $cat['actual'] = (float) $cat['actual'];
                $cat['difference'] = $cat['planned'] - $cat['actual'];
            }
            unset($cat);

            // Top 5 Categories lists
            // A. Highest Budget (planned)
            $topPlanned = $categoriesBreakdown;
            usort($topPlanned, function ($a, $b) {
                return $b['planned'] <=> $a['planned'];
            });
            $topPlanned = array_slice($topPlanned, 0, 5);

            // B. Highest Actual (deal value)
            $topActual = $categoriesBreakdown;
            usort($topActual, function ($a, $b) {
                return $b['actual'] <=> $a['actual'];
            });
            $topActual = array_slice($topActual, 0, 5);

            // C. Largest Difference (variance saving/overrun)
            $topDiff = $categoriesBreakdown;
            usort($topDiff, function ($a, $b) {
                return $b['difference'] <=> $a['difference']; // Positive difference (savings) first
            });
            $topDiff = array_slice($topDiff, 0, 5);

            // ── 3. GUEST INSIGHTS ───────────────────────────────────────────
            $guestStats = $this->operationModel->getAttendanceStats($workspaceId);

            $stmtSent = $db->prepare('SELECT COUNT(*) FROM guests WHERE workspace_id = :workspace_id AND is_deleted = 0 AND invitation_status IN ("sent", "opened")');
            $stmtSent->execute(['workspace_id' => $workspaceId]);
            $sentCount = (int) $stmtSent->fetchColumn();

            $stmtNotSent = $db->prepare('SELECT COUNT(*) FROM guests WHERE workspace_id = :workspace_id AND is_deleted = 0 AND invitation_status = "not_sent"');
            $stmtNotSent->execute(['workspace_id' => $workspaceId]);
            $notSentCount = (int) $stmtNotSent->fetchColumn();

            // Need follow up: pending RSVPs and invitation is sent/opened
            $stmtFollowUp = $db->prepare('
                SELECT COUNT(*) FROM guests 
                WHERE workspace_id = :workspace_id 
                  AND is_deleted = 0 
                  AND rsvp_status = "pending" 
                  AND invitation_status IN ("sent", "opened")
            ');
            $stmtFollowUp->execute(['workspace_id' => $workspaceId]);
            $needFollowUp = (int) $stmtFollowUp->fetchColumn();

            $totalGuests = $guestStats['total_guests'] ?? 0;
            $confirmed = $guestStats['rsvp_yes_count'] ?? 0;
            $declined = $guestStats['rsvp_no_count'] ?? 0;
            $pending = $guestStats['rsvp_pending_count'] ?? 0;
            $rsvpPercent = $totalGuests > 0 ? (($confirmed + $declined) / $totalGuests) * 100 : 0.0;

            // ── 4. TIMELINE INSIGHTS ────────────────────────────────────────
            $timelineModelStats = $this->timelineModel->getDashboardStats($workspaceId);

            $stmtTimelineFocus = $db->prepare('
                SELECT 
                    COUNT(CASE WHEN status NOT IN ("Completed", "Cancelled") AND due_date = CURDATE() THEN 1 END) as due_today,
                    COUNT(CASE WHEN status NOT IN ("Completed", "Cancelled") AND due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY) THEN 1 END) as due_this_week
                FROM timeline_tasks 
                WHERE workspace_id = :workspace_id AND is_deleted = 0
            ');
            $stmtTimelineFocus->execute(['workspace_id' => $workspaceId]);
            $tlFocus = $stmtTimelineFocus->fetch(\PDO::FETCH_ASSOC);

            $timelineSummary = [
                'total' => ($timelineModelStats['completed_tasks'] ?? 0) + ($timelineModelStats['pending_tasks'] ?? 0),
                'completed' => $timelineModelStats['completed_tasks'] ?? 0,
                'remaining' => $timelineModelStats['pending_tasks'] ?? 0,
                'due_today' => (int) ($tlFocus['due_today'] ?? 0),
                'due_this_week' => (int) ($tlFocus['due_this_week'] ?? 0),
                'overdue' => $timelineModelStats['overdue_tasks'] ?? 0
            ];

            // ── 5. VENDOR INSIGHTS ──────────────────────────────────────────
            $stmtVendors = $db->prepare('SELECT COUNT(*) FROM vendors WHERE workspace_id = :workspace_id AND is_deleted = 0');
            $stmtVendors->execute(['workspace_id' => $workspaceId]);
            $totalVendors = (int) $stmtVendors->fetchColumn();

            $stmtAssigned = $db->prepare('SELECT COUNT(*) FROM vendors WHERE workspace_id = :workspace_id AND is_deleted = 0 AND status IN ("Booked", "Completed")');
            $stmtAssigned->execute(['workspace_id' => $workspaceId]);
            $assignedVendors = (int) $stmtAssigned->fetchColumn();

            $stmtUnassignedCats = $db->prepare('
                SELECT COUNT(*) FROM budget_categories bc 
                WHERE bc.workspace_id = :workspace_id AND bc.is_deleted = 0
                  AND bc.id NOT IN (
                      SELECT DISTINCT category_id FROM budget_expenses WHERE workspace_id = :workspace_id2 AND is_deleted = 0 AND vendor_id IS NOT NULL
                  )
            ');
            $stmtUnassignedCats->execute([
                'workspace_id' => $workspaceId,
                'workspace_id2' => $workspaceId
            ]);
            $unassignedCats = (int) $stmtUnassignedCats->fetchColumn();

            $stmtUpcomingPaymentsCount = $db->prepare('
                SELECT COUNT(*) FROM budget_expenses e
                WHERE e.workspace_id = :workspace_id AND e.is_deleted = 0 AND e.due_date >= CURDATE() AND e.status != "Paid" AND e.status != "Cancelled"
            ');
            $stmtUpcomingPaymentsCount->execute(['workspace_id' => $workspaceId]);
            $upcomingPaymentsCount = (int) $stmtUpcomingPaymentsCount->fetchColumn();

            // Largest vendor expense
            $stmtLargestExp = $db->prepare('
                SELECT name, contract_value FROM vendors 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 AND contract_value > 0 
                ORDER BY contract_value DESC LIMIT 1
            ');
            $stmtLargestExp->execute(['workspace_id' => $workspaceId]);
            $largestVendorExpense = $stmtLargestExp->fetch(\PDO::FETCH_ASSOC);

            // ── 6. HIGHLIGHT CARDS ──────────────────────────────────────────
            // A. Largest Expense Category
            $stmtMaxCat = $db->prepare('
                SELECT c.name, SUM(e.estimated_cost) as total_est
                FROM budget_categories c
                JOIN budget_expenses e ON e.category_id = c.id AND e.is_deleted = 0
                WHERE c.workspace_id = :workspace_id AND c.is_deleted = 0
                GROUP BY c.id, c.name
                ORDER BY total_est DESC LIMIT 1
            ');
            $stmtMaxCat->execute(['workspace_id' => $workspaceId]);
            $largestExpenseCat = $stmtMaxCat->fetch(\PDO::FETCH_ASSOC);

            // B. Largest Remaining Payment Balance
            $stmtMaxRem = $db->prepare('
                SELECT e.name, e.actual_cost, e.estimated_cost,
                       COALESCE((SELECT SUM(amount) FROM budget_payments p WHERE p.expense_id = e.id AND p.is_deleted = 0), 0.00) as paid
                FROM budget_expenses e
                WHERE e.workspace_id = :workspace_id AND e.is_deleted = 0 AND e.status != "Paid" AND e.status != "Cancelled"
                ORDER BY (CASE WHEN e.actual_cost > 0 THEN e.actual_cost ELSE e.estimated_cost END) - (SELECT COALESCE(SUM(amount), 0.00) FROM budget_payments p WHERE p.expense_id = e.id AND p.is_deleted = 0) DESC
                LIMIT 1
            ');
            $stmtMaxRem->execute(['workspace_id' => $workspaceId]);
            $largestRemainingPayment = $stmtMaxRem->fetch(\PDO::FETCH_ASSOC);
            if ($largestRemainingPayment) {
                $target = (float)($largestRemainingPayment['actual_cost'] > 0 ? $largestRemainingPayment['actual_cost'] : $largestRemainingPayment['estimated_cost']);
                $largestRemainingPayment['balance'] = max(0.00, $target - (float)$largestRemainingPayment['paid']);
            }

            // C. Most Active Guest Group (Group with most confirmed Yes count)
            $stmtActiveGroup = $db->prepare('
                SELECT guest_group, COUNT(*) as yes_count 
                FROM guests 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 AND rsvp_status = "attending"
                GROUP BY guest_group
                ORDER BY yes_count DESC LIMIT 1
            ');
            $stmtActiveGroup->execute(['workspace_id' => $workspaceId]);
            $mostActiveGuestGroup = $stmtActiveGroup->fetch(\PDO::FETCH_ASSOC);

            // D. Newest Vendor
            $stmtNewestVendor = $db->prepare('
                SELECT name, created_at FROM vendors 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 
                ORDER BY created_at DESC LIMIT 1
            ');
            $stmtNewestVendor->execute(['workspace_id' => $workspaceId]);
            $newestVendor = $stmtNewestVendor->fetch(\PDO::FETCH_ASSOC);

            // E. Latest RSVP
            $stmtLatestRsvp = $db->prepare('
                SELECT full_name, rsvp_status, rsvp_submitted_at FROM guests 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 AND rsvp_status != "pending"
                ORDER BY rsvp_submitted_at DESC LIMIT 1
            ');
            $stmtLatestRsvp->execute(['workspace_id' => $workspaceId]);
            $latestRsvp = $stmtLatestRsvp->fetch(\PDO::FETCH_ASSOC);

            // ── 7. RECENT ACTIVITIES LOG (LATEST 10 ITEMS) ──────────────────
            $activities = [];

            // A. Latest RSVP
            $stmtRecentRsvps = $db->prepare('
                SELECT full_name, rsvp_status, rsvp_submitted_at as act_date 
                FROM guests WHERE workspace_id = :workspace_id AND is_deleted = 0 AND rsvp_status != "pending"
                ORDER BY rsvp_submitted_at DESC LIMIT 5
            ');
            $stmtRecentRsvps->execute(['workspace_id' => $workspaceId]);
            foreach ($stmtRecentRsvps->fetchAll(\PDO::FETCH_ASSOC) as $act) {
                $activities[] = [
                    'text' => 'RSVP: ' . escape_html($act['full_name']) . ' responded ' . strtoupper($act['rsvp_status']),
                    'date' => $act['act_date'],
                    'icon' => 'fa-solid fa-envelope-open-text text-sky-500'
                ];
            }

            // B. Latest Payments
            $stmtRecentPays = $db->prepare('
                SELECT p.amount, e.name as expense_name, p.payment_date as act_date 
                FROM budget_payments p JOIN budget_expenses e ON e.id = p.expense_id 
                WHERE e.workspace_id = :workspace_id AND e.is_deleted = 0 AND p.is_deleted = 0 
                ORDER BY p.payment_date DESC, p.id DESC LIMIT 5
            ');
            $stmtRecentPays->execute(['workspace_id' => $workspaceId]);
            foreach ($stmtRecentPays->fetchAll(\PDO::FETCH_ASSOC) as $act) {
                $activities[] = [
                    'text' => 'Budget Payment: Registered installment for ' . escape_html($act['expense_name']),
                    'date' => $act['act_date'],
                    'icon' => 'fa-solid fa-file-invoice-dollar text-emerald-550'
                ];
            }

            // C. Latest Vendor Updates
            $stmtRecentVendors = $db->prepare('
                SELECT name, status, COALESCE(updated_at, created_at) as act_date 
                FROM vendors WHERE workspace_id = :workspace_id AND is_deleted = 0 
                ORDER BY COALESCE(updated_at, created_at) DESC LIMIT 5
            ');
            $stmtRecentVendors->execute(['workspace_id' => $workspaceId]);
            foreach ($stmtRecentVendors->fetchAll(\PDO::FETCH_ASSOC) as $act) {
                $activities[] = [
                    'text' => 'Vendor Profile: Updated ' . escape_html($act['name']) . ' status to ' . $act['status'],
                    'date' => $act['act_date'],
                    'icon' => 'fa-solid fa-store text-indigo-500'
                ];
            }

            // D. Latest Guests Added
            $stmtRecentGuests = $db->prepare('
                SELECT full_name, created_at as act_date 
                FROM guests WHERE workspace_id = :workspace_id AND is_deleted = 0 
                ORDER BY created_at DESC LIMIT 5
            ');
            $stmtRecentGuests->execute(['workspace_id' => $workspaceId]);
            foreach ($stmtRecentGuests->fetchAll(\PDO::FETCH_ASSOC) as $act) {
                $activities[] = [
                    'text' => 'Guest Registry: Added guest ' . escape_html($act['full_name']) . ' to workspace',
                    'date' => $act['act_date'],
                    'icon' => 'fa-solid fa-user-plus text-purple-500'
                ];
            }

            // E. Latest Task Completions
            $stmtRecentTasks = $db->prepare('
                SELECT title, COALESCE(updated_at, created_at) as act_date 
                FROM timeline_tasks WHERE workspace_id = :workspace_id AND is_deleted = 0 AND status = "Completed" 
                ORDER BY act_date DESC LIMIT 5
            ');
            $stmtRecentTasks->execute(['workspace_id' => $workspaceId]);
            foreach ($stmtRecentTasks->fetchAll(\PDO::FETCH_ASSOC) as $act) {
                $activities[] = [
                    'text' => 'Timeline Checklist: Completed task "' . escape_html($act['title']) . '"',
                    'date' => $act['act_date'],
                    'icon' => 'fa-solid fa-circle-check text-teal-500'
                ];
            }

            // Sort and slice to exactly top 10
            usort($activities, function ($a, $b) {
                return strtotime($b['date']) <=> strtotime($a['date']);
            });
            $activities = array_slice($activities, 0, 10);

            // ── 8. UPCOMING SCHEDULE ACTIVITIES ─────────────────────────────
            $upcomingActivities = [];

            // A. Upcoming Payments (nearest 5)
            $stmtUpPay = $db->prepare('
                SELECT e.id, e.name, e.due_date, e.actual_cost, e.estimated_cost 
                FROM budget_expenses e
                WHERE e.workspace_id = :workspace_id AND e.is_deleted = 0 AND e.due_date >= CURDATE() AND e.status != "Paid" AND e.status != "Cancelled"
                ORDER BY e.due_date ASC LIMIT 5
            ');
            $stmtUpPay->execute(['workspace_id' => $workspaceId]);
            foreach ($stmtUpPay->fetchAll(\PDO::FETCH_ASSOC) as $up) {
                $cost = (float) ($up['actual_cost'] > 0 ? $up['actual_cost'] : $up['estimated_cost']);
                $upcomingActivities[] = [
                    'title' => 'Payment Due: ' . escape_html($up['name']) . ' (' . $this->formatMoney($cost, $currency) . ')',
                    'date' => $up['due_date'],
                    'type' => 'Payment',
                    'icon' => 'fa-solid fa-wallet text-amber-500'
                ];
            }

            // B. Upcoming Tasks (nearest 5)
            $stmtUpTasks = $db->prepare('
                SELECT id, title, due_date FROM timeline_tasks 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 AND due_date >= CURDATE() AND status NOT IN ("Completed", "Cancelled")
                ORDER BY due_date ASC LIMIT 5
            ');
            $stmtUpTasks->execute(['workspace_id' => $workspaceId]);
            foreach ($stmtUpTasks->fetchAll(\PDO::FETCH_ASSOC) as $up) {
                $upcomingActivities[] = [
                    'title' => 'Task Deadline: ' . escape_html($up['title']),
                    'date' => $up['due_date'],
                    'type' => 'Task',
                    'icon' => 'fa-solid fa-clock text-indigo-500'
                ];
            }

            // C. Upcoming RSVP Deadline
            $stmtUpRsvp = $db->prepare('
                SELECT rsvp_closes_at FROM invitations WHERE workspace_id = :workspace_id LIMIT 1
            ');
            $stmtUpRsvp->execute(['workspace_id' => $workspaceId]);
            $upRsvp = $stmtUpRsvp->fetch(\PDO::FETCH_ASSOC);
            if ($upRsvp && $upRsvp['rsvp_closes_at'] && strtotime($upRsvp['rsvp_closes_at']) >= time()) {
                $upcomingActivities[] = [
                    'title' => 'RSVP Closes Deadline',
                    'date' => date('Y-m-d', strtotime($upRsvp['rsvp_closes_at'])),
                    'type' => 'RSVP Target',
                    'icon' => 'fa-solid fa-envelope text-rose-500'
                ];
            }

            // Sort upcoming by date ascending
            usort($upcomingActivities, function ($a, $b) {
                return strtotime($a['date']) <=> strtotime($b['date']);
            });

            // ── 9. RENDER VIEW ──────────────────────────────────────────────
            $this->render('invitation/insights', [
                'title' => 'Wedding Insights',
                'workspace' => $workspace,
                'countdown_days' => $countdownDays,
                'weeks_remaining' => $weeksRemaining,
                'months_remaining' => $monthsRemaining,
                'total_budget' => $totalBudget,
                'allocated_budget' => $allocatedBudget,
                'actual_deal_value' => $actualDealValue,
                'paid' => $paid,
                'remaining_cash' => $remainingCash,
                'budget_difference' => $budgetDifference,
                'currency' => $currency,
                'top_planned' => $topPlanned,
                'top_actual' => $topActual,
                'top_diff' => $topDiff,
                'total_guests' => $totalGuests,
                'sent_count' => $sentCount,
                'not_sent_count' => $notSentCount,
                'confirmed' => $confirmed,
                'declined' => $declined,
                'pending' => $pending,
                'need_follow_up' => $needFollowUp,
                'rsvp_percent' => $rsvpPercent,
                'timeline' => $timelineSummary,
                'vendors_count' => $totalVendors,
                'assigned_vendors' => $assignedVendors,
                'unassigned_cats' => $unassignedCats,
                'upcoming_payments_count' => $upcomingPaymentsCount,
                'largest_vendor_expense' => $largestVendorExpense,
                'largest_expense_cat' => $largestExpenseCat,
                'largest_remaining_payment' => $largestRemainingPayment,
                'most_active_guest_group' => $mostActiveGuestGroup,
                'newest_vendor' => $newestVendor,
                'latest_rsvp' => $latestRsvp,
                'activities' => $activities,
                'upcoming_activities' => $upcomingActivities
            ]);

        } catch (\Throwable $e) {
            $this->flash('error', 'Insights failed to load: ' . $e->getMessage());
            $this->redirect('/app/couple/dashboard');
        }
    }

    /**
     * Display the Post Wedding Summary Report.
     * Route: GET /app/couple/report
     */
    public function report(Request $request): void
    {
        $workspaceId = (int) Session::get('active_workspace_id');

        try {
            $db = \App\Core\Database::getConnection();

            // Load Workspace details
            $stmtHeader = $db->prepare('
                SELECT w.id, w.groom_name, w.bride_name, w.wedding_date, w.workspace_status, w.wedding_day_at, p.name as plan_name 
                FROM workspaces w
                JOIN commercial_plans p ON w.commercial_plan_id = p.id
                WHERE w.id = :workspace_id LIMIT 1
            ');
            $stmtHeader->execute(['workspace_id' => $workspaceId]);
            $workspace = $stmtHeader->fetch(\PDO::FETCH_ASSOC);

            if (!$workspace) {
                throw new \RuntimeException("Workspace details not found.");
            }

            // Report is locked if wedding date is in the future AND status is not post_event
            $weddingDatePassed = (strtotime($workspace['wedding_date']) <= strtotime(date('Y-m-d')));
            $isReportCompleted = ($workspace['workspace_status'] === 'post_event');
            $isLocked = !($weddingDatePassed || $isReportCompleted);

            if ($isLocked) {
                $this->render('invitation/report', [
                    'title' => 'Wedding Report',
                    'workspace' => $workspace,
                    'isLocked' => true
                ]);
                return;
            }

            // ── SECTION 1: WEDDING SUMMARY ──────────────────────────────────
            // Fetch Venue Name
            $stmtVenue = $db->prepare('
                SELECT venue_name FROM wedding_events 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 
                ORDER BY is_primary DESC, sort_order ASC LIMIT 1
            ');
            $stmtVenue->execute(['workspace_id' => $workspaceId]);
            $venueName = $stmtVenue->fetchColumn() ?: 'Main Ballroom Venue';

            // ── SECTION 2 & 3: GUEST & WEDDING DAY SUMMARY ───────────────────
            $guestStats = $this->operationModel->getAttendanceStats($workspaceId);

            // Total Invited sum of invited_pax
            $stmtInvited = $db->prepare('SELECT SUM(invited_pax) FROM guests WHERE workspace_id = :workspace_id AND is_deleted = 0');
            $stmtInvited->execute(['workspace_id' => $workspaceId]);
            $totalInvited = (int) $stmtInvited->fetchColumn();

            // Invitation Sent count
            $stmtSent = $db->prepare('SELECT COUNT(*) FROM guests WHERE workspace_id = :workspace_id AND is_deleted = 0 AND invitation_status IN ("sent", "opened")');
            $stmtSent->execute(['workspace_id' => $workspaceId]);
            $invitationsSent = (int) $stmtSent->fetchColumn();

            // RSVP
            $confirmed = $guestStats['rsvp_yes_count'] ?? 0;
            $declined = $guestStats['rsvp_no_count'] ?? 0;
            $pending = $guestStats['rsvp_pending_count'] ?? 0;

            // Checked In
            $stmtCheckin = $db->prepare('SELECT COUNT(*) FROM guests WHERE workspace_id = :workspace_id AND is_deleted = 0 AND checkin_status = "checked_in"');
            $stmtCheckin->execute(['workspace_id' => $workspaceId]);
            $checkedIn = (int) $stmtCheckin->fetchColumn();

            // Walk In Guests (Checked in but RSVP was pending)
            $stmtWalkIn = $db->prepare('SELECT COUNT(*) FROM guests WHERE workspace_id = :workspace_id AND is_deleted = 0 AND checkin_status = "checked_in" AND rsvp_status = "pending"');
            $stmtWalkIn->execute(['workspace_id' => $workspaceId]);
            $walkIns = (int) $stmtWalkIn->fetchColumn();

            // No Show (RSVP was attending but checkin_status is not checked_in)
            $stmtNoShow = $db->prepare('SELECT COUNT(*) FROM guests WHERE workspace_id = :workspace_id AND is_deleted = 0 AND rsvp_status = "attending" AND checkin_status != "checked_in"');
            $stmtNoShow->execute(['workspace_id' => $workspaceId]);
            $noShow = (int) $stmtNoShow->fetchColumn();

            // Attendance Rate
            $attendancePercentage = $confirmed > 0 ? ($checkedIn / $confirmed) * 100 : 0.0;

            // First & Last check-ins
            $stmtFirstCh = $db->prepare('SELECT MIN(checked_in_at) FROM guests WHERE workspace_id = :workspace_id AND checked_in_at IS NOT NULL');
            $stmtFirstCh->execute(['workspace_id' => $workspaceId]);
            $firstCheckin = $stmtFirstCh->fetchColumn();

            $stmtLastCh = $db->prepare('SELECT MAX(checked_in_at) FROM guests WHERE workspace_id = :workspace_id AND checked_in_at IS NOT NULL');
            $stmtLastCh->execute(['workspace_id' => $workspaceId]);
            $lastCheckin = $stmtLastCh->fetchColumn();

            // Peak check-in hour
            $stmtPeak = $db->prepare('
                SELECT HOUR(checked_in_at) as hr, COUNT(*) as cnt 
                FROM guests 
                WHERE workspace_id = :workspace_id AND checkin_status = "checked_in" AND checked_in_at IS NOT NULL
                GROUP BY hr ORDER BY cnt DESC LIMIT 1
            ');
            $stmtPeak->execute(['workspace_id' => $workspaceId]);
            $peakRow = $stmtPeak->fetch(\PDO::FETCH_ASSOC);
            $peakCheckinHour = $peakRow ? (string)$peakRow['hr'] . ':00 - ' . (string)($peakRow['hr'] + 1) . ':00' : '—';

            // ── SECTION 4: BUDGET SUMMARY ───────────────────────────────────
            $stmtBgtSet = $db->prepare('SELECT total_budget, currency FROM budget_settings WHERE workspace_id = :workspace_id LIMIT 1');
            $stmtBgtSet->execute(['workspace_id' => $workspaceId]);
            $bgtSet = $stmtBgtSet->fetch(\PDO::FETCH_ASSOC);
            $totalBudget = (float) ($bgtSet['total_budget'] ?? 150000000.00);
            $currency = $bgtSet['currency'] ?? 'IDR';

            $stmtBgtAgg = $db->prepare('
                SELECT 
                    SUM(estimated_cost) as total_planned,
                    SUM(actual_cost) as total_actual,
                    SUM(CASE WHEN actual_cost > 0 THEN actual_cost ELSE estimated_cost END) as target_allocated
                FROM budget_expenses 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 AND status != "Cancelled"
            ');
            $stmtBgtAgg->execute(['workspace_id' => $workspaceId]);
            $bgtAgg = $stmtBgtAgg->fetch(\PDO::FETCH_ASSOC);

            $actualDealValue = (float) ($bgtAgg['total_actual'] ?? 0.00);
            $targetAllocated = (float) ($bgtAgg['target_allocated'] ?? 0.00);

            $stmtPaid = $db->prepare('SELECT SUM(amount) as total_paid FROM budget_payments WHERE workspace_id = :workspace_id AND is_deleted = 0');
            $stmtPaid->execute(['workspace_id' => $workspaceId]);
            $paid = (float) ($stmtPaid->fetchColumn() ?: 0.00);

            $remainingCash = max(0.00, $totalBudget - $paid);
            $budgetDifference = $totalBudget - $targetAllocated;

            // ── SECTION 5: TIMELINE SUMMARY ──────────────────────────────────
            $timelineStats = $this->timelineModel->getDashboardStats($workspaceId);
            $totalTasks = ($timelineStats['completed_tasks'] ?? 0) + ($timelineStats['pending_tasks'] ?? 0);
            $tasksCompleted = $timelineStats['completed_tasks'] ?? 0;
            $tasksRemaining = $timelineStats['pending_tasks'] ?? 0;
            $tasksOverdue = $timelineStats['overdue_tasks'] ?? 0;
            $timelinePercent = $totalTasks > 0 ? ($tasksCompleted / $totalTasks) * 100 : 0.0;

            // ── SECTION 6: VENDOR SUMMARY ────────────────────────────────────
            $stmtVendors = $db->prepare('SELECT COUNT(*) FROM vendors WHERE workspace_id = :workspace_id AND is_deleted = 0');
            $stmtVendors->execute(['workspace_id' => $workspaceId]);
            $vendorsCount = (int) $stmtVendors->fetchColumn();

            $stmtAssigned = $db->prepare('SELECT COUNT(*) FROM vendors WHERE workspace_id = :workspace_id AND is_deleted = 0 AND status IN ("Booked", "Completed")');
            $stmtAssigned->execute(['workspace_id' => $workspaceId]);
            $assignedVendors = (int) $stmtAssigned->fetchColumn();

            // Completed vs Outstanding payments
            // A vendor profile has completed payment if sum(payments mapped to that vendor) >= vendor contract_value
            $stmtVendorPayments = $db->prepare('
                SELECT v.id, v.contract_value, 
                       COALESCE((SELECT SUM(p.amount) FROM budget_payments p JOIN budget_expenses e ON e.id = p.expense_id WHERE e.vendor_id = v.id AND p.is_deleted = 0), 0.00) as paid
                FROM vendors v
                WHERE v.workspace_id = :workspace_id AND v.is_deleted = 0
            ');
            $stmtVendorPayments->execute(['workspace_id' => $workspaceId]);
            $vPayments = $stmtVendorPayments->fetchAll(\PDO::FETCH_ASSOC);

            $completedPaymentsCount = 0;
            $outstandingPaymentsCount = 0;
            foreach ($vPayments as $vp) {
                if ((float)$vp['paid'] >= (float)$vp['contract_value']) {
                    $completedPaymentsCount++;
                } else {
                    $outstandingPaymentsCount++;
                }
            }

            // Largest vendor expense
            $stmtLargestExp = $db->prepare('
                SELECT name, contract_value FROM vendors 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 AND contract_value > 0 
                ORDER BY contract_value DESC LIMIT 1
            ');
            $stmtLargestExp->execute(['workspace_id' => $workspaceId]);
            $largestVendorExpense = $stmtLargestExp->fetch(\PDO::FETCH_ASSOC);

            // ── SECTION 7: GUEST ENGAGEMENT ──────────────────────────────────
            $stmtWishesCount = $db->prepare('SELECT COUNT(*) FROM wishes WHERE workspace_id = :workspace_id');
            $stmtWishesCount->execute(['workspace_id' => $workspaceId]);
            $wishesCount = (int) $stmtWishesCount->fetchColumn();

            // Latest wishes
            $stmtWishes = $db->prepare('
                SELECT w.message, g.full_name, w.created_at 
                FROM wishes w
                JOIN guests g ON g.id = w.guest_id
                WHERE w.workspace_id = :workspace_id
                ORDER BY w.created_at DESC LIMIT 5
            ');
            $stmtWishes->execute(['workspace_id' => $workspaceId]);
            $latestWishes = $stmtWishes->fetchAll(\PDO::FETCH_ASSOC);

            // ── SECTION 8: FACTUAL HIGHLIGHTS ───────────────────────────────
            // A. Most Expensive Category
            $stmtMaxCat = $db->prepare('
                SELECT c.name, SUM(e.estimated_cost) as total_est
                FROM budget_categories c
                JOIN budget_expenses e ON e.category_id = c.id AND e.is_deleted = 0
                WHERE c.workspace_id = :workspace_id AND c.is_deleted = 0
                GROUP BY c.id, c.name
                ORDER BY total_est DESC LIMIT 1
            ');
            $stmtMaxCat->execute(['workspace_id' => $workspaceId]);
            $largestExpenseCat = $stmtMaxCat->fetch(\PDO::FETCH_ASSOC);

            // B. Largest Vendor Payment
            $stmtMaxPay = $db->prepare('
                SELECT amount, payment_date FROM budget_payments 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 
                ORDER BY amount DESC LIMIT 1
            ');
            $stmtMaxPay->execute(['workspace_id' => $workspaceId]);
            $largestPayment = $stmtMaxPay->fetch(\PDO::FETCH_ASSOC);

            // C. Highest RSVP Group
            $stmtRsvpGroup = $db->prepare('
                SELECT guest_group, COUNT(*) as yes_cnt 
                FROM guests 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 AND rsvp_status = "attending"
                GROUP BY guest_group
                ORDER BY yes_cnt DESC LIMIT 1
            ');
            $stmtRsvpGroup->execute(['workspace_id' => $workspaceId]);
            $highestRsvpGroup = $stmtRsvpGroup->fetch(\PDO::FETCH_ASSOC);

            // D. Latest Payment
            $stmtLatestPay = $db->prepare('
                SELECT p.amount, e.name, p.payment_date 
                FROM budget_payments p
                JOIN budget_expenses e ON e.id = p.expense_id
                WHERE e.workspace_id = :workspace_id AND e.is_deleted = 0 AND p.is_deleted = 0
                ORDER BY p.payment_date DESC, p.id DESC LIMIT 1
            ');
            $stmtLatestPay->execute(['workspace_id' => $workspaceId]);
            $latestPay = $stmtLatestPay->fetch(\PDO::FETCH_ASSOC);

            // E. Latest Timeline Task Completed
            $stmtLatestTask = $db->prepare('
                SELECT title, COALESCE(updated_at, created_at) as act_date 
                FROM timeline_tasks 
                WHERE workspace_id = :workspace_id AND is_deleted = 0 AND status = "Completed" 
                ORDER BY act_date DESC LIMIT 1
            ');
            $stmtLatestTask->execute(['workspace_id' => $workspaceId]);
            $latestTask = $stmtLatestTask->fetch(\PDO::FETCH_ASSOC);

            // Render view
            $this->render('invitation/report', [
                'title' => 'Wedding Report',
                'workspace' => $workspace,
                'isLocked' => false,
                'venue_name' => $venueName,
                'total_invited' => $totalInvited,
                'sent_count' => $invitationsSent,
                'confirmed' => $confirmed,
                'declined' => $declined,
                'pending' => $pending,
                'checked_in' => $checkedIn,
                'attendance_percent' => $attendancePercentage,
                'walk_ins' => $walkIns,
                'no_show' => $noShow,
                'peak_checkin_hour' => $peakCheckinHour,
                'first_checkin' => $firstCheckin,
                'last_checkin' => $lastCheckin,
                'total_budget' => $totalBudget,
                'actual_deal_value' => $actualDealValue,
                'paid' => $paid,
                'remaining_cash' => $remainingCash,
                'budget_difference' => $budgetDifference,
                'currency' => $currency,
                'total_tasks' => $totalTasks,
                'tasks_completed' => $tasksCompleted,
                'tasks_remaining' => $tasksRemaining,
                'tasks_overdue' => $tasksOverdue,
                'timeline_percent' => $timelinePercent,
                'vendors_count' => $vendorsCount,
                'assigned_vendors' => $assignedVendors,
                'completed_payments' => $completedPaymentsCount,
                'outstanding_payments' => $outstandingPaymentsCount,
                'largest_vendor_expense' => $largestVendorExpense,
                'wishes_count' => $wishesCount,
                'latest_wishes' => $latestWishes,
                'largest_expense_cat' => $largestExpenseCat,
                'largest_payment' => $largestPayment,
                'highest_rsvp_group' => $highestRsvpGroup,
                'latest_payment' => $latestPay,
                'latest_task' => $latestTask
            ]);

        } catch (\Throwable $e) {
            $this->flash('error', 'Report failed to load: ' . $e->getMessage());
            $this->redirect('/app/couple/dashboard');
        }
    }

    /**
     * Manually mark the wedding as Completed.
     * Route: POST /app/couple/report/complete
     */
    public function completeReport(Request $request): void
    {
        $workspaceId = (int) Session::get('active_workspace_id');

        try {
            $db = \App\Core\Database::getConnection();
            $stmt = $db->prepare('
                UPDATE workspaces 
                SET workspace_status = "post_event", 
                    wedding_day_at = NOW(),
                    updated_at = NOW()
                WHERE id = :workspace_id
            ');
            $stmt->execute(['workspace_id' => $workspaceId]);

            $this->flash('success', 'Congratulations! Your wedding has been marked as completed. The report is now active.');
        } catch (\Throwable $e) {
            $this->flash('error', 'Failed to update wedding status: ' . $e->getMessage());
        }

        $this->redirect('/app/couple/report');
    }

    private function formatMoney(float $amount, string $currency = 'IDR'): string
    {
        if ($currency === 'IDR') {
            return 'Rp ' . number_format($amount, 0, ',', '.');
        }
        return $currency . ' ' . number_format($amount, 2, '.', ',');
    }
}

