"""classify DRE publications by corporate-change relevance

Revision ID: 0010
Revises: 0009
Create Date: 2026-04-17

The DRE carries all sorts of publications: job vacancies, municipal decrees,
awards — most irrelevant for competitive intelligence. A small subset signals
actual changes at a company (management, capital, liquidation, headquarters).
Tagging at ingest so the UI can surface only what matters.
"""
from alembic import op


revision = "0010"
down_revision = "0009"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        "ALTER TABLE dre_publications "
        "ADD COLUMN relevance TEXT NOT NULL DEFAULT 'low' "
        "CHECK (relevance IN ('high','medium','low')), "
        "ADD COLUMN change_kind TEXT"
    )
    op.execute(
        "CREATE INDEX ix_dre_relevance ON dre_publications(company_id, relevance, date DESC)"
    )


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_dre_relevance")
    op.execute("ALTER TABLE dre_publications DROP COLUMN relevance, DROP COLUMN change_kind")