-- 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;
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'interaction_role') THEN CREATE ROLE interaction_role LOGIN; END IF; END $$;
CREATE SCHEMA IF NOT EXISTS interaction AUTHORIZATION interaction_role;
REVOKE ALL ON SCHEMA interaction 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)
);
CREATE TABLE IF NOT EXISTS work.goals (
  goal_id TEXT PRIMARY KEY,
  source_intent_id TEXT NOT NULL,
  source_utterance_id TEXT NOT NULL,
  actor_id TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  scope_id TEXT NOT NULL,
  project_id TEXT NOT NULL DEFAULT '',
  project_display_name TEXT NOT NULL DEFAULT '',
  desired_outcome TEXT NOT NULL,
  constraints_text TEXT NOT NULL,
  state TEXT NOT NULL,
  provenance TEXT NOT NULL DEFAULT '',
  revision BIGINT NOT NULL,
  created_at_unix_millis BIGINT NOT NULL,
  updated_at_unix_millis BIGINT NOT NULL,
  CHECK (desired_outcome <> ''),
  CHECK (state IN ('PROPOSED','UNDERSTOOD','PLANNING','READY','IN_PROGRESS','BLOCKED','COMPLETED','FAILED','CANCELLED','SUPERSEDED')),
  CHECK (revision > 0)
);
CREATE TABLE IF NOT EXISTS work.plan_candidates (
  plan_candidate_id TEXT PRIMARY KEY,
  goal_id TEXT NOT NULL,
  source TEXT NOT NULL,
  state TEXT NOT NULL,
  summary TEXT NOT NULL,
  validation_summary TEXT NOT NULL,
  requires_clarification BOOLEAN NOT NULL,
  accepted_at_unix_millis BIGINT NOT NULL DEFAULT 0,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (goal_id <> ''),
  CHECK (source <> ''),
  CHECK (summary <> ''),
  CHECK (state IN ('PROPOSED','VALIDATED','ACCEPTED','REJECTED'))
);
CREATE TABLE IF NOT EXISTS work.work_plans (
  work_plan_id TEXT PRIMARY KEY,
  goal_id TEXT NOT NULL,
  accepted_candidate_id TEXT NOT NULL,
  revision BIGINT NOT NULL,
  state TEXT NOT NULL,
  summary TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (goal_id <> ''),
  CHECK (accepted_candidate_id <> ''),
  CHECK (revision > 0),
  CHECK (state IN ('PROPOSED','VALIDATED','ACCEPTED','REJECTED'))
);
CREATE TABLE IF NOT EXISTS work.capability_requirements (
  capability_requirement_id TEXT PRIMARY KEY,
  plan_candidate_id TEXT NOT NULL,
  capability_kind TEXT NOT NULL,
  capability_id TEXT NOT NULL DEFAULT '',
  purpose TEXT NOT NULL,
  state TEXT NOT NULL,
  epistemic_precondition TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (plan_candidate_id <> ''),
  CHECK (capability_kind <> ''),
  CHECK (purpose <> ''),
  CHECK (state IN ('REQUIRED','AVAILABLE','MISSING','BLOCKED')),
  CHECK (epistemic_precondition IN ('UNVERIFIED','VERIFIED','REJECTED','UNKNOWN','NOT_APPLICABLE'))
);
CREATE TABLE IF NOT EXISTS work.plan_steps (
  plan_step_id TEXT PRIMARY KEY,
  plan_candidate_id TEXT NOT NULL,
  ordinal INTEGER NOT NULL,
  kind TEXT NOT NULL,
  description TEXT NOT NULL,
  requires_capability_id TEXT NOT NULL DEFAULT '',
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (plan_candidate_id <> ''),
  CHECK (ordinal > 0),
  CHECK (kind IN ('INVESTIGATE','OBSERVE','VERIFY','REASON','REPORT','PREPARE','COMMUNICATE','SCHEDULE','MODIFY','RUN_CHECKS','CLARIFY','NO_EXECUTION')),
  CHECK (description <> '')
);
CREATE TABLE IF NOT EXISTS work.delegation_candidates (
  delegation_candidate_id TEXT PRIMARY KEY,
  plan_candidate_id TEXT NOT NULL,
  capability_requirement_id TEXT NOT NULL,
  kind TEXT NOT NULL,
  executor_hint TEXT NOT NULL DEFAULT '',
  state TEXT NOT NULL,
  reason TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (plan_candidate_id <> ''),
  CHECK (capability_requirement_id <> ''),
  CHECK (kind IN ('AGENT','TOOL','ADAPTER','HUMAN','UNAVAILABLE')),
  CHECK (state IN ('PROPOSED','SELECTED','BLOCKED')),
  CHECK (reason <> '')
);
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
);
CREATE TABLE IF NOT EXISTS situation.projects (
  project_id TEXT PRIMARY KEY,
  display_name TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (project_id <> ''),
  CHECK (display_name <> '')
);
CREATE TABLE IF NOT EXISTS situation.project_aliases (
  project_id TEXT NOT NULL,
  alias TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  PRIMARY KEY (project_id, alias),
  CHECK (project_id <> ''),
  CHECK (alias <> '')
);
CREATE TABLE IF NOT EXISTS situation.active_project_focus (
  actor_id TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  project_display_name TEXT NOT NULL,
  provenance TEXT NOT NULL,
  confidence_millis BIGINT NOT NULL,
  source_utterance_id TEXT NOT NULL,
  updated_at_unix_millis BIGINT NOT NULL,
  PRIMARY KEY (actor_id, principal_id),
  CHECK (provenance IN ('EXPLICIT','INHERITED_FOCUS','ENTITY_REFERENCE','TASK_REFERENCE','REPOSITORY_REFERENCE','INFERRED','USER_CONFIRMED','AMBIGUOUS')),
  CHECK (confidence_millis BETWEEN 0 AND 1000)
);
INSERT INTO situation.projects(project_id, display_name, recorded_at_unix_millis)
VALUES ('assistant', 'Assistant', 1), ('upo', 'УПО', 1)
ON CONFLICT (project_id) DO NOTHING;
INSERT INTO situation.project_aliases(project_id, alias, recorded_at_unix_millis)
VALUES
  ('assistant', 'assistant', 1),
  ('assistant', 'домовой', 1),
  ('assistant', 'ассистент', 1),
  ('upo', 'упо', 1),
  ('upo', 'upo', 1)
ON CONFLICT (project_id, alias) DO NOTHING;
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)
);
CREATE TABLE IF NOT EXISTS capability.cognitive_provider_instances (
  provider_instance_id TEXT NOT NULL, revision BIGINT NOT NULL, provider_kind TEXT NOT NULL,
  endpoint_ref TEXT NOT NULL, credential_ref TEXT, enabled BOOLEAN NOT NULL,
  qualification TEXT NOT NULL, health TEXT NOT NULL, observed_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (provider_instance_id, revision), CHECK (revision > 0),
  CHECK (qualification IN ('DISCOVERED','PROBING','EVALUATING','VALIDATED','REJECTED')),
  CHECK (health IN ('UNKNOWN','AVAILABLE','DEGRADED','UNAVAILABLE')), CHECK (observed_at_unix_millis > 0)
);
CREATE TABLE IF NOT EXISTS capability.cognitive_model_descriptors (
  model_descriptor_id TEXT NOT NULL, revision BIGINT NOT NULL, provider_instance_id TEXT NOT NULL,
  provider_model_id TEXT NOT NULL, fingerprint TEXT NOT NULL, max_context_bytes BIGINT NOT NULL,
  modalities TEXT[] NOT NULL, capabilities TEXT[] NOT NULL, qualification TEXT NOT NULL,
  health TEXT NOT NULL, observed_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (model_descriptor_id, revision), CHECK (revision > 0), CHECK (max_context_bytes >= 0),
  CHECK (cardinality(modalities) > 0), CHECK (cardinality(capabilities) > 0),
  CHECK (qualification IN ('DISCOVERED','PROBING','EVALUATING','VALIDATED','REJECTED')),
  CHECK (health IN ('UNKNOWN','AVAILABLE','DEGRADED','UNAVAILABLE')), CHECK (observed_at_unix_millis > 0)
);
CREATE TABLE IF NOT EXISTS capability.cognitive_bindings (
  binding_id TEXT NOT NULL, revision BIGINT NOT NULL, capability_id TEXT NOT NULL,
  owner_principal_id TEXT NOT NULL, provider_instance_id TEXT NOT NULL, provider_revision BIGINT NOT NULL,
  model_descriptor_id TEXT NOT NULL, model_revision BIGINT NOT NULL,
  data_policy TEXT NOT NULL, qualification TEXT NOT NULL, activation TEXT NOT NULL,
  health TEXT NOT NULL, limit_policy_ref TEXT NOT NULL, recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (binding_id, revision), CHECK (revision > 0), CHECK (provider_revision > 0), CHECK (model_revision > 0),
  CHECK (data_policy IN ('LOCAL_ONLY','EXTERNAL_MESSAGE_ONLY','EXTERNAL_MINIMIZED_CONTEXT')),
  CHECK (qualification IN ('DISCOVERED','PROBING','EVALUATING','VALIDATED','REJECTED')),
  CHECK (activation IN ('INACTIVE','CANARY','ACTIVE','DEPRECATED','RETIRED')),
  CHECK (health IN ('UNKNOWN','AVAILABLE','DEGRADED','UNAVAILABLE'))
);
CREATE TABLE IF NOT EXISTS capability.cognitive_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)
);
CREATE TABLE IF NOT EXISTS capability.cognitive_binding_revision_heads (
  binding_id TEXT PRIMARY KEY,
  revision BIGINT NOT NULL CHECK (revision > 0),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS capability.cognitive_usage_observations (
  observation_id TEXT PRIMARY KEY, request_id TEXT NOT NULL, binding_id TEXT NOT NULL,
  binding_revision BIGINT NOT NULL, provider_instance_id TEXT NOT NULL, model_descriptor_id TEXT NOT NULL,
  observed_at_unix_millis BIGINT NOT NULL, latency_millis BIGINT NOT NULL,
  input_units BIGINT NOT NULL, output_units BIGINT NOT NULL, estimated_cost_micros BIGINT,
  actual_cost_micros BIGINT, result_class TEXT NOT NULL, failure_type TEXT NOT NULL DEFAULT '',
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (binding_revision > 0), CHECK (observed_at_unix_millis > 0), CHECK (latency_millis >= 0),
  CHECK (input_units >= 0), CHECK (output_units >= 0),
  CHECK (estimated_cost_micros IS NULL OR estimated_cost_micros >= 0),
  CHECK (actual_cost_micros IS NULL OR actual_cost_micros >= 0)
);
CREATE TABLE IF NOT EXISTS capability.cognitive_provider_health (
  provider_instance_id TEXT PRIMARY KEY, health TEXT NOT NULL, observed_at_unix_millis BIGINT NOT NULL,
  failure_class TEXT NOT NULL DEFAULT '', recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (health IN ('UNKNOWN','AVAILABLE','DEGRADED','UNAVAILABLE')), CHECK (observed_at_unix_millis > 0)
);
CREATE TABLE IF NOT EXISTS capability.cognitive_model_availability (
  provider_instance_id TEXT NOT NULL, model_descriptor_id TEXT NOT NULL,
  installed TEXT NOT NULL, loaded TEXT NOT NULL, invokable TEXT NOT NULL, health TEXT NOT NULL,
  observed_at_unix_millis BIGINT NOT NULL, recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (provider_instance_id, model_descriptor_id),
  CHECK (installed IN ('NOT_OBSERVED','YES','NO')), CHECK (loaded IN ('NOT_OBSERVED','YES','NO')),
  CHECK (invokable IN ('NOT_OBSERVED','YES','NO')), CHECK (health IN ('UNKNOWN','AVAILABLE','DEGRADED','UNAVAILABLE')),
  CHECK (observed_at_unix_millis > 0)
);
CREATE TABLE IF NOT EXISTS capability.cognitive_evaluated_capabilities (
  evaluation_id TEXT PRIMARY KEY, binding_id TEXT NOT NULL, binding_revision BIGINT NOT NULL,
  capability_id TEXT NOT NULL, result TEXT NOT NULL, quality_score_millis BIGINT,
  critical_failure_millis BIGINT, benchmark_version TEXT NOT NULL, artifact_id TEXT NOT NULL,
  observed_at_unix_millis BIGINT NOT NULL, recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (binding_revision > 0), CHECK (result IN ('UNKNOWN','SUPPORTED','UNSUPPORTED')),
  CHECK (quality_score_millis IS NULL OR quality_score_millis >= 0),
  CHECK (critical_failure_millis IS NULL OR critical_failure_millis >= 0), CHECK (observed_at_unix_millis > 0)
);
CREATE TABLE IF NOT EXISTS capability.cognitive_performance_evidence (
  evidence_id TEXT PRIMARY KEY, binding_id TEXT NOT NULL, binding_revision BIGINT NOT NULL,
  model_descriptor_id TEXT NOT NULL, model_revision BIGINT NOT NULL, hardware_profile TEXT NOT NULL,
  runtime TEXT NOT NULL, quantization TEXT NOT NULL, context_units BIGINT, thinking_enabled BOOLEAN,
  generation_tps_millis BIGINT, prompt_tps_millis BIGINT, ttft_micros BIGINT, latency_micros BIGINT,
  memory_bytes BIGINT, reliability_millis BIGINT, benchmark_version TEXT NOT NULL, artifact_id TEXT NOT NULL,
  observed_at_unix_millis BIGINT NOT NULL, recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (binding_revision > 0), CHECK (model_revision > 0),
  CHECK (context_units IS NULL OR context_units >= 0), CHECK (generation_tps_millis IS NULL OR generation_tps_millis >= 0),
  CHECK (prompt_tps_millis IS NULL OR prompt_tps_millis >= 0), CHECK (ttft_micros IS NULL OR ttft_micros >= 0),
  CHECK (latency_micros IS NULL OR latency_micros >= 0), CHECK (memory_bytes IS NULL OR memory_bytes >= 0),
  CHECK (reliability_millis IS NULL OR reliability_millis >= 0), CHECK (observed_at_unix_millis > 0)
);
CREATE TABLE IF NOT EXISTS capability.capability_descriptors (
  capability_id TEXT PRIMARY KEY,
  capability_kind TEXT NOT NULL,
  display_name TEXT NOT NULL,
  description TEXT NOT NULL,
  revision BIGINT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (capability_id <> ''),
  CHECK (capability_kind <> ''),
  CHECK (display_name <> ''),
  CHECK (revision > 0),
  CHECK (recorded_at_unix_millis > 0)
);
CREATE TABLE IF NOT EXISTS capability.capability_implementations (
  implementation_id TEXT NOT NULL,
  capability_id TEXT NOT NULL,
  capability_kind TEXT NOT NULL,
  owner_principal_id TEXT NOT NULL,
  revision BIGINT NOT NULL,
  implementation_kind TEXT NOT NULL,
  target_ref TEXT NOT NULL,
  credential_ref TEXT NOT NULL DEFAULT '',
  qualification TEXT NOT NULL,
  activation TEXT NOT NULL,
  health TEXT NOT NULL,
  authority_policy_ref TEXT NOT NULL,
  data_constraints TEXT[] NOT NULL,
  cost_score_millis BIGINT,
  latency_score_millis BIGINT,
  reliability_millis BIGINT,
  observed_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (implementation_id, revision),
  CHECK (implementation_id <> ''),
  CHECK (capability_id <> ''),
  CHECK (capability_kind <> ''),
  CHECK (owner_principal_id <> ''),
  CHECK (revision > 0),
  CHECK (implementation_kind IN ('INTERNAL_FUNCTION','COGNITIVE_BINDING','EXTERNAL_ADAPTER','AGENT')),
  CHECK (target_ref <> ''),
  CHECK (qualification IN ('DISCOVERED','PROBING','EVALUATING','VALIDATED','REJECTED')),
  CHECK (activation IN ('INACTIVE','CANARY','ACTIVE','DEPRECATED','RETIRED')),
  CHECK (health IN ('UNKNOWN','AVAILABLE','DEGRADED','UNAVAILABLE')),
  CHECK (authority_policy_ref <> ''),
  CHECK (cardinality(data_constraints) > 0),
  CHECK (cost_score_millis IS NULL OR cost_score_millis >= 0),
  CHECK (latency_score_millis IS NULL OR latency_score_millis >= 0),
  CHECK (reliability_millis IS NULL OR reliability_millis >= 0),
  CHECK (observed_at_unix_millis > 0)
);
CREATE TABLE IF NOT EXISTS capability.capability_implementation_heads (
  implementation_id TEXT PRIMARY KEY,
  revision BIGINT NOT NULL,
  capability_kind TEXT NOT NULL,
  owner_principal_id TEXT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (revision > 0)
);
CREATE TABLE IF NOT EXISTS capability.capability_gaps (
  gap_id TEXT PRIMARY KEY,
  requirement_id TEXT NOT NULL,
  capability_kind TEXT NOT NULL,
  capability_id TEXT NOT NULL DEFAULT '',
  owner_principal_id TEXT NOT NULL,
  original_intent_id TEXT NOT NULL,
  source_utterance_id TEXT NOT NULL,
  reason TEXT NOT NULL,
  detail TEXT NOT NULL,
  strategies TEXT[] NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (requirement_id <> ''),
  CHECK (capability_kind <> ''),
  CHECK (owner_principal_id <> ''),
  CHECK (original_intent_id <> ''),
  CHECK (source_utterance_id <> ''),
  CHECK (reason IN ('NO_IMPLEMENTATION','UNHEALTHY','INACTIVE','UNQUALIFIED','UNAUTHORIZED','INCOMPATIBLE','UNKNOWN')),
  CHECK (detail <> ''),
  CHECK (cardinality(strategies) > 0),
  CHECK (recorded_at_unix_millis > 0)
);
CREATE TABLE IF NOT EXISTS capability.capability_usage_observations (
  observation_id TEXT PRIMARY KEY,
  implementation_id TEXT NOT NULL,
  implementation_revision BIGINT NOT NULL,
  capability_id TEXT NOT NULL,
  capability_kind TEXT NOT NULL,
  owner_principal_id TEXT NOT NULL,
  original_intent_id TEXT NOT NULL,
  source_utterance_id TEXT NOT NULL,
  outcome TEXT NOT NULL,
  outcome_ref TEXT NOT NULL DEFAULT '',
  latency_millis BIGINT,
  observed_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (implementation_id <> ''),
  CHECK (implementation_revision > 0),
  CHECK (capability_id <> ''),
  CHECK (capability_kind <> ''),
  CHECK (owner_principal_id <> ''),
  CHECK (original_intent_id <> ''),
  CHECK (source_utterance_id <> ''),
  CHECK (outcome IN ('SUCCEEDED','FAILED','SKIPPED','UNKNOWN')),
  CHECK (latency_millis IS NULL OR latency_millis >= 0),
  CHECK (observed_at_unix_millis > 0)
);
INSERT INTO capability.capability_descriptors(capability_id, capability_kind, display_name, description, revision, recorded_at_unix_millis)
VALUES
  ('schedule-reminder', 'SCHEDULE_REMINDER', 'Reminder scheduling', 'Create a Continuity-owned prospective reminder item from an authenticated human directive.', 1, 1),
  ('creator-advisory', 'COGNITIVE_REASONING', 'Creator advisory cognition', 'Reasoning capability resolved through the specialized Cognitive Registry and Gateway.', 1, 1)
ON CONFLICT (capability_id) DO NOTHING;
INSERT INTO capability.capability_implementations(
  implementation_id, capability_id, capability_kind, owner_principal_id, revision, implementation_kind, target_ref,
  credential_ref, qualification, activation, health, authority_policy_ref, data_constraints,
  cost_score_millis, latency_score_millis, reliability_millis, observed_at_unix_millis)
VALUES (
  'internal-reminder-scheduler-v1', 'schedule-reminder', 'SCHEDULE_REMINDER', 'domovoy-creator-principal',
  1, 'INTERNAL_FUNCTION', 'continuity://prospective-reminder-v1', '', 'VALIDATED', 'ACTIVE', 'AVAILABLE',
  'creator-directive-v1', ARRAY['LOCAL_ONLY','NO_CREDENTIAL_FORWARD'], 0, 10, 950, 1
)
ON CONFLICT DO NOTHING;
INSERT INTO capability.capability_implementation_heads(implementation_id, revision, capability_kind, owner_principal_id)
VALUES ('internal-reminder-scheduler-v1', 1, 'SCHEDULE_REMINDER', 'domovoy-creator-principal')
ON CONFLICT (implementation_id) DO NOTHING;
INSERT INTO capability.notification_bindings (
  binding_id, capability_id, owner_principal_id, revision, state, https_origin,
  delivery_path, receipt_path, protocol, secret_ref, verified_at_unix_millis, health
)
VALUES (
  'domovoy-notification-binding', 'prospective-notification-delivery', 'domovoy-creator-principal',
  2, 'ACTIVE', 'https://assistant.cin.su', '/delivery/v1/deliveries', '/delivery/v1/receipts',
  'IDEMPOTENT_RECEIPT_V1', 'domovoy-notification-write-v1', 1, 'AVAILABLE'
)
ON CONFLICT DO NOTHING;
INSERT INTO capability.notification_binding_heads(capability_id, owner_principal_id, binding_id, revision)
VALUES ('prospective-notification-delivery', 'domovoy-creator-principal', 'domovoy-notification-binding', 2)
ON CONFLICT (capability_id, owner_principal_id) DO UPDATE SET binding_id=EXCLUDED.binding_id, revision=EXCLUDED.revision
WHERE capability.notification_binding_heads.revision < EXCLUDED.revision;
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,
  source_intent_id TEXT NOT NULL DEFAULT '',
  source_utterance_id TEXT NOT NULL DEFAULT '',
  summary TEXT NOT NULL DEFAULT '',
  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)
);
CREATE TABLE IF NOT EXISTS continuity.attention_items (
  attention_item_id TEXT PRIMARY KEY,
  owner_principal_id TEXT NOT NULL,
  scope_id TEXT NOT NULL,
  source_kind TEXT NOT NULL,
  source_prospective_item_id TEXT NOT NULL,
  level TEXT NOT NULL,
  state TEXT NOT NULL,
  summary TEXT NOT NULL,
  due_at_unix_millis BIGINT NOT NULL,
  created_at_unix_millis BIGINT NOT NULL,
  delivered_at_unix_millis BIGINT NOT NULL DEFAULT 0,
  action_intent_id TEXT NOT NULL DEFAULT '',
  authorized_action_id TEXT NOT NULL DEFAULT '',
  action_attempt_id TEXT NOT NULL DEFAULT '',
  delivery_outcome TEXT NOT NULL DEFAULT '',
  response_id TEXT NOT NULL DEFAULT '',
  response_utterance_id TEXT NOT NULL DEFAULT '',
  response_operation TEXT NOT NULL DEFAULT '',
  response_outcome TEXT NOT NULL DEFAULT '',
  responded_at_unix_millis BIGINT NOT NULL DEFAULT 0,
  revision BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (source_kind, source_prospective_item_id),
  CHECK (source_kind IN ('PROSPECTIVE_REMINDER')),
  CHECK (level IN ('URGENT','ACTION_REQUIRED','DUE','IMPORTANT_CHANGE','FYI','BACKGROUND')),
  CHECK (state IN ('PENDING','DELIVERING','DELIVERED','RESOLVED','SUPPRESSED','EXPIRED','FAILED')),
  CHECK (summary <> ''),
  CHECK (due_at_unix_millis > 0),
  CHECK (created_at_unix_millis > 0),
  CHECK (revision > 0),
  CHECK (state <> 'DELIVERED' OR (delivered_at_unix_millis > 0 AND action_intent_id <> '' AND authorized_action_id <> '' AND action_attempt_id <> '' AND delivery_outcome <> '')),
  CHECK (state <> 'RESOLVED' OR (
    response_id <> '' AND response_utterance_id <> '' AND
    response_operation IN ('ACKNOWLEDGE','COMPLETE','SNOOZE','CANCEL','DISMISS','CONTINUE','REJECT') AND
    response_outcome IN ('USER_REPORTED_COMPLETE','SNOOZED','CANCELLED','DISMISSED','NOOP') AND
    responded_at_unix_millis > 0
  ))
);
CREATE TABLE IF NOT EXISTS continuity.human_responses (
  human_response_id TEXT PRIMARY KEY,
  owner_principal_id TEXT NOT NULL,
  scope_id TEXT NOT NULL,
  source_utterance_id TEXT NOT NULL,
  source_intent_id TEXT NOT NULL,
  operation TEXT NOT NULL,
  target_kind TEXT NOT NULL,
  target_attention_id TEXT NOT NULL DEFAULT '',
  target_prospective_item_id TEXT NOT NULL DEFAULT '',
  target_external_ref TEXT NOT NULL DEFAULT '',
  resolution_state TEXT NOT NULL,
  outcome TEXT NOT NULL,
  clarification TEXT NOT NULL DEFAULT '',
  snooze_until_unix_millis BIGINT NOT NULL DEFAULT 0,
  user_reported_not_verified BOOLEAN NOT NULL DEFAULT FALSE,
  recorded_at_unix_millis BIGINT NOT NULL,
  revision BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (owner_principal_id <> ''),
  CHECK (scope_id <> ''),
  CHECK (source_utterance_id <> ''),
  CHECK (source_intent_id <> ''),
  CHECK (operation IN ('ACKNOWLEDGE','COMPLETE','SNOOZE','CANCEL','DISMISS','CONTINUE','REJECT','UNKNOWN')),
  CHECK (target_kind IN ('ATTENTION_ITEM','PROSPECTIVE_ITEM','WORK_TASK','CAPABILITY_GAP','INTERACTION_CONTEXT','UNKNOWN')),
  CHECK (resolution_state IN ('RESOLVED','AMBIGUOUS','NO_TARGET','NOOP')),
  CHECK (outcome IN ('USER_REPORTED_COMPLETE','SNOOZED','CANCELLED','DISMISSED','CLARIFICATION_NEEDED','NOOP')),
  CHECK (recorded_at_unix_millis > 0),
  CHECK (revision > 0),
  CHECK (operation <> 'SNOOZE' OR snooze_until_unix_millis > recorded_at_unix_millis),
  CHECK (resolution_state <> 'RESOLVED' OR target_kind <> 'UNKNOWN')
);
RESET ROLE;

SET ROLE interaction_role;
CREATE TABLE IF NOT EXISTS interaction.conversations (
  conversation_id TEXT PRIMARY KEY,
  actor_id TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  channel TEXT NOT NULL,
  state TEXT NOT NULL,
  created_at_unix_millis BIGINT NOT NULL,
  updated_at_unix_millis BIGINT NOT NULL,
  CHECK (channel = 'WEB_CHAT'),
  CHECK (state IN ('OPEN','ARCHIVED'))
);
CREATE TABLE IF NOT EXISTS interaction.utterances (
  utterance_id TEXT PRIMARY KEY,
  conversation_id TEXT NOT NULL,
  actor_id TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  channel TEXT NOT NULL,
  received_at_unix_millis BIGINT NOT NULL,
  correlation_id TEXT NOT NULL,
  content TEXT NOT NULL,
  content_ref TEXT NOT NULL,
  speech_act TEXT NOT NULL,
  interpretation_state TEXT NOT NULL,
  corrects_utterance_id TEXT NOT NULL DEFAULT '',
  CHECK (channel = 'WEB_CHAT'),
  CHECK (content <> ''),
  CHECK (content_ref <> ''),
  CHECK (speech_act IN ('DIRECTIVE','ASSERTION','DECISION','PREFERENCE','QUESTION','HYPOTHESIS','CORRECTION','REQUEST_TO_INVESTIGATE','CONFIRMATION','REJECTION','CANCELLATION','CONVERSATIONAL','UNKNOWN')),
  CHECK (interpretation_state IN ('RECORDED','INTERPRETED'))
);
CREATE TABLE IF NOT EXISTS interaction.original_human_intents (
  intent_id TEXT PRIMARY KEY,
  utterance_id TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  text TEXT NOT NULL,
  CHECK (text <> '')
);
CREATE TABLE IF NOT EXISTS interaction.relations (
  relation_id TEXT PRIMARY KEY,
  from_utterance_id TEXT NOT NULL,
  to_utterance_id TEXT NOT NULL,
  kind TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (kind IN ('CORRECTS','RESPONDS_TO','REQUESTS_COGNITION','COGNITION_FALLBACK','SUPERSEDES','CLARIFIES'))
);
CREATE TABLE IF NOT EXISTS interaction.utterance_interpretations (
  interpretation_id TEXT PRIMARY KEY,
  utterance_id TEXT NOT NULL,
  speech_act TEXT NOT NULL,
  confidence_millis BIGINT NOT NULL,
  source TEXT NOT NULL,
  assertion_verification TEXT NOT NULL,
  requested_outcome TEXT NOT NULL DEFAULT '',
  clarification TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (speech_act IN ('DIRECTIVE','ASSERTION','DECISION','PREFERENCE','QUESTION','HYPOTHESIS','CORRECTION','REQUEST_TO_INVESTIGATE','CONFIRMATION','REJECTION','CANCELLATION','CONVERSATIONAL','UNKNOWN')),
  CHECK (confidence_millis BETWEEN 0 AND 1000),
  CHECK (assertion_verification IN ('UNVERIFIED','VERIFIED','REJECTED','NOT_APPLICABLE')),
  CHECK (clarification IN ('NOT_REQUIRED','REQUIRED'))
);
CREATE TABLE IF NOT EXISTS interaction.project_references (
  project_reference_id TEXT PRIMARY KEY,
  utterance_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  project_display_name TEXT NOT NULL,
  role TEXT NOT NULL,
  provenance TEXT NOT NULL,
  confidence_millis BIGINT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (role IN ('PRIMARY','RELATED','REFERENCE_SOURCE')),
  CHECK (provenance IN ('EXPLICIT','INHERITED_FOCUS','ENTITY_REFERENCE','TASK_REFERENCE','REPOSITORY_REFERENCE','INFERRED','USER_CONFIRMED','AMBIGUOUS')),
  CHECK (confidence_millis BETWEEN 0 AND 1000)
);
RESET ROLE;
COMMIT;