DB_H2_till_4_beta_1

-- sheqmna

CREATE schema IF NOT EXISTS flights_DB;

CREATE TABLE IF NOT EXISTS airports(
  airport_code VARCHAR(3) NOT NULL,
    airport_name VARCHAR(2048),
    airport_city_ID INT(11),
--    airport_country_ID int(10),
    PRIMARY KEY (airport_code)
);

CREATE TABLE IF NOT EXISTS cities(
  city_ID INT(11) NOT NULL AUTO_INCREMENT,
    city_name VARCHAR(2048),
    city_country_ID INT(11),
    PRIMARY KEY (city_ID)
);

ALTER TABLE airports ADD CONSTRAINT FK_airport_to_city FOREIGN KEY (airport_city_ID) REFERENCES cities(city_ID);

CREATE TABLE IF NOT EXISTS countries(
  country_ID INT(11) NOT NULL AUTO_INCREMENT,
    country_name VARCHAR(2048),
    PRIMARY KEY (country_ID)
);

ALTER TABLE cities ADD CONSTRAINT FK_city_to_country FOREIGN KEY (city_country_ID) REFERENCES countries(country_ID);

CREATE TABLE IF NOT EXISTS flight_codes(
    flight_code VARCHAR(20) NOT NULL,
    flight_code_dep_airport VARCHAR(3),
    flight_code_arr_airport VARCHAR(3),

    PRIMARY KEY (flight_code)
);

ALTER TABLE flight_codes ADD CONSTRAINT FK_flight_code_to_dep_airport FOREIGN KEY (flight_code_dep_airport) REFERENCES airports(airport_code);

ALTER TABLE flight_codes ADD CONSTRAINT FK_flight_code_to_arr_airport FOREIGN KEY (flight_code_arr_airport) REFERENCES airports(airport_code);


CREATE TABLE IF NOT EXISTS flights(
  flight_ID INT(11) NOT NULL AUTO_INCREMENT,
    flight_code VARCHAR(20),
  -- flight_dep_country_ID int(10),
  -- flight_dep_city_ID int(10),
  -- flight_dep_airport_ID VARCHAR(5),
    flight_dep_time TIMESTAMP,
  -- flight_arr_country_ID int(10),
  -- flight_arr_city_ID int(10),
  --   flight_arr_airport_ID VARCHAR(5),
    flight_arr_time TIMESTAMP,
    flight_price DOUBLE(19,2),

    PRIMARY KEY (flight_ID)
);

ALTER TABLE flights ADD CONSTRAINT FK_flight_code_to_flight_code FOREIGN KEY (flight_code) REFERENCES flight_codes(flight_code);


-- sheyra

INSERT INTO countries(country_name)
  SELECT DISTINCT dep_country FROM flights_all
    UNION
    SELECT DISTINCT arr_country FROM flights_all;

SELECT * FROM countries;

INSERT cities(city_name, city_country_ID)
SELECT dep_city, country_ID FROM
(SELECT DISTINCT dep_city, dep_country FROM flights_all
UNION
SELECT DISTINCT arr_city, arr_country FROM flights_all) AS A JOIN countries C
ON A.dep_country= C.country_name;

SELECT * FROM cities;

INSERT airports(airport_code, airport_name, airport_city_ID)
SELECT dep_airport, dep_airport_name, city_ID FROM
(SELECT DISTINCT dep_airport, dep_airport_name, dep_city FROM flights_all
UNION
SELECT DISTINCT arr_airport, arr_airport_name, arr_city FROM flights_all) AS A JOIN cities C
ON A.dep_city= C.city_name;

SELECT * FROM flight_codes;

SELECT flight_code, dep_time, arr_time, price FROM flights_all;

INSERT INTO flights(flight_code, flight_dep_time, flight_arr_time, flight_price)
SELECT flight_code, dep_time, arr_time, price FROM flights_all;

SELECT * FROM flights F
JOIN flight_codes C
ON F.flight_code = C.flight_code;

SELECT * FROM flights;


SELECT DISTINCT F.flight_ID AS f_id, F.flight_code AS f_c, F.flight_price AS f_p, Cdep.city_name AS dep_city, Carr.city_name AS arr_city
    FROM
        flights F, flight_codes FC, airports Adep, airports Aarr, cities Cdep, cities Carr
    WHERE
        F.flight_code = FC.flight_code
        AND
        FC.flight_code_dep_airport = Adep.airport_code AND FC.flight_code_arr_airport = Aarr.airport_code
        AND
        Adep.airport_city_ID = Cdep.city_ID AND Aarr.airport_city_ID = Carr.city_ID
        AND
        Cdep.city_name = 'Vilnius' AND Carr.city_name = 'Kutaisi'
    ORDER BY f_p ASC LIMIT 1;


SELECT F1.flight_ID, FC1.flight_code, Cdep.city_name, Cmid.city_name, Carr.city_name, F1.flight_price, F2.flight_price
    FROM
        flights F1, flights F2, flight_codes FC1, flight_codes FC2, airports Adep, airports Amidarr, airports Amiddep, airports Aarr, cities Cdep, cities Cmid, cities Carr
    WHERE
        F1.flight_code = FC1.flight_code
        AND
        FC1.flight_code_dep_airport = Adep.airport_code AND FC1.flight_code_arr_airport = Amidarr.airport_code
        AND
        Adep.airport_city_ID = Cdep.city_ID AND Amidarr.airport_city_ID = Cmid.city_ID
        AND
        Cdep.city_name = 'Vilnius'
        AND
        F2.flight_code = FC2.flight_code
        AND
        FC2.flight_code_dep_airport = Amiddep.airport_code AND FC2.flight_code_arr_airport = Aarr.airport_code
        AND
        Amiddep.airport_city_ID = Cmid.city_ID AND Aarr.airport_city_ID = Carr.city_ID
        AND
        Carr.city_name = 'Warsaw'
        AND
        Amiddep.airport_city_ID = Amidarr.airport_city_ID;