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

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

if ($client_id <= 0) {
    die("Invalid Client");
}

$client = $conn->query("SELECT * FROM clients WHERE id=$client_id")->fetch_assoc();
if (!$client) die("Customer not found");

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

$ledgerRows = [];

// FINAL invoices only
$invQuery = $conn->query("
    SELECT invoice_no, invoice_date, net_total, received_amount, payment_mode
    FROM sales_invoices
    WHERE client_id = $client_id
      AND status = 'final'
      AND company_id = $cid
      AND invoice_date BETWEEN '$from_date' AND '$to_date'
    ORDER BY invoice_date ASC, id ASC
");

while ($inv = $invQuery->fetch_assoc()) {

    $ledgerRows[] = [
        'date' => $inv['invoice_date'],
        'type' => 'Sales Invoice',
        'ref'  => $inv['invoice_no'],
        'debit' => (float)$inv['net_total'],
        'credit' => 0,
        'note' => ''
    ];

    if ((float)$inv['received_amount'] > 0) {
        $ledgerRows[] = [
            'date' => $inv['invoice_date'],
            'type' => 'Payment Received',
            'ref'  => $inv['invoice_no'],
            'debit' => 0,
            'credit' => (float)$inv['received_amount'],
            'note' => $inv['payment_mode'] ?? ''
        ];
    }
}

// FINAL sales returns only
$retQuery = $conn->query("
    SELECT return_no, return_date, total_amount
    FROM sales_returns
    WHERE client_id = $client_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 ($ret = $retQuery->fetch_assoc()) {
    $ledgerRows[] = [
        'date' => $ret['return_date'],
        'type' => 'Sales Return',
        'ref'  => $ret['return_no'],
        'debit' => 0,
        'credit' => (float)$ret['total_amount'],
        'note' => ''
    ];
}

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

// Excel Output
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=Customer_Ledger_{$client_id}.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo "<table border='1'>";
echo "<tr>
        <th colspan='7' style='font-size:16px;'>Customer Ledger Report</th>
      </tr>";

echo "<tr>
        <td colspan='7'>
            <b>Customer:</b> ".htmlspecialchars($client['name'])." |
            <b>Phone:</b> ".htmlspecialchars($client['phone'])." |
            <b>From:</b> $from_date |
            <b>To:</b> $to_date
        </td>
      </tr>";

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

$balance = $opening;

// Opening row
echo "<tr style='background:#fffbe6;'>
        <td>$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>";

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 "<tr style='background:#eef7ff; font-weight:bold;'>
        <td colspan='5' align='right'>Closing Balance</td>
        <td>".number_format($balance,2)."</td>
        <td></td>
      </tr>";

echo "</table>";
exit;
