"""Add news, sentiment, and stock selection tables

Revision ID: 002
Revises: 001
Create Date: 2026-04-02
"""

from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa

revision: str = "002"
down_revision: Union[str, None] = "001"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "news_items",
        sa.Column("id", sa.Text, primary_key=True),
        sa.Column("source", sa.Text, nullable=False),
        sa.Column("headline", sa.Text, nullable=False),
        sa.Column("summary", sa.Text),
        sa.Column("url", sa.Text),
        sa.Column("published_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("symbols", sa.Text),
        sa.Column("sentiment", sa.Float, nullable=False),
        sa.Column("category", sa.Text, nullable=False),
        sa.Column("raw_data", sa.Text),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
        ),
    )
    op.create_index("idx_news_items_published", "news_items", ["published_at"])
    op.create_index("idx_news_items_source", "news_items", ["source"])

    op.create_table(
        "symbol_scores",
        sa.Column("id", sa.Text, primary_key=True),
        sa.Column("symbol", sa.Text, nullable=False, unique=True),
        sa.Column("news_score", sa.Float, nullable=False, server_default="0"),
        sa.Column("news_count", sa.Integer, nullable=False, server_default="0"),
        sa.Column("social_score", sa.Float, nullable=False, server_default="0"),
        sa.Column("policy_score", sa.Float, nullable=False, server_default="0"),
        sa.Column("filing_score", sa.Float, nullable=False, server_default="0"),
        sa.Column("composite", sa.Float, nullable=False, server_default="0"),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
    )

    op.create_table(
        "market_sentiment",
        sa.Column("id", sa.Text, primary_key=True),
        sa.Column("fear_greed", sa.Integer, nullable=False),
        sa.Column("fear_greed_label", sa.Text, nullable=False),
        sa.Column("vix", sa.Float),
        sa.Column("fed_stance", sa.Text, nullable=False, server_default="neutral"),
        sa.Column("market_regime", sa.Text, nullable=False, server_default="neutral"),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
    )

    op.create_table(
        "stock_selections",
        sa.Column("id", sa.Text, primary_key=True),
        sa.Column("trade_date", sa.Date, nullable=False),
        sa.Column("symbol", sa.Text, nullable=False),
        sa.Column("side", sa.Text, nullable=False),
        sa.Column("conviction", sa.Float, nullable=False),
        sa.Column("reason", sa.Text, nullable=False),
        sa.Column("key_news", sa.Text),
        sa.Column("sentiment_snapshot", sa.Text),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
        ),
    )
    op.create_index("idx_stock_selections_date", "stock_selections", ["trade_date"])


def downgrade() -> None:
    op.drop_table("stock_selections")
    op.drop_table("market_sentiment")
    op.drop_table("symbol_scores")
    op.drop_table("news_items")