---

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

CREATE TABLE passengers (
    id SERIAL PRIMARY KEY,
    passport_number VARCHAR(20) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    birth_date DATE
);

CREATE TABLE aircraft (
    id SERIAL PRIMARY KEY,
    registration_number VARCHAR(20) NOT NULL UNIQUE,
    model VARCHAR(50),
    service_life_years INTEGER, -- срок эксплуатации (лет)
    is_airworthy BOOLEAN DEFAULT TRUE -- исправен/не исправен (Да/Нет)
);

CREATE TABLE captains (
    id SERIAL PRIMARY KEY,
    personal_number VARCHAR(20) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    flight_hours INTEGER DEFAULT 0 -- налет (часов)
);

-- Связь: у самолета один командир, командир закреплен за одним самолетом
ALTER TABLE aircraft ADD COLUMN captain_id INTEGER UNIQUE REFERENCES captains(id) ON DELETE SET NULL;

CREATE TABLE routes (
    id SERIAL PRIMARY KEY,
    route_code VARCHAR(10) NOT NULL UNIQUE,
    departure_airport VARCHAR(100) NOT NULL,
    arrival_airport VARCHAR(100) NOT NULL,
    distance_km INTEGER
);

CREATE TABLE flights (
    id SERIAL PRIMARY KEY,
    flight_number VARCHAR(20) NOT NULL UNIQUE,
    route_id INTEGER NOT NULL REFERENCES routes(id) ON DELETE CASCADE,
    aircraft_id INTEGER NOT NULL REFERENCES aircraft(id) ON DELETE CASCADE,
    departure_datetime TIMESTAMP NOT NULL,
    arrival_datetime TIMESTAMP NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    is_cancelled BOOLEAN DEFAULT FALSE
);

CREATE TABLE tickets (
    id SERIAL PRIMARY KEY,
    passenger_id INTEGER NOT NULL REFERENCES passengers(id) ON DELETE CASCADE,
    flight_id INTEGER NOT NULL REFERENCES flights(id) ON DELETE CASCADE,
    seat_number VARCHAR(5),
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(passenger_id, flight_id)
);

-- Индексы для производительности
CREATE INDEX idx_flights_route_id ON flights(route_id);
CREATE INDEX idx_flights_aircraft_id ON flights(aircraft_id);
CREATE INDEX idx_tickets_passenger_id ON tickets(passenger_id);
CREATE INDEX idx_tickets_flight_id ON tickets(flight_id);


import sys
import os
from PyQt6 import QtWidgets
from PyQt6.QtCore import Qt
from dotenv import load_dotenv
import psycopg2
from psycopg2 import sql

# Загрузка переменных окружения
load_dotenv()

# Импорт сгенерированного интерфейса (создается через Qt Designer)
# В данном случае используется заглушка, так как UI-файл не предоставлен
# Для реальной работы нужно создать Ui_MainWindow через pyuic6
class Ui_MainWindow:
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 600)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.listWidget = QtWidgets.QListWidget(self.centralwidget)
        self.listWidget.setGeometry(QtWidgets.QRect(20, 20, 760, 540))
        MainWindow.setCentralWidget(self.centralwidget)


class DatabaseManager:
    """Класс для работы с базой данных"""
    
    def __init__(self):
        try:
            self.conn = psycopg2.connect(
                host=os.getenv('DB_HOST'),
                user=os.getenv('DB_USER'),
                password=os.getenv('DB_PASSWORD'),
                dbname=os.getenv('DB_NAME'),
                port=os.getenv('DB_PORT')
            )
            self.cur = self.conn.cursor()
            print("Подключение к БД успешно!")
        except Exception as e:
            print(f"Ошибка подключения к БД: {e}")
            sys.exit(1)
    
    def get_flights_by_passenger(self, passenger_id=1):
        """
        Получение всех рейсов, на которые куплены билеты у пассажира.
        Возвращает:
            - номер рейса
            - дата и время вылета
            - аэропорт назначения
            - цена
            - статус рейса (отменен/активен)
            - Ф.И.О. командира
            - модель самолета
        """
        sql_query = """
        SELECT 
            f.flight_number,
            f.departure_datetime,
            r.arrival_airport,
            f.price,
            f.is_cancelled,
            c.full_name AS captain_name,
            a.model AS aircraft_model
        FROM tickets t
        JOIN flights f ON t.flight_id = f.id
        JOIN routes r ON f.route_id = r.id
        JOIN aircraft a ON f.aircraft_id = a.id
        LEFT JOIN captains c ON a.captain_id = c.id
        WHERE t.passenger_id = %s
        ORDER BY f.departure_datetime DESC;
        """
        try:
            self.cur.execute(sql_query, (passenger_id,))
            return self.cur.fetchall()
        except Exception as e:
            print(f"Ошибка выполнения запроса: {e}")
            return []
    
    def get_all_passengers(self):
        """Получить список всех пассажиров для выбора"""
        self.cur.execute("SELECT id, full_name FROM passengers ORDER BY full_name")
        return self.cur.fetchall()
    
    def close(self):
        if self.cur:
            self.cur.close()
        if self.conn:
            self.conn.close()


class MainWindow(QtWidgets.QMainWindow, Ui_MainWindow):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.setWindowTitle("Авиакомпания 'Полет' - Рейсы пассажира")
        
        # Подключение к БД
        self.db = DatabaseManager()
        
        # Загрузка данных
        self.load_data()
    
    def load_data(self):
        """Загрузка и отображение рейсов для пассажира с ID=1"""
        rows = self.db.get_flights_by_passenger(passenger_id=1)
        
        self.listWidget.clear()
        
        if not rows:
            self.listWidget.addItem("Нет данных о рейсах для данного пассажира.")
            return
        
        for row in rows:
            flight_number = row[0]
            departure_dt = row[1].strftime("%d.%m.%Y %H:%M") if row[1] else "—"
            arrival_airport = row[2] or "—"
            price = f"{row[3]:.2f} руб." if row[3] else "—"
            status = "ОТМЕНЕН" if row[4] else "Активен"
            captain = row[5] or "Не назначен"
            aircraft = row[6] or "—"
            
            text = f"""
Рейс: {flight_number}
Дата и время вылета: {departure_dt}
Аэропорт назначения: {arrival_airport}
Цена: {price}
Статус: {status}
Командир: {captain}
Самолет: {aircraft}
{"-"*40}
"""
            self.listWidget.addItem(text.strip())
    
    def closeEvent(self, event):
        """Закрытие соединения с БД при завершении приложения"""
        self.db.close()
        event.accept()


if __name__ == "__main__":
    app = QtWidgets.QApplication(sys.argv)
    window = MainWindow()
    window.show()
    sys.exit(app.exec())




-- Добавление тестовых данных

INSERT INTO passengers (passport_number, full_name, address, phone, birth_date) VALUES
('AB1234567', 'Иванов Иван Иванович', 'Москва, ул. Тверская, д.1', '+7-999-123-45-67', '1985-05-15'),
('CD7654321', 'Петрова Анна Сергеевна', 'Санкт-Петербург, Невский пр., д.10', '+7-999-234-56-78', '1990-08-22');

INSERT INTO captains (personal_number, full_name, address, phone, flight_hours) VALUES
('P001', 'Сидоров Петр Алексеевич', 'Москва, ул. Ленина, д.5', '+7-999-345-67-89', 3500),
('P002', 'Козлова Елена Михайловна', 'Санкт-Петербург, ул. Пушкина, д.15', '+7-999-456-78-90', 2800);

INSERT INTO aircraft (registration_number, model, service_life_years, is_airworthy, captain_id) VALUES
('RA-12345', 'Boeing 737-800', 10, TRUE, 1),
('RA-67890', 'Airbus A320', 8, TRUE, 2);

INSERT INTO routes (route_code, departure_airport, arrival_airport, distance_km) VALUES
('SU001', 'Шереметьево (SVO)', 'Пулково (LED)', 600),
('SU002', 'Домодедово (DME)', 'Кольцово (SVX)', 1400);

INSERT INTO flights (flight_number, route_id, aircraft_id, departure_datetime, arrival_datetime, price, is_cancelled) VALUES
('SU1001', 1, 1, '2026-06-20 09:00:00', '2026-06-20 10:30:00', 8500.00, FALSE),
('SU1002', 1, 1, '2026-06-21 14:00:00', '2026-06-21 15:30:00', 9200.00, FALSE),
('SU2001', 2, 2, '2026-06-22 08:00:00', '2026-06-22 11:00:00', 15000.00, TRUE);

INSERT INTO tickets (passenger_id, flight_id, seat_number) VALUES
(1, 1, '12A'),
(1, 3, '5C'),
(2, 2, '8B');