drop table if exists cities cascade;
create table cities
(
	city_id serial primary key,
	city_name varchar
);

drop table if exists airports cascade;
create table airports
(
	airport_id serial primary key,
	city_id int references cities,
	full_name varchar,
	icao_code varchar,
	iata_code varchar
);

drop table if exists companies cascade;
create table companies
(
	company_id serial primary key,
	name varchar
);

drop table if exists flight_costs cascade;
create table flight_costs
(
	flight_cost_id serial primary key,
	price_per_km float	
);

drop table if exists passenger_types cascade;
create table passenger_types
(
	passenger_type_id serial primary key,
	passenger_type_name varchar,
	flight_cost_id int references flight_costs ON DELETE CASCADE
);

drop table if exists users cascade;
create table users
(
	user_id serial primary key,
	user_name varchar
);

drop table if exists aircraft_types cascade;
create table aircraft_types
(
	aircraft_type_id serial primary key,
	aircraft_name varchar	
);

drop table if exists searchings cascade;
create table searchings
(
	departure_city_id int references cities ON DELETE CASCADE,
	arrival_city_id int references cities ON DELETE CASCADE,
	user_id int references users,
	"count" int,
	primary key(departure_city_id, arrival_city_id, user_id)
);

drop table if exists passengers cascade;
create table passengers
(
	passenger_id serial primary key,
	passenger_name varchar,
	passenger_id_card varchar
);

drop table if exists bought_tickets cascade;
create table bought_tickets
(
	bought_ticket_id serial primary key,
	canceled boolean,
	flight_id int, -- flight id
	class_id int,
	passenger_id int references passengers ON DELETE CASCADE,
	seat_id int references seats ON DELETE CASCADE
);

drop table if exists classes cascade;
create table classes
(
	class_id serial primary key,
	class_name varchar,
	flight_cost_id int references flight_costs ON DELETE CASCADE
);

drop table if exists seat_count_in_class cascade;
create table seat_count_in_class
(
	"count" int,
	aircraft_type_id int references aircraft_types ON DELETE CASCADE,
	class_id int references classes ON DELETE CASCADE,
	primary key(aircraft_type_id, class_id)
);

drop table if exists seats cascade;
create table seats
(
	seat_id serial primary key,
	aircraft_type_id int references aircraft_types ON DELETE CASCADE,
	seat_number int,
	class_id int references classes ON DELETE CASCADE
);

drop table if exists flights cascade;
create table flights
(
	flight_id serial primary key,
	flight_name varchar,
	departure_time timestamp,
	arrival_time timestamp,
	distance int,
	departure_airport_id int references airports ON DELETE CASCADE,
	arrival_airport_id int references airports ON DELETE CASCADE,
	company_id int references companies,
	aircraft_type_id int references aircraft_types ON DELETE CASCADE,
	flight_cost_id int references flight_costs ON DELETE CASCADE
);

-- add flight_id
alter table bought_tickets add foreign key (flight_id) references flights ON DELETE CASCADE;

DROP FUNCTION random_nonbooked_seat_from_aircraft(integer,integer,integer);
create or replace function random_nonbooked_seat_from_aircraft(f_id int, a_id int, orig_s_id int) returns int language sql as
$$
select seats.seat_id from seats where aircraft_type_id=a_id and class_id=(SELECT class_id FROM seats WHERE seat_id=orig_s_id) and 
NOT EXISTS(SELECT * from bought_tickets WHERE flight_id=f_id AND canceled=false 
	AND bought_tickets.seat_id=seats.seat_id) order by random() limit 1
$$;

create or replace function arrival_city(f_id int) returns int language sql IMMUTABLE  as
$$
SELECT city_id from flights INNER JOIN airports ON airport_id=arrival_airport_id 
WHERE flight_id=f_id
$$;

create or replace function departure_city(f_id int) returns int language sql IMMUTABLE  as
$$
SELECT city_id from flights INNER JOIN airports ON airport_id=departure_airport_id 
WHERE flight_id=f_id
$$;

create or replace function aircraft_on_flight(f_id int) returns int language sql IMMUTABLE  as
$$
SELECT aircraft_type_id from flights WHERE flight_id=f_id
$$;

create or replace function can_reserve_ticket(f_id int, c_id int) returns boolean language sql IMMUTABLE  as
$$
SELECT (SELECT count(*) FROM bought_tickets WHERE bought_tickets.class_id=c_id AND bought_tickets.flight_id=f_id and canceled=false)
         <
        (SELECT "count" FROM seat_count_in_class WHERE seat_count_in_class.class_id=c_id AND seat_count_in_class.aircraft_type_id=aircraft_on_flight(f_id))
$$;

create or replace function random_seat(f_id int, c_id int) returns int language sql as
$$
select seats.seat_id from seats WHERE aircraft_type_id=aircraft_on_flight(f_id) and class_id=c_id order by random() offset random()*10000 LIMIT 1
$$;


/*
 * indexyyy
 */
DROP INDEX IF EXISTS index_flights_index_departure;
DROP INDEX IF EXISTS index_flights_index_arrival;
DROP INDEX IF EXISTS index_flights_index_departure_time;
DROP INDEX IF EXISTS index_flights_index_arrival_time;
DROP INDEX IF EXISTS index_flights_index_aircraft;
DROP INDEX IF EXISTS index_airports_cities_id;
DROP INDEX IF EXISTS index_bought_tickets_flight_id;
DROP INDEX IF EXISTS index_bought_tickets_seats_id;
DROP INDEX IF EXISTS index_seats_aircrafts_id;
DROP INDEX IF EXISTS index_seats_class_id;

CREATE INDEX index_flights_index_departure ON flights(departure_airport_id);
CREATE INDEX index_flights_index_arrival ON flights(arrival_airport_id);
CREATE INDEX index_flights_index_departure_time ON flights(departure_time);
CREATE INDEX index_flights_index_arrival_time ON flights(arrival_time);
CREATE INDEX index_flights_index_aircraft ON flights(aircraft_type_id);
CREATE INDEX index_airports_cities_id ON airports(city_id);
CREATE INDEX index_bought_tickets_flight_id ON bought_tickets(flight_id);
CREATE INDEX index_bought_tickets_seats_id ON bought_tickets(seat_id);
CREATE INDEX index_seats_aircrafts_id ON seats(aircraft_type_id);
CREATE INDEX index_seats_class_id ON seats(class_id);
