-- ===== Airport =====

INSERT INTO Airport (AirportName, IataCode, Location, ContactEmail, ContactPhone, OperationalStatus)
VALUES
('Skopje International Airport', 'SKP', 'Skopje, North Macedonia', 'info.skp@airports.mk', 3892402001, 'Open'),
('Ohrid St. Paul the Apostle Airport', 'OHD', 'Ohrid, North Macedonia', 'info.ohd@airports.mk', 3892402002, 'Open'),

('Belgrade Nikola Tesla Airport', 'BEG', 'Belgrade, Serbia', 'info.beg@airports.rs', 381112090001, 'Open'),
('Niš Constantine the Great Airport', 'INI', 'Niš, Serbia', 'info.ini@airports.rs', 381184550001, 'Open'),

('Zagreb Franjo Tuđman Airport', 'ZAG', 'Zagreb, Croatia', 'info.zag@airports.hr', 385145620001, 'Open'),
('Split Airport', 'SPU', 'Split, Croatia', 'info.spu@airports.hr', 385215550001, 'Open'),
('Dubrovnik Airport', 'DBV', 'Dubrovnik, Croatia', 'info.dbv@airports.hr', 385207700001, 'Open'),

('Sarajevo International Airport', 'SJJ', 'Sarajevo, Bosnia and Herzegovina', 'info.sjj@airports.ba', 38733569001, 'Open'),
('Banja Luka Airport', 'BNX', 'Banja Luka, Bosnia and Herzegovina', 'info.bnx@airports.ba', 38751230001, 'Open'),

('Podgorica Airport', 'TGD', 'Podgorica, Montenegro', 'info.tgd@airports.me', 38220444001, 'Open'),
('Tivat Airport', 'TIV', 'Tivat, Montenegro', 'info.tiv@airports.me', 38232670001, 'Open'),

('Tirana International Airport', 'TIA', 'Tirana, Albania', 'info.tia@airports.al', 35542381001, 'Open'),

('Pristina International Airport', 'PRN', 'Pristina, Kosovo', 'info.prn@airports.rks', 38338502001, 'Open'),

('Athens International Airport', 'ATH', 'Athens, Greece', 'info.ath@aia.gr', 30210353001, 'Open'),
('Thessaloniki Airport', 'SKG', 'Thessaloniki, Greece', 'info.skg@aia.gr', 30231047001, 'Open'),
('Heraklion Airport', 'HER', 'Crete, Greece', 'info.her@aia.gr', 30281024001, 'Open'),

('Sofia Airport', 'SOF', 'Sofia, Bulgaria', 'info.sof@sofia-airport.bg', 35929372001, 'Open'),
('Varna Airport', 'VAR', 'Varna, Bulgaria', 'info.var@varna-airport.bg', 35952602001, 'Open'),
('Burgas Airport', 'BOJ', 'Burgas, Bulgaria', 'info.boj@burgas-airport.bg', 35956870201, 'Open'),

('Henri Coandă Airport', 'OTP', 'Bucharest, Romania', 'info.otp@bucharestairports.ro', 40212004001, 'Open'),
('Cluj-Napoca Airport', 'CLJ', 'Cluj-Napoca, Romania', 'info.clj@airportcluj.ro', 40264500001, 'Open'),
('Timișoara Airport', 'TSR', 'Timișoara, Romania', 'info.tsr@aerotim.ro', 40256400001, 'Open'),

('Istanbul Airport', 'IST', 'Istanbul, Turkey', 'info.ist@igairport.com', 90212444001, 'Open'),
('Sabiha Gökçen Airport', 'SAW', 'Istanbul, Turkey', 'info.saw@sgairport.com', 90216589001, 'Open'),
('Ankara Esenboğa Airport', 'ESB', 'Ankara, Turkey', 'info.esb@dhmi.gov.tr', 90312300001, 'Open'),

('Frankfurt Airport', 'FRA', 'Frankfurt, Germany', 'info.fra@fraport.de', 49696900001, 'Open'),
('Munich Airport', 'MUC', 'Munich, Germany', 'info.muc@munich-airport.de', 49899750001, 'Open'),
('Berlin Brandenburg Airport', 'BER', 'Berlin, Germany', 'info.ber@berlin-airport.de', 49306091001, 'Open'),
('Hamburg Airport', 'HAM', 'Hamburg, Germany', 'info.ham@hamburg-airport.de', 49405095001, 'Open'),

('Paris Charles de Gaulle Airport', 'CDG', 'Paris, France', 'info.cdg@adp.fr', 33170363951, 'Open'),
('Nice Côte d''Azur Airport', 'NCE', 'Nice, France', 'info.nce@cotedazur.aeroport.fr', 33493230001, 'Open'),
('Lyon Saint-Exupéry Airport', 'LYS', 'Lyon, France', 'info.lys@lyonaeroports.com', 33472226001, 'Open'),

('Rome Fiumicino Airport', 'FCO', 'Rome, Italy', 'info.fco@adr.it', 39066595001, 'Open'),
('Milan Malpensa Airport', 'MXP', 'Milan, Italy', 'info.mxp@sea-aeroportimilano.it', 39022323001, 'Open'),
('Venice Marco Polo Airport', 'VCE', 'Venice, Italy', 'info.vce@veniceairport.it', 39041260001, 'Open'),
('Naples International Airport', 'NAP', 'Naples, Italy', 'info.nap@gesac.it', 390817896001, 'Open'),
('Bologna Guglielmo Marconi Airport', 'BLQ', 'Bologna, Italy', 'info.blq@bologna-airport.it', 39051447001, 'Open'),
('Turin Airport', 'TRN', 'Turin, Italy', 'info.trn@sagat.it', 390114679001, 'Open'),
('Palermo Airport', 'PMO', 'Palermo, Sicily, Italy', 'info.pmo@gesap.it', 39091706001, 'Open'),
('Catania Airport', 'CTA', 'Catania, Sicily, Italy', 'info.cta@aeroporto.catania.it', 390957239001, 'Open'),
('Cagliari Airport', 'CAG', 'Cagliari, Sardinia, Italy', 'info.cag@sogaer.it', 39070460001, 'Open'),
('Olbia Airport', 'OLB', 'Olbia, Sardinia, Italy', 'info.olb@geasar.it', 390789659001, 'Open'),

('Madrid Barajas Airport', 'MAD', 'Madrid, Spain', 'info.mad@aena.es', 34913211001, 'Open'),
('Barcelona El Prat Airport', 'BCN', 'Barcelona, Spain', 'info.bcn@aena.es', 34913211002, 'Open'),
('Valencia Airport', 'VLC', 'Valencia, Spain', 'info.vlc@aena.es', 34913211003, 'Open'),

('London Heathrow Airport', 'LHR', 'London, United Kingdom', 'info.lhr@heathrow.com', 442087591001, 'Open'),
('London Gatwick Airport', 'LGW', 'London, United Kingdom', 'info.lgw@gatwickairport.com', 442087100001, 'Open'),
('Manchester Airport', 'MAN', 'Manchester, United Kingdom', 'info.man@manairport.co.uk', 441619490001, 'Open'),

('Amsterdam Schiphol Airport', 'AMS', 'Amsterdam, Netherlands', 'info.ams@schiphol.nl', 31207940801, 'Open'),
('Brussels Airport', 'BRU', 'Brussels, Belgium', 'info.bru@brusselsairport.be', 3227534001, 'Open'),
('Eindhoven Airport', 'EIN', 'Eindhoven, Netherlands', 'info.ein@eindhovenairport.nl', 31402540001, 'Open'),

('Vienna International Airport', 'VIE', 'Vienna, Austria', 'info.vie@viennaairport.at', 43170070001, 'Open'),
('Zurich Airport', 'ZRH', 'Zurich, Switzerland', 'info.zrh@zurich-airport.ch', 41438161601, 'Open'),
('Geneva Airport', 'GVA', 'Geneva, Switzerland', 'info.gva@gva.ch', 41227170001, 'Open'),

('Warsaw Chopin Airport', 'WAW', 'Warsaw, Poland', 'info.waw@lotnisko-chopina.pl', 48226500001, 'Open'),
('Prague Václav Havel Airport', 'PRG', 'Prague, Czech Republic', 'info.prg@prg.aero', 420220111001, 'Open'),
('Budapest Ferenc Liszt Airport', 'BUD', 'Budapest, Hungary', 'info.bud@bud.hu', 36129670001, 'Open'),

-- Middle East
('Dubai International Airport', 'DXB', 'Dubai, UAE', 'info.dxb@dubaiairports.ae', 97142245001, 'Open'),
('Doha Hamad International Airport', 'DOH', 'Doha, Qatar', 'info.doh@hamad.qa', 97440106001, 'Open'),
('Abu Dhabi International Airport', 'AUH', 'Abu Dhabi, UAE', 'info.auh@adac.ae', 97125055001, 'Open'),

-- US North America
('John F. Kennedy International Airport', 'JFK', 'New York, USA', 'info.jfk@panynj.gov', 17182444001, 'Open'),
('Toronto Pearson International Airport', 'YYZ', 'Toronto, Canada', 'info.yyz@torontopearson.ca', 14162403001, 'Open'),

-- Asia
('Tokyo Haneda Airport', 'HND', 'Tokyo, Japan', 'info.hnd@tokyo-airport.jp', 81357577001, 'Open'),
('Beijing Capital International Airport', 'PEK', 'Beijing, China', 'info.pek@bcia.com.cn', 86109699001, 'Open'),
('Singapore Changi Airport', 'SIN', 'Singapore', 'info.sin@changiairport.com', 65659568001, 'Open');


-- ===== Taxiway =====

INSERT INTO Taxiway (TaxiwayId, TaxiwayName, AirportId, TaxiwayLength, Direction, Status) VALUES
(1, 'A', 1, 1780, 'Northwest', 'Open'),
(2, 'B', 1, 883, 'North', 'Restricted'),
(3, 'C', 1, 532, 'Southwest', 'Closed'),
(4, 'D', 1, 1317, 'Southeast', 'Open'),
(5, 'A', 2, 1530, 'West', 'Open'),
(6, 'A', 3, 1266, 'Northeast', 'Restricted'),
(7, 'B', 3, 232, 'Southwest', 'Open'),
(8, 'C', 3, 289, 'Northeast', 'Closed'),
(9, 'D', 3, 984, 'South', 'Available'),
(10, 'E', 3, 1373, 'East', 'Open'),
(11, 'F', 3, 630, 'East', 'Blocked'),
(12, 'G', 3, 791, 'East', 'Occupied'),
(13, 'A', 5, 849, 'East', 'Available'),
(14, 'B', 5, 1512, 'South', 'Available'),
(15, 'C', 5, 203, 'East', 'Open'),
(16, 'D', 5, 604, 'West', 'Blocked'),
(17, 'E', 5, 795, 'South', 'Open'),
(18, 'F', 5, 1650, 'Northeast', 'Open'),
(19, 'A', 8, 1614, 'East', 'Available'),
(20, 'B', 8, 701, 'Southwest', 'Restricted'),
(21, 'C', 8, 1531, 'South', 'Restricted'),
(22, 'D', 8, 755, 'North', 'Occupied'),
(23, 'E', 8, 332, 'South', 'Inactive'),
(24, 'F', 8, 1899, 'Northwest', 'Open'),
(25, 'A', 17, 520, 'Northwest', 'Available'),
(26, 'B', 17, 1729, 'Southwest', 'Available'),
(27, 'C', 17, 1331, 'Southeast', 'Open'),
(28, 'D', 17, 1650, 'South', 'Available'),
(29, 'E', 17, 1023, 'Northeast', 'Restricted'),
(30, 'F', 17, 1701, 'Northeast', 'Available');


-- ===== Runway =====

INSERT INTO Runway (RunwayName, AirportId, RunwayLength, RunwayType, Status) VALUES
('16/34',  1, 2950, 'Asphalt', 'Open'),
('11/29',  2, 2548, 'Asphalt', 'Open'),
('12L/30R',3, 3400, 'Asphalt', 'Open'),
('12R/30L',3, 3500, 'Asphalt', 'Open'),
('04/22',  5, 3252, 'Asphalt', 'Open'),
('11/29',  8, 2700, 'Asphalt', 'Open'),
('09/27',  17,3600, 'Asphalt', 'Open');


-- ===== Terminal =====

INSERT INTO Terminal (TerminalName, TerminalCode, AirportId, TerminalStatus, Capacity)
VALUES
('Terminal A', 'SKP-A', 1, 'Open', 1200000),
('Terminal B', 'SKP-B', 1, 'Under Maintenance', 800000);

INSERT INTO Terminal (TerminalName, TerminalCode, AirportId, TerminalStatus, Capacity)
VALUES
('Main', 'OHD-T1', 2, 'Open', 30000);

INSERT INTO Terminal (TerminalName, TerminalCode, AirportId, TerminalStatus, Capacity)
VALUES
('Terminal 1', 'BEG-T1', 3, 'Closed', 5000000),
('Terminal 2', 'BEG-T2', 3, 'Open', 7000000),
('Terminal 3', 'BEG-T3', 3, 'Open', 4000000);

INSERT INTO Terminal (TerminalName, TerminalCode, AirportId, TerminalStatus, Capacity)
VALUES
('Terminal A', 'ZAG-PAX', 5, 'Open', 3500000),
('Terminal B', 'ZAG-CGO', 5, 'Restricted', 1500000);

INSERT INTO Terminal (TerminalName, TerminalCode, AirportId, TerminalStatus, Capacity)
VALUES
('Main', 'SJJ-T1', 8, 'Open', 2000000);

INSERT INTO Terminal (TerminalName, TerminalCode, AirportId, TerminalStatus, Capacity)
VALUES
('Terminal 1', 'SOF-T1', 17, 'Closed', 3000000),
('Terminal 2', 'SOF-T2', 17, 'Open', 5000000);


-- ===== Gate =====

INSERT INTO Gate (GateCode, TerminalId, Status) VALUES

-- Skopje
(101, 1, 'Open'),
(102, 1, 'Open'),
(103, 1, 'Open'),
(104, 1, 'Open'),
(105, 1, 'Open'),

(201, 2, 'Open'),
(202, 2, 'Open'),
(203, 2, 'Open'),
(204, 2, 'Open'),
(205, 2, 'Open'),
(206, 2, 'Open'),

-- Ohrid
(1, 3, 'Open'),
(2, 3, 'Open'),
(3, 3, 'Open'),
(4, 3, 'Open'),

-- Belgrad
(1,  4, 'Open'),
(2,  4, 'Open'),
(3,  4, 'Open'),
(4,  4, 'Open'),
(5,  4, 'Open'),
(6,  4, 'Open'),
(7,  4, 'Open'),
(8,  4, 'Open'),
(9,  4, 'Open'),
(10, 4, 'Open'),
(11, 4, 'Open'),

(1,  5, 'Open'),
(2,  5, 'Open'),
(3,  5, 'Open'),
(4,  5, 'Open'),
(5,  5, 'Open'),
(6,  5, 'Open'),
(7,  5, 'Open'),
(8,  5, 'Open'),
(9,  5, 'Open'),
(10, 5, 'Open'),
(11, 5, 'Open'),
(12, 5, 'Open'),
(13, 5, 'Open'),
(14, 5, 'Open'),

-- Zagreb
(1,  7, 'Open'),
(2,  7, 'Open'),
(3,  7, 'Open'),
(4,  7, 'Open'),
(5,  7, 'Open'),
(6,  7, 'Open'),
(7,  7, 'Open'),
(8,  7, 'Open'),
(9,  7, 'Open'),
(10, 7, 'Open'),
(11, 7, 'Open'),
(12, 7, 'Open'),
(13, 7, 'Open'),
(21, 7, 'Open'),
(22, 7, 'Open'),
(23, 7, 'Open'),
(24, 7, 'Open'),
(25, 7, 'Open'),
(26, 7, 'Open'),
(27, 7, 'Open'),
(28, 7, 'Open'),

-- Saraevo
(1, 9, 'Open'),
(2, 9, 'Open'),
(3, 9, 'Open'),
(4, 9, 'Open'),
(5, 9, 'Open'),
(6, 9, 'Open'),

-- Sofia
(1, 10, 'Open'),
(2, 10, 'Open'),
(3, 10, 'Open'),
(4, 10, 'Open'),
(5, 10, 'Open'),
(6, 10, 'Open'),
(7, 10, 'Open'),

(1,  11, 'Open'),
(5,  11, 'Open'),
(6,  11, 'Open'),
(7,  11, 'Open'),
(8,  11, 'Open'),
(9,  11, 'Open'),
(10, 11, 'Open');


-- ===== Hangar =====

INSERT INTO Hangar (HangarName, AirportId, HangarCapacity, HangarType, Status) VALUES
('Skopje-Main-H1', 1, 3, 'Maintenance', 'Open'),
('Skopje-Cargo-H2', 1, 5, 'Storage', 'Open'),
('Ohrid-General-H1', 2, 2, 'General', 'Open'),
('Belgrade-Jat-Tech-1', 3, 12, 'Heavy Maintenance', 'Open'),
('Belgrade-Jat-Tech-2', 3, 10, 'Heavy Maintenance', 'Under Maintenance'),
('Belgrade-Cargo-A1', 3, 8, 'Storage', 'Full'),
('Belgrade-VIP-H3', 3, 2, 'Private', 'Open'),
('Zagreb-Croatia-H1', 5, 6, 'Maintenance', 'Open'),
('Zagreb-Gov-H2', 5, 2, 'Government/VIP', 'Open'),
('Zagreb-General-H3', 5, 4, 'Storage', 'Closed'),
('Sarajevo-Main-H1', 8, 3, 'Maintenance', 'Open'),
('Sarajevo-Private-H2', 8, 1, 'General', 'Open'),
('Sofia-Lufthansa-T1', 17, 15, 'Heavy Maintenance', 'Open'),
('Sofia-Lufthansa-T2', 17, 12, 'Heavy Maintenance', 'Open'),
('Sofia-BulgariaAir-H1', 17, 5, 'Maintenance', 'Open'),
('Sofia-General-H2', 17, 4, 'Storage', 'Under Maintenance'),
('Sofia-Cargo-C1', 17, 10, 'Cargo Storage', 'Full');


-- ===== MechanicEmployee =====

INSERT INTO MechanicEmployee (EmployeeId)
SELECT EmployeeId
FROM Employee
WHERE Position = 'Mechanic';


-- ===== WorksOn =====

INSERT INTO WorksOn (EmployeeId, MaintenanceId)
SELECT DISTINCT
    me.EmployeeId,
    am.MaintenanceId
FROM MechanicEmployee me
INNER JOIN Employee e ON me.EmployeeId = e.EmployeeId
CROSS JOIN LATERAL (
    SELECT MaintenanceId, StartDate
    FROM AircraftMaintenance
    WHERE MaintenanceId BETWEEN
        (me.EmployeeId * 1000) % 1000000 + 1
        AND (me.EmployeeId * 1000) % 1000000 + 20000
    AND StartDate >= e.HireDate
    AND (e.QuitDate IS NULL OR StartDate <= e.QuitDate)
    AND e.DateOfBirth + INTERVAL '18 years' <= StartDate
    LIMIT 5000
) am
WHERE (SELECT COUNT(*) FROM WorksOn w WHERE w.EmployeeId = me.EmployeeId) < 10000
LIMIT 2500000;


-- ===== Departures =====

INSERT INTO Departures (
    FlightId,
    GateId,
    RunwayId,
    Status,
    DepartureTime,
    DestinationAirportId,
    TaxiwayId
)
SELECT
    f.FlightId,

    (SELECT g.GateId
     FROM Gate g
     JOIN Terminal t ON g.TerminalId = t.TerminalId
     WHERE t.AirportId = f.OriginAirportId
     ORDER BY random()
     LIMIT 1),

    (SELECT r.RunwayId
     FROM Runway r
     WHERE r.AirportId = f.OriginAirportId
     ORDER BY random()
     LIMIT 1),

    CASE
        WHEN f.OperatingStatus IN ('Scheduled','Go to gate','Boarding','Departed','Delayed','Cancelled','Diverted')
            THEN f.OperatingStatus
        WHEN f.OperatingStatus = 'En Route'
            THEN 'Departed'
        ELSE 'Scheduled'
    END,

    f.DepartureTime,
    f.DestinationAirportId,

    (SELECT t.TaxiwayId
     FROM Taxiway t
     WHERE t.AirportId = f.OriginAirportId
     ORDER BY random()
     LIMIT 1)

FROM Flights f
WHERE f.OriginAirportId IN (1,2,3,5,8,17);


-- ===== Arrivals =====

INSERT INTO Arrivals (
    FlightId,
    GateId,
    RunwayId,
    Status,
    ArrivalTime,
    OriginAirportId,
    TaxiwayId
)
SELECT
    f.FlightId,

    (SELECT g.GateId
     FROM Gate g
     JOIN Terminal t ON g.TerminalId = t.TerminalId
     WHERE t.AirportId = f.DestinationAirportId
     ORDER BY random()
     LIMIT 1),

    (SELECT r.RunwayId
     FROM Runway r
     WHERE r.AirportId = f.DestinationAirportId
     ORDER BY random()
     LIMIT 1),

    CASE
        WHEN f.OperatingStatus IN ('Scheduled','En Route','Landed','Arrived','Delayed','Cancelled','Diverted')
            THEN f.OperatingStatus
        WHEN f.OperatingStatus = 'Departed'
            THEN 'En Route'
        ELSE 'Scheduled'
    END,

    f.ArrivalTime,
    f.OriginAirportId,

    (SELECT t.TaxiwayId
     FROM Taxiway t
     WHERE t.AirportId = f.DestinationAirportId
     ORDER BY random()
     LIMIT 1)

FROM Flights f
WHERE f.DestinationAirportId IN (1,2,3,5,8,17);


-- ===== Ticket =====

ALTER TABLE Ticket
ADD CONSTRAINT uq_ticket_flight_passenger
UNIQUE (FlightId, PassengerId);

SELECT setval('ticket_ticketid_seq', 3671843);

ROLLBACK;

BEGIN;

CREATE OR REPLACE FUNCTION get_ticket_class(p_airline_id bigint)
RETURNS varchar(20) AS $$
DECLARE
    r float := random();
BEGIN
    IF p_airline_id = ANY(ARRAY[35,36,39,47,48,49,50,54,61,62,63,64,65,66,68,69,70,71,72]) THEN
        RETURN 'Economy';

    ELSIF p_airline_id = ANY(ARRAY[31,32,33,34,42,55,56,57,58,59,73,74]) THEN
        IF r < 0.70 THEN RETURN 'Economy';
        ELSE RETURN 'Business';
        END IF;

    ELSE
        IF    r < 0.55 THEN RETURN 'Economy';
        ELSIF r < 0.70 THEN RETURN 'Premium Economy';
        ELSIF r < 0.92 THEN RETURN 'Business';
        ELSE               RETURN 'First Class';
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_ticket_price(p_class varchar(20), p_airline_id bigint)
RETURNS numeric AS $$
DECLARE
    lo numeric; hi numeric;
    lcc_factor numeric := 0.55;
    is_lcc boolean;
BEGIN
    is_lcc := p_airline_id = ANY(ARRAY[35,36,39,47,48,49,50,54,61,62,63,64,65,66,68,69,70,71,72]);

    CASE p_class
        WHEN 'Economy'         THEN lo := 35;   hi := 450;
        WHEN 'Premium Economy' THEN lo := 200;  hi := 900;
        WHEN 'Business'        THEN lo := 400;  hi := 3500;
        WHEN 'First Class'     THEN lo := 1200; hi := 8000;
        ELSE                        lo := 35;   hi := 450;
    END CASE;

    IF is_lcc THEN
        lo := lo * lcc_factor;
        hi := hi * lcc_factor;
    END IF;

    RETURN round((lo + random() * (hi - lo))::numeric, 2);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_ticket_status()
RETURNS varchar(20) AS $$
DECLARE r float := random();
BEGIN
    IF    r < 0.10 THEN RETURN 'Booked';
    ELSIF r < 0.45 THEN RETURN 'Confirmed';
    ELSIF r < 0.53 THEN RETURN 'Cancelled';
    ELSIF r < 0.85 THEN RETURN 'Used';
    ELSIF r < 0.93 THEN RETURN 'Refunded';
    ELSE               RETURN 'Pending';
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_booking_date(p_base timestamp)
RETURNS timestamp AS $$
BEGIN
    IF random() < 0.5 THEN
        RETURN p_base - (floor(random() * 4 + 1)::int || ' days')::interval;
    ELSE
        RETURN p_base + (floor(random() * 23 + 1)::int || ' hours')::interval;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TEMP TABLE tmp_luggage_assignment AS
SELECT
    row_number() OVER (ORDER BY random()) AS rn,
    l.LuggageId
FROM
    (SELECT LuggageId FROM Luggage ORDER BY random()) l
LIMIT 13017666;

CREATE INDEX ON tmp_luggage_assignment(rn);


INSERT INTO Ticket (FlightId, SeatNumber, Class, BookingDate, Status, TicketPrice, PassengerId, LuggageId)
WITH

flight_seats AS (
    SELECT
        f.FlightId,
        f.AirlineId,
        f.DepartureTime,
        s.seat_num,
        (((s.seat_num - 1) / 6) + 1)::text ||
        chr(65 + ((s.seat_num - 1) % 6))  AS SeatNumber
    FROM (
    SELECT *
    FROM (
    SELECT f.*
    FROM Flights f
    WHERE f.FlightId BETWEEN 71910 AND 498472
      AND NOT EXISTS (
          SELECT 1
          FROM Ticket t
          WHERE t.FlightId = f.FlightId
      )
    ORDER BY f.FlightId
) f
    WHERE FlightId BETWEEN 71910 AND 498472
    ORDER BY FlightId
) f
CROSS JOIN generate_series(1, 50) AS s(seat_num)
),

with_class AS (
    SELECT
        fs.FlightId,
        fs.AirlineId,
        fs.DepartureTime,
        fs.seat_num,
        fs.SeatNumber,
        get_ticket_class(fs.AirlineId)  AS Class
    FROM flight_seats fs
),

with_booking AS (
    SELECT
        wc.FlightId,
        wc.AirlineId,
        wc.DepartureTime,
        wc.seat_num,
        wc.SeatNumber,
        wc.Class,
        -- Base: одземи 7-180 дена
        get_booking_date(
            wc.DepartureTime - (floor(random() * 174 + 7)::int || ' days')::interval
        ) AS BookingDate
    FROM with_class wc
),

with_passenger AS (
    SELECT
        wb.*,
        ((wb.FlightId * 1000 + wb.seat_num) % 995623 + 1)::bigint AS PassengerId
    FROM with_booking wb
),

with_rownum AS (
    SELECT
        wp.*,
        row_number() OVER (ORDER BY wp.FlightId, wp.seat_num) AS rn
    FROM with_passenger wp
)

SELECT
    wr.FlightId,
    wr.SeatNumber,
    wr.Class,
    wr.BookingDate,
    get_ticket_status()                      AS Status,
    get_ticket_price(wr.Class, wr.AirlineId) AS TicketPrice,
    wr.PassengerId,
    la.LuggageId
FROM with_rownum wr
LEFT JOIN tmp_luggage_assignment la ON la.rn = wr.rn;

DROP TABLE IF EXISTS tmp_luggage_assignment;
DROP FUNCTION IF EXISTS get_ticket_class(bigint);
DROP FUNCTION IF EXISTS get_ticket_price(varchar, bigint);
DROP FUNCTION IF EXISTS get_ticket_status();
DROP FUNCTION IF EXISTS get_booking_date(timestamp);

COMMIT;



-- ===== BoardingPass =====

TRUNCATE TABLE BoardingPass CASCADE;
ROLLBACK;

ALTER TABLE BoardingPass
ALTER COLUMN Class TYPE varchar(20);

BEGIN;
CREATE OR REPLACE FUNCTION bp_get_status()
RETURNS varchar(20) AS $$
DECLARE r float := random();
BEGIN
    IF    r < 0.10 THEN RETURN 'Issued';
    ELSIF r < 0.22 THEN RETURN 'Scanned';
    ELSIF r < 0.77 THEN RETURN 'Boarded';
    ELSIF r < 0.90 THEN RETURN 'Cancelled';
    ELSE               RETURN 'Expired';
    END IF;
END;
$$ LANGUAGE plpgsql;

INSERT INTO BoardingPass (
    TicketId,
    BoardingTime,
    Class,
    Status,
    PrintedAt,
    GateId,
    LuggageId
)
WITH

eligible_flights AS (
    SELECT
        f.FlightId,
        f.OriginAirportId
    FROM Flights f
    WHERE f.OriginAirportId IN (1, 2, 3, 5, 8, 17)
),

flight_departure AS (
    SELECT
        ef.FlightId,
        d.GateId,
        d.DepartureTime
    FROM eligible_flights ef
    INNER JOIN Departures d ON d.FlightId = ef.FlightId
),

eligible_tickets AS (
    SELECT
        t.TicketId,
        t.FlightId,
        t.Class,
        t.LuggageId
    FROM Ticket t
    WHERE t.FlightId IN (SELECT FlightId FROM eligible_flights)
),

tickets_with_gate AS (
    SELECT
        et.TicketId,
        et.Class,
        et.LuggageId,
        fd.GateId,
        fd.DepartureTime
    FROM eligible_tickets et
    INNER JOIN flight_departure fd ON fd.FlightId = et.FlightId
),

tickets_with_times AS (
    SELECT
        twg.TicketId,
        twg.Class,
        twg.LuggageId,
        twg.GateId,
        twg.DepartureTime
            - (floor(random() * 41 + 20)::int || ' minutes')::interval
            AS BoardingTime
    FROM tickets_with_gate twg
)

SELECT
    twt.TicketId,
    twt.BoardingTime,
    twt.Class,
    bp_get_status() AS Status,
    twt.BoardingTime
        - (floor(random() * 211 + 30)::int || ' minutes')::interval
        AS PrintedAt,
    twt.GateId,
    twt.LuggageId
FROM tickets_with_times twt;

DROP FUNCTION IF EXISTS bp_get_status();

COMMIT;


-- ===== SecurityEmployee =====

INSERT INTO SecurityEmployee (EmployeeId)
SELECT EmployeeId
FROM Employee
WHERE Position IN ('Security', 'Security Guard');


-- ===== GateEmployee =====

INSERT INTO GateEmployee (EmployeeId)
SELECT EmployeeId
FROM Employee
WHERE Position = 'Gate Employee';


-- ===== SecurityCheck  =====

BEGIN;
CREATE OR REPLACE FUNCTION sc_get_terminal(p_gate_id bigint)
RETURNS int AS $$
BEGIN
    RETURN CASE
        WHEN p_gate_id BETWEEN  1 AND  5 THEN 1
        WHEN p_gate_id BETWEEN  6 AND 11 THEN 2
        WHEN p_gate_id BETWEEN 12 AND 15 THEN 3
        WHEN p_gate_id BETWEEN 16 AND 26 THEN 4
        WHEN p_gate_id BETWEEN 27 AND 34 THEN 5
        WHEN p_gate_id BETWEEN 35 AND 40 THEN 6
        WHEN p_gate_id BETWEEN 41 AND 54 THEN 7
        WHEN p_gate_id BETWEEN 55 AND 61 THEN 8
        WHEN p_gate_id BETWEEN 62 AND 67 THEN 9
        WHEN p_gate_id BETWEEN 68 AND 74 THEN 10
        WHEN p_gate_id BETWEEN 75 AND 81 THEN 11
        ELSE 1
    END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION sc_get_status(p_ticket_status varchar)
RETURNS varchar(20) AS $$
BEGIN
    IF p_ticket_status IN ('Cancelled', 'Refunded') THEN
        RETURN 'Failed';
    END IF;
    IF random() < 0.75 THEN
        RETURN 'Passed';
    ELSE
        RETURN 'In Progress';
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sc_get_notes(p_status varchar, p_type varchar)
RETURNS text AS $$
DECLARE r float := random();
BEGIN
    IF r < 0.30 THEN RETURN NULL; END IF;

    RETURN CASE p_status
        WHEN 'Passed' THEN
            CASE floor(random() * 3)::int
                WHEN 0 THEN 'All clear, no issues detected.'
                WHEN 1 THEN 'Standard check completed successfully.'
                ELSE        'Passenger and ' || lower(p_type) || ' check passed.'
            END
        WHEN 'Failed' THEN
            CASE floor(random() * 3)::int
                WHEN 0 THEN 'Ticket cancelled or refunded. Access denied.'
                WHEN 1 THEN 'Security check failed. Passenger flagged.'
                ELSE        'Failed verification. Refer to supervisor.'
            END
        WHEN 'In Progress' THEN
            CASE floor(random() * 2)::int
                WHEN 0 THEN 'Check currently in progress.'
                ELSE        'Awaiting secondary verification.'
            END
        ELSE NULL
    END;
END;
$$ LANGUAGE plpgsql;

INSERT INTO SecurityCheck (
    CheckTime,
    CheckType,
    TerminalId,
    Status,
    Notes,
    PassengerId
)
WITH

base_data AS (
    SELECT
        bp.TicketId,
        bp.BoardingTime,
        bp.GateId,
        t.PassengerId,
        t.LuggageId,
        t.Status                                AS ticket_status,
        sc_get_terminal(bp.GateId)              AS TerminalId,
        bp.BoardingTime
            - (floor(random() * 61 + 60)::int || ' minutes')::interval
            AS CheckTime
    FROM BoardingPass bp
    INNER JOIN Ticket t ON t.TicketId = bp.TicketId
),

mandatory_passenger AS (
    SELECT
        CheckTime,
        'Passenger'::varchar(20)                AS CheckType,
        TerminalId,
        sc_get_status(ticket_status)            AS Status,
        PassengerId,
        ticket_status
    FROM base_data
),

mandatory_document AS (
    SELECT
        CheckTime - '5 minutes'::interval       AS CheckTime,
        'Document'::varchar(20)                 AS CheckType,
        TerminalId,
        sc_get_status(ticket_status)            AS Status,
        PassengerId,
        ticket_status
    FROM base_data
),

baggage_check AS (
    SELECT
        CheckTime - '10 minutes'::interval      AS CheckTime,
        'Baggage'::varchar(20)                  AS CheckType,
        TerminalId,
        sc_get_status(ticket_status)            AS Status,
        PassengerId,
        ticket_status
    FROM base_data
    WHERE LuggageId IS NOT NULL
),

random_check AS (
    SELECT
        CheckTime - '15 minutes'::interval      AS CheckTime,
        'Random'::varchar(20)                   AS CheckType,
        TerminalId,
        sc_get_status(ticket_status)            AS Status,
        PassengerId,
        ticket_status
    FROM base_data
    WHERE random() < 0.175
),

fullbody_check AS (
    SELECT
        CheckTime - '20 minutes'::interval      AS CheckTime,
        'Full Body'::varchar(20)                AS CheckType,
        TerminalId,
        sc_get_status(ticket_status)            AS Status,
        PassengerId,
        ticket_status
    FROM base_data
    WHERE random() < 0.03
),

all_checks AS (
    SELECT * FROM mandatory_passenger
    UNION ALL
    SELECT * FROM mandatory_document
    UNION ALL
    SELECT * FROM baggage_check
    UNION ALL
    SELECT * FROM random_check
    UNION ALL
    SELECT * FROM fullbody_check
),

in_progress_followup AS (
    SELECT
        CheckTime + (floor(random() * 2 + 1)::int || ' minutes')::interval  AS CheckTime,
        CheckType,
        TerminalId,
        'Passed'::varchar(20)                   AS Status,
        PassengerId,
        ticket_status
    FROM all_checks
    WHERE Status = 'In Progress'
),

final_checks AS (
    SELECT * FROM all_checks
    UNION ALL
    SELECT * FROM in_progress_followup
)

SELECT
    fc.CheckTime,
    fc.CheckType,
    fc.TerminalId,
    fc.Status,
    sc_get_notes(fc.Status, fc.CheckType)       AS Notes,
    fc.PassengerId
FROM final_checks fc;

DROP FUNCTION IF EXISTS sc_get_terminal(bigint);
DROP FUNCTION IF EXISTS sc_get_status(varchar);
DROP FUNCTION IF EXISTS sc_get_notes(varchar, varchar);

COMMIT;


-- ===== WorksOnSecurityCheck =====

INSERT INTO WorksOnSecurityCheck (SecurityEmployeeEmployeeId, SecurityCheckCheckId)
WITH
RecentChecks AS (
    SELECT
        sc.CheckId,
        t.AirportId::integer AS AirportId
    FROM SecurityCheck sc
    JOIN Terminal t ON t.TerminalId = sc.TerminalId
    ORDER BY sc.CheckTime DESC
    LIMIT 500000
),
SecEmpByAirport AS (
    SELECT
        se.EmployeeId,
        e.AirportId
    FROM SecurityEmployee se
    JOIN Employee e ON e.EmployeeId = se.EmployeeId
    WHERE e.AirportId IS NOT NULL
),
Assigned AS (
    SELECT
        rc.CheckId,
        sea.EmployeeId,
        ROW_NUMBER() OVER (
            PARTITION BY rc.CheckId
            ORDER BY RANDOM()
        ) AS emp_rn
    FROM RecentChecks rc
    JOIN SecEmpByAirport sea ON sea.AirportId = rc.AirportId
)
SELECT
    EmployeeId AS SecurityEmployeeEmployeeId,
    CheckId    AS SecurityCheckCheckId
FROM Assigned
WHERE emp_rn = 1;