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

header('Content-Type: application/json');

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

$request_id = isset($_GET['request_id']) ? (int)$_GET['request_id'] : 0;

if (!$request_id) {
    echo json_encode(['success' => false, 'message' => 'Invalid request ID']);
    exit();
}

try {
    $database = new Database();
    $db = $database->getConnection();
    ensureRequestLockTable($db);
    ensureCsrTeamTables($db);
    ensureUnifiedTicketWorkflow($db);
    requireCsrRequestAccessJson($db, (int)$_SESSION['csr_id'], $request_id);

    
    
    // Get request details - MODIFIED to handle manual entries
    $query = "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,
                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,
                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,
                COALESCE(sz.city, l.location_name, c.city_name) AS city_name,
                COALESCE(sz.region, c.region, c.city_name) AS service_region_name,
                COALESCE(sz.state, c.state) as service_state,
                crl.csr_id as managing_csr_id,
                crl.csr_name as managing_csr,
                COALESCE(NULLIF(sr.customer_email, ''), cu.email) as email,
                sr.customer_email as manual_customer_email,
                CONCAT(csrc.first_name, ' ', csrc.last_name) as created_by_csr_name,
                cu.first_name as customer_first_name,
                cu.last_name as customer_last_name,
                -- Add flags for manual entries
                CASE WHEN sr.brand_id IS NULL THEN 1 ELSE 0 END as is_manual_brand,
                CASE WHEN sr.appliance_type_id IS NULL THEN 1 ELSE 0 END as is_manual_appliance
              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 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 customers cu ON sr.customer_id = cu.id
              LEFT JOIN csr_users csrc ON csrc.id = sr.created_by_csr_id
              WHERE sr.id = :request_id";
    
    $stmt = $db->prepare($query);
    $stmt->bindParam(':request_id', $request_id);
    $stmt->execute();
    
    $request = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$request) {
        echo json_encode(['success' => false, 'message' => 'Request not found']);
        exit();
    }

    // Backfill/display owner from existing active assignment if lock row is missing.
    if (empty($request['managing_csr_id'])) {
        $fallbackLock = getRequestLock($db, $request_id);
        if ($fallbackLock) {
            $request['managing_csr_id'] = $fallbackLock['csr_id'];
            $request['managing_csr'] = $fallbackLock['csr_name'];
        }
    }

    $request['is_locked_by_other_csr'] = (!empty($request['managing_csr_id']) && (int)$request['managing_csr_id'] !== (int)$_SESSION['csr_id']) ? 1 : 0;
    
    // Get images - FIXED: Update image paths to correct location
    $query = "SELECT * FROM request_images WHERE request_id = :request_id ORDER BY uploaded_at DESC";
    $stmt = $db->prepare($query);
    $stmt->bindParam(':request_id', $request_id);
    $stmt->execute();
    $images = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Fix image paths - they're stored as 'uploads/requests/...' but need '../customer/' prefix
    foreach ($images as &$image) {
        // Check if the path already has the correct prefix
        if (strpos($image['image_path'], '../') === false) {
            // Add the correct path prefix to reach the customer uploads directory
            $image['image_path'] = '../' . $image['image_path'];
        }
    }
    $request['images'] = $images;
    
    // Get all current assignments (not just one)
$query = "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 
            AND sa.status IN ('assigned', 'en_route', 'on_site')
          ORDER BY FIELD(sa.assignment_type, 'primary', 'secondary', 'helper'), sa.assigned_at ASC";
$stmt = $db->prepare($query);
$stmt->bindParam(':request_id', $request_id);
$stmt->execute();
$request['current_assignments'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
$request['technician_count'] = count($request['current_assignments']);
    //Updating Status by Assigning Tech
    
    // Get status history - Using request_status_history table
    $query = "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 = $db->prepare($query);
    $stmt->bindParam(':request_id', $request_id);
    $stmt->execute();
    $request['status_history'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

     // Get notes for this request
    $query = "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 = $db->prepare($query);
    $stmt->bindParam(':request_id', $request_id);
    $stmt->execute();
    $request['notes'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    

    // Get audit/activity logs with real account names
    $query = "SELECT rl.*,
                     CASE
                         WHEN rl.action = 'manual_ticket_created' THEN 'created this manual ticket'
                         WHEN rl.action = 'details_update' THEN 'updated ticket details'
                         WHEN rl.action = 'status_change' THEN 'changed ticket status'
                         WHEN rl.action = 'note_added' THEN 'added a note/update'
                         WHEN rl.action = 'request_claimed' THEN 'claimed/managed this ticket'
                         WHEN rl.action = 'technician_assigned' THEN 'assigned a technician'
                         WHEN rl.action = 'chat_reply' THEN 'replied in chat'
                         ELSE REPLACE(rl.action, '_', ' ')
                     END AS action_label
              FROM request_logs rl
              WHERE rl.request_id = :request_id
              ORDER BY rl.created_at DESC
              LIMIT 50";
    $stmt = $db->prepare($query);
    $stmt->bindParam(':request_id', $request_id);
    $stmt->execute();
    $request['activity_logs'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Get current assignment if any
    $query = "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
              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 
                AND sa.status IN ('assigned', 'en_route', 'on_site')
              ORDER BY sa.assigned_at DESC
              LIMIT 1";
    $stmt = $db->prepare($query);
    $stmt->bindParam(':request_id', $request_id);
    $stmt->execute();
    $request['current_assignment'] = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Add urgency_id for badge styling
    $request['urgency_id'] = $request['urgency_level_id'];
    
    // Format dates for better display
    $request['requested_at_formatted'] = date('M d, Y h:i A', strtotime($request['requested_at']));
    if ($request['preferred_date']) {
        $request['preferred_date_formatted'] = date('M d, Y', strtotime($request['preferred_date']));
    }
    if ($request['preferred_time']) {
        $request['preferred_time'];
    }
    
    echo json_encode([
        'success' => true,
        'request' => $request
    ]);
    
} catch (Exception $e) {
    echo json_encode([
        'success' => false,
        'message' => 'Database error: ' . $e->getMessage()
    ]);
}
?>