namespace FrameworkDAL.Query.EIPConversation.EIPOtp
{
    public static class EIPOtpQB
    {
        // Supersede any still-pending record for this user+capability before issuing a new
        // OTP — at most one PENDING record per (tenant, user, capability) at a time.
        public const string SUPERSEDE_PENDING = @"
            UPDATE TEIPOTPRECORD
            SET    OTPSTATUS  = 3, -- Expired
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  TENANTID       = @TenantId
              AND  USERIDENTIFIER = @UserIdentifier
              AND  CAPABILITYCODE = @CapabilityCode
              AND  OTPSTATUS      = 1;";

        public const string INSERT_OTP_RECORD = @"
            INSERT INTO TEIPOTPRECORD
                (TENANTID, USERIDENTIFIER, CAPABILITYCODE, OTPHASH, EXPIRESON, ATTEMPTCOUNT, OTPSTATUS,
                 VERSION, STATUS, SORTORDER, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@TenantId, @UserIdentifier, @CapabilityCode, @OtpHash, @ExpiresOn, 0, 1,
                 0, 1, 9999, 5, @CreatedById, GETDATE(), @ModifiedById, GETDATE());
            SELECT CAST(SCOPE_IDENTITY() AS INT);";

        // Most recent unexpired pending record for this user+capability.
        public const string GET_PENDING = @"
            SELECT TOP 1
                OTPRECORDID     AS OtpRecordId,
                TENANTID        AS TenantId,
                USERIDENTIFIER  AS UserIdentifier,
                CAPABILITYCODE  AS CapabilityCode,
                OTPHASH         AS OtpHash,
                EXPIRESON       AS ExpiresOn,
                VERIFIEDON      AS VerifiedOn,
                ATTEMPTCOUNT    AS AttemptCount,
                OTPSTATUS       AS OtpStatus
            FROM TEIPOTPRECORD
            WHERE TENANTID       = @TenantId
              AND USERIDENTIFIER = @UserIdentifier
              AND CAPABILITYCODE = @CapabilityCode
              AND OTPSTATUS      = 1
              AND EXPIRESON      > GETUTCDATE()
            ORDER BY OTPRECORDID DESC;";

        public const string MARK_VERIFIED = @"
            UPDATE TEIPOTPRECORD
            SET    OTPSTATUS    = 2, -- Verified
                   VERIFIEDON   = GETUTCDATE(),
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  OTPRECORDID = @OtpRecordId;";

        // Increments the attempt counter; once it reaches the caller-supplied limit the
        // record is marked Failed(4) so a fresh OTP must be requested instead of allowing
        // unlimited guesses against the same code.
        public const string INCREMENT_ATTEMPT = @"
            UPDATE TEIPOTPRECORD
            SET    ATTEMPTCOUNT = ATTEMPTCOUNT + 1,
                   OTPSTATUS    = CASE WHEN ATTEMPTCOUNT + 1 >= @MaxAttempts THEN 4 ELSE OTPSTATUS END,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  OTPRECORDID = @OtpRecordId;";
    }
}