"""Point-in-time history for the sourced fundamental observation

Revision ID: 033
Revises: 032
Create Date: 2026-08-12 00:00:00.000000

The hyperscaler capex / "good news, stock down" read lived in a single
``SystemSetting`` slot, so each refresh overwrote the last and no history
existed. The read is now a categorical channel reported alongside State and
Warning (never a term in either), and a channel with no history cannot be
replayed: a snapshot rebuild would record every historical session as if nothing
had ever been observed, and the event study could not measure the channel at all.

Keyed on ``effective_date`` (the session the observation becomes usable on,
normally the next weekday) rather than ``fetched_at``, because that is the gate
that stops a rebuild stamping today's reading onto historical rows.

The table starts empty. ``update_regime_monitor`` records the currently stored
observation on its next run, so a deployment does not lose the live reading —
but genuine history does not exist and cannot be invented here. Backfilling it
from the SEC capex line and earnings-date reactions is separate work; until then
every historical session reads ``unknown``, which is the honest value rather than
a guessed one.
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


revision: str = "033"
down_revision: Union[str, None] = "032"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "regime_fundamental_observations",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("effective_date", sa.Date(), nullable=False),
        sa.Column("f1_score", sa.Float(), nullable=True),
        sa.Column("f3_score", sa.Float(), nullable=True),
        sa.Column("capex_json", sa.Text(), nullable=False),
        sa.Column("good_news_stock_down", sa.String(length=10), nullable=False),
        sa.Column("reasoning", sa.Text(), nullable=True),
        sa.Column("source", sa.String(length=30), nullable=False),
        sa.Column("fetched_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    # One unique index, not a unique constraint plus a plain index: the model
    # declares `unique=True, index=True`, which SQLAlchemy renders as exactly
    # this. The constraint-plus-index pairing worked but left a redundant second
    # index on the column and a permanent metadata diff for autogenerate to keep
    # trying to reconcile. Matches RegimeSnapshot.date, the sibling table.
    op.create_index(
        "ix_regime_fundamental_observations_effective_date",
        "regime_fundamental_observations",
        ["effective_date"],
        unique=True,
    )


def downgrade() -> None:
    op.drop_index(
        "ix_regime_fundamental_observations_effective_date",
        table_name="regime_fundamental_observations",
    )
    op.drop_table("regime_fundamental_observations")