\c template1
DROP DATABASE call;
CREATE DATABASE call;
\c call

-- Create user groups
CREATE GROUP operators; -- response operators
CREATE GROUP phoneMonitors;
CREATE GROUP trustedReports; -- people who can read information from virtually anywhere in the database, from any IP address
CREATE GROUP untrustedReports; -- this account information may get to our competitors
CREATE GROUP fieldReps; -- for our reps who are usually in the field
CREATE GROUP serviceProviders; -- haulers, etc.

CREATE USER test_operator WITH PASSWORD 'test' IN GROUP operators;



CREATE SEQUENCE seq_call;
CREATE TABLE call
(
    id integer primary key default nextval('seq_call'),
    ringtime timestamp(0) not null default CURRENT_TIMESTAMP,
    phonenumber varchar(200) not null,
    lastModified timestamp(0) not null default CURRENT_TIMESTAMP(0) CHECK (lastModified = CURRENT_TIMESTAMP(0)),
    modifiedBy name not null default CURRENT_USER CHECK (modifiedBy = CURRENT_USER)
);
GRANT INSERT ON call TO GROUP phoneMonitors, GROUP operators;

INSERT INTO call (phonenumber) VALUES ('03 9528 2402');

--    who name not null default SESSION_USER

CREATE RULE call_insert AS ON INSERT TO call
    DO NOTIFY call_added;

CREATE RULE call_update AS ON UPDATE TO call
    DO NOTIFY call_updated;

CREATE OR REPLACE FUNCTION ageSeconds (timestamp) RETURNS integer AS '
    SELECT (CAST((extract(epoch from CURRENT_TIMESTAMP) - extract(epoch from $1)) AS integer) );
' LANGUAGE SQL;

CREATE OR REPLACE VIEW RecentCalls AS SELECT
        id AS _id,
        ageSeconds(ringtime) AS ringAge,
        phonenumber,
        ringtime,
        modifiedBy,
        ageSeconds(lastModified) AS _Modifiedage
    FROM call
    WHERE ageSeconds(lastModified) < 3600 * 3 -- limit to last 3 hours' data
;
GRANT SELECT ON RecentCalls TO GROUP PUBLIC;
