<?php
require_once __DIR__ . '/../../../db.php';
require_once __DIR__ . '/../../../auth.php';
requireLogin();

$user        = getCurrentUser();
$currentDate = getCurrentDate();
$currentPage = 'index';
$menuArea    = 'main_1';
$fullPage    = 'main_1/index';
$tpl         = __DIR__ . '/../../templates/';

$db = getDB();

// ── ตัวกรอง ──────────────────────────────────────────────────────────────────
$yearStr      = $_GET['year']     ?? date('Y');
$prCodeFilter = $_GET['province'] ?? '';
$statusFilter = $_GET['status']   ?? '';

// ── จังหวัด ──────────────────────────────────────────────────────────────────
$provinces = $db->query("SELECT PR_CODE, PR_NAME FROM EHONGDB.dbo.PROV ORDER BY PR_NAME")->fetchAll();

// ── สถานะ ────────────────────────────────────────────────────────────────────
$statuses  = $db->query("SELECT RAP_ID, RAP_NAMESTATUS FROM CONFIG.dbo.RAP_STATUS ORDER BY RAP_ID")->fetchAll();

// ── ดึงสถานที่ (พร้อม filter) ────────────────────────────────────────────────
$sql    = "SELECT l.RAP_ID, l.RAP_IDNAME, l.RAP_NAME, l.RAP_PRICE,
                  ISNULL(l.PR_CODE,'') PR_CODE, ISNULL(p.PR_NAME,'') PR_NAME,
                  ISNULL(l.RAP_STATUS,'') RAP_STATUS, ISNULL(s.RAP_NAMESTATUS,'') RAP_NAMESTATUS
           FROM CONFIG.dbo.LOCATION_RAP l
           LEFT JOIN EHONGDB.dbo.PROV p ON l.PR_CODE = p.PR_CODE
           LEFT JOIN CONFIG.dbo.RAP_STATUS s ON l.RAP_STATUS = s.RAP_ID
           WHERE 1=1";
$params = [];

if ($prCodeFilter !== '') { $sql .= " AND l.PR_CODE = ?";   $params[] = $prCodeFilter; }
if ($statusFilter !== '')  { $sql .= " AND l.RAP_STATUS = ?"; $params[] = $statusFilter; }
$sql .= " ORDER BY l.RAP_ID ASC";

$stmt = $db->prepare($sql);
$stmt->execute($params);
$locations = $stmt->fetchAll();

// ── สร้าง reportRows (12 เดือน) ──────────────────────────────────────────────
$reportRows = [];
foreach ($locations as $i => $loc) {
    $reportRows[] = [
        'no'       => $i + 1,
        'location' => $loc,
        'payments' => array_fill(0, 12, ''),
    ];
}

// ── ดึงยอดการจ่าย ─────────────────────────────────────────────────────────────
$rfStmt = $db->prepare("SELECT RAP_IDNAME, RF_M, ISNULL(RF_PRICE,'0') RF_PRICE
                         FROM CONFIG.dbo.LOCATION_RF WHERE RF_Y = ?");
$rfStmt->execute([$yearStr]);
$payments = $rfStmt->fetchAll();

// สร้าง index เพื่อค้นหาเร็ว
$rowIndex = [];
foreach ($reportRows as $i => $row) {
    $rowIndex[$row['location']['RAP_IDNAME']] = $i;
}

foreach ($payments as $pay) {
    $m = (int)$pay['RF_M'];
    if ($m < 1 || $m > 12) continue;
    $idname = $pay['RAP_IDNAME'];
    if (!isset($rowIndex[$idname])) continue;

    $idx      = $rowIndex[$idname];
    $existing = (float)($reportRows[$idx]['payments'][$m - 1] ?: '0');
    $add      = (float)$pay['RF_PRICE'];
    $sum      = $existing + $add;
    $reportRows[$idx]['payments'][$m - 1] = ($sum == floor($sum))
        ? number_format($sum, 0, '.', '')
        : number_format($sum, 2, '.', '');
}
?>
<!doctype html>
<html lang="en">
<head>
  <?php include $tpl . 'backend_head.php'; ?>
  <link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css">
</head>
<body class="layout-fixed sidebar-expand-lg sidebar-open bg-body-tertiary">
  <div class="app-wrapper">
    <?php include $tpl . 'backend_header.php'; ?>
    <?php include $tpl . 'backend_menu.php'; ?>
    <main class="app-main">
      <div class="app-content-header">
        <div class="container-fluid">
          <div class="row">
            <div class="col-sm-6"><h3 class="mb-0">ระบบงาน EHONG MD</h3></div>
            <div class="col-sm-6">
              <ol class="breadcrumb float-sm-end">
                <li class="breadcrumb-item"><a href="/projects/kkv1/ehongmd/ehongmd/main.php">หน้าหลัก</a></li>
                <li class="breadcrumb-item active">ระบบงาน EHONG MD</li>
              </ol>
            </div>
          </div>
        </div>
      </div>
      <div class="app-content">
        <div class="container-fluid">

          <!-- ฟอร์มค้นหา -->
          <div class="card card-outline card-primary mb-4">
            <div class="card-header">
              <h3 class="card-title">ค้นหารายงานสรุปยอดการจ่ายค่าเช่า</h3>
            </div>
            <div class="card-body">
              <form method="GET" action="">
                <div class="row align-items-end">
                  <div class="col-md-3 mb-3">
                    <label class="form-label">เลือกปี ค.ศ.</label>
                    <select class="form-select" id="yearSelect" name="year">
                      <!-- JS populate -->
                    </select>
                  </div>
                  <div class="col-md-3 mb-3">
                    <label class="form-label">จังหวัด</label>
                    <select class="form-select" name="province">
                      <option value="">ทั้งหมด</option>
                      <?php foreach ($provinces as $p): ?>
                        <option value="<?= htmlspecialchars($p['PR_CODE']) ?>"
                          <?= $p['PR_CODE'] === $prCodeFilter ? 'selected' : '' ?>>
                          <?= htmlspecialchars($p['PR_NAME']) ?>
                        </option>
                      <?php endforeach; ?>
                    </select>
                  </div>
                  <div class="col-md-3 mb-3">
                    <label class="form-label">สถานะการเช่า</label>
                    <select class="form-select" name="status">
                      <option value="">ทั้งหมด</option>
                      <?php foreach ($statuses as $s): ?>
                        <option value="<?= htmlspecialchars($s['RAP_ID']) ?>"
                          <?= $s['RAP_ID'] === $statusFilter ? 'selected' : '' ?>>
                          <?= htmlspecialchars($s['RAP_NAMESTATUS']) ?>
                        </option>
                      <?php endforeach; ?>
                    </select>
                  </div>
                  <div class="col-md-3 mb-3">
                    <button type="submit" class="btn btn-primary w-100">
                      <i class="bi bi-search me-1"></i> ค้นหารายงาน
                    </button>
                  </div>
                </div>
              </form>
            </div>
          </div>

          <!-- ตารางรายงาน -->
          <div class="card card-outline card-success shadow-sm">
            <div class="card-header">
              <h3 class="card-title mb-0">ตารางรายงานสรุปการจ่ายค่าเช่า ปี ค.ศ. <?= htmlspecialchars($yearStr) ?></h3>
            </div>
            <div class="card-body p-0">
              <div class="table-responsive">
                <table id="reportTable" class="table table-bordered table-hover align-middle mb-0" style="white-space:nowrap;font-size:0.9rem;">
                  <thead class="table-light text-center">
                    <tr>
                      <th style="width:50px;">ลำดับ</th>
                      <th>สถานที่</th>
                      <th>สถานะการเช่า</th>
                      <th>ค่าเช่า</th>
                      <th>ม.ค.</th><th>ก.พ.</th><th>มี.ค.</th><th>เม.ย.</th>
                      <th>พ.ค.</th><th>มิ.ย.</th><th>ก.ค.</th><th>ส.ค.</th>
                      <th>ก.ย.</th><th>ต.ค.</th><th>พ.ย.</th><th>ธ.ค.</th>
                    </tr>
                  </thead>
                  <tbody>
                    <?php if (empty($reportRows)): ?>
                      <tr><td colspan="16" class="text-center py-5 text-muted">ไม่พบข้อมูลสถานที่</td></tr>
                    <?php else: ?>
                      <?php foreach ($reportRows as $row): ?>
                        <tr class="<?= $row['location']['RAP_STATUS'] === '0' ? 'table-active' : '' ?>">
                          <td class="text-center"><?= $row['no'] ?></td>
                          <td><?= htmlspecialchars($row['location']['RAP_NAME']) ?></td>
                          <td class="text-center">
                            <?php if ($row['location']['RAP_NAMESTATUS']): ?>
                              <span class="badge text-bg-success"><?= htmlspecialchars($row['location']['RAP_NAMESTATUS']) ?></span>
                            <?php endif; ?>
                          </td>
                          <td class="text-end text-primary fw-bold">
                            <?= $row['location']['RAP_PRICE'] ? number_format((float)$row['location']['RAP_PRICE'], 0) : '-' ?>
                          </td>
                          <?php foreach ($row['payments'] as $pay): ?>
                            <?php if ($pay !== ''): ?>
                              <td class="text-end text-success fw-bold"><?= htmlspecialchars($pay) ?></td>
                            <?php else: ?>
                              <td class="text-center text-muted">-</td>
                            <?php endif; ?>
                          <?php endforeach; ?>
                        </tr>
                      <?php endforeach; ?>
                    <?php endif; ?>
                  </tbody>
                </table>
              </div>
            </div>
          </div>

        </div>
      </div>
    </main>
    <footer class="app-footer">
      <?php include $tpl . 'backend_footer.php'; ?>
    </footer>
  </div>
  <?php include $tpl . 'backend_js.php'; ?>
  <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
  <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>
  <script>
    document.addEventListener("DOMContentLoaded", function () {
      if (typeof $ !== 'undefined' && $.fn.DataTable) {
        $('#reportTable').DataTable({
          language: { url: "//cdn.datatables.net/plug-ins/1.13.6/i18n/th.json" },
          pageLength: 25,
          lengthMenu: [[25, 50, 100, -1], [25, 50, 100, "ทั้งหมด"]]
        });
      }

      const yearSelect  = document.getElementById('yearSelect');
      const currentYear = new Date().getFullYear();
      const selectedYear = parseInt("<?= (int)$yearStr ?>") || currentYear;
      for (let y = currentYear + 2; y >= 2020; y--) {
        const opt = document.createElement('option');
        opt.value = y; opt.textContent = y;
        if (y === selectedYear) opt.selected = true;
        yearSelect.appendChild(opt);
      }
    });
  </script>
</body>
</html>
