-- VenThrive business-news system — run this once against your Supabase project's
-- SQL editor (or `psql`). See docs/blueprint/adr/0015-business-news-system-design.md
-- for the reasoning behind every choice below.

create extension if not exists pgcrypto;

-- ─── Sources ────────────────────────────────────────────────────────────────
-- Sources are data, not code, so an admin can add/disable one without a deploy.
-- 'rss' sources are polled with feedparser; 'html' sources with a generic
-- title + first-paragraph extractor (see backend/news/scrape.py).
create table if not exists news_sources (
  id            uuid primary key default gen_random_uuid(),
  name          text not null,
  url           text not null unique,
  source_type   text not null check (source_type in ('rss', 'html')),
  -- Which of the three sourcing strategies this feeds (ADR-0015) — purely
  -- informational, doesn't change scrape behavior.
  source_group  text not null check (source_group in ('national_news', 'agency_bulletin', 'funder_program')),
  active        boolean not null default true,
  created_at    timestamptz not null default now()
);

-- A handful of real, well-known starter sources per ADR-0015's three source
-- groups. Add more through this table directly — no code change needed.
insert into news_sources (name, url, source_type, source_group) values
  ('BusinessDay',               'https://businessday.ng/feed/',                 'rss',  'national_news'),
  ('Nairametrics',              'https://nairametrics.com/feed/',               'rss',  'national_news'),
  ('Premium Times — Business',  'https://www.premiumtimesng.com/category/business/feed', 'rss', 'national_news'),
  ('NiMet — Weather Alerts',    'https://www.nimet.gov.ng',                     'html', 'agency_bulletin'),
  ('NEMA — Advisories',         'https://nema.gov.ng',                          'html', 'agency_bulletin'),
  ('SMEDAN',                    'https://smedan.gov.ng',                        'html', 'funder_program'),
  ('Bank of Industry — News',   'https://www.boi.ng/category/news/',            'html', 'funder_program'),
  ('Tony Elumelu Foundation',   'https://www.tonyelumelufoundation.org/news',   'html', 'funder_program')
on conflict (url) do nothing;

-- ─── Items ──────────────────────────────────────────────────────────────────
create table if not exists news_items (
  id              uuid primary key default gen_random_uuid(),
  source_id       uuid references news_sources(id) on delete set null,
  url             text not null,
  url_hash        text not null unique,
  title           text not null,
  raw_text        text not null default '',
  published_at    timestamptz,
  scraped_at      timestamptz not null default now(),

  status          text not null default 'pending'
                    check (status in ('pending', 'classified', 'needs_review', 'rejected')),

  -- Classification output (ADR-0015) — all null until classified. No 'threat' value:
  -- reporting something bad that already happened has no value once it can't be acted
  -- on; a predictive risk system is a separate, forward-looking feature.
  impact          text check (impact in ('opportunity', 'neutral')),
  category        text check (category in ('policy', 'weather', 'infrastructure', 'funding', 'security', 'other')),
  business_types  text[],          -- subsector ids from data/sectors.ts, see backend/news/subsectors.json
  location        text,
  deadline        date,            -- only set for an opportunity with an explicit deadline in the source text
  expires_at      timestamptz,     -- deadline if set, else scraped_at + the category's default window
  confidence      numeric,
  review_reason   text,            -- why this needs_review, for the admin queue
  classified_at   timestamptz,

  -- The completeness bar from ADR-0015, enforced here, not just in application
  -- code: a classified item must have a category, at least one business type,
  -- and a location.
  constraint classified_items_are_complete check (
    status <> 'classified'
    or (category is not null and location is not null
        and business_types is not null and array_length(business_types, 1) > 0)
  )
);

create index if not exists news_items_status_idx on news_items (status);
create index if not exists news_items_expires_at_idx on news_items (expires_at);
create index if not exists news_items_business_types_idx on news_items using gin (business_types);

-- ─── Poll run log ───────────────────────────────────────────────────────────
-- One row per admin-triggered poll (ADR-0015: admin-triggered only, daily
-- auto-poll deferred) — an audit trail and a place to surface run-level errors.
create table if not exists news_polls (
  id                 uuid primary key default gen_random_uuid(),
  triggered_by       text not null,   -- admin's Firebase UID
  since_days         int not null check (since_days between 1 and 30),
  started_at         timestamptz not null default now(),
  finished_at        timestamptz,
  items_scraped      int not null default 0,
  items_deduped_out  int not null default 0,
  items_classified   int not null default 0,
  items_flagged      int not null default 0,
  items_rejected     int not null default 0,  -- auto-discarded: incomplete and the model wasn't confident either
  current_source     text,  -- name of the source being scraped right now, for a live run console
  error              text
);

alter table news_polls add column if not exists items_rejected int not null default 0;
alter table news_polls add column if not exists current_source text;

-- 'threat' removed from impact (ADR-0015 addendum): a reactive "this already happened"
-- label has no value to a business owner who can't act on it anymore. Existing rows
-- are reclassified neutral; a predictive risk/forecast system is planned separately.
update news_items set impact = 'neutral' where impact = 'threat';
alter table news_items drop constraint if exists news_items_impact_check;
alter table news_items add constraint news_items_impact_check check (impact in ('opportunity', 'neutral'));