view: dt_manual_adjustment_flatten {
  derived_table: {
    sql:
WITH currency_exchange_rates AS (
    SELECT
        period_id,
        currency_from_id,
        currency_to_id,
        exchange_rate,
        IFF(period_id <= 235, currency_to_id, currency_from_id) AS r_map
    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.currency_exchange_rates
),
mac_mappings AS (
    SELECT
        category_id,
        CASE
            WHEN category_id = 68 THEN 'withheld'
            WHEN category_id = 69 THEN 'released'
            WHEN category_id IN (1,22,30,31,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,62,63,65,71,74,75,76,77,78,79,80,81,82,83,84)
              THEN 'recharges'
            WHEN category_id IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,23,24,25,26,27,28,29,32,33,49,50,51,52,53,54,55,56,57,58,59,60,61,64,66,67,70,72,73)
              THEN 'other'
            END AS mac_mapping
    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.manual_adjustment_category
),
manual_adjustments AS (
    SELECT
        apply_to_period_id,
        label_id,
        fx_currency,
        ROUND(MAX(fx_withheld), 4) AS fx_withheld,
        ROUND(MAX(fx_released), 4) AS fx_released,
        ROUND(MAX(fx_recharges), 4) AS fx_recharges,
        ROUND(MAX(fx_other), 4) AS fx_other,
        ROUND(MAX(usd_withheld), 4) AS usd_withheld,
        ROUND(MAX(usd_released), 4) AS usd_released,
        ROUND(MAX(usd_recharges), 4) AS usd_recharges,
        ROUND(MAX(usd_other), 4) AS usd_other
    FROM (
        SELECT
            apply_to_period_id,
            label_id,
            fx_currency,
            IFF(mac_mapping = 'withheld', fx_adjustment, 0) AS fx_withheld,
            IFF(mac_mapping= 'released', fx_adjustment, 0) AS fx_released,
            IFF(mac_mapping = 'recharges', fx_adjustment, 0) AS fx_recharges,
            IFF(mac_mapping = 'other', fx_adjustment, 0) AS fx_other,
            IFF(mac_mapping = 'withheld', usd_adjustment, 0) AS usd_withheld,
            IFF(mac_mapping= 'released', usd_adjustment, 0) AS usd_released,
            IFF(mac_mapping = 'recharges', usd_adjustment, 0) AS usd_recharges,
            IFF(mac_mapping = 'other', usd_adjustment, 0) AS usd_other
      FROM (
            SELECT
                mm.mac_mapping,
                ma.apply_to_period_id,
                ma.parent_id AS label_id,
                CASE
                    WHEN mm.mac_mapping = 'withheld' THEN cer1.currency_from_id
                    WHEN mm.mac_mapping = 'released' AND ma.apply_to_period_id <= 235 THEN cer2.currency_to_id
                    WHEN mm.mac_mapping = 'released' AND ma.apply_to_period_id > 235 THEN cer3.currency_from_id
                    WHEN mm.mac_mapping = 'recharges' THEN cer2.currency_to_id
                    WHEN mm.mac_mapping = 'other' THEN cer2.currency_to_id
                END AS fx_currency,
                CASE
                    WHEN mm.mac_mapping = 'withheld' THEN SUM(ma.amount / cer1.exchange_rate)
                    WHEN mm.mac_mapping = 'released' AND ma.apply_to_period_id <= 235 THEN SUM(ma.amount * cer2.exchange_rate)
                    WHEN mm.mac_mapping = 'released' AND ma.apply_to_period_id > 235 THEN SUM(ma.amount / cer3.exchange_rate)
                    WHEN mm.mac_mapping = 'recharges' THEN SUM(ma.amount * cer2.exchange_rate)
                    WHEN mm.mac_mapping = 'other' THEN SUM(ma.amount * cer2.exchange_rate)
                END AS fx_adjustment,
                SUM(ma.amount) AS usd_adjustment
            FROM ROYALTY_ACCOUNTING.PROD.VW_ABACUS_MANUAL_ADJUSTMENT ma
                INNER JOIN mac_mappings mm ON ma.category_id = mm.category_id
                INNER JOIN ROYALTY_ACCOUNTING_REPORTING.PROD.VW_DIM_ABACUS_AR_BOOKED_VENDOR_CONTRACT_SNAPSHOT bvcs
                    ON ma.apply_to_period_id = bvcs.period_id
                    AND ma.parent_id = bvcs.vendor_id
                LEFT JOIN currency_exchange_rates cer1 -- Reserves Withheld
                    ON cer1.period_id = bvcs.period_id
                    AND cer1.currency_to_id = 1
                    AND cer1.currency_from_id = bvcs.currency_id
                LEFT JOIN currency_exchange_rates cer2 -- Reserves Released <= 235
                    ON cer2.period_id = ma.apply_to_period_id
                    AND cer2.currency_to_id = bvcs.currency_id
                    AND cer2.currency_from_id = 1
                LEFT JOIN currency_exchange_rates cer3 -- Reserves Released > 235
                    ON cer3.period_id = ma.adjust_for_period_id
                    AND cer3.currency_to_id = 1
                    AND cer3.currency_from_id = bvcs.currency_id
            WHERE ma.parent_type = 'vendor'
            GROUP BY 1,2,3,4
        )
    ) GROUP BY 1,2,3
)
SELECT
    apply_to_period_id AS accounting_period_id,
    label_id,
    IFNULL(fx_currency, 1) AS fx_currency_id,
    COALESCE(fx_withheld, 0) AS fx_reserves_withheld,
    COALESCE(usd_withheld, 0) AS usd_reserves_withheld,
    COALESCE(fx_released, 0) AS fx_reserves_released,
    COALESCE(usd_released, 0) AS usd_reserves_released,
    COALESCE(fx_recharges, usd_recharges) AS fx_recharges,
    COALESCE(usd_recharges, 0) AS usd_recharges,
    COALESCE(fx_other, 0) AS fx_other,
    COALESCE(usd_other, 0) AS usd_other
FROM manual_adjustments
WHERE {% condition accounting_period_id %} apply_to_period_id {% endcondition %} ;;
}

  dimension: accounting_period_id {
    type: number
    sql: ${TABLE}.accounting_period_id ;;
  }

  dimension: label_id {
    type: number
    sql: ${TABLE}.label_id ;;
    hidden: yes
  }

  dimension: fx_currency_id {
    type: number
    sql: ${TABLE}.fx_currency_id ;;
    hidden: yes
  }

  dimension: fx_reserves_withheld {
    label: "Reserves Withheld FX"
    type: number
    sql: ${TABLE}.fx_reserves_withheld ;;
    value_format: "#,##0.00"
  }

  dimension: usd_reserves_withheld {
    label: "Reserves Withheld USD"
    type: number
    sql: ${TABLE}.usd_reserves_withheld ;;
    value_format: "$#,##0.00"
  }

  dimension: fx_reserves_released {
    label: "Reserves Released FX"
    type: number
    sql: ${TABLE}.fx_reserves_released ;;
    value_format: "#,##0.00"
  }

  dimension: usd_reserves_released {
    label: "Reserves Released USD"
    type: number
    sql: ${TABLE}.usd_reserves_released ;;
    value_format: "$#,##0.00"
  }

  dimension: fx_recharges {
    label: "Recharges FX"
    type: number
    sql: ${TABLE}.fx_recharges ;;
    value_format: "#,##0.00"
  }

  dimension: usd_recharges {
    label: "Recharges USD"
    type: number
    sql: ${TABLE}.usd_recharges ;;
    value_format: "$#,##0.00"
  }

  dimension: fx_other {
    label: "Other FX"
    type: number
    sql: ${TABLE}.fx_other ;;
    value_format: "#,##0.00"
  }

  dimension: usd_other {
    label: "Other USD"
    type: number
    sql: ${TABLE}.usd_other ;;
    value_format: "$#,##0.00"
  }
}