from sqlalchemy import and_, func, or_, case, literal

from models.user_project import UserProjectRoles, UserProject
from models import User, ArtistTeam, ArtistTeamUser, Artist, Polymorphable, Label, user_label
from sqlalchemy.orm import selectinload
from db import db
from models.projects import ProjectTargetItem, Project
from models.project_entity_type import ProjectEntityType, ProjectEntityAddType
from typing import List


class ArtistTeamRepository:

    def get_artist_team_for_project(self, project_id: int) -> ArtistTeam:
        return (
            db.session.query(ArtistTeam)
            .join(ArtistTeamUser, ArtistTeamUser.artist_team_id == ArtistTeam.id)
            .join(
                Project,
                and_(
                    Project.label_id == ArtistTeam.label_id,
                    Project.id == project_id,
                    Project.is_confidential.is_(False),
                ),
            )
            .join(Artist, and_(ArtistTeam.artist_id == Artist.id, Artist.is_unknown.is_(False)))
            .join(
                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,
                    ProjectTargetItem.entity_id == ArtistTeam.artist_id,
                ),
            )
            .options(
                selectinload(ArtistTeam.artist_team_users)
                .joinedload(ArtistTeamUser.user, innerjoin=True)
                .joinedload(User.labels, innerjoin=True)
            )
            .one_or_none()
        )

    def get_artist_team_users_and_label_admins_for_project(self, project_id: int):
        label_admins = (
            db.session.query(
                User.id,
                User.name,
                User.email,
                literal(UserProjectRoles.ADMIN.value).label("role")
            )
            .select_from(Project)
            .join(user_label, user_label.c.label_id == Project.label_id)
            .join(User, and_(User.id == user_label.c.user_id, User.is_admin.is_(True)))
            .filter(Project.id == project_id)
            .group_by(User.id)
        )
        artist_team_users = (
            db.session.query(
                User.id,
                User.name,
                User.email,
                func.min(ArtistTeamUser.role).label("role")
            )
            .select_from(Project)
            .join(Label, Label.id == Project.label_id)
            .join(
                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,
                ),
            )
            .join(Polymorphable, Polymorphable.id == ProjectTargetItem.entity_id)
            .join(Artist, Artist.id == Polymorphable.id)
            .join(
                ArtistTeam,
                and_(
                    Artist.id == ArtistTeam.artist_id,
                    ArtistTeam.label_id == Label.id,
                ),
            )
            .join(ArtistTeamUser, ArtistTeamUser.artist_team_id == ArtistTeam.id)
            .join(User, User.id == ArtistTeamUser.user_id)
            .filter(
                Project.id == project_id,
                Project.is_confidential.is_(False),
                User.id.notin_([user.id for user in label_admins])
            )
            .group_by(User.id)
        )
        return artist_team_users.union_all(label_admins).order_by(User.name).all()


    def get_projects_for_artist_team(self, artist_team_id: int) -> List[Project]:
        return (
            db.session.query(Project)
            .join(
                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,
                ),
            )
            .join(Polymorphable, Polymorphable.id == ProjectTargetItem.entity_id)
            .join(Artist, Artist.id == Polymorphable.id)
            .join(
                ArtistTeam,
                and_(
                    Artist.id == ArtistTeam.artist_id,
                    ArtistTeam.label_id == Project.label_id,
                    ArtistTeam.id == artist_team_id,
                ),
            )
            .filter(Project.is_confidential.is_(False))
            .all()
        )

    def get_artist_team_creation_data(self, project_id) -> list:
        return (
            db.session.query(func.array_agg(UserProject.user_id), Project.label_id, Artist.id)
            .join(ProjectTargetItem, ProjectTargetItem.project_id == Project.id)
            .join(Polymorphable, Polymorphable.id == ProjectTargetItem.entity_id)
            .join(Artist, Polymorphable.id == Artist.id)
            .join(
                UserProject,
                and_(
                    UserProject.project_id == Project.id,
                    UserProject.role == UserProjectRoles.EDITOR.value
                )
            )
            .filter(Project.is_claimed.is_(True), Project.id == project_id)
            .group_by(Project.label_id, Artist.id)
            .all()
        )

    def assign_user_to_team(self, user_id, artist_team_id, role) -> ArtistTeamUser:
        artist_team_user = ArtistTeamUser(artist_team_id=artist_team_id, user_id=user_id, role=role)
        db.session.add(artist_team_user)
        db.session.commit()
        db.session.refresh(artist_team_user)
        return artist_team_user

    def assign_user_to_multiple_teams_in_label_by_external_id(
            self,
            label_id: int,
            user_id: int,
            artists_ids: List[str]
    ):
        artists_without_team = (
            db.session.query(Artist)
            .outerjoin(ArtistTeam, and_(ArtistTeam.artist_id == Artist.id, ArtistTeam.label_id == label_id))
            .filter(and_(Artist.external_id.in_(artists_ids), ArtistTeam.id.is_(None)))
            .all()
        )
        db.session.bulk_insert_mappings(
            ArtistTeam,
            [dict(artist_id=artist.id, label_id=label_id) for artist in artists_without_team]
        )
        db.session.flush()
        artist_teams_ids = (
            db.session.query(ArtistTeam)
            .select_from(Artist)
            .join(ArtistTeam, and_(ArtistTeam.artist_id == Artist.id, ArtistTeam.label_id == label_id))
            .filter(Artist.external_id.in_(artists_ids))
            .all()
        )
        db.session.bulk_insert_mappings(
            ArtistTeamUser,
            [
                dict(artist_team_id=artist_team.id, user_id=user_id, role=UserProjectRoles.EDITOR.value)
                for artist_team in artist_teams_ids
            ],
        )

    def assign_multiple_users_to_artist_team_in_label_by_external_id(
            self,
            label_id: int,
            users_ids: List[int],
            artist_id: str
    ):
        artists_without_team = (
            db.session.query(Artist)
            .outerjoin(ArtistTeam, and_(ArtistTeam.artist_id == Artist.id, ArtistTeam.label_id == label_id))
            .filter(and_(Artist.external_id == artist_id, ArtistTeam.id.is_(None)))
            .all()
        )
        db.session.bulk_insert_mappings(
            ArtistTeam,
            [dict(artist_id=artist.id, label_id=label_id) for artist in artists_without_team]
        )
        db.session.flush()
        artist_team = (
            db.session.query(ArtistTeam)
            .select_from(Artist)
            .join(ArtistTeam, and_(ArtistTeam.artist_id == Artist.id, ArtistTeam.label_id == label_id))
            .filter(Artist.external_id == artist_id)
            .one()
        )
        db.session.bulk_insert_mappings(
            ArtistTeamUser,
            [
                dict(artist_team_id=artist_team.id, user_id=user_id, role=UserProjectRoles.EDITOR.value)
                for user_id in users_ids
            ],
        )

    def delete_user_from_artist_teams(self, user_id, artists_ids: List[str], label_id: int):
        (
            db.session.query(ArtistTeamUser)
            .filter(
                ArtistTeamUser.id.in_(
                    db.session.query(ArtistTeamUser.id)
                    .select_from(Artist)
                    .join(ArtistTeam, and_(ArtistTeam.label_id == label_id, ArtistTeam.artist_id == Artist.id))
                    .join(ArtistTeamUser, and_(
                        ArtistTeamUser.artist_team_id == ArtistTeam.id,
                        ArtistTeamUser.user_id == user_id
                    ))
                    .filter(Artist.external_id.in_(artists_ids))
                    .all()
                )
            )
            .delete(synchronize_session="fetch")
        )

    def delete_multiple_users_from_artist_teams(self, users_ids: List[int], artist_id: str, label_id: int):
        (
            db.session.query(ArtistTeamUser)
            .filter(
                ArtistTeamUser.id.in_(
                    db.session.query(ArtistTeamUser.id)
                    .select_from(Artist)
                    .join(ArtistTeam, and_(ArtistTeam.label_id == label_id, ArtistTeam.artist_id == Artist.id))
                    .join(ArtistTeamUser, and_(
                        ArtistTeamUser.artist_team_id == ArtistTeam.id,
                        ArtistTeamUser.user_id.in_(users_ids)
                    ))
                    .filter(Artist.external_id == artist_id)
                    .all()
                )
            )
            .delete(synchronize_session="fetch")
        )

    def remove_empty_artist_teams(self, artists_ids: List[str], label_id: int):
        (
            db.session.query(ArtistTeam)
            .filter(
                ArtistTeam.id.in_(
                    db.session.query(ArtistTeam.id)
                    .select_from(Artist)
                    .join(ArtistTeam, and_(ArtistTeam.label_id == label_id, ArtistTeam.artist_id == Artist.id))
                    .outerjoin(ArtistTeamUser, ArtistTeamUser.artist_team_id == ArtistTeam.id)
                    .filter(and_(Artist.external_id.in_(artists_ids), ArtistTeamUser.id.is_(None)))
                    .all()
                )
            )
            .delete(synchronize_session="fetch")
        )

    def get_artist_for_user_with_team_users_in_label(self, user_id: int, label_id: int, artists_ids: List[str]):
        return (
            db.session.query(Artist)
            .join(
                ArtistTeam,
                and_(
                    ArtistTeam.label_id == label_id,
                    ArtistTeam.artist_id == Artist.id
                )
            )
            .join(
                ArtistTeamUser,
                and_(
                    ArtistTeamUser.artist_team_id == ArtistTeam.id,
                    ArtistTeamUser.user_id == user_id
                )
            )
            .filter(Artist.external_id.in_(artists_ids))
            .all()
        )

    def get_artist_for_user_without_team_users_in_label(self, user_id: int, label_id: int, artists_ids: List[str]):
        return (
            db.session.query(Artist)
            .outerjoin(
                ArtistTeam,
                and_(
                    ArtistTeam.label_id == label_id,
                    ArtistTeam.artist_id == Artist.id
                )
            )
            .outerjoin(
                ArtistTeamUser,
                and_(
                    ArtistTeamUser.artist_team_id == ArtistTeam.id,
                    ArtistTeamUser.user_id == user_id
                )
            )
            .filter(Artist.external_id.in_(artists_ids), ArtistTeamUser.id.is_(None))
            .all()
        )

    def get_users_for_artist_with_team_users_in_label(self, users_ids: List[int], label_id: int, artist_id: str):
        return (
            db.session.query(User)
            .join(Artist, Artist.external_id == artist_id)
            .join(
                ArtistTeam,
                and_(
                    ArtistTeam.label_id == label_id,
                    ArtistTeam.artist_id == Artist.id
                )
            )
            .join(
                ArtistTeamUser,
                and_(
                    ArtistTeamUser.artist_team_id == ArtistTeam.id,
                    ArtistTeamUser.user_id == User.id
                )
            )
            .filter(User.id.in_(users_ids))
            .group_by(User.id)
            .all()
        )

    def get_users_for_artist_without_team_users_in_label(self, users_ids: List[int], label_id: int, artist_id: str):
        return (
            db.session.query(User)
            .join(Artist, Artist.external_id == artist_id)
            .outerjoin(
                ArtistTeam,
                and_(
                    ArtistTeam.label_id == label_id,
                    ArtistTeam.artist_id == Artist.id
                )
            )
            .outerjoin(
                ArtistTeamUser,
                and_(
                    ArtistTeamUser.artist_team_id == ArtistTeam.id,
                    ArtistTeamUser.user_id == User.id
                )
            )
            .filter(User.id.in_(users_ids), ArtistTeamUser.id.is_(None))
            .group_by(User.id)
            .all()
        )