from datetime import date
from typing import Iterable

from ....typings import Email, SqlQuery
from . import helpers
from .typings import LabelId


# noinspection SqlResolve
def get_countries() -> SqlQuery:
    """Get a list of country and region metadata."""
    return SqlQuery(
        """
        SELECT CR.ID,
               CR.NAME,
               C.COUNTRY_CODE AS CODE
        FROM INTELLIGENCE.DBT_PROD.DT_FLATTEN_COUNTRY_REGION CR
                 LEFT JOIN FACTS.PROD.DIM_COUNTRY C
                           ON CR.ID = C.COUNTRYID
        """
    )


# noinspection SqlResolve
def get_label_meta(label_ids: list[LabelId]) -> SqlQuery:
    """
    Get label metadata.

    Args:
        label_ids: List of label IDs to fetch metadata for. Example: [33960, 12345].
    """
    if not label_ids:
        raise ValueError("label_ids must not be empty")
    label_ids_string = ",".join(map(str, set(label_ids)))

    return SqlQuery(
        f"""
        WITH CONTRACTS AS (
            SELECT
                 vc.vendor_id,
                 vc.cont_start AS contract_start,
                 vc.lifecycle_term_end AS contract_end,
                 OA_USERS.F_NAME || ' ' || OA_USERS.L_NAME AS closer,
                 ROUND(MONTHS_BETWEEN(vc.lifecycle_term_end, vc.cont_start)/6) / 2 AS term,
                 CASE
                     WHEN vc.digital_split IS NULL THEN NULL
                     ELSE CAST(ROUND(1 - vc.digital_split, 2) AS DECIMAL(10, 2))
                 END AS digital_fee,
                 CASE
                     WHEN vc.digital_split IS NULL THEN NULL
                     ELSE CAST(ROUND(1 - vc.digital_split, 2) AS DECIMAL(10, 2))
                 END AS physical_fee,
                 IFF(MAX(NULLIF(TRIM(dms.value), '')) IS NULL,
                     ARRAY_CONSTRUCT(),  -- Empty array if no DMS Carve Outs
                     ARRAY_AGG(DISTINCT COALESCE(store.storename, dms.value))) AS dms_carve_out,
                 IFF(MAX(NULLIF(TRIM(terr.value), '')) IS NULL,
                     ARRAY_CONSTRUCT(),  -- Empty array if no Territory Carve Outs
                     ARRAY_AGG(DISTINCT COALESCE(country.COUNTRYNAME, terr.value))) AS territory_carve_out,
                 ROW_NUMBER() OVER (
                        PARTITION BY vc.vendor_id
                        ORDER BY vc.cont_start DESC
                     ) AS rn
             FROM ROYALTY_ACCOUNTING_REPORTING.PROD.VW_DIM_ABACUS_AR_VENDOR_CONTRACT vc
             LEFT JOIN LATERAL FLATTEN(input => SPLIT(COALESCE(vc.dms_carve_out, ''), ',')) dms
             LEFT JOIN FACTS.PROD.DIM_STORE store
                 ON TO_NUMBER(NULLIF(TRIM(dms.value), '')) = store.storeid

             LEFT JOIN LATERAL FLATTEN(input => SPLIT(COALESCE(vc.territory_carve_out, ''), ',')) terr
             LEFT JOIN FACTS.PROD.DIM_COUNTRY country
                 ON TO_NUMBER(NULLIF(TRIM(terr.value), '')) = country.COUNTRYID
             LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.ORCHADMIN_USERS OA_USERS
                 ON vc.orchrep_name = OA_USERS.ID
             WHERE vc.VENDOR_ID IN ({label_ids_string})
                AND vc.CONT_START IS NOT NULL
                AND vc.DIGITAL_SPLIT IS NOT NULL  -- Do not include flow-through contracts
             GROUP BY 1,2,3,4,5,6
            ),

            CONTRACT_CURRENT AS (
                SELECT *
                FROM CONTRACTS
                QUALIFY rn = 1
            ),

            -- Most recent: today's date as maximum release date
            LABEL_RELEASE_MOST_RECENT AS (
                SELECT
                    DR.LABELID,
                    DATE(MAX(DR.RELEASEDATE)) AS release_date
                FROM FACTS.PROD.DIM_RELEASE DR
                RIGHT JOIN CONTRACT_CURRENT CC
                    ON CC.vendor_id = DR.LABELID  -- Only selected label IDs
                WHERE DR.NOT_FOR_DISTRIBUTION = 'N'
                    AND DR.RELEASEDATE <= CURRENT_DATE
                GROUP BY LABELID
            ),

            -- Latest: maximum release date for the label, even if it is in the future
            LABEL_RELEASE_LATEST AS (
                SELECT
                    DR.LABELID,
                    DATE(MAX(DR.RELEASEDATE)) AS release_date
                FROM FACTS.PROD.DIM_RELEASE DR
                RIGHT JOIN CONTRACT_CURRENT CC
                    ON CC.vendor_id = DR.LABELID  -- Only selected label IDs
                WHERE DR.NOT_FOR_DISTRIBUTION = 'N'
                GROUP BY LABELID
            ),

            LABEL_RELEASE_AVERAGE AS (
                SELECT
                    DR.LABELID,
                    COUNT(*) / (
                        DATEDIFF(
                            DAY,
                            GREATEST(CC.contract_start, DATEADD(YEAR, -3, CURRENT_DATE)),
                            CURRENT_DATE
                        ) / 365.25
                    ) AS release_average_year
                FROM FACTS.PROD.DIM_RELEASE DR
                RIGHT JOIN CONTRACT_CURRENT CC
                    ON DR.LABELID = CC.vendor_id  -- Only selected label IDs
                WHERE DR.NOT_FOR_DISTRIBUTION = 'N'
                  AND DATE(DR.RELEASEDATE) BETWEEN GREATEST(CC.contract_start, DATEADD(YEAR, -3, CURRENT_DATE)) AND CURRENT_DATE  -- Contract start or 3 year average
                GROUP BY DR.LABELID,CC.contract_start
            )


            SELECT
                VENDOR.vendor_id AS label_id,
                VENDOR.name,
                VENDOR.company,
                VENDOR.owner,
                VENDOR.status,
                TIER_GROUPS.SERVICE_TIER_NAME AS service_tier,
                CONCAT(OA_USERS.F_NAME || ' ' || OA_USERS.L_NAME) AS label_manager,
                OBJECT_CONSTRUCT(
                    'date_start', CONTRACT_CURRENT.contract_start,
                    'date_end', CONTRACT_CURRENT.contract_end,
                    'closer', CONTRACT_CURRENT.closer,
                    'term', CONTRACT_CURRENT.term,
                    'digital_fee', CONTRACT_CURRENT.digital_fee,
                    'dms_carve_out', CONTRACT_CURRENT.dms_carve_out,
                    'territory_carve_out', CONTRACT_CURRENT.territory_carve_out
                ) AS contract,
                OBJECT_CONSTRUCT(
                    'full_name',
                        TRIM(REGEXP_REPLACE(
                            COALESCE(CONTACT_FIRST_NAME, '') || ' ' ||
                            COALESCE(CONTACT_MIDDLE_NAME, '') || ' ' ||
                            COALESCE(CONTACT_LAST_NAME, ''),
                            '\\\\s+', ' '
                        )),
                    'address',  -- Clean address
                        REGEXP_REPLACE(
                            TRIM(REGEXP_REPLACE(
                                COALESCE(ADDRESS_STREET, '') ||
                                COALESCE(', ' || ADDRESS_CITY, '') ||
                                COALESCE(', ' || STATE.NAME, '') ||
                                COALESCE(', ' || ADDRESS_ZIP, '') ||
                                COALESCE(' (' || COUNTRY.COUNTRYNAME || ')', ''),
                                '\\\\s+', ' '  -- First replace multiple spaces with single space
                            )),
                            '^[^a-zA-Z0-9)]+|[^a-zA-Z0-9)]+$', ''  -- Then remove non-alphanumeric/non-) chars from start and end
                        )
                ) AS master_contact,
                LRMR.release_date AS release_date_most_recent,
                LRL.release_date AS release_date_latest,
                COALESCE(LRA.release_average_year, 0) AS release_average_yearly

            FROM ROYALTY_ACCOUNTING_REPORTING.PROD.VW_DIM_ABACUS_AR_VENDOR VENDOR

            LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.ORCHADMIN_USERS OA_USERS
                ON VENDOR.ASSIGNED_TO = OA_USERS.ID

            LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS TIER_GROUPS
                ON VENDOR.VENDOR_ID = TIER_GROUPS.LABELID

            RIGHT JOIN CONTRACT_CURRENT
                USING(VENDOR_ID)  -- Only selected label IDs

            LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.VEND_CONTACT VENDOR_CONTACT
                USING(VENDOR_ID)

            LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CONTACT CONTACT
                USING(CONTACT_ID)

            LEFT JOIN FACTS.PROD.DIM_COUNTRY COUNTRY
                ON CONTACT.ORCHARD_COUNTRY = COUNTRY.COUNTRYID

            LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.ORCHARD_STATE STATE
                ON CONTACT.ORCHARD_COUNTRY = STATE.COUNTRY_ID
                AND TRY_TO_NUMBER(CONTACT.ADDRESS_STATE) = STATE.ID  -- Use 'TRY_TO_NUMBER' to handle cases where state is missing

            LEFT JOIN LABEL_RELEASE_MOST_RECENT LRMR
                ON LRMR.LABELID = VENDOR.vendor_id

            LEFT JOIN LABEL_RELEASE_LATEST LRL
                ON LRL.LABELID = VENDOR.vendor_id

            LEFT JOIN LABEL_RELEASE_AVERAGE LRA
                ON LRA.LABELID = VENDOR.vendor_id

            WHERE VENDOR_CONTACT.MASTER = 'Y'
                AND VENDOR_CONTACT.ACTIVE = 'Y'
        """
    )  # noqa: E501


# noinspection SqlResolve
def get_relationship_manager_clients(
    *,
    relationship_manager_emails: list[Email] | None = None,
    limit: int | None = None,
) -> SqlQuery:
    """Get clients by relationship manager(s).

    Args:
        relationship_manager_emails: Emails of the relationship managers to filter by.
            If None, all relationship managers will be included, as well
            as clients without a relationship manager.
        limit: Optional limit for the number of results. Default is None (no limit).
    """

    email_condition = (
        f"AND relationship_manager_email IN {_to_sql_tuple(relationship_manager_emails)}"
        if relationship_manager_emails
        else ""
    )

    limit_condition = f"LIMIT {limit}" if limit is not None else ""

    return SqlQuery(
        f"""
        SELECT
         V.ASSIGNED_TO AS relationship_manager_id,
         OAU.EMAIL AS relationship_manager_email,
         V.VENDOR_ID AS label_id,
         COALESCE(NULLIF(TRIM(V.COMPANY), ''), NULLIF(TRIM(V.NAME), ''), CONCAT('(Label #', V.VENDOR_ID, ')')) AS name
        FROM ROYALTY_ACCOUNTING_REPORTING.PROD.VW_DIM_ABACUS_AR_VENDOR V
        LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.ORCHADMIN_USERS OAU
            ON OAU.ID = V.ASSIGNED_TO
        WHERE 1=1
        {email_condition}
        {limit_condition}
        """
    )


def _filtered_month_cte(date_yearmonth: str | None) -> SqlQuery:
    """Generate a CTE to filter the month based on the provided date_yearmonth."""
    return SqlQuery(
        f"""
            WITH LATEST_COMPLETE_MONTH AS (
            SELECT year_month AS max_month
            FROM (
                SELECT
                    TO_CHAR(period_date, 'YYYY-MM') AS year_month,
                    DENSE_RANK() OVER (ORDER BY TO_CHAR(period_date, 'YYYY-MM') DESC) AS rnk
                FROM INTELLIGENCE.DBT_PROD.ORCA_CLIENT_SCORECARD_BASE
                WHERE month_gross_revenue_usd > 0
                  AND ltm_gross_revenue_usd > 0
            ) t
            WHERE rnk = 2  -- 1 = most recent (possibly incomplete), 2 = latest complete
            LIMIT 1
        ),

        FILTERED_MONTH AS (
            SELECT
                COALESCE(
                    (SELECT TO_CHAR(period_date, 'YYYY-MM')
                     FROM INTELLIGENCE.DBT_PROD.ORCA_CLIENT_SCORECARD_BASE
                     WHERE TO_CHAR(period_date, 'YYYY-MM') = '{date_yearmonth}'
                       AND TO_CHAR(period_date, 'YYYY-MM') <= (SELECT max_month FROM LATEST_COMPLETE_MONTH)
                     LIMIT 1),
                    (SELECT max_month FROM LATEST_COMPLETE_MONTH)
                ) AS selected_month
        )
        """
    )


# noinspection SqlResolve
def get_relationship_manager_stats(
    *, label_ids: list[LabelId] | None = None, date_yearmonth: str | None = None
) -> SqlQuery:
    """Get relationship manager statistics.

    Args:
        label_ids: List of label IDs to filter the results. Default is None
            (no filtering by label IDs).
        date_yearmonth: provide in 'YYYY-MM' format to get the result snapshot
            for the given month. If not provided, the query will use the most recent
            complete month.
    """
    if date_yearmonth is not None:
        helpers.validate_date_yearmonth(date_yearmonth)

    label_ids_condition = (
        f"AND LABELID IN ({','.join(map(str, set(label_ids)))})" if label_ids else ""
    )

    return SqlQuery(
        f"""
            {_filtered_month_cte(date_yearmonth)},

            BASE AS (
                SELECT *
                FROM INTELLIGENCE.DBT_PROD.ORCA_CLIENT_SCORECARD_BASE
                WHERE 1=1
                {label_ids_condition}
            ),

            BASE_AGG AS (
              SELECT
                     period_date,
                     fiscal_year,
                     relationship_manager,
                     relationship_manager_email,
                     labelid,
                     IFNULL(fiscal_year_ytd_gross_revenue_usd, 0) AS fiscal_year_ytd_gross_revenue_usd,
                     MAX(client_name)                   AS client_name,
                     SUM(ltm_gross_revenue_usd)         AS ltm_gross_revenue_usd,
                     SUM(prior_ltm_gross_revenue_usd)   AS prior_ltm_gross_revenue_usd,
                     SUM(month_gross_revenue_usd)       AS month_gross_revenue_usd,
                     SUM(prior_month_gross_revenue_usd) AS prior_month_gross_revenue_usd
              FROM BASE
              WHERE relationship_manager_email IS NOT NULL
                AND TO_CHAR(period_date, 'YYYY-MM') = (SELECT selected_month FROM FILTERED_MONTH)
              GROUP BY period_date, fiscal_year, relationship_manager_email, relationship_manager, labelid, fiscal_year_ytd_gross_revenue_usd
              ),

         RANKED_LTM AS (SELECT *,
                           ROW_NUMBER() OVER (
                               PARTITION BY relationship_manager_email
                               ORDER BY ltm_gross_revenue_usd DESC
                               ) AS client_rank
                    FROM BASE_AGG),

        RANKED_FISCAL_YEAR AS (SELECT *,
                           ROW_NUMBER() OVER (
                               PARTITION BY relationship_manager_email
                               ORDER BY fiscal_year_ytd_gross_revenue_usd DESC
                               ) AS client_rank
                    FROM BASE_AGG),

         MANAGER_SUMMARY AS (SELECT period_date,
                                    fiscal_year,
                                    relationship_manager,
                                    relationship_manager_email,
                                    COUNT(*)                   AS label_count,
                                    SUM(ltm_gross_revenue_usd) AS total_ltm_gross_revenue_usd,
                                    AVG(ltm_gross_revenue_usd) AS avg_ltm_gross_revenue_usd,
                                    AVG(
                                            CASE
                                                WHEN prior_ltm_gross_revenue_usd > 0
                                                    THEN (ltm_gross_revenue_usd / prior_ltm_gross_revenue_usd) - 1
                                                ELSE 0
                                                END
                                    )                          AS avg_ltm_revenue_mom_change,
                                    AVG(
                                            CASE
                                                WHEN prior_month_gross_revenue_usd > 0
                                                    THEN (month_gross_revenue_usd / prior_month_gross_revenue_usd) - 1
                                                ELSE 0
                                                END
                                    )                          AS avg_revenue_mom_change
                             FROM BASE_AGG
                             GROUP BY period_date, fiscal_year, relationship_manager_email, relationship_manager),

        LTM_REVENUE AS (
          SELECT
            relationship_manager_email,
            YEAR(period_date) AS year,
            SUM(month_gross_revenue_usd) AS ltm_gross_revenue_usd,
            COUNT(DISTINCT TO_CHAR(period_date, 'YYYY-MM')) AS months_in_year
          FROM BASE
          WHERE month_gross_revenue_usd > 0
          GROUP BY relationship_manager_email, YEAR(period_date)
        ),

        YEARLY_GROWTH AS (
          SELECT
            relationship_manager_email,
            ARRAY_AGG(
              OBJECT_CONSTRUCT(
                'year', year,
                'growth_pct', growth_pct / 100,
                'current_revenue_usd', ltm_gross_revenue_usd,
                'previous_revenue_usd', prev_year_revenue,
                'months', months_in_year
              )
            ) AS yearly_growth_history
          FROM (
            SELECT
              relationship_manager_email,
              year,
              ltm_gross_revenue_usd,
              months_in_year,
              LAG(ltm_gross_revenue_usd) OVER (
                PARTITION BY relationship_manager_email ORDER BY year
              ) AS prev_year_revenue,
              (ltm_gross_revenue_usd - LAG(ltm_gross_revenue_usd) OVER (
                PARTITION BY relationship_manager_email ORDER BY year
              )) / NULLIF(LAG(ltm_gross_revenue_usd) OVER (
                PARTITION BY relationship_manager_email ORDER BY year
              ), 0) * 100 AS growth_pct
            FROM LTM_REVENUE
          )
          WHERE prev_year_revenue IS NOT NULL AND prev_year_revenue != 0 AND growth_pct IS NOT NULL
          GROUP BY relationship_manager_email
        ),

        FISCAL_YEAR_REVENUE AS (
          SELECT
            relationship_manager_email,
            fiscal_year AS year,
            SUM(month_gross_revenue_usd) AS ltm_gross_revenue_usd,
            COUNT(DISTINCT TO_CHAR(period_date, 'YYYY-MM')) AS months_in_year
          FROM BASE
          WHERE month_gross_revenue_usd > 0
          GROUP BY relationship_manager_email, year
        ),

        YEARLY_GROWTH_FISCAL AS (
          SELECT
            relationship_manager_email,
            ARRAY_AGG(
              OBJECT_CONSTRUCT(
                'year', year,
                'growth_pct', growth_pct / 100,
                'current_revenue_usd', ltm_gross_revenue_usd,
                'previous_revenue_usd', prev_year_revenue,
                'months', months_in_year
              )
            ) AS yearly_growth_history
          FROM (
            SELECT
              relationship_manager_email,
              year,
              ltm_gross_revenue_usd,
              months_in_year,
              LAG(ltm_gross_revenue_usd) OVER (
                PARTITION BY relationship_manager_email ORDER BY year
              ) AS prev_year_revenue,
              (ltm_gross_revenue_usd - LAG(ltm_gross_revenue_usd) OVER (
                PARTITION BY relationship_manager_email ORDER BY year
              )) / NULLIF(LAG(ltm_gross_revenue_usd) OVER (
                PARTITION BY relationship_manager_email ORDER BY year
              ), 0) * 100 AS growth_pct
            FROM FISCAL_YEAR_REVENUE
          )
          WHERE prev_year_revenue IS NOT NULL AND prev_year_revenue != 0 AND growth_pct IS NOT NULL
          GROUP BY relationship_manager_email
        ),

         TOP_CLIENTS_LTM AS (SELECT relationship_manager_email,
                                ARRAY_AGG(
                                        OBJECT_CONSTRUCT(
                                                'name', client_name,
                                                'label_id', labelid,
                                                'ltm_gross_revenue_usd', ltm_gross_revenue_usd,
                                                'ltm_gross_revenue_usd_mom_change',
                                                CASE
                                                    WHEN prior_ltm_gross_revenue_usd > 0
                                                        THEN (ltm_gross_revenue_usd / prior_ltm_gross_revenue_usd) - 1
                                                    ELSE 0
                                                    END,
                                                'revenue_mom_change',
                                                CASE
                                                    WHEN prior_month_gross_revenue_usd > 0
                                                        THEN (month_gross_revenue_usd / prior_month_gross_revenue_usd) - 1
                                                    ELSE 0
                                                    END,
                                                'rank', client_rank
                                        )
                                ) WITHIN GROUP (ORDER BY client_rank) AS top_clients
                         FROM RANKED_LTM
                         WHERE client_rank <= 10
                         GROUP BY relationship_manager_email),

         TOP_CLIENTS_FISCAL_YEAR AS (SELECT relationship_manager_email,
                        ARRAY_AGG(
                                OBJECT_CONSTRUCT(
                                        'name', client_name,
                                        'label_id', labelid,
                                        'fiscal_year_ytd_gross_revenue_usd', fiscal_year_ytd_gross_revenue_usd,
                                        'revenue_mom_change',
                                        CASE
                                            WHEN prior_month_gross_revenue_usd > 0
                                                THEN (month_gross_revenue_usd / prior_month_gross_revenue_usd) - 1
                                            ELSE 0
                                            END,
                                        'rank', client_rank
                                )
                        ) WITHIN GROUP (ORDER BY client_rank) AS top_clients
                 FROM RANKED_FISCAL_YEAR
                 WHERE client_rank <= 10
                 GROUP BY relationship_manager_email),

    /*
    * Calculate fiscal year-to-date revenue for the selected period and
    * the same period from the previous year, for each relationship manager.
    */
    FISCAL_YEAR_YTD_REVENUE AS (
            SELECT
          relationship_manager_email,
          SUM(CASE WHEN TO_CHAR(period_date, 'YYYY-MM') = (SELECT selected_month FROM FILTERED_MONTH)
                   THEN IFNULL(fiscal_year_ytd_gross_revenue_usd, 0) END) AS fiscal_year_ytd_gross_revenue_usd,
          SUM(CASE WHEN TO_CHAR(period_date, 'YYYY-MM') = TO_CHAR(DATEADD(month, -12, TO_DATE((SELECT selected_month FROM FILTERED_MONTH) || '-01')), 'YYYY-MM')
                   THEN IFNULL(fiscal_year_ytd_gross_revenue_usd, 0) END) AS fiscal_year_ytd_gross_revenue_usd_12mo_ago
        FROM INTELLIGENCE.DBT_PROD.ORCA_CLIENT_SCORECARD_BASE
        WHERE TO_CHAR(period_date, 'YYYY-MM') IN (
            (SELECT selected_month FROM FILTERED_MONTH),
            TO_CHAR(DATEADD(month, -12, TO_DATE((SELECT selected_month FROM FILTERED_MONTH) || '-01')), 'YYYY-MM')
        )
        GROUP BY relationship_manager_email
    )

    SELECT MS.period_date,
           MS.fiscal_year,
           MS.relationship_manager,
           MS.relationship_manager_email,
           MS.label_count,
           MS.total_ltm_gross_revenue_usd,
           MS.avg_ltm_gross_revenue_usd,
           MS.avg_ltm_revenue_mom_change,
           MS.avg_revenue_mom_change,
           FYR.fiscal_year_ytd_gross_revenue_usd,
           FYR.fiscal_year_ytd_gross_revenue_usd_12mo_ago AS fiscal_year_ytd_gross_revenue_usd_prev_year,
           TCLTM.top_clients AS top_clients_ltm,
           TCFY.top_clients AS top_clients_fiscal_year,
           COALESCE(YG.yearly_growth_history, ARRAY_CONSTRUCT()) AS yearly_growth,
           COALESCE(YGF.yearly_growth_history, ARRAY_CONSTRUCT()) AS yearly_growth_fiscal
    FROM MANAGER_SUMMARY MS
             LEFT JOIN FISCAL_YEAR_YTD_REVENUE FYR
                USING(relationship_manager_email)
             LEFT JOIN TOP_CLIENTS_LTM TCLTM
                ON MS.relationship_manager_email = TCLTM.relationship_manager_email
             LEFT JOIN TOP_CLIENTS_FISCAL_YEAR TCFY
                ON MS.relationship_manager_email = TCFY.relationship_manager_email
             LEFT JOIN YEARLY_GROWTH YG
                ON MS.relationship_manager_email = YG.relationship_manager_email
             LEFT JOIN YEARLY_GROWTH_FISCAL YGF
                ON MS.relationship_manager_email = YGF.relationship_manager_email
    ORDER BY MS.total_ltm_gross_revenue_usd DESC;
    """
    )


# noinspection SqlResolve
def get_scorecard_filter_meta(
    *, relationship_manager_emails: list[Email] | None = None
) -> SqlQuery:
    """Get relationship manager based metadata from the client scorecard table.

    Args:
        relationship_manager_emails: Emails of the relationship managers to filter by.
            If None, all relationship managers will be included, as well as clients
            without a relationship manager.
    """

    email_condition = (
        f"AND relationship_manager_email IN {_to_sql_tuple(relationship_manager_emails)}"
        if relationship_manager_emails
        else ""
    )

    # Get all months from the beginning of the dataset until the most
    # recent complete month.
    filtered_month_cte = _filtered_month_cte(None)

    return SqlQuery(
        f"""
        {filtered_month_cte}
        SELECT
            relationship_manager_email,
            relationship_manager,
            MIN(period_date) AS min_period_date,
            MAX(period_date) AS max_period_date,
            ARRAY_AGG(
                DISTINCT OBJECT_CONSTRUCT(
                    'label_id', labelid,
                    'name', COALESCE(NULLIF(TRIM(V.COMPANY), ''), NULLIF(TRIM(V.NAME), ''), CONCAT('(Label #', labelid, ')'))
                    )) AS clients
        FROM INTELLIGENCE.DBT_PROD.ORCA_CLIENT_SCORECARD_BASE CSB
        LEFT JOIN ROYALTY_ACCOUNTING_REPORTING.PROD.VW_DIM_ABACUS_AR_VENDOR V
            ON CSB.LABELID = V.VENDOR_ID
        WHERE
            TO_CHAR(CSB.period_date, 'YYYY-MM') <= (SELECT selected_month FROM FILTERED_MONTH)
            {email_condition}
        GROUP BY relationship_manager_email, relationship_manager
        """
    )


# noinspection SqlResolve
def get_scorecard_relationship_managers() -> SqlQuery:
    """Get all unique relationship managers from the client scorecard base."""
    return SqlQuery(
        """
        SELECT relationship_manager_email AS email,
               relationship_manager       AS full_name
        FROM INTELLIGENCE.DBT_PROD.ORCA_CLIENT_SCORECARD_BASE
        WHERE relationship_manager_email IS NOT NULL
          AND relationship_manager_email != ''
        GROUP BY relationship_manager_email, relationship_manager
        """
    )


# noinspection SqlResolve
def get_scorecard_clients(
    *,
    relationship_manager_emails: list[Email] | None = None,
    label_ids: list[LabelId] | None = None,
    date_yearmonth: str | None = None,
    limit: int | None = None,
) -> SqlQuery:
    """Get clients and metadata from the client scorecard base.
    Any client not appearing in the client scorecard base will not be included in the
    results!

    Args:
        relationship_manager_emails: Emails of the relationship managers to filter by.
        label_ids: List of label IDs to filter the results. Default is None
            (no filtering by label IDs).
        limit: Optional limit for the number of results. Default is None (no limit).
        date_yearmonth: Date yearmonth to get the result snapshot for the given month.
            If provided, data will be fetched from the oldest available month until the
            given month.
    """
    email_condition = (
        f"AND CSB.relationship_manager_email IN {_to_sql_tuple(relationship_manager_emails)}"
        if relationship_manager_emails
        else ""
    )

    label_ids_condition = (
        f"AND CSB.labelid IN ({','.join(map(str, set(label_ids)))})"
        if label_ids
        else ""
    )

    limit_condition = f"LIMIT {limit}" if limit is not None else ""

    if date_yearmonth is not None:
        helpers.validate_date_yearmonth(date_yearmonth)

    # noinspection SqlConstantExpression
    return SqlQuery(
        f"""
        {_filtered_month_cte(date_yearmonth)}
        SELECT
            CSB.relationship_manager_email,
            CSB.period_date,
            CSB.labelid AS label_id,
            COALESCE(NULLIF(TRIM(V.COMPANY), ''), NULLIF(TRIM(V.NAME), ''), CONCAT('(Label #', CSB.labelid, ')')) AS name,
            CSB.ltm_gross_revenue_usd,
            CSB.prior_ltm_gross_revenue_usd,
            CSB.month_gross_revenue_usd,
            CSB.prior_month_gross_revenue_usd
        FROM INTELLIGENCE.DBT_PROD.ORCA_CLIENT_SCORECARD_BASE CSB
        LEFT JOIN ROYALTY_ACCOUNTING_REPORTING.PROD.VW_DIM_ABACUS_AR_VENDOR V
            ON CSB.LABELID = V.VENDOR_ID
        WHERE TO_CHAR(CSB.period_date, 'YYYY-MM') = (SELECT selected_month FROM FILTERED_MONTH)
        {email_condition}
        {label_ids_condition}
        {limit_condition}
        """
    )


# noinspection SqlResolve
def get_releases(
    *,
    relationship_manager_emails: list[Email] | None = None,
    date_from: date | str | None = None,
    date_to: date | str | None = None,
    priorities: list[str] | None = None,
    order_by: str | None = "DATE_RELEASE",  # Default order by release date
    order: str | None = "DESC",  # Default descending order
    limit: int | None = None,
    label_ids: list[LabelId] | None = None,
) -> SqlQuery:
    """Get releases.

    Args:
        relationship_manager_emails: Emails of the relationship managers to filter by.
        date_from: Optional start date for filtering releases. Must be in
            'YYYY-MM-DD' format or a date object.
        date_to: Optional end date for filtering releases. Must be in
            'YYYY-MM-DD' format or a date object.
        priorities: Optional list of priorities to filter the results. Default is None
            (no filtering by priorities). E.g. ['A', 'B'].
        order_by: Column to order the results by. Default is 'DATE_RELEASE'.
        order: Order direction. Can be 'ASC' or 'DESC'. Default is 'DESC'.
        limit: Optional limit for the number of results. Default is None (no limit).
        label_ids: Optional list of label IDs to filter the results. Default is None
            (no filtering by label IDs).
    """
    if date_from and not isinstance(date_from, str):
        date_from = date_from.strftime("%Y-%m-%d")
    if date_to and not isinstance(date_to, str):
        date_to = date_to.strftime("%Y-%m-%d")

    date_condition = ""
    if date_from and date_to:
        date_condition = f"AND DATE(RELEASEDATE) BETWEEN '{date_from}' AND '{date_to}'"
    elif date_from:
        date_condition = f"AND DATE(RELEASEDATE) >= '{date_from}'"
    elif date_to:
        date_condition = f"AND DATE(RELEASEDATE) <= '{date_to}'"

    priority_condition = ""
    if priorities:
        priority_condition = (
            f"AND UPPER(P.PRIORITY) IN "
            f"{_to_sql_tuple((p.strip().upper() for p in priorities), sort=True)}"
        )

    email_condition = ""
    if relationship_manager_emails:
        email_condition = (
            f"AND OAU.EMAIL IN {_to_sql_tuple(relationship_manager_emails)}"
        )

    order_condition = (
        f"ORDER BY {(order_by or "DATE_RELEASE").upper()} {(order or "DESC").upper()}"
    )

    if limit:
        order_condition += f" LIMIT {limit}"

    label_ids_condition = ""
    if label_ids:
        label_ids_condition = (
            f"AND DR.LABELID IN ({','.join(map(str, set(label_ids)))})"
        )

    return SqlQuery(
        f"""
        SELECT
            OAU.EMAIL AS RELATIONSHIP_MANAGER_EMAIL,
            RELEASEID AS RELEASE_ID,
            RELEASENAME AS RELEASE_NAME,
            PRODUCT_ID,
            DATE(RELEASEDATE) AS DATE_RELEASE,
            DATE(DATE_ADDED) AS DATE_ADDED,
            DATE(SALE_START_DATE) AS DATE_SALE_START,
            DA.ARTISTNAME AS ARTIST_NAME,
            DR.LABELID AS LABEL_ID,
            COALESCE(NULLIF(TRIM(V.COMPANY), ''), NULLIF(TRIM(V.NAME), ''), CONCAT('(Label #', labelid, ')')) AS LABEL_NAME,
            ARRAY_AGG(
              CASE
                -- Ignore some priorities with non-existing country 0
                WHEN (P.COUNTRY_ID IS NOT NULL OR P.PRIORITY IS NOT NULL) AND P.COUNTRY_ID > 0 THEN
                  OBJECT_CONSTRUCT('country', P.COUNTRY_ID, 'priority', UPPER(P.PRIORITY))
              END
            ) AS priorities
        FROM FACTS.PROD.DIM_RELEASE DR
        LEFT JOIN FACTS.PROD.DIM_ARTIST DA
            USING(ARTISTID)
        LEFT JOIN ROYALTY_ACCOUNTING_REPORTING.PROD.VW_DIM_ABACUS_AR_VENDOR V
            ON DR.LABELID = V.VENDOR_ID
        LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.ORCHADMIN_USERS OAU
            ON V.ASSIGNED_TO = OAU.ID
        LEFT JOIN FACTS.PROD.MKT_PRIORITY_ALL P
            ON P.RELEASE_ID = DR.PRODUCT_ID
        WHERE DR.RELEASEDATE IS NOT NULL
            AND DR.NOT_FOR_DISTRIBUTION = 'N'
            {priority_condition}
            {email_condition}
            {date_condition}
            {label_ids_condition}
        GROUP BY ALL
        {order_condition}
        """
    )


def _to_sql_tuple(values: Iterable, sort: bool = False) -> str:
    """
    Convert a list of values to a SQL tuple string, ensuring uniqueness
    and proper quoting for string literals.

    Args:
        values: List of values to convert.

    Returns:
        str: SQL tuple string, e.g. ('a','b','c')
    """
    unique_values: list[str] = list(set(map(str, values)))
    if sort:
        unique_values = sorted(unique_values)
    quoted = (f"'{v}'" for v in unique_values)
    return f"({','.join(quoted)})"