sql.db

CREATE TABLE IF NOT EXISTS Pilots (
    Pilot_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    First_Name TEXT NOT NULL,
    Last_Name TEXT NOT NULL,
    License_Number TEXT UNIQUE NOT NULL,
    Experience_Years INTEGER
);

CREATE TABLE IF NOT EXISTS Destinations (
    Destination_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    Airport_Code TEXT UNIQUE NOT NULL,
    City TEXT NOT NULL,
    Country TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS Flights (
    Flight_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    Flight_Number TEXT UNIQUE NOT NULL,
    Departure_Airport TEXT NOT NULL,
    Destination_ID INTEGER,
    Departure_Time DATETIME NOT NULL,
    Arrival_Time DATETIME NOT NULL,
    Status TEXT CHECK(Status IN ('Scheduled', 'Delayed', 'Cancelled', 'Completed')),
    Aircraft_Type TEXT,
    FOREIGN KEY (Destination_ID) REFERENCES Destinations(Destination_ID)
);

CREATE TABLE IF NOT EXISTS Flight_Pilot (
    Assignment_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    Flight_ID INTEGER,
    Pilot_ID INTEGER,
    Role TEXT CHECK(Role IN ('Captain', 'First Officer')),
    FOREIGN KEY (Flight_ID) REFERENCES Flights(Flight_ID),
    FOREIGN KEY (Pilot_ID) REFERENCES Pilots(Pilot_ID)

);