CREATE TABLE airport1(
   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 airport1;

SELECT * FROM airport1;
/*-------------------------------*/
CREATE TABLE airplane1(
  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 airport1 (id)
);

DROP TABLE airplane1;


/*-------------------------------*/
CREATE TABLE passenger1(
  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 passenger1;


/*-------------------------------*/
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;





-------------Procedure-------------------



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;

  ------ insert airplane---------------



  DROP PROCEDURE Insert_Airplane;
CREATE OR REPLACE PROCEDURE Insert_Airplane(airplaneName IN CHAR, noOfSeat IN int, airportID IN int, loc IN CHAR)
  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 (loc ='HN') THEN --Dung
        INSERT INTO AIRPLANE1 VALUES (idAirplane, airplaneName, noOfSeat, airportID);
    ELSIF (loc = 'HCM' ) THEN --Vu
        INSERT INTO AIRPLANE2@LINK_VUDB VALUES (idAirplane, airplaneName, noOfSeat, airportID);
    ELSIF (loc = 'DN') THEN --Trung
        INSERT INTO AIRPLANE3@LINK_TRUNGDB VALUES (idAirplane, airplaneName, noOfSeat, airportID);
    ELSIF (loc = 'HUE') 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----------------

  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, Loc 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 ( Loc ='HN') 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 ( Loc ='HCM') 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 ( Loc = 'DN') 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 ( Loc = 'Hue' ) 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;


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


----------------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,
  Loc IN CHAR)
  IS
  countid int;
  BEGIN
    SELECT COUNT(location) INTO countid FROM (
      (SELECT location FROM FLIGHT1)
        UNION ALL
      (SELECT location FROM FLIGHT2@LINK_VUDB)
        UNION ALL
      (SELECT location FROM FLIGHT3@LINK_TRUNGDB)
        UNION ALL
      (SELECT location FROM FLIGHT4@LINK_LONGDB)
    ) WHERE location = Loc;
    IF (countid >0 AND Loc = 'HN' ) 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 = fid;
    END;
    ELSIF (countid >0 AND Loc ='HCM') 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 = fid;
    END;
    ELSIF (countid >0 AND Loc ='DN') 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 = fid;
    END;
    ELSIF (countid >0 AND Loc = 'HUE') 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 = fid;
    END;
    END IF;
  END;


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