-- ============================================================
-- Ether 权限体系升级脚本 V3
-- 创建日期: 2026-02-27
-- 说明: 升级权限系统，新增7个角色、更新1个角色、新增39个按钮级权限
-- ============================================================

BEGIN;

-- ============================================================
-- 第零部分：扩展约束（添加新的枚举值支持）
-- ============================================================

-- 0.1 扩展 data_scope 约束，添加 PROJECT 值
ALTER TABLE auth_role DROP CONSTRAINT IF EXISTS auth_role_data_scope_check;
ALTER TABLE auth_role ADD CONSTRAINT auth_role_data_scope_check 
    CHECK (data_scope::text = ANY (ARRAY['ALL'::character varying, 'PROJECT'::character varying, 'DEPARTMENT'::character varying, 'SELF'::character varying]::text[]));

-- 0.2 扩展 action 约束，添加新的操作类型
ALTER TABLE auth_permission DROP CONSTRAINT IF EXISTS auth_permission_action_check;
ALTER TABLE auth_permission ADD CONSTRAINT auth_permission_action_check 
    CHECK (action::text = ANY (ARRAY[
        'VIEW'::character varying, 'CREATE'::character varying, 'EDIT'::character varying, 
        'DELETE'::character varying, 'EXPORT'::character varying, 'IMPORT_DATA'::character varying, 
        'APPROVE'::character varying, 'ASSIGN'::character varying,
        'ACCEPT'::character varying, 'START'::character varying, 'COMPLETE'::character varying,
        'TRANSFER'::character varying, 'CLOSE'::character varying, 'REPORT_FEE'::character varying,
        'AUDIT_FEE'::character varying, 'AUDIT_QUALITY'::character varying, 'SCAN'::character varying,
        'REPORT'::character varying, 'PLAN'::character varying, 'FORCE_CLOSE'::character varying,
        'MAINTAIN'::character varying, 'REGISTER'::character varying, 'VERIFY'::character varying,
        'RELEASE'::character varying, 'ADJUST'::character varying, 'COLLECT'::character varying,
        'PAY'::character varying, 'AUDIT'::character varying, 'REDUCE'::character varying
    ]::text[]));

-- ============================================================
-- 第一部分：角色升级
-- ============================================================

-- 1.1 新增系统角色
-- 使用固定UUID便于回滚和引用
INSERT INTO auth_role (id, project_id, role_code, role_name, description, role_type, data_scope, enabled, sort_order, created_at, updated_at)
SELECT 'd0000000-0000-0000-0000-000000000001', NULL, 'SYS_ADMIN', '系统管理员', '系统级管理，负责系统配置和运维', 'SYSTEM', 'ALL', true, 2, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_role WHERE role_code = 'SYS_ADMIN' AND project_id IS NULL);

INSERT INTO auth_role (id, project_id, role_code, role_name, description, role_type, data_scope, enabled, sort_order, created_at, updated_at)
SELECT 'd0000000-0000-0000-0000-000000000002', NULL, 'ENGINEERING_LEAD', '工程主管', '工程部管理，负责设备维护和工单调度', 'SYSTEM', 'DEPARTMENT', true, 11, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_role WHERE role_code = 'ENGINEERING_LEAD' AND project_id IS NULL);

INSERT INTO auth_role (id, project_id, role_code, role_name, description, role_type, data_scope, enabled, sort_order, created_at, updated_at)
SELECT 'd0000000-0000-0000-0000-000000000003', NULL, 'SECURITY_LEAD', '安保主管', '安保部管理，负责安保巡检和访客管理', 'SYSTEM', 'DEPARTMENT', true, 12, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_role WHERE role_code = 'SECURITY_LEAD' AND project_id IS NULL);

INSERT INTO auth_role (id, project_id, role_code, role_name, description, role_type, data_scope, enabled, sort_order, created_at, updated_at)
SELECT 'd0000000-0000-0000-0000-000000000004', NULL, 'CLEANING_LEAD', '保洁主管', '保洁部管理，负责保洁任务分配和品质检查', 'SYSTEM', 'DEPARTMENT', true, 13, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_role WHERE role_code = 'CLEANING_LEAD' AND project_id IS NULL);

INSERT INTO auth_role (id, project_id, role_code, role_name, description, role_type, data_scope, enabled, sort_order, created_at, updated_at)
SELECT 'd0000000-0000-0000-0000-000000000005', NULL, 'FINANCE_LEAD', '财务主管', '财务部管理，负责收费和账单管理', 'SYSTEM', 'DEPARTMENT', true, 14, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_role WHERE role_code = 'FINANCE_LEAD' AND project_id IS NULL);

INSERT INTO auth_role (id, project_id, role_code, role_name, description, role_type, data_scope, enabled, sort_order, created_at, updated_at)
SELECT 'd0000000-0000-0000-0000-000000000006', NULL, 'CLEANING_STAFF', '保洁人员', '保洁执行、品质检查', 'SYSTEM', 'SELF', true, 23, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_role WHERE role_code = 'CLEANING_STAFF' AND project_id IS NULL);

INSERT INTO auth_role (id, project_id, role_code, role_name, description, role_type, data_scope, enabled, sort_order, created_at, updated_at)
SELECT 'd0000000-0000-0000-0000-000000000007', NULL, 'OWNER', '业主', '业主用户，可查看个人账单和报修', 'SYSTEM', 'SELF', true, 30, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_role WHERE role_code = 'OWNER' AND project_id IS NULL);

-- 1.2 更新现有角色：CUSTOMER_SERVICE -> CS_STAFF
-- 更新角色编码和数据范围
UPDATE auth_role 
SET role_code = 'CS_STAFF',
    role_name = '客服人员',
    data_scope = 'PROJECT',
    description = '业主服务、访客核验',
    updated_at = NOW()
WHERE role_code = 'CUSTOMER_SERVICE' AND project_id IS NULL;

-- ============================================================
-- 第二部分：按钮级权限新增
-- ============================================================

-- 2.1 工单操作权限（12个）
INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0001-000000000001', 'ops:work_order:view', '查看工单', 'ops', 'work_order', 'VIEW', 'BUTTON', NULL, 1, true, '查看工单详情', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:work_order:view');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0001-000000000002', 'ops:work_order:create', '创建工单', 'ops', 'work_order', 'CREATE', 'BUTTON', NULL, 2, true, '创建新工单', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:work_order:create');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0001-000000000003', 'ops:work_order:assign', '分配工单', 'ops', 'work_order', 'ASSIGN', 'BUTTON', NULL, 3, true, '分配工单给处理人', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:work_order:assign');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0001-000000000004', 'ops:work_order:accept', '接单', 'ops', 'work_order', 'ACCEPT', 'BUTTON', NULL, 4, true, '接受工单任务', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:work_order:accept');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0001-000000000005', 'ops:work_order:start', '开始处理', 'ops', 'work_order', 'START', 'BUTTON', NULL, 5, true, '开始处理工单', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:work_order:start');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0001-000000000006', 'ops:work_order:complete', '完成工单', 'ops', 'work_order', 'COMPLETE', 'BUTTON', NULL, 6, true, '标记工单完成', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:work_order:complete');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0001-000000000007', 'ops:work_order:transfer', '转单', 'ops', 'work_order', 'TRANSFER', 'BUTTON', NULL, 7, true, '转派工单给他人', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:work_order:transfer');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0001-000000000008', 'ops:work_order:close', '关闭工单', 'ops', 'work_order', 'CLOSE', 'BUTTON', NULL, 8, true, '关闭工单', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:work_order:close');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0001-000000000009', 'ops:work_order:report_fee', '填报费用', 'ops', 'work_order', 'REPORT_FEE', 'BUTTON', NULL, 9, true, '填报工单费用', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:work_order:report_fee');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0001-000000000010', 'ops:work_order:audit_fee', '费用审核', 'ops', 'work_order', 'AUDIT_FEE', 'BUTTON', NULL, 10, true, '审核工单费用', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:work_order:audit_fee');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0001-000000000011', 'ops:work_order:audit_quality', '质量审核', 'ops', 'work_order', 'AUDIT_QUALITY', 'BUTTON', NULL, 11, true, '审核工单质量', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:work_order:audit_quality');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0001-000000000012', 'ops:work_order:delete', '删除工单', 'ops', 'work_order', 'DELETE', 'BUTTON', NULL, 12, true, '删除工单', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:work_order:delete');

-- 2.2 巡检操作权限（8个）
INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0002-000000000001', 'ops:inspection:view', '查看巡检', 'ops', 'inspection', 'VIEW', 'BUTTON', NULL, 1, true, '查看巡检记录', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:inspection:view');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0002-000000000002', 'ops:inspection:start', '开始巡检', 'ops', 'inspection', 'START', 'BUTTON', NULL, 2, true, '开始巡检任务', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:inspection:start');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0002-000000000003', 'ops:inspection:scan', '扫码签到', 'ops', 'inspection', 'SCAN', 'BUTTON', NULL, 3, true, '扫码签到巡检点', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:inspection:scan');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0002-000000000004', 'ops:inspection:report', '异常上报', 'ops', 'inspection', 'REPORT', 'BUTTON', NULL, 4, true, '上报巡检异常', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:inspection:report');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0002-000000000005', 'ops:inspection:complete', '完成巡检', 'ops', 'inspection', 'COMPLETE', 'BUTTON', NULL, 5, true, '完成巡检任务', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:inspection:complete');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0002-000000000006', 'ops:inspection:plan', '制定计划', 'ops', 'inspection', 'PLAN', 'BUTTON', NULL, 6, true, '制定巡检计划', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:inspection:plan');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0002-000000000007', 'ops:inspection:assign', '指派任务', 'ops', 'inspection', 'ASSIGN', 'BUTTON', NULL, 7, true, '指派巡检任务', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:inspection:assign');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0002-000000000008', 'ops:inspection:force_close', '强制闭环', 'ops', 'inspection', 'FORCE_CLOSE', 'BUTTON', NULL, 8, true, '强制关闭巡检异常', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:inspection:force_close');

-- 2.3 设备操作权限（6个）
INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0003-000000000001', 'mdm:equipment:view', '查看设备', 'mdm', 'equipment', 'VIEW', 'BUTTON', NULL, 1, true, '查看设备详情', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'mdm:equipment:view');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0003-000000000002', 'mdm:equipment:scan', '扫码巡检', 'mdm', 'equipment', 'SCAN', 'BUTTON', NULL, 2, true, '扫码查看设备信息', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'mdm:equipment:scan');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0003-000000000003', 'mdm:equipment:maintain', '维护记录', 'mdm', 'equipment', 'MAINTAIN', 'BUTTON', NULL, 3, true, '记录设备维护', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'mdm:equipment:maintain');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0003-000000000004', 'mdm:equipment:edit', '编辑设备', 'mdm', 'equipment', 'EDIT', 'BUTTON', NULL, 4, true, '编辑设备信息', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'mdm:equipment:edit');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0003-000000000005', 'mdm:equipment:create', '新增设备', 'mdm', 'equipment', 'CREATE', 'BUTTON', NULL, 5, true, '新增设备', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'mdm:equipment:create');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0003-000000000006', 'mdm:equipment:delete', '删除设备', 'mdm', 'equipment', 'DELETE', 'BUTTON', NULL, 6, true, '删除设备', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'mdm:equipment:delete');

-- 2.4 访客操作权限（5个）
INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0004-000000000001', 'ops:visitor:view', '查看访客', 'ops', 'visitor', 'VIEW', 'BUTTON', NULL, 1, true, '查看访客记录', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:visitor:view');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0004-000000000002', 'ops:visitor:register', '访客登记', 'ops', 'visitor', 'REGISTER', 'BUTTON', NULL, 2, true, '登记访客信息', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:visitor:register');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0004-000000000003', 'ops:visitor:verify', '访客核验', 'ops', 'visitor', 'VERIFY', 'BUTTON', NULL, 3, true, '核验访客身份', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:visitor:verify');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0004-000000000004', 'ops:visitor:release', '访客放行', 'ops', 'visitor', 'RELEASE', 'BUTTON', NULL, 4, true, '放行访客', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:visitor:release');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0004-000000000005', 'ops:visitor:export', '导出记录', 'ops', 'visitor', 'EXPORT', 'BUTTON', NULL, 5, true, '导出访客记录', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'ops:visitor:export');

-- 2.5 财务操作权限（8个）
INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0005-000000000001', 'finance:bill:view', '查看账单', 'finance', 'bill', 'VIEW', 'BUTTON', NULL, 1, true, '查看账单详情', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'finance:bill:view');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0005-000000000002', 'finance:bill:create', '生成账单', 'finance', 'bill', 'CREATE', 'BUTTON', NULL, 2, true, '生成新账单', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'finance:bill:create');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0005-000000000003', 'finance:bill:adjust', '调整账单', 'finance', 'bill', 'ADJUST', 'BUTTON', NULL, 3, true, '调整账单金额', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'finance:bill:adjust');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0005-000000000004', 'finance:bill:collect', '收费登记', 'finance', 'bill', 'COLLECT', 'BUTTON', NULL, 4, true, '登记收费记录', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'finance:bill:collect');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0005-000000000005', 'finance:bill:pay', '在线缴费', 'finance', 'bill', 'PAY', 'BUTTON', NULL, 5, true, '在线支付缴费', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'finance:bill:pay');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0005-000000000006', 'finance:bill:audit', '收费审核', 'finance', 'bill', 'AUDIT', 'BUTTON', NULL, 6, true, '审核收费记录', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'finance:bill:audit');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0005-000000000007', 'finance:bill:reduce', '减免审批', 'finance', 'bill', 'REDUCE', 'BUTTON', NULL, 7, true, '审批费用减免', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'finance:bill:reduce');

INSERT INTO auth_permission (id, permission_code, permission_name, module, resource_type, action, permission_type, parent_id, sort_order, enabled, description, created_at, updated_at)
SELECT 'e0000000-0000-0000-0005-000000000008', 'finance:bill:export', '导出报表', 'finance', 'bill', 'EXPORT', 'BUTTON', NULL, 8, true, '导出财务报表', NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM auth_permission WHERE permission_code = 'finance:bill:export');

-- ============================================================
-- 第三部分：为超级管理员分配所有新权限
-- ============================================================

-- 3.1 为 SUPER_ADMIN 分配所有新增按钮权限
INSERT INTO auth_role_permission (id, role_id, permission_id, created_at)
SELECT gen_random_uuid(), r.id, p.id, NOW()
FROM auth_role r
CROSS JOIN auth_permission p
WHERE r.role_code = 'SUPER_ADMIN' 
AND r.project_id IS NULL
AND p.permission_code IN (
    -- 工单权限
    'ops:work_order:view', 'ops:work_order:create', 'ops:work_order:assign',
    'ops:work_order:accept', 'ops:work_order:start', 'ops:work_order:complete',
    'ops:work_order:transfer', 'ops:work_order:close', 'ops:work_order:report_fee',
    'ops:work_order:audit_fee', 'ops:work_order:audit_quality', 'ops:work_order:delete',
    -- 巡检权限
    'ops:inspection:view', 'ops:inspection:start', 'ops:inspection:scan',
    'ops:inspection:report', 'ops:inspection:complete', 'ops:inspection:plan',
    'ops:inspection:assign', 'ops:inspection:force_close',
    -- 设备权限
    'mdm:equipment:view', 'mdm:equipment:scan', 'mdm:equipment:maintain',
    'mdm:equipment:edit', 'mdm:equipment:create', 'mdm:equipment:delete',
    -- 访客权限
    'ops:visitor:view', 'ops:visitor:register', 'ops:visitor:verify',
    'ops:visitor:release', 'ops:visitor:export',
    -- 财务权限
    'finance:bill:view', 'finance:bill:create', 'finance:bill:adjust',
    'finance:bill:collect', 'finance:bill:pay', 'finance:bill:audit',
    'finance:bill:reduce', 'finance:bill:export'
)
AND NOT EXISTS (
    SELECT 1 FROM auth_role_permission rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
);

-- ============================================================
-- 第四部分：验证数据
-- ============================================================

-- 验证新增角色
DO $$
DECLARE
    role_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO role_count FROM auth_role 
    WHERE role_code IN ('SYS_ADMIN', 'ENGINEERING_LEAD', 'SECURITY_LEAD', 'CLEANING_LEAD', 'FINANCE_LEAD', 'CLEANING_STAFF', 'OWNER')
    AND project_id IS NULL;
    
    IF role_count < 7 THEN
        RAISE NOTICE '警告: 新增角色数量不足，预期7个，实际%', role_count;
    ELSE
        RAISE NOTICE '成功: 新增7个角色';
    END IF;
END $$;

-- 验证更新角色
DO $$
DECLARE
    cs_staff_exists INTEGER;
BEGIN
    SELECT COUNT(*) INTO cs_staff_exists FROM auth_role 
    WHERE role_code = 'CS_STAFF' AND project_id IS NULL;
    
    IF cs_staff_exists = 0 THEN
        RAISE NOTICE '注意: CS_STAFF角色不存在（CUSTOMER_SERVICE角色原本可能不存在）';
    ELSE
        RAISE NOTICE '成功: CUSTOMER_SERVICE已更新为CS_STAFF';
    END IF;
END $$;

-- 验证新增权限
DO $$
DECLARE
    permission_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO permission_count FROM auth_permission 
    WHERE permission_code LIKE 'ops:work_order:%'
       OR permission_code LIKE 'ops:inspection:%'
       OR permission_code LIKE 'mdm:equipment:%'
       OR permission_code LIKE 'ops:visitor:%'
       OR permission_code LIKE 'finance:bill:%';
    
    IF permission_count < 39 THEN
        RAISE NOTICE '警告: 新增权限数量不足，预期39个，实际%', permission_count;
    ELSE
        RAISE NOTICE '成功: 新增39个按钮级权限';
    END IF;
END $$;

COMMIT;

-- ============================================================
-- 执行完成提示
-- ============================================================
-- 执行完成后请运行以下命令验证:
-- SELECT role_code, role_name, data_scope FROM auth_role WHERE project_id IS NULL ORDER BY sort_order;
-- SELECT permission_code, permission_name, module FROM auth_permission WHERE permission_type = 'BUTTON' ORDER BY module, permission_code;