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

from app.models.project_master import ProjectMaster
from app.models.product_image import ProductImage

class ProjectRepository:

    @staticmethod
    def create_project(session: Session, project: ProjectMaster) -> ProjectMaster:
        session.add(project)
        session.commit()
        session.refresh(project)
        return project
    
    @staticmethod
    def add_project_images(db: Session, mapping: ProductImage):
        db.add(mapping)
        db.commit()
        db.refresh(mapping)
        return mapping

    @staticmethod
    def get_project(session: Session, project_id: int) -> Optional[ProjectMaster]:
        return session.get(ProjectMaster, project_id)

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

        if search:
            like = f"%{search.lower()}%"
            stmt = stmt.where(
                func.lower(ProjectMaster.project_name).like(like)
                | func.lower(ProjectMaster.client_name).like(like)
            )

        valid_sort_fields = {
            "id": ProjectMaster.id,
            "project_name": ProjectMaster.project_name,
            "client_name": ProjectMaster.client_name,
            "created_at": ProjectMaster.created_at,
            "updated_at": ProjectMaster.updated_at,
        }
        sort_col = valid_sort_fields.get(sort_by, ProjectMaster.created_at)

        stmt = stmt.order_by(sort_col.asc() if order == "asc" else sort_col.desc())

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

        rows = session.exec(
            stmt.offset((page - 1) * limit).limit(limit)
        ).all()

        return rows, total

    @staticmethod
    def update_project(session: Session, project: ProjectMaster) -> ProjectMaster:
        session.add(project)
        session.commit()
        session.refresh(project)
        return project

    @staticmethod
    def soft_delete_project(session: Session, project: ProjectMaster) -> None:
        project.is_deleted = True
        session.add(project)
        session.commit()
        return True