<?php
/**
 * Dashboard API Endpoint
 * Fetches all dashboard statistics and data
 */

require_once '../../includes/db_connection.php';
require_once '../includes/auth.php';

// Require authentication
requireAuth();

header('Content-Type: application/json');

try {
    $db = Database::getInstance()->getConnection();
    
    $section = $_GET['section'] ?? 'all';
    $period = $_GET['period'] ?? 'year'; // Default to year for charts
    
    // Helper to get date condition
    function getDateCondition($period, $column = 'created_at') {
        switch ($period) {
            case 'today':
                return "DATE($column) = CURDATE()";
            case 'week':
                return "$column >= DATE_SUB(NOW(), INTERVAL 1 WEEK)";
            case 'month':
                return "$column >= DATE_SUB(NOW(), INTERVAL 1 MONTH)";
            case 'year':
                return "$column >= DATE_SUB(NOW(), INTERVAL 1 YEAR)";
            default:
                return "1=1";
        }
    }
    
    $response = ['success' => true, 'data' => []];
    
    // 1. Income Chart Data
    if ($section === 'all' || $section === 'income') {
        $incomeData = [];
        $dateCond = getDateCondition($period);
        
        // Adjust grouping based on period
        if ($period === 'today') {
            // Group by hour
            $stmt = $db->prepare("
                SELECT DATE_FORMAT(created_at, '%H:00') as label, SUM(total_amount) as total 
                FROM orders 
                WHERE payment_status = 'paid' AND $dateCond
                GROUP BY DATE_FORMAT(created_at, '%H:00')
                ORDER BY created_at ASC
            ");
        } elseif ($period === 'week' || $period === 'month') {
            // Group by day
            $stmt = $db->prepare("
                SELECT DATE_FORMAT(created_at, '%b %d') as label, SUM(total_amount) as total 
                FROM orders 
                WHERE payment_status = 'paid' AND $dateCond
                GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d')
                ORDER BY created_at ASC
            ");
        } else {
            // Group by month (default/year)
            $stmt = $db->prepare("
                SELECT DATE_FORMAT(created_at, '%b') as label, SUM(total_amount) as total 
                FROM orders 
                WHERE payment_status = 'paid' AND $dateCond
                GROUP BY DATE_FORMAT(created_at, '%Y-%m')
                ORDER BY created_at ASC
            ");
        }
        
        $stmt->execute();
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Format for chart
        $labels = array_column($results, 'label');
        $data = array_map('floatval', array_column($results, 'total'));
        
        if ($section === 'income') {
            echo json_encode(['success' => true, 'data' => ['labels' => $labels, 'data' => $data]]);
            exit;
        }
        $response['data']['charts']['monthly_income'] = ['labels' => $labels, 'data' => $data];
    }
    
    // 2. Best Selling Products
    if ($section === 'all' || $section === 'products') {
        $dateCond = getDateCondition($period, 'oi.created_at');
        
        $stmt = $db->prepare("
            SELECT p.id, p.title, p.slug, p.price, p.status, SUM(oi.quantity) as total_sold,
            (SELECT c.name FROM collections c JOIN product_collections pc ON c.id = pc.collection_id WHERE pc.product_id = p.id LIMIT 1) as category
            FROM order_items oi
            JOIN products p ON oi.product_id = p.id
            WHERE $dateCond
            GROUP BY p.id
            ORDER BY total_sold DESC
            LIMIT 5
        ");
        $stmt->execute();
        $products = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        foreach ($products as &$product) {
            $product['image'] = 'assets/images/products/01.png';
            $product['price'] = number_format($product['price'], 2);
        }
        
        if ($section === 'products') {
            echo json_encode(['success' => true, 'data' => $products]);
            exit;
        }
        $response['data']['best_selling_products'] = $products;
    }

    // 3. Other Data (Only for 'all')
    if ($section === 'all') {
        // Top Stats
        $stmt = $db->query("SELECT SUM(total_amount) as total FROM orders WHERE payment_status = 'paid'");
        $totalRevenue = $stmt->fetch(PDO::FETCH_ASSOC)['total'] ?? 0;
        
        $stmt = $db->query("SELECT COUNT(*) as total FROM orders");
        $totalOrders = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
        
        $stmt = $db->query("SELECT COUNT(*) as total FROM customers");
        $totalCustomers = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
        
        $stmt = $db->query("SELECT COUNT(*) as total FROM products WHERE status = 'active'");
        $totalProducts = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
        
        $response['data']['stats'] = [
            'revenue' => $totalRevenue,
            'orders' => $totalOrders,
            'customers' => $totalCustomers,
            'products' => $totalProducts
        ];
        
        // Recent Orders
        $stmt = $db->query("
            SELECT id, order_number, customer_first_name, customer_last_name, total_amount, payment_status, created_at 
            FROM orders 
            ORDER BY created_at DESC 
            LIMIT 5
        ");
        $response['data']['recent_orders'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Order Status
        $stmt = $db->query("SELECT delivery_status, COUNT(*) as count FROM orders GROUP BY delivery_status");
        $orderStatusRaw = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
        $orderStatus = [
            'fulfilled' => $orderStatusRaw['delivered'] ?? 0,
            'unfulfilled' => $orderStatusRaw['pending'] ?? 0,
            'shipped' => $orderStatusRaw['in_transit'] ?? 0,
            'cancelled' => $orderStatusRaw['cancelled'] ?? 0
        ];
        $totalOrdersCount = array_sum($orderStatus);
        $orderStatusPercentages = [];
        foreach ($orderStatus as $status => $count) {
            $orderStatusPercentages[$status] = $totalOrdersCount > 0 ? round(($count / $totalOrdersCount) * 100) : 0;
        }
        $response['data']['order_status'] = ['counts' => $orderStatus, 'percentages' => $orderStatusPercentages];
        
        // Low Stock (Empty for now)
        $response['data']['low_stock'] = [];
        
        // Best Selling Categories
        $stmt = $db->query("
            SELECT c.name as title, SUM(oi.quantity) as total_sold, SUM(oi.total_price) as total_revenue
            FROM order_items oi
            JOIN product_collections pc ON oi.product_id = pc.product_id
            JOIN collections c ON pc.collection_id = c.id
            GROUP BY c.id
            ORDER BY total_sold DESC
            LIMIT 5
        ");
        $response['data']['best_selling_categories'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Recent Refunds
        try {
            $stmt = $db->query("
                SELECT r.id, r.refund_number, r.refund_amount, r.refund_reason, r.created_at,
                       c.first_name, c.last_name
                FROM refunds r
                JOIN customers c ON r.customer_id = c.id
                ORDER BY r.created_at DESC
                LIMIT 5
            ");
            $response['data']['recent_refunds'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            $response['data']['recent_refunds'] = [];
        }
        
        // New Customers Chart
        $newCustomers = [];
        for ($i = 6; $i >= 0; $i--) {
            $date = date('Y-m-d', strtotime("-$i days"));
            $dayName = date('D', strtotime("-$i days"));
            $stmt = $db->prepare("SELECT COUNT(*) as count FROM customers WHERE DATE(created_at) = ?");
            $stmt->execute([$date]);
            $count = $stmt->fetch(PDO::FETCH_ASSOC)['count'];
            $newCustomers[] = ['day' => $dayName, 'count' => (int)$count];
        }
        $response['data']['charts']['new_customers'] = $newCustomers;
    }
    
    echo json_encode($response);
    
} catch (Exception $e) {
    error_log('Dashboard API error: ' . $e->getMessage());
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'message' => 'Failed to fetch dashboard data: ' . $e->getMessage()
    ]);
}
