<?php
require_once __DIR__ . '/../../../Models/Customer.php';
require_once __DIR__ . '/../../../Models/Vendor.php';
require_once __DIR__ . '/../../../Models/Maid.php';
require_once __DIR__ . '/../../../Models/Contract.php';

class DashboardController {
    public function index() {
        $customerModel = new Customer();
        $vendorModel = new Vendor();
        $maidModel = new Maid();
        $contractModel = new Contract();

        $db = Database::getInstance();

        // Fetch Chart Data Dynamically
        // 1. Registration Growth Chart (Last 6 Months)
        $growthData = [];
        $growthLabels = [];
        for ($i = 5; $i >= 0; $i--) {
            $monthKey = date('Y-m', strtotime("-$i months"));
            $monthLabel = date('M', strtotime("-$i months"));
            $growthLabels[] = $monthLabel;
            $growthData[$monthKey] = 0;
        }

        $growthRaw = $db->query("
            SELECT DATE_FORMAT(created_at, '%Y-%m') as ym, COUNT(*) as count 
            FROM (
                SELECT created_at FROM customers
                UNION ALL
                SELECT created_at FROM vendors
            ) combined
            WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
            GROUP BY DATE_FORMAT(created_at, '%Y-%m')
        ")->fetchAll(PDO::FETCH_ASSOC);

        foreach ($growthRaw as $row) {
            if (isset($growthData[$row['ym']])) {
                $growthData[$row['ym']] = (int)$row['count'];
            }
        }
        $growthValues = array_values($growthData);

        // 2. Revenue Overview Chart
        $revenueRaw = $db->query("
            SELECT related_type, SUM(amount) as total 
            FROM payments 
            WHERE status = 'paid'
            GROUP BY related_type
        ")->fetchAll(PDO::FETCH_ASSOC);

        $revenueBreakdown = [
            'subscription' => 0,
            'ad' => 0,
            'contract' => 0,
            'quotation' => 0
        ];
        foreach ($revenueRaw as $row) {
            if (isset($revenueBreakdown[$row['related_type']])) {
                $revenueBreakdown[$row['related_type']] = (float)$row['total'];
            }
        }

        $data = [
            'total_customers' => $customerModel->getTotalCount(),
            'total_vendors' => $vendorModel->getTotalCount(),
            'pending_vendors' => $db->query("SELECT COUNT(*) FROM vendors WHERE status = 'pending'")->fetchColumn(),
            'total_maids' => $maidModel->getTotalCount(),
            'pending_maids' => $db->query("SELECT COUNT(*) FROM maids WHERE status = 'pending'")->fetchColumn(),
            'available_maids' => $maidModel->getTotalAvailableCount(),
            'active_contracts' => $contractModel->getActiveCount(),
            'pending_quotations' => $db->query("SELECT COUNT(*) FROM quotation_requests WHERE status = 'pending'")->fetchColumn(),
            'interviews_today' => $db->query("SELECT COUNT(*) FROM interview_requests WHERE interview_date = CURDATE()")->fetchColumn(),
            'active_subscriptions' => $db->query("SELECT COUNT(*) FROM vendor_subscriptions WHERE status = 'active'")->fetchColumn(),
            'total_revenue' => $db->query("SELECT SUM(amount) FROM payments WHERE status = 'paid'")->fetchColumn() ?: 0,
            'active_ads' => $db->query("SELECT COUNT(*) FROM vendor_ads WHERE status = 'active'")->fetchColumn(),
            
            // New Advanced Metrics
            'total_maid_views' => $db->query("SELECT SUM(views) FROM maids")->fetchColumn() ?: 0,
            'total_favorites' => $db->query("SELECT COUNT(*) FROM favorite_maids")->fetchColumn(),
            'customer_conversion_rate' => (function($db) {
                $total = (int)$db->query("SELECT COUNT(*) FROM customers")->fetchColumn();
                $converted = (int)$db->query("SELECT COUNT(DISTINCT customer_id) FROM contracts WHERE status = 'active'")->fetchColumn();
                return $total > 0 ? round(($converted / $total) * 100, 1) : 0;
            })($db),
            'avg_interviews' => (function($db) {
                $totalCustomers = (int)$db->query("SELECT COUNT(*) FROM customers")->fetchColumn();
                $totalInterviews = (int)$db->query("SELECT COUNT(*) FROM interview_requests")->fetchColumn();
                return $totalCustomers > 0 ? round($totalInterviews / $totalCustomers, 2) : 0;
            })($db),
            
            // Recent Lists
            'recent_vendors' => $db->query("SELECT id, agency_name, status, created_at FROM vendors ORDER BY created_at DESC LIMIT 5")->fetchAll(),
            'recent_customers' => $db->query("SELECT id, full_name, status, created_at FROM customers ORDER BY created_at DESC LIMIT 5")->fetchAll(),
            'recent_payments' => $db->query("SELECT id, amount, status, created_at FROM payments ORDER BY created_at DESC LIMIT 5")->fetchAll(),
            'recent_tickets' => $db->query("SELECT id, subject, status, created_at FROM support_tickets ORDER BY created_at DESC LIMIT 5")->fetchAll(),
            
            // Charts Data
            'chart_growth_labels' => $growthLabels,
            'chart_growth_values' => $growthValues,
            'chart_revenue_breakdown' => $revenueBreakdown,
        ];

        // Extract makes array keys available as variables in the view
        extract($data);
        require __DIR__ . '/../../../../views/admin/dashboard.php';
    }
}
