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

$vendor_id = (int)($_GET['vendor_id'] ?? 0);
$from_date = $_GET['from_date'] ?? date('Y-m-01');
$to_date   = $_GET['to_date'] ?? date('Y-m-d');

if ($vendor_id <= 0) {
    die("Invalid Vendor");
}

$vendor = $conn->query("SELECT * FROM vendors WHERE id=$vendor_id")->fetch_assoc();
if (!$vendor) {
    die("Vendor not found");
}

$opening = (float)($vendor['opening_balance'] ?? 0);

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=vendor_ledger_{$vendor_id}.xls");

echo "<table border='1'>";
echo "<tr><th colspan='7' style='font-size:16px;'>Vendor Ledger Report</th></tr>";
echo "<tr><td colspan='7'><b>Vendor:</b> ".htmlspecialchars($vendor['name'])."</td></tr>";
echo "<tr><td colspan='7'><b>Period:</b> $from_date to $to_date</td></tr>";

echo "<tr style='background:#f2f2f2; font-weight:bold;'>
        <td>Date</td>
        <td>Particular</td>
        <td>Reference</td>
        <td>Debit</td>
        <td>Credit</td>
        <td>Balance</td>
        <td>Note</td>
      </tr>";

$balance = $opening;

echo "<tr>
        <td>".date("d-m-Y", strtotime($from_date))."</td>
        <td><b>Opening Balance</b></td>
        <td>-</td>
        <td>".($balance > 0 ? number_format($balance,2) : "0.00")."</td>
        <td>".($balance < 0 ? number_format(abs($balance),2) : "0.00")."</td>
        <td><b>".number_format($balance,2)."</b></td>
        <td></td>
      </tr>";

$ledgerRows = [];

// PURCHASE FINAL
$poQuery = $conn->query("
    SELECT po_no, purchase_date, net_amount, supplier_invoice_no
    FROM purchase_orders
    WHERE vendor_id = $vendor_id
      AND status = 'final'
      AND company_id = $cid
      AND purchase_date BETWEEN '$from_date' AND '$to_date'
    ORDER BY purchase_date ASC, id ASC
");

while ($po = $poQuery->fetch_assoc()) {
    $ledgerRows[] = [
        "date" => $po['purchase_date'],
        "type" => "Purchase Order",
        "ref"  => $po['po_no'],
        "debit" => (float)$po['net_amount'],
        "credit" => 0,
        "note" => $po['supplier_invoice_no'] ? "Supplier Inv: ".$po['supplier_invoice_no'] : ""
    ];
}

// PURCHASE RETURN FINAL
$prQuery = $conn->query("
    SELECT return_no, return_date, net_amount, reason
    FROM purchase_returns
    WHERE vendor_id = $vendor_id
      AND status = 'final'
      AND company_id = $cid
      AND return_date BETWEEN '$from_date' AND '$to_date'
    ORDER BY return_date ASC, id ASC
");

while ($pr = $prQuery->fetch_assoc()) {
    $ledgerRows[] = [
        "date" => $pr['return_date'],
        "type" => "Purchase Return",
        "ref"  => $pr['return_no'],
        "debit" => 0,
        "credit" => (float)$pr['net_amount'],
        "note" => $pr['reason'] ?? ''
    ];
}

// Sort by date
usort($ledgerRows, function($a, $b){
    return strtotime($a['date']) <=> strtotime($b['date']);
});

// Print rows
foreach ($ledgerRows as $row) {
    $balance += $row['debit'];
    $balance -= $row['credit'];

    echo "<tr>
            <td>".date("d-m-Y", strtotime($row['date']))."</td>
            <td>".htmlspecialchars($row['type'])."</td>
            <td>".htmlspecialchars($row['ref'])."</td>
            <td>".number_format($row['debit'],2)."</td>
            <td>".number_format($row['credit'],2)."</td>
            <td><b>".number_format($balance,2)."</b></td>
            <td>".htmlspecialchars($row['note'])."</td>
          </tr>";
}

echo "</table>";
exit;
