view: contact_created_date {
  derived_table: {
    sql: WITH union_contact_alt_email AS (SELECT DISTINCT
                  c1.contact_id,
                  c1.contact_email,
                  'N' AS is_alt
                FROM orchard_app_reporting_v2.art_relations_prod_art_relations.contact c1
                WHERE c1.contact_email IS NOT NULL
                  AND c1.contact_email != ''

                UNION ALL

                SELECT DISTINCT
                  c2.contact_id,
                  c2.alt_email,
                  'Y' AS is_alt
                FROM orchard_app_reporting_v2.art_relations_prod_art_relations.contact c2
                WHERE c2.alt_email IS NOT NULL
                  AND c2.alt_email != ''
                )
      SELECT
       --fact_prod_profile.Profile_name AS "fact_prod_profile.Profile_name",
          orch_app_ar_vendor.vendor_id  AS "orch_app_ar_vendor.vendor_id",
              CAST((TO_CHAR(DATE_TRUNC('second', fact_prod_profile.Updated_on), 'YYYY-MM-DD HH24:MI:SS')) AS DATETIME) AS "fact_prod_profile.updated_on_1",
          union_contact_alt_email.contact_email  AS "union_contact_alt_email.contact_email",
          label_manager.f_name||' '||label_manager.l_name  AS "label_manager.orchadmin_user",
          quarterback_label_manager.f_name||' '||quarterback_label_manager.l_name  AS "quarterback_label_manager.orchadmin_user",
              (CASE WHEN IFF(orch_app_ar_vend_contact.active = 'Y', TRUE, FALSE)  THEN 'Yes' ELSE 'No' END) AS "orch_app_ar_vend_contact.contact_is_active",
              (CASE WHEN IFF(orch_app_ar_vend_contact.master = 'Y', TRUE, FALSE)  THEN 'Yes' ELSE 'No' END) AS "orch_app_ar_vend_contact.contact_is_master",
              CAST((TO_CHAR(DATE_TRUNC('second', fact_prod_profile.Last_modified_at), 'YYYY-MM-DD HH24:MI:SS')) AS DATETIME) AS "fact_prod_profile.last_modified_at_1",
              CAST((TO_CHAR(DATE_TRUNC('second', fact_prod_profile.Created_at), 'YYYY-MM-DD HH24:MI:SS')) AS DATETIME) AS "fact_prod_profile.created_at_1",
              rank () OVER (partition by orch_app_ar_vendor.vendor_id order by "fact_prod_profile.updated_on_1" desc  ) as ranking
           -- "fact_prod_profile.last_modified_at_1" desc, "fact_prod_profile.created_at_1" desc
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.owner  AS orch_app_ar_owner
      INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor  AS orch_app_ar_vendor ON lower(orch_app_ar_owner.owner_abbrivation) = lower(orch_app_ar_vendor.owner)
      --INNER JOIN intelligence.dbt_prod.dt_access_filter_1091  AS dt_access_filter_1091 ON orch_app_ar_vendor.vendor_id = dt_access_filter_1091.vendor_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS label_manager ON orch_app_ar_vendor.assigned_to = label_manager.id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS quarterback_label_manager ON orch_app_ar_vendor.quarterback_label_manager = quarterback_label_manager.id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vend_contact  AS orch_app_ar_vend_contact ON orch_app_ar_vendor.vendor_id = orch_app_ar_vend_contact.vendor_id
            AND orch_app_ar_vend_contact.subaccount_id IS NULL
      LEFT JOIN facts.prod.profile AS fact_prod_profile ON orch_app_ar_vend_contact.id =  fact_prod_profile.Profile_id
          --AND fact_prod_profile.Profile_name = 'LabelProfile'
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.contact  AS orch_app_ar_contact ON orch_app_ar_vend_contact.contact_id = orch_app_ar_contact.contact_id
      LEFT JOIN union_contact_alt_email ON orch_app_ar_contact.contact_id = union_contact_alt_email.contact_id
     -- WHERE  (dt_access_filter_1091.access_control ) = 0
        WHERE fact_prod_profile.Profile_type = 'LabelProfile'
        AND fact_prod_profile.Updated_on IS NOT NULL
      GROUP BY
          (DATE_TRUNC('second', fact_prod_profile.Updated_on)),
          (DATE_TRUNC('second', fact_prod_profile.Created_at)),
          (DATE_TRUNC('second', fact_prod_profile.Last_modified_at)),
          1,
          3,
          4,
          5,
          6,
          7

      ORDER BY
          1 DESC,
          2 DESC
      ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }
  # dimension: fact_prod_profile_Profile_name {
  #   type: string
  #   sql: ${TABLE}."fact_prod_profile.Profile_name" ;;
  # }

  dimension: orch_app_ar_vendor_vendor_id {
    type: number
    sql: ${TABLE}."orch_app_ar_vendor.vendor_id" ;;
  }


  dimension: fact_prod_profile_updated_on_1 {
    type: date_time
    sql: ${TABLE}."fact_prod_profile.updated_on_1" ;;
  }

  dimension: union_contact_alt_email_contact_email {
    type: string
    sql: ${TABLE}."union_contact_alt_email.contact_email" ;;
  }

  dimension: label_manager_orchadmin_user {
    type: string
    sql: ${TABLE}."label_manager.orchadmin_user" ;;
  }

  dimension: quarterback_label_manager_orchadmin_user {
    type: string
    sql: ${TABLE}."quarterback_label_manager.orchadmin_user" ;;
  }

  dimension: orch_app_ar_vend_contact_contact_is_active {
    type: string
    sql: ${TABLE}."orch_app_ar_vend_contact.contact_is_active" ;;
  }

  dimension: orch_app_ar_vend_contact_contact_is_master {
    type: string
    sql: ${TABLE}."orch_app_ar_vend_contact.contact_is_master" ;;
  }

  dimension: fact_prod_profile_last_modified_at_1 {
    type: date_time
    sql: ${TABLE}."fact_prod_profile.last_modified_at_1" ;;
  }

  dimension: fact_prod_profile_created_at_1 {
    type: date_time
    sql: ${TABLE}."fact_prod_profile.created_at_1" ;;
  }

  dimension: ranking {
    type: number
    sql: ${TABLE}."RANKING" ;;
  }

  set: detail {
    fields: [
        orch_app_ar_vendor_vendor_id,
  fact_prod_profile_updated_on_1,
  union_contact_alt_email_contact_email,
  label_manager_orchadmin_user,
  quarterback_label_manager_orchadmin_user,
  orch_app_ar_vend_contact_contact_is_active,
  orch_app_ar_vend_contact_contact_is_master,
  fact_prod_profile_last_modified_at_1,
  fact_prod_profile_created_at_1,
  ranking
    ]
  }
}