"""Query templates for loading and flattening contract_term_conditions into Snowflake."""

from jinja2 import Template


def snapshot_flat_contract_term_condition_countries_template() -> Template:
    """Copy and flatten data from SNAPSHOT_CONTRACT_NR.CONDITIONS into SNAPSHOT_CONTRACT_TERM_CONDITION_COUNTRY."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_TERM_CONDITION_COUNTRY(
            ACCOUNTING_RUN_ID,
            CONTRACT_TERM_CONDITION_ID,
            COUNTRY_CODE,
            COUNTRY_ID
        ) SELECT DISTINCT
            c.accounting_run_id,
            c.contract_term_condition_id,
            LISTAGG(IFNULL(cn.value, 'WW'), ',') AS country_code, -- 'WW' is 'World Wide' AKA 'ALL'
            CASE
                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 ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_NR AS c
            INNER JOIN (LATERAL FLATTEN (
                input => PARSE_JSON(c.conditions),
                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
        WHERE c.accounting_run_id = {{accounting_run_id}}
        GROUP BY
            accounting_run_id,
            contract_term_condition_id,
            country_id;
    """)


def snapshot_flat_contract_term_condition_stores_template() -> Template:
    """Copy and flatten data from SNAPSHOT_CONTRACT_NR.CONDITIONS into SNAPSHOT_CONTRACT_TERM_CONDITION_STORE."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_TERM_CONDITION_STORE(
            ACCOUNTING_RUN_ID,
            CONTRACT_TERM_CONDITION_ID,
            STORE_ID
        ) SELECT DISTINCT
            c.ACCOUNTING_RUN_ID,
            c.CONTRACT_TERM_CONDITION_ID,
            IFNULL(s.value, 0) AS store_id
        FROM ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_NR AS c
            INNER JOIN (LATERAL FLATTEN (
                input => PARSE_JSON(c.conditions),
                path => 'stores',
                outer => true
            )) AS s
        WHERE c.ACCOUNTING_RUN_ID = {{accounting_run_id}};
    """)


def snapshot_flat_contract_term_condition_transaction_types_template() -> Template:
    """Copy and flatten data from SNAPSHOT_CONTRACT_NR.CONDITIONS into SNAPSHOT_CONTRACT_TERM_CONDITION_TRANSACTION_TYPE."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_TERM_CONDITION_TRANSACTION_TYPE(
            ACCOUNTING_RUN_ID,
            CONTRACT_TERM_CONDITION_ID,
            TRANSACTION_TYPE_ID
        ) SELECT DISTINCT
            c.ACCOUNTING_RUN_ID,
            c.CONTRACT_TERM_CONDITION_ID,
            IFNULL(tt.value, 0) AS transaction_type_id
        FROM ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_NR AS c
            INNER JOIN (LATERAL FLATTEN (
                input => PARSE_JSON(c.conditions),
                path => 'transaction_types',
                outer => true
            )) AS tt
        WHERE c.ACCOUNTING_RUN_ID = {{accounting_run_id}};
    """)