-- hr.sql

CREATE OR REPLACE PACKAGE hr AS
   TYPE min_wage_type IS TABLE OF NUMBER INDEX BY VARCHAR2(2);
   TYPE emp_type IS RECORD (eid NUMBER, pay NUMBER, ptc CHAR);
   TYPE emp_array_type IS TABLE OF emp_type;
   SUBTYPE money_type IS NUMBER(9,2);

   CURSOR emp_cur(store CHAR) RETURN emp_type;

   pay_below_min_wage EXCEPTION;
   invalid_store EXCEPTION;
   invalid_state EXCEPTION;
   invalid_person EXCEPTION;

   PROCEDURE add_employee(pers_id person.id%TYPE,
      store employee.store_number%TYPE, 
      title employee.title%TYPE DEFAULT NULL,
      pay money_type DEFAULT NULL, 
      ptc employee.pay_type_code%TYPE DEFAULT NULL,
      sup employee.supervisor_id%TYPE DEFAULT NULL);

   FUNCTION get_emps(store_num NUMBER) RETURN emp_array_type;
   FUNCTION get_manager(store_num NUMBER) RETURN NUMBER;
   FUNCTION get_state_min_wage(state CHAR) RETURN NUMBER;
END hr;
/

CREATE OR REPLACE PACKAGE BODY hr AS
   min_wages min_wage_type;   -- private variable. Use get_state_min_wage to 
                              -- access it from outside program.
   CURSOR emp_cur(store CHAR) RETURN emp_type IS 
      SELECT id, pay_amount, pay_type_code
        FROM employee
       WHERE store_number = store
    ORDER BY id;

   /* Forward Declarations of private subprograms */
   FUNCTION get_store_state(st_num NUMBER) RETURN VARCHAR2;

   FUNCTION get_state_min_wage(state CHAR) RETURN NUMBER AS
      min_wage NUMBER := min_wages(state); -- From the associative array
   BEGIN
      IF min_wage IS NULL THEN
         RAISE invalid_state;
      END IF;
      RETURN min_wages(state);
   END get_state_min_wage;

   -- Public functions declared in Specification
   PROCEDURE add_employee(pers_id person.id%TYPE,
      store employee.store_number%TYPE, 
      title employee.title%TYPE DEFAULT NULL,
      pay money_type DEFAULT NULL, 
      ptc employee.pay_type_code%TYPE DEFAULT NULL,
      sup employee.supervisor_id%TYPE DEFAULT NULL) 
   AS
      min_wage NUMBER;
      person_count NUMBER;
   BEGIN
      min_wage := hr.get_state_min_wage(get_store_state(store));
      IF pay < min_wage THEN
         RAISE pay_below_min_wage;
      END IF;

      SELECT count(*) INTO person_count 
        FROM person
       WHERE id = pers_id;

      IF person_count != 1 THEN
        RAISE invalid_person;
      END IF;

      INSERT INTO employee 
         (id, store_number, pay_type_code, pay_amount, title, supervisor_id)
      VALUES (pers_id, store, ptc, pay, title, sup);

   EXCEPTION
      WHEN pay_below_min_wage THEN
         raise_application_error(-20003, 'Pay amount must be at least ' || 
            min_wage || ' for employees in ' || get_store_state(store) || '.'); 
      WHEN invalid_store THEN
	 raise_application_error(-20006, 'Invalid store.');
      WHEN invalid_person THEN
	 raise_application_error(-20006, 'Person doesn''t exist.');
   END add_employee;

   FUNCTION get_emps(store_num NUMBER) RETURN emp_array_type AS
      emps emp_array_type := emp_array_type();
      i NUMBER := 1;  --counter
   BEGIN
      FOR rec IN emp_cur(store_num) LOOP
         emps.EXTEND;
	 emps(i) := rec;
         i := i + 1;
      END LOOP;
      RETURN emps;
   END get_emps;

   FUNCTION get_manager(store_num NUMBER) RETURN NUMBER AS
      mid store.manager_id%TYPE;
   BEGIN
      SELECT id
        INTO mid 
        FROM employee 
       WHERE id = (SELECT manager_id 
                     FROM store
                    WHERE store_number = store_num); 
      RETURN mid;
   END get_manager;

   /* The next function is private, since it's not declared in
      the spec. */
   FUNCTION get_store_state(st_num NUMBER) RETURN VARCHAR2 AS
      st store.state%TYPE; 
   BEGIN
      SELECT state
      INTO st 
      FROM store
      WHERE store_number = st_num;
      RETURN st;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
        RAISE invalid_store;
   END get_store_state;

   /* The BEGIN block is an optional block where you can initialize
      variables. It must appear after all other definitions.
   */
   BEGIN
      min_wages('CO') := 5.15;
      min_wages('CA') := 6.75;
      min_wages('WA') := 7.35;
      min_wages('UT') := 5.15;
      min_wages('NV') := 5.15;
END hr;
/
