--项目表新增审批状态字段
alter table zjk_product
    add approval_status integer;

comment on column zjk_product.approval_status is '审批状态（0：待审批，1：已审批，2：被驳回）';
--将旧数据审批状态改为已审批
update zjk_product set approval_status = 1;
--项目表新增审批原因字段
alter table zjk_product
    add approval_reason varchar(2000);

comment on column zjk_product.approval_reason is '审批原因';
--专家确认截至时间
alter table zjk_review_phase
    add notice_time timestamp;

comment on column zjk_review_phase.notice_time is '专家确认截至时间';
--专家抽取短信模板修改
update zjk_sms_template set tml_content='尊敬的{{expertName}}老师您好，您被{{productName}}抽取参加{{reviewTime}}于{{reviewPlace}}举行的的{{reviewSn}}评审会，请您于{{noticeTime}}前登录专家库管理集约化平台确认，超过该时间邀请将失效。您也可以回复短信确认，回复数字{{confirmCode}}接受邀请，回复数字{{refuseCode}}拒绝邀请。' where id=1

--短信通知模板新增
INSERT INTO "public"."zjk_sms_template" ("ID", "tml_type", "tml_content", "start_flag", "create_dept", "tenant_id", "CREATE_BY", "CREATE_TIME", "UPDATE_BY", "UPDATE_TIME", "title") VALUES (3, '3', '{{productName}}{{reviewSn}}阶段中您抽取的{{num}}名评审专家已全部接受评审邀请！', 1, NULL, '674758', NULL, NULL, NULL, NULL, '【云时代】');
INSERT INTO "public"."zjk_sms_template" ("ID", "tml_type", "tml_content", "start_flag", "create_dept", "tenant_id", "CREATE_BY", "CREATE_TIME", "UPDATE_BY", "UPDATE_TIME", "title") VALUES (4, '4', '{{productName}}{{reviewSn}}中您抽取的{{num}}名评审专家有{{noAcceptNum}}名专家未响应邀请，有{{rejectNum}}名专家拒绝参加评审，有{{acceptNum}}名专家接受评审邀请，请及时补抽！', 1, NULL, '674758', NULL, NULL, NULL, NULL, '【云时代】');

--学习园地新增表
DROP TABLE IF EXISTS zjk_learning_evaluate;
CREATE TABLE zjk_learning_evaluate(
                                      ID VARCHAR(32) NOT NULL,
                                      course_id int8,
                                      diff_level INTEGER,
                                      get_level INTEGER,
                                      finish_time TIMESTAMP(255),
                                      remark VARCHAR(255),
                                      create_dept VARCHAR(255),
                                      tenant_id VARCHAR(8),
                                      CREATE_BY int8,
                                      CREATE_TIME TIMESTAMP,
                                      UPDATE_BY int8,
                                      UPDATE_TIME TIMESTAMP,
                                      PRIMARY KEY (ID)
);

COMMENT ON TABLE zjk_learning_evaluate IS '学习评估';
COMMENT ON COLUMN zjk_learning_evaluate.ID IS '租户号';
COMMENT ON COLUMN zjk_learning_evaluate.course_id IS '课程id;zjk_learning_course主键';
COMMENT ON COLUMN zjk_learning_evaluate.diff_level IS '难易程度';
COMMENT ON COLUMN zjk_learning_evaluate.get_level IS '掌握程度';
COMMENT ON COLUMN zjk_learning_evaluate.finish_time IS '完成时间';
COMMENT ON COLUMN zjk_learning_evaluate.remark IS '学习心得';
COMMENT ON COLUMN zjk_learning_evaluate.create_dept IS '创建部门';
COMMENT ON COLUMN zjk_learning_evaluate.tenant_id IS '租户id';
COMMENT ON COLUMN zjk_learning_evaluate.CREATE_BY IS '创建人';
COMMENT ON COLUMN zjk_learning_evaluate.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN zjk_learning_evaluate.UPDATE_BY IS '更新人';
COMMENT ON COLUMN zjk_learning_evaluate.UPDATE_TIME IS '更新时间';

DROP TABLE IF EXISTS zjk_learning_suggest;
CREATE TABLE zjk_learning_suggest(
                                     ID VARCHAR(32) NOT NULL,
                                     suggest_name VARCHAR(255),
                                     suggest_content VARCHAR(255),
                                     file_id int8,
                                     create_dept VARCHAR(255),
                                     tenant_id VARCHAR(8),
                                     CREATE_BY int8,
                                     CREATE_TIME TIMESTAMP,
                                     UPDATE_BY int8,
                                     UPDATE_TIME TIMESTAMP,
                                     PRIMARY KEY (ID)
);

COMMENT ON TABLE zjk_learning_suggest IS '专家推荐';
COMMENT ON COLUMN zjk_learning_suggest.ID IS '租户号';
COMMENT ON COLUMN zjk_learning_suggest.suggest_name IS '推荐名称;zjk_learning_course主键';
COMMENT ON COLUMN zjk_learning_suggest.suggest_content IS '推荐内容';
COMMENT ON COLUMN zjk_learning_suggest.file_id IS '关联附件id';
COMMENT ON COLUMN zjk_learning_suggest.create_dept IS '创建部门';
COMMENT ON COLUMN zjk_learning_suggest.tenant_id IS '租户id';
COMMENT ON COLUMN zjk_learning_suggest.CREATE_BY IS '创建人';
COMMENT ON COLUMN zjk_learning_suggest.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN zjk_learning_suggest.UPDATE_BY IS '更新人';
COMMENT ON COLUMN zjk_learning_suggest.UPDATE_TIME IS '更新时间';

DROP TABLE IF EXISTS zjk_learning_back;
CREATE TABLE zjk_learning_back(
                                  ID VARCHAR(32) NOT NULL,
                                  back_name VARCHAR(255),
                                  back_content VARCHAR(255),
                                  create_dept VARCHAR(255),
                                  tenant_id VARCHAR(8),
                                  CREATE_BY int8,
                                  CREATE_TIME TIMESTAMP,
                                  UPDATE_BY int8,
                                  UPDATE_TIME TIMESTAMP,
                                  PRIMARY KEY (ID)
);

COMMENT ON TABLE zjk_learning_back IS '专家学习反馈';
COMMENT ON COLUMN zjk_learning_back.ID IS '租户号';
COMMENT ON COLUMN zjk_learning_back.back_name IS '反馈名称;zjk_learning_course主键';
COMMENT ON COLUMN zjk_learning_back.back_content IS '反馈内容';
COMMENT ON COLUMN zjk_learning_back.create_dept IS '创建部门';
COMMENT ON COLUMN zjk_learning_back.tenant_id IS '租户id';
COMMENT ON COLUMN zjk_learning_back.CREATE_BY IS '创建人';
COMMENT ON COLUMN zjk_learning_back.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN zjk_learning_back.UPDATE_BY IS '更新人';
COMMENT ON COLUMN zjk_learning_back.UPDATE_TIME IS '更新时间';

DROP TABLE IF EXISTS zjk_learning_plan;
CREATE TABLE zjk_learning_plan(
                                  ID VARCHAR(32) NOT NULL,
                                  plan_name VARCHAR(255),
                                  start_time TIMESTAMP,
                                  end_time TIMESTAMP,
                                  study_target VARCHAR(255),
                                  create_dept VARCHAR(255),
                                  tenant_id VARCHAR(8),
                                  CREATE_BY int8,
                                  CREATE_TIME TIMESTAMP,
                                  UPDATE_BY int8,
                                  UPDATE_TIME TIMESTAMP,
                                  PRIMARY KEY (ID)
);

COMMENT ON TABLE zjk_learning_plan IS '专家学习计划';
COMMENT ON COLUMN zjk_learning_plan.ID IS '租户号';
COMMENT ON COLUMN zjk_learning_plan.plan_name IS '计划名称;zjk_learning_course主键';
COMMENT ON COLUMN zjk_learning_plan.start_time IS '计划开始时间';
COMMENT ON COLUMN zjk_learning_plan.end_time IS '计划结束时间';
COMMENT ON COLUMN zjk_learning_plan.study_target IS '学习目标';
COMMENT ON COLUMN zjk_learning_plan.create_dept IS '创建部门';
COMMENT ON COLUMN zjk_learning_plan.tenant_id IS '租户id';
COMMENT ON COLUMN zjk_learning_plan.CREATE_BY IS '创建人';
COMMENT ON COLUMN zjk_learning_plan.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN zjk_learning_plan.UPDATE_BY IS '更新人';
COMMENT ON COLUMN zjk_learning_plan.UPDATE_TIME IS '更新时间';

DROP TABLE IF EXISTS zjk_learning_plan_course;
CREATE TABLE zjk_learning_plan_course(
                                         ID SERIAL NOT NULL,
                                         plan_id VARCHAR(32),
                                         course_id INTEGER,
                                         create_dept VARCHAR(255),
                                         tenant_id VARCHAR(8),
                                         CREATE_BY int8,
                                         CREATE_TIME TIMESTAMP,
                                         UPDATE_BY int8,
                                         UPDATE_TIME TIMESTAMP,
                                         PRIMARY KEY (ID)
);

COMMENT ON TABLE zjk_learning_plan_course IS '学习计划课程关系';
COMMENT ON COLUMN zjk_learning_plan_course.ID IS '主键';
COMMENT ON COLUMN zjk_learning_plan_course.plan_id IS '计划id';
COMMENT ON COLUMN zjk_learning_plan_course.course_id IS '课程id';
COMMENT ON COLUMN zjk_learning_plan_course.create_dept IS '创建部门';
COMMENT ON COLUMN zjk_learning_plan_course.tenant_id IS '租户id';
COMMENT ON COLUMN zjk_learning_plan_course.CREATE_BY IS '创建人';
COMMENT ON COLUMN zjk_learning_plan_course.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN zjk_learning_plan_course.UPDATE_BY IS '更新人';
COMMENT ON COLUMN zjk_learning_plan_course.UPDATE_TIME IS '更新时间';

--发票表新增项目相关字段
alter table zjk_invoice
    add review_sn int2;

comment on column zjk_invoice.review_sn is '项目阶段';

alter table zjk_invoice
    add product_id int8;

comment on column zjk_invoice.product_id is '项目id';

alter table zjk_invoice
    add settle_id int8;

comment on column zjk_invoice.settle_id is '结算单id';
alter table zjk_invoice
    add check_status int2;

comment on column zjk_invoice.check_status is '审核状态（0：未审核；1：已审核）';
alter table zjk_invoice
    alter column check_status set default 0;

--发票报表
DROP TABLE IF EXISTS zjk_invoice_report;
CREATE TABLE zjk_invoice_report(
                                   ID VARCHAR(32) NOT NULL,
                                   report_name VARCHAR(255),
                                   report_code VARCHAR(255),
                                   report_type INTEGER,
                                   date_range_start TIMESTAMP,
                                   date_range_end TIMESTAMP,
                                   create_dept VARCHAR(255),
                                   tenant_id VARCHAR(8),
                                   CREATE_BY int8,
                                   CREATE_TIME TIMESTAMP,
                                   UPDATE_BY int8,
                                   UPDATE_TIME TIMESTAMP,
                                   PRIMARY KEY (ID)
);

COMMENT ON TABLE zjk_invoice_report IS '发票报表';
COMMENT ON COLUMN zjk_invoice_report.ID IS '租户号';
COMMENT ON COLUMN zjk_invoice_report.report_name IS '报表名称';
COMMENT ON COLUMN zjk_invoice_report.report_code IS '报表编号';
COMMENT ON COLUMN zjk_invoice_report.report_type IS '报表类型;（1：年报表；0：月报表）';
COMMENT ON COLUMN zjk_invoice_report.date_range_start IS '时间范围开始';
COMMENT ON COLUMN zjk_invoice_report.date_range_end IS '时间范围结束';
COMMENT ON COLUMN zjk_invoice_report.create_dept IS '创建部门';
COMMENT ON COLUMN zjk_invoice_report.tenant_id IS '租户id';
COMMENT ON COLUMN zjk_invoice_report.CREATE_BY IS '创建人';
COMMENT ON COLUMN zjk_invoice_report.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN zjk_invoice_report.UPDATE_BY IS '更新人';
COMMENT ON COLUMN zjk_invoice_report.UPDATE_TIME IS '更新时间';

--专家库收支对账
DROP TABLE IF EXISTS zjk_inout_check;
CREATE TABLE zjk_inout_check(
                                ID VARCHAR(32),
                                check_no VARCHAR(255),
                                check_range_start TIMESTAMP,
                                check_range_end TIMESTAMP,
                                file_id int8,
                                create_dept VARCHAR(255),
                                tenant_id VARCHAR(8),
                                CREATE_BY int8,
                                CREATE_TIME TIMESTAMP,
                                UPDATE_BY int8,
                                UPDATE_TIME TIMESTAMP
);

COMMENT ON TABLE zjk_inout_check IS '专家库收支对账';
COMMENT ON COLUMN zjk_inout_check.ID IS '租户号';
COMMENT ON COLUMN zjk_inout_check.check_no IS '对账批次';
COMMENT ON COLUMN zjk_inout_check.check_range_start IS '时间范围开始';
COMMENT ON COLUMN zjk_inout_check.check_range_end IS '时间范围结束';
COMMENT ON COLUMN zjk_inout_check.file_id IS '文件id';
COMMENT ON COLUMN zjk_inout_check.create_dept IS '创建部门';
COMMENT ON COLUMN zjk_inout_check.tenant_id IS '租户id';
COMMENT ON COLUMN zjk_inout_check.CREATE_BY IS '创建人';
COMMENT ON COLUMN zjk_inout_check.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN zjk_inout_check.UPDATE_BY IS '更新人';
COMMENT ON COLUMN zjk_inout_check.UPDATE_TIME IS '更新时间';

--新增结算凭证表
DROP TABLE IF EXISTS zjk_settlement_voucher;
CREATE TABLE zjk_settlement_voucher(
                                       ID VARCHAR(32) NOT NULL,
                                       voucher_code VARCHAR(255),
                                       product_id int8,
                                       expert_id int8,
                                       money NUMERIC(10,2),
                                       settle_no VARCHAR(255),
                                       sync_status int2,
                                       create_dept VARCHAR(255),
                                       tenant_id VARCHAR(8),
                                       CREATE_BY int8,
                                       CREATE_TIME TIMESTAMP,
                                       UPDATE_BY int8,
                                       UPDATE_TIME TIMESTAMP,
                                       PRIMARY KEY (ID)
);

COMMENT ON TABLE zjk_settlement_voucher IS '结算凭证';
COMMENT ON COLUMN zjk_settlement_voucher.ID IS '租户号';
COMMENT ON COLUMN zjk_settlement_voucher.voucher_code IS '凭证号';
COMMENT ON COLUMN zjk_settlement_voucher.product_id IS '项目id';
COMMENT ON COLUMN zjk_settlement_voucher.expert_id IS '专家id';
COMMENT ON COLUMN zjk_settlement_voucher.money IS '金额';
COMMENT ON COLUMN zjk_settlement_voucher.settle_no IS '结算单号';
COMMENT ON COLUMN zjk_settlement_voucher.sync_status IS '同步状态;0：未同步；1：已同步';
COMMENT ON COLUMN zjk_settlement_voucher.create_dept IS '创建部门';
COMMENT ON COLUMN zjk_settlement_voucher.tenant_id IS '租户id';
COMMENT ON COLUMN zjk_settlement_voucher.CREATE_BY IS '创建人';
COMMENT ON COLUMN zjk_settlement_voucher.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN zjk_settlement_voucher.UPDATE_BY IS '更新人';
COMMENT ON COLUMN zjk_settlement_voucher.UPDATE_TIME IS '更新时间';

--系统用户表新增银汉信息
alter table sys_user
    add bank_name varchar;

comment on column sys_user.bank_name is '开户银行';

alter table sys_user
    add bank_code varchar;

comment on column sys_user.bank_code is '银行卡号';

alter table sys_user
    add bank_addr varchar;

comment on column sys_user.bank_addr is '开户网点';





