-- ============================================================================
-- 三类人员考核模块缺失表 DDL（4 张）
-- ============================================================================

-- 1. 考核指定表
CREATE TABLE slaj.bis_ttsa_design (
    guid VARCHAR(64) PRIMARY KEY,
    plan_guid VARCHAR(64),
    design_name VARCHAR(200),
    design_type VARCHAR(20),
    org_guid VARCHAR(64),
    design_stat VARCHAR(20),
    note TEXT,
    coll_time TIMESTAMP,
    upd_time TIMESTAMP,
    rec_pers VARCHAR(100)
);
COMMENT ON TABLE slaj.bis_ttsa_design IS '考核指定表（安全考核指派）';
COMMENT ON COLUMN slaj.bis_ttsa_design.guid IS '主键';
COMMENT ON COLUMN slaj.bis_ttsa_design.plan_guid IS '考核计划guid';
COMMENT ON COLUMN slaj.bis_ttsa_design.design_name IS '指定名称';
COMMENT ON COLUMN slaj.bis_ttsa_design.design_type IS '指定类型';
COMMENT ON COLUMN slaj.bis_ttsa_design.org_guid IS '单位guid';
COMMENT ON COLUMN slaj.bis_ttsa_design.design_stat IS '状态';
COMMENT ON COLUMN slaj.bis_ttsa_design.note IS '备注';
COMMENT ON COLUMN slaj.bis_ttsa_design.coll_time IS '采集时间';
COMMENT ON COLUMN slaj.bis_ttsa_design.upd_time IS '更新时间';
COMMENT ON COLUMN slaj.bis_ttsa_design.rec_pers IS '记录人员';

-- 2. 考核指定明细表
CREATE TABLE slaj.bis_ttsa_designdetail (
    guid VARCHAR(64) PRIMARY KEY,
    design_guid VARCHAR(64),
    pers_tts_guid VARCHAR(64),
    pers_name VARCHAR(100),
    pers_type VARCHAR(20),
    exam_type VARCHAR(20),
    note TEXT,
    coll_time TIMESTAMP,
    upd_time TIMESTAMP,
    rec_pers VARCHAR(100)
);
COMMENT ON TABLE slaj.bis_ttsa_designdetail IS '考核指定明细表';
COMMENT ON COLUMN slaj.bis_ttsa_designdetail.guid IS '主键';
COMMENT ON COLUMN slaj.bis_ttsa_designdetail.design_guid IS '指定guid';
COMMENT ON COLUMN slaj.bis_ttsa_designdetail.pers_tts_guid IS '人员guid';
COMMENT ON COLUMN slaj.bis_ttsa_designdetail.pers_name IS '人员姓名';
COMMENT ON COLUMN slaj.bis_ttsa_designdetail.pers_type IS '人员类型';
COMMENT ON COLUMN slaj.bis_ttsa_designdetail.exam_type IS '考核类型';
COMMENT ON COLUMN slaj.bis_ttsa_designdetail.note IS '备注';
COMMENT ON COLUMN slaj.bis_ttsa_designdetail.coll_time IS '采集时间';
COMMENT ON COLUMN slaj.bis_ttsa_designdetail.upd_time IS '更新时间';
COMMENT ON COLUMN slaj.bis_ttsa_designdetail.rec_pers IS '记录人员';

-- 3. 考核指定-考试关联表
CREATE TABLE slaj.bis_ttsa_design_exam_rel (
    guid VARCHAR(64) PRIMARY KEY,
    design_guid VARCHAR(64),
    exam_guid VARCHAR(64),
    paper_guid VARCHAR(64),
    note TEXT,
    coll_time TIMESTAMP,
    upd_time TIMESTAMP,
    rec_pers VARCHAR(100)
);
COMMENT ON TABLE slaj.bis_ttsa_design_exam_rel IS '考核指定-考试关联表';
COMMENT ON COLUMN slaj.bis_ttsa_design_exam_rel.guid IS '主键';
COMMENT ON COLUMN slaj.bis_ttsa_design_exam_rel.design_guid IS '指定guid';
COMMENT ON COLUMN slaj.bis_ttsa_design_exam_rel.exam_guid IS '考试guid';
COMMENT ON COLUMN slaj.bis_ttsa_design_exam_rel.paper_guid IS '试卷guid';
COMMENT ON COLUMN slaj.bis_ttsa_design_exam_rel.note IS '备注';
COMMENT ON COLUMN slaj.bis_ttsa_design_exam_rel.coll_time IS '采集时间';
COMMENT ON COLUMN slaj.bis_ttsa_design_exam_rel.upd_time IS '更新时间';
COMMENT ON COLUMN slaj.bis_ttsa_design_exam_rel.rec_pers IS '记录人员';

-- 4. 考核记录表
CREATE TABLE slaj.bis_ttsa_rec (
    guid VARCHAR(64) PRIMARY KEY,
    ttsa_guid VARCHAR(64),
    pers_tts_guid VARCHAR(64),
    exam_guid VARCHAR(64),
    ttsa_type VARCHAR(20),
    ttsa_score VARCHAR(20),
    ass_resu VARCHAR(20),
    rec_date TIMESTAMP,
    org_guid VARCHAR(64),
    note TEXT,
    coll_time TIMESTAMP,
    upd_time TIMESTAMP,
    rec_pers VARCHAR(100)
);
COMMENT ON TABLE slaj.bis_ttsa_rec IS '考核记录表（安全考核记录）';
COMMENT ON COLUMN slaj.bis_ttsa_rec.guid IS '主键';
COMMENT ON COLUMN slaj.bis_ttsa_rec.ttsa_guid IS '考核申请guid';
COMMENT ON COLUMN slaj.bis_ttsa_rec.pers_tts_guid IS '人员guid';
COMMENT ON COLUMN slaj.bis_ttsa_rec.exam_guid IS '考试guid';
COMMENT ON COLUMN slaj.bis_ttsa_rec.ttsa_type IS '考核类型';
COMMENT ON COLUMN slaj.bis_ttsa_rec.ttsa_score IS '考核成绩';
COMMENT ON COLUMN slaj.bis_ttsa_rec.ass_resu IS '评估结果';
COMMENT ON COLUMN slaj.bis_ttsa_rec.rec_date IS '记录日期';
COMMENT ON COLUMN slaj.bis_ttsa_rec.org_guid IS '单位guid';
COMMENT ON COLUMN slaj.bis_ttsa_rec.note IS '备注';
COMMENT ON COLUMN slaj.bis_ttsa_rec.coll_time IS '采集时间';
COMMENT ON COLUMN slaj.bis_ttsa_rec.upd_time IS '更新时间';
COMMENT ON COLUMN slaj.bis_ttsa_rec.rec_pers IS '记录人员';