using AccountsDAL.CustomCode.WarehouseFact;
using AccountsDAL.DTO.WarehouseFact;
using GB5Shared.DaprCache;
using GB5Shared.DTO.Framework.AutoNumber;
using GB5Shared.DTO.Framework.HybridCache;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using System.Text.RegularExpressions;
using static GB5Shared.GB5Constant.Constant;

namespace AccountsBLL.WarehouseFact
{
    // Admin CRUD for MWAREHOUSEFACT — see the GB5 Data Warehouse developer guide section 09.4
    // (lowest urgency; facts are added very infrequently). Key BLL rule enforced here:
    // FACTTABLENAME must be validated against ^[A-Z0-9_]+$ — the same allowlist
    // WarehouseFactPostingDAL already applies before substituting this value into DELETE/INSERT
    // SQL text — so a bad value is rejected here at save-time, never silently accepted and only
    // discovered the first time PostFact runs against it.
    public class WarehouseFactBLL : IWarehouseFactBLL
    {
        private const string FactEntityCode = "WAREHOUSEFACT";
        private static readonly Regex SafeIdentifier = new(@"^[A-Z0-9_]+$", RegexOptions.Compiled);

        private readonly IWarehouseFactDAL _WarehouseFactDAL;
        private readonly AutoNumber _AutoNumber;
        private readonly IValidation _Validation;
        private readonly KeyInvalidate _KeyInvalidate;

        public WarehouseFactBLL(
            IWarehouseFactDAL WarehouseFactDAL, AutoNumber AutoNumber,
            IValidation Validation, KeyInvalidate KeyInvalidate)
        {
            _WarehouseFactDAL = WarehouseFactDAL;
            _AutoNumber = AutoNumber;
            _Validation = Validation;
            _KeyInvalidate = KeyInvalidate;
        }

        public async Task<string> GetWarehouseFact(int FactId, LoginDTO LoginDTO)
        {
            try
            {
                return await _WarehouseFactDAL.GetWarehouseFact(FactId, LoginDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetWarehouseFactList(LoginDTO LoginDTO)
        {
            try
            {
                return await _WarehouseFactDAL.GetWarehouseFactList(LoginDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> SaveWarehouseFact(WarehouseFactDTO WarehouseFactDTO, LoginDTO LoginDTO)
        {
            if (WarehouseFactDTO == null) throw new ArgumentNullException(nameof(WarehouseFactDTO));

            try
            {
                await ValidateFactAsync(WarehouseFactDTO, LoginDTO).ConfigureAwait(false);

                WarehouseFactDTO.FactModifiedById = LoginDTO.UserId;
                WarehouseFactDTO.FactModifiedOn = DateTime.UtcNow;

                int initialId = WarehouseFactDTO.FactId;
                if (initialId == 0)
                {
                    int count = 1;
                    AutoNumberDTO autoNumberDTO = await _AutoNumber.GetNumberAsync(count, FactEntityCode, LoginDTO);
                    WarehouseFactDTO.FactId = autoNumberDTO.StartNumber;
                    WarehouseFactDTO.FactCreatedById = LoginDTO.UserId;
                    WarehouseFactDTO.FactCreatedOn = DateTime.UtcNow;

                    try
                    {
                        await _WarehouseFactDAL.SaveWarehouseFact(WarehouseFactDTO, LoginDTO);
                    }
                    catch
                    {
                        await _AutoNumber.RollbackAutoNumber(FactEntityCode, WarehouseFactDTO.FactId, LoginDTO);
                        throw;
                    }

                    await InvalidateCacheAsync(WarehouseFactDTO.FactId, LoginDTO).ConfigureAwait(false);
                    return $"{SuccessResponse.SaveSuccessMessage} {WarehouseFactDTO.FactId}";
                }
                else
                {
                    await _WarehouseFactDAL.UpdateWarehouseFact(WarehouseFactDTO, LoginDTO);

                    await InvalidateCacheAsync(WarehouseFactDTO.FactId, LoginDTO).ConfigureAwait(false);
                    return $"{SuccessResponse.UpdateSuccessMessage} {WarehouseFactDTO.FactId}";
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> DeleteWarehouseFact(int FactId, LoginDTO LoginDTO)
        {
            var result = await _WarehouseFactDAL.DeleteWarehouseFact(FactId, LoginDTO);
            await InvalidateCacheAsync(FactId, LoginDTO).ConfigureAwait(false);
            return result;
        }

        private async Task InvalidateCacheAsync(int factId, LoginDTO loginDTO)
        {
            var cacheKey = CacheKeyGenerator.KeyGeneration(
                factId, EntityConstant.OBJECTWAREHOUSEFACT, CacheKeyLevel.CLIENT_LEVEL, string.Empty, loginDTO);
            await _KeyInvalidate.AllInvalidateCache(cacheKey).ConfigureAwait(false);
        }

        private async Task ValidateFactAsync(WarehouseFactDTO dto, LoginDTO loginDTO)
        {
            await _Validation.NotEmpty(dto.FactCode, nameof(dto.FactCode));
            await _Validation.NotEmpty(dto.FactName, nameof(dto.FactName));
            await _Validation.NotEmpty(dto.FactTableName, nameof(dto.FactTableName));

            // Key BLL rule from the developer guide section 09.4: FACTTABLENAME must be validated
            // against ^[A-Z0-9_]+$ before saving — same allowlist WarehouseFactPostingDAL applies
            // right before substituting this value into DELETE/INSERT SQL text.
            var factTableName = dto.FactTableName.ToUpperInvariant();
            if (!SafeIdentifier.IsMatch(factTableName))
                throw new InvalidOperationException(
                    $"FactTableName '{dto.FactTableName}' is not a safe SQL identifier (expected [A-Z0-9_]+).");
            dto.FactTableName = factTableName;

            var tableExists = await _WarehouseFactDAL.CheckTableExists(factTableName, loginDTO).ConfigureAwait(false);
            if (!tableExists)
                throw new InvalidOperationException(
                    $"Table '{factTableName}' does not exist — create it via migration before registering this fact.");

            // Defaults matching MWAREHOUSEFACT's own DDL defaults (DF_MWAREHOUSEFACT_DATEDIMCOLUMN
            // = 'DATEID', DF_MWAREHOUSEFACT_OUDIMCOLUMN = 'OUID') when the caller leaves them blank.
            if (string.IsNullOrWhiteSpace(dto.DateDimColumn)) dto.DateDimColumn = "DATEID";
            if (string.IsNullOrWhiteSpace(dto.OuDimColumn)) dto.OuDimColumn = "OUID";

            if (!SafeIdentifier.IsMatch(dto.DateDimColumn.ToUpperInvariant()))
                throw new InvalidOperationException($"DateDimColumn '{dto.DateDimColumn}' is not a safe SQL identifier.");
            if (!SafeIdentifier.IsMatch(dto.OuDimColumn.ToUpperInvariant()))
                throw new InvalidOperationException($"OuDimColumn '{dto.OuDimColumn}' is not a safe SQL identifier.");
            dto.DateDimColumn = dto.DateDimColumn.ToUpperInvariant();
            dto.OuDimColumn = dto.OuDimColumn.ToUpperInvariant();

            if (dto.TenantId == 0) dto.TenantId = -1; // -1 = shared across tenants, per DIMOU/DIMPARTY convention

            // UK_MWAREHOUSEFACT_FACTCODE enforces one row per (FACTCODE, TENANTID) at the DB
            // level, but checked here first for a clear message instead of a raw constraint error.
            var existingFactId = await _WarehouseFactDAL
                .GetExistingFactIdForCode(dto.FactCode, dto.TenantId, loginDTO).ConfigureAwait(false);
            if (existingFactId.HasValue && existingFactId.Value != dto.FactId)
                throw new InvalidOperationException(
                    $"FactCode '{dto.FactCode}' already registered for TenantId {dto.TenantId} (FactId {existingFactId.Value}).");
        }
    }
}