view: stmt_db_trans_type {
  sql_table_name: trans_type ;;

  dimension: trans_type_id {
    primary_key: yes
    type: number
    sql: ${TABLE}.trans_type_id ;;
    label: "Transaction Type ID"
  }

  dimension: category {
    type: string
    sql: ${TABLE}.category ;;
    description: "Basic categorization of Transaction Type by music/video/physical/breakage. See Mapping for more detailed breakdown."
  }

  dimension: full_description {
    type: string
    sql: ${TABLE}.full_description ;;
    label: "Transaction Type Full Name"
  }

  dimension: rejects_only {
    type: string
    hidden:  yes
    sql: ${TABLE}.rejects_only ;;
  }

  dimension: trans_type {
    type: string
    sql: ${TABLE}.trans_type ;;
    label: "Transaction Type"
    description: "Standard Orchard Transaction Type abbreviation. See in Workstation --> Help --> Payments and Accounting for definitions & examples."
  }

  dimension: mapping {
    type: string
    label: "Management Mapping"
    sql: ${TABLE}.mapping ;;
    description: "Further expansion of Transaction Type categorization - used for management reporting Looks."
  }

  dimension: ifpi_trans_type_mapping {
    type: string
    # PLEASE DO NOT UPDATE THIS CASE WITHOUT NOTIFYING BECCA@THEORCHARD.COM OR ROYALTIES@THEORCHARD.COM PRIOR TO DOING SO
    sql:
    CASE
    WHEN ${trans_type} IN ('DA', 'UA', 'W') THEN 'Audio Full Album'
    WHEN ${trans_type} IN ('DT', 'U', 'UT') THEN 'Audio Single Track'
    WHEN ${trans_type} IN ('DJ', 'FA', 'MT', 'S', 'SA', 'SV', 'TA', 'TD', 'WS') OR ${stmt_db_dig_sales_analytics.customer_master_master_id} = 569 THEN 'Subscription Income'
    WHEN ${trans_type} IN ('CL') THEN 'Other'
    WHEN ${trans_type} IN ('DR', 'RV') THEN 'Master Ringtones'
    WHEN ${trans_type} IN ('DV', 'J', 'UV', 'V', 'X') THEN 'Music Video'
    WHEN ${trans_type} IN ('RB')  THEN 'Ringback Tunes'
    WHEN ${trans_type} IN ('AC', 'AO', 'AP', 'AV', 'MV', 'PF', 'SF', 'SV', 'VA', 'YP') THEN 'Video Streams'
    WHEN ${trans_type} IN ('LP', 'LR', 'MP', 'MR', 'OP', 'OR', 'PH', 'PS', 'RE') THEN 'Physical'
    WHEN ${trans_type} IN ('AS') AND ${stmt_db_dig_sales_analytics.customer_master_master_id} IN (453) THEN 'Video Streams'
    WHEN ${trans_type} IN ('AS', 'FS', 'NR', 'WA') AND ${stmt_db_dig_sales_analytics.customer_master_master_id} NOT IN (453) THEN 'Ad-Supported'
    ELSE 'NA'
    END ;;
    # PLEASE DO NOT UPDATE THIS CASE WITHOUT NOTIFYING BECCA@THEORCHARD.COM OR ROYALTIES@THEORCHARD.COM PRIOR TO DOING SO
    label: "IFPI Mapping"
  }

  dimension: sme_trans_type_mapping {
    type: string
    # PLEASE DO NOT UPDATE THIS CASE WITHOUT NOTIFYING BECCA@THEORCHARD.COM OR ROYALTIES@THEORCHARD.COM PRIOR TO DOING SO
    sql:
    Case
    WHEN ${trans_type} IN ('DA', 'DB', 'DR', 'DT', 'RB', 'RV', 'U', 'UA', 'UT', 'W') THEN 'Download'
    WHEN ${trans_type} IN ('AP', 'BR', 'CL', 'DJ', 'FA', 'FS', 'MT', 'S', 'SA', 'SB', 'SP', 'TA', 'TD', 'VP', 'WA', 'WS') THEN 'Streams'
    WHEN ${trans_type} IN ('AC', 'AO', 'AV', 'J', 'MV', 'PF', 'SF', 'SV', 'UV', 'V', 'VA', 'X', 'YP', 'YP') THEN 'Video'
    WHEN ${trans_type} IN ('LP', 'LR', 'MP', 'MR', 'OP', 'OR', 'PH', 'PS', 'RE', 'TP', 'TR') THEN 'Physical'
    WHEN ${trans_type} IN ('CE', 'CR', 'DV', 'MB', 'NT', 'TH', 'TL', 'TS', 'VR', 'VS', 'VU') THEN 'Film/TV'
    WHEN ${trans_type} IN ('CC', 'CX', 'EH', 'EH', 'ER', 'MA', 'MD', 'ME', 'NC', 'PA', 'PP', 'PV', 'PX', 'RC', 'RC', 'RL', 'RR', 'RW', 'SL', 'SR', 'TV', 'UN', 'UP') THEN 'Performance'
    WHEN ${trans_type} IN ('NR', 'NS') THEN 'Radio'
    WHEN ${trans_type} IN ('AS') AND ${stmt_db_dig_sales_analytics.customer_master_master_id} IN (453) THEN 'Video'
    WHEN ${trans_type} IN ('AS') AND ${stmt_db_dig_sales_analytics.customer_master_master_id} NOT IN (453) THEN 'Streams'
    -- WHEN ${trans_type} IN ('FS') THEN 'Fraudulent Streams'
    WHEN ${trans_type} IN ('SC') THEN 'Sync Revenue'
    ELSE 'NA'
    END ;;
    # PLEASE DO NOT UPDATE THIS CASE WITHOUT NOTIFYING BECCA@THEORCHARD.COM OR ROYALTIES@THEORCHARD.COM PRIOR TO DOING SO
    label: "SME Mapping"
  }

  measure: count {
    hidden: yes
    type: count
    drill_fields: [trans_type_id]
  }
}