import sqlalchemy as sa
from anydi import singleton

from dmp.adapters.db import ReportingDB
from dmp.config import Settings
from dmp.shopify.dtos import ShopifyStoreArtist
from dmp.shopify.validators import ShopifyStoreArtistListValidator


@singleton
class StoreArtistRepository:
    def __init__(self, db: ReportingDB, settings: Settings) -> None:
        self.db = db
        self.settings = settings

    def count_per_artist_by_association_id(
        self, *, association_id: str
    ) -> list[ShopifyStoreArtist]:
        query = f"""SELECT ca.GLOBAL_PARTICIPANT_ID,
       COUNT(DISTINCT ca.COLLECTION_ID) collections_count,
       COUNT(DISTINCT cp.PRODUCT_ID)    products_count
FROM SHOPIFY_COLLECTION_ARTIST ca
         INNER JOIN SHOPIFY_STORE_ASSOCIATION sa ON sa.ID = ca.ASSOCIATION_ID
         INNER JOIN {self.settings.snowflake_shopify_schema}.SHOPIFY_COLLECTION c
                    ON sa.FIVETRAN_SCHEMA = c.SOURCE_SCHEMA AND ca.COLLECTION_ID = c.ID
         LEFT JOIN {self.settings.snowflake_shopify_schema}.SHOPIFY_COLLECTION_PRODUCT cp
                   ON sa.FIVETRAN_SCHEMA = c.SOURCE_SCHEMA AND ca.COLLECTION_ID = cp.COLLECTION_ID
WHERE ca.ASSOCIATION_ID = :association_id
GROUP BY ca.GLOBAL_PARTICIPANT_ID
ORDER BY 2 DESC, 3 DESC;
        """.strip()
        bind_params = {"association_id": association_id}

        result = self.db.session.execute(sa.text(query).bindparams(**bind_params))
        return ShopifyStoreArtistListValidator.validate_python(result.mappings())