"""
SQLAlchemy async models for market data storage.
"""
from __future__ import annotations

from datetime import datetime

from sqlalchemy import DateTime, Float, Index, Integer, String, Text
from sqlalchemy.ext.asyncio import AsyncAttrs, async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

from app.config import settings


class Base(AsyncAttrs, DeclarativeBase):
    pass


class TradeRecord(Base):
    __tablename__ = "trades"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    symbol: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    price: Mapped[float] = mapped_column(Float, nullable=False)
    size: Mapped[float] = mapped_column(Float, nullable=False)
    side: Mapped[str | None] = mapped_column(String(8), nullable=True)
    trade_id: Mapped[str | None] = mapped_column(String(64), nullable=True)
    ts_exchange: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    ts_recv: Mapped[datetime] = mapped_column(DateTime, nullable=False)

    __table_args__ = (
        Index("ix_trades_symbol_ts", "symbol", "ts_recv"),
    )


class QuoteRecord(Base):
    __tablename__ = "quotes"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    symbol: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    bid: Mapped[float] = mapped_column(Float, nullable=False)
    ask: Mapped[float] = mapped_column(Float, nullable=False)
    bid_size: Mapped[float] = mapped_column(Float, nullable=False)
    ask_size: Mapped[float] = mapped_column(Float, nullable=False)
    ts_exchange: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    ts_recv: Mapped[datetime] = mapped_column(DateTime, nullable=False)

    __table_args__ = (
        Index("ix_quotes_symbol_ts", "symbol", "ts_recv"),
    )


class BookSnapshotRecord(Base):
    __tablename__ = "book_snapshots"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    symbol: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    bids_json: Mapped[str] = mapped_column(Text, nullable=False)  # JSON
    asks_json: Mapped[str] = mapped_column(Text, nullable=False)  # JSON
    depth: Mapped[int] = mapped_column(Integer, nullable=False)
    ts_exchange: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    ts_recv: Mapped[datetime] = mapped_column(DateTime, nullable=False)


# ── Engine & Session factory ──────────────────────────────────────────

engine = create_async_engine(settings.sqlite_url, echo=False)
async_session = async_sessionmaker(engine, expire_on_commit=False)


async def init_db() -> None:
    """Create all tables."""
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
