DatabaseCreation: console_2.sql

File console_2.sql, 29.7 KB (added by 231146, 4 hours ago)
Line 
1-- ===== Airport =====
2
3INSERT INTO Airport (AirportName, IataCode, Location, ContactEmail, ContactPhone, OperationalStatus)
4VALUES
5('Skopje International Airport', 'SKP', 'Skopje, North Macedonia', 'info.skp@airports.mk', 3892402001, 'Open'),
6('Ohrid St. Paul the Apostle Airport', 'OHD', 'Ohrid, North Macedonia', 'info.ohd@airports.mk', 3892402002, 'Open'),
7
8('Belgrade Nikola Tesla Airport', 'BEG', 'Belgrade, Serbia', 'info.beg@airports.rs', 381112090001, 'Open'),
9('Niš Constantine the Great Airport', 'INI', 'Niš, Serbia', 'info.ini@airports.rs', 381184550001, 'Open'),
10
11('Zagreb Franjo Tuđman Airport', 'ZAG', 'Zagreb, Croatia', 'info.zag@airports.hr', 385145620001, 'Open'),
12('Split Airport', 'SPU', 'Split, Croatia', 'info.spu@airports.hr', 385215550001, 'Open'),
13('Dubrovnik Airport', 'DBV', 'Dubrovnik, Croatia', 'info.dbv@airports.hr', 385207700001, 'Open'),
14
15('Sarajevo International Airport', 'SJJ', 'Sarajevo, Bosnia and Herzegovina', 'info.sjj@airports.ba', 38733569001, 'Open'),
16('Banja Luka Airport', 'BNX', 'Banja Luka, Bosnia and Herzegovina', 'info.bnx@airports.ba', 38751230001, 'Open'),
17
18('Podgorica Airport', 'TGD', 'Podgorica, Montenegro', 'info.tgd@airports.me', 38220444001, 'Open'),
19('Tivat Airport', 'TIV', 'Tivat, Montenegro', 'info.tiv@airports.me', 38232670001, 'Open'),
20
21('Tirana International Airport', 'TIA', 'Tirana, Albania', 'info.tia@airports.al', 35542381001, 'Open'),
22
23('Pristina International Airport', 'PRN', 'Pristina, Kosovo', 'info.prn@airports.rks', 38338502001, 'Open'),
24
25('Athens International Airport', 'ATH', 'Athens, Greece', 'info.ath@aia.gr', 30210353001, 'Open'),
26('Thessaloniki Airport', 'SKG', 'Thessaloniki, Greece', 'info.skg@aia.gr', 30231047001, 'Open'),
27('Heraklion Airport', 'HER', 'Crete, Greece', 'info.her@aia.gr', 30281024001, 'Open'),
28
29('Sofia Airport', 'SOF', 'Sofia, Bulgaria', 'info.sof@sofia-airport.bg', 35929372001, 'Open'),
30('Varna Airport', 'VAR', 'Varna, Bulgaria', 'info.var@varna-airport.bg', 35952602001, 'Open'),
31('Burgas Airport', 'BOJ', 'Burgas, Bulgaria', 'info.boj@burgas-airport.bg', 35956870201, 'Open'),
32
33('Henri Coandă Airport', 'OTP', 'Bucharest, Romania', 'info.otp@bucharestairports.ro', 40212004001, 'Open'),
34('Cluj-Napoca Airport', 'CLJ', 'Cluj-Napoca, Romania', 'info.clj@airportcluj.ro', 40264500001, 'Open'),
35('Timișoara Airport', 'TSR', 'Timișoara, Romania', 'info.tsr@aerotim.ro', 40256400001, 'Open'),
36
37('Istanbul Airport', 'IST', 'Istanbul, Turkey', 'info.ist@igairport.com', 90212444001, 'Open'),
38('Sabiha Gökçen Airport', 'SAW', 'Istanbul, Turkey', 'info.saw@sgairport.com', 90216589001, 'Open'),
39('Ankara Esenboğa Airport', 'ESB', 'Ankara, Turkey', 'info.esb@dhmi.gov.tr', 90312300001, 'Open'),
40
41('Frankfurt Airport', 'FRA', 'Frankfurt, Germany', 'info.fra@fraport.de', 49696900001, 'Open'),
42('Munich Airport', 'MUC', 'Munich, Germany', 'info.muc@munich-airport.de', 49899750001, 'Open'),
43('Berlin Brandenburg Airport', 'BER', 'Berlin, Germany', 'info.ber@berlin-airport.de', 49306091001, 'Open'),
44('Hamburg Airport', 'HAM', 'Hamburg, Germany', 'info.ham@hamburg-airport.de', 49405095001, 'Open'),
45
46('Paris Charles de Gaulle Airport', 'CDG', 'Paris, France', 'info.cdg@adp.fr', 33170363951, 'Open'),
47('Nice Côte d''Azur Airport', 'NCE', 'Nice, France', 'info.nce@cotedazur.aeroport.fr', 33493230001, 'Open'),
48('Lyon Saint-Exupéry Airport', 'LYS', 'Lyon, France', 'info.lys@lyonaeroports.com', 33472226001, 'Open'),
49
50('Rome Fiumicino Airport', 'FCO', 'Rome, Italy', 'info.fco@adr.it', 39066595001, 'Open'),
51('Milan Malpensa Airport', 'MXP', 'Milan, Italy', 'info.mxp@sea-aeroportimilano.it', 39022323001, 'Open'),
52('Venice Marco Polo Airport', 'VCE', 'Venice, Italy', 'info.vce@veniceairport.it', 39041260001, 'Open'),
53('Naples International Airport', 'NAP', 'Naples, Italy', 'info.nap@gesac.it', 390817896001, 'Open'),
54('Bologna Guglielmo Marconi Airport', 'BLQ', 'Bologna, Italy', 'info.blq@bologna-airport.it', 39051447001, 'Open'),
55('Turin Airport', 'TRN', 'Turin, Italy', 'info.trn@sagat.it', 390114679001, 'Open'),
56('Palermo Airport', 'PMO', 'Palermo, Sicily, Italy', 'info.pmo@gesap.it', 39091706001, 'Open'),
57('Catania Airport', 'CTA', 'Catania, Sicily, Italy', 'info.cta@aeroporto.catania.it', 390957239001, 'Open'),
58('Cagliari Airport', 'CAG', 'Cagliari, Sardinia, Italy', 'info.cag@sogaer.it', 39070460001, 'Open'),
59('Olbia Airport', 'OLB', 'Olbia, Sardinia, Italy', 'info.olb@geasar.it', 390789659001, 'Open'),
60
61('Madrid Barajas Airport', 'MAD', 'Madrid, Spain', 'info.mad@aena.es', 34913211001, 'Open'),
62('Barcelona El Prat Airport', 'BCN', 'Barcelona, Spain', 'info.bcn@aena.es', 34913211002, 'Open'),
63('Valencia Airport', 'VLC', 'Valencia, Spain', 'info.vlc@aena.es', 34913211003, 'Open'),
64
65('London Heathrow Airport', 'LHR', 'London, United Kingdom', 'info.lhr@heathrow.com', 442087591001, 'Open'),
66('London Gatwick Airport', 'LGW', 'London, United Kingdom', 'info.lgw@gatwickairport.com', 442087100001, 'Open'),
67('Manchester Airport', 'MAN', 'Manchester, United Kingdom', 'info.man@manairport.co.uk', 441619490001, 'Open'),
68
69('Amsterdam Schiphol Airport', 'AMS', 'Amsterdam, Netherlands', 'info.ams@schiphol.nl', 31207940801, 'Open'),
70('Brussels Airport', 'BRU', 'Brussels, Belgium', 'info.bru@brusselsairport.be', 3227534001, 'Open'),
71('Eindhoven Airport', 'EIN', 'Eindhoven, Netherlands', 'info.ein@eindhovenairport.nl', 31402540001, 'Open'),
72
73('Vienna International Airport', 'VIE', 'Vienna, Austria', 'info.vie@viennaairport.at', 43170070001, 'Open'),
74('Zurich Airport', 'ZRH', 'Zurich, Switzerland', 'info.zrh@zurich-airport.ch', 41438161601, 'Open'),
75('Geneva Airport', 'GVA', 'Geneva, Switzerland', 'info.gva@gva.ch', 41227170001, 'Open'),
76
77('Warsaw Chopin Airport', 'WAW', 'Warsaw, Poland', 'info.waw@lotnisko-chopina.pl', 48226500001, 'Open'),
78('Prague Václav Havel Airport', 'PRG', 'Prague, Czech Republic', 'info.prg@prg.aero', 420220111001, 'Open'),
79('Budapest Ferenc Liszt Airport', 'BUD', 'Budapest, Hungary', 'info.bud@bud.hu', 36129670001, 'Open'),
80
81-- Middle East
82('Dubai International Airport', 'DXB', 'Dubai, UAE', 'info.dxb@dubaiairports.ae', 97142245001, 'Open'),
83('Doha Hamad International Airport', 'DOH', 'Doha, Qatar', 'info.doh@hamad.qa', 97440106001, 'Open'),
84('Abu Dhabi International Airport', 'AUH', 'Abu Dhabi, UAE', 'info.auh@adac.ae', 97125055001, 'Open'),
85
86-- US North America
87('John F. Kennedy International Airport', 'JFK', 'New York, USA', 'info.jfk@panynj.gov', 17182444001, 'Open'),
88('Toronto Pearson International Airport', 'YYZ', 'Toronto, Canada', 'info.yyz@torontopearson.ca', 14162403001, 'Open'),
89
90-- Asia
91('Tokyo Haneda Airport', 'HND', 'Tokyo, Japan', 'info.hnd@tokyo-airport.jp', 81357577001, 'Open'),
92('Beijing Capital International Airport', 'PEK', 'Beijing, China', 'info.pek@bcia.com.cn', 86109699001, 'Open'),
93('Singapore Changi Airport', 'SIN', 'Singapore', 'info.sin@changiairport.com', 65659568001, 'Open');
94
95
96-- ===== Taxiway =====
97
98INSERT INTO Taxiway (TaxiwayId, TaxiwayName, AirportId, TaxiwayLength, Direction, Status) VALUES
99(1, 'A', 1, 1780, 'Northwest', 'Open'),
100(2, 'B', 1, 883, 'North', 'Restricted'),
101(3, 'C', 1, 532, 'Southwest', 'Closed'),
102(4, 'D', 1, 1317, 'Southeast', 'Open'),
103(5, 'A', 2, 1530, 'West', 'Open'),
104(6, 'A', 3, 1266, 'Northeast', 'Restricted'),
105(7, 'B', 3, 232, 'Southwest', 'Open'),
106(8, 'C', 3, 289, 'Northeast', 'Closed'),
107(9, 'D', 3, 984, 'South', 'Available'),
108(10, 'E', 3, 1373, 'East', 'Open'),
109(11, 'F', 3, 630, 'East', 'Blocked'),
110(12, 'G', 3, 791, 'East', 'Occupied'),
111(13, 'A', 5, 849, 'East', 'Available'),
112(14, 'B', 5, 1512, 'South', 'Available'),
113(15, 'C', 5, 203, 'East', 'Open'),
114(16, 'D', 5, 604, 'West', 'Blocked'),
115(17, 'E', 5, 795, 'South', 'Open'),
116(18, 'F', 5, 1650, 'Northeast', 'Open'),
117(19, 'A', 8, 1614, 'East', 'Available'),
118(20, 'B', 8, 701, 'Southwest', 'Restricted'),
119(21, 'C', 8, 1531, 'South', 'Restricted'),
120(22, 'D', 8, 755, 'North', 'Occupied'),
121(23, 'E', 8, 332, 'South', 'Inactive'),
122(24, 'F', 8, 1899, 'Northwest', 'Open'),
123(25, 'A', 17, 520, 'Northwest', 'Available'),
124(26, 'B', 17, 1729, 'Southwest', 'Available'),
125(27, 'C', 17, 1331, 'Southeast', 'Open'),
126(28, 'D', 17, 1650, 'South', 'Available'),
127(29, 'E', 17, 1023, 'Northeast', 'Restricted'),
128(30, 'F', 17, 1701, 'Northeast', 'Available');
129
130
131-- ===== Runway =====
132
133INSERT INTO Runway (RunwayName, AirportId, RunwayLength, RunwayType, Status) VALUES
134('16/34', 1, 2950, 'Asphalt', 'Open'),
135('11/29', 2, 2548, 'Asphalt', 'Open'),
136('12L/30R',3, 3400, 'Asphalt', 'Open'),
137('12R/30L',3, 3500, 'Asphalt', 'Open'),
138('04/22', 5, 3252, 'Asphalt', 'Open'),
139('11/29', 8, 2700, 'Asphalt', 'Open'),
140('09/27', 17,3600, 'Asphalt', 'Open');
141
142
143-- ===== Terminal =====
144
145INSERT INTO Terminal (TerminalName, TerminalCode, AirportId, TerminalStatus, Capacity)
146VALUES
147('Terminal A', 'SKP-A', 1, 'Open', 1200000),
148('Terminal B', 'SKP-B', 1, 'Under Maintenance', 800000);
149
150INSERT INTO Terminal (TerminalName, TerminalCode, AirportId, TerminalStatus, Capacity)
151VALUES
152('Main', 'OHD-T1', 2, 'Open', 30000);
153
154INSERT INTO Terminal (TerminalName, TerminalCode, AirportId, TerminalStatus, Capacity)
155VALUES
156('Terminal 1', 'BEG-T1', 3, 'Closed', 5000000),
157('Terminal 2', 'BEG-T2', 3, 'Open', 7000000),
158('Terminal 3', 'BEG-T3', 3, 'Open', 4000000);
159
160INSERT INTO Terminal (TerminalName, TerminalCode, AirportId, TerminalStatus, Capacity)
161VALUES
162('Terminal A', 'ZAG-PAX', 5, 'Open', 3500000),
163('Terminal B', 'ZAG-CGO', 5, 'Restricted', 1500000);
164
165INSERT INTO Terminal (TerminalName, TerminalCode, AirportId, TerminalStatus, Capacity)
166VALUES
167('Main', 'SJJ-T1', 8, 'Open', 2000000);
168
169INSERT INTO Terminal (TerminalName, TerminalCode, AirportId, TerminalStatus, Capacity)
170VALUES
171('Terminal 1', 'SOF-T1', 17, 'Closed', 3000000),
172('Terminal 2', 'SOF-T2', 17, 'Open', 5000000);
173
174
175-- ===== Gate =====
176
177INSERT INTO Gate (GateCode, TerminalId, Status) VALUES
178
179-- Skopje
180(101, 1, 'Open'),
181(102, 1, 'Open'),
182(103, 1, 'Open'),
183(104, 1, 'Open'),
184(105, 1, 'Open'),
185
186(201, 2, 'Open'),
187(202, 2, 'Open'),
188(203, 2, 'Open'),
189(204, 2, 'Open'),
190(205, 2, 'Open'),
191(206, 2, 'Open'),
192
193-- Ohrid
194(1, 3, 'Open'),
195(2, 3, 'Open'),
196(3, 3, 'Open'),
197(4, 3, 'Open'),
198
199-- Belgrad
200(1, 4, 'Open'),
201(2, 4, 'Open'),
202(3, 4, 'Open'),
203(4, 4, 'Open'),
204(5, 4, 'Open'),
205(6, 4, 'Open'),
206(7, 4, 'Open'),
207(8, 4, 'Open'),
208(9, 4, 'Open'),
209(10, 4, 'Open'),
210(11, 4, 'Open'),
211
212(1, 5, 'Open'),
213(2, 5, 'Open'),
214(3, 5, 'Open'),
215(4, 5, 'Open'),
216(5, 5, 'Open'),
217(6, 5, 'Open'),
218(7, 5, 'Open'),
219(8, 5, 'Open'),
220(9, 5, 'Open'),
221(10, 5, 'Open'),
222(11, 5, 'Open'),
223(12, 5, 'Open'),
224(13, 5, 'Open'),
225(14, 5, 'Open'),
226
227-- Zagreb
228(1, 7, 'Open'),
229(2, 7, 'Open'),
230(3, 7, 'Open'),
231(4, 7, 'Open'),
232(5, 7, 'Open'),
233(6, 7, 'Open'),
234(7, 7, 'Open'),
235(8, 7, 'Open'),
236(9, 7, 'Open'),
237(10, 7, 'Open'),
238(11, 7, 'Open'),
239(12, 7, 'Open'),
240(13, 7, 'Open'),
241(21, 7, 'Open'),
242(22, 7, 'Open'),
243(23, 7, 'Open'),
244(24, 7, 'Open'),
245(25, 7, 'Open'),
246(26, 7, 'Open'),
247(27, 7, 'Open'),
248(28, 7, 'Open'),
249
250-- Saraevo
251(1, 9, 'Open'),
252(2, 9, 'Open'),
253(3, 9, 'Open'),
254(4, 9, 'Open'),
255(5, 9, 'Open'),
256(6, 9, 'Open'),
257
258-- Sofia
259(1, 10, 'Open'),
260(2, 10, 'Open'),
261(3, 10, 'Open'),
262(4, 10, 'Open'),
263(5, 10, 'Open'),
264(6, 10, 'Open'),
265(7, 10, 'Open'),
266
267(1, 11, 'Open'),
268(5, 11, 'Open'),
269(6, 11, 'Open'),
270(7, 11, 'Open'),
271(8, 11, 'Open'),
272(9, 11, 'Open'),
273(10, 11, 'Open');
274
275
276-- ===== Hangar =====
277
278INSERT INTO Hangar (HangarName, AirportId, HangarCapacity, HangarType, Status) VALUES
279('Skopje-Main-H1', 1, 3, 'Maintenance', 'Open'),
280('Skopje-Cargo-H2', 1, 5, 'Storage', 'Open'),
281('Ohrid-General-H1', 2, 2, 'General', 'Open'),
282('Belgrade-Jat-Tech-1', 3, 12, 'Heavy Maintenance', 'Open'),
283('Belgrade-Jat-Tech-2', 3, 10, 'Heavy Maintenance', 'Under Maintenance'),
284('Belgrade-Cargo-A1', 3, 8, 'Storage', 'Full'),
285('Belgrade-VIP-H3', 3, 2, 'Private', 'Open'),
286('Zagreb-Croatia-H1', 5, 6, 'Maintenance', 'Open'),
287('Zagreb-Gov-H2', 5, 2, 'Government/VIP', 'Open'),
288('Zagreb-General-H3', 5, 4, 'Storage', 'Closed'),
289('Sarajevo-Main-H1', 8, 3, 'Maintenance', 'Open'),
290('Sarajevo-Private-H2', 8, 1, 'General', 'Open'),
291('Sofia-Lufthansa-T1', 17, 15, 'Heavy Maintenance', 'Open'),
292('Sofia-Lufthansa-T2', 17, 12, 'Heavy Maintenance', 'Open'),
293('Sofia-BulgariaAir-H1', 17, 5, 'Maintenance', 'Open'),
294('Sofia-General-H2', 17, 4, 'Storage', 'Under Maintenance'),
295('Sofia-Cargo-C1', 17, 10, 'Cargo Storage', 'Full');
296
297
298-- ===== MechanicEmployee =====
299
300INSERT INTO MechanicEmployee (EmployeeId)
301SELECT EmployeeId
302FROM Employee
303WHERE Position = 'Mechanic';
304
305
306-- ===== WorksOn =====
307
308INSERT INTO WorksOn (EmployeeId, MaintenanceId)
309SELECT DISTINCT
310 me.EmployeeId,
311 am.MaintenanceId
312FROM MechanicEmployee me
313INNER JOIN Employee e ON me.EmployeeId = e.EmployeeId
314CROSS JOIN LATERAL (
315 SELECT MaintenanceId, StartDate
316 FROM AircraftMaintenance
317 WHERE MaintenanceId BETWEEN
318 (me.EmployeeId * 1000) % 1000000 + 1
319 AND (me.EmployeeId * 1000) % 1000000 + 20000
320 AND StartDate >= e.HireDate
321 AND (e.QuitDate IS NULL OR StartDate <= e.QuitDate)
322 AND e.DateOfBirth + INTERVAL '18 years' <= StartDate
323 LIMIT 5000
324) am
325WHERE (SELECT COUNT(*) FROM WorksOn w WHERE w.EmployeeId = me.EmployeeId) < 10000
326LIMIT 2500000;
327
328
329-- ===== Departures =====
330
331INSERT INTO Departures (
332 FlightId,
333 GateId,
334 RunwayId,
335 Status,
336 DepartureTime,
337 DestinationAirportId,
338 TaxiwayId
339)
340SELECT
341 f.FlightId,
342
343 (SELECT g.GateId
344 FROM Gate g
345 JOIN Terminal t ON g.TerminalId = t.TerminalId
346 WHERE t.AirportId = f.OriginAirportId
347 ORDER BY random()
348 LIMIT 1),
349
350 (SELECT r.RunwayId
351 FROM Runway r
352 WHERE r.AirportId = f.OriginAirportId
353 ORDER BY random()
354 LIMIT 1),
355
356 CASE
357 WHEN f.OperatingStatus IN ('Scheduled','Go to gate','Boarding','Departed','Delayed','Cancelled','Diverted')
358 THEN f.OperatingStatus
359 WHEN f.OperatingStatus = 'En Route'
360 THEN 'Departed'
361 ELSE 'Scheduled'
362 END,
363
364 f.DepartureTime,
365 f.DestinationAirportId,
366
367 (SELECT t.TaxiwayId
368 FROM Taxiway t
369 WHERE t.AirportId = f.OriginAirportId
370 ORDER BY random()
371 LIMIT 1)
372
373FROM Flights f
374WHERE f.OriginAirportId IN (1,2,3,5,8,17);
375
376
377-- ===== Arrivals =====
378
379INSERT INTO Arrivals (
380 FlightId,
381 GateId,
382 RunwayId,
383 Status,
384 ArrivalTime,
385 OriginAirportId,
386 TaxiwayId
387)
388SELECT
389 f.FlightId,
390
391 (SELECT g.GateId
392 FROM Gate g
393 JOIN Terminal t ON g.TerminalId = t.TerminalId
394 WHERE t.AirportId = f.DestinationAirportId
395 ORDER BY random()
396 LIMIT 1),
397
398 (SELECT r.RunwayId
399 FROM Runway r
400 WHERE r.AirportId = f.DestinationAirportId
401 ORDER BY random()
402 LIMIT 1),
403
404 CASE
405 WHEN f.OperatingStatus IN ('Scheduled','En Route','Landed','Arrived','Delayed','Cancelled','Diverted')
406 THEN f.OperatingStatus
407 WHEN f.OperatingStatus = 'Departed'
408 THEN 'En Route'
409 ELSE 'Scheduled'
410 END,
411
412 f.ArrivalTime,
413 f.OriginAirportId,
414
415 (SELECT t.TaxiwayId
416 FROM Taxiway t
417 WHERE t.AirportId = f.DestinationAirportId
418 ORDER BY random()
419 LIMIT 1)
420
421FROM Flights f
422WHERE f.DestinationAirportId IN (1,2,3,5,8,17);
423
424
425-- ===== Ticket =====
426
427ALTER TABLE Ticket
428ADD CONSTRAINT uq_ticket_flight_passenger
429UNIQUE (FlightId, PassengerId);
430
431SELECT setval('ticket_ticketid_seq', 3671843);
432
433ROLLBACK;
434
435BEGIN;
436
437CREATE OR REPLACE FUNCTION get_ticket_class(p_airline_id bigint)
438RETURNS varchar(20) AS $$
439DECLARE
440 r float := random();
441BEGIN
442 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
443 RETURN 'Economy';
444
445 ELSIF p_airline_id = ANY(ARRAY[31,32,33,34,42,55,56,57,58,59,73,74]) THEN
446 IF r < 0.70 THEN RETURN 'Economy';
447 ELSE RETURN 'Business';
448 END IF;
449
450 ELSE
451 IF r < 0.55 THEN RETURN 'Economy';
452 ELSIF r < 0.70 THEN RETURN 'Premium Economy';
453 ELSIF r < 0.92 THEN RETURN 'Business';
454 ELSE RETURN 'First Class';
455 END IF;
456 END IF;
457END;
458$$ LANGUAGE plpgsql;
459
460CREATE OR REPLACE FUNCTION get_ticket_price(p_class varchar(20), p_airline_id bigint)
461RETURNS numeric AS $$
462DECLARE
463 lo numeric; hi numeric;
464 lcc_factor numeric := 0.55;
465 is_lcc boolean;
466BEGIN
467 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]);
468
469 CASE p_class
470 WHEN 'Economy' THEN lo := 35; hi := 450;
471 WHEN 'Premium Economy' THEN lo := 200; hi := 900;
472 WHEN 'Business' THEN lo := 400; hi := 3500;
473 WHEN 'First Class' THEN lo := 1200; hi := 8000;
474 ELSE lo := 35; hi := 450;
475 END CASE;
476
477 IF is_lcc THEN
478 lo := lo * lcc_factor;
479 hi := hi * lcc_factor;
480 END IF;
481
482 RETURN round((lo + random() * (hi - lo))::numeric, 2);
483END;
484$$ LANGUAGE plpgsql;
485
486CREATE OR REPLACE FUNCTION get_ticket_status()
487RETURNS varchar(20) AS $$
488DECLARE r float := random();
489BEGIN
490 IF r < 0.10 THEN RETURN 'Booked';
491 ELSIF r < 0.45 THEN RETURN 'Confirmed';
492 ELSIF r < 0.53 THEN RETURN 'Cancelled';
493 ELSIF r < 0.85 THEN RETURN 'Used';
494 ELSIF r < 0.93 THEN RETURN 'Refunded';
495 ELSE RETURN 'Pending';
496 END IF;
497END;
498$$ LANGUAGE plpgsql;
499
500CREATE OR REPLACE FUNCTION get_booking_date(p_base timestamp)
501RETURNS timestamp AS $$
502BEGIN
503 IF random() < 0.5 THEN
504 RETURN p_base - (floor(random() * 4 + 1)::int || ' days')::interval;
505 ELSE
506 RETURN p_base + (floor(random() * 23 + 1)::int || ' hours')::interval;
507 END IF;
508END;
509$$ LANGUAGE plpgsql;
510
511CREATE TEMP TABLE tmp_luggage_assignment AS
512SELECT
513 row_number() OVER (ORDER BY random()) AS rn,
514 l.LuggageId
515FROM
516 (SELECT LuggageId FROM Luggage ORDER BY random()) l
517LIMIT 13017666;
518
519CREATE INDEX ON tmp_luggage_assignment(rn);
520
521
522INSERT INTO Ticket (FlightId, SeatNumber, Class, BookingDate, Status, TicketPrice, PassengerId, LuggageId)
523WITH
524
525flight_seats AS (
526 SELECT
527 f.FlightId,
528 f.AirlineId,
529 f.DepartureTime,
530 s.seat_num,
531 (((s.seat_num - 1) / 6) + 1)::text ||
532 chr(65 + ((s.seat_num - 1) % 6)) AS SeatNumber
533 FROM (
534 SELECT *
535 FROM (
536 SELECT f.*
537 FROM Flights f
538 WHERE f.FlightId BETWEEN 71910 AND 498472
539 AND NOT EXISTS (
540 SELECT 1
541 FROM Ticket t
542 WHERE t.FlightId = f.FlightId
543 )
544 ORDER BY f.FlightId
545) f
546 WHERE FlightId BETWEEN 71910 AND 498472
547 ORDER BY FlightId
548) f
549CROSS JOIN generate_series(1, 50) AS s(seat_num)
550),
551
552with_class AS (
553 SELECT
554 fs.FlightId,
555 fs.AirlineId,
556 fs.DepartureTime,
557 fs.seat_num,
558 fs.SeatNumber,
559 get_ticket_class(fs.AirlineId) AS Class
560 FROM flight_seats fs
561),
562
563with_booking AS (
564 SELECT
565 wc.FlightId,
566 wc.AirlineId,
567 wc.DepartureTime,
568 wc.seat_num,
569 wc.SeatNumber,
570 wc.Class,
571 -- Base: одземи 7-180 дена
572 get_booking_date(
573 wc.DepartureTime - (floor(random() * 174 + 7)::int || ' days')::interval
574 ) AS BookingDate
575 FROM with_class wc
576),
577
578with_passenger AS (
579 SELECT
580 wb.*,
581 ((wb.FlightId * 1000 + wb.seat_num) % 995623 + 1)::bigint AS PassengerId
582 FROM with_booking wb
583),
584
585with_rownum AS (
586 SELECT
587 wp.*,
588 row_number() OVER (ORDER BY wp.FlightId, wp.seat_num) AS rn
589 FROM with_passenger wp
590)
591
592SELECT
593 wr.FlightId,
594 wr.SeatNumber,
595 wr.Class,
596 wr.BookingDate,
597 get_ticket_status() AS Status,
598 get_ticket_price(wr.Class, wr.AirlineId) AS TicketPrice,
599 wr.PassengerId,
600 la.LuggageId
601FROM with_rownum wr
602LEFT JOIN tmp_luggage_assignment la ON la.rn = wr.rn;
603
604DROP TABLE IF EXISTS tmp_luggage_assignment;
605DROP FUNCTION IF EXISTS get_ticket_class(bigint);
606DROP FUNCTION IF EXISTS get_ticket_price(varchar, bigint);
607DROP FUNCTION IF EXISTS get_ticket_status();
608DROP FUNCTION IF EXISTS get_booking_date(timestamp);
609
610COMMIT;
611
612
613
614-- ===== BoardingPass =====
615
616TRUNCATE TABLE BoardingPass CASCADE;
617ROLLBACK;
618
619ALTER TABLE BoardingPass
620ALTER COLUMN Class TYPE varchar(20);
621
622BEGIN;
623CREATE OR REPLACE FUNCTION bp_get_status()
624RETURNS varchar(20) AS $$
625DECLARE r float := random();
626BEGIN
627 IF r < 0.10 THEN RETURN 'Issued';
628 ELSIF r < 0.22 THEN RETURN 'Scanned';
629 ELSIF r < 0.77 THEN RETURN 'Boarded';
630 ELSIF r < 0.90 THEN RETURN 'Cancelled';
631 ELSE RETURN 'Expired';
632 END IF;
633END;
634$$ LANGUAGE plpgsql;
635
636INSERT INTO BoardingPass (
637 TicketId,
638 BoardingTime,
639 Class,
640 Status,
641 PrintedAt,
642 GateId,
643 LuggageId
644)
645WITH
646
647eligible_flights AS (
648 SELECT
649 f.FlightId,
650 f.OriginAirportId
651 FROM Flights f
652 WHERE f.OriginAirportId IN (1, 2, 3, 5, 8, 17)
653),
654
655flight_departure AS (
656 SELECT
657 ef.FlightId,
658 d.GateId,
659 d.DepartureTime
660 FROM eligible_flights ef
661 INNER JOIN Departures d ON d.FlightId = ef.FlightId
662),
663
664eligible_tickets AS (
665 SELECT
666 t.TicketId,
667 t.FlightId,
668 t.Class,
669 t.LuggageId
670 FROM Ticket t
671 WHERE t.FlightId IN (SELECT FlightId FROM eligible_flights)
672),
673
674tickets_with_gate AS (
675 SELECT
676 et.TicketId,
677 et.Class,
678 et.LuggageId,
679 fd.GateId,
680 fd.DepartureTime
681 FROM eligible_tickets et
682 INNER JOIN flight_departure fd ON fd.FlightId = et.FlightId
683),
684
685tickets_with_times AS (
686 SELECT
687 twg.TicketId,
688 twg.Class,
689 twg.LuggageId,
690 twg.GateId,
691 twg.DepartureTime
692 - (floor(random() * 41 + 20)::int || ' minutes')::interval
693 AS BoardingTime
694 FROM tickets_with_gate twg
695)
696
697SELECT
698 twt.TicketId,
699 twt.BoardingTime,
700 twt.Class,
701 bp_get_status() AS Status,
702 twt.BoardingTime
703 - (floor(random() * 211 + 30)::int || ' minutes')::interval
704 AS PrintedAt,
705 twt.GateId,
706 twt.LuggageId
707FROM tickets_with_times twt;
708
709DROP FUNCTION IF EXISTS bp_get_status();
710
711COMMIT;
712
713
714-- ===== SecurityEmployee =====
715
716INSERT INTO SecurityEmployee (EmployeeId)
717SELECT EmployeeId
718FROM Employee
719WHERE Position IN ('Security', 'Security Guard');
720
721
722-- ===== GateEmployee =====
723
724INSERT INTO GateEmployee (EmployeeId)
725SELECT EmployeeId
726FROM Employee
727WHERE Position = 'Gate Employee';
728
729
730-- ===== SecurityCheck =====
731
732BEGIN;
733CREATE OR REPLACE FUNCTION sc_get_terminal(p_gate_id bigint)
734RETURNS int AS $$
735BEGIN
736 RETURN CASE
737 WHEN p_gate_id BETWEEN 1 AND 5 THEN 1
738 WHEN p_gate_id BETWEEN 6 AND 11 THEN 2
739 WHEN p_gate_id BETWEEN 12 AND 15 THEN 3
740 WHEN p_gate_id BETWEEN 16 AND 26 THEN 4
741 WHEN p_gate_id BETWEEN 27 AND 34 THEN 5
742 WHEN p_gate_id BETWEEN 35 AND 40 THEN 6
743 WHEN p_gate_id BETWEEN 41 AND 54 THEN 7
744 WHEN p_gate_id BETWEEN 55 AND 61 THEN 8
745 WHEN p_gate_id BETWEEN 62 AND 67 THEN 9
746 WHEN p_gate_id BETWEEN 68 AND 74 THEN 10
747 WHEN p_gate_id BETWEEN 75 AND 81 THEN 11
748 ELSE 1
749 END;
750END;
751$$ LANGUAGE plpgsql IMMUTABLE;
752
753CREATE OR REPLACE FUNCTION sc_get_status(p_ticket_status varchar)
754RETURNS varchar(20) AS $$
755BEGIN
756 IF p_ticket_status IN ('Cancelled', 'Refunded') THEN
757 RETURN 'Failed';
758 END IF;
759 IF random() < 0.75 THEN
760 RETURN 'Passed';
761 ELSE
762 RETURN 'In Progress';
763 END IF;
764END;
765$$ LANGUAGE plpgsql;
766
767CREATE OR REPLACE FUNCTION sc_get_notes(p_status varchar, p_type varchar)
768RETURNS text AS $$
769DECLARE r float := random();
770BEGIN
771 IF r < 0.30 THEN RETURN NULL; END IF;
772
773 RETURN CASE p_status
774 WHEN 'Passed' THEN
775 CASE floor(random() * 3)::int
776 WHEN 0 THEN 'All clear, no issues detected.'
777 WHEN 1 THEN 'Standard check completed successfully.'
778 ELSE 'Passenger and ' || lower(p_type) || ' check passed.'
779 END
780 WHEN 'Failed' THEN
781 CASE floor(random() * 3)::int
782 WHEN 0 THEN 'Ticket cancelled or refunded. Access denied.'
783 WHEN 1 THEN 'Security check failed. Passenger flagged.'
784 ELSE 'Failed verification. Refer to supervisor.'
785 END
786 WHEN 'In Progress' THEN
787 CASE floor(random() * 2)::int
788 WHEN 0 THEN 'Check currently in progress.'
789 ELSE 'Awaiting secondary verification.'
790 END
791 ELSE NULL
792 END;
793END;
794$$ LANGUAGE plpgsql;
795
796INSERT INTO SecurityCheck (
797 CheckTime,
798 CheckType,
799 TerminalId,
800 Status,
801 Notes,
802 PassengerId
803)
804WITH
805
806base_data AS (
807 SELECT
808 bp.TicketId,
809 bp.BoardingTime,
810 bp.GateId,
811 t.PassengerId,
812 t.LuggageId,
813 t.Status AS ticket_status,
814 sc_get_terminal(bp.GateId) AS TerminalId,
815 bp.BoardingTime
816 - (floor(random() * 61 + 60)::int || ' minutes')::interval
817 AS CheckTime
818 FROM BoardingPass bp
819 INNER JOIN Ticket t ON t.TicketId = bp.TicketId
820),
821
822mandatory_passenger AS (
823 SELECT
824 CheckTime,
825 'Passenger'::varchar(20) AS CheckType,
826 TerminalId,
827 sc_get_status(ticket_status) AS Status,
828 PassengerId,
829 ticket_status
830 FROM base_data
831),
832
833mandatory_document AS (
834 SELECT
835 CheckTime - '5 minutes'::interval AS CheckTime,
836 'Document'::varchar(20) AS CheckType,
837 TerminalId,
838 sc_get_status(ticket_status) AS Status,
839 PassengerId,
840 ticket_status
841 FROM base_data
842),
843
844baggage_check AS (
845 SELECT
846 CheckTime - '10 minutes'::interval AS CheckTime,
847 'Baggage'::varchar(20) AS CheckType,
848 TerminalId,
849 sc_get_status(ticket_status) AS Status,
850 PassengerId,
851 ticket_status
852 FROM base_data
853 WHERE LuggageId IS NOT NULL
854),
855
856random_check AS (
857 SELECT
858 CheckTime - '15 minutes'::interval AS CheckTime,
859 'Random'::varchar(20) AS CheckType,
860 TerminalId,
861 sc_get_status(ticket_status) AS Status,
862 PassengerId,
863 ticket_status
864 FROM base_data
865 WHERE random() < 0.175
866),
867
868fullbody_check AS (
869 SELECT
870 CheckTime - '20 minutes'::interval AS CheckTime,
871 'Full Body'::varchar(20) AS CheckType,
872 TerminalId,
873 sc_get_status(ticket_status) AS Status,
874 PassengerId,
875 ticket_status
876 FROM base_data
877 WHERE random() < 0.03
878),
879
880all_checks AS (
881 SELECT * FROM mandatory_passenger
882 UNION ALL
883 SELECT * FROM mandatory_document
884 UNION ALL
885 SELECT * FROM baggage_check
886 UNION ALL
887 SELECT * FROM random_check
888 UNION ALL
889 SELECT * FROM fullbody_check
890),
891
892in_progress_followup AS (
893 SELECT
894 CheckTime + (floor(random() * 2 + 1)::int || ' minutes')::interval AS CheckTime,
895 CheckType,
896 TerminalId,
897 'Passed'::varchar(20) AS Status,
898 PassengerId,
899 ticket_status
900 FROM all_checks
901 WHERE Status = 'In Progress'
902),
903
904final_checks AS (
905 SELECT * FROM all_checks
906 UNION ALL
907 SELECT * FROM in_progress_followup
908)
909
910SELECT
911 fc.CheckTime,
912 fc.CheckType,
913 fc.TerminalId,
914 fc.Status,
915 sc_get_notes(fc.Status, fc.CheckType) AS Notes,
916 fc.PassengerId
917FROM final_checks fc;
918
919DROP FUNCTION IF EXISTS sc_get_terminal(bigint);
920DROP FUNCTION IF EXISTS sc_get_status(varchar);
921DROP FUNCTION IF EXISTS sc_get_notes(varchar, varchar);
922
923COMMIT;
924
925
926-- ===== WorksOnSecurityCheck =====
927
928INSERT INTO WorksOnSecurityCheck (SecurityEmployeeEmployeeId, SecurityCheckCheckId)
929WITH
930RecentChecks AS (
931 SELECT
932 sc.CheckId,
933 t.AirportId::integer AS AirportId
934 FROM SecurityCheck sc
935 JOIN Terminal t ON t.TerminalId = sc.TerminalId
936 ORDER BY sc.CheckTime DESC
937 LIMIT 500000
938),
939SecEmpByAirport AS (
940 SELECT
941 se.EmployeeId,
942 e.AirportId
943 FROM SecurityEmployee se
944 JOIN Employee e ON e.EmployeeId = se.EmployeeId
945 WHERE e.AirportId IS NOT NULL
946),
947Assigned AS (
948 SELECT
949 rc.CheckId,
950 sea.EmployeeId,
951 ROW_NUMBER() OVER (
952 PARTITION BY rc.CheckId
953 ORDER BY RANDOM()
954 ) AS emp_rn
955 FROM RecentChecks rc
956 JOIN SecEmpByAirport sea ON sea.AirportId = rc.AirportId
957)
958SELECT
959 EmployeeId AS SecurityEmployeeEmployeeId,
960 CheckId AS SecurityCheckCheckId
961FROM Assigned
962WHERE emp_rn = 1;
963
964
965-- ===== WorksOnGate =====
966
967INSERT INTO WorksOnGate
968(
969 GateId,
970 GateEmployeeEmployeeId,
971 Date,
972 StartTime,
973 EndTime
974)
975SELECT
976 g.GateId,
977 ge.EmployeeId,
978
979 CASE
980 WHEN random() < 0.05 THEN NULL
981 ELSE DATE '2026-04-07' - (floor(random() * 90))::int
982 END,
983
984 CASE
985 WHEN random() < 0.05 THEN NULL
986 WHEN shift_num = 1 THEN '06:00'::time
987 WHEN shift_num = 2 THEN '14:00'::time
988 ELSE '22:00'::time
989 END,
990
991 CASE
992 WHEN random() < 0.05 THEN NULL
993 WHEN shift_num = 1 THEN '14:00'::time
994 WHEN shift_num = 2 THEN '22:00'::time
995 ELSE '06:00'::time
996 END
997
998FROM
999(
1000 SELECT
1001 GateId
1002 FROM Gate
1003 ORDER BY random()
1004 LIMIT 8000
1005) g
1006
1007JOIN
1008(
1009 SELECT
1010 EmployeeId
1011 FROM GateEmployee
1012 ORDER BY random()
1013 LIMIT 8000
1014) ge
1015ON TRUE
1016
1017JOIN
1018(
1019 SELECT
1020 generate_series(1,8000) AS num,
1021 (floor(random()*3)+1)::int AS shift_num
1022) s
1023ON TRUE
1024
1025LIMIT 8000;
1026
1027
1028