from collections.abc import Sequence
from typing import cast

import sqlalchemy as sa

from dmp.adapters.db import ReportingRepository
from dmp.rosters.models import ArtistRosterMainRep


class ArtistRosterMainRepRepository(ReportingRepository[ArtistRosterMainRep]):
    def find_by_account_and_global_participant_ids(
        self, vendor_id: int, subaccount_id: int, global_participant_ids: list[str]
    ) -> Sequence[ArtistRosterMainRep]:
        if not global_participant_ids:
            return []
        stmt = sa.select(ArtistRosterMainRep).where(
            ArtistRosterMainRep.vendor_id == vendor_id,
            ArtistRosterMainRep.subaccount_id == subaccount_id,
            ArtistRosterMainRep.global_participant_id.in_(global_participant_ids),
        )
        result = self.db.session.execute(stmt)
        return result.scalars().all()

    def exists_by_account_and_global_participant_ids(
        self, vendor_id: int, subaccount_id: int, global_participant_ids: list[str]
    ) -> bool:
        if not global_participant_ids:
            return False
        stmt = sa.select(
            sa.select(1)
            .exists()
            .where(
                ArtistRosterMainRep.vendor_id == vendor_id,
                ArtistRosterMainRep.subaccount_id == subaccount_id,
                ArtistRosterMainRep.global_participant_id.in_(global_participant_ids),
            )
        )
        result = self.db.session.execute(stmt)
        return bool(result.scalar_one())

    def exists_by_artist_and_any_account(
        self,
        *,
        global_participant_id: str,
        vendor_ids: list[int],
        subaccount_ids: list[int],
    ) -> bool:
        if not vendor_ids:
            return False
        query = self.db.query_from_template(
            "main-rep/exists-by-artist-and-any-account.sql",
            context={
                "global_participant_id": global_participant_id,
                "vendor_ids": vendor_ids,
                "subaccount_ids": subaccount_ids,
            },
        )
        result = self.db.session.execute(query)
        return cast(bool, result.scalar_one())

    def find_by_artist_and_any_account(
        self,
        *,
        global_participant_id: str,
        vendor_ids: list[int],
        subaccount_ids: list[int],
    ) -> Sequence[ArtistRosterMainRep]:
        if not vendor_ids:
            return []
        query = self.db.query_from_template(
            "main-rep/find-by-artist-and-any-account.sql",
            context={
                "global_participant_id": global_participant_id,
                "vendor_ids": vendor_ids,
                "subaccount_ids": subaccount_ids,
            },
        )
        result = self.db.session.execute(
            sa.select(ArtistRosterMainRep).from_statement(query)
        )
        return result.scalars().all()