<?php
include 'layout.php';
include 'db.php';

// Fetch active banks for dropdown
$banks = $conn->query("SELECT id, account_title FROM bank_accounts WHERE is_active = 1");

// Fetch all payment methods for dynamic filtering
$payment_methods = $conn->query("SELECT method_name, display_name FROM payment_methods WHERE is_active = 1 ORDER BY sort_order");

// Get filter values
$account_type = $_GET['account_type'] ?? 'all';
$payment_method = $_GET['payment_method'] ?? 'all';
$bank_id = $_GET['bank_id'] ?? '';
$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';

// Set default dates if not provided
if (empty($start_date)) {
    $start_date = date('Y-m-d', strtotime('-30 days'));
}
if (empty($end_date)) {
    $end_date = date('Y-m-d');
}
?>

<div class="main-wrapper">
  <div class="page-wrapper">
    <div class="content">
      <div class="page-header d-flex justify-content-between align-items-center">
        <div class="page-title">
          <h4>Account Ledger</h4>
          <h6>View all cash and bank transactions with running balance</h6>
        </div>
      </div>

      <!-- Filter Form -->
      <form method="GET" class="row mb-4 g-2 align-items-end">
        <div class="col-md-2">
          <label>Account Type</label>
          <select name="account_type" class="form-control">
            <option value="all" <?= $account_type == 'all' ? 'selected' : '' ?>>All Types</option>
            <option value="cash" <?= $account_type == 'cash' ? 'selected' : '' ?>>Cash</option>
            <option value="bank" <?= $account_type == 'bank' ? 'selected' : '' ?>>Bank</option>
            <option value="card" <?= $account_type == 'card' ? 'selected' : '' ?>>Card</option>
            <option value="upi" <?= $account_type == 'upi' ? 'selected' : '' ?>>UPI</option>
            <option value="other" <?= $account_type == 'other' ? 'selected' : '' ?>>Other</option>
          </select>
        </div>
        <div class="col-md-2">
          <label>Payment Method</label>
          <select name="payment_method" class="form-control">
            <option value="all" <?= $payment_method == 'all' ? 'selected' : '' ?>>All Methods</option>
            <?php while($pm = $payment_methods->fetch_assoc()): ?>
              <option value="<?= $pm['method_name'] ?>" <?= $payment_method == $pm['method_name'] ? 'selected' : '' ?>><?= htmlspecialchars($pm['display_name']) ?></option>
            <?php endwhile; ?>
          </select>
        </div>
        <div class="col-md-2">
          <label>Bank Account</label>
          <select name="bank_id" class="form-control">
            <option value="">All Banks</option>
            <?php while($b = $banks->fetch_assoc()): ?>
              <option value="<?= $b['id'] ?>" <?= $bank_id == $b['id'] ? 'selected' : '' ?>><?= htmlspecialchars($b['account_title']) ?></option>
            <?php endwhile; ?>
          </select>
        </div>
        <div class="col-md-2">
          <label>Start Date</label>
          <input type="date" name="start_date" value="<?= htmlspecialchars($start_date) ?>" class="form-control">
        </div>
        <div class="col-md-2">
          <label>End Date</label>
          <input type="date" name="end_date" value="<?= htmlspecialchars($end_date) ?>" class="form-control">
        </div>
        <div class="col-md-2">
          <button class="btn btn-primary w-100">Filter</button>
        </div>
      </form>

             <?php
       // --- Fetch Transactions (moved before summary cards) ---
       $where_in = [];
       $where_out = [];
       $params = [];

       // Account type filter
       if ($account_type === 'cash') {
           $where_in[] = "at.account_type = 'cash'";
           $where_out[] = "e.payment_method = 'cash'";
       } elseif ($account_type === 'bank') {
           $where_in[] = "at.account_type = 'bank'";
           $where_out[] = "e.payment_method = 'bank'";
           if ($bank_id) {
               $where_in[] = "at.account_id = " . intval($bank_id);
               $where_out[] = "e.bank_account_id = " . intval($bank_id);
           }
       } elseif ($account_type === 'card') {
           $where_in[] = "at.account_type = 'card'";
           $where_out[] = "e.payment_method = 'card'";
       } elseif ($account_type === 'upi') {
           $where_in[] = "at.account_type = 'upi'";
           $where_out[] = "e.payment_method = 'upi'";
       } elseif ($account_type === 'other') {
           $where_in[] = "at.account_type NOT IN ('cash', 'bank', 'card', 'upi')";
           $where_out[] = "e.payment_method NOT IN ('cash', 'bank', 'card', 'upi')";
       } else { // all
           // No extra filter for inflow
           // No extra filter for outflow
           if ($bank_id) {
               $where_in[] = "at.account_id = " . intval($bank_id);
               $where_out[] = "e.bank_account_id = " . intval($bank_id);
           }
       }

       // Payment method filter
       if ($payment_method !== 'all') {
           $where_in[] = "at.account_type = '" . $conn->real_escape_string($payment_method) . "'";
           $where_out[] = "e.payment_method = '" . $conn->real_escape_string($payment_method) . "'";
       }

       // Date filter
       if ($start_date) {
           $where_in[] = "DATE(at.created_at) >= '" . $conn->real_escape_string($start_date) . "'";
           $where_out[] = "DATE(e.expense_date) >= '" . $conn->real_escape_string($start_date) . "'";
       }
       if ($end_date) {
           $where_in[] = "DATE(at.created_at) <= '" . $conn->real_escape_string($end_date) . "'";
           $where_out[] = "DATE(e.expense_date) <= '" . $conn->real_escape_string($end_date) . "'";
       }

       $inflow_sql = "
           SELECT 
               at.created_at AS txn_date,
               at.account_type AS payment_method,
               IFNULL(ba.account_title, 'Cash') AS account,
               at.description,
               CONCAT('Sale #', at.sale_id) AS reference_id,
               at.amount AS amount_in,
               0 AS amount_out
           FROM account_transactions at
           LEFT JOIN bank_accounts ba ON at.account_id = ba.id
           " . (count($where_in) ? ("WHERE " . implode(' AND ', $where_in)) : "") . "
           ORDER BY at.created_at DESC
       ";

       $outflow_sql = "
           SELECT 
               e.expense_date AS txn_date,
               e.payment_method,
               IFNULL(ba.account_title, 'Cash') AS account,
               e.remarks AS description,
               CONCAT('Expense #', e.id) AS reference_id,
               0 AS amount_in,
               e.amount AS amount_out
           FROM expenses e
           LEFT JOIN bank_accounts ba ON e.bank_account_id = ba.id
           " . (count($where_out) ? ("WHERE " . implode(' AND ', $where_out)) : "") . "
           ORDER BY e.expense_date DESC
       ";

       // Fetch and merge
       $transactions = [];
       $res_in = $conn->query($inflow_sql);
       while ($row = $res_in->fetch_assoc()) {
           $row['txn_type'] = 'in';
           $transactions[] = $row;
       }
       $res_out = $conn->query($outflow_sql);
       while ($row = $res_out->fetch_assoc()) {
           $row['txn_type'] = 'out';
           $transactions[] = $row;
       }

       // Sort by date, then by type (in before out if same date/time)
       usort($transactions, function($a, $b) {
           $cmp = strcmp($a['txn_date'], $b['txn_date']);
           if ($cmp === 0) {
               return $a['txn_type'] === 'in' ? -1 : 1;
           }
           return $cmp;
       });

       // Calculate running balance
       $balance = 0;
       foreach ($transactions as $k => $row) {
           $balance += $row['amount_in'] - $row['amount_out'];
           $transactions[$k]['balance'] = $balance;
       }

       // Helper function to get payment method display name
       function getPaymentMethodDisplayName($method_name) {
           global $conn;
           static $method_names = null;
           
           if ($method_names === null) {
               $method_names = [];
               $result = $conn->query("SELECT method_name, display_name FROM payment_methods WHERE is_active = 1");
               while ($row = $result->fetch_assoc()) {
                   $method_names[$row['method_name']] = $row['display_name'];
               }
           }
           
           return $method_names[$method_name] ?? ucfirst($method_name);
       }

       // Calculate summary totals
       $total_in = 0;
       $total_out = 0;
       $net_balance = 0;
       
       foreach ($transactions as $row) {
           $total_in += $row['amount_in'];
           $total_out += $row['amount_out'];
       }
       $net_balance = $total_in - $total_out;
       ?>

       <!-- Summary Cards -->
       <div class="row mb-4">
        <div class="col-md-3">
          <div class="card bg-success text-white">
            <div class="card-body">
              <h5 class="card-title">Total Inflow</h5>
              <h3 class="mb-0">Rs <?= number_format($total_in, 2) ?></h3>
            </div>
          </div>
        </div>
        <div class="col-md-3">
          <div class="card bg-danger text-white">
            <div class="card-body">
              <h5 class="card-title">Total Outflow</h5>
              <h3 class="mb-0">Rs <?= number_format($total_out, 2) ?></h3>
            </div>
          </div>
        </div>
        <div class="col-md-3">
          <div class="card <?= $net_balance >= 0 ? 'bg-primary' : 'bg-warning' ?> text-white">
            <div class="card-body">
              <h5 class="card-title">Net Balance</h5>
              <h3 class="mb-0">Rs <?= number_format($net_balance, 2) ?></h3>
            </div>
          </div>
        </div>
        <div class="col-md-3">
          <div class="card bg-info text-white">
            <div class="card-body">
              <h5 class="card-title">Transactions</h5>
              <h3 class="mb-0"><?= count($transactions) ?></h3>
            </div>
          </div>
        </div>
      </div>

      <!-- Ledger Table -->
      <div class="card">
        <div class="card-header">
          <h5 class="card-title mb-0">Transaction Details</h5>
        </div>
        <div class="card-body">
          <div class="table-responsive">
            <table class="table table-bordered table-striped">
            <thead class="table-light">
              <tr>
                <th>Date</th>
                <th>Payment Method</th>
                <th>Account</th>
                <th>Description</th>
                <th>Reference</th>
                <th class="text-end">In</th>
                <th class="text-end">Out</th>
                <th class="text-end">Balance</th>
              </tr>
            </thead>
                         <tbody>
              <?php if (count($transactions)): ?>
                <?php foreach ($transactions as $row): ?>
                  <tr>
                    <td><?= htmlspecialchars($row['txn_date'] ?? '') ?></td>
                    <td>
                      <span class="badge bg-<?= $row['payment_method'] === 'cash' ? 'success' : ($row['payment_method'] === 'bank' ? 'primary' : ($row['payment_method'] === 'card' ? 'info' : ($row['payment_method'] === 'upi' ? 'warning' : 'secondary'))) ?>">
                        <?= htmlspecialchars(getPaymentMethodDisplayName($row['payment_method'] ?? '')) ?>
                      </span>
                    </td>
                    <td><?= htmlspecialchars($row['account'] ?? '') ?></td>
                    <td><?= htmlspecialchars($row['description'] ?? '') ?></td>
                    <td><?= htmlspecialchars($row['reference_id'] ?? '') ?></td>
                    <td class="text-end"><?= $row['amount_in'] ? number_format($row['amount_in'], 2) : '' ?></td>
                    <td class="text-end"><?= $row['amount_out'] ? number_format($row['amount_out'], 2) : '' ?></td>
                    <td class="text-end"><?= number_format($row['balance'], 2) ?></td>
                  </tr>
                <?php endforeach; ?>
              <?php else: ?>
                <tr><td colspan="8" class="text-center">No transactions found.</td></tr>
              <?php endif; ?>
            </tbody>
          </table>
          </div>
        </div>
      </div>

    </div>
  </div>
</div> 