-- ============================================================================
-- 三类人员考核模块 (ttsa) 假数据 — 27 表
-- 列名对齐数据库实际结构
-- ============================================================================

-- 1. 三类人员信息
INSERT INTO slaj.obj_tts (guid, pers_guid, org_proj_dept_guid, cit_num, pers_name, born_plac, pers_type, org_guid, sex, born_date, high_edu, tech_tit, schl_name, major, grau_date, work_date, post_date, bemo_date, work_resum, arch_eng_sm, post_duty, ttsa_guid, note, coll_time, upd_time, rec_pers) VALUES
('tts_001', 'pers_001', 'org_001', '110101199001011234', '张三', '北京市', '1', 'org_001', '1', '1985-03-15 00:00:00', '3', '高级工程师', '清华大学', '水利工程', '2008-07-01 00:00:00', '2008-08-01 00:00:00', '2020-01-01 00:00:00', '2020-01-01 00:00:00', '多年水利工程安全管理经验', '青山水库除险加固', '项目负责人', NULL, NULL, '2025-01-01 00:00:00', NULL, '管理员'),
('tts_002', 'pers_002', 'org_001', '320101198508082345', '李四', '上海市', '2', 'org_001', '1', '1988-06-20 00:00:00', '2', '工程师', '河海大学', '水文水资源', '2011-06-30 00:00:00', '2011-08-01 00:00:00', '2018-03-01 00:00:00', '2018-03-01 00:00:00', '水利工程运行管理', '碧湖水库管理', '安全管理员', NULL, NULL, '2025-01-01 00:00:00', NULL, '管理员'),
('tts_003', 'pers_003', 'org_002', '440101199203204567', '王五', '广州市', '3', 'org_002', '1', '1992-09-10 00:00:00', '2', '助理工程师', '武汉大学', '土木工程', '2015-06-30 00:00:00', '2015-07-15 00:00:00', '2022-05-01 00:00:00', '2022-05-01 00:00:00', '施工现场安全管理', '龙潭水库加固', '企业负责人', NULL, NULL, '2025-01-01 00:00:00', NULL, '管理员'),
('tts_004', NULL, 'org_001', '500101197805056789', '赵六', '重庆市', '2', 'org_001', '0', '1978-11-20 00:00:00', '3', '教授级高工', '天津大学', '机电工程', '2002-07-01 00:00:00', '2002-08-01 00:00:00', '2015-01-01 00:00:00', '2015-01-01 00:00:00', '机电设备安全管理专家', NULL, '技术负责人', NULL, NULL, '2025-01-01 00:00:00', NULL, '管理员');

-- 2. 安全考核申请对象
INSERT INTO slaj.obj_ttsa (guid, pers_tts_guid, appl_stat, veri_stat, veri_opin, appr_stat, appr_opin, note, coll_time, upd_time, rec_pers, veri_pers, veri_time, appr_pers, appr_time, puno_guid, exam_stat, exam_appl_guid, appl_time) VALUES
('ttsa_001', 'tts_001', '1', '1', '材料齐全同意申请', '1', '批准参加考核', NULL, '2025-02-01 00:00:00', NULL, '管理员', '王建国', '2025-02-05 00:00:00', '周伟', '2025-02-08 00:00:00', NULL, '1', NULL, '2025-02-01 00:00:00'),
('ttsa_002', 'tts_002', '1', '1', '审核通过', '1', '同意', NULL, '2025-02-10 00:00:00', NULL, '管理员', '陈志远', '2025-02-12 00:00:00', '周伟', '2025-02-15 00:00:00', NULL, '0', NULL, '2025-02-10 00:00:00'),
('ttsa_003', 'tts_003', '0', '0', NULL, '0', NULL, '待提交材料', '2025-03-01 00:00:00', NULL, '管理员', NULL, NULL, NULL, NULL, NULL, '0', NULL, '2025-03-01 00:00:00');

-- 3. 批量考核申请
INSERT INTO slaj.obj_ttsa_exam_appl (guid, appl_org_guid, appl_time, appl_type, appl_exam_type, note, coll_time, upd_time, rec_pers) VALUES
('ea_001', 'org_001', '2025-03-01 00:00:00', '1', '1', '2025年第一季度三类人员考核', '2025-03-01 00:00:00', NULL, '管理员'),
('ea_002', 'org_002', '2025-03-15 00:00:00', '2', '2', '河湖管理处专项考核', '2025-03-15 00:00:00', NULL, '管理员'),
('ea_003', 'org_001', '2025-06-01 00:00:00', '1', '1', '2025年第二季度考核', '2025-06-01 00:00:00', NULL, '管理员');

-- 4. 三类人员证书
INSERT INTO slaj.bis_ttsa_cert (guid, p_guid, array_guid, cert_code, pers_tts_guid, ttsa_guid, ttsa_cert_type, issue_time, issue_org_guid, cert_expi_time, grant_inst, cert_stat, note, coll_time, upd_time, rec_pers) VALUES
('tcert_001', NULL, 'arr_001', 'TTSA-2025-0001', 'tts_001', 'ttsa_001', '1', '2025-03-01 00:00:00', 'org_001', '2028-03-01 00:00:00', '1', '1', NULL, '2025-03-01 00:00:00', NULL, '管理员'),
('tcert_002', NULL, 'arr_001', 'TTSA-2025-0002', 'tts_002', 'ttsa_002', '2', '2025-03-15 00:00:00', 'org_001', '2028-03-15 00:00:00', '1', '1', NULL, '2025-03-15 00:00:00', NULL, '管理员'),
('tcert_003', NULL, 'arr_002', 'TTSA-2024-0058', 'tts_004', NULL, '1', '2024-06-01 00:00:00', 'org_002', '2027-06-01 00:00:00', '2', '2', '即将到期', '2024-06-01 00:00:00', NULL, '管理员');

-- 5. 证书阵列
INSERT INTO slaj.bis_ttsa_cert_array (guid, array_name, ttsa_cert_type, ttsa_cert_num, grant_inst, issue_time, note, coll_time, upd_time, rec_pers, puno_guid) VALUES
('arr_001', '2025年第一批三类人员证书',  '1', 50, '1', '2025-03-01 00:00:00', NULL, '2025-03-01 00:00:00', NULL, '管理员', NULL),
('arr_002', '2024年第二批三类人员证书',  '1', 30, '2', '2024-06-01 00:00:00', NULL, '2024-06-01 00:00:00', NULL, '管理员', NULL),
('arr_003', '2025年专项考核证书',        '2', 20, '1', '2025-04-01 00:00:00', NULL, '2025-04-01 00:00:00', NULL, '管理员', NULL);

-- 6-14. 证书变更申请表 + 各类变更（各3条）
INSERT INTO slaj.bis_ttsa_cert_cha_appl (guid, ttsa_cert_guid, appl_org_guid, cert_code, chan_type, apll_time, veri_time, veri_pers, veri_if_agree, wiun_opin, appr_time, appr_pers, appr_if_agree, appr_wiun_opin, note, coll_time, upd_time, rec_pers) VALUES
('cha_001', 'tcert_003', 'org_001', 'TTSA-2024-0058', '1', '2025-04-01 00:00:00', '2025-04-10 00:00:00', '王建国', '1', '同意变更', NULL, NULL, NULL, NULL, NULL, '2025-04-01 00:00:00', NULL, '管理员'),
('cha_002', 'tcert_001', 'org_001', 'TTSA-2025-0001', '2', '2025-05-01 00:00:00', '2025-05-10 00:00:00', '陈志远', '1', '同意', '2025-05-15 00:00:00', '周伟', '1', '批准', NULL, '2025-05-01 00:00:00', NULL, '管理员'),
('cha_003', 'tcert_002', 'org_002', 'TTSA-2025-0002', '3', '2025-06-01 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2025-06-01 00:00:00', NULL, '管理员');

INSERT INTO slaj.bis_ttsa_cert_cha_comp (guid, cha_appl_guid, befor_wiun_guid, befor_cert_code, befor_start_time, befor_end_time, note, coll_time, upd_time, rec_pers) VALUES
('ccomp_001', 'cha_001', 'org_002', 'TTSA-2024-0058', '2024-06-01 00:00:00', '2025-03-31 00:00:00', NULL, '2025-04-01 00:00:00', NULL, '管理员');

INSERT INTO slaj.bis_ttsa_cert_cha_lost (guid, cha_appl_guid, befor_wiun_guid, befor_wiun_name, befor_cert_type, befor_cert_code, befor_start_time, befor_end_time, note, coll_time, upd_time, rec_pers) VALUES
('clost_001', 'cha_002', 'org_001', '水利工程管理局', '1', 'TTSA-2025-0001', '2025-03-01 00:00:00', '2028-03-01 00:00:00', '证书遗失声明已登报', '2025-05-01 00:00:00', NULL, '管理员');

INSERT INTO slaj.bis_ttsa_cert_cha_name (guid, cha_appl_guid, befor_cha_name, after_cha_name, note, coll_time, upd_time, rec_pers) VALUES
('cname_001', 'cha_003', '李四', '李思', '户口本姓名变更', '2025-06-01 00:00:00', NULL, '管理员');

INSERT INTO slaj.bis_ttsa_cert_cha_pers (guid, cha_appl_guid, cha_field, cha_expl, befor_cha_cont, after_cha_cont, note, coll_time, upd_time, rec_pers) VALUES
('cpers_001', 'cha_001', '职称', '高级工程师→教授级高工', '高级工程师', '教授级高工', NULL, '2025-04-01 00:00:00', NULL, '管理员');

INSERT INTO slaj.bis_ttsa_cert_cha_tran (guid, cha_appl_guid, befor_wiun_guid, after_wiun_guid, note, coll_time, upd_time, rec_pers) VALUES
('ctran_001', 'cha_001', 'org_002', 'org_001', '从河湖管理处调至水利工程管理局', '2025-04-01 00:00:00', NULL, '管理员');

INSERT INTO slaj.bis_ttsa_cert_reis_appl (guid, appl_wiun_code, cert_code, issue_time, apll_time, veri_time, veri_pers, veri_if_agree, wiun_opin, note, coll_time, upd_time, rec_pers) VALUES
('reis_001', 'SLJ-001', 'TTSA-2025-0001', '2025-03-01 00:00:00', '2025-05-10 00:00:00', '2025-05-15 00:00:00', '王建国', '1', '同意补发', NULL, '2025-05-10 00:00:00', NULL, '管理员'),
('reis_002', 'SLJ-002', 'TTSA-2024-0058', '2024-06-01 00:00:00', '2025-04-01 00:00:00', NULL, NULL, NULL, NULL, NULL, '2025-04-01 00:00:00', NULL, '管理员');

INSERT INTO slaj.bis_ttsa_cert_rev_appl (guid, ttsa_cert_guid, appl_org_guid, cert_code, appl_time, rev_reas, veri_time, veri_pers, veri_if_agree, wiun_opin, note, coll_time, upd_time, rec_pers) VALUES
('rev_001', 'tcert_003', 'org_001', 'TTSA-2024-0058', '2025-07-01 00:00:00', '证书到期不再续期', NULL, NULL, NULL, NULL, NULL, '2025-07-01 00:00:00', NULL, '管理员');

INSERT INTO slaj.bis_ttsa_ceri_ext_apll (guid, ttsa_cert_guid, appl_org_guid, cert_code, hold_pers, issu_time, ext_situ, apll_time, veri_time, veri_pers, veri_if_agree, wiun_opin, appr_time, appr_pers, appr_if_agree, appr_wiun_opin, note, coll_time, upd_time, rec_pers) VALUES
('ext_001', 'tcert_003', 'org_001', 'TTSA-2024-0058', '赵六', '2024-06-01 00:00:00', '1', '2025-04-15 00:00:00', '2025-04-20 00:00:00', '陈志远', '1', '同意延期', NULL, NULL, NULL, NULL, NULL, '2025-04-15 00:00:00', NULL, '管理员');

-- 15. 考核计划
INSERT INTO slaj.bis_ttsa_exam_plan (guid, plan_name, esta_org_guid, esta_time, plan_cont, appr_stat, appr_org_guid, appr_opin, rep_stat, note, coll_time, upd_time, rec_pers, appl_time, appr_time) VALUES
('plan_001', '2025年第一季度三类人员考核计划', 'org_001', '2025-01-15 00:00:00', 'Q1考核涵盖企业负责人和项目负责人', '1', 'org_001', '同意执行', '1', NULL, '2025-01-15 00:00:00', NULL, '管理员', '2025-01-10 00:00:00', '2025-01-15 00:00:00'),
('plan_002', '2025年汛前专项考核计划', 'org_002', '2025-03-01 00:00:00', '汛前安全管理人员专项考核', '1', 'org_001', '同意', '0', NULL, '2025-03-01 00:00:00', NULL, '管理员', '2025-02-20 00:00:00', '2025-03-01 00:00:00'),
('plan_003', '2025年度三类人员继续教育考核', 'org_001', '2025-06-01 00:00:00', '年度继续教育考核', '0', NULL, NULL, '0', '待审批', '2025-06-01 00:00:00', NULL, '管理员', '2025-05-20 00:00:00', NULL);

-- 16. 试卷
INSERT INTO slaj.bis_ttsa_paper (guid, paper_code, paper_grad, paper_name, paper_full_mark, paper_qual_score, paper_apply, paper_stat, exam_crea_type, design_guid, note, coll_time, upd_time, rec_pers) VALUES
('paper_001', 'PAPER-2025-001', '1', '企业负责人安全考核试卷A',  100, 60, '企业负责人', '1', '1', NULL, NULL, '2025-01-20 00:00:00', NULL, '管理员'),
('paper_002', 'PAPER-2025-002', '2', '项目负责人安全考核试卷B',  100, 60, '项目负责人', '1', '1', NULL, NULL, '2025-02-01 00:00:00', NULL, '管理员'),
('paper_003', 'PAPER-2025-003', '3', '安全管理人员考核试卷C',    100, 70, '安全管理人员','0', '2', NULL, '待启用', '2025-03-01 00:00:00', NULL, '管理员');

-- 17. 试题
INSERT INTO slaj.bis_ttsa_tque (guid, tque_type, que_type, tque_grad, tque_titl, tque_answ, appl_type, note, coll_time, upd_time, rec_pers) VALUES
('tque_001', '1', '单选', '1', '安全生产法规定企业负责人对本单位安全生产工作负什么责任？', 'A', '1', NULL, '2025-01-10 00:00:00', NULL, '管理员'),
('tque_002', '1', '单选', '2', '水利工程安全管理中"三类人员"不包括以下哪类？', 'C', '2', NULL, '2025-01-10 00:00:00', NULL, '管理员'),
('tque_003', '2', '多选', '2', '以下哪些是项目负责人的安全职责？', 'A,B,D', '1', NULL, '2025-01-15 00:00:00', NULL, '管理员'),
('tque_004', '3', '判断', '1', '所有水利工程都必须进行安全考核。', 'B', '1', NULL, '2025-01-15 00:00:00', NULL, '管理员');

-- 18. 试题选项
INSERT INTO slaj.bis_ttsa_tque_opt (guid, tque_guid, opt_flag, tque_opt, note, coll_time, upd_time, rec_pers) VALUES
('topt_001', 'tque_001', 'A', '全面责任', NULL, '2025-01-10 00:00:00', NULL, '管理员'),
('topt_002', 'tque_001', 'B', '部分责任', NULL, '2025-01-10 00:00:00', NULL, '管理员'),
('topt_003', 'tque_001', 'C', '连带责任', NULL, '2025-01-10 00:00:00', NULL, '管理员'),
('topt_004', 'tque_001', 'D', '无限责任', NULL, '2025-01-10 00:00:00', NULL, '管理员'),
('topt_005', 'tque_002', 'A', '企业负责人', NULL, '2025-01-10 00:00:00', NULL, '管理员'),
('topt_006', 'tque_002', 'B', '项目负责人', NULL, '2025-01-10 00:00:00', NULL, '管理员'),
('topt_007', 'tque_002', 'C', '监理工程师', NULL, '2025-01-10 00:00:00', NULL, '管理员'),
('topt_008', 'tque_002', 'D', '安全管理人员', NULL, '2025-01-10 00:00:00', NULL, '管理员');

-- 19. 考试成绩
INSERT INTO slaj.bis_ttsa_score (guid, pers_tts_guid, admi_tick, ttsa_exam_appl_guid, ttsa_exam_guid, ttsa_type, ttsa_score, ass_resu, veri_org_guid, veri_time, veri_stat, veri_opin, appr_org_guid, appr_time, appr_stat, appr_opin, note, coll_time, upd_time, rec_pers, exam_time) VALUES
('score_001', 'tts_001', 'ADM-001', 'ea_001', NULL, '1', 88, '1', 'org_001', '2025-03-15 00:00:00', '1', '成绩合格', 'org_001', '2025-03-20 00:00:00', '1', '同意发证', NULL, '2025-03-15 00:00:00', NULL, '管理员', '2025-03-10 00:00:00'),
('score_002', 'tts_002', 'ADM-002', 'ea_001', NULL, '1', 72, '1', 'org_001', '2025-03-15 00:00:00', '1', '成绩合格', NULL, NULL, '0', NULL, NULL, '2025-03-15 00:00:00', NULL, '管理员', '2025-03-10 00:00:00'),
('score_003', 'tts_003', 'ADM-003', 'ea_002', NULL, '2', 55, '0', 'org_002', '2025-04-01 00:00:00', '1', '成绩不合格需补考', NULL, NULL, NULL, NULL, NULL, '2025-04-01 00:00:00', NULL, '管理员', '2025-03-25 00:00:00');

-- 20. 考核记录
INSERT INTO slaj.bis_ttsa_rec (guid, ttsa_guid, pers_tts_guid, exam_guid, ttsa_type, ttsa_score, ass_resu, rec_date, org_guid, note, coll_time, upd_time, rec_pers) VALUES
('rec_001', 'ttsa_001', 'tts_001', NULL, '1', '88', '1', '2025-03-10 00:00:00', 'org_001', '初考通过', '2025-03-10 00:00:00', NULL, '管理员'),
('rec_002', 'ttsa_002', 'tts_002', NULL, '1', '72', '1', '2025-03-10 00:00:00', 'org_001', '初考通过', '2025-03-10 00:00:00', NULL, '管理员'),
('rec_003', 'ttsa_003', 'tts_003', NULL, '2', '55', '0', '2025-03-25 00:00:00', 'org_002', '第一次补考未通过', '2025-03-25 00:00:00', NULL, '管理员');

-- 21. 考核培训
INSERT INTO slaj.bis_ttsa_train (guid, cit_num, org_guid, train_org_name, train_type, train_start_time, train_comp_time, train_teat, train_addr, train_org_guid, train_pers_tot, train_hour, veri_stat, note, coll_time, upd_time, rec_pers) VALUES
('train_001', 'TRAIN-2025-001', 'org_001', '水利安全生产培训中心', '1', '2025-02-20 09:00:00', '2025-02-22 17:00:00', '王教授', '局机关三楼会议室', 'org_001', 30, '24', '1', NULL, '2025-02-20 09:00:00', NULL, '管理员'),
('train_002', 'TRAIN-2025-002', 'org_002', '省级水利培训基地', '2', '2025-03-10 09:00:00', '2025-03-12 17:00:00', '李教授', '河湖管理处培训中心', 'org_002', 20, '18', '1', NULL, '2025-03-10 09:00:00', NULL, '管理员'),
('train_003', 'TRAIN-2025-003', 'org_001', '在线学习平台', '3', '2025-04-01 08:00:00', '2025-04-30 18:00:00', NULL, '在线', 'org_001', 100, '40', '0', '线上培训', '2025-04-01 08:00:00', NULL, '管理员');

-- 22. 培训人员
INSERT INTO slaj.bis_ttsa_train_pers (guid, ttsa_cert_guid, pers_name, pers_type, ttsa_train_guid, note, coll_time, upd_time, rec_pers, train_resu) VALUES
('tp_001', 'tcert_001', '张三', '1', 'train_001', NULL, '2025-02-20 00:00:00', NULL, '管理员', '1'),
('tp_002', 'tcert_002', '李四', '2', 'train_001', NULL, '2025-02-20 00:00:00', NULL, '管理员', '1'),
('tp_003', NULL, '王五', '3', 'train_002', NULL, '2025-03-10 00:00:00', NULL, '管理员', '0'),
('tp_004', 'tcert_001', '赵六', '2', 'train_003', NULL, '2025-04-01 00:00:00', NULL, '管理员', '0');

-- 23. 考核指定
INSERT INTO slaj.bis_ttsa_design (guid, plan_guid, design_name, design_type, org_guid, design_stat, note, coll_time, upd_time, rec_pers) VALUES
('des_001', 'plan_001', 'Q1企业负责人考核指派',   '1', 'org_001', '1', NULL, '2025-01-20 00:00:00', NULL, '管理员'),
('des_002', 'plan_001', 'Q1项目负责人考核指派',   '2', 'org_001', '1', NULL, '2025-01-20 00:00:00', NULL, '管理员'),
('des_003', 'plan_002', '汛前安全管理人员考核指派','3', 'org_002', '0', NULL, '2025-03-05 00:00:00', NULL, '管理员');

-- 24. 指定明细
INSERT INTO slaj.bis_ttsa_designdetail (guid, design_guid, pers_tts_guid, pers_name, pers_type, exam_type, note, coll_time, upd_time, rec_pers) VALUES
('dd_001', 'des_001', 'tts_001', '张三', '1', '1', NULL, '2025-01-20 00:00:00', NULL, '管理员'),
('dd_002', 'des_002', 'tts_002', '李四', '2', '1', NULL, '2025-01-20 00:00:00', NULL, '管理员'),
('dd_003', 'des_003', 'tts_003', '王五', '3', '2', NULL, '2025-03-05 00:00:00', NULL, '管理员'),
('dd_004', 'des_001', 'tts_004', '赵六', '2', '1', NULL, '2025-01-20 00:00:00', NULL, '管理员');

-- 25. 指定考试关联
INSERT INTO slaj.bis_ttsa_design_exam_rel (guid, design_guid, exam_guid, paper_guid, note, coll_time, upd_time, rec_pers) VALUES
('der_001', 'des_001', NULL, 'paper_001', NULL, '2025-01-20 00:00:00', NULL, '管理员'),
('der_002', 'des_002', NULL, 'paper_002', NULL, '2025-01-20 00:00:00', NULL, '管理员'),
('der_003', 'des_003', NULL, 'paper_003', NULL, '2025-03-05 00:00:00', NULL, '管理员');

-- 26. 考核企业
INSERT INTO slaj.bis_ttsa_exam_ente (guid, plan_guid, ente_wium_guid, ente_time, note, coll_time, upd_time, rec_pers) VALUES
('ente_001', 'plan_001', 'org_001', '2025-01-20 00:00:00', NULL, '2025-01-20 00:00:00', NULL, '管理员'),
('ente_002', 'plan_001', 'org_002', '2025-01-20 00:00:00', NULL, '2025-01-20 00:00:00', NULL, '管理员'),
('ente_003', 'plan_002', 'org_002', '2025-03-05 00:00:00', NULL, '2025-03-05 00:00:00', NULL, '管理员');

-- 27. 申请安全元素
INSERT INTO slaj.bis_ttsa_appl_wiun (guid, appl_org_guid, org_account, conta_pers_tel, post_code, conta_pers_email, appl_type, appl_situ_stat, veri_org_guid, veri_time, veri_opin, note, coll_time, upd_time, rec_pers, org_source) VALUES
('aw_001', 'org_001', '62284800123456789', '13800001111', '100001', 'org1@test.com', '1', '1', 'org_001', '2025-02-05 00:00:00', '审核通过', NULL, '2025-02-01 00:00:00', NULL, '管理员', 'SLJ'),
('aw_002', 'org_002', '62284800987654321', '13800002222', '200001', 'org2@test.com', '2', '0', NULL, NULL, NULL, NULL, '2025-03-01 00:00:00', NULL, '管理员', 'SLJ'),
('aw_003', 'org_001', '62284800555444333', '13800003333', '100001', 'org1b@test.com','1', '1', 'org_001', '2025-06-05 00:00:00', '审核通过', NULL, '2025-06-01 00:00:00', NULL, '管理员', 'WATER');
