unit_tests:
  - name: test_deduplicate_by_fan_id
    description: >
      Test that phone only fan will get same
      fan_id as other fan with email
      in case they share cleaned phone numbers.
    model: STG_DEDUPLICATED_EXTERNAL_EVENTS_BY_FAN_ID_DBT
    given:
      - input: ref('STG_EXTERNAL_EVENT_DBT')
        rows:
          - {
              id: 'id_1',
              source: 'sme_crm',
              source_type: 'newsletter',
              key: 'key_1',
              fan_id: 'fan_1',
              fan_email: 'john@email.com',
              fan_mobile: '+372 1 2 3',
              happened_at: 2023-11-07 00:00:00-0,
              vendor_id: 34590,
              subaccount_id: 0,
              global_participant_id: 'artist a',
              crm_fan_id: 'crm_1',
            }
          - {
            id: 'id_2',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_2',
            fan_id: 'fan_2',
            fan_email: 'mary@email.com',
            fan_mobile: null,
            happened_at: 2023-11-13 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist a',
            crm_fan_id: 'crm_2',
          }
          - {
            id: 'id_3',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_3',
            fan_id: 'fan_3',
            fan_email: null,
            fan_mobile: '(372) 123',
            happened_at: 2023-11-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist a',
            crm_fan_id: null,
          }
          - {
            id: 'id_4',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_4',
            fan_id: 'fan_4',
            fan_email: null,
            fan_mobile: '372123',
            happened_at: 2024-11-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist a',
            crm_fan_id: null,
          }
          - {
            id: 'id_5',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_5',
            fan_id: 'fan_5',
            fan_email: null,
            fan_mobile: '44 77 88',
            happened_at: 2024-05-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist b',
            crm_fan_id: null,
          }
          - {
            id: 'id_6',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_6',
            fan_id: 'fan_6',
            fan_email: null,
            fan_mobile: '+44 77 88',
            happened_at: 2024-03-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist b',
            crm_fan_id: null,
          }
          - {
              id: 'id_7',
              source: 'sme_crm',
              source_type: 'newsletter',
              key: 'key_7',
              fan_id: 'fan_7',
              fan_email: null,
              fan_mobile: '+372 555',
              happened_at: 2023-11-07 00:00:00-0,
              vendor_id: 34590,
              subaccount_id: 0,
              global_participant_id: 'artist C',
              crm_fan_id: null,
            }
          # this row should be missing from output  because wrong phone
          - {
              id: 'id_8',
              source: 'sme_crm',
              source_type: 'newsletter',
              key: 'key_8',
              fan_id: 'fan_8',
              fan_email: null,
              fan_mobile: 'my phone nr',
              happened_at: 2023-11-07 00:00:00-0,
              vendor_id: 34590,
              subaccount_id: 0,
              global_participant_id: 'artist C',
              crm_fan_id: null,
            }
          # Fan has multiple pohones but we continue with latest one
          - {
              id: 'id_10',
              source: 'sme_crm',
              source_type: 'newsletter',
              key: 'key_10',
              fan_id: 'fan_10',
              fan_email: 'dummyemail@email.com',
              fan_mobile: '(372) 777 777',
              happened_at: 2023-10-07 00:00:00-0,
              vendor_id: 34590,
              subaccount_id: 0,
              global_participant_id: 'artist C',
              crm_fan_id: null,
            }
          - {
              id: 'id_11',
              source: 'sme_crm',
              source_type: 'newsletter',
              key: 'key_10',
              fan_id: 'fan_10',
              fan_email: 'dummyemail@email.com',
              fan_mobile: '(372) 555 555',
              happened_at: 2023-11-07 00:00:00-0,
              vendor_id: 34590,
              subaccount_id: 0,
              global_participant_id: 'artist C',
              crm_fan_id: null,
            }
      - input: ref('STG_CLEAN_FAN_MOBILE_PHONE_DBT')
        rows:
          - {
              fan_id: 'fan_1',
              fan_mobile: '(372) 123',
              happened_at: 2023-11-07 00:00:00-0
          }
          - {
              fan_id: 'fan_3',
              fan_mobile: '(372) 123',
              happened_at: 2023-11-07 00:00:00-0
          }
          - {
              fan_id: 'fan_4',
              fan_mobile: '(372) 123',
              happened_at: 2023-11-07 00:00:00-0
          }
          - {
              fan_id: 'fan_5',
              fan_mobile: '(44) 7788',
              happened_at: 2023-11-07 00:00:00-0
          }
          - {
              fan_id: 'fan_6',
              fan_mobile: '(44) 7788',
              happened_at: 2023-11-07 00:00:00-0
          }
          - {
              fan_id: 'fan_7',
              fan_mobile: '(372) 555',
              happened_at: 2023-11-07 00:00:00-0
          }
          - {
              fan_id: 'fan_10',
              fan_mobile: '(372) 555 555',
              happened_at: 2023-11-07 00:00:00-0
          }
          - {
              fan_id: 'fan_10',
              fan_mobile: '(372) 777 777 ',
              happened_at: 2023-10-07 00:00:00-0
          }
    expect:
      rows:
          - {
              id: 'id_1',
              source: 'sme_crm',
              source_type: 'newsletter',
              key: 'key_1',
              fan_id: 'fan_1',
              fan_email: 'john@email.com',
              fan_mobile: '+372 1 2 3',
              happened_at: 2023-11-07 00:00:00-0,
              vendor_id: 34590,
              subaccount_id: 0,
              global_participant_id: 'artist a',
              crm_fan_id: 'crm_1',
              cleaned_mobile: '(372) 123',
              original_fan_id: 'fan_1',
            }
          - {
            id: 'id_2',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_2',
            fan_id: 'fan_2',
            fan_email: 'mary@email.com',
            fan_mobile: null,
            happened_at: 2023-11-13 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist a',
            crm_fan_id: 'crm_2',
            cleaned_mobile: null,
            original_fan_id: 'fan_2',
          }
          - {
            id: 'id_3',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_3',
            fan_id: 'fan_1',
            fan_email: 'john@email.com',
            fan_mobile: '(372) 123',
            happened_at: 2023-11-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist a',
            crm_fan_id: null,
            cleaned_mobile: '(372) 123',
            original_fan_id: 'fan_3',
          }
          - {
            id: 'id_4',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_4',
            fan_id: 'fan_1',
            fan_email: 'john@email.com',
            fan_mobile: '372123',
            happened_at: 2024-11-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist a',
            crm_fan_id: null,
            cleaned_mobile: '(372) 123',
            original_fan_id: 'fan_4',
          }
          - {
            id: 'id_5',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_5',
            fan_id: 'fan_5',
            fan_email: null,
            fan_mobile: '44 77 88',
            happened_at: 2024-05-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist b',
            crm_fan_id: null,
            cleaned_mobile: '(44) 7788',
            original_fan_id: 'fan_5',
          }
          - {
            id: 'id_6',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_6',
            fan_id: 'fan_6',
            fan_email: null,
            fan_mobile: '+44 77 88',
            happened_at: 2024-03-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist b',
            crm_fan_id: null,
            cleaned_mobile: '(44) 7788',
            original_fan_id: 'fan_6',
          }
          - {
              id: 'id_7',
              source: 'sme_crm',
              source_type: 'newsletter',
              key: 'key_7',
              fan_id: 'fan_7',
              fan_email: null,
              fan_mobile: '+372 555',
              happened_at: 2023-11-07 00:00:00-0,
              vendor_id: 34590,
              subaccount_id: 0,
              global_participant_id: 'artist C',
              crm_fan_id: null,
              cleaned_mobile: '(372) 555',
              original_fan_id: 'fan_7',
            }
          - {
              id: 'id_10',
              source: 'sme_crm',
              source_type: 'newsletter',
              key: 'key_10',
              fan_id: 'fan_10',
              fan_email: 'dummyemail@email.com',
              fan_mobile: '(372) 777 777',
              happened_at: 2023-10-07 00:00:00-0,
              vendor_id: 34590,
              subaccount_id: 0,
              global_participant_id: 'artist C',
              crm_fan_id: null,
              cleaned_mobile: '(372) 555 555',
              original_fan_id: 'fan_10',
            }
          - {
              id: 'id_11',
              source: 'sme_crm',
              source_type: 'newsletter',
              key: 'key_10',
              fan_id: 'fan_10',
              fan_email: 'dummyemail@email.com',
              fan_mobile: '(372) 555 555',
              happened_at: 2023-11-07 00:00:00-0,
              vendor_id: 34590,
              subaccount_id: 0,
              global_participant_id: 'artist C',
              crm_fan_id: null,
              cleaned_mobile: '(372) 555 555',
              original_fan_id: 'fan_10',
            }
  - name: test_deduplicate_by_fan_mobile
    description: >
      Test that fans without email and with initially 
      differently formatted mobile phone will get
      same id if their formatted phones match.
    model: STG_DEDUPLICATED_EXTERNAL_EVENTS_DBT
    given:
      - input: ref('STG_DEDUPLICATED_EXTERNAL_EVENTS_BY_FAN_ID_DBT')
        rows:
          - {
              id: 'id_1',
              source: 'sme_crm',
              source_type: 'newsletter',
              key: 'key_1',
              fan_id: 'fan_1',
              fan_email: 'john@email.com',
              fan_mobile: '+372 1 2 3',
              happened_at: 2023-11-07 00:00:00-0,
              vendor_id: 34590,
              subaccount_id: 0,
              global_participant_id: 'artist a',
              crm_fan_id: 'crm_1',
              cleaned_mobile: '(372) 123',
              original_fan_id: 'fan_1',
            }
          - {
            id: 'id_2',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_2',
            fan_id: 'fan_2',
            fan_email: 'mary@email.com',
            fan_mobile: null,
            happened_at: 2023-11-13 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist a',
            crm_fan_id: 'crm_2',
            cleaned_mobile: null,
            original_fan_id: 'fan_2',
          }
          - {
            id: 'id_3',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_3',
            fan_id: 'fan_1',
            fan_email: 'john@email.com',
            fan_mobile: '(372) 123',
            happened_at: 2023-11-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist a',
            crm_fan_id: null,
            cleaned_mobile: '(372) 123',
            original_fan_id: 'fan_3',
          }
          - {
            id: 'id_4',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_4',
            fan_id: 'fan_1',
            fan_email: 'john@email.com',
            fan_mobile: '372123',
            happened_at: 2024-11-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist a',
            crm_fan_id: null,
            cleaned_mobile: '(372) 123',
            original_fan_id: 'fan_4',
          }
          - {
            id: 'id_5',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_5',
            fan_id: 'fan_5',
            fan_email: null,
            fan_mobile: '44 77 88',
            happened_at: 2024-05-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist b',
            crm_fan_id: null,
            cleaned_mobile: '(44) 7788',
            original_fan_id: 'fan_5',
          }
          - {
            id: 'id_6',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_6',
            fan_id: 'fan_6',
            fan_email: null,
            fan_mobile: '+44 77 88',
            happened_at: 2024-03-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist b',
            crm_fan_id: null,
            cleaned_mobile: '(44) 7788',
            original_fan_id: 'fan_6',
          }
          - {
              id: 'id_7',
              source: 'sme_crm',
              source_type: 'newsletter',
              key: 'key_7',
              fan_id: 'fan_7',
              fan_email: null,
              fan_mobile: '+372 555',
              happened_at: 2023-11-07 00:00:00-0,
              vendor_id: 34590,
              subaccount_id: 0,
              global_participant_id: 'artist C',
              crm_fan_id: null,
             cleaned_mobile: '(372) 555',
             original_fan_id: 'fan_7',
            }
    expect:
      rows:
          - {
              id: 'id_1',
              source: 'sme_crm',
              source_type: 'newsletter',
              key: 'key_1',
              fan_id: 'fan_1',
              fan_email: 'john@email.com',
              fan_mobile: '(372) 123',
              happened_at: 2023-11-07 00:00:00-0,
              vendor_id: 34590,
              subaccount_id: 0,
              global_participant_id: 'artist a',
              crm_fan_id: 'crm_1',
              original_fan_id: 'fan_1',
            }
          - {
            id: 'id_2',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_2',
            fan_id: 'fan_2',
            fan_email: 'mary@email.com',
            fan_mobile: null,
            happened_at: 2023-11-13 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist a',
            crm_fan_id: 'crm_2',
            original_fan_id: 'fan_2',
          }
          - {
            id: 'id_3',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_3',
            fan_id: 'fan_1',
            fan_email: 'john@email.com',
            fan_mobile: '(372) 123',
            happened_at: 2023-11-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist a',
            crm_fan_id: null,
            original_fan_id: 'fan_3',
          }
          - {
            id: 'id_4',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_4',
            fan_id: 'fan_1',
            fan_email: 'john@email.com',
            fan_mobile: '(372) 123',
            happened_at: 2024-11-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist a',
            crm_fan_id: null,
            original_fan_id: 'fan_4',
          }
          # fans with id 5 and 6 will get new id based on shared clean- mobile
          - {
            id: 'id_5',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_5',
            fan_id: 'b4881b68334aba67a83a22575dfd10b34ce48c096467c4579d4289668413d35f',
            fan_email: null,
            fan_mobile: '(44) 7788',
            happened_at: 2024-05-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist b',
            crm_fan_id: null,
            original_fan_id: 'fan_5',
          }
          - {
            id: 'id_6',
            source: 'sme_crm',
            source_type: 'newsletter',
            key: 'key_6',
            fan_id: 'b4881b68334aba67a83a22575dfd10b34ce48c096467c4579d4289668413d35f',
            fan_email: null,
            fan_mobile: '(44) 7788',
            happened_at: 2024-03-14 00:00:00-0,
            vendor_id: 34590,
            subaccount_id: 0,
            global_participant_id: 'artist b',
            crm_fan_id: null,
            original_fan_id: 'fan_6',
          }
          - {
              id: 'id_7',
              source: 'sme_crm',
              source_type: 'newsletter',
              key: 'key_7',
              fan_id: '472317fd1e19cb68ebb815035424368f66823181c6bd0535d984399679a246b6',
              fan_email: null,
              fan_mobile: '(372) 555',
              happened_at: 2023-11-07 00:00:00-0,
              vendor_id: 34590,
              subaccount_id: 0,
              global_participant_id: 'artist C',
              crm_fan_id: null,
             original_fan_id: 'fan_7',
            }