<?php
declare(strict_types=1);

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

require_admin();

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

function norm_id(string $s): string {
    $s = trim((string)$s);
    $s = preg_replace('/\s+/', '', $s);
    return strtoupper($s ?? '');
}

function csv_read_rows(string $path, int $maxRows = 50000): 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]) : '';
}

/**
 * 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 {
            $tmpUpload = $_FILES['csv']['tmp_name'];
            $rows = csv_read_rows($tmpUpload);

            if (count($rows) < 2) {
                $error = "CSV must have at least header + 1 data row.";
            } else {
                $header = array_shift($rows);
                $map = headers_map($header);

                $required_headers = ['school_id', 'gp_id', 'school_name'];
                foreach ($required_headers as $h) {
                    if (!isset($map[strtolower($h)])) {
                        $error = "Missing header: {$h}";
                        break;
                    }
                }

                if ($error === '') {
                    $preview = array_slice($rows, 0, 10);
                }
            }
        }
    }
}

/**
 * STEP 2: CONFIRM & IMPORT
 */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($_POST['action'] ?? '') === 'confirm') {
    if (!csrf_validate($_POST['csrf'] ?? '')) {
        $error = "Invalid request.";
    } elseif (!isset($_FILES['csv']) || ($_FILES['csv']['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
        $error = "CSV file missing.";
    } else {
        $tmpUpload = $_FILES['csv']['tmp_name'];
        $rows = csv_read_rows($tmpUpload);

        if (count($rows) < 2) {
            $error = "CSV is empty.";
        } else {
            $header = array_shift($rows);
            $map = headers_map($header);

            try {
                $pdo->beginTransaction();

                $inserted = 0;
                $duplicates = 0;
                $errors = 0;

                foreach ($rows as $row_idx => $row) {
                    $school_id = norm_id(getcol($row, $map, 'school_id'));
                    $gp_id = norm_id(getcol($row, $map, 'gp_id'));
                    $school_name = getcol($row, $map, 'school_name');
                    $address = getcol($row, $map, 'address') ?: null;
                    $category = getcol($row, $map, 'category') ?: null;

                    if ($school_id === '' || $gp_id === '' || $school_name === '') {
                        $errors++;
                        $report[] = "Row " . ($row_idx + 2) . ": Missing required fields.";
                        continue;
                    }

                    // Check if GP exists
                    $st = $pdo->prepare("SELECT 1 FROM gps WHERE id=? LIMIT 1");
                    $st->execute([$gp_id]);
                    if (!$st->fetchColumn()) {
                        $errors++;
                        $report[] = "Row " . ($row_idx + 2) . ": GP '{$gp_id}' does not exist.";
                        continue;
                    }

                    // Check if school already exists
                    $st = $pdo->prepare("SELECT 1 FROM schools WHERE id=? LIMIT 1");
                    $st->execute([$school_id]);
                    if ($st->fetchColumn()) {
                        $duplicates++;
                        $report[] = "Row " . ($row_idx + 2) . ": School '{$school_id}' already exists (skipped).";
                        continue;
                    }

                    // Insert school
                    try {
                        $st = $pdo->prepare("
                            INSERT INTO schools (id, gp_id, name, address, category)
                            VALUES (?, ?, ?, ?, ?)
                        ");
                        $st->execute([$school_id, $gp_id, $school_name, $address, $category]);
                        $inserted++;
                    } catch (Throwable $e) {
                        $errors++;
                        $report[] = "Row " . ($row_idx + 2) . ": Insert failed - " . $e->getMessage();
                    }
                }

                $pdo->commit();
                $success = "Import complete: {$inserted} inserted, {$duplicates} skipped, {$errors} errors.";

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

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

<div class="d-flex justify-content-between align-items-center mb-3">
  <div>
    <h5 class="mb-0">Import Schools</h5>
    <div class="text-muted small">Upload CSV file with school data</div>
  </div>
  <a class="btn btn-outline-dark btn-sm" href="/school-crm/public/admin/schools.php">View All Schools</a>
</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">CSV Format</h6>
  <p class="small text-muted mb-2">Required columns: <strong>school_id</strong>, <strong>gp_id</strong>, <strong>school_name</strong></p>
  <p class="small text-muted mb-2">Optional columns: <strong>address</strong>, <strong>category</strong></p>
  <p class="small text-muted mb-0">IDs are auto-uppercased. All GPs must exist before import.</p>
</div>

<?php if (empty($preview)): ?>

<div class="card card-soft shadow-soft p-3 p-md-4">
  <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">
      <label class="form-label">Select CSV File *</label>
      <input type="file" class="form-control" name="csv" accept=".csv" required>
    </div>
    <div class="col-12">
      <button type="submit" class="btn btn-dark">Preview</button>
    </div>
  </form>
</div>

<?php else: ?>

<div class="card card-soft shadow-soft p-3 p-md-4 mb-3">
  <h6 class="mb-3">Preview (First 10 rows)</h6>
  <div class="table-responsive">
    <table class="table table-sm mb-0">
      <thead>
        <tr>
          <th>School ID</th>
          <th>GP ID</th>
          <th>School Name</th>
          <th>Address</th>
          <th>Category</th>
        </tr>
      </thead>
      <tbody>
        <?php foreach ($preview as $row): 
          $school_id = isset($map['school_id']) ? trim((string)($row[$map['school_id']] ?? '')) : '';
          $gp_id = isset($map['gp_id']) ? trim((string)($row[$map['gp_id']] ?? '')) : '';
          $school_name = isset($map['school_name']) ? trim((string)($row[$map['school_name']] ?? '')) : '';
          $address = isset($map['address']) ? trim((string)($row[$map['address']] ?? '')) : '';
          $category = isset($map['category']) ? trim((string)($row[$map['category']] ?? '')) : '';
        ?>
        <tr>
          <td><?= htmlspecialchars($school_id) ?></td>
          <td><?= htmlspecialchars($gp_id) ?></td>
          <td><?= htmlspecialchars($school_name) ?></td>
          <td><?= htmlspecialchars($address) ?></td>
          <td><?= htmlspecialchars($category) ?></td>
        </tr>
        <?php endforeach; ?>
      </tbody>
    </table>
  </div>
</div>

<div class="card card-soft shadow-soft p-3 p-md-4">
  <form method="post" enctype="multipart/form-data">
    <input type="hidden" name="csrf" value="<?= htmlspecialchars(csrf_token()) ?>">
    <input type="hidden" name="action" value="confirm">
    <input type="hidden" name="csv" value="">

    <div class="mb-3">
      <label class="form-label">Re-upload file to confirm import:</label>
      <input type="file" class="form-control" name="csv" accept=".csv" required>
    </div>

    <div class="d-flex gap-2">
      <button type="submit" class="btn btn-success">✓ Confirm Import</button>
      <a href="?reload=1" class="btn btn-outline-dark">↻ New Upload</a>
    </div>
  </form>
</div>

<?php endif; ?>

<?php if (!empty($report)): ?>
<div class="card card-soft shadow-soft p-3 mt-3">
  <h6 class="mb-3">Report</h6>
  <div class="small">
    <?php foreach ($report as $msg): ?>
      <div class="text-muted"><?= htmlspecialchars($msg) ?></div>
    <?php endforeach; ?>
  </div>
</div>
<?php endif; ?>

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