namespace FrameworkDAL.Query.Dashboard
{
    public static class DashboardQB
    {
        public const string GET_DASHBOARD = @"SELECT
                            D.DASHBOARDID AS DashboardId,
                            D.DASHBOARDCODE AS DashboardCode,
                            D.DASHBOARDNAME AS DashboardName,
                            D.DASHBOARDTYPE AS DashboardType,
                            D.MODULEID AS ModuleId,
                            M.MODULECODE AS ModuleCode,
                            M.MODULENAME AS ModuleName,
                            D.OWNERID AS OwnerId,
                            D.IMAGEID AS ImageId,
                            I.IMAGENAME AS ImageName,
                            I.IMAGELINKURL AS ImageLinkURL,
                            D.REMARKS AS Remarks,
                            D.DEFAULTCRITERIACONFIGID AS DefaultCriteriaConfigId,
                            D.SORTORDER AS SortOrder,
                            D.STATUS AS Status,
                            D.VERSION AS Version,
                            D.SOURCETYPE AS SourceType,
                            D.CREATEDBYID AS CreatedById,
                            D.CREATEDON AS CreatedOn,
                            D.MODIFIEDBYID AS ModifiedById,
                            D.MODIFIEDON AS ModifiedOn
                            FROM MDASHBOARD D
                            LEFT JOIN MMODULE M ON D.MODULEID = M.MODULEID
                            LEFT JOIN MIMAGE I ON D.IMAGEID = I.IMAGEID
                            WHERE D.DASHBOARDID = @dashboardid;
                            ";

        public const string SAVE_DASHBOARD = @"INSERT
                            INTO MDASHBOARD (
                                 DASHBOARDID,
                                 DASHBOARDCODE,
                                 DASHBOARDNAME,
                                 DASHBOARDTYPE,
                                 MODULEID,
                                 OWNERID,
                                 IMAGEID,
                                 REMARKS,
                                 DEFAULTCRITERIACONFIGID,
                                 SORTORDER,
                                 STATUS,
                                 VERSION,
                                 SOURCETYPE,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON
                            ) VALUES (
                                 @DashboardId,
                                 @DashboardCode,
                                 @DashboardName,
                                 @DashboardType,
                                 @ModuleId,
                                 @OwnerId,
                                 @ImageId,
                                 @Remarks,
                                 @DefaultCriteriaConfigId,
                                 @SortOrder,
                                 @Status,
                                 @Version,
                                 @SourceType,
                                 @CreatedById,
                                 @CreatedOn,
                                 @ModifiedById,
                                 @ModifiedOn
                            );
                            ";

        public const string UPDATE_DASHBOARD = @"UPDATE
                            MDASHBOARD SET
                            DASHBOARDCODE = @DashboardCode,
                            DASHBOARDNAME = @DashboardName,
                            DASHBOARDTYPE = @DashboardType,
                            MODULEID = @ModuleId,
                            OWNERID = @OwnerId,
                            IMAGEID = @ImageId,
                            REMARKS = @Remarks,
                            DEFAULTCRITERIACONFIGID = @DefaultCriteriaConfigId,
                            SORTORDER = @SortOrder,
                            STATUS = @Status,
                            VERSION = @Version,
                            SOURCETYPE = @SourceType,
                            MODIFIEDBYID = @ModifiedById,
                            MODIFIEDON = @ModifiedOn
                            WHERE DASHBOARDID = @DashboardId;
                            ";

        public const string DELETE_DASHBOARD = @"DELETE
                            FROM MDASHBOARD
                            WHERE DASHBOARDID = @dashboardid;
                            ";

        public const string GET_SELECTLIST_DASHBOARD = @"SELECT
                            DASHBOARDID AS Id,
                            DASHBOARDCODE AS Code,
                            DASHBOARDNAME AS Name
                            FROM MDASHBOARD
                            ";

        // Resolves the dashboards visible to a user: unconditional Shared(1)/System(3),
        // owned Personal(0) via OwnerId, and Role(2) via MROLEVSDASHBOARD — each with its
        // ordered, visible MDASHBOARDPAGE list. Covering index note: IX_MDASHBOARD_OWNERID,
        // IX_MROLEVSDASHBOARD_ROLEID, IX_MDASHBOARDPAGE_DASHBOARDID (see migration).
        public const string GET_DASHBOARD_LIST_FOR_USER = @"SELECT
                            D.DASHBOARDID AS DashboardId,
                            D.DASHBOARDCODE AS DashboardCode,
                            D.DASHBOARDNAME AS DashboardName,
                            D.DASHBOARDTYPE AS DashboardType,
                            D.MODULEID AS ModuleId,
                            M.MODULECODE AS ModuleCode,
                            M.MODULENAME AS ModuleName,
                            D.OWNERID AS OwnerId,
                            D.IMAGEID AS ImageId,
                            I.IMAGENAME AS ImageName,
                            I.IMAGELINKURL AS ImageLinkURL,
                            D.REMARKS AS Remarks,
                            D.SORTORDER AS SortOrder,
                            D.STATUS AS Status,
                            D.VERSION AS Version,
                            D.SOURCETYPE AS SourceType,
                            D.CREATEDBYID AS CreatedById,
                            D.CREATEDON AS CreatedOn,
                            D.MODIFIEDBYID AS ModifiedById,
                            D.MODIFIEDON AS ModifiedOn,
                            ISNULL(UVD.ISPINNED, 0) AS IsPinned,
                            UVD.SLNO AS UserSlNo,
                            FC.FEATURECODE AS FeatureCode,
                            DP.DASHBOARDPAGEID AS DashboardPageId,
                            DP.SLNO AS SlNo,
                            DP.PAGEID AS PageId,
                            P.PAGECODE AS PageCode,
                            P.PAGENAME AS PageName,
                            DP.ISDEFAULTTAB AS IsDefaultTab,
                            DP.ISVISIBLE AS IsVisible
                            FROM MDASHBOARD D
                            LEFT JOIN MMODULE M ON D.MODULEID = M.MODULEID
                            LEFT JOIN MIMAGE I ON D.IMAGEID = I.IMAGEID
                            LEFT JOIN MUSERVSDASHBOARD UVD ON UVD.DASHBOARDID = D.DASHBOARDID AND UVD.USERID = @userid
                            OUTER APPLY (
                                SELECT TOP 1 MFDM.FEATURECODE
                                FROM MENTITLEMENTFEATUREDASHBOARDMAP MFDM
                                WHERE MFDM.DASHBOARDID = D.DASHBOARDID
                            ) FC
                            LEFT JOIN MDASHBOARDPAGE DP ON DP.DASHBOARDID = D.DASHBOARDID AND DP.ISVISIBLE = 1
                            LEFT JOIN MPAGE P ON P.PAGEID = DP.PAGEID
                            WHERE D.STATUS = 1
                              AND (
                                    D.DASHBOARDTYPE IN (1, 3)
                                 OR (D.DASHBOARDTYPE = 0 AND D.OWNERID = @userid)
                                 OR (D.DASHBOARDTYPE = 2 AND D.DASHBOARDID IN (
                                        SELECT RVD.DASHBOARDID FROM MROLEVSDASHBOARD RVD WHERE RVD.ROLEID = @roleid
                                     ))
                              )
                            ORDER BY ISNULL(UVD.ISPINNED, 0) DESC, ISNULL(UVD.SLNO, D.SORTORDER), D.DASHBOARDID, DP.SLNO;
                            ";
    }
}