<?php
ob_start();
require("includes/check_auth_user.php");
require("includes/dbconnect.php");
require("includes/config.php");
require("includes/function.php");

$formname = 'Customer Ageing Report';

$zdb = $_SESSION["zdbyear_" . $dbname];
mysqli_select_db($conn, $zdb);

// ── Defensive: ensure billsettle exists (bill-wise receipt/discount/return
//    allocations — see bankrecp.php, cashrecp.php, discountgiven.php,
//    salereturn.php, osbill_fr.php). Never had a CREATE TABLE anywhere in
//    this codebase; this is a no-op if it already exists. ──────────────────
mysqli_query($conn, "CREATE TABLE IF NOT EXISTS `billsettle` (
    `srl` INT(11) NOT NULL AUTO_INCREMENT,
    `vouchno` INT(11) NOT NULL DEFAULT 0,
    `source` VARCHAR(10) NOT NULL DEFAULT '',
    `vouchdt` DATE NULL,
    `saleinvno` INT(11) NOT NULL DEFAULT 0,
    `settleamt` DOUBLE NOT NULL DEFAULT 0,
    PRIMARY KEY (`srl`),
    KEY `vouchno` (`vouchno`),
    KEY `saleinvno` (`saleinvno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

// ── Inputs from filter form ──────────────────────────────────────────────
$facct = isset($_POST['acname']) ? (int)$_POST['acname'] : 0;

function d2sql($d) {
    if ($d != '' && preg_match('/^(\d{2})-(\d{2})-(\d{4})$/', $d, $m)) {
        return $m[3] . '-' . $m[2] . '-' . $m[1];
    }
    return '';
}
$fasondate    = isset($_POST['asondate']) ? trim($_POST['asondate']) : '';
$fasondateSql = d2sql($fasondate);

if ($fasondateSql == '') {
    browse("As On Date Cannot Be Empty");
    header("refresh:1; url=custaging.php");
    exit;
}

function pf($v) {
    $v = (float)$v;
    return $v == 0 ? '' : number_format($v, 2);
}

// Bucket keys, oldest → newest (matches the column order requested) ────────
$bucketDefs = [
    'b180'  => '> 180 Days',
    'b151'  => '151-180 Days',
    'b121'  => '121-150 Days',
    'b91'   => '91-120 Days',
    'b61'   => '61-90 Days',
    'b31'   => '31-60 Days',
    'b0'    => '< 31 Days',
];
function ageBucket($days) {
    if ($days > 180) return 'b180';
    if ($days > 150) return 'b151';
    if ($days > 120) return 'b121';
    if ($days > 90)  return 'b91';
    if ($days > 60)  return 'b61';
    if ($days > 30)  return 'b31';
    return 'b0';
}

// ── Seed one row per customer (grpcode='010') so an opening-only balance
//    (no bills at all) still shows up ───────────────────────────────────────
$custGroups = [];
$acctcond = ($facct > 0) ? " AND `accode`='$facct' " : '';
$cRes = mysqli_query($conn, "SELECT `accode`,`name`,`openbal`,`dc` FROM `accmast` WHERE `grpcode`='010' $acctcond ORDER BY `name`") or die(mysqli_error($conn));
while ($c = mysqli_fetch_assoc($cRes)) {
    $accode  = (int)$c['accode'];
    $opening = (float)$c['openbal'];
    if (strtoupper($c['dc']) == 'C') $opening = -$opening; // credit balance nets against receivable
    elseif (strtoupper($c['dc']) != 'D') $opening = 0;
    $row = ['name' => $c['name'], 'opening' => $opening, 'total' => $opening];
    foreach ($bucketDefs as $bk => $lbl) $row[$bk] = 0;
    $custGroups[$accode] = $row;
}

// ── Bill-wise outstanding, as on the cutoff date — same computation osbill_fr.php
//    (Receivable) uses, so the two reports always agree on totals ──────────────
$sql = "SELECT s.`vouchno`, s.`vouchdt`, s.`customer`, s.`totamt`,
               COALESCE((SELECT SUM(bs.`settleamt`) FROM `billsettle` bs
                         WHERE bs.`saleinvno`=s.`vouchno` AND bs.`vouchdt` <= '$fasondateSql'), 0) AS settled
        FROM `sale` s
        WHERE s.`vouchdt` <= '$fasondateSql' " . ($facct > 0 ? " AND s.`customer`='$facct' " : '') . "
        ORDER BY s.`customer`, s.`vouchdt`";
$res = mysqli_query($conn, $sql) or die(mysqli_error($conn));
while ($r = mysqli_fetch_assoc($res)) {
    $outstanding = (float)$r['totamt'] - (float)$r['settled'];
    if ($outstanding <= 0.005) continue;
    if ($r['vouchdt'] == '0000-00-00' || !$r['vouchdt']) continue;

    $custKey = (int)$r['customer'];
    if (!isset($custGroups[$custKey])) continue; // not a Receivable-group customer (or filtered out)

    $ageDays = (int)((strtotime($fasondateSql) - strtotime($r['vouchdt'])) / 86400);
    $bk = ageBucket($ageDays);

    $custGroups[$custKey][$bk]    += $outstanding;
    $custGroups[$custKey]['total'] += $outstanding;
}

// Drop customers with no balance at all (zero opening + zero every bucket)
foreach ($custGroups as $k => $g) {
    if (abs($g['total']) < 0.005) unset($custGroups[$k]);
}

$grand = ['opening' => 0, 'total' => 0];
foreach ($bucketDefs as $bk => $lbl) $grand[$bk] = 0;
foreach ($custGroups as $g) {
    $grand['opening'] += $g['opening'];
    $grand['total']   += $g['total'];
    foreach ($bucketDefs as $bk => $lbl) $grand[$bk] += $g[$bk];
}

$mtdt = date('d-m-Y', strtotime($fasondateSql));

// ── CSV Export ───────────────────────────────────────────────────────────
if (isset($_POST['excel'])) {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="Customer_Ageing_' . date('d-m-Y', strtotime($fasondateSql)) . '.csv"');
    $out = fopen('php://output', 'w');
    $hdr = ['Sr', 'Party', 'Opening'];
    foreach ($bucketDefs as $lbl) $hdr[] = $lbl;
    $hdr[] = 'Total Balance';
    fputcsv($out, $hdr);
    $sr = 1;
    foreach ($custGroups as $g) {
        $row = [$sr++, $g['name'], number_format($g['opening'],2,'.','')];
        foreach ($bucketDefs as $bk => $lbl) $row[] = number_format($g[$bk],2,'.','');
        $row[] = number_format($g['total'],2,'.','');
        fputcsv($out, $row);
    }
    $trow = ['', 'Grand Total', number_format($grand['opening'],2,'.','')];
    foreach ($bucketDefs as $bk => $lbl) $trow[] = number_format($grand[$bk],2,'.','');
    $trow[] = number_format($grand['total'],2,'.','');
    fputcsv($out, $trow);
    fclose($out);
    exit;
}

include("includes/linkshead.php");
?>
<style>
    body { background:#f0f2f5; margin:0; padding:10px 14px; }

    .sim-card {
        background:#fff; border-radius:10px;
        box-shadow:0 1px 3px rgba(0,0,0,.08), 0 4px 16px rgba(0,0,0,.06);
        overflow:hidden;
    }
    .sim-card-header {
        background:#fff; border-bottom:2px solid #f1f5f9; padding:12px 20px;
        display:flex; align-items:center; justify-content:space-between;
    }
    .sim-card-header h2 { font-size:15px; font-weight:700; color:#1e293b; margin:0; }
    .sim-card-header .sub { font-size:11.5px; color:#94a3b8; margin-top:2px; }

    .sim-body { padding:18px 20px; }

    .btn { padding:8px 16px; border:none; border-radius:6px; font-size:12.5px; font-weight:600; cursor:pointer; display:inline-flex; align-items:center; gap:6px; text-decoration:none; }
    .btn-export { background:#16a34a; color:#fff; }
    .btn-export:hover { background:#15803d; }
    .btn-print { background:#4a5759; color:#fff; }
    .btn-print:hover { background:#374043; }

    table.reg { width:100%; border-collapse:collapse; font-size:12px; }
    table.reg th {
        background:#f8fafc; color:#374151; font-weight:700; font-size:11px;
        text-transform:uppercase; letter-spacing:.04em; padding:8px 10px;
        border-bottom:2px solid #e2e8f0; text-align:left; white-space:nowrap;
    }
    table.reg th.num { text-align:right; }
    table.reg td { padding:6px 10px; border-bottom:1px solid #f1f5f9; white-space:nowrap; }
    table.reg td.num { text-align:right; }
    table.reg tbody tr.mainrow { cursor:pointer; }
    table.reg tbody tr.mainrow:hover { background:#eff6ff; }
    table.reg tfoot td { font-weight:700; background:#f8fafc; border-top:2px solid #e2e8f0; }
    table.reg td.oldbucket { color:#dc2626; font-weight:600; }
    table.reg td.totcell { font-weight:700; color:#1e293b; }

    .reg-wrap { overflow:auto; }
    .empty-state { text-align:center; padding:60px 20px; color:#94a3b8; }
    .empty-state i { font-size:32px; margin-bottom:12px; display:block; }

    @media print {
        body { background:#fff; padding:0; }
        .no-print { display:none !important; }
        .sim-card { box-shadow:none; border-radius:0; }
        table.reg tbody tr.mainrow:hover { background:none; }
    }
</style>
</head>
<body>
<div class="sim-card">
    <div class="sim-card-header">
        <div>
            <h2><i class="fa fa-hourglass-half" style="color:#3b82f6;margin-right:8px;font-size:14px;"></i><?php echo $formname; ?></h2>
            <div class="sub">As on <?php echo $mtdt; ?></div>
        </div>
        <div class="no-print" style="display:flex;gap:8px;">
            <button type="button" class="btn btn-print" onclick="window.print()"><i class="fa fa-print"></i> Print</button>
            <button onclick="if(window.parent&&window.parent!==window){window.parent.document.getElementById('rptframe').src='about:blank';}else{window.close();}" style="padding:8px 22px;background:#f1f5f9;color:#374151;border:none;border-radius:8px;font-size:13px;font-weight:600;cursor:pointer;margin-left:8px;"><i class="fa fa-arrow-left"></i> Go Back</button>
            <form method="POST" style="margin:0;">
                <?php foreach ($_POST as $k => $v) { if ($k != 'view') echo '<input type="hidden" name="' . htmlspecialchars($k) . '" value="' . htmlspecialchars($v) . '">'; } ?>
                <button type="submit" name="excel" value="1" class="btn btn-export"><i class="fa fa-file-csv"></i> Export CSV</button>
            </form>
        </div>
    </div>

    <div class="sim-body">

        <?php if (count($custGroups) == 0): ?>
        <div class="empty-state">
            <i class="fa fa-check-circle" style="color:#16a34a;"></i>
            No outstanding balances for the selected filters.
        </div>
        <?php else: ?>
        <div class="reg-wrap">
        <table class="reg">
            <thead>
                <tr>
                    <th>Sr.</th>
                    <th>Party</th>
                    <th class="num">Opening</th>
                    <?php foreach ($bucketDefs as $lbl): ?>
                    <th class="num"><?php echo $lbl; ?></th>
                    <?php endforeach; ?>
                    <th class="num">Total Balance</th>
                </tr>
            </thead>
            <tbody>
                <?php $sr = 1; foreach ($custGroups as $caccode => $g): ?>
                <tr class="mainrow" onclick="viewDetail(<?php echo $caccode; ?>)" title="Click for bill-wise detail">
                    <td><?php echo $sr++; ?></td>
                    <td style="font-weight:600;color:#1e293b;"><?php echo htmlspecialchars($g['name'] ?? ''); ?></td>
                    <td class="num"><?php echo pf($g['opening']); ?></td>
                    <?php foreach ($bucketDefs as $bk => $lbl): ?>
                    <td class="num<?php echo ($bk=='b180'||$bk=='b151') ? ' oldbucket' : ''; ?>"><?php echo pf($g[$bk]); ?></td>
                    <?php endforeach; ?>
                    <td class="num totcell"><?php echo pf($g['total']); ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
            <tfoot>
                <tr>
                    <td colspan="2" style="text-align:right;">Grand Total (<?php echo count($custGroups); ?> customers)</td>
                    <td class="num"><?php echo pf($grand['opening']); ?></td>
                    <?php foreach ($bucketDefs as $bk => $lbl): ?>
                    <td class="num"><?php echo pf($grand[$bk]); ?></td>
                    <?php endforeach; ?>
                    <td class="num"><?php echo pf($grand['total']); ?></td>
                </tr>
            </tfoot>
        </table>
        </div>
        <?php endif; ?>

    </div>
</div>

<?php include("includes/linksfoot.php"); ?>
<script>
// Bill-wise detail for a party reuses the existing Outstanding Receivable
// (osbill_fr.php) detail view, filtered to this customer + the same As On
// Date, so the two reports always show the exact same underlying bills.
function viewDetail(accode) {
    var f = document.createElement('form');
    f.method = 'POST';
    f.action = 'osbill_fr.php';
    f.target = '_blank';
    var fields = { para: 'R', acname: accode, asondate: <?php echo json_encode($fasondate); ?>, dtype: '2' };
    for (var k in fields) {
        var inp = document.createElement('input');
        inp.type = 'hidden';
        inp.name = k;
        inp.value = fields[k];
        f.appendChild(inp);
    }
    document.body.appendChild(f);
    f.submit();
}
</script>
</body>
</html>
