from sqlalchemy import func, and_, nullslast
from sqlalchemy.orm.query import Query as SAQuery
from db import db
from models import Project, ProjectHistoryItem
from shared.query_builders.projects_query_builder import ProjectsQueryBuilder


class RecentProjectsQueryBuilder(ProjectsQueryBuilder):
    def __init__(self, user_id: int):
        super().__init__(user_id)
        self.projects_updated_by_user_query = self.__get_projects_update_status_query()
        self.sort_expression = [nullslast(self.projects_updated_by_user_query.c.user_updated_at.desc()), "user_roles"]

    def __get_projects_update_status_query(self) -> SAQuery:
        return (
            db.session.query(
                Project.id.label("project_id"), func.max(ProjectHistoryItem.created_at).label("user_updated_at")
            )
            .outerjoin(
                ProjectHistoryItem,
                and_(ProjectHistoryItem.project_id == Project.id, ProjectHistoryItem.user_id == self.user_id),
            )
            .group_by(Project.id)
            .subquery("projects_updated_at_data")
        )

    def base_query(self, fields):
        return (
            super()
            .base_query(fields)
            .join(self.projects_updated_by_user_query, Project.id == self.projects_updated_by_user_query.c.project_id)
        )

    def only_with_my_changes(self):
        self.filter_items.append(self.projects_updated_by_user_query.c.user_updated_at.isnot(None))
        return self