<?php
session_start();
require_once '../../config/database.php';
require_once 'csr_request_lock_helper.php';
require_once '../../includes/app_helpers.php';

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();

    ensureRequestLockTable($db);
    ensureCsrTeamTables($db);
    ensureUnifiedTicketWorkflow($db);
    ensureWebRtcCallWorkflow($db);

    $csrId = (int)$_SESSION['csr_id'];
    $page = isset($_POST['page']) ? max(1, (int)$_POST['page']) : 1;
    $limit = 20;
    $offset = ($page - 1) * $limit;

    $where = [];
    $params = [];

    $ticketView = $_POST['ticket_view'] ?? 'active';

    if (!empty($_POST['status'])) {
        $where[] = "sr.job_status_id = :status";
        $params[':status'] = (int)$_POST['status'];
    } else {
        switch ($ticketView) {
            case 'new':
                $where[] = "sr.job_status_id = 1";
                break;
            case 'assigned':
                $where[] = "sr.job_status_id = 2";
                break;
            case 'in_progress':
                $where[] = "sr.job_status_id = 3";
                break;
            case 'completed':
                $where[] = "sr.job_status_id = 4";
                break;
            case 'cancelled':
                $where[] = "sr.job_status_id = 5";
                break;
            case 'all':
                // Show approved tickets from all statuses.
                break;
            case 'active':
            default:
                // Default queue view: tickets that still need attention.
                $where[] = "COALESCE(sr.job_status_id, 1) NOT IN (4, 5)";
                break;
        }
    }

    if (!empty($_POST['urgency'])) {
        $where[] = "sr.urgency_level_id = :urgency";
        $params[':urgency'] = (int)$_POST['urgency'];
    }

    if (!empty($_POST['date_range'])) {
        switch($_POST['date_range']) {
            case 'today': $where[] = "DATE(sr.requested_at) = CURDATE()"; break;
            case 'week': $where[] = "YEARWEEK(sr.requested_at) = YEARWEEK(CURDATE())"; break;
            case 'month': $where[] = "MONTH(sr.requested_at) = MONTH(CURDATE()) AND YEAR(sr.requested_at) = YEAR(CURDATE())"; break;
        }
    }

    if (!empty($_POST['search'])) {
        $search = '%' . trim($_POST['search']) . '%';
        $where[] = "(sr.request_number LIKE :search OR sr.manual_ticket_number LIKE :search OR sr.full_name LIKE :search OR sr.phone_number LIKE :search OR sr.customer_email LIKE :search)";
        $params[':search'] = $search;
    }

    $visibilitySql = buildCsrRequestVisibilitySql($db, $csrId, 'sr', $params, 'request_list_scope');
    $sourceFilter = $_POST['source_filter'] ?? ($_POST['source_type'] ?? 'all');
switch ($sourceFilter) {
    case 'php_csr':
        $where[] = "(sr.source_system = 'php_csr' OR sr.ticket_source = 'csr_manual' OR sr.created_by_csr_id IS NOT NULL OR sr.manual_ticket_number IS NOT NULL)";
        break;
    case 'php_cx':
    case 'cx_app':
        $where[] = "(sr.source_system = 'php_cx' OR sr.ticket_source IN ('cx_online','customer'))";
        break;
    case 'typescript_er':
    case 'er':
        $where[] = "(sr.source_system = 'typescript_er' OR sr.ticket_source IN ('er_typescript','er_imported','typescript_er'))";
        break;
    case 'communicable':
        $where[] = "(sr.customer_id IS NOT NULL OR sr.customer_email IS NOT NULL OR sr.source_system IN ('php_csr','php_cx'))";
        break;
    case 'all':
    default:
        break;
}

// Normal Tickets only show approved/legacy requests. Pending CX submissions stay in Verification Queue.
$where[] = "COALESCE(sr.verification_status, 'approved') = 'approved'";

    // Approved CX/customer requests should appear in the normal Ticket Table for CSR, TL, and Manager.
    // Pending/rejected CX requests stay hidden because of the approved filter above.
    // The visibility scope is still used for manual/team-owned/internal tickets.
    $approvedCustomerTicketSql = "(
        COALESCE(sr.verification_status, 'approved') = 'approved'
        AND (sr.source_system = 'php_cx' OR sr.ticket_source IN ('cx_online','customer'))
        AND COALESCE(sr.is_fake_ticket, 0) = 0
    )";

    $where[] = "(($visibilitySql) OR $approvedCustomerTicketSql)";
    $whereClause = "WHERE " . implode(" AND ", $where);

    $countQuery = "SELECT COUNT(*) AS total FROM service_requests sr LEFT JOIN csr_request_locks crl ON crl.request_id = sr.id $whereClause";
    $stmt = $db->prepare($countQuery);
    foreach ($params as $key => $value) $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
    $stmt->execute();
    $total = (int)$stmt->fetchColumn();

    $query = "
        SELECT 
            sr.*,
            CASE
                WHEN sr.source_system = 'typescript_er' OR sr.ticket_source IN ('er_typescript','er_imported','typescript_er') THEN 'TypeScript / ER'
                WHEN sr.source_system = 'php_csr' OR sr.ticket_source = 'csr_manual' OR sr.created_by_csr_id IS NOT NULL OR sr.manual_ticket_number IS NOT NULL THEN 'PHP / CSR Manual'
                WHEN sr.ticket_source = 'chat_followup' OR EXISTS (SELECT 1 FROM chat_conversations cc2 WHERE cc2.request_id = sr.id AND cc2.status = 'open' AND (cc2.first_response_at IS NOT NULL OR cc2.assignment_status = 'active')) THEN 'Chat Follow-Up Request'
                ELSE 'PHP / Customer App'
            END AS ticket_source_label,
            COALESCE(sr.source_system, CASE WHEN sr.ticket_source IN ('er_typescript','er_imported','typescript_er') THEN 'typescript_er' WHEN sr.ticket_source = 'csr_manual' OR sr.created_by_csr_id IS NOT NULL OR sr.manual_ticket_number IS NOT NULL THEN 'php_csr' ELSE 'php_cx' END) AS source_system_normalized,
            COALESCE(sr.origin_type, CASE WHEN sr.ticket_source IN ('er_typescript','er_imported','typescript_er') THEN 'ER Ticket' WHEN sr.ticket_source = 'csr_manual' OR sr.created_by_csr_id IS NOT NULL OR sr.manual_ticket_number IS NOT NULL THEN 'Manual Ticket' ELSE 'Customer App' END) AS origin_type_label,
            CASE WHEN (sr.customer_id IS NOT NULL OR sr.customer_email IS NOT NULL OR COALESCE(sr.source_system,'') IN ('php_csr','php_cx')) AND COALESCE(sr.source_system,'') <> 'typescript_er' THEN 1 ELSE 0 END AS can_communicate,
            sr.urgency_level_id AS urgency_id,
            COALESCE(ul.name, 'Normal') AS urgency_name,
            ul.time_frame AS urgency_time,
            js.status_name,
            js.color_code AS status_color,
            COALESCE(b.name, sr.manual_brand) AS brand,
            COALESCE(at.name, sr.manual_appliance_type) AS appliance_type,
            COALESCE(sz.city, l.location_name, c.city_name) AS city_name,
            COALESCE(sz.region, c.region, sr.region, c.city_name) AS service_region_name,
            COALESCE(crl.csr_name, CONCAT(owner_csr.first_name, ' ', owner_csr.last_name)) AS managing_csr,
            COALESCE(crl.csr_id, owner_assignment.assigned_by) AS managing_csr_id,
            COALESCE(sr.last_updated_at, sr.requested_at) AS last_updated_at,
            TIMESTAMPDIFF(MINUTE, sr.requested_at, NOW()) AS ticket_age_minutes,
            CASE WHEN sr.job_status_id = 3 THEN TIMESTAMPDIFF(MINUTE, COALESCE(sr.in_progress_started_at, sr.last_updated_at, sr.requested_at), NOW()) ELSE NULL END AS in_progress_minutes,
            (
                SELECT COUNT(*) FROM service_assignments sa
                WHERE sa.request_id = sr.id AND sa.status IN ('assigned', 'en_route', 'on_site')
            ) AS technician_count
        FROM service_requests sr
        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 brands b ON sr.brand_id = b.id
        LEFT JOIN appliance_types at ON sr.appliance_type_id = at.id
        LEFT JOIN cities c ON sr.city_id = c.id
        LEFT JOIN service_areas sz ON sz.zip_code = sr.zip_code AND sz.is_active = 1
        LEFT JOIN locations l ON sr.location_id = l.id
        LEFT JOIN csr_request_locks crl ON crl.request_id = sr.id
        LEFT JOIN service_assignments owner_assignment ON owner_assignment.id = (
            SELECT sa2.id FROM service_assignments sa2
            WHERE sa2.request_id = sr.id AND sa2.assigned_by IS NOT NULL AND sa2.status IN ('assigned', 'en_route', 'on_site')
            ORDER BY sa2.assigned_at ASC LIMIT 1
        )
        LEFT JOIN csr_users owner_csr ON owner_assignment.assigned_by = owner_csr.id
        $whereClause
        ORDER BY 
            CASE WHEN sr.job_status_id IN (4, 5) THEN 2 ELSE 1 END,
            CASE COALESCE(sr.urgency_level_id, 1) WHEN 3 THEN 1 WHEN 2 THEN 2 WHEN 1 THEN 3 ELSE 4 END,
            CASE COALESCE(sr.job_status_id, 1) WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 ELSE 6 END,
            sr.requested_at DESC,
            COALESCE(sr.last_updated_at, sr.requested_at) DESC
        LIMIT :limit OFFSET :offset
    ";

    $stmt = $db->prepare($query);
    foreach ($params as $key => $value) $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();

    $requests = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $total_pages = max(1, (int)ceil($total / $limit));
    $start_page = max(1, $page - 2);
    $end_page = min($total_pages, $page + 2);

    echo json_encode([
        'success' => true,
        'requests' => $requests,
        'total' => $total,
        'pagination' => [
            'current_page' => $page,
            'total_pages' => $total_pages,
            'start_page' => $start_page,
            'end_page' => $end_page,
            'start' => $total > 0 ? $offset + 1 : 0,
            'end' => min($offset + $limit, $total),
            'total' => $total
        ]
    ]);
} catch (Throwable $e) {
    echo json_encode(['success' => false, 'message' => 'Error loading tickets: ' . $e->getMessage()]);
}
?>
