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

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

$draw   = (int)($_POST['draw'] ?? 1);
$start  = (int)($_POST['start'] ?? 0);
$length = (int)($_POST['length'] ?? 25);

if ($vendor_id <= 0) {
    echo json_encode([
        "draw" => $draw,
        "recordsTotal" => 0,
        "recordsFiltered" => 0,
        "data" => []
    ]);
    exit;
}

$vendor = $conn->query("SELECT * FROM vendors WHERE id=$vendor_id")->fetch_assoc();
$opening = (float)($vendor['opening_balance'] ?? 0);

$ledgerRows = [];

// PURCHASE FINAL
$poQuery = $conn->query("
    SELECT id, 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'],
        "link_type" => "purchase",
        "link_id"   => (int)$po['id'],
        "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 id, 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'],
        "link_type" => "pur_return",
        "link_id"   => (int)$pr['id'],
        "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']);
});

// Running balance
$balance = $opening;

// Opening row always first
$finalRows = [];
$finalRows[] = [
    "date" => $from_date,
    "type" => "<b>Opening Balance</b>",
    "ref"  => "-",
    "debit" => ($balance > 0 ? number_format($balance,2) : "0.00"),
    "credit" => ($balance < 0 ? number_format(abs($balance),2) : "0.00"),
    "balance" => "<b>".number_format($balance,2)."</b>",
    "note" => ""
];

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

    $finalRows[] = [
        "date" => date("d-m-Y", strtotime($row['date'])),
        "type" => htmlspecialchars($row['type']),
        "ref"  => !empty($row['link_id'])
                    ? '<a href="#" onclick="openDocPopup(\''.htmlspecialchars($row['link_type']).'\','.(int)$row['link_id'].',\''.htmlspecialchars($row['ref'],ENT_QUOTES).'\');return false;" style="color:#2563eb;text-decoration:none;">'.htmlspecialchars($row['ref']).'</a>'
                    : htmlspecialchars($row['ref']),
        "debit" => number_format($row['debit'],2),
        "credit" => number_format($row['credit'],2),
        "balance" => "<b>".number_format($balance,2)."</b>",
        "note" => htmlspecialchars($row['note'])
    ];
}

$totalRecords = count($finalRows);

// Pagination
$pagedRows = array_slice($finalRows, $start, $length);

$data = [];
foreach ($pagedRows as $r) {
    $data[] = [
        $r['date'],
        $r['type'],
        $r['ref'],
        $r['debit'],
        $r['credit'],
        $r['balance'],
        $r['note']
    ];
}

echo json_encode([
    "draw" => $draw,
    "recordsTotal" => $totalRecords,
    "recordsFiltered" => $totalRecords,
    "data" => $data
]);
exit;