-- 创建角色
create role report_auto login;

-- 修改密码
alter role report_auto with password 'Richr00t!';

-- 创建模式
create schema report_auto;

-- 赋予权限
grant all on schema report_auto to report_auto;

-- 建表--暂时不考虑分区

-- 河北_CEM高品质2日统计
drop table if exists report_auto.he_d_high_quality;
CREATE TABLE report_auto.he_d_high_quality (
	id bigserial NOT NULL,
	month_id varchar(6) NOT NULL,
  day_id varchar(2) NOT NULL,
  acct_date varchar(30) NOT NULL,
  businoareaname varchar(15) NOT NULL,
  profes_dep varchar(15) NOT NULL,
  big_type_name varchar(60) NOT NULL,
  small_type_name varchar(120) NOT NULL,
  total_complaints varchar(30) NULL,
  hotline_complaints varchar(30) NULL,
  other_complaint varchar(30) NULL,
  litigation_volume varchar(30) NULL,
  satisfaction_rate varchar(30) NULL,
  satisfaction_count varchar(30) NULL,
  total_evaluation varchar(30) NULL,
  complaint_satisfied varchar(30) NULL,
  complaint_satisfied_list varchar(30) NULL,
  complaint_satisfied_count varchar(30) NULL,
  complaint_resolution varchar(30) NULL,
  complaint_resolution_list varchar(30) NULL,
  complaint_resolution_count varchar(30) NULL,
  complaint_response varchar(30) NULL,
  complaint_response_list varchar(30) NULL,
  complaint_response_count varchar(30) NULL,
  complaint varchar(30) NULL,
  fault_satisfaction_rate varchar(30) NULL,
  fault_satisfaction_list varchar(30) NULL,
  fault_satisfaction_count varchar(30) NULL,
  fault_resolution_rate varchar(30) NULL,
  fault_resolution_list varchar(30) NULL,
  fault_resolution_count varchar(30) NULL,
  fault_response_rate varchar(30) NULL,
  fault_response_list varchar(30) NULL,
  fault_response_count varchar(30) NULL,
  cteate_time timestamp NULL DEFAULT now(),
	CONSTRAINT he_d_high_quality_pkey PRIMARY KEY (id)
);

-- 河北_CEM移网质量投诉明细
drop table if exists report_auto.he_d_mobile_comp;
CREATE TABLE report_auto.he_d_mobile_comp (
	id bigserial NOT NULL,
  month_id varchar(6) NOT NULL,
  day_id varchar(2) NOT NULL,
  acct_date varchar(300) NOT NULL,
  sheet_no varchar(150) NULL,
  is_online_complete varchar(300) NULL,
  contact_no varchar(300) NULL,
  busi_no varchar(300) NULL,
  serv_content varchar(4136) NULL,
  last_deal_content varchar(4136) NULL,
  deal_depart_name varchar(300) NULL,
  deal_opinion varchar(4136) NULL,
  serv_type varchar(600) NULL,
  bus_type varchar(300) NULL,
  duty_reason varchar(600) NULL,
  accept_channel varchar(300) NULL,
  submit_channel varchar(300) NULL,
  compl_area_local varchar(300) NULL,
  duty_major varchar(300) NULL,
  product_name varchar(600) NULL,
  sp_product_code varchar(600) NULL,
  pre_repair_name varchar(300) NULL,
  pre_repair_charges varchar(24) NULL,
  fault_location varchar(300) NULL,
  cust_level varchar(300) NULL,
  satisfaction_in_reply varchar(300) NULL,
  is_ok_in_reply varchar(300) NULL,
  accept_time varchar(19) NULL,
  end_time varchar(19) NULL,
  proce_time varchar(19) NULL,
  cust_area varchar(300) NULL,
  is_cust_serv_complete varchar(300) NULL,
  is_send_sheet_complete varchar(300) NULL,
  is_repeat varchar(300) NULL,
  is_upgrade varchar(300) NULL,
  is_timeout varchar(300) NULL,
  gis_city varchar(300) NULL,
  process_nums varchar(24) NULL,
  deal_depart_name_1 varchar(300) NULL,
  deal_depart_name_2 varchar(300) NULL,
  deal_depart_name_3 varchar(300) NULL,
  first_call_back_time varchar(19) NULL,
  proce_remark varchar(4136) NULL,
  duty_major_day varchar(300) NULL,
  duty_reason_id_day varchar(300) NULL,
  duty_major_month varchar(300) NULL,
  duty_reason_id_month varchar(300) NULL,
  voice_text varchar(4136) NULL,
  cteate_time timestamp NULL DEFAULT now(),
  CONSTRAINT he_d_mobile_comp_pkey PRIMARY KEY (id)
)

-- 用户数
drop table if exists report_auto.user_count;
CREATE TABLE report_auto.user_count (
	id bigserial NOT NULL,
  month_id varchar(6) NOT NULL,
  city_name varchar(10) NOT NULL,
  user_count varchar(10) NOT NULL,
  cteate_time timestamp NULL DEFAULT now(),
  CONSTRAINT user_count_pkey PRIMARY KEY (id)
)

-- 目标万投比
drop table if exists report_auto.target_ts_ratio;
CREATE TABLE report_auto.target_ts_ratio (
	id bigserial NOT NULL,
  month_id varchar(6) NOT NULL,
  city_name varchar(10) NOT NULL,
  target_ts_ratio varchar(10) NOT NULL,
  cteate_time timestamp NULL DEFAULT now(),
  CONSTRAINT target_ts_ratio_pkey PRIMARY KEY (id)
)

-- 每月平均处理时长
drop table if exists report_auto.avg_duration;
CREATE TABLE report_auto.avg_duration (
	id bigserial NOT NULL,
	month_id varchar(6) NULL,
	city_name varchar(10) null,
	avg_duration float8 not null,
  cteate_time timestamp NULL DEFAULT now(),
	CONSTRAINT avg_duration_pkey PRIMARY KEY (id)
);