"""alerts + dre publications

Revision ID: 0002
Revises: 0001
Create Date: 2026-04-16

"""
from alembic import op


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


def upgrade() -> None:
    op.execute("""
        CREATE TABLE alerts_config (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
          type TEXT NOT NULL CHECK (type IN ('email','webhook')),
          target TEXT NOT NULL,
          enabled BOOLEAN NOT NULL DEFAULT TRUE,
          created_at TIMESTAMPTZ NOT NULL DEFAULT now()
        )
    """)
    op.execute("CREATE INDEX ix_alerts_config_company ON alerts_config(company_id)")
    op.execute(
        "CREATE INDEX ix_alerts_config_enabled ON alerts_config(enabled) WHERE enabled = TRUE"
    )

    op.execute("""
        CREATE TABLE alerts_log (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          process_id UUID NOT NULL REFERENCES processes(id) ON DELETE CASCADE,
          alert_config_id UUID NOT NULL REFERENCES alerts_config(id) ON DELETE CASCADE,
          status TEXT NOT NULL CHECK (status IN ('sent','failed')),
          response TEXT,
          created_at TIMESTAMPTZ NOT NULL DEFAULT now()
        )
    """)
    op.execute("CREATE INDEX ix_alerts_log_process ON alerts_log(process_id)")
    op.execute("CREATE INDEX ix_alerts_log_created ON alerts_log(created_at DESC)")

    op.execute("""
        CREATE TABLE dre_publications (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
          title TEXT NOT NULL,
          summary TEXT,
          date DATE,
          type TEXT,
          source_url TEXT,
          raw_json JSONB,
          dedup_hash CHAR(64) NOT NULL UNIQUE,
          created_at TIMESTAMPTZ NOT NULL DEFAULT now()
        )
    """)
    op.execute("CREATE INDEX ix_dre_company ON dre_publications(company_id)")
    op.execute("CREATE INDEX ix_dre_date ON dre_publications(date DESC)")


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS dre_publications CASCADE")
    op.execute("DROP TABLE IF EXISTS alerts_log CASCADE")
    op.execute("DROP TABLE IF EXISTS alerts_config CASCADE")