-- AI pursue briefs, donor receipts / thank-yous, sponsorship agreements

alter table opportunity_matches
  add column if not exists ai_priority text,
  add column if not exists ai_importance integer,
  add column if not exists ai_rationale text,
  add column if not exists ai_effort text,
  add column if not exists ai_overlap jsonb not null default '[]'::jsonb,
  add column if not exists ai_risks jsonb not null default '[]'::jsonb,
  add column if not exists ai_model text,
  add column if not exists ai_scored_at timestamptz;

alter table donations
  add column if not exists donor_email text,
  add column if not exists receipt_id text,
  add column if not exists thankyou_sent_at timestamptz;

create table if not exists donor_receipts (
  id text primary key,
  donation_id text not null references donations (id) on delete cascade,
  receipt_no text not null unique,
  donor_name text not null,
  donor_email text,
  amount_cents integer not null,
  received_at timestamptz not null,
  body_text text not null,
  thankyou_text text not null,
  sent_at timestamptz,
  sent_by text,
  status text not null default 'queued',
  created_at timestamptz not null default now()
);

create index if not exists donor_receipts_donation_idx on donor_receipts (donation_id);

create table if not exists agreements (
  id text primary key,
  kind text not null,
  title text not null,
  counterparty_name text not null,
  counterparty_email text,
  relationship_id text,
  donation_id text,
  opportunity_id text,
  amount_cents integer,
  term_months integer,
  purpose text,
  terms_json jsonb not null default '{}'::jsonb,
  body_text text not null,
  status text not null default 'draft',
  generated_by text,
  sent_at timestamptz,
  executed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists agreements_status_idx on agreements (status);