<?php  
include 'includes/config.php';
header('Content-Type: application/json');

$page = $_GET['page'] ?? 1;
$records_per_page = 100;
$offset = ($page - 1) * $records_per_page;

$search = $_GET['search'] ?? '';
$start = $_GET['start_date'] ?? '';
$end = $_GET['end_date'] ?? '';
$user_id = $_GET['user_id'] ?? 'all';

// Base SQL
$sql = "SELECT ss.*, p.name AS product_name, c.name AS customer_name, u.user_name
        FROM stock_sale ss
        JOIN products p ON ss.product_id = p.id
        LEFT JOIN contacts c ON ss.customer_id = c.id
        LEFT JOIN users u ON ss.user_id = u.id
        WHERE ss.invoice NOT LIKE '%Audit%' 
          AND ss.invoice NOT LIKE '%Open Stock%' 
          AND (c.name IS NULL OR c.name NOT LIKE '%Audit Sale%')";

// Filters
if (!empty($start) && !empty($end)) $sql .= " AND DATE(ss.date_sale) BETWEEN :start AND :end";
if (!empty($search)) $sql .= " AND p.name LIKE :search";
if ($user_id !== 'all') $sql .= " AND ss.user_id = :user_id";

// Pagination
$sql .= " ORDER BY ss.date_sale DESC LIMIT :limit OFFSET :offset";
$stmt = $conn->prepare($sql);
if (!empty($start) && !empty($end)) { $stmt->bindValue(':start', $start); $stmt->bindValue(':end', $end); }
if (!empty($search)) $stmt->bindValue(':search', "%$search%");
if ($user_id !== 'all') $stmt->bindValue(':user_id', $user_id);
$stmt->bindValue(':limit', $records_per_page, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Top Selling Items
$trend = "SELECT p.name AS product_name, SUM(ss.sale_qty) AS total_qty
          FROM stock_sale ss
          JOIN products p ON ss.product_id = p.id
          LEFT JOIN contacts c ON ss.customer_id = c.id
          WHERE ss.invoice NOT LIKE '%Audit%' 
            AND ss.invoice NOT LIKE '%Open Stock%'
            AND (c.name IS NULL OR c.name NOT LIKE '%Audit Sale%')";
if (!empty($start) && !empty($end)) $trend .= " AND DATE(ss.date_sale) BETWEEN :start AND :end";
if ($user_id !== 'all') $trend .= " AND ss.user_id = :user_id";
$trend .= " GROUP BY ss.product_id ORDER BY total_qty DESC LIMIT 5";
$tstmt = $conn->prepare($trend);
if (!empty($start) && !empty($end)) { $tstmt->bindValue(':start', $start); $tstmt->bindValue(':end', $end); }
if ($user_id !== 'all') $tstmt->bindValue(':user_id', $user_id);
$tstmt->execute();
$top_items = $tstmt->fetchAll(PDO::FETCH_ASSOC);

// Count total pages
$count_sql = "SELECT COUNT(*) FROM stock_sale ss
              LEFT JOIN contacts c ON ss.customer_id = c.id
              WHERE ss.invoice NOT LIKE '%Audit%' 
                AND ss.invoice NOT LIKE '%Open Stock%' 
                AND (c.name IS NULL OR c.name NOT LIKE '%Audit Sale%')";
if (!empty($start) && !empty($end)) $count_sql .= " AND DATE(ss.date_sale) BETWEEN :start AND :end";
if ($user_id !== 'all') $count_sql .= " AND ss.user_id = :user_id";
if (!empty($search)) $count_sql .= " AND ss.product_id IN (SELECT id FROM products WHERE name LIKE :search)";
$count_stmt = $conn->prepare($count_sql);
if (!empty($start) && !empty($end)) { $count_stmt->bindValue(':start', $start); $count_stmt->bindValue(':end', $end); }
if ($user_id !== 'all') $count_stmt->bindValue(':user_id', $user_id);
if (!empty($search)) $count_stmt->bindValue(':search', "%$search%");
$count_stmt->execute();
$total_pages = ceil($count_stmt->fetchColumn() / $records_per_page);

// Overall totals with discount applied
$total_sql = "SELECT 
                SUM(ss.sale_qty) AS total_qty, 
                SUM(ss.sale_qty * ss.price - IFNULL(ss.discount,0)) AS total_amount
              FROM stock_sale ss
              LEFT JOIN products p ON ss.product_id = p.id
              LEFT JOIN contacts c ON ss.customer_id = c.id
              WHERE ss.invoice NOT LIKE '%Audit%' 
                AND ss.invoice NOT LIKE '%Open Stock%' 
                AND (c.name IS NULL OR c.name NOT LIKE '%Audit Sale%')";
if (!empty($start) && !empty($end)) $total_sql .= " AND DATE(ss.date_sale) BETWEEN :start AND :end";
if ($user_id !== 'all') $total_sql .= " AND ss.user_id = :user_id";
if (!empty($search)) $total_sql .= " AND p.name LIKE :search";
$total_stmt = $conn->prepare($total_sql);
if (!empty($start) && !empty($end)) { $total_stmt->bindValue(':start', $start); $total_stmt->bindValue(':end', $end); }
if ($user_id !== 'all') $total_stmt->bindValue(':user_id', $user_id);
if (!empty($search)) $total_stmt->bindValue(':search', "%$search%");
$total_stmt->execute();
$overall_totals = $total_stmt->fetch(PDO::FETCH_ASSOC);

echo json_encode([
  'data' => $data,
  'top_items' => $top_items,
  'total_pages' => $total_pages,
  'overall_totals' => $overall_totals
]);
?>
