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

namespace AdminDAL.Query.UserAccessRights
{
    public static class UserAccessRightsQB
    {

        public const string GET_USERACCESSACCESS_BASED_ON_USERID = @"SELECT 
                                                                uar.USERACCESSRIGHTSID AS UserAccessRightsId,
                                                                uar.SLNO AS UserAccessRightsSlno,
                                                                uar.TYPE AS UserAccessRightsType,
                                                                uar.NOOFPERIOD AS UserAccessRightsNoOfPeriod,

                                                                usr.USERID AS UserId,
                                                                usr.USERCODE AS UserCode,
                                                                usr.USERNAME AS UserName,
                                                                usr.THEMEOPTION AS UserThemeOption,
                                                                usr.THEMEMODE AS UserThemeMode,

                                                                ou.OUID AS OrganizationUnitId,
                                                                ou.ORGANIZATIONUNITCODE AS OrganizationUnitCode,
                                                                ou.ORGANIZATIONUNITNAME AS OrganizationUnitName,

                                                                oug.ORGANIZATIONGROUPID AS OrganizationGroupId,
                                                                oug.ORGANIZATIONGROUPCODE AS OrganizationGroupCode,
                                                                oug.ORGANIZATIONGROUPNAME AS OrganizationGroupName,

                                                                1 AS MySettingRequired
                                                            FROM 
                                                                MUSERACCESSRIGHTS uar
                                                            LEFT JOIN MUSER usr ON uar.USERID = usr.USERID
                                                            LEFT JOIN MORGANIZATIONUNIT ou ON uar.OUID = ou.OUID
                                                            LEFT JOIN MORGANIZATIONGROUP oug ON uar.OUGROUPID = oug.ORGANIZATIONGROUPID
                                                            WHERE 
                                                                usr.USERID = @userid;";



        public const string GET_PERIOD_CHECK_POSTGRESQL = @"SELECT
                                                            period.PERIODID   AS Id,
                                                            period.PERIODCODE AS Code,
                                                            period.PERIODNAME AS Name
                                                        FROM MPERIOD AS period
                                                        WHERE period.PERIODID <> -1
                                                        ORDER BY period.FROMDATE DESC
                                                        LIMIT 1;";

        public const string GET_PERIOD_CHECK = @"SELECT TOP (1)
                                                        period.PERIODID   AS Id,
                                                        period.PERIODCODE AS Code,
                                                        period.PERIODNAME AS Name
                                                    FROM MPERIOD AS period
                                                    WHERE period.PERIODID <> -1
                                                    ORDER BY period.FROMDATE DESC;";

        public const string GET_USERWORKPERIOD = @"select WORKPERIODID AS WorkPeriodId 
					                            from MUSER where USERID = @userid;";

        public const string GET_USERACCESSRIGHTS = @"SELECT
                                                        UAR.USERACCESSRIGHTSID           AS UserAccessRightsId,
                                                        UAR.USERID                       AS UserId,
                                                        U.USERNAME                       AS UserName,
                                                        U.USERCODE                       AS UserCode,
                                                        UAR.SLNO                         AS UserAccessRightsSlno,
                                                        UAR.TYPE                         AS UserAccessRightsType,
                                                        UAR.OUID                         AS OrganizationUnitId,
                                                        UAR.OUGROUPID                    AS OrganizationGroupId,
                                                        UAR.NOOFPERIOD                   AS UserAccessRightsNoOfPeriod,
                                                        UAR.NOOFREADPERIOD               AS UserAccessRightsNoOfReadPeriod,
                                                        OU.ORGANIZATIONUNITCODE          AS OrganizationUnitCode,
                                                        OU.ORGANIZATIONUNITNAME          AS OrganizationUnitName,
                                                        OG.ORGANIZATIONGROUPCODE         AS OrganizationGroupCode,
                                                        OG.ORGANIZATIONGROUPNAME         AS OrganizationGroupName
                                                    FROM MUSERACCESSRIGHTS UAR
                                                    LEFT JOIN MUSER U ON U.USERID = UAR.USERID
                                                    LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = UAR.OUID
                                                    LEFT JOIN MORGANIZATIONGROUP OG ON OG.ORGANIZATIONGROUPID = UAR.OUGROUPID
                                                    WHERE UAR.USERACCESSRIGHTSID = @useraccessrightsid;";

        // GetUserAccessRightsList: modern equivalent of the legacy GB4
        // Criteria.svc/List ObjectCode=USERACCESSRIGHTS&FirstNumber=&MaxResult=
        // contract. Callers post a CriteriaDTO (SectionCriteriaList/AttributesCriteriaList)
        // plus FirstNumber/MaxResult query params; UserAccessRightsDAL remaps whitelisted
        // FieldNames (see _filterableFields) before CriteriaBuilder.Build fills in
        // /*CRITERIA*/. FirstNumber is 1-based, matching the legacy contract — OFFSET
        // is computed as (FirstNumber - 1) by the DAL before this SQL runs.
        public const string GET_USERACCESSRIGHTS_LIST = @"SELECT
                                                        UAR.USERACCESSRIGHTSID           AS UserAccessRightsId,
                                                        UAR.USERID                       AS UserId,
                                                        U.USERNAME                       AS UserName,
                                                        U.USERCODE                       AS UserCode,
                                                        UAR.SLNO                         AS UserAccessRightsSlno,
                                                        UAR.TYPE                         AS UserAccessRightsType,
                                                        UAR.OUID                         AS OrganizationUnitId,
                                                        UAR.OUGROUPID                    AS OrganizationGroupId,
                                                        UAR.NOOFPERIOD                   AS UserAccessRightsNoOfPeriod,
                                                        UAR.NOOFREADPERIOD               AS UserAccessRightsNoOfReadPeriod,
                                                        OU.ORGANIZATIONUNITCODE          AS OrganizationUnitCode,
                                                        OU.ORGANIZATIONUNITNAME          AS OrganizationUnitName,
                                                        OG.ORGANIZATIONGROUPCODE         AS OrganizationGroupCode,
                                                        OG.ORGANIZATIONGROUPNAME         AS OrganizationGroupName
                                                    FROM MUSERACCESSRIGHTS UAR
                                                    LEFT JOIN MUSER U ON U.USERID = UAR.USERID
                                                    LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = UAR.OUID
                                                    LEFT JOIN MORGANIZATIONGROUP OG ON OG.ORGANIZATIONGROUPID = UAR.OUGROUPID
                                                    WHERE 1 = 1
                                                    /*CRITERIA*/
                                                    ORDER BY UAR.USERACCESSRIGHTSID
                                                    OFFSET @Offset ROWS FETCH NEXT @MaxResult ROWS ONLY;";

        public const string SAVE_USERACCESSRIGHTS = @"INSERT INTO MUSERACCESSRIGHTS
                                                    (
                                                        USERACCESSRIGHTSID,
                                                        USERID,
                                                        SLNO,
                                                        TYPE,
                                                        OUID,
                                                        OUGROUPID,
                                                        NOOFPERIOD,
                                                        NOOFREADPERIOD
                                                    )
                                                    VALUES
                                                    (
                                                        @UserAccessRightsId,
                                                        @UserId,
                                                        @UserAccessRightsSlno,
                                                        @UserAccessRightsType,  
                                                        @OrganizationUnitId,    
                                                        @OrganizationGroupId,   
                                                        @UserAccessRightsNoOfPeriod,
                                                        @UserAccessRightsNoOfReadPeriod
                                                    )";

        public const string UPDATE_USERACCESSRIGHTS = @"UPDATE MUSERACCESSRIGHTS
                                                SET
                                                    USERID = @UserId,
                                                    SLNO = @UserAccessRightsSlno,
                                                    TYPE = @UserAccessRightsType,
                                                    OUID = @OrganizationUnitId,
                                                    OUGROUPID = @OrganizationGroupId,
                                                    NOOFPERIOD = @UserAccessRightsNoOfPeriod,
                                                    NOOFREADPERIOD = @UserAccessRightsNoOfReadPeriod
                                                WHERE 
                                                    USERACCESSRIGHTSID = @UserAccessRightsId";

        public const string DELETE_USERACCESSRIGHTS = @"DELETE FROM MUSERACCESSRIGHTS
                                                WHERE USERACCESSRIGHTSID = @useraccessrightsid";

        public const string DELETE_USERACCESSACCESS_BASED_ON_USERID = @"DELETE FROM MUSERACCESSRIGHTS
                                                                        WHERE USERID = @userid;";

        public const string GET_SELECTLIST_OU = @"SELECT DISTINCT
                            OU.OUID AS Id,
                            OU.ORGANIZATIONUNITCODE AS Code,
                            OU.ORGANIZATIONUNITNAME AS Name,
                            OU.ORGANIZATIONUNITSHORTNAME AS ShortName,
                            OU.PERIODGROUP AS PeriodGroup
                            FROM MORGANIZATIONUNIT OU
                            INNER JOIN MUSERACCESSRIGHTS UAR ON UAR.OUID = OU.OUID
                            WHERE UAR.OUID <> -1 AND UAR.USERID = @UserId
                            ";
    }
}