<?php
require_once '../includes/auth.php';
require_once '../db.php';

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=sales_report.xls");

$from = $_GET['from'] ?? date('Y-m-01');
$to   = $_GET['to'] ?? date('Y-m-d');
$status = $_GET['status'] ?? 'final';
$search = trim($_GET['search'] ?? '');

$where = " WHERE si.invoice_date BETWEEN ? AND ? AND si.company_id = ? ";
$params = [$from, $to, $cid];
$types  = "ssi";

if ($status !== 'all') {
    $where .= " AND si.status = ? ";
    $params[] = $status;
    $types .= "s";
}

if (!empty($search)) {
    $where .= " AND (si.invoice_no LIKE ? OR c.name LIKE ? OR c.phone LIKE ?) ";
    $like = "%$search%";
    $params[] = $like;
    $params[] = $like;
    $params[] = $like;
    $types .= "sss";
}

$sql = "
    SELECT 
        si.invoice_date,
        si.invoice_no,
        c.name AS customer_name,
        c.phone AS customer_phone,
        si.gross_total,
        si.total_taxable,
        si.total_gst,
        si.net_total,
        si.received_amount,
        si.balance_amount,
        si.status
    FROM sales_invoices si
    JOIN clients c ON c.id = si.client_id
    $where
    ORDER BY si.invoice_date DESC, si.id DESC
";

$stmt = $conn->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();

echo "<table border='1'>";
echo "<tr>
        <th>Date</th>
        <th>Invoice No</th>
        <th>Customer</th>
        <th>Phone</th>
        <th>Gross</th>
        <th>Taxable</th>
        <th>GST</th>
        <th>Net Total</th>
        <th>Received</th>
        <th>Balance</th>
        <th>Status</th>
      </tr>";

while ($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . htmlspecialchars($row['invoice_date']) . "</td>";
    echo "<td>" . htmlspecialchars($row['invoice_no']) . "</td>";
    echo "<td>" . htmlspecialchars($row['customer_name']) . "</td>";
    echo "<td>" . htmlspecialchars($row['customer_phone']) . "</td>";
    echo "<td>" . number_format($row['gross_total'], 2) . "</td>";
    echo "<td>" . number_format($row['total_taxable'], 2) . "</td>";
    echo "<td>" . number_format($row['total_gst'], 2) . "</td>";
    echo "<td>" . number_format($row['net_total'], 2) . "</td>";
    echo "<td>" . number_format($row['received_amount'], 2) . "</td>";
    echo "<td>" . number_format($row['balance_amount'], 2) . "</td>";
    echo "<td>" . strtoupper($row['status']) . "</td>";
    echo "</tr>";
}

echo "</table>";
exit;
