<?php
/**
 * GET /api/admin/kpi.php
 * Admin-only. One endpoint for the whole KPI dashboard - summary figures
 * plus the time-series/breakdown data the charts need, so the Overview
 * tab loads in one request instead of several.
 *
 * "Fulfilled" is the source of truth for anything revenue-related, per
 * the sales-confirmation workflow: a pending order isn't a sale yet, and
 * a cancelled one never was one, so neither should count as real revenue,
 * real "items rescued", or a real completed order.
 */

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../includes/response.php';
require_once __DIR__ . '/../includes/auth.php';

require_login();
require_role(['admin']);

$pdo = Database::connect();

// ---------- Summary cards ----------
$totalRevenue = (float) $pdo->query(
    "SELECT COALESCE(SUM(quantity * unit_price), 0) FROM order_items WHERE status = 'fulfilled'"
)->fetchColumn();

$revenueThisMonth = (float) $pdo->query(
    "SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)
     FROM order_items oi JOIN orders o ON o.id = oi.order_id
     WHERE oi.status = 'fulfilled' AND o.created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')"
)->fetchColumn();

$completedOrders = (int) $pdo->query(
    "SELECT COUNT(DISTINCT order_id) FROM order_items WHERE status = 'fulfilled'"
)->fetchColumn();

$itemsRescued = (int) $pdo->query(
    "SELECT COALESCE(SUM(quantity), 0) FROM order_items WHERE status = 'fulfilled'"
)->fetchColumn();

$pendingConfirmations = (int) $pdo->query(
    "SELECT COUNT(*) FROM order_items WHERE status = 'pending'"
)->fetchColumn();

$totalUsers = (int) $pdo->query("SELECT COUNT(*) FROM users WHERE account_type IN ('personal','business')")->fetchColumn();
$newUsersThisMonth = (int) $pdo->query(
    "SELECT COUNT(*) FROM users WHERE account_type IN ('personal','business') AND created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')"
)->fetchColumn();

$personalUsers = (int) $pdo->query("SELECT COUNT(*) FROM users WHERE account_type = 'personal'")->fetchColumn();
$businessUsers = (int) $pdo->query("SELECT COUNT(*) FROM users WHERE account_type = 'business'")->fetchColumn();
$suspendedUsers = (int) $pdo->query("SELECT COUNT(*) FROM users WHERE status = 'suspended'")->fetchColumn();
$totalListings = (int) $pdo->query("SELECT COUNT(*) FROM products")->fetchColumn();

$activeListings = (int) $pdo->query("SELECT COUNT(*) FROM products WHERE status = 'active'")->fetchColumn();
$openReports = (int) $pdo->query("SELECT COUNT(*) FROM reports WHERE status = 'open'")->fetchColumn();

$avgOrderValue = $completedOrders > 0 ? round($totalRevenue / $completedOrders, 2) : 0;

// ---------- Daily trend, last 30 days (revenue + signups), gap-filled so the chart has no holes ----------
$days = [];
for ($i = 29; $i >= 0; $i--) {
    $days[date('Y-m-d', strtotime("-$i days"))] = ['revenue' => 0, 'orders' => 0, 'signups' => 0];
}

$stmt = $pdo->query("
    SELECT DATE(o.created_at) AS day, SUM(oi.quantity * oi.unit_price) AS revenue, COUNT(DISTINCT o.id) AS orders
    FROM order_items oi JOIN orders o ON o.id = oi.order_id
    WHERE oi.status = 'fulfilled' AND o.created_at >= CURDATE() - INTERVAL 29 DAY
    GROUP BY DATE(o.created_at)
");
foreach ($stmt->fetchAll() as $row) {
    if (isset($days[$row['day']])) {
        $days[$row['day']]['revenue'] = round((float) $row['revenue'], 2);
        $days[$row['day']]['orders'] = (int) $row['orders'];
    }
}

$stmt = $pdo->query("
    SELECT DATE(created_at) AS day, COUNT(*) AS count
    FROM users
    WHERE account_type IN ('personal','business') AND created_at >= CURDATE() - INTERVAL 29 DAY
    GROUP BY DATE(created_at)
");
foreach ($stmt->fetchAll() as $row) {
    if (isset($days[$row['day']])) {
        $days[$row['day']]['signups'] = (int) $row['count'];
    }
}

$dailyTrend = [];
foreach ($days as $day => $vals) {
    $dailyTrend[] = ['date' => $day] + $vals;
}

// ---------- Order status breakdown ----------
$stmt = $pdo->query("SELECT status, COUNT(*) AS count FROM order_items GROUP BY status");
$statusBreakdown = $stmt->fetchAll();

// ---------- Top categories by revenue (fulfilled sales) ----------
$stmt = $pdo->query("
    SELECT COALESCE(c.name, 'Uncategorized') AS category_name,
           SUM(oi.quantity * oi.unit_price) AS revenue, SUM(oi.quantity) AS items_sold
    FROM order_items oi
    JOIN products p ON p.id = oi.product_id
    LEFT JOIN categories c ON c.id = p.category_id
    WHERE oi.status = 'fulfilled'
    GROUP BY category_name
    ORDER BY revenue DESC
    LIMIT 8
");
$topCategories = $stmt->fetchAll();
foreach ($topCategories as &$c) {
    $c['revenue'] = round((float) $c['revenue'], 2);
    $c['items_sold'] = (int) $c['items_sold'];
}

// ---------- Top vendors by revenue (fulfilled sales) ----------
$stmt = $pdo->query("
    SELECT COALESCE(bp.business_name, pp.full_name) AS vendor_name,
           SUM(oi.quantity * oi.unit_price) AS revenue, COUNT(DISTINCT oi.order_id) AS order_count
    FROM order_items oi
    JOIN users u ON u.id = oi.seller_id
    LEFT JOIN business_profiles bp ON bp.user_id = u.id
    LEFT JOIN personal_profiles pp ON pp.user_id = u.id
    WHERE oi.status = 'fulfilled'
    GROUP BY u.id
    ORDER BY revenue DESC
    LIMIT 8
");
$topVendors = $stmt->fetchAll();
foreach ($topVendors as &$v) {
    $v['revenue'] = round((float) $v['revenue'], 2);
    $v['order_count'] = (int) $v['order_count'];
}

send_json(true, 'OK', [
    'summary' => [
        'total_revenue' => round($totalRevenue, 2),
        'revenue_this_month' => round($revenueThisMonth, 2),
        'completed_orders' => $completedOrders,
        'items_rescued' => $itemsRescued,
        'avg_order_value' => $avgOrderValue,
        'pending_confirmations' => $pendingConfirmations,
        'total_users' => $totalUsers,
        'new_users_this_month' => $newUsersThisMonth,
        'active_listings' => $activeListings,
        'open_reports' => $openReports,
        'personal_users' => $personalUsers,
        'business_users' => $businessUsers,
        'suspended_users' => $suspendedUsers,
        'total_listings' => $totalListings,
    ],
    'daily_trend' => $dailyTrend,
    'status_breakdown' => $statusBreakdown,
    'top_categories' => $topCategories,
    'top_vendors' => $topVendors,
]);
