namespace DXPDAL.Auth;

// Required indexes:
//   MDXPUSER: UX (EMAIL)
//   MDXPUSERPARTYROLE: (DXPUSERID, STATUS), (DXPPARTYID)
//   MDXPREFRESHTOKEN: UX (TOKENHASH), (DXPUSERID, EXPIRESON) WHERE REVOKEDON IS NULL
//
// DBMS-neutral: plain ANSI SQL — see PartyQB.cs header for the rationale.
public static class AuthQB
{
    // ── MDXPUSER ─────────────────────────────────────────────────────────────

    public const string GET_USER_BY_ID = @"
SELECT U.DXPUSERID           AS DxpUserId,
       U.FULLNAME             AS FullName,
       U.EMAIL                AS Email,
       U.MOBILE               AS Mobile,
       U.PASSWORDHASH         AS PasswordHash,
       U.MFAENABLED           AS MfaEnabled,
       U.MFASECRETENCRYPTED   AS MfaSecretEncrypted,
       U.LASTLOGINON          AS LastLoginOn,
       U.FAILEDLOGINCOUNT     AS FailedLoginCount,
       U.VERSION              AS Version,
       U.STATUS               AS Status,
       U.CREATEDBYID          AS CreatedById,
       U.CREATEDON            AS CreatedOn,
       U.MODIFIEDBYID         AS ModifiedById,
       U.MODIFIEDON           AS ModifiedOn
FROM   MDXPUSER U
WHERE  U.DXPUSERID = @DxpUserId
  AND  U.STATUS   != 3";

    public const string GET_USER_BY_EMAIL = @"
SELECT U.DXPUSERID           AS DxpUserId,
       U.FULLNAME             AS FullName,
       U.EMAIL                AS Email,
       U.MOBILE               AS Mobile,
       U.PASSWORDHASH         AS PasswordHash,
       U.MFAENABLED           AS MfaEnabled,
       U.MFASECRETENCRYPTED   AS MfaSecretEncrypted,
       U.LASTLOGINON          AS LastLoginOn,
       U.FAILEDLOGINCOUNT     AS FailedLoginCount,
       U.VERSION              AS Version,
       U.STATUS               AS Status,
       U.CREATEDBYID          AS CreatedById,
       U.CREATEDON            AS CreatedOn,
       U.MODIFIEDBYID         AS ModifiedById,
       U.MODIFIEDON           AS ModifiedOn
FROM   MDXPUSER U
WHERE  U.EMAIL  = @Email
  AND  U.STATUS != 3";

    public const string INSERT_USER = @"
INSERT INTO MDXPUSER
    (DXPUSERID, FULLNAME, EMAIL, MOBILE, PASSWORDHASH, MFAENABLED, MFASECRETENCRYPTED,
     FAILEDLOGINCOUNT, VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
VALUES
    (@DxpUserId, @FullName, @Email, @Mobile, @PasswordHash, @MfaEnabled, @MfaSecretEncrypted,
     @FailedLoginCount, @Version, @Status, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

    public const string UPDATE_LOGIN_STATS = @"
UPDATE MDXPUSER SET
    LASTLOGINON      = @LastLoginOn,
    FAILEDLOGINCOUNT = @FailedLoginCount,
    MODIFIEDBYID     = @ModifiedById,
    MODIFIEDON       = @ModifiedOn
WHERE  DXPUSERID = @DxpUserId";

    // ── MDXPUSERPARTYROLE ────────────────────────────────────────────────────

    // The context-switch picker — every active role this user holds, joined to the party's
    // display name/type. Not paged/filtered beyond DXPUSERID — a person's own relationship
    // count is small, so a plain join is enough (no IQueryBuilder/ISqlDialect ceremony needed).
    public const string GET_MY_CONTEXTS = @"
SELECT R.DXPUSERPARTYROLEID AS DxpUserPartyRoleId,
       R.DXPUSERID           AS DxpUserId,
       R.DXPPARTYID          AS DxpPartyId,
       R.ROLECODE            AS RoleCode,
       R.GRANTEDON           AS GrantedOn,
       R.GRANTEDBYID         AS GrantedById,
       R.STATUS              AS Status,
       R.CREATEDBYID         AS CreatedById,
       R.CREATEDON           AS CreatedOn,
       R.MODIFIEDBYID        AS ModifiedById,
       R.MODIFIEDON          AS ModifiedOn,
       P.LEGALNAME           AS PartyLegalName,
       P.PARTYTYPECODE       AS PartyTypeCode
FROM   MDXPUSERPARTYROLE R
INNER JOIN MDXPPARTY P ON P.DXPPARTYID = R.DXPPARTYID
WHERE  R.DXPUSERID = @DxpUserId
  AND  R.STATUS   != 3
  AND  P.STATUS   != 4
ORDER BY P.LEGALNAME";

    public const string GET_USER_PARTY_ROLE_BY_ID = @"
SELECT R.DXPUSERPARTYROLEID AS DxpUserPartyRoleId,
       R.DXPUSERID           AS DxpUserId,
       R.DXPPARTYID          AS DxpPartyId,
       R.ROLECODE            AS RoleCode,
       R.GRANTEDON           AS GrantedOn,
       R.GRANTEDBYID         AS GrantedById,
       R.STATUS              AS Status,
       R.CREATEDBYID         AS CreatedById,
       R.CREATEDON           AS CreatedOn,
       R.MODIFIEDBYID        AS ModifiedById,
       R.MODIFIEDON          AS ModifiedOn
FROM   MDXPUSERPARTYROLE R
WHERE  R.DXPUSERPARTYROLEID = @DxpUserPartyRoleId
  AND  R.DXPUSERID          = @DxpUserId
  AND  R.STATUS             != 3";

    public const string INSERT_USER_PARTY_ROLE = @"
INSERT INTO MDXPUSERPARTYROLE
    (DXPUSERPARTYROLEID, DXPUSERID, DXPPARTYID, ROLECODE, GRANTEDON, GRANTEDBYID,
     STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
VALUES
    (@DxpUserPartyRoleId, @DxpUserId, @DxpPartyId, @RoleCode, @GrantedOn, @GrantedById,
     @Status, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

    // ── MDXPREFRESHTOKEN ─────────────────────────────────────────────────────

    public const string GET_REFRESH_TOKEN_BY_HASH = @"
SELECT T.DXPREFRESHTOKENID  AS DxpRefreshTokenId,
       T.DXPUSERID           AS DxpUserId,
       T.DXPUSERPARTYROLEID  AS DxpUserPartyRoleId,
       T.DXPPARTYLINKID      AS DxpPartyLinkId,
       T.TOKENHASH           AS TokenHash,
       T.DEVICEINFO          AS DeviceInfo,
       T.ISSUEDON            AS IssuedOn,
       T.EXPIRESON           AS ExpiresOn,
       T.REVOKEDON           AS RevokedOn,
       T.REPLACEDBYTOKENID   AS ReplacedByTokenId,
       T.CREATEDBYID         AS CreatedById,
       T.CREATEDON           AS CreatedOn
FROM   MDXPREFRESHTOKEN T
WHERE  T.TOKENHASH = @TokenHash";

    // Used by GetChainDescendantsAsync's one-PK-lookup-per-hop walk (mirrors
    // EntitlementDAL.Implementations.ClientRefreshTokenDAL's identical pattern) — a recursive CTE
    // would need dialect-specific syntax (Postgres WITH RECURSIVE vs. SQL Server WITH), which
    // would break this module's single-SQL-text-for-both-engines convention.
    public const string GET_REFRESH_TOKEN_BY_ID = @"
SELECT T.DXPREFRESHTOKENID  AS DxpRefreshTokenId,
       T.DXPUSERID           AS DxpUserId,
       T.DXPUSERPARTYROLEID  AS DxpUserPartyRoleId,
       T.DXPPARTYLINKID      AS DxpPartyLinkId,
       T.TOKENHASH           AS TokenHash,
       T.DEVICEINFO          AS DeviceInfo,
       T.ISSUEDON            AS IssuedOn,
       T.EXPIRESON           AS ExpiresOn,
       T.REVOKEDON           AS RevokedOn,
       T.REPLACEDBYTOKENID   AS ReplacedByTokenId,
       T.CREATEDBYID         AS CreatedById,
       T.CREATEDON           AS CreatedOn
FROM   MDXPREFRESHTOKEN T
WHERE  T.DXPREFRESHTOKENID = @DxpRefreshTokenId";

    public const string INSERT_REFRESH_TOKEN = @"
INSERT INTO MDXPREFRESHTOKEN
    (DXPREFRESHTOKENID, DXPUSERID, DXPUSERPARTYROLEID, DXPPARTYLINKID, TOKENHASH, DEVICEINFO,
     ISSUEDON, EXPIRESON, CREATEDBYID, CREATEDON)
VALUES
    (@DxpRefreshTokenId, @DxpUserId, @DxpUserPartyRoleId, @DxpPartyLinkId, @TokenHash, @DeviceInfo,
     @IssuedOn, @ExpiresOn, @CreatedById, @CreatedOn)";

    public const string REVOKE_REFRESH_TOKEN = @"
UPDATE MDXPREFRESHTOKEN SET
    REVOKEDON         = @RevokedOn,
    REPLACEDBYTOKENID = @ReplacedByTokenId
WHERE  DXPREFRESHTOKENID = @DxpRefreshTokenId";
}