<?php
function ensureRequestLockTable($db) {
    static $done = false;
    if ($done) { return; }
    $db->exec("CREATE TABLE IF NOT EXISTS csr_request_locks (
        id INT AUTO_INCREMENT PRIMARY KEY,
        request_id INT NOT NULL UNIQUE,
        csr_id INT NOT NULL,
        csr_name VARCHAR(255) NOT NULL,
        locked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )");

    // Safe for older copies of the table that were created before updated_at existed.
    try {
        $db->exec("ALTER TABLE csr_request_locks ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
    } catch (Exception $e) {}
    $done = true;
}

function getCurrentCSRName($db, $csr_id) {
    $stmt = $db->prepare("SELECT CONCAT(first_name, ' ', last_name) as csr_name FROM csr_users WHERE id = :csr_id LIMIT 1");
    $stmt->execute([':csr_id' => $csr_id]);
    $csr = $stmt->fetch(PDO::FETCH_ASSOC);
    return ($csr && !empty(trim($csr['csr_name']))) ? trim($csr['csr_name']) : 'CSR';
}

function getRequestLock($db, $request_id) {
    ensureRequestLockTable($db);

    $stmt = $db->prepare("SELECT request_id, csr_id, csr_name, locked_at FROM csr_request_locks WHERE request_id = :request_id LIMIT 1");
    $stmt->execute([':request_id' => $request_id]);
    $lock = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($lock) {
        return $lock;
    }

    // Fallback: if the request already has an active technician assignment from a CSR,
    // treat that CSR as the owner even if the lock table was missing/empty.
    $stmt = $db->prepare("SELECT sa.assigned_by as csr_id, CONCAT(cu.first_name, ' ', cu.last_name) as csr_name, sa.assigned_at as locked_at
                          FROM service_assignments sa
                          LEFT JOIN csr_users cu ON sa.assigned_by = cu.id
                          WHERE sa.request_id = :request_id
                            AND sa.assigned_by IS NOT NULL
                            AND sa.status IN ('assigned', 'en_route', 'on_site')
                          ORDER BY sa.assigned_at ASC
                          LIMIT 1");
    $stmt->execute([':request_id' => $request_id]);
    $assignmentOwner = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($assignmentOwner && !empty($assignmentOwner['csr_id'])) {
        $csrName = !empty(trim($assignmentOwner['csr_name'] ?? '')) ? trim($assignmentOwner['csr_name']) : 'CSR';
        $insert = $db->prepare("INSERT INTO csr_request_locks (request_id, csr_id, csr_name)
                                VALUES (:request_id, :csr_id, :csr_name)
                                ON DUPLICATE KEY UPDATE csr_id = csr_id");
        $insert->execute([
            ':request_id' => $request_id,
            ':csr_id' => $assignmentOwner['csr_id'],
            ':csr_name' => $csrName
        ]);
        return [
            'request_id' => $request_id,
            'csr_id' => $assignmentOwner['csr_id'],
            'csr_name' => $csrName,
            'locked_at' => $assignmentOwner['locked_at'] ?? null
        ];
    }

    return null;
}

function acquireRequestLock($db, $request_id, $csr_id, $csr_name = null) {
    ensureRequestLockTable($db);
    $csr_name = $csr_name ?: getCurrentCSRName($db, $csr_id);

    $lock = getRequestLock($db, $request_id);

    if ($lock && (int)$lock['csr_id'] !== (int)$csr_id) {
        return [
            'success' => false,
            'locked' => true,
            'locked_by' => $lock['csr_name'],
            'locked_by_id' => (int)$lock['csr_id'],
            'message' => 'This request is currently being handled by ' . $lock['csr_name'] . '. You can view it, but you cannot update it.'
        ];
    }

    if (!$lock) {
        $stmt = $db->prepare("INSERT INTO csr_request_locks (request_id, csr_id, csr_name)
                              VALUES (:request_id, :csr_id, :csr_name)");
        $stmt->execute([
            ':request_id' => $request_id,
            ':csr_id' => $csr_id,
            ':csr_name' => $csr_name
        ]);
    } else {
        $stmt = $db->prepare("UPDATE csr_request_locks SET csr_name = :csr_name WHERE request_id = :request_id AND csr_id = :csr_id");
        $stmt->execute([
            ':request_id' => $request_id,
            ':csr_id' => $csr_id,
            ':csr_name' => $csr_name
        ]);
    }

    return ['success' => true, 'locked' => false, 'locked_by' => $csr_name, 'locked_by_id' => (int)$csr_id];
}

function blockIfRequestLockedByOtherCSR($db, $request_id, $csr_id) {
    return acquireRequestLock($db, $request_id, $csr_id, getCurrentCSRName($db, $csr_id));
}
?>
