database-setup.sql

-- AeroTraceX Database Setup Script
-- Execute this script to create the database and airports table

-- Create database
CREATE DATABASE IF NOT EXISTS aerotracex;

-- Use the database
USE aerotracex;

-- Create airports table
CREATE TABLE IF NOT EXISTS airports (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    icao_code VARCHAR(4) NOT NULL UNIQUE,
    iata_code VARCHAR(3),
    name VARCHAR(255) NOT NULL,
    city VARCHAR(100),
    country VARCHAR(100),
    latitude DOUBLE NOT NULL,
    longitude DOUBLE NOT NULL,
    altitude INT DEFAULT 0,
    timezone VARCHAR(50),
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    INDEX idx_icao_code (icao_code),
    INDEX idx_iata_code (iata_code),
    INDEX idx_location (latitude, longitude)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert sample airport data
INSERT INTO airports (icao_code, iata_code, name, city, country, latitude, longitude, altitude, created_at, updated_at) VALUES
-- North America
('KJFK', 'JFK', 'John F. Kennedy International Airport', 'New York', 'United States', 40.6413, -73.7781, 13, NOW(), NOW()),
('KLAX', 'LAX', 'Los Angeles International Airport', 'Los Angeles', 'United States', 33.9416, -118.4085, 38, NOW(), NOW()),
('KORD', 'ORD', 'Chicago O''Hare International Airport', 'Chicago', 'United States', 41.9742, -87.9073, 205, NOW(), NOW()),
('KDFW', 'DFW', 'Dallas/Fort Worth International Airport', 'Dallas', 'United States', 32.8998, -97.0403, 183, NOW(), NOW()),
('KATL', 'ATL', 'Hartsfield-Jackson Atlanta International Airport', 'Atlanta', 'United States', 33.6407, -84.4277, 313, NOW(), NOW()),
('KSFO', 'SFO', 'San Francisco International Airport', 'San Francisco', 'United States', 37.6213, -122.3790, 4, NOW(), NOW()),
('KLAS', 'LAS', 'Las Vegas McCarran International Airport', 'Las Vegas', 'United States', 36.0840, -115.1537, 665, NOW(), NOW()),
('KSEA', 'SEA', 'Seattle-Tacoma International Airport', 'Seattle', 'United States', 47.4502, -122.3088, 132, NOW(), NOW()),
('KDEN', 'DEN', 'Denver International Airport', 'Denver', 'United States', 39.8561, -104.6737, 1655, NOW(), NOW()),
('KMIA', 'MIA', 'Miami International Airport', 'Miami', 'United States', 25.7959, -80.2870, 3, NOW(), NOW()),
('CYYZ', 'YYZ', 'Toronto Pearson International Airport', 'Toronto', 'Canada', 43.6777, -79.6248, 173, NOW(), NOW()),
('CYVR', 'YVR', 'Vancouver International Airport', 'Vancouver', 'Canada', 49.1947, -123.1815, 4, NOW(), NOW()),

-- Europe
('EGLL', 'LHR', 'London Heathrow Airport', 'London', 'United Kingdom', 51.4700, -0.4543, 25, NOW(), NOW()),
('LFPG', 'CDG', 'Paris Charles de Gaulle Airport', 'Paris', 'France', 49.0097, 2.5479, 119, NOW(), NOW()),
('EDDF', 'FRA', 'Frankfurt Airport', 'Frankfurt', 'Germany', 50.0379, 8.5622, 111, NOW(), NOW()),
('EHAM', 'AMS', 'Amsterdam Schiphol Airport', 'Amsterdam', 'Netherlands', 52.3105, 4.7683, -3, NOW(), NOW()),
('LEMD', 'MAD', 'Madrid-Barajas Airport', 'Madrid', 'Spain', 40.4983, -3.5676, 610, NOW(), NOW()),
('LIRF', 'FCO', 'Rome Fiumicino Airport', 'Rome', 'Italy', 41.8003, 12.2389, 4, NOW(), NOW()),

-- Asia
('RJTT', 'HND', 'Tokyo Haneda Airport', 'Tokyo', 'Japan', 35.5494, 139.7798, 11, NOW(), NOW()),
('VHHH', 'HKG', 'Hong Kong International Airport', 'Hong Kong', 'Hong Kong', 22.3080, 113.9185, 9, NOW(), NOW()),
('WSSS', 'SIN', 'Singapore Changi Airport', 'Singapore', 'Singapore', 1.3644, 103.9915, 7, NOW(), NOW()),
('ZBAA', 'PEK', 'Beijing Capital International Airport', 'Beijing', 'China', 40.0801, 116.5846, 35, NOW(), NOW()),
('ZSPD', 'PVG', 'Shanghai Pudong International Airport', 'Shanghai', 'China', 31.1434, 121.8052, 4, NOW(), NOW()),
('RKSI', 'ICN', 'Seoul Incheon International Airport', 'Seoul', 'South Korea', 37.4602, 126.4407, 7, NOW(), NOW()),

-- Middle East
('OMDB', 'DXB', 'Dubai International Airport', 'Dubai', 'United Arab Emirates', 25.2532, 55.3657, 19, NOW(), NOW()),
('OTHH', 'DOH', 'Doha Hamad International Airport', 'Doha', 'Qatar', 25.2731, 51.6080, 4, NOW(), NOW()),

-- Australia
('YSSY', 'SYD', 'Sydney Kingsford Smith Airport', 'Sydney', 'Australia', -33.9399, 151.1753, 6, NOW(), NOW()),
('YMML', 'MEL', 'Melbourne Airport', 'Melbourne', 'Australia', -37.6690, 144.8410, 132, NOW(), NOW())
ON DUPLICATE KEY UPDATE updated_at = NOW();

-- Create airplanes table
CREATE TABLE IF NOT EXISTS airplanes (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    icao24 VARCHAR(6) NOT NULL UNIQUE,
    callsign VARCHAR(10),
    origin_country VARCHAR(100),
    aircraft_type VARCHAR(50),
    latitude DOUBLE,
    longitude DOUBLE,
    altitude INT,
    groundspeed INT,
    heading INT,
    vertical_rate DOUBLE,
    on_ground BOOLEAN DEFAULT FALSE,
    squawk VARCHAR(4),
    last_contact BIGINT,
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    INDEX idx_icao24 (icao24),
    INDEX idx_callsign (callsign),
    INDEX idx_location (latitude, longitude),
    INDEX idx_last_contact (last_contact)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Verify the data
SELECT COUNT(*) as total_airports FROM airports;
SELECT * FROM airports LIMIT 5;