from datetime import UTC, datetime

from sqlalchemy import and_
from sqlalchemy.dialects.postgresql import JSONB

from app import db
from app.models.base import SoftDeleteMixin
from app.models.card import Card
from app.models.epic import Epic

wiki_entity_links = db.Table(
    "wiki_entity_links",
    db.Column(
        "wiki_id",
        db.Integer,
        db.ForeignKey("wikis.id", ondelete="CASCADE"),
        primary_key=True,
    ),
    db.Column("entity_type", db.String(50), nullable=False),  # 'Card', 'Epic'
    db.Column("entity_id", db.Integer, nullable=False),  # ID of the linked entity
    db.Column("created_at", db.DateTime, default=lambda: datetime.now(UTC)),
    db.Column("linked_by", db.Integer, db.ForeignKey("users.id", ondelete="SET NULL")),
)


class Wiki(db.Model, SoftDeleteMixin):
    """Wiki model for reusable rich text content within a board"""

    __tablename__ = "wikis"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False, index=True)
    slug = db.Column(db.String(255), index=True)  # URL-friendly identifier
    content = db.Column(JSONB, nullable=False)  # Rich text content (Slate.js JSON)
    summary = db.Column(db.Text)  # Brief description/abstract
    category = db.Column(db.String(100))  # Optional categorization

    # Foreign keys
    board_id = db.Column(
        db.Integer,
        db.ForeignKey("boards.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    created_by = db.Column(
        db.Integer,
        db.ForeignKey("users.id", ondelete="SET NULL"),
    )
    updated_by = db.Column(
        db.Integer,
        db.ForeignKey("users.id", ondelete="SET NULL"),
    )

    # Timestamps
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(UTC))
    updated_at = db.Column(
        db.DateTime,
        default=lambda: datetime.now(UTC),
        onupdate=lambda: datetime.now(UTC),
    )

    # JSON fields
    tags = db.Column(JSONB)  # List of tags for organization: ["security", "api"]

    # Relationships
    board = db.relationship("Board", backref="wikis")
    creator = db.relationship(
        "User", foreign_keys=[created_by], backref="created_wikis"
    )
    updater = db.relationship(
        "User", foreign_keys=[updated_by], backref="updated_wikis"
    )

    # Linked entities relationships
    linked_cards = db.relationship(
        "Card",
        secondary=wiki_entity_links,
        primaryjoin=and_(
            wiki_entity_links.c.wiki_id == id, wiki_entity_links.c.entity_type == "card"
        ),
        secondaryjoin=wiki_entity_links.c.entity_id == Card.id,
        viewonly=True,
    )
    linked_epics = db.relationship(
        "Epic",
        secondary=wiki_entity_links,
        primaryjoin=and_(
            wiki_entity_links.c.wiki_id == id, wiki_entity_links.c.entity_type == "epic"
        ),
        secondaryjoin=wiki_entity_links.c.entity_id == Epic.id,
        viewonly=True,
    )

    def to_dict(self):
        """Convert wiki to dictionary"""
        return {
            "id": self.id,
            "name": self.name,
            "slug": self.slug,
            "content": self.content,
            "summary": self.summary,
            "category": self.category,
            "board_id": self.board_id,
            "tags": self.tags or [],
            "created_by": self.created_by,
            "updated_by": self.updated_by,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
            "status": self.status,
            "deleted_at": self.deleted_at.isoformat() if self.deleted_at else None,
        }

    def __repr__(self):
        return f"<Wiki {self.name}>"