# flake8: noqa: E501
"""
Business Plan SQL Queries.
"""

from typing import Iterable

from monday_com_orca_backend.typings import LabelId, SqlQuery


def get_label_financials(label_ids: Iterable[LabelId]) -> SqlQuery:
    """Get financial data for the specified label IDs.

    Args:
        label_ids: A collection of label IDs to fetch financial data for.
    """
    # noinspection SqlResolve
    return SqlQuery(f"""
    WITH SalesRaw AS (
            SELECT
                LABELID,
                (EXTRACT(YEAR FROM DATEADD('month', 9, DATE_TRUNC('month', DATE_FROM_PARTS(activity_period.year, activity_period.month, '01'))))::integer) AS ACTIVITYYEAR,
                TT.TRANSACTIONTYPEABBR,
                GROSS,
                DISTRIBUTION_FEES
            FROM ROYALTY_ACCOUNTING.PROD.WORKSTATION_FACT_SALES_UNIFIED_DBT Sales
            LEFT JOIN facts.prod.dim_period AS activity_period ON Sales.activityperiodid = activity_period.periodid
            LEFT JOIN FACTS.PROD.DIM_TRANSACTIONTYPE TT USING (TRANSACTIONTYPEID)
            WHERE LABELID IN ({', '.join(map(str, set(label_ids)))})
            ),

    SalesAgg AS (
        SELECT
            LABELID,
            ACTIVITYYEAR,
            TRANSACTIONTYPEABBR,
            SUM(GROSS) AS gross,
            SUM(DISTRIBUTION_FEES) AS distribution_fees,
            COUNT_IF(GROSS != 0) AS row_count,
            AVG(IFF(GROSS = 0, NULL, (DISTRIBUTION_FEES * -1) / GROSS)) AS average
        FROM SalesRaw
        GROUP BY LABELID, ACTIVITYYEAR, TRANSACTIONTYPEABBR
        ),

    GrossAgg AS (
        SELECT
            LABELID,
            ACTIVITYYEAR,
            OBJECT_AGG(TRANSACTIONTYPEABBR, gross) AS GrossData
        FROM SalesAgg
        GROUP BY LABELID, ACTIVITYYEAR
        ),

    DistributionFeesAgg AS (
        SELECT
            LABELID,
            ACTIVITYYEAR,
            OBJECT_AGG(TRANSACTIONTYPEABBR, distribution_fees) AS DistributionFeesData
        FROM SalesAgg
        GROUP BY LABELID, ACTIVITYYEAR
        ),

    DistributionFeesPctAgg AS (
        SELECT
            LABELID,
            ACTIVITYYEAR,
            OBJECT_AGG(
            TRANSACTIONTYPEABBR,
            OBJECT_CONSTRUCT(
                  'row_count', row_count,
                  'average', average
            )  -- This will allow calculating weighted averages when considering more than one transaction type
        ) AS DistributionFeesPctData
        FROM SalesAgg
        GROUP BY LABELID, ACTIVITYYEAR
    )

    SELECT
        Gross.LABELID AS LABEL_ID,
        OBJECT_AGG(TO_NUMBER(Gross.ACTIVITYYEAR), Gross.GrossData) AS GROSS,
        OBJECT_AGG(TO_NUMBER(Dist.ACTIVITYYEAR), Dist.DistributionFeesData) AS DISTRIBUTION_FEES,
        OBJECT_AGG(TO_NUMBER(DistP.ACTIVITYYEAR), DistP.DistributionFeesPctData) AS DISTRIBUTION_FEES_PCT
        FROM GrossAgg Gross
        LEFT JOIN DistributionFeesAgg Dist
            ON Gross.LABELID = Dist.LABELID
            AND Gross.ACTIVITYYEAR = Dist.ACTIVITYYEAR
        LEFT JOIN DistributionFeesPctAgg DistP
            ON Gross.LABELID = DistP.LABELID
            AND Gross.ACTIVITYYEAR = DistP.ACTIVITYYEAR
        GROUP BY Gross.LABELID;
        """)


def get_label_asset_counts(
    label_ids: Iterable[LabelId], *, fiscal_year: int | None = None
) -> SqlQuery:
    """Get asset counts for the specified label IDs.

    Args:
        label_ids: A collection of label IDs to fetch Salesforce data for.
        fiscal_year: The fiscal year to filter the results by. If None, all fiscal
            years are included.
    """
    condition_fiscal_year = (
        f"VR.ACCOUNTINGYEAR = {fiscal_year}" if fiscal_year is not None else "TRUE"
    )
    # noinspection SqlResolve
    return SqlQuery(f"""
                WITH LABELS AS (
                SELECT DISTINCT LABELID
                FROM FACTS.PROD.DIM_RELEASE
                WHERE LABELID IN ({', '.join(map(str, set(label_ids)))})
            ),

            VENDOR_RELEASES AS (
                SELECT
                    DR.LABELID,
                    DR.DISPLAY_UPC,
                    S.ACCOUNTINGYEAR,
                    S.GROSS
                FROM FACTS.PROD.DIM_RELEASE DR
                LEFT JOIN FACTS.PROD.FACT_SALES S
                    USING (LABELID, RELEASEID)
                WHERE LABELID IN (SELECT * FROM LABELS)
            ),

            DIGITAL_RELEASES AS (
                SELECT
                    VR.LABELID,
                    COUNT(DISTINCT DISPLAY_UPC) AS cnt,
                    SUM(IFF({condition_fiscal_year}, GROSS, 0)) AS GROSS
                FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.RELEASES
                JOIN VENDOR_RELEASES VR
                    USING(DISPLAY_UPC)
                WHERE PRODUCT_TYPE_ID = 1
                GROUP BY LABELID
            ),

            PHYSICAL_RELEASES AS (
                SELECT
                    VR.LABELID,
                    COUNT(DISTINCT DISPLAY_UPC) AS cnt,
                    SUM(IFF({condition_fiscal_year}, GROSS, 0)) AS GROSS
                FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.RELEASES R
                LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.DISTRIBUTION_FORMAT DF
                  ON R.DISTRIBUTION_FORMAT_ID = DF.DISTRIBUTION_FORMAT_ID
                JOIN VENDOR_RELEASES VR
                    USING(DISPLAY_UPC)
                WHERE LOWER(DF.CONTEXT_TYPE) = 'physical'
                GROUP BY LABELID
            ),

            MUSIC_VIDEOS AS (
                SELECT
                    VR.LABELID,
                    COUNT(DISTINCT DISPLAY_UPC) AS cnt,
                    SUM(IFF({condition_fiscal_year}, GROSS, 0)) AS GROSS
                FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.RELEASES
                JOIN VENDOR_RELEASES VR
                    USING(DISPLAY_UPC)
                WHERE DISTRIBUTION_FORMAT_ID = 57
                GROUP BY LABELID
            )

            SELECT L.LABELID, 'digital_releases' AS type, COALESCE(DR.cnt, 0) AS cnt, COALESCE(DR.GROSS, 0) AS GROSS
            FROM LABELS L
            LEFT JOIN DIGITAL_RELEASES DR ON L.LABELID = DR.LABELID
            UNION ALL
            SELECT L.LABELID, 'physical_releases' AS type, COALESCE(PR.cnt, 0) AS cnt, COALESCE(PR.GROSS, 0) AS GROSS
            FROM LABELS L
            LEFT JOIN PHYSICAL_RELEASES PR ON L.LABELID = PR.LABELID
            UNION ALL
            SELECT L.LABELID, 'music_videos' AS type, COALESCE(MV.cnt, 0) AS cnt, COALESCE(MV.GROSS, 0) AS GROSS
            FROM LABELS L
            LEFT JOIN MUSIC_VIDEOS MV ON L.LABELID = MV.LABELID
    """)


def get_label_salesforce_meta(label_ids: Iterable[LabelId]) -> SqlQuery:
    """Get Salesforce data for the specified label IDs.

    Args:
        label_ids: A collection of label IDs to fetch Salesforce data for.
    """
    label_ids_regex = "|".join(map(str, set(label_ids)))
    # noinspection SqlResolve
    return SqlQuery(f"""
        SELECT
            TO_NUMBER(f.value) AS label_id,
            CA_MA_LABEL_ID_C AS label_id_raw,
            ARRAY_AGG(
                OBJECT_CONSTRUCT(
                    'currency', REGEXP_SUBSTR(CA_CURRENCY_C, '\\\\(([^)]+)\\\\)', 1, 1, 'e', 1),
                    'amount', CA_TOTAL_ADVANCE_AMOUNT_C
                )
            ) AS advance
        FROM BUSINESS_SYSTEMS_SALESFORCE_DATA.CA.OPPORTUNITY,
             LATERAL FLATTEN(  -- One row per matched label ID
                 input => REGEXP_SUBSTR_ALL(CA_MA_LABEL_ID_C, '({label_ids_regex})')
             ) AS f
        WHERE REGEXP_LIKE(CA_MA_LABEL_ID_C, '(^|\\\\D)(${label_ids_regex})(\\\\D|$)')
          AND CA_CURRENCY_C IS NOT NULL
          AND CA_TOTAL_ADVANCE_AMOUNT_C IS NOT NULL
        GROUP BY f.value, CA_MA_LABEL_ID_C;
    """)