namespace EntitlementDAL.QueryBuilders;

// Required indexes:
//   MENTITLEMENTCLIENTUSER: UX (CLIENTID, EMAIL) — per-client-unique email, not global
public static class ClientUserQB
{
    private const string COLUMNS = @"
       CLIENTUSERID     AS ClientUserId,
       CLIENTID         AS ClientId,
       EMAIL            AS Email,
       FULLNAME         AS FullName,
       PASSWORDHASH     AS PasswordHash,
       ROLE             AS Role,
       FAILEDLOGINCOUNT AS FailedLoginCount,
       LASTLOGINON      AS LastLoginOn,
       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_ID = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTCLIENTUSER
WHERE  CLIENTUSERID = @ClientUserId";

    public const string GET_BY_CLIENT_AND_EMAIL = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTCLIENTUSER
WHERE  CLIENTID = @ClientId
  AND  EMAIL    = @Email";

    public const string INSERT = @"
INSERT INTO MENTITLEMENTCLIENTUSER
    (CLIENTUSERID, CLIENTID, EMAIL, FULLNAME, PASSWORDHASH, ROLE, FAILEDLOGINCOUNT, LASTLOGINON,
     VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
VALUES
    (@ClientUserId, @ClientId, @Email, @FullName, @PasswordHash, @Role, @FailedLoginCount, @LastLoginOn,
     @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    public const string UPDATE_FAILED_LOGIN_COUNT = @"
UPDATE MENTITLEMENTCLIENTUSER SET
    FAILEDLOGINCOUNT = @FailedLoginCount,
    MODIFIEDBYID     = @ModifiedById,
    MODIFIEDON       = @ModifiedOn
WHERE  CLIENTUSERID = @ClientUserId";

    public const string UPDATE_STATUS = @"
UPDATE MENTITLEMENTCLIENTUSER SET
    STATUS       = @Status,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE  CLIENTUSERID = @ClientUserId";

    public const string UPDATE_LAST_LOGIN_ON = @"
UPDATE MENTITLEMENTCLIENTUSER SET
    LASTLOGINON  = @LastLoginOn,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE  CLIENTUSERID = @ClientUserId";

    public const string UPDATE_PASSWORD_HASH = @"
UPDATE MENTITLEMENTCLIENTUSER SET
    PASSWORDHASH = @PasswordHash,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE  CLIENTUSERID = @ClientUserId";

    public const string UPDATE_STATUS_AND_FAILED_LOGIN_COUNT = @"
UPDATE MENTITLEMENTCLIENTUSER SET
    STATUS           = @Status,
    FAILEDLOGINCOUNT = @FailedLoginCount,
    MODIFIEDBYID     = @ModifiedById,
    MODIFIEDON       = @ModifiedOn
WHERE  CLIENTUSERID = @ClientUserId";
}