-- 053_clan_event_store.sql
-- Minimal CLAN event store: consent_events (immutable), artifacts (versioned),
-- outbox (pending/done), state_transitions (immutable)

begin;

create schema if not exists clan;

-- 1) Immutable ConsentEvents
create table if not exists clan.clan_consent_events (
  consent_event_id text primary key,
  payload jsonb not null,
  decision_type text not null,
  target_artifact_ids text[] not null,
  request_id text not null,
  created_ts timestamptz not null default now(),
  constraint chk_consent_decision_type check (decision_type in ('approve', 'reject', 'revoke'))
);

create index if not exists idx_clan_consent_events_created_ts
  on clan.clan_consent_events(created_ts desc);

-- 2) Versioned Artifacts (CAS by version)
create table if not exists clan.clan_artifacts (
  artifact_id text primary key,
  artifact_type text not null,
  status text not null,
  visibility_level text not null,
  payload jsonb not null default '{}'::jsonb,
  provenance jsonb not null default '[]'::jsonb,
  version bigint not null default 1,
  created_ts timestamptz not null default now(),
  updated_ts timestamptz not null default now(),
  constraint chk_artifact_visibility check (visibility_level in ('public', 'interclan', 'incircle', 'soulsafe', 'sacred')),
  constraint chk_artifact_status check (
    status in (
      'draft',
      'waiting_for_consent',
      'needs_confirmation',
      'approved_for_execution',
      'confirmed',
      'rejected',
      'revoked'
    )
  )
);

create index if not exists idx_clan_artifacts_status
  on clan.clan_artifacts(status);

create index if not exists idx_clan_artifacts_updated_ts
  on clan.clan_artifacts(updated_ts desc);

-- 3) Outbox apply_consent (idempotent by outbox_id)
create table if not exists clan.clan_outbox (
  outbox_id text primary key, -- outbox_{consent_event_id}
  event_type text not null,   -- apply_consent
  consent_event_id text not null references clan.clan_consent_events(consent_event_id) on delete restrict,
  target_artifact_ids text[] not null,
  request_id text not null,
  status text not null default 'pending', -- pending|done
  attempts int not null default 0,
  last_error text,
  created_ts timestamptz not null default now(),
  updated_ts timestamptz not null default now(),
  constraint chk_outbox_status check (status in ('pending', 'done')),
  constraint chk_outbox_event_type check (event_type in ('apply_consent'))
);

create index if not exists idx_clan_outbox_pending
  on clan.clan_outbox(status, created_ts);

-- 4) Immutable Transition Log
create table if not exists clan.clan_state_transitions (
  transition_id text primary key,
  ts timestamptz not null default now(),
  artifact_id text not null references clan.clan_artifacts(artifact_id) on delete restrict,
  artifact_type text not null,
  from_status text not null,
  to_status text not null,
  op text not null,
  consent_event_id text not null references clan.clan_consent_events(consent_event_id) on delete restrict,
  decision_type text not null,
  request_id text not null,
  visibility_level text not null,
  versions jsonb not null default '{}'::jsonb,
  constraint chk_transition_decision_type check (decision_type in ('approve', 'reject', 'revoke')),
  constraint chk_transition_visibility check (visibility_level in ('public', 'interclan', 'incircle', 'soulsafe', 'sacred'))
);

create index if not exists idx_clan_transitions_artifact
  on clan.clan_state_transitions(artifact_id, ts desc);

create index if not exists idx_clan_transitions_consent
  on clan.clan_state_transitions(consent_event_id, ts desc);

commit;