<?php
/**
 * Renumber IV Orders Script
 * 
 * This script renumbers all IV-SG and IV-SR orders starting from 00001,
 * based on the oldest record date first.
 * 
 * Usage: Run this script once via browser or command line
 */

session_name('FONFIX_SYSTEM_ID');
session_start();

require 'db.php';

// Simple logActivity function if not available
if (!function_exists('logActivity')) {
    function logActivity($pdo, $action, $desc) {
        try {
            $user = $_SESSION['user'] ?? 'System';
            $pdo->exec("CREATE TABLE IF NOT EXISTS activity_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user VARCHAR(100),
                action VARCHAR(100),
                description TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )");
            $stmt = $pdo->prepare("INSERT INTO activity_logs (user, action, description) VALUES (?, ?, ?)");
            $stmt->execute([$user, $action, $desc]);
        } catch (Exception $e) {
            // Silently fail
        }
    }
}

// Security check - only allow if logged in as admin/master
if (!isset($_SESSION['user']) || (!isset($_SESSION['role']) || $_SESSION['role'] !== 'master')) {
    if (php_sapi_name() !== 'cli') {
        die("Access Denied. Only master/admin users can run this script.");
    }
}

header('Content-Type: text/html; charset=utf-8');

echo "<!DOCTYPE html><html><head><meta charset='utf-8'><title>Renumber IV Orders</title>";
echo "<style>body{font-family:Arial;padding:20px;background:#f5f5f5;} .container{max-width:800px;margin:0 auto;background:white;padding:20px;border-radius:8px;box-shadow:0 2px 10px rgba(0,0,0,0.1);} h1{color:#2980b9;} .success{color:#2ecc71;font-weight:bold;} .error{color:#e74c3c;font-weight:bold;} .info{color:#3498db;} .warning{background:#fff3cd;border:2px solid #ffc107;padding:15px;border-radius:5px;margin:20px 0;color:#856404;} table{border-collapse:collapse;width:100%;margin:20px 0;} th,td{padding:10px;text-align:left;border:1px solid #ddd;} th{background:#3498db;color:white;} tr:nth-child(even){background:#f9f9f9;} .summary{background:#e8f5e9;padding:15px;border-radius:5px;margin:20px 0;}</style>";
echo "</head><body><div class='container'>";
echo "<h1>🔄 Renumber IV Orders</h1>";
echo "<div class='warning'>";
echo "<strong>⚠️ IMPORTANT SAFETY INFORMATION:</strong><br>";
echo "• This script <strong>ONLY</strong> modifies the <code>job_id</code> field<br>";
echo "• <strong>ALL customer data</strong> (name, phone, address, etc.) is preserved<br>";
echo "• <strong>ALL order details</strong> (price, date, status, items, etc.) are preserved<br>";
echo "• Data integrity verification runs automatically before committing changes<br>";
echo "• If any data mismatch is detected, all changes are rolled back automatically<br>";
echo "• <strong>This cannot be undone</strong> without a database backup. Old IDs (e.g. IV-SG00125) will be replaced by 00001, 00002, …<br>";
echo "</div>";
echo "<div class='summary'>";
echo "<strong>What this does:</strong> Resets Sale IDs to <strong>IV-SG00001, IV-SG00002, …</strong> (and IV-SR00001, IV-SR00002, …) in <strong>date order</strong> (oldest order = 00001). All existing records, customer names, prices, items, dates, etc. are kept; only the <code>job_id</code> value is changed so numbering is clean and aligned with your current data.";
echo "</div>";

// Require explicit confirmation so it's not run by mistake
$confirmed = isset($_GET['confirm']) && ($_GET['confirm'] === '1' || strtolower($_GET['confirm']) === 'yes');
if (!$confirmed) {
    echo "<div class='warning' style='margin-top:20px;'>";
    echo "<p><strong>⚠️ Renumbering is not run yet.</strong></p>";
    echo "<p>To actually renumber all IV-SG and IV-SR orders, you must confirm:</p>";
    echo "<p><a href='?confirm=1' style='display:inline-block;padding:12px 24px;background:#e74c3c;color:white;text-decoration:none;border-radius:6px;font-weight:bold;'>I understand — run renumber</a></p>";
    echo "<p style='margin-top:15px;'><a href='dashboard.php'>← Back to dashboard</a></p>";
    echo "</div></div></body></html>";
    exit;
}

try {
    $pdo->beginTransaction();
    
    // Only renumber ACTIVE (non-deleted) IV orders - same logic as fill-gap-on-delete.
    // Soft-deleted records have job_id changed to DEL-xxx, but we filter explicitly for safety.
    $ivFilter = " AND (is_deleted = 0 OR is_deleted IS NULL) AND (status != 'Refund' OR status IS NULL) ";
    
    // SAFETY CHECK 1: Get FULL record data before any changes (for verification)
    $stmtSGFull = $pdo->prepare("
        SELECT * FROM jobs 
        WHERE job_id LIKE 'IV-SG%' AND job_id REGEXP '^IV-SG[0-9]{5}$'
        " . $ivFilter . "
        ORDER BY date ASC, id ASC
    ");
    $stmtSGFull->execute();
    $sgOrdersFull = $stmtSGFull->fetchAll(PDO::FETCH_ASSOC);
    
    $stmtSRFull = $pdo->prepare("
        SELECT * FROM jobs 
        WHERE job_id LIKE 'IV-SR%' AND job_id REGEXP '^IV-SR[0-9]{5}$'
        " . $ivFilter . "
        ORDER BY date ASC, id ASC
    ");
    $stmtSRFull->execute();
    $srOrdersFull = $stmtSRFull->fetchAll(PDO::FETCH_ASSOC);
    
    // Create backup maps: id => full record data (for verification after update)
    $sgBackupMap = [];
    $srBackupMap = [];
    foreach ($sgOrdersFull as $order) {
        $sgBackupMap[$order['id']] = $order;
    }
    foreach ($srOrdersFull as $order) {
        $srBackupMap[$order['id']] = $order;
    }
    
    // Get all IV-SG orders (active only, no refunds) sorted by date (oldest first), then by id (oldest first)
    $stmtSG = $pdo->prepare("
        SELECT id, job_id, date, branch, customer 
        FROM jobs 
        WHERE job_id LIKE 'IV-SG%' AND job_id REGEXP '^IV-SG[0-9]{5}$'
        " . $ivFilter . "
        ORDER BY date ASC, id ASC
    ");
    $stmtSG->execute();
    $sgOrders = $stmtSG->fetchAll(PDO::FETCH_ASSOC);
    
    // Get all IV-SR orders (active only, no refunds) sorted by date (oldest first), then by id (oldest first)
    $stmtSR = $pdo->prepare("
        SELECT id, job_id, date, branch, customer 
        FROM jobs 
        WHERE job_id LIKE 'IV-SR%' AND job_id REGEXP '^IV-SR[0-9]{5}$'
        " . $ivFilter . "
        ORDER BY date ASC, id ASC
    ");
    $stmtSR->execute();
    $srOrders = $stmtSR->fetchAll(PDO::FETCH_ASSOC);
    
    // SAFETY CHECK 0: Verify record counts match
    $totalBefore = count($sgOrders) + count($srOrders);
    
    echo "<div class='info'>";
    echo "<p>Found <strong>" . count($sgOrders) . "</strong> IV-SG orders</p>";
    echo "<p>Found <strong>" . count($srOrders) . "</strong> IV-SR orders</p>";
    echo "<p>Total IV orders to renumber: <strong>" . $totalBefore . "</strong></p>";
    echo "<p style='font-size:0.9em;color:#666;'>Excludes soft-deleted (trash) and refund records — same logic as fill-gap-on-delete.</p>";
    echo "</div>";
    
    if ($totalBefore === 0) {
        throw new Exception("No IV orders found to renumber. Exiting safely.");
    }
    
    $updates = [];
    
    // Phase 1: Update all IV-SG orders to temporary IDs to avoid conflicts
    echo "<h3>Phase 1: Moving IV-SG orders to temporary IDs...</h3>";
    $tempCounter = 1;
    foreach ($sgOrders as $order) {
        $tempId = 'IV-SG-TEMP-' . str_pad($tempCounter, 5, '0', STR_PAD_LEFT);
        // SAFETY: Only update job_id field, preserve all other data
        $updateStmt = $pdo->prepare("UPDATE jobs SET job_id = ? WHERE id = ?");
        $updateStmt->execute([$tempId, $order['id']]);
        $tempCounter++;
    }
    
    // Phase 2: Update IV-SG orders to final IDs
    echo "<h2>Renumbering IV-SG Orders</h2>";
    echo "<table><thead><tr><th>Old Job ID</th><th>New Job ID</th><th>Date</th><th>Customer</th></tr></thead><tbody>";
    
    $counter = 1;
    foreach ($sgOrders as $order) {
        $newJobId = 'IV-SG' . str_pad($counter, 5, '0', STR_PAD_LEFT);
        $oldJobId = $order['job_id'];
        
        // SAFETY: Only update job_id field, preserve all other data
        $updateStmt = $pdo->prepare("UPDATE jobs SET job_id = ? WHERE id = ?");
        $updateStmt->execute([$newJobId, $order['id']]);
        
        $updates[] = [
            'old' => $oldJobId,
            'new' => $newJobId,
            'date' => $order['date'],
            'customer' => $order['customer']
        ];
        
        echo "<tr>";
        echo "<td><strong>" . htmlspecialchars($oldJobId) . "</strong></td>";
        echo "<td class='success'>" . htmlspecialchars($newJobId) . "</td>";
        echo "<td>" . htmlspecialchars($order['date']) . "</td>";
        echo "<td>" . htmlspecialchars($order['customer']) . "</td>";
        echo "</tr>";
        
        $counter++;
    }
    
    echo "</tbody></table>";
    
    // Phase 1: Update all IV-SR orders to temporary IDs to avoid conflicts
    echo "<h3>Phase 1: Moving IV-SR orders to temporary IDs...</h3>";
    $tempCounter = 1;
    foreach ($srOrders as $order) {
        $tempId = 'IV-SR-TEMP-' . str_pad($tempCounter, 5, '0', STR_PAD_LEFT);
        // SAFETY: Only update job_id field, preserve all other data
        $updateStmt = $pdo->prepare("UPDATE jobs SET job_id = ? WHERE id = ?");
        $updateStmt->execute([$tempId, $order['id']]);
        $tempCounter++;
    }
    
    // Phase 2: Update IV-SR orders to final IDs
    echo "<h2>Renumbering IV-SR Orders</h2>";
    echo "<table><thead><tr><th>Old Job ID</th><th>New Job ID</th><th>Date</th><th>Customer</th></tr></thead><tbody>";
    
    $counter = 1;
    foreach ($srOrders as $order) {
        $newJobId = 'IV-SR' . str_pad($counter, 5, '0', STR_PAD_LEFT);
        $oldJobId = $order['job_id'];
        
        // SAFETY: Only update job_id field, preserve all other data
        $updateStmt = $pdo->prepare("UPDATE jobs SET job_id = ? WHERE id = ?");
        $updateStmt->execute([$newJobId, $order['id']]);
        
        $updates[] = [
            'old' => $oldJobId,
            'new' => $newJobId,
            'date' => $order['date'],
            'customer' => $order['customer']
        ];
        
        echo "<tr>";
        echo "<td><strong>" . htmlspecialchars($oldJobId) . "</strong></td>";
        echo "<td class='success'>" . htmlspecialchars($newJobId) . "</td>";
        echo "<td>" . htmlspecialchars($order['date']) . "</td>";
        echo "<td>" . htmlspecialchars($order['customer']) . "</td>";
        echo "</tr>";
        
        $counter++;
    }
    
    echo "</tbody></table>";
    
    // SAFETY CHECK 2: Update refund records that reference the old job_ids
    echo "<h3>🔗 Updating Refund Records...</h3>";
    $refundUpdates = 0;
    
    // Create a mapping of old job_id to new job_id for quick lookup
    $jobIdMapping = [];
    foreach ($updates as $update) {
        $jobIdMapping[$update['old']] = $update['new'];
    }
    
    // Find all refund records that reference IV orders
    // Refund records have "REFUND FOR: IV-SG00018" or "REFUND FOR: IV-SR00018" in the repair field
    $refundStmt = $pdo->prepare("
        SELECT id, job_id, repair 
        FROM jobs 
        WHERE repair LIKE 'REFUND FOR: IV-%'
    ");
    $refundStmt->execute();
    $refundRecords = $refundStmt->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($refundRecords as $refund) {
        // Extract the referenced job_id from "REFUND FOR: IV-SG00018" or "REFUND FOR: IV-SR00018"
        // Pattern matches: IV-SG or IV-SR followed by digits (e.g., IV-SG00018, IV-SR00001)
        if (preg_match('/REFUND FOR:\s*(IV-S[GR]\d{5,})/i', $refund['repair'], $matches)) {
            $referencedJobId = trim($matches[1]);
            
            // Check if this job_id was renumbered
            if (isset($jobIdMapping[$referencedJobId])) {
                $newReferencedJobId = $jobIdMapping[$referencedJobId];
                $newRepair = str_replace(
                    'REFUND FOR: ' . $referencedJobId,
                    'REFUND FOR: ' . $newReferencedJobId,
                    $refund['repair']
                );
                
                // Update the refund record
                $updateRefundStmt = $pdo->prepare("UPDATE jobs SET repair = ? WHERE id = ?");
                $updateRefundStmt->execute([$newRepair, $refund['id']]);
                $refundUpdates++;
                
                echo "<p style='color:#666; font-size:0.9em;'>Updated refund record {$refund['job_id']}: {$referencedJobId} → {$newReferencedJobId}</p>";
            }
        }
    }
    
    if ($refundUpdates > 0) {
        echo "<p class='success'>✅ Updated {$refundUpdates} refund record(s) that referenced renumbered job IDs.</p>";
    } else {
        echo "<p style='color:#999;'>No refund records found that needed updating.</p>";
    }
    
    // SAFETY CHECK 3: Verify customer data integrity after updates
    echo "<h3>🔒 Verifying Data Integrity...</h3>";
    $dataIntegrityPassed = true;
    $verificationErrors = [];
    
    // Verify IV-SG orders
    foreach ($sgBackupMap as $id => $originalData) {
        $verifyStmt = $pdo->prepare("SELECT * FROM jobs WHERE id = ?");
        $verifyStmt->execute([$id]);
        $currentData = $verifyStmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$currentData) {
            $dataIntegrityPassed = false;
            $verificationErrors[] = "IV-SG record ID {$id} missing after update!";
            continue;
        }
        
        // Check all fields except job_id match original
        $fieldsToCheck = ['customer', 'phone', 'date', 'branch', 'model', 'repair', 'price', 'deposit', 
                         'status', 'payment_status', 'completed_at', 'remarks', 'warranty', 
                         'payment_method', 'job_cost', 'token', 'pickup_date', 'imei', 'passcode'];
        
        foreach ($fieldsToCheck as $field) {
            $originalValue = $originalData[$field] ?? null;
            $currentValue = $currentData[$field] ?? null;
            
            // Normalize null/empty comparisons
            $originalValue = ($originalValue === null || $originalValue === '') ? null : $originalValue;
            $currentValue = ($currentValue === null || $currentValue === '') ? null : $currentValue;
            
            if ($originalValue !== $currentValue) {
                $dataIntegrityPassed = false;
                $verificationErrors[] = "IV-SG record ID {$id} ({$originalData['job_id']}): Field '{$field}' changed! Original: '{$originalValue}', Current: '{$currentValue}'";
            }
        }
    }
    
    // Verify IV-SR orders
    foreach ($srBackupMap as $id => $originalData) {
        $verifyStmt = $pdo->prepare("SELECT * FROM jobs WHERE id = ?");
        $verifyStmt->execute([$id]);
        $currentData = $verifyStmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$currentData) {
            $dataIntegrityPassed = false;
            $verificationErrors[] = "IV-SR record ID {$id} missing after update!";
            continue;
        }
        
        // Check all fields except job_id match original
        $fieldsToCheck = ['customer', 'phone', 'date', 'branch', 'model', 'repair', 'price', 'deposit', 
                         'status', 'payment_status', 'completed_at', 'remarks', 'warranty', 
                         'payment_method', 'job_cost', 'token', 'pickup_date', 'imei', 'passcode'];
        
        foreach ($fieldsToCheck as $field) {
            $originalValue = $originalData[$field] ?? null;
            $currentValue = $currentData[$field] ?? null;
            
            // Normalize null/empty comparisons
            $originalValue = ($originalValue === null || $originalValue === '') ? null : $originalValue;
            $currentValue = ($currentValue === null || $currentValue === '') ? null : $currentValue;
            
            if ($originalValue !== $currentValue) {
                $dataIntegrityPassed = false;
                $verificationErrors[] = "IV-SR record ID {$id} ({$originalData['job_id']}): Field '{$field}' changed! Original: '{$originalValue}', Current: '{$currentValue}'";
            }
        }
    }
    
    if (!$dataIntegrityPassed) {
        throw new Exception("DATA INTEGRITY CHECK FAILED! Customer data was modified. Errors: " . implode("; ", array_slice($verificationErrors, 0, 10)) . (count($verificationErrors) > 10 ? " ... and " . (count($verificationErrors) - 10) . " more" : ""));
    }
    
    // SAFETY CHECK 3: Verify record count after updates (same filter: active IV orders only)
    $countStmtSG = $pdo->prepare("SELECT COUNT(*) FROM jobs WHERE job_id REGEXP '^IV-SG[0-9]{5}$' " . $ivFilter);
    $countStmtSG->execute();
    $sgCountAfter = $countStmtSG->fetchColumn();
    
    $countStmtSR = $pdo->prepare("SELECT COUNT(*) FROM jobs WHERE job_id REGEXP '^IV-SR[0-9]{5}$' " . $ivFilter);
    $countStmtSR->execute();
    $srCountAfter = $countStmtSR->fetchColumn();
    
    $totalAfter = $sgCountAfter + $srCountAfter;
    
    if ($totalAfter !== $totalBefore) {
        throw new Exception("RECORD COUNT MISMATCH! Before: {$totalBefore}, After: {$totalAfter}. Changes rolled back for safety.");
    }
    
    echo "<p class='success'>✅ Data integrity verified! All customer data preserved.</p>";
    echo "<p class='success'>✅ Record count verified! All " . $totalBefore . " records accounted for.</p>";
    if ($refundUpdates > 0) {
        echo "<p class='success'>✅ Refund records updated! {$refundUpdates} refund record(s) now reference the new job IDs.</p>";
    }
    
    // Commit transaction
    $pdo->commit();
    
    // Summary
    echo "<div class='summary'>";
    echo "<h2>✅ Renumbering Complete!</h2>";
    echo "<p><strong>Total orders processed:</strong> " . (count($sgOrders) + count($srOrders)) . "</p>";
    echo "<p><strong>Total orders updated:</strong> " . count($updates) . "</p>";
    echo "<p><strong>Refund records updated:</strong> " . ($refundUpdates ?? 0) . "</p>";
    echo "<p><strong>IV-SG orders:</strong> " . count($sgOrders) . " (starting from IV-SG00001)</p>";
    echo "<p><strong>IV-SR orders:</strong> " . count($srOrders) . " (starting from IV-SR00001)</p>";
    echo "<p class='success'>✅ All IV orders have been renumbered based on oldest date first!</p>";
    echo "<p class='success'>✅ Data integrity verified - All customer data preserved!</p>";
    echo "<div class='warning' style='margin-top:20px;'>";
    echo "<strong>⚠️ IMPORTANT: Please refresh your browser page!</strong><br>";
    echo "After renumbering, you need to <strong>refresh the dashboard page</strong> (F5 or Ctrl+R) to see the updated job IDs in the Billing tab.<br>";
    echo "The Daily Repair/Sales tab should already show the new IDs, but the Billing tab may show cached data until you refresh.";
    echo "</div>";
    echo "<p style='color:#666; font-size:0.9em; margin-top:10px;'><strong>Safety Features:</strong></p>";
    echo "<ul style='color:#666; font-size:0.9em;'>";
    echo "<li>✅ Only job_id field was modified</li>";
    echo "<li>✅ All customer data (name, phone, etc.) preserved</li>";
    echo "<li>✅ All order details (price, date, status, etc.) preserved</li>";
    echo "<li>✅ Refund records updated to reference new job IDs</li>";
    echo "<li>✅ Billing tab data will automatically reflect new job IDs</li>";
    echo "<li>✅ Data integrity verification passed</li>";
    echo "<li>✅ Transaction-based (all or nothing)</li>";
    echo "</ul>";
    echo "</div>";
    
    // Log the activity
    logActivity($pdo, 'RENUMBER_IV_ORDERS', "Renumbered " . count($updates) . " IV orders. SG: " . count($sgOrders) . ", SR: " . count($srOrders));
    
} catch (Exception $e) {
    $pdo->rollBack();
    echo "<div class='error'>";
    echo "<h2>❌ Error occurred!</h2>";
    echo "<p>" . htmlspecialchars($e->getMessage()) . "</p>";
    echo "<p>All changes have been rolled back.</p>";
    echo "</div>";
}

echo "</div></body></html>";
?>
