package config

/**
 *
 * 埋点流量相关常量配置
 * @Author zhenxin.ma
 * @Date 2019/8/20 9:43
 * @Version 1.0
 */
object MyConfigSession {

    //从源表pica_log.picalog_trace_app_part中执行SQL获取源数据,这里获取昨天的
    final val SOURCE_SQL: String =
        """
          		  |select pseudo_session,doctor_id,mobile,device_token,user_token_tourist,class_name,view_path,action,
          		  |component_tag,app_version,device_type,device_brand,device_model,network_type,created from pica.picalog_trace_app_part
          		  | where  pseudo_session is not null and pseudo_session !=''
          		  | and pseudo_id !='' and extra_info !='com.picahealth.patient' and serviceName != 'trace3'
                | and action!='ACTION_WEB_AFTER'
                | and FROM_UNIXTIME(cast(substring(created,1,10) as bigint),'yyyy-MM-dd')=created_day and created is not null and created!=''
        		""".stripMargin //and `action`!='ACTION_EQUIP_INFO'


    //Hive的DW层流量表
    final val HIVE_TABLE0: String = "pica_ods.ods_log_session_pref"
    final val HIVE_TABLE1_TMP: String = "pica_dw.dw_fact_log_session_tmp"
    final val HIVE_TABLE2_TMP: String = "pica_dw.dw_fact_log_session_path_tmp"
    final val HIVE_TABLE1: String = "pica.dwd_fact_log_session"
    final val HIVE_TABLE2: String = "pica_dw.dw_fact_log_session_path"
    final val HIVE_TABLE3: String = "pica_dw.dw_fact_log_session_heart"
    final val HIVE_TABLE4: String = "pica.dwd_fact_log_session_term"
    final val HIVE_TABLE4_MID: String = "pica_dw.dw_fact_log_session_term_mid"
    final val HIVE_TABLE5: String = "pica_dw.pathconvertconfig"
    final val HIVE_TABLE6: String = "pica_dw.dw_fact_log_session_path_convert"


    //写入的文件路径
    final val PARQUET_PATH: String = "hdfs://bi-name1:8020/tmp/output/"


    //流量表中的临时视图
    final val VIEW_SESSION_ODS: String = "ods_session"
    final val VIEW_SESSION_NO_MATCH: String = "ods_session_no_user_id"
    final val VIEW_MOBILE_PHONE: String = "mobile_phone_match"
    final val VIEW_EQUIPMENT_INFO: String = "equipment_info"
    final val VIEW_DEVICE_TOKEN: String = "device_token_match"
    //流量表中使用的三个字典表作为过滤数据条件
    final val ACTION_TYPE_SQL: String = "select action_type,'1' as is_valid from pica.dwd_log_action_type where is_valid=1"
    final val ACTION_TYPE_SQL_HEART: String = "select action_type,'1' as is_valid from pica.dwd_log_action_type where  action_type='ACTION_HEART_BEAT'"
    final val CLASS_NAME_SQL: String = "select class_name, '0' as is_valid from pica.dwd_log_class_name where is_valid=0"
    final val MENU_CODE_SQL: String = "select distinct view_path,  menu_code from pica.dwd_log_menu_class_code where view_path is not Null"
    //流量表中根据action_type获取对应的action_category类型
    final val ACTION_CATEGORY_SQL: String = "select action_type,action_category from pica.dwd_log_action_type where is_valid=1"
    final val ACTION_CATEGORY_SQL_HEART: String = "select action_type,action_category from pica_dw.dw_dim_log_action_type  where  action_type='ACTION_HEART_BEAT'"
    final val ACTION_URLLABEL_SQL:String = "select url_content,label_value from pica.dwd_log_action_urllabel"

    //从源表pica_log.picalog_trace_app_part中执行SQL获取源数据,这里获取昨天的
    final val SOURCE_SQL_PREF: String =
        """
          		  |select device_token,pseudo_session,class_name,action,view_path,component_tag,created,mobile,doctor_id,device_brand,device_model,app_version,
          |device_type,web_data,web_data_type,alternate_info,network_type,remark1 login_state,remark2 first_app_version,remark3 ,user_token_tourist,serviceName
          | from pica_log.picalog_trace_app_part
          		  | where  pseudo_session is not null and pseudo_session !=''
          		  | and pseudo_id !='' and extra_info !='com.picahealth.patient' and serviceName != 'trace3'
          | and created is not null and created!='' and FROM_UNIXTIME(cast(substring(created,1,10) as bigint),'yyyy-MM-dd')=created_day
        		""".stripMargin



    //从源表pica_log.picalog_trace_app_part中执行SQL获取源数据,输入指定日期参数时执行的SQL
    final val SOURCE_SQL_FROM_PREF: String =
        s"""
           |select pseudo_session,cast(user_id_int as string) user_id,mobile,device_token,user_token  , view_class  ,view_path,action,action_type,
           |component_tag,menu_code,action_code,`position`,label_value,label_class,app_version,device_type,device_brand,device_model,"" device_system,
           |net_type,created_time,date_time from ${HIVE_TABLE0}
           | where 1=1  and `action`!='ACTION_EQUIP_INFO'
        """.stripMargin


    //从dw_fact_log_session表中筛选数据
    final val SOURCE_SQL_PATH: String =
        s"""
           |select id log_session_id, session_id,user_id_int user_id,device_token,action_type,user_token,menu_code,action_code,position,label_value,label_class,
           |app_version,device_type,created_time,date_time,module_class1,module_class2 from ${MyConfigSession.HIVE_TABLE1}
           | where  app_version >= '3.1.7'
           | AND ((action_type ='ACTION_CLICK'  and action_code != 'null' ) OR  action_type ='ACTION_VIEW' )
           | and (menu_code != '0' and menu_code !='null' and menu_code !='' and (length(menu_code) <= 3 or length(menu_code)=7) )
        """.stripMargin

    //从dw_fact_log_session_TERM表中筛选数据
    final val SOURCE_SQL_PATH_NEW: String =
        s"""
           |select id log_session_id, session_id,  user_id,device_token,action_type,user_token,menu_code,action_code,position,label_value,label_class,action_step,
           |app_version,device_type,device_brand,device_model,net_type,created_time,date_time,module_class1,module_class2 from ${MyConfigSession.HIVE_TABLE4}
           | where  app_version >= '3.1.7'
           | AND ((action_type ='ACTION_CLICK'  and action_code != 'null' )   OR  action_type ='ACTION_VIEW'   )
           | and (menu_code != '0' and menu_code !='null' and menu_code !='' and (length(menu_code) <= 3 or length(menu_code)=7) and cast(menu_code as int ) is not null)
          """.stripMargin

    //从pica_dw.pathconvertconfig表中筛选数据
    final val SOURCE_SQL_CONFIG: String =
        s"""
           |select
           |projectid,
           |nodeid,
           |nodename,
           |menucode,
           |actioncode,
           |uv,
           |refer_menu_code,
           |refer_action_code,
           |starttime,
           |endtime
           |from ${MyConfigSession.HIVE_TABLE5}
          """.stripMargin

    //从pica_dw.dw_fact_log_session_path_convert表中筛选数据
    final val SOURCE_SQL_PATH_CONVERT: String =
        s"""
           |select
           |log_session_id,session_id,user_id,action_type,
           |user_token,menu_code,action_code,position,
           |label_value,label_class,refer_log_session_id,
           |refer_menu_code,refer_action_code,refer_position,
           |refer_label_value,refer_time_diff,refer_created,step_id,
           |app_version,device_type,device_brand,
           |device_model,net_type,created_time,
           |date_time,module_class1,module_class2,
           |user_identity_id,created_day
           |from ${MyConfigSession.HIVE_TABLE6}
          """.stripMargin





    //从dw_fact_log_session_TERM表中筛选数据
    final val SOURCE_PATH_CONVERT: String =
        s"""
           |select id log_session_id, session_id,  user_id,device_token,action_type,user_token,menu_code,action_code,position,label_value,label_class,action_step,
           |app_version,device_type,device_brand,device_model,net_type,created_time,date_time,module_class1,module_class2,view_path from ${MyConfigSession.HIVE_TABLE4}
           | where  app_version >= '3.1.7'
           | AND ((action_type ='ACTION_CLICK'  and action_code != 'null' )   OR  action_type ='ACTION_VIEW'   )
           | and (menu_code != '0' and menu_code !='null' and menu_code !='' and (length(menu_code) <= 3 or length(menu_code)=7) and cast(menu_code as int ) is not null)
           | and menu_code !='499'
          """.stripMargin




    //匹配user_id的条件
    //0.使用pica_ds.pica_doctor表匹配,匹配不上的user_id值为'0'
    final val INIT_USER_ID_SQL =
    s"""
       |SELECT t.session_id, COALESCE(cast(b.id as string),'0') AS user_id, t.mobile, t.device_token, t.user_token,
       |t.view_class,t.view_path,t.action_type,t.component_tag, t.menu_code,
       |t.action_code, t.position, t.label_value,t.label_class,t.module_class1,t.module_class2,t.app_version,t.device_type,
       |t.device_brand, t.device_model, t.device_system,t.net_type,t.created_time,
       |t.date_time from ${MyConfigSession.VIEW_SESSION_ODS} as t
       |left join pica.ods_pica_p_doctor as b on t.user_id = cast(b.id as string)
  """.stripMargin
    final val INIT_USER_ID_SQL_PREF =
        s"""
           |SELECT t.pseudo_session, COALESCE(cast(b.id as string),'0') AS user_id, t.mobile, t.device_token, t.user_token,
           |t.view_class,t.view_path,t.action, t.component_tag, t.menu_code,
           |t.action_code, t.position, t.label_value,t.label_class,t.app_version,t.device_type,
           |t.device_brand, t.device_model, t.net_type,t.created_time,t.date_time,
           |t.web_data,t.web_data_type,t.alternate_info,t.login_state,t.first_app_version,t.serviceName
           | from ${MyConfigSession.VIEW_SESSION_ODS} as t
           |left join pica_ds.pica_doctor as b on t.user_id = cast(b.id as string)
  """.stripMargin
    //1.针对没有匹配到的user_id,先使用 mobile_phone 进行匹配,得到 user_id 匹配,'0',XK0HdMN6dAfOlYPOFHHL0A==表示''加密之后的mobile
    final val MOBILE_PHONE_SQL: String =
        s"""
           |SELECT ss.session_id, COALESCE(cast(b.id as string),'0') AS user_id, ss.mobile, ss.device_token, ss.user_token,
           |ss.view_class,ss.view_path,ss.action_type,ss.component_tag, ss.menu_code,
           |ss.action_code, ss.position,ss.label_value,ss.label_class,ss.module_class1,ss.module_class2,ss.app_version, ss.device_type,
           |ss.device_brand, ss.device_model,ss.device_system, ss.net_type,ss.created_time,
           |ss.date_time from ${MyConfigSession.VIEW_SESSION_NO_MATCH}  AS ss
           |left join (select distinct id,mobile_phone from pica.ods_pica_p_doctor where ods_pica_p_doctor.delete_flag = 1 and mobile_phone!='' and mobile_phone!='XK0HdMN6dAfOlYPOFHHL0A==' ) AS b on ss.mobile = b.mobile_phone
  """.stripMargin
    final val MOBILE_PHONE_SQL_PREF: String =
        s"""
           |SELECT ss.pseudo_session,COALESCE(cast(b.id as string),'0') AS user_id,ss.mobile,ss.device_token,ss.user_token,
           |ss.view_class,ss.view_path,ss.action, ss.component_tag,ss.menu_code,
           |ss.action_code,ss.position,ss.label_value,ss.label_class,ss.app_version,ss.device_type,
           |ss.device_brand,ss.device_model, ss.net_type,ss.created_time,
           |ss.date_time,ss.web_data,ss.web_data_type,ss.alternate_info,ss.login_state,ss.first_app_version,ss.serviceName
           | from ${MyConfigSession.VIEW_SESSION_NO_MATCH}  AS ss
           |left join (select distinct id,mobile_phone from pica_ds.pica_doctor where pica_doctor.delete_flag = 1 ) AS b on ss.mobile = b.mobile_phone
  """.stripMargin
    //2.使用equipment表匹配,默认是昨天的
    final val EQUIPMENT_INFO_SQL: String =
        """
          |SELECT a.user_id,a.device_token ,ROW_NUMBER() OVER ( PARTITION BY a.device_token ORDER BY a.creat_time DESC ) row_d
          |from (select e1.user_id,e1.device_token,e1.creat_time,e1.modify_time,e1.delete_flag from  pica.ods_picamessage_p_equipment_info e1  union
          | select e2.user_id,e2.device_token,e2.creat_time, e2.modify_time,e2.delete_flag  from  pica.ods_picamessage_p_equipment_info_shadow  e2 )  a
          | where a.user_id IS NOT NULL and a.user_id>0 and a.delete_flag=1 and device_token is not null
          | AND (to_date(a.creat_time) = date_sub(current_date(),1) OR to_date(a.modify_time) = date_sub(current_date(),1))
    """.stripMargin

    //注意:1.使用equipment表匹配,如果匹配的是历史指定日期的数据,那么需要使用不同的表和下面这个SQL表,限定stat_date日期
    final val EQUIPMENT_INFO_SQL_ARGS: String =
        """
      		|SELECT a.doctor_id as user_id, a.device_token, ROW_NUMBER() OVER (PARTITION BY a.device_token ORDER BY a.creat_time DESC) row_d
      		| from pica.dwd_study_zipper_picams_equipment_info AS a where a.doctor_id IS NOT NULL AND a.stat_date=
    """.stripMargin


    //先和equiment表进行匹配,使用 device_token 匹配,得到 user_id 匹配,'0'
    final val DEVICE_TOKEN_SQL: String =
        s"""
           |SELECT t.session_id, COALESCE(cast(b.user_id as string),'0') AS user_id, t.mobile, t.device_token, t.user_token,
           |t.view_class,t.view_path,t.action_type,t.component_tag, t.menu_code,
           |t.action_code, t.position, t.label_value,t.label_class,t.module_class1,t.module_class2,t.app_version,t.device_type,
           |t.device_brand, t.device_model, t.device_system,t.net_type,t.created_time,
           |t.date_time from (select * from  ${MyConfigSession.VIEW_MOBILE_PHONE}  a where a.user_id= '0' ) as t
           |left join ${MyConfigSession.VIEW_EQUIPMENT_INFO} as b on t.device_token = b.device_token
  """.stripMargin
    final val DEVICE_TOKEN_SQL_PREF: String =
        s"""
	   |SELECT t.pseudo_session,COALESCE(cast(b.user_id as string),'0') AS user_id,t.mobile,t.device_token,t.user_token,
	   |t.view_class,t.view_path,t.action ,t.component_tag,t.menu_code,
	   |t.action_code,t.position,t.label_value,t.label_class,t.app_version,t.device_type,
	   |t.device_brand,t.device_model, t.net_type,t.created_time,
	   |t.date_time,t.web_data,t.web_data_type,t.alternate_info,t.login_state,t.first_app_version,t.serviceName
	   | from (select * from  ${MyConfigSession.VIEW_MOBILE_PHONE}  a where a.user_id= '0' ) as t
	   |left join ${MyConfigSession.VIEW_EQUIPMENT_INFO} as b on t.device_token = b.device_token
""".stripMargin




    //sessionGap时间,切割 session 的依据,30 minutes
    final val SESSION_GAP: Long = 30 * 60 * 1000
    //分隔符
    final val DELIMITER: String = "\001"

    //hdfs127 Mysql配置,记录任务的执行情况
    final val JDBC_DRIVER = "com.mysql.jdbc.Driver"
    final val DATA_BASE = "pica_job"
    final val JDBC_URL = s"jdbc:mysql://hdfs127:3306/${DATA_BASE}?useTimezone=true&serverTimezone=GMT%2B8"
    final val JDBC_USERNAME = "pica_spider"
    final val JDBC_PSSWORD = "5$7FXgz#e5JWP08e"
    final val JDBC_TABLE = "schedule_job_record"

    final val mysql_url_user_profile="jdbc:mysql://hdfs127:3306/user_profile?useTimezone=true&serverTimezone=GMT%2B8"
    final val mysql_username_user_profile="bi_admin"
    final val mysql_password_user_profile="5$7FXgz#e5JWP08e"


    final val MENU_CODE_VIEW_PATH_SQL: String = "select distinct full_path view_path, menu_code from pica_ds.pica_bi_bi_menu_code_h5 where full_path is not Null"



}
