<?php
session_start();
ob_start();
if (!isset($_SESSION['role_id']) || $_SESSION['role_id'] != 4) {
    header("Location: ../../index.php");
    exit();
}
include('../../includes/config.php');
include('../../includes/header.php');

// Filters
$filter_store = $_GET['store_id'] ?? '';
$from_date = $_GET['from_date'] ?? date('Y-m-01');
$to_date   = $_GET['to_date'] ?? date('Y-m-d');

// Build where clause
$whereClauses = ["ft.status = 'approved'", "ft.transaction_date BETWEEN ? AND ?"];
$params = [$from_date, $to_date];
$types = "ss";

if ($filter_store !== '' && is_numeric($filter_store)) {
    $whereClauses[] = "ft.store_id = ?";
    $params[] = $filter_store;
    $types .= "i";
}

$whereSql = implode(" AND ", $whereClauses);

// Fetch account summaries
$sql = "
    SELECT a.id as acct_id, a.account_name,
           SUM(ft.debit) as total_debit,
           SUM(ft.credit) as total_credit
    FROM accounts a
    LEFT JOIN financial_transactions ft
      ON ft.account_id = a.id AND $whereSql
    GROUP BY a.id
    HAVING total_debit <> 0 OR total_credit <> 0
    ORDER BY a.account_name
";
$stmt = $conn->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();

// Compute totals
$grandDebit = $grandCredit = 0;
$rows = $result->fetch_all(MYSQLI_ASSOC);
foreach ($rows as $r) {
    $grandDebit += floatval($r['total_debit']);
    $grandCredit += floatval($r['total_credit']);
}



$page_title = "Trial Balance";
?>

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title><?= $page_title ?></title>
  <link rel="icon" type="image/png" href="../../images/logo.png" />
  <style>
    body {
      background: url('../../images/profile.jpg') center/cover fixed;
      color: #eee;
      font-family: 'Segoe UI', sans-serif;
      margin: 0;
      padding: 0;
    }

    .wrap {
      max-width: 1850px;
      margin: 100px auto 40px auto;
      padding: 0 30px;
    }

    .page-header {
      display: flex;
      justify-content: space-between;
      align-items: center;
      margin-bottom: 1rem;
    }

    h1.page-title {
      color: #ffa500;
      margin: 0;
      display: flex;
      align-items: center;
      gap: 10px;
      font-weight: 700;
      font-size: 2rem;
    }

    h1.page-title img {
      height: 40px;
      width: auto;
    }

    a.back-link {
      color: #ffa500;
      text-decoration: none;
      font-weight: 600;
      border: 1.5px solid #ffa500;
      padding: 6px 14px;
      border-radius: 8px;
      transition: background-color 0.3s ease, color 0.3s ease;
      white-space: nowrap;
    }

    a.back-link:hover {
      background-color: #ffa500;
      color: #222;
    }

    .filters {
      margin: 20px 0 30px 0;
      display: flex;
      gap: 15px;
      flex-wrap: wrap;
    }

    .filters input,
    .filters select,
    .filters button {
      padding: 10px 15px;
      border: none;
      border-radius: 8px;
      font-weight: 500;
      min-width: 160px;
    }

    .filters button {
      background: #ffa500;
      color: #222;
      cursor: pointer;
    }

    .summary-table,
    .detail-table {
      width: 100%;
      border-collapse: collapse;
      font-size: 15px;
      background: rgba(255,255,255,0.08);
      border-radius: 10px;
      overflow: hidden;
      margin-bottom: 40px;
    }

    .summary-table th,
    .summary-table td,
    .detail-table th,
    .detail-table td {
      padding: 12px 15px;
      border-bottom: 1px solid rgba(255,255,255,0.15);
    }

    .summary-table th,
    .detail-table th {
      background: rgba(255,165,0,0.25);
      color: #fff;
      text-align: left;
    }

    .summary-table td:nth-child(2),
    .summary-table td:nth-child(3),
    .summary-table td:nth-child(4),
    .summary-table th:nth-child(2),
    .summary-table th:nth-child(3),
    .summary-table th:nth-child(4) {
      text-align: right;
    }

    .summary-table a {
      color: #ffa500;
      font-weight: bold;
      text-decoration: none;
    }

    .summary-table a:hover {
      text-decoration: underline;
    }

    .footer td {
      font-weight: bold;
      background: rgba(255,255,255,0.05);
    }

    #detailContainer {
      margin-top: 30px;
    }
  </style>
</head>

<body>
  <div class="wrap">
    <div class="page-header">
      <h1 class="page-title">
        <img src="../../images/logo.png" alt="Logo">
        <?= htmlspecialchars($page_title) ?>
      </h1>
      <a href="reports_dashboard.php" class="back-link">&laquo; Back to Reports Dashboard</a>
    </div>

    <!-- Filters -->
    <form class="filters" method="GET">
      <input type="date" name="from_date" value="<?= htmlspecialchars($from_date) ?>">
      <input type="date" name="to_date" value="<?= htmlspecialchars($to_date) ?>">
      <select name="store_id">
        <option value="">All Stores</option>
        <?php foreach ($conn->query("SELECT id, store_name FROM stores") as $st): ?>
          <option value="<?= $st['id'] ?>" <?= ($filter_store == $st['id'] ? 'selected' : '') ?>>
            <?= htmlspecialchars($st['store_name']) ?>
          </option>
        <?php endforeach; ?>
      </select>
      <button type="submit">Filter</button>
    </form>

    <!-- Summary Table -->
    <table class="summary-table">
      <thead>
        <tr>
          <th>Account</th>
          <th>Total Debit</th>
          <th>Total Credit</th>
          <th>Balance</th>
        </tr>
      </thead>
      <tbody>
        <?php foreach ($rows as $r): 
          $d = floatval($r['total_debit']); 
          $c = floatval($r['total_credit']);
          $bal = $d - $c;
        ?>
        <tr>
          <td>
            <a href="#" onclick="loadDetail(<?= $r['acct_id'] ?>); return false;">
              <?= htmlspecialchars($r['account_name']) ?>
            </a>
          </td>
          <td><?= number_format($d,2) ?></td>
          <td><?= number_format($c,2) ?></td>
          <td><?= number_format($bal,2) ?></td>
        </tr>
        <?php endforeach; ?>
      </tbody>
      <tfoot>
        <tr class="footer">
          <td>Grand Totals</td>
          <td><?= number_format($grandDebit, 2) ?></td>
          <td><?= number_format($grandCredit, 2) ?></td>
          <td><?= number_format($grandDebit - $grandCredit, 2) ?></td>
        </tr>
      </tfoot>
    </table>

    <!-- Drill-down Detail Container -->
    <div id="detailContainer"></div>
  </div>

  <script>
    function loadDetail(acctId) {
      const params = new URLSearchParams({
        acct_id: acctId,
        from_date: "<?= $from_date ?>",
        to_date: "<?= $to_date ?>",
        store_id: "<?= $filter_store ?>",
      });

      const div = document.getElementById("detailContainer");
      div.innerHTML = "<p>Loading transactions…</p>";

      fetch("trial_balance_detail.php?" + params.toString())
        .then(res => res.text())
        .then(html => div.innerHTML = html)
        .catch(() => div.innerHTML = "<p style='color:red;'>Error loading data.</p>");
    }
  </script>

  <?php include('../../includes/footer.php'); ob_end_flush(); ?>
</body>
</html>
