<?php
// includes/csr_report_helpers.php
// Shared role/scope and report calculations for CSR reports.

require_once __DIR__ . '/app_helpers.php';

function ensureCsrRoleSchema(PDO $db): void {
    try {
        $db->exec("ALTER TABLE csr_users MODIFY role ENUM('csr','team_leader','csr_manager') NOT NULL DEFAULT 'csr'");
    } catch (Exception $e) {
        // Some hosts may block ALTER in runtime. The SQL migration included with this update performs the same change.
    }

    // Existing Team Leaders used to be stored as csr_manager because there were only two roles before.
    try {
        $db->exec("UPDATE csr_users cu
                  SET cu.role = 'team_leader'
                  WHERE cu.role = 'csr_manager'
                    AND EXISTS (SELECT 1 FROM csr_teams ct WHERE ct.manager_id = cu.id)
                    AND NOT EXISTS (
                        SELECT 1 FROM csr_users cm
                        WHERE cm.role = 'csr_manager'
                          AND cm.id <> cu.id
                    )");
    } catch (Exception $e) {}
}

function getCsrRoleLabelForReport(string $role): string {
    if ($role === 'csr_manager') return 'CSR Manager';
    if ($role === 'team_leader') return 'Team Leader';
    return 'CSR';
}

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

function getCsrReportTeamOptions(PDO $db, string $role, int $viewerId): array {
    if ($role === 'csr') return [];

    if ($role === 'team_leader') {
        $stmt = $db->prepare("SELECT id, team_name FROM csr_teams WHERE is_active = 1 AND manager_id = :viewer_id ORDER BY team_name");
        $stmt->execute([':viewer_id' => $viewerId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    $stmt = $db->query("SELECT id, team_name FROM csr_teams WHERE is_active = 1 ORDER BY team_name");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getCsrIdsForTeam(PDO $db, int $teamId, bool $includeLeader = true): array {
    $ids = [];
    if ($includeLeader) {
        $stmt = $db->prepare("SELECT manager_id FROM csr_teams WHERE id = :team_id AND is_active = 1 LIMIT 1");
        $stmt->execute([':team_id' => $teamId]);
        $managerId = (int)($stmt->fetchColumn() ?: 0);
        if ($managerId > 0) $ids[] = $managerId;
    }

    $stmt = $db->prepare("SELECT csr_id FROM csr_team_members WHERE team_id = :team_id");
    $stmt->execute([':team_id' => $teamId]);
    foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $id) {
        $id = (int)$id;
        if ($id > 0) $ids[] = $id;
    }
    return array_values(array_unique($ids));
}

function getAllOperationalCsrIds(PDO $db): array {
    $stmt = $db->query("SELECT id FROM csr_users WHERE is_active = 1 AND COALESCE(role, 'csr') IN ('csr','team_leader') ORDER BY id");
    return array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));
}

function getCsrReportAccessContext(PDO $db, int $viewerId, int $requestedTeamId = 0, int $requestedCsrId = 0): array {
    ensureCsrTeamTables($db);
    ensureCsrRoleSchema($db);

    $role = getCsrRoleForReport($db, $viewerId);
    $teamOptions = getCsrReportTeamOptions($db, $role, $viewerId);
    $teamIdsAllowed = array_map('intval', array_column($teamOptions, 'id'));
    $selectedTeamId = 0;
    $allowedIds = [];

    if ($role === 'csr') {
        $allowedIds = [$viewerId];
    } elseif ($role === 'team_leader') {
        if ($requestedTeamId > 0 && in_array($requestedTeamId, $teamIdsAllowed, true)) {
            $selectedTeamId = $requestedTeamId;
        } elseif (!empty($teamIdsAllowed)) {
            $selectedTeamId = (int)$teamIdsAllowed[0];
        }
        $allowedIds = $selectedTeamId > 0 ? getCsrIdsForTeam($db, $selectedTeamId, true) : [$viewerId];
        if (!in_array($viewerId, $allowedIds, true)) $allowedIds[] = $viewerId;
    } else { // csr_manager
        if ($requestedTeamId > 0 && in_array($requestedTeamId, $teamIdsAllowed, true)) {
            $selectedTeamId = $requestedTeamId;
            $allowedIds = getCsrIdsForTeam($db, $selectedTeamId, true);
        } else {
            $allowedIds = getAllOperationalCsrIds($db);
        }
    }

    $allowedIds = array_values(array_unique(array_filter(array_map('intval', $allowedIds))));
    if ($requestedCsrId > 0 && in_array($requestedCsrId, $allowedIds, true)) {
        $allowedIds = [$requestedCsrId];
    }
    if (!$allowedIds) $allowedIds = [$viewerId];

    return [
        'role' => $role,
        'role_label' => getCsrRoleLabelForReport($role),
        'can_download' => in_array($role, ['team_leader', 'csr_manager'], true),
        'can_filter_team' => in_array($role, ['team_leader', 'csr_manager'], true),
        'can_filter_csr' => in_array($role, ['team_leader', 'csr_manager'], true),
        'selected_team_id' => $selectedTeamId,
        'team_options' => $teamOptions,
        'allowed_csr_ids' => $allowedIds,
        'requested_csr_id' => $requestedCsrId
    ];
}

function getCsrReportSelectableCsrs(PDO $db, array $allowedIds): array {
    $allowedIds = array_values(array_unique(array_filter(array_map('intval', $allowedIds))));
    if (!$allowedIds) return [];
    $placeholders = [];
    $params = [];
    foreach ($allowedIds as $idx => $id) {
        $ph = ':csr_select_' . $idx;
        $placeholders[] = $ph;
        $params[$ph] = $id;
    }
    $stmt = $db->prepare("SELECT id, CONCAT(first_name, ' ', last_name) AS name, email, COALESCE(role, 'csr') AS role
                          FROM csr_users
                          WHERE id IN (" . implode(',', $placeholders) . ")
                          ORDER BY FIELD(id, " . implode(',', array_map('intval', $allowedIds)) . ")");
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function normalizeCsrReportDates(string $startDate = '', string $endDate = ''): array {
    if ($startDate !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $startDate)) $startDate = '';
    if ($endDate !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $endDate)) $endDate = '';
    if ($startDate !== '' && $endDate !== '' && strtotime($startDate) > strtotime($endDate)) {
        $tmp = $startDate; $startDate = $endDate; $endDate = $tmp;
    }
    return [$startDate, $endDate];
}

function buildCsrIdInCondition(array $csrIds, string $column, string $prefix, array &$params): string {
    $placeholders = [];
    foreach (array_values($csrIds) as $idx => $csrId) {
        $ph = ':' . $prefix . '_csr_' . $idx;
        $placeholders[] = $ph;
        $params[$ph] = (int)$csrId;
    }
    if (!$placeholders) return ' AND 1=0 ';
    return " AND {$column} IN (" . implode(',', $placeholders) . ") ";
}

function buildReportDateConditionForReport(string $column, string $prefix, array &$params, string $startDate, string $endDate): string {
    $condition = '';
    if ($startDate !== '') {
        $condition .= " AND DATE($column) >= :{$prefix}_start_date";
        $params[":{$prefix}_start_date"] = $startDate;
    }
    if ($endDate !== '') {
        $condition .= " AND DATE($column) <= :{$prefix}_end_date";
        $params[":{$prefix}_end_date"] = $endDate;
    }
    return $condition;
}

function calculateCsrReportStatsForScope(PDO $db, array $csrIds, string $startDate = '', string $endDate = ''): array {
    [$startDate, $endDate] = normalizeCsrReportDates($startDate, $endDate);
    $stats = [
        'total_tickets_handled' => 0,
        'total_technician_assigned' => 0,
        'total_in_progress' => 0,
        'total_completed' => 0,
        'total_canceled' => 0,
        'filter_start_date' => $startDate,
        'filter_end_date' => $endDate
    ];

    $params = [];
    $locksIds = buildCsrIdInCondition($csrIds, 'csr_id', 'handled_locks', $params);
    $assignmentIds = buildCsrIdInCondition($csrIds, 'assigned_by', 'handled_assignments', $params);
    $activityIds = buildCsrIdInCondition($csrIds, 'csr_id', 'handled_activity', $params);
    $logIds = buildCsrIdInCondition($csrIds, 'user_id', 'handled_logs', $params);
    $locksDate = buildReportDateConditionForReport('locked_at', 'handled_locks', $params, $startDate, $endDate);
    $assignmentDate = buildReportDateConditionForReport('assigned_at', 'handled_assignments', $params, $startDate, $endDate);
    $activityDate = buildReportDateConditionForReport('created_at', 'handled_activity', $params, $startDate, $endDate);
    $logDate = buildReportDateConditionForReport('created_at', 'handled_logs', $params, $startDate, $endDate);
    $stmt = $db->prepare("SELECT COUNT(DISTINCT handled.request_id) FROM (
            SELECT request_id FROM csr_request_locks WHERE 1=1 {$locksIds} {$locksDate}
            UNION
            SELECT request_id FROM service_assignments WHERE 1=1 {$assignmentIds} {$assignmentDate}
            UNION
            SELECT request_id FROM csr_activity WHERE request_id IS NOT NULL {$activityIds} {$activityDate}
            UNION
            SELECT request_id FROM request_logs WHERE user_role IN ('csr','team_leader','csr_manager') AND request_id IS NOT NULL {$logIds} {$logDate}
        ) handled");
    $stmt->execute($params);
    $stats['total_tickets_handled'] = (int)$stmt->fetchColumn();

    $params = [];
    $assignedIds = buildCsrIdInCondition($csrIds, 'assigned_by', 'tech_assigned', $params);
    $assignedDate = buildReportDateConditionForReport('assigned_at', 'tech_assigned', $params, $startDate, $endDate);
    $stmt = $db->prepare("SELECT COUNT(DISTINCT request_id) FROM service_assignments WHERE 1=1 {$assignedIds} {$assignedDate}");
    $stmt->execute($params);
    $stats['total_technician_assigned'] = (int)$stmt->fetchColumn();

    $params = [];
    $locksIds = buildCsrIdInCondition($csrIds, 'csr_id', 'inprogress_locks', $params);
    $assignmentIds = buildCsrIdInCondition($csrIds, 'assigned_by', 'inprogress_assignments', $params);
    $activityIds = buildCsrIdInCondition($csrIds, 'csr_id', 'inprogress_activity', $params);
    $logIds = buildCsrIdInCondition($csrIds, 'user_id', 'inprogress_logs', $params);
    $locksDate = buildReportDateConditionForReport('locked_at', 'inprogress_locks', $params, $startDate, $endDate);
    $assignmentDate = buildReportDateConditionForReport('assigned_at', 'inprogress_assignments', $params, $startDate, $endDate);
    $activityDate = buildReportDateConditionForReport('created_at', 'inprogress_activity', $params, $startDate, $endDate);
    $logDate = buildReportDateConditionForReport('created_at', 'inprogress_logs', $params, $startDate, $endDate);
    $stmt = $db->prepare("SELECT COUNT(DISTINCT sr.id)
        FROM service_requests sr
        WHERE sr.job_status_id = 3
        AND sr.id IN (
            SELECT request_id FROM csr_request_locks WHERE 1=1 {$locksIds} {$locksDate}
            UNION
            SELECT request_id FROM service_assignments WHERE 1=1 {$assignmentIds} {$assignmentDate}
            UNION
            SELECT request_id FROM csr_activity WHERE request_id IS NOT NULL {$activityIds} {$activityDate}
            UNION
            SELECT request_id FROM request_logs WHERE user_role IN ('csr','team_leader','csr_manager') AND request_id IS NOT NULL {$logIds} {$logDate}
        )");
    $stmt->execute($params);
    $stats['total_in_progress'] = (int)$stmt->fetchColumn();

    $params = [];
    $logIds = buildCsrIdInCondition($csrIds, 'user_id', 'completed_logs', $params);
    $activityIds = buildCsrIdInCondition($csrIds, 'csr_id', 'completed_activity', $params);
    $notesIds = buildCsrIdInCondition($csrIds, 'csr_id', 'completed_notes', $params);
    $logDate = buildReportDateConditionForReport('created_at', 'completed_logs', $params, $startDate, $endDate);
    $activityDate = buildReportDateConditionForReport('created_at', 'completed_activity', $params, $startDate, $endDate);
    $notesDate = buildReportDateConditionForReport('created_at', 'completed_notes', $params, $startDate, $endDate);
    $stmt = $db->prepare("SELECT COUNT(DISTINCT completed.request_id) FROM (
            SELECT request_id FROM request_logs WHERE user_role IN ('csr','team_leader','csr_manager') {$logIds} AND action = 'status_change' AND CAST(new_value AS CHAR) = '4' {$logDate}
            UNION
            SELECT request_id FROM csr_activity WHERE action = 'status_change' AND details LIKE '%to 4%' {$activityIds} {$activityDate}
            UNION
            SELECT request_id FROM request_notes WHERE note_content LIKE '%Status updated to:%Completed%' {$notesIds} {$notesDate}
        ) completed");
    $stmt->execute($params);
    $stats['total_completed'] = (int)$stmt->fetchColumn();

    $params = [];
    $logIds = buildCsrIdInCondition($csrIds, 'user_id', 'canceled_logs', $params);
    $activityIds = buildCsrIdInCondition($csrIds, 'csr_id', 'canceled_activity', $params);
    $notesIds = buildCsrIdInCondition($csrIds, 'csr_id', 'canceled_notes', $params);
    $logDate = buildReportDateConditionForReport('created_at', 'canceled_logs', $params, $startDate, $endDate);
    $activityDate = buildReportDateConditionForReport('created_at', 'canceled_activity', $params, $startDate, $endDate);
    $notesDate = buildReportDateConditionForReport('created_at', 'canceled_notes', $params, $startDate, $endDate);
    $stmt = $db->prepare("SELECT COUNT(DISTINCT canceled.request_id) FROM (
            SELECT request_id FROM request_logs WHERE user_role IN ('csr','team_leader','csr_manager') {$logIds} AND action = 'status_change' AND CAST(new_value AS CHAR) = '5' {$logDate}
            UNION
            SELECT request_id FROM csr_activity WHERE action = 'status_change' AND details LIKE '%to 5%' {$activityIds} {$activityDate}
            UNION
            SELECT request_id FROM request_notes WHERE note_content LIKE '%Status updated to:%Cancel%' {$notesIds} {$notesDate}
        ) canceled");
    $stmt->execute($params);
    $stats['total_canceled'] = (int)$stmt->fetchColumn();

    return $stats;
}

function getCsrReportScopeLabel(array $context): string {
    if ($context['role'] === 'csr') return 'My report only';
    if ($context['role'] === 'team_leader') return 'Team Leader scope';
    return $context['selected_team_id'] > 0 ? 'CSR Manager team filter' : 'CSR Manager all teams';
}
?>
