accounts:
  orchard:
    destination_s3_path: s3://cucumbers/AppleMusic/archives/{date}/{file_name}
    class_path: ./feed_ingestion/conf/
    vendor_id: 80028967
    detail_file:
      report_type: amEvent
      date_type: Daily
      report_subtype: Detailed
      file_name: AppleMusic_Event_{vendor_id}_{date}.txt.gz
      delimiter: \t
      copy_options: ["gzip","ignoreheader as 1"]
      temp_table: staging_apple_music_detail_file_{date}
      schema: [
        datestamp: date,
        apple_identifier: bigint,
        action_type: bigint,
        end_reason_type: bigint,
        time_bucket: bigint,
        utc_offset: bigint,
        membership: varchar(2000),
        membership_mode: varchar(2000),
        offline: bigint,
        storefront_name: varchar(2000),
        quantity: bigint,
        ingest_datestamp: date
        ]
    content_file:
      report_type: amContent
      date_type: Daily
      report_subtype: Detailed
      file_name: AppleMusic_Content_{vendor_id}_{date}.txt.gz
      delimiter: \t
      copy_options: ["gzip","ignoreheader as 1"]
      temp_table: staging_apple_music_content_file_{date}
      schema: [
        apple_identifier: bigint,
        isrc: varchar(20),
        item_title: varchar(2000),
        item_artist: varchar(2000),
        item_type: varchar(2000),
        media_type: varchar(2000),
        vendor_identifier: varchar(2000),
        label_studio_network: varchar(300),
        grid: varchar(20)
        ]
  ioda:
    destination_s3_path: s3://cucumbers/AppleMusic/archives/{date}/{file_name}
    class_path: ./feed_ingestion/conf/
    vendor_id: 80029727
    detail_file:
      report_type: amEvent
      date_type: Daily
      report_subtype: Detailed
      file_name: AppleMusic_Event_{vendor_id}_{date}.txt.gz
      delimiter: \t
      copy_options: ["gzip","ignoreheader as 1"]
      temp_table: staging_apple_music_detail_file_{date}
      schema: [
        datestamp: date,
        apple_identifier: bigint,
        action_type: bigint,
        end_reason_type: bigint,
        time_bucket: bigint,
        utc_offset: bigint,
        membership: varchar(2000),
        membership_mode: varchar(2000),
        offline: bigint,
        storefront_name: varchar(2000),
        quantity: bigint,
        ingest_datestamp: date
        ]
    content_file:
      report_type: amContent
      date_type: Daily
      report_subtype: Detailed
      file_name: AppleMusic_Content_{vendor_id}_{date}.txt.gz
      delimiter: \t
      copy_options: ["gzip","ignoreheader as 1"]
      temp_table: staging_apple_music_content_file_{date}
      schema: [
        apple_identifier: bigint,
        isrc: varchar(20),
        item_title: varchar(2000),
        item_artist: varchar(2000),
        item_type: varchar(2000),
        media_type: varchar(2000),
        vendor_identifier: varchar(2000),
        label_studio_network: varchar(300),
        grid: varchar(20)
        ]
staging_raw_apple_music_schema: [
  datestamp: date,
  apple_identifier: bigint,
  action_type: bigint,
  end_reason_type: bigint,
  time_bucket: bigint,
  utc_offset: bigint,
  membership: varchar(2000),
  membership_mode: varchar(2000),
  offline: bigint,
  storefront_name: varchar(2000),
  quantity: bigint,
  isrc: varchar(20),
  item_title: varchar(2000),
  item_artist: varchar(2000),
  item_type: varchar(2000),
  media_type: varchar(2000),
  download_date: date,
  vendor: varchar(100),
  ingest_datestamp: date]
database_schema: production
queries:
  insert_select_into_staging_raw_apple_music: "
      insert into {database_schema}.staging_raw_apple_music
      select datestamp, detail.apple_identifier, action_type,
             end_reason_type, time_bucket, utc_offset,
             membership, membership_mode, offline, storefront_name,
             quantity, isrc, item_title, item_artist, item_type,
             media_type, '{date}' as download_date,
             '{vendor}' as vendor, detail.ingest_datestamp
      from {database_schema}.staging_apple_music_detail_file_{date} detail
      inner join {database_schema}.staging_apple_music_content_file_{date} content
          on detail.apple_identifier = content.apple_identifier"
  delete_from_fact_analytics: "
      delete from {database_schema}.fact_analytics
      where storeid = 1
      and split_part(uuid,'_',2)='{date}' and split_part(uuid,'_',3)='AppleMusic'"
  delete_from_fact_analytics_error: "
      delete from {database_schema}.fact_analytics_error
      where storeid = 1
      and split_part(uuid,'_',2)='{date}' and split_part(uuid,'_',3)='AppleMusic'"
  unload_data_from_for_loading_fact_analytics: "
       select dr.artistid, ss.dayid, dt.labelid, ss.storeid, dr.genreid, cast(ss.releaseid as bigint),
       dt.trackid, ss.countryid, ss.transactiontypeid, ss.zipid, dr.catalogid, dt.isrcid,
       dr.imprintid, ss.currencyid, ss.storeuserid, ss.processeddaytime, ss.royalty, ss.royaltydollar,
       ss.quantity, ss.paid_units, ss.free_units, ss.uuid as uuid, dr.subaccountid, ss.displaydate, 1 as formatid
       from (
           select
           0 as artistid, dd.dayid, 0 as labelid, {store_id} as storeid,0 as genreid,
           m.orchard_release_id as releaseid,
           0 as trackid, dc.countryid,
           case when ss.media_type=2 then 17 else 1 end as transactiontypeid,
           0 as zipid, 0 as catalogid,0 as isrcid,
           0 as imprintid, 0 as currencyid, 0 as storeuserid, getdate() as processeddaytime, 0 as royalty, 0 as royaltydollar, ss.quantity,
           case when ss.membership_mode=\\'TRIAL\\' then 0 else ss.quantity end as paid_units,
           case when ss.membership_mode=\\'TRIAL\\' then ss.quantity else 0 end as free_units,
           ss.apple_identifier || \\'_\\' || \\'{date}\\' || \\'_\\' || \\'AppleMusic\\' as uuid, 0 as subaccountid, dd.displaydate,
           m.orchard_track_id as isrc
           from {database_schema}.staging_raw_apple_music ss
               inner join {database_schema}.apple_id_mapping m on m.apple_id = ss.apple_identifier
               inner join dim_day dd on dd.displaydate = trunc(dateadd(second, utc_offset, ss.datestamp))
               inner join dim_country dc on dc.country_code = ss.storefront_name
               {where_clause}
       ) ss
       inner join dim_track_clean dt on dt.isrc = ss.isrc and dt.upc = ss.releaseid
       inner join dim_release dr on dr.releaseid = dt.upc
       where dr.product_type = \\'digital\\'"
  unload_data_from_for_loading_fact_analytics_error: "
        select \\'\\' as id, ss.item_artist as artistname, \\'\\' as catalognumber, ss.storefront_name as countrycode,
        \\'\\' as currencycode, ss.download_date as downloaddate, \\'\\' as freeunits,
        \\'\\' as genrename, \\'\\' as imprint, ss.isrc as isrc, \\'\\' as labelname, \\'\\' as paidunits, \\'\\' as royalty, \\'\\' as royaltycurrency, \\'\\' as royaltyusd,
        ss.storefront_name as storename, \\'\\' as subaccountname, ss.item_title as trackname, \\'S\\' as transactiontypecode, ss.quantity as units,
        \\'\\' as upc, \\'\\' as zipcode,
        \\'\\' as artistid, \\'\\' as catalogid, ss.countryid as countryid, \\'\\' as currencyid, ss.dayid as dayid, \\'\\' as genreid, \\'\\' as imprintid,
        \\'\\' as isrcid, \\'\\' as labelid, cast(ss.releaseid as bigint) as releaseid, 1 as storeid, \\'\\' as subaccountid, \\'\\' as trackid, 1 as transactiontypeid,
        \\'\\' as zipid, \\'\\' as errorcode, \\'\\' as processed, \\'\\' as creditflag, ss.uuid,
        getdate() as processeddaytime, \\'\\' as formatid, \\'\\' as formatname
        from (
            select
            0 as artistid, dd.dayid, 0 as labelid, {store_id} as storeid,0 as genreid,
            m.orchard_release_id as releaseid,
            0 as trackid, dc.countryid, 1 as transactiontypeid, 0 as zipid, 0 as catalogid,0 as isrcid,
            0 as imprintid, 0 as currencyid, 0 as storeuserid, getdate() as processeddaytime, 0 as royalty, 0 as royaltydollar, ss.quantity,
            case when ss.membership_mode=\\'TRIAL\\' then 0 else ss.quantity end as paid_units,
            case when ss.membership_mode=\\'TRIAL\\' then ss.quantity else 0 end as free_units,
            ss.apple_identifier || \\'_\\' || \\'{date}\\' || \\'_\\' || \\'AppleMusic\\' as uuid, 0 as subaccountid, dd.displaydate,
            m.orchard_track_id as isrc, ss.storefront_name, ss.item_title, ss.item_artist, ss.download_date, ss.apple_identifier as apple_id
            from {database_schema}.staging_raw_apple_music ss
                left join {database_schema}.apple_id_mapping m on m.apple_id = ss.apple_identifier
                inner join dim_day dd on dd.displaydate = trunc(dateadd(second, utc_offset, ss.datestamp))
                inner join dim_country dc on dc.country_code = ss.storefront_name
                        where (ss.download_date = \\'{date}\\' and trunc(dateadd(second, utc_offset, ss.datestamp)) <= \\'{date}\\') or
                        (ss.download_date < \\'{date}\\' and trunc(dateadd(second, utc_offset, ss.datestamp)) = \\'{date}\\')
        ) ss
        left join dim_track_clean dt on dt.isrc = ss.isrc and dt.upc = ss.releaseid
        left join dim_release dr on dr.releaseid = dt.upc
        where dt.trackid is null or dr.releaseid is null or ss.apple_id is null
        and dr.product_type = \\'digital\\'"
from_num_of_days_back: 5
to_num_of_days_back: 1
store_id: 1
fact_analytics_s3_path: "s3://cucumbers/AppleMusic/fact_analytics/{date}/fact_analytics"
fact_analytics_error_s3_path: "s3://cucumbers/AppleMusic/fact_analytics_error/{date}/fact_analytics_error"
use_context_date: True
delay: 2