"""initial schema — quotes, headlines, feeds, strategic_logs, ai_calls,
portfolios, snapshots, positions, job_runs, quotes_daily.

Revision ID: 0001
Revises:
Create Date: 2026-05-15
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op


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


def upgrade() -> None:
    op.create_table(
        "quotes",
        sa.Column("id", sa.BigInteger, primary_key=True, autoincrement=True),
        sa.Column("symbol", sa.String(64), nullable=False),
        sa.Column("source", sa.String(32), nullable=False),
        sa.Column("label", sa.String(128), nullable=False, server_default=""),
        sa.Column("group_name", sa.String(64), nullable=False),
        sa.Column("price", sa.Float),
        sa.Column("currency", sa.String(8)),
        sa.Column("as_of", sa.String(16)),
        sa.Column("changes", sa.JSON),
        sa.Column("error", sa.String(255)),
        sa.Column("fetched_at", sa.DateTime(timezone=True), nullable=False),
    )
    op.create_index("ix_quotes_symbol_fetched", "quotes", ["symbol", "fetched_at"])
    op.create_index("ix_quotes_group", "quotes", ["group_name"])

    op.create_table(
        "quotes_daily",
        sa.Column("symbol", sa.String(64), primary_key=True),
        sa.Column("date", sa.Date, primary_key=True),
        sa.Column("close", sa.Float),
        sa.Column("high", sa.Float),
        sa.Column("low", sa.Float),
        sa.Column("source", sa.String(32), nullable=False),
    )

    op.create_table(
        "headlines",
        sa.Column("id", sa.BigInteger, primary_key=True, autoincrement=True),
        sa.Column("source", sa.String(64), nullable=False),
        sa.Column("category", sa.String(32), nullable=False),
        sa.Column("title", sa.String(512), nullable=False),
        sa.Column("url", sa.String(1024), nullable=False),
        sa.Column("published_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("fetched_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("fingerprint", sa.String(40), nullable=False),
        sa.UniqueConstraint("fingerprint", name="uq_headlines_fingerprint"),
    )
    op.create_index("ix_headlines_published", "headlines", ["published_at"])
    op.create_index("ix_headlines_category_published", "headlines", ["category", "published_at"])

    op.create_table(
        "feeds",
        sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
        sa.Column("category", sa.String(32), nullable=False),
        sa.Column("name", sa.String(64), nullable=False),
        sa.Column("url", sa.String(1024), nullable=False),
        sa.Column("enabled", sa.Boolean, nullable=False, server_default=sa.text("1")),
        sa.Column("consecutive_failures", sa.Integer, nullable=False, server_default=sa.text("0")),
        sa.Column("last_success_at", sa.DateTime(timezone=True)),
        sa.UniqueConstraint("category", "name", name="uq_feeds_cat_name"),
    )

    op.create_table(
        "strategic_logs",
        sa.Column("id", sa.BigInteger, primary_key=True, autoincrement=True),
        sa.Column("generated_at", sa.DateTime(timezone=True), nullable=False, index=True),
        sa.Column("model", sa.String(64), nullable=False),
        sa.Column("anchor_date", sa.String(16)),
        sa.Column("prompt_version", sa.Integer, nullable=False, server_default=sa.text("1")),
        sa.Column("content", sa.Text, nullable=False),
        sa.Column("prompt_tokens", sa.Integer),
        sa.Column("completion_tokens", sa.Integer),
        sa.Column("cost_usd", sa.Float),
    )

    op.create_table(
        "ai_calls",
        sa.Column("id", sa.BigInteger, primary_key=True, autoincrement=True),
        sa.Column("called_at", sa.DateTime(timezone=True), nullable=False, index=True),
        sa.Column("model", sa.String(64), nullable=False),
        sa.Column("prompt_tokens", sa.Integer),
        sa.Column("completion_tokens", sa.Integer),
        sa.Column("cost_usd", sa.Float),
        sa.Column("status", sa.String(16), nullable=False, server_default="ok"),
        sa.Column("error", sa.String(512)),
    )

    op.create_table(
        "portfolios",
        sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
        sa.Column("name", sa.String(64), nullable=False),
        sa.Column("source", sa.String(32), nullable=False),
        sa.Column("currency", sa.String(8), nullable=False, server_default="GBP"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.UniqueConstraint("name", name="uq_portfolios_name"),
    )

    op.create_table(
        "portfolio_snapshots",
        sa.Column("id", sa.BigInteger, primary_key=True, autoincrement=True),
        sa.Column("portfolio_id", sa.Integer, sa.ForeignKey("portfolios.id", ondelete="CASCADE"), nullable=False),
        sa.Column("snapshot_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("total_value", sa.Float),
        sa.Column("cash", sa.Float),
        sa.Column("invested", sa.Float),
        sa.Column("raw_json", sa.JSON),
    )
    op.create_index("ix_snap_portfolio_at", "portfolio_snapshots", ["portfolio_id", "snapshot_at"])

    op.create_table(
        "positions",
        sa.Column("id", sa.BigInteger, primary_key=True, autoincrement=True),
        sa.Column("snapshot_id", sa.BigInteger, sa.ForeignKey("portfolio_snapshots.id", ondelete="CASCADE"), nullable=False),
        sa.Column("ticker", sa.String(64), nullable=False),
        sa.Column("quantity", sa.Float),
        sa.Column("average_price", sa.Float),
        sa.Column("current_price", sa.Float),
        sa.Column("ppl", sa.Float),
    )

    op.create_table(
        "job_runs",
        sa.Column("id", sa.BigInteger, primary_key=True, autoincrement=True),
        sa.Column("name", sa.String(64), nullable=False),
        sa.Column("started_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("finished_at", sa.DateTime(timezone=True)),
        sa.Column("status", sa.String(16), nullable=False, server_default="running"),
        sa.Column("error", sa.Text),
        sa.Column("items_written", sa.Integer),
    )
    op.create_index("ix_jobruns_name_started", "job_runs", ["name", "started_at"])


def downgrade() -> None:
    for t in [
        "job_runs", "positions", "portfolio_snapshots", "portfolios",
        "ai_calls", "strategic_logs", "feeds", "headlines",
        "quotes_daily", "quotes",
    ]:
        op.drop_table(t)