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 airplane---------------
  DROP PROCEDURE Insert_Airplane;
CREATE PROCEDURE Insert_Airplane(airplaneName IN CHAR, noOfSeat IN int, airportID IN int)
  IS
  idAirplane int;
  BEGIN
    SELECT MAX(id) INTO idAirplane FROM (
          (SELECT id FROM AIRPLANE1)
              UNION ALL
          (SELECT id FROM AIRPLANE2@LINK_VUDB)
              UNION ALL
          (SELECT id FROM AIRPLANE3@LINK_TRUNGDB)
              UNION ALL
          (SELECT id FROM AIRPLANE4@LINK_LONGDB)
        );
        idAirplane := idAirplane + 1;

    IF (idAirplane < 5) THEN --Dung
        INSERT INTO AIRPLANE1 VALUES (idAirplane, airplaneName, noOfSeat, airportID);
    ELSIF (idAirplane >= 5 AND idAirplane < 10 ) THEN --Vu
        INSERT INTO AIRPLANE2@LINK_VUDB VALUES (idAirplane, airplaneName, noOfSeat, airportID);
    ELSIF (idAirplane >= 10 AND idAirplane < 15) THEN --Trung
        INSERT INTO AIRPLANE3@LINK_TRUNGDB VALUES (idAirplane, airplaneName, noOfSeat, airportID);
    ELSIF (idAirplane >= 15 AND idAirplane < 20) THEN --Long
        INSERT INTO AIRPLANE4@LINK_LONGDB VALUES (idAirplane, airplaneName, noOfSeat, airportID);
    ELSE
        dbms_output.put_line('Error');
    END IF;
  END;
  EXEC Insert_Airplane('trungAIRPLANE55', 500, 56);

    -----------------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,'2019/01/12 12:10:24','2019/01/13 11:17:10','HCM','DN');
SELECT * FROM Flight1;

---------------------------------------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

----------------Update 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 int;
  BEGIN
    SELECT MAX(flightid) INTO countid FROM (
      (SELECT flightid,passenger_id,airplane_id FROM FLIGHT1)
        UNION ALL
      (SELECT flightid,passenger_id,airplane_id FROM FLIGHT2@LINK_VUDB)
        UNION ALL
      (SELECT flightid,passenger_id,airplane_id FROM FLIGHT3@LINK_TRUNGDB)
        UNION ALL
      (SELECT flightid,passenger_id,airplane_id 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;

 EXEC Update_Flight(3,1,1,'2017/01/20 10:10:09','2018/01/09 12:10:18','HN','Hue');
 SELECT * FROM FLIGHT1;


---delete airplane
DROP PROCEDURE deleteAirplane;
CREATE PROCEDURE deleteAirplane (
aID IN int
)
IS
a_ID int;
BEGIN
SELECT MAX(id) INTO a_ID FROM (
  (SELECT id FROM airplane1)
  UNION ALL
(SELECT id FROM airplane2@link_vudb)
  UNION ALL
  (SELECT id FROM airplane3@link_trungdb)
  UNION ALL
  (SELECT id FROM airplane4@link_longdb)
  );
   IF(a_ID <5) THEN
   BEGIN
   DELETE FROM airplane1
   WHERE id = aID;
   END;
   ELSIF(a_id >=5 AND a_id <10) THEN
   BEGIN
   DELETE FROM airplane2@link_vudb
   WHERE id = aID;
   END;
   ELSIF (a_id >=10 AND a_ID<15) THEN
   BEGIN
   DELETE FROM airplane3@link_trungdb
   WHERE id=aid;
   END;
   ELSIF (a_id>=15 AND a_id <20) THEN
   BEGIN
   DELETE FROM airplane4@link_longdb
   WHERE id=aid;
   END;
   END IF;
END;

EXEC deleteairplane(10); --id