-- -*- sql-product: postgres; -*-

--Schema.
create schema if not exists core;

-- Functions
create function core.check_created () returns trigger as $$
begin
  raise exception using
    message = 'The CREATED field is immutable.',
    hint = 'Drop the CREATED field from the SQL statement.',
    detail = 'BR-001:  All CREATED fields are immutable';
  return new;
end
$$ language plpgsql;

create function core.check_updated () returns trigger as $$
begin
  raise exception using
    message = 'The UPDATED field is read-only.',
    hint = 'Drop the UPDATED field from the SQL statement.',
    detail = 'BR-002:  All UPDATED fields are read-only';
  return new;
end
$$ language plpgsql;

create function core.rewrite_updated () returns trigger as $$
begin
  new.updated = now();
  return new;
end
$$ language plpgsql;

-- Tables
create table core.entity (
id uuid not null unique default public.uuid_generate_v4());

create table core.tracked (
created timestamp not null default now(),
updated timestamp not null default now());

create type public.cms_status as enum ('PUBLISHED', 'DRAFT');

create table core.topic_page (
primary key (id),
taxonomy_id text not null,
cms_name text unique not null,
subject text not null,
classification text not null,
entry_name text unique not null,
status public.cms_status not null,
entry_updated timestamp not null,
entry_published timestamp not null)
inherits (core.entity, core.tracked);

create trigger check_created before update of created on core.topic_page
for each row execute procedure core.check_created();

create trigger check_updated before update of updated on core.topic_page
for each row execute procedure core.check_updated();

create trigger rewrite_updated before update on core.topic_page
for each row execute procedure core.rewrite_updated();

-- Views
create view public.topic_page as select * from core.topic_page with check option;

create table core.subject_page (
primary key (id),
taxonomy_id text not null,
cms_name text unique not null,
subject text unique not null,
status public.cms_status not null,
entry_updated timestamp not null,
entry_published timestamp not null)
inherits (core.entity, core.tracked);

create trigger check_created before update of created on core.subject_page
for each row execute procedure core.check_created();

create trigger check_updated before update of updated on core.subject_page
for each row execute procedure core.check_updated();

create trigger rewrite_updated before update on core.subject_page
for each row execute procedure core.rewrite_updated();

-- Views
create view public.subject_page as select * from core.subject_page with check option;