"""Phrase library: sentences from past bottom lines and problem discussions.

Built from `forecasts` / `forecast_problems` by app.phrases.rebuild, so any
source that lands in those tables (Drupal archive now; NAC/AFP later) feeds it.

Revision ID: 0003
Revises: 0002
Create Date: 2026-09-18
"""

from alembic import op

revision = "0003"
down_revision = "0002"
branch_labels = None
depends_on = None

UPGRADE = """
CREATE TABLE phrases (
    id bigserial PRIMARY KEY,
    center_id integer NOT NULL REFERENCES centers(id) ON DELETE CASCADE,
    forecast_id integer NOT NULL REFERENCES forecasts(id) ON DELETE CASCADE,
    field text NOT NULL CHECK (field IN ('bottom_line', 'problem')),
    problem_types smallint[] NOT NULL DEFAULT '{}',
    likelihood smallint,
    size_max numeric(2,1),
    locations text[] NOT NULL DEFAULT '{}',
    text text NOT NULL,
    norm text NOT NULL,
    valid_date date NOT NULL,
    source text NOT NULL,
    guidance_era text NOT NULL,
    tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', text)) STORED
);
CREATE INDEX phrases_center_field_idx ON phrases (center_id, field);
CREATE INDEX phrases_types_idx ON phrases USING gin (problem_types);
CREATE INDEX phrases_tsv_idx ON phrases USING gin (tsv);
"""


def upgrade() -> None:
    op.execute(UPGRADE)


def downgrade() -> None:
    op.execute("DROP TABLE phrases")