"""
SQLAlchemy 2.0 ORM Models — PostgreSQL & SQLite Compatible.
"""

from __future__ import annotations

from datetime import datetime
from typing import Optional

from sqlalchemy import (
    BigInteger,
    Boolean,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    func,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship

from database.base import Base


# =========================================================
# Users
# =========================================================

class User(Base):
    __tablename__ = "users"

    user_id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=False)
    username: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    points: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")
    referred_by: Mapped[Optional[int]] = mapped_column(BigInteger, nullable=True)
    banned: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, server_default="false")
    ai_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")
    ai_date: Mapped[Optional[str]] = mapped_column(String(32), nullable=True)
    joined_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
    )

    # Relationships
    tickets: Mapped[list["Ticket"]] = relationship("Ticket", back_populates="user", cascade="all, delete-orphan")
    achievements: Mapped[list["Achievement"]] = relationship("Achievement", back_populates="user", cascade="all, delete-orphan")
    vip_subscription: Mapped[Optional["VipSubscription"]] = relationship(
        "VipSubscription", back_populates="user", uselist=False, cascade="all, delete-orphan"
    )
    game_stats: Mapped[list["GameStat"]] = relationship("GameStat", back_populates="user", cascade="all, delete-orphan")
    point_backups: Mapped[list["PointBackup"]] = relationship("PointBackup", back_populates="user", cascade="all, delete-orphan")

    __table_args__ = (
        Index("idx_users_points", "points"),
        Index("idx_users_banned", "banned"),
    )


# =========================================================
# Tickets
# =========================================================

class Ticket(Base):
    __tablename__ = "tickets"

    ticket_id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("users.user_id", ondelete="CASCADE"), nullable=False)
    service: Mapped[str] = mapped_column(String(255), nullable=False)
    details: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    points_paid: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="pending", server_default="pending")
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
    )

    user: Mapped["User"] = relationship("User", back_populates="tickets")

    __table_args__ = (
        Index("idx_tickets_user_id", "user_id"),
        Index("idx_tickets_status", "status"),
        Index("idx_tickets_created_at", "created_at"),
    )


# =========================================================
# Channels
# =========================================================

class Channel(Base):
    __tablename__ = "channels"

    channel_username: Mapped[str] = mapped_column(String(255), primary_key=True)
    title: Mapped[Optional[str]] = mapped_column(String(512), nullable=True)


# =========================================================
# Buttons
# =========================================================

class Button(Base):
    __tablename__ = "buttons"

    button_id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    parent_id: Mapped[Optional[int]] = mapped_column(Integer, ForeignKey("buttons.button_id", ondelete="CASCADE"), nullable=True)
    title: Mapped[str] = mapped_column(String(255), nullable=False)
    type: Mapped[str] = mapped_column(String(64), nullable=False)
    content: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    file_id: Mapped[Optional[str]] = mapped_column(String(512), nullable=True)
    caption: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    extra_text: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    media_type: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    folder_content: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    point_cost: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")
    vip_only: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, server_default="false")
    early_access: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")
    order_index: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
    )

    children: Mapped[list["Button"]] = relationship(
        "Button",
        back_populates="parent",
        cascade="all, delete-orphan",
    )
    parent: Mapped[Optional["Button"]] = relationship(
        "Button",
        back_populates="children",
        remote_side=[button_id],
    )

    __table_args__ = (
        Index("idx_buttons_parent_id", "parent_id"),
    )


# =========================================================
# Settings
# =========================================================

class Setting(Base):
    __tablename__ = "settings"

    key: Mapped[str] = mapped_column(String(255), primary_key=True)
    value: Mapped[Optional[str]] = mapped_column(Text, nullable=True)


# =========================================================
# Achievements
# =========================================================

class Achievement(Base):
    __tablename__ = "achievements"

    user_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("users.user_id", ondelete="CASCADE"), primary_key=True)
    achievement: Mapped[str] = mapped_column(String(128), primary_key=True)
    unlocked_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
    )

    user: Mapped["User"] = relationship("User", back_populates="achievements")

    __table_args__ = (
        Index("idx_achievements_user", "user_id"),
    )


# =========================================================
# VIP Subscriptions
# =========================================================

class VipSubscription(Base):
    __tablename__ = "vip_subscriptions"

    user_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("users.user_id", ondelete="CASCADE"), primary_key=True)
    expiry_date: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    auto_renew: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, server_default="false")

    user: Mapped["User"] = relationship("User", back_populates="vip_subscription")

    __table_args__ = (
        Index("idx_vip_expiry", "expiry_date"),
    )


# =========================================================
# Game Stats
# =========================================================

class GameStat(Base):
    __tablename__ = "game_stats"

    user_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("users.user_id", ondelete="CASCADE"), primary_key=True)
    game_type: Mapped[str] = mapped_column(String(64), primary_key=True)
    wins: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")
    losses: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")

    user: Mapped["User"] = relationship("User", back_populates="game_stats")


# =========================================================
# Admin Codes
# =========================================================

class AdminCode(Base):
    __tablename__ = "admin_codes"

    code: Mapped[str] = mapped_column(String(64), primary_key=True)
    created_by: Mapped[int] = mapped_column(BigInteger, ForeignKey("users.user_id", ondelete="CASCADE"), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
    )
    used_by: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("users.user_id", ondelete="SET NULL"), nullable=True)
    used_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    expires_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    vip_days: Mapped[int] = mapped_column(Integer, nullable=False, default=30, server_default="30")
    is_used: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, server_default="false")

    __table_args__ = (
        Index("idx_admin_codes_code", "code"),
        Index("idx_admin_codes_used", "is_used"),
    )


# =========================================================
# Points Backup
# =========================================================

class PointBackup(Base):
    __tablename__ = "points_backup"

    backup_id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    backup_date: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
    )
    user_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("users.user_id", ondelete="CASCADE"), nullable=False)
    points_backup: Mapped[int] = mapped_column(Integer, nullable=False)
    restored: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, server_default="false")

    user: Mapped["User"] = relationship("User", back_populates="point_backups")

    __table_args__ = (
        Index("idx_backup_user", "user_id"),
        Index("idx_backup_restored", "restored"),
        Index("idx_backup_date", "backup_date"),
    )

# ملاحظة: LEGACY_AI_PROMPT و get_legacy_ai_prompt() انتقلوا نهائياً إلى
# database/crud.py — هو المصدر الوحيد المستخدم فعلياً عبر database/__init__.py.
# لا تُعرَّف هنا لتفادي تعارض نسختين من نفس البرومبت (كان سبب مشكلة سابقة).