-- Bootstrap roles/schemas. Replace passwords via ALTER ROLE from secret-aware deployment; do not hardcode real passwords.
BEGIN;
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'identity_role') THEN CREATE ROLE identity_role LOGIN; END IF; END $$;
CREATE SCHEMA IF NOT EXISTS identity AUTHORIZATION identity_role;
REVOKE ALL ON SCHEMA identity FROM PUBLIC;
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'authority_role') THEN CREATE ROLE authority_role LOGIN; END IF; END $$;
CREATE SCHEMA IF NOT EXISTS authority AUTHORIZATION authority_role;
REVOKE ALL ON SCHEMA authority FROM PUBLIC;
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'situation_role') THEN CREATE ROLE situation_role LOGIN; END IF; END $$;
CREATE SCHEMA IF NOT EXISTS situation AUTHORIZATION situation_role;
REVOKE ALL ON SCHEMA situation FROM PUBLIC;
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'work_role') THEN CREATE ROLE work_role LOGIN; END IF; END $$;
CREATE SCHEMA IF NOT EXISTS work AUTHORIZATION work_role;
REVOKE ALL ON SCHEMA work FROM PUBLIC;
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'epistemics_role') THEN CREATE ROLE epistemics_role LOGIN; END IF; END $$;
CREATE SCHEMA IF NOT EXISTS epistemics AUTHORIZATION epistemics_role;
REVOKE ALL ON SCHEMA epistemics FROM PUBLIC;
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'continuity_role') THEN CREATE ROLE continuity_role LOGIN; END IF; END $$;
CREATE SCHEMA IF NOT EXISTS continuity AUTHORIZATION continuity_role;
REVOKE ALL ON SCHEMA continuity FROM PUBLIC;
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'capability_role') THEN CREATE ROLE capability_role LOGIN; END IF; END $$;
CREATE SCHEMA IF NOT EXISTS capability AUTHORIZATION capability_role;
REVOKE ALL ON SCHEMA capability FROM PUBLIC;
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'commit_role') THEN CREATE ROLE commit_role LOGIN; END IF; END $$;
CREATE SCHEMA IF NOT EXISTS commit AUTHORIZATION commit_role;
REVOKE ALL ON SCHEMA commit FROM PUBLIC;
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'artifact_role') THEN CREATE ROLE artifact_role LOGIN; END IF; END $$;
CREATE SCHEMA IF NOT EXISTS artifact AUTHORIZATION artifact_role;
REVOKE ALL ON SCHEMA artifact FROM PUBLIC;

SET ROLE identity_role;
CREATE TABLE IF NOT EXISTS identity.actors (
  actor_id TEXT PRIMARY KEY,
  display_name TEXT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS identity.principals (
  principal_id TEXT PRIMARY KEY,
  display_name TEXT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS identity.principal_bindings (
  actor_id TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  active BOOLEAN NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (actor_id, principal_id)
);
INSERT INTO identity.actors (actor_id, display_name) VALUES ('domovoy-creator-actor', 'Domovoy Creator') ON CONFLICT (actor_id) DO NOTHING;
INSERT INTO identity.principals (principal_id, display_name) VALUES ('domovoy-creator-principal', 'Domovoy Creator Principal') ON CONFLICT (principal_id) DO NOTHING;
INSERT INTO identity.principal_bindings (actor_id, principal_id, active) VALUES ('domovoy-creator-actor', 'domovoy-creator-principal', TRUE) ON CONFLICT (actor_id, principal_id) DO NOTHING;
RESET ROLE;

SET ROLE authority_role;
CREATE TABLE IF NOT EXISTS authority.scopes (
  scope_id TEXT PRIMARY KEY,
  label TEXT NOT NULL,
  global_scope BOOLEAN NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS authority.authority_grants (
  grant_id TEXT PRIMARY KEY,
  actor_id TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  scope_id TEXT NOT NULL,
  grant_class TEXT NOT NULL,
  revision BIGINT NOT NULL,
  active BOOLEAN NOT NULL,
  valid_from TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  valid_until TIMESTAMPTZ,
  CHECK (revision > 0)
);
INSERT INTO authority.scopes (scope_id, label, global_scope) VALUES ('domovoy-global-scope', 'All Domovoy scopes', TRUE) ON CONFLICT (scope_id) DO NOTHING;
INSERT INTO authority.authority_grants (grant_id, actor_id, principal_id, scope_id, grant_class, revision, active) VALUES ('domovoy-creator-grant', 'domovoy-creator-actor', 'domovoy-creator-principal', 'domovoy-global-scope', 'CREATOR', 1, TRUE) ON CONFLICT (grant_id) DO NOTHING;
RESET ROLE;

SET ROLE commit_role;
CREATE TABLE IF NOT EXISTS commit.authorized_actions (
  authorized_action_id TEXT PRIMARY KEY, action_intent_id TEXT NOT NULL,
  state TEXT NOT NULL, revision BIGINT NOT NULL, CHECK (revision > 0)
);
CREATE TABLE IF NOT EXISTS commit.action_attempts (
  action_attempt_id TEXT PRIMARY KEY, authorized_action_id TEXT NOT NULL,
  action_intent_id TEXT NOT NULL, state TEXT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revision BIGINT NOT NULL DEFAULT 1,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (revision > 0)
);
CREATE TABLE IF NOT EXISTS commit.authorized_action_envelopes (
  authorized_action_id TEXT PRIMARY KEY,
  action_intent_id TEXT NOT NULL UNIQUE,
  actor_id TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  grant_id TEXT NOT NULL,
  grant_revision BIGINT NOT NULL,
  scope_id TEXT NOT NULL,
  task_id TEXT NOT NULL,
  goal_id TEXT NOT NULL,
  task_scope_id TEXT NOT NULL,
  task_revision BIGINT NOT NULL,
  capability_id TEXT NOT NULL,
  operation TEXT NOT NULL,
  prospective_item_id TEXT NOT NULL,
  expected_prospective_revision BIGINT NOT NULL,
  prospective_from_state TEXT NOT NULL,
  prospective_to_state TEXT NOT NULL,
  evidence_ref TEXT NOT NULL,
  proposal_state TEXT NOT NULL,
  action_state TEXT NOT NULL,
  authorized_at_unix_millis BIGINT NOT NULL,
  envelope_revision BIGINT NOT NULL,
  notification_template_code TEXT NOT NULL DEFAULT '',
  capability_binding_id TEXT NOT NULL DEFAULT '',
  capability_binding_revision BIGINT NOT NULL DEFAULT 0,
  notification_title TEXT NOT NULL DEFAULT '',
  notification_message TEXT NOT NULL DEFAULT '',
  request_digest TEXT NOT NULL DEFAULT '',
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (grant_revision > 0), CHECK (task_revision > 0),
  CHECK (authorized_at_unix_millis > 0), CHECK (envelope_revision = 1),
  CHECK (operation <> 'deliver-prospective-notification-v1' OR (
    notification_template_code = 'prospective-delivered-v1' AND notification_title <> '' AND notification_message <> '' AND capability_binding_id <> '' AND
    capability_binding_revision > 0 AND request_digest ~ '^[0-9a-f]{64}$' AND
    expected_prospective_revision > 0 AND prospective_item_id <> ''
  ))
);
RESET ROLE;

SET ROLE work_role;
CREATE TABLE IF NOT EXISTS work.tasks (
  task_id TEXT PRIMARY KEY, goal_id TEXT NOT NULL, scope_id TEXT NOT NULL,
  revision BIGINT NOT NULL, CHECK (revision > 0)
);
RESET ROLE;

SET ROLE situation_role;
CREATE TABLE IF NOT EXISTS situation.focus_bindings (
  focus_binding_id TEXT PRIMARY KEY,
  actor_id TEXT NOT NULL,
  session_id TEXT NOT NULL,
  workspace_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  goal_id TEXT NOT NULL,
  scope_id TEXT NOT NULL,
  active BOOLEAN NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
RESET ROLE;

SET ROLE capability_role;
CREATE TABLE IF NOT EXISTS capability.health (
  capability_id TEXT PRIMARY KEY,
  state TEXT NOT NULL,
  reason TEXT NOT NULL,
  revision BIGINT NOT NULL,
  semantic_version INTEGER NOT NULL,
  observed_at_unix_millis BIGINT,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (revision > 0),
  CHECK ((semantic_version = 1 AND observed_at_unix_millis IS NULL) OR (semantic_version = 2 AND observed_at_unix_millis > 0))
);
CREATE TABLE IF NOT EXISTS capability.health_projection (
  capability_id TEXT PRIMARY KEY,
  state TEXT NOT NULL,
  reason TEXT NOT NULL,
  revision BIGINT NOT NULL,
  semantic_version INTEGER NOT NULL,
  observed_at_unix_millis BIGINT
);
CREATE TABLE IF NOT EXISTS capability.notification_bindings (
  binding_id TEXT NOT NULL, capability_id TEXT NOT NULL, owner_principal_id TEXT NOT NULL,
  revision BIGINT NOT NULL, state TEXT NOT NULL, https_origin TEXT NOT NULL,
  delivery_path TEXT NOT NULL, receipt_path TEXT NOT NULL, protocol TEXT NOT NULL,
  secret_ref TEXT NOT NULL, verified_at_unix_millis BIGINT NOT NULL, health TEXT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (binding_id, revision),
  CHECK (revision > 0), CHECK (verified_at_unix_millis > 0),
  CHECK (state IN ('ACTIVE','RETIRED')), CHECK (health IN ('AVAILABLE','DEGRADED','UNAVAILABLE')),
  CHECK (protocol = 'IDEMPOTENT_RECEIPT_V1'), CHECK (https_origin LIKE 'https://%'),
  CHECK (delivery_path LIKE '/%'), CHECK (receipt_path LIKE '/%')
);
CREATE TABLE IF NOT EXISTS capability.notification_binding_heads (
  capability_id TEXT NOT NULL, owner_principal_id TEXT NOT NULL,
  binding_id TEXT NOT NULL, revision BIGINT NOT NULL,
  PRIMARY KEY (capability_id, owner_principal_id), CHECK (revision > 0)
);
RESET ROLE;

SET ROLE continuity_role;
CREATE TABLE IF NOT EXISTS continuity.prospective_items (
  prospective_item_id TEXT PRIMARY KEY,
  commitment_id TEXT NOT NULL,
  owner_principal_id TEXT NOT NULL,
  scope_id TEXT NOT NULL,
  goal_id TEXT NOT NULL,
  trigger_at_unix_millis BIGINT NOT NULL,
  state TEXT NOT NULL,
  revision BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (revision > 0)
);
CREATE TABLE IF NOT EXISTS continuity.action_receipts (
  action_intent_id TEXT PRIMARY KEY,
  authorized_action_id TEXT NOT NULL,
  owner_principal_id TEXT NOT NULL,
  prospective_item_id TEXT NOT NULL,
  expected_revision BIGINT NOT NULL,
  from_state TEXT NOT NULL,
  to_state TEXT NOT NULL,
  evidence_ref TEXT NOT NULL,
  result_state TEXT NOT NULL,
  result_revision BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (expected_revision > 0), CHECK (result_revision > expected_revision)
);
RESET ROLE;
COMMIT;