from typing import List
from datetime import date
from utils.snowflake.gras_data.constants import MomentTypes


class ArtistMomentsQuery:
    @staticmethod
    def moments_queries(gras_ids: List[str]) -> str:
        max_date = date.strftime(date(date.today().year + 2, 1, 1), "%Y%m%d")
        return f"""
            SELECT
                participant.PARTICIP_NO,
                participant.PARTICIP_FULL_NAME as title,
                OBJECT_CONSTRUCT(
                    'death', TO_DATE(participant.DATE_OF_DEATH, 'YYYYMMDD'),
                    'birth', TO_DATE(participant.DATE_OF_BIRTH, 'YYYYMMDD')
                ) AS dates,
                {MomentTypes.PERSONAL_DEATH_BIRTH.value} AS type
            FROM V_GRAS_PARTICIPANT AS participant
            WHERE (participant.DATE_OF_BIRTH IS NOT NULL OR participant.DATE_OF_DEATH IS NOT NULL)
                AND participant.PARTICIP_NO IN ({", ".join(gras_ids)})
            UNION (
                SELECT
                    participant.PARTICIP_NO,
                    CASE
                        WHEN product.CONFIG_CAT_NAME IN ('Singles', 'Digital Singles ( One Track Link )')
                            THEN track.TRACK_NAME || COALESCE(' (' || track.TRACK_NAME_SUPPL || ')', '')
                        ELSE product_version.PROD_TITLE
                    END as title,
                    CASE
                        WHEN product.CONFIG_CAT_NAME IN ('Singles', 'Digital Singles ( One Track Link )')
                            THEN OBJECT_CONSTRUCT('date', TO_DATE(track.ORIG_RELEASE_DATE, 'YYYYMMDD'))
                        ELSE OBJECT_CONSTRUCT('date', TO_DATE(product.first_release_date, 'YYYYMMDD'))
                    END as dates,
                CASE
                    WHEN product.CONFIG_CAT_NAME IN ('Singles', 'Digital Singles ( One Track Link )')
                        THEN {MomentTypes.SINGLE_RELEASE.value}
                    WHEN product.CONFIG_CAT_NAME = 'Longplay'
                        THEN {MomentTypes.ALBUM_RELEASE.value}
                    WHEN product.CONFIG_CAT_NAME = 'Master Use License'
                        THEN {MomentTypes.CULTURE_REACTIVE_SYNC.value}
                    WHEN product.CONFIG_CAT_NAME = 'Video'
                        THEN {MomentTypes.VIDEO_RELEASE.value}
                END AS type
                FROM V_GRAS_PARTICIPANT     AS participant
                JOIN V_GRAS_PRODUCT_VERSION AS product_version
                    ON (product_version.MAIN_ARTIST_NO = participant.PARTICIP_NO
                        AND product_version.IS_PROD_FAM_HEAD = 'Y')
                JOIN V_GRAS_PRODUCT         AS product         ON product.PROD_VERS_NO = product_version.PROD_VERS_NO
                JOIN V_GRAS_PRODUCT_TRACK   AS product_track   ON product.PROD_NO = product_track.PROD_NO
                JOIN V_GRAS_TRACK           AS track           ON product_track.TRACK_NO = track.TRACK_NO
                WHERE participant.PARTICIP_NO IN ({", ".join(gras_ids)})
                    AND (
                        (
                            product.first_release_date IS NOT NULL
                            AND product.CONFIG_CAT_NAME IN ('Longplay', 'Master Use License', 'Video')
                            AND product.first_release_date < {max_date}
                        )
                        OR
                        (
                            track.ORIG_RELEASE_DATE IS NOT NULL
                            AND product.CONFIG_CAT_NAME IN ('Singles', 'Digital Singles ( One Track Link )')
                            AND track.ORIG_RELEASE_DATE < {max_date}
                        )
                    )
                GROUP BY participant.PARTICIP_NO, product.PROD_NO, title, dates, type
            )
        """


class ProductFamiliesQuery:
    def __projects_subquery(self, external_ids: List[str]):
        projects = ["({})".format(item) for item in external_ids]
        return f"""SELECT * FROM ( VALUES {",".join(projects)} ) AS q (gras_project_number) """

    def fetch_products_info(self, external_ids: List[str]):
        projects_subquery = self.__projects_subquery(external_ids)
        return f"""
            WITH PROJECTS_SUBQUERY as ({
                projects_subquery
            })
            SELECT DISTINCT
                project.REC_PROJECT_NUMBER,
                product_version.PROD_FAM_NO,
                product_version.PROD_TITLE,
                product_version.CONFIG_CAT_NAME
            FROM "V_GRAS_RECORDING_PROJECT" as project
            JOIN PROJECTS_SUBQUERY as projects_subquery
                ON project.REC_PROJECT_NUMBER = projects_subquery.gras_project_number
            JOIN "V_GRAS_REC_PROJECT_PRODUCT_VERSION" as project_product_version
                ON project.REC_PROJECT_ID = project_product_version.REC_PROJECT_ID AND project_product_version.mod_flag != 'D'
            JOIN "V_GRAS_PRODUCT_VERSION" as product_version
                ON project_product_version.PROD_VERS_NO = product_version.PROD_VERS_NO AND product_version.CONFIG_CAT_KEY = 'D' AND product_version.mod_flag != 'D'
            JOIN "V_GRAS_PRODUCT" as product ON product_version.PROD_VERS_NO = product.PROD_VERS_NO AND product.mod_flag != 'D'
            WHERE project.REC_PROJECT_TYPE_NAME ILIKE 'business affairs approved'
            GROUP BY project.REC_PROJECT_NUMBER, product_version.PROD_FAM_NO, product_version.PROD_TITLE, product_version.CONFIG_CAT_NAME
            ORDER BY project.REC_PROJECT_NUMBER, product_version.PROD_FAM_NO
        """


class ProtectedProjectsQuery:
    def __projects_subquery(self, external_ids: List[str]):
        projects = ["({})".format(item) for item in external_ids]
        return f"""SELECT * FROM ( VALUES {",".join(projects)} ) AS q (gras_project_number) """

    def protected_query(self, external_ids: List[str]):
        projects_subquery = self.__projects_subquery(external_ids)
        return f"""
            WITH PROJECTS_SUBQUERY as ({
                projects_subquery
            })
            SELECT
                projects_subquery.gras_project_number,
                CASE WHEN project.REC_PROJECT_NUMBER IS NOT NULL THEN 0 ELSE 1 END AS is_confidential
            FROM PROJECTS_SUBQUERY as projects_subquery
            LEFT OUTER JOIN "V_GRAS_RECORDING_PROJECT" as project
                ON PROJECTS_SUBQUERY.gras_project_number = project.REC_PROJECT_NUMBER;
        """


class ArtistsByParticipantNoQuery:
    def __artists_subquery(self, external_ids: List[str]):
        artists = ["({})".format(item) for item in external_ids]
        return f"""SELECT * FROM ( VALUES {",".join(artists)} ) AS q (participant_no)"""

    def artists_query(self, external_ids: List[str]):
        return f"""
            WITH ARTISTS_SUBQUERY as ({self.__artists_subquery(external_ids)})
            SELECT PARTICIP_NO, PARTICIP_FULL_NAME
            FROM "V_GRAS_PARTICIPANT" as gras_participant
            JOIN ARTISTS_SUBQUERY as artists_subquery
                ON gras_participant.PARTICIP_NO = artists_subquery.participant_no
        """