---

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

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

class SqlQw:
    def __init__(self):
        self.con = 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.con.cursor()

    def get_flights_by_passenger(self, passenger_id=1):
        sql = """
        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;
        """
        self.cur.execute(sql, (passenger_id,))
        return self.cur.fetchall()

class MainWindow(QtWidgets.QMainWindow):
    def __init__(self):
        super().__init__()
        uic.loadUi('air.ui', self)
        
        self.setWindowTitle("Авиакомпания 'Полет' - Рейсы пассажира")
        
        self.db = SqlQw()
        self.load_data()

    def load_data(self):
        rows = self.db.get_flights_by_passenger(passenger_id=1)
        
        self.listWidget.clear()
        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}
"""
            self.listWidget.addItem(text)

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