view: sony_applications_data_leads {
  derived_table: {
    sql:
      select
        replace(record_content:Comments__c, '"', '') AS Comments,
        replace(record_content:Company, '"', '') AS Company,
        replace(record_content:Consultant__c, '"', '') AS Consultant,
        replace(record_content:ConvertedAccountId, '"', '') AS ConvertedAccountId,
        replace(record_content:ConvertedContactId, '"', '') AS ConvertedContactId,
        coalesce(TERRITORIES.NAME,  replace(record_content:Country, '"', '')) AS Country,
        split_part(replace(record_content:CreatedDate, '"', ''), '+', 1) AS CreatedDate,
        replace(record_content:Email, '"', '') AS Email,
        replace(record_content:FirstName, '"', '') AS FirstName,
        replace(record_content:Id, '"', '') AS Id,
        replace(record_content:IsConverted, '"', '') AS IsConverted,
        split_part(replace(record_content:LastModifiedDate, '"', ''), '+', 1) AS LastModifiedDate,
        replace(record_content:LastName, '"', '') AS LastName,
        replace(record_content:LeadScore__c, '"', '') AS LeadScore,
        replace(record_content:Status, '"', '') AS Status,
        CASE
          WHEN replace(record_content:Owner:Email, '"', '') = 'mlorenz@theorchard.com' THEN 'Auto Approval'
          ELSE CONCAT(replace(record_content:Owner:FirstName, '"', ''), ' ', replace(record_content:Owner:LastName, '"', ''))
        END AS Approver,
        replace(record_content:Owner:Email, '"', '') AS ApproverEmail,
        replace(record_content:VoucherCode__c, '"', '') AS VoucherCode,
        replace(record_content:VoucherCodeOwner__c, '"', '') AS VoucherCodeOwner
      FROM
        FACTS.PROD.AUDIT_EVENT_GDA_LEADS
      LEFT JOIN
        AWAL_SAX.SAX.TERRITORIES ON ISO_ALPHA_3_CODE = replace(record_content:Country, '"', '')
      WHERE
        replace(record_content:IsConverted, '"', '') = 'true';;
  }

  dimension: Comments {
    type: string
    sql: ${TABLE}.Comments ;;
    label: "Comments"
  }

  dimension: Company {
    type: string
    sql: ${TABLE}.Company ;;
    label: "Company"
  }

  dimension: Consultant {
    type: string
    sql: ${TABLE}.Consultant ;;
    label: "Correlation ID"
  }

  dimension: ConvertedAccountId {
    type: string
    sql: ${TABLE}.ConvertedAccountId ;;
    label: "Converted Account Id"
  }

  dimension: ConvertedContactId {
    type: string
    sql: ${TABLE}.ConvertedContactId ;;
    label: "Converted Contact Id"
  }

  dimension: Country {
    type: string
    sql: ${TABLE}.Country ;;
    label: "Country"
  }

  dimension_group: CreatedDate {
    type: time
    timeframes: [
      raw,
      date,
      month,
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.CreatedDate ;;
    label: "Created Date"
  }

  dimension: Email {
    type: string
    sql: ${TABLE}.Email ;;
    label: "Email"
  }

  dimension: FirstName {
    type: string
    sql: ${TABLE}.FirstName ;;
    label: "First Name"
  }

  dimension: Id {
    type: string
    sql: ${TABLE}.Id ;;
    label: "Id"
  }

  dimension: IsConverted {
    type: string
    sql: ${TABLE}.IsConverted ;;
    label: "Is Converted"
  }

  dimension_group: LastModifiedDate {
    type: time
    timeframes: [
        date,
        month,
        month_num,
        month_name,
        quarter,
        quarter_of_year,
        year
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.LastModifiedDate ;;
    label: "Last Modified Date"
  }

  dimension: LastName {
    type: string
    sql: ${TABLE}.LastName ;;
    label: "Last Name"
  }

  dimension: LeadScore {
    type: string
    sql: ${TABLE}.LeadScore ;;
    label: "Lead Score"
  }

  dimension: Approver {
    type: string
    sql: ${TABLE}.Approver ;;
    label: "Approver"
  }

  dimension: ApproverEmail {
    type: string
    sql: ${TABLE}.ApproverEmail ;;
    label: "Approver Email"
  }

  dimension: Status {
    type: string
    sql: ${TABLE}.Status ;;
    label: "Status"
  }

  dimension: VoucherCode {
    type: string
    sql: ${TABLE}.VoucherCode ;;
    label: "Referral Code"
  }

  dimension: VoucherCodeOwner {
    type: string
    sql: ${TABLE}.VoucherCodeOwner ;;
    label: "Referral Code Owner"
  }
}