use emr_doppler_v1;

set hive.exec.compress.output=true;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;
set mapreduce.map.memory.mb=2048;
set mapreduce.reduce.memory.mb=3072;

set mapred.reduce.tasks=200;

INSERT overwrite table dim_pc_more_tmp
select game_id,
       to_date(arrival_ts)                             AS checkpoint,
       player_id                                       AS player_id,
       user_id,
       max(country_code)                               as country_code,
       max(facebook_id)                                as facebook_id,
       max(ios_id)                                     as ios_id,
       coalesce(max(android_id), max(v1_android_id))   as android_id,
       coalesce(max(google_aid), max(v1_google_aid))   as google_aid,
       max(ios_idfa)                                   as ios_idfa,
       max(ios_idfv)                                   as ios_idfv,
       max(googleplus_id)                              as googleplus_id,
       coalesce(max(gender), max(v1_gender))           as gender,
       max(substr(birth_year, 1, 4))                   as birth_year,
       coalesce(max(sdk_version), max(v1_sdk_version)) as sdk_version,
       max(engine_version)                             as engine_version,
       coalesce(max(os_version), max(v1_os_major))     as os_version,
       max(substr(manufacturer, 1, 32))                as manufacturer,
       coalesce(max(device), max(v1_device))           as device,
       coalesce(max(platform), max(v1_platform))       as platform,
       max(build)                                      as build,
       max(connection_type)                            as connection_type,
       max(is_jailbroken)                              as is_jailbroken,
       max(is_ad_tracking_limited)                     as is_ad_tracking_limited,
       max(is_logged_gamecenter)                       as is_logged_gamecenter,
       max(is_logged_googleplay)                       as is_logged_googleplay,
       max(install_ts)                                 as installed,
       max(install_publisher)                          as install_publisher,
       max(install_site)                               as install_site,
       max(install_campaign)                           as install_campaign,
       max(install_adgroup)                            as install_adgroup,
       max(install_ad)                                 as install_ad,
       max(install_keyword)                            as install_keyword,
       case
           when coalesce(max(install_publisher), max(install_site), max(install_campaign), max(install_adgroup),
                         max(install_ad), max(install_keyword)) is not null then true
           else false end                              as is_acquired,
       count(*)                                        as count_event
from emr_doppler_v1.event_${date_str_undline}
group by game_id, user_id, player_id, to_date(arrival_ts);

INSERT overwrite table dim_player_error_counts
SELECT game_id,
       player_id                                              AS player_id,
       SUM(CASE WHEN severity = 'debug' THEN 1 ELSE 0 END)    AS count_debug_error,
       SUM(CASE WHEN severity = 'info' THEN 1 ELSE 0 END)     AS count_info_error,
       SUM(CASE WHEN severity = 'warning' THEN 1 ELSE 0 END)  AS count_warning_error,
       SUM(CASE WHEN severity = 'error' THEN 1 ELSE 0 END)    AS count_error_error,
       SUM(CASE WHEN severity = 'critical' THEN 1 ELSE 0 END) AS count_critical_error
FROM emr_doppler_v1.event_${date_str_undline}
WHERE category = 'error'
  AND severity IS NOT NULL
GROUP BY game_id, player_id;