<?php
// api.php - v16.2: Hardened headers + IP rate limit + CSP-Report-Only
session_name('FONFIX_SYSTEM_ID');
session_start();

// Security Headers
header('X-Frame-Options: SAMEORIGIN');
header('X-Content-Type-Options: nosniff');
header('X-XSS-Protection: 1; mode=block');
header('Referrer-Policy: strict-origin-when-cross-origin');
header('Permissions-Policy: geolocation=(), microphone=(), camera=()');
$isHttpsApi = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on')
    || (($_SERVER['HTTP_X_FORWARDED_PROTO'] ?? '') === 'https');
if ($isHttpsApi) {
    header('Strict-Transport-Security: max-age=300');
}
header("Content-Security-Policy-Report-Only: default-src 'self'; frame-ancestors 'none'");
header('Content-Type: application/json');

// Bot Detection - Block common crawlers from API
$userAgent = strtolower($_SERVER['HTTP_USER_AGENT'] ?? '');
$blockedBots = ['googlebot', 'bingbot', 'slurp', 'duckduckbot', 'baiduspider', 'yandex', 'sogou', 'exabot', 'facebot', 'ia_archiver', 'crawler', 'spider', 'bot'];
foreach ($blockedBots as $bot) {
    if (strpos($userAgent, $bot) !== false) {
        http_response_code(403);
        die(json_encode(['success' => false, 'error' => 'Access Denied']));
    }
}

// Block empty or suspicious user agents
if (empty($userAgent) || preg_match('/^(curl|wget|python|libwww|perl|java|scrapy)/i', $userAgent)) {
    http_response_code(403);
    die(json_encode(['success' => false, 'error' => 'Access Denied']));
}

require 'db.php';
require_once 'helpers.php';

ini_set('display_errors', 0); 
function sendError($msg) { echo json_encode(['success'=>false, 'error'=>$msg]); exit; }
function sendSuccess($data=[]) { echo json_encode(array_merge(['success'=>true], $data)); exit; }

function runInTransaction($pdo, callable $callback) {
    $startedTx = false;
    if (!$pdo->inTransaction()) {
        $pdo->beginTransaction();
        $startedTx = true;
    }
    try {
        $result = $callback();
        if ($startedTx && $pdo->inTransaction()) {
            $pdo->commit();
        }
        return $result;
    } catch (Exception $e) {
        if ($startedTx && $pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

function normalizeStockName($name) {
    $clean = trim((string)$name);
    $clean = preg_replace('/\s+/', ' ', $clean);
    return strtolower($clean);
}

// Verbose logging flag. Routine "Stock OK" log lines are noise in production
// (stock_movements table already records every change). Failures, mismatches
// and "no match" lines are still always logged. Flip to true via env
// FONFIX_VERBOSE_LOG or config.features.verbose_log when investigating.
function f2f_verbose_log(): bool {
    static $v = null;
    if ($v === null) {
        $env = getenv('FONFIX_VERBOSE_LOG');
        if ($env !== false && $env !== '') {
            $v = filter_var($env, FILTER_VALIDATE_BOOLEAN);
        } elseif (function_exists('getConfig')) {
            $v = (bool) getConfig('features.verbose_log', false);
        } else {
            $v = false;
        }
    }
    return $v;
}

// ============================================================
// === v18.0 ARCHIVE CONSOLE HELPERS ==========================
// ============================================================
// Whitelist of archive scopes the UI is allowed to address. Never accept
// arbitrary table names from the client - that would be a SQLi vector.
function archive_scope_meta(string $scope): ?array {
    static $map = null;
    if ($map === null) {
        $map = [
            'dropoff' => [
                'live'    => 'jobs',
                'archive' => 'jobs_archive',
                'where'   => "model <> 'Retail Sale' AND job_id LIKE 'JS-S%' AND is_deleted = 0",
                'ts_col'  => 'date',
                'label'   => 'Drop-off Repairs',
            ],
            'retail' => [
                'live'    => 'jobs',
                'archive' => 'jobs_archive',
                'where'   => "model = 'Retail Sale' AND job_id LIKE 'IV-S%' AND is_deleted = 0",
                'ts_col'  => 'date',
                'label'   => 'Retail Sales / Billing',
            ],
            'expenses' => [
                'live'    => 'expenses',
                'archive' => 'expenses_archive',
                'where'   => '1=1',
                'ts_col'  => 'date',
                'label'   => 'Expenses',
            ],
            'activity_logs' => [
                'live'    => 'activity_logs',
                'archive' => 'activity_logs_archive',
                'where'   => '1=1',
                'ts_col'  => 'created_at',
                'label'   => 'Activity Logs',
            ],
            'stock_activity_logs' => [
                'live'    => 'stock_activity_logs',
                'archive' => 'stock_activity_logs_archive',
                'where'   => '1=1',
                'ts_col'  => 'created_at',
                'label'   => 'Stock Activity Logs',
            ],
        ];
    }
    return $map[$scope] ?? null;
}

// Idempotent: clones live structure into archive table on first call,
// then patches any columns present in live but missing in archive. Returns
// the ordered list of column names to use in explicit INSERT/SELECT lists.
function archive_ensure_schema(PDO $pdo, string $liveTable, string $archiveTable): array {
    $check = $pdo->prepare("SHOW TABLES LIKE ?");
    $check->execute([$liveTable]);
    if ($check->rowCount() === 0) {
        throw new RuntimeException("Live table '$liveTable' missing");
    }
    $pdo->exec("CREATE TABLE IF NOT EXISTS `$archiveTable` LIKE `$liveTable`");

    $liveCols = [];
    foreach ($pdo->query("SHOW COLUMNS FROM `$liveTable`") as $r) {
        $liveCols[$r['Field']] = $r;
    }
    $archCols = [];
    foreach ($pdo->query("SHOW COLUMNS FROM `$archiveTable`") as $r) {
        $archCols[$r['Field']] = $r;
    }
    foreach ($liveCols as $name => $col) {
        if (!isset($archCols[$name])) {
            try {
                $type = $col['Type'];
                $pdo->exec("ALTER TABLE `$archiveTable` ADD COLUMN `$name` $type NULL");
                $archCols[$name] = $col;
            } catch (Throwable $e) {
                // best-effort; INSERT below will surface real failures
            }
        }
    }
    $ordered = [];
    foreach ($liveCols as $name => $_) {
        if (isset($archCols[$name])) { $ordered[] = $name; }
    }
    return $ordered;
}

function archive_master_gate(): void {
    if (!isAdmin()) {
        sendError("ACCESS DENIED: Archive Console is master-only.");
    }
}

// Build WHERE extras + bound params from UI filters. Whitelisted values only.
function archive_filter_clause(string $scope, array $filters): array {
    $extras = [];
    $params = [];
    if (in_array($scope, ['dropoff', 'retail'], true)) {
        $status = trim((string)($filters['status'] ?? ''));
        $allowed = ['Pending', 'In Progress', 'Completed', 'Cancelled', 'Refund'];
        if ($status !== '' && $status !== 'all' && in_array($status, $allowed, true)) {
            $extras[] = "status = ?";
            $params[] = $status;
        }
    }
    if (in_array($scope, ['dropoff', 'retail', 'expenses'], true)) {
        $branch = trim((string)($filters['branch'] ?? ''));
        if ($branch !== '' && $branch !== 'all' && in_array($branch, ['Gombak', 'Sri Rampai'], true)) {
            $extras[] = "branch = ?";
            $params[] = $branch;
        }
    }
    return [$extras, $params];
}

// --- SCHEMA MIGRATIONS (run once per deploy, not per session) ---
// The marker file lives under backups/ which is denied from the web by
// .htaccess. Bump the version suffix when you add new ALTER TABLE / CREATE
// TABLE blocks below so existing installs re-run the checks once.
$migrationsMarker = __DIR__ . '/backups/.migrations_done.v17';
$migrationsAlreadyDone = isset($_SESSION['_migrations_done']) || is_file($migrationsMarker);
if (!$migrationsAlreadyDone) {
    $pdo->exec("CREATE TABLE IF NOT EXISTS expenses (
        id INT AUTO_INCREMENT PRIMARY KEY,
        branch VARCHAR(50),
        date DATE,
        description VARCHAR(255),
        amount DECIMAL(10,2),
        created_by VARCHAR(50)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS credit_notes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        stock_id INT NOT NULL,
        item_name VARCHAR(255) NOT NULL,
        branch VARCHAR(50) NOT NULL,
        quantity INT NOT NULL,
        date DATE NOT NULL,
        reason TEXT,
        supplier VARCHAR(255),
        created_by VARCHAR(50) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (stock_id) REFERENCES stock_list(id) ON DELETE CASCADE,
        INDEX idx_date (date),
        INDEX idx_stock_id (stock_id),
        INDEX idx_branch (branch)
    )");

    $newColumns = [
        'can_add_cross_branch_stock',
        'can_view_stock_logs',
        'can_merge_stock',
        'can_bulk_ops',
        'can_bulk_print',
        'can_bulk_import',
        'can_convert_uppercase'
    ];
    foreach ($newColumns as $col) {
        try {
            $checkColumn = $pdo->query("SHOW COLUMNS FROM users LIKE '$col'");
            if ($checkColumn->rowCount() == 0) {
                $pdo->exec("ALTER TABLE users ADD COLUMN $col TINYINT(1) DEFAULT 0");
            }
        } catch (Exception $e) {
            error_log("Column migration error for $col: " . $e->getMessage());
        }
    }

    try {
        $checkCol = $pdo->query("SHOW COLUMNS FROM stock_list LIKE 'brand'");
        if ($checkCol->rowCount() == 0) {
            $pdo->exec("ALTER TABLE stock_list ADD COLUMN brand VARCHAR(100) DEFAULT NULL");
        }
    } catch (Exception $e) {
        error_log("Brand column migration error: " . $e->getMessage());
    }

    $inventoryColumns = [
        'reorder_point_gombak' => 'INT DEFAULT 5',
        'reorder_point_sr' => 'INT DEFAULT 5',
        'low_stock_threshold_gombak' => 'INT DEFAULT 3',
        'low_stock_threshold_sr' => 'INT DEFAULT 3',
        'last_purchase_date' => 'DATE NULL',
        'last_purchase_cost' => 'DECIMAL(10,2) NULL',
        'average_cost' => 'DECIMAL(10,2) DEFAULT 0',
        'total_purchased' => 'INT DEFAULT 0',
        'last_sold_date' => 'DATE NULL',
        'turnover_rate' => 'DECIMAL(10,2) DEFAULT 0'
    ];
    foreach ($inventoryColumns as $col => $def) {
        try {
            $checkColumn = $pdo->query("SHOW COLUMNS FROM stock_list LIKE '$col'");
            if ($checkColumn->rowCount() == 0) {
                $pdo->exec("ALTER TABLE stock_list ADD COLUMN $col $def");
            }
        } catch (Exception $e) {
            error_log("Inventory column migration error for $col: " . $e->getMessage());
        }
    }

    try {
        $checkPartNorm = $pdo->query("SHOW COLUMNS FROM stock_list LIKE 'part_name_norm'");
        if ($checkPartNorm->rowCount() == 0) {
            $pdo->exec("ALTER TABLE stock_list ADD COLUMN part_name_norm VARCHAR(255) NULL");
        }
        $pdo->exec("UPDATE stock_list SET part_name_norm = LOWER(TRIM(part_name)) WHERE part_name_norm IS NULL OR part_name_norm = ''");
    } catch (Exception $e) {
        error_log("part_name_norm migration error: " . $e->getMessage());
    }

    $pdo->exec("CREATE TABLE IF NOT EXISTS purchase_orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        po_number VARCHAR(50) UNIQUE,
        supplier VARCHAR(100),
        order_date DATE,
        expected_date DATE,
        status VARCHAR(50) DEFAULT 'Pending',
        total_amount DECIMAL(10,2) DEFAULT 0,
        notes TEXT,
        created_by VARCHAR(100),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_supplier (supplier),
        INDEX idx_status (status),
        INDEX idx_order_date (order_date)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS purchase_order_items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        po_id INT,
        item_name VARCHAR(200),
        quantity INT,
        unit_cost DECIMAL(10,2),
        total_cost DECIMAL(10,2),
        received_qty INT DEFAULT 0,
        FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
        INDEX idx_po_id (po_id)
    )");

    try {
        $checkDeletedAt = $pdo->query("SHOW COLUMNS FROM jobs LIKE 'deleted_at'");
        if ($checkDeletedAt->rowCount() == 0) {
            $pdo->exec("ALTER TABLE jobs ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL");
        }
    } catch (Exception $e) {
        error_log("Deleted_at column migration error: " . $e->getMessage());
    }

    $jobsNewColumns = [
        'request_id' => "VARCHAR(64) NULL",
        'deleted_by' => "VARCHAR(100) NULL",
        'delete_reason' => "TEXT NULL",
        'delete_request_id' => "VARCHAR(64) NULL"
    ];
    foreach ($jobsNewColumns as $col => $def) {
        try {
            $checkColumn = $pdo->query("SHOW COLUMNS FROM jobs LIKE '$col'");
            if ($checkColumn->rowCount() == 0) {
                $pdo->exec("ALTER TABLE jobs ADD COLUMN $col $def");
            }
        } catch (Exception $e) {
            error_log("Jobs column migration error for $col: " . $e->getMessage());
        }
    }

    try {
        $checkPaymentMethod = $pdo->query("SHOW COLUMNS FROM jobs WHERE Field = 'payment_method'");
        $columnInfo = $checkPaymentMethod->fetch(PDO::FETCH_ASSOC);
        if ($columnInfo && strpos($columnInfo['Type'], 'varchar(50)') !== false) {
            $pdo->exec("ALTER TABLE jobs MODIFY COLUMN payment_method VARCHAR(200) DEFAULT 'Cash'");
        }
    } catch (Exception $e) {
        error_log("Payment method column migration error: " . $e->getMessage());
    }

    $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,
        INDEX idx_user (user),
        INDEX idx_action (action),
        INDEX idx_created_at (created_at)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS login_attempts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(100),
        ip_address VARCHAR(45),
        attempt_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        success TINYINT(1) DEFAULT 0,
        INDEX idx_username_time (username, attempt_time),
        INDEX idx_ip_time (ip_address, attempt_time)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS stock_movements (
        id INT AUTO_INCREMENT PRIMARY KEY,
        stock_id INT,
        part_name VARCHAR(255),
        branch VARCHAR(50),
        movement_type VARCHAR(30),
        qty INT,
        movement_date DATETIME DEFAULT CURRENT_TIMESTAMP,
        job_id VARCHAR(50),
        staff VARCHAR(100),
        reference TEXT,
        INDEX idx_stock_date (stock_id, movement_date),
        INDEX idx_branch (branch),
        INDEX idx_job (job_id),
        INDEX idx_movement_type (movement_type)
    )");

    // Database indexes for jobs table
    try {
        $pdo->exec("CREATE INDEX idx_jobs_main ON jobs (is_deleted, model, branch, date)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE INDEX idx_jobs_job_id ON jobs (job_id)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE INDEX idx_jobs_status ON jobs (status, is_deleted)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE INDEX idx_jobs_active_date ON jobs (is_deleted, date, created_at)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE INDEX idx_jobs_deleted_date ON jobs (is_deleted, deleted_at, created_at)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE INDEX idx_jobs_branch_date ON jobs (is_deleted, branch, date, created_at)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE INDEX idx_jobs_phone_date ON jobs (phone, date)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE INDEX idx_jobs_repair_deleted ON jobs (is_deleted, repair(120))");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE UNIQUE INDEX uq_jobs_request_id ON jobs (request_id)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE UNIQUE INDEX uq_jobs_job_id ON jobs (job_id)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE UNIQUE INDEX uq_jobs_token ON jobs (token)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE UNIQUE INDEX uq_jobs_invoice_id ON jobs (invoice_id)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE INDEX idx_activity_action_created ON activity_logs (action, created_at)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE INDEX idx_stock_list_type_category ON stock_list (type, category)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE INDEX idx_stock_list_part_name ON stock_list (part_name)");
    } catch (Exception $e) {}
    try {
        $pdo->exec("CREATE INDEX idx_stock_list_part_name_norm ON stock_list (part_name_norm)");
    } catch (Exception $e) {}

    $_SESSION['_migrations_done'] = true;
    // Persist marker across sessions (best effort; runtime self-heal below
    // will recover anything missed if the marker write fails).
    if (!is_dir(__DIR__ . '/backups')) { @mkdir(__DIR__ . '/backups', 0750, true); }
    @file_put_contents($migrationsMarker, date('c') . "\n");
} // end migrations

// --- RUNTIME SCHEMA SELF-HEAL (handles old sessions / partial migrations) ---
// If a prior session set _migrations_done before this column existed, queries using part_name_norm can fail.
try {
    $checkPartNormRuntime = $pdo->query("SHOW COLUMNS FROM stock_list LIKE 'part_name_norm'");
    if (!$checkPartNormRuntime || $checkPartNormRuntime->rowCount() == 0) {
        $pdo->exec("ALTER TABLE stock_list ADD COLUMN part_name_norm VARCHAR(255) NULL");
        $pdo->exec("UPDATE stock_list SET part_name_norm = LOWER(TRIM(part_name)) WHERE part_name_norm IS NULL OR part_name_norm = ''");
    }
    try {
        $pdo->exec("CREATE INDEX idx_stock_list_part_name_norm ON stock_list (part_name_norm)");
    } catch (Exception $e) {}
} catch (Exception $e) {
    error_log("Runtime part_name_norm self-heal error: " . $e->getMessage());
}

try {
    $jobDeleteMetaCols = [
        'deleted_by' => "VARCHAR(100) NULL",
        'delete_reason' => "TEXT NULL",
        'delete_request_id' => "VARCHAR(64) NULL"
    ];
    foreach ($jobDeleteMetaCols as $col => $def) {
        $checkCol = $pdo->query("SHOW COLUMNS FROM jobs LIKE '$col'");
        if (!$checkCol || $checkCol->rowCount() == 0) {
            $pdo->exec("ALTER TABLE jobs ADD COLUMN $col $def");
        }
    }
} catch (Exception $e) {
    error_log("Runtime jobs delete-meta self-heal error: " . $e->getMessage());
}

// --- PERMISSION HELPERS ---
// OPTIMIZATION: Cache permissions in session to avoid repeated DB queries
function checkLivePermission($pdo, $userId, $permType) {
    if (empty($userId)) return false;
    
    // Check cache first (key includes user_id to handle multiple users)
    $cacheKey = '_user_perms_' . $userId;
    if (isset($_SESSION[$cacheKey])) {
        $cached = $_SESSION[$cacheKey];
        // Check if cache is still valid (same user session)
        if (isset($cached['user_id']) && $cached['user_id'] == $userId) {
            // Master and endie always return true
            if (strtolower($cached['role']) === 'master') return true;
            if (isset($_SESSION['user']) && strtolower($_SESSION['user']) === 'endie') return true;
            return ($cached[$permType] == 1);
        }
    }
    
    // Cache miss - fetch from DB
    $stmt = $pdo->prepare("SELECT role, can_edit, can_delete, can_transfer, can_refund, can_restore, can_add_cross_branch_stock FROM users WHERE id = ?");
    $stmt->execute([$userId]);
    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$user) return false;
    
    // Cache the result (include user_id for validation)
    $user['user_id'] = $userId;
    $_SESSION[$cacheKey] = $user;
    
    if (strtolower($user['role']) === 'master') return true;
    if (isset($_SESSION['user']) && strtolower($_SESSION['user']) === 'endie') return true;
    
    return ($user[$permType] == 1);
}

function logActivity($pdo, $action, $desc) {
    try {
        $user = $_SESSION['user'] ?? 'Unknown';
        $stmt = $pdo->prepare("INSERT INTO activity_logs (user, action, description) VALUES (?, ?, ?)");
        $stmt->execute([$user, $action, $desc]);
    } catch (Exception $e) {
        error_log("Activity log error: " . $e->getMessage());
    }
}

// stock_movements table is now created in the session-guarded migration block above

function logStockMovement($pdo, $stockId, $partName, $branch, $type, $qty, $jobId = null, $reference = null) {
    if (empty($qty) || $qty <= 0) return;
    try {
        $staff = $_SESSION['user'] ?? 'Unknown';
        $branchName = (stripos($branch, 'gombak') !== false) ? 'Gombak' : 'Sri Rampai';
        $stmt = $pdo->prepare("INSERT INTO stock_movements (stock_id, part_name, branch, movement_type, qty, job_id, staff, reference) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        $stmt->execute([$stockId ?: null, $partName, $branchName, $type, $qty, $jobId, $staff, $reference]);
    } catch (Exception $e) {
        error_log("Stock movement log error: " . $e->getMessage());
    }
}

function isAdmin() {
    $role = $_SESSION['role'] ?? '';
    $user = strtolower($_SESSION['user'] ?? '');
    return ($role === 'master' || $user === 'endie');
}

// Helper function for export (used by export_data.php)
if (!function_exists('isAdmin')) {
    function isAdmin() {
        $role = $_SESSION['role'] ?? '';
        $user = strtolower($_SESSION['user'] ?? '');
        return ($role === 'master' || $user === 'endie');
    }
}

// --- HELPER: EXTRACT CATEGORY FROM NAME ---
function extractCategoryFromName($partName) {
    $name = strtoupper($partName);
    
    $categories = [
        'LCD' => ['LCD', 'SCREEN', 'DISPLAY'],
        'Battery' => ['BATTERY', 'BATT'],
        'Housing' => ['HOUSING', 'FRAME', 'CHASSIS'],
        'Back Glass' => ['BACK GLASS', 'BACKGLASS', 'REAR GLASS'],
        'Charging Port' => ['CHARGING PORT', 'CHARGING', 'PORT', 'USB PORT'],
        'Camera' => ['CAMERA', 'CAM'],
        'Face ID' => ['FACE ID', 'FACEID', 'FACE ID'],
        'Motherboard' => ['MOTHERBOARD', 'MAINBOARD', 'LOGIC BOARD'],
        'Speaker' => ['SPEAKER', 'SPK'],
        'Unlock/Flash' => ['UNLOCK', 'FLASH', 'UNLOCK/FLASH'],
        'Tempered Glass' => ['TEMPERED GLASS', 'TEMPERED', 'GLASS'],
        'Cable' => ['CABLE', 'CORD'],
        'Charger' => ['CHARGER', 'ADAPTER']
    ];
    
    foreach ($categories as $cat => $keywords) {
        foreach ($keywords as $keyword) {
            if (strpos($name, $keyword) !== false) {
                return $cat;
            }
        }
    }
    
    return 'Other';
}

// --- HELPER: GENERATE ID ---
// Fills gaps (e.g. after deleted or failed inserts) so Sale IDs don't skip (IV-SG00125, IV-SG00126, ...).
function generateCustomID($pdo, $type, $branch) {
    $branch = trim($branch);
    $isGombak = (stripos($branch, 'gombak') !== false);

    if ($type === 'Retail') {
        $prefix = $isGombak ? "IV-SG" : "IV-SR";
    } else {
        $prefix = $isGombak ? "JS-SG" : "JS-SR";
    }

    $prefixLen = strlen($prefix);

    $stmt = $pdo->prepare("SELECT MAX(CAST(SUBSTRING(job_id, ?) AS UNSIGNED)) FROM jobs WHERE job_id LIKE ? AND (is_deleted = 0 OR is_deleted IS NULL)");
    $stmt->execute([$prefixLen + 1, $prefix . '%']);
    $max = (int)$stmt->fetchColumn();
    return $prefix . str_pad((string)($max + 1), 5, '0', STR_PAD_LEFT);
}

/**
 * When an IV-SG or IV-SR order is soft-deleted, shift subsequent orders down to fill the gap.
 * E.g. delete 226 → 227 becomes 226, 228 becomes 227, etc.
 */
function fillGapOnIVDelete($pdo, $deletedJobId) {
    if (!preg_match('/^(IV-S[GR])(\d{5,})$/i', $deletedJobId, $m)) return; // Not IV-SG/IV-SR
    $prefix = strtoupper($m[1]); // IV-SG or IV-SR
    $deletedNum = (int)$m[2];
    $prefixLen = strlen($prefix);

    // Get all non-deleted IV orders with number > deletedNum, ordered by number ASC
    $stmt = $pdo->prepare("
        SELECT id, job_id FROM jobs 
        WHERE job_id LIKE ? AND (is_deleted = 0 OR is_deleted IS NULL)
        ORDER BY id ASC
    ");
    $stmt->execute([$prefix . '%']);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $toShift = [];
    foreach ($rows as $r) {
        $numPart = substr($r['job_id'], $prefixLen);
        if (ctype_digit($numPart) && (int)$numPart > $deletedNum) {
            $toShift[] = ['id' => $r['id'], 'oldNum' => (int)$numPart];
        }
    }
    if (empty($toShift)) return;

    // Sort by oldNum ASC so we process 227, 228, 229...
    usort($toShift, fn($a, $b) => $a['oldNum'] <=> $b['oldNum']);

    // Build mapping: old job_id -> new job_id (shift down by 1)
    $jobIdMapping = [];
    foreach ($toShift as $r) {
        $newNum = $r['oldNum'] - 1;
        $oldJobId = $prefix . str_pad((string)$r['oldNum'], 5, '0', STR_PAD_LEFT);
        $newJobId = $prefix . str_pad((string)$newNum, 5, '0', STR_PAD_LEFT);
        $jobIdMapping[$oldJobId] = $newJobId;
    }

    $startedTx = false;
    if (!$pdo->inTransaction()) {
        $pdo->beginTransaction();
        $startedTx = true;
    }
    try {
        $updateStmt = $pdo->prepare("UPDATE jobs SET job_id = ? WHERE id = ?");

        $tempCounter = 1;
        foreach ($toShift as $r) {
            $tempId = $prefix . '-TEMP-' . str_pad((string)$tempCounter++, 5, '0', STR_PAD_LEFT);
            $updateStmt->execute([$tempId, $r['id']]);
        }

        foreach ($toShift as $r) {
            $newJobId = $prefix . str_pad((string)($r['oldNum'] - 1), 5, '0', STR_PAD_LEFT);
            $updateStmt->execute([$newJobId, $r['id']]);
        }

        $refundStmt = $pdo->prepare("SELECT id, repair FROM jobs WHERE repair LIKE ?");
        $refundStmt->execute(['REFUND FOR: ' . $prefix . '%']);
        $refunds = $refundStmt->fetchAll(PDO::FETCH_ASSOC);
        $updateRepairStmt = $pdo->prepare("UPDATE jobs SET repair = ? WHERE id = ?");
        foreach ($refunds as $ref) {
            if (preg_match('/REFUND FOR:\s*(' . preg_quote($prefix) . '\d{5,})/i', $ref['repair'], $matches)) {
                $refJobId = trim($matches[1]);
                if (isset($jobIdMapping[$refJobId])) {
                    $newRefJobId = $jobIdMapping[$refJobId];
                    $newRepair = str_replace('REFUND FOR: ' . $refJobId, 'REFUND FOR: ' . $newRefJobId, $ref['repair']);
                    $updateRepairStmt->execute([$newRepair, $ref['id']]);
                }
            }
        }
        if ($startedTx && $pdo->inTransaction()) {
            $pdo->commit();
        }
    } catch (Exception $e) {
        if ($startedTx && $pdo->inTransaction()) {
            $pdo->rollBack();
        }
        error_log("fillGapOnIVDelete error: " . $e->getMessage());
        // Bubble up when called inside another transaction (e.g. delete_job).
        if (!$startedTx) {
            throw $e;
        }
    }
}

// --- HELPER: CALCULATE TOTAL COST FROM STOCK ---
// $costMap is optional: if provided (array of part_name => cost), uses it instead of querying DB
// This allows batch fetching for performance optimization
function calculateItemsCost($pdo, $repairString, $costMap = null) {
    $totalCost = 0.00;
    $items = explode(' || ', $repairString);
    foreach ($items as $itemStr) {
        // Match format: "part_name (xqty) @ price [W: warranty]" or "part_name [SN: sn] (xqty) @ price [W: warranty]"
        if (preg_match('/^(.*?) \(x(\d+)\)/', trim($itemStr), $matches)) {
            $rawName = trim($matches[1]);
            // Remove [SN: ...] tags if present
            $partName = trim(preg_replace('/\[SN:.*?\]/', '', $rawName));
            // Remove [W: ...] tags if present (though they usually come after the quantity)
            $partName = trim(preg_replace('/\[W:.*?\]/', '', $partName));
            $partName = trim(preg_replace('/\[Alt:.*?\]/', '', $partName));
            // Normalize whitespace
            $partName = preg_replace('/\s+/', ' ', trim($partName));
            $qty = intval($matches[2]);
            
            if (!empty($partName)) {
                $cost = null;
                $partNameLower = strtolower($partName);
                
                // Use cost map if provided (batch optimization), otherwise query DB
                if ($costMap !== null) {
                    // Check both exact match and lowercase match in cost map
                    if (isset($costMap[$partName])) {
                        $cost = $costMap[$partName];
                    } elseif (isset($costMap[$partNameLower])) {
                        $cost = $costMap[$partNameLower];
                    }
                }
                
                // If not found in cost map, query DB with case-insensitive search
                if ($cost === null) {
                    $partNorm = normalizeStockName($partName);
                    $stmt = $pdo->prepare("SELECT cost FROM stock_list WHERE part_name_norm = ? LIMIT 1");
                    $stmt->execute([$partNorm]);
                    $cost = $stmt->fetchColumn();
                }
                
                if ($cost) {
                    $totalCost += (floatval($cost) * $qty);
                }
            }
        }
    }
    return $totalCost;
}

// --- HELPER: CALCULATE TOTAL FROM REPAIR STRING (for display/DB sync; same logic as receipt.php) ---
function calculateTotalFromRepair($repair) {
    if (empty($repair)) return 0;
    $total = 0;
    $rawItems = explode(' || ', $repair);
    foreach ($rawItems as $rawItem) {
        $clean = trim(preg_replace('/\[W:.*?\]/', '', $rawItem));
        $clean = trim(preg_replace('/\[SN:.*?\]/', '', $clean));
        $clean = trim(preg_replace('/\[Alt:.*?\]/', '', $clean));
        if (preg_match('/\([xX](\d+)\)\s*@\s*([0-9.]+)/', $clean, $m)) {
            $total += (float)$m[1] * (float)$m[2];
        }
    }
    return round($total, 2);
}

// --- HELPER: MANAGE STOCK (RESTORE/DEDUCT) ---
function manageStockForJob($pdo, $job, $action) {
    $repair = $job['repair'] ?? '';
    if (empty(trim($repair)) || strpos($repair, 'REFUND FOR:') !== false) return;

    // Parse items: support both " || " separator and preg_match_all for robustness (handles edge cases)
    $parsedItems = [];
    $items = explode(' || ', $repair);
    foreach ($items as $itemStr) {
        if (preg_match('/^(.*?) \(x(\d+)\)/i', trim($itemStr), $matches)) {
            $rawName = trim($matches[1]);
            $partName = trim(preg_replace('/\[SN:.*?\]/', '', $rawName));
            $partName = trim(preg_replace('/\[W:.*?\]/', '', $partName));
            $partName = trim(preg_replace('/\[Alt:.*?\]/', '', $partName));
            $partName = preg_replace('/\s+/', ' ', trim($partName));
            $qty = intval($matches[2]);
            if ($qty > 0 && !empty($partName)) {
                $parsedItems[] = ['name' => $partName, 'qty' => $qty];
            }
        }
    }

    $branchKey = strtolower($job['branch'] ?? '');
    $stockColumn = (strpos($branchKey, 'gombak') !== false) ? 'stock_gombak' : 'stock_sr';
    $operator = ($action === 'RESTORE') ? '+' : '-';

    foreach ($parsedItems as $parsed) {
        $normalizedPartName = $parsed['name'];
        $qty = $parsed['qty'];

        // Match using LOWER(TRIM(part_name)) - same as CREATE_RETAIL for consistency
        $searchVariations = [
            $normalizedPartName,
            str_replace([' 1set', ' 2set', ' 3set', ' 4set', ' 5set', '1set', '2set', '3set', '4set', '5set', 'set'], '', $normalizedPartName),
        ];
        $searchVariations = array_unique(array_map('trim', $searchVariations));

        $foundPartData = null;
        foreach ($searchVariations as $variation) {
            if (empty($variation)) continue;
            $variationNorm = normalizeStockName($variation);
            $findStmt = $pdo->prepare("SELECT id, part_name, $stockColumn as current_stock FROM stock_list WHERE part_name_norm = ? LIMIT 1");
            $findStmt->execute([$variationNorm]);
            $foundPartData = $findStmt->fetch(PDO::FETCH_ASSOC);
            if ($foundPartData) break;
        }
                
        if ($foundPartData && !empty($foundPartData['part_name'])) {
            $foundPart = $foundPartData['part_name'];
            $stockId = $foundPartData['id'] ?? null;
            $oldStock = (int)($foundPartData['current_stock'] ?? 0);

            if ($action === 'DEDUCT') {
                if ($stockColumn === 'stock_gombak') {
                    $updateSql = "UPDATE stock_list SET stock_gombak = stock_gombak - ? WHERE part_name = ? AND stock_gombak >= ?";
                } else {
                    $updateSql = "UPDATE stock_list SET stock_sr = stock_sr - ? WHERE part_name = ? AND stock_sr >= ?";
                }
                $stmt = $pdo->prepare($updateSql);
                $stmt->execute([$qty, $foundPart, $qty]);
            } else {
                if ($stockColumn === 'stock_gombak') {
                    $updateSql = "UPDATE stock_list SET stock_gombak = stock_gombak + ? WHERE part_name = ?";
                } else {
                    $updateSql = "UPDATE stock_list SET stock_sr = stock_sr + ? WHERE part_name = ?";
                }
                $stmt = $pdo->prepare($updateSql);
                $stmt->execute([$qty, $foundPart]);
            }
            $affected = $stmt->rowCount();

            if ($affected > 0) {
                $branchName = ($stockColumn === 'stock_gombak') ? 'Gombak' : 'Sri Rampai';
                $jobId = $job['job_id'] ?? null;
                if ($action === 'RESTORE') {
                    logStockMovement($pdo, $stockId, $foundPart, $branchName, 'REFUND', $qty, $jobId, 'Stock circled back (refund/job edit)');
                } else {
                    logStockMovement($pdo, $stockId, $foundPart, $branchName, 'OUT', $qty, $jobId, 'Used in repair/sale');
                }
                $verifyStmt = $pdo->prepare("SELECT $stockColumn FROM stock_list WHERE part_name = ?");
                $verifyStmt->execute([$foundPart]);
                $newStock = (int)$verifyStmt->fetchColumn();
                $expectedStock = ($action === 'RESTORE') ? ($oldStock + $qty) : ($oldStock - $qty);
                if ($newStock == $expectedStock) {
                    if (f2f_verbose_log()) {
                        error_log("Stock $action OK: '$foundPart' qty=$qty $stockColumn. Old=$oldStock New=$newStock");
                    }
                } else {
                    error_log("Stock $action mismatch: '$foundPart' qty=$qty Expected=$expectedStock Actual=$newStock");
                }
            } else {
                error_log("Stock $action FAIL (0 rows): '$foundPart' qty=$qty Old=$oldStock");
            }
        } else {
            // Fallback: fuzzy match (LOWER(TRIM) for consistency)
            $searchTerm = str_replace(['1set', '2set', '3set', '4set', '5set', 'set'], '', $normalizedPartName);
            $searchTerm = trim(preg_replace('/\s+/', ' ', $searchTerm));
            if (!empty($searchTerm)) {
                $fuzzyStmt = $pdo->prepare("SELECT id, part_name, $stockColumn as current_stock FROM stock_list WHERE part_name_norm LIKE ? LIMIT 1");
                $fuzzyStmt->execute(['%' . normalizeStockName($searchTerm) . '%']);
                $fuzzyPartData = $fuzzyStmt->fetch(PDO::FETCH_ASSOC);

                if ($fuzzyPartData && !empty($fuzzyPartData['part_name'])) {
                    $foundPart = $fuzzyPartData['part_name'];
                    $stockId = $fuzzyPartData['id'] ?? null;
                    $oldStock = (int)($fuzzyPartData['current_stock'] ?? 0);

                    if ($stockColumn === 'stock_gombak') {
                        $updateSql = "UPDATE stock_list SET stock_gombak = stock_gombak $operator ? WHERE part_name = ?";
                    } else {
                        $updateSql = "UPDATE stock_list SET stock_sr = stock_sr $operator ? WHERE part_name = ?";
                    }

                    $stmt = $pdo->prepare($updateSql);
                    $stmt->execute([$qty, $foundPart]);
                    $affected = $stmt->rowCount();

                    if ($affected > 0) {
                        $branchName = ($stockColumn === 'stock_gombak') ? 'Gombak' : 'Sri Rampai';
                        $jobId = $job['job_id'] ?? null;
                        if ($action === 'RESTORE') {
                            logStockMovement($pdo, $stockId, $foundPart, $branchName, 'REFUND', $qty, $jobId, 'Stock circled back (refund/job edit)');
                        } else {
                            logStockMovement($pdo, $stockId, $foundPart, $branchName, 'OUT', $qty, $jobId, 'Used in repair/sale');
                        }
                        if (f2f_verbose_log()) {
                            error_log("Stock $action OK (fuzzy): '$foundPart' qty=$qty $stockColumn. Old=$oldStock");
                        }
                    } else {
                        error_log("Stock $action FAIL fuzzy (0 rows): '$foundPart' qty=$qty");
                    }
                } else {
                    error_log("Stock $action: No match for '$normalizedPartName' (qty=$qty) - not in stock_list");
                }
            }
        }
    }
}

// ---------------------------------------------------------------------------
// Rate Limiting for API calls (file-backed IP store; survives session clearing)
// ---------------------------------------------------------------------------
$ip = $_SERVER['REMOTE_ADDR'] ?? 'unknown';
$rateLimitMax    = 100; // Max requests per minute per IP
$rateLimitWindow = 60;  // 1 minute window

$rateDir = __DIR__ . '/backups/ratelimit';
if (!is_dir($rateDir)) {
    @mkdir($rateDir, 0750, true);
}
$rateFile = $rateDir . '/' . substr(sha1($ip), 0, 16) . '.json';

$rateLimit = ['count' => 0, 'reset_time' => time() + $rateLimitWindow];
if (is_file($rateFile)) {
    $decoded = json_decode((string) @file_get_contents($rateFile), true);
    if (is_array($decoded) && isset($decoded['count'], $decoded['reset_time'])) {
        $rateLimit = $decoded;
    }
}
if (time() > (int) $rateLimit['reset_time']) {
    $rateLimit = ['count' => 0, 'reset_time' => time() + $rateLimitWindow];
}
$rateLimit['count'] = ((int) $rateLimit['count']) + 1;

// Best-effort atomic write; failure to persist must NOT break the API call.
$tmp = $rateFile . '.tmp';
if (@file_put_contents($tmp, json_encode($rateLimit), LOCK_EX) !== false) {
    @rename($tmp, $rateFile);
}

if ($rateLimit['count'] > $rateLimitMax) {
    http_response_code(429);
    sendError("Rate limit exceeded. Please slow down.");
}

try {
    $action = $_POST['action'] ?? '';
    if(!$action) {
        $rawInput = file_get_contents('php://input');
        $in = json_decode($rawInput, true);
        $action = $in['action'] ?? $_GET['action'] ?? '';
    } else {
        // For FormData uploads, populate $in from $_POST
        $in = $_POST;
    }
    
    // CSRF Protection for state-changing operations (except GET requests)
    $stateChangingActions = ['CREATE_JOB', 'CREATE_RETAIL', 'update_full_job', 'delete_job', 'insert_stock_item', 
                            'update_full_stock', 'delete_stock_item', 'transfer_stock', 'create_user', 'delete_user', 
                            'update_user_permissions', 'add_expense', 'delete_expense', 'refund_retail', 'restore_job',
                            // v18.0 Archive Console (master-only):
                            'archive_move', 'archive_restore_one', 'archive_restore_range'];
    
    if (in_array($action, $stateChangingActions) && $_SERVER['REQUEST_METHOD'] !== 'GET') {
        // Check CSRF token from multiple sources: header, request body, or POST data
        $token = '';
        if (!empty($_SERVER['HTTP_X_CSRF_TOKEN'])) {
            $token = $_SERVER['HTTP_X_CSRF_TOKEN'];
        } elseif (isset($in['csrf_token'])) {
            $token = $in['csrf_token'];
        } elseif (isset($_POST['csrf_token'])) {
            $token = $_POST['csrf_token'];
        }
        
        if (empty($token) || !isset($_SESSION['csrf_token']) || !hash_equals($_SESSION['csrf_token'], $token)) {
            sendError("CSRF token validation failed. Please refresh the page.");
        }
    }

    if (isset($_SESSION['assigned_branch']) && !isAdmin()) {
        $enforced = $_SESSION['assigned_branch'];
        if (isset($in)) { $in['branch'] = $enforced; }
        if (isset($_GET['branch'])) { $_GET['branch'] = $enforced; }
    }

    // --- LOGIN ---
    if ($action === 'login') {
        require_once 'helpers.php';
        $user = sanitizeString($in['username']);
        $pass = $in['password'];
        $ip = $_SERVER['REMOTE_ADDR'] ?? 'unknown';
        
        // Check rate limiting
        $maxAttempts = getConfig('security.max_login_attempts', 5);
        $lockoutDuration = getConfig('security.lockout_duration', 900);
        
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM login_attempts 
            WHERE (username = ? OR ip_address = ?) 
            AND attempt_time > DATE_SUB(NOW(), INTERVAL ? SECOND)
            AND success = 0");
        $stmt->execute([$user, $ip, $lockoutDuration]);
        $failedAttempts = $stmt->fetchColumn();
        
        if ($failedAttempts >= $maxAttempts) {
            $stmt = $pdo->prepare("INSERT INTO login_attempts (username, ip_address, success) VALUES (?, ?, 0)");
            $stmt->execute([$user, $ip]);
            sendError("Too many failed login attempts. Please try again in " . round($lockoutDuration / 60) . " minutes.");
        }
        
        $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
        $stmt->execute([$user]);
        $u = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($u && password_verify($pass, $u['password'])) {
            // Clear failed attempts on successful login
            $pdo->prepare("DELETE FROM login_attempts WHERE username = ? OR ip_address = ?")->execute([$user, $ip]);
            
            // Record successful login
            $stmt = $pdo->prepare("INSERT INTO login_attempts (username, ip_address, success) VALUES (?, ?, 1)");
            $stmt->execute([$user, $ip]);
            
            $_SESSION['loggedin'] = true;
            $_SESSION['user'] = $u['username'];
            $_SESSION['role'] = $u['role']; 
            $_SESSION['user_id'] = $u['id'];
            $_SESSION['assigned_branch'] = $u['assigned_branch'] ?? 'Gombak';
            $_SESSION['restrict_branch'] = ($u['restrict_branch'] == 1); 
            $_SESSION['branch'] = $_SESSION['assigned_branch'];
            logActivity($pdo, 'LOGIN', "User logged in");
            sendSuccess(['redirect' => 'dashboard.php']);
        } else {
            // Record failed attempt
            $stmt = $pdo->prepare("INSERT INTO login_attempts (username, ip_address, success) VALUES (?, ?, 0)");
            $stmt->execute([$user, $ip]);
            sendError("Invalid credentials.");
        }
    }

    if (!isset($_SESSION['loggedin']) || $_SESSION['loggedin'] !== true) {
        sendError('Auth failed: Please log in again.');
    }

    // ================= METHOD-AGNOSTIC ENDPOINTS =================
    if ($action === 'get_stock_movements') {
        $stockId = (int)($in['stock_id'] ?? 0);
        $limit = min(500, max(50, (int)($in['limit'] ?? 100)));
        if ($stockId <= 0) {
            sendError("Stock ID required.");
        }
        $stmt = $pdo->prepare("SELECT sm.* FROM stock_movements sm WHERE sm.stock_id = ? ORDER BY sm.movement_date DESC LIMIT ?");
        $stmt->execute([$stockId, $limit]);
        $movements = $stmt->fetchAll(PDO::FETCH_ASSOC);
        sendSuccess(['movements' => $movements]);
    }

    // ================= GET REQUESTS =================
    if ($_SERVER['REQUEST_METHOD'] === 'GET') {
        
        if ($action === 'fetch_jobs' || $action === 'fetch_retail_history') {
            $isRetail = ($action === 'fetch_retail_history');
            
            $can_edit = checkLivePermission($pdo, $_SESSION['user_id'], 'can_edit');
            $can_delete = checkLivePermission($pdo, $_SESSION['user_id'], 'can_delete');
            $can_refund = checkLivePermission($pdo, $_SESSION['user_id'], 'can_refund');
            $can_restore = checkLivePermission($pdo, $_SESSION['user_id'], 'can_restore');
            
            $sql = "SELECT id, job_id, invoice_id, branch, date, pickup_date, customer, phone, model, imei, repair, remarks, price, deposit, warranty, status, payment_method, payment_status, completed_at, token, created_at FROM jobs WHERE is_deleted = 0";
            $params = [];
            if ($isRetail) {
                $sql .= " AND model = ?";
                $params[] = 'Retail Sale';
                // For retail history, ONLY return IV- orders (exclude JS- repair jobs)
                $sql .= " AND job_id LIKE 'IV-S%'";
            } else {
                $sql .= " AND model <> ?";
                $params[] = 'Retail Sale';
            }
            
            if (!isAdmin()) {
                $assigned = $_SESSION['assigned_branch'] ?? 'Gombak';
                $sql .= " AND branch = ?";
                $params[] = $assigned;
            }
            
            // Order by date field (entry date) instead of created_at for better chronological order
            // Using LIMIT to prevent loading too much data at once
            $sql .= " ORDER BY date DESC, created_at DESC LIMIT 500";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $jobs = $stmt->fetchAll(PDO::FETCH_ASSOC);

            if ($isRetail && count($jobs) > 0) {
                $refundSearchList = [];
                $jobIdMap = [];
                foreach ($jobs as $index => $job) {
                    if (strpos($job['repair'], 'REFUND FOR:') === false) {
                        $searchStr = 'REFUND FOR: ' . $job['job_id'];
                        $refundSearchList[] = $searchStr;
                        $jobIdMap[$searchStr] = $index;
                    }
                }
                if (!empty($refundSearchList)) {
                    $placeholders = implode(',', array_fill(0, count($refundSearchList), '?'));
                    $rStmt = $pdo->prepare("SELECT repair, remarks, date FROM jobs WHERE repair IN ($placeholders) AND is_deleted = 0");
                    $rStmt->execute($refundSearchList);
                    $refunds = $rStmt->fetchAll(PDO::FETCH_ASSOC);
                    foreach ($refunds as $refund) {
                        $targetStr = $refund['repair'];
                        if (isset($jobIdMap[$targetStr])) {
                            $jobIndex = $jobIdMap[$targetStr];
                            $staff = 'Unknown';
                            if (preg_match('/Processed by (.*)/', $refund['remarks'], $m)) { $staff = trim($m[1]); }
                            $jobs[$jobIndex]['refund_info'] = [ 'by' => $staff, 'date' => $refund['date'] ];
                        }
                    }
                }
            }

            sendSuccess([
                'jobs' => $jobs,
                'permissions' => [
                    'can_edit' => $can_edit, 
                    'can_delete' => $can_delete, 
                    'can_refund' => $can_refund,
                    'can_restore' => $can_restore
                ]
            ]);
        }

        elseif ($action === 'fetch_deleted_jobs') {
            if (!checkLivePermission($pdo, $_SESSION['user_id'], 'can_restore')) {
                sendError("ACCESS DENIED: You do not have permission to view deleted items.");
            }
            $hasDeleteMetaCols = false;
            try {
                $checkReason = $pdo->query("SHOW COLUMNS FROM jobs LIKE 'delete_reason'");
                $checkBy = $pdo->query("SHOW COLUMNS FROM jobs LIKE 'deleted_by'");
                $hasDeleteMetaCols = ($checkReason && $checkReason->rowCount() > 0 && $checkBy && $checkBy->rowCount() > 0);
            } catch (Exception $e) {
                $hasDeleteMetaCols = false;
            }

            if ($hasDeleteMetaCols) {
                $sql = "SELECT id, job_id, branch, date, customer, phone, model, repair, remarks, delete_reason, deleted_by, price, status, deleted_at, token, created_at FROM jobs WHERE is_deleted = 1";
            } else {
                // Backward-compatible fallback for databases that have not migrated yet.
                $sql = "SELECT id, job_id, branch, date, customer, phone, model, repair, remarks, '' as delete_reason, NULL as deleted_by, price, status, deleted_at, token, created_at FROM jobs WHERE is_deleted = 1";
            }
            $params = [];
            if (!isAdmin()) {
                $assigned = $_SESSION['assigned_branch'] ?? 'Gombak';
                $sql .= " AND branch = ?";
                $params[] = $assigned;
            }
            $sql .= " ORDER BY deleted_at DESC, created_at DESC LIMIT 200";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            sendSuccess(['jobs' => $stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }
        
        elseif ($action === 'fetch_stock') {
            try {
                if (!isset($_SESSION['_schema_cache'])) {
                    $checkCol = $pdo->query("SHOW COLUMNS FROM stock_list LIKE 'category'");
                    $checkShortcode = $pdo->query("SHOW COLUMNS FROM stock_list LIKE 'shortcode'");
                    $_SESSION['_schema_cache'] = [
                        'hasCategory' => $checkCol->rowCount() > 0,
                        'hasShortcode' => $checkShortcode->rowCount() > 0
                    ];
                }
                $hasCategory = $_SESSION['_schema_cache']['hasCategory'];
                $hasShortcode = $_SESSION['_schema_cache']['hasShortcode'];
                
                // Use appropriate ORDER BY based on available columns
                if ($hasCategory) {
                    $stmt = $pdo->query("SELECT id, type, model_group, device_model, part_name, category, brand, cost, selling_price, supplier, stock_gombak, stock_sr, shortcode FROM stock_list ORDER BY type, category, part_name");
                } else {
                    $stmt = $pdo->query("SELECT id, type, model_group, device_model, part_name, category, brand, cost, selling_price, supplier, stock_gombak, stock_sr FROM stock_list ORDER BY type, model_group, part_name");
                }
                
                $raw = $stmt->fetchAll(PDO::FETCH_ASSOC);
                $data = ['Part'=>[], 'Retail'=>[]];
                
                foreach($raw as $r) {
                    // Normalize type: handle both "PART"/"RETAIL" (uppercase) and "Part"/"Retail" (title case)
                    $typeRaw = $r['type'] ?: 'Part';
                    $t = ucfirst(strtolower($typeRaw)); // Convert "PART" -> "Part", "RETAIL" -> "Retail"
                    if ($t !== 'Part' && $t !== 'Retail') {
                        $t = 'Part'; // Default fallback
                    }
                    
                    // Use category if available, otherwise use model_group or 'Other'
                    if ($hasCategory && isset($r['category']) && !empty($r['category'])) {
                        $cat = $r['category'];
                    } else {
                        $cat = $r['model_group'] ?? 'General';
                    }
                    
                    // Ensure shortcode field exists in response (even if empty)
                    if (!$hasShortcode) {
                        $r['shortcode'] = '';
                    }
                    if (!$hasCategory) {
                        $r['category'] = $cat;
                    }
                    
                    if(!isset($data[$t][$cat])) $data[$t][$cat]=[];
                    $data[$t][$cat][]=$r;
                }
                sendSuccess(['stock'=>$data]);
            } catch (PDOException $e) {
                // If query fails, try without category column
                try {
                    $stmt = $pdo->query("SELECT id, type, model_group, device_model, part_name, brand, cost, selling_price, supplier, stock_gombak, stock_sr FROM stock_list ORDER BY type, model_group, part_name");
                    $raw = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    $data = ['Part'=>[], 'Retail'=>[]];
                    foreach($raw as $r) {
                        $typeRaw = $r['type'] ?: 'Part';
                        $t = ucfirst(strtolower($typeRaw));
                        if ($t !== 'Part' && $t !== 'Retail') {
                            $t = 'Part'; // Default fallback
                        }
                        $cat = $r['model_group'] ?? 'General';
                        $r['shortcode'] = '';
                        $r['category'] = $cat;
                        if(!isset($data[$t][$cat])) $data[$t][$cat]=[];
                        $data[$t][$cat][]=$r;
                    }
                    sendSuccess(['stock'=>$data]);
                } catch (PDOException $e2) {
                    sendError("Database error: " . $e2->getMessage());
                }
            }
        }
        elseif ($action === 'get_transfers') {
            $stmt = $pdo->query("SELECT id, part_name, from_branch, to_branch, qty, date, transferred_by FROM stock_transfers ORDER BY date DESC LIMIT 50");
            sendSuccess(['logs'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }
        elseif ($action === 'get_activity_logs') {
            $stmt = $pdo->query("SELECT id, user, action, description, created_at FROM activity_logs ORDER BY created_at DESC LIMIT 50");
            sendSuccess(['logs'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }
        elseif ($action === 'get_stock_logs') {
            $stmt = $pdo->prepare("SELECT id, user, action, description, created_at FROM activity_logs WHERE action IN ('ADD_STOCK', 'UPDATE_STOCK', 'DELETE_STOCK', 'DELETE_STOCK_LOCATION', 'TRANSFER_STOCK', 'EDIT_STOCK') ORDER BY created_at DESC LIMIT 200");
            $stmt->execute();
            sendSuccess(['logs' => $stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }
        elseif ($action === 'fetch_device_models') {
            // Fetch unique device models from stock_list for Part type items
            try {
                $stmt = $pdo->query("SELECT DISTINCT device_model FROM stock_list WHERE type = 'Part' AND device_model IS NOT NULL AND device_model != '' AND device_model != 'Universal' ORDER BY device_model ASC");
                $models = $stmt->fetchAll(PDO::FETCH_COLUMN);
                sendSuccess(['models' => $models]);
            } catch (PDOException $e) {
                sendSuccess(['models' => []]); // Return empty array on error
            }
        }
        elseif ($action === 'get_users') {
            if(!isAdmin()) sendError("Access Denied");
            $stmt = $pdo->query("SELECT id, username, role, can_edit, can_delete, can_transfer, can_refund, can_restore, assigned_branch, restrict_branch, can_add_cross_branch_stock, can_view_stock_logs, can_merge_stock, can_bulk_ops, can_bulk_print, can_bulk_import, can_convert_uppercase FROM users ORDER BY username");
            sendSuccess(['users'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }
        elseif ($action === 'get_low_stock_alerts') {
            // Get items that are below reorder point or low stock threshold
            $branch = $in['branch'] ?? 'all';
            $branchCondition = "";
            if ($branch === 'Gombak') {
                $branchCondition = "AND (stock_gombak <= COALESCE(reorder_point_gombak, 5) OR stock_gombak <= COALESCE(low_stock_threshold_gombak, 3))";
            } elseif ($branch === 'Sri Rampai') {
                $branchCondition = "AND (stock_sr <= COALESCE(reorder_point_sr, 5) OR stock_sr <= COALESCE(low_stock_threshold_sr, 3))";
            } else {
                $branchCondition = "AND ((stock_gombak <= COALESCE(reorder_point_gombak, 5) OR stock_gombak <= COALESCE(low_stock_threshold_gombak, 3)) 
                                    OR (stock_sr <= COALESCE(reorder_point_sr, 5) OR stock_sr <= COALESCE(low_stock_threshold_sr, 3)))";
            }
            
            $stmt = $pdo->prepare("SELECT id, part_name, type, category, supplier, cost, 
                                   stock_gombak, stock_sr, 
                                   COALESCE(reorder_point_gombak, 5) as reorder_gombak,
                                   COALESCE(reorder_point_sr, 5) as reorder_sr,
                                   COALESCE(low_stock_threshold_gombak, 3) as threshold_gombak,
                                   COALESCE(low_stock_threshold_sr, 3) as threshold_sr
                                   FROM stock_list 
                                   WHERE (stock_gombak > 0 OR stock_sr > 0)
                                   $branchCondition
                                   ORDER BY part_name");
            $stmt->execute();
            $alerts = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Add status for each item
            foreach ($alerts as &$item) {
                $item['status'] = [];
                if ($item['stock_gombak'] <= $item['threshold_gombak']) {
                    $item['status'][] = ['branch' => 'Gombak', 'level' => 'critical', 'stock' => $item['stock_gombak']];
                } elseif ($item['stock_gombak'] <= $item['reorder_gombak']) {
                    $item['status'][] = ['branch' => 'Gombak', 'level' => 'warning', 'stock' => $item['stock_gombak']];
                }
                if ($item['stock_sr'] <= $item['threshold_sr']) {
                    $item['status'][] = ['branch' => 'Sri Rampai', 'level' => 'critical', 'stock' => $item['stock_sr']];
                } elseif ($item['stock_sr'] <= $item['reorder_sr']) {
                    $item['status'][] = ['branch' => 'Sri Rampai', 'level' => 'warning', 'stock' => $item['stock_sr']];
                }
            }
            
            sendSuccess(['alerts' => $alerts, 'count' => count($alerts)]);
        }
        elseif ($action === 'get_stock_valuation') {
            // Calculate total stock valuation by branch and category
            $stmt = $pdo->query("SELECT 
                                type,
                                COALESCE(category, 'Other') as category,
                                SUM(stock_gombak * COALESCE(cost, 0)) as value_gombak,
                                SUM(stock_sr * COALESCE(cost, 0)) as value_sr,
                                SUM((stock_gombak + stock_sr) * COALESCE(cost, 0)) as total_value,
                                SUM(stock_gombak) as qty_gombak,
                                SUM(stock_sr) as qty_sr,
                                COUNT(*) as item_count
                                FROM stock_list
                                WHERE (stock_gombak > 0 OR stock_sr > 0)
                                GROUP BY type, category
                                ORDER BY type, category");
            $valuation = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Calculate totals
            $totals = [
                'gombak_value' => 0,
                'sr_value' => 0,
                'total_value' => 0,
                'total_items' => 0,
                'total_qty_gombak' => 0,
                'total_qty_sr' => 0
            ];
            
            foreach ($valuation as $v) {
                $totals['gombak_value'] += (float)$v['value_gombak'];
                $totals['sr_value'] += (float)$v['value_sr'];
                $totals['total_value'] += (float)$v['total_value'];
                $totals['total_items'] += (int)$v['item_count'];
                $totals['total_qty_gombak'] += (int)$v['qty_gombak'];
                $totals['total_qty_sr'] += (int)$v['qty_sr'];
            }
            
            sendSuccess(['valuation' => $valuation, 'totals' => $totals]);
        }
        elseif ($action === 'get_purchase_orders') {
            $status = $in['status'] ?? 'all';
            $sql = "SELECT po.*, 
                    COUNT(poi.id) as item_count,
                    SUM(poi.quantity - COALESCE(poi.received_qty, 0)) as pending_qty
                    FROM purchase_orders po
                    LEFT JOIN purchase_order_items poi ON po.id = poi.po_id
                    WHERE 1=1";
            $params = [];
            
            if ($status !== 'all') {
                $sql .= " AND po.status = ?";
                $params[] = $status;
            }
            
            $sql .= " GROUP BY po.id ORDER BY po.order_date DESC, po.created_at DESC LIMIT 100";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $orders = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            if (!empty($orders)) {
                $poIds = array_column($orders, 'id');
                $placeholders = implode(',', array_fill(0, count($poIds), '?'));
                $itemStmt = $pdo->prepare("SELECT po_id, part_name, qty, cost FROM purchase_order_items WHERE po_id IN ($placeholders) ORDER BY id");
                $itemStmt->execute($poIds);
                $allPoItems = $itemStmt->fetchAll(PDO::FETCH_ASSOC);
                $itemsByPo = [];
                foreach ($allPoItems as $item) { $itemsByPo[$item['po_id']][] = $item; }
                foreach ($orders as &$order) { $order['items'] = $itemsByPo[$order['id']] ?? []; }
                unset($order);
            }
            
            sendSuccess(['orders' => $orders]);
        }
        elseif ($action === 'get_inventory_analytics') {
            // Get inventory analytics: turnover rates, dead stock, ABC analysis
            $days = (int)($in['days'] ?? 90); // Default 90 days
            
            // Calculate turnover rate (items sold / average stock)
            $stmt = $pdo->prepare("
                SELECT 
                    sl.part_name,
                    sl.type,
                    sl.category,
                    sl.stock_gombak,
                    sl.stock_sr,
                    sl.cost,
                    COALESCE(sales.sold_qty, 0) as sold_qty,
                    COALESCE(sales.total_revenue, 0) as revenue,
                    CASE 
                        WHEN (sl.stock_gombak + sl.stock_sr) > 0 
                        THEN COALESCE(sales.sold_qty, 0) / ((sl.stock_gombak + sl.stock_sr) / 2.0)
                        ELSE 0 
                    END as turnover_rate,
                    sl.last_sold_date,
                    DATEDIFF(CURDATE(), COALESCE(sl.last_sold_date, sl.created_at)) as days_since_sold
                FROM stock_list sl
                LEFT JOIN (
                    SELECT 
                        part_name,
                        SUM(quantity) as sold_qty,
                        SUM(quantity * price) as total_revenue
                    FROM (
                        SELECT 
                            SUBSTRING_INDEX(SUBSTRING_INDEX(repair, ' (x', 1), ' || ', numbers.n) as part_name,
                            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(repair, ' (x', -1), ')', 1) AS UNSIGNED) as quantity,
                            price
                        FROM jobs
                        CROSS JOIN (
                            SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
                        ) numbers
                        WHERE repair LIKE '%(x%' 
                        AND date >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
                        AND is_deleted = 0
                    ) as sales_data
                    WHERE part_name IS NOT NULL AND part_name != ''
                    GROUP BY part_name
                ) as sales ON LOWER(TRIM(sl.part_name)) = LOWER(TRIM(sales.part_name))
                WHERE (sl.stock_gombak > 0 OR sl.stock_sr > 0)
                ORDER BY turnover_rate DESC, revenue DESC
            ");
            $stmt->execute([$days]);
            $analytics = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Classify dead stock (no sales in last 90 days and has stock)
            $deadStock = [];
            $abcAnalysis = ['A' => [], 'B' => [], 'C' => []];
            $totalRevenue = 0;
            
            foreach ($analytics as $item) {
                $revenue = (float)$item['revenue'];
                $totalRevenue += $revenue;
                
                if ($item['days_since_sold'] > $days && ($item['stock_gombak'] > 0 || $item['stock_sr'] > 0)) {
                    $deadStock[] = $item;
                }
            }
            
            // ABC Analysis: A = top 80% revenue, B = next 15%, C = remaining 5%
            $cumulativeRevenue = 0;
            foreach ($analytics as $item) {
                $revenue = (float)$item['revenue'];
                $cumulativeRevenue += $revenue;
                $percentage = $totalRevenue > 0 ? ($cumulativeRevenue / $totalRevenue) * 100 : 0;
                
                if ($percentage <= 80) {
                    $item['abc_class'] = 'A';
                    $abcAnalysis['A'][] = $item;
                } elseif ($percentage <= 95) {
                    $item['abc_class'] = 'B';
                    $abcAnalysis['B'][] = $item;
                } else {
                    $item['abc_class'] = 'C';
                    $abcAnalysis['C'][] = $item;
                }
            }
            
            sendSuccess([
                'analytics' => $analytics,
                'dead_stock' => array_slice($deadStock, 0, 50),
                'abc_analysis' => $abcAnalysis,
                'summary' => [
                    'total_items' => count($analytics),
                    'dead_stock_count' => count($deadStock),
                    'total_revenue' => $totalRevenue,
                    'period_days' => $days
                ]
            ]);
        }

        // === v18.0 ARCHIVE CONSOLE: READ-ONLY ACTIONS ===
        elseif ($action === 'archive_preview') {
            archive_master_gate();
            $scope = (string)($_GET['scope'] ?? '');
            $meta = archive_scope_meta($scope);
            if (!$meta) sendError("Unknown archive scope.");
            $days = max(30, min(7300, (int)($_GET['days'] ?? 365)));
            [$extras, $params] = archive_filter_clause($scope, $_GET);

            $check = $pdo->prepare("SHOW TABLES LIKE ?");
            $check->execute([$meta['live']]);
            if ($check->rowCount() === 0) {
                sendSuccess(['scope' => $scope, 'count' => 0, 'sample' => [], 'note' => 'Live table missing']);
            }

            $whereSql = $meta['where'];
            if (!empty($extras)) { $whereSql .= " AND " . implode(' AND ', $extras); }
            $whereSql .= " AND `{$meta['ts_col']}` < DATE_SUB(NOW(), INTERVAL ? DAY)";
            $allParams = array_merge($params, [$days]);

            $cntStmt = $pdo->prepare("SELECT COUNT(*) FROM `{$meta['live']}` WHERE $whereSql");
            $cntStmt->execute($allParams);
            $count = (int) $cntStmt->fetchColumn();

            $sampleStmt = $pdo->prepare("SELECT * FROM `{$meta['live']}` WHERE $whereSql ORDER BY `{$meta['ts_col']}` ASC LIMIT 5");
            $sampleStmt->execute($allParams);
            $sample = $sampleStmt->fetchAll(PDO::FETCH_ASSOC);

            sendSuccess([
                'scope'  => $scope,
                'label'  => $meta['label'],
                'days'   => $days,
                'count'  => $count,
                'sample' => $sample,
            ]);
        }

        elseif ($action === 'archive_list') {
            archive_master_gate();
            $scope = (string)($_GET['scope'] ?? '');
            $meta = archive_scope_meta($scope);
            if (!$meta) sendError("Unknown archive scope.");

            $check = $pdo->prepare("SHOW TABLES LIKE ?");
            $check->execute([$meta['archive']]);
            if ($check->rowCount() === 0) {
                sendSuccess(['scope' => $scope, 'rows' => [], 'total' => 0, 'page' => 1, 'page_size' => 50, 'note' => 'Archive table empty/missing']);
            }

            $page = max(1, (int)($_GET['page'] ?? 1));
            $pageSize = max(10, min(100, (int)($_GET['page_size'] ?? 50)));
            $offset = ($page - 1) * $pageSize;
            $search = trim((string)($_GET['search'] ?? ''));
            $dateFrom = trim((string)($_GET['date_from'] ?? ''));
            $dateTo = trim((string)($_GET['date_to'] ?? ''));

            // Scope-baseline WHERE (so retail archive doesn't show dropoff rows etc.)
            $whereSql = $meta['where'];
            $params = [];

            if ($dateFrom !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateFrom)) {
                $whereSql .= " AND `{$meta['ts_col']}` >= ?";
                $params[] = $dateFrom;
            }
            if ($dateTo !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateTo)) {
                $whereSql .= " AND `{$meta['ts_col']}` <= ?";
                $params[] = $dateTo;
            }
            if ($search !== '') {
                $like = '%' . $search . '%';
                if (in_array($scope, ['dropoff', 'retail'], true)) {
                    $whereSql .= " AND (job_id LIKE ? OR customer LIKE ? OR phone LIKE ? OR imei LIKE ?)";
                    array_push($params, $like, $like, $like, $like);
                } elseif ($scope === 'expenses') {
                    $whereSql .= " AND (description LIKE ? OR created_by LIKE ?)";
                    array_push($params, $like, $like);
                } else {
                    $whereSql .= " AND description LIKE ?";
                    $params[] = $like;
                }
            }

            $cntStmt = $pdo->prepare("SELECT COUNT(*) FROM `{$meta['archive']}` WHERE $whereSql");
            $cntStmt->execute($params);
            $total = (int) $cntStmt->fetchColumn();

            $rowStmt = $pdo->prepare("SELECT * FROM `{$meta['archive']}` WHERE $whereSql ORDER BY `{$meta['ts_col']}` DESC LIMIT $pageSize OFFSET $offset");
            $rowStmt->execute($params);
            $rows = $rowStmt->fetchAll(PDO::FETCH_ASSOC);

            sendSuccess([
                'scope'     => $scope,
                'label'     => $meta['label'],
                'rows'      => $rows,
                'total'     => $total,
                'page'      => $page,
                'page_size' => $pageSize,
            ]);
        }
    }

    // ================= POST REQUESTS =================
    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
        
        // --- NEW: UPLOAD REPORT PDF ---
        if ($action === 'upload_report_pdf') {
            if (!isset($_FILES['pdf']) || $_FILES['pdf']['error'] !== UPLOAD_ERR_OK) {
                sendError("File upload failed.");
            }
            
            $uploadDir = 'reports/';
            if (!is_dir($uploadDir)) {
                mkdir($uploadDir, 0755, true);
            }
            
            // Generate unique filename
            $filename = 'report_' . date('Ymd_His') . '_' . uniqid() . '.pdf';
            $targetPath = $uploadDir . $filename;
            
            if (move_uploaded_file($_FILES['pdf']['tmp_name'], $targetPath)) {
                // Return full URL
                $protocol = isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https" : "http";
                $url = "$protocol://{$_SERVER['HTTP_HOST']}/$targetPath";
                sendSuccess(['url' => $url]);
            } else {
                sendError("Failed to save PDF.");
            }
        }

        // --- NEW: ADD EXPENSE ---
        if ($action === 'add_expense') {
            if (!checkLivePermission($pdo, $_SESSION['user_id'], 'can_edit')) sendError("ACCESS DENIED.");
            $pdo->prepare("INSERT INTO expenses (branch, date, description, amount, created_by) VALUES (?, ?, ?, ?, ?)")
                ->execute([$in['branch'], $in['date'], $in['description'], $in['amount'], $_SESSION['user']]);
            logActivity($pdo, 'ADD_EXPENSE', "Added Expense: RM " . $in['amount']);
            sendSuccess();
        }

        // --- NEW: DELETE EXPENSE ---
        if ($action === 'delete_expense') {
            if (!checkLivePermission($pdo, $_SESSION['user_id'], 'can_delete')) sendError("ACCESS DENIED.");
            $pdo->prepare("DELETE FROM expenses WHERE id = ?")->execute([$in['id']]);
            logActivity($pdo, 'DEL_EXPENSE', "Deleted Expense ID: " . $in['id']);
            sendSuccess();
        }

        // --- NEW: CREATE CREDIT NOTE ---
        if ($action === 'create_credit_note') {
            // Only master users can create credit notes
            $user_role = $_SESSION['role'] ?? '';
            $username = strtolower($_SESSION['user'] ?? '');
            $is_master = ($user_role === 'master' || $username === 'endie');
            
            if (!$is_master) {
                sendError("ACCESS DENIED. Only master users can create credit notes.");
            }
            
            $stock_id = intval($in['stock_id'] ?? 0);
            $branch = $in['branch'] ?? '';
            $quantity = intval($in['quantity'] ?? 0);
            $date = $in['date'] ?? date('Y-m-d');
            $reason = $in['reason'] ?? '';
            $supplier = $in['supplier'] ?? 'Unknown';
            
            if (!$stock_id || !$branch || !$quantity || !$date) {
                sendError("Missing required fields");
            }
            
            if ($quantity < 1) {
                sendError("Quantity must be at least 1");
            }
            
            // Get stock item details
            $stmt = $pdo->prepare("SELECT part_name, stock_gombak, stock_sr FROM stock_list WHERE id = ?");
            $stmt->execute([$stock_id]);
            $stock = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if (!$stock) {
                sendError("Stock item not found");
            }
            
            // Check available stock
            $stockColumn = ($branch === 'Gombak') ? 'stock_gombak' : 'stock_sr';
            $availableStock = (int)$stock[$stockColumn];
            
            if ($quantity > $availableStock) {
                sendError("Insufficient stock. Available: {$availableStock}");
            }
            
            // Start transaction
            if ($pdo->inTransaction()) {
                $pdo->rollBack(); // Rollback any existing transaction
            }
            $pdo->beginTransaction();
            
            try {
                // Deduct stock
                $updateSql = "UPDATE stock_list SET {$stockColumn} = {$stockColumn} - ? WHERE id = ?";
                $updateStmt = $pdo->prepare($updateSql);
                $updateStmt->execute([$quantity, $stock_id]);
                
                // Create credit note record
                $insertStmt = $pdo->prepare("INSERT INTO credit_notes (stock_id, item_name, branch, quantity, date, reason, supplier, created_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
                $insertStmt->execute([
                    $stock_id,
                    $stock['part_name'],
                    $branch,
                    $quantity,
                    $date,
                    $reason,
                    $supplier,
                    $_SESSION['user']
                ]);
                
                // Commit transaction first
                $pdo->commit();
                
                // Log activity after successful commit (outside transaction)
                logActivity($pdo, 'CREDIT_NOTE', "Credit Note: {$stock['part_name']} x{$quantity} from {$branch} - {$reason}");
                
                sendSuccess(['message' => 'Credit note created successfully']);
            } catch (Exception $e) {
                // Only rollback if transaction is still active
                if ($pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                sendError("Failed to create credit note: " . $e->getMessage());
            }
        }

        // --- NEW: GET CREDIT NOTE HISTORY ---
        if ($action === 'get_credit_note_history') {
            // Only master users can view credit note history
            $user_role = $_SESSION['role'] ?? '';
            $username = strtolower($_SESSION['user'] ?? '');
            $is_master = ($user_role === 'master' || $username === 'endie');
            
            if (!$is_master) {
                sendError("ACCESS DENIED. Only master users can view credit note history.");
            }
            
            $stmt = $pdo->prepare("SELECT cn.*, sl.part_name as item_name FROM credit_notes cn 
                                   LEFT JOIN stock_list sl ON cn.stock_id = sl.id 
                                   ORDER BY cn.date DESC, cn.created_at DESC LIMIT 500");
            $stmt->execute();
            $history = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Use item_name from credit_notes if available, otherwise from stock_list
            foreach ($history as &$item) {
                if (empty($item['item_name'])) {
                    $item['item_name'] = $item['item_name'] ?? 'Unknown Item';
                }
            }
            
            sendSuccess(['history' => $history]);
        }

        // --- UPDATED: GET REPORT ---
        // --- UPDATED: GET REPORT ---
        if ($action === 'get_report') {
            $start = $in['start'] ?? date('Y-m-01'); 
            $end = $in['end'] ?? date('Y-m-d'); 
            $branch = $in['branch'] ?? 'all';
            
            if (!isAdmin() && !empty($_SESSION['assigned_branch'])) { $branch = $_SESSION['assigned_branch']; }
            
            // 1. Fetch Jobs - Include Retail Sales (Daily Repair/Sales) AND Refunds (negative amounts reduce totals)
            // Exclude Drop Off Repairs, Return, Pending, Cancelled
            $sql = "SELECT id, job_id, branch, date, customer, repair, remarks, price, deposit, status, payment_method, payment_status, job_cost, created_at FROM jobs WHERE date BETWEEN ? AND ? AND is_deleted = 0 AND status NOT IN ('Return', 'Pending', 'Cancelled') AND (model = 'Retail Sale' OR remarks = 'Retail Order')";
            $params = [$start, $end];
            if($branch !== 'all'){ $sql .= " AND branch = ?"; $params[] = $branch; }
            $sql .= " ORDER BY date DESC";
            
            $stmt = $pdo->prepare($sql); 
            $stmt->execute($params); 
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            $totalSales = 0; $totalCost = 0; 
            
            // OPTIMIZATION: Batch fetch all part costs to avoid N+1 queries
            // Only fetch costs for jobs that have 0 cost (might need recalculation)
            $allPartNames = [];
            foreach($rows as $r) {
                // Only recalculate if cost is exactly 0 (might be unset) and has repair items
                if (($r['job_cost'] == 0 || empty($r['job_cost'])) && !empty($r['repair'])) {
                    $items = explode(' || ', $r['repair']);
                    foreach ($items as $itemStr) {
                        if (preg_match('/^(.*?) \(x(\d+)\)/', $itemStr, $matches)) {
                            $rawName = trim($matches[1]);
                            $partName = trim(preg_replace('/\[SN:.*?\]/', '', $rawName));
                            $partName = trim(preg_replace('/\[W:.*?\]/', '', $partName));
                            $partName = trim(preg_replace('/\[Alt:.*?\]/', '', $partName));
                            if (!empty($partName)) {
                                $allPartNames[$partName] = true;
                            }
                        }
                    }
                }
            }
            
            // Batch fetch all costs in a single query
            $costMap = [];
            if (!empty($allPartNames)) {
                $placeholders = str_repeat('?,', count($allPartNames) - 1) . '?';
                $stmtCosts = $pdo->prepare("SELECT part_name, cost FROM stock_list WHERE part_name IN ($placeholders)");
                $stmtCosts->execute(array_keys($allPartNames));
                $costResults = $stmtCosts->fetchAll(PDO::FETCH_ASSOC);
                foreach ($costResults as $row) {
                    $costMap[$row['part_name']] = (float)$row['cost'];
                }
            }
            
            // Iterate using reference (&$r) so we can modify the row data directly
            foreach($rows as &$r) { 
                $p = (float)$r['price']; 
                $c = (float)($r['job_cost'] ?? 0); 
                
                // Only recalculate cost if it's 0 AND there are items to calculate from
                // This means the cost was never set (not manually set to 0)
                // If cost is already set (even if 0), it means it was manually set, so respect it
                // We check if repair string exists and has items, and cost is exactly 0 (not set)
                if ($c == 0 && !empty($r['repair'])) {
                    $calc = calculateItemsCost($pdo, $r['repair'], $costMap);
                    // Only use calculated cost if we found one AND the original was truly 0 (not manually set)
                    // Since we can't distinguish "never set" from "manually set to 0", we'll only
                    // recalculate if calculated cost > 0, meaning there were actual items with costs
                    if ($calc > 0) {
                        $c = $calc;
                        $r['job_cost'] = $c; 
                    }
                    // If calc is 0, it means either no items or items have no cost, so keep 0
                }
                
                $totalSales += $p; 
                $totalCost += $c; 
            }
            unset($r); // Break reference

            // 2. Fetch Expenses
            $sqlExp = "SELECT id, date, description, amount, branch, created_by FROM expenses WHERE date BETWEEN ? AND ?";
            $expParams = [$start, $end];
            if($branch !== 'all'){ $sqlExp .= " AND branch = ?"; $expParams[] = $branch; }
            $sqlExp .= " ORDER BY date DESC";
            $stmtExp = $pdo->prepare($sqlExp); $stmtExp->execute($expParams); $expenses = $stmtExp->fetchAll(PDO::FETCH_ASSOC);
            
            $totalExp = 0;
            foreach($expenses as $e) $totalExp += (float)$e['amount'];

            // Batch-load all stock data for category/cost lookups (avoids N+1 queries)
            $stockLookup = [];
            $allStockRows = $pdo->query("SELECT LOWER(TRIM(part_name)) as pn, category, cost FROM stock_list")->fetchAll(PDO::FETCH_ASSOC);
            foreach ($allStockRows as $sr) { $stockLookup[$sr['pn']] = $sr; }

            // Advanced Analytics
            $salesByStaff = [];
            $hourlySales = [];
            $topItems = [];
            $categoryProfit = [];
            
            foreach($rows as $r) {
                // Sales by staff (from remarks or created_by if available)
                $staff = 'Unknown';
                if (!empty($r['remarks']) && preg_match('/Processed by (.+?)(\||$)/i', $r['remarks'], $m)) {
                    $staff = trim($m[1]);
                }
                $salesByStaff[$staff] = ($salesByStaff[$staff] ?? 0) + (float)$r['price'];
                
                // Hourly sales (extract hour from created_at or date)
                $hour = '00:00';
                if (!empty($r['created_at'])) {
                    $hour = date('H:00', strtotime($r['created_at']));
                }
                $hourlySales[$hour] = ($hourlySales[$hour] ?? 0) + (float)$r['price'];
                
                // Top items and category profit
                if (!empty($r['repair'])) {
                    $items = explode(' || ', $r['repair']);
                    foreach ($items as $itemStr) {
                        if (preg_match('/^(.*?) \(x(\d+)\)/', $itemStr, $matches)) {
                            $rawName = trim($matches[1]);
                            $partName = trim(preg_replace('/\[SN:.*?\]/', '', $rawName));
                            $partName = trim(preg_replace('/\[W:.*?\]/', '', $partName));
                            $partName = trim(preg_replace('/\[Alt:.*?\]/', '', $partName));
                            $qty = (int)$matches[2];
                            
                            if (!empty($partName)) {
                                // Top items by quantity
                                $topItems[$partName] = ($topItems[$partName] ?? 0) + $qty;
                                
                                // Category profit (if we can determine category)
                                // First, try to extract price from item string if it has "@ price" format
                                $itemPrice = null;
                                if (preg_match('/@\s*([0-9.]+)/', $itemStr, $priceMatch)) {
                                    $itemPrice = (float)$priceMatch[1];
                                }
                                
                                $itemData = $stockLookup[strtolower(trim($partName))] ?? null;
                                if ($itemData) {
                                    $cat = $itemData['category'] ?? 'Other';
                                    $itemCost = (float)($itemData['cost'] ?? 0);
                                    
                                    if (!isset($categoryProfit[$cat])) {
                                        $categoryProfit[$cat] = ['sales' => 0, 'cost' => 0];
                                    }
                                    
                                    // Use extracted price per item if available, otherwise calculate proportionally
                                    if ($itemPrice !== null) {
                                        // Item has explicit price in the format: "item (xqty) @ price"
                                        $categoryProfit[$cat]['sales'] += ($itemPrice * $qty);
                                    } else {
                                        // No explicit price - use job cost proportionally
                                        // Calculate based on item cost vs total job cost
                                        $jobCost = (float)($r['job_cost'] ?? 0);
                                        $totalItemsCost = 0;
                                        
                                        // Calculate total cost of all items in this job
                                        foreach ($items as $otherItemStr) {
                                            if (preg_match('/^(.*?) \(x(\d+)\)/', $otherItemStr, $otherMatches)) {
                                                $otherPartName = trim(preg_replace('/\[SN:.*?\]/', '', trim($otherMatches[1])));
                                                $otherPartName = trim(preg_replace('/\[W:.*?\]/', '', $otherPartName));
                                                $otherPartName = trim(preg_replace('/\[Alt:.*?\]/', '', $otherPartName));
                                                $otherData = $stockLookup[strtolower(trim($otherPartName))] ?? null;
                                                $otherCost = (float)($otherData['cost'] ?? 0);
                                                $otherQty = (int)$otherMatches[2];
                                                $totalItemsCost += ($otherCost * $otherQty);
                                            }
                                        }
                                        
                                        if ($totalItemsCost > 0) {
                                            // Calculate price proportionally based on cost ratio
                                            $costRatio = ($itemCost * $qty) / $totalItemsCost;
                                            $pricePerItem = ((float)$r['price'] * $costRatio) / $qty;
                                        } else {
                                            // Fallback: divide equally
                                            $pricePerItem = (float)$r['price'] / array_sum(array_map(function($i) {
                                                if (preg_match('/\(x(\d+)\)/', $i, $m)) return (int)$m[1];
                                                return 1;
                                            }, $items));
                                        }
                                        $categoryProfit[$cat]['sales'] += ($pricePerItem * $qty);
                                    }
                                    
                                    // Use actual item cost
                                    $categoryProfit[$cat]['cost'] += ($itemCost * $qty);
                                }
                            }
                        }
                    }
                }
            }
            
            // Sort top items by quantity
            arsort($topItems);
            $topItems = array_slice($topItems, 0, 10, true);
            
            // Calculate category profits (keep detailed data for breakdown)
            $categoryProfits = [];
            foreach ($categoryProfit as $cat => $data) {
                $categoryProfits[$cat] = $data['sales'] - $data['cost'];
            }
            arsort($categoryProfits);
            
            // Also include detailed breakdown (sales, cost) for modal display
            $categoryProfitDetails = [];
            foreach ($categoryProfit as $cat => $data) {
                $categoryProfitDetails[$cat] = [
                    'sales' => $data['sales'],
                    'cost' => $data['cost'],
                    'profit' => $data['sales'] - $data['cost']
                ];
            }

            sendSuccess([
                'details'=>$rows, 
                'expenses'=>$expenses,
                'summary'=>[
                    'total_sales'=>number_format($totalSales,2), 
                    'total_cost'=>number_format($totalCost,2), 
                    'total_expenses'=>$totalExp,
                    'total_profit'=>number_format($totalSales - $totalCost - $totalExp, 2), 
                    'total_jobs'=>count($rows)
                ],
                'analytics'=>[
                    'top_items'=>$topItems,
                    'category_profits'=>$categoryProfits,
                    'category_profit_details'=>$categoryProfitDetails
                ]
            ]);
        }

        if ($action === 'restore_job') {
            if (!checkLivePermission($pdo, $_SESSION['user_id'], 'can_restore')) {
                sendError("ACCESS DENIED: You do not have permission to restore items.");
            }
            $stmt = $pdo->prepare("SELECT * FROM jobs WHERE id = ?"); $stmt->execute([$in['id']]); $job = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($job) {
                $type = ($job['model'] == 'Retail Sale') ? 'Retail' : 'Repair';
                $newID = generateCustomID($pdo, $type, $job['branch']);
                manageStockForJob($pdo, $job, 'DEDUCT');
                $pdo->prepare("UPDATE jobs SET job_id = ?, is_deleted = 0 WHERE id = ?")->execute([$newID, $in['id']]);
                logActivity($pdo, 'RESTORE', "Restored Job. New ID: $newID");
            }
            sendSuccess();
        }

        if ($action === 'force_delete_job') {
            if (!checkLivePermission($pdo, $_SESSION['user_id'], 'can_delete')) {
                sendError("ACCESS DENIED: Permanent delete requires delete permission.");
            }
            $pdo->prepare("DELETE FROM jobs WHERE id = ?")->execute([$in['id']]);
            logActivity($pdo, 'PERMANENT_DELETE', "Permanently deleted Job ID: " . $in['id']);
            sendSuccess();
        }

        if ($action === 'delete_job') {
            if (!checkLivePermission($pdo, $_SESSION['user_id'], 'can_delete')) sendError("ACCESS DENIED.");
            $stmt = $pdo->prepare("SELECT * FROM jobs WHERE id = ?"); $stmt->execute([$in['id']]); $job = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$job) sendError("Job not found.");
            $jobBranch = strtolower(trim((string)($job['branch'] ?? '')));
            $sessionBranch = strtolower(trim((string)($_SESSION['assigned_branch'] ?? '')));
            if (!isAdmin() && $jobBranch !== $sessionBranch) sendError("ACCESS DENIED.");
            
            $deletedJobId = $job['job_id'];
            $delID = "DEL-" . date('ymd') . "-" . $deletedJobId;
            $reason = $in['reason'] ?? 'No reason provided';
            $deleteRequestId = trim((string)($in['request_id'] ?? ''));
            if ($deleteRequestId === '') {
                $deleteRequestId = bin2hex(random_bytes(16));
            }
            $deletedBy = $_SESSION['user'] ?? 'Unknown';

            try {
                runInTransaction($pdo, function() use ($pdo, $job, $delID, $reason, $in, $deletedJobId, $deletedBy, $deleteRequestId) {
                    manageStockForJob($pdo, $job, 'RESTORE');
                    $pdo->prepare("UPDATE jobs 
                        SET job_id = ?, 
                            is_deleted = 1, 
                            deleted_at = NOW(), 
                            deleted_by = ?,
                            delete_reason = ?,
                            delete_request_id = ?,
                            remarks = CONCAT(COALESCE(remarks, ''), ' | Deleted: ', ?)
                        WHERE id = ?")
                        ->execute([$delID, $deletedBy, $reason, $deleteRequestId, $reason, $in['id']]);
                    // Fill gap: shift subsequent IV-SG/IV-SR orders down (e.g. delete 226 -> 227 becomes 226)
                    fillGapOnIVDelete($pdo, $deletedJobId);
                });
            } catch (Exception $e) {
                sendError("Delete failed: " . $e->getMessage());
            }
            logActivity($pdo, 'SOFT_DELETE', "Deleted " . $deletedJobId . ". Reason: " . $reason);
            sendSuccess();
        }

        if ($action === 'refund_retail') {
            if (!checkLivePermission($pdo, $_SESSION['user_id'], 'can_refund')) sendError("ACCESS DENIED.");
            $originalId = $in['id'];
            $stmt = $pdo->prepare("SELECT * FROM jobs WHERE id = ?"); $stmt->execute([$originalId]); $originalJob = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$originalJob) sendError("Original sale not found.");
            
            $checkStmt = $pdo->prepare("SELECT id FROM jobs WHERE repair = ? AND is_deleted = 0");
            $checkStmt->execute(['REFUND FOR: ' . $originalJob['job_id']]);
            if ($checkStmt->fetch()) { sendError("This item has already been refunded."); }
            if ($originalJob['status'] === 'Refund' || strpos($originalJob['repair'], 'REFUND FOR:') === 0) { sendError("Cannot refund a refund."); }

            manageStockForJob($pdo, $originalJob, 'RESTORE');

            $refundAmount = -1 * abs($originalJob['price']); 
            $refundCost = -1 * abs($originalJob['job_cost']); 
            $refundID = generateCustomID($pdo, 'Retail', $originalJob['branch']);

            $stmt = $pdo->prepare("INSERT INTO jobs (job_id, branch, date, customer, phone, model, repair, price, deposit, status, payment_status, completed_at, remarks, warranty, payment_method, job_cost, token) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            $stmt->execute([$refundID . '-REF', $originalJob['branch'], date('Y-m-d'), $originalJob['customer'], $originalJob['phone'], 'Retail Sale', 'REFUND FOR: ' . $originalJob['job_id'], $refundAmount, 0, 'Refund', 'Refunded', date('Y-m-d'), 'Processed by ' . $_SESSION['user'], 'N/A', 'Bank Transfer/QR', $refundCost, bin2hex(random_bytes(16))]);
            
            logActivity($pdo, 'REFUND', "Refunded Job ID: " . $originalJob['job_id'] . " (Stock Restored)");
            sendSuccess(['message' => 'Refund Processed & Stock Restored']);
        }

        if ($action === 'CREATE_JOB') {
            $requestId = trim((string)($in['request_id'] ?? ''));
            if ($requestId === '') {
                $requestId = bin2hex(random_bytes(16));
            }
            $reqStmt = $pdo->prepare("SELECT job_id, token FROM jobs WHERE request_id = ? LIMIT 1");
            $reqStmt->execute([$requestId]);
            $requestDuplicate = $reqStmt->fetch(PDO::FETCH_ASSOC);
            if ($requestDuplicate) {
                sendSuccess([
                    'newJobId' => $requestDuplicate['job_id'],
                    'token' => $requestDuplicate['token'] ?? '',
                    'duplicate_blocked' => true,
                    'message' => 'Duplicate request blocked'
                ]);
            }
            // Prevent accidental duplicate submissions (double-click / network retry) within a short window.
            $dupStmt = $pdo->prepare("SELECT job_id, token FROM jobs
                WHERE is_deleted = 0
                AND branch = ?
                AND date = ?
                AND customer = ?
                AND phone = ?
                AND model = ?
                AND COALESCE(imei, '') = ?
                AND repair = ?
                AND CAST(price AS DECIMAL(10,2)) = CAST(? AS DECIMAL(10,2))
                AND CAST(COALESCE(deposit, 0) AS DECIMAL(10,2)) = CAST(? AS DECIMAL(10,2))
                AND status = ?
                AND created_at >= DATE_SUB(NOW(), INTERVAL 20 SECOND)
                ORDER BY id DESC LIMIT 1");
            $dupStmt->execute([
                $in['branch'] ?? '',
                $in['date'] ?? date('Y-m-d'),
                $in['customer'] ?? '',
                $in['phone'] ?? '',
                $in['model'] ?? '',
                $in['imei'] ?? '',
                $in['repair'] ?? '',
                $in['price'] ?? 0,
                $in['deposit'] ?? 0,
                $in['status'] ?? 'Pending'
            ]);
            $recentDuplicate = $dupStmt->fetch(PDO::FETCH_ASSOC);
            if ($recentDuplicate) {
                sendSuccess([
                    'newJobId' => $recentDuplicate['job_id'],
                    'token' => $recentDuplicate['token'] ?? '',
                    'duplicate_blocked' => true,
                    'message' => 'Duplicate submit prevented'
                ]);
            }

            try {
                if (!$pdo->inTransaction()) {
                    $pdo->beginTransaction();
                }

                $pdo->prepare("INSERT INTO customers (name, phone) VALUES (?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name)")->execute([$in['customer'], $in['phone']]);
                $newJobID = generateCustomID($pdo, 'Repair', $in['branch']);
                $compDate = ($in['status'] === 'Completed') ? date('Y-m-d') : null;
            
            // If creating a job with Completed status, assign Invoice ID (IV prefix)
            $invoiceId = null;
            if ($in['status'] === 'Completed') {
                $branch = trim($in['branch'] ?? '');
                $isGombak = (stripos($branch, 'gombak') !== false);
                $targetPrefix = $isGombak ? 'IV-SG' : 'IV-SR';
                
                // Find the highest existing Invoice ID for this branch
                $stmtIV = $pdo->prepare("SELECT invoice_id FROM jobs WHERE invoice_id LIKE ? ORDER BY LENGTH(invoice_id) DESC, invoice_id DESC LIMIT 1");
                $stmtIV->execute([$targetPrefix . '%']);
                $lastIv = $stmtIV->fetchColumn();
                
                // Extract number part
                $nextSeq = 1;
                if ($lastIv && strlen($lastIv) >= 6) {
                    $numPart = substr($lastIv, 5);
                    $nextSeq = (int)$numPart + 1;
                }
                $invoiceId = $targetPrefix . str_pad($nextSeq, 5, '0', STR_PAD_LEFT);
            }
            
            $itemListString = $in['repair']; 
            $branchKey = strtolower($in['branch']);
            $stockColumn = (strpos($branchKey, 'gombak') !== false) ? 'stock_gombak' : 'stock_sr';
            
            // Use provided job_cost if available, otherwise calculate from items
            $totalJobCost = 0;
            if (isset($in['job_cost']) && $in['job_cost'] > 0) {
                $totalJobCost = (float)$in['job_cost'];
            } else {
                $totalJobCost = calculateItemsCost($pdo, $itemListString);
            }

            $items = explode(' || ', $itemListString);
            foreach ($items as $itemStr) {
                // Match format: "part_name (xqty) @ price [W: warranty]" or "part_name [SN: sn] (xqty) @ price [W: warranty]"
                if (preg_match('/^(.*?) \(x(\d+)\)/', trim($itemStr), $matches)) {
                    $rawName = trim($matches[1]);
                    // Remove [SN: ...] tags if present
                    $partName = trim(preg_replace('/\[SN:.*?\]/', '', $rawName));
                    // Remove [W: ...] tags if present
                    $partName = trim(preg_replace('/\[W:.*?\]/', '', $partName));
                    $partName = trim(preg_replace('/\[Alt:.*?\]/', '', $partName));
                    // Normalize whitespace
                    $partName = preg_replace('/\s+/', ' ', trim($partName));
                    $qty = intval($matches[2]);
                    
                    if ($qty > 0 && !empty($partName)) {
                        // Normalize the part name for matching (trim and normalize whitespace)
                        $normalizedPartName = preg_replace('/\s+/', ' ', trim($partName));
                        
                        // First, try to find the exact part_name in database (to get the exact format)
                        // Try multiple variations: exact, case-insensitive, and with/without common suffixes
                        $searchVariations = [
                            $normalizedPartName,
                            str_replace([' 1set', ' 2set', ' 3set', ' 4set', ' 5set', '1set', '2set', '3set', '4set', '5set', 'set'], '', $normalizedPartName),
                            trim($normalizedPartName)
                        ];
                        $searchVariations = array_unique($searchVariations);
                        
                        $foundPartData = null;
                        foreach ($searchVariations as $variation) {
                            $variation = trim($variation);
                            if (empty($variation)) continue;
                            
                            $variationNorm = normalizeStockName($variation);
                            $findStmt = $pdo->prepare("SELECT id, part_name, $stockColumn as current_stock FROM stock_list WHERE part_name_norm = ? LIMIT 1 FOR UPDATE");
                            $findStmt->execute([$variationNorm]);
                            $foundPartData = $findStmt->fetch(PDO::FETCH_ASSOC);
                            if ($foundPartData) break;
                        }
                        
                        if ($foundPartData && !empty($foundPartData['part_name'])) {
                            $foundPart = $foundPartData['part_name'];
                            $stockId = $foundPartData['id'] ?? null;
                            $currentStock = $foundPartData['current_stock'] ?? 0;
                            
                            if ($stockColumn === 'stock_gombak') {
                                $updateSql = "UPDATE stock_list SET stock_gombak = stock_gombak - ? WHERE part_name = ? AND stock_gombak >= ?";
                            } else {
                                $updateSql = "UPDATE stock_list SET stock_sr = stock_sr - ? WHERE part_name = ? AND stock_sr >= ?";
                            }
                            
                            $stmt = $pdo->prepare($updateSql);
                            $stmt->execute([$qty, $foundPart, $qty]);
                            $affected = $stmt->rowCount();
                            
                            $verifyStmt = $pdo->prepare("SELECT $stockColumn FROM stock_list WHERE part_name = ?");
                            $verifyStmt->execute([$foundPart]);
                            $newStock = $verifyStmt->fetchColumn();
                            
                            if ($affected > 0) {
                                $branchName = ($stockColumn === 'stock_gombak') ? 'Gombak' : 'Sri Rampai';
                                logStockMovement($pdo, $stockId, $foundPart, $branchName, 'OUT', $qty, $newJobID, 'Used in repair');
                            } else {
                                error_log("ERROR: Stock deduction failed - Repair item: '$foundPart' (qty: $qty) - Update query returned 0 rows. SQL: $updateSql");
                            }
                        } else {
                            // Try fuzzy/partial matching as fallback
                            $searchTerm = str_replace(['1set', '2set', '3set', '4set', '5set', 'set'], '', $normalizedPartName);
                            $searchTerm = trim(preg_replace('/\s+/', ' ', $searchTerm));
                            
                            $searchNorm = normalizeStockName($searchTerm);
                            $fuzzyStmt = $pdo->prepare("SELECT id, part_name, $stockColumn as current_stock FROM stock_list WHERE part_name_norm LIKE ? OR part_name_norm LIKE ? LIMIT 1 FOR UPDATE");
                            $fuzzyStmt->execute(['%' . $searchNorm . '%', '%' . str_replace(' ', '%', $searchNorm) . '%']);
                            $fuzzyPartData = $fuzzyStmt->fetch(PDO::FETCH_ASSOC);
                            
                            if ($fuzzyPartData && !empty($fuzzyPartData['part_name'])) {
                                $foundPart = $fuzzyPartData['part_name'];
                                $stockId = $fuzzyPartData['id'] ?? null;
                                $currentStock = $fuzzyPartData['current_stock'] ?? 0;
                                
                                if ($stockColumn === 'stock_gombak') {
                                    $updateSql = "UPDATE stock_list SET stock_gombak = stock_gombak - ? WHERE part_name = ? AND stock_gombak >= ?";
                                } else {
                                    $updateSql = "UPDATE stock_list SET stock_sr = stock_sr - ? WHERE part_name = ? AND stock_sr >= ?";
                                }
                                
                                $stmt = $pdo->prepare($updateSql);
                                $stmt->execute([$qty, $foundPart, $qty]);
                                $affected = $stmt->rowCount();
                                
                                if ($affected > 0) {
                                    $branchName = ($stockColumn === 'stock_gombak') ? 'Gombak' : 'Sri Rampai';
                                    logStockMovement($pdo, $stockId, $foundPart, $branchName, 'OUT', $qty, $newJobID, 'Used in repair');
                                }
                            } else {
                                error_log("ERROR: Stock deduction failed - Repair item: '$normalizedPartName' (qty: $qty) - No matching stock found");
                            }
                        }
                    }
                }
            }

                $receiptToken = bin2hex(random_bytes(16));
                $stmt = $pdo->prepare("INSERT INTO jobs (job_id, branch, date, customer, phone, model, imei, passcode, repair, price, deposit, status, payment_status, completed_at, remarks, pickup_date, warranty, payment_method, job_cost, token, invoice_id, request_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                $stmt->execute([$newJobID, $in['branch'], $in['date'], $in['customer'], $in['phone'], $in['model'], $in['imei'], $in['passcode'], $in['repair'], $in['price'], $in['deposit'] ?? 0, $in['status'], $in['payment_status'], $compDate, $in['remarks'] ?? '', $in['pickup_date'], $in['warranty'], $in['paymentMethod'], $totalJobCost, $receiptToken, $invoiceId, $requestId]);
                if ($pdo->inTransaction()) {
                    $pdo->commit();
                }
                sendSuccess(['newJobId' => $newJobID, 'invoiceId' => $invoiceId, 'token' => $receiptToken, 'request_id' => $requestId]);
            } catch (Exception $e) {
                if ($pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                sendError("Create job failed: " . $e->getMessage());
            }
        }

        if ($action === 'CREATE_RETAIL') {
            if (!isAdmin()) { $in['branch'] = $_SESSION['assigned_branch'] ?? 'Gombak'; }
            $requestId = trim((string)($in['request_id'] ?? ''));
            if ($requestId === '') {
                $requestId = bin2hex(random_bytes(16));
            }
            $reqStmt = $pdo->prepare("SELECT job_id, token FROM jobs WHERE request_id = ? LIMIT 1");
            $reqStmt->execute([$requestId]);
            $requestDuplicate = $reqStmt->fetch(PDO::FETCH_ASSOC);
            if ($requestDuplicate) {
                sendSuccess([
                    'newJobId' => $requestDuplicate['job_id'],
                    'token' => $requestDuplicate['token'] ?? '',
                    'duplicate_blocked' => true,
                    'message' => 'Duplicate request blocked'
                ]);
            }
            // Prevent accidental duplicate submissions (double-click / network retry) within a short window.
            $priceCheck = !empty($in['price']) ? $in['price'] : ($in['totalAmount'] ?? 0);
            $dupStmt = $pdo->prepare("SELECT job_id, token FROM jobs
                WHERE is_deleted = 0
                AND model = 'Retail Sale'
                AND branch = ?
                AND date = ?
                AND customer = ?
                AND phone = ?
                AND COALESCE(imei, '') = ?
                AND repair = ?
                AND CAST(price AS DECIMAL(10,2)) = CAST(? AS DECIMAL(10,2))
                AND COALESCE(payment_method, '') = ?
                AND created_at >= DATE_SUB(NOW(), INTERVAL 20 SECOND)
                ORDER BY id DESC LIMIT 1");
            $dupStmt->execute([
                $in['branch'] ?? 'Gombak',
                $in['date'] ?? date('Y-m-d'),
                $in['customer'] ?? 'Walk-in Customer',
                $in['phone'] ?? '',
                $in['imei'] ?? '',
                $in['repair'] ?? '',
                $priceCheck,
                $in['paymentMethod'] ?? 'Cash'
            ]);
            $recentDuplicate = $dupStmt->fetch(PDO::FETCH_ASSOC);
            if ($recentDuplicate) {
                sendSuccess([
                    'newJobId' => $recentDuplicate['job_id'],
                    'token' => $recentDuplicate['token'] ?? '',
                    'duplicate_blocked' => true,
                    'message' => 'Duplicate submit prevented'
                ]);
            }

            try {
                if (!$pdo->inTransaction()) {
                    $pdo->beginTransaction();
                }

                $newJobID = generateCustomID($pdo, 'Retail', $in['branch']);
            $items = $in['cartItems'] ?? [];
            // Warranty is now per-item, but keep this for backward compatibility/fallback
            $warranty = $in['warranty'] ?? 'No Warranty';
            // IMPORTANT: Stock deduction uses the SALE BRANCH (where the sale is made), 
            // NOT the QR code branch. This ensures correct deduction even if item was transferred.
            // QR codes are now branch-agnostic (contain only part name), but old QR codes 
            // with branch prefix are still supported and will work correctly.
            $stockColumn = ($in['branch'] == 'Gombak' || $in['branch'] == 'gombak') ? 'stock_gombak' : 'stock_sr';
            $itemListString = "";
            $totalJobCost = 0;
            
            // Build cost map for batch optimization
            $allPartNames = [];
            foreach ($items as $item) {
                $n = $item['name'] ?? 'Item'; 
                // Clean branch prefixes (Gombak| or Sri Rampai|)
                $n = preg_replace('/^(Gombak\||Sri Rampai\|)/i', '', trim($n));
                $n = trim(preg_replace('/\[Alt:.*?\]/', '', $n));
                // Normalize the name: trim and remove extra whitespace
                $n = preg_replace('/\s+/', ' ', trim($n));
                if (!empty($n)) {
                    $allPartNames[strtolower($n)] = $n;
                }
            }
            
            // Batch fetch all costs
            $costMap = [];
            if (!empty($allPartNames)) {
                $placeholders = implode(',', array_fill(0, count($allPartNames), '?'));
                $stmtCosts = $pdo->prepare("SELECT part_name_norm as part_name_lower, cost FROM stock_list WHERE part_name_norm IN ($placeholders)");
                $stmtCosts->execute(array_map('normalizeStockName', array_values($allPartNames)));
                $costResults = $stmtCosts->fetchAll(PDO::FETCH_ASSOC);
                foreach ($costResults as $row) {
                    $costMap[$row['part_name_lower']] = (float)$row['cost'];
                }
            }
            
            // Process cart items
            foreach ($items as $item) {
                $n = $item['name'] ?? 'Item'; 
                // Clean branch prefixes (Gombak| or Sri Rampai|)
                $n = preg_replace('/^(Gombak\||Sri Rampai\|)/i', '', trim($n));
                // Normalize the name: trim and remove extra whitespace
                $n = preg_replace('/\s+/', ' ', trim($n));
                $q = (int)($item['qty'] ?? 1); 
                $p = $item['price'] ?? '0.00';
                $sn = $item['imei'] ?? ''; 
                $snTag = !empty($sn) ? " [SN: $sn]" : "";
                // Use per-item warranty if provided, otherwise fallback to global or default
                $itemWarranty = $item['warranty'] ?? $warranty ?? 'No Warranty';
                $itemListString .= "$n$snTag (x$q) @ $p [W: $itemWarranty] || ";
                
                // Strip [Alt:...] tag for stock/cost lookup (keep in item list for display)
                $cleanName = trim(preg_replace('/\[Alt:.*?\]/', '', $n));
                $cleanName = preg_replace('/\s+/', ' ', trim($cleanName));
                
                // Calculate cost for this item
                $nLower = strtolower($cleanName);
                if (isset($costMap[$nLower])) {
                    $itemCost = $costMap[$nLower] * $q;
                    $totalJobCost += $itemCost;
                }
                
                if (!empty($cleanName) && $q > 0) {
                    // Normalize the part name for matching
                    $normalizedName = $cleanName;
                    
                        // First, try to find the exact part_name in database (to get the exact format)
                        // Try multiple variations: exact, case-insensitive, and with/without common suffixes
                        $searchVariations = [
                            $normalizedName,
                            str_replace([' 1set', ' 2set', ' 3set', ' 4set', ' 5set', '1set', '2set', '3set', '4set', '5set', 'set'], '', $normalizedName),
                            trim($normalizedName)
                        ];
                        $searchVariations = array_unique($searchVariations);
                        
                        $foundPartData = null;
                        foreach ($searchVariations as $variation) {
                            $variation = trim($variation);
                            if (empty($variation)) continue;
                            
                            $variationNorm = normalizeStockName($variation);
                            $findStmt = $pdo->prepare("SELECT id, part_name, $stockColumn as current_stock FROM stock_list WHERE part_name_norm = ? LIMIT 1 FOR UPDATE");
                            $findStmt->execute([$variationNorm]);
                            $foundPartData = $findStmt->fetch(PDO::FETCH_ASSOC);
                            if ($foundPartData) break;
                        }
                        
                    if ($foundPartData && !empty($foundPartData['part_name'])) {
                        $foundPart = $foundPartData['part_name'];
                        $stockId = $foundPartData['id'] ?? null;
                        $currentStock = $foundPartData['current_stock'] ?? 0;
                        
                        if ($stockColumn === 'stock_gombak') {
                            $updateSql = "UPDATE stock_list SET stock_gombak = stock_gombak - ? WHERE part_name = ? AND stock_gombak >= ?";
                        } else {
                            $updateSql = "UPDATE stock_list SET stock_sr = stock_sr - ? WHERE part_name = ? AND stock_sr >= ?";
                        }
                        
                        $stmt = $pdo->prepare($updateSql);
                        $stmt->execute([$q, $foundPart, $q]);
                        $affected = $stmt->rowCount();
                        
                        // Verify the update worked by checking new stock value
                        $verifyStmt = $pdo->prepare("SELECT $stockColumn FROM stock_list WHERE part_name = ?");
                        $verifyStmt->execute([$foundPart]);
                        $newStock = $verifyStmt->fetchColumn();
                        
                        if ($affected > 0) {
                            $branchName = ($stockColumn === 'stock_gombak') ? 'Gombak' : 'Sri Rampai';
                            logStockMovement($pdo, $stockId, $foundPart, $branchName, 'OUT', $q, $newJobID, 'Retail sale');
                        } else {
                            error_log("ERROR: Stock deduction failed - Item: '$foundPart' (qty: $q) - Update query returned 0 rows affected. SQL: $updateSql");
                        }
                    } else {
                        // Try fuzzy/partial matching as fallback
                        $searchTerm = str_replace(['1set', '2set', '3set', '4set', '5set', 'set'], '', $normalizedName);
                        $searchTerm = trim(preg_replace('/\s+/', ' ', $searchTerm));
                        
                        $searchNorm = normalizeStockName($searchTerm);
                        $fuzzyStmt = $pdo->prepare("SELECT id, part_name, $stockColumn as current_stock FROM stock_list WHERE part_name_norm LIKE ? OR part_name_norm LIKE ? LIMIT 1 FOR UPDATE");
                        $fuzzyStmt->execute(['%' . $searchNorm . '%', '%' . str_replace(' ', '%', $searchNorm) . '%']);
                        $fuzzyPartData = $fuzzyStmt->fetch(PDO::FETCH_ASSOC);
                        
                        if ($fuzzyPartData && !empty($fuzzyPartData['part_name'])) {
                            $foundPart = $fuzzyPartData['part_name'];
                            $stockId = $fuzzyPartData['id'] ?? null;
                            $currentStock = $fuzzyPartData['current_stock'] ?? 0;
                            
                            if ($stockColumn === 'stock_gombak') {
                                $updateSql = "UPDATE stock_list SET stock_gombak = stock_gombak - ? WHERE part_name = ? AND stock_gombak >= ?";
                            } else {
                                $updateSql = "UPDATE stock_list SET stock_sr = stock_sr - ? WHERE part_name = ? AND stock_sr >= ?";
                            }
                            
                            $stmt = $pdo->prepare($updateSql);
                            $stmt->execute([$q, $foundPart, $q]);
                            $affected = $stmt->rowCount();
                            
                            if ($affected > 0) {
                                $branchName = ($stockColumn === 'stock_gombak') ? 'Gombak' : 'Sri Rampai';
                                logStockMovement($pdo, $stockId, $foundPart, $branchName, 'OUT', $q, $newJobID, 'Retail sale');
                            }
                        } else {
                            error_log("ERROR: Stock deduction failed - Item: '$normalizedName' (qty: $q) - No matching stock found");
                        }
                    }
                }
            }
            
            // Process repair description and parts
            $repairDesc = $in['repair'] ?? '';
            
            // For retail sales, model should always be 'Retail Sale'
            // Repair description is already set, no need to modify
            
            if (!empty($repairDesc)) {
                if (!empty($itemListString)) {
                    $itemListString = rtrim($itemListString, " || ") . " || " . $repairDesc;
                } else {
                    $itemListString = $repairDesc;
                }
                
                // Deduct parts from stock
                $parts = [];
                $partsMatches = [];
                preg_match_all('/([^|]+) \(x(\d+)\)/', $repairDesc, $partsMatches, PREG_SET_ORDER);
                foreach ($partsMatches as $match) {
                    $partName = trim($match[1]);
                    $partQty = (int)$match[2];
                    if (!empty($partName) && $partQty > 0) {
                        // Normalize the part name
                        $normalizedPartName = preg_replace('/\s+/', ' ', trim($partName));
                        
                        // First, try to find the exact part_name in database
                        $partNorm = normalizeStockName($normalizedPartName);
                        $findStmt = $pdo->prepare("SELECT id, part_name FROM stock_list WHERE part_name_norm = ? LIMIT 1 FOR UPDATE");
                        $findStmt->execute([$partNorm]);
                        $foundPartRow = $findStmt->fetch(PDO::FETCH_ASSOC);
                        $foundPart = $foundPartRow['part_name'] ?? null;
                        
                        if ($foundPart) {
                            $stockId = $foundPartRow['id'] ?? null;
                            // Use the exact part_name from database for update
                            $stmt = $pdo->prepare("UPDATE stock_list SET $stockColumn = $stockColumn - ? WHERE part_name = ?");
                            $stmt->execute([$partQty, $foundPart]);
                            $affected = $stmt->rowCount();
                            
                            if ($affected > 0) {
                                $branchName = ($stockColumn === 'stock_gombak') ? 'Gombak' : 'Sri Rampai';
                                logStockMovement($pdo, $stockId, $foundPart, $branchName, 'OUT', $partQty, $newJobID, 'Retail sale (repair desc)');
                                error_log("Stock deducted successfully: '$foundPart' (qty: $partQty) from $stockColumn");
                            } else {
                                error_log("Stock deduction failed for repair desc item: '$foundPart' (qty: $partQty) - Update query returned 0 rows");
                            }
                        } else {
                            error_log("Stock deduction failed for repair desc item: '$normalizedPartName' (qty: $partQty) - No matching stock found in database");
                        }
                    }
                }
            }
            
            $itemListString = rtrim($itemListString, " || "); 
            if(empty($itemListString)) $itemListString = "Retail Sale";
            
            // Cost is already calculated above during item processing, but verify with calculateItemsCost as backup
            // Only use calculateItemsCost if totalJobCost is still 0 (items might be in repair description instead)
            if ($totalJobCost == 0 && !empty($itemListString) && $itemListString !== "Retail Sale") {
                $totalJobCost = calculateItemsCost($pdo, $itemListString);
            }

            // Use provided values or defaults
            // IMPORTANT: Always set model to 'Retail Sale' for retail entries to ensure proper filtering
            $date = !empty($in['date']) ? $in['date'] : date('Y-m-d');
            $pickupDate = !empty($in['pickup_date']) ? $in['pickup_date'] : date('Y-m-d');
            $model = 'Retail Sale'; // Always 'Retail Sale' for retail entries (required for filtering)
            $imei = $in['imei'] ?? '';
            $passcode = ''; // Removed field for retail sales
            // For retail sales, always set status to Completed and deposit to 0
            $status = 'Completed';
            $paymentStatus = 'Paid'; // Always Paid for Daily Repair/Sales
            $completedAt = date('Y-m-d'); // Always completed for retail sales
            $remarks = !empty($in['remarks']) ? $in['remarks'] : 'Retail Order';
            $price = !empty($in['price']) ? $in['price'] : ($in['totalAmount'] ?? 0);
            $deposit = 0; // No deposit for retail sales

                $receiptToken = bin2hex(random_bytes(16));
                $stmt = $pdo->prepare("INSERT INTO jobs (job_id, branch, date, customer, phone, model, imei, passcode, repair, price, deposit, status, payment_status, completed_at, remarks, pickup_date, warranty, payment_method, job_cost, token, request_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                $stmt->execute([$newJobID, $in['branch'] ?? 'Gombak', $date, $in['customer'] ?? 'Walk-in Customer', $in['phone'] ?? '', $model, $imei, $passcode, $itemListString, $price, $deposit, $status, $paymentStatus, $completedAt, $remarks, $pickupDate, $warranty, $in['paymentMethod'] ?? 'Cash', $totalJobCost, $receiptToken, $requestId]);
                if ($pdo->inTransaction()) {
                    $pdo->commit();
                }
                sendSuccess(['newJobId' => $newJobID, 'token' => $receiptToken, 'request_id' => $requestId, 'message' => 'Daily Repairs/Sales Recorded']);
            } catch (Exception $e) {
                if ($pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                sendError("Create retail failed: " . $e->getMessage());
            }
        }

        if ($action === 'update_full_job') {
            if (!checkLivePermission($pdo, $_SESSION['user_id'], 'can_edit')) sendError("ACCESS DENIED.");
            
            $stmt = $pdo->prepare("SELECT * FROM jobs WHERE id = ?"); 
            $stmt->execute([$in['id']]); 
            $oldJob = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$oldJob) sendError("Job not found.");

            // Check if this is a retail sale (daily repair)
            $isRetail = ($oldJob['model'] === 'Retail Sale' || $oldJob['remarks'] === 'Retail Order');
            
            // Check if user is master/admin
            $stmtUser = $pdo->prepare("SELECT role, username FROM users WHERE id = ?");
            $stmtUser->execute([$_SESSION['user_id']]);
            $user = $stmtUser->fetch(PDO::FETCH_ASSOC);
            $isAdmin = isAdmin();
            
            // For retail sales, non-master users can only edit customer name and phone
            if ($isRetail && !$isAdmin) {
                // Only allow customer and phone to be updated, keep all other fields from oldJob
                $customer = $in['customer'] ?? $oldJob['customer'];
                $phone = $in['phone'] ?? $oldJob['phone'];
                
                if (empty($customer)) {
                    sendError("Customer name is required.");
                }
                
                // Use old job values for all other fields
                $model = $oldJob['model'];
                $imei = $oldJob['imei'];
                $passcode = $oldJob['passcode'];
                $repair = $oldJob['repair'];
                $remarks = $oldJob['remarks'];
                $price = floatval($oldJob['price']);
                $deposit = floatval($oldJob['deposit']);
                $status = $oldJob['status'];
                $paymentStatus = $oldJob['payment_status'];
                $paymentMethod = $oldJob['payment_method'];
                $warranty = $oldJob['warranty'];
                $finalCost = floatval($oldJob['job_cost']);
                $newInvoiceId = $oldJob['invoice_id'];
                $compDate = $oldJob['completed_at'];
                
                $sql = "UPDATE jobs SET customer=?, phone=? WHERE id=?";
                $stmt = $pdo->prepare($sql);
                $stmt->execute([$customer, $phone, $in['id']]);
                
                logActivity($pdo, 'EDIT', "Edited Job ID: " . $oldJob['job_id'] . " (Restricted: Customer & Phone only)");
                sendSuccess(['invoice_id' => $newInvoiceId, 'restricted' => true]);
                return;
            }

            // --- INVOICE ID LOGIC ---
            // When a repair job (JS prefix) is marked as Completed, assign an Invoice ID (IV prefix)
            $newInvoiceId = $oldJob['invoice_id']; 
            if ($in['status'] === 'Completed' && empty($newInvoiceId)) {
                // Determine prefix based on branch (case-insensitive)
                $branch = trim($oldJob['branch'] ?? '');
                $isGombak = (stripos($branch, 'gombak') !== false);
                $targetPrefix = $isGombak ? 'IV-SG' : 'IV-SR';
                
                // Find the highest existing Invoice ID for this branch
                $stmtIV = $pdo->prepare("SELECT invoice_id FROM jobs WHERE invoice_id LIKE ? ORDER BY LENGTH(invoice_id) DESC, invoice_id DESC LIMIT 1");
                $stmtIV->execute([$targetPrefix . '%']);
                $lastIv = $stmtIV->fetchColumn();
                
                // Extract number part: "IV-SG00045" -> "00045" -> 45
                // Prefix is 5 chars (IV-SG or IV-SR), so start at position 5
                $nextSeq = 1;
                if ($lastIv && strlen($lastIv) >= 6) {
                    $numPart = substr($lastIv, 5); // Get everything after "IV-SG" or "IV-SR"
                    $nextSeq = (int)$numPart + 1;
                }
                $newInvoiceId = $targetPrefix . str_pad($nextSeq, 5, '0', STR_PAD_LEFT);
            }

            try {
                // Manage stock changes
                manageStockForJob($pdo, $oldJob, 'RESTORE');
                $newJobData = $oldJob; 
                $newJobData['repair'] = $in['repair']; 
                manageStockForJob($pdo, $newJobData, 'DEDUCT');
                
                // --- FIX: PRIORITY TO MANUAL COST ---
                // Check if job_cost was provided (even if it's 0, it means user explicitly set it)
                // Trim whitespace and check if it's a valid number
                $jobCostInput = isset($in['job_cost']) ? trim((string)$in['job_cost']) : '';
                if ($jobCostInput !== '' && $jobCostInput !== null && is_numeric($jobCostInput)) {
                    // User provided a cost (including 0), use it
                    $finalCost = (float)$jobCostInput;
                    error_log("Using manual cost: " . $finalCost);
                } else {
                    // No manual cost provided, auto-calculate from items
                    $finalCost = calculateItemsCost($pdo, $in['repair']);
                    error_log("Auto-calculated cost: " . $finalCost);
                }
                // -----------------------------------

                $compDate = (!empty($in['completed_at'])) ? $in['completed_at'] : (($in['status'] === 'Completed' || $in['status'] === 'Return') ? date('Y-m-d') : null);
                $jobDate = $in['date'] ?? $oldJob['date']; // Use provided date or keep existing
                $sql = "UPDATE jobs SET customer=?, phone=?, date=?, model=?, imei=?, passcode=?, repair=?, remarks=?, price=?, deposit=?, status=?, payment_status=?, completed_at=?, payment_method=?, warranty=?, job_cost=?, invoice_id=? WHERE id=?";
                
                // Ensure all required fields have values
                $customer = $in['customer'] ?? '';
                $phone = $in['phone'] ?? '';
                $model = $in['model'] ?? '';
                $imei = $in['imei'] ?? '';
                $passcode = $in['passcode'] ?? '';
                $repair = $in['repair'] ?? '';
                $remarks = $in['remarks'] ?? '';
                $price = floatval($in['price'] ?? 0);
                $deposit = floatval($in['deposit'] ?? 0);
                $status = $in['status'] ?? 'Pending';
                $paymentStatus = $in['payment_status'] ?? 'Unpaid';
                $paymentMethod = $in['paymentMethod'] ?? 'Cash';
                $warranty = $in['warranty'] ?? 'No Warranty';
                
                if (empty($customer)) {
                    sendError("Customer name is required.");
                }
                if ($price < 0) {
                    sendError("Price cannot be negative.");
                }
                
                if (empty($jobDate)) {
                    sendError("Date is required.");
                }
                
                $stmt = $pdo->prepare($sql);
                $stmt->execute([
                    $customer, $phone, $jobDate, $model, $imei, $passcode, 
                    $repair, $remarks, $price, $deposit, 
                    $status, $paymentStatus, $compDate, $paymentMethod, 
                    $warranty, 
                    $finalCost, // Saving the correct cost
                    $newInvoiceId, 
                    $in['id']
                ]);
                
                logActivity($pdo, 'EDIT', "Edited Job ID: " . $oldJob['job_id']);
                sendSuccess(['invoice_id' => $newInvoiceId]);
            } catch (PDOException $e) {
                error_log("Update job error: " . $e->getMessage());
                error_log("SQL: " . $sql);
                error_log("Data: " . print_r($in, true));
                sendError("Database error: " . $e->getMessage());
            } catch (Exception $e) {
                error_log("Update job error: " . $e->getMessage());
                sendError("Error updating job: " . $e->getMessage());
            }
        }

        // ... (Remaining stock/user functions)
        if ($action === 'insert_stock_item') { 
            // Check if user can add stock for cross-branch
            $canCrossBranch = isAdmin(); // Master/Endie can always add for any branch
            if (!$canCrossBranch) {
                // Check permission
                $stmt = $pdo->prepare("SELECT can_add_cross_branch_stock, assigned_branch FROM users WHERE id = ?");
                $stmt->execute([$_SESSION['user_id']]);
                $userPerms = $stmt->fetch(PDO::FETCH_ASSOC);
                $canCrossBranch = ($userPerms['can_add_cross_branch_stock'] ?? 0) == 1;
                $userBranch = strtolower($userPerms['assigned_branch'] ?? '');
                
                // If restricted, only allow stock for assigned branch
                if (!$canCrossBranch && !empty($userBranch)) {
                    $isGombak = strpos($userBranch, 'gombak') !== false;
                    // Force stock to be 0 for the other branch
                    if ($isGombak) {
                        $in['stock_sr'] = 0;
                    } else {
                        $in['stock_gombak'] = 0;
                    }
                }
            }
            
            $sellPrice = $in['selling_price'] ?? 0.00;
            $category = $in['category'] ?? 'Other';
            
            // Auto-detect category if not provided
            if ($category === 'Other' && !empty($in['part_name'])) {
                $category = extractCategoryFromName($in['part_name']);
            }
            
            // Normalize the part name for comparison (trim, remove extra spaces, lowercase)
            $normalizedPartName = preg_replace('/\s+/', ' ', trim($in['part_name']));
            $normalizedPartNameLower = normalizeStockName($normalizedPartName);
            
            // Get input cost and supplier for comparison (case-insensitive)
            $inputCost = isset($in['cost']) ? (float)$in['cost'] : 0;
            $inputSupplier = isset($in['supplier']) ? trim($in['supplier']) : 'Unknown';
            if (empty($inputSupplier) || $inputSupplier === '') {
                $inputSupplier = 'Unknown';
            }
            $inputSupplierLower = strtolower($inputSupplier);
            
            // Normalize type and category for case-insensitive comparison
            $inputType = strtolower(trim($in['type'] ?? ''));
            if (empty($inputType)) {
                $inputType = 'part'; // Default to 'part' if type is empty
            }
            $categoryLower = strtolower(trim($category));
            if (empty($categoryLower)) {
                $categoryLower = 'other'; // Default to 'other' if category is empty
            }
            
            // Check if duplicate exists - Match by: name, type, category, supplier, AND cost (all case-insensitive)
            // This ensures same item with same supplier and same cost gets merged
            // But same item with different cost or supplier stays separate
            
            // First, try to find existing items by fetching all with matching type and category
            // Then do PHP-level comparison for more accurate matching
            $allStmt = $pdo->prepare("SELECT id, part_name, part_name_norm, stock_gombak, stock_sr, cost, selling_price, supplier, type, category FROM stock_list WHERE LOWER(TRIM(type)) = ? AND LOWER(TRIM(category)) = ?");
            $allStmt->execute([$inputType, $categoryLower]);
            $allItems = $allStmt->fetchAll(PDO::FETCH_ASSOC);
            
            $existing = null;
            foreach ($allItems as $item) {
                // Normalize part name for comparison (same method as input)
                $existingNormalized = !empty($item['part_name_norm'])
                    ? normalizeStockName($item['part_name_norm'])
                    : normalizeStockName($item['part_name'] ?? '');
                
                // Normalize supplier (handle empty/null, default to 'Unknown')
                $existingSupplier = trim($item['supplier'] ?? 'Unknown');
                if (empty($existingSupplier)) {
                    $existingSupplier = 'Unknown';
                }
                $existingSupplierLower = strtolower($existingSupplier);
                
                // Get cost (handle null/empty)
                $existingCost = (float)($item['cost'] ?? 0);
                
                // Match if: normalized names match AND supplier matches (case-insensitive) AND cost matches (within epsilon)
                if ($normalizedPartNameLower === $existingNormalized && 
                    $existingSupplierLower === $inputSupplierLower && 
                    abs($existingCost - $inputCost) < 0.01) { // Use small epsilon for float comparison
                    $existing = $item;
                    break;
                }
            }
            
            if ($existing) {
                // Item already exists - combine stock quantities instead of creating duplicate
                // Ensure proper integer conversion (handle empty strings, null, etc.)
                $existingStockGombak = (int)($existing['stock_gombak'] ?? 0);
                $existingStockSr = (int)($existing['stock_sr'] ?? 0);
                // Handle input values - convert empty strings to 0 before casting
                $inputGombak = $in['stock_gombak'] ?? 0;
                $inputSr = $in['stock_sr'] ?? 0;
                $newStockGombak = (int)(($inputGombak === '' || $inputGombak === null) ? 0 : $inputGombak);
                $newStockSr = (int)(($inputSr === '' || $inputSr === null) ? 0 : $inputSr);
                
                $combinedStockGombak = $existingStockGombak + $newStockGombak;
                $combinedStockSr = $existingStockSr + $newStockSr;
                
                // Since we only merge when cost and supplier match, keep the existing cost
                // (They should be the same anyway, but use existing as source of truth)
                $finalCost = (float)($existing['cost'] ?? 0);
                
                // Update stock quantities (cost and supplier remain the same since they matched)
                $updateSql = "UPDATE stock_list SET stock_gombak = ?, stock_sr = ?";
                $updateParams = [$combinedStockGombak, $combinedStockSr];
                
                // Update selling_price if provided (for retail items)
                if (!empty($sellPrice) && $sellPrice > 0) {
                    $updateSql .= ", selling_price = ?";
                    $updateParams[] = $sellPrice;
                }
                
                // Update supplier if provided
                if (!empty($in['supplier']) && $in['supplier'] !== 'Unknown') {
                    $updateSql .= ", supplier = ?";
                    $updateParams[] = $in['supplier'];
                }
                
                $updateSql .= " WHERE id = ?";
                $updateParams[] = $existing['id'];
                
                $stmt = $pdo->prepare($updateSql);
                $stmt->execute($updateParams);
                $affected = $stmt->rowCount();
                
                // Log stock IN for each branch (delta = what we're adding)
                $partName = $in['part_name'] ?? 'Unknown';
                $gombakDelta = $newStockGombak - $existingStockGombak;
                $srDelta = $newStockSr - $existingStockSr;
                if ($gombakDelta > 0) logStockMovement($pdo, $existing['id'], $partName, 'Gombak', 'IN', $gombakDelta, null, 'Added/merged stock');
                if ($srDelta > 0) logStockMovement($pdo, $existing['id'], $partName, 'Sri Rampai', 'IN', $srDelta, null, 'Added/merged stock');
                
                // Verify the update worked - get actual values from database
                $verifyStmt = $pdo->prepare("SELECT stock_gombak, stock_sr, cost FROM stock_list WHERE id = ?");
                $verifyStmt->execute([$existing['id']]);
                $verified = $verifyStmt->fetch(PDO::FETCH_ASSOC);
                
                if ($affected > 0 && $verified) {
                    $actualGombak = (int)($verified['stock_gombak'] ?? 0);
                    $actualSr = (int)($verified['stock_sr'] ?? 0);
                    $actualCost = (float)($verified['cost'] ?? 0);
                    
                    // Log with both calculated and actual values for debugging
                    error_log("MERGE_STOCK: $partName - Calculated: Gombak=$combinedStockGombak, SR=$combinedStockSr, Cost=$finalCost | Actual DB: Gombak=$actualGombak, SR=$actualSr, Cost=$actualCost");
                    
                    logActivity($pdo, 'UPDATE_STOCK', "Merged duplicate: $partName (Supplier: " . ($existing['supplier'] ?? 'Unknown') . ", Cost: RM$actualCost) - Combined stock: Gombak=$actualGombak, SR=$actualSr");
                    sendSuccess([
                        'message' => 'Stock combined with existing item', 
                        'existing_id' => $existing['id'],
                        'combined_stock' => [
                            'gombak' => $actualGombak,  // Use actual DB value
                            'sr' => $actualSr           // Use actual DB value
                        ],
                        'cost' => $actualCost,  // Include final cost in response
                        'supplier' => $existing['supplier'] ?? 'Unknown',
                        'added_stock' => [
                            'gombak' => $newStockGombak,
                            'sr' => $newStockSr
                        ],
                        'existing_stock' => [
                            'gombak' => $existingStockGombak,
                            'sr' => $existingStockSr
                        ]
                    ]);
                } else {
                    error_log("MERGE_STOCK_FAILED: $partName - affected=$affected, verified=" . ($verified ? 'yes' : 'no'));
                    sendError("Failed to update stock quantities. Please try again.");
                }
            } else {
                // New item - insert normally (without shortcode)
                $brand = $in['brand'] ?? null;
                $sql = "INSERT INTO stock_list (type, model_group, device_model, part_name, part_name_norm, category, brand, cost, selling_price, supplier, stock_gombak, stock_sr) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"; 
                $pdo->prepare($sql)->execute([
                    $in['type'], 
                    $in['model_group'] ?? 'General', 
                    $in['device_model'] ?? 'Universal', 
                    $in['part_name'], 
                    normalizeStockName($in['part_name'] ?? ''),
                    $category,
                    $brand ?: null, 
                    $in['cost'], 
                    $sellPrice, 
                    $in['supplier'] ?? 'Unknown', 
                    (int)($in['stock_gombak'] ?? 0), 
                    (int)($in['stock_sr'] ?? 0)
                ]); 
                $newId = $pdo->lastInsertId();
                $partName = $in['part_name'] ?? 'Unknown';
                $gombakQty = (int)($in['stock_gombak'] ?? 0);
                $srQty = (int)($in['stock_sr'] ?? 0);
                if ($gombakQty > 0) logStockMovement($pdo, $newId, $partName, 'Gombak', 'IN', $gombakQty, null, 'New stock entry');
                if ($srQty > 0) logStockMovement($pdo, $newId, $partName, 'Sri Rampai', 'IN', $srQty, null, 'New stock entry');
                logActivity($pdo, 'ADD_STOCK', "Added " . $in['part_name']);
                sendSuccess(); 
            }
        }
        if ($action === 'update_full_stock') {
            if (!isAdmin()) { sendError("ACCESS DENIED."); }
            
            // Check if user can edit stock for cross-branch
            $canCrossBranch = isAdmin(); // Master/Endie can always edit for any branch
            if (!$canCrossBranch) {
                // Check permission
                $stmt = $pdo->prepare("SELECT can_add_cross_branch_stock, assigned_branch FROM users WHERE id = ?");
                $stmt->execute([$_SESSION['user_id']]);
                $userPerms = $stmt->fetch(PDO::FETCH_ASSOC);
                $canCrossBranch = ($userPerms['can_add_cross_branch_stock'] ?? 0) == 1;
                $userBranch = strtolower($userPerms['assigned_branch'] ?? '');
                
                // If restricted, preserve stock for other branch (don't allow changes)
                if (!$canCrossBranch && !empty($userBranch)) {
                    // Get current stock values
                    $currentStmt = $pdo->prepare("SELECT stock_gombak, stock_sr FROM stock_list WHERE id = ?");
                    $currentStmt->execute([$in['id']]);
                    $currentStock = $currentStmt->fetch(PDO::FETCH_ASSOC);
                    
                    $isGombak = strpos($userBranch, 'gombak') !== false;
                    // Preserve stock for the other branch
                    if ($isGombak) {
                        $in['stock_sr'] = $currentStock['stock_sr'] ?? 0;
                    } else {
                        $in['stock_gombak'] = $currentStock['stock_gombak'] ?? 0;
                    }
                }
            }
            
            $category = $in['category'] ?? 'Other';
            $brand = $in['brand'] ?? null;
            
            $sql = "UPDATE stock_list SET type=?, model_group=?, device_model=?, part_name=?, part_name_norm=?, category=?, brand=?, cost=?, selling_price=?, supplier=?, stock_gombak=?, stock_sr=? WHERE id=?";
            $pdo->prepare($sql)->execute([
                $in['type'], 
                $in['model_group'], 
                $in['device_model'], 
                $in['part_name'], 
                normalizeStockName($in['part_name'] ?? ''),
                $category,
                $brand ?: null, 
                $in['cost'], 
                $in['selling_price'], 
                $in['supplier'], 
                $in['stock_gombak'], 
                $in['stock_sr'], 
                $in['id']
            ]);
            logActivity($pdo, 'EDIT_STOCK', "Updated Stock ID: " . $in['id']);
            sendSuccess();
        }
        if ($action === 'delete_stock_item') {
            if (strtolower($_SESSION['user']) !== 'endie') { sendError("ACCESS DENIED."); }
            $pdo->prepare("DELETE FROM stock_list WHERE id = ?")->execute([$in['id']]);
            logActivity($pdo, 'DELETE_STOCK', "Deleted stock ID: " . $in['id']);
            sendSuccess();
        }
        if ($action === 'delete_stock_by_location') {
            if (!checkLivePermission($pdo, $_SESSION['user_id'], 'can_delete')) sendError("ACCESS DENIED.");
            
            $id = (int)($in['id'] ?? 0);
            $location = $in['location'] ?? ''; // 'SG' or 'SR'
            // qty can be null (delete all) or a positive integer (delete specific quantity)
            $qty = (isset($in['qty']) && $in['qty'] !== null && $in['qty'] !== '') ? (int)$in['qty'] : null;
            
            if (empty($id) || !in_array($location, ['SG', 'SR'])) {
                sendError("Invalid parameters.");
            }
            
            // Get current stock values
            $stmt = $pdo->prepare("SELECT part_name, stock_gombak, stock_sr FROM stock_list WHERE id = ?");
            $stmt->execute([$id]);
            $item = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if (!$item) {
                sendError("Stock item not found.");
            }
            
            $stockColumn = ($location === 'SG') ? 'stock_gombak' : 'stock_sr';
            $locationName = ($location === 'SG') ? 'Gombak (SG)' : 'Sri Rampai (SR)';
            $currentStock = $item[$stockColumn] ?? 0;
            
            // Determine quantity to delete
            if ($qty === null) {
                // Delete all
                $qtyToDelete = $currentStock;
                $newStock = 0;
            } else {
                // Delete specific quantity
                if ($qty <= 0) {
                    sendError("Quantity must be greater than 0.");
                }
                if ($qty > $currentStock) {
                    sendError("Cannot delete more than available stock ($currentStock).");
                }
                $qtyToDelete = $qty;
                $newStock = $currentStock - $qty;
            }
            
            // Update stock
            $updateSql = "UPDATE stock_list SET $stockColumn = ? WHERE id = ?";
            $pdo->prepare($updateSql)->execute([$newStock, $id]);
            
            $branchName = ($location === 'SG') ? 'Gombak' : 'Sri Rampai';
            logStockMovement($pdo, $id, $item['part_name'], $branchName, 'DELETE', $qtyToDelete, null, 'Stock deleted from location');
            logActivity($pdo, 'DELETE_STOCK_LOCATION', "Deleted $qtyToDelete qty from $locationName for item: " . $item['part_name']);
            sendSuccess(['message' => "Deleted $qtyToDelete quantity from $locationName", 'new_stock' => $newStock]);
        }
        if ($action === 'transfer_stock') { 
            if (!checkLivePermission($pdo, $_SESSION['user_id'], 'can_transfer')) sendError("ACCESS DENIED.");
            $id = $in['id']; $qty = (int)$in['qty']; 
            $from = ($in['from'] == 'Gombak') ? 'stock_gombak' : 'stock_sr'; 
            $to = ($in['to'] == 'Gombak') ? 'stock_gombak' : 'stock_sr'; 
            $staff = $_SESSION['user'] ?? 'Unknown';
            $partName = $in['name'] ?? 'Unknown';
            $pdo->beginTransaction(); 
            $pdo->prepare("UPDATE stock_list SET $from = $from - ? WHERE id = ?")->execute([$qty, $id]); 
            $pdo->prepare("UPDATE stock_list SET $to = $to + ? WHERE id = ?")->execute([$qty, $id]); 
            logStockMovement($pdo, $id, $partName, $in['from'], 'TRANSFER_OUT', $qty, null, 'Transferred to ' . $in['to']);
            logStockMovement($pdo, $id, $partName, $in['to'], 'TRANSFER_IN', $qty, null, 'Transferred from ' . $in['from']);
            $pdo->prepare("INSERT INTO stock_transfers (item_name, qty, from_branch, to_branch, user) VALUES (?,?,?,?,?)")->execute([$partName, $qty, $in['from'], $in['to'], $staff]); 
            logActivity($pdo, 'TRANSFER_STOCK', "Transferred $qty qty of " . $partName . " from " . $in['from'] . " to " . $in['to']);
            $pdo->commit(); 
            sendSuccess(); 
        }
        if ($action === 'update_stock_field') {
            if (!isAdmin()) { sendError("ACCESS DENIED."); }
            $allowed = ['stock_gombak', 'stock_sr', 'cost', 'part_name', 'supplier'];
            if (!in_array($in['field'], $allowed)) sendError("Invalid field.");
            if ($in['field'] === 'part_name') {
                $sql = "UPDATE stock_list SET part_name = ?, part_name_norm = ? WHERE id = ?";
                $pdo->prepare($sql)->execute([$in['value'], normalizeStockName($in['value'] ?? ''), $in['id']]);
            } else {
                $sql = "UPDATE stock_list SET {$in['field']} = ? WHERE id = ?";
                $pdo->prepare($sql)->execute([$in['value'], $in['id']]);
            }
            sendSuccess();
        }
        
        if ($action === 'create_user') {
            if(!isAdmin()) sendError("Access Denied");
            $stmt = $pdo->prepare("SELECT id FROM users WHERE username = ?"); $stmt->execute([$in['username']]);
            if($stmt->fetch()) sendError("Username already exists.");
            $branch = $in['assigned_branch'] ?? 'Gombak'; $hash = password_hash($in['password'], PASSWORD_DEFAULT);
            $pdo->prepare("INSERT INTO users (username, password, role, can_edit, can_delete, can_transfer, can_refund, can_restore, assigned_branch, restrict_branch, can_add_cross_branch_stock) VALUES (?, ?, 'staff', 0, 0, 0, 0, 0, ?, 1, 0)")->execute([$in['username'], $hash, $branch]);
            sendSuccess();
        }
        if ($action === 'delete_user') {
            if(!isAdmin()) sendError("Access Denied");
            $pdo->prepare("DELETE FROM users WHERE id=?")->execute([$in['user_id']]);
            sendSuccess();
        }
        if ($action === 'update_user_permissions') { 
            if(!isAdmin()) sendError("Access Denied"); 
            $pdo->prepare("UPDATE users SET can_edit=?, can_delete=?, can_transfer=?, can_refund=?, can_restore=?, restrict_branch=?, can_add_cross_branch_stock=?, can_view_stock_logs=?, can_merge_stock=?, can_bulk_ops=?, can_bulk_print=?, can_bulk_import=?, can_convert_uppercase=? WHERE id=?")->execute([
                $in['can_edit'], 
                $in['can_delete'], 
                $in['can_transfer'], 
                $in['can_refund'], 
                $in['can_restore'], 
                $in['restrict_branch'], 
                $in['can_add_cross_branch_stock'] ?? 0,
                $in['can_view_stock_logs'] ?? 0,
                $in['can_merge_stock'] ?? 0,
                $in['can_bulk_ops'] ?? 0,
                $in['can_bulk_print'] ?? 0,
                $in['can_bulk_import'] ?? 0,
                $in['can_convert_uppercase'] ?? 0,
                $in['user_id']
            ]); 
            // Clear permission cache for the updated user
            $cacheKey = '_user_perms_' . $in['user_id'];
            if (isset($_SESSION[$cacheKey])) {
                unset($_SESSION[$cacheKey]);
            }
            sendSuccess(); 
        }
        if ($action === 'update_user_branch') { if(!isAdmin()) sendError("Access Denied"); $pdo->prepare("UPDATE users SET assigned_branch=? WHERE id=?")->execute([$in['branch'], $in['user_id']]); sendSuccess(); }
        if ($action === 'admin_reset_password') { if(!isAdmin()) sendError("Access Denied"); $hash = password_hash($in['new_pass'], PASSWORD_DEFAULT); $pdo->prepare("UPDATE users SET password=? WHERE id=?")->execute([$hash, $in['user_id']]); sendSuccess(); }
        if ($action === 'change_own_password') { $stmt = $pdo->prepare("SELECT password FROM users WHERE id=?"); $stmt->execute([$_SESSION['user_id']]); $curr = $stmt->fetchColumn(); if(!password_verify($in['old_pass'], $curr)) sendError("Current password incorrect."); $hash = password_hash($in['new_pass'], PASSWORD_DEFAULT); $pdo->prepare("UPDATE users SET password=? WHERE id=?")->execute([$hash, $_SESSION['user_id']]); sendSuccess(); }
        if ($action === 'search_customer') { 
            $stmt = $pdo->prepare("SELECT name, phone FROM customers WHERE phone LIKE ? LIMIT 5"); 
            $stmt->execute(["%{$in['term']}%"]); 
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Get customer history if phone number is provided
            $history = [];
            if (!empty($in['term']) && preg_match('/^[0-9-]+$/', $in['term'])) {
                $phone = preg_replace('/[^0-9]/', '', $in['term']);
                if (strlen($phone) >= 8) {
                    $histStmt = $pdo->prepare("SELECT job_id, date, customer, model, repair, price, status, payment_method FROM jobs WHERE phone LIKE ? ORDER BY date DESC LIMIT 10");
                    $histStmt->execute(["%{$phone}%"]);
                    $history = $histStmt->fetchAll(PDO::FETCH_ASSOC);
                }
            }
            
            sendSuccess(['results' => $results, 'history' => $history]); 
        }

        if ($action === 'print_thermal_receipt') {
            $jobId = (int)($in['job_id'] ?? 0);
            if (!$jobId) sendError("Job ID required.");
            
            $stmt = $pdo->prepare("SELECT * FROM jobs WHERE id = ?");
            $stmt->execute([$jobId]);
            $job = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if (!$job) sendError("Job not found.");
            
            // Generate ESC/POS commands for thermal printer
            $escPos = "";
            $escPos .= "\x1B\x40"; // Initialize printer
            $escPos .= "\x1B\x61\x01"; // Center align
            $escPos .= "\x1B\x21\x30"; // Double height and width
            $escPos .= "RECEIPT\n";
            $escPos .= "\x1B\x21\x00"; // Normal text
            $escPos .= "\x1B\x61\x00"; // Left align
            $escPos .= "--------------------------------\n";
            $escPos .= "Job ID: " . $job['job_id'] . "\n";
            $escPos .= "Date: " . date('Y-m-d H:i', strtotime($job['date'])) . "\n";
            $escPos .= "Customer: " . $job['customer'] . "\n";
            $escPos .= "Phone: " . $job['phone'] . "\n";
            $escPos .= "--------------------------------\n";
            $escPos .= "Items:\n";
            $escPos .= $job['repair'] . "\n";
            $escPos .= "--------------------------------\n";
            $escPos .= "\x1B\x21\x10"; // Double width
            $escPos .= "Total: RM " . number_format($job['price'], 2) . "\n";
            $escPos .= "\x1B\x21\x00"; // Normal text
            if (!empty($job['deposit'])) {
                $escPos .= "Deposit: RM " . number_format($job['deposit'], 2) . "\n";
                $escPos .= "Balance: RM " . number_format($job['price'] - $job['deposit'], 2) . "\n";
            }
            $escPos .= "Payment: " . $job['payment_method'] . "\n";
            $escPos .= "Status: " . $job['status'] . "\n";
            $escPos .= "--------------------------------\n";
            $escPos .= "\x1B\x61\x01"; // Center align
            $escPos .= "Thank You!\n";
            $escPos .= "\x1B\x61\x00"; // Left align
            $escPos .= "\n\n\n";
            $escPos .= "\x1D\x56\x00"; // Cut paper
            
            sendSuccess(['esc_pos_commands' => base64_encode($escPos)]);
        }

        if ($action === 'bulk_update_stock') {
            if (!checkLivePermission($pdo, $_SESSION['user_id'], 'can_edit')) sendError("ACCESS DENIED.");
            
            $ids = $in['ids'] ?? [];
            $field = $in['field'] ?? '';
            $value = $in['value'] ?? '';
            
            if (empty($ids) || !is_array($ids) || empty($field)) {
                sendError("Invalid parameters.");
            }
            
            $allowedFields = ['stock_gombak', 'stock_sr', 'cost', 'selling_price', 'category', 'supplier'];
            if (!in_array($field, $allowedFields)) {
                sendError("Invalid field.");
            }
            
            $count = 0;
            $placeholders = implode(',', array_fill(0, count($ids), '?'));
            
            if ($field === 'stock_gombak' || $field === 'stock_sr' || $field === 'cost' || $field === 'selling_price') {
                $value = (float)$value;
                $sql = "UPDATE stock_list SET $field = ? WHERE id IN ($placeholders)";
                $params = array_merge([$value], $ids);
            } else {
                $sql = "UPDATE stock_list SET $field = ? WHERE id IN ($placeholders)";
                $params = array_merge([$value], $ids);
            }
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $count = $stmt->rowCount();
            
            logActivity($pdo, 'BULK_UPDATE_STOCK', "Bulk updated $count items: $field to $value");
            sendSuccess(['message' => "Bulk update successful for $count items."]);
        }

        if ($action === 'get_stock_item') {
            $id = (int)($in['id'] ?? 0);
            if (!$id) sendError("Item ID required.");
            $stmt = $pdo->prepare("SELECT * FROM stock_list WHERE id = ?");
            $stmt->execute([$id]);
            $item = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$item) sendError("Item not found.");
            sendSuccess(['item' => $item]);
        }

        if ($action === 'generate_barcode') {
            $id = (int)($in['id'] ?? 0);
            if (!$id) sendError("Item ID required.");
            
            $stmt = $pdo->prepare("SELECT part_name, selling_price FROM stock_list WHERE id = ?");
            $stmt->execute([$id]);
            $item = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if (!$item) {
                sendError("Item not found.");
            }
            
            // Generate a barcode number (using item ID padded to 12 digits for EAN-13)
            $barcodeNumber = str_pad($id, 12, '0', STR_PAD_LEFT);
            
            sendSuccess([
                'item' => $item,
                'barcode_number' => $barcodeNumber,
                'format' => 'EAN-13'
            ]);
        }

        if ($action === 'convert_stock_to_uppercase') {
            // Convert all text fields in stock_list to uppercase
            // SAFETY: This function ONLY converts text to uppercase - it does NOT:
            // - Delete any records
            // - Modify numeric fields (cost, stock_gombak, stock_sr, selling_price)
            // - Change NULL values
            // - Remove or truncate any data
            // - Change data structure or relationships
            
            if (!isAdmin()) {
                sendError("ACCESS DENIED: Only administrators can perform this operation.");
            }
            
            try {
                // Start transaction for safety - can rollback if anything goes wrong
                $pdo->beginTransaction();
                
                // Check which columns exist
                $checkCols = $pdo->query("SHOW COLUMNS FROM stock_list");
                $columns = $checkCols->fetchAll(PDO::FETCH_COLUMN);
                
                $hasCategory = in_array('category', $columns);
                
                // Get ALL fields for each item to ensure we don't lose any data
                // Only SELECT text fields that we will convert - we don't touch numeric fields
                $selectFields = "id, type, model_group, device_model, part_name, supplier";
                if ($hasCategory) {
                    $selectFields .= ", category";
                }
                
                $stmt = $pdo->query("SELECT $selectFields FROM stock_list ORDER BY id");
                $allItems = $stmt->fetchAll(PDO::FETCH_ASSOC);
                
                $updated = 0;
                $skipped = 0;
                $errors = [];
                
                foreach ($allItems as $item) {
                    $updates = [];
                    $params = [];
                    $hasChanges = false;
                    
                    // SAFETY: Only convert text fields to uppercase
                    // Preserve NULL values - do NOT convert NULL to empty string or 'NULL'
                    // Only update if the value actually changes (direct string comparison)
                    
                    // Type field
                    if (isset($item['type']) && $item['type'] !== null && $item['type'] !== '') {
                        $original = trim($item['type']);
                        $uppercase = strtoupper($original);
                        // Only update if it's different (direct string comparison, not case-insensitive)
                        if ($original !== $uppercase) {
                            $updates[] = "type = ?";
                            $params[] = $uppercase;
                            $hasChanges = true;
                        }
                    }
                    
                    // Model Group field
                    if (isset($item['model_group']) && $item['model_group'] !== null && $item['model_group'] !== '') {
                        $original = trim($item['model_group']);
                        $uppercase = strtoupper($original);
                        if ($original !== $uppercase) {
                            $updates[] = "model_group = ?";
                            $params[] = $uppercase;
                            $hasChanges = true;
                        }
                    }
                    
                    // Device Model field - preserve "Universal" as-is
                    if (isset($item['device_model']) && $item['device_model'] !== null && $item['device_model'] !== '') {
                        $original = trim($item['device_model']);
                        // Don't convert "Universal" to uppercase (case-insensitive check)
                        if (strcasecmp($original, 'Universal') === 0) {
                            // Skip - keep as-is
                        } else {
                            $uppercase = strtoupper($original);
                            if ($original !== $uppercase) {
                                $updates[] = "device_model = ?";
                                $params[] = $uppercase;
                                $hasChanges = true;
                            }
                        }
                    }
                    
                    // Part Name field - always convert (required field)
                    if (isset($item['part_name']) && $item['part_name'] !== null && $item['part_name'] !== '') {
                        $original = trim($item['part_name']);
                        $uppercase = strtoupper($original);
                        if ($original !== $uppercase) {
                            $updates[] = "part_name = ?";
                            $params[] = $uppercase;
                            $updates[] = "part_name_norm = ?";
                            $params[] = normalizeStockName($uppercase);
                            $hasChanges = true;
                        }
                    }
                    
                    // Supplier field
                    if (isset($item['supplier']) && $item['supplier'] !== null && $item['supplier'] !== '') {
                        $original = trim($item['supplier']);
                        $uppercase = strtoupper($original);
                        if ($original !== $uppercase) {
                            $updates[] = "supplier = ?";
                            $params[] = $uppercase;
                            $hasChanges = true;
                        }
                    }
                    
                    // Category field (if exists)
                    if ($hasCategory && isset($item['category']) && $item['category'] !== null && $item['category'] !== '') {
                        $original = trim($item['category']);
                        $uppercase = strtoupper($original);
                        if ($original !== $uppercase) {
                            $updates[] = "category = ?";
                            $params[] = $uppercase;
                            $hasChanges = true;
                        }
                    }
                    
                    // Only update if there are actual changes
                    if ($hasChanges && !empty($updates)) {
                        $params[] = $item['id'];
                        $updateSql = "UPDATE stock_list SET " . implode(", ", $updates) . " WHERE id = ?";
                        
                        try {
                            $updateStmt = $pdo->prepare($updateSql);
                            $updateStmt->execute($params);
                            
                            // Verify the update was successful
                            if ($updateStmt->rowCount() > 0) {
                                $updated++;
                            } else {
                                $skipped++;
                            }
                        } catch (PDOException $e) {
                            $errors[] = "Error updating item ID {$item['id']}: " . $e->getMessage();
                            // Continue processing other items even if one fails
                        }
                    } else {
                        $skipped++; // Item already in uppercase or no changes needed
                    }
                }
                
                // Commit transaction if no critical errors
                if (count($errors) === 0 || count($errors) < count($allItems) * 0.1) {
                    // Only commit if less than 10% errors (allows for minor issues)
                    $pdo->commit();
                    
                    logActivity($pdo, 'CONVERT_STOCK_UPPERCASE', "Converted $updated stock items to uppercase (skipped $skipped, errors: " . count($errors) . ")");
                    
                    sendSuccess([
                        'message' => "Successfully converted $updated stock items to uppercase",
                        'updated' => $updated,
                        'skipped' => $skipped,
                        'total' => count($allItems),
                        'errors' => $errors
                    ]);
                } else {
                    // Too many errors - rollback for safety
                    $pdo->rollBack();
                    sendError("Too many errors occurred. Transaction rolled back for safety. No data was changed. Errors: " . implode("; ", array_slice($errors, 0, 5)));
                }
                
            } catch (PDOException $e) {
                // Rollback on any exception
                if ($pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                sendError("Database error: " . $e->getMessage() . " - Transaction rolled back. No data was changed.");
            }
        }

        if ($action === 'merge_duplicate_stocks') {
            if (!isAdmin()) sendError("ACCESS DENIED: Only administrators can merge duplicates.");
            
            $dupeGroups = $pdo->query("
                SELECT LOWER(TRIM(REGEXP_REPLACE(part_name, '\\\\s+', ' '))) as norm_name,
                       LOWER(TRIM(COALESCE(NULLIF(type,''), 'part'))) as norm_type,
                       LOWER(TRIM(COALESCE(NULLIF(category,''), 'Other'))) as norm_cat,
                       LOWER(TRIM(COALESCE(NULLIF(supplier,''), 'Unknown'))) as norm_sup,
                       ROUND(COALESCE(cost, 0), 2) as norm_cost,
                       GROUP_CONCAT(id ORDER BY CHAR_LENGTH(part_name) DESC, id ASC) as ids
                FROM stock_list
                GROUP BY norm_name, norm_type, norm_cat, norm_sup, norm_cost
                HAVING COUNT(*) > 1
            ")->fetchAll(PDO::FETCH_ASSOC);

            $merged = [];
            $totalMerged = 0;
            $pdo->beginTransaction();

            try {
                $fetchStmt = $pdo->prepare("SELECT id, part_name, stock_gombak, stock_sr, cost, selling_price, supplier FROM stock_list WHERE id = ?");
                $updateStmt = $pdo->prepare("UPDATE stock_list SET part_name = ?, part_name_norm = ?, stock_gombak = ?, stock_sr = ?, cost = COALESCE(NULLIF(?, 0), cost), selling_price = COALESCE(NULLIF(?, 0), selling_price), supplier = COALESCE(NULLIF(?, 'Unknown'), supplier) WHERE id = ?");

                foreach ($dupeGroups as $group) {
                    $ids = array_map('intval', explode(',', $group['ids']));
                    if (count($ids) < 2) continue;

                    $baseId = $ids[0];
                    $dupeIds = array_slice($ids, 1);

                    $items = [];
                    foreach ($ids as $id) {
                        $fetchStmt->execute([$id]);
                        $items[$id] = $fetchStmt->fetch(PDO::FETCH_ASSOC);
                    }

                    $base = $items[$baseId];
                    $combinedGombak = (int)($base['stock_gombak'] ?? 0);
                    $combinedSr = (int)($base['stock_sr'] ?? 0);
                    $bestName = $base['part_name'];
                    $bestCost = (float)($base['cost'] ?? 0);
                    $bestSellPrice = (float)($base['selling_price'] ?? 0);
                    $bestSupplier = $base['supplier'] ?? 'Unknown';
                    $mergedNames = [$base['part_name']];

                    foreach ($dupeIds as $did) {
                        $dup = $items[$did];
                        $combinedGombak += (int)($dup['stock_gombak'] ?? 0);
                        $combinedSr += (int)($dup['stock_sr'] ?? 0);
                        $mergedNames[] = $dup['part_name'];
                        if (strlen($dup['part_name']) > strlen($bestName)) $bestName = $dup['part_name'];
                        if ($bestCost == 0 && (float)($dup['cost'] ?? 0) > 0) $bestCost = (float)$dup['cost'];
                        if ($bestSellPrice == 0 && (float)($dup['selling_price'] ?? 0) > 0) $bestSellPrice = (float)$dup['selling_price'];
                        if (($bestSupplier === 'Unknown' || empty($bestSupplier)) && !empty($dup['supplier']) && $dup['supplier'] !== 'Unknown') $bestSupplier = $dup['supplier'];
                    }

                    $updateStmt->execute([$bestName, normalizeStockName($bestName), $combinedGombak, $combinedSr, $bestCost, $bestSellPrice, $bestSupplier, $baseId]);

                    $delPlaceholders = implode(',', array_fill(0, count($dupeIds), '?'));
                    $pdo->prepare("DELETE FROM stock_list WHERE id IN ($delPlaceholders)")->execute($dupeIds);

                    $merged[] = [
                        'base_id' => $baseId,
                        'base_name' => $bestName,
                        'merged_count' => count($dupeIds),
                        'merged_names' => $mergedNames,
                        'total_stock_gombak' => $combinedGombak,
                        'total_stock_sr' => $combinedSr
                    ];
                    $totalMerged += count($dupeIds);

                    logActivity($pdo, 'MERGE_DUPLICATES', "Merged " . count($dupeIds) . " duplicates into: " . $bestName . " (Gombak: $combinedGombak, SR: $combinedSr)");
                }

                $pdo->commit();
            } catch (Exception $e) {
                $pdo->rollBack();
                sendError("Merge failed: " . $e->getMessage());
            }
            
            sendSuccess([
                'merged' => $merged,
                'total_merged' => $totalMerged,
                'message' => "Merged $totalMerged duplicate items into " . count($merged) . " base items."
            ]);
        }

        // === v18.0 ARCHIVE CONSOLE: WRITE ACTIONS (master-only) ===
        elseif ($action === 'archive_move') {
            archive_master_gate();
            $scope = (string)($in['scope'] ?? '');
            $meta = archive_scope_meta($scope);
            if (!$meta) sendError("Unknown archive scope.");
            $days = max(30, min(7300, (int)($in['days'] ?? 365)));
            $dryRun = !empty($in['dry_run']);
            [$extras, $params] = archive_filter_clause($scope, $in);

            $check = $pdo->prepare("SHOW TABLES LIKE ?");
            $check->execute([$meta['live']]);
            if ($check->rowCount() === 0) sendError("Live table '{$meta['live']}' does not exist.");

            $cols = archive_ensure_schema($pdo, $meta['live'], $meta['archive']);
            if (empty($cols)) sendError("Could not mirror columns into archive table.");
            $colList = '`' . implode('`,`', $cols) . '`';

            $whereSql = $meta['where'];
            if (!empty($extras)) $whereSql .= " AND " . implode(' AND ', $extras);
            $whereSql .= " AND `{$meta['ts_col']}` < DATE_SUB(NOW(), INTERVAL ? DAY)";
            $allParams = array_merge($params, [$days]);

            $cntStmt = $pdo->prepare("SELECT COUNT(*) FROM `{$meta['live']}` WHERE $whereSql");
            $cntStmt->execute($allParams);
            $eligible = (int) $cntStmt->fetchColumn();

            if ($dryRun || $eligible === 0) {
                sendSuccess([
                    'scope' => $scope, 'label' => $meta['label'],
                    'eligible' => $eligible, 'moved' => 0, 'dry_run' => true,
                ]);
            }

            // Confirmation token requirement for batches > 500
            if ($eligible > 500) {
                $expectedToken = "ARCHIVE $eligible";
                $providedToken = trim((string)($in['confirm_token'] ?? ''));
                if (!hash_equals($expectedToken, $providedToken)) {
                    sendError("Large batch ($eligible rows) requires confirmation. Type exactly: $expectedToken");
                }
            }

            $moved = 0;
            try {
                $pdo->beginTransaction();
                $insertSql = "INSERT INTO `{$meta['archive']}` ($colList) SELECT $colList FROM `{$meta['live']}` WHERE $whereSql";
                $insStmt = $pdo->prepare($insertSql);
                $insStmt->execute($allParams);
                $moved = $insStmt->rowCount();

                $delStmt = $pdo->prepare("DELETE FROM `{$meta['live']}` WHERE $whereSql");
                $delStmt->execute($allParams);
                $deleted = $delStmt->rowCount();

                if ($deleted !== $moved) {
                    throw new RuntimeException("Row count mismatch: inserted $moved, deleted $deleted - aborting");
                }
                $pdo->commit();
            } catch (Throwable $e) {
                if ($pdo->inTransaction()) $pdo->rollBack();
                sendError("Archive failed: " . $e->getMessage());
            }

            logActivity($pdo, 'ARCHIVE', "Archived $moved row(s) from '{$meta['live']}' (scope=$scope, days>$days)");
            sendSuccess([
                'scope' => $scope, 'label' => $meta['label'],
                'eligible' => $eligible, 'moved' => $moved, 'dry_run' => false,
            ]);
        }

        elseif ($action === 'archive_restore_one') {
            archive_master_gate();
            $scope = (string)($in['scope'] ?? '');
            $meta = archive_scope_meta($scope);
            if (!$meta) sendError("Unknown archive scope.");
            $archiveId = (int)($in['archive_id'] ?? 0);
            if ($archiveId <= 0) sendError("archive_id required.");

            $check = $pdo->prepare("SHOW TABLES LIKE ?");
            $check->execute([$meta['archive']]);
            if ($check->rowCount() === 0) sendError("Archive table not initialised.");

            $cols = archive_ensure_schema($pdo, $meta['live'], $meta['archive']);
            $colList = '`' . implode('`,`', $cols) . '`';

            try {
                $pdo->beginTransaction();
                $insSql = "INSERT INTO `{$meta['live']}` ($colList) SELECT $colList FROM `{$meta['archive']}` WHERE id = ?";
                $insStmt = $pdo->prepare($insSql);
                $insStmt->execute([$archiveId]);
                $movedBack = $insStmt->rowCount();
                if ($movedBack === 0) {
                    throw new RuntimeException("Row id=$archiveId not found in archive.");
                }
                $delStmt = $pdo->prepare("DELETE FROM `{$meta['archive']}` WHERE id = ?");
                $delStmt->execute([$archiveId]);
                $pdo->commit();
            } catch (Throwable $e) {
                if ($pdo->inTransaction()) $pdo->rollBack();
                sendError("Restore failed: " . $e->getMessage());
            }

            logActivity($pdo, 'ARCHIVE_RESTORE', "Restored 1 row to '{$meta['live']}' (scope=$scope, archive_id=$archiveId)");
            sendSuccess(['scope' => $scope, 'restored' => 1]);
        }

        elseif ($action === 'archive_restore_range') {
            archive_master_gate();
            $scope = (string)($in['scope'] ?? '');
            $meta = archive_scope_meta($scope);
            if (!$meta) sendError("Unknown archive scope.");
            $dateFrom = trim((string)($in['date_from'] ?? ''));
            $dateTo = trim((string)($in['date_to'] ?? ''));
            if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateFrom) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateTo)) {
                sendError("date_from and date_to required as YYYY-MM-DD.");
            }

            $check = $pdo->prepare("SHOW TABLES LIKE ?");
            $check->execute([$meta['archive']]);
            if ($check->rowCount() === 0) sendError("Archive table not initialised.");

            $cols = archive_ensure_schema($pdo, $meta['live'], $meta['archive']);
            $colList = '`' . implode('`,`', $cols) . '`';

            $cntStmt = $pdo->prepare("SELECT COUNT(*) FROM `{$meta['archive']}` WHERE `{$meta['ts_col']}` BETWEEN ? AND ?");
            $cntStmt->execute([$dateFrom, $dateTo]);
            $eligible = (int) $cntStmt->fetchColumn();
            if ($eligible === 0) {
                sendSuccess(['scope' => $scope, 'restored' => 0, 'note' => 'No archive rows in range']);
            }
            if ($eligible > 500) {
                $expectedToken = "RESTORE $eligible";
                $providedToken = trim((string)($in['confirm_token'] ?? ''));
                if (!hash_equals($expectedToken, $providedToken)) {
                    sendError("Large restore ($eligible rows) requires confirmation. Type exactly: $expectedToken");
                }
            }

            $restored = 0;
            try {
                $pdo->beginTransaction();
                $insSql = "INSERT INTO `{$meta['live']}` ($colList) SELECT $colList FROM `{$meta['archive']}` WHERE `{$meta['ts_col']}` BETWEEN ? AND ?";
                $insStmt = $pdo->prepare($insSql);
                $insStmt->execute([$dateFrom, $dateTo]);
                $restored = $insStmt->rowCount();
                $delStmt = $pdo->prepare("DELETE FROM `{$meta['archive']}` WHERE `{$meta['ts_col']}` BETWEEN ? AND ?");
                $delStmt->execute([$dateFrom, $dateTo]);
                $pdo->commit();
            } catch (Throwable $e) {
                if ($pdo->inTransaction()) $pdo->rollBack();
                sendError("Restore range failed: " . $e->getMessage());
            }

            logActivity($pdo, 'ARCHIVE_RESTORE', "Restored $restored row(s) to '{$meta['live']}' (scope=$scope, $dateFrom..$dateTo)");
            sendSuccess(['scope' => $scope, 'restored' => $restored, 'eligible' => $eligible]);
        }

        sendError("Action '$action' is not recognized");
    }
} catch (Exception $e) {
    if($pdo->inTransaction()) $pdo->rollBack();
    sendError('Server Error: ' . $e->getMessage());
}
?>