<?php
// csr/ajax/get_report_ticket_list.php
require_once '../../config/database.php';
session_start();
header('Content-Type: application/json');

if (!isset($_SESSION['csr_id'])) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit();
}

try {
    $database = new Database();
    $db = $database->getConnection();
    require_once '../../includes/app_helpers.php';
    require_once '../../includes/csr_report_helpers.php';
    ensureUnifiedTicketWorkflow($db);

    $csrId = (int)$_SESSION['csr_id'];
    $type = isset($_GET['type']) ? trim($_GET['type']) : 'handled';
    $scope = isset($_GET['scope']) ? trim($_GET['scope']) : 'range';
    $startDate = isset($_GET['start_date']) ? trim($_GET['start_date']) : '';
    $endDate = isset($_GET['end_date']) ? trim($_GET['end_date']) : '';
    $requestedTeamId = isset($_GET['team_id']) ? (int)$_GET['team_id'] : 0;
    $requestedCsrId = isset($_GET['csr_id']) ? (int)$_GET['csr_id'] : 0;

    $allowedTypes = ['handled', 'assigned', 'in_progress', 'completed', 'canceled'];
    if (!in_array($type, $allowedTypes, true)) $type = 'handled';
    if ($scope === 'today') { $startDate = date('Y-m-d'); $endDate = date('Y-m-d'); }
    [$startDate, $endDate] = normalizeCsrReportDates($startDate, $endDate);

    $context = getCsrReportAccessContext($db, $csrId, $requestedTeamId, $requestedCsrId);
    $csrIds = $context['allowed_csr_ids'];

    $params = [];
    $handledSubquery = '';

    if ($type === 'assigned') {
        $ids = buildCsrIdInCondition($csrIds, 'assigned_by', 'assigned', $params);
        $date = buildReportDateConditionForReport('assigned_at', 'assigned', $params, $startDate, $endDate);
        $handledSubquery = "SELECT request_id, MAX(assigned_at) AS activity_date
                            FROM service_assignments
                            WHERE 1=1 {$ids} {$date}
                            GROUP BY request_id";
    } elseif ($type === 'completed') {
        $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);
        $handledSubquery = "SELECT request_id, MAX(activity_date) AS activity_date FROM (
                SELECT request_id, created_at AS activity_date 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 ALL
                SELECT request_id, created_at AS activity_date FROM csr_activity
                WHERE action = 'status_change' AND details LIKE '%to 4%' {$activityIds} {$activityDate}
                UNION ALL
                SELECT request_id, created_at AS activity_date FROM request_notes
                WHERE note_content LIKE '%Status updated to:%Completed%' {$notesIds} {$notesDate}
            ) completed_actions GROUP BY request_id";
    } elseif ($type === 'canceled') {
        $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);
        $handledSubquery = "SELECT request_id, MAX(activity_date) AS activity_date FROM (
                SELECT request_id, created_at AS activity_date 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 ALL
                SELECT request_id, created_at AS activity_date FROM csr_activity
                WHERE action = 'status_change' AND details LIKE '%to 5%' {$activityIds} {$activityDate}
                UNION ALL
                SELECT request_id, created_at AS activity_date FROM request_notes
                WHERE note_content LIKE '%Status updated to:%Cancel%' {$notesIds} {$notesDate}
            ) canceled_actions GROUP BY request_id";
    } else {
        $lockIds = 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);
        $lockDate = 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);
        $handledSubquery = "SELECT request_id, MAX(activity_date) AS activity_date FROM (
                SELECT request_id, locked_at AS activity_date FROM csr_request_locks WHERE 1=1 {$lockIds} {$lockDate}
                UNION ALL
                SELECT request_id, assigned_at AS activity_date FROM service_assignments WHERE 1=1 {$assignmentIds} {$assignmentDate}
                UNION ALL
                SELECT request_id, created_at AS activity_date FROM csr_activity WHERE request_id IS NOT NULL {$activityIds} {$activityDate}
                UNION ALL
                SELECT request_id, created_at AS activity_date FROM request_logs WHERE user_role IN ('csr','team_leader','csr_manager') AND request_id IS NOT NULL {$logIds} {$logDate}
            ) handled_actions GROUP BY request_id";
    }

    $statusCondition = $type === 'in_progress' ? ' AND sr.job_status_id = 3' : '';
    $sql = "SELECT sr.id, sr.request_number, sr.full_name, sr.phone_number, sr.requested_at,
                   CASE WHEN sr.ticket_source = 'csr_manual' OR sr.created_by_csr_id IS NOT NULL OR sr.manual_ticket_number IS NOT NULL THEN 'CSR Manual Ticket' WHEN sr.ticket_source = 'chat_followup' OR EXISTS (SELECT 1 FROM chat_conversations cc_src WHERE cc_src.request_id = sr.id AND cc_src.status='open' AND (cc_src.first_response_at IS NOT NULL OR cc_src.assignment_status = 'active')) THEN 'Chat Follow-Up Request' ELSE 'Customer Online Request' END AS ticket_source_label,
                   TIMESTAMPDIFF(MINUTE, sr.requested_at, NOW()) AS ticket_age_minutes,
                   COALESCE(sr.last_updated_at, sr.requested_at) AS last_updated_at,
                   h.activity_date,
                   js.status_name, js.color_code AS status_color,
                   ul.name AS urgency_name,
                   CASE WHEN sr.appliance_type_id IS NULL THEN sr.manual_appliance_type ELSE at.name END AS appliance_type,
                   CASE WHEN sr.brand_id IS NULL THEN sr.manual_brand ELSE b.name END AS brand,
                   c.city_name,
                   COALESCE(techs.technician_names, 'No technician assigned') AS technician_names,
                   COALESCE(notes.notes_count, 0) AS notes_count
            FROM ({$handledSubquery}) h
            INNER JOIN service_requests sr ON sr.id = h.request_id
            LEFT JOIN job_statuses js ON sr.job_status_id = js.id
            LEFT JOIN urgency_levels ul ON sr.urgency_level_id = ul.id
            LEFT JOIN appliance_types at ON sr.appliance_type_id = at.id
            LEFT JOIN brands b ON sr.brand_id = b.id
            LEFT JOIN cities c ON sr.city_id = c.id
            LEFT JOIN (
                SELECT sa.request_id, GROUP_CONCAT(CONCAT(t.first_name, ' ', t.last_name) ORDER BY sa.assigned_at SEPARATOR ', ') AS technician_names
                FROM service_assignments sa
                LEFT JOIN technicians t ON sa.technician_id = t.id
                GROUP BY sa.request_id
            ) techs ON techs.request_id = sr.id
            LEFT JOIN (SELECT request_id, COUNT(*) AS notes_count FROM request_notes GROUP BY request_id) notes ON notes.request_id = sr.id
            WHERE 1=1 {$statusCondition}
            ORDER BY h.activity_date DESC, sr.requested_at DESC
            LIMIT 300";

    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $tickets = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode(['success' => true, 'tickets' => $tickets, 'count' => count($tickets), 'type' => $type, 'scope' => $scope, 'start_date' => $startDate, 'end_date' => $endDate, 'report_context' => $context]);
} catch (Exception $e) {
    echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
}
?>
