import sqlalchemy as sa

from dmp.adapters.db import ReportingDB, ReportingRepository
from dmp.config import Settings
from dmp.shopify.dtos import ShopifyCollectionWithArtist
from dmp.shopify.models import ShopifyCollection
from dmp.shopify.validators import ShopifyCollectionWithArtistListValidator


class CollectionRepository(ReportingRepository[ShopifyCollection]):
    def __init__(self, db: ReportingDB, settings: Settings):
        super().__init__(db)
        self.settings = settings

    def find_by_association_id(
        self, *, association_id: str
    ) -> list[ShopifyCollectionWithArtist]:
        query = f"""
            SELECT c.id, c.title, c.products_count, ca.global_participant_id
            FROM {self.settings.snowflake_shopify_schema}.shopify_collection c
                JOIN shopify_store_association a
                    ON a.fivetran_schema = c.source_schema
                LEFT JOIN shopify_collection_artist ca
                    ON a.id = ca.association_id AND c.id = ca.collection_id
            WHERE a.id = :association_id
            ORDER BY c.title;
        """.strip()
        result = self.db.session.execute(
            sa.text(query).bindparams(association_id=association_id)
        )
        return ShopifyCollectionWithArtistListValidator.validate_python(
            result.mappings()
        )