<?php
/**
 * One-off fix: Repair jobs that wrongly have IV- as job_id (should be JS-SG / JS-SR).
 * Run once in browser: fix_repair_job_ids.php
 * Safe to run multiple times; only updates rows where job_id LIKE 'IV-%' and model != 'Retail Sale'.
 */
require_once __DIR__ . '/db.php';

header('Content-Type: text/html; charset=utf-8');
echo "<h2>Fix Repair Job IDs (IV- → JS-)</h2>";

// Find repair jobs (not retail) that have IV- as job_id
$stmt = $pdo->query("
    SELECT id, job_id, branch, customer, invoice_id
    FROM jobs
    WHERE (model IS NULL OR model != 'Retail Sale')
      AND (job_id LIKE 'IV-SG%' OR job_id LIKE 'IV-SR%')
      AND (is_deleted = 0 OR is_deleted IS NULL)
");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (empty($rows)) {
    echo "<p>No repair jobs with IV- job_id found. Nothing to fix.</p>";
    exit;
}

echo "<p>Found " . count($rows) . " repair job(s) with IV- job_id. Fixing…</p><ul>";

foreach ($rows as $row) {
    $id = (int)$row['id'];
    $oldId = $row['job_id'];
    $branch = trim($row['branch'] ?? '');
    $isGombak = (stripos($branch, 'gombak') !== false);
    $prefix = $isGombak ? 'JS-SG' : 'JS-SR';

    // Prefer id-based JS number (e.g. id=5 → JS-SG0005) if free; else next available
    $stmtExist = $pdo->prepare("SELECT job_id FROM jobs WHERE job_id LIKE ? AND (is_deleted = 0 OR is_deleted IS NULL)");
    $stmtExist->execute([$prefix . '%']);
    $existing = $stmtExist->fetchAll(PDO::FETCH_COLUMN);
    $used = [];
    foreach ($existing as $jid) {
        $num = substr($jid, 5);
        if (ctype_digit($num)) $used[(int)$num] = true;
    }
    $candidate = $prefix . str_pad((string)$id, 5, '0', STR_PAD_LEFT);
    if (empty($used[$id])) {
        $newId = $candidate;
    } else {
        $max = !empty($used) ? max(array_keys($used)) : 0;
        $newId = $prefix . str_pad((string)($max + 1), 5, '0', STR_PAD_LEFT);
    }

    $pdo->prepare("UPDATE jobs SET job_id = ? WHERE id = ?")->execute([$newId, $id]);
    echo "<li>ID $id: <strong>$oldId</strong> → <strong>$newId</strong> (customer: " . htmlspecialchars($row['customer']) . ", invoice_id kept: " . htmlspecialchars($row['invoice_id'] ?? '') . ")</li>";
}

echo "</ul><p>Done. <a href='dashboard.php'>Back to Dashboard</a></p>";
