USE [YL_MESDB];
GO

SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @TargetAccount int = 1;
DECLARE @ResearchMenuId int;
DECLARE @PreferredResearchMenuId int = 9;

BEGIN TRANSACTION;

SELECT TOP (1)
    @ResearchMenuId = [id]
FROM [dbo].[登录基础数据_二级菜单] WITH (UPDLOCK, HOLDLOCK)
WHERE [账号代码] = @TargetAccount
  AND [pid] = 0
  AND
  (
      [title] = N'研发管理'
      OR [path] = '/ResearchManagement'
      OR [name] = 'ResearchManagement'
  )
ORDER BY
    CASE WHEN [title] = N'研发管理' THEN 0 ELSE 1 END,
    [id];

IF @ResearchMenuId IS NULL
BEGIN
    IF NOT EXISTS
    (
        SELECT 1
        FROM [dbo].[登录基础数据_二级菜单] WITH (UPDLOCK, HOLDLOCK)
        WHERE [账号代码] = @TargetAccount
          AND [id] = @PreferredResearchMenuId
    )
    BEGIN
        SET @ResearchMenuId = @PreferredResearchMenuId;
    END
    ELSE
    BEGIN
        SELECT @ResearchMenuId = ISNULL(MAX([id]), 0) + 1
        FROM [dbo].[登录基础数据_二级菜单] WITH (UPDLOCK, HOLDLOCK)
        WHERE [账号代码] = @TargetAccount;
    END;

    INSERT INTO [dbo].[登录基础数据_二级菜单]
        ([id], [pid], [path], [componet], [redirect], [name], [title], [icon], [账号代码], [模块代码], [是否启用], [操作时间])
    VALUES
        (@ResearchMenuId, 0, '/ResearchManagement', '/layout/Layout', NULL, 'ResearchManagement', N'研发管理', 'CreateProject', @TargetAccount, @ResearchMenuId, 1, GETDATE());
END
ELSE
BEGIN
    UPDATE [dbo].[登录基础数据_二级菜单]
    SET
        [pid] = 0,
        [path] = '/ResearchManagement',
        [componet] = '/layout/Layout',
        [redirect] = NULL,
        [name] = 'ResearchManagement',
        [title] = N'研发管理',
        [icon] = 'CreateProject',
        [模块代码] = [id],
        [是否启用] = 1,
        [操作时间] = GETDATE()
    WHERE [账号代码] = @TargetAccount
      AND [id] = @ResearchMenuId;
END;

DECLARE @Menus TABLE
(
    rn int IDENTITY(1,1) PRIMARY KEY,
    [path] varchar(100) NOT NULL,
    [componet] varchar(100) NOT NULL,
    [name] varchar(100) NOT NULL,
    [title] varchar(100) NOT NULL,
    [icon] varchar(100) NULL
);

INSERT INTO @Menus ([path], [componet], [name], [title], [icon])
VALUES
    ('DesignReportTask/Task/index', '/ProductionManagement/DesignReportTask/Task/index', 'DesignReportTask', N'设计任务', 'DocumentImport'),
    ('DesignReportTask/Report/index', '/ProductionManagement/DesignReportTask/Report/index', 'DesignReportWork', N'设计报工', 'DocumentImport'),
    ('DesignReportTask/Check/index', '/ProductionManagement/DesignReportTask/Check/index', 'DesignReportCheck', N'设计工时校验', 'PayStateSearch'),
    ('DesignReportTask/HoursReport/index', '/ProductionManagement/DesignReportTask/HoursReport/index', 'DesignReportHoursReport', N'设计工时报表', 'DimensionsSummaryReport');

DECLARE @BaseId int;

SELECT @BaseId = ISNULL(MAX([id]), 0)
FROM [dbo].[登录基础数据_二级菜单] WITH (UPDLOCK, HOLDLOCK)
WHERE [账号代码] = @TargetAccount;

;WITH MissingMenus AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY m.rn) AS new_rn,
        m.[path],
        m.[componet],
        m.[name],
        m.[title],
        m.[icon]
    FROM @Menus AS m
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM [dbo].[登录基础数据_二级菜单] AS existing WITH (UPDLOCK, HOLDLOCK)
        WHERE existing.[账号代码] = @TargetAccount
          AND
          (
              existing.[path] = m.[path]
              OR existing.[componet] = m.[componet]
              OR existing.[title] = m.[title]
          )
    )
)
INSERT INTO [dbo].[登录基础数据_二级菜单]
    ([id], [pid], [path], [componet], [redirect], [name], [title], [icon], [账号代码], [模块代码], [是否启用], [操作时间])
SELECT
    @BaseId + new_rn,
    @ResearchMenuId,
    [path],
    [componet],
    NULL,
    [name],
    [title],
    [icon],
    @TargetAccount,
    @BaseId + new_rn,
    1,
    GETDATE()
FROM MissingMenus;

UPDATE target
SET
    target.[pid] = @ResearchMenuId,
    target.[path] = source.[path],
    target.[componet] = source.[componet],
    target.[redirect] = NULL,
    target.[name] = source.[name],
    target.[title] = source.[title],
    target.[icon] = source.[icon],
    target.[模块代码] = target.[id],
    target.[是否启用] = 1,
    target.[操作时间] = GETDATE()
FROM [dbo].[登录基础数据_二级菜单] AS target
INNER JOIN @Menus AS source
    ON target.[账号代码] = @TargetAccount
   AND
   (
       target.[path] = source.[path]
       OR target.[componet] = source.[componet]
       OR target.[title] = source.[title]
   );

COMMIT TRANSACTION;

SELECT
    [id],
    [pid],
    [path],
    [componet],
    [name],
    [title],
    [icon],
    [账号代码],
    [模块代码],
    [是否启用]
FROM [dbo].[登录基础数据_二级菜单]
WHERE [账号代码] = @TargetAccount
  AND
  (
      [id] = @ResearchMenuId
      OR [path] IN (SELECT [path] FROM @Menus)
      OR [componet] IN (SELECT [componet] FROM @Menus)
      OR [title] IN (SELECT [title] FROM @Menus)
  )
ORDER BY [pid], [id];
GO
