from datetime import UTC, datetime

from sqlalchemy import (
    BigInteger,
    Boolean,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    TypeDecorator,
    UniqueConstraint,
    text,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


def now_utc() -> datetime:
    return datetime.now(UTC)


class UtcDateTime(TypeDecorator):
    """Always store and surface UTC-aware datetimes.

    SQLite has no native tz-aware type and pysqlite strips tzinfo on
    round-trip. We convert inputs to UTC, persist them as naive UTC under
    the hood, and re-stamp tzinfo=UTC on read. The result: every datetime
    on a model attribute is aware UTC, always.
    """

    impl = DateTime
    cache_ok = True

    def process_bind_param(self, value, dialect):
        if value is None:
            return None
        if value.tzinfo is None:
            raise TypeError(
                "naive datetime passed to UtcDateTime column; "
                "all writes must be UTC-aware"
            )
        return value.astimezone(UTC).replace(tzinfo=None)

    def process_result_value(self, value, dialect):
        if value is None:
            return None
        return value.replace(tzinfo=UTC)


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    password_digest: Mapped[str] = mapped_column(String(255), nullable=False)
    admin: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    active: Mapped[bool] = mapped_column(
        Boolean, default=True, nullable=False, server_default=text("1"),
    )
    created_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)
    password_changed_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)


class Overlay(Base):
    __tablename__ = "overlays"
    __table_args__ = (
        Index(
            "uq_overlay_name_system",
            "name",
            unique=True,
            sqlite_where=text("user_id IS NULL"),
        ),
        Index(
            "uq_overlay_name_per_user",
            "name",
            "user_id",
            unique=True,
            sqlite_where=text("user_id IS NOT NULL"),
        ),
        Index("ix_overlays_type_user_id", "type", "user_id"),
        {"sqlite_autoincrement": True},
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(128), nullable=False)
    path: Mapped[str] = mapped_column(String(512), nullable=False)
    type: Mapped[str] = mapped_column(String(16), nullable=False, default="workshop")
    user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    script: Mapped[str] = mapped_column(Text, default="", nullable=False)
    last_build_status: Mapped[str] = mapped_column(String(16), default="", nullable=False)
    created_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)


class WorkshopItem(Base):
    __tablename__ = "workshop_items"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    steam_id: Mapped[str] = mapped_column(String(20), unique=True, nullable=False)
    title: Mapped[str] = mapped_column(String(255), default="", nullable=False)
    filename: Mapped[str] = mapped_column(String(255), default="", nullable=False)
    file_url: Mapped[str] = mapped_column(Text, default="", nullable=False)
    file_size: Mapped[int] = mapped_column(BigInteger, default=0, nullable=False)
    time_updated: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    preview_url: Mapped[str] = mapped_column(Text, default="", nullable=False)
    last_downloaded_at: Mapped[datetime | None] = mapped_column(UtcDateTime, nullable=True)
    last_error: Mapped[str] = mapped_column(Text, default="", nullable=False)
    created_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)


class OverlayWorkshopItem(Base):
    __tablename__ = "overlay_workshop_items"
    __table_args__ = (
        UniqueConstraint("overlay_id", "workshop_item_id", name="uq_overlay_workshop_item"),
        Index("ix_owi_workshop_item", "workshop_item_id"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    overlay_id: Mapped[int] = mapped_column(
        ForeignKey("overlays.id", ondelete="CASCADE"), nullable=False
    )
    workshop_item_id: Mapped[int] = mapped_column(
        ForeignKey("workshop_items.id", ondelete="RESTRICT"), nullable=False
    )


class Blueprint(Base):
    __tablename__ = "blueprints"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), nullable=False)
    name: Mapped[str] = mapped_column(String(128), nullable=False)
    arguments: Mapped[str] = mapped_column(Text, default="[]", nullable=False)
    config: Mapped[str] = mapped_column(Text, default="[]", nullable=False)
    created_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)


class BlueprintOverlay(Base):
    __tablename__ = "blueprint_overlays"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    blueprint_id: Mapped[int] = mapped_column(ForeignKey("blueprints.id"), nullable=False)
    overlay_id: Mapped[int] = mapped_column(ForeignKey("overlays.id"), nullable=False)
    position: Mapped[int] = mapped_column(Integer, nullable=False)
    expose_server_cfg: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default=text("0")
    )
    created_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)


class Server(Base):
    __tablename__ = "servers"
    __table_args__ = (
        UniqueConstraint("user_id", "name", name="uq_servers_user_name"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), nullable=False)
    blueprint_id: Mapped[int] = mapped_column(ForeignKey("blueprints.id"), nullable=False)
    name: Mapped[str] = mapped_column(String(128), nullable=False)
    port: Mapped[int] = mapped_column(Integer, unique=True, nullable=False)
    desired_state: Mapped[str] = mapped_column(String(16), default="stopped", nullable=False)
    actual_state: Mapped[str] = mapped_column(String(16), default="unknown", nullable=False)
    actual_state_updated_at: Mapped[datetime | None] = mapped_column(UtcDateTime, nullable=True)
    last_error: Mapped[str] = mapped_column(Text, default="", nullable=False)
    rcon_password: Mapped[str] = mapped_column(
        String(64), nullable=False, default="", server_default=""
    )
    hostname: Mapped[str] = mapped_column(
        String(128), nullable=False, default="", server_default=""
    )
    created_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)


class Job(Base):
    __tablename__ = "jobs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    server_id: Mapped[int | None] = mapped_column(ForeignKey("servers.id"), nullable=True)
    overlay_id: Mapped[int | None] = mapped_column(ForeignKey("overlays.id"), nullable=True)
    operation: Mapped[str] = mapped_column(String(32), nullable=False)
    state: Mapped[str] = mapped_column(String(16), default="queued", nullable=False)
    exit_code: Mapped[int | None] = mapped_column(Integer, nullable=True)
    started_at: Mapped[datetime | None] = mapped_column(UtcDateTime, nullable=True)
    finished_at: Mapped[datetime | None] = mapped_column(UtcDateTime, nullable=True)
    created_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)


class JobLog(Base):
    __tablename__ = "job_logs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    job_id: Mapped[int] = mapped_column(ForeignKey("jobs.id"), nullable=False)
    seq: Mapped[int] = mapped_column(Integer, nullable=False)
    stream: Mapped[str] = mapped_column(String(8), nullable=False)
    line: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(UtcDateTime, default=now_utc, nullable=False)


class ServerLiveState(Base):
    __tablename__ = "server_live_state"
    __table_args__ = (
        Index("ix_sls_server_started", "server_id", "started_at"),
        {"sqlite_autoincrement": True},
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    server_id: Mapped[int] = mapped_column(
        ForeignKey("servers.id", ondelete="CASCADE"), nullable=False
    )
    started_at: Mapped[datetime] = mapped_column(UtcDateTime, nullable=False)
    last_seen_at: Mapped[datetime] = mapped_column(UtcDateTime, nullable=False)
    players: Mapped[int] = mapped_column(Integer, nullable=False)
    max_players: Mapped[int] = mapped_column(Integer, nullable=False)
    bots: Mapped[int] = mapped_column(Integer, nullable=False)
    map: Mapped[str] = mapped_column(String(64), nullable=False)
    hibernating: Mapped[bool] = mapped_column(Boolean, nullable=False)


class ServerPlayerSession(Base):
    __tablename__ = "server_player_session"
    __table_args__ = (
        Index("ix_sps_server_open", "server_id", "left_at"),
        Index("ix_sps_steam_history", "steam_id_64", "joined_at"),
        {"sqlite_autoincrement": True},
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    server_id: Mapped[int] = mapped_column(
        ForeignKey("servers.id", ondelete="CASCADE"), nullable=False
    )
    steam_id_64: Mapped[str] = mapped_column(String(20), nullable=False)
    joined_at: Mapped[datetime] = mapped_column(UtcDateTime, nullable=False)
    left_at: Mapped[datetime | None] = mapped_column(UtcDateTime, nullable=True)
    name_at_join: Mapped[str] = mapped_column(String(64), nullable=False)
    min_ping: Mapped[int] = mapped_column(Integer, nullable=False)
    max_ping: Mapped[int] = mapped_column(Integer, nullable=False)


class SteamUserProfile(Base):
    __tablename__ = "steam_user_profile"

    steam_id_64: Mapped[str] = mapped_column(String(20), primary_key=True)
    persona_name: Mapped[str] = mapped_column(String(64), nullable=False)
    avatar_url: Mapped[str] = mapped_column(Text, nullable=False)
    fetched_at: Mapped[datetime] = mapped_column(UtcDateTime, nullable=False)


class CommandHistory(Base):
    __tablename__ = "command_history"
    __table_args__ = (
        Index("ix_cmdhist_user_server_id", "user_id", "server_id", "id"),
    )

    id:         Mapped[int]      = mapped_column(Integer, primary_key=True)
    user_id:    Mapped[int]      = mapped_column(ForeignKey("users.id"), nullable=False)
    server_id:  Mapped[int]      = mapped_column(
        ForeignKey("servers.id", ondelete="CASCADE"), nullable=False
    )
    command:    Mapped[str]      = mapped_column(Text, nullable=False)
    reply:      Mapped[str]      = mapped_column(
        Text, nullable=False, default="", server_default=""
    )
    is_error:   Mapped[bool]     = mapped_column(
        Boolean, nullable=False, default=False, server_default=text("0")
    )
    created_at: Mapped[datetime] = mapped_column(
        UtcDateTime, default=now_utc, nullable=False
    )
