from datetime import date, datetime, timezone, timedelta
from typing import List, Optional
from sqlalchemy import and_, exists, or_, distinct, extract, literal, not_
from sqlalchemy.sql import func
from sqlalchemy.orm.query import Query as SAQuery
from sqlalchemy.orm import aliased, joinedload
from models import (
    Artist,
    Playlist,
    PRSBudgetGroup,
    PRSProjectBudget,
    PRSPurchaseOrder,
    MarketingAccount,
    PRSBudgetCategory,
    Linkfire,
    LinkfireProject,
    LinkfireCampaign,
    ArtistTeamUser,
    ArtistTeam,
)
from models.campaign import CampaignSourceType
from models.project_entity_type import ProjectEntityType, ProjectEntityAddType
from projects.models import UsersSearchModel
from sqlalchemy.util import OrderedSet

from db import db
from shared.queries import Queries as SharedQueries
from models.campaign import Campaign
from models.campaign_territories import CampaignTerritory
from models.projects import Project, ProjectTargetItem, ProjectCampaign, ProjectCampaignStatus
from models.projects_history import ProjectHistoryItem, HistoryActionType
from models.user import user_label, User
from models.user_project import UserProject, UserProjectRoles
from models.territories import Territory
from models.labels import Label
from models.product_family import ProductFamily, ProjectProductFamily
from models.media_plans import MediaPlan
from models.release_types import ReleaseType
from models.recent_search_item import RecentSearchItem, RecentSearchItemType
from shared.query_builders.projects_query_builder import ProjectsQueryBuilder, DynamicFields
from constants.labels_constants import TEST_UK_LABEL_ID, TEST_US_LABEL_ID


class ProjectsRepository:
    def media_plan_exist(self, project_id: int, media_plan_id: int):
        return db.session.query(
            db.session.query(MediaPlan)
            .filter(MediaPlan.project_id == project_id, MediaPlan.id == media_plan_id)
            .exists()
        ).scalar()

    def media_plan_with_name_exist(self, project_id: int, name: str, except_id: int = None):
        filters = [MediaPlan.project_id == project_id, MediaPlan.name == name]
        if except_id:
            filters.append(not_(MediaPlan.id == except_id))
        return db.session.query(
            db.session.query(MediaPlan)
            .filter(*filters)
            .exists()
        ).scalar()

    def get_name_autogeneration_info(self, media_plan_id: int):
        return (
            db.session.query(
                Project.gras_project_code,
                Project.id.label("project_id"),
                Label.abbreviation,
                Label.country,
                MediaPlan.release_name,
                ReleaseType.name.label("release_type_name"))
            .join(Project, Project.id == MediaPlan.project_id)
            .join(Label, Label.id == Project.label_id)
            .outerjoin(ReleaseType, ReleaseType.id == MediaPlan.release_type_id)
            .filter(MediaPlan.id == media_plan_id, Project.is_deleted.is_(False))
            .one_or_none()
        )

    def get_project_by_id(self, project_id: int) -> Optional[Project]:
        return db.session.query(Project).filter(Project.id == project_id, Project.is_deleted.is_(False)).one_or_none()

    def project_by_id_exist(self, project_id: int):
        return db.session.query(
            db.session.query(Project)
            .filter(Project.id == project_id, Project.is_deleted.is_(False))
            .exists()
        ).scalar()

    def get_project_currency(self, project_id: int) -> str:
        return db.session.query(Project.currency).filter(Project.id == project_id).scalar()

    def get_projects_by_GRAS_id(self, gras_id: str, currency: str = None) -> List[Project]:
        query = (
            db.session.query(Project).filter(Project.gras_project_code == gras_id)
        )
        if currency is not None:
            query = query.filter(Project.currency == currency)
        return query.all()

    def get_project_by_PRS_id(self, dsp_id: str, currency: str = None) -> Optional[Project]:
        query = (
            db.session.query(Project)
            .filter(or_(Project.prs_project_code == dsp_id, Project.ccp_project_code == dsp_id))
        )
        if currency is not None:
            query = query.filter(Project.currency == currency)
        return query.one_or_none()

    def get_territories_by_project_id(self, project_id: int):
        return (
            db.session.query(CampaignTerritory.territory_id, Territory.name)
            .join(Territory, CampaignTerritory.territory)
            .join(Campaign, CampaignTerritory.campaign)
            .filter(Campaign.project_id == project_id, Campaign.is_deleted.is_(False))
            .order_by(Territory.name)
            .distinct()
            .all()
        )

    def get_budgets_groups_for_project_id(self, project_id: int):
        budgets_data = (
            db.session.query(PRSBudgetGroup.id, func.sum(PRSProjectBudget.value).label("budget"))
            .select_from(Project)
            .join(PRSProjectBudget, Project.prs_project_budgets)
            .join(PRSBudgetGroup, PRSProjectBudget.prs_budget_group)
            .filter(Project.id == project_id)
            .group_by(PRSBudgetGroup.id)
            .subquery()
        )
        allocations_data = (
            db.session.query(PRSBudgetGroup.id, func.sum(PRSPurchaseOrder.total_amount).label("allocation"))
            .select_from(Project)
            .outerjoin(
                PRSPurchaseOrder,
                and_(
                    Project.id == PRSPurchaseOrder.project_id,
                    PRSPurchaseOrder.parent_po_number.is_(None),
                    PRSPurchaseOrder.is_under_uncommited_blanket.is_(False),
                ),
            )
            .join(PRSBudgetGroup, PRSPurchaseOrder.prs_budget_group)
            .filter(Project.id == project_id)
            .group_by(PRSBudgetGroup.id)
            .subquery()
        )

        return (
            db.session.query(
                PRSBudgetGroup.id.label("id"),
                PRSBudgetGroup.name.label("name"),
                func.coalesce(budgets_data.c.budget, 0.0).label("budget"),
                func.coalesce(allocations_data.c.allocation, 0.0).label("allocation"),
            )
            .outerjoin(budgets_data, budgets_data.c.id == PRSBudgetGroup.id)
            .outerjoin(allocations_data, allocations_data.c.id == PRSBudgetGroup.id)
            .filter(or_(budgets_data.c.budget.isnot(None), allocations_data.c.allocation.isnot(None)))
            .all()
        )

    def get_budget_categories_by_project_id(self, project_id: int):
        budgets_data = (
            db.session.query(
                PRSBudgetGroup.id.label("group_id"),
                PRSBudgetCategory.id.label("category_id"),
                func.sum(PRSProjectBudget.value).label("budget"),
            )
            .select_from(Project)
            .join(PRSProjectBudget, Project.prs_project_budgets)
            .join(PRSBudgetGroup, PRSProjectBudget.prs_budget_group)
            .join(PRSBudgetCategory, PRSProjectBudget.prs_budget_category)
            .filter(Project.id == project_id)
            .group_by(PRSBudgetCategory.id, PRSBudgetGroup.id)
            .subquery()
        )
        allocations_data = (
            db.session.query(
                PRSBudgetGroup.id.label("group_id"),
                PRSBudgetCategory.id.label("category_id"),
                func.sum(PRSPurchaseOrder.total_amount).label("allocation"),
            )
            .select_from(Project)
            .outerjoin(
                PRSPurchaseOrder,
                and_(
                    Project.id == PRSPurchaseOrder.project_id,
                    PRSPurchaseOrder.parent_po_number.is_(None),
                    PRSPurchaseOrder.is_under_uncommited_blanket.is_(False),
                ),
            )
            .join(PRSBudgetGroup, PRSPurchaseOrder.prs_budget_group)
            .join(PRSBudgetCategory, PRSPurchaseOrder.prs_budget_category)
            .filter(Project.id == project_id)
            .group_by(PRSBudgetCategory.id, PRSBudgetGroup.id)
            .subquery()
        )
        return (
            db.session.query(
                PRSBudgetGroup.id.label("group_id"),
                PRSBudgetGroup.name.label("group_name"),
                PRSBudgetCategory.id.label("category_id"),
                PRSBudgetCategory.name.label("category_name"),
                func.coalesce(budgets_data.c.budget, 0.0).label("budget"),
                func.coalesce(allocations_data.c.allocation, 0.0).label("allocation"),
            )
            .select_from(budgets_data)
            .join(
                allocations_data,
                and_(
                    allocations_data.c.category_id == budgets_data.c.category_id,
                    allocations_data.c.group_id == budgets_data.c.group_id,
                ),
                full=True,
            )
            .join(
                PRSBudgetGroup,
                or_(PRSBudgetGroup.id == budgets_data.c.group_id, PRSBudgetGroup.id == allocations_data.c.group_id),
            )
            .join(
                PRSBudgetCategory,
                or_(
                    PRSBudgetCategory.id == budgets_data.c.category_id,
                    PRSBudgetCategory.id == allocations_data.c.category_id,
                ),
            )
            .filter(or_(budgets_data.c.budget.isnot(None), allocations_data.c.allocation.isnot(None)))
            .all()
        )

    def get_deleted_project_by_id(self, project_id: int) -> Optional[Project]:
        return db.session.query(Project).filter(Project.id == project_id, Project.is_deleted.is_(True)).one_or_none()

    def get_shared_status(self, project_id: int, user_id: int) -> str:
        return (
            db.session.query(SharedQueries.project_shared_status_query(user_id))
            .filter(Project.id == project_id)
            .scalar()
        )

    def get_project_linkfire_links_count(self, project_id: int):
        campaign_links = aliased(Linkfire)
        project_links = aliased(Linkfire)
        return (
            db.session.query(
                func.coalesce(func.count(distinct(campaign_links.id)), 0).label("campaign_links"),
                func.coalesce(func.count(distinct(project_links.id)), 0).label("project_links"),
            )
            .select_from(Project)
            .outerjoin(Campaign, Project.campaigns)
            .outerjoin(LinkfireProject, Project.linkfire_projects)
            .outerjoin(LinkfireCampaign, Campaign.linkfire_campaigns)
            .outerjoin(campaign_links, LinkfireCampaign.linkfire_id == campaign_links.id)
            .outerjoin(project_links, LinkfireProject.linkfire_id == project_links.id)
            .filter(Project.id == project_id)
            .one()
        )

    def __get_user_project_user_ids_query(self, project_id: int) -> SAQuery:
        return db.session.query(UserProject.user_id).filter_by(project_id=project_id)

    def get_project_prs_id(self, project_id: int) -> str:
        return (
            db.session.query(
                func.dsp_id(Project.source, func.coalesce(Project.prs_project_code, Project.ccp_project_code))
            )
            .filter(Project.id == project_id)
            .one_or_none()
        )

    def get_user_project_user_ids_count(self, project_id: int) -> int:
        return self.__get_user_project_user_ids_query(project_id).count()

    def get_unique_project_users_count(self, project_id: int) -> int:
        return (
            db.session.query(func.count(func.distinct(UserProject.user_id)))
            .filter_by(project_id=project_id)
        ).scalar()

    def is_project_exists(self, project_id: int) -> bool:
        return db.session.query(exists().where(and_(Project.id == project_id, Project.is_deleted.is_(False)))).scalar()

    def get_user_project(self, project_id: int, user_id: int) -> UserProject:
        return (
            db.session.query(UserProject)
            .filter(
                UserProject.project_id == project_id,
                UserProject.user_id == user_id,
                UserProject.role != UserProjectRoles.APPROVER.value
            )
            .one_or_none()
        )

    def get_user_project_by_role(self, project_id: int, user_id: int, role_id: int) -> UserProject:
        return (
            db.session.query(UserProject)
            .filter(
                UserProject.project_id == project_id,
                UserProject.user_id == user_id,
                UserProject.role == role_id
            )
            .one_or_none()
        )

    def is_user_project_exists(self, project_id: int, user_id: int) -> bool:
        query = db.session.query(UserProject).filter(
            UserProject.project_id == project_id, UserProject.user_id == user_id
        )
        return db.session.query(literal(True)).filter(query.exists()).scalar()

    def get_user_projects_for_users_except_approver(self, project_id: int, users_ids: List[int]):
        return db.session.query(UserProject).filter(
            UserProject.project_id == project_id,
            UserProject.user_id.in_(users_ids),
            UserProject.role != UserProjectRoles.APPROVER.value
        ).all()

    def is_users_projects_not_approver_exists(self, project_id: int, users_ids: List[int]) -> bool:
        query = db.session.query(UserProject).filter(
            UserProject.project_id == project_id,
            UserProject.user_id.in_(users_ids),
            UserProject.role != UserProjectRoles.APPROVER.value
        )
        return db.session.query(literal(True)).filter(query.exists()).scalar()

    def get_all_user_project_ids(self, user_id: int) -> List[int]:
        return (
            db.session.query(Project.id)
            .outerjoin(UserProject, UserProject.project_id == Project.id)
            .outerjoin(user_label, user_label.c.label_id == Project.label_id)
            .filter(or_(UserProject.user_id == user_id, user_label.c.user_id == user_id))
            .group_by(Project.id)
            .order_by(Project.id)
        ).all()

    def get_history_item(self, project_id: int, action_id: Optional[int] = None) -> Optional[ProjectHistoryItem]:
        query = db.session.query(ProjectHistoryItem).filter_by(project_id=project_id)
        if action_id:
            query = query.filter_by(action_id=action_id)
        return query.one_or_none()

    def add_or_update_project(self, project: Project):
        db.session.add(project)
        db.session.commit()
        Project.index_model(project)

    def refresh_project(self, project):
        db.session.refresh(project)

    def delete_existed_user_project(self, user_id: int, project_id: int):
        user_project = self.get_user_project(user_id=user_id, project_id=project_id)
        if user_project:
            db.session.delete(user_project)

    def search_users(self, project_id: int, search: Optional[str]) -> List[UsersSearchModel]:
        artist_team_users = (
            db.session.query(ArtistTeamUser.user_id)
            .join(ArtistTeam, ArtistTeamUser.artist_team_id == ArtistTeam.id)
            .join(
                Project,
                and_(
                    Project.label_id == ArtistTeam.label_id,
                    Project.id == project_id,
                    Project.is_confidential.is_(False),
                ),
            )
            .join(Artist, and_(ArtistTeam.artist_id == Artist.id, Artist.is_unknown.is_(False)))
            .join(
                ProjectTargetItem,
                and_(
                    ProjectTargetItem.project_id == project_id,
                    ProjectTargetItem.is_deleted.is_(False),
                    ProjectTargetItem.entity_type == ProjectEntityType.PRIMARY_ARTIST.value,
                    ProjectTargetItem.add_type == ProjectEntityAddType.LOCKED.value,
                    ProjectTargetItem.entity_id == ArtistTeam.artist_id,
                ),
            )
        )

        label_admin_users = (
            db.session.query(User.id)
            .join(Project, Project.id == project_id)
            .join(user_label, user_label.c.user_id == User.id)
            .filter(User.is_admin.is_(True), user_label.c.label_id == Project.label_id)
        )

        unassigned_users = (
            db.session.query(
                User.id.label("id"),
                User.email.label("email"),
                User.name.label("name"),
                func.json_strip_nulls(func.json_agg(func.json_build_object("id", Label.id, "name", Label.name))).label(
                    "labels"
                ),
            )
            .outerjoin(user_label, user_label.c.user_id == User.id)
            .outerjoin(Label, Label.id == user_label.c.label_id)
            .filter(User.id.notin_(list(artist_team_users) + list(label_admin_users)))
            .group_by(User.id, User.email)
            .order_by(User.id)
        )

        if search:
            unassigned_users = unassigned_users.filter(
                or_(User.email.ilike("%{}%".format(search)), Label.name.ilike("%{}%".format(search)))
            )

        return [UsersSearchModel(user) for user in unassigned_users.all()]

    def get_team_members_by_project(self, project: Project) -> List[User]:
        team = (
            db.session.query(User)
            .join(UserProject, User.user_project)
            .filter(UserProject.project_id == project.id)
            .order_by(UserProject.role, User.name)
            .all()
        )

        users = list(OrderedSet(team))
        return users

    def get_user_projects_by_project(self, project_id: int) -> List[User]:
        users = (
            db.session.query(
                User.id,
                User.name,
                User.email,
                UserProject.role
            )
            .join(UserProject, User.user_project)
            .filter(UserProject.project_id == project_id)
            .order_by(User.name)
            .all()
        )

        return users

    def __get_product_families(self, project_id: int, limit: Optional[int] = None) -> SAQuery:
        query = (
            db.session.query(ProductFamily)
            .join(Project, Project.id == project_id)
            .join(ProjectProductFamily, Project.id == ProjectProductFamily.project_id)
            .filter(ProductFamily.id == ProjectProductFamily.product_family_id)
            .options(joinedload(ProductFamily.tracks))
        )
        if limit is not None:
            query = query.limit(limit)
        return query

    def get_external_campaigns(self, project_id: int) -> List[Campaign]:
        return (
            db.session.query(Campaign).filter(and_(Campaign.project_id == project_id, Campaign.external_id.isnot(None)))
        ).all()

    def get_external_primary_artists(self, project_id: int) -> List[Artist]:
        return (
            db.session.query(Artist).join(
                ProjectTargetItem,
                and_(
                    ProjectTargetItem.entity_id == Artist.id,
                    ProjectTargetItem.project_id == project_id,
                    ProjectTargetItem.entity_type == ProjectEntityType.PRIMARY_ARTIST.value,
                ),
            )
        ).all()

    def get_target_playlists(self, project_id: int) -> List[Playlist]:
        return (
            db.session.query(Playlist).join(
                ProjectTargetItem,
                and_(
                    ProjectTargetItem.entity_id == Playlist.id,
                    ProjectTargetItem.project_id == project_id,
                    ProjectTargetItem.entity_type == ProjectEntityType.PLAYLIST.value,
                ),
            )
        ).all()

    def get_product_families(self, project_id: int, limit: Optional[int] = None) -> List[ProductFamily]:
        return self.__get_product_families(project_id, limit).all()

    def get_product_families_count(self, project_id: int) -> int:
        return self.__get_product_families(project_id).count()

    def get_dsp_project_code_with_prefix(self, project_id: int):
        return (
            db.session.query(
                func.dsp_id(Project.source, func.coalesce(Project.prs_project_code, Project.ccp_project_code)).label(
                    "prs_code"
                )
            )
            .filter(Project.id == project_id)
            .scalar()
        )

    def get_projects_with_artists(
        self, artists_ids: List[str], start_date: date, marketing_account_id: str, currency: str = None
    ) -> List[Project]:
        time_diff = extract("epoch", start_date) - extract("epoch", Project.initial_start_date)

        filters = [
            Artist.external_id.in_(artists_ids),
            or_(
                ProjectCampaign.status != ProjectCampaignStatus.REJECTED.value, ProjectCampaign.status.is_(None)
            ).self_group(),
            time_diff > 0,
            ProjectTargetItem.entity_type == ProjectEntityType.PRIMARY_ARTIST.value,
            Project.is_deleted.is_(False),
            Project.label_id.notin_([TEST_UK_LABEL_ID, TEST_US_LABEL_ID])
        ]

        if currency is not None:
            filters.append(
                Project.currency == currency
            )

        return (
            db.session.query(Project)
            .join(ProjectTargetItem, Project.id == ProjectTargetItem.project_id)
            .join(Artist, Artist.id == ProjectTargetItem.entity_id)
            .join(Label, Project.label_id == Label.id)
            .join(
                MarketingAccount,
                and_(MarketingAccount.label_id == Label.id, MarketingAccount.external_id == str(marketing_account_id)),
            )
            .outerjoin(ProjectCampaign)
            .filter(
                and_(
                    *filters
                )
            )
            .order_by(time_diff.asc())
            .all()
        )

    def delete_users_from_artists_projects(self, users_ids: List[int], artists_external_ids: List[str]):
        ids_query = (
            db.session.query(UserProject.id)
            .join(Project, UserProject.project_id == Project.id)
            .join(
                ProjectTargetItem,
                and_(
                    ProjectTargetItem.project_id == Project.id,
                    ProjectTargetItem.is_deleted.is_(False),
                    ProjectTargetItem.entity_type == ProjectEntityType.PRIMARY_ARTIST.value,
                    ProjectTargetItem.add_type == ProjectEntityAddType.LOCKED.value,
                ),
            )
            .join(Artist,
                  and_(ProjectTargetItem.entity_id == Artist.id, Artist.external_id.in_(artists_external_ids)))
            .filter(
                Project.is_confidential.is_(False),
                UserProject.user_id.in_(users_ids),
                UserProject.role != UserProjectRoles.APPROVER.value,
            )
        )

        return (
            db.session.query(UserProject)
            .filter(UserProject.id.in_(ids_query.subquery()))
            .delete(synchronize_session="fetch")
        )

    def set_projects_without_project_teams_unclaimed(self, artist_id: int, label_id: int):
        project_ids = (
            db.session.query(Project.id)
            .select_from(Project)
            .join(
                ProjectTargetItem,
                and_(
                    ProjectTargetItem.project_id == Project.id,
                    ProjectTargetItem.is_deleted.is_(False),
                    ProjectTargetItem.entity_type == ProjectEntityType.PRIMARY_ARTIST.value,
                    ProjectTargetItem.add_type == ProjectEntityAddType.LOCKED.value,
                )
            )
            .join(Artist, and_(ProjectTargetItem.entity_id == Artist.id, Artist.id == artist_id))
            .outerjoin(ArtistTeam, and_(Artist.id == ArtistTeam.artist_id, ArtistTeam.label_id == label_id))
            .outerjoin(ArtistTeamUser, ArtistTeamUser.artist_team_id == ArtistTeam.id)
            .outerjoin(UserProject, UserProject.project_id == Project.id)
            .filter(
                Project.label_id == label_id,
                UserProject.id.is_(None),
                or_(ArtistTeam.id.is_(None), ArtistTeamUser.id.is_(None)),
                Project.is_claimed.is_(True),
            )
            .all()
        )

        db.session.query(Project).filter(Project.id.in_(project_ids)).update(
            {Project.is_claimed: False,
             Project.updated_at: datetime.now()},
            synchronize_session="fetch"
        )

    def get_projects_without_project_teams_by_artists_external_ids(
            self,
            artists_ids: List[str],
            label_id: int
    ):
        return (
            db.session.query(Project.id)
            .select_from(Project)
            .join(
                ProjectTargetItem,
                and_(
                    ProjectTargetItem.project_id == Project.id,
                    ProjectTargetItem.is_deleted.is_(False),
                    ProjectTargetItem.entity_type == ProjectEntityType.PRIMARY_ARTIST.value,
                    ProjectTargetItem.add_type == ProjectEntityAddType.LOCKED.value,
                )
            )
            .join(Artist, and_(ProjectTargetItem.entity_id == Artist.id, Artist.external_id.in_(artists_ids)))
            .outerjoin(ArtistTeam, and_(Artist.id == ArtistTeam.artist_id, ArtistTeam.label_id == label_id))
            .outerjoin(UserProject, UserProject.project_id == Project.id)
            .filter(
                Project.label_id == label_id,
                UserProject.id.is_(None),
                or_(ArtistTeam.id.is_(None), ArtistTeamUser.id.is_(None)),
                Project.is_claimed.is_(True),
            )
            .all()
        )

    def unclaim_projects(self, projects_ids):
        db.session.query(Project).filter(Project.id.in_(projects_ids)).update(
            {Project.is_claimed: False},
            synchronize_session="fetch"
        )

    def get_projects_for_artists_from_label(self, artists_ids: List[str], label_id: int):
        return (
            db.session.query(Project)
            .select_from(Project)
            .join(
                ProjectTargetItem,
                and_(
                    ProjectTargetItem.project_id == Project.id,
                    ProjectTargetItem.is_deleted.is_(False),
                    ProjectTargetItem.entity_type == ProjectEntityType.PRIMARY_ARTIST.value,
                    ProjectTargetItem.add_type == ProjectEntityAddType.LOCKED.value,
                )
            )
            .join(Artist, and_(ProjectTargetItem.entity_id == Artist.id, Artist.external_id.in_(artists_ids)))
            .join(ArtistTeam, and_(Artist.id == ArtistTeam.artist_id, ArtistTeam.label_id == label_id))
            .join(ArtistTeamUser, ArtistTeamUser.artist_team_id == ArtistTeam.id)
            .filter(
                Project.label_id == label_id,
                ArtistTeamUser.id.isnot(None),
                Project.is_claimed.is_(False),
                Project.is_confidential.is_(False)
            )
            .all()
        )

    def set_projects_claimed(self, projects_ids: List[int]):
        db.session.query(Project).filter(Project.id.in_(projects_ids)).update(
            {Project.is_claimed: True},
            synchronize_session="fetch"
        )

    def touch_project_last_edit(self, project_id: int):
        db.session.query(Project).filter(Project.id == project_id).update(
            {Project.last_edit_at: datetime.now(timezone.utc)},
            synchronize_session="fetch"
        )

    def touch_projects_last_edit(self, projects_ids: List[int]):
        db.session.query(Project).filter(Project.id.in_(projects_ids)).update(
            {Project.last_edit_at: datetime.now(timezone.utc)},
            synchronize_session="fetch"
        )

    def get_gras_project_campaign_schedule(self, project_id: int):
        return (
            db.session.query(
                func.min(Campaign.start_date).label("earliest_start_date"),
                func.max(func.coalesce(Campaign.end_date, Campaign.start_date)).label("latest_end_date"),
            )
            .select_from(Campaign)
            .join(
                ProjectCampaign,
                and_(
                    ProjectCampaign.campaign_id == Campaign.id,
                    ProjectCampaign.project_id == project_id,
                    ProjectCampaign.status == ProjectCampaignStatus.APPROVED.value,
                ),
            )
            .filter(Campaign.source.in_(CampaignSourceType.all_external_sources()), Campaign.is_deleted.is_(False))
            .group_by(ProjectCampaign.project_id)
            .one_or_none()
        )

    def get_recent_viewed_projects(self, params, user_id):
        query = (
            db.session.query(Project)
            .join(
                RecentSearchItem,
                and_(
                    RecentSearchItem.project_id == Project.id,
                    RecentSearchItem.user_id == user_id,
                )
            )
            .filter(
                RecentSearchItem.type == RecentSearchItemType.RECENTLY_VIEWED_PROJECT.value,
                Project.is_deleted.is_(False)
            )
        )
        if params.label:
            query = query.filter(Project.label_id == params.label)
        query = query.order_by(RecentSearchItem.searched_at.desc())

        return query.limit(params.limit).all()

    def get_new_projects(self, params, user_id: int):
        builder = ProjectsQueryBuilder(user_id)
        builder.dynamic_fields = []
        builder.only_accessible_projects_by_artist().limit_to(params.limit).sort_by("-created_at")
        if params.label:
            builder.filtered_by_labels([params.label])
        return builder.items_query().all()

    def get_projects_from_label_created_after_last_run_date(self, label_id: int, last_run_date: Optional[str]):
        query = (
            db.session.query(Project)
            .filter(
                Project.is_deleted.is_(False),
                Project.label_id == label_id
            )
        )
        if last_run_date:
            query = query.filter(Project.created_at > last_run_date)

        return query.all()

    def get_max_created_at_date_by_label_id(self, label_id: int):
        return (
            db.session.query(func.max(Project.created_at))
            .filter(Project.label_id == label_id)
            .scalar()
        )