-- Decisions AI · public schema snapshot
-- Mirror of the live `public` schema in the Supabase project
-- lydtdudifolcdzunlvtu. Regenerate via `npm run gen:schema`.
--
-- Notes:
--   • Every table has RLS enabled with a `demo_all` policy of `(true, true)`
--     for both `anon` and `authenticated`. v1 is open by design.
--   • The publication `supabase_realtime` includes `decisions`, `runs`,
--     `run_steps`, and `chat_messages` so Realtime broadcasts row updates.
--   • `runs.heartbeat_at` and the `claim_pipeline_run()` advisory-lock RPC
--     enable abandoned-run detection and resume from `pg_cron`.

-- ============================================================================
-- pipelines
-- ============================================================================

create table public.pipelines (
  id              uuid primary key default gen_random_uuid(),
  name            text        not null,
  name_ar         text,
  description     text,
  description_ar  text,
  is_default      boolean     default false,
  created_at      timestamptz default now(),
  updated_at      timestamptz default now()
);

-- ============================================================================
-- pipeline_nodes
-- ============================================================================

create table public.pipeline_nodes (
  id              uuid primary key default gen_random_uuid(),
  pipeline_id     uuid not null references public.pipelines(id) on delete cascade,
  role            text not null, -- research | advocate_for | advocate_against | judge | counter | chief_of_staff | custom
  label           text not null,
  label_ar        text,
  description     text,
  description_ar  text,
  system_prompt   text not null,
  model           text default 'claude-opus-4-7',
  effort          text default 'xhigh',  -- low | medium | high | xhigh
  thinking        boolean default true,
  tools           jsonb default '[]'::jsonb,        -- string[] of tool names
  position        jsonb default '{"x": 0, "y": 0}'::jsonb,
  config          jsonb default '{}'::jsonb,
  sequence        integer default 0,
  created_at      timestamptz default now()
);

create index pipeline_nodes_pipeline_id_idx on public.pipeline_nodes (pipeline_id);

-- ============================================================================
-- pipeline_edges
-- ============================================================================

create table public.pipeline_edges (
  id              uuid primary key default gen_random_uuid(),
  pipeline_id     uuid not null references public.pipelines(id) on delete cascade,
  source_node_id  uuid not null references public.pipeline_nodes(id) on delete cascade,
  target_node_id  uuid not null references public.pipeline_nodes(id) on delete cascade,
  label           text,
  condition       jsonb default '{}'::jsonb,
  created_at      timestamptz default now()
);

create index pipeline_edges_pipeline_id_idx on public.pipeline_edges (pipeline_id);

-- ============================================================================
-- decisions  ← top-level user-facing record
-- ============================================================================

create table public.decisions (
  id                   uuid primary key default gen_random_uuid(),
  title                text not null,
  title_ar             text,
  question             text not null,
  question_ar          text,
  context              text,
  context_ar           text,
  scope_frame          text,                -- 150-220 word structured frame, auto-generated
  scope_frame_ar       text,
  pipeline_id          uuid references public.pipelines(id),
  status               text default 'draft', -- draft | queued | running | complete | failed | cancelled
  current_node_id      uuid,                -- reserved
  final_markdown       text,
  final_markdown_ar    text,
  language             text default 'en',
  created_by           text,                -- reserved (RLS flip)
  heos_user_id         text,                -- HEOS-platform user id; set when the decision originates from HEOS
  suggested_followups  jsonb,               -- string[] of 3-5 follow-up prompts
  created_at           timestamptz default now(),
  updated_at           timestamptz default now(),
  completed_at         timestamptz
);

create index decisions_status_idx on public.decisions (status);

-- Partial index: most rows have heos_user_id null, so only index the
-- HEOS-tagged subset. Powers `decision_overview?heos_user_id=eq.<id>`.
create index decisions_heos_user_id_idx
  on public.decisions (heos_user_id)
  where heos_user_id is not null;

-- ============================================================================
-- documents
-- ============================================================================

create table public.documents (
  id              uuid primary key default gen_random_uuid(),
  decision_id     uuid references public.decisions(id) on delete cascade,
  file_name       text not null,
  file_path       text not null,           -- path inside the `documents` Storage bucket
  mime_type       text,
  size_bytes      bigint,
  extracted_text  text,
  summary         text,
  created_at      timestamptz default now()
);

create index documents_decision_id_idx on public.documents (decision_id);

-- ============================================================================
-- runs  ← one execution of a pipeline against a decision
-- ============================================================================

create table public.runs (
  id            uuid primary key default gen_random_uuid(),
  decision_id   uuid not null references public.decisions(id) on delete cascade,
  pipeline_id   uuid references public.pipelines(id),
  status        text default 'running', -- queued | running | complete | failed | cancelled
  started_at    timestamptz default now(),
  completed_at  timestamptz,
  error         text,
  heartbeat_at  timestamptz,             -- refreshed every ~5s by the active worker
  worker_id     text                     -- short opaque id of the worker holding the advisory lock
);

create index runs_decision_id_idx on public.runs (decision_id);
create index idx_runs_running on public.runs (status, heartbeat_at) where (status = 'running');
create index idx_runs_active_worker on public.runs (decision_id, worker_id, heartbeat_at) where (status = 'running');
create unique index runs_one_running_per_decision on public.runs (decision_id) where (status = 'running');

-- ============================================================================
-- run_steps  ← per-token stream lives here
-- ============================================================================

create table public.run_steps (
  id                 uuid primary key default gen_random_uuid(),
  run_id             uuid not null references public.runs(id) on delete cascade,
  node_id            uuid not null references public.pipeline_nodes(id),
  role               text,
  label              text,
  sequence           integer,
  status             text default 'pending', -- pending | running | complete | failed
  input              jsonb,                  -- reserved
  output_text        text,                   -- grows monotonically during streaming
  output_text_ar     text,
  thinking_summary   text,                   -- grows monotonically during streaming
  tokens_input       integer,
  tokens_output      integer,
  tools_used         jsonb,                  -- Record<string, number>
  sources            jsonb default '[]'::jsonb, -- WebSource[]
  started_at         timestamptz,
  completed_at       timestamptz,
  last_streamed_at   timestamptz             -- refreshed on every partial flush
);

create index run_steps_run_id_idx on public.run_steps (run_id);
create unique index run_steps_run_node_unique on public.run_steps (run_id, node_id);

-- ============================================================================
-- chat_messages  ← refinement chat for a finished decision
-- ============================================================================

create table public.chat_messages (
  id           uuid primary key default gen_random_uuid(),
  decision_id  uuid not null references public.decisions(id) on delete cascade,
  role         text not null,            -- user | assistant
  content      text not null,
  content_ar   text,
  created_at   timestamptz default now()
);

create index chat_messages_decision_id_idx on public.chat_messages (decision_id);

-- ============================================================================
-- translation_jobs  ← internal queue for the EN↔AR background translator
-- ============================================================================

create table public.translation_jobs (
  id            uuid primary key default gen_random_uuid(),
  source_table  text not null,
  source_id     uuid not null,
  source_field  text not null,
  target_field  text not null,
  source_lang   text default 'en',
  target_lang   text default 'ar',
  status        text default 'pending', -- pending | done | failed
  error         text,
  created_at    timestamptz default now(),
  completed_at  timestamptz
);

create index translation_jobs_status_idx on public.translation_jobs (status);

-- ============================================================================
-- Row-level security (v1 posture: open)
-- ============================================================================

alter table public.pipelines        enable row level security;
alter table public.pipeline_nodes   enable row level security;
alter table public.pipeline_edges   enable row level security;
alter table public.decisions        enable row level security;
alter table public.documents        enable row level security;
alter table public.runs             enable row level security;
alter table public.run_steps        enable row level security;
alter table public.chat_messages    enable row level security;
alter table public.translation_jobs enable row level security;

-- Each table carries an identical policy (`demo_all`) so the publishable
-- anon key can read and write every row. Replace with `auth.uid()`-based
-- policies when you flip to per-user auth; the SDK requires no changes.
create policy demo_all on public.pipelines        as permissive for all to anon, authenticated using (true) with check (true);
create policy demo_all on public.pipeline_nodes   as permissive for all to anon, authenticated using (true) with check (true);
create policy demo_all on public.pipeline_edges   as permissive for all to anon, authenticated using (true) with check (true);
create policy demo_all on public.decisions        as permissive for all to anon, authenticated using (true) with check (true);
create policy demo_all on public.documents        as permissive for all to anon, authenticated using (true) with check (true);
create policy demo_all on public.runs             as permissive for all to anon, authenticated using (true) with check (true);
create policy demo_all on public.run_steps        as permissive for all to anon, authenticated using (true) with check (true);
create policy demo_all on public.chat_messages    as permissive for all to anon, authenticated using (true) with check (true);
create policy demo_all on public.translation_jobs as permissive for all to anon, authenticated using (true) with check (true);

-- ============================================================================
-- Realtime publication
-- ============================================================================

alter publication supabase_realtime add table public.decisions;
alter publication supabase_realtime add table public.runs;
alter publication supabase_realtime add table public.run_steps;
alter publication supabase_realtime add table public.chat_messages;

-- ============================================================================
-- RPC: claim_pipeline_run(decision_id, worker_id) → text | null
-- ============================================================================
--
-- Called by the run-pipeline worker before doing any work. Atomically takes
-- ownership of the run by setting `worker_id` and refreshing `heartbeat_at`,
-- using a Postgres advisory lock keyed on `decision_id`. Returns the
-- existing `worker_id` if the run is already claimed by another live worker,
-- or NULL if the caller successfully took ownership. Pseudocode:
--
--   if no run exists for decision_id:
--     insert one with status='running', heartbeat_at=now(), worker_id=$2
--     return null  -- claimed
--   if run.worker_id is not null and heartbeat_at > now() - interval '25 seconds':
--     return run.worker_id  -- another worker owns it
--   update runs set worker_id=$2, heartbeat_at=now() where id = run.id
--   return null  -- claimed (resume)

-- ============================================================================
-- pg_cron jobs
-- ============================================================================
--
-- resume_stale_runs:  every minute, re-invokes /functions/v1/run-pipeline
--                     for any decision whose latest run is `running` with a
--                     heartbeat_at older than 25 seconds.
-- translate:          every minute, drains `translation_jobs` by invoking
--                     /functions/v1/translate.

-- ============================================================================
-- View: decision_overview
-- ============================================================================
--
-- Read-only convenience view: one row per decision joined with the latest
-- run summary and step counts. Lets list pages avoid an N+1 fan-out.

create or replace view public.decision_overview as
with latest_run as (
  select distinct on (r.decision_id)
    r.decision_id,
    r.id           as run_id,
    r.status       as run_status,
    r.started_at   as run_started_at,
    r.completed_at as run_completed_at,
    r.error        as run_error,
    r.heartbeat_at as run_heartbeat_at
  from public.runs r
  order by r.decision_id, r.started_at desc
),
step_stats as (
  select
    rs.run_id,
    count(*)                                      as steps_total,
    count(*) filter (where rs.status = 'complete') as steps_complete,
    count(*) filter (where rs.status = 'running')  as steps_running,
    count(*) filter (where rs.status = 'failed')   as steps_failed
  from public.run_steps rs
  group by rs.run_id
)
select
  d.id,
  d.title,
  d.title_ar,
  d.question,
  d.question_ar,
  d.context,
  d.context_ar,
  d.scope_frame,
  d.scope_frame_ar,
  d.pipeline_id,
  d.status,
  d.language,
  d.created_by,
  d.heos_user_id,
  d.suggested_followups,
  d.created_at,
  d.updated_at,
  d.completed_at,
  d.final_markdown,
  d.final_markdown_ar,
  lr.run_id,
  lr.run_status,
  lr.run_started_at,
  lr.run_completed_at,
  lr.run_error,
  lr.run_heartbeat_at,
  coalesce(ss.steps_total,    0) as steps_total,
  coalesce(ss.steps_complete, 0) as steps_complete,
  coalesce(ss.steps_running,  0) as steps_running,
  coalesce(ss.steps_failed,   0) as steps_failed
from public.decisions d
left join latest_run lr on lr.decision_id = d.id
left join step_stats ss on ss.run_id      = lr.run_id;

grant select on public.decision_overview to anon, authenticated;

-- ============================================================================
-- RPC: create_decision_and_run(title, question, context, pipeline_id,
--                              language, heos_user_id)
-- ============================================================================
--
-- One round-trip helper for non-JS clients. Inserts a `decisions` row with
-- status='queued' and returns its id. The caller still needs to invoke
-- /functions/v1/run-pipeline to start the worker; the JS SDK does this
-- inside `createDecision()`.
--
-- Pass `p_heos_user_id` whenever the decision originates from the HEOS
-- platform; it's persisted to `decisions.heos_user_id` so the row can be
-- listed/filtered for that user later. Empty string is normalised to NULL.

create or replace function public.create_decision_and_run(
  p_title         text,
  p_question      text,
  p_context       text default null,
  p_pipeline_id   uuid default null,
  p_language      text default 'en',
  p_heos_user_id  text default null
) returns uuid
language plpgsql
security invoker
as $$
declare
  v_pipeline_id uuid := p_pipeline_id;
  v_id          uuid;
begin
  if v_pipeline_id is null then
    select id into v_pipeline_id
      from public.pipelines
     where is_default = true
     order by created_at desc
     limit 1;
  end if;

  insert into public.decisions (
    title, question, context, pipeline_id, language, status, heos_user_id
  )
  values (
    p_title, p_question, p_context, v_pipeline_id, coalesce(p_language, 'en'),
    'queued', nullif(p_heos_user_id, '')
  )
  returning id into v_id;

  return v_id;
end;
$$;

grant execute on function public.create_decision_and_run(text, text, text, uuid, text, text)
  to anon, authenticated;
