--
--  OSSP as -- Accounting System
--  Copyright (c) 2002-2003 Cable & Wireless Deutschland <http://www.cw.com/de/>
--  Copyright (c) 2002-2003 Ralf S. Engelschall <rse@engelschall.com>
--
--  This file is part of OSSP as, an accounting system which can be
--  found at http://www.ossp.org/pkg/tool/as/
--
--  This program is free software; you can redistribute it and/or
--  modify it under the terms of the GNU General Public License
--  as published by the Free Software Foundation; either version
--  2.0 of the License, or (at your option) any later version.
--
--  This program is distributed in the hope that it will be useful,
--  but WITHOUT ANY WARRANTY; without even the implied warranty of
--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
--  General Public License for more details.
--
--  You should have received a copy of the GNU General Public License
--  along with this program; if not, write to the Free Software
--  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
--  USA, or contact The OSSP Project <ossp@ossp.org>.
--
--  bres-history.sql -- History Tracking Basic Research
--

--  TODO:
--  - delete entries from as_hs_ut for expiring history: does it still not correctly
--    and especially should only allow the oldest entry to be deleted or implicitly
--    delete also all older entries.

--  ##  _____________________________________________________________________
--  ##
--  ##  Database Schema
--  ##  _____________________________________________________________________
--  ##

--  cascadingly drop existing schema
--  (just ignore errors on console)
DROP SEQUENCE as_hs_st_seq CASCADE;
DROP TABLE as_hs_st CASCADE;
DROP TABLE as_hs_ut CASCADE;
DROP TABLE as_hs_ut_no CASCADE;
DROP TABLE as_account CASCADE;

--  System Time Sequence
CREATE SEQUENCE as_hs_st_seq
    INCREMENT 1 MINVALUE 1 START 1;

--  System Time Table
CREATE TABLE as_hs_st (
    st_oid         BIGINT
                   NOT NULL,
                   -- REFERENCES as_oid (id_oid) FIXME
                   -- MATCH FULL DEFERRABLE,     FIXME
                   -- object identifier
                   -- [1] (rw)
    st_hid         BIGINT
                   UNIQUE NOT NULL
                   DEFAULT nextval('as_hs_st_seq'),
                   -- history id associated with system time (discrete monoton increasing)
                   -- [742] (rw)
    st_beg         TIMESTAMP (4) WITH TIME ZONE
                   NOT NULL
                   DEFAULT 'now'
                   -- system time of point in history (not monoton increasing, could backstep)
                   -- [2002-01-01 00:00:00.0000] (rw)
);

--  User Time Table
CREATE TABLE as_hs_ut (
    ut_oid         BIGINT
                   NOT NULL,
                   -- REFERENCES as_oid (id_oid) FIXME
                   -- MATCH FULL DEFERRABLE,     FIXME
                   -- object identifier
                   -- [1] (rw)
    ut_beg         TIMESTAMP (4) WITH TIME ZONE
                   NOT NULL
                   DEFAULT 'now',
                   -- user time begin (inclusive)
                   -- [2002-01-01 00:00:00.0000] (rw)
    ut_end         TIMESTAMP (4) WITH TIME ZONE
                   NOT NULL
                   DEFAULT 'infinity',
                   -- user time end (exclusive)
                   -- [2002-01-01 00:00:00.0000] (rw)
    ut_hid         BIGINT
                   NOT NULL
                   REFERENCES as_hs_st (st_hid)
                   MATCH FULL DEFERRABLE
                   -- user time history identifier
                   -- [42] (rw)
);

--  User Time Table (non-overlapping)
CREATE TABLE as_hs_ut_no (
    ut_oid         BIGINT
                   NOT NULL,
                   -- REFERENCES as_oid (id_oid) FIXME
                   -- MATCH FULL DEFERRABLE,     FIXME
                   -- object identifier
                   -- [1] (rw)
    ut_beg         TIMESTAMP (4) WITH TIME ZONE
                   NOT NULL
                   DEFAULT 'now',
                   -- user time begin (inclusive)
                   -- [2002-01-01 00:00:00.0000] (rw)
    ut_end         TIMESTAMP (4) WITH TIME ZONE
                   NOT NULL
                   DEFAULT 'infinity',
                   -- user time end (exclusive)
                   -- [2002-01-01 00:00:00.0000] (rw)
    ut_hid         BIGINT
                   NOT NULL
                   REFERENCES as_hs_st (st_hid)
                   MATCH FULL DEFERRABLE,
                   -- user time history identifier
                   -- [42] (rw)
    ut_hid_max     BIGINT
                   NOT NULL
                   -- REFERENCES as_hs_st (st_hid) FIXME
                   -- MATCH FULL DEFERRABLE        FIXME
                   -- user time history identifier (maximum value until which this entry is valid)
                   -- [42] (rw)
);

--  Account Table
CREATE TABLE as_account(
    ac_oid         BIGINT
                   NOT NULL,
                   -- REFERENCES as_oid (id_oid)
                   -- MATCH FULL DEFERRABLE,
                   -- object identifier
                   -- [1] (rw)
    ac_name        TEXT
                   NOT NULL,
                   -- name of account
                   -- [foo] (rw)
    ac_hid         BIGINT
                   NOT NULL
                   REFERENCES as_hs_st (st_hid)
                   MATCH FULL DEFERRABLE
                   -- account history identifier
                   -- [42] (rw)
);

--  ##  _____________________________________________________________________
--  ##
--  ##  Automated Update of as_hs_ut_no Table
--  ##  _____________________________________________________________________
--  ##

--  as_hs_ut update function
DROP FUNCTION as_hs_ut_update(BIGINT, BIGINT) CASCADE;
CREATE FUNCTION as_hs_ut_update(BIGINT, BIGINT) 
RETURNS VOID LANGUAGE 'plpgsql' STABLE AS '
DECLARE
    oid BIGINT;
    hid BIGINT;
    pos_prev TIMESTAMP (4) WITH TIME ZONE;
    pos_this TIMESTAMP (4) WITH TIME ZONE;
    pos_next TIMESTAMP (4) WITH TIME ZONE;
    alt_this BIGINT;
    alt_prev BIGINT;
BEGIN
    oid := $1;
    hid := $2;

    --  delete all old entries corresponding to OID
    DELETE FROM as_hs_ut_no WHERE ut_oid = oid AND ut_hid_max = hid;

    --  initialize iteration variables
    pos_prev := ''-infinity'';
    pos_this := ''-infinity'';
    pos_next := NULL;
    alt_this := NULL;
    alt_prev := NULL;

    LOOP
        --  determine next position from existing begin/end positions
        pos_next := (
            SELECT min(v) FROM (
                    (SELECT min(ut_beg) FROM as_hs_ut WHERE ut_beg > pos_this AND ut_oid = oid AND ut_hid <= hid)
              UNION (SELECT min(ut_end) FROM as_hs_ut WHERE ut_end > pos_this AND ut_oid = oid AND ut_hid <= hid)
            ) AS result (v)
        );
        IF pos_next IS NULL THEN
            pos_next := ''infinity'';
        END IF;

        --  determine current altidude
        alt_this := (
            SELECT max(ut_hid) FROM as_hs_ut
            WHERE ut_beg <= pos_this AND pos_this < ut_end AND ut_oid = oid AND ut_hid <= hid
        );
        IF alt_this IS NULL THEN
            alt_this := -1;
        END IF;

        -- RAISE NOTICE ''pos_this = %, pos_next = %, alt_this = %'', pos_this, pos_next, alt_this;

        IF alt_this = -1 THEN
            -- RAISE NOTICE ''hit ground - pruning entry'';
        ELSE
            IF alt_this = alt_prev THEN
                -- RAISE NOTICE ''keeping altitude - consolidating entry'';
                UPDATE as_hs_ut_no SET ut_end = pos_next
                    WHERE ut_hid = alt_this AND ut_beg = pos_prev AND ut_end = pos_this AND ut_oid = oid AND ut_hid_max = hid;
            ELSE
                -- RAISE NOTICE ''changing altitude - new entry'';
                INSERT INTO as_hs_ut_no (ut_hid, ut_oid, ut_beg, ut_end, ut_hid_max)
                    VALUES (alt_this, oid, pos_this, pos_next, hid);
                pos_prev := pos_this;
            END IF;
        END IF;

        --  stop if maximum is reached
        IF pos_next >= ''infinity'' THEN
           EXIT;
        END IF;

        --  move one step
        pos_this := pos_next;
        alt_prev := alt_this;
    END LOOP;
    RETURN;
END;
';

--  as_hs_ut trigger stub function
DROP FUNCTION ut_trigger() CASCADE;
CREATE FUNCTION ut_trigger() 
RETURNS TRIGGER LANGUAGE 'plpgsql' STABLE AS '
BEGIN
    IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
        PERFORM as_hs_ut_update(NEW.ut_oid, NEW.ut_hid);
    ELSIF TG_OP = ''DELETE'' THEN
        PERFORM as_hs_ut_update(OLD.ut_oid, OLD.ut_hid);
    END IF;
    RETURN NULL;
END;
';

--  as_hs_ut trigger
--DROP TRIGGER ut_trigger ON as_hs_ut CASCADE;
CREATE TRIGGER ut_trigger
    AFTER INSERT OR UPDATE OR DELETE
    ON as_hs_ut FOR EACH ROW
    EXECUTE PROCEDURE ut_trigger ();

--  ##  _____________________________________________________________________
--  ##
--  ##  Convinience History Tracking Constructor/Destructor Functions
--  ##  hid := as_hs_make([oid[, ut_beg, ut_end[, st_beg]]]);
--  ##  as_hs_delete(oid, st_hid);
--  ##  _____________________________________________________________________
--  ##

--  hid := as_hs_make(oid, ut_beg, ut_end, st_beg)
CREATE OR REPLACE FUNCTION as_hs_make
(BIGINT, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE) 
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
DECLARE
    var_oid    BIGINT;
    var_ut_beg TIMESTAMP (4) WITH TIME ZONE;
    var_ut_end TIMESTAMP (4) WITH TIME ZONE;
    var_st_beg TIMESTAMP (4) WITH TIME ZONE;
    var_hid    BIGINT;
BEGIN
    var_oid    := $1;
    var_ut_beg := $2; IF var_ut_beg IS NULL THEN var_ut_beg := now();        END IF;
    var_ut_end := $3; IF var_ut_end IS NULL THEN var_ut_end := ''infinity''; END IF;
    var_st_beg := $4; IF var_st_beg IS NULL THEN var_st_beg := now();        END IF;
    var_hid    := NULL;
    IF var_oid IS NOT NULL THEN
        --  create new system time entry
        INSERT INTO as_hs_st (st_oid, st_beg)
            VALUES (var_oid, var_st_beg);
        var_hid := currval(''as_hs_st_seq'');
        --  create new user time entry
        INSERT INTO as_hs_ut (ut_oid, ut_beg, ut_end, ut_hid)
            VALUES (var_oid, var_ut_beg, var_ut_end, var_hid);
    ELSE
        --  return just the current/maximum history id
        var_hid := currval(''as_hs_st_seq'');
    END IF;
    RETURN var_hid;
END;
';

--  convenience function overloading
CREATE OR REPLACE FUNCTION as_hs_make
(BIGINT, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE) 
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
BEGIN
    RETURN as_hs_make($1, $2, $3, NULL);
END;
';
CREATE OR REPLACE FUNCTION as_hs_make
(BIGINT, TIMESTAMP (4) WITH TIME ZONE)
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
BEGIN
    RETURN as_hs_make($1, $2, NULL, NULL);
END;
';
CREATE OR REPLACE FUNCTION as_hs_make(BIGINT) 
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
BEGIN
    RETURN as_hs_make($1, NULL, NULL, NULL);
END;
';
CREATE OR REPLACE FUNCTION as_hs_make() 
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
BEGIN
    RETURN as_hs_make(NULL, NULL, NULL, NULL);
END;
';

--  as_hs_delete(oid, st_hid);
CREATE OR REPLACE FUNCTION as_hs_delete (BIGINT, BIGINT)
RETURNS VOID LANGUAGE 'plpgsql' STABLE AS '
DECLARE
    var_oid BIGINT;
    var_hid BIGINT;
BEGIN
    var_oid := $1;
    var_hid := $2;
    DELETE FROM as_hs_ut WHERE ut_oid = var_oid AND ut_hid = var_hid;
    DELETE FROM as_hs_st WHERE st_oid = var_oid AND st_hid = var_hid;
    RETURN;
END;
';

--  ##  _____________________________________________________________________
--  ##
--  ##  Parametrized History Table View
--  ##  <oid, hid-max>* := as_hs(ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]])
--  ##  _____________________________________________________________________
--  ##

--  This finds (optionally only maximum) oid/hid combinations between
--  st_beg and st_end (both inclusive) system time, for which the
--  user time touches the time range between ut_beg and ut_end (both
--  inclusive).

--  parametrized view return type
DROP TYPE as_hs_t CASCADE;
CREATE TYPE as_hs_t AS (
    hs_oid BIGINT,
    hs_hid BIGINT
);
CREATE OR REPLACE FUNCTION as_hs
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
 TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, INTEGER)
RETURNS SETOF as_hs_t LANGUAGE 'plpgsql' STABLE AS '
DECLARE
    arg_ut_beg TIMESTAMP (4) WITH TIME ZONE; -- user   time begin (including)
    arg_ut_end TIMESTAMP (4) WITH TIME ZONE; -- user   time end   (including)
    arg_st_beg TIMESTAMP (4) WITH TIME ZONE; -- system time begin (including)
    arg_st_end TIMESTAMP (4) WITH TIME ZONE; -- system time end   (including)
    arg_st_max INTEGER;   -- whether to take only maximum system time into account
    rec RECORD;
BEGIN
    --  pre-process input arguments by providing reasonable defaults
    arg_ut_beg := $1; IF arg_ut_beg IS NULL THEN arg_ut_beg := ''-infinity''; END IF;
    arg_ut_end := $2; IF arg_ut_end IS NULL THEN arg_ut_end :=  ''infinity''; END IF;
    arg_st_beg := $3; IF arg_st_beg IS NULL THEN arg_st_beg := ''-infinity''; END IF;
    arg_st_end := $4; IF arg_st_end IS NULL THEN arg_st_end :=  ''infinity''; END IF;
    arg_st_max := $5; IF arg_st_max IS NULL THEN arg_st_max := 1;             END IF;
    
    --  perform the query operation
    IF arg_st_max = 1 THEN
        --  variant 1: take only maximum system time for each user time and oid
        FOR rec IN
           SELECT ut_oid, ut_hid
           FROM as_hs_ut_no, (
               SELECT   st_oid, max(st_hid) AS st_hid
               FROM     as_hs_st 
               WHERE    (arg_st_beg <= st_beg AND st_beg <= arg_st_end)
               GROUP BY st_oid
           ) AS as_hs_st
           WHERE
               ut_oid = st_oid AND ut_hid_max = st_hid
               AND (ut_beg <= arg_ut_end AND arg_ut_beg < ut_end)
        LOOP
           RETURN NEXT rec;
        END LOOP;
    ELSE
        --  variant 2: take all system times for each user time and oid
        FOR rec IN
           SELECT DISTINCT ut_oid, ut_hid
           FROM as_hs_ut_no, (
               SELECT   st_oid, st_hid
               FROM     as_hs_st 
               WHERE    (arg_st_beg <= st_beg AND st_beg <= arg_st_end)
           ) AS as_hs_st
           WHERE
               ut_oid = st_oid AND ut_hid_max = st_hid
               AND (ut_beg <= arg_ut_end AND arg_ut_beg < ut_end)
        LOOP
           RETURN NEXT rec;
        END LOOP;
    END IF;
    RETURN;
END;
';

--  convenience overloading functions
CREATE OR REPLACE FUNCTION as_hs
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
 TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
    SELECT * FROM as_hs($1, $2, $3, $4, NULL);
';
CREATE OR REPLACE FUNCTION as_hs
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
 TIMESTAMP (4) WITH TIME ZONE)
RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
    SELECT * FROM as_hs($1, $2, $3, NULL, NULL);
';
CREATE OR REPLACE FUNCTION as_hs
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
    SELECT * FROM as_hs($1, $2, NULL, NULL, NULL);
';
CREATE OR REPLACE FUNCTION as_hs
(TIMESTAMP (4) WITH TIME ZONE)
RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
    SELECT * FROM as_hs($1, NULL, NULL, NULL, NULL);
';
CREATE OR REPLACE FUNCTION as_hs ()
RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
    SELECT * FROM as_hs(NULL, NULL, NULL, NULL, NULL);
';

--  ##  _____________________________________________________________________
--  ##
--  ##  Account Table Convinience Manipulation Triggers
--  ##  _____________________________________________________________________
--  ##

--  as_account trigger function
CREATE OR REPLACE FUNCTION as_account_trigger() 
RETURNS TRIGGER LANGUAGE 'plpgsql' STABLE AS '
DECLARE
    hid INTEGER;
BEGIN
    IF TG_WHEN = ''BEFORE'' AND TG_OP = ''INSERT'' THEN
        IF new.ac_hid IS NULL THEN
            --  auto-generate history id if not explicitly set by user
            new.ac_hid := as_hs_make(new.ac_oid);
        END IF;
        RETURN new;
    ELSIF TG_WHEN = ''BEFORE'' AND TG_OP = ''UPDATE'' THEN
        --  clone old entry to allow old entry to be overwritten as it would be new entry
        INSERT INTO as_account VALUES (old.ac_oid, old.ac_name, old.ac_hid);
        --  give new entry a new history id if not explicity set by user
        IF (new.ac_hid = old.ac_hid) THEN
            new.ac_hid := as_hs_make(new.ac_oid);
        END IF;
        RETURN new;
    ELSIF TG_WHEN = ''AFTER'' AND TG_OP = ''DELETE'' THEN
        --  delete corresponding history entries
        PERFORM as_hs_delete(old.ac_oid, old.ac_hid); 
        RETURN old;
    END IF;
END;
';

--  as_account triggers
CREATE TRIGGER as_account_trigger_before
    BEFORE INSERT OR UPDATE ON as_account FOR EACH ROW
    EXECUTE PROCEDURE as_account_trigger();
CREATE TRIGGER as_account_trigger_after
    AFTER DELETE ON as_account FOR EACH ROW
    EXECUTE PROCEDURE as_account_trigger();

--  ##  _____________________________________________________________________
--  ##
--  ##  Parametrized Account Table View
--  ##  <as_account>* := as_account([ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]]])
--  ##  _____________________________________________________________________
--  ##

CREATE OR REPLACE FUNCTION as_account () 
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
    SELECT ac.* FROM as_account ac, as_hs() WHERE ac_hid = hs_hid;
';
CREATE OR REPLACE FUNCTION as_account
(TIMESTAMP (4) WITH TIME ZONE) 
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
    SELECT ac.* FROM as_account ac, as_hs($1) WHERE ac_hid = hs_hid;
';
CREATE OR REPLACE FUNCTION as_account
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
    SELECT ac.* FROM as_account ac, as_hs($1, $2, $3) WHERE ac_hid = hs_hid;
';
CREATE OR REPLACE FUNCTION as_account
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
 TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
    SELECT ac.* FROM as_account ac, as_hs($1, $2, $3, $4) WHERE ac_hid = hs_hid;
';
CREATE OR REPLACE FUNCTION as_account
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
 TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, INTEGER)
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
    SELECT ac.* FROM as_account ac, as_hs($1, $2, $3, $4, $5) WHERE ac_hid = hs_hid;
';

--  ##  _____________________________________________________________________
--  ##
--  ##  Test Data
--  ##  _____________________________________________________________________
--  ##

-- whole test data as view
-- #  ^          ^
-- #  |          |
-- #  as_hs_st         hid      |          |          |         |
-- #  2007-01-01 #6       |          [==20:bar3=[         |
-- #  2006-01-01 #5       |          [==20:bar2===========[
-- #  2005-01-01 #4       [=============20:bar1===========[
-- #  2004-01-01 #3       [=============10:foo============[
-- #  2002-01-01 #2       |          [==10:bar==[         |
-- #  2001-01-01 #1       [==10:foo==[          |         |
-- #           as_hs_ut-->  0100-01-01 0200-01-01 0300-01-01 infinity

-- whole test data as raw data
-- #  1st (systime hid=1) create record for oid=10, name="foo",  100 <= usertime < 200
-- #  2nd (systime hid=2) create record for oid=10, name="bar",  200 <= usertime < 300
-- #  3rd (systime hid=3) create record for oid=10, name="foo",  100 <= usertime < oo
-- #  4th (systime hid=4) create record for oid=20, name="bar1", 100 <= usertime < oo
-- #  5th (systime hid=5) create record for oid=20, name="bar2", 200 <= usertime < oo
-- #  6th (systime hid=6) create record for oid=20, name="bar3", 200 <= usertime < 300

-- whole test data in prosa
-- #  1st create a new record "foo" containing payload valid between usertime 100 upto not including 200
-- #  2nd rename "foo" to "bar" and move usertime frame to 200 upto n. i. 300
-- #  3rd rename "bar" back to "foo", extend usertime begin back to original 100 and extend usertime end to infinity
-- #  4th create a new record "bar1" containing payload valid between usertime 100 and infinity
-- #  5th give "bar1" a new name "bar2" beginning with usertime 200
-- #  6th another new name "bar3" valid for usertime frame 200 upto n. i. 300

-- whole test data injected
INSERT INTO as_account VALUES (10, 'foo',  as_hs_make(10, '0100-01-01', '0200-01-01', '2001-01-01'));
INSERT INTO as_account VALUES (10, 'bar',  as_hs_make(10, '0200-01-01', '0300-01-01', '2002-01-01'));
INSERT INTO as_account VALUES (10, 'foo',  as_hs_make(10, '0100-01-01', 'infinity',   '2004-01-01'));
INSERT INTO as_account VALUES (20, 'bar1', as_hs_make(20, '0100-01-01', 'infinity',   '2005-01-01'));
INSERT INTO as_account VALUES (20, 'bar2', as_hs_make(20, '0200-01-01', 'infinity',   '2006-01-01'));
INSERT INTO as_account VALUES (20, 'bar3', as_hs_make(20, '0200-01-01', '0300-01-01', '2007-01-01'));

--  ##  _____________________________________________________________________
--  ##
--  ##  Test Data Dumping
--  ##  _____________________________________________________________________
--  ##

select * from as_account;
-- #  ac_oid | ac_name | ac_hid 
-- # --------+---------+--------
-- #      10 | foo     |      1
-- #      10 | bar     |      2
-- #      10 | foo     |      3
-- #      20 | bar1    |      4
-- #      20 | bar2    |      5
-- #      20 | bar3    |      6
-- # (6 rows)

select * from as_hs_ut;
-- #  ut_oid |       ut_beg        |       ut_end        | ut_hid 
-- # --------+---------------------+---------------------+--------
-- #      10 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 |      1
-- #      10 | 0200-01-01 00:00:00 | 0300-01-01 00:00:00 |      2
-- #      10 | 0100-01-01 00:00:00 | infinity            |      3
-- #      20 | 0100-01-01 00:00:00 | infinity            |      4
-- #      20 | 0200-01-01 00:00:00 | infinity            |      5
-- #      20 | 0200-01-01 00:00:00 | 0300-01-01 00:00:00 |      6
-- # (6 rows)

select * from as_hs_ut_no;
-- #  ut_oid |       ut_beg        |       ut_end        | ut_hid | ut_hid_max 
-- # --------+---------------------+---------------------+--------+------------
-- #      10 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 |      1 |          1
-- #      10 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 |      1 |          2
-- #      10 | 0200-01-01 00:00:00 | 0300-01-01 00:00:00 |      2 |          2
-- #      10 | 0100-01-01 00:00:00 | infinity            |      3 |          3
-- #      20 | 0100-01-01 00:00:00 | infinity            |      4 |          4
-- #      20 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 |      4 |          5
-- #      20 | 0200-01-01 00:00:00 | infinity            |      5 |          5
-- #      20 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 |      4 |          6
-- #      20 | 0200-01-01 00:00:00 | 0300-01-01 00:00:00 |      6 |          6
-- #      20 | 0300-01-01 00:00:00 | infinity            |      5 |          6
-- # (10 rows)

select * from as_hs_st;
-- #  st_oid | st_hid |         st_beg         
-- # --------+--------+------------------------
-- #      10 |      1 | 2001-01-01 00:00:00+01
-- #      10 |      2 | 2002-01-01 00:00:00+01
-- #      10 |      3 | 2004-01-01 00:00:00+01
-- #      20 |      4 | 2005-01-01 00:00:00+01
-- #      20 |      5 | 2006-01-01 00:00:00+01
-- #      20 |      6 | 2007-01-01 00:00:00+01
-- # (6 rows)

-- ##
-- ##  Test Usage
-- ##

-- whole test data retrieved
SELECT * FROM as_account;
-- #   ac_oid | ac_name | ac_hid 
-- #  --------+---------+--------
-- #       10 | foo     |      1
-- #       10 | bar     |      2
-- #       10 | foo     |      3
-- #       20 | bar1    |      4
-- #       20 | bar2    |      5
-- #       20 | bar3    |      6
-- #  (6 rows)

-- for any usertime and any systime, list any valid oid for every point in systime (all oids with full history)
SELECT * FROM as_hs('-infinity','infinity','-infinity','infinity',0);
-- #   hs_oid | hs_hid 
-- #  --------+--------
-- #       10 |      1
-- #       10 |      2
-- #       10 |      3
-- #       20 |      4
-- #       20 |      5
-- #       20 |      6
-- #  (6 rows)

-- for any usertime and any systime, list max valid oid for every point in systime (oids with highest altitude)
SELECT * FROM as_hs('-infinity','infinity','-infinity','infinity',1); -- same as SELECT FROM as_hs();
-- #   hs_oid | hs_hid 
-- #  --------+--------
-- #       10 |      3
-- #       20 |      4
-- #       20 |      6
-- #       20 |      5
-- #  (4 rows)

-- same as previous but join with payload to retrieve more details (details from oids with hightes altitude)
SELECT * FROM as_account, as_hs() WHERE ac_hid = hs_hid;
-- #   ac_oid | ac_name | ac_hid | hs_oid | hs_hid 
-- #  --------+---------+--------+--------+--------
-- #       10 | foo     |      3 |     10 |      3
-- #       20 | bar1    |      4 |     20 |      4
-- #       20 | bar2    |      5 |     20 |      5
-- #       20 | bar3    |      6 |     20 |      6
-- #  (4 rows)

-- same as previous but we are only interested in oids valid for usertime point 222
SELECT * FROM as_account, as_hs('0222-01-01','0222-01-01','-infinity','infinity',1) WHERE ac_hid = hs_hid;
-- #   ac_oid | ac_name | ac_hid | hs_oid | hs_hid 
-- #  --------+---------+--------+--------+--------
-- #       10 | foo     |      3 |     10 |      3
-- #       20 | bar3    |      6 |     20 |      6
-- #  (2 rows)

-- same as previous but we look back in systime 2006
SELECT * FROM as_account, as_hs('0222-01-01','0222-01-01','-infinity','2006-01-01',1) WHERE ac_hid = hs_hid;
-- #   ac_oid | ac_name | ac_hid | hs_oid | hs_hid 
-- #  --------+---------+--------+--------+--------
-- #       10 | foo     |      3 |     10 |      3
-- #       20 | bar2    |      5 |     20 |      5
-- #  (2 rows)