<?php
// includes/app_helpers.php
// Shared helper functions for security, audit logging, notifications, metrics, and uploads.

function jsonResponse($success, $message = '', $data = []) {
    header('Content-Type: application/json');
    echo json_encode(array_merge(['success' => (bool)$success, 'message' => $message], $data));
    exit;
}

function h($value) {
    return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
}



function columnExists(PDO $db, string $table, string $column): bool {
    try {
        $stmt = $db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table AND COLUMN_NAME = :column");
        $stmt->execute([':table' => $table, ':column' => $column]);
        return (int)$stmt->fetchColumn() > 0;
    } catch (Exception $e) {
        return false;
    }
}

function addColumnIfMissing(PDO $db, string $table, string $column, string $definition): void {
    if (!columnExists($db, $table, $column)) {
        $db->exec("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
    }
}

function getActorInfo(PDO $db, $userId, string $userRole): array {
    $userId = (int)$userId;
    $fallbackName = ucfirst($userRole);
    $fallbackEmail = null;

    if ($userId <= 0) {
        return ['name' => $fallbackName, 'email' => $fallbackEmail, 'role_label' => ucfirst($userRole)];
    }

    try {
        if ($userRole === 'admin') {
            $stmt = $db->prepare("SELECT CONCAT(first_name, ' ', last_name) AS full_name, email FROM admin_users WHERE id = :id LIMIT 1");
        } elseif ($userRole === 'csr' || $userRole === 'team_leader' || $userRole === 'csr_manager') {
            $stmt = $db->prepare("SELECT CONCAT(first_name, ' ', last_name) AS full_name, email, COALESCE(role, 'csr') AS role FROM csr_users WHERE id = :id LIMIT 1");
        } else {
            return ['name' => $fallbackName, 'email' => $fallbackEmail, 'role_label' => ucfirst($userRole)];
        }

        $stmt->execute([':id' => $userId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$row) {
            return ['name' => $fallbackName . ' #' . $userId, 'email' => $fallbackEmail, 'role_label' => ucfirst($userRole)];
        }

        $roleLabel = $userRole === 'admin' ? 'Admin' : (($row['role'] ?? $userRole) === 'csr_manager' ? 'CSR Manager' : ((($row['role'] ?? $userRole) === 'team_leader') ? 'Team Leader' : 'CSR'));
        return [
            'name' => trim($row['full_name'] ?? '') ?: ($fallbackName . ' #' . $userId),
            'email' => $row['email'] ?? null,
            'role_label' => $roleLabel,
        ];
    } catch (Exception $e) {
        return ['name' => $fallbackName . ' #' . $userId, 'email' => $fallbackEmail, 'role_label' => ucfirst($userRole)];
    }
}

function ensureAnnouncementTables(PDO $db): void {
    // Fresh/simple announcement system.
    // Announcements are stored only in csr_announcements and read status is stored only in csr_announcement_reads.
    // They should never be counted in the normal csr_notifications bell.
    addColumnIfMissing($db, 'csr_users', 'role', "ENUM('csr','team_leader','csr_manager') NOT NULL DEFAULT 'csr' AFTER max_active_chats");

    $db->exec("CREATE TABLE IF NOT EXISTS csr_announcements (
        id INT AUTO_INCREMENT PRIMARY KEY,
        team_id INT NULL,
        title VARCHAR(150) NOT NULL DEFAULT 'Announcement',
        message TEXT NULL,
        priority ENUM('info','success','warning','danger') NOT NULL DEFAULT 'info',
        created_by INT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        starts_at DATETIME NULL,
        expires_at DATETIME NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_announcements_team (team_id),
        INDEX idx_announcements_active (is_active),
        INDEX idx_announcements_created (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    addColumnIfMissing($db, 'csr_announcements', 'team_id', "INT NULL AFTER id");
    addColumnIfMissing($db, 'csr_announcements', 'title', "VARCHAR(150) NOT NULL DEFAULT 'Announcement' AFTER team_id");
    addColumnIfMissing($db, 'csr_announcements', 'message', "TEXT NULL AFTER title");
    addColumnIfMissing($db, 'csr_announcements', 'priority', "ENUM('info','success','warning','danger') NOT NULL DEFAULT 'info' AFTER message");
    addColumnIfMissing($db, 'csr_announcements', 'created_by', "INT NULL AFTER priority");
    addColumnIfMissing($db, 'csr_announcements', 'is_active', "TINYINT(1) NOT NULL DEFAULT 1 AFTER created_by");
    addColumnIfMissing($db, 'csr_announcements', 'starts_at', "DATETIME NULL AFTER is_active");
    addColumnIfMissing($db, 'csr_announcements', 'expires_at', "DATETIME NULL AFTER starts_at");
    addColumnIfMissing($db, 'csr_announcements', 'created_at', "TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER expires_at");
    addColumnIfMissing($db, 'csr_announcements', 'updated_at', "TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at");

    $db->exec("CREATE TABLE IF NOT EXISTS csr_announcement_reads (
        id INT AUTO_INCREMENT PRIMARY KEY,
        announcement_id INT NOT NULL,
        csr_id INT NOT NULL,
        read_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_announcement_read (announcement_id, csr_id),
        INDEX idx_announcement_reads_csr (csr_id),
        INDEX idx_announcement_reads_announcement (announcement_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    addColumnIfMissing($db, 'csr_announcement_reads', 'read_at', "TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER csr_id");

    try {
        // Remove announcement items from the normal notification bell. This keeps announcements on the bullhorn only.
        if (columnExists($db, 'csr_notifications', 'reference_type')) {
            $db->exec("UPDATE csr_notifications SET is_read = 1 WHERE COALESCE(reference_type, '') = 'announcement'");
        }
    } catch (Throwable $e) {}

    try {
        // Old builds accidentally expired announcements because of server timezone/date formatting.
        // The remade system does not hide by expiration; keep old announcements visible until inactive/delete/read.
        $db->exec("UPDATE csr_announcements SET expires_at = NULL WHERE expires_at IS NOT NULL");
    } catch (Throwable $e) {}
}


function ensureCsrTeamTables(PDO $db): void {
    addColumnIfMissing($db, 'csr_users', 'role', "ENUM('csr','team_leader','csr_manager') NOT NULL DEFAULT 'csr' AFTER max_active_chats");

    $db->exec("CREATE TABLE IF NOT EXISTS csr_teams (
        id INT AUTO_INCREMENT PRIMARY KEY,
        team_name VARCHAR(120) NOT NULL,
        manager_id INT NOT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        last_assigned_at DATETIME NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_csr_teams_manager (manager_id),
        INDEX idx_csr_teams_active (is_active, last_assigned_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $db->exec("CREATE TABLE IF NOT EXISTS csr_team_members (
        id INT AUTO_INCREMENT PRIMARY KEY,
        team_id INT NOT NULL,
        csr_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_team_member (team_id, csr_id),
        INDEX idx_team_members_team (team_id),
        INDEX idx_team_members_csr (csr_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}

function isCsrManager(PDO $db, int $csrId): bool {
    ensureAnnouncementTables($db);
    $stmt = $db->prepare("SELECT COALESCE(role, 'csr') FROM csr_users WHERE id = :id LIMIT 1");
    $stmt->execute([':id' => $csrId]);
    return in_array($stmt->fetchColumn(), ['team_leader','csr_manager'], true);
}

function ensureOperationalTables(PDO $db) {
    $db->exec("CREATE TABLE IF NOT EXISTS request_logs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        request_id INT NOT NULL,
        user_id INT NULL,
        user_role VARCHAR(50) NOT NULL,
        actor_name VARCHAR(150) NULL,
        actor_email VARCHAR(150) NULL,
        actor_role_label VARCHAR(80) NULL,
        action VARCHAR(100) NOT NULL,
        old_value TEXT NULL,
        new_value TEXT NULL,
        notes TEXT NULL,
        ip_address VARCHAR(45) NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_request_logs_request (request_id),
        INDEX idx_request_logs_user (user_id, user_role),
        INDEX idx_request_logs_created (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    addColumnIfMissing($db, 'request_logs', 'actor_name', 'VARCHAR(150) NULL AFTER user_role');
    addColumnIfMissing($db, 'request_logs', 'actor_email', 'VARCHAR(150) NULL AFTER actor_name');
    addColumnIfMissing($db, 'request_logs', 'actor_role_label', 'VARCHAR(80) NULL AFTER actor_email');
    addColumnIfMissing($db, 'csr_users', 'role', "ENUM('csr','team_leader','csr_manager') NOT NULL DEFAULT 'csr' AFTER max_active_chats");

    // Manual CSR ticket entry fields. These are additive and safe for older databases.
    addColumnIfMissing($db, 'service_requests', 'ticket_source', 'VARCHAR(80) NULL AFTER request_number');
    addColumnIfMissing($db, 'service_requests', 'secondary_phone', 'VARCHAR(30) NULL AFTER phone_number');
    addColumnIfMissing($db, 'service_requests', 'customer_email', 'VARCHAR(150) NULL AFTER secondary_phone');
    addColumnIfMissing($db, 'service_requests', 'product_model_version', 'VARCHAR(120) NULL AFTER serial_number');
    addColumnIfMissing($db, 'service_requests', 'purchase_date', 'DATE NULL AFTER special_request');
    addColumnIfMissing($db, 'service_requests', 'warranty_type', 'VARCHAR(80) NULL AFTER purchase_date');
    addColumnIfMissing($db, 'service_requests', 'call_received_date', 'DATE NULL AFTER warranty_type');
    addColumnIfMissing($db, 'service_requests', 'is_fake_ticket', 'TINYINT(1) NOT NULL DEFAULT 0 AFTER call_received_date');
    addColumnIfMissing($db, 'service_requests', 'created_by_csr_id', 'INT NULL AFTER is_fake_ticket');
    addColumnIfMissing($db, 'service_requests', 'manual_ticket_number', 'VARCHAR(80) NULL AFTER ticket_source');
    addColumnIfMissing($db, 'service_requests', 'follow_up_reminder', 'VARCHAR(255) NULL AFTER completed_date');
    addColumnIfMissing($db, 'service_requests', 'follow_up_due_at', 'DATETIME NULL AFTER follow_up_reminder');
    addColumnIfMissing($db, 'service_requests', 'in_progress_started_at', 'DATETIME NULL AFTER follow_up_due_at');
    addColumnIfMissing($db, 'service_requests', 'last_updated_at', 'DATETIME NULL AFTER in_progress_started_at');

    $db->exec("CREATE TABLE IF NOT EXISTS csr_activity (
        id INT AUTO_INCREMENT PRIMARY KEY,
        csr_id INT NOT NULL,
        action VARCHAR(100) NOT NULL,
        request_id INT NULL,
        details TEXT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_csr_activity_csr (csr_id),
        INDEX idx_csr_activity_request (request_id),
        INDEX idx_csr_activity_created (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $db->exec("CREATE TABLE IF NOT EXISTS notification_reads (
        id INT AUTO_INCREMENT PRIMARY KEY,
        notification_id INT NOT NULL,
        user_id INT NOT NULL,
        user_role VARCHAR(50) NOT NULL,
        read_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_notification_read (notification_id, user_id, user_role)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}

function logRequestAction(PDO $db, $requestId, $userId, $userRole, $action, $oldValue = null, $newValue = null, $notes = null) {
    ensureOperationalTables($db);
    $actor = getActorInfo($db, $userId, $userRole);
    $stmt = $db->prepare("INSERT INTO request_logs (request_id, user_id, user_role, actor_name, actor_email, actor_role_label, action, old_value, new_value, notes, ip_address)
                          VALUES (:request_id, :user_id, :user_role, :actor_name, :actor_email, :actor_role_label, :action, :old_value, :new_value, :notes, :ip_address)");
    $stmt->execute([
        ':request_id' => (int)$requestId,
        ':user_id' => $userId ?: null,
        ':user_role' => $userRole,
        ':actor_name' => $actor['name'] ?? null,
        ':actor_email' => $actor['email'] ?? null,
        ':actor_role_label' => $actor['role_label'] ?? null,
        ':action' => $action,
        ':old_value' => is_scalar($oldValue) || $oldValue === null ? $oldValue : json_encode($oldValue),
        ':new_value' => is_scalar($newValue) || $newValue === null ? $newValue : json_encode($newValue),
        ':notes' => $notes,
        ':ip_address' => $_SERVER['REMOTE_ADDR'] ?? null,
    ]);
}
function logCSRActivity(PDO $db, $csrId, $action, $requestId = null, $details = null) {
    ensureOperationalTables($db);
    $stmt = $db->prepare("INSERT INTO csr_activity (csr_id, action, request_id, details)
                          VALUES (:csr_id, :action, :request_id, :details)");
    $stmt->execute([
        ':csr_id' => (int)$csrId,
        ':action' => $action,
        ':request_id' => $requestId ?: null,
        ':details' => $details,
    ]);
}


function ensureUnifiedTicketWorkflow(PDO $db): void {
    ensureOperationalTables($db);
    ensureAddressAndCallQueueTables($db);

    addColumnIfMissing($db, 'service_requests', 'manual_ticket_number', "VARCHAR(80) NULL AFTER ticket_source");
    addColumnIfMissing($db, 'service_requests', 'ticket_source', "VARCHAR(80) NULL AFTER request_number");
    addColumnIfMissing($db, 'service_requests', 'source_system', "VARCHAR(40) NULL AFTER ticket_source");
    addColumnIfMissing($db, 'service_requests', 'origin_type', "VARCHAR(80) NULL AFTER source_system");
    addColumnIfMissing($db, 'service_requests', 'er_ticket_id', "VARCHAR(120) NULL AFTER origin_type");
    addColumnIfMissing($db, 'service_requests', 'sync_status', "ENUM('local_only','pending_er_sync','synced_to_er','er_imported','sync_failed') NOT NULL DEFAULT 'local_only' AFTER er_ticket_id");
    addColumnIfMissing($db, 'service_requests', 'sync_error', "TEXT NULL AFTER sync_status");
    addColumnIfMissing($db, 'service_requests', 'last_synced_at', "DATETIME NULL AFTER sync_error");
    addColumnIfMissing($db, 'service_requests', 'follow_up_reminder', "VARCHAR(255) NULL AFTER completed_date");
    addColumnIfMissing($db, 'service_requests', 'follow_up_due_at', "DATETIME NULL AFTER follow_up_reminder");
    addColumnIfMissing($db, 'service_requests', 'in_progress_started_at', "DATETIME NULL AFTER follow_up_due_at");
    addColumnIfMissing($db, 'service_requests', 'last_updated_at', "DATETIME NULL AFTER in_progress_started_at");

    // CSR Verification Queue workflow: CX submissions wait for CSR review before they appear as normal tickets.
    addColumnIfMissing($db, 'service_requests', 'verification_status', "ENUM('pending','approved','rejected') NOT NULL DEFAULT 'approved' AFTER last_updated_at");
    addColumnIfMissing($db, 'service_requests', 'verification_reviewed_by', "INT NULL AFTER verification_status");
    addColumnIfMissing($db, 'service_requests', 'verification_reviewed_at', "DATETIME NULL AFTER verification_reviewed_by");
    addColumnIfMissing($db, 'service_requests', 'verification_reject_reason', "VARCHAR(150) NULL AFTER verification_reviewed_at");
    addColumnIfMissing($db, 'service_requests', 'verification_notes', "TEXT NULL AFTER verification_reject_reason");

    try {
        $db->exec("UPDATE service_requests
                  SET ticket_source = CASE
                      WHEN ticket_source = 'customer' OR ticket_source IS NULL OR ticket_source = '' THEN 'cx_online'
                      WHEN ticket_source IN ('csr_manual','chat_followup','cx_online') THEN ticket_source
                      ELSE ticket_source
                  END");
    } catch (Exception $e) {}

    try {
        $db->exec("UPDATE service_requests
                  SET ticket_source = 'csr_manual'
                  WHERE created_by_csr_id IS NOT NULL OR manual_ticket_number IS NOT NULL");
    } catch (Exception $e) {}

    try {
        $db->exec("UPDATE service_requests
                  SET last_updated_at = COALESCE(last_updated_at, requested_at)");
    } catch (Exception $e) {}

    try {
        $db->exec("UPDATE service_requests
                  SET source_system = CASE
                      WHEN ticket_source IN ('er_typescript','er_imported','typescript_er') THEN 'typescript_er'
                      WHEN ticket_source = 'csr_manual' OR created_by_csr_id IS NOT NULL OR manual_ticket_number IS NOT NULL THEN 'php_csr'
                      ELSE 'php_cx'
                  END
                  WHERE source_system IS NULL OR source_system = ''");
    } catch (Exception $e) {}

    try {
        $db->exec("UPDATE service_requests
                  SET origin_type = CASE
                      WHEN ticket_source IN ('er_typescript','er_imported','typescript_er') THEN 'ER Ticket'
                      WHEN ticket_source = 'csr_manual' OR created_by_csr_id IS NOT NULL OR manual_ticket_number IS NOT NULL THEN 'Manual Ticket'
                      WHEN ticket_source IN ('cx_online','customer','') OR ticket_source IS NULL THEN 'Customer App'
                      ELSE REPLACE(ticket_source, '_', ' ')
                  END
                  WHERE origin_type IS NULL OR origin_type = ''");
    } catch (Exception $e) {}

    ensureWebRtcCallWorkflow($db);
}

function getTicketSourceLabel($source): string {
    $source = strtolower(trim((string)$source));
    if ($source === 'csr_manual') return 'CSR Manual Ticket';
    if ($source === 'chat_followup') return 'Chat Follow-Up Request';
    if ($source === 'web_call') return 'Web Call';
    if ($source === 'phone_call') return 'Phone Call';
    if (in_array($source, ['er_typescript','er_imported','typescript_er'], true)) return 'TypeScript / ER';
    if ($source === 'cx_online' || $source === 'customer' || $source === '') return 'Customer App';
    return ucwords(str_replace('_', ' ', $source));
}

function getTicketSourceSystemLabel($sourceSystem): string {
    $sourceSystem = strtolower(trim((string)$sourceSystem));
    if ($sourceSystem === 'typescript_er') return 'TypeScript / ER';
    if ($sourceSystem === 'php_csr') return 'PHP / CSR';
    if ($sourceSystem === 'php_cx') return 'PHP / Customer App';
    return 'Unknown Source';
}

function ensureWebRtcCallWorkflow(PDO $db): void {
    try {
        addColumnIfMissing($db, 'call_requests', 'room_token', "VARCHAR(128) NULL AFTER provider_call_sid");
        addColumnIfMissing($db, 'call_requests', 'call_mode', "ENUM('external_phone','webrtc') NOT NULL DEFAULT 'external_phone' AFTER room_token");
        addColumnIfMissing($db, 'call_requests', 'customer_email', "VARCHAR(150) NULL AFTER customer_name");
        addColumnIfMissing($db, 'call_requests', 'call_reason', "VARCHAR(255) NULL AFTER notes");
        addColumnIfMissing($db, 'call_requests', 'call_direction', "ENUM('inbound','outbound') NOT NULL DEFAULT 'inbound' AFTER call_reason");
        try { $db->exec("UPDATE call_requests SET call_direction = 'inbound' WHERE call_direction IS NULL OR call_direction = ''"); } catch (Throwable $e) {}
        try { $db->exec("CREATE UNIQUE INDEX idx_call_requests_room_token ON call_requests(room_token)"); } catch (Throwable $e) {}
        try { $db->exec("CREATE INDEX idx_call_requests_mode_status ON call_requests(call_mode, status)"); } catch (Throwable $e) {}
        try { $db->exec("CREATE INDEX idx_call_requests_direction ON call_requests(call_direction)"); } catch (Throwable $e) {}
    } catch (Throwable $e) {}

    try {
        $db->exec("CREATE TABLE IF NOT EXISTS call_logs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            call_request_id INT NULL,
            request_id INT NULL,
            customer_id INT NULL,
            customer_email VARCHAR(150) NULL,
            csr_id INT NULL,
            result ENUM('connected','missed','cancelled','failed','ended') NOT NULL DEFAULT 'ended',
            duration_seconds INT NOT NULL DEFAULT 0,
            notes TEXT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_call_logs_call_request_id (call_request_id),
            INDEX idx_call_logs_request_id (request_id),
            INDEX idx_call_logs_customer_id (customer_id),
            INDEX idx_call_logs_csr_id (csr_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        addColumnIfMissing($db, 'call_logs', 'call_direction', "ENUM('inbound','outbound') NOT NULL DEFAULT 'inbound' AFTER customer_email");
    } catch (Throwable $e) {}
}

function validateUploadedImage($tmpPath, $originalName, $sizeBytes) {
    $allowedExt = ['jpg','jpeg','png','gif','webp'];
    $allowedMime = ['image/jpeg','image/png','image/gif','image/webp'];
    $maxSize = 5 * 1024 * 1024;
    $ext = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));

    if (!in_array($ext, $allowedExt, true)) {
        return ['success' => false, 'message' => 'Invalid file extension.'];
    }
    if ($sizeBytes <= 0 || $sizeBytes > $maxSize) {
        return ['success' => false, 'message' => 'Image must be 5MB or smaller.'];
    }
    if (!is_uploaded_file($tmpPath)) {
        return ['success' => false, 'message' => 'Invalid upload.'];
    }
    $mime = mime_content_type($tmpPath);
    if (!in_array($mime, $allowedMime, true)) {
        return ['success' => false, 'message' => 'Invalid image MIME type.'];
    }
    if (@getimagesize($tmpPath) === false) {
        return ['success' => false, 'message' => 'Uploaded file is not a valid image.'];
    }
    return ['success' => true, 'extension' => $ext, 'mime' => $mime];
}

function safeUploadDirectory($dir) {
    if (!is_dir($dir)) {
        mkdir($dir, 0755, true);
    }
    $htaccess = rtrim($dir, '/\\') . '/.htaccess';
    if (!file_exists($htaccess)) {
        file_put_contents($htaccess, "Options -Indexes\n<FilesMatch \"\\.(php|phtml|phar|cgi|pl|py|jsp|asp|aspx|sh)$\">\nDeny from all\n</FilesMatch>\n");
    }
}

function tableExistsSafe(PDO $db, string $table): bool {
    try {
        $stmt = $db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table");
        $stmt->execute([':table' => $table]);
        return (int)$stmt->fetchColumn() > 0;
    } catch (Exception $e) {
        return false;
    }
}

function addInClauseParams(array &$params, array $values, string $prefix): string {
    $placeholders = [];
    $i = 0;
    foreach (array_values(array_unique(array_map('intval', $values))) as $value) {
        if ($value <= 0) { continue; }
        $key = ':' . $prefix . '_' . $i++;
        $params[$key] = $value;
        $placeholders[] = $key;
    }
    return $placeholders ? implode(',', $placeholders) : 'NULL';
}

function getCsrRequestScope(PDO $db, int $csrId): array {
    ensureCsrTeamTables($db);

    $stmt = $db->prepare("SELECT id, COALESCE(role, 'csr') AS role FROM csr_users WHERE id = :id AND is_active = 1 LIMIT 1");
    $stmt->execute([':id' => $csrId]);
    $csr = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$csr) {
        return [
            'csr_id' => $csrId,
            'role' => 'csr',
            'is_manager' => false,
            'team_ids' => [],
            'manager_ids' => [],
            'member_ids' => [],
            'visible_csr_ids' => [$csrId],
        ];
    }

    $role = (string)($csr['role'] ?? 'csr');
    $isManager = in_array($role, ['team_leader','csr_manager'], true);
    $teamIds = [];
    $managerIds = [];
    $memberIds = [];

    if ($isManager) {
        if ($role === 'csr_manager') {
            $teamRows = $db->query("SELECT id, manager_id FROM csr_teams WHERE is_active = 1")->fetchAll(PDO::FETCH_ASSOC);
            foreach ($teamRows as $teamRow) {
                $teamIds[] = (int)$teamRow['id'];
                $managerIds[] = (int)$teamRow['manager_id'];
            }
        } else {
            $stmt = $db->prepare("SELECT id, manager_id FROM csr_teams WHERE manager_id = :manager_id AND is_active = 1");
            $stmt->execute([':manager_id' => $csrId]);
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $teamRow) {
                $teamIds[] = (int)$teamRow['id'];
                $managerIds[] = (int)$teamRow['manager_id'];
            }
        }

        if ($teamIds) {
            $in = implode(',', array_fill(0, count($teamIds), '?'));
            $stmt = $db->prepare("SELECT DISTINCT csr_id FROM csr_team_members WHERE team_id IN ($in)");
            $stmt->execute($teamIds);
            $memberIds = array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));
        }
    } else {
        $stmt = $db->prepare("SELECT DISTINCT t.id, t.manager_id
                              FROM csr_team_members tm
                              JOIN csr_teams t ON t.id = tm.team_id
                              WHERE tm.csr_id = :csr_id
                                AND t.is_active = 1");
        $stmt->execute([':csr_id' => $csrId]);
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $teamIds[] = (int)$row['id'];
            $managerIds[] = (int)$row['manager_id'];
        }
    }

    $visibleCsrIds = $isManager ? array_values(array_unique(array_merge([$csrId], $managerIds, $memberIds))) : [$csrId];

    return [
        'csr_id' => $csrId,
        'role' => $role,
        'is_manager' => $isManager,
        'team_ids' => array_values(array_unique(array_filter($teamIds))),
        'manager_ids' => array_values(array_unique(array_filter($managerIds))),
        'member_ids' => array_values(array_unique(array_filter($memberIds))),
        'visible_csr_ids' => array_values(array_unique(array_filter($visibleCsrIds))),
    ];
}

function buildCsrRequestVisibilitySql(PDO $db, int $csrId, string $requestAlias, array &$params, string $prefix = 'scope'): string {
    $scope = getCsrRequestScope($db, $csrId);
    $requestIdExpr = $requestAlias . '.id';
    $paramSerial = 0;

    // Create unique placeholders every time to avoid SQLSTATE[HY093].
    $makeIn = function(array $values, string $label) use (&$params, $prefix, &$paramSerial): string {
        $placeholders = [];
        foreach (array_values(array_unique(array_map('intval', $values))) as $value) {
            if ($value <= 0) { continue; }
            $key = ':' . $prefix . '_' . $label . '_' . $paramSerial++;
            $params[$key] = $value;
            $placeholders[] = $key;
        }
        return $placeholders ? implode(',', $placeholders) : 'NULL';
    };

    $makeValue = function(int $value, string $label) use (&$params, $prefix, &$paramSerial): string {
        $key = ':' . $prefix . '_' . $label . '_' . $paramSerial++;
        $params[$key] = $value;
        return $key;
    };

    $hasLocks = tableExistsSafe($db, 'csr_request_locks');
    $hasChats = tableExistsSafe($db, 'chat_conversations')
        && columnExists($db, 'chat_conversations', 'request_id');
    $hasAssignments = tableExistsSafe($db, 'service_assignments');

    $visibleCsrIds = array_values(array_unique(array_map('intval', $scope['visible_csr_ids'] ?? [$csrId])));
    if (empty($visibleCsrIds)) {
        $visibleCsrIds = [$csrId];
    }

    $lockVisibleCsrIn = $makeIn($visibleCsrIds, 'lock_visible_csr');

    // STRICT OWNERSHIP RULE:
    // 1. If a ticket has a CSR lock, that lock is the current owner.
    //    Only that CSR or that CSR's Team Leader can see it.
    // 2. If there is no CSR lock, use the team/chat assignment.
    // 3. If there is no lock and no chat assignment, fall back to service_assignments.assigned_by.
    // 4. Do NOT use request_logs or csr_activity for visibility because old edits/history can leak records across teams.
    $lockedCondition = '0 = 1';
    $notLockedCondition = '1 = 1';
    if ($hasLocks) {
        $lockedCondition = "EXISTS (SELECT 1 FROM csr_request_locks crl_owner WHERE crl_owner.request_id = {$requestIdExpr} AND crl_owner.csr_id IN ($lockVisibleCsrIn))";
        $notLockedCondition = "NOT EXISTS (SELECT 1 FROM csr_request_locks crl_any WHERE crl_any.request_id = {$requestIdExpr})";
    }

    $unlockedClauses = [];

    if ($hasChats) {
        if (!empty($scope['is_manager'])) {
            $chatParts = [];

            if (columnExists($db, 'chat_conversations', 'assigned_team_id')) {
                $teamIn = $makeIn($scope['team_ids'] ?? [], 'chat_team');
                $chatParts[] = "cc_scope.assigned_team_id IN ($teamIn)";
            }

            if (columnExists($db, 'chat_conversations', 'assigned_manager_id')) {
                $managerKey = $makeValue($csrId, 'chat_manager');
                $chatParts[] = "cc_scope.assigned_manager_id = $managerKey";
            }

            if (columnExists($db, 'chat_conversations', 'assigned_csr_id')) {
                $chatAssignedCsrIn = $makeIn($visibleCsrIds, 'chat_assigned_csr');
                $chatParts[] = "cc_scope.assigned_csr_id IN ($chatAssignedCsrIn)";
            }

            if (columnExists($db, 'chat_conversations', 'csr_id')) {
                $chatOwnerCsrIn = $makeIn($visibleCsrIds, 'chat_owner_csr');
                $chatParts[] = "cc_scope.csr_id IN ($chatOwnerCsrIn)";
            }

            if ($chatParts) {
                $unlockedClauses[] = "EXISTS (SELECT 1 FROM chat_conversations cc_scope WHERE cc_scope.request_id = {$requestIdExpr} AND (" . implode(' OR ', $chatParts) . "))";
            }
        } else {
            $csrChatParts = [];
            $csrKeyA = $makeValue($csrId, 'chat_regular_a');
            if (columnExists($db, 'chat_conversations', 'assigned_csr_id')) {
                $csrChatParts[] = "cc_scope.assigned_csr_id = $csrKeyA";
            }

            if (columnExists($db, 'chat_conversations', 'csr_id')) {
                $csrKeyB = $makeValue($csrId, 'chat_regular_b');
                $csrChatParts[] = "cc_scope.csr_id = $csrKeyB";
            }

            if ($csrChatParts) {
                $unlockedClauses[] = "EXISTS (SELECT 1 FROM chat_conversations cc_scope WHERE cc_scope.request_id = {$requestIdExpr} AND (" . implode(' OR ', $csrChatParts) . "))";
            }
        }
    }

    // Approved customer-app requests should be visible in the normal view-only ticket table for CSR, TL, and Manager.
    // Pending/rejected customer requests are still kept out by the Ticket Table approved filter.
    if (columnExists($db, 'service_requests', 'verification_status')) {
        $unlockedClauses[] = "(COALESCE({$requestAlias}.verification_status, 'approved') = 'approved' AND ({$requestAlias}.source_system = 'php_cx' OR {$requestAlias}.ticket_source IN ('customer','cx_online')) AND COALESCE({$requestAlias}.is_fake_ticket, 0) = 0)";
    } else {
        $unlockedClauses[] = "({$requestAlias}.source_system = 'php_cx' OR {$requestAlias}.ticket_source IN ('customer','cx_online'))";
    }

    // Team Leaders also need to work from the main ticket queue.
    // Allow them to see unclaimed customer/main tickets from service_requests,
    // then once they manage/lock the ticket it becomes owned by that Team Leader.
    if (($scope['role'] ?? 'csr') === 'team_leader') {
        $unlockedClauses[] = "({$requestAlias}.ticket_source IS NULL OR {$requestAlias}.ticket_source = '' OR {$requestAlias}.ticket_source IN ('customer','cx_online'))";
    }

    // CSR-created manual tickets must be visible to the CSR who created them,
    // and to that CSR's manager/team scope. Without this, newly submitted
    // Ticket-tab entries can save correctly but disappear from Manual Request
    // because they have no chat assignment or technician assignment yet.
    if (columnExists($db, 'service_requests', 'created_by_csr_id')) {
        $createdByVisibleCsrIn = $makeIn($visibleCsrIds, 'manual_creator_csr');
        $unlockedClauses[] = "{$requestAlias}.created_by_csr_id IN ($createdByVisibleCsrIn)";
    }

    if ($hasAssignments) {
        $noChatCondition = '1 = 1';
        if ($hasChats) {
            $noChatCondition = "NOT EXISTS (SELECT 1 FROM chat_conversations cc_any WHERE cc_any.request_id = {$requestIdExpr})";
        }

        $assignVisibleCsrIn = $makeIn($visibleCsrIds, 'assign_visible_csr');
        $unlockedClauses[] = "($noChatCondition AND EXISTS (SELECT 1 FROM service_assignments sa_scope WHERE sa_scope.request_id = {$requestIdExpr} AND sa_scope.assigned_by IN ($assignVisibleCsrIn)))";
    }

    if (!$unlockedClauses) {
        $unlockedSql = '0 = 1';
    } else {
        $unlockedSql = '(' . implode(' OR ', $unlockedClauses) . ')';
    }

    return "(($lockedCondition) OR (($notLockedCondition) AND $unlockedSql))";
}

function canCsrAccessRequest(PDO $db, int $csrId, int $requestId): bool {
    $params = [':request_id_scope_check' => $requestId];
    $visibilitySql = buildCsrRequestVisibilitySql($db, $csrId, 'sr', $params, 'access_scope');
    $stmt = $db->prepare("SELECT 1 FROM service_requests sr WHERE sr.id = :request_id_scope_check AND {$visibilitySql} LIMIT 1");
    foreach ($params as $key => $value) {
        $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
    }
    $stmt->execute();
    return (bool)$stmt->fetchColumn();
}

function requireCsrRequestAccessJson(PDO $db, int $csrId, int $requestId): void {
    if (!canCsrAccessRequest($db, $csrId, $requestId)) {
        jsonResponse(false, 'Access denied. This request is not assigned to you or your team.');
    }
}



function normalizeStateForCoverage(string $state): string {
    $state = trim($state);
    $map = [
        'AL'=>'Alabama','ALABAMA'=>'Alabama','AR'=>'Arkansas','ARKANSAS'=>'Arkansas','FL'=>'Florida','FLORIDA'=>'Florida','GA'=>'Georgia','GEORGIA'=>'Georgia',
        'IL'=>'Illinois','ILLINOIS'=>'Illinois','IN'=>'Indiana','INDIANA'=>'Indiana','KY'=>'Kentucky','KENTUCKY'=>'Kentucky','LA'=>'Louisiana','LOUISIANA'=>'Louisiana',
        'MS'=>'Mississippi','MISSISSIPPI'=>'Mississippi','MO'=>'Missouri','MISSOURI'=>'Missouri','NC'=>'North Carolina','NORTH CAROLINA'=>'North Carolina',
        'OK'=>'Oklahoma','OKLAHOMA'=>'Oklahoma','SC'=>'South Carolina','SOUTH CAROLINA'=>'South Carolina','TN'=>'Tennessee','TENNESSEE'=>'Tennessee',
        'TX'=>'Texas','TEXAS'=>'Texas','VA'=>'Virginia','VIRGINIA'=>'Virginia'
    ];
    return $map[strtoupper($state)] ?? $state;
}

function stateAbbrevForCoverage(string $state): string {
    $state = trim($state);
    $map = [
        'ALABAMA'=>'AL','AL'=>'AL','ARKANSAS'=>'AR','AR'=>'AR','FLORIDA'=>'FL','FL'=>'FL','GEORGIA'=>'GA','GA'=>'GA',
        'ILLINOIS'=>'IL','IL'=>'IL','INDIANA'=>'IN','IN'=>'IN','KENTUCKY'=>'KY','KY'=>'KY','LOUISIANA'=>'LA','LA'=>'LA',
        'MISSISSIPPI'=>'MS','MS'=>'MS','MISSOURI'=>'MO','MO'=>'MO','NORTH CAROLINA'=>'NC','NC'=>'NC','OKLAHOMA'=>'OK','OK'=>'OK',
        'SOUTH CAROLINA'=>'SC','SC'=>'SC','TENNESSEE'=>'TN','TN'=>'TN','TEXAS'=>'TX','TX'=>'TX','VIRGINIA'=>'VA','VA'=>'VA'
    ];
    return $map[strtoupper($state)] ?? strtoupper($state);
}

function ensureCoverageCityRecord(PDO $db, string $city, string $state, string $region = ''): ?int {
    $city = trim($city);
    $state = normalizeStateForCoverage($state);
    $region = trim($region);
    if ($city === '') { return null; }
    try {
        $q = $db->prepare("SELECT id FROM cities WHERE LOWER(city_name)=LOWER(:city) AND (LOWER(state)=LOWER(:state) OR UPPER(state)=UPPER(:abbr)) LIMIT 1");
        $q->execute([':city'=>$city, ':state'=>$state, ':abbr'=>stateAbbrevForCoverage($state)]);
        $id = $q->fetchColumn();
        if ($id) { return (int)$id; }
        $ins = $db->prepare("INSERT INTO cities (city_name, region, state, is_active) VALUES (:city, :region, :state, 1)");
        $ins->execute([':city'=>$city, ':region'=>$region, ':state'=>$state]);
        return (int)$db->lastInsertId();
    } catch (Exception $e) {
        return null;
    }
}

function ensureAddressAndCallQueueTables(PDO $db): void {
    $db->exec("CREATE TABLE IF NOT EXISTS service_areas (
        id INT AUTO_INCREMENT PRIMARY KEY,
        zip_code VARCHAR(10) NOT NULL,
        city VARCHAR(100) NOT NULL,
        state VARCHAR(50) NOT NULL,
        region VARCHAR(100) NOT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY unique_zip_city_state_region (zip_code, city, state, region),
        INDEX idx_service_zip (zip_code),
        INDEX idx_service_city (city),
        INDEX idx_service_state (state),
        INDEX idx_service_region (region),
        INDEX idx_service_active (is_active)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Address normalization fields
    addColumnIfMissing($db, 'cities', 'region', "VARCHAR(100) NULL AFTER id");
    addColumnIfMissing($db, 'locations', 'region', "VARCHAR(100) NULL AFTER id");
    addColumnIfMissing($db, 'zip_codes', 'city_id', "INT NULL AFTER id");
    addColumnIfMissing($db, 'zip_codes', 'region', "VARCHAR(100) NULL AFTER zip_code");
    addColumnIfMissing($db, 'zip_codes', 'state', "VARCHAR(50) NULL AFTER region");
    addColumnIfMissing($db, 'customers', 'region', "VARCHAR(100) NULL AFTER address");
    addColumnIfMissing($db, 'customers', 'state', "VARCHAR(50) NULL AFTER city_id");
    addColumnIfMissing($db, 'service_requests', 'region', "VARCHAR(100) NULL AFTER service_address_2");
    addColumnIfMissing($db, 'service_requests', 'state', "VARCHAR(50) NULL AFTER city_id");
    addColumnIfMissing($db, 'service_requests', 'call_request_id', "INT NULL AFTER validation_message");

    // Backfill city_id/state/region into zip_codes where possible.
    try {
        $db->exec("UPDATE zip_codes z
                  JOIN locations l ON l.id = z.location_id
                  JOIN cities c ON c.id = l.city_id
                  SET z.city_id = COALESCE(z.city_id, c.id),
                      z.state = COALESCE(NULLIF(z.state,''), c.state),
                      z.region = COALESCE(NULLIF(z.region,''), NULLIF(l.region,''), NULLIF(c.region,''), c.city_name)");
    } catch (Exception $e) {}

    $db->exec("CREATE TABLE IF NOT EXISTS call_requests (
        id INT AUTO_INCREMENT PRIMARY KEY,
        request_id INT NULL,
        customer_id INT NULL,
        customer_name VARCHAR(200) NOT NULL,
        phone_number VARCHAR(40) NOT NULL,
        notes TEXT NULL,
        team_id INT NULL,
        manager_id INT NULL,
        assigned_csr_id INT NULL,
        accepted_by INT NULL,
        status ENUM('manager_queue','assigned','accepted','missed','completed','cancelled') NOT NULL DEFAULT 'manager_queue',
        queued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        assigned_at DATETIME NULL,
        accepted_at DATETIME NULL,
        completed_at DATETIME NULL,
        call_started_at DATETIME NULL,
        call_ended_at DATETIME NULL,
        call_duration_seconds INT NULL,
        recording_path VARCHAR(255) NULL,
        recording_mime VARCHAR(100) NULL,
        recording_uploaded_at DATETIME NULL,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_call_status (status, queued_at),
        INDEX idx_call_team (team_id, manager_id),
        INDEX idx_call_assigned_csr (assigned_csr_id),
        INDEX idx_call_request (request_id),
        INDEX idx_call_customer (customer_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    addColumnIfMissing($db, 'call_requests', 'call_started_at', 'DATETIME NULL AFTER completed_at');
    addColumnIfMissing($db, 'call_requests', 'call_ended_at', 'DATETIME NULL AFTER call_started_at');
    addColumnIfMissing($db, 'call_requests', 'call_duration_seconds', 'INT NULL AFTER call_ended_at');
    addColumnIfMissing($db, 'call_requests', 'recording_path', 'VARCHAR(255) NULL AFTER call_duration_seconds');
    addColumnIfMissing($db, 'call_requests', 'recording_mime', 'VARCHAR(100) NULL AFTER recording_path');
    addColumnIfMissing($db, 'call_requests', 'recording_uploaded_at', 'DATETIME NULL AFTER recording_mime');
    addColumnIfMissing($db, 'call_requests', 'call_direction', "ENUM('inbound','outbound') NOT NULL DEFAULT 'inbound' AFTER call_reason");
    try { $db->exec("UPDATE call_requests SET call_direction = 'inbound' WHERE call_direction IS NULL OR call_direction = ''"); } catch (Throwable $e) {}
}

function getZipAddressMeta(PDO $db, string $zipCode): ?array {
    ensureAddressAndCallQueueTables($db);
    $zipCode = trim($zipCode);
    if ($zipCode === '') { return null; }

    // New master coverage table: this is the source of truth for ZIP -> Region/City/State.
    try {
        $stmt = $db->prepare("SELECT id, zip_code, city, state, region
                              FROM service_areas
                              WHERE zip_code = :zip AND is_active = 1
                              ORDER BY region, city
                              LIMIT 1");
        $stmt->execute([':zip' => $zipCode]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row) {
            $stateFull = normalizeStateForCoverage((string)$row['state']);
            $cityId = ensureCoverageCityRecord($db, (string)$row['city'], $stateFull, (string)$row['region']);
            return [
                'zip_code' => $row['zip_code'],
                'city_id' => $cityId,
                'city_name' => $row['city'],
                'state' => $stateFull,
                'state_abbr' => stateAbbrevForCoverage($stateFull),
                'region' => $row['region'],
                'location_id' => null,
                'location_name' => $row['city'],
                'service_area_id' => (int)$row['id'],
                'service_region_name' => $row['region'],
                'service_region_state' => $stateFull
            ];
        }
    } catch (Exception $e) {}

    // Legacy fallback for older zip_codes/locations/cities data.
    try {
        $stmt = $db->prepare("SELECT 
                                     z.zip_code,
                                     z.region AS zip_region,
                                     z.state AS zip_state,
                                     c.id AS city_id,
                                     c.city_name AS service_region_name,
                                     c.state AS service_region_state,
                                     c.region AS city_region,
                                     l.id AS location_id,
                                     l.location_name,
                                     l.region AS location_region
                              FROM zip_codes z
                              JOIN locations l ON l.id = z.location_id
                              JOIN cities c ON c.id = COALESCE(z.city_id, l.city_id)
                              WHERE z.zip_code = :zip
                                AND COALESCE(z.is_active,1)=1
                                AND COALESCE(l.is_active,1)=1
                                AND COALESCE(c.is_active,1)=1
                              ORDER BY c.city_name, l.location_name
                              LIMIT 1");
        $stmt->execute([':zip' => $zipCode]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$row) { return null; }
        $actualCity = trim((string)($row['location_name'] ?? ''));
        $serviceRegion = trim((string)($row['zip_region'] ?: ($row['location_region'] ?: ($row['city_region'] ?: $row['service_region_name']))));
        $state = normalizeStateForCoverage((string)($row['zip_state'] ?: $row['service_region_state']));
        return [
            'zip_code' => $row['zip_code'],
            'city_id' => (int)$row['city_id'],
            'city_name' => $actualCity ?: $row['service_region_name'],
            'state' => $state,
            'state_abbr' => stateAbbrevForCoverage($state),
            'region' => $serviceRegion ?: $row['service_region_name'],
            'location_id' => (int)$row['location_id'],
            'location_name' => $actualCity ?: $row['service_region_name'],
            'service_region_name' => $row['service_region_name'],
            'service_region_state' => $row['service_region_state']
        ];
    } catch (Exception $e) {
        return null;
    }
}

function routeCallRequestToTeam(PDO $db): array {
    ensureCsrTeamTables($db);

    // Prefer active teams that have an active Team Leader/Manager and at least one active regular CSR member.
    // This prevents new web-call requests from being sent to a dead manager queue that nobody can assign.
    $stmt = $db->query("SELECT t.id, t.manager_id, t.team_name
                       FROM csr_teams t
                       JOIN csr_users m ON m.id = t.manager_id
                       WHERE t.is_active = 1
                         AND m.is_active = 1
                         AND COALESCE(m.role, 'csr') IN ('team_leader','csr_manager')
                         AND EXISTS (
                             SELECT 1
                             FROM csr_team_members tm
                             JOIN csr_users cu ON cu.id = tm.csr_id
                             WHERE tm.team_id = t.id
                               AND cu.is_active = 1
                               AND COALESCE(cu.role, 'csr') = 'csr'
                         )
                       ORDER BY COALESCE(t.last_assigned_at, '1970-01-01') ASC, t.id ASC
                       LIMIT 1");
    $team = $stmt ? $stmt->fetch(PDO::FETCH_ASSOC) : null;

    // Fallback: any active team with active Team Leader/Manager.
    if (!$team) {
        $stmt = $db->query("SELECT t.id, t.manager_id, t.team_name
                           FROM csr_teams t
                           JOIN csr_users m ON m.id = t.manager_id
                           WHERE t.is_active = 1
                             AND m.is_active = 1
                             AND COALESCE(m.role, 'csr') IN ('team_leader','csr_manager')
                           ORDER BY COALESCE(t.last_assigned_at, '1970-01-01') ASC, t.id ASC
                           LIMIT 1");
        $team = $stmt ? $stmt->fetch(PDO::FETCH_ASSOC) : null;
    }

    if (!$team) {
        return ['team_id'=>null, 'manager_id'=>null, 'team_name'=>'Unassigned Queue'];
    }

    $upd = $db->prepare("UPDATE csr_teams SET last_assigned_at = NOW() WHERE id = :id");
    $upd->execute([':id'=>(int)$team['id']]);
    return ['team_id'=>(int)$team['id'], 'manager_id'=>(int)$team['manager_id'], 'team_name'=>$team['team_name']];
}

function getCsrTeamMemberIds(PDO $db, int $managerId): array {
    ensureCsrTeamTables($db);
    $stmt = $db->prepare("SELECT DISTINCT tm.csr_id FROM csr_team_members tm JOIN csr_teams t ON t.id = tm.team_id WHERE t.manager_id = :manager_id AND t.is_active = 1");
    $stmt->execute([':manager_id'=>$managerId]);
    return array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));
}

function canCsrAccessCallRequest(PDO $db, int $csrId, int $callId): bool {
    ensureAddressAndCallQueueTables($db);
    $scope = getCsrRequestScope($db, $csrId);
    $params = [':call_access_id' => $callId];

    if (!empty($scope['is_manager'])) {
        $teamIds = $scope['team_ids'] ?? [];
        $in = addInClauseParams($params, $teamIds, 'call_team_scope');
        $params[':call_manager_a'] = $csrId;
        $params[':call_manager_b'] = $csrId;
        $params[':call_manager_c'] = $csrId;

        $sql = "SELECT 1 
                FROM call_requests 
                WHERE id = :call_access_id 
                  AND (
                        manager_id = :call_manager_a 
                        OR team_id IN ($in) 
                        OR assigned_csr_id = :call_manager_b 
                        OR accepted_by = :call_manager_c
                  ) 
                LIMIT 1";
    } else {
        $params[':call_csr_a'] = $csrId;
        $params[':call_csr_b'] = $csrId;

        $sql = "SELECT 1 
                FROM call_requests 
                WHERE id = :call_access_id 
                  AND (
                        assigned_csr_id = :call_csr_a 
                        OR accepted_by = :call_csr_b
                        OR (status = 'manager_queue' AND accepted_by IS NULL)
                  ) 
                LIMIT 1";
    }

    $stmt = $db->prepare($sql);
    foreach ($params as $k => $v) {
        $stmt->bindValue($k, $v, is_int($v) ? PDO::PARAM_INT : PDO::PARAM_STR);
    }
    $stmt->execute();
    return (bool)$stmt->fetchColumn();
}

?>
