DB_H2_xx

-- sheqmna

CREATE schema IF NOT EXISTS flights_DB;

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

CREATE TABLE IF NOT EXISTS cities(
  city_ID INT(10) NOT NULL AUTO_INCREMENT,
    city_name VARCHAR(50),
    city_country_ID INT(50),
    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(10) NOT NULL AUTO_INCREMENT,
    country_name VARCHAR(50),
    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 flights(
  flight_ID INT(8) 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_dep_airport_to_airport FOREIGN KEY (flight_dep_airport_ID) REFERENCES airports(airport_code);

ALTER TABLE flights ADD CONSTRAINT FK_arr_airport_to_airport FOREIGN KEY (flight_arr_airport_ID) REFERENCES airports(airport_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;