"""Query templates for flattening nested JSON in contract snapshots."""

from jinja2 import Template


def insert_flat_distro_contract_data_template() -> Template:
    """Flatten json in contract csvs and insert data into contract denorm table."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.CONTRACT_DENORMALIZED_DISTRO(
            ACCOUNTING_RUN_ID,
            ACCOUNT_ID,
            ACCOUNT_CURRENCY_CODE,
            CONTRACT_ID,
            CONTRACT_TERM_ID,
            CONTRACT_TERM_CONDITION_ID,
            CONTRACT_TERM_START,
            CONTRACT_TERM_END,
            TERM_TYPE,
            TERM_RATE,
            LABEL_ID,
            PRIORITY,
            UPC,
            ISRC,
            COUNTRY_ID,
            STORE_ID,
            TRANSACTION_TYPE_ID
        )
        WITH denorm AS (
            WITH products (
                contract_term_condition_id,
                term_type,
                label_id,
                upc,
                isrc
            ) AS (
                SELECT
                    ct.$7 AS contract_term_condition_id,
                    ct.$3 AS term_type,
                    at.value AS label_id,
                    NULL AS upc,
                    NULL AS isrc
                FROM @{{tsv_stage}}/{{term_csv_url}} (file_format => ROYALTY_ACCOUNTING.{{schema}}.ABACUS_CSV_FORMAT) AS ct
                    INNER JOIN (TABLE (SPLIT_TO_TABLE(ct.$4, ','))) AS at
                WHERE ct.$3 = 'label'

                UNION ALL

                SELECT
                    ct.$7 AS contract_term_condition_id,
                    ct.$3 AS term_type,
                    v.vendor_id AS label_id,
                    r.upc,
                    NULL AS isrc
                FROM @{{tsv_stage}}/{{term_csv_url}} (file_format => ROYALTY_ACCOUNTING.{{schema}}.ABACUS_CSV_FORMAT) AS ct
                    INNER JOIN (TABLE (SPLIT_TO_TABLE(ct.$4, ','))) AS at
                    INNER JOIN (LATERAL FLATTEN (
                        input => PARSE_JSON(ct.$5),
                        path => 'label_ids',
                        outer=>true
                    )) AS ar
                    INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.vendor AS v
                        ON v.vendor_id = TO_NUMBER(ar.value) AND v._fivetran_deleted = false
                    INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.project AS p
                        ON p.vendor_id = v.vendor_id AND p._fivetran_deleted = false
                    INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.releases AS r
                        ON r.project_id = p.project_id AND r.upc = TO_NUMBER(at.value) AND r._fivetran_deleted = false
                WHERE ct.$3 = 'product'

                UNION ALL

                SELECT
                    ct.$7 AS contract_term_condition_id,
                    ct.$3 AS term_type,
                    v.vendor_id AS label_id,
                    r.upc,
                    t.isrc
                FROM @{{tsv_stage}}/{{term_csv_url}} (file_format => ROYALTY_ACCOUNTING.{{schema}}.ABACUS_CSV_FORMAT) AS ct
                    INNER JOIN (TABLE (SPLIT_TO_TABLE(ct.$4, ','))) AS at
                    INNER JOIN (LATERAL FLATTEN (
                        input => PARSE_JSON(ct.$5),
                        path => 'label_ids',
                        outer => true
                    )) AS ar
                    INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.vendor AS v
                        ON v.vendor_id = TO_NUMBER(ar.value) AND v._fivetran_deleted = false
                    INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.project AS p
                        ON p.vendor_id = v.vendor_id AND p._fivetran_deleted = false
                    INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.releases AS r
                        ON r.project_id = p.project_id AND r._fivetran_deleted = false
                    INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.track AS t
                        ON t.release_id = r.release_id AND t.isrc = TO_CHAR(at.value) AND t._fivetran_deleted = false
                WHERE ct.$3 = 'track'
            ),
            countries AS (
                SELECT
                    ct.$7 AS contract_term_condition_id,
                    IFNULL(cn.value, 'WW') AS country_code, -- 'WW' is 'World Wide' AKA 'ALL'
                    CASE -- merge some new/extinct countries into old countries
                        WHEN cn.value IN ('BES','CUW','SXM') THEN 196
                        WHEN cn.value IN ('BLM','MAF') THEN 6
                        WHEN cn.value IN ('MNE','SRB') THEN 190
                        WHEN cn.value='SSD' THEN 165
                        WHEN cn.value='SUR' THEN 166
                        WHEN STARTSWITH(cn.value, 'X') OR cn.value = 'TBD' THEN -1
                        ELSE IFNULL(cy.id, 0)
                    END AS country_id
                FROM @{{tsv_stage}}/{{term_csv_url}} (file_format => ROYALTY_ACCOUNTING.{{schema}}.ABACUS_CSV_FORMAT) AS ct
                    INNER JOIN (LATERAL FLATTEN (
                        input => PARSE_JSON(ct.$8),
                        path => 'countries',
                        outer => true
                    )) AS cn
                    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.country AS cy
                        ON cy.iso3166a3 = cn.value
            ),
            transaction_types AS (
                SELECT
                    ct.$7 AS contract_term_condition_id,
                    IFNULL(cn.value, 0) AS transaction_type_id
                FROM @{{tsv_stage}}/{{term_csv_url}} (file_format => ROYALTY_ACCOUNTING.{{schema}}.ABACUS_CSV_FORMAT) AS ct
                    INNER JOIN (LATERAL FLATTEN (
                        input => PARSE_JSON(ct.$8),
                        path => 'transaction_types',
                        outer => true
                    )) AS cn
            ),
            sequestered_transactions AS (
                SELECT  sd.country_id,
                        sd.isrc,
                        sd.label_id,
                        sd.store_id,
                        sd.transaction_type_id,
                        sd.upc
                FROM    ROYALTY_ACCOUNTING.{{schema}}.STMT_DB_SALES_DISTRO AS sd
                        INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.SEQUESTERED_SALES_DISTRO AS ssd
                            USING (stmt_db_sales_distro_txn_id)
            ),
            stores AS (
                SELECT
                    ct.$7 AS contract_term_condition_id,
                    IFNULL(cn.value, 0) AS store_id
                FROM @{{tsv_stage}}/{{term_csv_url}} (file_format => ROYALTY_ACCOUNTING.{{schema}}.ABACUS_CSV_FORMAT) AS ct
                    INNER JOIN (LATERAL FLATTEN (
                        input => PARSE_JSON(ct.$8),
                        path => 'stores',
                        outer=>true
                    )) AS cn
            )
            SELECT
                {{accounting_run_id}} AS accounting_run_id,
                c.$2 AS account_id,
                c.$5 AS account_currency_code,
                c.$1 AS contract_id,
                ct.$2 AS contract_term_id,
                ct.$7 AS contract_term_condition_id,
                IFNULL(c.$3, '1970-02-01') AS contract_term_start,
                IFNULL(c.$4, '2100-01-01') AS contract_term_end,
                ct.$3 AS term_type,
                ct.$9 AS term_rate,
                pd.label_id AS label_id,
                ct.$10 AS priority,
                pd.upc AS upc,
                pd.isrc AS isrc,
                cn.country_id AS country_id,
                st.store_id AS store_id,
                tt.transaction_type_id AS transaction_type_id,
                SUM(IFF(ct.$3 = 'track', 1, NULL)) OVER (
                    partition BY
                        c.$2,   -- account_id
                        pd.label_id,
                        pd.upc,
                        pd.isrc,
                        cn.country_id,
                        tt.transaction_type_id,
                        st.store_id
                    ORDER BY
                        TO_NUMBER(priority) ASC,
                        TO_NUMBER(term_rate) DESC
                    ROWS BETWEEN unbounded preceding AND CURRENT ROW
                ) AS track_rank,
                SUM(IFF(ct.$3 = 'product', 1, NULL)) OVER (
                    partition BY
                        c.$2,   -- account_id
                        pd.label_id,
                        pd.upc,
                        pd.isrc,
                        cn.country_id,
                        tt.transaction_type_id,
                        st.store_id
                    ORDER BY
                        TO_NUMBER(priority) ASC,
                        TO_NUMBER(term_rate) DESC
                    ROWS BETWEEN unbounded preceding AND CURRENT ROW
                ) AS product_rank,
                SUM(IFF(ct.$3 = 'label', 1, NULL)) OVER (
                    partition BY
                        c.$2,   -- account_id
                        pd.label_id,
                        pd.upc,
                        pd.isrc,
                        cn.country_id,
                        tt.transaction_type_id,
                        st.store_id
                    ORDER BY
                        TO_NUMBER(priority) ASC,
                        TO_NUMBER(term_rate) DESC
                    ROWS BETWEEN unbounded preceding AND CURRENT ROW
                ) AS label_rank
            FROM @{{tsv_stage}}/{{contract_csv_url}} (file_format => ROYALTY_ACCOUNTING.{{schema}}.ABACUS_CSV_FORMAT) AS c
                INNER JOIN @{{tsv_stage}}/{{term_csv_url}} (file_format => ROYALTY_ACCOUNTING.{{schema}}.ABACUS_CSV_FORMAT) AS ct
                    ON ct.$1 = c.$1 -- contract_id
                INNER JOIN products AS pd ON pd.contract_term_condition_id = ct.$7 AND pd.term_type = ct.$3
                INNER JOIN countries AS cn ON cn.contract_term_condition_id = ct.$7
                INNER JOIN transaction_types AS tt ON tt.contract_term_condition_id = ct.$7
                INNER JOIN stores AS st ON st.contract_term_condition_id = ct.$7
            WHERE (cn.country_id <> 0 OR (cn.country_id = 0 AND cn.country_code = 'WW'))
                -- exclude sequestered transactions
                AND NOT EXISTS (
                    SELECT  1
                    FROM    sequestered_transactions AS txns
                    WHERE   txns.country_id = cn.country_id
                            AND txns.isrc = pd.isrc
                            AND txns.label_id = pd.label_id
                            AND txns.store_id = st.store_id
                            AND txns.transaction_type_id = tt.transaction_type_id
                            AND txns.upc = pd.upc
                )
        QUALIFY track_rank = 1
            OR product_rank = 1
            OR label_rank = 1
        )
        SELECT
            ACCOUNTING_RUN_ID,
            ACCOUNT_ID,
            ACCOUNT_CURRENCY_CODE,
            CONTRACT_ID,
            CONTRACT_TERM_ID,
            CONTRACT_TERM_CONDITION_ID,
            CONTRACT_TERM_START,
            CONTRACT_TERM_END,
            TERM_TYPE,
            TERM_RATE,
            LABEL_ID,
            PRIORITY,
            UPC,
            ISRC,
            COUNTRY_ID,
            STORE_ID,
            TRANSACTION_TYPE_ID
        FROM denorm;
    """)