import uuid
from datetime import date
from typing import Optional, List
from sqlalchemy.orm import Query
from flask import g

from db import db, InvalidSourceStateError
from sqlalchemy import and_, or_, exists, distinct, not_, desc
from sqlalchemy.sql import func, case
from sqlalchemy.orm import joinedload, selectinload

from models.gender import Gender
from models import (
    Territory,
    CampaignProvider,
    ProjectCampaign,
    MarketingAccount,
    user_label,
    UserProject,
    MediaPlanPhase,
)
from models.labels import Label
from models.campaign_objective import CampaignGoal, CampaignObjective
from models.campaign_territories import CampaignTerritory
from models.projects import Project
from models.projects import ProjectCampaignStatus
from models.projects_history import ProjectHistoryItem
from models.campaign import (
    Campaign,
    CampaignTypeGroup,
    CampaignLink,
    CampaignSourceType,
    CampaignPlatforms,
    CampaignTypes,
    CampaignPlacements,
    campaign_platforms_links,
    CampaignCreativeLink,
)

from campaigns.schemas import UnassignedCampaignsRequestModel
from shared.query_builders.campaigns_query_builder import CampaignsQueryBuilder
from utils.exceptions import NotFound
from utils import sorting_utils
from campaigns.exceptions import InvalidTargetStatus
from handlers.enums import CampaignWorkflowStatus


class CampaignsRepository:

    campaigns_sort_map = {
        **CampaignsQueryBuilder.campaigns_sort_map,
        "endDate": (Campaign.end_date, Campaign.id),
        "updatedDate": (Campaign.updated_at, Campaign.created_at),
    }

    def get_pending_campaigns_count(self, project_id: int):
        return (
            db.session.query(func.count(distinct(ProjectCampaign.id)))
            .select_from(Project)
            .outerjoin(
                Campaign,
                and_(
                    Campaign.project_id == Project.id,
                    Campaign.is_deleted.is_(False)
                )
            )
            .outerjoin(ProjectCampaign, ProjectCampaign.project_id == Project.id)
            .filter(
                Project.id == project_id,
                ProjectCampaign.status == ProjectCampaignStatus.PENDING.value
            )
            .one()
        )

    def get_external_campaigns_count(self, project_id: int):
        return (
            db.session.query(func.count(distinct(Campaign.id)))
            .filter(
                Campaign.source.in_(CampaignSourceType.all_external_sources()),
                Campaign.project_id == project_id,
                Campaign.is_deleted.is_(False),
                not_(and_(*Campaign.draft_campaign_filters())),
            )
            .one()
        )

    def get_internal_campaigns_count(self, project_id: int):
        return (
            db.session.query(func.count(distinct(Campaign.id)))
            .filter(
                or_(Campaign.source.is_(None), Campaign.source.in_(CampaignSourceType.all_internal_sources())),
                Campaign.project_id == project_id,
                Campaign.is_deleted.is_(False),
                not_(and_(*Campaign.draft_campaign_filters())),
            )
            .one()
        )

    def get_campaign_details_by_uuid(self, campaign_uuid: str) -> Campaign:
        return (
            db.session.query(Campaign)
            .filter_by(uuid=campaign_uuid, is_deleted=False)
            .options(
                selectinload(Campaign.history).joinedload(ProjectHistoryItem.user, innerjoin=True),
                selectinload(Campaign.territories).joinedload(CampaignTerritory.territory, innerjoin=True),
                joinedload(Campaign.genders),
                joinedload(Campaign.provider),
                joinedload(Campaign.type).joinedload(CampaignTypes.group),
                joinedload(Campaign.platforms),
            )
            .first()
        )

    def get_campaign_details(self, project_id, campaign_uuid):
        return (
            db.session.query(Campaign)
            .options(
                selectinload(Campaign.history).joinedload(ProjectHistoryItem.user, innerjoin=True),
                selectinload(Campaign.territories).joinedload(CampaignTerritory.territory, innerjoin=True),
                joinedload(Campaign.genders),
                joinedload(Campaign.provider),
                joinedload(Campaign.type).joinedload(CampaignTypes.group),
                joinedload(Campaign.platforms),
            )
            .outerjoin(
                ProjectCampaign,
                and_(
                    ProjectCampaign.campaign_id == Campaign.id,
                    ProjectCampaign.status != ProjectCampaignStatus.REJECTED.value,
                ),
            )
            .outerjoin(Project, Project.id == ProjectCampaign.project_id)
            .filter(
                and_(
                    Campaign.uuid == campaign_uuid,
                    or_(Campaign.project_id == project_id, Project.id == project_id),
                    Campaign.is_deleted.is_(False),
                )
            )
            .first()
        )

    def get_unassigned_campaign_details_by_uuid(self, campaign_uuid: str) -> Optional[Campaign]:
        return (
            db.session.query(Campaign)
            .outerjoin(
                ProjectCampaign,
                and_(
                    ProjectCampaign.campaign_id == Campaign.id,
                    ProjectCampaign.status != ProjectCampaignStatus.REJECTED.value,
                ),
            )
            .filter(
                ProjectCampaign.id.is_(None),
                Campaign.uuid == campaign_uuid,
                Campaign.is_deleted.is_(False),
                Campaign.project_id.is_(None),
            )
            .options(
                selectinload(Campaign.territories).joinedload(CampaignTerritory.territory, innerjoin=True),
                joinedload(Campaign.genders),
                joinedload(Campaign.provider),
                joinedload(Campaign.type).joinedload(CampaignTypes.group),
                joinedload(Campaign.platforms),
            )
            .first()
        )

    def get_campaign_by_external_id(self, external_id: str) -> Optional[Campaign]:
        return db.session.query(Campaign).filter_by(external_id=external_id, is_deleted=False).one_or_none()

    def get_campaigns_by_external_ids(self, external_ids: List[str]) -> Optional[Campaign]:
        return (
            db.session.query(Campaign)
            .filter(Campaign.external_id.in_(external_ids), Campaign.is_deleted == False)  # noqa: E712
            .all()
        )

    def update_campaign_schedule(self, campaign_id: int, start_date: date, end_date: Optional[date]):
        db.session.query(Campaign).filter_by(id=campaign_id).update({"start_date": start_date, "end_date": end_date})
        db.session.flush()

    def is_pending_campaign(self, campaign_id: int):
        return db.session.query(
            exists().where(
                and_(
                    ProjectCampaign.campaign_id == campaign_id,
                    ProjectCampaign.status == ProjectCampaignStatus.PENDING.value,
                )
            )
        ).scalar()

    def is_pending_campaign_for_project(self, project_id: int, campaign_id: int):
        return db.session.query(
            exists().where(
                and_(
                    ProjectCampaign.campaign_id == campaign_id,
                    ProjectCampaign.project_id == project_id,
                    ProjectCampaign.status == ProjectCampaignStatus.PENDING.value,
                )
            )
        ).scalar()

    def get_campaign_by_id(self, campaign_id: int, project_id: Optional[int] = None) -> Optional[Campaign]:
        campaign = self.get_campaigns_by_ids([campaign_id], project_id)
        if not campaign:
            return None
        return campaign.pop()

    def get_campaigns_by_ids(self, campaign_ids: List[int], project_id: Optional[int] = None) -> List[Campaign]:
        query = (
            db.session.query(Campaign)
            .filter(Campaign.id.in_(campaign_ids))
            .options(joinedload(Campaign.type).joinedload(CampaignTypes.group), joinedload(Campaign.platforms))
        )
        if project_id is not None:
            query = query.filter(Campaign.project_id == project_id)
        return query.all()

    def get_campaign_goals(self) -> List[CampaignGoal]:
        return db.session.query(CampaignGoal).all()

    def get_campaign_goal_by_objective_id(self, objective_id: int) -> CampaignGoal:
        return (
            db.session.query(CampaignGoal)
            .join(CampaignObjective, CampaignObjective.goal_id == CampaignGoal.id)
            .filter(CampaignObjective.id == objective_id).one()
        )

    def get_campaigns_for_export_by_project(self, project_id: int) -> List[Campaign]:
        return (
            db.session.query(Campaign)
            .options(
                joinedload(Campaign.project, innerjoin=True),
                selectinload(Campaign.territories).joinedload(CampaignTerritory.territory, innerjoin=True),
            )
            .filter(Campaign.project_id == project_id, Campaign.is_deleted.is_(False))
            .order_by(Campaign.name)
            .all()
        )

    def get_history_item_for_campaign(self, campaign_id: int, action_id: Optional[int] = None) -> ProjectHistoryItem:
        query = db.session.query(ProjectHistoryItem).filter_by(campaign_id=campaign_id)
        if action_id:
            query = query.filter_by(action_id=action_id)
        return query.one()

    def delete_campaign(self, campaign: Campaign, project_id: int, user_id: int):
        db.session.query(Campaign).filter_by(id=campaign.id).update({"is_deleted": True})
        db.session.query(Project).filter_by(id=project_id).update({"edit_user_id": user_id})
        db.session.commit()
        db.session.refresh(campaign)
        Campaign.delete_index(campaign)

    def delete_campaigns(self, campaigns: List[Campaign], project_id: int, user_id: int):
        db.session.query(Campaign).filter(Campaign.id.in_([c.id for c in campaigns])).update(
            {"is_deleted": True},
            synchronize_session="fetch")
        db.session.query(Project).filter_by(id=project_id).update({"edit_user_id": user_id})
        db.session.commit()
        for campaign in campaigns:
            db.session.refresh(campaign)
            Campaign.delete_index(campaign)

    def undo_delete_campaign(self, campaign: Campaign, history_item: ProjectHistoryItem):
        db.session.delete(history_item)
        campaign.is_deleted = False
        db.session.commit()
        Campaign.index_model(campaign)

    def get_campaign_type_groups(self) -> List[CampaignTypeGroup]:
        return db.session.query(CampaignTypeGroup).all()

    def get_campaign_type_groups_by_ids(self, campaign_type_group_ids: List[int]) -> List[CampaignTypeGroup]:
        categories = db.session.query(CampaignTypeGroup).filter(CampaignTypeGroup.id.in_(campaign_type_group_ids)).all()
        if len(categories) != len(campaign_type_group_ids):
            raise NotFound("Category does not exists")
        return categories

    def update_campaign_territories(self, campaign: Campaign, territories: List[int], budget_spend: int):
        db.session.query(CampaignTerritory).filter_by(campaign_id=campaign.id).delete()
        new_territories = self.create_campaign_territories(campaign, territories, budget_spend)
        db.session.add_all(new_territories)

    def update_project_schedule_date(self, project_id: int, campaign_start_date: date, campaign_end_date: date):
        project = db.session.query(Project).filter_by(id=project_id).one()

        # campaign_start_date should be used as possible end_date in case if campaign_end_date is null
        end_dates = [project.end_date, campaign_start_date, campaign_end_date]
        project.end_date = max(d for d in end_dates if d is not None)

    def update_campaign_links(self, campaign: Campaign, links: List[str]):
        [db.session.delete(link) for link in campaign.links]
        db.session.flush()
        campaign.links = [CampaignLink(link=link) for link in links]

    def update_campaign_creative_links(self, campaign: Campaign, creative_links: List[str]):
        [db.session.delete(link) for link in campaign.ad_creative_links]
        db.session.flush()
        campaign.ad_creative_links = [CampaignCreativeLink(url=url) for url in creative_links] if creative_links else []

    def safe_update_campaign_links(self, campaign: Campaign, links: List[str]):
        campaign_links = [campaign_link.link for campaign_link in campaign.links]
        new_links = list(set(links) - set(campaign_links))
        for campaign_link in campaign.links:
            if campaign_link in links:
                continue
            else:
                db.session.delete(campaign_link)
        db.session.flush()

        campaign.links = [CampaignLink(link=link) for link in new_links]

    def provider_id_for_account(self, account: int) -> Optional[int]:
        return (
            db.session.query(MarketingAccount.provider_id).filter(MarketingAccount.external_id == str(account)).scalar()
        )

    def save_campaign_changes(self, campaign: Campaign, edited_by: Optional[int] = None):
        if edited_by:
            db.session.query(Project).filter_by(id=campaign.project_id).update({"edit_user_id": edited_by})
        db.session.commit()
        db.session.refresh(campaign)
        Campaign.index_model(campaign)

    def create_campaign_territories(
        self, campaign: Campaign, territory_ids: List[int], budget: int
    ) -> List[CampaignTerritory]:
        if not territory_ids:
            return []
        budget_per_territory = budget / len(territory_ids)
        return [
            CampaignTerritory(campaign=campaign, territory_id=id, budget_spend=budget_per_territory)
            for id in territory_ids
        ]

    def get_campaign_project_territories(self, project_id: int) -> List[Territory]:
        return (
            db.session.query(Territory)
            .join(CampaignTerritory)
            .join(Campaign)
            .join(Project)
            .filter(Project.id == project_id, Campaign.is_deleted.is_(False))
            .all()
        )

    def get_campaign_providers(self, only_existing: bool) -> List[CampaignProvider]:
        base_query = db.session.query(CampaignProvider)
        if only_existing is True:
            base_query = base_query.join(Campaign, Campaign.provider_id == CampaignProvider.id)
            base_query = self.filter_only_available_campaigns(base_query)
        base_query = base_query.filter(CampaignProvider.is_prs_vendor.is_(False))
        return base_query.order_by(CampaignProvider.id).all()

    def get_campaign_providers_for_campaign(self, campaign_uuid: str) -> List[CampaignProvider]:
        return (
            db.session.query(CampaignProvider)
            .join(Campaign, Campaign.uuid == campaign_uuid)
            .filter(or_(CampaignProvider.is_prs_vendor.is_(False), CampaignProvider.id == Campaign.provider_id))
            .order_by(CampaignProvider.id)
            .all()
        )

    def get_campaign_genders(self) -> List[Gender]:
        return db.session.query(Gender).all()

    def get_campaign_genders_by_ids(self, genders_ids: List[int]) -> List[Gender]:
        if not genders_ids:
            return []
        return db.session.query(Gender).filter(Gender.id.in_(genders_ids)).all()

    def get_available_platforms_for_project(
            self,
            project_id: int,
            with_pending: bool = False,
            only_external: bool = False
    ) -> List[str]:
        project_campaign_join_conditions = [
            ProjectCampaign.project_id == Project.id,
            ProjectCampaign.status != ProjectCampaignStatus.REJECTED.value,
        ]
        if not with_pending:
            project_campaign_join_conditions.append(ProjectCampaign.status != ProjectCampaignStatus.PENDING.value)

        campaign_join_conditions = [
            Campaign.id == campaign_platforms_links.c.campaign_id,
            or_(Campaign.project_id == project_id, ProjectCampaign.campaign_id == Campaign.id).self_group(),
            Campaign.is_deleted.is_(False),
        ]
        if only_external:
            campaign_join_conditions.append(Campaign.source.in_(CampaignSourceType.all_external_sources()))

        return (
            db.session.query(CampaignPlatforms.name)
            .join(campaign_platforms_links, CampaignPlatforms.id == campaign_platforms_links.c.platform_id)
            .join(Project, Project.id == project_id)
            .outerjoin(
                ProjectCampaign,
                and_(
                    *project_campaign_join_conditions
                ),
            )
            .join(
                Campaign,
                and_(
                    *campaign_join_conditions
                ),
            )
            .group_by(CampaignPlatforms.name)
            .order_by(CampaignPlatforms.name)
            .all()
        )

    def get_project_campaign(
        self, project_id: int, campaign_uuid: str, only_pending: bool = False
    ) -> Optional[ProjectCampaign]:
        campaign_status_condition = ProjectCampaign.status != ProjectCampaignStatus.REJECTED.value
        if only_pending:
            campaign_status_condition = ProjectCampaign.status == ProjectCampaignStatus.PENDING.value
        query = db.session.query(ProjectCampaign).join(
            Campaign, ProjectCampaign.campaign_id == Campaign.id
        ).filter(
            and_(
                ProjectCampaign.project_id == project_id,
                Campaign.uuid == campaign_uuid,
                campaign_status_condition,
            )
        )
        return query.one_or_none()

    def get_project_campaigns(self, project_id: int, campaign_uuids: List[str], only_pending: bool = False):
        campaign_status_condition = ProjectCampaign.status != ProjectCampaignStatus.REJECTED.value
        if only_pending:
            campaign_status_condition = ProjectCampaign.status == ProjectCampaignStatus.PENDING.value
        query = db.session.query(ProjectCampaign).join(
            Campaign, ProjectCampaign.campaign_id == Campaign.id
        ).filter(
            and_(
                ProjectCampaign.project_id == project_id,
                Campaign.uuid.in_(campaign_uuids),
                campaign_status_condition,
            )
        )
        return query.all()

    def __get_all_project_campaigns_by_campaigns(self, campaign_uuids: List[str]):
        return (
            db.session.query(ProjectCampaign)
            .join(Campaign, ProjectCampaign.campaign_id == Campaign.id)
            .filter(
                and_(
                    ProjectCampaign.status == ProjectCampaignStatus.PENDING.value,
                    Campaign.uuid.in_(campaign_uuids),
                )
            )
            .all()
        )

    def assign_and_approve_campaigns(self, project_id: int, campaign_uuids: List[str]):
        campaigns_with_pc = (
            db.session.query(Campaign, ProjectCampaign)
            .select_from(Campaign)
            .outerjoin(
                ProjectCampaign,
                and_(ProjectCampaign.campaign_id == Campaign.id, ProjectCampaign.project_id == project_id)
            )
            .filter(Campaign.uuid.in_(campaign_uuids))
            .all()
        )

        campaign_ids = [campaign_with_pc.Campaign.id for campaign_with_pc in campaigns_with_pc]

        for campaign in campaigns_with_pc:
            if campaign.ProjectCampaign is None:
                db.session.add(
                    ProjectCampaign(
                        project_id=project_id,
                        campaign_id=campaign.Campaign.id,
                        status=ProjectCampaignStatus.APPROVED.value
                    )
                )
            else:
                campaign.ProjectCampaign.status = ProjectCampaignStatus.APPROVED.value

        self.cleanup_project_campaigns_after_campaign_approval(project_id, campaign_ids)

    def cleanup_project_campaigns_after_campaign_approval(self, project_id, campaign_ids):
        (
            db.session.query(ProjectCampaign)
            .filter(
                and_(
                    ProjectCampaign.campaign_id.in_(campaign_ids),
                    ProjectCampaign.project_id != project_id,
                )
            )
            .update({ProjectCampaign.status: ProjectCampaignStatus.REJECTED.value}, synchronize_session="fetch")
        )

    def unassign_campaign(self, project_id: int, campaign_uuid: str):
        project_campaigns = (
            db.session.query(ProjectCampaign)
            .join(Campaign, ProjectCampaign.campaign_id == Campaign.id)
            .filter(
                and_(
                    Campaign.uuid == campaign_uuid,
                    ProjectCampaign.project_id == project_id,
                )
            )
            .all()
        )
        project_campaign_ids = [project_campaign.id for project_campaign in project_campaigns]
        db.session.query(ProjectCampaign).filter(ProjectCampaign.id.in_(project_campaign_ids)).update(
            {ProjectCampaign.status: ProjectCampaignStatus.REJECTED.value},
            synchronize_session="fetch"
        )
        db.session.query(Campaign).filter_by(uuid=campaign_uuid).update({Campaign.project_id: None})
        db.session.commit()

    def label_for_marketing_account(self, account_id: int) -> Optional[Label]:
        return (
            db.session.query(Label)
            .join(MarketingAccount, Label.marketing_accounts)
            .filter(MarketingAccount.external_id == str(account_id))
            .one_or_none()
        )

    def change_workflow_status_by_campaings_uuid(self, uuids: List[uuid.UUID], status: str):
        campaigns = self.get_campaigns_by_uuids(uuids)
        for campaign in campaigns:
            self.update_workflow_status(campaign, status)

    def update_workflow_status(self, campaign: Campaign, status: str):
        status_handler_mapping = {
            CampaignWorkflowStatus.DRAFT.value: campaign.draft_transition_handler,
            CampaignWorkflowStatus.LIVE.value: campaign.live_transition_handler,
            CampaignWorkflowStatus.BOOKED.value: campaign.booked_transition_handler,
            CampaignWorkflowStatus.PAUSED.value: campaign.paused_transition_handler,
            CampaignWorkflowStatus.COMPLETED.value: campaign.completed_transition_handler,
            CampaignWorkflowStatus.PENDING_APPROVAL.value: campaign.pending_approval_transition_handler,
            CampaignWorkflowStatus.APPROVED.value: campaign.approved_transition_handler,
            CampaignWorkflowStatus.DISAPPROVED.value: campaign.disapproved_transition_handler,
        }
        handler = status_handler_mapping.get(status)
        if handler and campaign.workflow_status and campaign.workflow_status != status:
            try:
                handler.set()
            except InvalidSourceStateError:
                raise InvalidTargetStatus()

    def get_currency_for_marketing_account(self, account_id: int) -> Optional[str]:
        ma = db.session.query(MarketingAccount).filter(MarketingAccount.external_id == str(account_id)).one_or_none()
        if ma:
            return ma.currency
        return None

    def get_currency(self, campaign_uuid: str):
        return (
            db.session.query(
                func.coalesce(Project.currency, MarketingAccount.currency)
            )
            .select_from(Campaign)
            .outerjoin(MarketingAccount, Campaign.external_marketing_account_id == MarketingAccount.external_id)
            .outerjoin(Project, Project.id == Campaign.project_id)
            .filter(Campaign.uuid == campaign_uuid)
        ).scalar()

    def filter_only_available_campaigns(self, query: Query) -> Query:
        have_access_to_assigned_campaign = or_(user_label.c.label_id == Project.label_id, UserProject.id.isnot(None))
        have_access_to_unassigned_campaign = and_(MarketingAccount.id.isnot(None), Project.id.is_(None))
        return (
            query.outerjoin(
                ProjectCampaign, and_(ProjectCampaign.campaign_id == Campaign.id, ProjectCampaign.status != 2)
            )
            .outerjoin(Project, or_(Project.id == Campaign.project_id, Project.id == ProjectCampaign.project_id))
            .join(user_label, user_label.c.user_id == g.user_id)
            .outerjoin(
                UserProject, and_(UserProject.user_id == user_label.c.user_id, UserProject.project_id == Project.id)
            )
            .outerjoin(
                MarketingAccount,
                and_(
                    MarketingAccount.external_id == Campaign.external_marketing_account_id,
                    MarketingAccount.label_id == user_label.c.label_id,
                ),
            )
            .filter(
                or_(have_access_to_assigned_campaign, have_access_to_unassigned_campaign).self_group(),
                Campaign.is_deleted.is_(False),
                or_(Project.id.is_(None), Project.is_deleted.is_(False)).self_group(),
            )
        )

    def get_unassigned_campaigns_data(self, params: UnassignedCampaignsRequestModel, project: Project):
        func_currency = func.coalesce(Project.currency, MarketingAccount.currency)
        assigned_to_current_project_in_pending_or_approved_statuses = and_(
            ProjectCampaign.project_id == project.id,
            ProjectCampaign.status.in_(
                [ProjectCampaignStatus.PENDING.value, ProjectCampaignStatus.APPROVED.value]
            )
        )

        assigned_to_another_projects_campaigns_in_pending_status = and_(
            ProjectCampaign.project_id != project.id,
            ProjectCampaign.status == ProjectCampaignStatus.PENDING.value
        )

        unassigned_not_pending_campaigns = and_(
            Campaign.project_id.is_(None),
            or_(
                ProjectCampaign.id.is_(None),
                ProjectCampaign.status != ProjectCampaignStatus.PENDING.value
            )
        )

        query = (
            db.session.query(Campaign)
            .outerjoin(ProjectCampaign, ProjectCampaign.campaign_id == Campaign.id)
            .outerjoin(Project, Project.id == Campaign.project_id)
            .join(MarketingAccount, Campaign.marketing_account)
            .filter(
                Campaign.is_deleted.is_(False),
                Campaign.source.in_(CampaignSourceType.all_external_sources()),
                MarketingAccount.label_id == project.label_id,
                or_(
                    assigned_to_current_project_in_pending_or_approved_statuses,
                    unassigned_not_pending_campaigns,
                    assigned_to_another_projects_campaigns_in_pending_status,
                )
            )
            .options(
                joinedload(Campaign.territories),
                joinedload(Campaign.links),
                joinedload(Campaign.genders),
                joinedload(Campaign.provider),
                joinedload(Campaign.project).joinedload(Project.target_items, innerjoin=True),
                joinedload(Campaign.type).joinedload(CampaignTypes.group),
                joinedload(Campaign.platforms),
            )
            .group_by(
                Campaign.id,
                ProjectCampaign.id,
                ProjectCampaign.status,
                ProjectCampaign.project_id,
                ProjectCampaign.campaign_id,
                func_currency
            )
            .having(func_currency == project.currency)
        ).distinct()
        if params.freeText:
            found_ids = Campaign.search(expression=params.freeText)
            query = query.filter(Campaign.id.in_(found_ids))

        count = query.count()
        ordering_cases = case(
            [
                (
                    and_(
                        ProjectCampaign.project_id == project.id,
                        ProjectCampaign.status == ProjectCampaignStatus.PENDING.value,
                    ),
                    0
                ),
                (
                    and_(
                        ProjectCampaign.project_id == project.id,
                        ProjectCampaign.status == ProjectCampaignStatus.APPROVED.value,
                    ),
                    1
                ),
                (unassigned_not_pending_campaigns, 2),
                (assigned_to_another_projects_campaigns_in_pending_status, 3),
            ],
            else_=4
        )
        if params.sort:
            query = query.order_by(
                ordering_cases,
                *sorting_utils.sorting_expression(self.campaigns_sort_map, params.sort)
            )

        if params.limit:
            query = query.limit(params.limit)

        offset = params.offset or 0
        return query.offset(offset).all(), count

    def get_campaign_platforms_by_ids(self, campaign_platforms_ids: List[int]) -> List[CampaignPlatforms]:
        if not campaign_platforms_ids:
            return []
        return db.session.query(CampaignPlatforms).filter(CampaignPlatforms.id.in_(campaign_platforms_ids)).all()

    def get_campaign_platforms_for_labels(self, label_ids: List[int]) -> List[CampaignPlatforms]:
        return (
            db.session.query(
                CampaignPlatforms
            )
            .filter(or_(CampaignPlatforms.label_id.is_(None), CampaignPlatforms.label_id.in_(label_ids)))
            .options(joinedload(CampaignPlatforms.platform_group))
            .order_by(CampaignPlatforms.name)
            .all()
        )

    def get_campaign_platforms_by_names(self, campaign_platforms_name: List[str]) -> List[CampaignPlatforms]:
        return (
            db.session.query(CampaignPlatforms)
            .filter(or_(*[CampaignPlatforms.name.ilike(name) for name in campaign_platforms_name]))
            .all()
        )

    def get_campaign_platform_by_id(self, campaign_platform_id: int) -> CampaignPlatforms:
        return (
            db.session.query(CampaignPlatforms)
            .filter(CampaignPlatforms.id == campaign_platform_id)
            .one_or_none()
        )

    def get_campaign_type_by_name(self, campaign_type_name) -> CampaignTypes:
        return db.session.query(CampaignTypes).filter(CampaignTypes.name.ilike(campaign_type_name)).one()

    def get_campaign_type_by_id(self, campaign_type_id) -> CampaignTypes:
        return db.session.query(CampaignTypes).filter(CampaignTypes.id == campaign_type_id).one()

    def get_campaign_types(self) -> List[CampaignTypes]:
        return db.session.query(CampaignTypes).order_by(CampaignTypes.group_id).all()

    def get_campaign_groups(self) -> List[CampaignTypeGroup]:
        return (
            db.session.query(CampaignTypeGroup)
            .options(joinedload(CampaignTypeGroup.campaign_types))
            .filter(CampaignTypeGroup.is_hidden.is_(False))
            .all()
        )

    def get_campaign_budget_platforms(self, project_id: int):
        return (
            db.session.query(
                Campaign.id,
                Campaign.planned_budget,
                func.coalesce(Campaign.ecpm, 0).label("campaign_ecpm"),
                func.count(Campaign.id).label("platforms_count"),
                func.array_agg(
                    func.jsonb_build_object("id", CampaignPlatforms.id, "name", CampaignPlatforms.name)
                ).label("campaign_platforms"),
            )
            .select_from(Campaign)
            .join(campaign_platforms_links, campaign_platforms_links.c.campaign_id == Campaign.id)
            .join(CampaignPlatforms, CampaignPlatforms.id == campaign_platforms_links.c.platform_id)
            .filter(
                Campaign.is_deleted.is_(False),
                or_(Campaign.source.is_(None), Campaign.source.in_(CampaignSourceType.all_internal_sources())),
                Campaign.project_id == project_id,
            )
            .group_by(Campaign.id, Campaign.budget_spend, Campaign.ecpm)
            .all()
        )

    def get_campaign_budget_ecpms(self, project_id: int):
        return (
            db.session.query(
                Campaign.id,
                Campaign.planned_budget,
                func.coalesce(Campaign.ecpm, 0).label("ecpm")
                )
                .select_from(Campaign)
                .filter(
                    Campaign.is_deleted.is_(False),
                    or_(Campaign.source.is_(None), Campaign.source.in_(CampaignSourceType.all_internal_sources())),
                    Campaign.project_id == project_id,
                )
                .all()
        )

    def get_available_placements_for_labels(self, label_ids: List[int]) -> List[CampaignPlacements]:
        return (
            db.session.query(CampaignPlacements)
            .filter(or_(CampaignPlacements.label_id.is_(None), CampaignPlacements.label_id.in_(label_ids)))
            .all()
        )

    def get_campaign_placement_by_name_and_label_id(self, name: str, label_id: int) -> Optional[CampaignPlacements]:
        return (
            db.session.query(CampaignPlacements)
            .filter(and_(CampaignPlacements.name == name, CampaignPlacements.label_id == label_id))
            .one_or_none()
        )

    def get_campaign_placements_by_id(self, placement_ids: List[int]) -> List[CampaignPlacements]:
        if not placement_ids:
            return []
        return db.session.query(CampaignPlacements).filter(CampaignPlacements.id.in_(placement_ids)).all()

    def get_campaign_by_uuid(self, campaign_uuid: str, project_id: Optional[int] = None) -> Optional[Campaign]:
        campaign = self.get_campaigns_by_uuids([campaign_uuid], project_id)
        if not campaign:
            return None
        return campaign.pop()

    def get_campaigns_by_uuids(self, campaign_uuids: List[str], project_id: Optional[int] = None) -> List[Campaign]:
        query = (
            db.session.query(Campaign)
            .filter(Campaign.uuid.in_(campaign_uuids))
            .options(joinedload(Campaign.type).joinedload(CampaignTypes.group), joinedload(Campaign.platforms))
        )
        if project_id is not None:
            query = query.filter(Campaign.project_id == project_id)
        return query.all()

    def get_manual_not_draft_campaigns_by_media_plan_id(self, media_plan_id: int):
        return (
            db.session.query(Campaign)
            .select_from(MediaPlanPhase)
            .join(Campaign, and_(
                Campaign.media_plan_phase_id == MediaPlanPhase.id,
                or_(Campaign.source.is_(None), Campaign.source.in_(CampaignSourceType.all_internal_sources())),
                Campaign.is_deleted.is_(False),
                not_(and_(*Campaign.draft_campaign_filters())),
            ))
            .filter(MediaPlanPhase.media_plan_id == media_plan_id)
            .all()
        )

    def get_campaigns_platforms_count(self, campaign_ids):
        return (
            db.session.query(func.count(func.distinct(campaign_platforms_links.c.platform_id)).label('platforms_count'))
            .select_from(campaign_platforms_links)
            .filter(campaign_platforms_links.c.campaign_id.in_(campaign_ids))
            .first()
        )

    def get_campaigns_territories(self, campaign_ids):
        return (
            db.session.query(CampaignTerritory.territory_id, Territory.name)
            .join(Territory, CampaignTerritory.territory)
            .join(Campaign, CampaignTerritory.campaign)
            .filter(Campaign.id.in_(campaign_ids))
            .order_by(Territory.name)
            .distinct()
            .all()
        )

    def check_campaigns_rejected(self, campaign_uuids: List[uuid.UUID], project_id: int):
        return db.session.query(
            db.session.query(ProjectCampaign)
            .join(Campaign, ProjectCampaign.campaign_id == Campaign.id)
            .filter(
                and_(
                    Campaign.uuid.in_(campaign_uuids),
                    ProjectCampaign.project_id == project_id,
                    ProjectCampaign.status == ProjectCampaignStatus.REJECTED.value
                )
            )
            .exists()
        ).scalar()

    def check_campaigns_already_assigned_to_another_project(self, campaign_uuids: List[uuid.UUID], project_id: int):
        return db.session.query(
            db.session.query(Campaign)
            .filter(
                and_(
                    Campaign.uuid.in_(campaign_uuids),
                    Campaign.project_id.isnot(None),
                    Campaign.project_id != project_id
                )
            )
            .exists()
        ).scalar()

    def get_digital_campaigns_asigned_to_project(self, project_id: int):
        return (
            db.session.query(Campaign)
            .filter(
                Campaign.project_id == project_id,
                Campaign.is_deleted.is_(False),
                Campaign.source.in_(CampaignSourceType.all_external_sources())
            )
            .all()
        )

    def get_marketing_account_by_marketing_account(self, marketing_account, label_id):
        return (
            db.session.query(MarketingAccount)
            .filter(
                MarketingAccount.label_id == label_id,
                MarketingAccount.currency == marketing_account.currency,
                MarketingAccount.name == marketing_account.name,
                MarketingAccount.provider_id == marketing_account.provider_id
            )
            .one_or_none()
        )

    def get_unassigned_campaigns_after_last_run_date_by_label_id(self, label_id: int, last_run_date: Optional[str]):
        query = (
            db.session.query(Campaign)
            .join(Campaign.marketing_account)
            .filter(
                MarketingAccount.label_id == label_id,
                Campaign.is_deleted.is_(False),
                Campaign.project_id.is_(None)
            )
        )
        if last_run_date:
            query = query.filter(Campaign.created_at > last_run_date)
        return query.all()