<?php
// ============================================================
//  MediCore — Financial Reports
//  File: accounting/reports-financial.php
// ============================================================
$page  = 'accounting/reports-financial';
$title = 'Financial Reports';
require_once '../includes/header.php';

$cur  = $settings['currency'];
$from = $_GET['from'] ?? date('Y-m-01');
$to   = $_GET['to']   ?? date('Y-m-d');
$export = $_GET['export'] ?? '';

// ── Due Report data ──────────────────────────────────────────
$customerDues = db_rows(
    "SELECT id, name, phone, due
       FROM customers
      WHERE id > 1 AND due > 0
      ORDER BY due DESC"
);
$supplierDues = db_rows(
    "SELECT id, name, phone, balance
       FROM suppliers
      WHERE balance > 0
      ORDER BY balance DESC"
);
$totalCustDue = (float) db_val("SELECT COALESCE(SUM(due),0) FROM customers WHERE id>1");
$totalSuppDue = (float) db_val("SELECT COALESCE(SUM(balance),0) FROM suppliers");

// ── Advance Report data ──────────────────────────────────────
$customerAdvances = db_rows(
    "SELECT id, name, phone, advance
       FROM customers
      WHERE id > 1 AND advance > 0
      ORDER BY advance DESC"
);
$totalAdvance = (float) db_val("SELECT COALESCE(SUM(advance),0) FROM customers WHERE id>1");

// Advance usage history (from sales.advance_used)
// Defensive migration: guarantee the column exists — older databases
// (or any sale saved before this column existed) won't have it, and
// this report must never hard-fail just because that history is thin.
try {
    $hasAdvUsedCol = db_val(
        "SELECT COUNT(*) FROM information_schema.COLUMNS
          WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME   = 'sales'
            AND COLUMN_NAME  = 'advance_used'"
    );
    if (!$hasAdvUsedCol) {
        db_run("ALTER TABLE sales ADD COLUMN advance_used DECIMAL(12,2) DEFAULT 0.00 AFTER paid");
    }
} catch (Throwable $e) { /* best-effort */ }

$advanceUsage = db_rows(
    "SELECT s.invoice, s.customer_name, s.date, s.advance_used
       FROM sales s
      WHERE s.advance_used > 0 AND s.date BETWEEN ? AND ?
      ORDER BY s.date DESC", [$from, $to]
);
$totalAdvUsed = array_sum(array_column($advanceUsage, 'advance_used'));

// ── Ledger Report data ────────────────────────────────────────
$ledgerType    = $_GET['ltype']   ?? '';
$ledgerAccount = $_GET['laccount'] ?? '';

$lWhere  = ['date BETWEEN ? AND ?'];
$lParams = [$from, $to];
if ($ledgerType)    { $lWhere[] = 'type = ?';    $lParams[] = $ledgerType; }
if ($ledgerAccount) { $lWhere[] = 'account = ?'; $lParams[] = $ledgerAccount; }
$lWhereSql = implode(' AND ', $lWhere);

$ledgerRows = db_rows(
    "SELECT * FROM cash_ledger WHERE $lWhereSql ORDER BY date DESC, id DESC",
    $lParams
);
$ledgerIn  = array_sum(array_map(fn($r) => $r['type']==='in'  ? (float)$r['amount'] : 0, $ledgerRows));
$ledgerOut = array_sum(array_map(fn($r) => $r['type']==='out' ? (float)$r['amount'] : 0, $ledgerRows));
$ledgerNet = $ledgerIn - $ledgerOut;

$accounts = db_rows("SELECT DISTINCT account FROM cash_ledger ORDER BY account");

// ── CSV exports ────────────────────────────────────────────────
if ($export) {
    $map = [
        'due_customer' => [$customerDues, ['Customer','Phone','Due'],
            fn($c) => [$c['name'], $c['phone'], number_format((float)$c['due'],2)]],
        'due_supplier' => [$supplierDues, ['Supplier','Phone','Balance Due'],
            fn($s) => [$s['name'], $s['phone'], number_format((float)$s['balance'],2)]],
        'advance' => [$customerAdvances, ['Customer','Phone','Advance Balance'],
            fn($c) => [$c['name'], $c['phone'], number_format((float)$c['advance'],2)]],
        'ledger' => [$ledgerRows, ['Date','Description','Account','Type','Amount','Reference'],
            fn($l) => [$l['date'], $l['description'], $l['account'],
                ucfirst($l['type']), number_format((float)$l['amount'],2), $l['ref']]],
    ];
    if (isset($map[$export])) {
        [$rows, $headers, $rowFn] = $map[$export];
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="financial_'.$export.'_'.date('Y-m-d').'.csv"');
        $out = fopen('php://output','w');
        fputcsv($out, $headers);
        foreach ($rows as $r) fputcsv($out, $rowFn($r));
        fclose($out); exit;
    }
}
?>

<!-- ── Tabs ───────────────────────────────────────── -->
<div style="display:flex;gap:0;margin-bottom:14px;border-bottom:2px solid var(--border)">
  <?php
  $tabs = [
    'due'     => ['💰', 'Due Report',      'var(--danger)'],
    'advance' => ['💳', 'Advance Report',  'var(--accent)'],
    'ledger'  => ['📒', 'Ledger Report',   'var(--brand)'],
  ];
  foreach ($tabs as $id => [$icon, $label, $color]):
  ?>
  <button id="tab-<?= $id ?>" onclick="switchTab('<?= $id ?>')"
          data-color="<?= $color ?>"
    style="padding:9px 20px;font-size:12.5px;font-weight:600;border:none;
           cursor:pointer;background:transparent;color:var(--text2);
           border-bottom:2px solid transparent;margin-bottom:-2px;transition:all .15s">
    <?= $icon ?> <?= $label ?>
  </button>
  <?php endforeach; ?>
</div>

<!-- ═══════ TAB 1 — Due Report ══════════════════════ -->
<div id="pane-due">

  <div style="display:grid;grid-template-columns:repeat(2,1fr);gap:10px;margin-bottom:14px">
    <div class="sc" style="border-top:3px solid var(--danger)">
      <div class="slabel">Total Customer Due</div>
      <div class="sval b" style="color:var(--danger)">
        <?= $cur . number_format($totalCustDue, 2) ?>
      </div>
      <div style="font-size:10px;color:var(--text3);margin-top:2px">
        <?= count($customerDues) ?> customer(s)
      </div>
    </div>
    <div class="sc" style="border-top:3px solid #ea580c">
      <div class="slabel">Total Supplier Payable</div>
      <div class="sval b" style="color:#ea580c">
        <?= $cur . number_format($totalSuppDue, 2) ?>
      </div>
      <div style="font-size:10px;color:var(--text3);margin-top:2px">
        <?= count($supplierDues) ?> supplier(s)
      </div>
    </div>
  </div>

  <div style="display:grid;grid-template-columns:1fr 1fr;gap:12px">

    <!-- Customer Dues -->
    <div class="card" style="padding:0">
      <div class="ch" style="padding:10px 14px">
        <div class="ct">👤 Customers with Due</div>
        <button class="btn btn-sm" onclick="exportTab('due_customer')">📤 CSV</button>
      </div>
      <div style="padding:8px 14px 0;display:flex;gap:6px;align-items:center">
        <input type="text" id="cd-search" placeholder="🔍 Search customer…" oninput="filterRows('cd-body')"
          style="padding:4px 8px;border:1px solid var(--border);border-radius:var(--rsm);font-size:11.5px;outline:none;width:160px">
        <span id="cd-count" style="font-size:10.5px;color:var(--text3)"></span>
      </div>
      <div class="tw" style="max-height:420px;overflow-y:auto">
        <table>
          <thead><tr><th>Customer</th><th>Phone</th><th style="text-align:right">Due</th></tr></thead>
          <tbody id="cd-body">
            <?php if ($customerDues): foreach ($customerDues as $c): ?>
            <tr class="data-row" data-name="<?= htmlspecialchars(strtolower($c['name'])) ?>">
              <td style="font-weight:600;font-size:12px"><?= htmlspecialchars($c['name']) ?></td>
              <td style="font-size:11px;color:var(--text2)"><?= htmlspecialchars($c['phone'] ?: '—') ?></td>
              <td class="mono" style="text-align:right;font-weight:700;color:var(--danger)">
                <?= $cur . number_format($c['due'], 2) ?>
              </td>
            </tr>
            <?php endforeach; else: ?>
            <tr><td colspan="3" class="empty-state">✅ No customer dues</td></tr>
            <?php endif; ?>
          </tbody>
        </table>
      </div>
      <div id="cd-pager" style="display:flex;align-items:center;padding:6px 14px;border-top:1px solid var(--border)"></div>
    </div>

    <!-- Supplier Dues -->
    <div class="card" style="padding:0">
      <div class="ch" style="padding:10px 14px">
        <div class="ct">🏢 Suppliers Payable</div>
        <button class="btn btn-sm" onclick="exportTab('due_supplier')">📤 CSV</button>
      </div>
      <div style="padding:8px 14px 0;display:flex;gap:6px;align-items:center">
        <input type="text" id="sd-search" placeholder="🔍 Search supplier…" oninput="filterRows('sd-body')"
          style="padding:4px 8px;border:1px solid var(--border);border-radius:var(--rsm);font-size:11.5px;outline:none;width:160px">
        <span id="sd-count" style="font-size:10.5px;color:var(--text3)"></span>
      </div>
      <div class="tw" style="max-height:420px;overflow-y:auto">
        <table>
          <thead><tr><th>Supplier</th><th>Phone</th><th style="text-align:right">Balance</th></tr></thead>
          <tbody id="sd-body">
            <?php if ($supplierDues): foreach ($supplierDues as $s): ?>
            <tr class="data-row" data-name="<?= htmlspecialchars(strtolower($s['name'])) ?>">
              <td style="font-weight:600;font-size:12px"><?= htmlspecialchars($s['name']) ?></td>
              <td style="font-size:11px;color:var(--text2)"><?= htmlspecialchars($s['phone'] ?: '—') ?></td>
              <td class="mono" style="text-align:right;font-weight:700;color:#ea580c">
                <?= $cur . number_format($s['balance'], 2) ?>
              </td>
            </tr>
            <?php endforeach; else: ?>
            <tr><td colspan="3" class="empty-state">✅ No supplier dues</td></tr>
            <?php endif; ?>
          </tbody>
        </table>
      </div>
      <div id="sd-pager" style="display:flex;align-items:center;padding:6px 14px;border-top:1px solid var(--border)"></div>
    </div>

  </div>
</div>

<!-- ═══════ TAB 2 — Advance Report ══════════════════ -->
<div id="pane-advance" style="display:none">

  <div style="display:grid;grid-template-columns:repeat(3,1fr);gap:10px;margin-bottom:14px">
    <div class="sc" style="border-top:3px solid var(--accent)">
      <div class="slabel">Total Advance Balance</div>
      <div class="sval b" style="color:var(--accent)">
        <?= $cur . number_format($totalAdvance, 2) ?>
      </div>
      <div style="font-size:10px;color:var(--text3);margin-top:2px">
        <?= count($customerAdvances) ?> customer(s)
      </div>
    </div>
    <div class="sc" style="border-top:3px solid var(--brand)">
      <div class="slabel">Advance Used (period)</div>
      <div class="sval b" style="color:var(--brand)">
        <?= $cur . number_format($totalAdvUsed, 2) ?>
      </div>
      <div style="font-size:10px;color:var(--text3);margin-top:2px">
        Adjusted against invoices
      </div>
    </div>
    <div class="sc" style="border-top:3px solid var(--teal)">
      <div class="slabel">Usage Transactions</div>
      <div class="sval b" style="color:var(--teal)"><?= count($advanceUsage) ?></div>
    </div>
  </div>

  <div style="display:grid;grid-template-columns:1fr 1fr;gap:12px">

    <!-- Customer Advance Balances -->
    <div class="card" style="padding:0">
      <div class="ch" style="padding:10px 14px">
        <div class="ct">💳 Customer Advance Balances</div>
        <button class="btn btn-sm" onclick="exportTab('advance')">📤 CSV</button>
      </div>
      <div style="padding:8px 14px 0;display:flex;gap:6px;align-items:center">
        <input type="text" id="ca-search" placeholder="🔍 Search customer…" oninput="filterRows('ca-body')"
          style="padding:4px 8px;border:1px solid var(--border);border-radius:var(--rsm);font-size:11.5px;outline:none;width:160px">
        <span id="ca-count" style="font-size:10.5px;color:var(--text3)"></span>
      </div>
      <div class="tw" style="max-height:420px;overflow-y:auto">
        <table>
          <thead><tr><th>Customer</th><th>Phone</th><th style="text-align:right">Balance</th></tr></thead>
          <tbody id="ca-body">
            <?php if ($customerAdvances): foreach ($customerAdvances as $c): ?>
            <tr class="data-row" data-name="<?= htmlspecialchars(strtolower($c['name'])) ?>">
              <td style="font-weight:600;font-size:12px"><?= htmlspecialchars($c['name']) ?></td>
              <td style="font-size:11px;color:var(--text2)"><?= htmlspecialchars($c['phone'] ?: '—') ?></td>
              <td class="mono" style="text-align:right;font-weight:700;color:var(--accent)">
                <?= $cur . number_format($c['advance'], 2) ?>
              </td>
            </tr>
            <?php endforeach; else: ?>
            <tr><td colspan="3" class="empty-state">No customers with advance balance</td></tr>
            <?php endif; ?>
          </tbody>
        </table>
      </div>
      <div id="ca-pager" style="display:flex;align-items:center;padding:6px 14px;border-top:1px solid var(--border)"></div>
    </div>

    <!-- Advance Usage History -->
    <div class="card" style="padding:0">
      <div class="ch" style="padding:10px 14px">
        <div class="ct">📋 Advance Usage History</div>
        <span style="font-size:10.5px;color:var(--text3)"><?= $from ?> → <?= $to ?></span>
      </div>
      <div style="padding:8px 14px 0;display:flex;gap:6px;align-items:center">
        <input type="text" id="au-search" placeholder="🔍 Search invoice/customer…" oninput="filterRows('au-body')"
          style="padding:4px 8px;border:1px solid var(--border);border-radius:var(--rsm);font-size:11.5px;outline:none;width:180px">
        <span id="au-count" style="font-size:10.5px;color:var(--text3)"></span>
      </div>
      <div class="tw" style="max-height:420px;overflow-y:auto">
        <table>
          <thead><tr><th>Date</th><th>Invoice</th><th>Customer</th><th style="text-align:right">Used</th></tr></thead>
          <tbody id="au-body">
            <?php if ($advanceUsage): foreach ($advanceUsage as $a): ?>
            <tr class="data-row" data-name="<?= htmlspecialchars(strtolower($a['invoice'].' '.$a['customer_name'])) ?>">
              <td style="font-size:11.5px"><?= $a['date'] ?></td>
              <td class="mono" style="font-size:11px;color:var(--brand)"><?= htmlspecialchars($a['invoice']) ?></td>
              <td style="font-size:12px"><?= htmlspecialchars($a['customer_name']) ?></td>
              <td class="mono" style="text-align:right;font-weight:700;color:var(--danger)">
                -<?= $cur . number_format($a['advance_used'], 2) ?>
              </td>
            </tr>
            <?php endforeach; else: ?>
            <tr><td colspan="4" class="empty-state">No advance usage in this period</td></tr>
            <?php endif; ?>
          </tbody>
        </table>
      </div>
      <div id="au-pager" style="display:flex;align-items:center;padding:6px 14px;border-top:1px solid var(--border)"></div>
    </div>

  </div>
</div>

<!-- ═══════ TAB 3 — Ledger Report ═══════════════════ -->
<div id="pane-ledger" style="display:none">

  <!-- Filters -->
  <form method="GET" style="display:flex;gap:9px;align-items:center;
        flex-wrap:wrap;margin-bottom:14px;padding:10px 14px;
        background:var(--surface);border:1px solid var(--border);border-radius:var(--r)">
    <input type="hidden" name="tab" value="ledger">
    <span style="font-size:12px;font-weight:600;color:var(--text2)">📅 Range:</span>
    <input type="date" name="from" value="<?= $from ?>"
      style="padding:5px 8px;border:1px solid var(--border);border-radius:var(--rsm);font-size:12px;outline:none">
    <span style="color:var(--text3);font-size:11px">to</span>
    <input type="date" name="to" value="<?= $to ?>"
      style="padding:5px 8px;border:1px solid var(--border);border-radius:var(--rsm);font-size:12px;outline:none">
    <select name="ltype"
      style="padding:5px 9px;border:1px solid var(--border);border-radius:var(--rsm);font-size:12px;outline:none">
      <option value="">All Types</option>
      <option value="in"  <?= $ledgerType==='in'  ?'selected':'' ?>>💰 In</option>
      <option value="out" <?= $ledgerType==='out' ?'selected':'' ?>>📤 Out</option>
    </select>
    <select name="laccount"
      style="padding:5px 9px;border:1px solid var(--border);border-radius:var(--rsm);font-size:12px;outline:none">
      <option value="">All Accounts</option>
      <?php foreach ($accounts as $a): ?>
      <option value="<?= htmlspecialchars($a['account']) ?>"
              <?= $ledgerAccount===$a['account']?'selected':'' ?>>
        <?= htmlspecialchars($a['account']) ?>
      </option>
      <?php endforeach; ?>
    </select>
    <button type="submit" class="btn btn-primary btn-sm">Apply</button>
    <div style="margin-left:auto">
      <button type="button" class="btn btn-sm" onclick="exportTab('ledger')">📤 Export CSV</button>
    </div>
  </form>

  <div style="display:grid;grid-template-columns:repeat(3,1fr);gap:10px;margin-bottom:14px">
    <div class="sc" style="border-top:3px solid var(--accent)">
      <div class="slabel">Total In</div>
      <div class="sval b" style="color:var(--accent)"><?= $cur . number_format($ledgerIn, 2) ?></div>
    </div>
    <div class="sc" style="border-top:3px solid var(--danger)">
      <div class="slabel">Total Out</div>
      <div class="sval b" style="color:var(--danger)"><?= $cur . number_format($ledgerOut, 2) ?></div>
    </div>
    <div class="sc" style="border-top:3px solid var(--brand)">
      <div class="slabel">Net Balance</div>
      <div class="sval b" style="color:<?= $ledgerNet>=0?'var(--brand)':'var(--danger)' ?>">
        <?= $cur . number_format($ledgerNet, 2) ?>
      </div>
    </div>
  </div>

  <div style="display:flex;gap:8px;margin-bottom:10px">
    <input type="text" id="lg-search" placeholder="🔍 Search description/account/reference…" oninput="filterRows('lg-body')"
      style="padding:5px 10px;border:1px solid var(--border);border-radius:var(--rsm);font-size:12px;outline:none;width:280px">
    <button class="btn btn-sm" onclick="document.getElementById('lg-search').value='';filterRows('lg-body')">✕ Clear</button>
    <span id="lg-count" style="font-size:11px;color:var(--text3);align-self:center"></span>
  </div>

  <div class="card" style="padding:0">
    <div class="tw" style="max-height:520px;overflow-y:auto">
      <table>
        <thead><tr>
          <th>Date</th><th>Description</th><th>Account</th>
          <th style="text-align:center">Type</th>
          <th style="text-align:right">Amount</th>
          <th>Reference</th>
        </tr></thead>
        <tbody id="lg-body">
          <?php if ($ledgerRows): foreach ($ledgerRows as $l):
            $isIn = $l['type'] === 'in';
          ?>
          <tr class="data-row"
              data-name="<?= htmlspecialchars(strtolower($l['description'].' '.$l['account'].' '.($l['ref'] ?? ''))) ?>">
            <td style="font-size:11.5px"><?= $l['date'] ?></td>
            <td style="font-size:12px"><?= htmlspecialchars($l['description']) ?></td>
            <td style="font-size:11.5px;color:var(--text2)"><?= htmlspecialchars($l['account']) ?></td>
            <td style="text-align:center">
              <span class="badge"
                    style="background:<?= $isIn?'#d4f0e6':'#fde8e8' ?>;
                           color:<?= $isIn?'var(--accent)':'var(--danger)' ?>;font-weight:700">
                <?= $isIn ? '💰 In' : '📤 Out' ?>
              </span>
            </td>
            <td class="mono" style="text-align:right;font-weight:700;
                color:<?= $isIn?'var(--accent)':'var(--danger)' ?>">
              <?= $isIn?'+':'-' ?><?= $cur . number_format($l['amount'], 2) ?>
            </td>
            <td class="mono" style="font-size:11px;color:var(--text3)">
              <?= htmlspecialchars($l['ref'] ?? '') ?>
            </td>
          </tr>
          <?php endforeach; else: ?>
          <tr><td colspan="6" class="empty-state">No ledger entries in this period</td></tr>
          <?php endif; ?>
        </tbody>
      </table>
    </div>
    <div id="lg-pager" style="display:flex;align-items:center;padding:8px 14px;border-top:1px solid var(--border)"></div>
  </div>
</div>

<script>
let activeTab = 'due';

function switchTab(id) {
  activeTab = id;
  document.querySelectorAll('[id^="pane-"]').forEach(p => p.style.display='none');
  document.querySelectorAll('[id^="tab-"]').forEach(b => {
    b.style.borderBottomColor='transparent'; b.style.color='var(--text2)';
  });
  document.getElementById('pane-'+id).style.display = 'block';
  const btn = document.getElementById('tab-'+id);
  btn.style.borderBottomColor = btn.dataset.color;
  btn.style.color = btn.dataset.color;
  history.replaceState(null,'','?tab='+id+'<?= $from && $to ? "&from=".$from."&to=".$to : "" ?>');
}

function exportTab(type) {
  const params = new URLSearchParams(window.location.search);
  params.set('export', type);
  window.location.href = '?' + params.toString();
}

// ── Pagination — see mediPaginate() in includes/footer.php.
const reportPagers = {};

function filterRows(bodyId) {
  reportPagers[bodyId]?.refresh();
}

function initReportPagers() {
  const configs = [
    ['cd-body', 'cd-search', 'cd-pager', 'cd-count', 'customer'],
    ['sd-body', 'sd-search', 'sd-pager', 'sd-count', 'supplier'],
    ['ca-body', 'ca-search', 'ca-pager', 'ca-count', 'customer'],
    ['au-body', 'au-search', 'au-pager', 'au-count', 'entry'],
    ['lg-body', 'lg-search', 'lg-pager', 'lg-count', 'entry'],
  ];
  configs.forEach(([bodyId, searchId, pagerId, countId, label]) => {
    const body = document.getElementById(bodyId);
    if (!body) return;
    reportPagers[bodyId] = mediPaginate({
      rows: Array.from(body.querySelectorAll('.data-row')),
      predicate: row => {
        const q = document.getElementById(searchId).value.toLowerCase();
        return !q || row.dataset.name.includes(q);
      },
      pagerEl: document.getElementById(pagerId),
      countEl: document.getElementById(countId),
      countLabel: label,
      perPage: 20, renumber: false,
    });
  });
}

document.addEventListener('DOMContentLoaded', () => {
  const params = new URLSearchParams(window.location.search);
  const initTab = params.get('tab') || 'due';
  switchTab(initTab);
  initReportPagers();
});
</script>

<?php require_once '../includes/footer.php'; ?>
