from sqlalchemy import func, and_, or_, distinct, case
from sqlalchemy.orm import joinedload
from typing import List

from db import db
from models import (
    Artist,
    Project,
    ProjectTargetItem,
    ArtistTeam,
    ArtistTeamUser,
    User, Polymorphable,
)
from models.project_entity_type import ProjectEntityAddType, ProjectEntityType
from models.user_project import UserProjectRoles
from utils import sorting_utils
from utils.string_utils import random_string
from sqlalchemy.dialects.postgresql import aggregate_order_by


class DynamicFields:
    TEAM_MEMBERS = 1


class LabelArtistsQueryBuilder:
    artists_sort_map = {
        "id": (Artist.id,),
        "name": (Artist.name, Artist.id),
    }

    def __init__(self, label_id: int, with_images: bool = False):
        self.label_id = label_id
        self.filter_items = [
            or_(
                ArtistTeam.id.isnot(None),
                and_(
                    Project.label_id == label_id,
                    ProjectTargetItem.id.isnot(None)
                )
            )
        ]
        self.token = random_string(5)
        self.team_members_data_query = self.__get_team_members_data_query(label_id).cte(
            f"team_members_data{self.token}"
        )
        self.sort_expression = sorting_utils.sorting_expression(self.artists_sort_map, "name")
        self.dynamic_fields = []
        self.artist_entity_types = [ProjectEntityType.PRIMARY_ARTIST.value, ProjectEntityType.FEATURED_ARTIST.value]
        self.with_images = with_images

    def __get_team_members_data_query(self, label_id):
        return (
            db.session.query(
                ArtistTeam.id.label("artist_team_id"),
                case([(User.is_admin, UserProjectRoles.ADMIN.value)], else_=ArtistTeamUser.role).label("role"),
                User.id.label("user_id"),
                User.email.label("email"),
                User.name.label("name"),
            )
            .select_from(User)
            .join(ArtistTeam, ArtistTeam.label_id == label_id)
            .join(
                ArtistTeamUser,
                and_(ArtistTeamUser.artist_team_id == ArtistTeam.id, ArtistTeamUser.user_id == User.id)
            )
        )

    def base_query(self, fields_set):
        query = (
            db.session.query(*fields_set)
            .select_from(Artist)
            .outerjoin(
                ProjectTargetItem,
                and_(
                    ProjectTargetItem.entity_id == Artist.id,
                    ProjectTargetItem.entity_type == ProjectEntityType.PRIMARY_ARTIST.value,
                    ProjectTargetItem.is_deleted.is_(False),
                )
            )
            .outerjoin(
                Project,
                and_(
                    Project.label_id == self.label_id,
                    Project.is_deleted.is_(False),
                    ProjectTargetItem.project_id == Project.id,
                )
            )
            .outerjoin(
                ArtistTeam,
                and_(
                    ArtistTeam.label_id == self.label_id,
                    ArtistTeam.artist_id == Artist.id
                )
            )

        )

        if DynamicFields.TEAM_MEMBERS in self.dynamic_fields:
            query = query.outerjoin(
                self.team_members_data_query,
                self.team_members_data_query.c.artist_team_id == ArtistTeam.id
            )

        return query

    def external_id_in(self, external_ids: List[str]):
        self.filter_items.append(Artist.external_id.in_(external_ids))
        return self

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

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

    def __fields_set(self):
        fields_set = [Artist]
        if DynamicFields.TEAM_MEMBERS in self.dynamic_fields:
            fields_set.append(
                func.json_strip_nulls(
                    func.json_agg(
                        func.distinct(
                            func.jsonb_build_object(
                                "user_id", self.team_members_data_query.c.user_id,
                                "name", self.team_members_data_query.c.name,
                                "email", self.team_members_data_query.c.email,
                                "role", self.team_members_data_query.c.role,
                            )
                        )
                    )
                ).label("team_members"),)
        return fields_set

    def items_query(self):
        query = self.base_query(self.__fields_set())
        if self.with_images:
            query = query.options(
                joinedload(Artist.images),
            )

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

        query = query.group_by(Artist.id, Polymorphable.id)

        query = query.order_by(*self.sort_expression)

        return query

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

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

        query.group_by(Artist.id)

        return query