-- Wever Labs Supabase Runtime Deployment Pack v1
-- Apply in Supabase SQL editor or migration runner.
-- Order matters: base runtime records first, provider activation, intake dispatch, execution binding, then pilot records.

begin;

-- ==============================================
-- wever-runtime-data-binding-v1.sql
-- ==============================================

-- Wever Labs Runtime Data Binding v1
-- Supabase/Postgres migration scaffold for binding public agent-facing contracts to durable OS records.

create extension if not exists pgcrypto;

create table if not exists agent_onboarding_records (
  id uuid primary key default gen_random_uuid(),
  onboarding_id text unique not null,
  agent_id text,
  manifest_url text,
  trust_status text default 'pending_review',
  status_url text,
  quote_path_url text,
  run_contract_path_url text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists agent_trust_profiles (
  id uuid primary key default gen_random_uuid(),
  trust_profile_id text unique not null,
  agent_id text not null,
  trust_state text not null default 'pending_review',
  allowed_tools text[] not null default '{}',
  callback_url text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists agent_credential_envelopes (
  id uuid primary key default gen_random_uuid(),
  envelope_id text unique not null,
  agent_id text not null,
  trust_profile_id text,
  key_scope text[] not null default '{}',
  delivery_status text not null default 'queued',
  delivery_channel text,
  expires_at timestamptz,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists agent_quote_records (
  id uuid primary key default gen_random_uuid(),
  quote_id text unique not null,
  agent_id text,
  requested_tool text not null,
  workflow_type text,
  quoted_credits numeric,
  quote_state text not null default 'issued',
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists settlement_provider_routes (
  id uuid primary key default gen_random_uuid(),
  route_id text unique not null,
  provider text not null,
  agent_id text,
  quote_id text,
  settlement_id text,
  route_state text not null default 'created',
  confirmation_status text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists agent_credit_entitlements (
  id uuid primary key default gen_random_uuid(),
  credit_entitlement_id text unique not null,
  agent_id text,
  quote_id text,
  settlement_id text,
  credits_granted numeric not null default 0,
  credits_remaining numeric not null default 0,
  entitlement_state text not null default 'active',
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists agent_run_contracts (
  id uuid primary key default gen_random_uuid(),
  contract_id text unique not null,
  agent_id text,
  requested_tool text not null,
  workflow_type text,
  contract_state text not null default 'active',
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists agent_task_runs (
  id uuid primary key default gen_random_uuid(),
  task_token text unique not null,
  agent_id text,
  requested_tool text not null,
  workflow_type text,
  contract_id text,
  quote_id text,
  credit_entitlement_id text,
  canonical_state text not null default 'queued',
  evidence_score numeric,
  result_contract_id text,
  callback_id text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists agent_lifecycle_events (
  id uuid primary key default gen_random_uuid(),
  event_id text unique not null,
  task_token text not null,
  previous_state text,
  next_state text not null,
  source text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists agent_result_contracts (
  id uuid primary key default gen_random_uuid(),
  result_contract_id text unique not null,
  task_token text not null,
  contract_id text,
  result_state text not null default 'result_ready',
  machine_result jsonb not null default '{}'::jsonb,
  human_summary text,
  proof_objects jsonb not null default '{}'::jsonb,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists agent_settlement_receipts (
  id uuid primary key default gen_random_uuid(),
  settlement_receipt_id text unique not null,
  settlement_id text,
  provider text,
  agent_id text,
  quote_id text,
  credit_entitlement_id text,
  receipt_state text not null default 'issued',
  amount numeric,
  asset text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists agent_usage_receipts (
  id uuid primary key default gen_random_uuid(),
  usage_receipt_id text unique not null,
  task_token text not null,
  agent_id text,
  requested_tool text,
  credits_consumed numeric not null default 0,
  receipt_state text not null default 'issued',
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists agent_callback_deliveries (
  id uuid primary key default gen_random_uuid(),
  callback_id text unique not null,
  task_token text not null,
  agent_id text,
  callback_url text,
  delivery_state text not null default 'queued',
  attempts integer not null default 0,
  last_attempt_at timestamptz,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists agent_result_attestations (
  id uuid primary key default gen_random_uuid(),
  attestation_id text unique not null,
  task_token text not null,
  result_contract_id text,
  attestation_state text not null default 'issued',
  payload_hash text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists runtime_binding_events (
  id uuid primary key default gen_random_uuid(),
  event_id text unique not null,
  task_token text,
  record_id text,
  record_type text not null,
  event_type text not null,
  previous_state text,
  next_state text,
  source text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists idx_agent_task_runs_state on agent_task_runs(canonical_state);
create index if not exists idx_agent_task_runs_tool on agent_task_runs(requested_tool);
create index if not exists idx_agent_lifecycle_events_task on agent_lifecycle_events(task_token);
create index if not exists idx_agent_usage_receipts_task on agent_usage_receipts(task_token);
create index if not exists idx_agent_callback_deliveries_task on agent_callback_deliveries(task_token);
create index if not exists idx_runtime_binding_events_task on runtime_binding_events(task_token);



-- ==============================================
-- wever-provider-activation-binding-v1.sql
-- ==============================================

-- Wever Labs Provider Activation Binding v1
-- Provider confirmations become credit funding events, settlement receipts, and run eligibility records.

create table if not exists provider_webhook_verifications (
  id uuid primary key default gen_random_uuid(),
  verification_id text unique not null,
  provider_id text not null,
  event_id text not null,
  event_type text,
  signature_status text not null,
  idempotency_key text,
  verification_state text not null,
  provider_reference text,
  raw_event_hash text,
  normalized_payload jsonb default '{}'::jsonb,
  received_at timestamptz default now(),
  verified_at timestamptz default now(),
  created_at timestamptz default now()
);

create table if not exists provider_activation_bindings (
  id uuid primary key default gen_random_uuid(),
  activation_id text unique not null,
  provider_id text not null,
  route_id text not null,
  settlement_id text not null,
  confirmation_id text not null,
  credit_entitlement_id text not null,
  agent_id text,
  task_token text,
  run_contract_id text,
  runtime_record_id text,
  activation_state text not null,
  credits_funded numeric default 0,
  credit_account_id text,
  settlement_receipt_url text,
  result_contract_url text,
  callback_url text,
  provider_reference text,
  verification_id text,
  next_action text,
  payload jsonb default '{}'::jsonb,
  created_at timestamptz default now(),
  activated_at timestamptz default now()
);

create table if not exists credit_funding_events (
  id uuid primary key default gen_random_uuid(),
  funding_event_id text unique not null,
  credit_entitlement_id text not null,
  credit_account_id text not null,
  agent_id text,
  task_token text,
  source_provider text not null,
  settlement_id text,
  confirmation_id text,
  credits_funded numeric not null,
  credit_currency text default 'wever_os_credit',
  settlement_receipt_id text,
  settlement_receipt_url text,
  funding_state text not null default 'funded',
  created_at timestamptz default now()
);

create index if not exists idx_provider_activation_task_token on provider_activation_bindings(task_token);
create index if not exists idx_provider_activation_settlement_id on provider_activation_bindings(settlement_id);
create index if not exists idx_credit_funding_task_token on credit_funding_events(task_token);



-- ==============================================
-- wever-intake-work-order-dispatch-v1.sql
-- ==============================================

-- Wever Labs Intake to Work Order Dispatch v1
-- Creates durable dispatch records that bind client/agent intake to Work Orders,
-- runtime records, selected tools, queue state, and return contracts.

create table if not exists wever_intake_work_order_dispatches (
  id uuid primary key default gen_random_uuid(),
  dispatch_id text unique not null,
  intake_request_id text not null,
  work_order_id text not null,
  requested_tool text not null,
  runtime_record_id text,
  run_contract_id text,
  quote_id text,
  credit_entitlement_id text,
  provider_route_id text,
  queue_state text not null default 'accepted',
  return_contract jsonb not null default '{}'::jsonb,
  evidence_bindings jsonb not null default '[]'::jsonb,
  operator_review_required boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists wever_work_order_creation_events (
  id uuid primary key default gen_random_uuid(),
  event_id text unique not null,
  work_order_id text not null,
  intake_request_id text not null,
  requested_tool text not null,
  runtime_record_id text,
  task_token text,
  queue_state text not null,
  links jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists idx_wever_dispatch_intake on wever_intake_work_order_dispatches(intake_request_id);
create index if not exists idx_wever_dispatch_work_order on wever_intake_work_order_dispatches(work_order_id);
create index if not exists idx_wever_dispatch_tool on wever_intake_work_order_dispatches(requested_tool);
create index if not exists idx_wever_dispatch_queue_state on wever_intake_work_order_dispatches(queue_state);



-- ==============================================
-- wever-work-order-execution-binding-v1.sql
-- ==============================================

-- Wever Labs Work Order Execution Binding v1
-- Creates durable records for binding routed Work Orders to operating tool execution.

create table if not exists work_order_execution_bindings (
  id uuid primary key default gen_random_uuid(),
  binding_id text unique not null,
  work_order_id text not null,
  intake_id text,
  task_token text not null,
  tool text not null,
  execution_state text not null default 'bound',
  runtime_record_id text,
  run_contract_id text,
  credit_entitlement_id text,
  provider_confirmation_id text,
  queue_id text,
  result_contract_url text,
  settlement_receipt_url text,
  usage_receipt_url text,
  callback_url text,
  ledger_url text,
  attestation_url text,
  metadata jsonb default '{}'::jsonb,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

create table if not exists tool_execution_commands (
  id uuid primary key default gen_random_uuid(),
  command_id text unique not null,
  binding_id text not null references work_order_execution_bindings(binding_id),
  work_order_id text not null,
  task_token text not null,
  tool text not null,
  operation text not null,
  input_refs jsonb default '[]'::jsonb,
  execution_rules jsonb default '[]'::jsonb,
  return_contract jsonb default '{}'::jsonb,
  created_at timestamptz default now()
);

create table if not exists tool_execution_result_events (
  id uuid primary key default gen_random_uuid(),
  event_id text unique not null,
  binding_id text not null,
  work_order_id text not null,
  task_token text not null,
  tool text not null,
  event_type text not null,
  state_after_event text,
  result_contract_url text,
  receipt_url text,
  callback_status text,
  payload jsonb default '{}'::jsonb,
  occurred_at timestamptz default now()
);

create index if not exists idx_work_order_execution_bindings_task_token on work_order_execution_bindings(task_token);
create index if not exists idx_work_order_execution_bindings_work_order on work_order_execution_bindings(work_order_id);
create index if not exists idx_tool_execution_events_task_token on tool_execution_result_events(task_token);



-- ==============================================
-- wever-tokenops-live-pilot-flow-v1.sql
-- ==============================================

-- Wever Labs TokenOps Live Pilot Flow v1
-- Maps a funded TokenOps pilot run into durable OS runtime records.

create table if not exists public.tokenops_live_pilot_runs (
  id uuid primary key default gen_random_uuid(),
  pilot_id text unique not null,
  agent_id text,
  operating_tool text not null default 'tokenops',
  onboarding_id text,
  trust_profile_id text,
  credential_envelope_id text,
  quote_id text,
  provider_route text,
  provider_confirmation_id text,
  credit_entitlement_id text,
  run_contract_id text,
  task_token text,
  credit_state text not null default 'funded',
  run_state text not null default 'created',
  runtime_record jsonb not null default '{}'::jsonb,
  result_contract jsonb not null default '{}'::jsonb,
  settlement_receipt jsonb not null default '{}'::jsonb,
  usage_receipt jsonb not null default '{}'::jsonb,
  callback_state jsonb not null default '{}'::jsonb,
  ledger_reference jsonb not null default '{}'::jsonb,
  attestation jsonb not null default '{}'::jsonb,
  proof_envelope jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.tokenops_live_pilot_events (
  id uuid primary key default gen_random_uuid(),
  pilot_id text not null references public.tokenops_live_pilot_runs(pilot_id) on delete cascade,
  event_type text not null,
  event_payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists idx_tokenops_live_pilot_runs_task_token on public.tokenops_live_pilot_runs(task_token);
create index if not exists idx_tokenops_live_pilot_events_pilot_id on public.tokenops_live_pilot_events(pilot_id);




-- Shared updated_at trigger helper for runtime tables.
create or replace function wever_set_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

-- Attach updated_at triggers where the column exists.
do $$
declare
  table_name text;
begin
  foreach table_name in array array[
    'agent_onboarding_records',
    'agent_trust_profiles',
    'agent_credential_envelopes',
    'agent_quote_records',
    'settlement_provider_routes',
    'agent_credit_entitlements',
    'agent_run_contracts',
    'agent_task_runs',
    'agent_result_contracts',
    'agent_callback_deliveries',
    'wever_intake_work_order_dispatches',
    'work_order_execution_bindings',
    'tokenops_live_pilot_runs'
  ] loop
    execute format('drop trigger if exists set_updated_at on %I', table_name);
    execute format('create trigger set_updated_at before update on %I for each row execute function wever_set_updated_at()', table_name);
  end loop;
end $$;

-- RLS is intentionally enabled later in the activation sequence after service-role function access is verified.
-- The first controlled pilot should run through service-role Netlify functions, not direct public table writes.

commit;


-- Wever Labs Callback Delivery + Retry Binding v1
-- Applies after the runtime deployment pack and runtime function wiring.

create table if not exists callback_delivery_bindings (
  callback_delivery_id text primary key,
  task_token text not null,
  work_order_id text,
  agent_id text,
  requested_tool text,
  callback_url text,
  contact_channel text,
  delivery_state text not null default 'pending',
  attempt_count integer not null default 0,
  max_attempts integer not null default 3,
  next_retry_at timestamptz,
  result_contract_url text,
  settlement_receipt_url text,
  usage_receipt_url text,
  ledger_url text,
  attestation_url text,
  payload_ref text,
  last_error text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists callback_delivery_events (
  callback_event_id text primary key,
  callback_delivery_id text not null references callback_delivery_bindings(callback_delivery_id) on delete cascade,
  task_token text,
  event_type text not null,
  event_state text not null,
  attempt_number integer not null default 0,
  http_status integer,
  response_body_ref text,
  failure_reason text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists callback_retry_commands (
  retry_command_id text primary key,
  callback_delivery_id text not null references callback_delivery_bindings(callback_delivery_id) on delete cascade,
  task_token text not null,
  work_order_id text,
  retry_state text not null default 'scheduled',
  attempt_number integer not null default 1,
  scheduled_for timestamptz,
  reason_code text,
  preserve_payload_ref text,
  result_contract_url text,
  created_by text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists idx_callback_delivery_task_token on callback_delivery_bindings(task_token);
create index if not exists idx_callback_delivery_state on callback_delivery_bindings(delivery_state);
create index if not exists idx_callback_events_delivery on callback_delivery_events(callback_delivery_id);
create index if not exists idx_callback_retry_delivery on callback_retry_commands(callback_delivery_id);
