<?php
// public/admin/seed_masters.php
// Inserts Districts + ULBs from up_admin_tree.php into SQL tables.
// Safe to run multiple times (uses ON DUPLICATE KEY UPDATE).

declare(strict_types=1);

require __DIR__ . '/../../includes/db.php';

// ---- sanity: make sure unique keys exist on codes (recommended) ----
// Run these once in your DB/migration; kept here as docs:
//
// ALTER TABLE districts ADD UNIQUE KEY uniq_district_code (code);
// ALTER TABLE ulbs      ADD UNIQUE KEY uniq_ulb_code      (code);

header('Content-Type: text/plain; charset=utf-8');

try {
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
  try { $pdo->exec("SET time_zone = '+05:30'"); } catch (Throwable $e) {}

  // Load your nested tree
  $TREE = include __DIR__ . '/up_admin_tree.php';
  if (!is_array($TREE) || empty($TREE['UP']['nodals'])) {
    throw new RuntimeException('up_admin_tree.php structure not as expected.');
  }

  $insDistrict = $pdo->prepare("
    INSERT INTO districts (name, code)
    VALUES (:name, :code)
    ON DUPLICATE KEY UPDATE name = VALUES(name)
  ");

  $selDistrictId = $pdo->prepare("SELECT id FROM districts WHERE code = :code LIMIT 1");

  $insUlb = $pdo->prepare("
    INSERT INTO ulbs (district_id, name, code)
    VALUES (:district_id, :name, :code)
    ON DUPLICATE KEY UPDATE
      name = VALUES(name),
      district_id = VALUES(district_id)
  ");

  $pdo->beginTransaction();

  $districtCount = 0;
  $ulbCount = 0;

  foreach ($TREE['UP']['nodals'] as $nodalName => $nodal) {
    if (empty($nodal['districts']) || !is_array($nodal['districts'])) {
      continue;
    }

    foreach ($nodal['districts'] as $districtName => $districtData) {
      $districtCode = (string)($districtData['code'] ?? '');
      if ($districtName === '' || $districtCode === '') {
        continue; // skip invalid
      }

      // Insert/Update District
      $insDistrict->execute([
        'name' => $districtName,
        'code' => $districtCode,
      ]);
      $districtCount++;

      // Get district_id
      $selDistrictId->execute(['code' => $districtCode]);
      $districtRow = $selDistrictId->fetch();
      if (!$districtRow) {
        throw new RuntimeException("Failed to fetch district_id for code {$districtCode}");
      }
      $districtId = (int)$districtRow['id'];

      // Insert ULBs
      $ulbs = $districtData['ulbs'] ?? [];
      if (is_array($ulbs)) {
        foreach ($ulbs as $u) {
          $ulbName = (string)($u['name'] ?? '');
          $ulbCode = (string)($u['code'] ?? '');
          if ($ulbName === '' || $ulbCode === '') continue;

          $insUlb->execute([
            'district_id' => $districtId,
            'name'        => $ulbName,
            'code'        => $ulbCode,
          ]);
          $ulbCount++;
        }
      }
    }
  }

  $pdo->commit();

  echo "Seeding completed.\n";
  echo "Districts processed: {$districtCount}\n";
  echo "ULBs processed: {$ulbCount}\n";

} catch (Throwable $e) {
  if ($pdo->inTransaction()) { $pdo->rollBack(); }
  http_response_code(500);
  echo "ERROR: " . $e->getMessage() . "\n";
  // Uncomment for debug:
  // echo $e->getTraceAsString();
}
