CREATE TABLE REC_LOAN_LAST.loan_after_risk_task(
id VARCHAR(30) NOT NULL,
code VARCHAR(30) DEFAULT NULL,
generate_time DATETIME DEFAULT NULL,
loan_after_risk_task_rule_id VARCHAR(30) DEFAULT NULL,
state VARCHAR(20) DEFAULT NULL,
hit_people_number INT DEFAULT NULL,
not_hit_people_number INT DEFAULT NULL,
create_time DATETIME DEFAULT NULL,
create_date DATE DEFAULT NULL,
update_time DATETIME DEFAULT NULL,
update_date DATE DEFAULT NULL,
bank_code VARCHAR(100) DEFAULT NULL,
create_user VARCHAR(8) DEFAULT NULL,
create_org VARCHAR(8) DEFAULT NULL,
update_user VARCHAR(8) DEFAULT NULL,
update_org VARCHAR(8) DEFAULT NULL,
cur_vers INT DEFAULT 1,
PRIMARY KEY(id)) ;

COMMENT ON TABLE REC_LOAN_LAST.loan_after_risk_task IS '贷后管理风险检查任务表';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.bank_code IS '银行编码';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.code IS '任务编号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.create_date IS '登记日期';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.create_org IS '登记机构编号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.create_time IS '登记时间戳';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.create_user IS '登记用户编号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.cur_vers IS '版本号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.generate_time IS '任务生成时间';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.hit_people_number IS '命中人数';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.id IS '主键';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.loan_after_risk_task_rule_id IS '任务规则id';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.not_hit_people_number IS '未命中人数';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.state IS '任务状态 处理中(PROCESSING) 处理完成(PROCESSED)';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.update_date IS '更新日期';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.update_org IS '更新机构编号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.update_time IS '更新时间戳';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.update_user IS '更新员工编号';



CREATE TABLE REC_LOAN_LAST.loan_after_risk_task_result(
id VARCHAR(30) NOT NULL,
cust_no VARCHAR(1024) DEFAULT NULL,
hit_state VARCHAR(20) DEFAULT NULL,
loan_after_risk_task_id VARCHAR(30) DEFAULT NULL,
hit_target VARCHAR(500) DEFAULT NULL,
create_time DATETIME DEFAULT NULL,
create_date DATE DEFAULT NULL,
update_time DATETIME DEFAULT NULL,
update_date DATE DEFAULT NULL,
bank_code VARCHAR(100) DEFAULT NULL,
create_user VARCHAR(8) DEFAULT NULL,
create_org VARCHAR(8) DEFAULT NULL,
update_user VARCHAR(8) DEFAULT NULL,
update_org VARCHAR(8) DEFAULT NULL,
cur_vers INT DEFAULT 1,
PRIMARY KEY(id)) ;

COMMENT ON TABLE REC_LOAN_LAST.loan_after_risk_task_result IS '贷后管理风险检查任务结果表';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.bank_code IS '银行编码';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.create_date IS '登记日期';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.create_org IS '登记机构编号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.create_time IS '登记时间戳';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.create_user IS '登记用户编号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.cur_vers IS '版本号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.cust_no IS '客户编号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.hit_state IS '是否命中 命中(HIT) 未命中(MISS)';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.hit_target IS '命中指标 json数组格式';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.id IS '主键';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.loan_after_risk_task_id IS '任务id';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.update_date IS '更新日期';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.update_org IS '更新机构编号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.update_time IS '更新时间戳';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_result.update_user IS '更新员工编号';



CREATE TABLE REC_LOAN_LAST.loan_after_risk_task_rule(
id VARCHAR(30) NOT NULL,
rule_code VARCHAR(10) DEFAULT NULL,
product_id BIGINT DEFAULT NULL,
people_number INT DEFAULT NULL,
business_time_start DATETIME DEFAULT NULL,
business_time_end DATETIME DEFAULT NULL,
screen_rule VARCHAR(500) DEFAULT NULL,
check_rule VARCHAR(50) DEFAULT NULL,
execution_mode VARCHAR(20) DEFAULT NULL,
execution_period VARCHAR(20) DEFAULT NULL,
start_date DATETIME DEFAULT NULL,
execution_time DATETIME DEFAULT NULL,
remark VARCHAR(1000) DEFAULT NULL,
state VARCHAR(20) DEFAULT NULL,
execution_state VARCHAR(20) DEFAULT NULL,
create_time DATETIME DEFAULT NULL,
create_date DATE DEFAULT NULL,
update_time DATETIME DEFAULT NULL,
update_date DATE DEFAULT NULL,
bank_code VARCHAR(100) DEFAULT NULL,
create_user VARCHAR(8) DEFAULT NULL,
create_org VARCHAR(8) DEFAULT NULL,
update_user VARCHAR(8) DEFAULT NULL,
update_org VARCHAR(8) DEFAULT NULL,
cur_vers INT DEFAULT 1,
PRIMARY KEY(id));

COMMENT ON TABLE REC_LOAN_LAST.loan_after_risk_task_rule IS '贷后管理风险检查任务规则表';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.bank_code IS '银行编码';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.business_time_end IS '业务日期结束';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.business_time_start IS '业务日期开始';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.check_rule IS '检查规则';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.create_date IS '登记日期';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.create_org IS '登记机构编号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.create_time IS '登记时间戳';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.create_user IS '登记用户编号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.cur_vers IS '版本号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.execution_mode IS '执行方式 单次(ONCE) 循环(LOOP)';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.execution_period IS '周期 周(WEEK) 月(MONTH) 季(QUARTER)';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.execution_state IS '执行状态 执行中(EXECUTING) 未执行(UNEXECUTED) 执行完成(EXECUTED)';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.execution_time IS '最近一次执行时间';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.id IS '主键';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.people_number IS '人数';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.product_id IS '产品id';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.remark IS '备注';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.rule_code IS '规则编号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.screen_rule IS '筛选规则 json数组格式';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.start_date IS '开始时间';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.state IS '启用状态 启用(ENABLE) 停用(DISABLE)';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.update_date IS '更新日期';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.update_org IS '更新机构编号';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.update_time IS '更新时间戳';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task_rule.update_user IS '更新员工编号';



ALTER TABLE REC_LOAN_LAST."loan_after_risk_task" ADD screen_rule VARCHAR(500) DEFAULT NULL ;
ALTER TABLE REC_LOAN_LAST."loan_after_risk_task" ADD check_rule VARCHAR(50) DEFAULT NULL;
ALTER TABLE REC_LOAN_LAST."loan_after_risk_task" ADD people_number INT DEFAULT NULL;

COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.people_number IS '人数';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.screen_rule IS '筛选规则 json数组格式';
COMMENT ON COLUMN REC_LOAN_LAST.loan_after_risk_task.check_rule IS '检查规则';