import config
from utils.snowflake.constants import LABEL_EXTERNAL_IDS


class Query:
    @staticmethod
    def fetch_gras_projects_query():
        return f"""
            WITH
                artists_stats AS (
                  SELECT
                      project.REC_PROJECT_NUMBER as stats_project_id,
                      participant.PARTICIP_NO as artist_id,
                      participant.PARTICIP_FULL_NAME as artist_name,
                      participant.PARTICIP_TYPE_NAME as artist_type,
                      group_member.PARTICIP_NO as member_id,
                      group_member.PARTICIP_FULL_NAME as member_name,
                      p_member.MEMBER_TYPE_NAME as member_type,
                      COUNT(group_member.PARTICIP_NO) as member_popularity,
                      COUNT(participant.PARTICIP_NO) as artist_popularity,

                      project.REC_PROJECT_NUMBER as main_stats_project_id,
                      main_p.PARTICIP_NO as main_artist_id,
                      main_p.PARTICIP_FULL_NAME as main_artist_name,
                      main_p.PARTICIP_TYPE_NAME as main_artist_type,
                      m_group_member.PARTICIP_NO as main_member_id,
                      m_group_member.PARTICIP_FULL_NAME as main_member_name,
                      mp_member.MEMBER_TYPE_NAME as main_member_type,
                      COUNT(m_group_member.PARTICIP_NO) as main_member_popularity,
                      COUNT(main_p.PARTICIP_NO) as main_artist_popularity
                  FROM "V_GRAS_RECORDING_PROJECT" as project
                  LEFT OUTER JOIN "V_GRAS_REC_PROJECT_PRODUCT_VERSION" as project_product_v
                    ON (project.REC_PROJECT_ID = project_product_v.REC_PROJECT_ID)
                  LEFT OUTER JOIN "V_GRAS_PRODUCT_VERSION" as product_version
                    ON (project_product_v.PROD_VERS_NO = product_version.PROD_VERS_NO)
                  LEFT OUTER JOIN "V_GRAS_PARTICIPANT" as main_p
                    ON (product_version.MAIN_ARTIST_NO = main_p.PARTICIP_NO)
                  LEFT OUTER JOIN "V_GRAS_PARTICIPANT_MEMBER" as mp_member
                    ON (main_p.PARTICIP_NO = mp_member.PARTICIP_NO)
                  LEFT OUTER JOIN "V_GRAS_PARTICIPANT" as m_group_member
                    ON (mp_member.MEMBER_PARTICIP_NO = m_group_member.PARTICIP_NO)

                  LEFT OUTER JOIN "V_GRAS_PRODUCT" as product ON (product.PROD_VERS_NO = product_version.PROD_FAM_NO)
                  LEFT OUTER JOIN "V_GRAS_PRODUCT_TRACK" as product_track ON (product.PROD_NO = product_track.PROD_NO)
                  LEFT OUTER JOIN "V_GRAS_TRACK" as track ON (product_track.TRACK_NO = track.TRACK_NO)
                  LEFT OUTER JOIN "V_GRAS_TRACK_PARTICIPANT" as track_participant
                    ON (track.TRACK_NO = track_participant.TRACK_NO)
                  LEFT OUTER JOIN "V_GRAS_PARTICIPANT" as participant
                    ON (track_participant.PARTICIP_NO = participant.PARTICIP_NO)
                  LEFT OUTER JOIN "V_GRAS_PARTICIPANT_MEMBER" as p_member
                    ON (participant.PARTICIP_NO = p_member.PARTICIP_NO)
                  LEFT OUTER JOIN "V_GRAS_PARTICIPANT" as group_member
                    ON (p_member.MEMBER_PARTICIP_NO = group_member.PARTICIP_NO)
                  WHERE (member_type ILIKE 'Primary' OR member_type ILIKE 'Featured' OR member_type IS NULL)
                  GROUP BY main_stats_project_id, main_artist_id, main_artist_name, main_artist_type, main_member_id,
                           main_member_name, main_member_type, stats_project_id, artist_id, artist_name, artist_type,
                           member_id, member_name, member_type
            )
            SELECT DISTINCT
                project.REC_PROJECT_NUMBER as project_id,
                project.REC_PROJECT_TITLE as title,
                company.COMPANY_KEY as parent_rep_owner_id,
                TO_DATE(project.RAAS_INIT_DATE, 'YYYYMMDD') as project_start_date,
                CASE WHEN project.MOD_FLAG = 'D' THEN True ELSE False END as is_deleted,

                artists_stats.main_artist_id as main_art_id,
                artists_stats.main_artist_name as main_art_name,
                artists_stats.main_artist_type as main_art_type,
                artists_stats.main_artist_popularity as main_art_popularity,
                artists_stats.main_member_id as main_memb_id,
                artists_stats.main_member_name as main_memb_name,
                artists_stats.main_member_type as main_memb_type,
                artists_stats.main_member_popularity as main_memb_popularity,

                artists_stats.artist_id as participant_id,
                artists_stats.artist_name as participant_name,
                artists_stats.artist_type as participant_type,
                artists_stats.artist_popularity as participant_popularity,
                artists_stats.member_id as member_id,
                artists_stats.member_name as member_name,
                artists_stats.member_type as member_type,
                artists_stats.member_popularity as member_popularity
            FROM "V_GRAS_RECORDING_PROJECT" as project
            JOIN "V_GRAS_REPERTOIRE_OWNER_HIERARCHY" as rep_owner ON (project.REP_OWNER_KEY = rep_owner.REP_OWNER_KEY)
            JOIN "V_GRAS_COMPANY" as company ON (company.COMPANY_KEY = rep_owner.PARENT_REP_OWNER_KEY)
            LEFT OUTER JOIN artists_stats ON (project.REC_PROJECT_NUMBER = artists_stats.stats_project_id)
            WHERE project.REC_PROJECT_TYPE_NAME ILIKE 'business affairs approved'
                AND project_start_date > '{config.EARLIEST_SNOWFLAKE_DATA_FETCH_DATE}'
                AND parent_rep_owner_id IN {LABEL_EXTERNAL_IDS}
            ORDER BY project_id
         """

    @staticmethod
    def fetch_rep_owners():
        return """
            SELECT DISTINCT
                company.COMPANY_KEY as parent_rep_owner_id,
                company.COMPANY_NAME as parent_rep_owner_name,
                COUNT(project.REC_PROJECT_ID) as proj_count
            FROM "V_GRAS_RECORDING_PROJECT" as project
            LEFT OUTER JOIN "V_GRAS_REPERTOIRE_OWNER_HIERARCHY" as rep_owner
                ON (project.REP_OWNER_KEY = rep_owner.REP_OWNER_KEY )
            LEFT OUTER JOIN "V_GRAS_COMPANY" as company ON (company.COMPANY_KEY = rep_owner.PARENT_REP_OWNER_KEY)
            WHERE parent_rep_owner_id != '9999' AND parent_rep_owner_id IS NOT NULL
            GROUP BY parent_rep_owner_id,parent_rep_owner_name
            ORDER BY proj_count DESC
        """

    @staticmethod
    def fetch_rep_owners_for_various_tracks_projects():
        return """
            SELECT
              project.REC_PROJECT_NUMBER as tracks_rep_owners_project_id,
              TRO.COMPANY_KEY as track_rep_owner_id,
              COUNT(TRO.COMPANY_KEY) as rep_owner_popularity
            FROM "V_GRAS_RECORDING_PROJECT" as project
            JOIN "V_GRAS_REPERTOIRE_OWNER_HIERARCHY" as PROH ON (project.REP_OWNER_KEY = PROH.REP_OWNER_KEY)
            JOIN "V_GRAS_COMPANY" as PRO ON (PRO.COMPANY_KEY = PROH.PARENT_REP_OWNER_KEY)
            JOIN "V_GRAS_REC_PROJECT_PRODUCT_VERSION" as project_product_v
                ON (project.REC_PROJECT_ID = project_product_v.REC_PROJECT_ID)
            JOIN "V_GRAS_PRODUCT_VERSION" as product_version
                ON (project_product_v.PROD_VERS_NO = product_version.PROD_VERS_NO)
            JOIN "V_GRAS_PRODUCT" as product ON (product.PROD_VERS_NO = product_version.PROD_FAM_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)
            JOIN "V_GRAS_REPERTOIRE_OWNER_HIERARCHY" as TROH ON (track.REP_OWNER_KEY = TROH.REP_OWNER_KEY)
            JOIN "V_GRAS_COMPANY" as TRO ON (TRO.COMPANY_KEY = TROH.PARENT_REP_OWNER_KEY)
            WHERE PRO.COMPANY_KEY = '9999'
            GROUP BY tracks_rep_owners_project_id, track_rep_owner_id
            ORDER BY tracks_rep_owners_project_id ASC, rep_owner_popularity DESC
        """

    @staticmethod
    def fetch_products_info():
        return f"""
            SELECT DISTINCT
                project.REC_PROJECT_NUMBER,
                product_version.PROD_FAM_NO,
                product_version.PROD_TITLE,
                product_version.CONFIG_CAT_NAME,
                artist.PARTICIP_NO,
                artist.PARTICIP_FULL_NAME,
                m_group_member.PARTICIP_NO,
                m_group_member.PARTICIP_FULL_NAME,
                track.TRACK_NAME,
                track.TRACK_NAME_SUPPL,
                track.TRACK_TYPE_NAME,
                track.ISRC,
                track.ORIG_RELEASE_DATE
            FROM "V_GRAS_RECORDING_PROJECT" as project
            JOIN "V_GRAS_REC_PROJECT_PRODUCT_VERSION" as project_product_version
                ON project.REC_PROJECT_ID = project_product_version.REC_PROJECT_ID
            JOIN "V_GRAS_PRODUCT_VERSION" as product_version
                ON project_product_version.PROD_VERS_NO = product_version.PROD_VERS_NO
            JOIN "V_GRAS_PARTICIPANT" as artist ON product_version.MAIN_ARTIST_NO = artist.PARTICIP_NO
            JOIN "V_GRAS_PRODUCT" as product ON product_version.PROD_VERS_NO = product.PROD_VERS_NO
            JOIN "V_GRAS_PRODUCT_TRACK" as product_track ON product_track.PROD_NO = product.PROD_NO
            JOIN "V_GRAS_TRACK" as track ON track.TRACK_NO = product_track.TRACK_NO
            LEFT OUTER JOIN "V_GRAS_PARTICIPANT_MEMBER" as mp_member ON artist.PARTICIP_NO = mp_member.PARTICIP_NO
            LEFT OUTER JOIN "V_GRAS_PARTICIPANT" as m_group_member
                ON mp_member.MEMBER_PARTICIP_NO = m_group_member.PARTICIP_NO
            WHERE project.REC_PROJECT_TYPE_NAME ILIKE 'business affairs approved'
                  AND TO_DATE(project.RAAS_INIT_DATE, 'YYYYMMDD') > '{config.EARLIEST_SNOWFLAKE_DATA_FETCH_DATE}'
            ORDER BY project.REC_PROJECT_NUMBER, product_version.PROD_FAM_NO
        """