<?php
declare(strict_types=1);

require_once __DIR__ . "/../../config/db.php";
require_once __DIR__ . "/../../config/auth.php";
require_admin();

if (session_status() !== PHP_SESSION_ACTIVE) session_start();

$success = "";
$error = "";
$report = [];
$preview = [];
$preview_header = [];
$preview_meta = [];

function norm_id(string $s): string {
    $s = trim((string)$s);
    // remove hidden spaces, tabs, newlines inside ids
    $s = preg_replace('/\s+/', '', $s);
    return strtoupper($s ?? '');
}

function ensure_dir(string $dir): void {
    if (!is_dir($dir)) @mkdir($dir, 0775, true);
}

function safe_tmp_path(string $baseDir): string {
    ensure_dir($baseDir);
    $name = "master_" . date("Ymd_His") . "_" . bin2hex(random_bytes(6)) . ".csv";
    return rtrim($baseDir, "/\\") . DIRECTORY_SEPARATOR . $name;
}

function csv_read_rows(string $path, int $maxRows = 300000): array {
    $rows = [];
    $fh = fopen($path, 'r');
    if (!$fh) return $rows;

    $i = 0;
    while (($data = fgetcsv($fh)) !== false) {
        $i++;
        if ($i > $maxRows) break;
        $rows[] = $data;
    }
    fclose($fh);
    return $rows;
}

function headers_map(array $header): array {
    $map = [];
    foreach ($header as $idx => $name) {
        $k = strtolower(trim((string)$name));
        $map[$k] = $idx;
    }
    return $map;
}

function getcol(array $row, array $map, string $key): string {
    $key = strtolower($key);
    if (!isset($map[$key])) return '';
    $idx = $map[$key];
    return isset($row[$idx]) ? trim((string)$row[$idx]) : '';
}

$required_headers = [
    'district_id','district_name',
    'block_id','block_name',
    'gp_id','gp_name',
    'school_id','school_name'
    // address, category optional
];

function validate_headers(array $map, array $required): ?string {
    foreach ($required as $h) {
        if (!isset($map[strtolower($h)])) return "Missing header: {$h}";
    }
    return null;
}

/**
 * STEP 1: PREVIEW
 */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($_POST['action'] ?? '') === 'preview') {
    if (!csrf_validate($_POST['csrf'] ?? '')) {
        $error = "Invalid request.";
    } elseif (!isset($_FILES['csv']) || ($_FILES['csv']['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
        $error = "Please upload a CSV file.";
    } else {
        $origName = (string)($_FILES['csv']['name'] ?? '');
        if (!preg_match('/\.csv$/i', $origName)) {
            $error = "Only .csv file allowed.";
        } else {
            $cleanup_mode = (string)($_POST['cleanup_mode'] ?? 'none'); // none | gp_refresh
            $tmpUpload = $_FILES['csv']['tmp_name'];
            $storePath = safe_tmp_path(__DIR__ . "/../../storage/imports");

            if (!@move_uploaded_file($tmpUpload, $storePath)) {
                $error = "Failed to store uploaded file. Create writable folder: school-crm/storage/imports";
            } else {
                $rows = csv_read_rows($storePath);
                if (count($rows) < 2) {
                    $error = "CSV seems empty (need header + at least 1 row).";
                } else {
                    $header = $rows[0];
                    $map = headers_map($header);
                    $err = validate_headers($map, $required_headers);
                    if ($err) {
                        $error = $err;
                    } else {
                        $_SESSION['master_import'] = [
                            'path' => $storePath,
                            'cleanup_mode' => $cleanup_mode,
                            'uploaded_name' => $origName
                        ];

                        $preview_header = $header;
                        $max = min(20, count($rows) - 1);
                        for ($i = 1; $i <= $max; $i++) $preview[] = $rows[$i];

                        $preview_meta = [
                            'rows_total' => count($rows) - 1,
                            'file' => $origName,
                            'cleanup_mode' => $cleanup_mode
                        ];
                    }
                }
            }
        }
    }
}

/**
 * STEP 2: IMPORT (4-Pass: Districts -> Blocks -> GPs -> Schools)
 */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($_POST['action'] ?? '') === 'import') {
    if (!csrf_validate($_POST['csrf'] ?? '')) {
        $error = "Invalid request.";
    } else {
        $state = $_SESSION['master_import'] ?? null;
        if (!$state || empty($state['path']) || !is_file((string)$state['path'])) {
            $error = "No preview file found. Please upload again.";
        } else {
            $path = (string)$state['path'];
            $cleanup_mode = (string)($state['cleanup_mode'] ?? 'none');

            $rows = csv_read_rows($path);
            $header = $rows[0] ?? [];
            $map = headers_map($header);

            $err = validate_headers($map, $required_headers);
            if ($err) {
                $error = $err;
            } else {
                $inserted = 0; $updated = 0; $skipped = 0; $errors = 0;
                $report = [];

                try {
                    $pdo->beginTransaction();

                    // UPSERT statements
                    $stUpDistrict = $pdo->prepare("
                      INSERT INTO districts (id, name)
                      VALUES (?, ?)
                      ON DUPLICATE KEY UPDATE name=VALUES(name)
                    ");
                    $stUpBlock = $pdo->prepare("
                      INSERT INTO blocks (id, district_id, name)
                      VALUES (?, ?, ?)
                      ON DUPLICATE KEY UPDATE district_id=VALUES(district_id), name=VALUES(name)
                    ");
                    $stUpGP = $pdo->prepare("
                      INSERT INTO gps (id, block_id, name)
                      VALUES (?, ?, ?)
                      ON DUPLICATE KEY UPDATE block_id=VALUES(block_id), name=VALUES(name)
                    ");
                    $stUpSchool = $pdo->prepare("
                      INSERT INTO schools (id, gp_id, name, address, category)
                      VALUES (?, ?, ?, ?, ?)
                      ON DUPLICATE KEY UPDATE
                        gp_id=VALUES(gp_id),
                        name=VALUES(name),
                        address=VALUES(address),
                        category=VALUES(category)
                    ");

                    // Exists checks (for counting)
                    $stExSchool = $pdo->prepare("SELECT 1 FROM schools WHERE id=? LIMIT 1");

                    // 1) COLLECT unique entities
                    $D = []; $B = []; $G = []; $S = [];

                    for ($i = 1; $i < count($rows); $i++) {
                        $r = $rows[$i];

                        // skip empty
                        if (!array_filter($r, fn($v)=>trim((string)$v)!=='')) { $skipped++; continue; }

                        $did = norm_id(getcol($r,$map,'district_id'));
                        $dname = getcol($r,$map,'district_name');

                        $bid = norm_id(getcol($r,$map,'block_id'));
                        $bname = getcol($r,$map,'block_name');

                        $gid = norm_id(getcol($r,$map,'gp_id'));
                        $gname = getcol($r,$map,'gp_name');

                        $sid = trim(getcol($r,$map,'school_id'));
                        $sname = getcol($r,$map,'school_name');

                        $addr = getcol($r,$map,'address');
                        $cat  = getcol($r,$map,'category');

                        if ($did==='' || $bid==='' || $gid==='' || $sid==='' || $sname==='') {
                            $errors++;
                            $report[] = "Row {$i}: missing required id/name fields (district_id/block_id/gp_id/school_id/school_name)";
                            continue;
                        }

                        $D[$did] = ($dname !== '' ? $dname : $did);
                        $B[$bid] = ['did'=>$did, 'name'=>($bname !== '' ? $bname : $bid)];
                        $G[$gid] = ['bid'=>$bid, 'name'=>($gname !== '' ? $gname : $gid)];
                        $S[$sid] = ['gid'=>$gid, 'name'=>$sname, 'addr'=>$addr, 'cat'=>$cat];
                    }

                    // 2) PASS 1: Districts
                    foreach ($D as $did => $dname) {
                        $stUpDistrict->execute([$did, $dname]);
                    }

                    // 3) PASS 2: Blocks (FK safe now)
                    foreach ($B as $bid => $v) {
                        $stUpBlock->execute([$bid, $v['did'], $v['name']]);
                    }

                    // 4) PASS 3: GPs
                    foreach ($G as $gid => $v) {
                        $stUpGP->execute([$gid, $v['bid'], $v['name']]);
                    }

                    // 5) Cleanup mode (optional) - delete schools under GPs in CSV
                    if ($cleanup_mode === 'gp_refresh') {
                        $gpIds = array_keys($G);
                        if ($gpIds) {
                            $in = implode(',', array_fill(0, count($gpIds), '?'));
                            $stDel = $pdo->prepare("DELETE FROM schools WHERE gp_id IN ($in)");
                            $stDel->execute($gpIds);
                        }
                    }

                    // 6) PASS 4: Schools (Upsert)
                    foreach ($S as $sid => $v) {
                        $stExSchool->execute([$sid]);
                        $was = (bool)$stExSchool->fetchColumn();

                        $stUpSchool->execute([
                            $sid,
                            $v['gid'],
                            $v['name'],
                            ($v['addr'] ?? '') !== '' ? $v['addr'] : null,
                            ($v['cat'] ?? '')  !== '' ? $v['cat']  : null
                        ]);

                        $was ? $updated++ : $inserted++;
                    }

                    $pdo->commit();
                    $success = "Master Import done ✅ Schools Inserted: {$inserted}, Updated: {$updated}, Skipped: {$skipped}, Errors: {$errors}";

                    // cleanup stored file + session
                    $old = $_SESSION['master_import']['path'] ?? null;
                    unset($_SESSION['master_import']);
                    if ($old && is_file((string)$old)) @unlink((string)$old);

                } catch (Throwable $e) {
                    if ($pdo->inTransaction()) $pdo->rollBack();
                    $error = "Import failed: " . $e->getMessage();
                }
            }
        }
    }
}

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

<div class="d-flex justify-content-between align-items-center mb-3">
  <div>
    <h5 class="mb-0">Master CSV Import (Single File)</h5>
    <div class="text-muted small">District + Block + GP + School → Preview → Confirm</div>
  </div>
  <div class="d-flex gap-2">
    <a class="btn btn-outline-dark btn-sm" href="/school-crm/public/admin/schools.php">Schools</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; ?>

<div class="card card-soft shadow-soft p-3 p-md-4 mb-3">
  <h6 class="mb-3">Step 1: Upload for Preview</h6>

  <form method="post" enctype="multipart/form-data" class="row g-3">
    <input type="hidden" name="csrf" value="<?= htmlspecialchars(csrf_token()) ?>">
    <input type="hidden" name="action" value="preview">

    <div class="col-12 col-md-5">
      <label class="form-label">CSV File (Master)</label>
      <input class="form-control" type="file" name="csv" accept=".csv" required>
      <div class="form-text">
        Required headers: <?= htmlspecialchars(implode(", ", $required_headers)) ?> (address,category optional)
      </div>
    </div>

    <div class="col-12 col-md-5">
      <label class="form-label">Duplicate Cleanup</label>
      <select class="form-select" name="cleanup_mode">
        <option value="none">None (Upsert Only)</option>
        <option value="gp_refresh">GP-wise Refresh (Delete schools under GPs present in CSV)</option>
      </select>
      <div class="form-text">Use GP-wise refresh when importing the full list for those GPs.</div>
    </div>

    <div class="col-12 col-md-2 d-grid d-md-flex align-items-end">
      <button class="btn btn-dark w-100">Preview</button>
    </div>
  </form>
</div>

<?php if ($preview_meta): ?>
<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">
    <div>
      <h6 class="mb-0">Step 2: Preview</h6>
      <div class="text-muted small">
        File: <?= htmlspecialchars((string)$preview_meta['file']) ?> •
        Rows: <?= (int)$preview_meta['rows_total'] ?> •
        Cleanup: <?= htmlspecialchars((string)$preview_meta['cleanup_mode']) ?>
      </div>
    </div>

    <form method="post" class="d-grid d-md-flex">
      <input type="hidden" name="csrf" value="<?= htmlspecialchars(csrf_token()) ?>">
      <input type="hidden" name="action" value="import">
      <button class="btn btn-success px-4" onclick="return confirm('Confirm master import?');">Confirm Import</button>
    </form>
  </div>

  <div class="table-responsive">
    <table class="table table-sm table-striped align-middle mb-0">
      <thead>
        <tr>
          <?php foreach ($preview_header as $h): ?>
            <th><?= htmlspecialchars((string)$h) ?></th>
          <?php endforeach; ?>
        </tr>
      </thead>
      <tbody>
        <?php foreach ($preview as $r): ?>
          <tr>
            <?php foreach ($r as $c): ?>
              <td><?= htmlspecialchars((string)$c) ?></td>
            <?php endforeach; ?>
          </tr>
        <?php endforeach; ?>
      </tbody>
    </table>
  </div>

  <div class="text-muted small mt-2">Showing first 20 rows only.</div>
</div>
<?php endif; ?>

<?php if (!empty($report)): ?>
<div class="card card-soft shadow-soft p-3 p-md-4 mt-3">
  <h6 class="mb-2">Row Errors (first 30)</h6>
  <div class="alert alert-warning mb-0">
    <ul class="mb-0">
      <?php foreach (array_slice($report, 0, 30) as $line): ?>
        <li><?= htmlspecialchars($line) ?></li>
      <?php endforeach; ?>
    </ul>
  </div>
</div>
<?php endif; ?>

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