"""
SQLAlchemy ORM models for profiling storage (dbo schema).

- **user_profiling** — published data (admin API reads this only). One row per user_id.
- **user_profiling_staging** — batch job writes here; a full successful scan ends with
  :meth:`ProfilingRepository.publish_staging_to_published` (single transaction).

Other app code (data_fetcher, deep analysis) stays on raw SQL + pyodbc.
"""

from __future__ import annotations

from datetime import datetime
from typing import Optional

from sqlalchemy import BigInteger, DateTime, Identity, UnicodeText, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class UserProfiling(Base):
    """
    Published snapshot. Admin/API reads dbo.user_profiling.

    Legacy/production tables often use ``id`` BIGINT IDENTITY as PK and ``user_id`` as
    a separate column (possibly multiple historical rows per user until deduped).
    Upserts use raw MERGE in :class:`ProfilingRepository` so ``id`` is generated by SQL Server.

    Staging uses ``updated_at``; publish MERGE maps staging timestamps into ``created_at``.
    """

    __tablename__ = "user_profiling"
    __table_args__ = {"schema": "dbo"}

    id: Mapped[int] = mapped_column(BigInteger, Identity(), primary_key=True)
    user_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=False),
        nullable=False,
        server_default=text("SYSUTCDATETIME()"),
    )
    profile_json: Mapped[str] = mapped_column(UnicodeText, nullable=False)
    deep_analysis_markdown: Mapped[Optional[str]] = mapped_column(UnicodeText, nullable=True)


class UserProfilingStaging(Base):
    """
    Batch refresh workspace. Not read by admin API until publish.
    Table: dbo.user_profiling_staging
    """

    __tablename__ = "user_profiling_staging"
    __table_args__ = {"schema": "dbo"}

    # Natural PK (no IDENTITY); MSSQL dialect otherwise enables IDENTITY_INSERT on insert.
    user_id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=False)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=False),
        nullable=False,
        server_default=text("SYSUTCDATETIME()"),
    )
    profile_json: Mapped[str] = mapped_column(UnicodeText, nullable=False)
    deep_analysis_markdown: Mapped[Optional[str]] = mapped_column(UnicodeText, nullable=True)


class UserProfilingTracker(Base):
    """
    Last successful *published* run per user (batch recency skip).

    Surrogate ``id`` IDENTITY + unique ``user_id`` matches SQL Server-friendly layout and
    avoids ORM IDENTITY_INSERT on a non-identity ``user_id`` PK.
    """

    __tablename__ = "user_profiling_tracker"
    __table_args__ = {"schema": "dbo"}

    id: Mapped[int] = mapped_column(BigInteger, Identity(), primary_key=True)
    user_id: Mapped[int] = mapped_column(BigInteger, nullable=False, unique=True)
    last_completed_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=False),
        nullable=False,
    )