unit_tests:
  - name: test_fan_location
    description: >
      Test fan locations are cleaned properly
      and assigned to correct user id.
    model: FAN_LOCATION_DBT
    given:
      - input: source('fansifter', 'standard_location_postal_code')
        rows:
          - {
            postal_code: "84044",
            country_code2: 'US',
            admin1_code: 'UT',
            admin2_code: '035',
            admin3_code: '',
            latitude: 40.7009,
            longitude: -112.0809,
            place_name: "Magna",
          }
          - {
            postal_code: "PC14",
            country_code2: 'GB',
            admin1_code: 'ENG',
            admin2_code: '123456',
            admin3_code: '',
            latitude: 40.5,
            longitude: -74.5,
            place_name: "Little Town",
          }
          - {
            postal_code: "PC14",
            country_code2: 'GB',
            admin1_code: 'ENG',
            admin2_code: '123456',
            admin3_code: '',
            latitude: 39.5,
            longitude: -73.5,
            place_name: "Big Town",
          }
      - input: source('fansifter', 'standard_location_city')
        rows:
          - {
              id: 5128581,
              name: 'New York City',
              name_native: 'New York City',
              alternate_names: '...',
              country_code2: 'US',
              region_code: 'NY',
              latitude: 40.71427,
              longitude: -74.00597,
              population: 8804190,
            }
          - {
            id: 5780993,
            name: 'Salt Lake City',
            name_native: 'Salt Lake City',
            alternate_names: '...',
            country_code2: 'US',
            region_code: 'UT',
            latitude: 40.76078,
            longitude: -111.89105,
            population: 215548,
          }
      - input: source('fansifter', 'standard_location_region')
        rows:
          - {
              code: 'NY',
              country_code2: 'US',
              name: 'New York',
              name_native: 'New York',
            }
          - {
              code: 'UT',
              country_code2: 'US',
              name: 'Utah',
              name_native: 'Utah',
            }
      - input: ref('INT_FAN_LOCATION_CLEANED_FAN_ID_DBT')
        rows:
          - {
              fan_id: 'fan_1',
              country_iso2: 'US',
              region: 'NY',
              city: 'new york',
              postal_code: null,
            }
          - {
              fan_id: 'fan_1',
              country_iso2: 'US',
              region: 'New York',
              city: 'New York City',
              postal_code: null,
            }
          - {
              fan_id: 'fan_3',
              country_iso2: 'US',
              region: 'Utah',
              city: null,
              postal_code: null,
            }
          - {
              fan_id: 'fan_3',
              country_iso2: 'US',
              region: 'UT',
              city: null,
              postal_code: null,
            }
          - {
              fan_id: 'fan_6',
              country_iso2: 'US',
              region: 'NY',
              city: null,
              postal_code: null,
            }
          - {
              fan_id: 'fan_7',
              country_iso2: 'US',
              region: 'UT',
              city: null,
              postal_code: null,
            }
          - {
            fan_id: 'fan_8',
            country_iso2: 'GB',
            region: 'ENG',
            city: null,
            postal_code: null,
          }
          - {
            fan_id: 'fan_9',
            country_iso2: 'US',
            region: 'UT',
            city: 'Salt Lake City',
            postal_code: null,
          }
          - {
            fan_id: 'fan_10_postal',
            country_iso2: 'US',
            region: 'UT',
            city: null,
            postal_code: '84044',
          }
          - {
            fan_id: 'fan_11_multiple_postals',
            country_iso2: 'GB',
            region: null,
            city: null,
            postal_code: 'PC14',
          }
      - input: ref('LOCATION_RENAME_MAPPING_DBT')
        format: sql
        rows: select * from LOCATION_RENAME_MAPPING_DBT
    expect:
      rows:
          - {
              fan_id: 'fan_1',
              country_iso2: 'US',
              region: 'New York',
              region_code: 'NY',
              city: 'New York City',
              city_id: 5128581,
              latitude: '40.71427',
              longitude: '-74.00597',
              coordinates_from_postal_code: false,
            }
          - {
              fan_id: 'fan_3',
              country_iso2: 'US',
              region: 'Utah',
              region_code: 'UT',
              city: null,
              city_id: null,
              latitude: null,
              longitude: null,
              coordinates_from_postal_code: false,
            }
          - {
              fan_id: 'fan_6',
              country_iso2: 'US',
              region: 'New York',
              region_code: 'NY',
              city: null,
              city_id: null,
              latitude: null,
              longitude: null,
              coordinates_from_postal_code: false,
            }
          - {
              fan_id: 'fan_7',
              country_iso2: 'US',
              region: 'Utah',
              region_code: 'UT',
              city: null,
              city_id: null,
              latitude: null,
              longitude: null,
              coordinates_from_postal_code: false,
            }
          - {
            fan_id: 'fan_8',
            country_iso2: 'GB',
            region: null,
            region_code: null,
            city: null,
            city_id: null,
            latitude: null,
            longitude: null,
            coordinates_from_postal_code: false,
          }
          - {
            fan_id: 'fan_9',
            country_iso2: 'US',
            region: 'Utah',
            region_code: 'UT',
            city: 'Salt Lake City',
            city_id: 5780993,
            latitude: 40.76078,
            longitude: -111.89105,
            coordinates_from_postal_code: false,
          }
          - {
            fan_id: 'fan_10_postal',
            country_iso2: 'US',
            region: 'Utah',
            region_code: 'UT',
            city: null,
            city_id: null,
            latitude: 40.7009,
            longitude: -112.0809,
            coordinates_from_postal_code: true,
          }
          - {
            fan_id: 'fan_11_multiple_postals',
            country_iso2: 'GB',
            region: null,
            region_code: null,
            city: null,
            city_id: null,
            latitude: 40.0,
            longitude: -74.0,
            coordinates_from_postal_code: true,
          }
  - name: test_fan_gender
    description: >
      Test fan is assigned correct gender.
    model: FAN_GENDER_DBT
    given:
      - input: ref('INT_CRM_FAN_DBT')
        rows:
          - {
              crm_fan_id: 'fan_1',
              crm_fan_email: 'dummy0@email.com',
              crm_fan_country: 'US',
              crm_fan_gender: 'non binary other',
              crm_fan_first_name: null,
              crm_last_modified_date: 2024-11-07 00:00:00-0,
            }
          - {
              crm_fan_id: 'fan_2',
              crm_fan_email: 'dummy@email.com',
              crm_fan_country: 'US',
              crm_fan_gender: 'm',
              crm_fan_first_name: 'Jake',
              crm_last_modified_date: 2024-11-07 00:00:00-0,
            }
          - {
              crm_fan_id: 'fan_3',
              crm_fan_email: 'dummy2@email.com',
              crm_fan_country: 'US',
              crm_fan_gender: 'FEMALE',
              crm_fan_first_name: 'Michelle',
              crm_last_modified_date: 2024-11-07 00:00:00-0,
            }
          - {
              crm_fan_id: 'fan_4',
              crm_fan_email: 'dummy3@email.com',
              crm_fan_country: 'US',
              crm_fan_gender: 'MALE',
              crm_fan_first_name: 'John',
              crm_last_modified_date: 2024-11-07 00:00:00-0,
            }
      - input: ref('EVENT_DBT')
        rows:

          - {
              key: '123456',
              source: 'fileupload_crm',
              fan_id: 'fan_5',
              happened_at: 2024-11-07 00:00:00-0,
            }
          - {
              key: '789012',
              source: 'fileupload_crm',
              fan_id: 'fan_6',
              happened_at: 2024-11-07 00:00:00-0,
            }
          - {
              key: 'key_1',
              source: 'sme_marketing_cloud',
              fan_id: '494c9f101fac4582b23e01cf8f926c283c5b234e6ce6b563784dd67e2a3271e3',
              happened_at: 2024-11-07 00:00:00-0,
            }
          - {
              key: 'key_2',
              source: 'sme_marketing_cloud',
              fan_id: '90ed0c6566c9da54920e0d6d4f492f7d44390c08520526a347ccb3a0443ef1d7',
              happened_at: 2024-11-07 00:00:00-0,
            }
          - {
            key: 'key_3',
            source: 'sme_marketing_cloud',
            fan_id: '667b801705664e78b43d590677fec38f77a4530a661153f23504d9cae874a873',
            happened_at: 2024-11-07 00:00:00-0,
          }
          - {
              key: '100100:100:brand:100',
              source: 'ae',
              fan_id: '667b801705664e78b43d590677fec38f77a4530a661153f23504d9cae874a873',
              happened_at: 2024-11-07 00:00:00-0,
            }
          - {
            key: 'key_4',
            source: 'sme_marketing_cloud',
            fan_id: 'b0e6d402f772caf0453548d01d2df31cd3e2d8f0ad2bab1b798b86418f3045a9',
            happened_at: 2024-11-07 00:00:00-0,
          }
          - {
              key: '200200:100:brand:100',
              source: 'ae',
              fan_id: 'b0e6d402f772caf0453548d01d2df31cd3e2d8f0ad2bab1b798b86418f3045a9',
              happened_at: 2024-11-07 00:00:00-0,
            }
      - input: source('crm_fans', 'event_fanresponse_validated')
        rows:
          - {
              gender: 'Female',
              event_row_id: 123456,
              platform_name: 'file_upload'
            }
          - {
              gender: null,
              event_row_id: 789012,
              platform_name: 'file_upload'
            }
      - input: source('ae', 'ae_member')
        rows:
          - {
              last_updated: 2024-03-07 00:00:00-0,
              id: 100100,
              country_code: 'US'
            }
          - {
              last_updated: 2024-03-07 00:00:00-0,
              id: 200200,
              country_code: 'US'
            }
      - input: source('ae', 'ae_member_personal')
        rows:
          - {
              member_id: 100100,
              gender: 'F',
              email: 'dummy2@email.com',
              first_name: 'Michelle'
            }
          - {
              member_id: 200200,
              gender: 'MALE',
              email: 'dummy3@email.com',
              first_name: 'Jake'
            }
    expect:
      rows:
          - {
              fan_id: '667b801705664e78b43d590677fec38f77a4530a661153f23504d9cae874a873',
              gender: 'FEMALE',
            }
          - {
              fan_id: '90ed0c6566c9da54920e0d6d4f492f7d44390c08520526a347ccb3a0443ef1d7',
              gender: 'MALE',
          }
          - {
              fan_id: 'b0e6d402f772caf0453548d01d2df31cd3e2d8f0ad2bab1b798b86418f3045a9',
              gender: 'MALE',
            }
          - {
              fan_id: 'fan_5',
              gender: 'FEMALE',
            }
          - {
              fan_id: '494c9f101fac4582b23e01cf8f926c283c5b234e6ce6b563784dd67e2a3271e3',
              gender: 'NON_BINARY_OTHER',
            }
  - name: test_fan_dsp
    description: >
      Test fan is assigned correct dsps.
    model: FAN_DSP_DBT
    given:
      - input: ref('INT_CRM_FAN_DBT')
        rows:
          - {
              crm_fan_dsp: 'Email',
              crm_fan_email: 'dummy@email.com',
            }
          - {
              crm_fan_dsp: 'Email;Spotify',
              crm_fan_email: 'dummy2@email.com',
            }
          - {
              crm_fan_dsp: 'Deezer;Apple',
              crm_fan_email: 'dummy3@email.com',
            }
      - input: ref('STG_SONGWHIP_PRESAVE_EVENT_DBT')
        rows:
          - {
              fan_id: 'fan_8',
              key: 'album5395:amazon-music-presave-amzn1.account.AFLVC3Z2VSTRLHIJ54ITRCXKHHJQ',
            }
      - input: ref('DSP_STORE_ID_MAPPING_DBT')
        rows:
          - {
              store_id: 187,
              dsp: 'AMAZON',
            }
          - {
              store_id: 348,
              dsp: 'DEEZER',
            }
          - {
              store_id: 286,
              dsp: 'SPOTIFY',
            }
          - {
              store_id: 1,
              dsp: 'APPLE',
            }
      - input: source('fansifter', 'songwhip_presave')
        rows:
          - {
              store_id: 187,
              id: 'album5395:amazon-music-presave-amzn1.account.AFLVC3Z2VSTRLHIJ54ITRCXKHHJQ',
            }
      - input: ref('EVENT_DBT')
        rows:
          - {
              key: '123456',
              source: 'fileupload_crm',
              fan_id: 'fan_5',
            }
      - input: source('crm_fans', 'event_fanresponse_validated')
        rows:
          - {
              dsp: 'Deezer',
              event_row_id: 123456,
              platform_name: 'file_upload'
            }
    expect:
      rows:
          - {
              fan_id: '667b801705664e78b43d590677fec38f77a4530a661153f23504d9cae874a873',
              dsp: 'SPOTIFY',
            }
          - {
              fan_id: 'b0e6d402f772caf0453548d01d2df31cd3e2d8f0ad2bab1b798b86418f3045a9',
              dsp: 'DEEZER',
            }
          - {
              fan_id: 'b0e6d402f772caf0453548d01d2df31cd3e2d8f0ad2bab1b798b86418f3045a9',
              dsp: 'APPLE',
            }
          - {
              fan_id: 'fan_8',
              dsp: 'AMAZON',
            }
          - {
              fan_id: 'fan_5',
              dsp: 'DEEZER',
            }
  - name: test_fan_age
    description: >
      Test fan is assigned correct age. 
      NB! Expected age_in_years and age_range are not updated dynamically when calendar year changes.
      This means manual updates like in FS-6118.
    model: FAN_AGE_DBT
    given:
      - input: ref('EVENT_DBT')
        rows:
          - {
              key: 'key_11',
              source: 'sme_crm',
              fan_id: 'b0e6d402f772caf0453548d01d2df31cd3e2d8f0ad2bab1b798b86418f3045a9',
              happened_at: 2024-10-14 00:00:00-0,
            }
          - {
              key: '123456',
              source: 'fileupload_crm',
              fan_id: 'fan_5',
              happened_at: 2024-11-07 00:00:00-0,
            }
          - {
              key: '100100:100:brand:100',
              source: 'ae',
              fan_id: '667b801705664e78b43d590677fec38f77a4530a661153f23504d9cae874a873',
              happened_at: 2024-10-24 00:00:00-0,
            }
          - {
              key: 'key_12',
              source: 'sme_crm',
              fan_id: '667b801705664e78b43d590677fec38f77a4530a661153f23504d9cae874a873',
              happened_at: 2024-10-14 00:00:00-0,
            }
          - {
                key: 'key_13',
              source: 'sme_crm',
              fan_id: '90ed0c6566c9da54920e0d6d4f492f7d44390c08520526a347ccb3a0443ef1d7',
              happened_at: 2024-09-19 00:00:00-0,
            }
      - input: source('crm_fans', 'event_fanresponse_validated')
        rows:
          - {
              birthdate_mm_dd_yyyy: '2006-12-15',
              event_row_id: 123456,
              platform_name: 'file_upload'
            }
      - input: source('ae', 'ae_member')
        rows:
          - {
              last_updated: 2024-03-07 00:00:00-0,
              id: 100100,
            }
      - input: source('ae', 'ae_member_personal')
        rows:
          - {
              member_id: 100100,
              birth_date: 1980-11-07 00:00:00-0,
              email: 'dummy2@email.com',
            }
      - input: ref('INT_CRM_FAN_DBT')
        rows:
          - {
              crm_fan_id: 'fan_2',
              crm_fan_email: 'dummy@email.com',
              crm_last_modified_date: 2023-11-07 00:00:00-0,
              crm_fan_birthdate: '1990-02-09',
            }
          - {
              crm_fan_id: 'fan_3',
              crm_fan_email: 'dummy2@email.com',
              crm_last_modified_date: 2023-11-07 00:00:00-0,
              crm_fan_birthdate: null,
            }
          - {
              crm_fan_id: 'fan_4',
              crm_fan_email: 'dummy3@email.com',
              crm_last_modified_date: 2023-11-07 00:00:00-0,
              crm_fan_birthdate: '1970-01-01',
            }
    expect:
      rows:
          - {
              fan_id: 'fan_5',
              age_in_years: 20,
              age_range: 'FROM_18_TO_22',
            }
          - {
              fan_id: '667b801705664e78b43d590677fec38f77a4530a661153f23504d9cae874a873',
              age_in_years: 46,
              age_range: 'FROM_41_TO_51',
            }
          - {
              fan_id: '90ed0c6566c9da54920e0d6d4f492f7d44390c08520526a347ccb3a0443ef1d7',
              age_in_years: 36,
              age_range: 'FROM_31_TO_40',
            }