<?php
session_start();
header('Content-Type: application/json');

// 🔐 Check for session authentication
if (
    !isset($_SESSION['username'], $_SESSION['database'], $_SESSION['userid']) ||
    empty($_SESSION['username']) || empty($_SESSION['database']) || empty($_SESSION['userid'])
) {
    echo json_encode(['error' => 'Unauthorized. Please log in.']);
    http_response_code(401);
    exit();
}
require_once 'connection.php';

$data = [];

// 1. KPI Metrics
$query1 = "
    SELECT 
        SUM(Revenue) AS total_revenue,
        COUNT(*) AS total_orders,
        AVG(Revenue) AS avg_order_value,
        SUM(CASE WHEN [Order Status] = 'Delivered' THEN 1 ELSE 0 END) AS delivered_orders,
        SUM(CASE WHEN PaymentStatus = 'Paid' THEN 1 ELSE 0 END) AS paid_orders
    FROM DemoSales
";
$stmt1 = $pdo->query($query1);
$data['kpi'] = $stmt1->fetch(PDO::FETCH_ASSOC);

// 2. Avg Delivery Days by Courier Partner
$query2 = "
    SELECT [Courier Partner], AVG(CAST([Order Delivery days] AS FLOAT)) AS avg_days 
    FROM DemoSales 
    WHERE [Courier Partner] IS NOT NULL AND [Order Delivery days] IS NOT NULL
    GROUP BY [Courier Partner]
    ORDER BY avg_days
";
$stmt2 = $pdo->query($query2);
$data['courier_avg_days'] = $stmt2->fetchAll(PDO::FETCH_ASSOC);

// 3. Top 10 Cities by Revenue
$query3 = "
    SELECT TOP 10 City, SUM(Revenue) AS total_revenue 
    FROM DemoSales 
    GROUP BY City 
    ORDER BY total_revenue DESC
";
$stmt3 = $pdo->query($query3);
$data['top_cities'] = $stmt3->fetchAll(PDO::FETCH_ASSOC);

// 4. Gender Distribution
$query4 = "
    SELECT Gender, COUNT(*) AS count 
    FROM DemoSales 
    WHERE Gender IS NOT NULL
    GROUP BY Gender
";
$stmt4 = $pdo->query($query4);
$data['gender'] = $stmt4->fetchAll(PDO::FETCH_ASSOC);

// 5. Payment Method Revenue
$query5 = "
    SELECT [Payment Method], SUM(Revenue) AS total_revenue 
    FROM DemoSales 
    GROUP BY [Payment Method] 
    ORDER BY total_revenue DESC
";
$stmt5 = $pdo->query($query5);
$data['payment_methods'] = $stmt5->fetchAll(PDO::FETCH_ASSOC);

// 6. Orders by Status
$query6 = "
    SELECT [Order Status], COUNT(*) AS count 
    FROM DemoSales 
    GROUP BY [Order Status]
";
$stmt6 = $pdo->query($query6);
$data['order_status'] = $stmt6->fetchAll(PDO::FETCH_ASSOC);

// 7. Top 5 Categories by Revenue
$query7 = "
    SELECT TOP(5) ProdCategory, SUM(Revenue) AS total_revenue 
    FROM DemoSales 
    GROUP BY ProdCategory 
    ORDER BY total_revenue DESC
";
$stmt7 = $pdo->query($query7);
$data['categories'] = $stmt7->fetchAll(PDO::FETCH_ASSOC);

// Output as JSON
header('Content-Type: application/json');
echo json_encode($data, JSON_PRETTY_PRINT);
