-- For testing
DROP TABLE emp_pk_counter cascade;
DROP TABLE employees cascade;
DROP TABLE expenses cascade;
DROP TABLE emp_exp_pk_counter cascade;

-- Employee Data Field
CREATE TABLE employees
(
        employee_pk int8 DEFAULT emp_pk_next(), -- Identifies the employee.
        employee_name text,
        -- ...lots of non-relevent columns omitted ...
        exp_report_seq int4 DEFAULT 0, -- Compound sequence control.
        CONSTRAINT employee_pkey PRIMARY KEY (employee_pk)
);

CREATE RULE nodel_employees AS ON DELETE TO employees
DO NOTHING;

-- Employee Primary Key Counter Table --
CREATE TABLE emp_pk_counter
(       employee_pk int8
);

-- Initialize table with one row on creation --
INSERT INTO emp_pk_counter VALUES (0);

-- Disallow further insertions and deletions --
CREATE RULE noins_emp_pk AS ON INSERT TO emp_pk_counter
DO NOTHING;
CREATE RULE nodel_only_emp_pk AS ON DELETE TO emp_pk_counter
DO NOTHING;

-- Get next available emp_pk value from counter --
CREATE OR REPLACE FUNCTION emp_pk_next()
returns int8 AS
$$
DECLARE
	next_pk int8;
BEGIN
	UPDATE emp_pk_counter set employee_pk = employee_pk + 1;
	SELECT INTO next_pk employee_pk from emp_pk_counter;
	RETURN next_pk;
END;
$$ LANGUAGE 'plpgsql';

-- Get the current counter value. You know theyll ask for it --
CREATE OR REPLACE FUNCTION emp_pk_curr()
RETURNS int8 AS
$$
	SELECT employee_pk from emp_pk_counter;
$$ LANGUAGE 'sql';


------------ for adding expense primary key ------------------

ALTER TABLE employees ADD COLUMN exp_report_counter int8 DEFAULT 0;

CREATE OR REPLACE FUNCTION emp_exp_next(emp_pk int8)
RETURNS int8 AS
$$
DECLARE
   next_pk int8;
BEGIN
   UPDATE employees set exp_report_counter = exp_report_counter + 1
      WHERE employee_pk = emp_pk;
   SELECT INTO next_pk exp_report_counter from employees
      WHERE employee_pk = emp_pk;
   RETURN next_pk;
END;
$$ LANGUAGE 'plpgsql';

--- expense table
CREATE TABLE expenses
(
   employee_pk int4 NOT NULL,
   expense_report_pk int4 NOT NULL, -- set by trigger
   expense_descr text NOT NULL,
   -- ...lots of non-relevent columns omitted ...  --
   CONSTRAINT expense_report_pkey
      PRIMARY KEY (employee_pk, expense_report_pk),
   CONSTRAINT expense_fkey FOREIGN KEY (employee_pk)
      REFERENCES employees (employee_pk)
);
CREATE RULE nodel_expenses AS ON DELETE TO expenses
DO NOTHING;


CREATE OR REPLACE FUNCTION set_exp_pk ()
RETURNS TRIGGER AS
$$
BEGIN
   NEW.expense_report_pk = emp_exp_next(NEW.employee_pk);
   RETURN NEW;
END
$$ language 'plpgsql';
CREATE TRIGGER set_exp_pk BEFORE INSERT ON expenses
FOR EACH ROW EXECUTE PROCEDURE set_exp_pk();