-- CFL Grant Scout · four-layer schema (SPEC-003 §4)

create table if not exists org_profiles (
  id text primary key,
  name text not null,
  ein text not null,
  mission_text text not null,
  focus_keywords jsonb not null default '[]'::jsonb,
  zero_equity_language text not null,
  geography text not null,
  version integer not null default 1,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists opportunities (
  id text primary key,
  source text not null,
  external_id text,
  title text not null,
  funder_name text not null,
  amount_min integer,
  amount_max integer,
  deadline date,
  eligibility_raw text,
  url text,
  status text not null default 'open',
  raw_json jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  unique (source, external_id)
);

create index if not exists opportunities_deadline_idx on opportunities (deadline);
create index if not exists opportunities_status_idx on opportunities (status);

create table if not exists opportunity_matches (
  id text primary key,
  opportunity_id text not null references opportunities (id) on delete cascade,
  org_profile_id text not null references org_profiles (id),
  fit_score integer not null,
  fit_reason text,
  knockout_reason text,
  created_at timestamptz not null default now()
);

create index if not exists opportunity_matches_opp_idx on opportunity_matches (opportunity_id);
create index if not exists opportunity_matches_score_idx on opportunity_matches (fit_score desc);

create table if not exists foundations (
  id text primary key,
  ein text not null unique,
  name text not null,
  state text not null,
  city text,
  assets bigint,
  total_giving bigint,
  form_type text not null default '990-PF',
  ntee text,
  ntee_label text,
  source text not null default 'seed',
  last_filing_year integer,
  geo_tier text not null default 'national',
  officers jsonb not null default '[]'::jsonb,
  website text,
  raw_json jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists foundations_state_idx on foundations (state);
create index if not exists foundations_geo_idx on foundations (geo_tier);

create table if not exists foundation_grants (
  id text primary key,
  foundation_id text not null references foundations (id) on delete cascade,
  tax_year integer not null,
  recipient_name text not null,
  recipient_ein text,
  amount integer not null,
  purpose_raw text,
  source_ref text
);

create index if not exists foundation_grants_fnd_idx on foundation_grants (foundation_id);
create index if not exists foundation_grants_year_idx on foundation_grants (tax_year);

create table if not exists foundation_scores (
  id text primary key,
  foundation_id text not null references foundations (id) on delete cascade,
  org_profile_id text not null references org_profiles (id),
  fit_score integer not null,
  median_grant integer,
  mean_grant integer,
  grant_count integer not null default 0,
  small_grant_ratio numeric,
  geo_match text,
  computed_at timestamptz not null default now()
);

create unique index if not exists foundation_scores_unique
  on foundation_scores (foundation_id, org_profile_id);

create table if not exists drafts (
  id text primary key,
  match_id text references opportunity_matches (id) on delete set null,
  foundation_id text references foundations (id) on delete set null,
  opportunity_id text references opportunities (id) on delete set null,
  kind text not null default 'narrative',
  status text not null default 'draft',
  title text not null,
  narrative_sections jsonb not null default '{}'::jsonb,
  generated_by text,
  reviewed_by text,
  version integer not null default 1,
  validation_flags jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists drafts_status_idx on drafts (status);

create table if not exists relationships (
  id text primary key,
  foundation_id text not null references foundations (id) on delete cascade,
  stage text not null,
  owner_name text not null default 'CFL Staff',
  stage_entered_at timestamptz not null default now(),
  next_action text,
  next_action_due date,
  notes text,
  gift_donation_id text,
  created_at timestamptz not null default now()
);

create unique index if not exists relationships_foundation_idx on relationships (foundation_id);
create index if not exists relationships_stage_idx on relationships (stage);

create table if not exists solicitations (
  id text primary key,
  opportunity_id text references opportunities (id) on delete set null,
  foundation_id text references foundations (id) on delete set null,
  relationship_id text references relationships (id) on delete set null,
  contact_name text not null,
  contact_role text,
  method text not null,
  sent_at timestamptz,
  sent_by text,
  package_ref text,
  status text not null default 'queued',
  notes text,
  hold_reason text,
  created_at timestamptz not null default now()
);

create index if not exists solicitations_status_idx on solicitations (status);

create table if not exists solicitation_responses (
  id text primary key,
  solicitation_id text not null references solicitations (id) on delete cascade,
  response_type text not null,
  responded_at timestamptz not null default now(),
  raw_content text,
  notes text,
  actor_id text not null default 'CFL Staff'
);

create index if not exists solicitation_responses_sol_idx on solicitation_responses (solicitation_id);

create table if not exists discovery_runs (
  id text primary key,
  source text not null,
  started_at timestamptz not null default now(),
  finished_at timestamptz,
  records_ingested integer not null default 0,
  records_new integer not null default 0,
  errors_json jsonb not null default '[]'::jsonb,
  status text not null default 'running',
  notes text
);

create index if not exists discovery_runs_started_idx on discovery_runs (started_at desc);

create table if not exists pipeline_events (
  id text primary key,
  subject_type text not null,
  subject_id text not null,
  event_type text not null,
  actor_id text not null default 'CFL Staff',
  notes text,
  created_at timestamptz not null default now()
);

create index if not exists pipeline_events_subject_idx on pipeline_events (subject_type, subject_id);
create index if not exists pipeline_events_created_idx on pipeline_events (created_at desc);

create table if not exists donations (
  id text primary key,
  donor_name text not null,
  amount_cents integer not null,
  campaign text,
  amount_tier text not null,
  recurring boolean not null default false,
  restricted boolean not null default false,
  restriction_note text,
  source text not null default 'donorbox',
  relationship_id text references relationships (id) on delete set null,
  received_at timestamptz not null default now(),
  raw_json jsonb not null default '{}'::jsonb
);

create index if not exists donations_received_idx on donations (received_at desc);

create table if not exists documents (
  id text primary key,
  filename text not null,
  mime text not null,
  content_text text not null,
  version integer not null default 1,
  subject_type text,
  subject_id text,
  created_at timestamptz not null default now()
);

create table if not exists notifications (
  id text primary key,
  kind text not null,
  title text not null,
  body text not null,
  href text,
  read boolean not null default false,
  created_at timestamptz not null default now()
);

create table if not exists settings (
  key text primary key,
  value jsonb not null,
  updated_at timestamptz not null default now()
);

create table if not exists coverage_spikes (
  id text primary key,
  ran_at timestamptz not null default now(),
  sample_size integer not null,
  structured_count integer not null,
  paper_count integer not null,
  notes text,
  rows_json jsonb not null default '[]'::jsonb
);