from typing import Optional, List
from sqlmodel import Session, select, func

from app.models.product_template import ProductTemplate


class ProductTemplateRepository:
    """Database operations for ProductTemplate."""

    @staticmethod
    def create(session: Session, tpl: ProductTemplate) -> ProductTemplate:
        session.add(tpl)
        session.commit()
        session.refresh(tpl)
        return tpl

    @staticmethod
    def get_by_id(session: Session, tpl_id: int) -> Optional[ProductTemplate]:
        return session.get(ProductTemplate, tpl_id)

    @staticmethod
    def list_all(session: Session) -> List[ProductTemplate]:
        stmt = select(ProductTemplate).where(ProductTemplate.is_deleted == False)
        return session.exec(stmt).all()

    @staticmethod
    def list_by_ids(session, template_ids: List[int]):
        """
        Return product templates that match the provided IDs and are not deleted.
        """
        if not template_ids:
            return []

        stmt = (
            select(ProductTemplate)
            .where(ProductTemplate.id.in_(template_ids))
            .where(ProductTemplate.is_deleted == False)
        )
        return session.exec(stmt).all()

    @staticmethod
    def list_advanced(
        session: Session,
        page: int,
        limit: int,
        search: Optional[str],
        sort_by: Optional[str],
        order: Optional[str],
    ):
        stmt = select(ProductTemplate).where(ProductTemplate.is_deleted == False)

        # Case-insensitive search
        if search:
            lowered = f"%{search.lower()}%"
            stmt = stmt.where(func.lower(ProductTemplate.name).like(lowered))

        # Sorting
        valid_sort_fields = {
            "id": ProductTemplate.id,
            "name": ProductTemplate.name,
            "created_at": ProductTemplate.created_at,
            "updated_at": ProductTemplate.updated_at,
        }
        sort_col = valid_sort_fields.get(sort_by, ProductTemplate.created_at)
        stmt = stmt.order_by(sort_col.asc() if order == "asc" else sort_col.desc())

        # total count
        count_stmt = stmt.with_only_columns(func.count()).order_by(None)
        total = session.exec(count_stmt).one()

        # pagination
        items = session.exec(
            stmt.offset((page - 1) * limit).limit(limit)
        ).all()

        return items, total

    @staticmethod
    def update(session: Session, tpl: ProductTemplate) -> ProductTemplate:
        session.add(tpl)
        session.commit()
        session.refresh(tpl)
        return tpl

    @staticmethod
    def soft_delete_template(session: Session, tpl: ProductTemplate) -> None:
        tpl.is_deleted = True
        session.add(tpl)
        session.commit()
        return True