-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements.  See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License.  You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

AUTOCOMMIT OFF;

CREATE TABLE AIRLINES
   (
      AIRLINE CHAR(2) NOT NULL ,
      AIRLINE_FULL VARCHAR(24),
      BASIC_RATE DOUBLE PRECISION,
      DISTANCE_DISCOUNT DOUBLE PRECISION,
      BUSINESS_LEVEL_FACTOR DOUBLE PRECISION,
      FIRSTCLASS_LEVEL_FACTOR DOUBLE PRECISION,
      ECONOMY_SEATS INTEGER,
      BUSINESS_SEATS INTEGER,
      FIRSTCLASS_SEATS INTEGER
   );


ALTER TABLE AIRLINES
   ADD CONSTRAINT AIRLINES_PK Primary Key (
      AIRLINE);


-- \************************************************************\

CREATE TABLE COUNTRIES
   (
      COUNTRY VARCHAR(26) NOT NULL,
      COUNTRY_ISO_CODE CHAR(2) NOT NULL ,
      REGION VARCHAR(26)
   );
 

ALTER TABLE COUNTRIES
   ADD CONSTRAINT COUNTRIES_PK Primary Key (
      COUNTRY_ISO_CODE);

ALTER TABLE COUNTRIES
   ADD CONSTRAINT COUNTRIES_UNQ_NM Unique (
      COUNTRY);


ALTER TABLE COUNTRIES
   ADD CONSTRAINT COUNTRIES_UC
   CHECK (country_ISO_code = upper(country_ISO_code) );


-- \************************************************************\

CREATE TABLE CITIES
   (
      CITY_ID INTEGER NOT NULL ,
      CITY_NAME VARCHAR(24) NOT NULL,
	COUNTRY VARCHAR(26) NOT NULL,
	AIRPORT VARCHAR(3),
	LANGUAGE  VARCHAR(16),
      COUNTRY_ISO_CODE CHAR(2) 
   );


ALTER TABLE CITIES
   ADD CONSTRAINT CITIES_PK Primary Key (
      CITY_ID);

ALTER TABLE CITIES
   ADD CONSTRAINT COUNTRIES_FK Foreign Key (
      COUNTRY_ISO_CODE)
   REFERENCES COUNTRIES (
      COUNTRY_ISO_CODE);


-- \************************************************************\

CREATE TABLE FLIGHTS
   (
      FLIGHT_ID CHAR(6) NOT NULL ,
      SEGMENT_NUMBER INTEGER NOT NULL ,
      ORIG_AIRPORT CHAR(3),
      DEPART_TIME TIME,
      DEST_AIRPORT CHAR(3),
      ARRIVE_TIME TIME,
      MEAL CHAR(1),
      FLYING_TIME DOUBLE PRECISION,
      MILES INTEGER,
      AIRCRAFT VARCHAR(6)
   );

CREATE INDEX DESTINDEX ON FLIGHTS (
      DEST_AIRPORT) ;
  
CREATE INDEX ORIGINDEX ON FLIGHTS (
      ORIG_AIRPORT) ;
 
ALTER TABLE FLIGHTS
   ADD CONSTRAINT FLIGHTS_PK Primary Key (
      FLIGHT_ID,
      SEGMENT_NUMBER);

ALTER TABLE FLIGHTS
   ADD CONSTRAINT MEAL_CONSTRAINT
   CHECK (meal IN ('B', 'L', 'D', 'S'));


-- \************************************************************\

CREATE TABLE FLIGHTAVAILABILITY
   (
      FLIGHT_ID CHAR(6) NOT NULL ,
      SEGMENT_NUMBER INTEGER NOT NULL ,
      FLIGHT_DATE DATE NOT NULL ,
      ECONOMY_SEATS_TAKEN INTEGER DEFAULT 0,
      BUSINESS_SEATS_TAKEN INTEGER DEFAULT 0,
      FIRSTCLASS_SEATS_TAKEN INTEGER DEFAULT 0
   );


ALTER TABLE FLIGHTAVAILABILITY
   ADD CONSTRAINT FLIGHTAVAIL_PK Primary Key (
      FLIGHT_ID,
      SEGMENT_NUMBER,
      FLIGHT_DATE);

ALTER TABLE FLIGHTAVAILABILITY
   ADD CONSTRAINT FLIGHTS_FK2 Foreign Key (
      FLIGHT_ID,
      SEGMENT_NUMBER)
   REFERENCES FLIGHTS (
      FLIGHT_ID,
      SEGMENT_NUMBER);


-- \************************************************************\

CREATE TABLE MAPS
   (
      MAP_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
      MAP_NAME VARCHAR(24) NOT NULL,
      REGION VARCHAR(26),
      AREA DECIMAL(8,4) NOT NULL,
      PHOTO_FORMAT VARCHAR(26) NOT NULL,
      PICTURE BLOB(102400),
      UNIQUE (MAP_ID, MAP_NAME)
   );


-- \************************************************************\

CREATE TABLE FLIGHTS_HISTORY
   (
      FLIGHT_ID CHAR(6),
      SEGMENT_NUMBER INTEGER,
      ORIG_AIRPORT CHAR(3),
      DEPART_TIME TIME,
      DEST_AIRPORT CHAR(3),
      ARRIVE_TIME TIME,
      MEAL CHAR(1),
      FLYING_TIME DOUBLE PRECISION,
      MILES INTEGER,
      AIRCRAFT VARCHAR(6), 
      STATUS VARCHAR (20)
   );


-- \************************************************************\

CREATE TRIGGER TRIG1 AFTER UPDATE ON FLIGHTS REFERENCING OLD AS UPDATEDROW FOR EACH ROW INSERT INTO FLIGHTS_HISTORY VALUES (UPDATEDROW.FLIGHT_ID, UPDATEDROW.SEGMENT_NUMBER, UPDATEDROW.ORIG_AIRPORT,UPDATEDROW.DEPART_TIME, UPDATEDROW.DEST_AIRPORT,UPDATEDROW.ARRIVE_TIME,UPDATEDROW.MEAL, UPDATEDROW.FLYING_TIME, UPDATEDROW.MILES, UPDATEDROW.AIRCRAFT,'INSERTED FROM TRIG1');

CREATE TRIGGER TRIG2 AFTER DELETE ON FLIGHTS FOR EACH STATEMENT
INSERT INTO FLIGHTS_HISTORY (STATUS) VALUES ('INSERTED FROM TRIG2');

COMMIT;