from sqlalchemy.dialects.postgresql import aggregate_order_by
from sqlalchemy import and_, case, func

from db import db
from models import (
    Label,
    user_label,
    User,
    ArtistTeam,
    ArtistTeamUser,
    Artist,
)
from models.user_project import UserProjectRoles
from shared.query_builders.label_artists_query_builder import LabelArtistsQueryBuilder, DynamicFields


class LabelPermissionsRepository:

    def count_team_members(self, label_id: int):
        return (
            db.session.query(func.count(User.id))
            .select_from(User)
            .join(user_label, user_label.c.user_id == User.id)
            .filter(user_label.c.label_id == label_id)
            .scalar()
        )

    def count_artists(self, label_id: int):
        return LabelArtistsQueryBuilder(label_id).count_query().scalar()

    def get_team_member_permissions(self, label_id: int):
        artists_subquery = (
            db.session.query(
                Artist.external_id.label("id"),
                Artist.name.label("name"),
                User.id.label("user_id")
            )
            .select_from(User)
            .join(ArtistTeam, ArtistTeam.label_id == label_id)
            .join(
                ArtistTeamUser,
                and_(ArtistTeamUser.artist_team_id == ArtistTeam.id, ArtistTeamUser.user_id == User.id)
            )
            .join(Artist, Artist.id == ArtistTeam.artist_id)
            .subquery()
        )

        return (
            db.session.query(
                User.id.label("id"),
                User.email.label("email"),
                User.name.label("name"),
                case(
                    [(User.is_admin.is_(True), UserProjectRoles.ADMIN.value)],
                    else_=UserProjectRoles.EDITOR.value
                ).label("role"),
                func.json_strip_nulls(func.json_agg(
                    aggregate_order_by(
                        func.json_build_object("id", artists_subquery.c.id, "name", artists_subquery.c.name),
                        artists_subquery.c.name.asc()
                    )
                )).label("artists"),
            )
            .select_from(Label)
            .join(user_label, user_label.c.label_id == Label.id)
            .join(User, User.id == user_label.c.user_id)
            .outerjoin(artists_subquery, artists_subquery.c.user_id == User.id)
            .filter(Label.id == label_id)
            .group_by(User.id, User.email)
            .order_by(
                case(
                    [(User.is_admin.is_(True), UserProjectRoles.ADMIN.value)],
                    else_=UserProjectRoles.EDITOR.value
                ).asc(),
                User.name.asc()
            )
        )

    def get_label_artists(self, label_id: int):
        builder = LabelArtistsQueryBuilder(label_id)
        builder.dynamic_fields = [DynamicFields.TEAM_MEMBERS]
        return builder.items_query().all()