view: performer_salesforce_society {
  sql_table_name: BUSINESS_SYSTEMS_SALESFORCE_DATA.NR.SOCIETY_C ;;

  # =========================================================================
  # 1. PRIMARY KEYS & UNIQUE IDENTIFIERS
  # =========================================================================
  dimension: id {
    primary_key: yes
    type: string
    sql: ${TABLE}."ID" ;;
    label: "Society Connection ID"
    description: "The primary unique identifier for the Salesforce Society record."
  }

  dimension: contributor_c {
    type: string
    sql: ${TABLE}."CONTRIBUTOR_C" ;;
    label: "Contributor ID Link"
    description: "Foreign key joining directly back to performer_salesforce_contributor.id."
  }

  # =========================================================================
  # 2. CORE SOCIETY DETAILS
  # =========================================================================
  dimension: name {
    type: string
    sql: ${TABLE}."NAME" ;;
    group_label: "Society Details"
    label: "Society Assignment Name"
  }

  dimension: status_c {
    type: string
    sql: ${TABLE}."STATUS_C" ;;
    group_label: "Society Details"
    label: "Status"
  }

  dimension: notes_c {
    type: string
    sql: ${TABLE}."NOTES_C" ;;
    group_label: "Society Details"
    label: "Notes"
  }

  dimension_group: assignment_date {
    type: time
    timeframes: [raw, date, week, month, quarter, year]
    convert_tz: no
    datatype: date
    sql: ${TABLE}."DATE_C" ;;
    group_label: "Society Details"
    label: "Assignment"
  }

  # =========================================================================
  # 3. SOCIETY IDENTIFICATION NUMBERS
  # =========================================================================
  dimension: society_performer_id_c {
    type: string
    sql: ${TABLE}."SOCIETY_PERFORMER_ID_C" ;;
    group_label: "Society Identifiers"
    label: "Society Performer ID"
    description: "The primary performer ID assigned by the collection society."
  }

  # DISCOVERED FIELD: Society Performer ID 2
  dimension: society_performer_id_2_c {
    type: string
    sql: ${TABLE}."SOCIETY_PERFORMER_ID_2_C" ;;
    group_label: "Society Identifiers"
    label: "Society Performer ID 2"
    description: "The secondary or alternative performer ID assigned by the collection society."
  }

  dimension: cmm_id_text_c {
    type: string
    sql: ${TABLE}."CMM_ID_TEXT_C" ;;
    group_label: "Society Identifiers"
    label: "CMM ID Text"
    description: "The CMM reference text identifier."
  }

  # =========================================================================
  # 4. SYSTEM METADATA TIMESTAMPS (Hidden to keep Explores pristine)
  # =========================================================================
  dimension: is_deleted { type: yesno sql: ${TABLE}."IS_DELETED" ;; group_label: "System Metadata" hidden: yes }
  dimension: created_by_id { type: string sql: ${TABLE}."CREATED_BY_ID" ;; group_label: "System Metadata" hidden: yes }
  dimension: last_modified_by_id { type: string sql: ${TABLE}."LAST_MODIFIED_BY_ID" ;; group_label: "System Metadata" hidden: yes }
  dimension: _fivetran_deleted { type: yesno sql: ${TABLE}."_FIVETRAN_DELETED" ;; group_label: "System Metadata" hidden: yes }

  dimension_group: created_date { type: time timeframes: [raw, date, week, month, year] convert_tz: no datatype: timestamp sql: ${TABLE}."CREATED_DATE" ;; group_label: "System Metadata" hidden: yes }
  dimension_group: last_modified_date { type: time timeframes: [raw, date, week, month, year] convert_tz: no datatype: timestamp sql: ${TABLE}."LAST_MODIFIED_DATE" ;; group_label: "System Metadata" hidden: yes }
  dimension_group: system_modstamp { type: time timeframes: [raw, date, week, month, year] convert_tz: no datatype: timestamp sql: ${TABLE}."SYSTEM_MODSTAMP" ;; group_label: "System Metadata" hidden: yes }
  dimension_group: last_viewed_date { type: time timeframes: [raw, date, week, month, year] convert_tz: no datatype: timestamp sql: ${TABLE}."LAST_VIEWED_DATE" ;; group_label: "System Metadata" hidden: yes }
  dimension_group: last_referenced_date { type: time timeframes: [raw, date, week, month, year] convert_tz: no datatype: timestamp sql: ${TABLE}."LAST_REFERENCED_DATE" ;; group_label: "System Metadata" hidden: yes }
  dimension_group: _fivetran_synced { type: time timeframes: [raw, date] convert_tz: no datatype: timestamp sql: ${TABLE}."_FIVETRAN_SYNCED" ;; group_label: "System Metadata" hidden: yes }

  # =========================================================================
  # 5. MEASURES / AGGREGATIONS
  # =========================================================================
  measure: count {
    type: count
    description: "Total count of Salesforce Society affiliation records."
    drill_fields: [name]
  }
}