"""add alert_events and emerging_classifications tables

Revision ID: 0004
Revises: 0003
Create Date: 2026-09-08

StockLab final master engineering pass, §20/§52 (Emerging discovery) and §59 (alert events).

Purely additive: two new tables, no existing table touched, no backfill. Consistent with
docs/DEPLOYMENT.md §5's additive-only migration policy and with 0002/0003.

`alert_events` is the event LOG, deliberately separate from the existing `alerts` table, which is
a user's standing SUBSCRIPTION. §59 requires an event to carry what changed, from what, to what,
when, why and how serious; a subscription table cannot hold any of that. `owner_user_id` is
deliberately absent — an event is a fact about a security, not about a person, so one price move
produces one row rather than one row per watching user, and fan-out is a read-time join.

`emerging_classifications` is kept out of `scores` on purpose: §20 requires Emerging Opportunities
to be a separate surface from Top Opportunities, and co-locating the two scores is the first step
toward someone blending them.

NOT RUN in the environment this migration was authored in — `alembic` and `sqlalchemy` are not
installed there and there is no PostgreSQL instance (see docs/TEST_REPORT.md §0). Verified with
`python3 -m py_compile` only. Run `alembic upgrade head` against a real Postgres and confirm the
resulting tables match `app/models/user_facing.py` before trusting this file, exactly as disclosed
for 0001-0003.
"""
from alembic import op
import sqlalchemy as sa

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


def upgrade() -> None:
    op.create_table(
        "alert_events",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("security_id", sa.String(36), sa.ForeignKey("securities.id"), nullable=False),
        sa.Column("event_type", sa.String(48), nullable=False),
        sa.Column("previous_value", sa.JSON, nullable=True),
        sa.Column("new_value", sa.JSON, nullable=True),
        sa.Column("severity", sa.String(16), nullable=False),
        sa.Column("reason", sa.String(512), nullable=False),
        sa.Column("occurred_on", sa.Date, nullable=False),
        sa.Column("model_version", sa.String(16), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.UniqueConstraint("security_id", "event_type", "occurred_on",
                            name="uq_alert_event_security_type_date"),
    )
    op.create_index("ix_alert_events_security_id", "alert_events", ["security_id"])
    op.create_index("ix_alert_events_event_type", "alert_events", ["event_type"])
    op.create_index("ix_alert_events_severity", "alert_events", ["severity"])
    op.create_index("ix_alert_events_occurred_on", "alert_events", ["occurred_on"])
    # The dashboard query this exists for: "critical events in the last N days".
    op.create_index("ix_alert_events_occurred_severity", "alert_events", ["occurred_on", "severity"])

    op.create_table(
        "emerging_classifications",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("security_id", sa.String(36), sa.ForeignKey("securities.id"), nullable=False),
        sa.Column("calculation_date", sa.Date, nullable=False),
        sa.Column("status", sa.String(24), nullable=False),
        sa.Column("years_of_history", sa.Integer, nullable=False),
        sa.Column("emerging_score", sa.Float, nullable=True),
        sa.Column("signals", sa.JSON, nullable=True),
        sa.Column("discovery_reasons", sa.JSON, nullable=True),
        sa.Column("first_discovered_on", sa.Date, nullable=True),
        sa.Column("model_version", sa.String(16), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.UniqueConstraint("security_id", "calculation_date", name="uq_emerging_security_date"),
    )
    op.create_index("ix_emerging_security_id", "emerging_classifications", ["security_id"])
    op.create_index("ix_emerging_calculation_date", "emerging_classifications", ["calculation_date"])
    op.create_index("ix_emerging_status", "emerging_classifications", ["status"])
    # The Emerging Opportunities ranking query: highest emerging score among EMERGING companies.
    op.create_index("ix_emerging_status_score", "emerging_classifications", ["status", "emerging_score"])


def downgrade() -> None:
    # Additive migration, so the downgrade is a clean drop -- but note it DESTROYS the entire event
    # log and every emerging classification. docs/UPGRADE.md §6.2 recommends restoring from backup
    # over running a downgrade, for exactly this reason.
    op.drop_table("emerging_classifications")
    op.drop_table("alert_events")