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

$db = getDBConnection();
$result = [];
$done   = false;
$debug  = [];

// ── Read XLSX without library ─────────────────────────────────
function readXlsx($file) {
    $zip = new ZipArchive();
    if ($zip->open($file) !== true) return ['error'=>'Cannot open zip'];

    // Shared strings
    $strings = [];
    $ssXml = $zip->getFromName('xl/sharedStrings.xml');
    if ($ssXml) {
        $xml = simplexml_load_string($ssXml);
        foreach ($xml->si as $si) {
            $t = '';
            if (isset($si->t)) {
                $t = (string)$si->t;
            } else {
                foreach ($si->r as $r) $t .= (string)$r->t;
            }
            $strings[] = $t;
        }
    }

    // Sheet1
    $sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');
    $zip->close();
    if (!$sheetXml) return ['error'=>'No sheet1.xml'];

    $xml  = simplexml_load_string($sheetXml);
    $grid = [];

    foreach ($xml->sheetData->row as $row) {
        $rowIdx = (int)$row['r'] - 1; // 0-based
        foreach ($row->c as $cell) {
            $ref = (string)$cell['r'];
            preg_match('/([A-Z]+)(\d+)/', $ref, $m);
            $colIdx = colToIndex($m[1]);
            $type   = (string)$cell['t'];
            $val    = isset($cell->v) ? (string)$cell->v : '';
            if ($type === 's') $val = $strings[(int)$val] ?? '';
            $grid[$rowIdx][$colIdx] = $val;
        }
    }

    return $grid;
}

function colToIndex($col) {
    $idx = 0;
    foreach (str_split($col) as $c) {
        $idx = $idx * 26 + (ord($c) - ord('A') + 1);
    }
    return $idx - 1;
}

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel'])) {

    $file = $_FILES['excel']['tmp_name'];
    $ext  = strtolower(pathinfo($_FILES['excel']['name'], PATHINFO_EXTENSION));

    $grid = readXlsx($file);

    if (isset($grid['error'])) {
        $result['error'] = $grid['error'];
    } else {
        // Show raw first 5 rows for debug
        $debug['total_rows'] = count($grid);
        // only show debug if import not done yet
        $debug['raw'] = [];
        foreach (array_slice($grid, 0, 5, true) as $ri => $cols) {
            $debug['raw'][$ri] = array_values($cols);
        }

        // Find header row (row that contains 'Sc No' or 'Name')
        $headerRowIdx = null;
        foreach ($grid as $ri => $cols) {
            foreach ($cols as $val) {
                if (trim($val) === 'Sc No' || trim($val) === 'Name') {
                    $headerRowIdx = $ri;
                    break 2;
                }
            }
        }

        $debug['header_row_idx'] = $headerRowIdx;

        if ($headerRowIdx === null) {
            $result['error'] = 'Header row not found. See raw dump below.';
        } else {
            $headerCols = $grid[$headerRowIdx];
            $debug['headers'] = $headerCols;

            $inserted = 0; $updated = 0; $skipped = 0;

            foreach ($grid as $ri => $cols) {
                if ($ri <= $headerRowIdx) continue;

                // Map by position (cols is a 0-indexed array)
                $colValues = array_values($cols);
                $row = [];
                foreach (array_values($headerCols) as $ci => $hdr) {
                    $row[trim($hdr)] = trim($colValues[$ci] ?? '');
                }

                $division          = $row['Sub Division.'] ?? $row['Sub Division'] ?? '';
                $connection_number = $row['Sc No'] ?? '';
                $consumer_name     = $row['Name']  ?? '';
                $address           = $row['Address'] ?? '';
                $phone             = preg_replace('/\D/', '', $row['Phone No'] ?? '');
                $meter_number      = $row['Meter No'] ?? '';

                if (!$connection_number || !$consumer_name) { $skipped++; continue; }

                if (strlen($phone) > 10) $phone = substr($phone, -10);
                if (!$phone) $phone = null;

                try {
                    $existing = dbVal($db, "SELECT id FROM consumers WHERE connection_number=?", [$connection_number]);
                    if ($existing) {
                        $db->prepare("UPDATE consumers SET consumer_name=?,meter_number=?,address=?,division=?,phone=?,is_active=1 WHERE connection_number=?")
                           ->execute([$consumer_name, $meter_number?:null, $address?:null, $division?:null, $phone, $connection_number]);
                        $updated++;
                    } else {
                        $db->prepare("INSERT INTO consumers (consumer_name,meter_number,connection_number,address,division,phone,type,is_active) VALUES (?,?,?,?,?,?,?,1)")
                           ->execute([$consumer_name, $meter_number?:null, $connection_number, $address?:null, $division?:null, $phone, 'kcc']);
                        $inserted++;
                    }
                } catch (PDOException $e) {
                    $skipped++;
                }
            }

            $result = compact('inserted','updated','skipped');
            $done   = true;
        }
    }
}

include __DIR__ . '/includes/header.php';
?>

<div class="erp-card" style="max-width:640px;">
    <h5 class="fw-bold mb-3"><i class="bi bi-file-earmark-spreadsheet me-2 text-success"></i>Import Consumers</h5>

    <?php if (!empty($result['error'])): ?>
    <div class="alert alert-danger"><?= h($result['error']) ?></div>
    <?php endif; ?>

    <?php if ($done): ?>
    <div class="alert alert-success">
        <div class="fw-bold mb-1">✅ Import Complete!</div>
        <div>Inserted : <strong><?= $result['inserted'] ?></strong></div>
        <div>Updated  : <strong><?= $result['updated'] ?></strong></div>
        <div>Skipped  : <strong><?= $result['skipped'] ?></strong></div>
    </div>
    <a href="<?= BASE_URL ?>/modules/admin/consumers.php" class="btn btn-erp w-100 mb-2">
        <i class="bi bi-people-fill me-2"></i>View Consumers
    </a>
    <?php endif; ?>

    <?php if (!empty($debug)): ?>
    <div class="alert alert-info" style="font-size:.75rem;">
        <strong>DEBUG</strong><br>
        Total rows in grid: <strong><?= $debug['total_rows'] ?></strong><br>
        Header row index: <strong><?= $debug['header_row_idx'] ?? 'NOT FOUND' ?></strong><br>
        <?php if (!empty($debug['headers'])): ?>
        <br><strong>Headers:</strong><br>
        <code><?= h(implode(' | ', $debug['headers'])) ?></code>
        <?php endif; ?>
        <br><br><strong>Raw first 5 rows:</strong><br>
        <?php foreach ($debug['raw'] as $ri => $cols): ?>
        <code>Row <?= $ri ?>: <?= h(json_encode($cols, JSON_UNESCAPED_UNICODE)) ?></code><br>
        <?php endforeach; ?>
    </div>
    <?php endif; ?>

    <?php if (!$done): ?>
    <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">Select Excel File (.xlsx)</label>
            <input type="file" name="excel" class="form-control" accept=".xlsx,.xls,.csv" required>
        </div>
        <div class="alert alert-warning py-2" style="font-size:.82rem;">
            <i class="bi bi-exclamation-triangle me-1"></i>
            Existing consumers (by <strong>Sc No</strong>) will be updated. New ones inserted.
        </div>
        <button type="submit" class="btn btn-erp w-100">
            <i class="bi bi-upload me-2"></i>Import Now
        </button>
    </form>
    <?php endif; ?>

</div>

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