"""Initial schema

Revision ID: 001_initial
Revises:
Create Date: 2025-01-01 00:00:00.000000

"""

from collections.abc import Sequence

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

# revision identifiers, used by Alembic.
revision: str = "001_initial"
down_revision: str | None = None
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    # Create meetings table
    op.create_table(
        "meetings",
        sa.Column("id", sa.Integer(), nullable=False, comment="HRNZ meeting ID"),
        sa.Column("meeting_date", sa.Date(), nullable=False),
        sa.Column("venue", sa.String(length=255), nullable=False),
        sa.Column(
            "raw_json", JSONB, nullable=False, comment="Original HRNZ API response"
        ),
        sa.Column(
            "created_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.Column(
            "updated_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_meetings_meeting_date"), "meetings", ["meeting_date"])

    # Create horses table
    op.create_table(
        "horses",
        sa.Column("id", sa.Integer(), nullable=False, comment="HRNZ horse ID"),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column(
            "raw_json", JSONB, nullable=True, comment="Additional horse metadata"
        ),
        sa.Column(
            "created_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.Column(
            "updated_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_horses_name"), "horses", ["name"])

    # Create drivers table
    op.create_table(
        "drivers",
        sa.Column("id", sa.Integer(), nullable=False, comment="HRNZ driver ID"),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column(
            "raw_json", JSONB, nullable=True, comment="Additional driver metadata"
        ),
        sa.Column(
            "created_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.Column(
            "updated_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_drivers_name"), "drivers", ["name"])

    # Create trainers table
    op.create_table(
        "trainers",
        sa.Column("id", sa.Integer(), nullable=False, comment="HRNZ trainer ID"),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column(
            "raw_json", JSONB, nullable=True, comment="Additional trainer metadata"
        ),
        sa.Column(
            "created_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.Column(
            "updated_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_trainers_name"), "trainers", ["name"])

    # Create races table
    op.create_table(
        "races",
        sa.Column("id", sa.Integer(), nullable=False, autoincrement=True),
        sa.Column("meeting_id", sa.Integer(), nullable=False),
        sa.Column("race_number", sa.Integer(), nullable=False),
        sa.Column(
            "distance_m", sa.Integer(), nullable=True, comment="Distance in meters"
        ),
        sa.Column(
            "start_type",
            sa.String(length=50),
            nullable=True,
            comment="mobile or standing",
        ),
        sa.Column("gait", sa.String(length=50), nullable=True, comment="pace or trot"),
        sa.Column("race_datetime", sa.DateTime(), nullable=True),
        sa.Column(
            "raw_json", JSONB, nullable=False, comment="Original HRNZ API response"
        ),
        sa.Column(
            "created_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.Column(
            "updated_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.ForeignKeyConstraint(["meeting_id"], ["meetings.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("meeting_id", "race_number", name="uq_meeting_race"),
    )
    op.create_index(op.f("ix_races_meeting_id"), "races", ["meeting_id"])
    op.create_index(op.f("ix_races_race_datetime"), "races", ["race_datetime"])

    # Create starters table
    op.create_table(
        "starters",
        sa.Column("id", sa.Integer(), nullable=False, autoincrement=True),
        sa.Column("race_id", sa.Integer(), nullable=False),
        sa.Column("horse_id", sa.Integer(), nullable=True),
        sa.Column("driver_id", sa.Integer(), nullable=True),
        sa.Column("trainer_id", sa.Integer(), nullable=True),
        sa.Column(
            "barrier",
            sa.Integer(),
            nullable=True,
            comment="Starting barrier/gate number",
        ),
        sa.Column(
            "handicap_m",
            sa.Integer(),
            nullable=True,
            comment="Handicap in meters (back marks)",
        ),
        sa.Column(
            "placing",
            sa.Integer(),
            nullable=True,
            comment="Final placing (1=winner, NULL=DNF/no result)",
        ),
        sa.Column(
            "did_not_finish",
            sa.Boolean(),
            default=False,
            comment="DNF, pulled up, etc.",
        ),
        sa.Column(
            "raw_json", JSONB, nullable=False, comment="Original runner data from HRNZ"
        ),
        sa.Column(
            "created_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.Column(
            "updated_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.ForeignKeyConstraint(["race_id"], ["races.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["horse_id"], ["horses.id"], ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["driver_id"], ["drivers.id"], ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["trainer_id"], ["trainers.id"], ondelete="SET NULL"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_starters_race_id"), "starters", ["race_id"])
    op.create_index(op.f("ix_starters_horse_id"), "starters", ["horse_id"])
    op.create_index(op.f("ix_starters_driver_id"), "starters", ["driver_id"])
    op.create_index(op.f("ix_starters_trainer_id"), "starters", ["trainer_id"])

    # Create rating_snapshots table
    op.create_table(
        "rating_snapshots",
        sa.Column("id", sa.Integer(), nullable=False, autoincrement=True),
        sa.Column(
            "entity_type",
            sa.Enum("HORSE", "DRIVER", "TRAINER", name="entitytype"),
            nullable=False,
            comment="horse, driver, or trainer",
        ),
        sa.Column(
            "entity_id", sa.Integer(), nullable=False, comment="ID in respective table"
        ),
        sa.Column(
            "as_of_race_id",
            sa.Integer(),
            nullable=False,
            comment="Rating after this race",
        ),
        sa.Column("rating", sa.Float(), nullable=False, comment="Elo rating"),
        sa.Column(
            "rd", sa.Float(), nullable=True, comment="Rating deviation (uncertainty)"
        ),
        sa.Column(
            "meta",
            JSONB,
            nullable=True,
            comment="Additional metadata: components, K used, race count, etc.",
        ),
        sa.Column(
            "created_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.ForeignKeyConstraint(["as_of_race_id"], ["races.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "entity_type", "entity_id", "as_of_race_id", name="uq_entity_race_snapshot"
        ),
    )
    op.create_index(
        op.f("ix_rating_snapshots_entity_type"), "rating_snapshots", ["entity_type"]
    )
    op.create_index(
        op.f("ix_rating_snapshots_entity_id"), "rating_snapshots", ["entity_id"]
    )
    op.create_index(
        op.f("ix_rating_snapshots_as_of_race_id"), "rating_snapshots", ["as_of_race_id"]
    )

    # Create barrier_adjustments table
    op.create_table(
        "barrier_adjustments",
        sa.Column("id", sa.Integer(), nullable=False, autoincrement=True),
        sa.Column(
            "venue",
            sa.String(length=255),
            nullable=True,
            comment="Specific venue or NULL for global",
        ),
        sa.Column(
            "start_type",
            sa.String(length=50),
            nullable=True,
            comment="mobile/standing or NULL for any",
        ),
        sa.Column(
            "distance_bucket",
            sa.String(length=50),
            nullable=False,
            comment="e.g., '<1700', '1700-2000'",
        ),
        sa.Column("barrier", sa.Integer(), nullable=False, comment="Barrier number"),
        sa.Column(
            "adjustment",
            sa.Float(),
            default=0.0,
            nullable=False,
            comment="Rating points to add for this barrier",
        ),
        sa.Column(
            "sample_count",
            sa.Integer(),
            default=0,
            nullable=False,
            comment="Number of observations",
        ),
        sa.Column(
            "created_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.Column(
            "updated_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "venue",
            "start_type",
            "distance_bucket",
            "barrier",
            name="uq_barrier_adjustment",
        ),
    )

    # Create handicap_adjustments table
    op.create_table(
        "handicap_adjustments",
        sa.Column("id", sa.Integer(), nullable=False, autoincrement=True),
        sa.Column(
            "venue",
            sa.String(length=255),
            nullable=True,
            comment="Specific venue or NULL for global",
        ),
        sa.Column(
            "start_type",
            sa.String(length=50),
            nullable=True,
            comment="mobile/standing or NULL for any",
        ),
        sa.Column(
            "distance_bucket",
            sa.String(length=50),
            nullable=False,
            comment="e.g., '<1700', '1700-2000'",
        ),
        sa.Column(
            "handicap_m", sa.Integer(), nullable=False, comment="Handicap in meters"
        ),
        sa.Column(
            "adjustment",
            sa.Float(),
            default=0.0,
            nullable=False,
            comment="Rating points to add for this handicap",
        ),
        sa.Column(
            "sample_count",
            sa.Integer(),
            default=0,
            nullable=False,
            comment="Number of observations",
        ),
        sa.Column(
            "created_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.Column(
            "updated_at", sa.DateTime(), server_default=sa.text("now()"), nullable=False
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "venue",
            "start_type",
            "distance_bucket",
            "handicap_m",
            name="uq_handicap_adjustment",
        ),
    )


def downgrade() -> None:
    op.drop_table("handicap_adjustments")
    op.drop_table("barrier_adjustments")
    op.drop_table("rating_snapshots")
    op.drop_table("starters")
    op.drop_table("races")
    op.drop_table("trainers")
    op.drop_table("drivers")
    op.drop_table("horses")
    op.drop_table("meetings")
    sa.Enum(name="entitytype").drop(op.get_bind())