---

-- 1. Таблица Авиакомпаний
CREATE TABLE Airlines (
    airline_id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    country NVARCHAR(50) NOT NULL,
    iata_code CHAR(2) UNIQUE NOT NULL -- Код авиакомпании (например, SU, S7)
);
GO

-- 2. Таблица Аэропортов (пункты отправления и назначения)
CREATE TABLE Airports (
    airport_id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(150) NOT NULL,
    city NVARCHAR(100) NOT NULL,
    country NVARCHAR(50) NOT NULL,
    iata_code CHAR(3) UNIQUE NOT NULL -- Код аэропорта (например, SVO, JFK)
);
GO

-- 3. Таблица Воздушных судов (Самолетов)
CREATE TABLE Aircraft (
    aircraft_id INT IDENTITY(1,1) PRIMARY KEY,
    model NVARCHAR(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
);
GO

-- 4. Таблица Рейсов
CREATE TABLE Flights (
    flight_id INT IDENTITY(1,1) 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 DATETIME2 NOT NULL,
    arrival_time DATETIME2 NOT NULL,
    status VARCHAR(20) DEFAULT 'On Time', -- On Time, Delayed, Cancelled, Boarding
    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
);
GO

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

-- 6. Таблица Билетов
CREATE TABLE Tickets (
    ticket_id INT IDENTITY(1,1) PRIMARY KEY,
    passenger_id INT NOT NULL,
    flight_id INT NOT NULL,
    seat_number VARCHAR(5),                -- Номер места (например, 12A)
    price DECIMAL(10, 2) NOT NULL,
    ticket_class VARCHAR(20) NOT NULL,     -- Economy, Business, First
    purchase_date DATETIME2 DEFAULT SYSDATETIME(),
    FOREIGN KEY (passenger_id) REFERENCES Passengers(passenger_id) ON DELETE RESTRICT,
    FOREIGN KEY (flight_id) REFERENCES Flights(flight_id) ON DELETE CASCADE
);
GO

-- 7. Таблица Багажа
CREATE TABLE Baggage (
    baggage_id INT IDENTITY(1,1) PRIMARY KEY,
    ticket_id INT NOT NULL,
    weight_kg DECIMAL(5, 2) NOT NULL,
    is_oversized BIT DEFAULT 0,            -- Крупногабаритный багаж (0 = false, 1 = true)
    status VARCHAR(20) DEFAULT 'Checked',  -- Checked, Loaded, Lost, Delivered
    FOREIGN KEY (ticket_id) REFERENCES Tickets(ticket_id) ON DELETE CASCADE
);
GO