ESIM/EsimLao/Esim.Apis/Business/User/UserBusinessImpl.cs

using Common;
using Common.Constant;
using Common.Http;
using Common.Logic;
//using Esim.Apis.Logic;
using Esim.Apis.Singleton;
using Database;
using Database.Database;
using log4net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.VisualBasic;
using Newtonsoft.Json;
using System;
using System.Threading.Tasks;
using System.Xml.Serialization;

namespace Esim.Apis.Business
{
    public class UserBusinessImpl : IUserBusiness
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(
            typeof(UserBusinessImpl)
        );

        private ModelContext dbContext;
        IConfiguration configuration;

        public UserBusinessImpl(ModelContext _dbContext, IConfiguration _configuration)
        {
            dbContext = _dbContext;
            configuration = _configuration;
        }

        private string GetParameter(string key)
        {
            return configuration.GetSection(key).Value ?? "";
        }


        /// <summary>
        /// Request OTP to be sent to email
        /// </summary>
        public async Task<IActionResult> RequestOtp(HttpRequest httpRequest, RequestOtpReq request)
        {
            var url = httpRequest.Path;
            var json = JsonConvert.SerializeObject(request);
            log.Debug("URL: " + url + " => Request: " + json);
            try
            {
                if (string.IsNullOrEmpty(request.email))
                {
                    return DotnetLib.Http.HttpResponse.BuildResponse(
                        log,
                        url,
                        json,
                        CommonErrorCode.RequiredFieldMissing,
                        ConfigManager.Instance.GetConfigWebValue("EMAIL_REQUIRED"),
                        new { }
                    );
                }

                // Generate 6-digit OTP (fixed 111111 for test account abc@gmail.com)
                bool isTestAccount = request.email.ToLower() == "abc@gmail.com";
                string otpCode = isTestAccount ? "111111" : GenerateOtp();

                // Check if customer exists, if not create new
                var customer = dbContext.CustomerInfos
                    .Where(c => c.Email == request.email)
                    .FirstOrDefault();

                decimal? customerId = customer?.Id;

                if (customer == null)
                {
                    // Create new customer record - manually get ID from Oracle sequence
                    var newCustomerId = await Database.DbLogic.GenIdAsync(dbContext, "CUSTOMER_INFO_SEQ");
                    
                    // Extract name from email (part before @)
                    string emailUsername = request.email.Split('@')[0];
                    
                    var newCustomer = new CustomerInfo
                    {
                        Id = newCustomerId,
                        Email = request.email,
                        SurName = emailUsername,
                        LastName = emailUsername,
                        Status = true,
                        IsVerified = false,
                        CreatedDate = DateTime.Now,
                        LastUpdate = DateTime.Now
                    };
                    dbContext.CustomerInfos.Add(newCustomer);
                    await dbContext.SaveChangesAsync();
                    customerId = newCustomerId;
                }

                // Check if there's an existing OTP record for this email (ANY record, used or unused)
                int otpExpireMinutes = 5;
                int minSecondsBetweenRequests = 60; // Anti-spam: minimum 60 seconds between requests
                
                var existingOtp = dbContext.OtpVerifications
                    .Where(o => o.UserEmail == request.email)
                    .OrderByDescending(o => o.CreatedDate)
                    .FirstOrDefault();

                if (existingOtp != null)
                {
                    // Anti-spam check: prevent rapid OTP requests
                    var secondsSinceLastRequest = (DateTime.Now - (existingOtp.CreatedDate ?? DateTime.Now)).TotalSeconds;
                    if (secondsSinceLastRequest < minSecondsBetweenRequests)
                    {
                        var waitSeconds = (int)(minSecondsBetweenRequests - secondsSinceLastRequest);
                        log.Warn($"Spam prevention: OTP request too soon for {request.email}. Last request {secondsSinceLastRequest:F0}s ago.");
                        
                        return DotnetLib.Http.HttpResponse.BuildResponse(
                            log,
                            url,
                            json,
                            CommonErrorCode.OtpTooManyRequests,
                            $"Please wait {waitSeconds} seconds before requesting a new OTP.",
                            new { 
                                waitSeconds = waitSeconds,
                                canRequestAt = existingOtp.CreatedDate?.AddSeconds(minSecondsBetweenRequests)
                            }
                        );
                    }
                    
                    // UPDATE existing record - reuse for this email to prevent table bloat
                    existingOtp.OtpCode = otpCode;
                    existingOtp.ExpiredAt = DateTime.Now.AddMinutes(otpExpireMinutes);
                    existingOtp.AttemptCount = 0; // Reset attempt count
                    existingOtp.IsUsed = false;   // Reset to unused (allow reuse)
                    existingOtp.CustomerId = customerId; // Update customer ID if changed
                    existingOtp.CreatedDate = DateTime.Now; // Update to current time
                    
                    log.Info($"Updated existing OTP record ID={existingOtp.Id} for {request.email} (was {(existingOtp.IsUsed == true ? "used" : "unused")})");
                }
                else
                {
                    // INSERT new record only for first-time email
                    var otpId = (int)await Database.DbLogic.GenIdAsync(dbContext, "OTP_VERIFICATION_SEQ");
                    var otpVerification = new OtpVerification
                    {
                        Id = otpId,
                        CustomerId = customerId,
                        UserEmail = request.email,
                        OtpCode = otpCode,
                        OtpType = 1, // Login OTP
                        ExpiredAt = DateTime.Now.AddMinutes(otpExpireMinutes),
                        IsUsed = false,
                        AttemptCount = 0,
                        CreatedDate = DateTime.Now
                    };

                    dbContext.OtpVerifications.Add(otpVerification);
                    log.Info($"Created new OTP record for {request.email} (first time)");
                }

                await dbContext.SaveChangesAsync();

                // Skip email sending for test account
                if (!isTestAccount)
                {
                    // Add to MESSAGE_QUEUE for background email sending
                    // Resolve template content now so Worker only needs to send email
                    string lang = CommonLogic.GetLanguage(httpRequest, request.lang);
                    string templateCode = "OTP_LOGIN";

                    // Query template and get language-specific content
                    var template = dbContext.MessageTemplates
                        .FirstOrDefault(t => t.TemplateCode == templateCode && t.Status == true);

                    if (template == null)
                    {
                        log.Error($"Template '{templateCode}' not found in MESSAGE_TEMPLATE");
                        throw new Exception($"Email template '{templateCode}' not found");
                    }

                    // Get subject based on language (fallback to default column if _LO/_EN is null)
                    string emailSubject = lang == "en"
                        ? (template.SubjectEn ?? template.Subject ?? "")
                        : (template.SubjectLo ?? template.Subject ?? "");

                    // Get content based on language (fallback to default column if _LO/_EN is null)
                    string emailContent = lang == "en"
                        ? (template.ContentEn ?? template.Content ?? "")
                        : (template.ContentLo ?? template.Content ?? "");

                    // Replace placeholders in content
                    emailContent = emailContent
                        .Replace("{{OTP_CODE}}", otpCode)
                        .Replace("{{EXPIRE_MINUTES}}", otpExpireMinutes.ToString());

                    // Replace placeholders in subject (if any)
                    emailSubject = emailSubject
                        .Replace("{{OTP_CODE}}", otpCode)
                        .Replace("{{EXPIRE_MINUTES}}", otpExpireMinutes.ToString());
                    var emailMessageID = (int)await Database.DbLogic.GenIdAsync(dbContext, "MESSAGE_QUEUE_SEQ");
                    var emailMessage = new MessageQueue
                    {
                        Id = emailMessageID,
                        MessageType = 1, // Email
                        Recipient = request.email,
                        Subject = emailSubject,     // Pre-resolved subject
                        Content = emailContent,     // Pre-resolved content
                        Priority = true, // High priority
                        Status = 0, // Pending
                        ScheduledAt = DateTime.Now,
                        RetryCount = 0,
                        MaxRetry = 3,
                        CreatedBy = customerId,
                        CreatedDate = DateTime.Now
                    };

                    dbContext.MessageQueues.Add(emailMessage);
                    await dbContext.SaveChangesAsync();
                }

                log.Info($"OTP generated for {request.email}: {otpCode} - {(isTestAccount ? "Test account, no email sent" : "Email queued")}");

                //return DotnetLib.Http.HttpResponse.BuildResponse(
                //    log,
                //    url,
                //    json,
                //    CommonErrorCode.Success,
                //    ConfigManager.Instance.GetConfigWebValue("OTP_SENT_SUCCESS"),
                //    new
                //    {
                //        email = request.email,
                //        expireInSeconds = otpExpireMinutes * 60
                //    }
                //);

                return DotnetLib.Http.HttpResponse.BuildResponse(
                    log,
                    url,
                    json,
                    CommonErrorCode.Success,
                    ConfigManager.Instance.GetConfigWebValue("OTP_SENT_SUCCESS"),
                    new
                    {
                        email = request.email,
                        expireInSeconds = otpExpireMinutes * 60
                    }
                );
            }
            catch (Exception exception)
            {
                log.Error("Exception: ", exception);
            }
            return DotnetLib.Http.HttpResponse.BuildResponse(
                log,
                url,
                json,
                CommonErrorCode.SystemError,
                ConfigManager.Instance.GetConfigWebValue("SYSTEM_FAILURE"),
                new { }
            );
        }

        /// <summary>
        /// Verify OTP and complete login - return JWT token
        /// </summary>
        public async Task<IActionResult> VerifyOtp(HttpRequest httpRequest, VerifyOtpReq request)
        {
            var url = httpRequest.Path;
            var json = JsonConvert.SerializeObject(request);
            log.Debug("URL: " + url + " => Request: " + json);
            try
            {
                if (string.IsNullOrEmpty(request.email) || string.IsNullOrEmpty(request.otpCode))
                {
                    string lang = CommonLogic.GetLanguage(httpRequest, request.lang);
                    return DotnetLib.Http.HttpResponse.BuildResponse(
                        log,
                        url,
                        json,
                        CommonErrorCode.RequiredFieldMissing,
                        ConfigManager.Instance.GetConfigWebValue("EMAIL_OTP_REQUIRED", lang),
                        new { }
                    );
                }

                // Get language for response messages
                string responseLang = CommonLogic.GetLanguage(httpRequest, request.lang);

                // Find valid OTP
                var otpRecord = dbContext.OtpVerifications
                    .Where(o => o.UserEmail == request.email
                        && o.OtpCode == request.otpCode
                        && o.IsUsed == false
                        && o.ExpiredAt > DateTime.Now)
                    .OrderByDescending(o => o.CreatedDate)
                    .FirstOrDefault();

                if (otpRecord == null)
                {
                    // Check if OTP exists but expired or used
                    var anyOtp = dbContext.OtpVerifications
                        .Where(o => o.UserEmail == request.email && o.OtpCode == request.otpCode)
                        .FirstOrDefault();

                    if (anyOtp != null)
                    {
                        if (anyOtp.IsUsed == true)
                        {
                            return DotnetLib.Http.HttpResponse.BuildResponse(
                                log,
                                url,
                                json,
                                CommonErrorCode.OtpAlreadyUsed,
                                ConfigManager.Instance.GetConfigWebValue("OTP_ALREADY_USED", responseLang),
                                new { }
                            );
                        }
                        if (anyOtp.ExpiredAt <= DateTime.Now)
                        {
                            return DotnetLib.Http.HttpResponse.BuildResponse(
                                log,
                                url,
                                json,
                                CommonErrorCode.OtpExpired,
                                ConfigManager.Instance.GetConfigWebValue("OTP_EXPIRED", responseLang),
                                new { }
                            );
                        }
                    }

                    return DotnetLib.Http.HttpResponse.BuildResponse(
                        log,
                        url,
                        json,
                        CommonErrorCode.OtpInvalid,
                        ConfigManager.Instance.GetConfigWebValue("OTP_INVALID", responseLang),
                        new { }
                    );
                }

                // Mark OTP as used
                otpRecord.IsUsed = true;

                // Get customer info
                var customer = dbContext.CustomerInfos
                    .Where(c => c.Email == request.email)
                    .FirstOrDefault();

                if (customer == null)
                {
                    return DotnetLib.Http.HttpResponse.BuildResponse(
                        log,
                        url,
                        json,
                        CommonErrorCode.UserNotFound,
                        ConfigManager.Instance.GetConfigWebValue("USER_NOT_FOUND", responseLang),
                        new { }
                    );
                }

                // Update customer verification status
                customer.IsVerified = true;
                customer.LastLoginDate = DateTime.Now;
                customer.LastUpdate = DateTime.Now;

                // Generate JWT tokens
                int tokenExpireHours = 24;
                int refreshTokenExpireDays = 30;

                string accessToken = CommonLogic.GenToken(configuration, customer.Email ?? "", customer.Id.ToString() ?? "");
                string refreshToken = CommonLogic.GenRefreshToken(configuration, customer.Email ?? "");
                var expiresAt = DateTime.Now.AddHours(tokenExpireHours);
                var refreshExpiresAt = DateTime.Now.AddDays(refreshTokenExpireDays);

                // Revoke old tokens
                var oldTokens = dbContext.UserTokens
                    .Where(t => t.CustomerId == customer.Id && t.IsRevoked == false)
                    .ToList();

                foreach (var oldToken in oldTokens)
                {
                    oldToken.IsRevoked = true;
                }

                // Save new token
                var tokenId = (int)await Database.DbLogic.GenIdAsync(dbContext, "USER_TOKEN_SEQ");
                var userToken = new UserToken
                {
                    Id = tokenId,
                    CustomerId = customer.Id,
                    AccessToken = accessToken,
                    RefreshToken = refreshToken,
                    TokenType = "Bearer",
                    DeviceInfo = httpRequest.Headers["User-Agent"].ToString(),
                    IpAddress = GetClientIpAddress(httpRequest),
                    ExpiredAt = expiresAt,
                    RefreshExpiredAt = refreshExpiresAt,
                    IsRevoked = false,
                    CreatedDate = DateTime.Now,
                    LastUsed = DateTime.Now
                };

                dbContext.UserTokens.Add(userToken);
                await dbContext.SaveChangesAsync();

                return DotnetLib.Http.HttpResponse.BuildResponse(
                    log,
                    url,
                    json,
                    CommonErrorCode.Success,
                    ConfigManager.Instance.GetConfigWebValue("LOGIN_SUCCESS", responseLang),
                    new
                    {
                        userId = customer.Id,
                        email = customer.Email ?? "",
                        fullName = $"{customer.SurName} {customer.LastName}".Trim(),
                        avatarUrl = customer.AvatarUrl,
                        accessToken,
                        refreshToken,
                        expiresAt
                    }
                );
            }
            catch (Exception exception)
            {
                log.Error("Exception: ", exception);
            }
            return DotnetLib.Http.HttpResponse.BuildResponse(
                log,
                url,
                json,
                CommonErrorCode.SystemError,
                ConfigManager.Instance.GetConfigWebValue("SYSTEM_FAILURE"),
                new { }
            );
        }

        /// <summary>
        /// Generate 6-digit OTP code
        /// </summary>
        private string GenerateOtp()
        {
            using (var rng = System.Security.Cryptography.RandomNumberGenerator.Create())
            {
                var bytes = new byte[4];
                rng.GetBytes(bytes);
                var number = Math.Abs(BitConverter.ToInt32(bytes, 0)) % 1000000;
                return number.ToString("D6");
            }
        }

        /// <summary>
        /// Get client IP address
        /// </summary>
        private string GetClientIpAddress(HttpRequest httpRequest)
        {
            var ipAddress = httpRequest.Headers["X-Forwarded-For"].FirstOrDefault();
            if (string.IsNullOrEmpty(ipAddress))
            {
                ipAddress = httpRequest.HttpContext.Connection.RemoteIpAddress?.ToString();
            }
            return ipAddress ?? "Unknown";
        }

    }
}