<?php
require_once '../includes/auth.php';
requirePermission('reports', 'export');
require_once '../db.php';

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

$from   = $_GET['from'] ?? "";
$to     = $_GET['to'] ?? "";
$search = $_GET['search'] ?? "";
$batch  = (int)($_GET['batch'] ?? 0);

$where = " WHERE p.company_id = $cid ";

if (!empty($from) && !empty($to)) {
    $where .= " AND DATE(sm.created_at) BETWEEN '$from' AND '$to' ";
}

if (!empty($search)) {
    $s = $conn->real_escape_string($search);
    $where .= " AND (p.name LIKE '%$s%' OR sm.type LIKE '%$s%' OR pi.batch_no LIKE '%$s%') ";
}

/* ================================
   BATCH WISE EXPORT
================================ */
if ($batch === 1) {

    $sql = "
    SELECT 
        sm.created_at,
        p.name AS product_name,
        pi.batch_no,
        sm.type,
        sm.quantity,
        sm.reference_type,
        sm.reference_id
    FROM stock_movements sm
    JOIN products p ON p.id = sm.product_id
    LEFT JOIN purchase_items pi ON pi.id = sm.purchase_item_id
    $where
    ORDER BY sm.created_at DESC
    ";

    $res = $conn->query($sql);

    echo "<table border='1'>";
    echo "<tr>
            <th>Date</th>
            <th>Product</th>
            <th>Batch No</th>
            <th>Type</th>
            <th>Qty</th>
            <th>Reference</th>
          </tr>";

    while ($row = $res->fetch_assoc()) {
        $ref = "-";
        if (!empty($row['reference_type']) && !empty($row['reference_id'])) {
            $ref = strtoupper($row['reference_type']) . " #" . $row['reference_id'];
        }

        echo "<tr>";
        echo "<td>" . date("d-m-Y h:i A", strtotime($row['created_at'])) . "</td>";
        echo "<td>" . htmlspecialchars($row['product_name']) . "</td>";
        echo "<td>" . htmlspecialchars($row['batch_no'] ?? '-') . "</td>";
        echo "<td>" . htmlspecialchars($row['type']) . "</td>";
        echo "<td>" . htmlspecialchars($row['quantity']) . "</td>";
        echo "<td>" . htmlspecialchars($ref) . "</td>";
        echo "</tr>";
    }

    echo "</table>";
    exit;
}


/* ================================
   PRODUCT WISE SUMMARY EXPORT
================================ */
$sql = "
SELECT 
    sm.product_id,
    p.name AS product_name,
    SUM(CASE WHEN sm.type='IN' THEN sm.quantity ELSE 0 END) AS total_in,
    SUM(CASE WHEN sm.type='OUT' THEN sm.quantity ELSE 0 END) AS total_out,
    SUM(CASE WHEN sm.type='RETURN' THEN sm.quantity ELSE 0 END) AS total_return
FROM stock_movements sm
JOIN products p ON p.id = sm.product_id
LEFT JOIN purchase_items pi ON pi.id = sm.purchase_item_id
$where
GROUP BY sm.product_id
ORDER BY p.name ASC
";

$res = $conn->query($sql);

echo "<table border='1'>";
echo "<tr>
        <th>Product</th>
        <th>Total IN</th>
        <th>Total OUT</th>
        <th>Total RETURN</th>
        <th>Balance</th>
      </tr>";

while ($row = $res->fetch_assoc()) {
    $balance = ($row['total_in'] + $row['total_return']) - $row['total_out'];

    echo "<tr>";
    echo "<td>" . htmlspecialchars($row['product_name']) . "</td>";
    echo "<td>" . $row['total_in'] . "</td>";
    echo "<td>" . $row['total_out'] . "</td>";
    echo "<td>" . $row['total_return'] . "</td>";
    echo "<td>" . $balance . "</td>";
    echo "</tr>";
}

echo "</table>";
exit;
