namespace FrameworkDAL.Query.Dashboard
{
    public static class RoleVsDashboardQB
    {
        public const string GET_ROLEVSDASHBOARD = @"SELECT
                            RVD.ROLEVSDASHBOARDID AS RoleVsDashboardId,
                            RVD.ROLEID AS RoleId,
                            RO.ROLECODE AS RoleCode,
                            RO.ROLENAME AS RoleName,
                            RVD.DASHBOARDID AS DashboardId,
                            D.DASHBOARDCODE AS DashboardCode,
                            D.DASHBOARDNAME AS DashboardName,
                            RVD.SLNO AS SlNo
                            FROM MROLEVSDASHBOARD RVD
                            LEFT JOIN MROLE RO ON RVD.ROLEID = RO.ROLEID
                            LEFT JOIN MDASHBOARD D ON RVD.DASHBOARDID = D.DASHBOARDID
                            WHERE RVD.ROLEID = @roleid;
                            ";

        public const string SAVE_ROLEVSDASHBOARD = @"INSERT
                            INTO MROLEVSDASHBOARD (
                                 ROLEVSDASHBOARDID,
                                 ROLEID,
                                 DASHBOARDID,
                                 SLNO
                            ) VALUES (
                                 @RoleVsDashboardId,
                                 @RoleId,
                                 @DashboardId,
                                 @SlNo
                            );
                            ";

        public const string UPDATE_ROLEVSDASHBOARD = @"UPDATE
                            MROLEVSDASHBOARD SET
                            ROLEID = @RoleId,
                            DASHBOARDID = @DashboardId,
                            SLNO = @SlNo
                            WHERE ROLEVSDASHBOARDID = @RoleVsDashboardId;
                            ";

        public const string DELETE_ROLEVSDASHBOARD = @"DELETE
                            FROM MROLEVSDASHBOARD
                            WHERE ROLEID = @roleid;
                            ";

        // Reconciliation pair — mirrors RoleVsPortletQB.GET_ROLEVSPORTLET_FOR_UPDATE_USERVSPORTLET /
        // GET_USERVSPORTLET_FOR_DELETE_USERVSPORTLET exactly (Phase 6 of the Portlet/Page/Dashboard
        // roadmap). These only compute a diff — nothing here writes to MUSERVSDASHBOARD; the
        // caller decides whether to act on the result via the existing SaveUserVsDashboard/
        // DeleteUserVsDashboard endpoints.

        // Finds (User, Dashboard) pairs a role now grants that are missing a MUSERVSDASHBOARD row —
        // scoped to users who already have at least one materialized MUSERVSDASHBOARD row, same as
        // the Portlet original (brand-new, never-materialized users are out of scope for this diff).
        public const string GET_ROLEVSDASHBOARD_FOR_UPDATE_USERVSDASHBOARD = @"SELECT
                            U.USERID AS UserId,
                            RVD.DASHBOARDID AS DashboardId,
                            D.DASHBOARDCODE AS DashboardCode,
                            D.DASHBOARDNAME AS DashboardName,
                            RVD.SLNO AS SlNo
                            FROM MUSER U
                            LEFT JOIN MROLEVSDASHBOARD RVD ON U.ROLEID = RVD.ROLEID
                            LEFT JOIN MDASHBOARD D ON RVD.DASHBOARDID = D.DASHBOARDID
                            WHERE RVD.DASHBOARDID NOT IN (
                                SELECT DISTINCT UVD.DASHBOARDID
                                FROM MUSERVSDASHBOARD UVD
                                WHERE UVD.DASHBOARDID = RVD.DASHBOARDID
                                AND UVD.USERID = U.USERID
                            ) AND U.USERID IN (
                                SELECT DISTINCT UVD.USERID
                                FROM MUSERVSDASHBOARD UVD
                                WHERE UVD.USERID = U.USERID
                            ) AND RVD.ROLEID = @roleid;
                            ";

        // Finds existing MUSERVSDASHBOARD rows (for users currently on this role) that no longer
        // correspond to any MROLEVSDASHBOARD grant — stale materialized rows left behind after the
        // role's dashboard set shrinks.
        public const string GET_USERVSDASHBOARD_FOR_DELETE_USERVSDASHBOARD = @"SELECT
                            UVD.USERVSDASHBOARDID AS UserVsDashboardId,
                            UVD.USERID AS UserId,
                            UVD.DASHBOARDID AS DashboardId,
                            D.DASHBOARDCODE AS DashboardCode,
                            D.DASHBOARDNAME AS DashboardName,
                            UVD.SLNO AS SlNo,
                            UVD.ISPINNED AS IsPinned
                            FROM MUSERVSDASHBOARD UVD
                            LEFT JOIN MUSER U ON UVD.USERID = U.USERID
                            LEFT JOIN MDASHBOARD D ON UVD.DASHBOARDID = D.DASHBOARDID
                            WHERE UVD.DASHBOARDID NOT IN (
                                SELECT DISTINCT RVD.DASHBOARDID
                                FROM MROLEVSDASHBOARD RVD
                                WHERE RVD.DASHBOARDID = UVD.DASHBOARDID
                            ) AND UVD.USERID IN (
                                SELECT DISTINCT U.USERID
                                FROM MUSER U
                                WHERE U.USERID = UVD.USERID
                                AND U.ROLEID = @roleid
                            );
                            ";
    }
}