-- OA 已办中心财务备注（Gitea #7），MySQL 5.7，先于应用代码部署。
-- 在目标业务库执行；结构与菜单插入均可重复执行，不自动授予任何角色权限。
-- 权限配置：在已办中心下勾选「财务备注（查看和修改）」；同一权限码作用于两端。
-- 菜单按当前租户中既有 OA 已办页面定位，不挂载到旧工作流已办中心。

SET @oa_finance_column_exists = (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'oa_instance'
      AND column_name = 'finance_remark'
);
SET @oa_finance_ddl = IF(
    @oa_finance_column_exists = 0,
    'ALTER TABLE oa_instance ADD COLUMN finance_remark VARCHAR(512) NULL COMMENT ''已办财务备注（专用权限控制）'' AFTER status',
    'SELECT ''oa_instance.finance_remark already exists'' AS migration_status'
);
PREPARE oa_finance_stmt FROM @oa_finance_ddl;
EXECUTE oa_finance_stmt;
DEALLOCATE PREPARE oa_finance_stmt;

-- 由租户及父菜单生成稳定的 UUID 形状 ID，避免多行 SELECT 中 UUID 表达式被复用。
-- 已有软删/停用权限节点不自动复活，由管理员决定是否恢复。
INSERT INTO sys_menu (
    id, menu_name, parent_id, menu_type, path, component, perms,
    is_frame, visible, status, tenant_no, order_num, remark,
    create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT
    INSERT(INSERT(MD5(CONCAT('oa-finance-remark:', parent.tenant_no, ':', parent.id)),
                  13, 1, '3'), 17, 1, '8'),
    '财务备注（查看和修改）', parent.id, 'F', '', NULL,
    'oa:done:finance-remark', 1, '1', '1', parent.tenant_no, 90,
    '控制本人已办审批单的共享财务备注；Web 与移动端共用权限',
    'migration', '', NOW(), 'migration', NOW(), 0
FROM sys_menu AS parent
WHERE parent.del_flag = 0 AND parent.status = '1' AND parent.menu_type = 'C'
  AND (
      parent.perms IN ('oa:done', 'mini:oa:done')
      OR TRIM(BOTH '/' FROM parent.component) = 'oa-approval/done/index'
      OR TRIM(BOTH '/' FROM parent.path) IN ('pages/oa/done/index', 'oa/done/index')
  )
  AND NOT EXISTS (
      SELECT 1 FROM sys_menu AS existing
      WHERE existing.parent_id = parent.id
        AND existing.tenant_no = parent.tenant_no
        AND existing.perms = 'oa:done:finance-remark'
  );

-- 验证必须至少存在当前租户的权限节点；若无结果，先核对该租户 OA 已办菜单配置。
SELECT tenant_no, parent_id, id, menu_name, perms, status
FROM sys_menu WHERE perms = 'oa:done:finance-remark' AND del_flag = 0;