-- Minimal Supabase schema for Yeomen demo

create extension if not exists pgcrypto;

create table if not exists users (
  id uuid primary key default gen_random_uuid(),
  email text,
  created_at timestamptz default now()
);

create table if not exists api_keys (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references users(id),
  name text,
  key_hash text,
  is_primary boolean default false,
  created_at timestamptz default now(),
  revoked_at timestamptz
);

create table if not exists skills (
  id text primary key,
  type text,
  name text,
  description text,
  capabilities jsonb,
  chains jsonb,
  inputs_schema jsonb,
  outputs_schema jsonb,
  risk_level text,
  proposes_only boolean,
  runner jsonb,
  policy jsonb,
  version text,
  source_path text,
  created_at timestamptz default now()
);

create table if not exists intents (
  id text primary key,
  intent text,
  context jsonb,
  webhook_url text,
  plan_id text,
  proposal_id text,
  approval_required boolean,
  skill_id text,
  run_id text,
  llm_plan jsonb,
  llm_summary text,
  created_at timestamptz default now()
);

create table if not exists plans (
  id text primary key,
  intent_id text,
  steps jsonb,
  risk_notes jsonb,
  cost_summary text,
  skill_id text,
  created_at timestamptz default now()
);

create table if not exists proposals (
  id text primary key,
  intent_id text,
  summary text,
  transactions jsonb,
  risk_notes jsonb,
  expires_at timestamptz,
  created_at timestamptz default now()
);

create table if not exists runs (
  id text primary key,
  intent_id text,
  schedule_id text,
  kind text,
  status text,
  sandbox_id text,
  execution_id text,
  lifecycle text,
  created_at timestamptz default now(),
  updated_at timestamptz
);

alter table runs add column if not exists sandbox_id text;
alter table runs add column if not exists execution_id text;
alter table runs add column if not exists lifecycle text;

create table if not exists run_events (
  id text primary key,
  run_id text,
  type text,
  payload jsonb,
  created_at timestamptz default now()
);

create table if not exists approvals (
  id text primary key,
  intent_id text,
  status text,
  note text,
  created_at timestamptz default now()
);

create table if not exists executions (
  id text primary key,
  intent_id text,
  status text,
  approval_note text,
  approval_id text,
  tx_hashes jsonb,
  llm_summary text,
  executed_at timestamptz,
  created_at timestamptz default now()
);

alter table intents add column if not exists llm_plan jsonb;
alter table intents add column if not exists llm_summary text;
alter table executions add column if not exists llm_summary text;
alter table executions add column if not exists approval_id text;
alter table api_keys add column if not exists is_primary boolean default false;

create table if not exists schedules (
  id text primary key,
  name text,
  cron text,
  skill_id text,
  input jsonb,
  policy_profile_id text,
  enabled boolean,
  created_at timestamptz default now(),
  updated_at timestamptz
);

create table if not exists audit_logs (
  id text primary key,
  level text,
  source text,
  message text,
  payload jsonb,
  created_at timestamptz default now()
);

create table if not exists request_logs (
  id text primary key,
  method text,
  path text,
  status integer,
  duration_ms integer,
  ip text,
  created_at timestamptz default now()
);
