<?php
// Enable error reporting for debugging
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Include necessary files
require_once '../../config/cors.php';
require_once '../../config/database.php';
require_once '../../middleware/auth.php'; // Assuming this handles authentication
require_once '../../utils/jwt.php';      // Assuming this is for JWT utilities

// Establish database connection
$database = new Database();
$db = $database->getConnection();

// Handle only GET requests
if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    try {
        // Get time filter from query string, default to '7d'
        $timeFilter = $_GET['timeFilter'] ?? '7d';
        
        // --- FIX IMPLEMENTED HERE ---
        // Create a reusable date calculation string instead of a full WHERE clause.
        // This makes it easier to apply to different tables and avoids ambiguity.
        $dateSubQuery = "DATE_SUB(NOW(), INTERVAL 7 DAY)"; // Default value
        switch ($timeFilter) {
            case '24h':
                $dateSubQuery = "DATE_SUB(NOW(), INTERVAL 1 DAY)";
                break;
            case '7d':
                $dateSubQuery = "DATE_SUB(NOW(), INTERVAL 7 DAY)";
                break;
            case '30d':
                $dateSubQuery = "DATE_SUB(NOW(), INTERVAL 30 DAY)";
                break;
            case '90d':
                $dateSubQuery = "DATE_SUB(NOW(), INTERVAL 90 DAY)";
                break;
            case '1y':
                $dateSubQuery = "DATE_SUB(NOW(), INTERVAL 1 YEAR)";
                break;
        }

        // 1. Sales Trend Data (Revenue over time)
        $salesTrendStmt = $db->prepare("
            SELECT 
                DATE_FORMAT(created_at, '%b %d') as label,
                DATE(created_at) as date,
                SUM(total_price) as value
            FROM orders 
            WHERE created_at >= $dateSubQuery -- Using the new variable
            GROUP BY DATE(created_at)
            ORDER BY date ASC
            LIMIT 10
        ");
        $salesTrendStmt->execute();
        $salesTrend = $salesTrendStmt->fetchAll(PDO::FETCH_ASSOC);

        // 2. Weekly Orders Data (No change needed, specific interval)
        $weeklyOrdersStmt = $db->prepare("
            SELECT 
                DAYNAME(created_at) as label,
                COUNT(*) as value
            FROM orders 
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
            GROUP BY DAYOFWEEK(created_at), DAYNAME(created_at)
            ORDER BY DAYOFWEEK(created_at)
        ");
        $weeklyOrdersStmt->execute();
        $weeklyOrders = $weeklyOrdersStmt->fetchAll(PDO::FETCH_ASSOC);

        // 3. Order Status Distribution
        $orderStatusStmt = $db->prepare("
            SELECT 
                status as label,
                COUNT(*) as value,
                CASE 
                    WHEN status = 'delivered' THEN '#10b981'
                    WHEN status = 'processing' THEN '#3b82f6'
                    WHEN status = 'pending' THEN '#f59e0b'
                    WHEN status = 'cancelled' THEN '#ef4444'
                    ELSE '#6b7280'
                END as color
            FROM orders 
            WHERE created_at >= $dateSubQuery -- Using the new variable
            GROUP BY status
        ");
        $orderStatusStmt->execute();
        $orderStatus = $orderStatusStmt->fetchAll(PDO::FETCH_ASSOC);

        // 4. Top Categories Data
        // --- AMBIGUITY FIX APPLIED ---
        // We specify `o.created_at` to tell the database to use the `created_at`
        // column from the `orders` table (aliased as `o`).
        $topCategoriesStmt = $db->prepare("
            SELECT 
                c.name as label,
                COUNT(oi.id) as value
            FROM categories c
            JOIN products p ON c.id = p.category_id
            JOIN order_items oi ON p.id = oi.product_id
            JOIN orders o ON oi.order_id = o.id
            WHERE o.created_at >= $dateSubQuery -- The fix is here: `o.created_at`
            GROUP BY c.id, c.name
            ORDER BY value DESC
            LIMIT 5
        ");
        $topCategoriesStmt->execute();
        $topCategories = $topCategoriesStmt->fetchAll(PDO::FETCH_ASSOC);

        // 5. Today's Highlights (No change needed)
        $todayHighlightsStmt = $db->prepare("
            SELECT 
                COUNT(*) as orders_today,
                COALESCE(SUM(total_price), 0) as revenue_today,
                COUNT(DISTINCT user_id) as new_customers_today
            FROM orders 
            WHERE DATE(created_at) = CURDATE()
        ");
        $todayHighlightsStmt->execute();
        $todayHighlights = $todayHighlightsStmt->fetch(PDO::FETCH_ASSOC);

        // 6. Performance Metrics (No change needed)
        $performanceStmt = $db->prepare("
            SELECT 
                COUNT(*) as total_orders,
                AVG(total_price) as avg_order_value,
                SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*) * 100 as return_rate
            FROM orders 
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        ");
        $performanceStmt->execute();
        $performance = $performanceStmt->fetch(PDO::FETCH_ASSOC);

        // 7. Recent Activities (mixed data)
        // --- AMBIGUITY FIX APPLIED ---
        // The final `ORDER BY created_at` is also ambiguous. We create a common
        // column alias `activity_time` in both SELECTs of the UNION to sort reliably.
        $activitiesStmt = $db->prepare("
            (SELECT 
                'new_order' as type,
                o.id,
                u.name as user_name,
                o.total_price,
                o.created_at as activity_time, -- Aliased for sorting
                CONCAT('New order #', o.id, ' placed by ', u.name) as message
            FROM orders o 
            JOIN users u ON o.user_id = u.id 
            ORDER BY o.created_at DESC 
            LIMIT 10)
            UNION ALL
            (SELECT 
                'new_user' as type,
                u.id,
                u.name as user_name,
                NULL as total_price,
                u.created_at as activity_time, -- Aliased for sorting
                CONCAT('New user ', u.name, ' registered') as message
            FROM users u 
            ORDER BY u.created_at DESC 
            LIMIT 5)
            ORDER BY activity_time DESC -- Sorting by the common alias
            LIMIT 15
        ");
        $activitiesStmt->execute();
        $activities = $activitiesStmt->fetchAll(PDO::FETCH_ASSOC);

        // Prepare the final JSON response
        $response = [
            'success' => true,
            'timeFilter' => $timeFilter,
            'charts' => [
                'salesTrend' => array_map(function($item) {
                    return [
                        'label' => $item['label'],
                        'value' => (float)$item['value']
                    ];
                }, $salesTrend),
                'weeklyOrders' => array_map(function($item) {
                    return [
                        'label' => substr($item['label'], 0, 3),
                        'value' => (int)$item['value']
                    ];
                }, $weeklyOrders),
                'orderStatus' => array_map(function($item) {
                    return [
                        'label' => ucfirst($item['label']),
                        'value' => (int)$item['value'],
                        'color' => $item['color']
                    ];
                }, $orderStatus),
                'topCategories' => array_map(function($item) {
                    return [
                        'label' => $item['label'],
                        'value' => (int)$item['value']
                    ];
                }, $topCategories)
            ],
            'highlights' => [
                'ordersToday' => (int)$todayHighlights['orders_today'],
                'revenueToday' => (float)$todayHighlights['revenue_today'],
                'newCustomersToday' => (int)$todayHighlights['new_customers_today'],
                'avgResponseTime' => '2.3min' // Placeholder, requires separate tracking
            ],
            'performance' => [
                'conversionRate' => '3.2%', // Placeholder, requires view tracking
                'avgOrderValue' => (float)$performance['avg_order_value'],
                'returnRate' => round((float)$performance['return_rate'], 1) . '%',
                'customerSatisfaction' => '4.8/5' // Placeholder, requires reviews
            ],
            'activities' => $activities
        ];

        http_response_code(200);
        header('Content-Type: application/json');
        echo json_encode($response);

    } catch (PDOException $e) {
        // Catch and return any database errors
        http_response_code(500);
        header('Content-Type: application/json');
        echo json_encode([
            'success' => false,
            'error' => 'Database Query Failed: ' . $e->getMessage()
        ]);
    }
} else {
    // Handle incorrect HTTP method
    http_response_code(405);
    header('Content-Type: application/json');
    echo json_encode([
        'success' => false,
        'error' => 'Method not allowed'
    ]);
}
?>
