<?php
require_once __DIR__ . '/../../../Models/Database.php';

class ReportController {
    public function index() {
        $db = Database::getInstance();
        
        // Revenue Chart (Last 6 Months)
        $revenueStmt = $db->query("
            SELECT DATE_FORMAT(created_at, '%b %Y') as month, SUM(amount) as total
            FROM payments
            WHERE status = 'paid' AND created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
            GROUP BY month
            ORDER BY MIN(created_at) ASC
        ");
        $revenueData = $revenueStmt->fetchAll();

        // Registration Chart (Last 6 Months)
        $customerStmt = $db->query("
            SELECT DATE_FORMAT(created_at, '%b %Y') as month, COUNT(*) as count
            FROM customers
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
            GROUP BY month
            ORDER BY MIN(created_at) ASC
        ");
        $customerData = $customerStmt->fetchAll();

        $vendorStmt = $db->query("
            SELECT DATE_FORMAT(created_at, '%b %Y') as month, COUNT(*) as count
            FROM vendors
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
            GROUP BY month
            ORDER BY MIN(created_at) ASC
        ");
        $vendorData = $vendorStmt->fetchAll();

        // Build continuous 6-month timeline to guarantee charts always render beautifully
        $monthsTimeline = [];
        $chartRevenue = [];
        $chartCustomersCount = 0;
        $chartVendorsCount = 0;

        for ($i = 5; $i >= 0; $i--) {
            $m = date('M Y', strtotime("-$i months"));
            $monthsTimeline[] = $m;
            $chartRevenue[$m] = 0.0;
        }

        foreach ($revenueData as $row) {
            $monthKey = $row['month'];
            if (array_key_exists($monthKey, $chartRevenue)) {
                $chartRevenue[$monthKey] = (float)$row['total'];
            }
        }

        $tempCustomers = [];
        $tempVendors = [];
        foreach ($monthsTimeline as $m) {
            $tempCustomers[$m] = 0;
            $tempVendors[$m] = 0;
        }
        foreach ($customerData as $row) {
            if (array_key_exists($row['month'], $tempCustomers)) {
                $tempCustomers[$row['month']] = (int)$row['count'];
            }
        }
        foreach ($vendorData as $row) {
            if (array_key_exists($row['month'], $tempVendors)) {
                $tempVendors[$row['month']] = (int)$row['count'];
            }
        }
        $chartCustomersCount = array_sum($tempCustomers);
        $chartVendorsCount = array_sum($tempVendors);

        // Top Maids by Views
        $topViewedMaidsStmt = $db->query("
            SELECT m.id, m.full_name, m.views, v.agency_name
            FROM maids m
            LEFT JOIN vendors v ON m.vendor_id = v.id
            ORDER BY m.views DESC
            LIMIT 5
        ");
        $topViewedMaids = $topViewedMaidsStmt->fetchAll();

        // Top Maids by Interview Requests
        $topRequestedMaidsStmt = $db->query("
            SELECT m.id, m.full_name, v.agency_name, COUNT(i.id) as request_count
            FROM maids m
            LEFT JOIN interview_requests i ON m.id = i.maid_id
            LEFT JOIN vendors v ON m.vendor_id = v.id
            GROUP BY m.id
            ORDER BY request_count DESC
            LIMIT 5
        ");
        $topRequestedMaids = $topRequestedMaidsStmt->fetchAll();

        // Most Active Customers
        $activeCustomersStmt = $db->query("
            SELECT 
                c.id, 
                c.full_name, 
                c.email,
                COUNT(DISTINCT i.id) as interview_count,
                COUNT(DISTINCT q.id) as quote_count,
                COUNT(DISTINCT con.id) as contract_count,
                (COUNT(DISTINCT i.id) + COUNT(DISTINCT q.id) + COUNT(DISTINCT con.id)) as total_actions
            FROM customers c
            LEFT JOIN interview_requests i ON c.id = i.customer_id
            LEFT JOIN quotation_requests q ON c.id = q.customer_id
            LEFT JOIN contracts con ON c.id = con.customer_id
            GROUP BY c.id
            ORDER BY total_actions DESC
            LIMIT 5
        ");
        $activeCustomers = $activeCustomersStmt->fetchAll();

        // Customer Engagement Metrics
        $totalCustomers = (int)$db->query("SELECT COUNT(*) FROM customers")->fetchColumn();
        $totalInterviews = (int)$db->query("SELECT COUNT(*) FROM interview_requests")->fetchColumn();
        $totalQuotes = (int)$db->query("SELECT COUNT(*) FROM quotation_requests")->fetchColumn();
        $totalFavorites = (int)$db->query("SELECT COUNT(*) FROM favorite_maids")->fetchColumn();
        $convertedCustomers = (int)$db->query("SELECT COUNT(DISTINCT customer_id) FROM contracts WHERE status = 'active'")->fetchColumn();

        $avgInterviews = $totalCustomers > 0 ? round($totalInterviews / $totalCustomers, 2) : 0;
        $avgQuotes = $totalCustomers > 0 ? round($totalQuotes / $totalCustomers, 2) : 0;
        $avgFavorites = $totalCustomers > 0 ? round($totalFavorites / $totalCustomers, 2) : 0;
        $conversionRate = $totalCustomers > 0 ? round(($convertedCustomers / $totalCustomers) * 100, 1) : 0;

        $engagementMetrics = [
            'avg_interviews' => $avgInterviews,
            'avg_quotes' => $avgQuotes,
            'avg_favorites' => $avgFavorites,
            'conversion_rate' => $conversionRate,
            'total_favorites' => $totalFavorites
        ];

        require __DIR__ . '/../../../../views/admin/reports/index.php';
    }

    public function exportPayments() {
        $db = Database::getInstance();
        $stmt = $db->query("
            SELECT vs.*, v.agency_name, sp.name_en as plan_name, sp.price as amount 
            FROM vendor_subscriptions vs
            JOIN vendors v ON vs.vendor_id = v.id
            JOIN subscription_plans sp ON vs.plan_id = sp.id
            ORDER BY vs.created_at DESC
        ");
        $data = $stmt->fetchAll();

        $filename = "payments_report_" . date('Y-m-d') . ".csv";
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');

        $output = fopen('php://output', 'w');
        fputcsv($output, ['ID', 'Vendor', 'Plan', 'Amount', 'Status', 'Date']);

        foreach ($data as $row) {
            fputcsv($output, [
                $row['id'],
                $row['agency_name'],
                $row['plan_name'],
                $row['amount'],
                $row['status'],
                $row['created_at']
            ]);
        }
        fclose($output);
        exit;
    }

    public function exportRegistrations() {
        $db = Database::getInstance();
        
        // Export Customers
        $type = $_GET['type'] ?? 'customers';
        if ($type === 'vendors') {
            $stmt = $db->query("SELECT id, agency_name as name, email, phone, status, created_at FROM vendors ORDER BY created_at DESC");
        } else {
            $stmt = $db->query("SELECT id, full_name as name, email, phone, status, created_at FROM customers ORDER BY created_at DESC");
        }
        $data = $stmt->fetchAll();

        $filename = $type . "_report_" . date('Y-m-d') . ".csv";
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');

        $output = fopen('php://output', 'w');
        fputcsv($output, ['ID', 'Name', 'Email', 'Phone', 'Status', 'Joined Date']);

        foreach ($data as $row) {
            fputcsv($output, [
                $row['id'],
                $row['name'],
                $row['email'],
                $row['phone'],
                $row['status'],
                $row['created_at']
            ]);
        }
        fclose($output);
        exit;
    }
}
