---

-- 1. Таблица Авиакомпаний
CREATE TABLE Airlines (
    airline_id SERIAL PRIMARY KEY,  -- Автоинкремент
    name VARCHAR(100) NOT NULL,
    country VARCHAR(50) NOT NULL,
    iata_code CHAR(2) UNIQUE NOT NULL
);

-- 2. Таблица Аэропортов
CREATE TABLE Airports (
    airport_id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    city VARCHAR(100) NOT NULL,
    country VARCHAR(50) NOT NULL,
    iata_code CHAR(3) UNIQUE NOT NULL
);

-- 3. Таблица Воздушных судов
CREATE TABLE Aircraft (
    aircraft_id SERIAL PRIMARY KEY,
    model VARCHAR(50) NOT NULL,
    tail_number VARCHAR(20) UNIQUE NOT NULL,
    capacity INT NOT NULL,
    airline_id INT NOT NULL,
    FOREIGN KEY (airline_id) REFERENCES Airlines(airline_id) ON DELETE CASCADE
);

-- 4. Таблица Рейсов
CREATE TABLE Flights (
    flight_id SERIAL PRIMARY KEY,
    flight_number VARCHAR(10) NOT NULL,
    departure_airport_id INT NOT NULL,
    arrival_airport_id INT NOT NULL,
    aircraft_id INT NOT NULL,
    departure_time TIMESTAMP NOT NULL,
    arrival_time TIMESTAMP NOT NULL,
    status VARCHAR(20) DEFAULT 'On Time',
    FOREIGN KEY (departure_airport_id) REFERENCES Airports(airport_id) ON DELETE RESTRICT,
    FOREIGN KEY (arrival_airport_id) REFERENCES Airports(airport_id) ON DELETE RESTRICT,
    FOREIGN KEY (aircraft_id) REFERENCES Aircraft(aircraft_id) ON DELETE RESTRICT
);

-- 5. Таблица Пассажиров
CREATE TABLE Passengers (
    passenger_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    passport_number VARCHAR(20) UNIQUE NOT NULL,
    date_of_birth DATE NOT NULL,
    phone VARCHAR(20)
);

-- 6. Таблица Билетов
CREATE TABLE Tickets (
    ticket_id SERIAL PRIMARY KEY,
    passenger_id INT NOT NULL,
    flight_id INT NOT NULL,
    seat_number VARCHAR(5),
    price DECIMAL(10, 2) NOT NULL,
    ticket_class VARCHAR(20) NOT NULL,
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (passenger_id) REFERENCES Passengers(passenger_id) ON DELETE RESTRICT,
    FOREIGN KEY (flight_id) REFERENCES Flights(flight_id) ON DELETE CASCADE
);

-- 7. Таблица Багажа
CREATE TABLE Baggage (
    baggage_id SERIAL PRIMARY KEY,
    ticket_id INT NOT NULL,
    weight_kg DECIMAL(5, 2) NOT NULL,
    is_oversized BOOLEAN DEFAULT FALSE,
    status VARCHAR(20) DEFAULT 'Checked',
    FOREIGN KEY (ticket_id) REFERENCES Tickets(ticket_id) ON DELETE CASCADE
);