namespace FrameworkDAL.Query.MenuGroup
{
    /// <summary>
    /// SQL constants for MMENUGROUP / MMENUGROUPDETAIL. Every query here uses only
    /// ANSI-standard constructs (COALESCE, not ISNULL) so a single constant is valid on
    /// both SQL Server and PostgreSQL — unlike MenuQB.cs's legacy dual-dialect pairs,
    /// no _POSTGRE-suffixed duplicates are needed for this table family.
    ///
    /// Required indexes (mirrored in the schema migration):
    ///   MMENUGROUP       (MENUSETID, PARENTGROUPID)
    ///   MMENUGROUPDETAIL (MENUGROUPID)
    ///   MMENUGROUPDETAIL (MENUID)
    ///   MMENUSETDETAIL   (MENUSETID, MENUGROUPID)
    /// </summary>
    public static class MenuGroupQB
    {
        /// <summary>
        /// Flat list of every group visible to a given MenuSetId: shared bundle nodes
        /// (MENUSETID IS NULL) plus any private nodes scoped to this set. The BLL builds
        /// the tree from this flat list via TreeHelper.ConvertToTree, after translating
        /// ParentGroupId == -1 to a null Parent reference (mirrors MenuBLL's MMENU handling).
        /// </summary>
        public const string GET_MENUGROUP_FLAT_LIST = @"
SELECT
    MENUGROUPID     AS MenuGroupId,
    GROUPCODE       AS GroupCode,
    GROUPNAME       AS GroupName,
    MODULEID        AS ModuleId,
    MENUSETID       AS MenuSetId,
    PARENTGROUPID   AS ParentGroupId,
    PARENTGROUPIDS  AS ParentGroupIds,
    LEVELS          AS Levels,
    ICONNAME        AS IconName,
    REMARKS         AS Remarks,
    SORTORDER       AS SortOrder,
    STATUS          AS Status,
    VERSION         AS Version,
    SOURCETYPE      AS SourceType,
    CREATEDBYID     AS CreatedById,
    CREATEDON       AS CreatedOn,
    MODIFIEDBYID    AS ModifiedById,
    MODIFIEDON      AS ModifiedOn
FROM MMENUGROUP
WHERE (MENUSETID IS NULL OR MENUSETID = @MenuSetId)
  AND STATUS = 1
ORDER BY SORTORDER";

        /// <summary>Small, dedicated lookup — not a reuse of MenuSetQB.GET_MENUSET, see
        /// MenuSetPresentationInfoDTO's remarks.</summary>
        public const string GET_MENUSET_PRESENTATION_INFO = @"
 SELECT menusetid        AS MenuSetId,
       moduleid         AS ModuleId 
       --presentationtype AS PresentationType,
      -- menugroupid      AS MenuGroupId
FROM   mmenuset 
WHERE  menusetid  = @MenuSetId";

        public const string GET_MENUGROUP_BY_ID = @"
SELECT
    MENUGROUPID AS MenuGroupId, GROUPCODE AS GroupCode, GROUPNAME AS GroupName,
    MODULEID AS ModuleId, MENUSETID AS MenuSetId, PARENTGROUPID AS ParentGroupId,
    ICONNAME AS IconName, SORTORDER AS SortOrder, STATUS AS Status,
    VERSION AS Version, SOURCETYPE AS SourceType
FROM MMENUGROUP
WHERE MENUGROUPID = @MenuGroupId";

        /// <summary>
        /// Resolved leaves for a set of group ids (the tree just walked): bundle membership
        /// (MMENUGROUPDETAIL) left-joined to MMENU for the fallback display name, left-joined
        /// to MMENUSETDETAIL for this MenuSetId's per-client override. ResolvedMenuGroupId
        /// applies the MENUGROUPID re-filing override; the other Resolved* columns apply the
        /// display-name/sort/visibility override, per the resolution order documented in
        /// gb5_menu_presentation_layer.sql: override row found -> use it, not found -> MMENU's
        /// own display name + the bundle's own SORTORDER + ISVISIBLE = 1.
        ///
        /// @RoleId is nullable by design: admin/content-management callers (the MenuGroup admin
        /// tree, the Client Menu Set Editor) pass NULL to see full bundle membership regardless
        /// of any one role's rights. End-user callers (the production menu-tree endpoint) pass
        /// the caller's LoginDTO.RoleId, which applies the same MROLEVSMENU view-right check
        /// (Allow position 1 = '0') the legacy GetMenuForUserModule query enforces.
        /// </summary>
        public const string GET_RESOLVED_LEAVES_BY_GROUPIDS = @"
SELECT
    COALESCE(msd.MENUGROUPID, gd.MENUGROUPID)                                   AS MenuGroupId,
    gd.MENUID                                                                   AS MenuId,
    m.MENUCODE                                                                  AS MenuCode,
    CASE WHEN COALESCE(msd.MENUDISPLAYNAME, '') = '' THEN m.MENUDISPLAYNAME ELSE msd.MENUDISPLAYNAME END AS DisplayName,
    COALESCE(msd.SORTORDER, gd.SORTORDER)                                       AS SortOrder,
    COALESCE(msd.ISVISIBLE, 1)                                                  AS IsVisible,
    COALESCE(msd.MENUSETDETAILID, 0)                                            AS MenuSetDetailId
FROM MMENUGROUPDETAIL gd
JOIN MMENU m ON m.MENUID = gd.MENUID
LEFT JOIN MMENUSETDETAIL msd ON msd.MENUSETID = @MenuSetId AND msd.MENUID = gd.MENUID
WHERE gd.MENUGROUPID IN @GroupIds
  AND (@RoleId IS NULL OR EXISTS (
        SELECT 1 FROM MROLEVSMENU rvm
        WHERE rvm.MENUID = gd.MENUID AND rvm.ROLEID = @RoleId AND SUBSTRING(rvm.ALLOW, 1, 1) = '0'
      ))";

        /// <summary>Resolves the MenuSetId a client currently has bound to a module, via
        /// MCLIENTMODULE — the same join the legacy GET_MENU_TREE_FOR_USER_MODULE query uses
        /// inline. Returns null if the client has no menu set assigned for this module.</summary>
        public const string GET_MENUSETID_FOR_MODULE = @"
SELECT MENUSETID
FROM MCLIENTMODULE
WHERE CLIENTID = @ClientId AND MODULEID = @ModuleId";

        public const string INSERT_MENUGROUP = @"
INSERT INTO MMENUGROUP
    (MENUGROUPID, GROUPCODE, GROUPNAME, MODULEID, MENUSETID, PARENTGROUPID, ICONNAME, REMARKS,
     SORTORDER, STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
VALUES
    (@MenuGroupId, @GroupCode, @GroupName, @ModuleId, @MenuSetId, @ParentGroupId, @IconName, @Remarks,
     @SortOrder, @Status, @Version, @SourceType, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE_MENUGROUP = @"
UPDATE MMENUGROUP
SET GROUPCODE = @GroupCode, GROUPNAME = @GroupName, MODULEID = @ModuleId, MENUSETID = @MenuSetId,
    PARENTGROUPID = @ParentGroupId, ICONNAME = @IconName, REMARKS = @Remarks, SORTORDER = @SortOrder,
    STATUS = @Status, VERSION = VERSION + 1, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
WHERE MENUGROUPID = @MenuGroupId";

        public const string DELETE_MENUGROUP = "DELETE FROM MMENUGROUP WHERE MENUGROUPID = @MenuGroupId";

        // ── MMENUGROUPDETAIL diff-save statements (SkillProcessStageMapDAL shape) ──────────
        public const string DELETE_MENUGROUPDETAIL_NOT_IN = @"
DELETE FROM MMENUGROUPDETAIL
WHERE MENUGROUPID = @MenuGroupId AND MENUGROUPDETAILID NOT IN @SurvivingIds";

        public const string DELETE_MENUGROUPDETAIL_BY_GROUP = "DELETE FROM MMENUGROUPDETAIL WHERE MENUGROUPID = @MenuGroupId";

        public const string INSERT_MENUGROUPDETAIL = @"
INSERT INTO MMENUGROUPDETAIL
    (MENUGROUPDETAILID, MENUGROUPID, MENUID, SORTORDER, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
VALUES
    (@MenuGroupDetailId, @MenuGroupId, @MenuId, @SortOrder, @SourceType, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE_MENUGROUPDETAIL = @"
UPDATE MMENUGROUPDETAIL
SET MENUID = @MenuId, SORTORDER = @SortOrder, SOURCETYPE = @SourceType,
    MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
WHERE MENUGROUPDETAILID = @MenuGroupDetailId";
    }
}