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

$from = $_GET['from'] ?? date('Y-m-01');
$to   = $_GET['to'] ?? date('Y-m-d');
$vendor_id = $_GET['vendor_id'] ?? '';
$status    = $_GET['status']    ?? 'final';
$itemWise = isset($_GET['itemwise']) ? 1 : 0;
$search = trim($_GET['search'] ?? '');

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

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

if (!empty($vendor_id)) {
    $where .= " AND po.vendor_id = ? ";
    $params[] = $vendor_id;
    $types .= "i";
}

if (!empty($search)) {
    $where .= " AND (po.po_no LIKE ? OR v.name LIKE ? OR po.supplier_invoice_no LIKE ?) ";
    $like = "%$search%";
    $params[] = $like;
    $params[] = $like;
    $params[] = $like;
    $types .= "sss";
}

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

echo "<table border='1'>";

if ($itemWise) {

    echo "<tr style='background:#ddd;font-weight:bold;'>
            <td>PO No</td>
            <td>Supplier Invoice</td>
            <td>Date</td>
            <td>Vendor</td>
            <td>Status</td>
            <td>Product</td>
            <td>Brand</td>
            <td>HSN</td>
            <td>Unit</td>
            <td>Batch</td>
            <td>MRP</td>
            <td>Purchase Price</td>
            <td>Selling Price</td>
            <td>Total Qty</td>
            <td>Available Qty</td>
            <td>GST %</td>
            <td>Expiry</td>
          </tr>";

    $sql = "
        SELECT 
            po.po_no,
            po.supplier_invoice_no,
            po.purchase_date,
            po.status,
            v.name AS vendor_name,
            pi.batch_no,
            pi.mrp,
            pi.purchase_price,
            pi.selling_price,
            pi.quantity,
            pi.available_qty,
            pi.gst_percent,
            pi.expiry_date,
            p.name AS product_name,
            p.brand,
            p.hsn_code,
            p.unit
        FROM purchase_orders po
        JOIN vendors v ON v.id = po.vendor_id
        JOIN purchase_items pi ON pi.purchase_order_id = po.id
        JOIN products p ON p.id = pi.product_id
        $where
        ORDER BY po.id DESC
    ";

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

    while ($row = $res->fetch_assoc()) {
        echo "<tr>
                <td>{$row['po_no']}</td>
                <td>".($row['supplier_invoice_no'] ?? '-')."</td>
                <td>{$row['purchase_date']}</td>
                <td>{$row['vendor_name']}</td>
                <td>{$row['status']}</td>
                <td>{$row['product_name']}</td>
                <td>{$row['brand']}</td>
                <td>{$row['hsn_code']}</td>
                <td>{$row['unit']}</td>
                <td>{$row['batch_no']}</td>
                <td>{$row['mrp']}</td>
                <td>{$row['purchase_price']}</td>
                <td>{$row['selling_price']}</td>
                <td>{$row['quantity']}</td>
                <td>{$row['available_qty']}</td>
                <td>{$row['gst_percent']}</td>
                <td>{$row['expiry_date']}</td>
              </tr>";
    }

} else {

    echo "<tr style='background:#ddd;font-weight:bold;'>
            <td>PO No</td>
            <td>Supplier Invoice</td>
            <td>Date</td>
            <td>Vendor</td>
            <td>Status</td>
            <td>Total</td>
            <td>GST</td>
            <td>Net</td>
          </tr>";

    $sql = "
        SELECT po.*, v.name AS vendor_name
        FROM purchase_orders po
        JOIN vendors v ON v.id = po.vendor_id
        $where
        ORDER BY po.id DESC
    ";

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

    while ($row = $res->fetch_assoc()) {
        echo "<tr>
                <td>{$row['po_no']}</td>
                <td>".($row['supplier_invoice_no'] ?? '-')."</td>
                <td>{$row['purchase_date']}</td>
                <td>{$row['vendor_name']}</td>
                <td>{$row['status']}</td>
                <td>{$row['total_amount']}</td>
                <td>{$row['gst_amount']}</td>
                <td>{$row['net_amount']}</td>
              </tr>";
    }
}

echo "</table>";
exit;