-- Phase 7: close, file, PDFs and prints.

-- Every printed copy and every export, append-only. Who, when, what and how many pages.
CREATE TABLE print_log (
    id          bigserial PRIMARY KEY,
    at          timestamptz NOT NULL DEFAULT now(),
    by          uuid REFERENCES users (id),
    by_name     text,
    session_id  uuid,
    packet_id   bigint REFERENCES packets (id),
    unit_id     bigint REFERENCES units (id),
    itp_id      bigint REFERENCES itps (id),
    -- traveler, map-h, map-w, clean, void, databook, doc, itp, csv, databook-export, itp-pdf
    kind        text NOT NULL,
    -- the human label: "Job traveler", "Drawing with heat map (sheet 2)", "MTR - item 3"
    what        text NOT NULL,
    pages       int,
    -- the document printed, for kind doc; the filed data book for databook
    asset_id    uuid REFERENCES assets (id),
    -- the stamp line as printed, and the 16 hex characters signed into it
    stamp       text,
    stamp_sig   text,
    ip          text
);
CREATE INDEX print_log_packet ON print_log (packet_id, id);
CREATE INDEX print_log_itp ON print_log (itp_id, id);

CREATE TRIGGER print_log_append_only
    BEFORE UPDATE OR DELETE ON print_log
    FOR EACH ROW EXECUTE FUNCTION ps_append_only();
CREATE TRIGGER print_log_no_truncate
    BEFORE TRUNCATE ON print_log
    FOR EACH STATEMENT EXECUTE FUNCTION ps_append_only();

-- Explicit, after the default privileges: the app may only read and append the print log.
REVOKE UPDATE, DELETE, TRUNCATE ON print_log FROM ps_app;
GRANT SELECT, INSERT ON print_log TO ps_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ps_app;

-- The data book filed with a seal, and the work orders the feed still showed open at filing.
ALTER TABLE packet_seals ADD COLUMN databook_asset_id uuid REFERENCES assets (id);
ALTER TABLE packet_seals ADD COLUMN databook_pages int;
ALTER TABLE packet_seals ADD COLUMN open_wos text[];