import uuid
from datetime import datetime
from sqlalchemy import BigInteger, Boolean, DateTime, ForeignKey, Index, Integer, String, Text, UniqueConstraint
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.orm import Mapped, mapped_column, relationship
from infrastructure.database.base import Base, UUIDPrimaryKeyMixin, TimestampMixin, TenantMixin


class Bot(Base, UUIDPrimaryKeyMixin, TimestampMixin, TenantMixin):
    __tablename__ = "bots"

    name: Mapped[str] = mapped_column(String(255), nullable=False)
    username: Mapped[str] = mapped_column(String(255), index=True, nullable=False)
    status: Mapped[str] = mapped_column(String(50), default="active", nullable=False)
    default_locale: Mapped[str] = mapped_column(String(10), default="en", nullable=False)

    # Relationships
    credentials: Mapped["BotCredential"] = relationship("BotCredential", back_populates="bot", uselist=False, cascade="all, delete-orphan", lazy="selectin")
    webhook: Mapped["BotWebhook"] = relationship("BotWebhook", back_populates="bot", uselist=False, cascade="all, delete-orphan", lazy="selectin")
    settings: Mapped["BotSetting"] = relationship("BotSetting", back_populates="bot", uselist=False, cascade="all, delete-orphan", lazy="selectin")
    commands: Mapped[list["BotCommand"]] = relationship("BotCommand", back_populates="bot", cascade="all, delete-orphan", lazy="selectin")
    users: Mapped[list["BotUser"]] = relationship("BotUser", back_populates="bot", cascade="all, delete-orphan", lazy="selectin")
    modules: Mapped[list["BotModule"]] = relationship("BotModule", back_populates="bot", cascade="all, delete-orphan", lazy="selectin")
    referrals: Mapped[list["BotReferralRecord"]] = relationship("BotReferralRecord", back_populates="bot", cascade="all, delete-orphan", lazy="selectin")
    redeem_codes: Mapped[list["BotRedeemCode"]] = relationship("BotRedeemCode", back_populates="bot", cascade="all, delete-orphan", lazy="selectin")
    redeem_logs: Mapped[list["BotRedeemLog"]] = relationship("BotRedeemLog", back_populates="bot", cascade="all, delete-orphan", lazy="selectin")
    files: Mapped[list["BotFile"]] = relationship("BotFile", back_populates="bot", cascade="all, delete-orphan", lazy="selectin")
    support_tickets: Mapped[list["BotSupportTicket"]] = relationship("BotSupportTicket", back_populates="bot", cascade="all, delete-orphan", lazy="selectin")


class BotCredential(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    __tablename__ = "bot_credentials"

    bot_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bots.id", ondelete="CASCADE"),
        unique=True,
        nullable=False,
    )
    encrypted_token: Mapped[str] = mapped_column(Text, nullable=False)
    key_version: Mapped[int] = mapped_column(Integer, default=1, nullable=False)

    bot: Mapped["Bot"] = relationship("Bot", back_populates="credentials")


class BotWebhook(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    __tablename__ = "bot_webhooks"

    bot_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bots.id", ondelete="CASCADE"),
        unique=True,
        nullable=False,
    )
    secret_hash: Mapped[str] = mapped_column(String(128), nullable=False)
    webhook_url: Mapped[str] = mapped_column(String(512), nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)

    bot: Mapped["Bot"] = relationship("Bot", back_populates="webhook")


class BotSetting(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    __tablename__ = "bot_settings"

    bot_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bots.id", ondelete="CASCADE"),
        unique=True,
        nullable=False,
    )
    settings_json: Mapped[dict] = mapped_column(JSONB, default=dict, nullable=False)

    bot: Mapped["Bot"] = relationship("Bot", back_populates="settings")


class BotCommand(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    __tablename__ = "bot_commands"

    bot_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bots.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    command: Mapped[str] = mapped_column(String(100), nullable=False)
    handler: Mapped[str] = mapped_column(String(100), nullable=False)
    enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)

    bot: Mapped["Bot"] = relationship("Bot", back_populates="commands")

    __table_args__ = (
        UniqueConstraint("bot_id", "command", name="uq_bot_command"),
    )


class BotUpdate(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    __tablename__ = "bot_updates"

    bot_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bots.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    update_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
    status: Mapped[str] = mapped_column(String(50), default="processed", nullable=False)

    __table_args__ = (
        UniqueConstraint("bot_id", "update_id", name="uq_bot_update_id"),
    )


class BotUser(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    __tablename__ = "bot_users"

    bot_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bots.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    telegram_user_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
    username: Mapped[str | None] = mapped_column(String(255), nullable=True)
    first_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
    last_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
    preferred_locale: Mapped[str] = mapped_column(String(10), default="en", nullable=False)
    is_blocked: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    is_opted_out: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    points_balance: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    vip_until: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    is_verified: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    last_checkin_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    streak_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)

    bot: Mapped["Bot"] = relationship("Bot", back_populates="users")

    __table_args__ = (
        UniqueConstraint("bot_id", "telegram_user_id", name="uq_bot_telegram_user"),
    )


class BotModule(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    """
    Modular feature installed on a Telegram Bot.
    Controls backend feature activation, 3-way trigger controls, and configuration.
    """
    __tablename__ = "bot_modules"

    bot_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bots.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    module_id: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    is_enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    enable_command: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    enable_inline_button: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    enable_menu_button: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    config: Mapped[dict] = mapped_column(JSONB, default=dict, nullable=False)

    bot: Mapped["Bot"] = relationship("Bot", back_populates="modules")

    __table_args__ = (
        UniqueConstraint("bot_id", "module_id", name="uq_bot_module"),
    )


class BotReferralRecord(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    """
    Tracks subscriber-to-subscriber referrals for a specific Telegram Bot.
    """
    __tablename__ = "bot_referrals"

    bot_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bots.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    referrer_user_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
    referred_user_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
    points_awarded: Mapped[int] = mapped_column(Integer, default=10, nullable=False)
    status: Mapped[str] = mapped_column(String(30), default="active", nullable=False)

    bot: Mapped["Bot"] = relationship("Bot", back_populates="referrals")

    __table_args__ = (
        UniqueConstraint("bot_id", "referred_user_id", name="uq_bot_referred_user"),
    )


class BotRedeemCode(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    """
    Promotional gift voucher / redeem code created by the bot owner.
    Unlocks subscription VIP days, reward points, or wallet balance.
    """
    __tablename__ = "bot_redeem_codes"

    bot_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bots.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    code: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    title: Mapped[str | None] = mapped_column(String(128), nullable=True)
    reward_type: Mapped[str] = mapped_column(String(32), default="points", nullable=False)  # "subscription_days", "points", "balance"
    reward_value: Mapped[int] = mapped_column(Integer, default=10, nullable=False)
    max_uses: Mapped[int] = mapped_column(Integer, default=1, nullable=False)  # -1 for unlimited
    times_used: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)

    bot: Mapped["Bot"] = relationship("Bot", back_populates="redeem_codes")
    logs: Mapped[list["BotRedeemLog"]] = relationship("BotRedeemLog", back_populates="code_record", cascade="all, delete-orphan", lazy="selectin")

    __table_args__ = (
        UniqueConstraint("bot_id", "code", name="uq_bot_redeem_code"),
    )


class BotRedeemLog(Base, UUIDPrimaryKeyMixin):
    """
    Audit log of redeemed codes per telegram subscriber.
    Prevents duplicate redemptions by the same subscriber.
    """
    __tablename__ = "bot_redeem_logs"

    bot_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bots.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    code_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bot_redeem_codes.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    telegram_user_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
    reward_type: Mapped[str] = mapped_column(String(32), nullable=False)
    reward_value: Mapped[int] = mapped_column(Integer, nullable=False)
    claimed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)

    bot: Mapped["Bot"] = relationship("Bot", back_populates="redeem_logs")
    code_record: Mapped["BotRedeemCode"] = relationship("BotRedeemCode", back_populates="logs")

    __table_args__ = (
        UniqueConstraint("code_id", "telegram_user_id", name="uq_code_user_redemption"),
    )


class BotFile(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    """
    Digital product / file stored for delivery to Telegram subscribers.
    """
    __tablename__ = "bot_files"

    bot_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bots.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    file_id: Mapped[str] = mapped_column(String(255), nullable=False)
    file_name: Mapped[str] = mapped_column(String(255), nullable=False)
    file_type: Mapped[str] = mapped_column(String(32), default="document", nullable=False)  # document, video, audio, photo
    file_size: Mapped[int] = mapped_column(BigInteger, default=0, nullable=False)
    access_type: Mapped[str] = mapped_column(String(32), default="free", nullable=False)  # free, vip_only, points, pay
    cost: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    unlock_code: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    downloads_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)

    bot: Mapped["Bot"] = relationship("Bot", back_populates="files")

    __table_args__ = (
        UniqueConstraint("bot_id", "unlock_code", name="uq_bot_file_unlock_code"),
    )


class BotSupportTicket(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    """
    Live support ticket relay connecting subscribers with administrators via Telegram Forum Topics.
    """
    __tablename__ = "bot_support_tickets"

    bot_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("bots.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    telegram_user_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
    topic_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    subject: Mapped[str] = mapped_column(String(255), default="General Query", nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="open", nullable=False)  # open, answered, closed
    last_message_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)

    bot: Mapped["Bot"] = relationship("Bot", back_populates="support_tickets")



