"""SQLAlchemy 2.0 ORM models.

All timestamps are stored in UTC. Sensitive values (bot token, raw Telegram
updates) are not persisted here. Access tokens are stored so the administrator
can retrieve a post's deep link; they are never written to logs.
"""

from __future__ import annotations

import enum
from datetime import datetime
from typing import Any

from sqlalchemy import (
    JSON,
    BigInteger,
    Boolean,
    Enum,
    Float,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.database.base import Base, TimestampMixin, UTCDateTime, utcnow


class MediaStatus(str, enum.Enum):
    RECEIVED = "RECEIVED"
    READY = "READY"
    IN_USE = "IN_USE"
    INVALID = "INVALID"
    ERROR = "ERROR"
    IGNORED = "IGNORED"


class PostStatus(str, enum.Enum):
    DRAFT = "DRAFT"
    READY = "READY"
    SCHEDULED = "SCHEDULED"
    PUBLISHED = "PUBLISHED"
    PAUSED = "PAUSED"
    ARCHIVED = "ARCHIVED"
    FAILED = "FAILED"


class DestinationStatus(str, enum.Enum):
    PENDING = "PENDING"
    SENDING = "SENDING"
    PUBLISHED = "PUBLISHED"
    FAILED = "FAILED"
    UNKNOWN = "UNKNOWN"
    SKIPPED = "SKIPPED"


class JobType(str, enum.Enum):
    PUBLISH_POST = "PUBLISH_POST"
    DELETE_USER_MESSAGE = "DELETE_USER_MESSAGE"
    DELETE_CHANNEL_MESSAGE = "DELETE_CHANNEL_MESSAGE"


class JobStatus(str, enum.Enum):
    PENDING = "PENDING"
    RUNNING = "RUNNING"
    DONE = "DONE"
    FAILED = "FAILED"
    CANCELLED = "CANCELLED"


class DeleteStatus(str, enum.Enum):
    PENDING = "PENDING"
    DELETED = "DELETED"
    FAILED = "FAILED"
    SKIPPED = "SKIPPED"


class MembershipMode(str, enum.Enum):
    INHERIT = "INHERIT"
    ENFORCE = "ENFORCE"
    DISABLE = "DISABLE"


class OperationStatus(str, enum.Enum):
    STARTED = "STARTED"
    SUCCESS = "SUCCESS"
    FAILED = "FAILED"
    UNKNOWN = "UNKNOWN"


def _enum(enum_type: type[enum.Enum], name: str) -> Enum:
    return Enum(
        enum_type,
        name=name,
        native_enum=False,
        values_callable=lambda members: [item.value for item in members],
        validate_strings=True,
        length=32,
    )


class User(TimestampMixin, Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    telegram_id: Mapped[int] = mapped_column(BigInteger, unique=True, index=True, nullable=False)
    username: Mapped[str | None] = mapped_column(String(64))
    display_name: Mapped[str | None] = mapped_column(String(255))
    first_seen: Mapped[datetime] = mapped_column(UTCDateTime, default=utcnow, nullable=False)
    last_active: Mapped[datetime] = mapped_column(UTCDateTime, default=utcnow, nullable=False)
    request_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    last_request_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    is_blocked: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    blocked_reason: Mapped[str | None] = mapped_column(String(500))

    deliveries: Mapped[list[UserDelivery]] = relationship(back_populates="user")


class Admin(TimestampMixin, Base):
    __tablename__ = "admins"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    telegram_id: Mapped[int] = mapped_column(BigInteger, unique=True, index=True, nullable=False)
    title: Mapped[str | None] = mapped_column(String(255))
    is_primary: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    is_env_admin: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    added_by: Mapped[int | None] = mapped_column(BigInteger)


class SourceChat(TimestampMixin, Base):
    __tablename__ = "source_chats"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    chat_id: Mapped[int] = mapped_column(BigInteger, unique=True, index=True, nullable=False)
    title: Mapped[str | None] = mapped_column(String(255))
    chat_type: Mapped[str] = mapped_column(String(32), default="supergroup", nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    auto_ingest: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    added_by: Mapped[int | None] = mapped_column(BigInteger)
    last_message_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    last_error: Mapped[str | None] = mapped_column(String(1000))

    media: Mapped[list[MediaAsset]] = relationship(back_populates="source_chat")


class MediaAsset(TimestampMixin, Base):
    __tablename__ = "media_assets"
    __table_args__ = (
        UniqueConstraint("source_chat_id", "source_message_id", name="uq_media_source_message"),
        UniqueConstraint("file_unique_id", name="uq_media_file_unique_id"),
        Index("ix_media_status_received", "status", "received_at"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    source_chat_id: Mapped[int] = mapped_column(
        ForeignKey("source_chats.id", ondelete="CASCADE"), nullable=False, index=True
    )
    source_message_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    file_id: Mapped[str] = mapped_column(String(512), nullable=False)
    file_unique_id: Mapped[str] = mapped_column(String(255), nullable=False)
    media_type: Mapped[str] = mapped_column(String(32), default="video", nullable=False)
    mime_type: Mapped[str | None] = mapped_column(String(128))
    file_name: Mapped[str | None] = mapped_column(String(255))
    file_size: Mapped[int | None] = mapped_column(BigInteger)
    duration_seconds: Mapped[float | None] = mapped_column(Float)
    width: Mapped[int | None] = mapped_column(Integer)
    height: Mapped[int | None] = mapped_column(Integer)
    thumbnail_file_id: Mapped[str | None] = mapped_column(String(512))
    original_caption: Mapped[str | None] = mapped_column(Text)
    status: Mapped[MediaStatus] = mapped_column(
        _enum(MediaStatus, "media_status"), default=MediaStatus.READY, nullable=False
    )
    error_message: Mapped[str | None] = mapped_column(String(1000))
    received_at: Mapped[datetime] = mapped_column(UTCDateTime, default=utcnow, nullable=False)

    source_chat: Mapped[SourceChat] = relationship(back_populates="media")
    posts: Mapped[list[Post]] = relationship(back_populates="media_asset")


class PublishChannel(TimestampMixin, Base):
    __tablename__ = "publish_channels"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    chat_id: Mapped[int] = mapped_column(BigInteger, unique=True, index=True, nullable=False)
    title: Mapped[str | None] = mapped_column(String(255))
    username: Mapped[str | None] = mapped_column(String(64))
    chat_type: Mapped[str] = mapped_column(String(32), default="channel", nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    is_default: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    can_post: Mapped[bool | None] = mapped_column(Boolean)
    last_checked_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    last_error: Mapped[str | None] = mapped_column(String(1000))
    added_by: Mapped[int | None] = mapped_column(BigInteger)

    destinations: Mapped[list[PostDestination]] = relationship(back_populates="channel")


class Post(TimestampMixin, Base):
    __tablename__ = "posts"
    __table_args__ = (Index("ix_posts_status_created", "status", "created_at"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    title: Mapped[str | None] = mapped_column(String(255))
    description: Mapped[str | None] = mapped_column(Text)
    media_asset_id: Mapped[int] = mapped_column(
        ForeignKey("media_assets.id", ondelete="RESTRICT"), nullable=False, index=True
    )
    cover_file_id: Mapped[str | None] = mapped_column(String(512))
    caption_override: Mapped[str | None] = mapped_column(Text)
    caption_template_override: Mapped[str | None] = mapped_column(Text)
    caption_prefix_override: Mapped[str | None] = mapped_column(Text)
    caption_suffix_override: Mapped[str | None] = mapped_column(Text)
    signature_override: Mapped[str | None] = mapped_column(String(255))
    video_link_template_override: Mapped[str | None] = mapped_column(String(512))
    buttons_override: Mapped[list[dict[str, str]] | None] = mapped_column(JSON)
    video_link: Mapped[str | None] = mapped_column(String(512))
    status: Mapped[PostStatus] = mapped_column(
        _enum(PostStatus, "post_status"), default=PostStatus.DRAFT, nullable=False, index=True
    )
    created_by: Mapped[int | None] = mapped_column(BigInteger)
    published_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    scheduled_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    auto_delete_enabled: Mapped[bool | None] = mapped_column(Boolean)
    user_delete_after_seconds: Mapped[int | None] = mapped_column(Integer)
    channel_delete_after_seconds: Mapped[int | None] = mapped_column(Integer)
    membership_mode: Mapped[MembershipMode] = mapped_column(
        _enum(MembershipMode, "membership_mode"),
        default=MembershipMode.INHERIT,
        nullable=False,
    )
    protect_content: Mapped[bool | None] = mapped_column(Boolean)
    publish_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    last_error: Mapped[str | None] = mapped_column(String(2000))

    media_asset: Mapped[MediaAsset] = relationship(back_populates="posts")
    destinations: Mapped[list[PostDestination]] = relationship(
        back_populates="post", cascade="all, delete-orphan"
    )
    required_channels: Mapped[list[PostRequiredChannel]] = relationship(
        back_populates="post", cascade="all, delete-orphan"
    )
    access_tokens: Mapped[list[AccessToken]] = relationship(
        back_populates="post", cascade="all, delete-orphan"
    )
    deliveries: Mapped[list[UserDelivery]] = relationship(
        back_populates="post", cascade="all, delete-orphan"
    )


class PostDestination(TimestampMixin, Base):
    __tablename__ = "post_destinations"
    __table_args__ = (
        UniqueConstraint("post_id", "channel_id", name="uq_post_destination"),
        Index("ix_destination_status", "status"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    post_id: Mapped[int] = mapped_column(
        ForeignKey("posts.id", ondelete="CASCADE"), nullable=False, index=True
    )
    channel_id: Mapped[int] = mapped_column(
        ForeignKey("publish_channels.id", ondelete="CASCADE"), nullable=False, index=True
    )
    status: Mapped[DestinationStatus] = mapped_column(
        _enum(DestinationStatus, "destination_status"),
        default=DestinationStatus.PENDING,
        nullable=False,
    )
    telegram_message_id: Mapped[int | None] = mapped_column(BigInteger)
    error_message: Mapped[str | None] = mapped_column(String(2000))
    attempted_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    published_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    delete_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    deleted_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    delete_error: Mapped[str | None] = mapped_column(String(1000))

    post: Mapped[Post] = relationship(back_populates="destinations")
    channel: Mapped[PublishChannel] = relationship(back_populates="destinations")


class RequiredChannel(TimestampMixin, Base):
    __tablename__ = "required_channels"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    chat_id: Mapped[int] = mapped_column(BigInteger, unique=True, index=True, nullable=False)
    title: Mapped[str] = mapped_column(String(255), nullable=False)
    invite_link: Mapped[str] = mapped_column(String(512), nullable=False)
    chat_type: Mapped[str] = mapped_column(String(32), default="channel", nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    is_required_globally: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    sort_order: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    bot_status: Mapped[str | None] = mapped_column(String(32))
    last_checked_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    last_error: Mapped[str | None] = mapped_column(String(1000))

    post_links: Mapped[list[PostRequiredChannel]] = relationship(
        back_populates="channel", cascade="all, delete-orphan"
    )


class PostRequiredChannel(Base):
    __tablename__ = "post_required_channels"

    post_id: Mapped[int] = mapped_column(
        ForeignKey("posts.id", ondelete="CASCADE"), primary_key=True
    )
    channel_id: Mapped[int] = mapped_column(
        ForeignKey("required_channels.id", ondelete="CASCADE"), primary_key=True
    )
    is_required: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)

    post: Mapped[Post] = relationship(back_populates="required_channels")
    channel: Mapped[RequiredChannel] = relationship(back_populates="post_links")


class AccessToken(TimestampMixin, Base):
    __tablename__ = "access_tokens"
    __table_args__ = (Index("ix_tokens_active_expiry", "is_active", "expires_at"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    post_id: Mapped[int] = mapped_column(
        ForeignKey("posts.id", ondelete="CASCADE"), nullable=False, index=True
    )
    token: Mapped[str] = mapped_column(String(64), unique=True, index=True, nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    expires_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    max_uses: Mapped[int | None] = mapped_column(Integer)
    use_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    last_used_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    rotated_at: Mapped[datetime | None] = mapped_column(UTCDateTime)

    post: Mapped[Post] = relationship(back_populates="access_tokens")
    deliveries: Mapped[list[UserDelivery]] = relationship(back_populates="access_token")


class UserDelivery(TimestampMixin, Base):
    __tablename__ = "user_deliveries"
    __table_args__ = (Index("ix_delivery_delete", "delete_at", "deleted_at"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    user_id: Mapped[int] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True
    )
    post_id: Mapped[int] = mapped_column(
        ForeignKey("posts.id", ondelete="CASCADE"), nullable=False, index=True
    )
    access_token_id: Mapped[int] = mapped_column(
        ForeignKey("access_tokens.id", ondelete="CASCADE"), nullable=False
    )
    telegram_message_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    sent_at: Mapped[datetime] = mapped_column(UTCDateTime, default=utcnow, nullable=False)
    delete_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    deleted_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    delete_status: Mapped[DeleteStatus] = mapped_column(
        _enum(DeleteStatus, "delete_status"), default=DeleteStatus.PENDING, nullable=False
    )
    delete_error: Mapped[str | None] = mapped_column(String(1000))
    correlation_id: Mapped[str | None] = mapped_column(String(64), index=True)

    user: Mapped[User] = relationship(back_populates="deliveries")
    post: Mapped[Post] = relationship(back_populates="deliveries")
    access_token: Mapped[AccessToken] = relationship(back_populates="deliveries")


class ScheduledJob(TimestampMixin, Base):
    __tablename__ = "scheduled_jobs"
    __table_args__ = (
        Index("ix_jobs_due", "status", "run_at"),
        UniqueConstraint("idempotency_key", name="uq_jobs_idempotency_key"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    job_type: Mapped[JobType] = mapped_column(
        _enum(JobType, "job_type"), nullable=False, index=True
    )
    status: Mapped[JobStatus] = mapped_column(
        _enum(JobStatus, "job_status"), default=JobStatus.PENDING, nullable=False
    )
    run_at: Mapped[datetime] = mapped_column(UTCDateTime, nullable=False)
    payload: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    attempts: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    max_attempts: Mapped[int] = mapped_column(Integer, default=5, nullable=False)
    last_error: Mapped[str | None] = mapped_column(String(2000))
    correlation_id: Mapped[str | None] = mapped_column(String(64), index=True)
    idempotency_key: Mapped[str | None] = mapped_column(String(255))
    started_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    finished_at: Mapped[datetime | None] = mapped_column(UTCDateTime)
    post_id: Mapped[int | None] = mapped_column(
        ForeignKey("posts.id", ondelete="SET NULL"), index=True
    )
    delivery_id: Mapped[int | None] = mapped_column(
        ForeignKey("user_deliveries.id", ondelete="SET NULL"), index=True
    )


class AppSetting(Base):
    __tablename__ = "app_settings"

    key: Mapped[str] = mapped_column(String(128), primary_key=True)
    value: Mapped[str] = mapped_column(Text, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(
        UTCDateTime, default=utcnow, onupdate=utcnow, nullable=False
    )
    updated_by: Mapped[int | None] = mapped_column(BigInteger)


class AuditLog(Base):
    __tablename__ = "audit_logs"
    __table_args__ = (Index("ix_audit_created", "created_at"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    actor_id: Mapped[int | None] = mapped_column(BigInteger, index=True)
    action: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    entity_type: Mapped[str | None] = mapped_column(String(64))
    entity_id: Mapped[str | None] = mapped_column(String(64))
    details: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    correlation_id: Mapped[str | None] = mapped_column(String(64), index=True)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime, default=utcnow, nullable=False)


class OperationLog(Base):
    __tablename__ = "operation_logs"
    __table_args__ = (Index("ix_operation_created", "created_at"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    correlation_id: Mapped[str] = mapped_column(String(64), index=True, nullable=False)
    operation: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    status: Mapped[OperationStatus] = mapped_column(
        _enum(OperationStatus, "operation_status"), nullable=False
    )
    entity_type: Mapped[str | None] = mapped_column(String(64))
    entity_id: Mapped[str | None] = mapped_column(String(64))
    error_message: Mapped[str | None] = mapped_column(String(2000))
    meta: Mapped[dict[str, Any]] = mapped_column("metadata", JSON, default=dict, nullable=False)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime, default=utcnow, nullable=False)
    finished_at: Mapped[datetime | None] = mapped_column(UTCDateTime)


class RateLimitEvent(Base):
    __tablename__ = "rate_limit_events"
    __table_args__ = (Index("ix_rate_limit_lookup", "telegram_id", "action", "created_at"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    telegram_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    action: Mapped[str] = mapped_column(String(64), nullable=False)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime, default=utcnow, nullable=False)


class MembershipCheck(Base):
    __tablename__ = "membership_checks"
    __table_args__ = (Index("ix_membership_check_created", "created_at"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    telegram_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
    post_id: Mapped[int | None] = mapped_column(
        ForeignKey("posts.id", ondelete="SET NULL"), index=True
    )
    all_joined: Mapped[bool] = mapped_column(Boolean, nullable=False)
    error_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime, default=utcnow, nullable=False)