#############################################################################################################
# Owner: Customer Main
# Created by: yangstanley@, hillz@
# Created: April 8, 2021
# Purpose: This table includes a list of legal entities which could be Association, corporation, partnership,
#          proprietorship, non-profit institution, government institution, or individual that has legal standing
#          in the eyes of law. Associated with each other via a legal hierarchy.
#
#          This table is partitioned by data injection date(_PARTITIONDATE)
#############################################################################################################

view: demo_l4c {
  derived_table: {
    sql: WITH legal_entities AS (
          SELECT
            legal_entity_id
          , name
          , address.revision AS revision
          , address.region_code AS region_code
          , address.language_code AS language_code
          , address.postal_code AS postal_code
          , address.sorting_code AS sorting_code
          , address.administrative_area AS administrative_area
          , address.locality AS locality
          , address.organization AS organization
          , duns
          , customer_id
          , parent_id
          , commit_timestamp
          , address_lines
          , recipients
          , _PARTITIONDATE AS partition_date
          FROM concord-prod.service_customermdm_test.legal_entities
          LEFT JOIN UNNEST(address.address_lines) AS address_lines
          LEFT JOIN UNNEST(address.recipients) AS recipients
          WHERE _PARTITIONDATE = (
                 SELECT MAX(_PARTITIONDATE)
                 FROM concord-prod.service_customermdm_test.legal_entities
                 WHERE _PARTITIONDATE > DATE_ADD(CURRENT_DATE, INTERVAL -3 DAY) )
          AND _PARTITIONDATE > DATE_ADD(CURRENT_DATE, INTERVAL -3 DAY)
          GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
          )

          SELECT
            legal_entity_id
          , name
          , revision
          , region_code
          , language_code
          , postal_code
          , sorting_code
          , administrative_area
          , locality
          , organization
          , duns
          , customer_id
          , parent_id
          , commit_timestamp
          , partition_date
          , STRING_AGG(address_lines, " " ORDER BY address_lines) AS address_lines
          , STRING_AGG(recipients, " " ORDER BY recipients) AS recipients

          FROM legal_entities
          GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 ;;
  }

  ########## PRIMARY KEYS ##########

  dimension: id {
    primary_key: yes
    group_label: "IDs"
    label: "Billing Account ID"
    description: "ID of the legal entity/billing account"
    type: string
    sql: ${TABLE}.legal_entity_id ;;
  }

  ########## DIMENSIONS ##########

  dimension: address_lines {
    group_label: "Address"
    description: "Street address of the customer. Some data are not well documented or are missing"
    type: string
    sql: ${TABLE}.address_lines ;;
  }

  dimension: administrative_area {
    group_label: "Address"
    description: "State/Province of the legal entity"
    type: string
    sql: ${TABLE}.administrative_area ;;
  }

  dimension_group: commit {
    hidden: yes
    description: "The date the legal entity's data was last committed"
    type: time
    datatype: datetime
    sql: ${TABLE}.commit_timestamp ;;
    timeframes: [date]
  }

  dimension: customer_id {
    hidden: yes
    group_label: "IDs"
    description: "ID of the customer associates to the legal eneity"
    type: string
    sql: ${TABLE}.customer_id ;;
  }

  dimension: duns_id {
    group_label: "IDs"
    label: "D&B DUNS ID"
    description: "Duns & Bradstreet ID of the legal entity"
    type: string
    sql: ${TABLE}.duns ;;
  }

  dimension: language_code {
    group_label: "Address"
    description: "ISO language code of the legal entity"
    type: string
    sql: ${TABLE}.language_code ;;
  }

  dimension: locality {
    group_label: "Address"
    description: "City of the legal entity"
    type: string
    sql: ${TABLE}.locality ;;
  }

  dimension: name {
    label: "Billing Account Name"
    description: "Name of the legal entity/billing account"
    type: string
    sql: ${TABLE}.name ;;
  }

  dimension: parent_id {
    group_label: "IDs"
    label: "Parent Billing Account ID"
    description: "The ID of the parent company of the legal entity"
    type: string
    sql: ${TABLE}.parent_id ;;
  }

  dimension: postal_code {
    group_label: "Address"
    description: "Postal Code of the legal entity"
    type: string
    sql: ${TABLE}.postal_code ;;
  }

  dimension: region_code {
    group_label: "Address"
    description: "Country code of the legal entity"
    type: string
    sql: ${TABLE}.region_code ;;
  }

  dimension: sorting_code {
    group_label: "Address"
    description: "Last 4 digits of the 9-digit USA postal code of the legal entity"
    type: string
    sql: ${TABLE}.sorting_code ;;
  }

  ########## MEASURES ##########

  measure: count {
    label: "Billing Account Count"
    description: "Legal entity count"
    type: count_distinct
    sql: ${id} ;;
    drill_fields: [customer_entities.id ,customer_entities.name, id ,name, duns_id, parent_id]
  }

  measure: count_parent {
    description: "Parent Account Counts"
    type: count_distinct
    sql: ${parent_id} ;;
  }

}