-- ---------------------------------------------------------
-- CLEANUP IF DATABASE ALREADY EXISTS
--
DROP SCHEMA IF EXISTS tb CASCADE;
DROP SCHEMA IF EXISTS ws CASCADE;
DROP SCHEMA IF EXISTS op CASCADE;
DROP USER IF EXISTS "htsql_regress.owner";
DROP USER IF EXISTS "htsql_regress.user";
DROP USER IF EXISTS "htsql_regress.limited";
DROP ROLE IF EXISTS "htsql_regress.staff";
DROP ROLE IF EXISTS "htsql_regress.public";
DROP ROLE IF EXISTS "htsql_regress.clerk";
DROP ROLE IF EXISTS "htsql_regress.admin";

-- ---------------------------------------------------------
-- CREATE AUTHORITIES
--
-- There are 2 users and 4 roles that are created.  The roles form a
-- diamond structure, to simulate multiple-base-class problem for the
-- database representation.
--
--          STAFF
--        /      \
-- PUBLIC         ADMIN
--        \      /
--          CLERK
--
SET TIMEZONE TO -8;
SET CLIENT_ENCODING='UTF-8';
CREATE USER "htsql_regress.owner" WITH CREATEDB CREATEROLE CREATEUSER;
SET SESSION AUTHORIZATION "htsql_regress.owner";
CREATE USER "htsql_regress.limited";
CREATE USER "htsql_regress.user";
CREATE ROLE "htsql_regress.admin";
CREATE ROLE "htsql_regress.staff";
CREATE ROLE "htsql_regress.public";
CREATE ROLE "htsql_regress.clerk";

GRANT "htsql_regress.public" TO "htsql_regress.clerk";
GRANT "htsql_regress.public" TO "htsql_regress.staff";
GRANT "htsql_regress.clerk" TO "htsql_regress.admin";
GRANT "htsql_regress.staff" TO "htsql_regress.admin";
GRANT "htsql_regress.public" TO "htsql_regress.limited";
GRANT "htsql_regress.admin" TO "htsql_regress.user";


-- ---------------------------------------------------------
-- OP - ORGANIZATIONAL PLANNING
--
CREATE SCHEMA op;
GRANT USAGE ON SCHEMA op TO "htsql_regress.public";
COMMENT ON SCHEMA op IS E'
---
title: Organizational Planning
notes: >
  This schema tests HTSQL using three data structure patterns and two
  link patterns.  The project and organization tables are trunks, person
  is a branch, and participation is a cross-product.  The organization
  table demonstrates a recursive link, and finally, the project table is
  categorized by organization.  The schema also has a foreign key, from
  participation to person, that refers to a unique candidate key, but not
  the foreign key of the target table.

  The schema also shows several column types.  The status column is an
  authority controlled field, start_date is a date, description is a
  unlimited length memo field, is_active is a boolean, billing_rate is
  numeric, email has a check constraint (for presence of the at sign),
  and capacity is a comma-separated list field, e.g., poor mans array.

diagram: |
  +-------------------+              +-------------------+
  | OP.PROJECT        |              | OP.ORGANIZATION   |
  +-------------------+              +-------------------+
  | prj_id         PK |--\       /---| org_id         PK |---\ ---\
  | name        NN,UK |  |       |   | name           NN |   |    |
  | status      NN,CK |  |       |   | is_active         |   |    |
  | client         FK |>---------/   | division_of    FK |>--/    |
  | start_date        |  |    .      +-------------------+  .     |
  | description       |  |     .                           .      |
  +-------------------+  |   project is        an organization    |
                        .|   related to        may be a division  |
    a project has zero . |   at most one       of a larger        |
    or more people who   |   organization      organization       |
    participate in it    |                                        |
                         |     +-------------------+              |
  +-------------------+  |     | OP.PERSON         |              |
  | OP.PARTICIPATION  |  |     +-------------------+              |
  +-------------------+  |     | org_id     FK,PK1 |>-------------/
  | project    FK,PK1 |>-/     | nickname   NN,PK2 |    .
  | _ppl_seq   FK,PK2 |>-------| _ppl_seq    NN,UK |   .
  | billing_rate      |   .    | full_name      NN |  each person
  +-------------------+  .     | email          UK |  is part of
                        .      +-------------------+  exactly one
    a person participates in                          organization
    zero or more projects

  PK - Primary Key      FK - Foreign Key      NN - Not NULL
  UK - Unique Key       CK - Check Constraint
';

CREATE TABLE op.organization (
    org_id         VARCHAR(16) NOT NULL,
    name           VARCHAR(64) NOT NULL,
    is_active      BOOLEAN DEFAULT TRUE,
    division_of    VARCHAR(16),
    CONSTRAINT organization_pk
      PRIMARY KEY (org_id),
    CONSTRAINT organization_division_of_fk
      FOREIGN KEY (division_of)
      REFERENCES op.organization(org_id)
);
GRANT SELECT ON op.organization TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON op.organization TO "htsql_regress.admin";

CREATE DOMAIN op.status_t AS VARCHAR(16);
ALTER DOMAIN  op.status_t
   ADD CONSTRAINT status_check
     CHECK (VALUE IN ('planned','in-progress','completed','abandoned'));
ALTER DOMAIN op.status_t SET NOT NULL; 
ALTER DOMAIN op.status_t SET DEFAULT 'planned';

CREATE TABLE op.project (
    prj_id         VARCHAR(16) NOT NULL,
    name           VARCHAR(64) NOT NULL,
--  status         op.status_t,  # line below shoul not be required given above
    status         op.status_t NOT NULL DEFAULT 'planned',
    client         VARCHAR(16),
    start_date     DATE,
    description    TEXT,
    CONSTRAINT project_pk
      PRIMARY KEY (prj_id),
    CONSTRAINT project_name_uk UNIQUE (name),
    CONSTRAINT project_organization_fk
      FOREIGN KEY (client)
      REFERENCES op.organization(org_id)
);
GRANT SELECT ON op.project TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON op.project TO "htsql_regress.clerk";

CREATE SEQUENCE op.person_seq START WITH 100;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE op.person_seq TO "htsql_regress.clerk";

CREATE TABLE op.person (
    org_id         VARCHAR(16) NOT NULL,
    nickname       VARCHAR(16) NOT NULL,
    _ppl_seq       INTEGER NOT NULL DEFAULT nextval('op.person_seq'),
    full_name      VARCHAR(64) NOT NULL,
    email          VARCHAR(64),
    CONSTRAINT person_pk
      PRIMARY KEY (org_id, nickname),
    CONSTRAINT person_uk UNIQUE (_ppl_seq),
    CONSTRAINT person_organization_fk
      FOREIGN KEY (org_id)
      REFERENCES op.organization(org_id)
);
GRANT SELECT ON op.person TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON op.person TO "htsql_regress.clerk";

CREATE TABLE op.participation (
    project        VARCHAR(16) NOT NULL,
    _ppl_seq       INTEGER NOT NULL,
    billing_rate   DECIMAL(6,2),
    CONSTRAINT participation_pk
      PRIMARY KEY (project, _ppl_seq),
    CONSTRAINT participation_project_fk
      FOREIGN KEY (project)
      REFERENCES op.project(prj_id),
    CONSTRAINT participation_person_fk
      FOREIGN KEY (_ppl_seq)
      REFERENCES op.person(_ppl_seq)
);
GRANT SELECT ON op.participation TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON op.participation TO "htsql_regress.clerk";


INSERT INTO op.organization (org_id,name,is_active,division_of) VALUES
('meyers','Meyers Construction',TRUE,NULL),
('meyers_elec','Meyers Electric',TRUE,'meyers'),
('acorn','Acorn Architecture',TRUE,NULL),
('lakeside','Lake Side Partners, LLC',NULL,NULL),
('lake-carmen','Lake Carmen Towers',TRUE,'lakeside'),
('lake-apts','Lake Shore Apartments',FALSE,'lakeside'),
('smith','Rudgen, Taupe, & Smith',FALSE,NULL)
;

INSERT INTO op.person (_ppl_seq,org_id,nickname,email,full_name) VALUES
(10,'meyers','jack','jack.meyers@example.com','Jack C. Meyers'),
(11,'meyers','hill','mark.hill@example.com','Mark Thomas Hill'),
(12,'meyers','jim','jim.meyers@example.com','Jim Meyers'),
(21,'smith','maggy','','Margret N. Smith'),
-- (22,'smith','josé','jose.martenes@example.com','José N. Marteñes'),
(22,'smith','jose','jose.martenes@example.com','José N. Marteñes'),
(23,'smith','jack','jack.taupe@example.com','Jack Taupe'),
(31,'lakeside','dave','david.joines@example.com','David Jones'),
(32,'lakeside','amy',NULL,'Amy S. Buckworth'),
(33,'lake-apts','tom',NULL,'Tommy O''Mally'),
(40,'acorn','hideo','hideo.watanabe@example.com','WATANABE Hideo')
;

INSERT INTO op.project (prj_id,name,status,client,start_date,description) VALUES
('overhead','General Management Work','in-progress',NULL,'2003-06-12',
 'Internal overhead, not attached to a particular client'),
('smbl','Smith Balcony Expansion','completed','smith','2005-02-03',
 'An expansion of the Executive balcony at Rudgen, Taupe and Smith'),
('smen','Smith Entry and Waiting Room','completed','smith','2005-08-09',
 'Restructuring of the entry way and waiting room to handle clients who ' ||
 'have complained about the decor.'),
('smak','Smith Associate Window and Roof','abandoned','smith','2006-08-03',
 'Upgrade Jack''s office to have an alcove and a bay window.'),
('la-102','Kitchen Remodel at 102 N. Ocean View','completed',
 'lake-apts','2004-03-27',
 'Completely gutting the ancient stuff in this otherwise pretty apartment'),
('la-334','Siding / roof at 334 Ocean Blvd',
 'completed','lake-apts','2004-04-20',
 'Replace the siding and ancient roofing with vinyl stuff.'),
('lt-802','Toaster Re-Do and Roof Leak','in-progress','lake-carmen','2006-11-23',
 'Reworking Apt 802 to recover from a Toaster that caught fire.'),
('lt-711','Updating Fire Escape','planned','lake-carmen',NULL,
 'Bringing the fire-escape up-to-code. No Participants Yet.')
 ;


INSERT INTO op.participation (project, _ppl_seq) VALUES
('smbl',21),
('smen',21),
('smak',23),
('smak',21),
('la-102',33),
('la-334',33),
('la-102',31),
('la-334',31),
('lt-802',32)
;

INSERT INTO op.participation (project, _ppl_seq, billing_rate) VALUES
('smbl',10,65.50),
('smak',10,65.50),
('smen',10,65.50),
('la-102',10,69.00),
('la-334',10,69.00),
('lt-802',10,69.00),
('smak',11,65.50),
('la-102',11,55.00),
('lt-802',11,55.00) ,
('smak',12,35.50),
('smen',12,65.50),
('la-102',12,69.00),
('la-334',12,69.00),
('lt-802',12,69.00),
('smbl',40,135.00),
('smak',40,135.00),
('overhead',10,0.00)
;

-- ---------------------------------------------------------
-- HR - HUMAN RESOURCES
--
CREATE SCHEMA hr;
GRANT USAGE ON SCHEMA hr TO "htsql_regress.public";
COMMENT ON SCHEMA hr IS E'
---
title: Human Resources
notes: >
  The objective of this schema is to introduce a facet of person,
  private_info, and to excerise structured types and arrays.  The
  regression tests for this facet will use tax_ident primarly,
  leaving the other values NULL so that databases without support
  for structured types and arrays will work.

diagram: |
  +-------------------+  a person has at      +-------------------+
  | HR.PRIVATE_INFO   |  most one private /---| OP.PERSON         |
  +-------------------+  info record      |   +-------------------+
  | _ppl_seq    FK,PK |o------------------/
  | tax_ident      NN |      ................        .............
  | ethnicity   CK,[] |  ,..: HR.ADDRESS_T   :    ,.: HR.REGION_T :
  | address        {} |..   +................+   ,  +.............+
  | phone_nums   {}[] |     : lines [],      :  ,   : city        :
  +-------------------+     : region {},     :..    : state,      :
  [] - ARRAY                : postal_code NN :      : country     :
  {} - STRUCTURE             ................        .............
';

CREATE DOMAIN hr.postal_code_t VARCHAR(32) NOT NULL;
ALTER DOMAIN hr.postal_code_t
  ADD CONSTRAINT postal_code_not_empty
    CHECK (VALUE IS NOT NULL AND VALUE != '');

CREATE TYPE hr.region_t AS (
    city           VARCHAR(32),
    state          VARCHAR(32),
    country        VARCHAR(32)
);

CREATE TYPE hr.address_t AS (
    lines          VARCHAR(32)[],
    region         hr.region_t,
    postal_code    hr.postal_code_t
);

CREATE DOMAIN hr.ethnicity_t VARCHAR(16);
ALTER DOMAIN  hr.ethnicity_t
   ADD CONSTRAINT ethnicity_check
     CHECK (VALUE IN ('white','black','hispanic','asian',
                      'indian','islander'));

CREATE TABLE hr.private_info (
    _ppl_seq       INTEGER NOT NULL,
    tax_ident      VARCHAR(16) NOT NULL,
--  ethnicity      hr.ethnicity_t[],
    ethnicity      VARCHAR(16)[],
    address        hr.address_t,
--  phone_nums     hr.phone_t[],
    CONSTRAINT private_info_pk
      PRIMARY KEY (_ppl_seq),
    CONSTRAINT private_info_uk
      UNIQUE (tax_ident),
    CONSTRAINT private_info_person_fk
      FOREIGN KEY (_ppl_seq)
      REFERENCES op.person(_ppl_seq)
      ON DELETE CASCADE
);
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.private_info
  TO "htsql_regress.admin";

INSERT INTO hr.private_info (_ppl_seq,tax_ident,ethnicity,address) VALUES
(10,'222-22-1492',ARRAY['white'],
    ROW(ARRAY['1 Rockway Park Dr.','Apt #2'],
        ROW('Santa Monica','CA','USA'), '03884-0394')),
(11,'456-27-7645','{}',NULL),
(12,'983-71-8458',NULL, ROW(NULL, ROW('Roanoke','VA','USA'), '38234')),
(40,'E32-3483',ARRAY['asian'], ROW(NULL, ROW(NULL,NULL,NULL), '38234')),
(21,'826-44-2325',ARRAY['indian'], ROW('{}', NULL,'03995-3434')),
(22,'725-23-8646',ARRAY['black','hispanic'],NULL),
(23,'651-97-2682',ARRAY['black'],
    ROW(ARRAY['34 Beltway Ave.'],
        ROW('Santa Monica','CA','USA'), '03884-0394'))
;

-- ---------------------------------------------------------
-- WS - WORK SCHEDULING
--
CREATE SCHEMA ws;
GRANT USAGE ON SCHEMA ws TO "htsql_regress.public";
COMMENT ON SCHEMA ws IS E'
---
title: Work Scheduling
notes: >
  The work scheduling core demonstrates a composite pattern, mutual
  recursion of two tables.  The dependency provides a M-M relation
  of the same table with itself.

diagram: |
  /---------------------------------------------------------------\
  |  +-------------------+                 +-------------------+  |     
  |  | WS.WORKLIST       |     /-----------| OP.PERSON         |  |     
  |  +-------------------+     |           +-------------------+  ^     
  |  | prj_id    FK1,PK1 |\____|___        +-------------------+  |
  \--| item_no   FK2,PK2 |/    |   \    /--| OP.PROJECT        |  |
     | overview          |     |   |    |  +-------------------+  |
     | assigned_to    FK |>----/   |    \-----<-------<--------\  ^
     | last_updated   NN |         |   +-------------------+   |  |
     +-------------------+         |   | WS.WORKITEM       |   |  ^
  +----------------------+         |   +-------------------+   |  |
  | WS.DEPENDENCY        |    __ __\___| prj_id     FK,PK1 |>--/  |
  +----------------------+   |  /      | item_no       PK2 |      |
  | of_prj_id    FK1,PK1 |\__/  |      | title             |      |
  | of_item_no   FK2,PK2 |/     |      | part_of       FK2 |>-----/
  | on_prj_id    FK1,PK3 |\_____/      | subclass          |
  | on_item_no   FK2,PK4 |/            | status      CK,NN |
  | on_item_no   FK2,PK4 |/            | version        NN |
  +----------------------+             +-------------------+ 
';

CREATE DOMAIN ws.item_type_t AS VARCHAR(16);
ALTER DOMAIN  ws.item_type_t
   ADD CONSTRAINT item_type_check
     CHECK (VALUE IN ('meeting','issue'));

-- TODO: designate this as a depedent list somehow?
CREATE DOMAIN ws.status_t AS VARCHAR(16);
ALTER DOMAIN  ws.status_t
   ADD CONSTRAINT status_check
     CHECK (VALUE IN ('planned','in-progress','done','abandoned'));

CREATE TABLE ws.workitem (
    prj_id         VARCHAR(16) NOT NULL,
    item_no        INTEGER NOT NULL,
    title          VARCHAR(60),
    part_of        INTEGER,
    subclass       ws.item_type_t,
    status         ws.status_t NOT NULL DEFAULT 'planned',
    version        INTEGER NOT NULL DEFAULT 1,
    CONSTRAINT workitem_pk
      PRIMARY KEY (prj_id,item_no),
    CONSTRAINT workitem_project_fk
      FOREIGN KEY (prj_id)
      REFERENCES op.project(prj_id)
);
GRANT SELECT ON ws.workitem TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON ws.workitem TO "htsql_regress.clerk";

CREATE TABLE ws.worklist (
    prj_id         VARCHAR(16) NOT NULL,
    item_no        INTEGER NOT NULL,
    overview       TEXT,
    assigned_to    INTEGER,
    last_updated   TIMESTAMP NOT NULL,
    CONSTRAINT worklist_pk
      PRIMARY KEY (prj_id,item_no),
    CONSTRAINT worklist_workitem_fk
      FOREIGN KEY (prj_id,item_no)
      REFERENCES ws.workitem(prj_id,item_no),
    CONSTRAINT worklist_person_fk
      FOREIGN KEY (assigned_to)
      REFERENCES op.person(_ppl_seq)
);
GRANT SELECT ON ws.worklist TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON ws.worklist TO "htsql_regress.clerk";

ALTER TABLE ws.workitem ADD
    CONSTRAINT workitem_worklist_fk
      FOREIGN KEY (prj_id, part_of)
      REFERENCES ws.worklist(prj_id, item_no);

CREATE TABLE ws.dependency (
    of_prj_id      VARCHAR(16) NOT NULL,
    of_item_no     INTEGER NOT NULL,
    on_prj_id      VARCHAR(16) NOT NULL,
    on_item_no     INTEGER NOT NULL,
    CONSTRAINT dependency_pk
      PRIMARY KEY (of_prj_id,of_item_no,on_prj_id,on_item_no),
    CONSTRAINT dependency_of_workitem_fk
      FOREIGN KEY (of_prj_id,of_item_no)
      REFERENCES ws.workitem(prj_id,item_no),
    CONSTRAINT dependency_on_workitem_fk
      FOREIGN KEY (on_prj_id,on_item_no)
      REFERENCES ws.workitem(prj_id,item_no)
);
GRANT SELECT ON ws.dependency TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON ws.dependency TO "htsql_regress.clerk";

INSERT INTO ws.workitem (prj_id,item_no,status,subclass,part_of,title) VALUES
('smbl',1,'done',NULL,NULL,'Preliminary Investigation of Requirements'),
('smen',1,'done',NULL,NULL,'Preliminary Investigation of Entry Requirements'),
('smak',1,'done',NULL,NULL,'Preliminary Investigation of Bay Window Need'),
('smbl',2,'done','meeting',NULL,'Meeting About of Balcony Requirements'),
('smen',2,'done','meeting',NULL,'Meeting About of Entry Requirements'),
('smak',2,'done','meeting',NULL,'Meeting About of Bay Window Need'),
('smbl',3,'done',NULL,NULL,'Deliverables for Balcony Requirements'),
('smen',3,'done',NULL,NULL,'Deliverables for Entry Requirements'),
('smak',3,'abandoned',NULL,NULL,'Deliverables for Bay Window Need'),
('smbl',4,'in-progress','issue',NULL,'Complaint about Balcony'),
('smen',4,'planned','issue',NULL,'Complaint about Entry'),
('smak',4,'in-progress','issue',NULL,'Complaint about Bay Window'),
('smak',5,'abandoned','meeting',NULL,'Bay Window Planning Meeting');

INSERT INTO ws.worklist (prj_id,item_no,assigned_to,overview,last_updated) VALUES
('smbl',3,10,'Work Plan for Balcony Requirements','2005-10-23 12:29:12.29'),
('smen',3,10,'Work Plan for Entry Requirements','2006-03-12 15:33:35.34'),
('smak',3,11,'Work Plan for Bay Window Need','2006-05-02 10:51:05.59');

INSERT INTO ws.workitem (prj_id,item_no,status,subclass,part_of,title) VALUES
('smbl',30,'done','meeting',3,'Standing Planning Meeting'),
('smbl',31,'done',NULL,3,'Remove Tree by Balcony'),
('smbl',32,'in-progress','issue',3,'Complaint about Removed Tree'),
('smbl',33,'done',NULL,3,'Order Wood for Balcony'),
('smbl',34,'done',NULL,3,'Construct Balcony'),
('smbl',35,'done',NULL,3,'Build Door to Balcony'),
('smbl',36,'abandoned','issue',3,'Complaint about Door Position');

INSERT INTO ws.dependency(of_prj_id,of_item_no,on_prj_id,on_item_no) VALUES
('smbl',35,'smbl',34),
('smbl',35,'smbl',36),
('smbl',34,'smbl',33);

-- ---------------------------------------------------------
-- WSX - WORK SCHEDULING EXTENSIONS
--
CREATE SCHEMA wsx;
GRANT USAGE ON SCHEMA wsx TO "htsql_regress.public";
COMMENT ON SCHEMA wsx IS E'
---
title: Work Scheduling Extensions
notes: >
  This schema extends WX to include two subclasses of workitem,
  meeting and issue.  Further, meeting has its own facet for
  recurring meetings, and a child table for meeting topics.

diagram: |
  +----------------------+                    +-------------------+
  | WSX.ISSUE            |         /------ /--| WS.WORKITEM       |
  +----------------------+         |       |  +-------------------+
  | prj_id       FK1,PK1 |\________/       |
  | item_no      FK2,PK2 |/                |  +-------------------+
  | reported_by          |>-------------------| WS.PERSON         |
  +----------------------+                 |  +-------------------+
                                           |
  +----------------------+                 \-----------------\
  | WSX.MEETING_TOPIC    |                                   |
  +----------------------+          +--------------------+   |
  | meeting_seq  FK1,PK1 |>---\     | WSX.MEETING        |   |
  | topic_no         PK2 |    |     +--------------------+   |
  | topic_text           |    |     | prj_id     FK1,PK1 |\__/
  +----------------------+    |     | item_no    FK2,PK2 |/
                              \- /--| meeting_seq        |
  +----------------------+       |  | starts_at          |
  | WSX.STANDING_MEETING |       |  | duration           |
  +----------------------+       |  +--------------------+
  | meeting_seq   FK1,PK |o------/
  | repeat_interval      |
  +----------------------+
';

CREATE TABLE wsx.issue (
    prj_id         VARCHAR(16) NOT NULL,
    item_no        INTEGER NOT NULL,
    reported_by    INTEGER,
    CONSTRAINT issue_pk
      PRIMARY KEY (prj_id,item_no),
    CONSTRAINT issue_workitem_fk
      FOREIGN KEY (prj_id,item_no)
      REFERENCES ws.workitem(prj_id,item_no),
    CONSTRAINT issue_person_fk
      FOREIGN KEY (reported_by)
      REFERENCES op.person(_ppl_seq)
);
GRANT SELECT ON wsx.issue TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON wsx.issue TO "htsql_regress.clerk";

CREATE SEQUENCE wsx.meeting_seq START WITH 100;
GRANT USAGE, SELECT, UPDATE ON
   SEQUENCE wsx.meeting_seq TO "htsql_regress.clerk";

CREATE TABLE wsx.meeting (
    prj_id         VARCHAR(16) NOT NULL,
    item_no        INTEGER NOT NULL,
    meeting_seq    INTEGER NOT NULL DEFAULT nextval('wsx.meeting_seq'),
    starts_at      TIMESTAMP WITH TIME ZONE,
    duration       INTERVAL HOUR TO MINUTE,
    CONSTRAINT meeting_pk
      PRIMARY KEY (prj_id,item_no),
    CONSTRAINT meeting_uk
      UNIQUE (meeting_seq),
    CONSTRAINT meeting_workitem_fk
      FOREIGN KEY (prj_id,item_no)
      REFERENCES ws.workitem(prj_id,item_no)
);
GRANT SELECT ON wsx.meeting TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON wsx.meeting TO "htsql_regress.clerk";

CREATE TABLE wsx.meeting_topic (
    meeting_seq    INTEGER NOT NULL,
    topic_no       INTEGER NOT NULL,
    topic_text     TEXT,
    CONSTRAINT meeting_topic_pk
      PRIMARY KEY (meeting_seq,topic_no),
    CONSTRAINT meeting_topic_fk
      FOREIGN KEY (meeting_seq)
      REFERENCES wsx.meeting(meeting_seq)
);
GRANT SELECT ON wsx.meeting_topic TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON wsx.meeting_topic TO "htsql_regress.clerk";

CREATE TABLE wsx.standing_meeting (
    meeting_seq     INTEGER NOT NULL,
    repeat_interval INTERVAL, -- YEAR TO HOUR, non-standard
    CONSTRAINT standing_meeting_pk
      PRIMARY KEY (meeting_seq),
    CONSTRAINT standing_meeting_fk
      FOREIGN KEY (meeting_seq)
      REFERENCES wsx.meeting(meeting_seq)
);
GRANT SELECT ON wsx.standing_meeting TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON wsx.standing_meeting TO "htsql_regress.clerk";

INSERT INTO wsx.issue (prj_id, item_no, reported_by) VALUES
('smbl',4,21),
('smen',4,22),
('smak',4,22),
('smbl',32,21),
('smbl',36,23);

INSERT INTO wsx.meeting (prj_id, item_no, meeting_seq, starts_at, duration) VALUES
('smbl',2,1,'2005-12-10 15:30 -3:00','1:30'),
('smbl',30,2,'2005-12-15 13:00 -3:00','0:30'),
('smen',2,3,'2006-05-01 10:00 -3:00','1:00'),
('smak',2,4,'2007-02-28 16:00 -4:00','2:00'),
('smak',5,5,'2006-09-01 13:00 -4:00','1:00');

INSERT INTO wsx.standing_meeting (meeting_seq, repeat_interval) VALUES
(2,'7 days'),
(5,'1 month');

INSERT INTO wsx.meeting_topic (meeting_seq, topic_no, topic_text) VALUES
(1,1,'Introductions'),
(1,2,'Discuss Balcony Needs'),
(1,3,'Brainstorm Provisional Plan'),
(1,4,'Open Topic'),
(1,5,'Conclusions'),
(2,1,'Review Last Week''s Progress'),
(2,2,'Discuss New Concerns and Requirements'),
(2,3,'Wrap-Up and Minutes');

-- ---------------------------------------------------------
-- TB - TIME AND BILLING
--
CREATE SCHEMA tb;
GRANT USAGE ON SCHEMA tb TO "htsql_regress.public";
COMMENT ON SCHEMA tb IS E'
---
title: Time and Billing
notes: >
  The time and billing schema only introduces complexity of depth, via
  providing for a secondary hierarchy, and then linking them together
  via a 1-1 or symmetric foreign key.  This happens when a foreign key
  reference itself participates in a unique constraint.  In this case,
  the pair (prj_id,item_no) are not only a foreign key to workitem, but
  also form a unique key ensuring that the reverse link is singular.

diagram: |
  +-------------------+                    +-------------------+
  | OP.PARTICIPATION  |---\                | OP.ORGANIZATION   |--\
  +-------------------+   |                +-------------------+  |
                          |                                       |
  +-------------------+   |                +-------------------+  |
  | TB.TIMESLIP       |   |                | TB.INVOICE        |  |
  +-------------------+   |                +-------------------+  |
  | prj_id    FK1,PK1 |\__/            /---| inv_no        PK1 |  |
  | _ppl_seq  FK2,PK2 |/               |   | org_id      NN,FK |>-/
  | slip_no       PK3 |                |   | billing_date   NN |
  | inv_no    PK1,UK1 |\__________     |   | total_amount   NN |
  | line_no   PK2,UK2 |/          \    |   +-------------------+
  | hours             |           |    \--------------------------\
  | description       |           |        +-------------------+  |
  | item_no   UK2,FK2 ||--\       |        | TB.LINE_ITEM      |  |
  +-------------------+   |       |        +-------------------+  |
     /--------------------/       \________| inv_no        PK1 |>-/
     | via prj_id, item_no                 | line_no     NN,FK |
     |   +-------------------+             | line_text      NN |
     \---| WS.WORKITEM       |             | amount            |
         +-------------------+             +-------------------+
';

CREATE SEQUENCE tb.invoice_seq START WITH 10000;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE tb.invoice_seq TO "htsql_regress.clerk";

CREATE TABLE tb.invoice (
    inv_no         INTEGER NOT NULL DEFAULT nextval('tb.invoice_seq'),
    org_id         VARCHAR(16) NOT NULL,
    billing_date   DATE,
    total_amount   DECIMAL(12,2) NOT NULL DEFAULT 0,
    CONSTRAINT invoice_pk
      PRIMARY KEY (inv_no),
    CONSTRAINT invoice_organization_fk
      FOREIGN KEY (org_id)
      REFERENCES op.organization(org_id)
);
GRANT SELECT ON tb.invoice TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON tb.invoice TO "htsql_regress.clerk";

CREATE TABLE tb.lineitem (
    inv_no         INTEGER NOT NULL,
    line_no        INTEGER NOT NULL,
    line_text      VARCHAR(64) NOT NULL,
    amount         DECIMAL(12,2),
    CONSTRAINT lineitem_pk
      PRIMARY KEY (inv_no,line_no),
    CONSTRAINT lineitem_invoice_fk
      FOREIGN KEY (inv_no)
      REFERENCES tb.invoice(inv_no)
);
GRANT SELECT ON tb.lineitem TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON tb.lineitem TO "htsql_regress.clerk";

CREATE TABLE tb.timeslip (
    project        VARCHAR(16) NOT NULL,
    person         INTEGER NOT NULL,
    slip_no        INTEGER NOT NULL,
    inv_no         INTEGER,
    line_no        INTEGER,
    hours    DECIMAL(6,2),
    description    TEXT,
    item_no        INTEGER,
    CONSTRAINT timeslip_pk
      PRIMARY KEY (project,person,slip_no),
    CONSTRAINT timeslip_participation_fk
      FOREIGN KEY (project,person)
      REFERENCES op.participation(project,_ppl_seq),
    CONSTRAINT timeslip_lineitem_fk
      FOREIGN KEY (inv_no, line_no)
      REFERENCES tb.lineitem(inv_no, line_no),
    CONSTRAINT timeslip_lineitem_symmetric_uk
      UNIQUE (project, item_no),
    CONSTRAINT timeslip_workitem_fk
      FOREIGN KEY (project, item_no)
      REFERENCES ws.workitem(prj_id, item_no)
);
GRANT SELECT ON tb.timeslip TO "htsql_regress.public";
GRANT INSERT, UPDATE, DELETE ON tb.timeslip TO "htsql_regress.clerk";

-- CREATE UNIQUE INDEX timeslip_symmetric_lineitem_uk
--  ON tb.timeslip (prj_id, item_no) WHERE line_no IS NOT NULL;

INSERT INTO tb.invoice(inv_no,org_id,billing_date,total_amount) VALUES
(1,'smith','2004-12-15',1257), -- BALCONY, INITIATE
(2,'smith','2005-02-15',7401), -- BALCONY, FINAL
(3,'smith','2005-09-01',4843), -- ENTRY and WATING ROOM
(4,'smith','2006-09-01',1500)  -- ABANDONED WINDOW, NEGOTIATED REDUCTION
;

INSERT INTO tb.lineitem(inv_no,line_no,amount,line_text) VALUES
(1,1,550,'Architectural Design of Balcony'),
(1,2,350,'Organizational Meetings'),
(1,3,357,'Materials'),
(2,1,350,'Architectural Adjustments'),
(2,2,490,'Organizational Meetings, Oversight'),
(2,3,1256,'Purchasing and Materials'),
(2,4,435,'Tree Removal'),
(2,5,3620,'Labor'),
(2,6,1250,'Licensing and Building Permit')
;

INSERT INTO tb.timeslip (inv_no,line_no,project,person,slip_no,
    item_no,hours,description) VALUES 
(1,1,'smbl',40,1,NULL,11,'Worked on layout of balcony'),
(1,1,'smbl',10,1,NULL,3,'Review of design and materials evaluation'),
(1,2,'smbl',40,2,NUlL,2,'Walk-Through of BluePrint'),
(1,2,'smbl',10,2,NULL,7,'Organizational Meetings'),
(NULL,NULL,'overhead',10,1,NULL,3,'Non-Billable Management Overhead');


-- ---------------------------------------------------------
-- FUN - DATA TYPE COMPLETENESS AND OTHER TESTS
--
CREATE SCHEMA "F-U-N";
GRANT USAGE ON SCHEMA "F-U-N" TO PUBLIC;

CREATE TYPE "F-U-N".file_t AS (
    "name"      VARCHAR(256),
    "size"      INTEGER,         -- Size of the file, uncompressed
    "location"  VARCHAR(2083),   -- IE Explorer URL Length
    "search"    TEXT,            -- full-text search field
    "mimetype"  VARCHAR(60),
    "timestamp" TIMESTAMP WITH TIME ZONE
);

CREATE TABLE "F-U-N".types (
    "pgsql_serial"    SERIAL,                 -- autoincrementing integer
    "sql92_char"      CHARACTER(10),          -- fixed-length, padded text length
    "sql92_varchar"   CHARACTER VARYING(10),  -- unpadded character
    "sql92_bit"       BIT(10),                -- fixed-length octets
    "sql92_varbit"    BIT VARYING(10),        -- variable length octets
    "sql92_numeric"   NUMERIC,                -- see SQL92 4.4.1
    "sql92_num_3"     NUMERIC(3),             -- an integer, 3 places
    "sql92_num_3_2"   NUMERIC(3,2),           -- N.NN, 3 plates, 2 fractional
    "sql92_decimal"   DECIMAL,                -- implementation specific scale
    "sql92_dec_3"     DECIMAL(3),             -- greater than or equal to what
    "sql92_dec_3_2"   DECIMAL(3,2),           -- is specified
    "sql92_integer"   INTEGER,
    "sql92_smallint"  SMALLINT,
    "sql92_float"     FLOAT,
    "sql92_float_5"   FLOAT,                 -- float with precision of 5
    "sql92_real"      REAL,
    "sql92_double"    DOUBLE PRECISION,
    "sql92_date"      DATE,
    "sql92_time"      TIME,                  -- a precision of 0 is implicit
    "sql92_time_2"    TIME(2),               -- time with fractional seconds
    "sql92_time_tz"   TIME WITH TIME ZONE,   -- nonsensical... tz requires date
    "sql92_timestamp" TIMESTAMP,             -- a precision of 6 is implicit
    "sql92_timest_2"  TIMESTAMP(2),
    "sql92_timest_tz" TIMESTAMP WITH TIME ZONE,
    "sql92_interval"  INTERVAL,
    -- PostgreSQL Types
    "pgsql_text"      TEXT,                  -- unlimited length memo field
    -- SQL99
    "sql03_bool"      BOOLEAN,
    "sql03_array_vc"  VARCHAR(10) ARRAY[7],
    -- HTSQL, SQL99 Structured Types
    "file" "F-U-N".file_t,
    CONSTRAINT "regress_types_pk"
      PRIMARY KEY ("pgsql_serial")
);
GRANT SELECT ON "F-U-N"."types" TO PUBLIC;
SET TIMEZONE TO -8;
INSERT INTO "F-U-N"."types" VALUES (DEFAULT,
    '8IN10',
    E'CHAR''"\\VAL',
    34::BIT(10),
    34::BIT(10),
    12345,
    123,
    1.23,
    12345,
    123,
    1.23,
    123,
    123,
    1.234e83,
    1.2e-9,
    1.234e3,
    1.234e83,
    '2007-09-01',
    '13:35',
    '13:35:20.11',
    '13:35+4:00',
    '2007-09-01 13:35:20',
    '2007-09-01 13:35:20.11',
    '2007-09-01 13:35:20.11 +4:00',
    '1 year 2 months 3 days 4 hours 5 minutes 6 seconds',
    NULL,
    TRUE,
    ARRAY[NULL,'aa','bb','cc','dd','ee','ff'],
    NULL
);

CREATE TABLE "F-U-N"."Is it weird?" (
    id  INTEGER NOT NULL,
    weird_column    VARCHAR(2),
    "Weird Column"  VARCHAR(2),
    "WEIRD-COLUMN"  VARCHAR(2),
    "WEIRD.COLUMN"  VARCHAR(2),
    "Rea11y #31rd $olum"""""   VARCHAR(2),
    CONSTRAINT "Is it weird?.id"
      PRIMARY KEY (id)
);
GRANT SELECT ON "F-U-N"."Is it weird?" TO PUBLIC;
INSERT INTO "F-U-N"."Is it weird?" VALUES (1,'A','B','C','D','E');
INSERT INTO "F-U-N"."Is it weird?" VALUES (2,'X','Y','Z','T','V');


CREATE TABLE "F-U-N".weird_identifiers (
    id1 INTEGER NOT NULL,
    id2 INTEGER NOT NULL,
    id3 FLOAT NOT NULL,
    id4 VARCHAR(15) NOT NULL,
    CONSTRAINT weird_identifiers_pk
        PRIMARY KEY (id1,id2,id3,id4)
);
GRANT SELECT ON "F-U-N".weird_identifiers TO PUBLIC;
INSERT INTO "F-U-N".weird_identifiers VALUES (1, 2, 1.2, '''[3]''." "');
INSERT INTO "F-U-N".weird_identifiers VALUES (99999,-34, 2.7172818, 'QuoteMixCase');
INSERT INTO "F-U-N".weird_identifiers VALUES (1,1, 2.7172818e+28, 'do_not_quote');

-- A TABLE NAME CONFLICT
CREATE TABLE "F-U-N".issue (
    not_a_workitem TEXT
);
GRANT SELECT ON "F-U-N".issue TO PUBLIC;

-- ---------------------------------------------------------
-- UNICODE - UNICODE DATA TESTS
--
CREATE SCHEMA "unicode_С";
GRANT USAGE ON SCHEMA "unicode_С" TO PUBLIC;
CREATE TABLE "unicode_С"."test_Т" (
    "test_К"  VARCHAR(32) NOT NULL,
    CONSTRAINT "test_Т_pk"
        PRIMARY KEY ("test_К")
);
GRANT SELECT ON "unicode_С"."test_Т" TO PUBLIC;

INSERT INTO "unicode_С"."test_Т" VALUES ('test_К');

CREATE TABLE "unicode_С"."Таблица" (
    "Ключ"  VARCHAR(32) NOT NULL,
    "Колонка" VARCHAR(32) NOT NULL,
    CONSTRAINT "Таблица.Ключ"
        PRIMARY KEY ("Ключ")
);
GRANT SELECT ON "unicode_С"."Таблица" TO PUBLIC;

INSERT INTO "unicode_С"."Таблица" VALUES 
('Ключ1','Значение1');
INSERT INTO "unicode_С"."Таблица" VALUES 
('Ключ2','Значение2');
INSERT INTO "unicode_С"."Таблица" VALUES 
('Ключ3','Значение3');