/*
cca 350 miest
v každom meste cca 5 letísk (1500 letisk)
cca 250 000 hľadaní
cca 250 000 užívateľov
cca 1600 lietadiel
cca 1400 leteckých spoločností
cca 1 250 000 kupenych listkov
cca 1 500 000 pasažierov
cca 170 000 letov (cca 166 letov z/na letisko, 6 listkov na let)
*/

--mazeme data

truncate table cities, airports, companies, passenger_types, users, aircraft_types, flight_costs, searchings, bought_tickets, passengers, classes, seat_count_in_class, seats, flights restart identity cascade;

--pomocne fcie

create or replace function random_city() returns integer language sql as
$$
SELECT floor(1+random()*350)::int
$$;

--cities

insert into cities (city_name) values ('Bratislava'),('Košice'),('Martin'),('Nitra'),('Nové Zámky'),('Piešťany'),('Poprad'),('Prievidza'),('Partizánske'),('Dubnica n/Váhom'),('Sliač'),('Spišská Nová Ves'),('Svidník'),('Žilina'),('Bidovce'),('Boleráz'),('Dubová'),('Holíč'),('Jasná'),('Kamenica n/Cirochou'),('Kráľová pri Senci'),('Lučenec'),('Ražňany'),('Očová'),('Ružomberok'),('Senica'),('Šurany'),('Trenčín'),('Malacky / Kuchyňa'),('Prešov'),('Brno'),('Karlove Vary'),('Ostrava'),('Pardubice'),('Praha 6'),('Kunovice'),('Vodochody'),('Benešov'),('Blatná'),('Bohuňovice'),('Broumov'),('Břeclav'),('Bubovice'),('Česká Lípa'),('České Budějovice'),('Dvůr Králové n/Labem'),('Erpužice'),('Frýdlant n/Ostravicí'),('Havlíčkův Brod'),('Hodkovice n/Mohelkou'),('Hořice'),('Hořovice'),('Hosín'),('Mimoň'),('Hradec Králové'),('Hranice'),('Cheb'),('Choceň'),('Chomutov'),('Chotěboř'),('Chrudim'),('Jaroměř'),('Mikulovice'),('Jičín'),('Jihlava'),('Jindřichův Hradec'),('Kladno'),('Klatovy'),('Kolín'),('Krnov'),('Kroměříž'),('Křižanov'),('Planá'),('Kyjov'),('Liberec'),('Mariánské Lázně'),('Brno-Medlánky'),('Milovice'),('Mladá Boleslav'),('Mnichovo Hradiště'),('Moravská Třebová'),('Most'),('Nové Město n/Metují'),('Nymburk'),('Olomouc'),('Otrokovice'),('Kámen'),('Panenský Týnec'),('Písek'),('Plasy'),('Líně'),('Letkov'),('Podhořany u Ronova'),('Polička'),('Praha-Letňany'),('Praha 12'),('Prostějov'),('Příbram'),('Přibyslav'),('Rakovník'),('Raná'),('Rokycany'),('Roudnice n/Labem'),('Sazená'),('Skuteč'),('Slaný'),('Soběslav'),('Staňkov'),('Mostkovice'),('Strakonice'),('Strunkovice n/Blanicí'),('Šumperk'),('Tábor'),('Tábor'),('Tachov'),('Toužim'),('Ústí n/Labem'),('Ústí n/Orlicí'),('Velké Poříčí'),('Vlašim'),('Vrchlabí'),('Vysoké Mýto'),('Vyškov'),('Zábřeh'),('Zbraslavice'),('Znojmo'),('Žamberk'),('Žatec');

insert into cities(city_name)
	select city_name||' '||i from cities cross join generate_series(1,2) as seq(i);

--airports

insert into airports (city_id, icao_code, iata_code, full_name) 
	select random_city(), 'LK'||i, 'A'||i, 'letisko cislo '||i
	from generate_series(1,5*350) as seq(i)  ON CONFLICT DO NOTHING; 

insert into companies (name) values ('FLN Frisia Luftverkehr'),('Lübeck Air'),('Lufthansa'),('Lufthansa CityLine'),('OFD Ostfriesischer-Flug-Dienst'),('ACM Air Charter'),('ADAC Luftrettung'),('Aero-Dienst'),('Air Hamburg'),('Air Independence'),('air-taxi europe'),('AirGO Flugservice'),('Arcus-Air'),('Avanti Air'),('Businesswings'),('Condor'),('DC Aviation'),('Deutsche Lufthansa Berlin-Stiftung'),('Deutsche Zeppelin Reederei'),('DRF Stiftung Luftrettung'),('FAI rent-a-jet'),('Hahn Air'),('Jet Executive'),('PrivatAir (Germany)'),('Private Wings'),('RWL German Flight Academy'),('Stuttgarter Flugdienst'),('Sundair'),('Sylt Air'),('TUIfly'),('WDL Aviation'),('Wiking Helikopter Service'),('Windrose Air'),('Air Corsica'),('Air France'),('Air Guyane Express'),('Chalair Aviation'),('CAIRE'),('(Air Antilles Express)'),('Corsair International'),('Finist air'),('French Bee'),('HOP!'),('IGavion'),('Oya Vendée Hélicoptères'),('La Compagnie'),('St-Barth Commuter'),('Transavia France'),('Twinjet'),('Avdef'),('Pan Europeenne'),('Phenix Aviation'),('Oyonnair'),('OpenFlight'),('AstonJet'),('Voldirect'),('iXair'),('Alpines Airlines'),('JetCorp'),('Evolem Aviation'),('Airbus Transport International'),('Air France Cargo'),('ASL Airlines France');

drop table if exists countries cascade;
create table countries(name varchar);
INSERT INTO countries (name) VALUES ('Afghanistan'),('Albania'),('Algeria'),('Andorra'),('Angola'),('Antigua and Barbuda'),('Argentina'),('Armenia'),('Australia'),('Austria'),('Azerbaijan'),('Bahamas'),('Bahrain'),('Bangladesh'),('Barbados'),('Belarus'),('Belgium'),('Belize'),('Benin'),('Bhutan'),('Bolivia'),('Bosnia and Herzegovina'),('Botswana'),('Brazil'),('Brunei'),('Bulgaria'),('Burkina Faso'),('Burundi'),('Côte d Ivoire'),('Cabo Verde'),('Cambodia'),('Cameroon'),('Canada'),('Central African Republic'),('Chad'),('Chile'),('China'),('Colombia'),('Comoros'),('Congo (Congo-Brazzaville)'),('Costa Rica'),('Croatia'),('Cuba'),('Cyprus'),('Czechia (Czech Republic)'),('Democratic Republic of the Congo'),('Denmark'),('Djibouti'),('Dominica'),('Dominican Republic'),('Ecuador'),('Egypt'),('El Salvador'),('Equatorial Guinea'),('Eritrea'),('Estonia'),('Eswatini (fmr. "Swaziland")'),('Ethiopia'),('Fiji'),('Finland'),('France'),('Gabon'),('Gambia'),('Georgia'),('Germany'),('Ghana'),('Greece'),('Grenada'),('Guatemala'),('Guinea'),('Guinea-Bissau'),('Guyana'),('Haiti'),('Holy See'),('Honduras'),('Hungary'),('Iceland'),('India'),('Indonesia'),('Iran'),('Iraq'),('Ireland'),('Israel'),('Italy'),('Jamaica'),('Japan'),('Jordan'),('Kazakhstan'),('Kenya'),('Kiribati'),('Kuwait'),('Kyrgyzstan'),('Laos'),('Latvia'),('Lebanon'),('Lesotho'),('Liberia'),('Libya'),('Liechtenstein'),('Lithuania'),('Luxembourg'),('Madagascar'),('Malawi'),('Malaysia'),('Maldives'),('Mali'),('Malta'),('Marshall Islands'),('Mauritania'),('Mauritius'),('Mexico'),('Micronesia'),('Moldova'),('Monaco'),('Mongolia'),('Montenegro'),('Morocco'),('Mozambique'),('Myanmar (formerly Burma)'),('Namibia'),('Nauru'),('Nepal'),('Netherlands'),('New Zealand'),('Nicaragua'),('Niger'),('Nigeria'),('North Korea'),('North Macedonia'),('Norway'),('Oman'),('Pakistan'),('Palau'),('Palestine State'),('Panama'),('Papua New Guinea'),('Paraguay'),('Peru'),('Philippines'),('Poland'),('Portugal'),('Qatar'),('Romania'),('Russia'),('Rwanda'),('Saint Kitts and Nevis'),('Saint Lucia'),('Saint Vincent and the Grenadines'),('Samoa'),('San Marino'),('Sao Tome and Principe'),('Saudi Arabia'),('Senegal'),('Serbia'),('Seychelles'),('Sierra Leone'),('Singapore'),('Slovakia'),('Slovenia'),('Solomon Islands'),('Somalia'),('South Africa'),('South Korea'),('South Sudan'),('Spain'),('Sri Lanka'),('Sudan'),('Suriname'),('Sweden'),('Switzerland'),('Syria'),('Tajikistan'),('Tanzania'),('Thailand'),('Timor-Leste'),('Togo'),('Tonga'),('Trinidad and Tobago'),('Tunisia'),('Turkey'),('Turkmenistan'),('Tuvalu'),('Uganda'),('Ukraine'),('United Arab Emirates'),('United Kingdom'),('United States of America'),('Uruguay'),('Uzbekistan'),('Vanuatu'),('Venezuela'),('Vietnam'),('Yemen'),('Zambia'),('Zimbabwe');

drop table if exists airliners_name cascade;
create table airliners_name(name varchar);
INSERT INTO airliners_name (name) VALUES ('air'),('national airline'),('low cost airlines'),('jet'),('travel'),('fly'),('airline');

INSERT INTO companies (name)
	select c.name||' '||a.name from countries as c cross join airliners_name as a;

create or replace function random_cost() returns float language sql as
$$
select random()+random()*3
$$;

insert into flight_costs (price_per_km)
	select random_cost()
	from generate_series(1,2000) as seq(i);

create or replace function random_flight_costs() returns int language sql as
$$
SELECT floor(1+random()*1999)::int
$$;

insert into passenger_types (passenger_type_name, flight_cost_id) values ('adult', random_flight_costs()), ('infant', random_flight_costs()), ('senior', random_flight_costs()), ('junior', random_flight_costs()), ('child', random_flight_costs()),('semi-adult', random_flight_costs()), ('BIG infant', random_flight_costs()), ('really old senior', random_flight_costs()), ('young junior', random_flight_costs()), ('teen', random_flight_costs()),('fat adult w/ lot baggage', random_flight_costs()), ('semi-infant', random_flight_costs()), ('young senior', random_flight_costs()), ('old junior', random_flight_costs()), ('small child', random_flight_costs()), ('toddler', random_flight_costs()), ('baby', random_flight_costs());

insert into users (user_name) values ('admin'), ('adam'), ('bozena'), ('cyril'), ('david'), ('emil'), ('frantisek'), ('gustav'), ('helena'), ('ivan'), ('jozef'), ('karol'), ('ladislav');

insert into users(user_name)
	select user_name||' no. '||i from users cross join generate_series(0,2000) as seq(i);

insert into aircraft_types (aircraft_name) values ('SyberJet SJ30i'),('Citation CJ3+'),('Phenom 300E[41]'),('Nextant 400XTi'),('Citation CJ4'),('Pilatus PC-24'),('Learjet 70'),('Cirrus SF50 G2'),('Phenom 100EV'),('Citation M2'),('HondaJet Elite'),('Citation XLS+'),('Learjet 75'),('Legacy 450'),('Praetor 500'),('Citation Latitude'),('Citation Sovereign+'),('Legacy 500'),('Praetor 600'),('Gulfstream G280'),('Challenger 350'),('Embraer Legacy 650E'),('Citation Longitude'),('Falcon 2000S/EX'),('Challenger 650'),('Falcon 2000LXS/EX'),('Falcon 900LX/EX'),('Gulfstream 500'),('Global 5000'),('Falcon 7X'),('Gulfstream G600'),('Falcon 8X'),('Gulfstream G550'),('Global 6000'),('Gulfstream G650ER'),('Global 7500'),('Airbus A220'),('Antonov An-140'),('Antonov An-148'),('ATR 42'),('ATR 72'),('De Havilland Canada Dash 8'),('Britten-Norman Islander'),('CASA C-212 Aviocar'),('CASA/IPTN CN-235'),('Cessna 208'),('Comac ARJ21'),('DHC-6 Twin Otter (Viking -400)'),('Dornier 228/Dornier 228NG'),('Embraer E-Jet E2 family'),('Embraer E-Jet family'),('Harbin Y-12'),('Ilyushin Il-114'),('Let L-410 Turbolet'),('Mitsubishi MRJ'),('PZL M28 Skytruck'),('Sukhoi Superjet 100'),('Xian Y-7 / MA60 / MA600'),('Airco DH.4A'),('Airco DH.9C'),('Airco DH.16'),('BAT F.K.26'),('Blériot-SPAD S.27'),('Curtiss Eagle'),('Farman F.50P'),('Farman F.60 Goliath'),('Grahame-White Charabanc'),('Junkers F.13'),('Nieuport-Delage NiD 30'),('Potez SEA VII'),('Sopwith Wallaby'),('Westland Limousine'),('Airspeed Envoy'),('Albatros L 73'),('ANEC III'),('Armstrong Whitworth Argosy'),('Armstrong Whitworth Atalanta'),('Armstrong Whitworth Ensign'),('Avro 618 Ten'),('Avro 642 Eighteen'),('BFW M.20'),('Blériot-SPAD S.33'),('Blériot-SPAD S.46'),('Blériot-SPAD S.56'),('Boeing 40'),('Boeing 80'),('Boeing 247'),('Boeing 307'),('Boeing 314'),('Breguet 26T'),('Breguet 280T'),('Breguet 393T'),('Bristol Ten-seater'),('Consolidated Commodore'),('Consolidated Fleetster'),('Curtiss Condor'),('Curtiss Kingbird'),('Curtiss Robin'),('de Havilland DH.18'),('de Havilland DH.34'),('de Havilland DH.50'),('de Havilland DH.66'),('de Havilland Dragon'),('de Havilland Dragon Rapide'),('de Havilland Express'),('de Havilland Fox Moth'),('de Havilland Hercules'),('Dewoitine D.332'),('Dewoitine D.338'),('Dornier Do X'),('Douglas DC-1'),('Douglas DC-2'),('Douglas DC-3'),('Douglas DC-4E'),('Douglas DC-5'),('Douglas XCG-17'),('Farman F.120'),('Farman F.190'),('Farman F.300'),('Focke-Wulf A 17'),('Focke-Wulf Fw 200'),('Fokker F.II'),('Fokker F.III'),('Fokker F.VII'),('Fokker F.XII'),('Fokker F.XVIII'),('Fokker F.XXII'),('Ford Trimotor'),('Handley Page Type W'),('Handley Page H.P.42'),('Handley Page Halifax'),('Junkers G 24'),('Junkers G 31'),('Junkers W 34'),('Junkers Ju 52'),('Junkers Ju 60'),('Junkers Ju 86'),('Junkers Ju 90'),('Junkers Ju 160'),('Kalinin K-4'),('Kalinin K-5'),('Lockheed Vega'),('Lockheed Air Express'),('Lockheed Orion'),('Lockheed Electra'),('Lockheed Electra Junior'),('Lockheed Super Electra'),('Lockheed Lodestar'),('Martin M-130'),('Messerschmitt M 18'),('Messerschmitt M 24'),('Nieuport-Delage NiD 39'),('Nieuport-Delage NiD 640'),('Northrop Alpha'),('Northrop Delta'),('Percival Petrel'),('Rohrbach Roland'),('Short Empire'),('Short S.26'),('Sikorsky S-40'),('Sikorsky S-42'),('SNCASE Languedoc'),('Spartan Cruiser'),('Stinson Detroiter'),('Stinson Model A'),('Stinson Model T'),('Stinson Model U'),('Stinson Reliant'),('Supermarine Swan'),('Travel Air 6000'),('Udet U 11 Kondor'),('Vickers Vimy Commercial'),('Vickers Vulcan'),('Vultee V-1'),('Westland Wessex'),('Wibault 280'),('Wibault 360'),('Zeppelin-Staaken E-4/20'),('Aérospatiale Corvette'),('Airspeed Ambassador'),('Antonov An-10'),('Antonov An-24'),('Aviation Traders Accountant'),('Aviation Traders Carvair'),('Avro Lancastrian'),('Avro Tudor'),('Avro York'),('Avro Canada C102 Jetliner'),('BAC 1-11'),('Beechcraft 90 King Air'),('Beechcraft A100 King Air'),('Beechcraft B100 King Air'),('Boeing 377'),('Boeing 707'),('Boeing 727'),('Boeing 737'),('Boeing 747'),('Boeing 2707 (project)'),('Boeing B-17 Flying Fortress'),('Breda-Zappata BZ.308'),('Breguet Deux-Ponts'),('Breguet 941'),('Bristol Britannia'),('Bristol Type 223 (project)'),('Canadair North Star'),('Cessna 402'),('Concorde'),('Convair 240'),('Convair 340'),('Convair 440'),('Convair 540'),('Convair 580'),('Convair 600'),('Convair 640'),('Convair 880'),('Convair 990'),('Curtiss Commando'),('Dassault Mercure'),('de Havilland Comet'),('de Havilland Dove'),('de Havilland Heron'),('de Havilland Canada DHC-2 Beaver'),('de Havilland Canada DHC-3 Otter'),('de Havilland Canada DHC-4 Caribou'),('de Havilland Canada DHC-5 Buffalo'),('de Havilland Canada DHC-6 Twin Otter'),('Douglas DC-4'),('Douglas DC-6'),('Douglas DC-7'),('Douglas DC-8'),('Embraer Bandeirante'),('Fiat G.12'),('Fiat G.212'),('Fokker F27 Friendship'),('Fokker F28 Fellowship'),('Grumman Gulfstream I'),('Handley Page Halton'),('Handley Page Hermes'),('Handley Page Dart Herald'),('Handley Page Hastings'),('Handley Page Herald'),('Hawker Siddeley HS 748'),('Hawker Siddeley Trident'),('Hughes H-4 Hercules'),('Ilyushin Il-62'),('Kawasaki Ki-56'),('Lockheed Hudson'),('Lockheed Constellation'),('Lockheed L-188 Electra'),('Lockheed L-1011 TriStar'),('Martin 4-0-4'),('McDonnell Douglas DC-9'),('McDonnell Douglas DC-10'),('Percival Pembroke'),('Piper PA-31 Navajo'),('Riley Turbo Skyliner'),('Saunders ST-27'),('Saunders ST-28'),('Saunders-Roe Princess'),('Savoia-Marchetti SM.95'),('Shin Meiwa Tawron'),('Short Sandringham'),('SNCASE Armagnac'),('Sud Aviation Caravelle'),('Tupolev Tu-104'),('Tupolev Tu-114'),('Tupolev Tu-144'),('Tupolev Tu-154'),('VFW-Fokker 614'),('Vickers VC.1 Viking'),('Vickers Vanguard'),('Vickers Viscount'),('Vickers VC-10'),('Yakovlev Yak-40'),('ATR 42'),('ATR 72'),('Airbus A220'),('Airbus A300'),('Airbus A310'),('Airbus A318'),('Airbus A319'),('Airbus A320'),('Airbus A321'),('Airbus A330'),('Airbus A340'),('Airbus A350'),('Airbus A380'),('Antonov An-140'),('Antonov An-148'),('Antonov An-158'),('Beechcraft 200 Super King Air'),('Beechcraft 1300'),('Beriev Be-200'),('British Aerospace BAe 146'),('Boeing 717'),('Boeing 757'),('Boeing 767'),('Boeing 777'),('Boeing 787'),('Bombardier CRJ-100'),('Bombardier CRJ-200'),('Bombardier CRJ-700'),('Bombardier CRJ-900'),('Bombardier Dash 8'),('CASA C-212 Aviocar'),('Cessna 208'),('Cessna 404'),('Comac C919 (project)'),('Convair CV5800'),('de Havilland Canada Dash 7'),('De Havilland Canada Dash 8'),('Dornier 328'),('Embraer EMB 121 Xingu'),('Embraer EMB 120 Brasilia'),('Embraer EMB 820C'),('Embraer/FMA CBA 123 Vector'),('Embraer ERJ 135'),('Embraer ERJ 140'),('Embraer ERJ 145'),('Embraer 170'),('Embraer 175'),('Embraer 190'),('Embraer 195'),('Fairchild Dornier 328JET'),('Fokker 50'),('Fokker 70'),('Fokker 100'),('Irkut MS-21 (project)'),('Ilyushin Il-86'),('Ilyushin Il-96'),('Ilyushin Il-114'),('Indonesian Aerospace N-219'),('Indonesian Aerospace N-245'),('Indonesian Aerospace N-270'),('IPTN N-250'),('IPTN N-2130'),('McDonnell-Douglas MD-11'),('McDonnell Douglas MD-80'),('McDonnell Douglas MD-90'),('McDonnell-Douglas MD-95'),('Mitsubishi Regional Jet (project)'),('Neiva Carajá'),('Piper Chieftain'),('Piper T-1020'),('Piper T-1040'),('Reims-Cessna F406 Caravan II'),('Short 330'),('Short 360'),('Sukhoi Superjet 100'),('Tupolev Tu-204'),('Yakovlev Yak-42'),('Saab 2000'),('Airspeed Courier'),('Antonov An-2'),('Beechcraft Bonanza'),('Cessna 205/206/207'),('Cessna 208 Caravan'),('Cessna 210 Centurion'),('Comte AC-4 Gentleman'),('Comte AC-8'),('Curtiss Robin'),('de Havilland Canada DHC-2 Beaver'),('de Havilland Canada DHC-3 Otter'),('de Havilland DH.83 Fox Moth'),('Fairchild FC-2 & Fairchild 71'),('Farman F.190'),('Focke-Wulf A 33'),('Fokker Super Universal'),('GippsAero GA8 Airvan'),('Koolhoven F.K.41 & Desoutter'),('Miles M.38 Messenger'),('Noorduyn Norseman'),('PAC P-750 XSTOL'),('Percival Proctor'),('Percival Vega Gull'),('Pilatus PC-12'),('Piper PA-32'),('Piper PA-46 Malibu and Matrix'),('Ryan Brougham'),('SOCATA TBM'),('Stinson SM Detroiter'),('Thruxton Jackaroo'),('Yakovlev Yak-18T'),('Airspeed Envoy'),('Antonov An-28'),('Barkley-Grow T8P-1'),('Beechcraft 60 Duke'),('Beechcraft King Air'),('Beechcraft Super King Air'),('Beechcraft Model 18'),('Beechcraft Queen Air'),('Britten-Norman Islander'),('Caudron C.440 Goéland'),('Caudron Simoun'),('Caproni Ca.308 Borea'),('Cessna T-50'),('Cessna T303 Crusader'),('Cessna 310 & 320'),('Cessna 336 Skymaster'),('Cessna 340'),('Cessna 401/402'),('Cessna 404 Titan'),('Cessna 411'),('Cessna 414'),('Cessna 421 Golden Eagle'),('Cessna 425'),('Cessna 441 Conquest II'),('de Havilland Canada DHC-6 Twin Otter'),('de Havilland DH.84 Dragon'),('de Havilland DH.89 Dragon Rapide'),('de Havilland DH.90 Dragonfly'),('Farman F.430'),('GAF Nomad'),('General Aircraft ST-3, 4, 6, 10, 11 and 12 Monospar'),('General Aircraft ST-25 Monospar'),('Harbin Y-12'),('Let L-410 Turbolet'),('Lockheed 10 Electra'),('Lockheed 12 Electra Junior'),('Miles M.57 Aerovan'),('Miles M.65 Gemini'),('Nakajima AT-2/Ki-34'),('Partenavia P68'),('Percival Q.6 Petrel'),('Piper PA-23 Aztec'),('Piper PA-31 Navajo'),('Piper PA-34 Seneca'),('Piper PA-42 Cheyenne'),('Piper PA-44 Seminole'),('Piper PA-60/61 Aerostar'),('Potez 56'),('Short SC.7 Skyvan'),('Short Scion'),('Short Scion Senior'),('Tugan Gannet'),('Westland IV & Wessex');

drop table if exists additional_aircraft_type cascade;
create table additional_aircraft_type(name varchar);
	INSERT INTO additional_aircraft_type (name) VALUES ('combi'),('commuter'),('executive'),('economy');

insert into aircraft_types (aircraft_name)
	select aircraft_name||' '||name from aircraft_types cross join additional_aircraft_type;

create or replace function random_user() returns int language sql as
$$
SELECT floor(1+random()*25000)::int
$$;

insert into searchings (departure_city_id, arrival_city_id, user_id, "count")
	select random_city(), random_city(), random_user(), floor(random() * 10)
	from generate_series(1, (50)*5000) as seq(i) ON CONFLICT DO NOTHING;
	
insert into bought_tickets (canceled, flight_id, class_id)
	select 109*i%523<103, null, null
	from generate_series(1,1.25*1000*1000) as seq(i);
	
create or replace function random_ticket() returns int language sql as
$$
SELECT floor(1+random()*1200000)::int
$$;
	
insert into passengers (passenger_name, passenger_id_card)
	select i||' meno', 'ab'||i||'c'||(2*i)||'32d'||(109*i%17)
	from generate_series(1,1.5*1000*1000) as seq(i);
	
create or replace function random_passenger() returns int language sql as
$$
SELECT floor(1+random()*1450000)::int
$$;
	
insert into classes (class_name, flight_cost_id) values ('business', random_flight_costs()), ('economy', random_flight_costs()), ('tourist', random_flight_costs()), ('first', random_flight_costs()), ('luxury', random_flight_costs()), ('batozinovy priestor :D', random_flight_costs()), ('low-cost', random_flight_costs());

insert into classes (class_name, flight_cost_id) values ('premium luxury', random_flight_costs()), ('podvozková šachta', random_flight_costs()), ('čierny pasažier', random_flight_costs()), ('kokpit', random_flight_costs()), ('prezlečený za letušku', random_flight_costs()), ('ryanair :D', random_flight_costs());

create or replace function random_class() returns int language sql as
$$
SELECT floor(1+random()*12)::int
$$;

create or replace function random_class_in_flight(id int) returns int language sql as
$$
select classes.class_id from classes WHERE can_reserve_ticket(id, class_id)
 order by random() LIMIT 1
$$;

create or replace function random_aircraft() returns int language sql as
$$
SELECT floor(1+random()*2100)::int
$$;

create or replace function random_airport() returns int language sql as
$$
SELECT floor(1+random()*1500)::int
$$;

create or replace function random_company() returns int language sql as
$$
SELECT floor(1+random()*1400)::int
$$;

insert into seat_count_in_class (aircraft_type_id, class_id, "count")
	select aircraft_types.aircraft_type_id, classes.class_id, floor(random() * 30)
	from aircraft_types cross join classes where random()<0.2 ON CONFLICT DO NOTHING; -- ~kazda piata classa tam nie je

insert into flights(flight_name, departure_time, arrival_time, distance, departure_airport_id, arrival_airport_id, company_id, aircraft_type_id, flight_cost_id)
	select 'name'||i, departure, departure + random()*(timestamp '2014-01-01 23:59:59' -
                   timestamp '2014-01-01 00:00:00'), floor(random()*1000+50),
	random_airport(), random_airport(), random_company(), random_aircraft(), random_flight_costs()
	from (select timestamp '2016-01-01 00:00' + random()*(timestamp '2017-01-01 23:59:59' -
                   timestamp '2014-01-01 00:00:00') as departure, i from generate_series(1,170*1000) as seq(i))  as tmp;

create or replace function random_flight() returns int language sql as
$$
SELECT floor(1+random()*149999)::int
$$;

drop table if exists seat_numbers cascade;
create table seat_numbers
(
    seat_number int
);

insert into seat_numbers (seat_number)
	select i from generate_series(1,(select max("count") from seat_count_in_class)) as seq(i);

insert into seats (aircraft_type_id, seat_number, class_id)
	select aircraft_types.aircraft_type_id, seat_number, classes.class_id
		from seat_count_in_class inner join classes on seat_count_in_class.class_id = classes.class_id
		inner join aircraft_types on seat_count_in_class.aircraft_type_id = aircraft_types.aircraft_type_id
		cross join seat_numbers where seat_numbers.seat_number<"count" ON CONFLICT DO NOTHING;

/*
update bought_tickets set flight_id=random_flight(), passenger_id=random_passenger();
update bought_tickets set class_id=random_class_in_flight(flight_id);
*/
drop table if exists flights_classes;
create table flights_classes
(
    id serial primary key,
    flight_id int references flights,
    class_id int references classes
);

insert into flights_classes (flight_id, class_id)
    select flight_id, seat_count_in_class.class_id from flights inner join aircraft_types ON flights.aircraft_type_id=aircraft_types.aircraft_type_id
        inner join seat_count_in_class ON seat_count_in_class.aircraft_type_id=aircraft_types.aircraft_type_id order by random();
insert into flights_classes (flight_id, class_id)
    select flight_id, seat_count_in_class.class_id from flights inner join aircraft_types ON flights.aircraft_type_id=aircraft_types.aircraft_type_id
        inner join seat_count_in_class ON seat_count_in_class.aircraft_type_id=aircraft_types.aircraft_type_id order by random();
insert into flights_classes (flight_id, class_id)
    select flight_id, seat_count_in_class.class_id from flights inner join aircraft_types ON flights.aircraft_type_id=aircraft_types.aircraft_type_id
        inner join seat_count_in_class ON seat_count_in_class.aircraft_type_id=aircraft_types.aircraft_type_id order by random();

update bought_tickets set flight_id=flights_classes.flight_id,
    class_id=flights_classes.class_id, passenger_id=random_passenger(),
    seat_id=(CASE WHEN random()<0.25 THEN random_seat(flights_classes.flight_id, flights_classes.class_id) ELSE null END)
    from flights_classes WHERE bought_ticket_id=flights_classes.id;

--update bought_tickets set seat_id=(CASE WHEN random()<0.25 THEN random_seat(flight_id, class_id) ELSE null END);

drop table if exists seat_numbers;
drop table if exists countries;
drop table if exists airliners_name;
drop table if exists additional_aircraft_type;
drop table if exists flights_classes;