"""add gainer_snapshots table

Revision ID: p7g8h9i0j1k2
Revises: o6e7f8g9j0a1
Create Date: 2026-05-06

5-minute intraday snapshots of Yahoo Finance day_gainers for backtesting.
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision: str = "p7g8h9i0j1k2"
down_revision: Union[str, Sequence[str], None] = "o6e7f8g9j0a1"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "gainer_snapshots",
        sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("snapshot_at", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.Column("rank", sa.Integer(), nullable=False),
        sa.Column("symbol", sa.String(10), nullable=False),
        sa.Column("name", sa.Text(), nullable=True),
        sa.Column("exchange", sa.String(20), nullable=True),
        sa.Column("price", sa.Float(), nullable=True),
        sa.Column("change_percent", sa.Float(), nullable=True),
        sa.Column("volume", sa.BigInteger(), nullable=True),
        sa.Column("avg_volume_3m", sa.BigInteger(), nullable=True),
        sa.Column("market_cap", sa.BigInteger(), nullable=True),
        sa.Column("pe_ratio", sa.Float(), nullable=True),
        sa.Column("forward_pe", sa.Float(), nullable=True),
        sa.Column("eps_ttm", sa.Float(), nullable=True),
        sa.Column("dividend_yield", sa.Float(), nullable=True),
        sa.Column("fifty_two_week_high", sa.Float(), nullable=True),
        sa.Column("fifty_two_week_low", sa.Float(), nullable=True),
        sa.Column("created_at", sa.TIMESTAMP(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("snapshot_at", "symbol", name="uq_gainer_snapshot_symbol"),
    )
    op.create_index("idx_gainer_snapshot_at", "gainer_snapshots", ["snapshot_at"])
    op.create_index("idx_gainer_symbol_snapshot", "gainer_snapshots", ["symbol", "snapshot_at"])


def downgrade() -> None:
    op.drop_index("idx_gainer_symbol_snapshot", table_name="gainer_snapshots")
    op.drop_index("idx_gainer_snapshot_at", table_name="gainer_snapshots")
    op.drop_table("gainer_snapshots")