---

-- 1. Создание таблиц

-- Маршруты
CREATE TABLE маршруты (
    номер_маршрута SERIAL PRIMARY KEY,
    аэропорт_вылета VARCHAR(100) NOT NULL,
    аэропорт_назначения VARCHAR(100) NOT NULL,
    цена_билета_руб DECIMAL(10,2) NOT NULL CHECK (цена_билета_руб >= 0),
    продолжительность_полета_мин INTEGER NOT NULL CHECK (продолжительность_полета_мин > 0)
);

-- Самолеты
CREATE TABLE самолеты (
    бортовой_номер VARCHAR(20) PRIMARY KEY,
    модель VARCHAR(50) NOT NULL,
    дата_изготовления DATE NOT NULL,
    срок_эксплуатации_лет INTEGER NOT NULL CHECK (срок_эксплуатации_лет > 0),
    готовность_к_вылету BOOLEAN NOT NULL DEFAULT TRUE -- TRUE = Да, FALSE = Нет
);

-- Командиры корабля
CREATE TABLE командиры (
    личный_номер SERIAL PRIMARY KEY,
    фио VARCHAR(150) NOT NULL,
    адрес TEXT,
    телефон VARCHAR(20),
    налет_часов INTEGER NOT NULL CHECK (налет_часов >= 0)
);

-- Рейсы
CREATE TABLE рейсы (
    номер_рейса SERIAL PRIMARY KEY,
    номер_маршрута INTEGER NOT NULL,
    бортовой_номер VARCHAR(20) NOT NULL,
    дата_и_время_вылета TIMESTAMP NOT NULL,
    рейс_отменен BOOLEAN NOT NULL DEFAULT FALSE, -- TRUE = отменен, FALSE = не отменен
    FOREIGN KEY (номер_маршрута) REFERENCES маршруты(номер_маршрута) ON DELETE RESTRICT,
    FOREIGN KEY (бортовой_номер) REFERENCES самолеты(бортовой_номер) ON DELETE RESTRICT
);

-- Пассажиры
CREATE TABLE пассажиры (
    номер_паспорта VARCHAR(20) PRIMARY KEY,
    фио VARCHAR(150) NOT NULL,
    адрес TEXT,
    телефон VARCHAR(20)
);

-- Связь пассажиров и рейсов (билеты)
CREATE TABLE билеты (
    номер_паспорта VARCHAR(20) NOT NULL,
    номер_рейса INTEGER NOT NULL,
    PRIMARY KEY (номер_паспорта, номер_рейса),
    FOREIGN KEY (номер_паспорта) REFERENCES пассажиры(номер_паспорта) ON DELETE CASCADE,
    FOREIGN KEY (номер_рейса) REFERENCES рейсы(номер_рейса) ON DELETE CASCADE
);

-- Закрепление командиров за самолетами (один к одному)
-- У каждого самолета обязательно есть командир, командир закреплен за одним самолетом
ALTER TABLE самолеты ADD COLUMN личный_номер_командира INTEGER NOT NULL UNIQUE;
ALTER TABLE самолеты ADD FOREIGN KEY (личный_номер_командира) REFERENCES командиры(личный_номер) ON DELETE RESTRICT;

-- 2. Индексы для производительности (опционально)
CREATE INDEX idx_рейсы_маршрут ON рейсы(номер_маршрута);
CREATE INDEX idx_рейсы_самолет ON рейсы(бортовой_номер);
CREATE INDEX idx_рейсы_дата ON рейсы(дата_и_время_вылета);
CREATE INDEX idx_билеты_рейс ON билеты(номер_рейса);

-- 3. Пример вставки тестовых данных (можно удалить или изменить)

-- Командиры
INSERT INTO командиры (фио, адрес, телефон, налет_часов) VALUES
('Иванов Иван Иванович', 'Минск, ул. Ленина 1', '+375291234567', 3500),
('Петров Петр Петрович', 'Минск, ул. Гагарина 5', '+375292345678', 4200);

-- Самолеты (с привязкой к командирам)
INSERT INTO самолеты (бортовой_номер, модель, дата_изготовления, срок_эксплуатации_лет, готовность_к_вылету, личный_номер_командира) VALUES
('EW-101PA', 'Боинг-747', '2015-06-01', 25, TRUE, 1),
('EW-202TB', 'Ту-134', '2010-03-12', 30, TRUE, 2);

-- Маршруты
INSERT INTO маршруты (аэропорт_вылета, аэропорт_назначения, цена_билета_руб, продолжительность_полета_мин) VALUES
('Минск', 'Франкфурт', 250.00, 120),
('Минск', 'Рига', 180.00, 80);

-- Рейсы
INSERT INTO рейсы (номер_маршрута, бортовой_номер, дата_и_время_вылета, рейс_отменен) VALUES
(1, 'EW-101PA', '2025-05-20 08:30:00', FALSE),
(2, 'EW-202TB', '2025-05-20 14:15:00', FALSE);

-- Пассажиры
INSERT INTO пассажиры (номер_паспорта, фио, адрес, телефон) VALUES
('MP1234567', 'Сидоров Сидор Сидорович', 'Минск, ул. Красная 10', '+375293334455'),
('MP7654321', 'Кузнецова Анна Петровна', 'Брест, пр. Машерова 22', '+375256667788');

-- Билеты
INSERT INTO билеты (номер_паспорта, номер_рейса) VALUES
('MP1234567', 1),
('MP7654321', 1),
('MP7654321', 2);

-- 4. Представление (VIEW) для отображения РЕЙСОВ авторизованных ПАССАЖИРОВ
-- Под "авторизованных" обычно понимается конкретный пассажир по номеру паспорта
-- Создаем функцию, которая принимает номер паспорта и возвращает список рейсов этого пассажира

CREATE OR REPLACE FUNCTION рейсы_пассажира(p_номер_паспорта VARCHAR)
RETURNS TABLE (
    номер_рейса INTEGER,
    дата_и_время_вылета TIMESTAMP,
    рейс_отменен BOOLEAN,
    аэропорт_вылета VARCHAR,
    аэропорт_назначения VARCHAR,
    модель_самолета VARCHAR,
    фио_командира VARCHAR
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        р.номер_рейса,
        р.дата_и_время_вылета,
        р.рейс_отменен,
        м.аэропорт_вылета,
        м.аэропорт_назначения,
        с.модель AS модель_самолета,
        к.фио AS фио_командира
    FROM билеты б
    JOIN рейсы р ON б.номер_рейса = р.номер_рейса
    JOIN маршруты м ON р.номер_маршрута = м.номер_маршрута
    JOIN самолеты с ON р.бортовой_номер = с.бортовой_номер
    JOIN командиры к ON с.личный_номер_командира = к.личный_номер
    WHERE б.номер_паспорта = p_номер_паспорта
    ORDER BY р.дата_и_время_вылета;
END;
$$ LANGUAGE plpgsql;

-- Пример использования функции для пассажира с паспортом 'MP7654321'
-- SELECT * FROM рейсы_пассажира('MP7654321');