-- 新租户初始化清单 SQL（按模板租户复制）
-- 用法：
-- 1) 先创建租户拿到 @target_tenant_id
-- 2) 以 tenant_id=1 作为模板租户执行
-- 3) 该脚本尽量幂等（重复执行不会重复插入）

SET @template_tenant_id := 1;
SET @target_tenant_id := 2; -- TODO: 改成目标租户ID

-- =====================================================
-- A. 必须初始化（权限与菜单链路）
-- 表：role/menu/role_permission/role_menu
-- =====================================================

-- A1) 复制模板租户角色（role_code 维度）
SET @next_role_id := (SELECT IFNULL(MAX(id), 0) FROM role);

INSERT INTO role (id, tenant_id, role_code, role_name, status, is_deleted, created_by, updated_by)
SELECT
    (@next_role_id := @next_role_id + 1) AS id,
    @target_tenant_id AS tenant_id,
    tr.role_code,
    tr.role_name,
    tr.status,
    0 AS is_deleted,
    0 AS created_by,
    0 AS updated_by
FROM role tr
LEFT JOIN role er
    ON er.tenant_id=@target_tenant_id
   AND er.role_code=tr.role_code
   AND er.is_deleted=0
WHERE tr.tenant_id=@template_tenant_id
  AND tr.is_deleted=0
  AND er.id IS NULL;

-- A2) 复制模板租户菜单
INSERT INTO menu (tenant_id, menu_code, menu_name, route_path, permission_code, sort_no, status, is_deleted, created_by, updated_by)
SELECT
    @target_tenant_id AS tenant_id,
    tm.menu_code,
    tm.menu_name,
    tm.route_path,
    tm.permission_code,
    tm.sort_no,
    tm.status,
    0 AS is_deleted,
    0 AS created_by,
    0 AS updated_by
FROM menu tm
LEFT JOIN menu em
    ON em.tenant_id=@target_tenant_id
   AND em.menu_code=tm.menu_code
   AND em.is_deleted=0
WHERE tm.tenant_id=@template_tenant_id
  AND tm.is_deleted=0
  AND em.id IS NULL;

-- A3) 复制 role_permission（按 role_code 映射）
SET @next_role_perm_id := (SELECT IFNULL(MAX(id), 0) FROM role_permission);

INSERT INTO role_permission (id, tenant_id, role_id, permission_id)
SELECT
    (@next_role_perm_id := @next_role_perm_id + 1) AS id,
    @target_tenant_id AS tenant_id,
    target_role.id AS role_id,
    rp.permission_id
FROM role_permission rp
JOIN role template_role
    ON template_role.tenant_id=rp.tenant_id
   AND template_role.id=rp.role_id
   AND template_role.is_deleted=0
JOIN role target_role
    ON target_role.tenant_id=@target_tenant_id
   AND target_role.role_code=template_role.role_code
   AND target_role.is_deleted=0
LEFT JOIN role_permission existing_rp
    ON existing_rp.tenant_id=@target_tenant_id
   AND existing_rp.role_id=target_role.id
   AND existing_rp.permission_id=rp.permission_id
WHERE rp.tenant_id=@template_tenant_id
  AND existing_rp.id IS NULL;

-- A4) 复制 role_menu（按 role_code + menu_code 映射）
SET @next_role_menu_id := (SELECT IFNULL(MAX(id), 0) FROM role_menu);

INSERT INTO role_menu (id, tenant_id, role_id, menu_id)
SELECT
    (@next_role_menu_id := @next_role_menu_id + 1) AS id,
    @target_tenant_id AS tenant_id,
    target_role.id AS role_id,
    target_menu.id AS menu_id
FROM role_menu rm
JOIN role template_role
    ON template_role.tenant_id=rm.tenant_id
   AND template_role.id=rm.role_id
   AND template_role.is_deleted=0
JOIN menu template_menu
    ON template_menu.tenant_id=rm.tenant_id
   AND template_menu.id=rm.menu_id
   AND template_menu.is_deleted=0
JOIN role target_role
    ON target_role.tenant_id=@target_tenant_id
   AND target_role.role_code=template_role.role_code
   AND target_role.is_deleted=0
JOIN menu target_menu
    ON target_menu.tenant_id=@target_tenant_id
   AND target_menu.menu_code=template_menu.menu_code
   AND target_menu.is_deleted=0
LEFT JOIN role_menu existing_rm
    ON existing_rm.tenant_id=@target_tenant_id
   AND existing_rm.role_id=target_role.id
   AND existing_rm.menu_id=target_menu.id
WHERE rm.tenant_id=@template_tenant_id
  AND existing_rm.id IS NULL;

-- =====================================================
-- B. 可选初始化（建议）
-- =====================================================

-- B1) 模板类型开关（template_type_option）
-- 注意：type_code 是主键。若库里是“全局唯一”，目标租户可能已存在同 code。
-- 这里按 (type_code, tenant_id) 语义做幂等，重复时仅更新展示字段。
INSERT INTO template_type_option (type_code, tenant_id, type_name, status, sort_no)
SELECT
    tto.type_code,
    @target_tenant_id AS tenant_id,
    tto.type_name,
    tto.status,
    tto.sort_no
FROM template_type_option tto
WHERE tto.tenant_id=@template_tenant_id
ON DUPLICATE KEY UPDATE
    type_name=VALUES(type_name),
    status=VALUES(status),
    sort_no=VALUES(sort_no),
    updated_at=CURRENT_TIMESTAMP;

-- B2) 会议字段字典（meeting_field）
INSERT INTO meeting_field (tenant_id, field_code, field_name, field_values, scope_type, project_id, sort_no, status, is_deleted, created_by, updated_by)
SELECT
    @target_tenant_id AS tenant_id,
    mf.field_code,
    mf.field_name,
    mf.field_values,
    mf.scope_type,
    NULL AS project_id,
    mf.sort_no,
    mf.status,
    0 AS is_deleted,
    0 AS created_by,
    0 AS updated_by
FROM meeting_field mf
LEFT JOIN meeting_field emf
    ON emf.tenant_id=@target_tenant_id
   AND emf.field_code=mf.field_code
   AND emf.is_deleted=0
WHERE mf.tenant_id=@template_tenant_id
  AND mf.is_deleted=0
  AND emf.id IS NULL;

-- =====================================================
-- C. 不建议初始化（运行数据表，仅业务发生时产生）
-- project/meeting/audit*/finance*/notification_task/export_task/operation_audit_log 等
-- =====================================================