<?php
function ensureChatTables(PDO $db) {
    static $done = false;
    if ($done) { return; }

    $db->exec("CREATE TABLE IF NOT EXISTS chat_conversations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        request_id INT NOT NULL UNIQUE,
        customer_id INT NOT NULL,
        csr_id INT NULL,
        status ENUM('open','closed') DEFAULT 'open',
        last_customer_read_at DATETIME NULL,
        last_csr_read_at DATETIME NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_customer_id (customer_id),
        INDEX idx_csr_id (csr_id),
        INDEX idx_updated_at (updated_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $db->exec("CREATE TABLE IF NOT EXISTS chat_messages (
        id INT AUTO_INCREMENT PRIMARY KEY,
        conversation_id INT NOT NULL,
        request_id INT NOT NULL,
        sender_type ENUM('customer','csr','system','bot') NOT NULL,
        sender_id INT NULL,
        message TEXT NOT NULL,
        message_type ENUM('chat','system','bot','quick_reply','technician','status','reminder') DEFAULT 'chat',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_conversation_id (conversation_id),
        INDEX idx_request_id (request_id),
        INDEX idx_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    ensureChatAssignmentColumns($db);
    $done = true;
}

function chatColumnExists(PDO $db, string $table, string $column): bool {
    $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;
}

function chatIndexExists(PDO $db, string $table, string $index): bool {
    $stmt = $db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table AND INDEX_NAME = :index_name");
    $stmt->execute([':table' => $table, ':index_name' => $index]);
    return (int)$stmt->fetchColumn() > 0;
}

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

function addChatIndexIfMissing(PDO $db, string $table, string $index, string $definition): void {
    if (!chatIndexExists($db, $table, $index)) {
        $db->exec("ALTER TABLE `{$table}` ADD INDEX `{$index}` {$definition}");
    }
}

function ensureChatAssignmentColumns(PDO $db): void {
    addChatColumnIfMissing($db, 'chat_conversations', 'assigned_csr_id', 'INT NULL AFTER csr_id');
    addChatColumnIfMissing($db, 'chat_conversations', 'assigned_team_id', 'INT NULL AFTER assigned_csr_id');
    addChatColumnIfMissing($db, 'chat_conversations', 'assigned_manager_id', 'INT NULL AFTER assigned_team_id');
    addChatColumnIfMissing($db, 'chat_conversations', 'assigned_at', 'DATETIME NULL AFTER assigned_manager_id');
    addChatColumnIfMissing($db, 'chat_conversations', 'first_response_at', 'DATETIME NULL AFTER assigned_at');
    addChatColumnIfMissing($db, 'chat_conversations', 'last_customer_message_at', 'DATETIME NULL AFTER first_response_at');
    addChatColumnIfMissing($db, 'chat_conversations', 'assignment_status', "ENUM('unassigned','manager_queue','waiting_first_response','active','returned_queue','closed') DEFAULT 'unassigned' AFTER last_customer_message_at");
    try {
        $db->exec("ALTER TABLE chat_conversations MODIFY assignment_status ENUM('unassigned','manager_queue','waiting_first_response','active','returned_queue','closed') DEFAULT 'unassigned'");
    } catch (Exception $e) {
        // Existing database may already have a compatible column.
    }
    addChatColumnIfMissing($db, 'chat_conversations', 'returned_to_queue_at', 'DATETIME NULL AFTER assignment_status');
    addChatColumnIfMissing($db, 'chat_conversations', 'assignment_note', 'VARCHAR(255) NULL AFTER returned_to_queue_at');
    addChatColumnIfMissing($db, 'csr_users', 'max_active_chats', 'INT NOT NULL DEFAULT 5 AFTER is_active');
    addChatColumnIfMissing($db, 'csr_users', 'role', "ENUM('csr','csr_manager') NOT NULL DEFAULT 'csr' AFTER max_active_chats");

    ensureCsrTeamTablesForChat($db);

    addChatIndexIfMissing($db, 'chat_conversations', 'idx_assigned_csr_id', '(assigned_csr_id)');
    addChatIndexIfMissing($db, 'chat_conversations', 'idx_assigned_manager_id', '(assigned_manager_id)');
    addChatIndexIfMissing($db, 'chat_conversations', 'idx_assigned_team_id', '(assigned_team_id)');
    addChatIndexIfMissing($db, 'chat_conversations', 'idx_assignment_status', '(assignment_status)');
}

function ensureCsrTeamTablesForChat(PDO $db): void {
    $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 getCsrRoleForChat(PDO $db, int $csr_id): string {
    $stmt = $db->prepare("SELECT COALESCE(role, 'csr') FROM csr_users WHERE id = :id LIMIT 1");
    $stmt->execute([':id' => $csr_id]);
    return (string)($stmt->fetchColumn() ?: 'csr');
}

function isCsrManagerForChat(PDO $db, int $csr_id): bool {
    return in_array(getCsrRoleForChat($db, $csr_id), ['team_leader','csr_manager'], true);
}

function getManagedTeamForManager(PDO $db, int $manager_id): ?array {
    ensureCsrTeamTablesForChat($db);
    $stmt = $db->prepare("SELECT * FROM csr_teams WHERE manager_id = :manager_id AND is_active = 1 ORDER BY id LIMIT 1");
    $stmt->execute([':manager_id' => $manager_id]);
    $team = $stmt->fetch(PDO::FETCH_ASSOC);
    return $team ?: null;
}

function getTeamMembersForManager(PDO $db, int $manager_id): array {
    ensureCsrTeamTablesForChat($db);
    $stmt = $db->prepare("SELECT cu.id, CONCAT(cu.first_name, ' ', cu.last_name) AS name, cu.email
                          FROM csr_teams t
                          JOIN csr_team_members tm ON tm.team_id = t.id
                          JOIN csr_users cu ON cu.id = tm.csr_id
                          WHERE t.manager_id = :manager_id
                            AND t.is_active = 1
                            AND cu.is_active = 1
                            AND COALESCE(cu.role, 'csr') = 'csr'
                          ORDER BY cu.first_name, cu.last_name");
    $stmt->execute([':manager_id' => $manager_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function selectNextTeamForChat(PDO $db): ?array {
    ensureCsrTeamTablesForChat($db);
    $stmt = $db->query("SELECT t.*, CONCAT(cu.first_name, ' ', cu.last_name) AS manager_name
                        FROM csr_teams t
                        JOIN csr_users cu ON cu.id = t.manager_id
                        WHERE t.is_active = 1
                          AND cu.is_active = 1
                          AND COALESCE(cu.role, 'csr') = 'team_leader'
                        ORDER BY COALESCE(t.last_assigned_at, '1970-01-01 00:00:00') ASC, t.id ASC
                        LIMIT 1");
    $team = $stmt->fetch(PDO::FETCH_ASSOC);
    return $team ?: null;
}

function assignConversationToManagerTeam(PDO $db, array $conversation, array $request): ?array {
    ensureChatTables($db);
    $team = selectNextTeamForChat($db);
    if (!$team) {
        return null;
    }

    $stmt = $db->prepare("UPDATE chat_conversations
                          SET assigned_team_id = :team_id,
                              assigned_manager_id = :manager_id,
                              assigned_csr_id = NULL,
                              csr_id = NULL,
                              assigned_at = NOW(),
                              first_response_at = NULL,
                              assignment_status = 'manager_queue',
                              returned_to_queue_at = NULL,
                              assignment_note = :note,
                              updated_at = NOW()
                          WHERE id = :conversation_id");
    $stmt->execute([
        ':team_id' => (int)$team['id'],
        ':manager_id' => (int)$team['manager_id'],
        ':note' => 'Queued to Team Leader: ' . ($team['team_name'] ?? 'Team'),
        ':conversation_id' => (int)$conversation['id']
    ]);

    $db->prepare("UPDATE csr_teams SET last_assigned_at = NOW() WHERE id = :id")->execute([':id' => (int)$team['id']]);
    createCsrNotificationForOne($db, (int)$team['manager_id'], (int)$conversation['request_id'], 'New Chat in Team Queue', 'Request #' . $request['request_number'] . ' is waiting for assignment to one of your CSRs.', 'primary');
    addChatMessage($db, (int)$conversation['id'], (int)$conversation['request_id'], 'system', null, 'This chat was routed to ' . ($team['team_name'] ?? 'a CSR Manager team') . ' for assignment.', 'system', true);
    return $team;
}

function assignConversationToTeamCsr(PDO $db, int $request_id, int $manager_id, int $csr_id): array {
    ensureChatTables($db);
    $team = getManagedTeamForManager($db, $manager_id);
    if (!$team) {
        return ['success' => false, 'message' => 'You do not have an active team yet.'];
    }

    $check = $db->prepare("SELECT cu.id, CONCAT(cu.first_name, ' ', cu.last_name) AS name
                           FROM csr_team_members tm
                           JOIN csr_users cu ON cu.id = tm.csr_id
                           WHERE tm.team_id = :team_id
                             AND tm.csr_id = :csr_id
                             AND cu.is_active = 1
                             AND COALESCE(cu.role, 'csr') = 'csr'
                           LIMIT 1");
    $check->execute([':team_id' => (int)$team['id'], ':csr_id' => $csr_id]);
    $member = $check->fetch(PDO::FETCH_ASSOC);
    if (!$member) {
        return ['success' => false, 'message' => 'This CSR is not an active member of your team.'];
    }

    $conversation = getOrCreateConversation($db, $request_id, null);
    if (!$conversation) return ['success' => false, 'message' => 'Conversation not found.'];

    $stmt = $db->prepare("UPDATE chat_conversations
                          SET assigned_team_id = :team_id,
                              assigned_manager_id = :manager_id,
                              assigned_csr_id = :csr_id,
                              csr_id = :csr_id2,
                              assigned_at = NOW(),
                              first_response_at = NULL,
                              assignment_status = 'waiting_first_response',
                              returned_to_queue_at = NULL,
                              assignment_note = :note,
                              updated_at = NOW()
                          WHERE id = :conversation_id");
    $stmt->execute([
        ':team_id' => (int)$team['id'],
        ':manager_id' => $manager_id,
        ':csr_id' => $csr_id,
        ':csr_id2' => $csr_id,
        ':note' => 'Assigned by Team Leader to ' . ($member['name'] ?? ('CSR #' . $csr_id)),
        ':conversation_id' => (int)$conversation['id']
    ]);

    addChatMessage($db, (int)$conversation['id'], $request_id, 'system', null, 'Team Leader assigned this chat to ' . ($member['name'] ?? 'CSR') . '.', 'system', true);
    createCsrNotificationForOne($db, $csr_id, $request_id, 'Chat Assigned by Team Leader', 'A customer chat was assigned to you. Please reply within 5 minutes.', 'primary');

    return ['success' => true, 'message' => 'Chat assigned to ' . ($member['name'] ?? 'CSR') . '.'];
}

function getOrCreateConversation(PDO $db, int $request_id, ?int $preferred_csr_id = null) {
    ensureChatTables($db);

    $stmt = $db->prepare("SELECT cc.*, sr.request_number, sr.customer_id
                          FROM chat_conversations cc
                          JOIN service_requests sr ON sr.id = cc.request_id
                          WHERE cc.request_id = :request_id LIMIT 1");
    $stmt->execute([':request_id' => $request_id]);
    $conversation = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($conversation) {
        return $conversation;
    }

    $stmt = $db->prepare("SELECT id, customer_id, request_number FROM service_requests WHERE id = :request_id LIMIT 1");
    $stmt->execute([':request_id' => $request_id]);
    $request = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$request || empty($request['customer_id'])) {
        return false;
    }

    $stmt = $db->prepare("INSERT INTO chat_conversations (request_id, customer_id, csr_id, assigned_csr_id, assigned_at, assignment_status, created_at, updated_at)
                          VALUES (:request_id, :customer_id, :csr_id, :assigned_csr_id, :assigned_at, :assignment_status, NOW(), NOW())");
    $stmt->execute([
        ':request_id' => $request_id,
        ':customer_id' => $request['customer_id'],
        ':csr_id' => $preferred_csr_id,
        ':assigned_csr_id' => $preferred_csr_id,
        ':assigned_at' => $preferred_csr_id ? date('Y-m-d H:i:s') : null,
        ':assignment_status' => $preferred_csr_id ? 'waiting_first_response' : 'unassigned'
    ]);

    $conversation_id = (int)$db->lastInsertId();
    $botMessage = "Hello 👋\nWelcome to US In Home Services Support.\n\nHow can we help you regarding this request?\n\n• Track Technician\n• Reschedule Appointment\n• Billing Concern\n• Talk to CSR";
    addChatMessage($db, $conversation_id, $request_id, 'bot', null, $botMessage, 'bot', false);

    $stmt = $db->prepare("SELECT cc.*, sr.request_number, sr.customer_id
                          FROM chat_conversations cc
                          JOIN service_requests sr ON sr.id = cc.request_id
                          WHERE cc.id = :id LIMIT 1");
    $stmt->execute([':id' => $conversation_id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

function addChatMessage(PDO $db, int $conversation_id, int $request_id, string $sender_type, $sender_id, string $message, string $message_type = 'chat', bool $touch = true) {
    ensureChatTables($db);
    $stmt = $db->prepare("INSERT INTO chat_messages (conversation_id, request_id, sender_type, sender_id, message, message_type, created_at)
                          VALUES (:conversation_id, :request_id, :sender_type, :sender_id, :message, :message_type, NOW())");
    $stmt->execute([
        ':conversation_id' => $conversation_id,
        ':request_id' => $request_id,
        ':sender_type' => $sender_type,
        ':sender_id' => $sender_id,
        ':message' => $message,
        ':message_type' => $message_type
    ]);
    if ($touch) {
        $stmt = $db->prepare("UPDATE chat_conversations SET updated_at = NOW() WHERE id = :id");
        $stmt->execute([':id' => $conversation_id]);
    }
    return (int)$db->lastInsertId();
}

function getCsrName(PDO $db, ?int $csr_id): string {
    if (!$csr_id) return '';
    $stmt = $db->prepare("SELECT CONCAT(first_name, ' ', last_name) AS name FROM csr_users WHERE id = :id LIMIT 1");
    $stmt->execute([':id' => $csr_id]);
    return (string)($stmt->fetchColumn() ?: 'CSR #' . $csr_id);
}

function countActiveChatsForCsr(PDO $db, int $csr_id): int {
    ensureChatTables($db);
    $stmt = $db->prepare("SELECT COUNT(*) FROM chat_conversations WHERE assigned_csr_id = :csr_id AND assignment_status IN ('waiting_first_response','active') AND status = 'open'");
    $stmt->execute([':csr_id' => $csr_id]);
    return (int)$stmt->fetchColumn();
}

function getRandomAvailableCsr(PDO $db): ?array {
    ensureChatTables($db);
    $stmt = $db->query("SELECT cu.id, CONCAT(cu.first_name, ' ', cu.last_name) AS name, COALESCE(cu.max_active_chats, 5) AS max_active_chats,
                               (SELECT COUNT(*) FROM chat_conversations cc WHERE cc.assigned_csr_id = cu.id AND cc.assignment_status IN ('waiting_first_response','active') AND cc.status = 'open') AS active_chats
                        FROM csr_users cu
                        WHERE cu.is_active = 1
                        HAVING active_chats < max_active_chats
                        ORDER BY RAND()
                        LIMIT 1");
    $csr = $stmt->fetch(PDO::FETCH_ASSOC);
    return $csr ?: null;
}

function createCsrNotificationForOne(PDO $db, int $csr_id, int $request_id, string $title, string $message, string $type = 'primary') {
    $stmt = $db->prepare("INSERT INTO csr_notifications (csr_id, title, message, type, link, reference_id, reference_type, created_at)
                          VALUES (:csr_id, :title, :message, :type, :link, :request_id, 'chat', NOW())");
    return $stmt->execute([
        ':csr_id' => $csr_id,
        ':title' => $title,
        ':message' => $message,
        ':type' => $type,
        ':link' => 'messages.php?request_id=' . $request_id,
        ':request_id' => $request_id
    ]);
}

function createCsrNotificationForAll(PDO $db, int $request_id, string $title, string $message, string $type = 'primary') {
    $stmt = $db->prepare("INSERT INTO csr_notifications (csr_id, title, message, type, link, reference_id, reference_type, created_at)
                          SELECT id, :title, :message, :type, :link, :request_id, 'chat', NOW()
                          FROM csr_users WHERE is_active = 1");
    return $stmt->execute([
        ':title' => $title,
        ':message' => $message,
        ':type' => $type,
        ':link' => 'messages.php?request_id=' . $request_id,
        ':request_id' => $request_id
    ]);
}

function assignConversationToCsr(PDO $db, int $conversation_id, int $request_id, int $csr_id, string $status = 'waiting_first_response', string $note = 'Auto-assigned to CSR'): bool {
    ensureChatTables($db);

    $stmt = $db->prepare("UPDATE chat_conversations
                          SET assigned_csr_id = :assigned_csr_id,
                              csr_id = :csr_id,
                              assigned_at = NOW(),
                              first_response_at = CASE WHEN :first_response_status = 'active' THEN NOW() ELSE NULL END,
                              assignment_status = :assignment_status,
                              returned_to_queue_at = NULL,
                              assignment_note = :note,
                              updated_at = NOW()
                          WHERE id = :conversation_id");
    return $stmt->execute([
        ':assigned_csr_id' => $csr_id,
        ':csr_id' => $csr_id,
        ':first_response_status' => $status,
        ':assignment_status' => $status,
        ':note' => $note,
        ':conversation_id' => $conversation_id
    ]);
}

function assignConversationRandomly(PDO $db, array $conversation, array $request): ?array {
    // New flow: customer chats go to Team Leaders first. If no active team exists,
    // keep the old random CSR fallback so the system never stops receiving chats.
    $team = assignConversationToManagerTeam($db, $conversation, $request);
    if ($team) {
        return ['id' => null, 'team_id' => (int)$team['id'], 'manager_id' => (int)$team['manager_id'], 'name' => $team['manager_name'] ?? 'CSR Manager'];
    }

    $csr = getRandomAvailableCsr($db);
    if (!$csr) {
        $db->prepare("UPDATE chat_conversations SET assignment_status = 'unassigned', assigned_csr_id = NULL, csr_id = NULL, assigned_at = NULL, first_response_at = NULL, assignment_note = 'No active CSR Manager team or available CSR', updated_at = NOW() WHERE id = :id")
           ->execute([':id' => $conversation['id']]);
        createCsrNotificationForAll($db, (int)$conversation['request_id'], 'Customer Message Waiting', 'Customer sent a message for request #' . $request['request_number'] . '. No CSR Manager team was available.', 'warning');
        return null;
    }

    assignConversationToCsr($db, (int)$conversation['id'], (int)$conversation['request_id'], (int)$csr['id'], 'waiting_first_response', 'Fallback random auto-assignment');
    createCsrNotificationForOne($db, (int)$csr['id'], (int)$conversation['request_id'], 'Chat Assigned to You', 'Customer sent a message for request #' . $request['request_number'] . '. Please respond within 5 minutes.', 'primary');
    return $csr;
}

function releaseOverdueChatAssignments(PDO $db, int $timeoutMinutes = 5): int {
    ensureChatTables($db);
    $stmt = $db->prepare("SELECT cc.id, cc.request_id, cc.assigned_csr_id, cc.assigned_team_id, cc.assigned_manager_id, sr.request_number
                          FROM chat_conversations cc
                          JOIN service_requests sr ON sr.id = cc.request_id
                          WHERE cc.status = 'open'
                            AND cc.assignment_status = 'waiting_first_response'
                            AND cc.assigned_csr_id IS NOT NULL
                            AND cc.first_response_at IS NULL
                            AND cc.assigned_at IS NOT NULL
                            AND cc.assigned_at <= DATE_SUB(NOW(), INTERVAL :timeout MINUTE)");
    $stmt->bindValue(':timeout', $timeoutMinutes, PDO::PARAM_INT);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rows as $row) {
        $csrName = getCsrName($db, (int)$row['assigned_csr_id']);
        $managerId = !empty($row['assigned_manager_id']) ? (int)$row['assigned_manager_id'] : 0;
        $teamId = !empty($row['assigned_team_id']) ? (int)$row['assigned_team_id'] : 0;

        if ($managerId && $teamId) {
            $db->prepare("UPDATE chat_conversations
                          SET assigned_csr_id = NULL,
                              csr_id = NULL,
                              assignment_status = 'manager_queue',
                              returned_to_queue_at = NOW(),
                              assignment_note = 'Returned to Team Leader queue after no CSR reply',
                              updated_at = NOW()
                          WHERE id = :id")
               ->execute([':id' => $row['id']]);

            $msg = "This chat returned to the Team Leader queue because {$csrName} did not reply within {$timeoutMinutes} minutes.";
            addChatMessage($db, (int)$row['id'], (int)$row['request_id'], 'system', null, $msg, 'system', true);
            createCsrNotificationForOne($db, $managerId, (int)$row['request_id'], 'Chat Returned to Team Queue', 'Request #' . $row['request_number'] . ' needs reassignment. ' . $csrName . ' did not reply within ' . $timeoutMinutes . ' minutes.', 'warning');
        } else {
            $db->prepare("UPDATE chat_conversations
                          SET assigned_csr_id = NULL,
                              csr_id = NULL,
                              assignment_status = 'returned_queue',
                              returned_to_queue_at = NOW(),
                              assignment_note = 'Returned to queue after no CSR reply',
                              updated_at = NOW()
                          WHERE id = :id")
               ->execute([':id' => $row['id']]);

            $msg = "This chat returned to queue because {$csrName} did not reply within {$timeoutMinutes} minutes.";
            addChatMessage($db, (int)$row['id'], (int)$row['request_id'], 'system', null, $msg, 'system', true);
            createCsrNotificationForAll($db, (int)$row['request_id'], 'Chat Returned to Queue', 'Request #' . $row['request_number'] . ' is waiting. First CSR to handle it will lock the chat.', 'warning');
        }
    }

    return count($rows);
}

function handleCustomerChatMessageAssignment(PDO $db, array $conversation, array $request): array {
    ensureChatTables($db);
    releaseOverdueChatAssignments($db, 5);

    $stmt = $db->prepare("SELECT * FROM chat_conversations WHERE id = :id LIMIT 1");
    $stmt->execute([':id' => $conversation['id']]);
    $conversation = $stmt->fetch(PDO::FETCH_ASSOC) ?: $conversation;

    $db->prepare("UPDATE chat_conversations SET last_customer_message_at = NOW(), updated_at = NOW() WHERE id = :id")
       ->execute([':id' => $conversation['id']]);

    $status = $conversation['assignment_status'] ?? 'unassigned';
    $assignedCsrId = !empty($conversation['assigned_csr_id']) ? (int)$conversation['assigned_csr_id'] : 0;
    $assignedManagerId = !empty($conversation['assigned_manager_id']) ? (int)$conversation['assigned_manager_id'] : 0;

    if ($assignedCsrId && in_array($status, ['waiting_first_response', 'active'], true)) {
        createCsrNotificationForOne($db, $assignedCsrId, (int)$conversation['request_id'], 'New Customer Message', 'Customer sent a message for request #' . $request['request_number'], 'primary');
        return ['assigned' => true, 'csr_id' => $assignedCsrId, 'status' => $status];
    }

    if ($assignedManagerId && $status === 'manager_queue') {
        createCsrNotificationForOne($db, $assignedManagerId, (int)$conversation['request_id'], 'Customer Message Waiting', 'Request #' . $request['request_number'] . ' is still waiting in your Team Leader queue.', 'warning');
        return ['assigned' => true, 'manager_id' => $assignedManagerId, 'status' => 'manager_queue'];
    }

    $assigned = assignConversationRandomly($db, $conversation, $request);
    return [
        'assigned' => (bool)$assigned,
        'csr_id' => isset($assigned['id']) ? (int)$assigned['id'] : null,
        'manager_id' => isset($assigned['manager_id']) ? (int)$assigned['manager_id'] : null,
        'team_id' => isset($assigned['team_id']) ? (int)$assigned['team_id'] : null,
        'status' => $assigned ? (isset($assigned['team_id']) ? 'manager_queue' : 'waiting_first_response') : 'unassigned'
    ];
}

function claimChatConversation(PDO $db, int $request_id, int $csr_id): array {
    ensureChatTables($db);
    releaseOverdueChatAssignments($db, 5);
    $conversation = getOrCreateConversation($db, $request_id, null);
    if (!$conversation) return ['success' => false, 'message' => 'Conversation not found.'];

    $stmt = $db->prepare("SELECT cc.*, CONCAT(cu.first_name, ' ', cu.last_name) AS assigned_csr_name, t.team_name, t.manager_id
                          FROM chat_conversations cc
                          LEFT JOIN csr_users cu ON cu.id = cc.assigned_csr_id
                          LEFT JOIN csr_teams t ON t.id = cc.assigned_team_id
                          WHERE cc.id = :id LIMIT 1");
    $stmt->execute([':id' => $conversation['id']]);
    $conversation = $stmt->fetch(PDO::FETCH_ASSOC);

    $assignedCsrId = !empty($conversation['assigned_csr_id']) ? (int)$conversation['assigned_csr_id'] : 0;
    $status = $conversation['assignment_status'] ?? 'unassigned';
    $role = getCsrRoleForChat($db, $csr_id);

    if ($assignedCsrId && $assignedCsrId !== $csr_id && in_array($status, ['waiting_first_response', 'active'], true)) {
        return ['success' => false, 'message' => 'This chat is currently handled by ' . ($conversation['assigned_csr_name'] ?: 'another CSR') . '.'];
    }

    if ($status === 'manager_queue') {
        if (in_array($role, ['team_leader','csr_manager'], true) && (int)($conversation['manager_id'] ?? $conversation['assigned_manager_id'] ?? 0) === $csr_id) {
            assignConversationToCsr($db, (int)$conversation['id'], $request_id, $csr_id, 'active', 'Team Leader handled directly');
            return ['success' => true, 'message' => 'Chat is now locked to you.'];
        }
        return ['success' => false, 'message' => 'This chat is waiting for the Team Leader to assign it.'];
    }

    assignConversationToCsr($db, (int)$conversation['id'], $request_id, $csr_id, 'active', 'Manually claimed by CSR');
    return ['success' => true, 'message' => 'Chat is now locked to you.'];
}

function getChatAssignmentInfo(PDO $db, int $request_id, ?int $current_csr_id = null): array {
    ensureChatTables($db);
    releaseOverdueChatAssignments($db, 5);
    $stmt = $db->prepare("SELECT cc.*, CONCAT(cu.first_name, ' ', cu.last_name) AS assigned_csr_name,
                                 t.team_name, t.manager_id, CONCAT(mgr.first_name, ' ', mgr.last_name) AS manager_name
                          FROM chat_conversations cc
                          LEFT JOIN csr_users cu ON cu.id = cc.assigned_csr_id
                          LEFT JOIN csr_teams t ON t.id = cc.assigned_team_id
                          LEFT JOIN csr_users mgr ON mgr.id = COALESCE(cc.assigned_manager_id, t.manager_id)
                          WHERE cc.request_id = :request_id LIMIT 1");
    $stmt->execute([':request_id' => $request_id]);
    $c = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$c) {
        return ['assignment_status' => 'unassigned', 'assigned_csr_id' => null, 'assigned_csr_name' => null, 'can_reply' => false, 'label' => 'Unassigned'];
    }

    $assignedId = !empty($c['assigned_csr_id']) ? (int)$c['assigned_csr_id'] : null;
    $managerId = !empty($c['assigned_manager_id']) ? (int)$c['assigned_manager_id'] : (!empty($c['manager_id']) ? (int)$c['manager_id'] : null);
    $teamId = !empty($c['assigned_team_id']) ? (int)$c['assigned_team_id'] : null;
    $status = $c['assignment_status'] ?: 'unassigned';
    $role = $current_csr_id ? getCsrRoleForChat($db, (int)$current_csr_id) : 'csr';
    $isMine = $current_csr_id && $assignedId === (int)$current_csr_id;
    $isTeamManager = $current_csr_id && $managerId === (int)$current_csr_id && in_array($role, ['team_leader','csr_manager'], true);
    $canManageTeam = $isTeamManager && $status === 'manager_queue' && !$assignedId;
    $canReply = $isMine || (!$assignedId && in_array($status, ['unassigned', 'returned_queue'], true));

    if ($status === 'manager_queue') {
        if ($isTeamManager) {
            $label = 'Team Queue - assign to one of your CSRs';
        } else {
            $label = 'Waiting for Team Leader' . (!empty($c['manager_name']) ? ' - ' . $c['manager_name'] : '');
        }
    } elseif ($isMine && $status === 'waiting_first_response') {
        $label = 'Assigned to You - Waiting for First Response';
    } elseif ($isMine) {
        $label = 'Assigned to You';
    } elseif ($assignedId && $status === 'waiting_first_response') {
        $label = 'Waiting for First Response - Handled by ' . ($c['assigned_csr_name'] ?: 'CSR #' . $assignedId);
    } elseif ($assignedId) {
        $label = 'Handled by ' . ($c['assigned_csr_name'] ?: 'CSR #' . $assignedId);
    } elseif ($status === 'returned_queue') {
        $label = 'Returned to Queue - First to Handle';
    } else {
        $label = 'Unassigned - First to Handle';
    }

    return [
        'conversation_id' => (int)$c['id'],
        'assignment_status' => $status,
        'assigned_csr_id' => $assignedId,
        'assigned_csr_name' => $c['assigned_csr_name'] ?? null,
        'assigned_manager_id' => $managerId,
        'assigned_manager_name' => $c['manager_name'] ?? null,
        'assigned_team_id' => $teamId,
        'assigned_team_name' => $c['team_name'] ?? null,
        'assigned_at' => $c['assigned_at'] ?? null,
        'first_response_at' => $c['first_response_at'] ?? null,
        'can_reply' => $canReply,
        'can_manage_team' => $canManageTeam,
        'is_mine' => $isMine,
        'is_team_manager' => $isTeamManager,
        'label' => $label
    ];
}

function addSystemChatMessageForRequest(PDO $db, int $request_id, string $message, string $message_type = 'system') {
    $conversation = getOrCreateConversation($db, $request_id);
    if (!$conversation) return false;
    return addChatMessage($db, (int)$conversation['id'], $request_id, 'system', null, $message, $message_type);
}

function createCustomerNotification(PDO $db, int $request_id, string $title, string $message, string $type = 'info') {
    $stmt = $db->prepare("SELECT customer_id FROM service_requests WHERE id = :request_id LIMIT 1");
    $stmt->execute([':request_id' => $request_id]);
    $request = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$request || empty($request['customer_id'])) return false;

    $link = 'messages.php?request_id=' . $request_id;
    $stmt = $db->prepare("INSERT INTO notifications (customer_id, title, message, type, link, is_read, created_at)
                          VALUES (:customer_id, :title, :message, :type, :link, 0, NOW())");
    return $stmt->execute([
        ':customer_id' => $request['customer_id'],
        ':title' => $title,
        ':message' => $message,
        ':type' => $type,
        ':link' => $link
    ]);
}

function formatEstimatedVisit($datetime, $period = null) {
    if (!$datetime) return 'To be confirmed';
    $ts = strtotime($datetime);
    if (!$ts) return 'To be confirmed';

    $period = strtoupper(trim((string)$period));
    if ($period === 'AM' || $period === 'MORNING (AM)' || $period === 'MORNING') {
        $label = 'Morning (AM)';
    } elseif ($period === 'PM' || $period === 'AFTERNOON (PM)' || $period === 'AFTERNOON') {
        $label = 'Afternoon (PM)';
    } elseif ($period === 'EVENING (PM)' || $period === 'EVENING') {
        $label = 'Evening (PM)';
    } else {
        $hour = (int)date('H', $ts);
        $label = ($hour < 12) ? 'Morning (AM)' : (($hour < 17) ? 'Afternoon (PM)' : 'Evening (PM)');
    }

    return date('M d, Y', $ts) . ' - ' . $label;
}

function sendDueAppointmentReminders(PDO $db) {
    ensureChatTables($db);
    $db->exec("CREATE TABLE IF NOT EXISTS appointment_reminder_log (
        id INT AUTO_INCREMENT PRIMARY KEY,
        request_id INT NOT NULL,
        reminder_key VARCHAR(100) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY unique_reminder (request_id, reminder_key)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $query = "SELECT sr.id, sr.request_number, sr.customer_id, sr.preferred_date, sr.preferred_time
              FROM service_requests sr
              WHERE sr.customer_id IS NOT NULL
                AND sr.job_status_id IN (1,2,3)
                AND sr.preferred_date IS NOT NULL
                AND sr.preferred_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 DAY)";
    foreach ($db->query($query)->fetchAll(PDO::FETCH_ASSOC) as $request) {
        $key = 'preferred_' . $request['preferred_date'];
        try {
            $stmt = $db->prepare("INSERT INTO appointment_reminder_log (request_id, reminder_key) VALUES (:request_id, :reminder_key)");
            $stmt->execute([':request_id' => $request['id'], ':reminder_key' => $key]);
            $timeText = 'your selected time slot';
            if (!empty($request['preferred_time'])) {
                $hour = (int)date('H', strtotime($request['preferred_time']));
                $timeText = ($hour < 12) ? 'Morning (AM)' : (($hour < 17) ? 'Afternoon (PM)' : 'Evening (PM)');
            }
            $msg = "Appointment reminder for request #{$request['request_number']}: your preferred visit date is " . date('M d, Y', strtotime($request['preferred_date'])) . " - {$timeText}.";
            addSystemChatMessageForRequest($db, (int)$request['id'], $msg, 'reminder');
            createCustomerNotification($db, (int)$request['id'], 'Appointment Reminder', $msg, 'info');
        } catch (Exception $e) {
            // already sent; ignore
        }
    }
}
?>
