<?php
header('Content-Type: application/json');
require_once '../includes/db.php';

$response = ['status' => 'error', 'message' => 'An error occurred.', 'data' => []];

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $from_city = trim($_POST['from_city'] ?? '');
    $to_city = trim($_POST['to_city'] ?? '');

    if (!empty($from_city) && !empty($to_city)) {
        $from_query = "%" . $from_city . "%";
        $to_query = "%" . $to_city . "%";

        /*
         * FINALIZED SQL QUERY:
         * This version uses your 'airports' table and its 'name' column for the city.
        */
        $sql = "SELECT 
                    fr.id, 
                    fr.airline_name, 
                    fr.flight_number,
                    origin_airport.name AS origin_city,      -- UPDATED to use 'name' column
                    dest_airport.name AS destination_city,   -- UPDATED to use 'name' column
                    fr.departure_time,
                    fr.arrival_time,
                    fr.price
                FROM 
                    flight_routes AS fr
                JOIN 
                    airports AS origin_airport ON fr.origin_airport_id = origin_airport.id
                JOIN 
                    airports AS dest_airport ON fr.destination_airport_id = dest_airport.id
                WHERE 
                    origin_airport.name LIKE ? AND dest_airport.name LIKE ?"; // UPDATED to filter by 'name'
        
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ss", $from_query, $to_query);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $flights = [];
        while ($row = $result->fetch_assoc()) {
            $flights[] = $row;
        }
        
        $response['status'] = 'success';
        $response['message'] = count($flights) . " flight(s) found.";
        $response['data'] = $flights;
        
        $stmt->close();
    } else {
        $response['message'] = 'Origin and destination are required.';
    }
} else {
    $response['message'] = 'Invalid request method.';
}

$conn->close();
echo json_encode($response);
?>
