<code sql>

DROP SCHEMA serials CASCADE;

BEGIN;

-- New stuff, new schema
CREATE SCHEMA serials;


-- Table to maintain entry points into the actual pattern definitions
CREATE TABLE serials.issue_label_pattern (
    id      SERIAL  PRIMARY KEY,
    name        TEXT    UNIQUE NOT NULL
);

/** This is how the actual patterns are defined.  The pattern templates
  * themselves are standard TO_CHAR() templates.  They support padded and unpadded
  * signed decimal numbers, roman numerals, ordianl suffixes, locale-specific
  * currency symbol and thousands grouping, and more.  For more info, see:
  * http://www.postgresql.org/docs/current/static/functions-formatting.html#FUNCTIONS-FORMATTING-NUMERIC-TABLE
 **/
CREATE TABLE serials.issue_label_pattern_part (
    id          SERIAL  PRIMARY KEY,
    issue_label_pattern INT NOT NULL,
    level           INT NOT NULL DEFAULT 1 CHECK (level > 0),       -- should add a trigger to verify this more strictly
    cycle           INT,                            -- NULL means infinite
    pattern_template    TEXT    NOT NULL,                   -- to_char() template
    CONSTRAINT level_once_per_pattern UNIQUE (level, issue_label_pattern)
);


-- This tracks the overall subscription that brings items into the library.  There
-- will be one subscription object per order of a thing represented by a bib record
CREATE TABLE serials.subscription (
    id          SERIAL              PRIMARY KEY,
    create_date     TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
    edit_date       TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
    creator         INT             NOT NULL,
    editor          INT             NOT NULL,
    owner           INT             NOT NULL,
    bib_record      INT             NOT NULL,
    name            TEXT                NOT NULL,
    issue_label_pattern INT             NOT NULL,
    first_issue_number  INT             NOT NULL DEFAULT 1,
    claim_interval      INTERVAL            NOT NULL DEFAULT '1 week',
    timespan        INT             NOT NULL
);

-- Um ... notes. For subscriptions...
CREATE TABLE serials.subscription_note (
    id      SERIAL              PRIMARY KEY,
    subscription    INT             NOT NULL,
    creator     INT             NOT NULL,
    create_date TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
    value       TEXT                NOT NULL
);


-- Issue records created inside a subscription.
CREATE TABLE serials.issue (
    id      SERIAL  PRIMARY KEY,
    subscription    INT NOT NULL,
    issue_number    INT NOT NULL,
    timespan    INT NOT NULL,
    label       TEXT    NOT NULL  -- CN label, generated using the issue_label_{pattern|path} tables
);


CREATE TABLE serials.issue_note (
    id      SERIAL              PRIMARY KEY,
    issue       INT,
    creator     INT,
    create_date TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
    value       TEXT                NOT NULL
);


CREATE TABLE serials.claim (
    id      SERIAL  PRIMARY KEY,
    timespan    INT,
    claimer     INT,
    claim_time  TIMESTAMP WITH TIME ZONE,
    initial_note    TEXT
);

CREATE TABLE serials.claim_note (
    id      SERIAL              PRIMARY KEY,
    claim       INT,

    creator     INT,
    create_date TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
    value       TEXT                NOT NULL
);


CREATE TABLE serials.receive (
    id      SERIAL              PRIMARY KEY,
    issue       INT             NOT NULL,
    claim       INT             NOT NULL,
    timespan    INT             NOT NULL,
    receiver    INT             NOT NULL,
    receive_time    TIMESTAMP WITH TIME ZONE,
    initial_note    TEXT
);

CREATE TABLE serials.receive_note (
    id      SERIAL              PRIMARY KEY,
    receive     INT,
    creator     INT,
    create_date TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
    value       TEXT                NOT NULL
);


CREATE TABLE serials.timespan_granularity (
    code        TEXT    PRIMARY KEY,
    label       TEXT    UNIQUE NOT NULL,
    template    TEXT    NOT NULL
);

INSERT INTO serials.timespan_granularity (code, label, template)
    VALUES  ('year', 'Year',     'YYYY0000000000'),
        ('month', 'Month',   'YYYYMM00000000'),
        ('day', 'Day',       'YYYYMMDD000000'),
        ('hour', 'Hour',     'YYYYMMDDHH240000'),
        ('minute', 'Minute', 'YYYYMMDDHH24MI00'),
        ('second', 'Second', 'YYYYMMDDHH24MISS');


CREATE TABLE serials.timespan (
    id      SERIAL      PRIMARY KEY,
    granularity TEXT        NOT NULL DEFAULT 'second',
    dtstart     TIMESTAMP   NOT NULL,
    dtend       TIMESTAMP   NOT NULL
);


CREATE TABLE serials.subscription_timespan () INHERITS (serials.timespan);

CREATE TABLE serials.issue_timespan () INHERITS (serials.timespan);

CREATE TABLE serials.receive_timespan () INHERITS (serials.timespan);

CREATE TABLE serials.claim_timespan () INHERITS (serials.timespan);

-- Rows here are (re)generated based on subscription and issue information, and possibly by MARC data.
CREATE TABLE serials.coverage_timespan (
    owner       INT NOT NULL,
    bib_record  INT NOT NULL
) INHERITS (serials.timespan);

CREATE TABLE serials.timespan_note (
    id      SERIAL              PRIMARY KEY,
    timespan    INT             NOT NULL,
    creator     INT             NOT NULL,
    create_date TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
    value       TEXT                NOT NULL
);


CREATE OR REPLACE FUNCTION serials.timeline_to_box( TIMESTAMP, TIMESTAMP, TEXT ) RETURNS box AS $$
    SELECT  box(
            point( CAST( TO_CHAR($1, template) AS DOUBLE PRECISION ),0 ),
            point( CAST( TO_CHAR($2, template) AS DOUBLE PRECISION ),0 )
        )
      FROM  serials.timespan_granularity WHERE code = $3;
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION serials.timeline_to_box( TIMESTAMP, TIMESTAMP ) RETURNS box AS $$
    SELECT serials.timeline_to_box( $1, $2, 'second' );
$$ LANGUAGE SQL IMMUTABLE;


CREATE OR REPLACE FUNCTION serials.timeline_to_box( TIMESTAMP, TEXT ) RETURNS box AS $$
    SELECT serials.timeline_to_box( $1, $1, $2 );
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION serials.timeline_to_box( TIMESTAMP ) RETURNS box AS $$
    SELECT serials.timeline_to_box( $1, 'second' );
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION serials.timeline_to_box( DATE, DATE, TEXT ) RETURNS box AS $$
    SELECT serials.timeline_to_box( $1::TIMESTAMP, $2::TIMESTAMP, $3 );
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION serials.timeline_to_box( DATE, DATE ) RETURNS box AS $$
    SELECT serials.timeline_to_box( $1::TIMESTAMP, $2::TIMESTAMP, 'day' );
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION serials.timeline_to_box( DATE, TEXT ) RETURNS box AS $$
    SELECT serials.timeline_to_box( $1, $1, $2 );
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION serials.timeline_to_box( DATE ) RETURNS box AS $$
    SELECT serials.timeline_to_box( $1, 'day' );
$$ LANGUAGE SQL IMMUTABLE;


CREATE INDEX sub_segment_span_intersection ON serials.subscription_timespan USING GIST ( serials.timeline_to_box(dtstart, dtend, granularity) );
CREATE INDEX issue_segment_span_intersection ON serials.issue_timespan      USING GIST ( serials.timeline_to_box(dtstart, dtend, granularity) );
CREATE INDEX receive_segment_span_intersection ON serials.receive_timespan  USING GIST ( serials.timeline_to_box(dtstart, dtend, granularity) );
CREATE INDEX claim_segment_span_intersection ON serials.claim_timespan      USING GIST ( serials.timeline_to_box(dtstart, dtend, granularity) );
CREATE INDEX coverage_segment_span_intersection ON serials.coverage_timespan    USING GIST ( serials.timeline_to_box(dtstart, dtend, granularity) );


ALTER TABLE serials.issue_label_pattern_part
    ADD CONSTRAINT  parent_pattern
      FOREIGN KEY   (issue_label_pattern)
      REFERENCES    serials.issue_label_pattern (id)
            DEFERRABLE
            INITIALLY DEFERRED;

ALTER TABLE serials.subscription
    ADD CONSTRAINT  sub_creator_usr_fkey
      FOREIGN KEY   (creator)

      REFERENCES actor.usr (id);

ALTER TABLE serials.subscription
    ADD CONSTRAINT  sub_editor_usr_fkey
      FOREIGN KEY   (editor)
      REFERENCES    actor.usr (id);

ALTER TABLE serials.subscription
    ADD CONSTRAINT  sub_owner_org_unit_fkey
      FOREIGN KEY   (owner)
      REFERENCES    actor.org_unit (id);

ALTER TABLE serials.subscription
    ADD CONSTRAINT  sub_bib_record_fkey
      FOREIGN KEY   (bib_record)
      REFERENCES    biblio.record_entry (id);

ALTER TABLE serials.subscription
    ADD CONSTRAINT  sub_issue_label_pattern_fkey
      FOREIGN KEY   (issue_label_pattern)
      REFERENCES    serials.issue_label_pattern (id)
            DEFERRABLE
            INITIALLY DEFERRED;

ALTER TABLE serials.subscription_timespan
    ADD CONSTRAINT  serials_subscription_timespan_pkey
      PRIMARY KEY   (id);

ALTER TABLE serials.subscription
    ADD CONSTRAINT  sub_timespan_fkey
      FOREIGN KEY   (timespan)
      REFERENCES    serials.subscription_timespan (id)
            DEFERRABLE
            INITIALLY DEFERRED;

ALTER TABLE serials.subscription_note
    ADD CONSTRAINT  sub_note_subscription_fkey
      FOREIGN KEY   (subscription)
      REFERENCES    serials.subscription (id);

ALTER TABLE serials.subscription_note
    ADD CONSTRAINT  sub_note_creator_fkey
      FOREIGN KEY   (creator)
      REFERENCES    actor.usr (id);

ALTER TABLE serials.issue
    ADD CONSTRAINT  issue_subscription_fkey

      FOREIGN KEY   (subscription)
      REFERENCES    serials.subscription (id);

ALTER TABLE serials.issue_timespan
    ADD CONSTRAINT  serials_issue_timespan_pkey
    PRIMARY KEY (id);

ALTER TABLE serials.issue
    ADD CONSTRAINT  issue_timespan_fkey
      FOREIGN KEY   (timespan)
      REFERENCES    serials.issue_timespan (id)
            DEFERRABLE
            INITIALLY DEFERRED;

ALTER TABLE serials.issue_note
    ADD CONSTRAINT  issue_note_issue_fkey
      FOREIGN KEY   (issue)
      REFERENCES    serials.issue (id);

ALTER TABLE serials.issue_note
    ADD CONSTRAINT  issue_note_creator_fkey
      FOREIGN KEY   (creator)
      REFERENCES    actor.usr (id);

ALTER TABLE serials.claim
    ADD CONSTRAINT  claim_claimer_fkey
      FOREIGN KEY   (claimer)
      REFERENCES    actor.usr (id);

ALTER TABLE serials.claim_timespan
    ADD CONSTRAINT  serials_claim_timespan_pkey
    PRIMARY KEY (id);

ALTER TABLE serials.claim
    ADD CONSTRAINT  claim_timespan_fkey
      FOREIGN KEY   (timespan)
      REFERENCES    serials.claim_timespan (id)
            DEFERRABLE
            INITIALLY DEFERRED;

ALTER TABLE serials.claim_note
    ADD CONSTRAINT  claim_note_claim_fkey
      FOREIGN KEY   (claim)
      REFERENCES    serials.claim (id);

ALTER TABLE serials.claim_note
    ADD CONSTRAINT  claim_note_creator_fkey

      FOREIGN KEY   (creator)
      REFERENCES    actor.usr (id);

ALTER TABLE serials.receive
    ADD CONSTRAINT  receive_receiver_fkey
      FOREIGN KEY   (receiver)
      REFERENCES    actor.usr (id);

ALTER TABLE serials.receive
    ADD CONSTRAINT  receive_issue_fkey
      FOREIGN KEY   (issue)
      REFERENCES    serials.issue (id);

ALTER TABLE serials.receive
    ADD CONSTRAINT  receive_claim_fkey
      FOREIGN KEY   (claim)
      REFERENCES    serials.claim (id);

ALTER TABLE serials.receive_timespan
    ADD CONSTRAINT  serials_receive_timespan_pkey
    PRIMARY KEY (id);

ALTER TABLE serials.receive
    ADD CONSTRAINT  receive_timespan_fkey
      FOREIGN KEY   (timespan)
      REFERENCES    serials.receive_timespan (id)
            DEFERRABLE
            INITIALLY DEFERRED;

ALTER TABLE serials.receive_note
    ADD CONSTRAINT  receive_note_receive_fkey
      FOREIGN KEY   (receive)
      REFERENCES    serials.receive (id);

ALTER TABLE serials.receive_note
    ADD CONSTRAINT  receive_note_creator_fkey
      FOREIGN KEY   (creator)
      REFERENCES    actor.usr (id);

ALTER TABLE serials.timespan
    ADD CONSTRAINT  timespan_granularity_fkey
      FOREIGN KEY   (granularity)
      REFERENCES    serials.timespan_granularity (code);

/*
ALTER TABLE serials.timespan_note
    ADD CONSTRAINT  timespan_note_timespan_fkey

      FOREIGN KEY   (timespan)
      REFERENCES    serials.timespan (id);
*/

ALTER TABLE serials.timespan_note
    ADD CONSTRAINT  timespan_note_creator_fkey
      FOREIGN KEY   (creator)
      REFERENCES    actor.usr (id);

ALTER TABLE serials.coverage_timespan
    ADD CONSTRAINT  serials_coverage_timespan_pkey
    PRIMARY KEY (id);

ALTER TABLE serials.coverage_timespan
    ADD CONSTRAINT  coverage_timespan_owner_org_unit_fkey
      FOREIGN KEY   (owner)
      REFERENCES    actor.org_unit (id);

ALTER TABLE serials.coverage_timespan
    ADD CONSTRAINT  coverage_timespan_bib_record_fkey
      FOREIGN KEY   (bib_record)
      REFERENCES    biblio.record_entry (id);


/*

Now we can do things like...

 SELECT DISTINCT bib_record
  FROM  serials.coverage_timespan
  WHERE serials.timeline_to_box(dtstart, dtend, granularity) ~ serials.timeline_to_box('2001-03-01'::DATE);

to get all bib that have coverage on March 1, 2001.  This is handy for extending OPAC searches.  For an ACQ/overview of
events ocurring during a particular month we could use

 SELECT t.*, c.relname
  FROM  serials.coverage_timespan t
    JOIN pg_class c ON (t.tableoid = c.oid)
  WHERE serials.timeline_to_box(dtstart, dtend, granularity) && serials.timeline_to_box('2001-03-01'::TIMESTAMP,'month');

To get all events entirely contained within the first half of 2001 we could use

 SELECT t.*, c.relname
  FROM  serials.coverage_timespan t
    JOIN pg_class c ON (t.tableoid = c.oid)

  WHERE serials.timeline_to_box(dtstart, dtend, granularity) @ serials.timeline_to_box('2001-01-01'::DATE, '2001-06-01'::DATE, 'month');

Operators of obvious use are && (overlaps), ~ (left contains right), and @ (left contained in or on right).  Of possible use
are << (is strictly left of, or before), >> (is strictly right of, or after), and &< and &> (is right-bound/left-bound,
left doesn't extend after/before right).

The current schema does not make use of the Y-axis for calculating timespan boxes, but if we were to, say, separate each
timespan type to it's own Y-axis "level" (or "levels") then we could use pure bounding-box logic to find most related events.
The one obvious place where this breaks down is for claim events, which are tied to an issue event, but may live entirely
outside said event.  This may also be the case for receive events, and for issue events in relation to subscriptions.


To find all owned May '07 issues of a particular bib, one could do something like

 SELECT i.*
  FROM  serials.issue i
    JOIN serials.issue_timespan t ON (i.timespan = t.id)
    JOIN serials.subscription s ON (i.subscription = s.id)
  WHERE s.bib_record = 12345
    AND serials.timeline_to_box(t.dtstart, t.dtend, t.granularity) && serials.timeline_to_box('2007-05-01'::TIMESTAMP);

*/

</code>