<?php
require_once __DIR__ . '/../../includes/init.php';
middleware(['super_admin']);

$db = getDBConnection();

// ── XML namespace stripper (handles x14ac:dyDescent etc.) ─────
function emrsStripNs($xml) {
    // Remove XML declaration
    $xml = preg_replace('/<\?xml[^?]*\?>\s*/i', '', $xml);
    // Remove namespace declarations (xmlns:xx="..." and xmlns="...")
    $xml = preg_replace('/\s+xmlns(?::[a-zA-Z0-9_]+)?="[^"]*"/i', '', $xml);
    // Remove namespaced attributes e.g. x14ac:dyDescent="0.35" mc:Ignorable="..." xr:uid="..."
    $xml = preg_replace('/\s+[a-zA-Z][a-zA-Z0-9_]*:[a-zA-Z][a-zA-Z0-9_]*="[^"]*"/i', '', $xml);
    // Remove namespace prefixes from opening tags: <mc:Tag → <Tag
    $xml = preg_replace('/<([a-zA-Z][a-zA-Z0-9_]*):([ a-zA-Z])/', '<$2', $xml);
    // Remove namespace prefixes from closing tags: </mc:Tag> → </Tag>
    $xml = preg_replace('/<\/[a-zA-Z][a-zA-Z0-9_]*:/', '</', $xml);
    return $xml;
}

// ── Pure-PHP XLSX reader ──────────────────────────────────────
function emrsReadXlsx($path) {
    if (!class_exists('ZipArchive')) return null;
    $zip = new ZipArchive;
    if ($zip->open($path) !== true) return null;

    // Shared strings
    $shared = array();
    $ssRaw  = $zip->getFromName('xl/sharedStrings.xml');
    if ($ssRaw) {
        $ss = @simplexml_load_string(emrsStripNs($ssRaw));
        if ($ss) {
            foreach ($ss->si as $si) {
                $txt = '';
                if (isset($si->r)) { foreach ($si->r as $r) $txt .= (string)$r->t; }
                elseif (isset($si->t)) { $txt = (string)$si->t; }
                $shared[] = $txt;
            }
        }
    }

    // First sheet
    $sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');
    $zip->close();
    if (!$sheetXml) return null;

    $sheet = @simplexml_load_string(emrsStripNs($sheetXml));
    if (!$sheet || !isset($sheet->sheetData)) return null;

    $rows = array();
    $maxC = 0;

    foreach ($sheet->sheetData->row as $row) {
        $rn    = (int)$row['r'] - 1;
        $cells = array();
        foreach ($row->c as $cell) {
            $ref = (string)$cell['r'];
            if (!preg_match('/^([A-Z]+)(\d+)$/', $ref, $m)) continue;
            $ci = 0;
            for ($i = 0; $i < strlen($m[1]); $i++) $ci = $ci * 26 + (ord($m[1][$i]) - 64);
            $ci--;
            $t = (string)$cell['t'];
            $v = isset($cell->v) ? (string)$cell->v : '';
            $cells[$ci] = ($t === 's') ? (isset($shared[(int)$v]) ? $shared[(int)$v] : '') : $v;
            $maxC = max($maxC, $ci);
        }
        $filled = array();
        for ($c = 0; $c <= $maxC; $c++) $filled[] = isset($cells[$c]) ? trim($cells[$c]) : '';
        $rows[$rn] = $filled;
    }
    return array_values($rows);
}

// ── Detect header row index ───────────────────────────────────
function detectHeaderRow($rows) {
    foreach ($rows as $i => $row) {
        foreach ($row as $cell) {
            $cell = strtolower(trim($cell));
            if (strpos($cell, 'consumer') !== false || strpos($cell, 'meter') !== false) {
                return $i;
            }
        }
    }
    return 0;
}

// ── Division map ──────────────────────────────────────────────
$allDivisions = array(
    'ROORKEE_SD-I', 'ROORKEE_SD-II', 'EDSD DHANORI',
    'EDSD-I RAMNAGAR ROORKEE', 'EDSD-II RAMNAGAR ROORKEE', 'EDSD HIMALAYAN',
    'MANGLORE', 'EDSD JHEBREDA', 'LANDHAURA',
);

// Clear session if user wants fresh start
if (isset($_GET['new']) || isset($_GET['fresh'])) {
    if (!empty($_SESSION['_import_tmp'])) @unlink($_SESSION['_import_tmp']);
    unset($_SESSION['_import_tmp'], $_SESSION['_import_hdridx'], $_SESSION['_import_headers']);
    header('Location: import_consumers.php'); exit;
}

$step     = 'upload';
$error    = '';
$info     = '';
$preview  = array();
$headers  = array();
$tmpPath  = '';
$autoDiv  = '';
$totalRows = 0;

// ── STEP 1 → 2 : File uploaded ────────────────────────────────
if ($_SERVER['REQUEST_METHOD'] === 'POST' && !empty($_FILES['xlsxfile']['tmp_name'])) {
    if (!validateCsrfToken($_POST['csrf_token'] ?? '')) {
        $error = 'Invalid request.';
    } elseif ($_FILES['xlsxfile']['error'] !== UPLOAD_ERR_OK) {
        $error = 'Upload failed (error code ' . $_FILES['xlsxfile']['error'] . ').';
    } else {
        $ext = strtolower(pathinfo($_FILES['xlsxfile']['name'], PATHINFO_EXTENSION));
        if ($ext !== 'xlsx') { $error = 'Only .xlsx files supported.'; }
        else {
            $tmpPath = sys_get_temp_dir() . DIRECTORY_SEPARATOR . 'emrs_imp_' . session_id() . '.xlsx';
            move_uploaded_file($_FILES['xlsxfile']['tmp_name'], $tmpPath);
            $rows = emrsReadXlsx($tmpPath);

            if (!$rows || count($rows) < 3) {
                $error = 'Could not read file or file is empty. Make sure it is a valid .xlsx file.';
                @unlink($tmpPath);
            } else {
                $hdrIdx  = detectHeaderRow($rows);
                $headers = $rows[$hdrIdx];

                // Auto-detect division from row 2 style (Office: DIVISION_NAME)
                foreach (array_slice($rows, 0, 5) as $r) {
                    $cell = implode(' ', $r);
                    foreach ($allDivisions as $d) {
                        if (stripos($cell, $d) !== false) { $autoDiv = $d; break 2; }
                    }
                }

                // Count valid data rows — works for both UPCL format (numeric S.No) and master format
                $totalRows = 0;
                for ($i = $hdrIdx + 1; $i < count($rows); $i++) {
                    $nonEmpty = count(array_filter($rows[$i], function($c){ return trim((string)$c) !== ''; }));
                    if ($nonEmpty >= 3) $totalRows++;
                }

                // Preview: first 8 data rows
                $pCount = 0;
                for ($i = $hdrIdx + 1; $i < count($rows) && $pCount < 8; $i++) {
                    $nonEmpty = count(array_filter($rows[$i], function($c){ return trim((string)$c) !== ''; }));
                    if ($nonEmpty >= 3) { $preview[] = $rows[$i]; $pCount++; }
                }

                // Store context in session
                $_SESSION['_import_tmp']    = $tmpPath;
                $_SESSION['_import_hdridx'] = $hdrIdx;
                $_SESSION['_import_headers']= $headers;
                $step = 'map';
            }
        }
    }
}

// ── STEP 2 → 3 : Do import ───────────────────────────────────
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['do_import'])) {
    if (!validateCsrfToken($_POST['csrf_token'] ?? '')) { $error = 'Invalid request.'; }
    else {
        $tmpPath  = $_SESSION['_import_tmp']    ?? '';
        $hdrIdx   = (int)($_SESSION['_import_hdridx'] ?? 0);
        $headers  = $_SESSION['_import_headers'] ?? array();

        $colName   = (int)$_POST['col_name'];
        $colMeter  = (int)$_POST['col_meter'];
        $colConn   = isset($_POST['col_conn'])    && $_POST['col_conn']    !== '' ? (int)$_POST['col_conn']    : null;
        $colPhone  = isset($_POST['col_phone'])   && $_POST['col_phone']   !== '' ? (int)$_POST['col_phone']   : null;
        $colAddr   = isset($_POST['col_addr'])    && $_POST['col_addr']    !== '' ? (int)$_POST['col_addr']    : null;
        $colDiv    = isset($_POST['col_division'])&& $_POST['col_division']!== '' ? (int)$_POST['col_division']: null;
        $colType   = isset($_POST['col_type'])    && $_POST['col_type']    !== '' ? (int)$_POST['col_type']    : null;
        $defDiv    = trim($_POST['def_division'] ?? '');
        $defType   = $_POST['def_type']   ?? 'solar';
        $defActive = (int)($_POST['def_active'] ?? 1);
        $dupAction = $_POST['dup_action']  ?? 'skip'; // skip | update

        $rows = emrsReadXlsx($tmpPath);

        if (!$rows) { $error = 'Could not re-read file.'; }
        else {
            $inserted = 0; $updated = 0; $skipped = 0; $errored = 0;

            $db->beginTransaction();
            try {
                for ($i = $hdrIdx + 1; $i < count($rows); $i++) {
                    $row = $rows[$i];
                    // Skip rows with fewer than 3 non-empty cells (blank/footer rows)
                    $nonEmpty = count(array_filter($row, function($c){ return trim((string)$c) !== ''; }));
                    if ($nonEmpty < 3) continue;

                    $name  = trim($row[$colName]  ?? '');
                    $meter = trim($row[$colMeter] ?? '');
                    if (!$name || !$meter) { $errored++; continue; }

                    $conn  = $colConn  !== null ? trim($row[$colConn]  ?? '') : null;
                    $phone = $colPhone !== null ? trim($row[$colPhone] ?? '') : null;
                    $addr  = $colAddr  !== null ? trim($row[$colAddr]  ?? '') : null;
                    // Per-row division: use mapped column if set, else fallback to default
                    $rowDiv  = $colDiv  !== null ? trim($row[$colDiv]  ?? '') : '';
                    $rowType = $colType !== null ? trim($row[$colType] ?? '') : '';
                    $div  = $rowDiv  ?: ($defDiv  ?: null);
                    $type = $rowType ?: ($defType ?: 'solar');
                    // Clean phone
                    if ($phone) { $phone = preg_replace('/\D/', '', $phone); $phone = strlen($phone) >= 10 ? $phone : null; }

                    $exists = (int)safeDbVal($db, "SELECT COUNT(*) FROM consumers WHERE meter_number=?", array($meter));

                    if ($exists) {
                        if ($dupAction === 'skip') { $skipped++; continue; }
                        $db->prepare("UPDATE consumers SET consumer_name=?,connection_number=?,phone=?,address=?,division=?,type=?,is_active=? WHERE meter_number=?")
                           ->execute(array($name,$conn?:null,$phone,$addr?:null,$div,$type,$defActive,$meter));
                        $updated++;
                    } else {
                        $db->prepare("INSERT INTO consumers (consumer_name,meter_number,connection_number,phone,address,division,type,is_active) VALUES (?,?,?,?,?,?,?,?)")
                           ->execute(array($name,$meter,$conn?:null,$phone,$addr?:null,$div,$type,$defActive));
                        $inserted++;
                    }
                }
                $db->commit();
                @unlink($tmpPath);
                unset($_SESSION['_import_tmp'],$_SESSION['_import_hdridx'],$_SESSION['_import_headers']);
                $step = 'done';
            } catch (PDOException $e) {
                $db->rollBack();
                $error = 'Database error: ' . $e->getMessage();
                $step  = 'upload';
            }
        }
    }
}

// ── Restore map step from session ────────────────────────────
if ($step === 'upload' && !$error && !empty($_SESSION['_import_tmp']) && file_exists($_SESSION['_import_tmp'])) {
    $tmpPath  = $_SESSION['_import_tmp'];
    $hdrIdx   = (int)$_SESSION['_import_hdridx'];
    $headers  = $_SESSION['_import_headers'];
    $rows     = emrsReadXlsx($tmpPath);
    foreach ($allDivisions as $d) {
        foreach (array_slice($rows ?? array(), 0, 5) as $r) {
            if (stripos(implode(' ', $r), $d) !== false) { $autoDiv = $d; break 2; }
        }
    }
    $totalRows = 0;
    for ($i = $hdrIdx + 1; $i < count($rows ?? array()); $i++) {
        if (isset($rows[$i][0]) && is_numeric($rows[$i][0])) $totalRows++;
    }
    $pCount = 0;
    for ($i = $hdrIdx + 1; $i < count($rows ?? array()) && $pCount < 8; $i++) {
        if (isset($rows[$i][0]) && is_numeric($rows[$i][0])) { $preview[] = $rows[$i]; $pCount++; }
    }
    $step = 'map';
}

// ── Auto-guess column indexes from headers ────────────────────
function guessCol($headers, $keywords) {
    foreach ($headers as $i => $h) {
        foreach ($keywords as $kw) {
            if (stripos($h, $kw) !== false) return $i;
        }
    }
    return '';
}
$guessName  = guessCol($headers, array('consumer_name','name','consumer name'));
$guessMeter = guessCol($headers, array('meter_number','meter no','meter'));
$guessConn  = guessCol($headers, array('connection_number','consumer number','connection','account','sc no'));
$guessPhone = guessCol($headers, array('phone','mobile','contact'));
$guessAddr  = guessCol($headers, array('address','addr'));
$guessDiv   = guessCol($headers, array('division','sub division','sub_division','subdiv'));
$guessType  = guessCol($headers, array('type','meter type'));

$pageTitle = 'Import Consumers';
include __DIR__ . '/../../includes/header.php';
?>

<div class="page-header">
    <div>
        <h1><i class="bi bi-cloud-upload-fill me-2 text-success"></i>Import Consumers</h1>
        <nav aria-label="breadcrumb"><ol class="breadcrumb mb-0" style="font-size:.8rem;">
            <li class="breadcrumb-item"><a href="<?= BASE_URL ?>/modules/admin/consumers.php">Consumers</a></li>
            <li class="breadcrumb-item active">Import</li>
        </ol></nav>
    </div>
</div>

<?php if ($error): ?>
<div class="alert alert-danger d-flex gap-2"><i class="bi bi-exclamation-circle-fill"></i><?= h($error) ?></div>
<?php endif; ?>

<?php if ($step === 'done'): ?>
<!-- ═══ DONE ═══ -->
<div class="erp-card text-center py-5">
    <i class="bi bi-check-circle-fill text-success" style="font-size:3rem;"></i>
    <h4 class="mt-3 fw-bold">Import Complete!</h4>
    <div class="row g-3 justify-content-center mt-2" style="max-width:400px;margin:0 auto;">
        <div class="col-4">
            <div style="background:#dcfce7;border-radius:10px;padding:12px;">
                <div style="font-size:1.6rem;font-weight:800;color:#15803d;"><?= $inserted ?></div>
                <div style="font-size:.74rem;color:#15803d;">Inserted</div>
            </div>
        </div>
        <div class="col-4">
            <div style="background:#dbeafe;border-radius:10px;padding:12px;">
                <div style="font-size:1.6rem;font-weight:800;color:#1d4ed8;"><?= $updated ?></div>
                <div style="font-size:.74rem;color:#1d4ed8;">Updated</div>
            </div>
        </div>
        <div class="col-4">
            <div style="background:#f3f4f6;border-radius:10px;padding:12px;">
                <div style="font-size:1.6rem;font-weight:800;color:#6b7280;"><?= $skipped ?></div>
                <div style="font-size:.74rem;color:#6b7280;">Skipped</div>
            </div>
        </div>
        <?php if ($errored > 0): ?>
        <div class="col-4">
            <div style="background:#fee2e2;border-radius:10px;padding:12px;">
                <div style="font-size:1.6rem;font-weight:800;color:#dc2626;"><?= $errored ?></div>
                <div style="font-size:.74rem;color:#dc2626;">Errors</div>
            </div>
        </div>
        <?php endif; ?>
    </div>
    <div class="mt-4 d-flex gap-2 justify-content-center">
        <a href="<?= BASE_URL ?>/modules/admin/consumers.php" class="btn btn-erp">
            <i class="bi bi-people-fill me-2"></i>View Consumers
        </a>
        <a href="?" class="btn btn-outline-secondary">Import Another</a>
    </div>
</div>

<?php elseif ($step === 'map'): ?>
<!-- ═══ MAP COLUMNS ═══ -->
<form method="POST">
    <input type="hidden" name="csrf_token" value="<?= generateCsrfToken() ?>">
    <input type="hidden" name="do_import" value="1">

    <!-- Column Mapping -->
    <div class="erp-card mb-3">
        <h6 class="fw-bold mb-3"><i class="bi bi-table me-2 text-primary"></i>Map Excel Columns</h6>
        <p class="text-muted" style="font-size:.83rem;">
            File has <strong><?= $totalRows ?></strong> consumer records.
            <?php if ($autoDiv): ?>
            Detected division: <strong><?= h($autoDiv) ?></strong>
            <?php endif; ?>
        </p>

        <div class="row g-3">
            <div class="col-6 col-md-4">
                <label class="form-label fw-semibold" style="font-size:.82rem;">Consumer Name <span class="text-danger">*</span></label>
                <select name="col_name" class="form-select form-select-sm" required>
                    <?php foreach ($headers as $i => $h): if (!$h) continue; ?>
                    <option value="<?= $i ?>" <?= $i==$guessName?'selected':'' ?>><?= h($h) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-6 col-md-4">
                <label class="form-label fw-semibold" style="font-size:.82rem;">Meter Number <span class="text-danger">*</span></label>
                <select name="col_meter" class="form-select form-select-sm" required>
                    <?php foreach ($headers as $i => $h): if (!$h) continue; ?>
                    <option value="<?= $i ?>" <?= $i==$guessMeter?'selected':'' ?>><?= h($h) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-6 col-md-4">
                <label class="form-label fw-semibold" style="font-size:.82rem;">Connection Number</label>
                <select name="col_conn" class="form-select form-select-sm">
                    <option value="">-- Skip --</option>
                    <?php foreach ($headers as $i => $h): if (!$h) continue; ?>
                    <option value="<?= $i ?>" <?= $i==$guessConn?'selected':'' ?>><?= h($h) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-6 col-md-4">
                <label class="form-label fw-semibold" style="font-size:.82rem;">Phone</label>
                <select name="col_phone" class="form-select form-select-sm">
                    <option value="">-- Skip --</option>
                    <?php foreach ($headers as $i => $h): if (!$h) continue; ?>
                    <option value="<?= $i ?>" <?= $i==$guessPhone?'selected':'' ?>><?= h($h) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-6 col-md-4">
                <label class="form-label fw-semibold" style="font-size:.82rem;">Address</label>
                <select name="col_addr" class="form-select form-select-sm">
                    <option value="">-- Skip --</option>
                    <?php foreach ($headers as $i => $h): if (!$h) continue; ?>
                    <option value="<?= $i ?>" <?= $i==$guessAddr?'selected':'' ?>><?= h($h) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-6 col-md-4">
                <label class="form-label fw-semibold" style="font-size:.82rem;">
                    Division (from file)
                    <?php if ($guessDiv !== ''): ?>
                    <span class="badge bg-success ms-1" style="font-size:.65rem;">Auto</span>
                    <?php endif; ?>
                </label>
                <select name="col_division" class="form-select form-select-sm">
                    <option value="">-- Use Default Below --</option>
                    <?php foreach ($headers as $i => $h): if (!$h) continue; ?>
                    <option value="<?= $i ?>" <?= $i==$guessDiv?'selected':'' ?>><?= h($h) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-6 col-md-4">
                <label class="form-label fw-semibold" style="font-size:.82rem;">
                    Type (from file)
                    <?php if ($guessType !== ''): ?>
                    <span class="badge bg-success ms-1" style="font-size:.65rem;">Auto</span>
                    <?php endif; ?>
                </label>
                <select name="col_type" class="form-select form-select-sm">
                    <option value="">-- Use Default Below --</option>
                    <?php foreach ($headers as $i => $h): if (!$h) continue; ?>
                    <option value="<?= $i ?>" <?= $i==$guessType?'selected':'' ?>><?= h($h) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
        </div>
    </div>

    <!-- Default Values -->
    <div class="erp-card mb-3">
        <h6 class="fw-bold mb-3"><i class="bi bi-sliders me-2 text-warning"></i>Default Values for All Rows</h6>
        <div class="row g-3">

            <!-- Step 1: Parent Division -->
            <div class="col-6 col-md-3">
                <label class="form-label fw-semibold" style="font-size:.82rem;">
                    Division <span class="text-danger">*</span>
                </label>
                <select id="selParent" class="form-select form-select-sm" onchange="filterSubDiv(this.value)" required>
                    <option value="">-- Select Division --</option>
                    <?php foreach (array_keys($divisionMap) as $parent): ?>
                    <option value="<?= h($parent) ?>"><?= h($parent) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>

            <!-- Step 2: Sub-Division (filtered) -->
            <div class="col-6 col-md-3">
                <label class="form-label fw-semibold" style="font-size:.82rem;">
                    Sub-Division <span class="text-danger">*</span>
                </label>
                <select name="def_division" id="selSubDiv" class="form-select form-select-sm" required>
                    <option value="">-- Select Division First --</option>
                </select>
            </div>
            <div class="col-6 col-md-3">
                <label class="form-label fw-semibold" style="font-size:.82rem;">Type</label>
                <select name="def_type" class="form-select form-select-sm">
                    <option value="solar" selected>Solar</option>
                    <option value="kcc">KCC</option>
                    <option value="both">Both</option>
                </select>
            </div>
            <div class="col-6 col-md-3">
                <label class="form-label fw-semibold" style="font-size:.82rem;">Status</label>
                <select name="def_active" class="form-select form-select-sm">
                    <option value="1" selected>Active</option>
                    <option value="0">Inactive</option>
                </select>
            </div>
            <div class="col-6 col-md-3">
                <label class="form-label fw-semibold" style="font-size:.82rem;">If Meter Already Exists</label>
                <select name="dup_action" class="form-select form-select-sm">
                    <option value="skip" selected>Skip (keep existing)</option>
                    <option value="update">Update existing</option>
                </select>
            </div>
        </div>
    </div>

    <!-- Preview -->
    <div class="erp-card mb-3">
        <h6 class="fw-bold mb-2"><i class="bi bi-eye me-2 text-info"></i>Preview (first 8 rows)</h6>
        <div class="table-responsive">
            <table class="table table-sm table-hover mb-0" style="font-size:.77rem;">
                <thead class="table-light">
                    <tr>
                        <?php foreach ($headers as $h): if (!$h) continue; ?>
                        <th><?= h($h) ?></th>
                        <?php endforeach; ?>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($preview as $row): ?>
                    <tr>
                        <?php foreach ($headers as $i => $h): if (!$h) continue; ?>
                        <td><?= h($row[$i] ?? '') ?></td>
                        <?php endforeach; ?>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>

    <div class="d-flex gap-2">
        <button type="submit" class="btn btn-erp flex-fill py-3" style="font-size:1rem;">
            <i class="bi bi-cloud-upload-fill me-2"></i>Import <?= number_format($totalRows) ?> Consumers
        </button>
        <a href="?" class="btn btn-outline-secondary">Cancel</a>
    </div>
</form>

<?php else: ?>
<!-- ═══ UPLOAD ═══ -->
<div class="erp-card" style="max-width:520px;">
    <h6 class="fw-bold mb-1">Upload XLSX File</h6>
    <p class="text-muted mb-3" style="font-size:.83rem;">UPCL consumer report format (Non-TOD Unbilled Consumers) supported.</p>

    <form method="POST" enctype="multipart/form-data">
        <input type="hidden" name="csrf_token" value="<?= generateCsrfToken() ?>">
        <div class="mb-3">
            <label class="form-label fw-semibold">Choose Excel File (.xlsx)</label>
            <input type="file" name="xlsxfile" class="form-control" accept=".xlsx" required>
            <div class="text-muted mt-1" style="font-size:.76rem;">Max size: 10MB. First row with "Name / Meter" will be auto-detected as header.</div>
        </div>
        <button type="submit" class="btn btn-erp w-100">
            <i class="bi bi-upload me-2"></i>Upload &amp; Preview
        </button>
    </form>

    <hr class="my-3">
    <div style="font-size:.78rem;color:#6b7280;">
        <strong>Expected UPCL format columns:</strong><br>
        S.No · Book No · Bill Grp · <strong>Consumer Number</strong> · <strong>Name</strong> · <strong>Address</strong> · <strong>Phone No</strong> · Billing Status · Load · Last Bill Date · <strong>Meter No</strong> · ...
    </div>
</div>
<?php endif; ?>

<script>
// Division → Sub-Division map
const divMap = <?php echo json_encode($divisionMap); ?>;

function filterSubDiv(parent) {
    const sel  = document.getElementById('selSubDiv');
    sel.innerHTML = '';

    if (!parent || !divMap[parent]) {
        sel.innerHTML = '<option value="">-- Select Division First --</option>';
        return;
    }

    sel.innerHTML = '<option value="">-- Select Sub-Division --</option>';
    divMap[parent].forEach(function(sub) {
        const opt  = document.createElement('option');
        opt.value  = sub;
        opt.textContent = sub;
        sel.appendChild(opt);
    });

    // Auto-select if only one option
    if (divMap[parent].length === 1) sel.value = divMap[parent][0];
}

// Auto-detect on page load if autoDiv is set
<?php if ($autoDiv): ?>
(function() {
    const parent = <?php echo json_encode(isset($subToParent[$autoDiv]) ? $subToParent[$autoDiv] : ''); ?>;
    const sub    = <?php echo json_encode($autoDiv); ?>;
    if (parent) {
        const pSel = document.getElementById('selParent');
        if (pSel) { pSel.value = parent; filterSubDiv(parent); }
        const sSel = document.getElementById('selSubDiv');
        if (sSel) sSel.value = sub;
    }
})();
<?php endif; ?>
</script>

<?php include __DIR__ . '/../../includes/footer.php'; ?>
