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

namespace FrameworkDAL.Query.MenuSet
{
    public static class MenuSetQB
    {
        public const string GET_MENUSET = @"
SELECT 
    
    MS.MENUSETID               AS MenuSetId,
    MS.MENUSETNAME             AS MenuSetName,
    MS.MODULEID                AS ModuleId,
    MOD.MODULECODE             AS ModuleCode,
    COALESCE(NULLIF(TRMOD_PP.MODULENAME,''), NULLIF(TRMOD_GEN.MODULENAME,''), MOD.MODULENAME) AS ModuleName,
    MS.APPLICABLETYPE          AS MenuSetApplicableType,
    MS.CLIENTID                AS ClientId,
    C.CLIENTCODE               AS ClientCode,
    C.CLIENTNAME               AS ClientName,
    MS.SORTORDER               AS MenuSetSortOrder,
    MS.STATUS                  AS MenuSetStatus,
    MS.VERSION                 AS MenuSetVersion,
    MS.SOURCETYPE              AS MenuSetSourceType,
    MS.CREATEDBYID             AS MenuSetCreatedById,
    MS.CREATEDON               AS MenuSetCreatedOn,
    MS.MODIFIEDBYID            AS MenuSetModifiedById,
    MS.MODIFIEDON              AS MenuSetModifiedOn,
    MS.PRESENTATIONTYPE        AS PresentationType,
    MS.MENUGROUPID             AS MenuGroupId,

    MSD.MENUSETDETAILID        AS MenuSetDetailId,
    MSD.MENUSETID              AS MenuSetId,
    MSD.SLNO                   AS MenuSetDetailSlNo,
    MSD.MENUID                 AS MenuId,
    M.MENUCODE                 AS MenuCode,
    M.MENUNAME                 AS MenuName,
    MSD.MENUDISPLAYNAME        AS MenuSetDetailDisplayName,
    MSD.MENUKEY                AS MenuSetDetailMenuKey,
    MSD.SORTORDER              AS MenuSetDetailSortOrder,
    MSD.ISVISIBLE              AS MenuSetDetailIsVisible,

    MSD.DEFAULTREPORTVIEWID    AS ReportViewId,
    RV.REPORTVIEWNAME          AS ReportViewName,
    MSD.DEFAULTREPORTFORMATID  AS ReportFormatId,
    RF.REPORTFORMATNAME        AS ReportFormatName,

    MSD.OBJECTMODELID          AS ObjectModelId,
    F.FILENAME                 AS ObjectModelName

FROM MMENUSET MS
LEFT JOIN MMODULE MOD ON MS.MODULEID = MOD.MODULEID
LEFT JOIN TR_MMODULE TRMOD_GEN ON MOD.MODULEID = TRMOD_GEN.MODULEID AND TRMOD_GEN.LANGUAGEID = @languageid AND TRMOD_GEN.PARTNERPRODUCTID IS NULL
LEFT JOIN TR_MMODULE TRMOD_PP  ON MOD.MODULEID = TRMOD_PP.MODULEID  AND TRMOD_PP.LANGUAGEID  = @languageid AND TRMOD_PP.PARTNERPRODUCTID = @partnerproductid
LEFT JOIN MCLIENT C ON MS.CLIENTID = C.CLIENTID

LEFT JOIN MMENUSETDETAIL MSD ON MS.MENUSETID = MSD.MENUSETID AND MSD.MENUSETDETAILID IS NOT NULL
LEFT JOIN MMENU M ON MSD.MENUID = M.MENUID
LEFT JOIN MREPORTVIEW RV ON MSD.DEFAULTREPORTVIEWID = RV.REPORTVIEWID
LEFT JOIN MREPORTFORMAT RF ON MSD.DEFAULTREPORTFORMATID = RF.REPORTFORMATID
LEFT JOIN MFILE F ON MSD.OBJECTMODELID = F.FILEID
WHERE MS.MENUSETID = @menusetid;";

        public const string GetSelectList_MenuSet = @"
        SELECT
        MENUSETID AS Id,
        MENUSETNAME AS Name
        from MMENUSET;";

        // ── MMENUSET CRUD — MMENUSET never had a Save/Update/Delete path before this
        // follow-up (Get/List only). Column list matches the confirmed-live SELECT in
        // GET_MENUSET above (MENUSETID, MENUSETNAME, MODULEID, APPLICABLETYPE, CLIENTID,
        // SORTORDER, STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID,
        // MODIFIEDON), plus the additive PRESENTATIONTYPE/MENUGROUPID columns.
        public const string INSERT_MENUSET = @"
INSERT INTO MMENUSET
    (MENUSETID, MENUSETNAME, MODULEID, APPLICABLETYPE, CLIENTID, SORTORDER, STATUS, VERSION,
     SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, PRESENTATIONTYPE, MENUGROUPID)
VALUES
    (@MenuSetId, @MenuSetName, @ModuleId, @MenuSetApplicableType, @ClientId, @MenuSetSortOrder,
     @MenuSetStatus, @MenuSetVersion, @MenuSetSourceType, @MenuSetCreatedById, @MenuSetCreatedOn,
     @MenuSetModifiedById, @MenuSetModifiedOn, @PresentationType, @MenuGroupId)";

        public const string UPDATE_MENUSET = @"
UPDATE MMENUSET
SET MENUSETNAME = @MenuSetName, MODULEID = @ModuleId, APPLICABLETYPE = @MenuSetApplicableType,
    CLIENTID = @ClientId, SORTORDER = @MenuSetSortOrder, STATUS = @MenuSetStatus,
    VERSION = VERSION + 1, SOURCETYPE = @MenuSetSourceType, MODIFIEDBYID = @MenuSetModifiedById,
    MODIFIEDON = @MenuSetModifiedOn, PRESENTATIONTYPE = @PresentationType, MENUGROUPID = @MenuGroupId
WHERE MENUSETID = @MenuSetId";

        public const string DELETE_MENUSET = "DELETE FROM MMENUSET WHERE MENUSETID = @MenuSetId";

        // ── MMENUSETDETAIL diff-save (SkillProcessStageMapDAL / MenuGroupDetail shape) ──
        // Column list matches the confirmed-live SELECT in GET_MENUSET above exactly
        // (MENUSETDETAILID, MENUSETID, SLNO, MENUID, MENUDISPLAYNAME, MENUKEY, SORTORDER,
        // ISVISIBLE, DEFAULTREPORTVIEWID, DEFAULTREPORTFORMATID, OBJECTMODELID), plus the
        // additive MENUGROUPID column from 20260728_Framework_MenuGroup_AlterMenuSetDetail.
        public const string DELETE_MENUSETDETAIL_NOT_IN = @"
DELETE FROM MMENUSETDETAIL
WHERE MENUSETID = @MenuSetId AND MENUSETDETAILID NOT IN @SurvivingIds";

        public const string DELETE_MENUSETDETAIL_BY_SET = "DELETE FROM MMENUSETDETAIL WHERE MENUSETID = @MenuSetId";

        public const string INSERT_MENUSETDETAIL = @"
INSERT INTO MMENUSETDETAIL
    (MENUSETDETAILID, MENUSETID, SLNO, MENUID, MENUDISPLAYNAME, MENUKEY, SORTORDER, ISVISIBLE,
     DEFAULTREPORTVIEWID, DEFAULTREPORTFORMATID, OBJECTMODELID, MENUGROUPID)
VALUES
    (@MenuSetDetailId, @MenuSetId, @MenuSetDetailSlNo, @MenuId, @MenuSetDetailDisplayName, @MenuSetDetailMenuKey,
     @MenuSetDetailSortOrder, @MenuSetDetailIsVisible, @ReportViewId, @ReportFormatId, @ObjectModelId, @MenuGroupId)";

        public const string UPDATE_MENUSETDETAIL = @"
UPDATE MMENUSETDETAIL
SET MENUID = @MenuId, MENUDISPLAYNAME = @MenuSetDetailDisplayName, MENUKEY = @MenuSetDetailMenuKey,
    SORTORDER = @MenuSetDetailSortOrder, ISVISIBLE = @MenuSetDetailIsVisible,
    DEFAULTREPORTVIEWID = @ReportViewId, DEFAULTREPORTFORMATID = @ReportFormatId,
    OBJECTMODELID = @ObjectModelId, MENUGROUPID = @MenuGroupId
WHERE MENUSETDETAILID = @MenuSetDetailId";
    }
}