CREATE TABLE employees (
	emp_no     INT8 NOT NULL,
	birth_date DATE NOT NULL,
	first_name VARCHAR(14) NOT NULL,
	last_name  VARCHAR(16) NOT NULL,
	gender     CHAR NOT NULL,
	hire_date  DATE NOT NULL,
	PRIMARY KEY (emp_no)
);

CREATE TABLE titles (
	emp_no    INT8 NOT NULL,
	title     VARCHAR(50) NOT NULL,
	from_date DATE NOT NULL,
	to_date   DATE,
	FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
	PRIMARY KEY (emp_no, title, from_date)
);

CREATE TABLE salaries (
	emp_no    INT8 NOT NULL,
	salary    INT8 NOT NULL,
	from_date DATE NOT NULL,
	to_date   DATE NOT NULL,
	FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
	PRIMARY KEY (emp_no, from_date)
);

CREATE TABLE salary_audit (
	audit_date DATE NOT NULL,
	emp_no     INT8 NOT NULL,
	sal_date   DATE NOT NULL,
	title      VARCHAR(50) NOT NULL,
	title_date DATE NOT NULL,
	valid      BOOL NOT NULL,
	FOREIGN KEY (emp_no, sal_date) REFERENCES salaries (emp_no, from_date),
	FOREIGN KEY (emp_no, title, title_date) REFERENCES titles (emp_no, title, from_date),
	FOREIGN KEY (emp_no) REFERENCES employees (emp_no),
	PRIMARY KEY (audit_date, emp_no, sal_date)
);

CREATE OR REPLACE FUNCTION f_random_text(
    length integer
)
RETURNS text AS
$body$
SELECT string_agg(_char, '')
FROM (SELECT _char FROM (SELECT unnest(string_to_array('A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 0 1 2 3 4 5 6 7 8 9', ' ')) AS _char) chars ORDER BY random() LIMIT $1) charlist;
$body$
LANGUAGE sql;

CREATE OR REPLACE FUNCTION f_random_gender()
RETURNS char AS
$body$
SELECT string_agg(_char, '')
FROM (SELECT _char FROM (SELECT unnest(string_to_array('M F N', ' ')) AS _char) chars ORDER BY random() LIMIT 1) charlist;
$body$
LANGUAGE sql;

INSERT INTO employees
  SELECT 
    generate_series(0, 100), 
    (now() - trunc(random() * 365)::int * '1 day'::interval - 20 * '1 year'::interval - trunc(random() * 60)::int * '1 year'::interval)::date, 
    f_random_text(10), 
    f_random_text(10), 
    f_random_gender(), 
    (now() - trunc(random() * 365)::int * '1 day'::interval - trunc(random() * 10)::int * '1 year'::interval)::date;
   
INSERT INTO titles
  SELECT 
    generate_series(0, 100),
    f_random_text(20),
    (now() - trunc(random() * 365)::int * '1 day'::interval - trunc(random() * 5)::int * '1 year'::interval)::date,
    (now() - trunc(random() * 365)::int * '1 day'::interval - trunc(random() * 5)::int * '1 year'::interval)::date;

INSERT INTO salaries 
  SELECT 
    generate_series(0, 100),
    random() * 200000 + 50000,
    (now() - trunc(random() * 365)::int * '1 day'::interval - trunc(random() * 5)::int * '1 year'::interval)::date,
    (now() - trunc(random() * 365)::int * '1 day'::interval - trunc(random() * 5)::int * '1 year'::interval)::date;

INSERT INTO salary_audit
  SELECT
    (now() - trunc(random() * 365)::int * '1 day'::interval)::date,
    s.emp_no,
    s.from_date,
    t.title,
    t.from_date,
    't'::bool
  FROM salaries s, titles t
  WHERE s.emp_no = t.emp_no
  ORDER BY random()
  LIMIT 100;

INSERT INTO salary_audit
  SELECT * FROM (SELECT
    (now() - trunc(random() * 365)::int * '1 day'::interval)::date,
    s.emp_no,
    s.from_date,
    t.title,
    t.from_date,
    't'::bool
  FROM salaries s, titles t
  WHERE s.emp_no = t.emp_no
  ORDER BY random()
  LIMIT 100);