<?php
declare(strict_types=1);

require_once __DIR__ . "/../../config/db.php";
require_once __DIR__ . "/../../config/auth.php";

require_admin();

$success = "";
$error = "";

/** helpers */
function fetchAll(PDO $pdo, string $sql, array $params = []): array {
    $st = $pdo->prepare($sql);
    $st->execute($params);
    return $st->fetchAll(PDO::FETCH_ASSOC);
}

function post_str(string $k): string {
    return trim((string)($_POST[$k] ?? ''));
}

/**
 * CREATE School
 */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($_POST['action'] ?? '') === 'create') {
    if (!csrf_validate($_POST['csrf'] ?? '')) {
        $error = "Invalid request.";
    } else {
        $school_id = strtoupper(post_str('school_id'));   // You can use your School ID from CSV
        $gp_id     = strtoupper(post_str('gp_id'));
        $name      = post_str('name');
        $address   = post_str('address');
        $category  = post_str('category');

        if ($school_id === '' || $gp_id === '' || $name === '') {
            $error = "School ID, GP and School Name are required.";
        } else {
            // verify gp exists
            $st = $pdo->prepare("SELECT 1 FROM gps WHERE id=? LIMIT 1");
            $st->execute([$gp_id]);
            if (!$st->fetchColumn()) {
                $error = "Invalid GP selected.";
            } else {
                try {
                    $st = $pdo->prepare("
                        INSERT INTO schools (id, gp_id, name, address, category)
                        VALUES (?, ?, ?, ?, ?)
                    ");
                    $st->execute([
                        $school_id,
                        $gp_id,
                        $name,
                        $address !== '' ? $address : null,
                        $category !== '' ? $category : null
                    ]);
                    $success = "School added successfully.";
                } catch (Throwable $e) {
                    $error = "School ID already exists (or invalid data).";
                }
            }
        }
    }
}

/**
 * DELETE School
 */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($_POST['action'] ?? '') === 'delete') {
    if (!csrf_validate($_POST['csrf'] ?? '')) {
        $error = "Invalid request.";
    } else {
        $sid = strtoupper(trim((string)($_POST['id'] ?? '')));
        if ($sid !== '') {
            try {
                $st = $pdo->prepare("DELETE FROM schools WHERE id=?");
                $st->execute([$sid]);
                $success = "School deleted.";
            } catch (Throwable $e) {
                $error = "Cannot delete. This school may be linked with visits.";
            }
        }
    }
}

/**
 * FILTERS (GET)
 */
$district_id = strtoupper(trim((string)($_GET['district_id'] ?? '')));
$block_id    = strtoupper(trim((string)($_GET['block_id'] ?? '')));
$gp_id       = strtoupper(trim((string)($_GET['gp_id'] ?? '')));
$q           = trim((string)($_GET['q'] ?? ''));

$districts = fetchAll($pdo, "SELECT id, name FROM districts ORDER BY name ASC");

$blocks = [];
if ($district_id !== '') {
    $blocks = fetchAll($pdo, "SELECT id, name FROM blocks WHERE district_id=? ORDER BY name ASC", [$district_id]);
}

$gps = [];
if ($block_id !== '') {
    $gps = fetchAll($pdo, "SELECT id, name FROM gps WHERE block_id=? ORDER BY name ASC", [$block_id]);
}

/**
 * LIST Schools
 */
$where = "1=1";
$params = [];

if ($district_id !== '') {
    $where .= " AND b.district_id=?";
    $params[] = $district_id;
}
if ($block_id !== '') {
    $where .= " AND g.block_id=?";
    $params[] = $block_id;
}
if ($gp_id !== '') {
    $where .= " AND s.gp_id=?";
    $params[] = $gp_id;
}
if ($q !== '') {
    $where .= " AND (s.name LIKE ? OR s.id LIKE ?)";
    $params[] = "%{$q}%";
    $params[] = "%{$q}%";
}

$schools = fetchAll($pdo, "
    SELECT
      s.id, s.name, s.address, s.category,
      g.id AS gp_id, g.name AS gp_name,
      b.id AS block_id, b.name AS block_name,
      d.name AS district_name, b.district_id
    FROM schools s
    JOIN gps g ON g.id=s.gp_id
    JOIN blocks b ON b.id=g.block_id
    LEFT JOIN districts d ON d.id=b.district_id
    WHERE {$where}
    ORDER BY d.name ASC, b.name ASC, g.name ASC, s.name ASC
    LIMIT 500
", $params);

$page_title = "Admin - Schools";
require_once __DIR__ . "/../partials/header.php";
?>

<div class="d-flex flex-column flex-md-row justify-content-between align-items-md-center gap-2 mb-3">
  <div>
    <h5 class="mb-0">Schools</h5>
    <div class="text-muted small">Add / Filter / Manage Schools</div>
  </div>
  <div class="d-flex gap-2">
    <a class="btn btn-outline-dark btn-sm" href="/school-crm/public/admin/districts.php">Districts</a>
    <a class="btn btn-outline-dark btn-sm" href="/school-crm/public/admin/blocks.php">Blocks</a>
    <a class="btn btn-outline-dark btn-sm" href="/school-crm/public/admin/gps.php">GPs</a>
  </div>
</div>

<?php if ($success): ?><div class="alert alert-success py-2"><?= htmlspecialchars($success) ?></div><?php endif; ?>
<?php if ($error): ?><div class="alert alert-danger py-2"><?= htmlspecialchars($error) ?></div><?php endif; ?>

<!-- ADD SCHOOL -->
<div class="card card-soft shadow-soft p-3 p-md-4 mb-3">
  <div class="d-flex flex-column flex-md-row justify-content-between align-items-md-center gap-2 mb-2">
    <h6 class="mb-0">Add School</h6>
    <span class="badge badge-soft">District → Block → GP → School</span>
  </div>

  <form method="post" class="row g-3" id="addSchoolForm">
    <input type="hidden" name="csrf" value="<?= htmlspecialchars(csrf_token()) ?>">
    <input type="hidden" name="action" value="create">

    <div class="col-12 col-md-3">
      <label class="form-label">District</label>
      <select class="form-select" id="dSel">
        <option value="">Select District</option>
        <?php foreach ($districts as $d): ?>
          <option value="<?= htmlspecialchars($d['id']) ?>"><?= htmlspecialchars($d['name']) ?> (<?= htmlspecialchars($d['id']) ?>)</option>
        <?php endforeach; ?>
      </select>
      <div class="form-text">For loading Blocks</div>
    </div>

    <div class="col-12 col-md-3">
      <label class="form-label">Block</label>
      <select class="form-select" id="bSel" disabled>
        <option value="">Select Block</option>
      </select>
      <div class="form-text">For loading GPs</div>
    </div>

    <div class="col-12 col-md-3">
      <label class="form-label">GP <span class="text-danger">*</span></label>
      <select class="form-select" name="gp_id" id="gSel" required disabled>
        <option value="">Select GP</option>
      </select>
    </div>

    <div class="col-12 col-md-3">
      <label class="form-label">School ID <span class="text-danger">*</span></label>
      <input class="form-control" name="school_id" placeholder="e.g. 19061104002" required>
      <div class="form-text">Use your dataset School ID</div>
    </div>

    <div class="col-12 col-md-6">
      <label class="form-label">School Name <span class="text-danger">*</span></label>
      <input class="form-control" name="name" required>
    </div>

    <div class="col-12 col-md-6">
      <label class="form-label">Category (Optional)</label>
      <input class="form-control" name="category" placeholder="HS / Primary / ...">
    </div>

    <div class="col-12">
      <label class="form-label">Address (Optional)</label>
      <input class="form-control" name="address">
    </div>

    <div class="col-12 d-grid d-md-flex justify-content-md-end">
      <button class="btn btn-dark px-4">Save School</button>
    </div>
  </form>
</div>

<!-- FILTERS -->
<div class="card card-soft shadow-soft p-3 p-md-4 mb-3">
  <h6 class="mb-3">Filter Schools</h6>

  <form method="get" class="row g-2 align-items-end">
    <div class="col-12 col-md-3">
      <label class="form-label">District</label>
      <select class="form-select" name="district_id" id="fDistrict">
        <option value="">All Districts</option>
        <?php foreach ($districts as $d): ?>
          <option value="<?= htmlspecialchars($d['id']) ?>" <?= ($district_id===$d['id'])?'selected':'' ?>>
            <?= htmlspecialchars($d['name']) ?> (<?= htmlspecialchars($d['id']) ?>)
          </option>
        <?php endforeach; ?>
      </select>
    </div>

    <div class="col-12 col-md-3">
      <label class="form-label">Block</label>
      <select class="form-select" name="block_id" id="fBlock" <?= ($district_id==='')?'disabled':'' ?>>
        <option value="">All Blocks</option>
        <?php foreach ($blocks as $b): ?>
          <option value="<?= htmlspecialchars($b['id']) ?>" <?= ($block_id===$b['id'])?'selected':'' ?>>
            <?= htmlspecialchars($b['name']) ?> (<?= htmlspecialchars($b['id']) ?>)
          </option>
        <?php endforeach; ?>
      </select>
    </div>

    <div class="col-12 col-md-3">
      <label class="form-label">GP</label>
      <select class="form-select" name="gp_id" id="fGP" <?= ($block_id==='')?'disabled':'' ?>>
        <option value="">All GPs</option>
        <?php foreach ($gps as $g): ?>
          <option value="<?= htmlspecialchars($g['id']) ?>" <?= ($gp_id===$g['id'])?'selected':'' ?>>
            <?= htmlspecialchars($g['name']) ?> (<?= htmlspecialchars($g['id']) ?>)
          </option>
        <?php endforeach; ?>
      </select>
    </div>

    <div class="col-12 col-md-2">
      <label class="form-label">Search</label>
      <input class="form-control" name="q" value="<?= htmlspecialchars($q) ?>" placeholder="School name / ID">
    </div>

    <div class="col-12 col-md-1 d-grid">
      <button class="btn btn-outline-dark">Go</button>
    </div>
  </form>

  <div class="text-muted small mt-2">Showing max 500 rows. Use filters/search for faster view.</div>
</div>

<!-- LIST -->
<div class="card card-soft shadow-soft p-3 p-md-4">
  <div class="d-flex flex-column flex-md-row justify-content-between align-items-md-center gap-2 mb-2">
    <h6 class="mb-0">Schools List</h6>
    <span class="text-muted small">Total: <?= count($schools) ?></span>
  </div>

  <div class="table-responsive">
    <table class="table table-striped table-hover align-middle mb-0">
      <thead>
        <tr>
          <th>School ID</th>
          <th>School Name</th>
          <th>District</th>
          <th>Block</th>
          <th>GP</th>
          <th>Category</th>
          <th>Address</th>
          <th class="text-end">Action</th>
        </tr>
      </thead>
      <tbody>
      <?php if (!$schools): ?>
        <tr><td colspan="8" class="text-muted">No schools found.</td></tr>
      <?php endif; ?>

      <?php foreach ($schools as $s): ?>
        <tr>
          <td class="text-nowrap fw-semibold"><?= htmlspecialchars($s['id']) ?></td>
          <td><?= htmlspecialchars($s['name']) ?></td>
          <td><?= htmlspecialchars($s['district_name'] ?? '-') ?></td>
          <td><?= htmlspecialchars($s['block_name']) ?> (<?= htmlspecialchars($s['block_id']) ?>)</td>
          <td><?= htmlspecialchars($s['gp_name']) ?> (<?= htmlspecialchars($s['gp_id']) ?>)</td>
          <td><?= htmlspecialchars($s['category'] ?? '-') ?></td>
          <td style="min-width:220px;"><?= htmlspecialchars($s['address'] ?? '-') ?></td>
          <td class="text-end">
            <form method="post" class="d-inline" onsubmit="return confirm('Delete this school?');">
              <input type="hidden" name="csrf" value="<?= htmlspecialchars(csrf_token()) ?>">
              <input type="hidden" name="action" value="delete">
              <input type="hidden" name="id" value="<?= htmlspecialchars($s['id']) ?>">
              <button class="btn btn-sm btn-outline-danger">Delete</button>
            </form>
          </td>
        </tr>
      <?php endforeach; ?>
      </tbody>
    </table>
  </div>
</div>

<script>
async function loadJSON(url){
  const res = await fetch(url, {credentials:'same-origin'});
  if(!res.ok) return [];
  return await res.json();
}

function setOptions(selectEl, rows, placeholder){
  selectEl.innerHTML = '';
  const o0 = document.createElement('option');
  o0.value = '';
  o0.textContent = placeholder;
  selectEl.appendChild(o0);

  rows.forEach(r=>{
    const o = document.createElement('option');
    o.value = r.id;
    o.textContent = r.name + " (" + r.id + ")";
    selectEl.appendChild(o);
  });
}

function disableSelect(selectEl, placeholder){
  selectEl.disabled = true;
  setOptions(selectEl, [], placeholder);
}

const dSel = document.getElementById('dSel');
const bSel = document.getElementById('bSel');
const gSel = document.getElementById('gSel');

// ADD FORM: District -> Blocks
dSel.addEventListener('change', async ()=>{
  disableSelect(bSel, 'Select Block');
  disableSelect(gSel, 'Select GP');

  const did = dSel.value;
  if(!did) return;

  const blocks = await loadJSON('/school-crm/api/blocks.php?district_id=' + encodeURIComponent(did));
  setOptions(bSel, blocks, 'Select Block');
  bSel.disabled = false;
});

// ADD FORM: Block -> GPs
bSel.addEventListener('change', async ()=>{
  disableSelect(gSel, 'Select GP');

  const bid = bSel.value;
  if(!bid) return;

  const gps = await loadJSON('/school-crm/api/gps.php?block_id=' + encodeURIComponent(bid));
  setOptions(gSel, gps, 'Select GP');
  gSel.disabled = false;
});

// FILTER: district change -> reload page for blocks
document.getElementById('fDistrict').addEventListener('change', ()=>{
  const url = new URL(window.location.href);
  const d = document.getElementById('fDistrict').value;
  if(d) url.searchParams.set('district_id', d); else url.searchParams.delete('district_id');
  url.searchParams.delete('block_id');
  url.searchParams.delete('gp_id');
  window.location.href = url.toString();
});

// FILTER: block change -> reload page for gps
const fBlock = document.getElementById('fBlock');
if (fBlock) {
  fBlock.addEventListener('change', ()=>{
    const url = new URL(window.location.href);
    const b = fBlock.value;
    if(b) url.searchParams.set('block_id', b); else url.searchParams.delete('block_id');
    url.searchParams.delete('gp_id');
    window.location.href = url.toString();
  });
}
</script>

<?php require_once __DIR__ . "/../partials/footer.php"; ?>
