-- BountyDialer Phase 1 schema
-- Phase 1 is a curated B2B meeting marketplace.
-- The agent model is intentionally abstract so an agent can later be either:
-- 1. a human operator with dashboard access
-- 2. an API-driven bot with its own credentials

create extension if not exists pgcrypto;

create table if not exists profiles (
  user_id uuid primary key references auth.users(id) on delete cascade,
  full_name text,
  default_role text not null default 'requester' check (default_role in ('requester', 'agent', 'admin')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists agents (
  id text primary key,
  owner_user_id uuid references auth.users(id) on delete set null,
  agent_kind text not null check (agent_kind in ('human', 'api')),
  display_name text not null,
  status text not null check (status in ('applied', 'approved', 'rejected', 'suspended')),
  specialties jsonb not null default '[]'::jsonb,
  channels jsonb not null default '[]'::jsonb,
  description text,
  webhook_url text,
  api_key_hash text,
  api_key_prefix text,
  score numeric(5,2),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists bounties (
  id text primary key,
  requester_user_id uuid not null references auth.users(id) on delete restrict,
  target_name text not null,
  target_company text not null,
  request_type text not null,
  meeting_type text not null,
  bounty_amount_cents integer not null check (bounty_amount_cents > 0),
  platform_fee_cents integer not null default 0,
  notes text,
  success_criteria jsonb not null default '{}'::jsonb,
  assignment_mode text not null default 'curated_competition' check (assignment_mode in ('exclusive', 'curated_competition', 'open_market')),
  max_agent_claims integer not null default 3,
  status text not null check (
    status in (
      'draft',
      'submitted',
      'under_review',
      'approved',
      'rejected',
      'open_for_claims',
      'in_progress',
      'proof_submitted',
      'verified',
      'completed',
      'refunded',
      'disputed',
      'closed'
    )
  ),
  risk_level text not null default 'medium' check (risk_level in ('low', 'medium', 'high')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists bounty_claims (
  id text primary key,
  bounty_id text not null references bounties(id) on delete cascade,
  agent_id text not null references agents(id) on delete restrict,
  status text not null check (status in ('active', 'expired', 'withdrawn', 'won', 'lost')),
  exclusive_until timestamptz,
  created_at timestamptz not null default now()
);

create table if not exists claim_activities (
  id text primary key,
  claim_id text not null references bounty_claims(id) on delete cascade,
  actor_user_id uuid references auth.users(id) on delete set null,
  actor_agent_id text references agents(id) on delete set null,
  source_type text not null check (source_type in ('human', 'agent_api', 'system')),
  activity_type text not null,
  channel text,
  summary text not null,
  evidence_urls jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists proof_submissions (
  id text primary key,
  claim_id text not null references bounty_claims(id) on delete cascade,
  proof_type text not null,
  meeting_start_at timestamptz,
  meeting_type text,
  route_summary text,
  evidence_urls jsonb not null default '[]'::jsonb,
  status text not null check (status in ('submitted', 'verified', 'rejected')),
  submitted_at timestamptz not null default now(),
  reviewed_at timestamptz
);

create table if not exists payments (
  id text primary key,
  bounty_id text not null references bounties(id) on delete cascade,
  provider text not null default 'stripe',
  provider_payment_intent_id text unique,
  provider_checkout_session_id text unique,
  authorization_status text not null check (
    authorization_status in ('pending', 'authorized', 'captured', 'refunded', 'failed')
  ),
  gross_amount_cents integer not null,
  platform_fee_cents integer not null,
  created_at timestamptz not null default now()
);

create table if not exists payouts (
  id text primary key,
  claim_id text not null references bounty_claims(id) on delete cascade,
  provider text not null default 'stripe_connect',
  provider_transfer_id text,
  payout_amount_cents integer not null,
  status text not null check (status in ('pending', 'released', 'failed', 'reversed')),
  released_at timestamptz
);

create table if not exists bounty_events (
  id text primary key,
  bounty_id text not null references bounties(id) on delete cascade,
  actor_user_id uuid references auth.users(id) on delete set null,
  actor_agent_id text references agents(id) on delete set null,
  event_type text not null,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists idx_profiles_default_role on profiles(default_role);
create index if not exists idx_agents_owner_user_id on agents(owner_user_id);
create index if not exists idx_agents_status on agents(status);
create index if not exists idx_bounties_requester_user_id on bounties(requester_user_id);
create index if not exists idx_bounties_status on bounties(status);
create index if not exists idx_bounty_claims_bounty_id on bounty_claims(bounty_id);
create index if not exists idx_bounty_claims_agent_id on bounty_claims(agent_id);
create index if not exists idx_claim_activities_claim_id on claim_activities(claim_id);
create index if not exists idx_proof_submissions_claim_id on proof_submissions(claim_id);
create index if not exists idx_bounty_events_bounty_id on bounty_events(bounty_id);
