<?php
// csr/ajax/get_report_ticket_details.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();
}

$requestId = isset($_GET['request_id']) ? (int)$_GET['request_id'] : 0;
if ($requestId <= 0) {
    echo json_encode(['success' => false, 'message' => 'Invalid request ID']);
    exit();
}

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

    $context = getCsrReportAccessContext($db, (int)$_SESSION['csr_id'], isset($_GET['team_id']) ? (int)$_GET['team_id'] : 0, isset($_GET['csr_id']) ? (int)$_GET['csr_id'] : 0);
    $allowedIds = $context['allowed_csr_ids'];
    $paramsAccess = [':request_id' => $requestId];
    $lockIds = buildCsrIdInCondition($allowedIds, 'csr_id', 'detail_locks', $paramsAccess);
    $assignmentIds = buildCsrIdInCondition($allowedIds, 'assigned_by', 'detail_assignments', $paramsAccess);
    $activityIds = buildCsrIdInCondition($allowedIds, 'csr_id', 'detail_activity', $paramsAccess);
    $logIds = buildCsrIdInCondition($allowedIds, 'user_id', 'detail_logs', $paramsAccess);
    $accessSql = "SELECT 1 FROM (
        SELECT request_id FROM csr_request_locks WHERE request_id = :request_id {$lockIds}
        UNION SELECT request_id FROM service_assignments WHERE request_id = :request_id {$assignmentIds}
        UNION SELECT request_id FROM csr_activity WHERE request_id = :request_id {$activityIds}
        UNION SELECT request_id FROM request_logs WHERE request_id = :request_id AND user_role IN ('csr','team_leader','csr_manager') {$logIds}
    ) allowed LIMIT 1";
    $accessStmt = $db->prepare($accessSql);
    $accessStmt->execute($paramsAccess);
    if (!$accessStmt->fetchColumn()) {
        echo json_encode(['success' => false, 'message' => 'You do not have access to this report ticket.']);
        exit();
    }

    $sql = "SELECT sr.*,
                   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,
                   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,
                   COALESCE(sr.last_updated_at, sr.requested_at) AS last_updated_at,
                   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,
                   cu.email,
                   crl.csr_id AS managing_csr_id,
                   crl.csr_name AS managing_csr
            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 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 customers cu ON sr.customer_id = cu.id
            LEFT JOIN csr_request_locks crl ON crl.request_id = sr.id
            WHERE sr.id = :request_id";
    $stmt = $db->prepare($sql);
    $stmt->execute([':request_id' => $requestId]);
    $request = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$request) {
        echo json_encode(['success' => false, 'message' => 'Request not found']);
        exit();
    }

    $stmt = $db->prepare("SELECT sa.*,
                                 CONCAT(t.first_name, ' ', t.last_name) AS technician_name,
                                 t.phone_number AS technician_phone,
                                 t.email AS technician_email,
                                 ts.status_name AS technician_status,
                                 CASE
                                     WHEN sa.assignment_type = 'primary' THEN 'Lead Technician'
                                     WHEN sa.assignment_type = 'secondary' THEN 'Secondary Technician'
                                     WHEN sa.assignment_type = 'helper' THEN 'Helper'
                                     ELSE 'Technician'
                                 END AS role_display
                          FROM service_assignments sa
                          LEFT JOIN technicians t ON sa.technician_id = t.id
                          LEFT JOIN technician_statuses ts ON t.status_id = ts.id
                          WHERE sa.request_id = :request_id
                          ORDER BY sa.assigned_at DESC");
    $stmt->execute([':request_id' => $requestId]);
    $request['all_assignments'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $db->prepare("SELECT rsh.*,
                                 js.status_name, js.color_code,
                                 CONCAT(csr.first_name, ' ', csr.last_name) AS updated_by_name
                          FROM request_status_history rsh
                          LEFT JOIN job_statuses js ON rsh.job_status_id = js.id
                          LEFT JOIN csr_users csr ON rsh.changed_by = csr.id
                          WHERE rsh.request_id = :request_id
                          ORDER BY rsh.changed_at DESC");
    $stmt->execute([':request_id' => $requestId]);
    $request['status_history'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $db->prepare("SELECT rn.*,
                                 CONCAT(cu.first_name, ' ', cu.last_name) AS created_by_name,
                                 CASE
                                     WHEN rn.note_type = 'csr_note' THEN 'Internal Notes'
                                     WHEN rn.note_type = 'internal' THEN 'Internal Notes'
                                     WHEN rn.note_type = 'customer' THEN 'Customer Updates'
                                     ELSE 'Note'
                                 END AS note_type_label
                          FROM request_notes rn
                          LEFT JOIN csr_users cu ON rn.csr_id = cu.id
                          WHERE rn.request_id = :request_id
                          ORDER BY rn.created_at DESC");
    $stmt->execute([':request_id' => $requestId]);
    $request['notes'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $db->prepare("SELECT rl.*,
                                 CONCAT(csr.first_name, ' ', csr.last_name) AS user_name
                          FROM request_logs rl
                          LEFT JOIN csr_users csr ON rl.user_role IN ('csr','team_leader','csr_manager') AND rl.user_id = csr.id
                          WHERE rl.request_id = :request_id
                          ORDER BY rl.created_at DESC
                          LIMIT 100");
    $stmt->execute([':request_id' => $requestId]);
    $request['edit_logs'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $db->prepare("SELECT * FROM request_images WHERE request_id = :request_id ORDER BY uploaded_at DESC");
    $stmt->execute([':request_id' => $requestId]);
    $images = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($images as &$image) {
        if (isset($image['image_path']) && strpos($image['image_path'], '../') === false) {
            $image['image_path'] = '../' . $image['image_path'];
        }
    }
    $request['images'] = $images;

    echo json_encode(['success' => true, 'request' => $request]);
} catch (Exception $e) {
    echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
}
?>
