namespace EntitlementDAL.QueryBuilders;

// Required indexes:
//   MENTITLEMENTCLIENTUSERROLE: UX (CLIENTUSERID, ROLECODE)
public static class ClientUserRoleQB
{
    private const string COLUMNS = @"
       CLIENTUSERROLEID AS ClientUserRoleId,
       CLIENTUSERID     AS ClientUserId,
       ROLECODE         AS RoleCode,
       GRANTEDON        AS GrantedOn,
       GRANTEDBYID      AS GrantedById,
       VERSION          AS Version,
       STATUS           AS Status,
       SORTORDER        AS SortOrder,
       CREATEDBYID      AS CreatedById,
       CREATEDON        AS CreatedOn,
       MODIFIEDBYID     AS ModifiedById,
       MODIFIEDON       AS ModifiedOn,
       SOURCETYPE       AS SourceType,
       TENANTID         AS TenantId";

    public const string GET_BY_CLIENT_USER = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTCLIENTUSERROLE
WHERE  CLIENTUSERID = @ClientUserId
  AND  STATUS       = 1";

    // Used to build the JWT's Capabilities claim at login/refresh — plain string list, no DTO
    // mapping overhead.
    public const string GET_ACTIVE_ROLE_CODES = @"
SELECT ROLECODE
FROM   MENTITLEMENTCLIENTUSERROLE
WHERE  CLIENTUSERID = @ClientUserId
  AND  STATUS       = 1";

    public const string INSERT = @"
INSERT INTO MENTITLEMENTCLIENTUSERROLE
    (CLIENTUSERROLEID, CLIENTUSERID, ROLECODE, GRANTEDON, GRANTEDBYID,
     VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
VALUES
    (@ClientUserRoleId, @ClientUserId, @RoleCode, @GrantedOn, @GrantedById,
     @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    public const string REVOKE = @"
UPDATE MENTITLEMENTCLIENTUSERROLE SET
    STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE  CLIENTUSERID = @ClientUserId
  AND  ROLECODE     = @RoleCode
  AND  STATUS       = 1";
}