<?php
/**
 * GET /api/admin/sales_report.php
 * Admin-only. Query params (all optional):
 *   date_from, date_to   (YYYY-MM-DD)
 *   product_name         partial match
 *   vendor_id             seller's user id
 *   category_id
 *   export=csv            stream a CSV download instead of JSON
 *
 * Returns transaction-level rows (one per order line), not pre-aggregated,
 * so the admin can slice by day, item, or vendor on the client side, or
 * export the exact filtered set.
 */

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();

$where = [];
$params = [];

if (!empty($_GET['date_from'])) {
    $where[] = 'DATE(o.created_at) >= ?';
    $params[] = $_GET['date_from'];
}
if (!empty($_GET['date_to'])) {
    $where[] = 'DATE(o.created_at) <= ?';
    $params[] = $_GET['date_to'];
}
if (!empty($_GET['product_name'])) {
    $where[] = 'p.name LIKE ?';
    $params[] = '%' . $_GET['product_name'] . '%';
}
if (!empty($_GET['vendor_id'])) {
    $where[] = 'oi.seller_id = ?';
    $params[] = (int) $_GET['vendor_id'];
}
if (!empty($_GET['category_id'])) {
    $where[] = 'p.category_id = ?';
    $params[] = (int) $_GET['category_id'];
}
// Default view is completed sales only (revenue that actually happened) -
// pass status=all to see pending/cancelled items too.
$statusFilter = $_GET['status'] ?? 'fulfilled';
if (in_array($statusFilter, ['pending', 'fulfilled', 'cancelled'], true)) {
    $where[] = 'oi.status = ?';
    $params[] = $statusFilter;
}

$whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

$sql = "
    SELECT o.id AS order_id, o.created_at,
           p.name AS product_name, c.name AS category_name,
           oi.quantity, oi.unit_price, (oi.quantity * oi.unit_price) AS line_total, oi.status,
           seller.email AS vendor_email,
           COALESCE(sbp.business_name, spp.full_name) AS vendor_name,
           buyer.email AS buyer_email
    FROM order_items oi
    JOIN orders o ON o.id = oi.order_id
    JOIN products p ON p.id = oi.product_id
    LEFT JOIN categories c ON c.id = p.category_id
    JOIN users seller ON seller.id = oi.seller_id
    LEFT JOIN business_profiles sbp ON sbp.user_id = seller.id
    LEFT JOIN personal_profiles spp ON spp.user_id = seller.id
    JOIN users buyer ON buyer.id = o.buyer_id
    $whereSql
    ORDER BY o.created_at DESC
    LIMIT 2000
";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll();

if (($_GET['export'] ?? '') === 'csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="ragreen-sales-report.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Order ID', 'Date', 'Product', 'Category', 'Qty', 'Unit Price', 'Line Total', 'Status', 'Vendor', 'Vendor Email', 'Buyer Email']);
    foreach ($rows as $r) {
        fputcsv($out, [
            $r['order_id'], $r['created_at'], $r['product_name'], $r['category_name'],
            $r['quantity'], $r['unit_price'], $r['line_total'], $r['status'], $r['vendor_name'], $r['vendor_email'], $r['buyer_email'],
        ]);
    }
    fclose($out);
    exit;
}

$totalRevenue = array_sum(array_column($rows, 'line_total'));
$totalItems = array_sum(array_column($rows, 'quantity'));

send_json(true, 'OK', [
    'rows' => $rows,
    'summary' => [
        'total_revenue' => round($totalRevenue, 2),
        'total_items_sold' => (int) $totalItems,
        'transaction_count' => count($rows),
    ],
]);
