<?php
include 'includes/config.php';

$user_ids = $_GET['user_ids'] ?? ['all'];
$brand_ids = $_GET['brand_ids'] ?? ['all'];
$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$page_size = isset($_GET['page_size']) ? (int)$_GET['page_size'] : 20;
$offset = ($page - 1) * $page_size;

$params = [];
$conditions = [];
$exclude = "c.name NOT LIKE '%Audit Sale%' AND c.name != ''";
$conditions[] = $exclude;

if(!empty($start_date) && !empty($end_date)){
    $conditions[] = "DATE(ss.date_sale) BETWEEN ? AND ?";
    $params[] = $start_date;
    $params[] = $end_date;
}

if(!in_array('all', $user_ids)){
    $placeholders = implode(',', array_fill(0, count($user_ids), '?'));
    $conditions[] = "ss.user_id IN ($placeholders)";
    $params = array_merge($params, $user_ids);
}

if(!in_array('all', $brand_ids)){
    $placeholders = implode(',', array_fill(0, count($brand_ids), '?'));
    $conditions[] = "p.brand_id IN ($placeholders)";
    $params = array_merge($params, $brand_ids);
}

$whereClause = "WHERE " . implode(' AND ', $conditions);

$summaryQuery = "SELECT 
        SUM(ss.sale_qty) AS summary_quantity, 
        SUM(ss.sale_qty * ss.price) AS summary_price 
    FROM stock_sale ss
    LEFT JOIN contacts c ON ss.customer_id = c.id
    LEFT JOIN products p ON ss.product_id = p.id
    LEFT JOIN brands b ON p.brand_id = b.id
    $whereClause";

$stmtSummary = $conn->prepare($summaryQuery);
$stmtSummary->execute($params);
$summary = $stmtSummary->fetch(PDO::FETCH_ASSOC);

$query = "SELECT 
    b.name AS brand_name,
    p.name AS product_name,
    SUM(ss.sale_qty) AS total_qty,
    AVG(ss.price) AS unit_price,
    SUM(ss.sale_qty * ss.price) AS total_amount
FROM stock_sale ss
LEFT JOIN contacts c ON ss.customer_id = c.id
LEFT JOIN products p ON ss.product_id = p.id
LEFT JOIN brands b ON p.brand_id = b.id
$whereClause
GROUP BY b.id, p.id
ORDER BY b.name, p.name
LIMIT $offset, $page_size";

try {
    $stmt = $conn->prepare($query);
    $stmt->execute($params);
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $countQuery = "SELECT COUNT(*) FROM (
        SELECT 1 FROM stock_sale ss
        LEFT JOIN products p ON ss.product_id = p.id
        LEFT JOIN brands b ON p.brand_id = b.id
        LEFT JOIN contacts c ON ss.customer_id = c.id
        $whereClause
        GROUP BY b.id, p.id
    ) AS t";

    $stmtCount = $conn->prepare($countQuery);
    $stmtCount->execute($params);
    $total = $stmtCount->fetchColumn();

    echo json_encode([
        'data' => $data,
        'total' => $total,
        'summary_quantity' => (float)$summary['summary_quantity'],
        'summary_price' => (float)$summary['summary_price']
    ]);

} catch(PDOException $e){
    echo json_encode(['error' => $e->getMessage()]);
}
?>
