"""Templates for inserting and updating SNAPSHOT_MECHANICAL_TRANSACTION in snowflake."""

from jinja2 import Template


def insert_snapshot_mechanical_transactions() -> Template:
    """Find transactions that could be eligible for mechanical calculations."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_MECHANICAL_TRANSACTION(
            STATEMENT_PERIOD_ID,
            ACCOUNTING_PERIOD_ID,
            TXN_ID,
            TRANSACTION_TYPE_ID,
            MECHANICAL_TYPE,
            COUNTRY_CODE,
            CURRENCY_CODE,
            ROYALTY_AMOUNT
        )
        SELECT
            {{statement_period_id}} AS statement_period_id,
            {{accounting_period_id}} AS accounting_period_id,
            txn.stmt_db_sales_distro_txn_id AS txn_id,
            txn.transaction_type_id,
            CASE WHEN rttgtt.reference_transaction_type_id = 4 -- 'DR'
                    THEN 'ringtone'
                WHEN rttgtt.reference_transaction_type_group_id = 2 -- 'Download - Digital Distribution'
                    THEN 'digital'
                ELSE 'physical'
            END AS mechanical_type,
            cy.iso3166a3 AS country_code,
            txn.sale_currency_code,
            NULL AS royalty_amount
        FROM ROYALTY_ACCOUNTING.{{schema}}.STMT_DB_SALES_DISTRO AS txn
            INNER JOIN ORCHARD_APP_REPORTING_V2.{{schema}}_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.reference_transaction_type_group_transaction_type AS rttgtt
                ON txn.transaction_type_id = rttgtt.reference_transaction_type_id
            INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.country AS cy
                ON txn.country_id = cy.id
        WHERE txn.sales_file_id IN ({{sales_file_ids|join(', ' )}})
            AND txn.store_id NOT IN (615, 1688, 1690, 1811, 1865)
            AND rttgtt.reference_transaction_type_group_admin = 'contract_mechanical_admin';
    """)


def calculate_mechanical_royalty_amounts() -> Template:
    """Calculate royalty amounts for each transaction using snapshot_mechanical_transaction_detail."""
    return Template("""
        UPDATE ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_MECHANICAL_TRANSACTION AS mech_txn
        SET mech_txn.royalty_amount = sub.royalty_amount
        FROM (
            SELECT
                detail.mechanical_transaction_id,
                SUM(detail.royalty_amount) AS royalty_amount
            FROM ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_MECHANICAL_TRANSACTION_DETAIL AS detail
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_MECHANICAL_TRANSACTION AS mech_txn_2
                    ON detail.mechanical_transaction_id = mech_txn_2.mechanical_transaction_id
            WHERE mech_txn_2.accounting_period_id = {{accounting_period_id}}
                AND detail.is_licensed = true
            GROUP BY detail.mechanical_transaction_id
        ) AS sub
        WHERE mech_txn.mechanical_transaction_id = sub.mechanical_transaction_id;
    """)