using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FirebaseAdmin.Auth.Multitenancy;
using FrameworkDAL.Query.DocumentSet;
using FrameworkDAL.Query.UserGroup;
using Google.Api.Gax;
using Google.Protobuf.Compiler;
using Microsoft.Data.SqlClient;
using Org.BouncyCastle.Utilities.Collections;

namespace FrameworkDAL.Query.ECMRights
{
    public static class ECMRightsQB
    {
        public const string GET_ECMRIGHTS = @"SELECT
                                                    -- Parent (ECMRightsDTO)

                                                    ER.ECMRIGHTSID          AS ECMRightsId,
                                                    ER.USERTYPE             AS ECMRightsUserType,
                                                    ER.RIGHTSFORTYPE        AS ECMRightsRightsForType,
                                                    ER.USERID               AS ECMRightsUserId,
                                                    U.USERCODE              AS UserCode,
                                                    U.USERNAME              AS UserName,
                                                    ER.USERGROUPID          AS ECMRightsUserGroupId,
                                                    UG.USERGROUPCODE        AS UserGroupCode,
                                                    UG.USERGROUPNAME        AS UserGroupName,
                                                    ER.ROLEID               AS ECMRightsRoleId,
                                                    R.ROLECODE              AS RoleCode,
                                                    R.ROLENAME              AS RoleName,
                                                    ER.SPACEID              AS ECMRightsSpaceId,
                                                    GCM.GCMCODE             AS SpaceCode,
                                                    GCM.GCMNAME             AS SpaceName,
                                                    ER.DOCUMENTSETID        AS ECMRightsDocumentSetId,
                                                    DS.DOCUMENTSETCODE      AS DocumentSetCode,
                                                    DS.DOCUMENTSETNAME      AS DocumentSetName,
                                                    ER.OBJECTTYPEID         AS ECMRightsObjectTypeId,
                                                    E.ENTITYCODE            AS ObjectTypeCode,
                                                    E.ENTITYNAME            AS ObjectTypeName,
                                                    ER.SORTORDER            AS ECMRightsSortOrder,
                                                    ER.STATUS               AS ECMRightsStatus,
                                                    ER.VERSION              AS ECMRightsVersion,
                                                    ER.SOURCETYPE           AS ECMRightsSourceType,
                                                    ER.CREATEDBYID          AS ECMRightsCreatedById,
                                                    ER.CREATEDON            AS ECMRightsCreatedOn,
                                                    ER.MODIFIEDBYID         AS ECMRightsModifiedById,
                                                    ER.MODIFIEDON           AS ECMRightsModifiedOn,
                                                    ER.TENANTID             AS ECMRightsTenantId,

                                                    -- Child (ECMRightsDetailDTO)

                                                    RD.ECMRIGHTSDETAILID    AS ECMRightsDetailId,
                                                    RD.ECMRIGHTSID          AS ECMRightsId,
                                                    RD.SLNO                 AS ECMRightsDetailSLNO,
                                                    RD.SECURITYGROUPID      AS SecurityGroupId,
                                                    SG.SECURITYGROUPCODE    AS SecurityGroupCode,
                                                    SG.SECURITYGROUPNAME    AS SecurityGroupName,
                                                    RD.SECURITYMARKID       AS SecurityMarkId,  -- Only ID returned, no Code/Name here
                                                    RD.ECMROLEID            AS ECMRoleId,
                                                    CR.ECMROLECODE          AS ECMRoleCode,
                                                    CR.ECMROLENAME          AS ECMRoleName

                                                FROM MECMRIGHTS ER
                                                -- Foreign Key Joins
                                                LEFT JOIN MUSER              U   ON ER.USERID        = U.USERID
                                                LEFT JOIN MUSERGROUP         UG  ON ER.USERGROUPID   = UG.USERGROUPID
                                                LEFT JOIN MROLE              R   ON ER.ROLEID        = R.ROLEID
                                                LEFT JOIN MGCM               GCM ON ER.SPACEID       = GCM.GCMID
                                                LEFT JOIN MDOCUMENTSET       DS  ON ER.DOCUMENTSETID = DS.DOCUMENTSETID
                                                LEFT JOIN MENTITY            E   ON ER.OBJECTTYPEID  = E.ENTITYID
                                                -- ECMRights Detail Joins
                                                LEFT JOIN MECMRIGHTSDETAIL   RD  ON ER.ECMRIGHTSID     = RD.ECMRIGHTSID
                                                LEFT JOIN MSECURITYGROUP     SG  ON RD.SECURITYGROUPID = SG.SECURITYGROUPID
                                                LEFT JOIN MSECURITYGROUPDETAIL SM ON RD.SECURITYMARKID  = SM.SECURITYGROUPDETAILID
                                                LEFT JOIN MECMROLE           CR  ON RD.ECMROLEID       = CR.ECMROLEID
                                                WHERE ER.ECMRIGHTSID = @ecmrightsid;";

        public const string SAVE_ECMRIGHTS = @"INSERT INTO MECMRIGHTS (
                                    ECMRIGHTSID, USERTYPE, RIGHTSFORTYPE, USERID, USERGROUPID, ROLEID, SPACEID, DOCUMENTSETID, OBJECTTYPEID,
                                    SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID
                                )
                                VALUES (
                                    @ECMRightsId, @ECMRightsUserType, @ECMRightsRightsForType, @ECMRightsUserId, @ECMRightsUserGroupId, @ECMRightsRoleId,
                                    @ECMRightsSpaceId, @ECMRightsDocumentSetId, @ECMRightsObjectTypeId,
                                    @ECMRightsSortOrder, @ECMRightsStatus, @ECMRightsVersion,
                                    @ECMRightsCreatedById, @ECMRightsCreatedOn,
                                    @ECMRightsModifiedById, @ECMRightsModifiedOn,
                                    @ECMRightsSourceType, @ECMRightsTenantId
                                );";

        public const string UPDATE_ECMRIGHTS = @"UPDATE MECMRIGHTS
                                                SET
                                                    USERTYPE = @ECMRightsUserType,
                                                    RIGHTSFORTYPE = @ECMRightsRightsForType,
                                                    USERID = @ECMRightsUserId,
                                                    USERGROUPID = @ECMRightsUserGroupId,
                                                    ROLEID = @ECMRightsRoleId,
                                                    SPACEID = @ECMRightsSpaceId,
                                                    DOCUMENTSETID = @ECMRightsDocumentSetId,
                                                    OBJECTTYPEID = @ECMRightsObjectTypeId,
                                                    SORTORDER = @ECMRightsSortOrder,
                                                    STATUS = @ECMRightsStatus,
                                                    VERSION = @ECMRightsVersion,
                                                    MODIFIEDBYID = @ECMRightsModifiedById,
                                                    MODIFIEDON = @ECMRightsModifiedOn,
                                                    SOURCETYPE = @ECMRightsSourceType
                                                WHERE ECMRIGHTSID = @ECMRightsId;";

        public const string GET_SELECTLIST_ECMRIGHTS = @"WITH PagedECMRights AS (
                                                    SELECT
                                                        ER.ECMRIGHTSID AS Id,
                                                        ER.ECMRIGHTSCODE AS Code,
                                                        ER. AS Name,
                                                        ROW_NUMBER() OVER (ORDER BY ER.ECMRIGHTSID) AS RowNum
                                                    FROM MECMRIGHTS ER
                                                    WHERE ER.STATUS = 1
                                                )
                                                SELECT
                                                    ECMRightsId,
                                                    ECMRightsUserType,
                                                    ECMRightsRightsForType
                                                FROM PagedECMRights
                                                WHERE RowNum BETWEEN (@firstnumber + 1) AND (@firstnumber + @maxresult);";

        public const string DELETE_ECMRIGHTS = @"DELETE FROM MECMRIGHTS
                                                WHERE ECMRIGHTSID = @ECMRightsId;";

    }
}