use dwh;

insert overwrite  table dwh.etl_af_org_daily_basic
select device_id,case when substring(app_id,1,2) ='id' and length(app_id) = 11 then 'idfa' else 'gaid' end as device_type,
case when substring(app_id,1,2) ='id' and length(app_id) = 11 then 'ios'  else 'android' end as platform,
case when substring(app_id,1,2) ='id' and length(app_id) = 11 then (case when category = 'All_GF_ADR_TransportNoFood-P2E_L365D' then '00000000001' else '00000000002' end )
else  (case when category = 'All_GF_ADR_TransportNoFood-P2E_L365D' then 'grabandroid.target1'
             when category = 'Mobvista_VN_Adr_TransportNoFood_19092019' then 'grabandroid.target2'
         else 'grabandroid.target3' end ) end as package_names,
category,
split(input__file__name,'[._/]')[7]  advertiser
 from dwh.etl_af_org_daily where dt='${dt_today}'
 and split(input__file__name,'[._/]')[10]='add'
 and  device_id rlike  '^[0-9a-fA-F]{8}(-[0-9a-fA-F]{4}){3}-[0-9a-fA-F]{12}$'
 and  split(input__file__name,'[._/]')[7] = 'grabmtg1';


insert overwrite table dwh.etl_af_org_total partition(dt='${dt_today}')
select device_id,max(device_type) device_type,
max(platform) platform,max(package_names) package_names,
max(category) category, max(advertiser) advertiser,
'${update_date}' update_date
from etl_af_org_daily_basic
group by device_id;

insert overwrite table ods_dmp_user_info partition(dt='${dt_today}', business='appsflyer')
select device_id,max(device_type) device_type,
max(platform) platform,
'VN' country,
'' age,
'' gender,
'' tags,
'${update_date}' first_req_day,
'${update_date}' last_req_day
 from etl_af_org_daily_basic
group by device_id