from sqlalchemy import text

from delivery_metadata.api.app import app
from delivery_metadata.models.schemas import GenreMapping


async def get_genre_mapping(store_id: int) -> list[GenreMapping]:
    async with app.state.art_relations_connector.db_session() as session:
        result = await session.execute(
            text(
                """
                SELECT genre_mapping.genre, genre_mapping.genre_code,
                    COALESCE(genre_mapping.subgenre, NULL) AS subgenre,
                    COALESCE(genre_mapping.subgenre_code, NULL) AS subgenre_code,
                    genre_mapping.subgenre_id
                FROM (
                    SELECT dmg.dms_master_genre AS genre, dmg.genre_code,
                        dms.dms_master_subgenre AS subgenre, dms.subgenre_code,
                        sg.orchard_id AS subgenre_id
                    FROM dms_subgenre_mapping dsm
                    INNER JOIN dms_master_subgenre dms ON dsm.dms_master_subgenre_id = dms.dms_master_subgenre_id
                    INNER JOIN dms_master_genre dmg ON dmg.dms_master_genre_id = dms.dms_master_genre_id
                    INNER JOIN subgenre sg ON sg.orchard_id = dsm.orchard_subgenre_id
                    INNER JOIN genre g ON sg.genre_id = g.genre_id
                    WHERE dmg.customer_master_master_id = :store_id

                    UNION ALL

                    SELECT dmg.dms_master_genre AS genre, dmg.genre_code, NULL AS subgenre,
                        NULL AS subgenre_code, sg.orchard_id AS subgenre_id
                    FROM dms_genre_mapping dgm
                    INNER JOIN dms_master_genre dmg ON dgm.dms_master_genre_id = dmg.dms_master_genre_id
                    INNER JOIN subgenre sg ON sg.orchard_id = dgm.orchard_subgenre_id
                    INNER JOIN genre g ON sg.genre_id = g.genre_id
                    WHERE customer_master_master_id = :store_id
                ) genre_mapping
                GROUP BY subgenre_id
                """
            ),
            {
                "store_id": store_id,
            },
        )

        return [
            GenreMapping(
                genre=item.genre,
                genre_code=item.genre_code,
                subgenre=item.subgenre,
                subgenre_code=item.subgenre_code,
                subgenre_id=item.subgenre_id,
            )
            for item in result.mappings().all()
        ]