from typing import List, Optional
from flask import g
from sqlalchemy import and_, func, or_, distinct
from sqlalchemy.orm import joinedload

from campaigns.repositories.campaigns_repository import CampaignsRepository
from db import db
from models.projects import ProjectCampaignStatus
from models.performance_metrics import MetricsField, user_metrics_field
from models.marketing_accounts import MarketingAccount
from models.user import user_label, User
from models import (
    Project,
    Campaign,
    Artist,
    CampaignObjective,
    ProjectTargetItem,
    PerformanceBreakdownOption,
    ProjectCampaign,
    Territory,
    CampaignProvider,
    UserProject,
    MediaPlanPhase,
    ArtistTeam,
    ArtistTeamUser,
    CampaignPlatforms,
)
from models.project_entity_type import ProjectEntityType, ProjectEntityAddType
from shared.queries import Queries as SharedQueries
from performance.schemas import PerformanceMetricsParamsSchema
from utils.elastic_search.query_strategy import QueryStrategyType


DEFAULT_RESPONSE_SIZE = 20
UNCATEGORIZED_METRIC_TYPE = 7


class MetricsRepository:
    def fetch_metrics_fields_by_objective(self) -> List[CampaignObjective]:
        return (
            db.session.query(CampaignObjective)
            .join(MetricsField, CampaignObjective.metric_fields)
            .options(
                joinedload(CampaignObjective.metric_fields).joinedload(MetricsField.type),
                joinedload(CampaignObjective.metric_fields).joinedload(MetricsField.format),
            )
            .order_by(CampaignObjective.id)
            .all()
        )

    def fetch_metrics_fields(self) -> List[MetricsField]:
        return (
            db.session.query(MetricsField)
            .options(joinedload(MetricsField.type), joinedload(MetricsField.format))
            .filter(MetricsField.type_id != UNCATEGORIZED_METRIC_TYPE)
            .all()
        )

    def fetch_metrics_fields_for_user(self, user_id: int) -> List[MetricsField]:
        return (
            db.session.query(MetricsField)
            .join(
                user_metrics_field,
                and_(user_metrics_field.c.user_id == user_id, user_metrics_field.c.metric_field_id == MetricsField.id),
            )
            .all()
        )

    def update_metrics_fields_for_user(self, user_id: int, metrics_ids: List[int]):
        db.session.execute(user_metrics_field.delete().where(user_metrics_field.c.user_id == user_id))
        for metric_id in metrics_ids:
            statement = user_metrics_field.insert().values(user_id=user_id, metric_field_id=metric_id)
            db.session.execute(statement)
        db.session.commit()

    def validate_metrics_ids(self, metrics_ids: List[int]) -> bool:
        count = db.session.query(MetricsField.id).filter(MetricsField.id.in_(metrics_ids)).count()
        return count == len(metrics_ids)

    def get_available_platforms(self, only_external: Optional[bool] = False) -> List[str]:
        starter_query = db.session.query(CampaignPlatforms.name).join(Campaign, CampaignPlatforms.campaign)
        campaigns_rep = CampaignsRepository()
        result_query = campaigns_rep.filter_only_available_campaigns(starter_query)
        result = result_query.group_by(CampaignPlatforms.name).order_by(CampaignPlatforms.name).all()
        return [r[0] for r in result]

    def __permissions_joining_condition(self, query, user_id: int, label_ids: List[int]):
        query = self.__join_with_user_project(query)
        if label_ids:
            query = self.__join_with_labels(label_ids, query, user_id)
        else:
            query = self.__join_with_user_labels(query, user_id)

        user_in_label = user_label.c.user_id.isnot(None)
        user_in_team = UserProject.user_id.isnot(None)

        return query.filter(or_(user_in_label, user_in_team))

    def __join_with_user_project(self, query):
        return query.outerjoin(
            UserProject, and_(UserProject.project_id == Project.id, UserProject.user_id == g.user_id)
        )

    def __join_with_user_labels(self, query, user_id):
        return query.outerjoin(
            user_label, and_(user_label.c.label_id == Project.label_id, user_label.c.user_id == user_id)
        )

    def __join_with_labels(self, label_ids, query, user_id):
        return query.outerjoin(
            user_label,
            and_(
                user_label.c.label_id == Project.label_id,
                and_(user_label.c.user_id == user_id, user_label.c.label_id.in_(label_ids)),
            ),
        )

    def get_campaigns_metadata(
        self, external_ids: List[str], project_id: Optional[int] = None, project_dsp_id: Optional[str] = None
    ):
        query = (
            db.session.query(
                Campaign.id.label("id"),
                func.campaign_dsp_id(Campaign.source, Campaign.external_id).label("campaign_dsp_id"),
                func.array_agg(distinct(func.coalesce(Campaign.project_id, Project.id))).label("project_ids"),
                func.count(Project.id).label("pending_count"),
            )
            .outerjoin(
                ProjectCampaign,
                and_(
                    ProjectCampaign.campaign_id == Campaign.id,
                    ProjectCampaign.status == ProjectCampaignStatus.PENDING.value,
                ),
            )
            .outerjoin(Project, and_(Project.id == ProjectCampaign.project_id, Project.is_claimed.is_(True)))
            .filter(func.campaign_dsp_id(Campaign.source, Campaign.external_id).in_(external_ids))
            .group_by(Campaign.id, Campaign.external_id, Campaign.source)
        )

        if project_id:
            query = query.filter(or_(Project.id == project_id, Campaign.project_id == project_id))
        if project_dsp_id:
            query = query.filter(
                func.dsp_id(Project.source, func.coalesce(Project.prs_project_code, Project.ccp_project_code))
                == project_dsp_id
            )

        return query.all()

    def get_projects_metadata(self, ids: List[str]):
        role = SharedQueries.role_for_project_query(g.user_id)
        query = (
            db.session.query(
                Project.id.label("id"),
                func.dsp_id(Project.source, func.coalesce(Project.prs_project_code, Project.ccp_project_code)).label(
                    "project_dsp_id"
                ),
                func.count(Campaign.id).label("pending_campaigns"),
                role.label("role"),
                Project.is_confidential.label("is_confidential"),
            )
            .select_from(Project)
            .join(User, User.id == g.user_id)
            .outerjoin(UserProject, and_(UserProject.project_id == Project.id, UserProject.user_id == g.user_id))
            .outerjoin(
                ProjectCampaign,
                and_(
                    ProjectCampaign.project_id == Project.id,
                    ProjectCampaign.status == ProjectCampaignStatus.PENDING.value,
                ),
            )
            .outerjoin(Campaign, and_(ProjectCampaign.campaign_id == Campaign.id, Campaign.is_deleted.is_(False)))
            .outerjoin(
                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,
                ),
            )
            .outerjoin(
                ArtistTeam,
                and_(ArtistTeam.label_id == Project.label_id, ProjectTargetItem.entity_id == ArtistTeam.artist_id),
            )
            .outerjoin(
                ArtistTeamUser,
                and_(ArtistTeamUser.artist_team_id == ArtistTeam.id, ArtistTeamUser.user_id == g.user_id),
            )
            .filter(
                func.dsp_id(Project.source, func.coalesce(Project.prs_project_code, Project.ccp_project_code)).in_(ids)
            )
            .group_by(Project.id, role, Project.prs_project_code, Project.ccp_project_code, Project.source)
        )
        return query.all()

    def project_names_by_external_ids(self, user_id: int, external_ids: List[str]) -> dict:
        results = (
            db.session.query(Project.name.label("name"), Project.id.label("id"))
            .filter(Project.id.in_(external_ids))
            .all()
        )
        return self.__map_names(results)

    def provider_accounts(self) -> List[dict]:
        results = (
            db.session.query(
                func.array_agg(MarketingAccount.external_id).label("account_ids"),
                CampaignProvider.name.label("name"),
                MarketingAccount.provider_id.label("provider_id"),
            )
            .join(CampaignProvider)
            .group_by(MarketingAccount.provider_id, CampaignProvider.name)
            .all()
        )
        return results

    def artist_names_by_external_ids(self, ids: List[int]) -> dict:
        results = db.session.query(Artist.name.label("name"), Artist.id.label("id")).filter(Artist.id.in_(ids)).all()
        return self.__map_names(results)

    def territories_by_codes(self, codes: List[str]) -> dict:
        results = (
            db.session.query(Territory.name.label("name"), Territory.code.label("code"))
            .filter(Territory.code.in_(codes))
            .all()
        )
        return self.__map_country_codes(results)

    def campaign_names_by_external_ids(self, external_ids: List[str]) -> dict:
        results = (
            db.session.query(Campaign.name.label("name"), Campaign.external_id.label("id"))
            .filter(Campaign.external_id.in_(external_ids))
            .all()
        )
        return self.__map_names(results)

    def phase_names_by_ids(self, ids: List[int]) -> dict:
        results = (
            db.session.query(MediaPlanPhase.name.label("name"), MediaPlanPhase.id.label("id"))
            .filter(MediaPlanPhase.id.in_(ids))
            .all()
        )
        return self.__map_names(results)

    def __map_names(self, data) -> dict:
        names_dict = {}
        for result in data:
            names_dict[int(result.id)] = result.name
        return names_dict

    def __map_country_codes(self, data) -> dict:
        names_dict = {}
        for result in data:
            names_dict[result.code] = result.name
        return names_dict


class BreakdownOptionsRepository:
    def get_breakdown_option_by_id(self, id) -> Optional[PerformanceBreakdownOption]:
        return db.session.query(PerformanceBreakdownOption).filter(PerformanceBreakdownOption.id == id).one_or_none()

    def get_sorted_options_list(self, excluded_options: List[int]) -> List[PerformanceBreakdownOption]:
        return (
            db.session.query(PerformanceBreakdownOption)
            .filter(PerformanceBreakdownOption.id.notin_(excluded_options))
            .order_by(PerformanceBreakdownOption.order, PerformanceBreakdownOption.group)
            .all()
        )


class MetricsApiSearchRepository:
    def __map_external_ids(self, dsp_ids):
        return [external_id.dsp_id for external_id in dsp_ids][0:DEFAULT_RESPONSE_SIZE]

    async def search_by_campaigns(self, params: PerformanceMetricsParamsSchema):
        found_ids = Campaign.search(
            expression=params.freeText, fields=[Campaign.name], strategy=QueryStrategyType.MULTIMATCH_PHRASE_QUERY
        )
        query = (
            db.session.query(func.campaign_dsp_id(Campaign.source, Campaign.external_id).label("dsp_id"))
            .outerjoin(MarketingAccount, MarketingAccount.external_id == Campaign.external_marketing_account_id)
            .outerjoin(
                ProjectCampaign,
                and_(
                    ProjectCampaign.campaign_id == Campaign.id,
                    ProjectCampaign.status.in_(
                        [ProjectCampaignStatus.APPROVED.value, ProjectCampaignStatus.PENDING.value]
                    ),
                ),
            )
            .outerjoin(Project, or_(Project.id == ProjectCampaign.project_id, Project.id == Campaign.project_id))
            .join(user_label, and_(user_label.c.label_id == Project.label_id, user_label.c.user_id == g.user_id))
            .filter(
                Campaign.id.in_(found_ids),
                Campaign.external_id.isnot(None),
                Campaign.is_deleted.is_(False),
                or_(Project.id.is_(None), Project.is_deleted.is_(False)),
            )
        )
        if params.labels:
            query = query.filter(Project.label_id.in_(params.labels))
        dsp_ids = query.group_by(Campaign.source, Campaign.external_id).all()
        return self.__map_external_ids(dsp_ids)

    async def search_by_unassigned_campaigns(self, params: PerformanceMetricsParamsSchema):
        found_ids = Campaign.search(
            expression=params.freeText, fields=[Campaign.name], strategy=QueryStrategyType.MULTIMATCH_PHRASE_QUERY
        )
        query = (
            db.session.query(func.campaign_dsp_id(Campaign.source, Campaign.external_id).label("dsp_id"))
            .join(MarketingAccount, MarketingAccount.external_id == Campaign.external_marketing_account_id)
            .outerjoin(ProjectCampaign, ProjectCampaign.campaign_id == Campaign.id)
            .filter(
                Campaign.id.in_(found_ids),
                Campaign.project_id.is_(None),
                Campaign.is_deleted.is_(False),
                or_(ProjectCampaign.id.is_(None), ProjectCampaign.status == ProjectCampaignStatus.REJECTED.value),
            )
        )
        if params.labels:
            query = query.filter(MarketingAccount.label_id.in_(params.labels))
        dsp_ids = query.group_by(Campaign.source, Campaign.external_id).all()
        return self.__map_external_ids(dsp_ids)

    async def search_by_projects(self, params: PerformanceMetricsParamsSchema):
        found_ids = Project.search(
            expression=params.freeText, fields=[Project.name], strategy=QueryStrategyType.MULTIMATCH_PHRASE_QUERY
        )
        query = (
            db.session.query(
                func.dsp_id(Project.source, func.coalesce(Project.prs_project_code, Project.ccp_project_code)).label(
                    "dsp_id"
                )
            )
            .join(
                ProjectCampaign,
                and_(
                    ProjectCampaign.project_id == Project.id,
                    ProjectCampaign.status == ProjectCampaignStatus.APPROVED.value,
                ),
            )
            .join(
                Campaign,
                and_(
                    ProjectCampaign.campaign_id == Campaign.id,
                    Campaign.is_deleted.is_(False),
                    Campaign.external_id.isnot(None),
                ),
            )
            .join(user_label, and_(user_label.c.label_id == Project.label_id, user_label.c.user_id == g.user_id))
            .filter(Project.is_deleted.is_(False), Project.id.in_(found_ids))
            .group_by(Project.prs_project_code, Project.ccp_project_code, Project.source)
        )
        if params.labels:
            query = query.filter(Project.label_id.in_(params.labels))
        dsp_ids = query.all()
        return self.__map_external_ids(dsp_ids)

    async def search_campaigns_in_project(self, project_id: str, params: PerformanceMetricsParamsSchema):
        found_ids = Campaign.search(
            expression=params.freeText, fields=[Campaign.name], strategy=QueryStrategyType.MULTIMATCH_PHRASE_QUERY
        )
        query = (
            db.session.query(func.campaign_dsp_id(Campaign.source, Campaign.external_id).label("dsp_id"))
            .outerjoin(
                ProjectCampaign,
                and_(
                    ProjectCampaign.campaign_id == Campaign.id,
                    ProjectCampaign.status.in_(
                        [ProjectCampaignStatus.PENDING.value, ProjectCampaignStatus.APPROVED.value]
                    ),
                ),
            )
            .join(Project, or_(Project.id == ProjectCampaign.project_id, Project.id == Campaign.project_id))
            .filter(
                Campaign.id.in_(found_ids),
                Campaign.is_deleted.is_(False),
                Campaign.external_id.isnot(None),
                func.dsp_id(Project.source, func.coalesce(Project.prs_project_code, Project.ccp_project_code))
                == project_id,
            )
        )
        dsp_ids = query.group_by(Campaign.source, Campaign.external_id).all()
        return self.__map_external_ids(dsp_ids)