CREATE TABLE airport(
   id int NOT NULL,
   name CHAR(50) NOT NULL,
   location CHAR(50) NOT NULL,
   numberOfPlane int NOT NULL,
   CONSTRAINT airport_pk PRIMARY KEY (id)
);

DROP TABLE airport;


/*-------------------------------*/
CREATE TABLE airplane(
  id int NOT NULL,
  name CHAR(50) NOT NULL,
  numberOfSeat int NOT NULL,
  airport_id int,
  CONSTRAINT airplane_pk PRIMARY KEY(id),
  CONSTRAINT fk_airplane
    FOREIGN KEY (airport_id)
    REFERENCES airport (id)
);

DROP TABLE airplane;


/*-------------------------------*/
CREATE TABLE passenger(
  id int NOT NULL,
  name CHAR(50) NOT NULL,
  phone CHAR(50) NOT NULL,
  mail CHAR(50) NOT NULL,
  CONSTRAINT passenger_pk
    PRIMARY KEY (id)
);

DROP TABLE passenger;


/*-------------------------------*/
CREATE TABLE flight1(
  flightid int NOT NULL,
  passenger_id int NOT NULL,
  airplane_id int NOT NULL,
  departure_time DATE,
  arrival_time DATE,
  fromWhere CHAR(50),
  toWhere CHAR(50),
  PRIMARY KEY (flightid),
  CONSTRAINT flight_fk1
    FOREIGN KEY (airplane_id)
    REFERENCES airplane1 (id),
  CONSTRAINT flight_fk2
    FOREIGN KEY (passenger_id)
    REFERENCES passenger1 (id)
);

DROP TABLE flight1;

----------------insert airport---------------------

DROP PROCEDURE Insert_Airport;
CREATE OR REPLACE PROCEDURE Insert_Airport(airportName IN CHAR, airportLocation IN CHAR, noOfPlane IN int)
  IS
  idAirport int;
  BEGIN
          SELECT MAX(id) INTO idAirport FROM (
          (SELECT id FROM AIRPORT1)
              UNION ALL
          (SELECT id FROM AIRPORT2@LINK_VUDB)
              UNION ALL
          (SELECT id FROM AIRPORT3@LINK_TRUNGDB)
              UNION ALL
          (SELECT id FROM AIRPORT4@LINK_LONGDB)
        );
        idAirport := idAirport + 1;

    IF (noOfPlane < 10 AND airportLocation = 'HN') THEN --Dung site 1
        INSERT INTO AIRPORT1 VALUES (idAirport, airportName, airportLocation, noOfPlane);
    ELSIF (noOfPlane < 10 AND airportLocation = 'HCM') THEN --Vu site 2
        INSERT INTO AIRPORT2@LINK_VUDB VALUES (idAirport, airportName, airportLocation, noOfPlane);
    ELSIF (noOfPlane >= 10 AND airportLocation = 'DN') THEN --Trung site 3
        INSERT INTO AIRPORT3@LINK_TRUNGDB VALUES (idAirport, airportName, airportLocation, noOfPlane);
    ELSIF (noOfPlane >= 10 AND airportLocation = 'Hue') THEN --Long site 4
        INSERT INTO AIRPORT4@LINK_LONGDB VALUES (idAirport, airportName, airportLocation, noOfPlane);
    ELSE
        dbms_output.put_line('Error to insert');
    END IF;
  END;
  SELECT * FROM airport1;



    -----------------insert flight----------------
SELECT * FROM flight3@link_trungdb;

  DROP PROCEDURE Insert_Flight;
CREATE OR REPLACE PROCEDURE Insert_Flight(passengerid IN int, airplaneid IN int, departuretime IN CHAR, arrivaltime IN CHAR, fromwhere IN CHAR, towhere IN CHAR)
  IS
  fid int;

  BEGIN
          SELECT MAX(flightid) INTO fid FROM (
          (SELECT flightid FROM flight1)
              UNION ALL
          (SELECT flightid FROM flight2@LINK_VUDB)
              UNION ALL
          (SELECT flightid FROM flight3@LINK_TRUNGDB)
              UNION ALL
          (SELECT flightid FROM flight4@LINK_LONGDB)
        );
        fid := fid + 1;

    IF (passengerid < 10 AND airplaneid < 10) THEN --Dung site 1
        INSERT INTO FLIGHT1 VALUES (fid, passengerid, airplaneid, TO_DATE(departuretime,'yyyy/mm/dd HH:MI:SS'),TO_DATE(arrivaltime,'yyyy/mm/dd HH:MI:SS'),fromwhere,towhere);
    ELSIF (passengerid < 10 AND airplaneid >= 10) THEN -- site 2
        INSERT INTO FLIGHT2@LINK_VUDB VALUES (fid, passengerid, airplaneid, TO_DATE(departuretime,'yyyy/mm/dd HH:MI:SS'),TO_DATE(arrivaltime,'yyyy/mm/dd HH:MI:SS'),fromwhere,towhere);
    ELSIF (passengerid >= 10 AND airplaneid < 10) THEN -- site 2
        INSERT INTO FLIGHT3@LINK_TRUNGDB VALUES (fid, passengerid, airplaneid, TO_DATE(departuretime,'yyyy/mm/dd HH:MI:SS'),TO_DATE(arrivaltime,'yyyy/mm/dd HH:MI:SS'),fromwhere,towhere);
    ELSIF (passengerid >= 10 AND airplaneid >= 10) THEN -- site 2
        INSERT INTO FLIGHT4@LINK_LONGDB VALUES (fid, passengerid, airplaneid, TO_DATE(departuretime,'yyyy/mm/dd HH:MI:SS'),TO_DATE(arrivaltime,'yyyy/mm/dd HH:MI:SS'),fromwhere,towhere);
    ELSE
        dbms_output.put_line('Error to insert');
    END IF;
  END;

EXEC Insert_Flight(1,1,'2017/01/13 12:10:24','2019/01/13 11:17:10','HN','DN');


---------------------------------------UPDATE AIRPORT--------------------

DROP PROCEDURE Update_Airport;
CREATE OR REPLACE PROCEDURE Update_Airport(
  airport_Location IN CHAR,
  airportName IN CHAR,
  noOfPlane IN int
  )
  IS
  airportLocation CHAR;
  BEGIN
    SELECT COUNT(location) INTO airportLocation FROM (
      (SELECT location FROM AIRPORT1)
        UNION ALL
      (SELECT location FROM AIRPORT2@LINK_VUDB)
        UNION ALL
      (SELECT location FROM AIRPORT3@LINK_TRUNGDB)
        UNION ALL
      (SELECT location FROM AIRPORT4@LINK_LONGDB)
    ) WHERE location = airport_Location;
    IF (airportLocation > 0 AND airport_Location = 'HN' ) THEN --site 1
    BEGIN
      UPDATE AIRPORT1
      SET numberOfPlane = noOfPlane
      WHERE name = airportName;
    END;
    ELSIF (airportLocation > 0 AND airport_Location = 'HCM') THEN -- site 2
    BEGIN
      UPDATE AIRPORT2@LINK_VUDB
      SET numberOfPlane = noOfPlane
      WHERE name = airportName;
    END;
    ELSIF (airportLocation > 0 AND airport_Location = 'DN') THEN --site 3
    BEGIN
      UPDATE AIRPORT3@LINK_TRUNGDB
      SET numberOfPlane = noOfPlane
      WHERE name = airportName;
    END;
    ELSIF (airportLocation > 0 AND airport_Location = 'Hue') THEN --site 4
    BEGIN
      UPDATE AIRPORT4@LINK_LONGDB
      SET numberOfPlane = noOfPlane
      WHERE name = airportName;
    END;
    END IF;
  END;

EXEC Update_Airport ('HN','CS', 100);   --name, location, no.ofplane

----------------FLIGHT------------------
DROP PROCEDURE Update_Flight;
CREATE OR REPLACE PROCEDURE Update_Flight(
  fid IN CHAR,
  passengerid IN int,
  airplaneid IN int,
  departuretime IN CHAR,
  arrivaltime IN CHAR,
  from_where IN CHAR,
  to_where IN CHAR)
  IS
  countid CHAR;
  BEGIN
    SELECT flightid INTO countid FROM (
      (SELECT flightid FROM FLIGHT1)
        UNION ALL
      (SELECT flightid FROM FLIGHT2@LINK_VUDB)
        UNION ALL
      (SELECT flightid FROM FLIGHT3@LINK_TRUNGDB)
        UNION ALL
      (SELECT flightid FROM FLIGHT4@LINK_LONGDB)
    ) WHERE passenger_id = passengerid AND airplane_id = airplaneid;
    IF (passengerid < 10 AND airplaneid <10 ) THEN --site 1
    BEGIN
      UPDATE FLIGHT1
      SET departure_time = TO_DATE(departuretime,'yyyy/mm/dd HH:MI:SS'),
      arrival_time = TO_DATE(arrivaltime,'yyyy/mm/dd HH:MI:SS'),
      fromwhere = from_where,
      towhere=to_where
      WHERE flightid = countid;
    END;
    ELSIF (passengerid < 10 AND airplaneid >= 10) THEN -- site 2
    BEGIN
      UPDATE FLIGHT2@LINK_VUDB
      SET departure_time = TO_DATE(departuretime,'yyyy/mm/dd HH:MI:SS'),
      arrival_time = TO_DATE(arrivaltime,'yyyy/mm/dd HH:MI:SS'),
      fromwhere = from_where,
      towhere=to_where
      WHERE flightid = countid;
    END;
    ELSIF (passengerid >= 10 AND airplaneid <10) THEN --site 3
    BEGIN
      UPDATE FLIGHT3@LINK_TRUNGDB
      SET departure_time = TO_DATE(departuretime,'yyyy/mm/dd HH:MI:SS'),
      arrival_time = TO_DATE(arrivaltime,'yyyy/mm/dd HH:MI:SS'),
      fromwhere = from_where,
      towhere=to_where
      WHERE flightid = countid;
    END;
    ELSIF (passengerid >= 10 AND airplaneid >= 10) THEN --site 4
    BEGIN
      UPDATE FLIGHT4@LINK_LONGDB
      SET departure_time = TO_DATE(departuretime,'yyyy/mm/dd HH:MI:SS'),
      arrival_time = TO_DATE(arrivaltime,'yyyy/mm/dd HH:MI:SS'),
      fromwhere = from_where,
      towhere=to_where
      WHERE flightid = countid;
    END;
    END IF;
  END;

 SELECT * FROM FLIGHT2@LINK_VUDB;