from datetime import date
from operator import ge, le

from sqlalchemy import func, and_, or_, literal_column, case, distinct
from sqlalchemy.dialects.postgresql import aggregate_order_by
from sqlalchemy.orm import aliased, joinedload
from typing import List, Optional

from db import db
from models import (
    Campaign,
    CampaignAd,
    Project,
    user_label,
    UserProject,
    Linkfire,
    ProjectCampaign,
    MarketingAccount,
    CampaignTerritory,
    LinkfireCampaign,
    User,
    CampaignTypes,
    CampaignPlatforms,
)
from models.projects import ProjectCampaignStatus
from utils import sorting_utils


class DynamicFields:
    IS_PENDING = 1


TAXONOMY_TYPE_PLATFORM = "Platforms"


class CampaignsQueryBuilder:
    campaigns_sort_map = {
        "id": (Campaign.id,),
        "name": (Campaign.name, Campaign.id),
        "spend": (Campaign.budget_spend, Campaign.id),
        "budget": (Campaign.planned_budget, Campaign.id),
        "startDate": (Campaign.start_date, Campaign.id),
        "endDate": (func.coalesce(Campaign.end_date, func.max(Project.end_date)), Campaign.id),
        "adSetsCount": (func.count(CampaignAd.id), Campaign.id),
        "platforms": (
            func.string_agg(CampaignPlatforms.name, aggregate_order_by(literal_column("','"), CampaignPlatforms.name)),
            Campaign.id,
        ),
        "linkfireLinks": (
            func.string_agg(Linkfire.link_url, aggregate_order_by(literal_column("','"), Linkfire.link_url)),
            Campaign.id,
        ),
    }

    marketing_account_label = aliased(user_label)

    def __init__(self, user_id: int):
        self.user_id = user_id
        self.filter_items = [Campaign.is_deleted.is_(False), or_(Project.id.is_(None), Project.is_deleted.is_(False))]
        self.sort_expression = sorting_utils.sorting_expression(self.campaigns_sort_map, "id")
        self.dynamic_fields = []
        self.sort_pending_first = False
        self.limit = 20
        self.offset = 0

    def base_query(self, fields_set):
        query = (
            db.session.query(*fields_set)
            .outerjoin(Campaign.type)
            .outerjoin(Campaign.platforms)
            .outerjoin(Campaign.territories)
            .outerjoin(
                ProjectCampaign,
                and_(
                    Campaign.id == ProjectCampaign.campaign_id,
                    ProjectCampaign.status != ProjectCampaignStatus.REJECTED.value,
                ),
            )
            .outerjoin(Project, or_(Project.id == ProjectCampaign.project_id, Project.id == Campaign.project_id))
            .outerjoin(
                user_label, and_(user_label.c.label_id == Project.label_id, user_label.c.user_id == self.user_id)
            )
            .outerjoin(UserProject, and_(UserProject.project_id == Project.id, UserProject.user_id == self.user_id))
            .outerjoin(MarketingAccount, Campaign.marketing_account)
            .outerjoin(
                self.marketing_account_label,
                and_(
                    self.marketing_account_label.c.label_id == MarketingAccount.label_id,
                    self.marketing_account_label.c.user_id == self.user_id,
                ),
            )
            .outerjoin(LinkfireCampaign, LinkfireCampaign.campaign_id == Campaign.id)
            .outerjoin(Linkfire, Linkfire.id == LinkfireCampaign.linkfire_id)
            .outerjoin(CampaignAd, CampaignAd.campaign_id == Campaign.id)
            .join(User, User.id == self.user_id)
        )

        return query

    def only_assigned(self):
        self.filter_items.append(or_(ProjectCampaign.id.isnot(None), Campaign.project_id.isnot(None)))
        return self

    def only_related_to_project(self, project_id):
        self.filter_items.append(or_(ProjectCampaign.project_id == project_id, Campaign.project_id == project_id))
        return self

    def only_unassigned(self):
        self.filter_items.append(and_(ProjectCampaign.id.is_(None), Campaign.project_id.is_(None)))
        return self

    def only_accessible_campaigns(self):
        is_labelmate = user_label.c.user_id.isnot(None)
        can_see_project = or_(
            and_(is_labelmate, Project.is_confidential.is_(False)).self_group(),
            and_(is_labelmate, User.is_admin).self_group(),
        ).self_group()
        is_collaborator = and_(UserProject.user_id.isnot(None), Project.id.isnot(None)).self_group()

        can_see_unassigned_campaign = and_(self.marketing_account_label.c.user_id.isnot(None), Project.id.is_(None))

        self.filter_items.append(
            or_(
                can_see_project,
                is_collaborator,
                can_see_unassigned_campaign,
            )
        )

        return self

    def filtered_by_project(self, project_id: int):
        self.filter_items.append(or_(Campaign.project_id == project_id, ProjectCampaign.project_id == project_id))
        return self

    def only_approved(self, project_id: int):
        self.filter_items.append(
            or_(
                Campaign.project_id == project_id,
                and_(
                    ProjectCampaign.project_id == project_id,
                    ProjectCampaign.status == ProjectCampaignStatus.APPROVED.value,
                ),
            )
        )
        return self

    def status_in(self, statuses: List[int]):
        self.filter_items.append(ProjectCampaign.status.in_(statuses))
        return self

    def status_not_equal(self, status: int):
        self.filter_items.append(ProjectCampaign.status != status)
        return self

    def filtered_by_search(self, query: str):
        found_ids = Campaign.search(expression=query)
        self.filter_items.append(Campaign.id.in_(found_ids))
        return self

    def filtered_by_date_range(self, start_date: date, end_date: date):
        self.filter_items.append(and_(ge(Campaign.start_date, start_date), le(Campaign.end_date, end_date)))
        return self

    def filtered_by_territories(self, territories: List[int]):
        self.filter_items.append(CampaignTerritory.territory_id.in_(territories))
        return self

    def filtered_by_platforms(self, platforms: List[str]):
        self.filter_items.append(CampaignPlatforms.name.in_(platforms))
        return self

    def filtered_by_sources(self, sources: List[str]):
        self.filter_items.append(Campaign.source.in_(sources))
        return self

    def sort_by(self, sort_field: str):
        self.sort_expression = sorting_utils.sorting_expression(self.campaigns_sort_map, sort_field)
        return self

    def limit_to(self, limit: Optional[int]):
        self.limit = limit
        return self

    def offset_by(self, offset: int):
        self.offset = offset
        return self

    def set_dynamic_fields(self, fields: List[DynamicFields]):
        self.dynamic_fields = fields

    def __fields_set(self):
        fields_set = [Campaign]
        if DynamicFields.IS_PENDING in self.dynamic_fields:
            fields_set.append(self.__campaign_status_function().label("is_pending"))
        return fields_set

    def __campaign_status_function(self):
        return case([(ProjectCampaign.status == ProjectCampaignStatus.PENDING.value, True)], else_=False)

    def items_query(self):
        query = self.base_query(self.__fields_set()).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),
        )

        for f in self.filter_items:
            query = query.filter(f)

        if self.sort_pending_first:
            self.sort_expression = [ProjectCampaign.status.asc(), *self.sort_expression]

        query = query.group_by(Campaign.id)
        if DynamicFields.IS_PENDING in self.dynamic_fields:
            query = query.group_by(Campaign.id, ProjectCampaign.status)

        query = query.order_by(*self.sort_expression)

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

        return query.offset(self.offset)

    def count_query(self):
        query = self.base_query([func.count(distinct(Campaign.id))])

        for f in self.filter_items:
            query = query.filter(f)

        return query