-- Table Client

CREATE TABLE client (
  client_id     NUMBER(10)      NOT NULL,
  first_name    VARCHAR2(32)    NOT NULL,
  last_name     VARCHAR2(32)    NOT NULL,
  is_vip        NUMBER(1)       DEFAULT '0'
);
/
ALTER TABLE client ADD (CONSTRAINT client_pk PRIMARY KEY (client_id));
/
CREATE SEQUENCE client_id_seq START WITH 1;
/
CREATE OR REPLACE TRIGGER client_id_trigger BEFORE INSERT ON client FOR EACH ROW
BEGIN
  SELECT client_id_seq.NEXTVAL INTO :NEW.client_id FROM   dual;
END;
/
-- Table Employee

CREATE TABLE employee (
  employee_id   NUMBER(10)      NOT NULL,
  first_name    VARCHAR2(32)    NOT NULL,
  last_name     VARCHAR2(32)    NOT NULL,
  role          VARCHAR2(32)    NOT NULL
);
/
ALTER TABLE employee ADD (CONSTRAINT employee_pk PRIMARY KEY (employee_id));
/
CREATE SEQUENCE employee_id_seq START WITH 1;
/
CREATE OR REPLACE TRIGGER employee_id_trigger BEFORE INSERT ON employee FOR EACH ROW
BEGIN
  SELECT employee_id_seq.NEXTVAL INTO :NEW.employee_id FROM  dual;
END;
/
-- Table Category
CREATE TABLE category (
  category_id   NUMBER(10)      NOT NULL,
  name          VARCHAR2(32)    NOT NULL,
  price         NUMBER(8,2)     NOT NULL
);
/
ALTER TABLE category ADD (CONSTRAINT category_pk PRIMARY KEY (category_id));
/
CREATE SEQUENCE category_id_seq START WITH 1;
/
CREATE OR REPLACE TRIGGER category_id_trigger BEFORE INSERT ON category
FOR EACH ROW
BEGIN
  SELECT category_id_seq.NEXTVAL INTO :NEW.category_id FROM   dual;
END;
/

-- Table Room

CREATE TABLE room (
  room_id       NUMBER(10)      NOT NULL,
  category_id   NUMBER(10)      NOT NULL,
  room_no       NUMBER(10)      NOT NULL,
  CONSTRAINT room_fk_category
    FOREIGN KEY (category_id)
    REFERENCES category (category_id)
);
/
ALTER TABLE room ADD (CONSTRAINT room_pk PRIMARY KEY (room_id));
/
CREATE SEQUENCE room_id_seq START WITH 1;
/
CREATE OR REPLACE TRIGGER room_id_trigger BEFORE INSERT ON room
FOR EACH ROW
BEGIN
  SELECT room_id_seq.NEXTVAL INTO :NEW.room_id FROM   dual;
END;
/
-- Table reservation

CREATE TABLE reservation (
  reservation_id   NUMBER(10)      NOT NULL,
  client_id        NUMBER(10)      NOT NULL,
  category_id      NUMBER(10)      NOT NULL,
  room_id          NUMBER(10),
  start_date       DATE            NOT NULL,
  end_date         DATE            NOT NULL,
  status           VARCHAR2(32)    DEFAULT 'pending' NOT NULL,
  price_per_night  NUMBER(8,2),
  CONSTRAINT reservation_fk_client
    FOREIGN KEY (client_id)
    REFERENCES client (client_id),
  CONSTRAINT reservation_fk_category
    FOREIGN KEY (category_id)
    REFERENCES category (category_id),
  CONSTRAINT reservation_fk_room
    FOREIGN KEY (room_id)
    REFERENCES room (room_id)
);
/
ALTER TABLE reservation ADD (CONSTRAINT reservation_pk PRIMARY KEY (reservation_id));
/
CREATE SEQUENCE reservation_id_seq START WITH 1;
/
CREATE OR REPLACE TRIGGER reservation_id_trigger BEFORE INSERT ON reservation
FOR EACH ROW
BEGIN
  SELECT reservation_id_seq.NEXTVAL INTO :NEW.reservation_id FROM   dual;
END;
/
CREATE OR REPLACE TRIGGER reservation_date_trigger
  BEFORE INSERT OR UPDATE OR DELETE ON reservation
  FOR EACH ROW
BEGIN
  IF :NEW.end_date < :NEW.start_date  THEN
    Raise_Application_Error (-20343, 'End date after start date.');
  END IF;
END;
/
-- Table client_summary
CREATE TABLE client_summary (
    client_id       NUMBER(10)      NOT NULL,
    first_name      VARCHAR2(32)    NOT NULL,
    last_name       VARCHAR2(32)    NOT NULL,
    how_many_times  NUMBER(10)      DEFAULT 0 NOT NULL,
    how_many_night  NUMBER(10)      DEFAULT 0 NOT NULL,
    CONSTRAINT client_summary_fk_client
      FOREIGN KEY (client_id)
      REFERENCES client (client_id)
);
/
ALTER TABLE client_summary ADD (CONSTRAINT client_summary_pk PRIMARY KEY (client_id));
/
CREATE OR REPLACE TRIGGER reservation_summary_trigger
  AFTER INSERT OR UPDATE OR DELETE ON reservation
  FOR EACH ROW
DECLARE
      v_diff_days       NUMBER;
BEGIN
  IF DELETING THEN
    v_diff_days := :NEW.end_date - :NEW.start_date;
    UPDATE client_summary
    SET    how_many_times = how_many_times - 1, how_many_night = how_many_night - v_diff_days
    WHERE  client_id = :NEW.client_id;
  END IF;

  IF INSERTING THEN
    v_diff_days := :NEW.end_date - :NEW.start_date;
    UPDATE client_summary
    SET    how_many_times = how_many_times + 1, how_many_night = how_many_night + v_diff_days
    WHERE  client_id = :NEW.client_id;
  END IF;

  IF UPDATING THEN
    v_diff_days := (:OLD.end_date - :OLD.start_date) - (:NEW.end_date - :NEW.start_date);
    UPDATE client_summary
    SET    how_many_times = how_many_times, how_many_night = how_many_night + v_diff_days
    WHERE  client_id = :NEW.client_id;
  END IF;
END;
/
INSERT INTO client(first_name, last_name, is_vip) VALUES('Jan', 'Kowalski', 1);
INSERT INTO client(first_name, last_name, is_vip) VALUES('Darek', 'Mazurczak', 0);
INSERT INTO client(first_name, last_name, is_vip) VALUES('Marta', 'Jurek', 0);
INSERT INTO client(first_name, last_name, is_vip) VALUES('Ola', 'Piwowarska', 0);
/
INSERT INTO employee(first_name, last_name, role) VALUES('Marta', 'Wdowa', 'normal');
INSERT INTO employee(first_name, last_name, role) VALUES('Kuba', 'Kwiatowski', 'manager');
/
INSERT INTO category(name, price) VALUES('Niebieski', 100);
INSERT INTO category(name, price) VALUES('Zielony', 200);
/
INSERT INTO room(category_id, room_no) VALUES (1, 10);
INSERT INTO room(category_id, room_no) VALUES (1, 11);
INSERT INTO room(category_id, room_no) VALUES (2, 20);
INSERT INTO room(category_id, room_no) VALUES (2, 21);
/
INSERT INTO reservation(client_id, category_id, room_id, start_date, end_date, status, price_per_night) VALUES (1, 1, 1, SYSDATE - 10, SYSDATE - 7, 'finished', 100);
INSERT INTO reservation(client_id, category_id, room_id, start_date, end_date, status, price_per_night) VALUES (2, 1, 2, SYSDATE - 5, SYSDATE + 10, 'current', 100);
INSERT INTO reservation(client_id, category_id, room_id, start_date, end_date, status, price_per_night) VALUES (3, 2, 3, SYSDATE, SYSDATE + 10, 'pending', 100);
INSERT INTO reservation(client_id, category_id, room_id, start_date, end_date, status, price_per_night) VALUES (4, 2, 3, SYSDATE + 3, SYSDATE + 8, 'pending', 100);
INSERT INTO reservation(client_id, category_id, room_id, start_date, end_date, status, price_per_night) VALUES (4, 2, 4, SYSDATE, SYSDATE + 1, 'pending', 100);
INSERT INTO reservation(client_id, category_id, room_id, start_date, end_date, status, price_per_night) VALUES (4, 2, 4, SYSDATE - 1, SYSDATE + 1, 'pending', 100);
INSERT INTO reservation(client_id, category_id, start_date, end_date, status, price_per_night) VALUES (4, 2, SYSDATE + 5, SYSDATE + 8, 'pending', 100);
/
CREATE TABLE current_employee_id_value(x INT);

INSERT INTO current_employee_id_value VALUES (0);

COMMIT;

CREATE OR REPLACE PACKAGE get_current_employee_id
AS
  FUNCTION Val
  RETURN NUMBER;
  PROCEDURE set_val (
    p_x IN NUMBER );
END;

/

CREATE OR REPLACE PACKAGE BODY get_current_employee_id
AS
  FUNCTION Val
  RETURN NUMBER
  AS
    l_x NUMBER;
  BEGIN
      SELECT x
      INTO   l_x
      FROM   current_employee_id_value;

      RETURN l_x;
  END;
  PROCEDURE Set_val(p_x IN NUMBER)
  AS
    PRAGMA autonomous_transaction;
  BEGIN
      UPDATE current_employee_id_value
      SET    x = p_x;

      COMMIT;
  END;
END;
/
CREATE OR REPLACE PACKAGE hotel_mgmt AS

   FUNCTION new_employee (p_first_name VARCHAR2, p_last_name VARCHAR2, p_role VARCHAR2)
       RETURN NUMBER;

   FUNCTION new_client (p_first_name VARCHAR2, p_last_name VARCHAR2, p_is_vip NUMERIC)
       RETURN NUMBER;

   PROCEDURE print_free_rooms(p_start_date DATE, p_end_date DATE);

   FUNCTION save_new_reservation(
       p_first_name VARCHAR2,
       p_last_name VARCHAR2,
       p_category VARCHAR2,
       p_start_date DATE,
       p_end_date DATE
   )
   RETURN NUMBER;

   FUNCTION save_new_vip_reservation(
       p_first_name VARCHAR2,
       p_last_name VARCHAR2,
       p_category VARCHAR2,
       p_date DATE
   )
   RETURN NUMBER;

   PROCEDURE client_reception (
       p_first_name VARCHAR,
       p_last_name VARCHAR
   );

   PROCEDURE client_release (
       p_first_name VARCHAR,
       p_last_name VARCHAR
   );

   PROCEDURE reservation_cancel (
       p_reservation_id NUMBER
   );

   FUNCTION count_number_of_visited_per_client(
       p_first_name VARCHAR2,
       p_last_Name VARCHAR2
   )
   RETURN NUMBER;

   PROCEDURE print_client_today;

   PROCEDURE remove_old_reservations;

   PROCEDURE reservation_client_raport;
END hotel_mgmt;
/
CREATE OR REPLACE PACKAGE BODY hotel_mgmt AS

  FUNCTION new_employee (p_first_name VARCHAR2, p_last_name VARCHAR2, p_role VARCHAR2)
      RETURN NUMBER
  IS
    new_emp_id NUMBER;
  BEGIN
    INSERT INTO employee(first_name, last_name, role) VALUES (p_first_name, p_last_name, p_role);
    RETURN(employee_id_seq.CURRVAL);
  END;

  FUNCTION new_client (p_first_name VARCHAR2, p_last_name VARCHAR2, p_is_vip NUMERIC)
      RETURN NUMBER
  IS
  BEGIN
    DECLARE
      v_client client%ROWTYPE;
    BEGIN
      SELECT * INTO v_client FROM client WHERE first_name = p_first_name AND last_name = p_last_name;
      Raise_Application_Error (-20343, 'Client found.');
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        INSERT INTO client(first_name, last_name, is_vip) VALUES (p_first_name, p_last_name, p_is_vip);
    END;
    RETURN(client_id_seq.CURRVAL);
  END;

  PROCEDURE print_free_rooms(p_start_date DATE, p_end_date DATE)
  IS
    v_room room%ROWTYPE;
    v_reservation reservation%ROWTYPE;
    v_category category%ROWTYPE;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('In period ' || p_start_date || ' - ' || p_end_date || ' free rooms:');
    FOR v_room IN (SELECT * FROM room) LOOP
      DECLARE
        is_found NUMERIC(1) := 0;
      BEGIN
        FOR v_reservation IN (SELECT * FROM reservation) LOOP
          IF v_room.room_id = v_reservation.room_id AND
            NOT (
              (v_reservation.start_date < p_start_date AND v_reservation.end_date < p_start_date)  OR
              (v_reservation.start_date > p_end_date AND v_reservation.end_date > p_end_date)
          ) THEN
            is_found := 1;
          END IF;
        END LOOP;
        IF is_found = 0 THEN
          SELECT
            * INTO v_category
          FROM
            category
          WHERE
            category_id = v_room.category_id;
          DBMS_OUTPUT.PUT_LINE('Room [id=' || v_room.room_id || ', room_no=' || v_room.room_no || ', room_category=[Category [id = ' || v_category.category_id || ', name = ' || v_category.name || '] ]  ]');
        END IF;
      END;
    END LOOP;
  END;

  FUNCTION save_new_reservation(
      p_first_name VARCHAR2,
      p_last_name VARCHAR2,
      p_category VARCHAR2,
      p_start_date DATE,
      p_end_date DATE
  )
  RETURN NUMBER
  IS
    v_client client%ROWTYPE;
    v_category category%ROWTYPE;
    v_room room%ROWTYPE;
    v_reservation reservation%ROWTYPE;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Reservation creating. ');
    -- Searching for client
    BEGIN
      SELECT
          * INTO v_client
      FROM
          client
      WHERE
          client.first_name = p_first_name AND client.last_name = p_last_name;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Client not found. Create a new client.');
        INSERT INTO client(first_name, last_name) VALUES (p_first_name, p_last_name);
        SELECT
          * INTO v_client
        FROM
          client
        WHERE
          client.first_name = p_first_name AND client.last_name = p_last_name;
    END;
    DBMS_OUTPUT.PUT_LINE('Found client: Client[id=' || v_client.client_id ||', first_name=' || v_client.first_name ||', last_name=' || v_client.last_name ||'], ');
    -- Searching for category
    BEGIN
      SELECT * INTO v_category
      FROM category
      WHERE category.name = p_category;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        Raise_Application_Error (-20343, 'Category not found.');
    END;
    DBMS_OUTPUT.PUT_LINE('Found category: category[id=' || v_category.category_id || ', name=' || v_category.name || ']');

    -- Searching free room
    DECLARE
      v_room2 room%ROWTYPE;
      v_reservation2 reservation%ROWTYPE;
    BEGIN
      FOR v_room2 IN (SELECT * FROM room) LOOP
        DECLARE
          is_found NUMERIC(1) := 0;
        BEGIN
          IF v_room2.category_id = v_category.category_id THEN
            FOR v_reservation2 IN (SELECT * FROM reservation) LOOP
              IF v_room2.room_id = v_reservation2.room_id AND
                NOT (
                  (v_reservation2.start_date < p_start_date AND v_reservation2.end_date < p_start_date)  OR
                  (v_reservation2.start_date > p_end_date AND v_reservation2.end_date > p_end_date)
              )
              THEN
                is_found := 1;
              END IF;
            END LOOP;
            IF is_found = 0 THEN
              v_room := v_room2;
            END IF;
          END IF;
        END;
      END LOOP;
    END;

    -- DBMS_OUTPUT.PUT_LINE('Found room: Room[no=' || v_room.room_no ||']');

    IF v_room.room_id IS NULL THEN
      INSERT INTO reservation(client_id, category_id, start_date, end_date, status) VALUES (
        v_client.client_id, v_category.category_id, p_start_date, p_end_date, 'pending'
      );
      DBMS_OUTPUT.PUT_LINE('Reservation created without room. ');
      DBMS_OUTPUT.PUT_LINE('Reservation[');
      DBMS_OUTPUT.PUT_LINE('    id = ' || reservation_id_seq.CURRVAL || ',');
      DBMS_OUTPUT.PUT_LINE('    client=Client[id=' || v_client.client_id || ', first_name=' || v_client.first_name ||', last_name=' || v_client.last_name ||'], ');
      DBMS_OUTPUT.PUT_LINE('    category=category[name=' || v_category.name || ']');
      DBMS_OUTPUT.PUT_LINE(']; ');
    ELSE
      INSERT INTO reservation(client_id, category_id, room_id, start_date, end_date, status, price_per_night
      ) VALUES (
        v_client.client_id, v_category.category_id, v_room.room_id, p_start_date, p_end_date, 'pending', v_category.price
      );
      DBMS_OUTPUT.PUT_LINE('Reservation created with room. ');
      DBMS_OUTPUT.PUT_LINE('Reservation[');
      -- DBMS_OUTPUT.PUT_LINE('    id = ' || reservation_id_seq.CURRVAL || ',');
      DBMS_OUTPUT.PUT_LINE('    room=Room[no=' || v_room.room_no ||'], ');
      DBMS_OUTPUT.PUT_LINE('    client=Client[id=' || v_client.client_id ||', first_name=' || v_client.first_name ||', last_name=' || v_client.last_name ||'], ');
      DBMS_OUTPUT.PUT_LINE('    category=category[name=' || v_category.name || ']');
      DBMS_OUTPUT.PUT_LINE(']; ');
    END IF;
    RETURN(5);
    -- RETURN(reservation_id_seq.CURRVAL);
  END;

  FUNCTION save_new_vip_reservation(
      p_first_name VARCHAR2,
      p_last_name VARCHAR2,
      p_category VARCHAR2,
      p_date DATE
  )
  RETURN NUMBER
  IS
    v_client client%ROWTYPE;
    v_category category%ROWTYPE;
    v_room room%ROWTYPE;
    v_reservation reservation%ROWTYPE;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Reservation creating. ');
    -- Searching for client
    BEGIN
      SELECT
          * INTO v_client
      FROM
          client
      WHERE
          client.first_name = p_first_name AND client.last_name = p_last_name;
      IF v_client.is_vip = 1 THEN
        Raise_Application_Error (-20343, 'Category is not vip.');
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        Raise_Application_Error (-20343, 'Client not found.');
    END;

    DBMS_OUTPUT.PUT_LINE('Found client: Client[id=' || v_client.client_id ||', first_name=' || v_client.first_name ||', last_name=' || v_client.last_name ||'], ');
    -- Searching for category
    BEGIN
      SELECT * INTO v_category
      FROM category
      WHERE category.name = p_category;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        Raise_Application_Error (-20343, 'Category not found.');
    END;
    DBMS_OUTPUT.PUT_LINE('Found category: category[id=' || v_category.category_id || ', name=' || v_category.name || ']');

    -- Searching free room
    DECLARE
      v_room2 room%ROWTYPE;
      v_reservation2 reservation%ROWTYPE;
    BEGIN
      FOR v_room2 IN (SELECT * FROM room) LOOP
        DECLARE
          is_found NUMERIC(1) := 0;
        BEGIN
          IF v_room2.category_id = v_category.category_id THEN
            FOR v_reservation2 IN (SELECT * FROM reservation) LOOP
              IF v_room2.room_id = v_reservation2.room_id AND
                NOT (
                  (v_reservation2.start_date < p_date AND v_reservation2.end_date < p_date)  OR
                  (v_reservation2.start_date > p_date + 1 AND v_reservation2.end_date > p_date + 1)
                )
              THEN
                is_found := 1;
              END IF;
            END LOOP;
            IF is_found = 0 THEN
              v_room := v_room2;
            END IF;
          END IF;
        END;
      END LOOP;
    END;

    DBMS_OUTPUT.PUT_LINE('Found room: Room[no=' || v_room.room_no ||']');

    IF v_room.room_id IS NULL THEN
      DECLARE
        v_room2 room%ROWTYPE;
        v_reservation2 reservation%ROWTYPE;
        v_found_reservation reservation%ROWTYPE;
      BEGIN
        FOR v_room2 IN (SELECT * FROM room) LOOP
          BEGIN
            IF v_room2.category_id = v_category.category_id THEN
              FOR v_reservation2 IN (SELECT * FROM reservation) LOOP
                IF v_room2.room_id = v_reservation2.room_id AND
                (
                    (v_reservation2.start_date < p_date AND v_reservation2.end_date < p_date)  OR
                    (v_reservation2.start_date > p_date + 1 AND v_reservation2.end_date > p_date + 1)
                )
                THEN
                  v_found_reservation := v_reservation2;
                  v_room := v_room2;
                END IF;
              END LOOP;
            END IF;
          END;
        END LOOP;
        UPDATE reservation
          SET room_id = NULL
        WHERE reservation_id = v_found_reservation.reservation_id;
      END;
      -- INSERT INTO reservation(client_id, category_id, start_date, end_date, status, price_per_night) VALUES (
      --   v_client.client_id, v_category.category_id, p_start_date, p_end_date, 'pending', v_category.price
      -- );
      DBMS_OUTPUT.PUT_LINE('Reservation created without room. ');
      DBMS_OUTPUT.PUT_LINE('Reservation[');
      DBMS_OUTPUT.PUT_LINE('    id = ' || reservation_id_seq.CURRVAL || ',');
      DBMS_OUTPUT.PUT_LINE('    client=Client[id=' || v_client.client_id || ', first_name=' || v_client.first_name ||', last_name=' || v_client.last_name ||'], ');
      DBMS_OUTPUT.PUT_LINE('    category=category[name=' || v_category.name || ']');
      DBMS_OUTPUT.PUT_LINE(']; ');
    ELSE
      -- INSERT INTO reservation(client_id, category_id, room_id, start_date, end_date, status, price_per_night) VALUES (
      --   v_client.client_id, v_category.category_id, v_room.room_id, p_start_date, p_end_date, 'pending', v_category.price
      -- );
      DBMS_OUTPUT.PUT_LINE('Reservation created with room. ');
      DBMS_OUTPUT.PUT_LINE('Reservation[');
      -- DBMS_OUTPUT.PUT_LINE('    id = ' || reservation_id_seq.CURRVAL || ',');
      DBMS_OUTPUT.PUT_LINE('    room=Room[no=' || v_room.room_no ||'], ');
      DBMS_OUTPUT.PUT_LINE('    client=Client[id=' || v_client.client_id ||', first_name=' || v_client.first_name ||', last_name=' || v_client.last_name ||'], ');
      DBMS_OUTPUT.PUT_LINE('    category=category[name=' || v_category.name ||'], ');
      DBMS_OUTPUT.PUT_LINE(']; ');
    END IF;
    -- RETURN(5);
    RETURN(reservation_id_seq.CURRVAL);
  END;

  PROCEDURE client_reception (
      p_first_name VARCHAR,
      p_last_name VARCHAR
  ) IS
    v_reservation reservation%ROWTYPE;
    v_client client%ROWTYPE;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Client reception');
    BEGIN
      SELECT
          * INTO v_client
      FROM
          client
      WHERE
          client.client_id = v_reservation.client_id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      Raise_Application_Error (-20343, 'Client not found. ');
    END;

    FOR v_reservation IN (SELECT * FROM reservation) LOOP
      IF v_reservation.start_date <= SYSDATE AND
        v_reservation.status = 'pending'
        THEN
          UPDATE reservation
            SET status = 'current'
          WHERE reservation_id = v_reservation.reservation_id;
          DBMS_OUTPUT.PUT_LINE('Round reservation: ' ||
            'Reservation[id=' || v_reservation.reservation_id ||
            ', start_date = ' || v_reservation.start_date ||
            ', end_date = ' || v_reservation.end_date || ' ]');
      END IF;
    END LOOP;
  END;

  PROCEDURE client_release (
      p_first_name VARCHAR,
      p_last_name VARCHAR
  ) IS
    v_reservation reservation%ROWTYPE;
    v_client client%ROWTYPE;
    v_sum_price NUMBER;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Client release');
    BEGIN
      SELECT
          * INTO v_client
      FROM
          client
      WHERE
          client.client_id = v_reservation.client_id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      Raise_Application_Error (-20343, 'Client not found. ');
    END;

    FOR v_reservation IN (SELECT * FROM reservation) LOOP
      IF v_reservation.status = 'current' THEN
        UPDATE reservation
          SET status = 'finished'
        WHERE reservation_id = v_reservation.reservation_id;
        DBMS_OUTPUT.PUT_LINE('Reservation[id=' || v_reservation.reservation_id ||
          ', start_date = ' || v_reservation.start_date ||
          ', end_date = ' || v_reservation.end_date || ' ]');
        v_sum_price := v_sum_price + ((v_reservation.end_date - v_reservation.start_date) * v_reservation.price_per_night );
      END IF;
      IF count_number_of_visited_per_client(v_client.first_name, v_client.last_name) > 3 THEN
        DBMS_OUTPUT.PUT_LINE('Topay [sum= ' || v_sum_price
          || ', rabat=' || (v_sum_price * 0.1)
          || ', total=' || (v_sum_price - (v_sum_price * 0.1)) || ']');
      ELSE
        DBMS_OUTPUT.PUT_LINE('Topay [sum= ' || v_sum_price || ']');
      END IF;
    END LOOP;
  END;

  FUNCTION count_number_of_visited_per_client(
      p_first_name VARCHAR2,
      p_last_Name VARCHAR2
  )
  RETURN NUMBER
  IS
    v_cnt NUMBER := 0;
    v_client client%ROWTYPE;
    v_reservation reservation%ROWTYPE;
  BEGIN
    SELECT
        * INTO v_client
    FROM
        client
    WHERE
        client.first_name = p_first_name AND client.last_name = p_last_name;
    FOR v_reservation IN (SELECT * FROM reservation) LOOP
      IF v_reservation.client_id = v_client.client_id
        THEN
        v_cnt := v_cnt + 1;
      END IF;
    END LOOP;
    RETURN(v_cnt);
  END;

  PROCEDURE print_client_today
  IS
    v_client client%ROWTYPE;
    v_reservation reservation%ROWTYPE;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('print_client_today');
    FOR v_reservation IN (SELECT * FROM reservation) LOOP
      IF v_reservation.start_date <= SYSDATE AND
        v_reservation.end_date >= SYSDATE AND
        v_reservation.status = 'pending'
        THEN
        SELECT
            * INTO v_client
        FROM
            client
        WHERE
            client.client_id = v_reservation.client_id;
        DBMS_OUTPUT.PUT_LINE('Client[' || v_client.client_id || '] : ' || v_client.first_name || ' ' || v_client.last_name || ' | Reservation [' || v_reservation.reservation_id || ']  '  || v_reservation.start_date || ' - ' || v_reservation.end_date);
      END IF;
    END LOOP;
  END;

  PROCEDURE remove_old_reservations
  IS
    v_reservation reservation%ROWTYPE;
    v_cnt NUMBER := 0;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('remove_old_reservations');
    FOR v_reservation IN (SELECT * FROM reservation) LOOP
      IF v_reservation.end_date < SYSDATE - 5*12*30 THEN
        DELETE FROM reservation WHERE reservation_id = v_reservation.reservation_id;
        v_cnt := v_cnt + 1;
      END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Deleted ' || v_cnt || ' reservations');
  END;

  PROCEDURE reservation_cancel (
      p_reservation_id NUMBER
  )
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('reservation_cancel');
    DECLARE
      v_employee employee%ROWTYPE;
    BEGIN
      SELECT * INTO v_employee FROM employee WHERE employee_id = get_current_employee_id.Val;
      IF v_employee.role != 'manager' THEN
        Raise_Application_Error (-20343, 'Employee not authorized. ');
      END IF;
      DBMS_OUTPUT.PUT_LINE('Employee authorized.');
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      Raise_Application_Error (-20343, 'Employee not found.');
    END;

    DELETE FROM reservation WHERE reservation_id = p_reservation_id;
    IF SQL%NOTFOUND THEN
      DBMS_OUTPUT.PUT_LINE('Reservation not found.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Reservation canceled.');
    END IF;
  END;

  PROCEDURE reservation_client_raport
  IS
    v_client client%ROWTYPE;
    v_reservation reservation%ROWTYPE;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Raport generating.');
    FOR v_client IN (SELECT * FROM client) LOOP
      DECLARE
        v_count NUMBER;
        v_sum_night NUMBER;
      BEGIN
        v_count := 0;
        v_sum_night := 0;
        FOR v_reservation IN (SELECT * FROM reservation) LOOP
          IF v_reservation.client_id = v_client.client_id THEN
            v_sum_night := v_sum_night + FLOOR(
                (TO_DATE(v_reservation.end_date, 'yyyy/mm/dd') - TO_DATE(v_reservation.start_date, 'yyyy/mm/dd'))/365
            );
            v_count := v_count + 1;
          END IF;
        END LOOP;
        BEGIN
          INSERT INTO client_summary (
            client_id, first_name, last_name , how_many_times, how_many_night
          ) VALUES (
              1, v_client.first_name, v_client.last_name , v_count, v_sum_night
          );
          DBMS_OUTPUT.PUT_LINE('Inserted Client[ ' ||
            'client_id='|| v_client.client_id ||', ' ||
            'first_name='|| v_client.first_name ||', ' ||
            'last_name='|| v_client.last_name ||',  ' ||
            'v_sum_day=' || v_sum_night || ', ' ||
            'v_count=' || v_count || ']');
        EXCEPTION
         WHEN DUP_VAL_ON_INDEX THEN
            UPDATE client_summary
            SET    how_many_times = v_count, how_many_night = v_sum_night
            WHERE  client_id = v_client.client_id;
            DBMS_OUTPUT.PUT_LINE('Update Client[ ' ||
              'client_id='|| v_client.client_id ||', ' ||
              'first_name='|| v_client.first_name ||', ' ||
              'last_name='|| v_client.last_name ||',  ' ||
              'v_sum_day=' || v_sum_night || ', ' ||
              'v_count=' || v_count || ']');
        END;
      END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Raport generated.');
  END;
END hotel_mgmt;

/
DECLARE
  v_client client%ROWTYPE;
BEGIN
  get_current_employee_id.set_val(2);
  DBMS_OUTPUT.PUT_LINE('Current employee id: ' ||  get_current_employee_id.Val);
  hotel_mgmt.print_client_today;
  hotel_mgmt.print_free_rooms(SYSDATE + 1, SYSDATE + 8);

  DBMS_OUTPUT.PUT_LINE('Summary client visit count');

  FOR v_client IN (SELECT * FROM client) LOOP
    DBMS_OUTPUT.PUT_LINE(v_client.first_name || ' ' || v_client.last_name || ' = ' ||
      TO_CHAR(hotel_mgmt.count_number_of_visited_per_client(v_client.first_name, v_client.last_name)));
  END LOOP;

    DBMS_OUTPUT.PUT_LINE('New client ID = ' ||   hotel_mgmt.save_new_reservation(
       'Jan',
       'Kowalski',
       'Niebieski',
       SYSDATE + 15,
       SYSDATE + 20
   ));

   hotel_mgmt.reservation_client_raport;
   hotel_mgmt.reservation_cancel(6);
   hotel_mgmt.remove_old_reservations;
   DBMS_OUTPUT.PUT_LINE('New client ID = ' || hotel_mgmt.new_client('Kamil', 'Bregula', 1));
END;