CREATE TABLE client (
  client_id     NUMBER(10)      NOT NULL,
  first_name    VARCHAR2(32)    NOT NULL,
  last_name     VARCHAR2(32)    NOT NULL
);

ALTER TABLE client ADD (CONSTRAINT client_pk PRIMARY KEY (ID));
CREATE SEQUENCE client_id_seq START WITH 1;
CREATE OR REPLACE TRIGGER departments_id_trigger BEFORE INSERT ON client
FOR EACH ROW
BEGIN
  SELECT client_id_seq.NEXTVAL INTO :NEW.id FROM   dual;
END;
/
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 (ID));
CREATE SEQUENCE employee_id_seq START WITH 1;
CREATE OR REPLACE TRIGGER departments_id_trigger BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
  SELECT employee_id_seq.NEXTVAL INTO :NEW.id FROM  dual;
END;
/

CREATE TALBE room (
  room_id       NUMBER(10)      NOT NULL,
  category_id   NUMBER(10)      NOT NULL,
  no            NUMBER(10)      NOT NULL,
);

ALTER TABLE room ADD (CONSTRAINT room_pk PRIMARY KEY (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.id FROM   dual;
END;
/

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 (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.id FROM   dual;
END;
/

CREATE TABLE reservation (
  reservation_id   NUMBER(10)      NOT NULL,
  category_id      NUMBER(10)      NOT NULL,
  room_id          NUMBER(10)      NOT NULL,
  name             VARCHAR2(32)    NOT NULL,
  start_date       DATE            NOT NULL,
  end_date         DATE            NOT NULL,
  status           VARCHAR2(32)
);

ALTER TABLE reservation ADD (CONSTRAINT reservation_pk PRIMARY KEY (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.id FROM   dual;
END;
/


CREATE OR REPLACE PACKAGE hotel_mgmt AS
   FUNCTION new_employee (first_name VARCHAR2, last_name VARCHAR2, role VARCHAR2)
      RETURN NUMBER;

END emp_mgmt;
/


CREATE OR REPLACE PACKAGE BODY hotel_mgmt AS
   FUNCTION new_employee (p_first_name VARCHAR2, p_first_name VARCHAR2, p_first_name VARCHAR2)
      RETURN NUMBER IS new_emp_id;
  BEGIN
    INSERT INTO employee(first_name, last_name, role) VALUES (p_first_name, p_last_name, p_role);
    RETURN(employee_id_seq.CURRVAL);
  END;

END emp_mgmt;
/