<?php
session_start();
require '../assets/db_connect.php';

$shop_id = $_SESSION['user']['shop_id'] ?? 0;

$from = $_POST['from_date'] ?? '';
$to   = $_POST['to_date'] ?? '';

// Build WHERE clause
$where = "WHERE s.shop_id = $shop_id";

if ($from !== "" && $to !== "") {
    $where .= " AND DATE(s.sale_date) BETWEEN '$from' AND '$to'";
}

$sql = "
    SELECT s.*, c.name AS customer_name, u.username AS created_by_name
    FROM sales s
    LEFT JOIN customers c ON s.customer_id = c.id
    LEFT JOIN users u ON s.created_by = u.id
    $where
    ORDER BY s.id DESC
";

$stmt = $pdo->query($sql);

while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {

    // Fetch product names for this sale
    $pstmt = $pdo->prepare("
        SELECT p.name 
        FROM sale_lines sl
        JOIN products p ON sl.product_id = p.id
        WHERE sl.sale_id = ?
        LIMIT 5
    ");
    $pstmt->execute([$r['id']]);
    $products = $pstmt->fetchAll(PDO::FETCH_COLUMN);

    $product_list = implode(", ", $products);

    echo "
    <tr id='row-{$r['id']}'>
        <td>{$r['id']}</td>
        <td>{$r['invoice_no']}</td>
        <td>" . date('d-M-Y H:i', strtotime($r['sale_date'])) . "</td>
        <td>{$r['customer_name']}</td>
        <td>$product_list</td>
        <td>" . number_format($r['total'],2) . "</td>
        <td>" . number_format($r['paid'],2) . "</td>
        <td>{$r['status']}</td>
        <td class='no-print'>
            <a href='view_sale.php?id={$r['id']}' class='btn btn-sm btn-info'>View</a>
            <a href='edit_sale.php?id={$r['id']}' class='btn btn-sm btn-warning'>Edit</a>
            <button class='btn btn-sm btn-danger' onclick='deleteSale({$r['id']})'>Delete</button>
        </td>
    </tr>";
}
