-- 三人审核组表决：表、退回重填时间、三角色（一次性手工执行，勿做成启动 Runner）
-- 新环境部署时执行一次即可；已执行过的环境不要再挂启动脚本

CREATE TABLE IF NOT EXISTS t_alarm_audit_vote (
    id BIGSERIAL PRIMARY KEY,
    alarm_id BIGINT NOT NULL,
    auditor_id BIGINT,
    auditor_name VARCHAR(64),
    auditor_role VARCHAR(32) NOT NULL,
    result VARCHAR(32) NOT NULL,
    opinion VARCHAR(500),
    create_time TIMESTAMP,
    update_time TIMESTAMP
);

CREATE UNIQUE INDEX IF NOT EXISTS uk_alarm_audit_vote_alarm_role
    ON t_alarm_audit_vote(alarm_id, auditor_role);

ALTER TABLE t_alarm_report ADD COLUMN IF NOT EXISTS return_refill_time TIMESTAMP NULL;

-- 审核组三角色（已存在则跳过）
INSERT INTO t_role (role_name, role_code, remark, create_time, update_time)
SELECT '审核组-法制', 'alarm_audit_legal', '警情定性审核-法制席位', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM t_role WHERE role_code = 'alarm_audit_legal');

INSERT INTO t_role (role_name, role_code, remark, create_time, update_time)
SELECT '审核组-公职律师', 'alarm_audit_lawyer', '警情定性审核-公职律师席位', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM t_role WHERE role_code = 'alarm_audit_lawyer');

INSERT INTO t_role (role_name, role_code, remark, create_time, update_time)
SELECT '审核组-经侦', 'alarm_audit_jz', '警情定性审核-经侦席位', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM t_role WHERE role_code = 'alarm_audit_jz');

-- 将「审核监督」菜单及其子权限绑到三角色 + JZ_ADMIN（按菜单名匹配，已绑定则跳过）
INSERT INTO t_role_menu (role_id, menu_id, create_time, update_time)
SELECT r.role_id, m.menu_id, NOW(), NOW()
FROM t_role r
CROSS JOIN t_menu m
WHERE r.role_code IN ('alarm_audit_legal', 'alarm_audit_lawyer', 'alarm_audit_jz', 'JZ_ADMIN')
  AND m.deleted_flag = 0
  AND (
    m.menu_name = '审核监督'
    OR m.parent_id IN (SELECT menu_id FROM t_menu WHERE menu_name = '审核监督' AND deleted_flag = 0)
  )
  AND NOT EXISTS (
    SELECT 1 FROM t_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = m.menu_id
  );