DROP TABLE departments CASCADE CONSTRAINTS;

DROP TABLE employees CASCADE CONSTRAINTS;

DROP TABLE jobs CASCADE CONSTRAINTS;

DROP TABLE job_history CASCADE CONSTRAINTS;

DROP TABLE locations CASCADE CONSTRAINTS;

DROP TABLE countries CASCADE CONSTRAINTS;

DROP TABLE regions CASCADE CONSTRAINTS;


DROP TYPE department_t;

DROP TYPE employee_t;

DROP TYPE jobs_t;

DROP TYPE job_history_t;

DROP TYPE zone_t;

DROP TYPE region_t;

DROP TYPE location_t;

DROP TYPE country_t;

DROP TYPE department_employees;

DROP TYPE department_jobs;


-- Departments --
CREATE OR REPLACE TYPE department_employees AS VARRAY(107) of NUMBER(5);
CREATE OR REPLACE TYPE department_jobs AS VARRAY(19) of NUMBER(2);

CREATE OR REPLACE TYPE department_t AS OBJECT(
    department_id NUMBER(4,0),
    department_name VARCHAR2(30),
    location_id REF location_t,
    manager_id REF employee_t,
    dep_employees department_employees,
    dep_jobs department_jobs,
    map member function get_ID return NUMBER,
    member function get_total_jobs return NUMBER,
    member function get_total_employees return NUMBER,
    member function get_highest_salary return NUMBER
   );

CREATE OR REPLACE TYPE BODY department_t AS
     map member function get_ID return NUMBER is
        begin
        return department_id;
        end get_ID;
     member function get_total_jobs return NUMBER is
        total number(3) := 0;
        begin
        for m in 1..dep_jobs.count loop
        if not(dep_jobs(m) is NULL) then
        total := total + dep_jobs(m);
        end if;
        end loop;
        return total;
        end get_total_jobs;
      member function get_total_employees return NUMBER is
        amount NUMBER(3) := 0;
        BEGIN
        FOR i in 1..dep_employees.count
        loop
        if(dep_employees(i) is not null)
        then amount := amount + 1;
        end if;
        end loop;
        end get_total_employees;
      member function get_highest_salary return NUMBER is
        employee NUMBER(3) := 0;
        BEGIN
        FOR i in 1..dep_employees.count
        loop
        if(dep_employees(i)>dep_employees(employee))
        then employee := i;
        end if;
        end loop;
        employee := employee + 100 - 1;
        END get_highest_salary;
end;

-- Employees --
CREATE OR REPLACE TYPE employee_t AS OBJECT(
    employee_id NUMBER(6,0),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    email VARCHAR2(25),
    phone_number VARCHAR2(20),
    hire_date DATE,
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    department_id ref department_t,
    job_id ref jobs_t,
    manager_id ref employee_t
   );

-- Jobs --
CREATE OR REPLACE TYPE jobs_t AS OBJECT(
    job_id VARCHAR2(10),
    job_title VARCHAR2(35),
    min_salary NUMBER(6,0),
    max_salary NUMBER(6,0)
   );

-- Job History --
CREATE OR REPLACE TYPE job_history_t AS OBJECT(
    start_date DATE,
    end_date DATE,
    job_id REF jobs_t,
    employee_id REF employee_t,
    department_id REF department_t
   );

-- Zone --
CREATE OR REPLACE TYPE zone_t AS OBJECT(
    id NUMBER(4,0),
    name VARCHAR2(40),
    map member function get_id return number,
    member function get_name return varchar2
   ) not final;

CREATE OR REPLACE TYPE BODY zone_t AS
    map member function get_id return number is
        begin
        return id;
        end get_id;
    member function get_name return varchar2 is
        begin
        return name;
        end get_name;
end;


-- Regions --
CREATE OR REPLACE TYPE region_t UNDER zone_t(
    overriding member function get_name return varchar2
   );

CREATE OR REPLACE TYPE BODY region_t AS
    overriding member function get_name return varchar2 is
        begin
        return 'Region: ' || name;
        end get_name;
end;

-- Countries --
CREATE OR REPLACE TYPE country_t UNDER zone_t(
    region ref region_t,
    overriding member function get_name return varchar2
   );

CREATE OR REPLACE TYPE BODY country_t AS
    overriding member function get_name return varchar2 is
        begin
        return 'Country: ' || name;
        end get_name;
end;

-- Locations --
CREATE OR REPLACE TYPE location_t UNDER zone_t (
    postal_code VARCHAR2(12),
    city VARCHAR2(30),
    state_province VARCHAR2(25),
    country ref country_t,
    overriding member function get_name return varchar2
   );

CREATE OR REPLACE TYPE BODY location_t AS
    overriding member function get_name return varchar2 is
        begin
        return 'Location: ' || name;
        end get_name;
end;


--------------------------------------------------------
------------------   CREATE TABLES   -------------------
--------------------------------------------------------

CREATE TABLE departments OF department_t;
CREATE TABLE employees OF employee_t;
CREATE TABLE jobs OF jobs_t;
CREATE TABLE job_history OF job_history_t;
CREATE TABLE locations OF location_t;
CREATE TABLE countries OF country_t;
CREATE TABLE regions OF region_t;