from sqlalchemy import case, and_, or_, Integer
from sqlalchemy.dialects.postgresql import aggregate_order_by
from sqlalchemy.orm import aliased
from typing import Optional, List

from models import CampaignPlatforms, Campaign, MediaPlan
from sqlalchemy.orm.query import Query as SAQuery
from sqlalchemy.sql import func

from artists.schemas import ArtistRosterRequestSchema, ArtistRosterProjectsRequestSchema
from constants.artist_statuses import ArtistStatuses
from db import db
from models.artist_team import ArtistTeam, ArtistTeamUser
from models.artists import Artist
from models.polymorphable import Polymorphable
from models.project_entity_type import ProjectEntityType
from models.projects import Project, ProjectTargetItem, ProjectCampaign, ProjectCampaignStatus
from models.user import User, user_label
from models.user_project import UserProject
from shared.query_builders.projects_query_builder import ProjectsQueryBuilder, DynamicFields


class ArtistRosterRepository:
    def get_artist_roster(self, user_id: int, params: ArtistRosterRequestSchema):
        projects_subquery = self.__get_projects_subquery(user_id)
        all_projects_subquery = aliased(projects_subquery)
        completed_projects_subquery = aliased(projects_subquery)

        artist_status_field = case(
            [
                (
                    or_(
                        func.min(projects_subquery.c.start_date).is_(None),
                        and_(
                            func.count(func.distinct(projects_subquery.c.is_claimed)) == 1,
                            func.min(func.cast(projects_subquery.c.is_claimed, Integer)) == 0,
                            #  check that only unclaimed projects for the artist
                        )

                    ),
                    ArtistStatuses.ARTIST_STATUS_INACTIVE
                ),
                (
                    func.min(projects_subquery.c.start_date) <= func.current_date(),
                    ArtistStatuses.ARTIST_STATUS_LIVE
                ),
                (
                    func.min(projects_subquery.c.start_date) > func.current_date(),
                    ArtistStatuses.ARTIST_STATUS_PLANNED
                ),
            ],
            else_=ArtistStatuses.ARTIST_STATUS_INACTIVE,
        )

        user_in_team = (User.id == ArtistTeamUser.user_id)
        user_is_label_admin = and_(User.is_admin, user_label.c.label_id == ArtistTeam.label_id).self_group()

        filters = []

        if params.labelId:
            filters.append(all_projects_subquery.c.project_label == params.labelId)

        artists = (
            db.session.query(
                Artist,
                func.count(func.distinct(projects_subquery.c.project_id)).label("active_projects_count"),
                func.count(func.distinct(completed_projects_subquery.c.project_id)).label("completed_projects_count"),
                func.min(projects_subquery.c.start_date).label("live_min_start_date"),
                func.max(projects_subquery.c.end_date).label("live_max_end_date"),
                func.min(all_projects_subquery.c.start_date).label("all_min_start_date"),
                func.max(all_projects_subquery.c.end_date).label("all_max_end_date"),
                artist_status_field.label("artist_status"),
                case([(ArtistTeamUser.id.isnot(None), True)], else_=False).label("is_team_member")
            )
            .select_from(Artist)
            .join(User, User.id == user_id)
            .outerjoin(user_label, user_label.c.user_id == User.id)
            .outerjoin(ArtistTeam, and_(
                Artist.id == ArtistTeam.artist_id,
                ArtistTeam.label_id == user_label.c.label_id,
            ))
            .outerjoin(
                ArtistTeamUser,
                and_(
                    ArtistTeam.id == ArtistTeamUser.artist_team_id,
                    or_(user_in_team, user_is_label_admin),
                ))
            .outerjoin(
                projects_subquery,
                and_(
                    projects_subquery.c.project_label == user_label.c.label_id,
                    projects_subquery.c.artist_ids.any(Artist.id),
                    projects_subquery.c.end_date >= func.current_date(),
                    or_(
                        projects_subquery.c.is_confidential.is_(False),
                        User.is_admin.is_(True),
                        projects_subquery.c.user_project_id.isnot(None)
                    ).self_group()
                ),
            )
            .outerjoin(
                all_projects_subquery,
                and_(
                    all_projects_subquery.c.project_label == user_label.c.label_id,
                    all_projects_subquery.c.artist_ids.any(Artist.id),
                    or_(
                        all_projects_subquery.c.is_confidential.is_(False),
                        User.is_admin.is_(True),
                        all_projects_subquery.c.user_project_id.isnot(None)
                    ).self_group()
                ),
            )
            .outerjoin(
                completed_projects_subquery,
                and_(
                    completed_projects_subquery.c.artist_ids.any(Artist.id),
                    completed_projects_subquery.c.end_date < func.current_date(),
                    completed_projects_subquery.c.end_date.isnot(None),
                    completed_projects_subquery.c.is_claimed.is_(True),
                )
            )
            .filter(*filters)
            .group_by(Artist.id, Polymorphable.id, ArtistTeamUser.id)
            .order_by(Artist.id)
            .all()
        )

        return artists

    @staticmethod
    def __apply_filters(
        builder: ProjectsQueryBuilder,
        params: ArtistRosterProjectsRequestSchema,
        artist_external_id: Optional[str] = None
    ):
        if params.statuses is not None:
            builder.filter_by_statuses(params.statuses)
        (
            builder
            .filtered_by_labels([params.labelId])
            .only_accessible_projects()
            .filtered_by_artist(artist_external_id)
            .only_claimed()
            .sort_by("earliestStartDate")
        )

    def __get_projects_subquery(self, user_id) -> SAQuery:
        return (
            db.session.query(
                Project.id.label("project_id"),
                Project.is_claimed.label("is_claimed"),
                Project.is_confidential.label("is_confidential"),
                Project.label_id.label("project_label"),
                Project.end_date.label("end_date"),
                Project.initial_start_date.label("start_date"),
                UserProject.id.label("user_project_id"),
                func.array_agg(ProjectTargetItem.entity_id).label("artist_ids"),
            )
            .join(
                ProjectTargetItem,
                and_(
                    ProjectTargetItem.project_id == Project.id,
                    ProjectTargetItem.entity_type == ProjectEntityType.PRIMARY_ARTIST.value,
                    ProjectTargetItem.is_deleted.is_(False)
                )
            )
            .outerjoin(UserProject, and_(UserProject.user_id == user_id, UserProject.project_id == Project.id))
            # .filter(Project.is_claimed.is_(True))
            .group_by(Project.id, UserProject.id)
            .subquery("artist_roster_projects")
        )

    def get_projects_list(self, user_id: int, artist_external_id: str, params: ArtistRosterProjectsRequestSchema):
        builder = ProjectsQueryBuilder(user_id)
        builder.dynamic_fields = [
            DynamicFields.START_DATE,
            DynamicFields.STATUS,
            DynamicFields.CAMPAIGNS_COUNT,
        ]
        self.__apply_filters(builder, params, artist_external_id)

        return builder.items_query().all()

    def get_project_platforms(self, projects_ids: List[int]):
        return (
            db.session.query(
                Project.id.label("project_id"),
                func.array_agg(
                    func.distinct(
                        func.jsonb_build_object("id", CampaignPlatforms.id, "name", CampaignPlatforms.name)
                    )
                ).label("platforms")
            )
            .select_from(Project)
            .outerjoin(
                ProjectCampaign,
                and_(
                    ProjectCampaign.project_id == Project.id,
                    ProjectCampaign.status != ProjectCampaignStatus.REJECTED.value,
                ),
            )
            .join(
                Campaign,
                and_(
                    or_(Campaign.project_id == Project.id, ProjectCampaign.campaign_id == Campaign.id).self_group(),
                    Campaign.is_deleted.is_(False),
                ),
            )
            .join(CampaignPlatforms, Campaign.platforms)
            .filter(Project.id.in_(projects_ids))
            .group_by(Project.id)
            .order_by(Project.id)
        )

    def get_project_media_plans(self, projects_ids: List[int]):
        return (
            db.session.query(
                Project.id.label("project_id"),
                func.array_agg(
                    aggregate_order_by(
                        func.jsonb_build_object("id", MediaPlan.id, "name", MediaPlan.name),
                        MediaPlan.order.asc()
                    )
                ).label("media_plans")
            )
            .select_from(Project)
            .join(MediaPlan, MediaPlan.project_id == Project.id)
            .filter(Project.id.in_(projects_ids))
            .group_by(Project.id)
        )