kanban-app/backend/app/models/card.py

from datetime import UTC, datetime

from sqlalchemy.dialects.postgresql import JSONB

from app import db
from app.models.base import SoftDeleteMixin


class Card(db.Model, SoftDeleteMixin):
    """Card model for Kanban cards"""

    __tablename__ = "cards"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    description = db.Column(db.Text)
    closed = db.Column(db.Boolean, default=False)
    due = db.Column(db.DateTime)
    due_complete = db.Column(db.Boolean, default=False)
    pos = db.Column(db.Float)  # position for sorting
    id_short = db.Column(db.Integer)  # short ID for URLs

    # Foreign keys
    board_id = db.Column(
        db.Integer,
        db.ForeignKey("boards.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    list_id = db.Column(
        db.Integer,
        db.ForeignKey("lists.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    epic_id = db.Column(
        db.Integer, db.ForeignKey("epics.id", ondelete="SET NULL"), index=True
    )

    # Timestamps
    date_last_activity = db.Column(db.DateTime)
    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
    badges = db.Column(JSONB)  # card badges/stats
    cover = db.Column(JSONB)  # cover settings
    desc_data = db.Column(JSONB)

    # Relationships - only active records
    checklists = db.relationship(
        "Checklist",
        backref="card",
        cascade="all, delete-orphan",
        lazy="dynamic",
        primaryjoin="and_(Card.id == Checklist.card_id, Checklist.status == 'active')",
    )
    labels = db.relationship(
        "CardLabel",
        backref="card",
        cascade="all, delete-orphan",
        lazy="dynamic",
        primaryjoin="and_(Card.id == CardLabel.card_id, CardLabel.status == 'active')",
    )
    comments = db.relationship(
        "Comment",
        backref="card",
        cascade="all, delete-orphan",
        lazy="dynamic",
        primaryjoin="and_(Card.id == Comment.card_id, Comment.status == 'active')",
    )
    attachments = db.relationship(
        "FileAttachment",
        foreign_keys="FileAttachment.attachable_id",
        primaryjoin="""and_(FileAttachment.attachable_id == Card.id,
        FileAttachment.attachable_type == 'Card',
        FileAttachment.status == 'active')""",
        cascade="all, delete-orphan",
        lazy="dynamic",
    )

    # Card link relationships (self-referential many-to-many) - only active links
    child_links = db.relationship(
        "CardLink",
        foreign_keys="CardLink.parent_card_id",
        back_populates="parent_card",
        cascade="all, delete-orphan",
        lazy="dynamic",
        primaryjoin="""and_(Card.id == CardLink.parent_card_id,
        CardLink.status == 'active')""",
    )
    parent_links = db.relationship(
        "CardLink",
        foreign_keys="CardLink.child_card_id",
        back_populates="child_card",
        cascade="all, delete-orphan",
        lazy="dynamic",
        primaryjoin="""and_(Card.id == CardLink.child_card_id,
        CardLink.status == 'active')""",
    )

    def to_dict(self, include_linked=False):
        """Convert card to dictionary"""
        result = {
            "id": self.id,
            "name": self.name,
            "description": self.description,
            "closed": self.closed,
            "due": self.due.isoformat() if self.due else None,
            "due_complete": self.due_complete,
            "pos": self.pos,
            "id_short": self.id_short,
            "board_id": self.board_id,
            "list_id": self.list_id,
            "list_name": self.list.name if self.list else None,
            "epic_id": self.epic_id,
            "date_last_activity": self.date_last_activity.isoformat()
            if self.date_last_activity
            else None,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
            "badges": self.badges,
            "cover": self.cover,
            "desc_data": self.desc_data,
            "status": self.status,
            "deleted_at": self.deleted_at.isoformat() if self.deleted_at else None,
            "parent_card_name": (
                pl.parent_card.name
                if (pl := self.parent_links.first()) and pl.parent_card
                else None
            ),
        }
        if include_linked:
            result["parent_cards"] = [
                link.child_card.to_dict()
                for link in self.parent_links
                if link.child_card
            ]
            result["child_cards"] = [
                link.child_card.to_dict()
                for link in self.child_links
                if link.child_card
            ]
        return result

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


# SQLAlchemy event listeners to update Epic metrics
def update_epic_metrics_on_card_change(mapper, connection, target):
    """Update epic card_count when card epic_id changes"""
    if target.epic_id:
        from sqlalchemy import select, update

        from app.models import Epic

        card_count_stmt = select(db.func.count(Card.id)).where(
            Card.epic_id == target.epic_id
        )
        card_count = connection.execute(card_count_stmt).scalar()

        completed_list_id_stmt = select(Epic.completed_list_id).where(
            Epic.id == target.epic_id
        )
        completed_list_id = connection.execute(completed_list_id_stmt).scalar()

        completed_cards_count = 0
        if completed_list_id:
            completed_cards_stmt = select(db.func.count(Card.id)).where(
                Card.epic_id == target.epic_id, Card.list_id == completed_list_id
            )
            completed_cards_count = connection.execute(completed_cards_stmt).scalar()

        connection.execute(
            update(Epic)
            .where(Epic.id == target.epic_id)
            .values(
                metrics={
                    "card_count": card_count,
                    "completed_cards_count": completed_cards_count,
                }
            )
        )


def update_epic_metrics_on_card_insert(mapper, connection, target):
    """Update epic card_count when a card is added to an epic"""
    if target.epic_id:
        from sqlalchemy import select, update

        from app.models import Epic

        card_count_stmt = select(db.func.count(Card.id)).where(
            Card.epic_id == target.epic_id
        )
        card_count = connection.execute(card_count_stmt).scalar()

        completed_list_id_stmt = select(Epic.completed_list_id).where(
            Epic.id == target.epic_id
        )
        completed_list_id = connection.execute(completed_list_id_stmt).scalar()

        completed_cards_count = 0
        if completed_list_id:
            completed_cards_stmt = select(db.func.count(Card.id)).where(
                Card.epic_id == target.epic_id, Card.list_id == completed_list_id
            )
            completed_cards_count = connection.execute(completed_cards_stmt).scalar()

        connection.execute(
            update(Epic)
            .where(Epic.id == target.epic_id)
            .values(
                metrics={
                    "card_count": card_count,
                    "completed_cards_count": completed_cards_count,
                }
            )
        )


def update_epic_metrics_on_card_delete(mapper, connection, target):
    """Update epic card_count when a card is removed from an epic"""
    if target.epic_id:
        from sqlalchemy import select, update

        from app.models import Epic

        card_count_stmt = select(db.func.count(Card.id)).where(
            Card.epic_id == target.epic_id
        )
        card_count = connection.execute(card_count_stmt).scalar()

        completed_list_id_stmt = select(Epic.completed_list_id).where(
            Epic.id == target.epic_id
        )
        completed_list_id = connection.execute(completed_list_id_stmt).scalar()

        completed_cards_count = 0
        if completed_list_id:
            completed_cards_stmt = select(db.func.count(Card.id)).where(
                Card.epic_id == target.epic_id, Card.list_id == completed_list_id
            )
            completed_cards_count = connection.execute(completed_cards_stmt).scalar()

        connection.execute(
            update(Epic)
            .where(Epic.id == target.epic_id)
            .values(
                metrics={
                    "card_count": card_count,
                    "completed_cards_count": completed_cards_count,
                }
            )
        )


# Register event listeners
db.event.listen(Card, "after_update", update_epic_metrics_on_card_change)
db.event.listen(Card, "after_insert", update_epic_metrics_on_card_insert)
db.event.listen(Card, "after_delete", update_epic_metrics_on_card_delete)