from __future__ import annotations
from datetime import datetime
from sqlalchemy import String, Integer, DateTime, func, Index, Text, Boolean
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.ext.associationproxy import association_proxy
from shared.db.base import Base

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    email: Mapped[str] = mapped_column(String(255), unique=True, index=True, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=func.now())
    last_login_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    # Ghost membership linkage
    ghost_id: Mapped[str | None] = mapped_column(String(64), unique=True, index=True, nullable=True)
    name: Mapped[str | None] = mapped_column(String(255), nullable=True)
    ghost_status: Mapped[str | None] = mapped_column(String(50), nullable=True)  # free, paid, comped
    ghost_subscribed: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, server_default=func.true())
    ghost_note: Mapped[str | None] = mapped_column(Text, nullable=True)
    avatar_image: Mapped[str | None] = mapped_column(Text, nullable=True)
    stripe_customer_id: Mapped[str | None] = mapped_column(String(255), index=True, nullable=True)
    ghost_raw: Mapped[dict | None] = mapped_column(JSONB, nullable=True)

    # Author profile fields (merged from Ghost Author)
    slug: Mapped[str | None] = mapped_column(String(191), unique=True, index=True, nullable=True)
    bio: Mapped[str | None] = mapped_column(Text, nullable=True)
    profile_image: Mapped[str | None] = mapped_column(Text, nullable=True)
    cover_image: Mapped[str | None] = mapped_column(Text, nullable=True)
    website: Mapped[str | None] = mapped_column(Text, nullable=True)
    location: Mapped[str | None] = mapped_column(Text, nullable=True)
    facebook: Mapped[str | None] = mapped_column(Text, nullable=True)
    twitter: Mapped[str | None] = mapped_column(Text, nullable=True)
    is_admin: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, server_default=func.false())

    # Relationships to Ghost-related entities

    user_newsletters = relationship("UserNewsletter", back_populates="user", cascade="all, delete-orphan", lazy="selectin")
    newsletters = association_proxy("user_newsletters", "newsletter")
    labels = relationship("GhostLabel", secondary="user_labels", back_populates="users", lazy="selectin")
    subscriptions = relationship("GhostSubscription", back_populates="user", cascade="all, delete-orphan", lazy="selectin")

    # Cross-domain reverse relationships removed (liked_products, liked_posts,
    # cart_items) — those tables live in different domain DBs

    __table_args__ = (
        Index("ix_user_email", "email", unique=True),
    )

    def __repr__(self) -> str:
        return f"<User {self.id} {self.email}>"
