"""
SQLAlchemy 2.0 ORM models for IMDS (Instrument Market Data Service) tables.

Tables
------
  ImdsIdxData      — Market index snapshots (capital value, deviation)
  ImdsManData      — Market announcements / corporate actions
  ImdsMkistatData  — Per-instrument market statistics (OHLC, VWAP, volume)
  ImdsTrdData      — Aggregate trade summary (total trades, volume, value)

Column naming
-------------
  DB columns preserve the original UPPERCASE MySQL names.
  Python attributes use the same names — they map 1:1 via mapped_column().
  SQLAlchemy quotes them automatically in generated SQL.

Timestamp convention
--------------------
  *_STORE_TIMESTAMP defaults to NOW() at the DB level — never pass it
  explicitly from Python; let the server fill it in.
"""

from __future__ import annotations

from datetime import date, datetime, time
from typing import Optional

import sqlalchemy as sa
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class ImdsBase(DeclarativeBase):
    """Separate declarative base so IMDS models don't mix with auth tables."""
    pass


# ===========================================================================
# last_sync — DSE incremental sync checkpoint
# ===========================================================================

class LastSync(ImdsBase):
    """
    Tracks the last successfully synced data timestamp per source table.
    One row per table: TRD | IDX | MAN | MKISTAT.
    DseRetrieve reads this to build incremental WHERE clauses.
    """
    __tablename__ = "last_sync"

    id: Mapped[int] = mapped_column(sa.Integer, primary_key=True, autoincrement=True)
    table_name: Mapped[str] = mapped_column(sa.String(20), nullable=False, unique=True)
    last_synced_timestamp: Mapped[Optional[datetime]] = mapped_column(
        sa.DateTime, nullable=True
    )
    last_synced_at: Mapped[Optional[datetime]] = mapped_column(
        sa.DateTime, nullable=True, server_default=sa.text("NOW()")
    )

    def __repr__(self) -> str:
        return (
            f"<LastSync table={self.table_name!r} "
            f"last={self.last_synced_timestamp}>"
        )


# ===========================================================================
# imds_idx_data — market index snapshots
# ===========================================================================

class ImdsIdxData(ImdsBase):
    """One row per index snapshot received from the feed."""
    __tablename__ = "imds_idx_data"

    IDX_ID: Mapped[int] = mapped_column(
        sa.Integer, primary_key=True, autoincrement=True
    )
    IDX_INDEX_ID: Mapped[str] = mapped_column(sa.String(10), nullable=False)
    IDX_DATE_TIME: Mapped[datetime] = mapped_column(sa.DateTime, nullable=False)
    IDX_CAPITAL_VALUE: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(20, 6), nullable=True
    )
    IDX_DEVIATION: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(20, 6), nullable=True
    )
    IDX_PERCENTAGE_DEVIATION: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(20, 6), nullable=True
    )
    IDX_STORE_DATE: Mapped[Optional[date]] = mapped_column(sa.Date, nullable=True)
    IDX_STORE_TIME: Mapped[Optional[time]] = mapped_column(sa.Time, nullable=True)
    IDX_STORE_TIMESTAMP: Mapped[Optional[datetime]] = mapped_column(
        sa.DateTime,
        nullable=True,
        server_default=sa.text("NOW()"),
    )

    def __repr__(self) -> str:
        return (
            f"<ImdsIdxData id={self.IDX_ID} "
            f"index={self.IDX_INDEX_ID!r} "
            f"dt={self.IDX_DATE_TIME}>"
        )


# ===========================================================================
# imds_man_data — market announcements
# ===========================================================================

class ImdsManData(ImdsBase):
    """One row per market announcement or corporate action."""
    __tablename__ = "imds_man_data"

    MAN_ID: Mapped[int] = mapped_column(
        sa.Integer, primary_key=True, autoincrement=True
    )
    MAN_ANNOUNCEMENT_DATE_TIME: Mapped[datetime] = mapped_column(
        sa.DateTime, nullable=False
    )
    MAN_ANNOUNCEMENT_PREFIX: Mapped[Optional[str]] = mapped_column(
        sa.String(20), nullable=True
    )
    MAN_ANNOUNCEMENT: Mapped[Optional[str]] = mapped_column(sa.Text, nullable=True)
    MAN_EXPIRY_DATE: Mapped[Optional[date]] = mapped_column(sa.Date, nullable=True)
    MAN_STORE_DATE: Mapped[Optional[date]] = mapped_column(sa.Date, nullable=True)
    MAN_STORE_TIME: Mapped[Optional[time]] = mapped_column(sa.Time, nullable=True)
    MAN_STORE_TIMESTAMP: Mapped[Optional[datetime]] = mapped_column(
        sa.DateTime,
        nullable=True,
        server_default=sa.text("NOW()"),
    )

    def __repr__(self) -> str:
        return (
            f"<ImdsManData id={self.MAN_ID} "
            f"prefix={self.MAN_ANNOUNCEMENT_PREFIX!r} "
            f"dt={self.MAN_ANNOUNCEMENT_DATE_TIME}>"
        )


# ===========================================================================
# imds_mkistat_data — per-instrument market statistics
# ===========================================================================

class ImdsMkistatData(ImdsBase):
    """
    One row per instrument per polling tick.

    Nullable optional fields: OHLC prices, volume, trade counts, VWAP.
    NOT NULL required fields (no DB default): change, averages, advances.
    """
    __tablename__ = "imds_mkistat_data"

    MKISTAT_ID: Mapped[int] = mapped_column(
        sa.Integer, primary_key=True, autoincrement=True
    )
    MKISTAT_INSTRUMENT_CODE: Mapped[Optional[str]] = mapped_column(
        sa.String(50), nullable=True
    )
    MKISTAT_INSTRUMENT_NUMBER: Mapped[Optional[str]] = mapped_column(
        sa.String(50), nullable=True
    )
    MKISTAT_QUOTE_BASES: Mapped[Optional[str]] = mapped_column(
        sa.String(50), nullable=True
    )
    MKISTAT_OPEN_PRICE: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(10, 2), nullable=True
    )
    MKISTAT_PUB_LAST_TRADED_PRICE: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(10, 2), nullable=True
    )
    MKISTAT_SPOT_LAST_TRADED_PRICE: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(10, 2), nullable=True
    )
    MKISTAT_HIGH_PRICE: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(10, 2), nullable=True
    )
    MKISTAT_LOW_PRICE: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(10, 2), nullable=True
    )
    MKISTAT_CLOSE_PRICE: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(10, 2), nullable=True
    )
    MKISTAT_YDAY_CLOSE_PRICE: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(10, 2), nullable=True
    )
    MKISTAT_TOTAL_TRADES: Mapped[Optional[float]] = mapped_column(
        sa.Float, nullable=True
    )
    MKISTAT_TOTAL_VOLUME: Mapped[Optional[float]] = mapped_column(
        sa.Float, nullable=True
    )
    MKISTAT_TOTAL_VALUE: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(20, 6), nullable=True
    )
    MKISTAT_PUBLIC_TOTAL_TRADES: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(20, 3), nullable=True
    )
    MKISTAT_PUBLIC_TOTAL_VOLUME: Mapped[Optional[float]] = mapped_column(
        sa.Float, nullable=True
    )
    MKISTAT_PUBLIC_TOTAL_VALUE: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(20, 3), nullable=True
    )
    MKISTAT_SPOT_TOTAL_TRADES: Mapped[Optional[float]] = mapped_column(
        sa.Float, nullable=True
    )
    MKISTAT_SPOT_TOTAL_VOLUME: Mapped[Optional[float]] = mapped_column(
        sa.Float, nullable=True
    )
    MKISTAT_SPOT_TOTAL_VALUE: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(20, 3), nullable=True
    )
    MKISTAT_LM_DATE_TIME: Mapped[Optional[datetime]] = mapped_column(
        sa.DateTime, nullable=True
    )
    # --- required fields (no DB default) ------------------------------------
    MKISTAT_CHANGE_YDAY_CLOSE: Mapped[float] = mapped_column(sa.Float, nullable=False)
    MKISTAT_AVG_TRADE_SHARE:   Mapped[float] = mapped_column(sa.Float, nullable=False)
    MKISTAT_ADV_SHARES:        Mapped[float] = mapped_column(sa.Float, nullable=False)
    MKISTAT_ADV_VALUE:         Mapped[float] = mapped_column(sa.Float, nullable=False)
    MKISTAT_CHANGE_VOLUME:     Mapped[float] = mapped_column(sa.Float, nullable=False)
    MKISTAT_PVR:               Mapped[float] = mapped_column(sa.Float, nullable=False)
    MKISTAT_STD_DEV_60MIN:     Mapped[float] = mapped_column(sa.Float, nullable=False)
    MKISTAT_VWAP_DAILY:        Mapped[float] = mapped_column(sa.Float, nullable=False)
    MKISTAT_VWAP_1H:           Mapped[float] = mapped_column(sa.Float, nullable=False)
    # --- derived / enriched columns (added in migration 29d799b3e124) ------
    MKISTAT_SECTOR: Mapped[Optional[str]] = mapped_column(
        sa.String(100), nullable=True,
        comment="Sector from mkt_security_code lookup",
    )
    MKISTAT_YESTERDAY_CHANGE_PCT: Mapped[Optional[float]] = mapped_column(
        sa.Float, nullable=True,
        comment="(last - yday_close) / yday_close * 100",
    )
    # -----------------------------------------------------------------------
    MKISTAT_STORE_DATE: Mapped[Optional[date]] = mapped_column(sa.Date, nullable=True)
    MKISTAT_STORE_TIME: Mapped[Optional[time]] = mapped_column(sa.Time, nullable=True)
    MKISTAT_STORE_TIMESTAMP: Mapped[Optional[datetime]] = mapped_column(
        sa.DateTime,
        nullable=True,
        server_default=sa.text("NOW()"),
    )

    def __repr__(self) -> str:
        return (
            f"<ImdsMkistatData id={self.MKISTAT_ID} "
            f"code={self.MKISTAT_INSTRUMENT_CODE!r} "
            f"close={self.MKISTAT_CLOSE_PRICE}>"
        )


# ===========================================================================
# imds_trd_data — aggregate trade summary
# ===========================================================================

class ImdsTrdData(ImdsBase):
    """Aggregate trade summary row — total trades, volume, value for a session."""
    __tablename__ = "imds_trd_data"

    TRD_ID: Mapped[int] = mapped_column(
        sa.Integer, primary_key=True, autoincrement=True
    )
    TRD_SNO: Mapped[Optional[int]] = mapped_column(sa.Integer, nullable=True)
    TRD_TOTAL_TRADES: Mapped[Optional[float]] = mapped_column(sa.Float, nullable=True)
    TRD_TOTAL_VOLUME: Mapped[Optional[float]] = mapped_column(sa.Float, nullable=True)
    TRD_TOTAL_VALUE: Mapped[Optional[float]] = mapped_column(
        sa.Numeric(20, 4), nullable=True
    )
    TRD_MKT_STATUS: Mapped[Optional[str]] = mapped_column(sa.String(255), nullable=True)
    TRD_LM_DATE_TIME: Mapped[Optional[datetime]] = mapped_column(
        sa.DateTime, nullable=True
    )
    TRD_STORE_DATE: Mapped[Optional[date]] = mapped_column(sa.Date, nullable=True)
    TRD_STORE_TIME: Mapped[Optional[time]] = mapped_column(sa.Time, nullable=True)
    TRD_STORE_TIMESTAMP: Mapped[Optional[datetime]] = mapped_column(
        sa.DateTime,
        nullable=True,
        server_default=sa.text("NOW()"),
    )

    def __repr__(self) -> str:
        return (
            f"<ImdsTrdData id={self.TRD_ID} "
            f"status={self.TRD_MKT_STATUS!r} "
            f"trades={self.TRD_TOTAL_TRADES}>"
        )


# ===========================================================================
# mkt_security_code — instrument → sector lookup
# ===========================================================================

class MktSecurityCode(ImdsBase):
    """
    Reference table: maps MKISTAT_INSTRUMENT_CODE → sector.
    Populated by ops team or a separate import script.
    service_1 reads the full table at startup and caches it for 8 h.
    """
    __tablename__ = "mkt_security_code"

    id: Mapped[int] = mapped_column(sa.Integer, primary_key=True, autoincrement=True)
    security_code: Mapped[str] = mapped_column(
        sa.String(50), nullable=False, unique=True
    )
    sector: Mapped[Optional[str]] = mapped_column(sa.String(100), nullable=True)
    created_at: Mapped[Optional[datetime]] = mapped_column(
        sa.DateTime, nullable=True, server_default=sa.text("NOW()")
    )
    updated_at: Mapped[Optional[datetime]] = mapped_column(
        sa.DateTime, nullable=True, server_default=sa.text("NOW()")
    )

    def __repr__(self) -> str:
        return f"<MktSecurityCode code={self.security_code!r} sector={self.sector!r}>"