using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.Query.RoleVsMenu
{
    /// <summary>
    /// RoleVsMenuQB
    /// </summary>
    public static class RoleVsMenuQB
    {
        /// <summary>
        /// SAVE_ROLEVSMENU
        /// </summary>
        public const string SAVE_ROLEVSMENU = @"INSERT INTO MROLEVSMENU (
                                                    ROLEVSMENUID,
                                                    ROLEID,
                                                    MENUID,
                                                    SLNO,
                                                    ALLOW
                                                )
                                                VALUES (
                                                    @RoleVsMenuId,
                                                    @RoleVsMenuRoleId,
                                                    @RoleVsMenuMenuId,
                                                    @RoleVsMenuSlNo,
                                                    @RoleVsMenuAllow
                                                );
                                                ";

        /// <summary>
        /// UPDATE_ROLEVSMENU — diff-save path: reuses the existing ROLEVSMENUID for a
        /// (RoleId, MenuId) pair that already exists, instead of delete+reinsert, so
        /// CREATEDON/audit history on unrelated rows survives a save.
        /// </summary>
        public const string UPDATE_ROLEVSMENU = @"
UPDATE MROLEVSMENU
SET SLNO = @RoleVsMenuSlNo, ALLOW = @RoleVsMenuAllow
WHERE ROLEVSMENUID = @RoleVsMenuId";

        /// <summary>
        /// GET_EXISTING_ROLEVSMENU_FOR_MODULE — existing (RoleVsMenuId, MenuId) pairs for a
        /// role, scoped to a module's menus (same join MMODULEVSMENU shape as the legacy
        /// DeleteRoleVsMenu), used by the diff-save to compute add/update/delete sets.
        /// </summary>
        public const string GET_EXISTING_ROLEVSMENU_FOR_MODULE = @"
SELECT rv.ROLEVSMENUID AS RoleVsMenuId, rv.MENUID AS RoleVsMenuMenuId
FROM MROLEVSMENU rv
WHERE rv.ROLEID = @RoleId
  AND rv.MENUID IN (SELECT DISTINCT MENUID FROM MMODULEVSMENU WHERE MODULEID = @ModuleId)";

        /// <summary>
        /// GET_EXISTING_ROLEVSMENU_FOR_MENUIDS — existing (RoleVsMenuId, MenuId) pairs for a
        /// role, scoped to the specific menu ids in this save batch (PageId != -1 case).
        /// </summary>
        public const string GET_EXISTING_ROLEVSMENU_FOR_MENUIDS = @"
SELECT ROLEVSMENUID AS RoleVsMenuId, MENUID AS RoleVsMenuMenuId
FROM MROLEVSMENU
WHERE ROLEID = @RoleId AND MENUID IN @MenuIds";

        public const string DELETE_ROLEVSMENU_BY_IDS = "DELETE FROM MROLEVSMENU WHERE ROLEVSMENUID IN @Ids";

        /// <summary>
        /// GET_ROLE_VS_MENU
        /// </summary>
        public const string GET_ROLE_VS_MENU =
            @"SELECT
                rolevsmenu.ROLEVSMENUID         AS RoleVsMenuId,
                modulevsmenu.MENUID             AS RoleVsMenuMenuId,
                menu.MENUCODE                   AS RoleVsMenuMenuCode,
                menu.MENUDISPLAYNAME            AS RoleVsMenuMenuName,
                modulevsmenu.PARENTID           AS RoleVsMenuMenuParentId,
                rolevsmenu.SLNO                 AS RoleVsMenuSlNo,
                rolevsmenu.ALLOW                AS RoleVsMenuAllow,
                rolevsmenu.MAXDAYSNORMAL        AS RoleVsMenuMaxDaysNormal,
                rolevsmenu.DAYSLIMIT            AS RoleVsMenuDaysLimit,
                rolevsmenu.MAXRECORDSNORMAL     AS RoleVsMenuMaxRecordsNormal,
                rolevsmenu.RECORDSLIMIT         AS RoleVsMenuRecordsLimit
            FROM MROLEVSMENU rolevsmenu
            JOIN MMODULEVSMENU modulevsmenu ON rolevsmenu.MENUID   = modulevsmenu.MENUID
                                           AND modulevsmenu.MODULEID = @moduleid
            JOIN MMENU menu                ON menu.MENUID          = rolevsmenu.MENUID
            WHERE rolevsmenu.ROLEID = @roleid";

        /// <summary>
        /// GET_ROLE_VS_MENU_BY_ROLE_ID
        /// </summary>
        public const string GET_ROLE_VS_MENU_BY_ROLE_ID =
            @"SELECT
                rvm.ROLEVSMENUID        AS RoleVsMenuId,
                rvm.ROLEID              AS RoleVsMenuRoleId,
                r.ROLECODE              AS RoleVsMenuRoleCode,
                r.ROLENAME              AS RoleVsMenuRoleName,
                rvm.MENUID              AS RoleVsMenuMenuId,
                m.MENUCODE              AS RoleVsMenuMenuCode,
                m.MENUDISPLAYNAME       AS RoleVsMenuMenuName,
                mvs.PARENTID            AS RoleVsMenuMenuParentId,
                mvs.MODULEID            AS RoleVsMenuMenuModuleId,
                rvm.SLNO                AS RoleVsMenuSlNo,
                rvm.ALLOW               AS RoleVsMenuAllow
              FROM MROLEVSMENU rvm
              JOIN MMENU m    ON m.MENUID   = rvm.MENUID
              JOIN MROLE r    ON r.ROLEID   = rvm.ROLEID
              JOIN MMODULEVSMENU mvs ON mvs.MENUID = rvm.MENUID
             WHERE rvm.ROLEID = @RoleId";

        /// <summary>
        /// GET_ROLE_VS_MENU_DETAIL
        /// </summary>
        public const string GET_ROLE_VS_MENU_DETAIL =
            @"SELECT
                rvm.ROLEVSMENUID        AS RoleVsMenuId,
                rvm.ROLEID              AS RoleId,
                r.ROLECODE              AS RoleCode,
                r.ROLENAME              AS RoleName,
                rvm.SLNO                AS RoleVsMenuSlNo,
                rvm.MENUID              AS MenuId,
                m.MENUCODE              AS MenuCode,
                m.MENUDISPLAYNAME       AS MenuDisplayName,
                m.MENUNAME              AS MenuName,
                CASE m.MENUTYPE
                    WHEN 0 THEN 'Master'
                    WHEN 1 THEN 'Transaction'
                    WHEN 2 THEN 'Report'
                    WHEN 3 THEN 'Others'
                    WHEN 4 THEN 'Bot'
                    ELSE ''
                END                     AS MenuType,
                m.MENUNATURE            AS MenuNature,
                m.SECTION               AS Section,
                mvs.MODULEID            AS ModuleId,
                mod.MODULECODE          AS ModuleCode,
                mod.MODULENAME          AS ModuleName,
                mvs.PARENTID            AS ParentMenuId,
                pm.MENUCODE             AS ParentMenuCode,
                pm.MENUNAME             AS ParentMenuName,
                pm.MENUDISPLAYNAME      AS ParentDisplayName,
                rvm.ALLOW               AS RoleVsMenuAllow,
                rvm.ALLOW               AS Allow
              FROM MROLEVSMENU rvm
              JOIN MMENU m        ON m.MENUID      = rvm.MENUID
              JOIN MROLE r        ON r.ROLEID      = rvm.ROLEID
              JOIN MMODULEVSMENU mvs ON mvs.MENUID = rvm.MENUID
              JOIN MMODULE mod    ON mod.MODULEID  = mvs.MODULEID
              LEFT JOIN MMENU pm  ON pm.MENUID     = mvs.PARENTID
             WHERE (@SearchKeyword IS NULL OR r.ROLENAME LIKE '%' + @SearchKeyword + '%'
                    OR m.MENUCODE LIKE '%' + @SearchKeyword + '%')
               AND (@MenuId       IS NULL OR rvm.MENUID   = @MenuId)
               AND (@RoleId       IS NULL OR rvm.ROLEID   = @RoleId)
               AND (@ModuleId     IS NULL OR mvs.MODULEID = @ModuleId)
               AND (@ParentMenuId IS NULL OR mvs.PARENTID = @ParentMenuId)
             ORDER BY r.ROLENAME, mod.MODULENAME, m.MENUCODE
            OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY";

        /// <summary>
        /// GET_ROLE_VS_MENU_REPORT_DETAIL
        /// </summary>
        public const string GET_ROLE_VS_MENU_REPORT_DETAIL =
            @"SELECT
                rvm.ROLEVSMENUID        AS RoleVsMenuId,
                rvm.ROLEID              AS RoleId,
                r.ROLECODE              AS RoleCode,
                r.ROLENAME              AS RoleName,
                rvm.SLNO                AS RoleVsMenuSlNo,
                rvm.MENUID              AS MenuId,
                m.MENUCODE              AS MenuCode,
                m.MENUDISPLAYNAME       AS MenuDisplayName,
                m.MENUNAME              AS MenuName,
                CASE m.MENUTYPE
                    WHEN 0 THEN 'Master'
                    WHEN 1 THEN 'Transaction'
                    WHEN 2 THEN 'Report'
                    WHEN 3 THEN 'Others'
                    WHEN 4 THEN 'Bot'
                    ELSE ''
                END                     AS MenuType,
                m.MENUNATURE            AS MenuNature,
                m.SECTION               AS Section,
                mvs.MODULEID            AS ModuleId,
                mod.MODULECODE          AS ModuleCode,
                mod.MODULENAME          AS ModuleName,
                mvs.PARENTID            AS ParentMenuId,
                pm.MENUCODE             AS ParentMenuCode,
                pm.MENUNAME             AS ParentMenuName,
                pm.MENUDISPLAYNAME      AS ParentDisplayName,
                rvm.ALLOW               AS RoleVsMenuAllow,
                rvm.ALLOW               AS Allow
              FROM MROLEVSMENU rvm
              JOIN MMENU m        ON m.MENUID      = rvm.MENUID
              JOIN MROLE r        ON r.ROLEID      = rvm.ROLEID
              JOIN MMODULEVSMENU mvs ON mvs.MENUID = rvm.MENUID
              JOIN MMODULE mod    ON mod.MODULEID  = mvs.MODULEID
              LEFT JOIN MMENU pm  ON pm.MENUID     = mvs.PARENTID
             WHERE (@SearchKeyword IS NULL OR r.ROLENAME LIKE '%' + @SearchKeyword + '%'
                    OR m.MENUCODE LIKE '%' + @SearchKeyword + '%')
               AND (@MenuId       IS NULL OR rvm.MENUID   = @MenuId)
               AND (@RoleId       IS NULL OR rvm.ROLEID   = @RoleId)
               AND (@ModuleId     IS NULL OR mvs.MODULEID = @ModuleId)
               AND (@ParentMenuId IS NULL OR mvs.PARENTID = @ParentMenuId)
             ORDER BY r.ROLENAME, mod.MODULENAME, m.MENUCODE
            OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY";

        /// <summary>
        /// GET_ROLE_VS_MENU_FOR_MENU
        /// </summary>
        public const string GET_ROLE_VS_MENU_FOR_MENU =
            @"SELECT
                rvm_mod.ROLEID                  AS RoleId,
                r.ROLENAME                      AS RoleName,
                CASE WHEN MAX(CASE WHEN rvm.MENUID IS NULL THEN 0 ELSE 1 END) = 0
                     THEN CAST(1 AS TINYINT)
                     ELSE CAST(0 AS TINYINT)
                END                             AS IsAvailable,
                MAX(rvm.ALLOW)                  AS Allow
              FROM MROLEVSMODULES rvm_mod
              JOIN MROLE r              ON r.ROLEID  = rvm_mod.ROLEID
              LEFT JOIN MROLEVSMENU rvm ON rvm.ROLEID = rvm_mod.ROLEID
                                      AND rvm.MENUID  = @MenuId
             WHERE rvm_mod.ROLEID <> -1
               AND rvm_mod.MODULEID = @ModuleId
             GROUP BY rvm_mod.ROLEID, r.ROLENAME
             ORDER BY r.ROLENAME";

        /// <summary>
        /// GET_MENU_FROM_MODULEMENU
        /// </summary>
        public const string GET_MENU_FROM_MODULEMENU =

            @"Select" + "\r\n" +
            "       distinct menusetdetail.MenuId as RoleVsMenuMenuId," + "\r\n" +
            "       Menu.MENUCODE as RoleVsMenuMenuCode," + "\r\n" +
            "       Menu.MenuDisplayName as RoleVsMenuMenuName," + "\r\n" +
            "       modulevsmenu.ParentId as RoleVsMenuMenuParentId" + "\r\n" +
            "  from  " + "\r\n" +
            "       mMenuSetDetail menusetdetail,mMenuSet menuset,mModuleVsMenu modulevsmenu, MMENU menu" + "\r\n" +
            "  where  " + "\r\n" +
            "       menuset.MENUSETID=menusetdetail.MenuSetId " + "\r\n" +
            "       and modulevsmenu.MenuId=menusetdetail.MenuId " + "\r\n" +
            "       and menusetdetail.MENUID = menu.MENUID" + "\r\n" +
            "       and menusetdetail.ISVISIBLE = 0 " + "\r\n" +
            "       and menusetdetail.MenuId NOT IN ( " + "\r\n" +
            "       select rolevsmenu.MenuId as RoleVsMenuMenuId from " + "\r\n" +
            "       MRoleVsMenu rolevsmenu where rolevsmenu.RoleId=@roleid) " + "\r\n" +
            "       and modulevsmenu.ModuleId=@moduleid " + "\r\n";
    }
}