using AccountsDAL.CustomCode.WarehouseMeasure;
using AccountsDAL.DTO.WarehouseMeasure;
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.WarehouseMeasure
{
    // Admin CRUD for MWAREHOUSEMEASURE — see the GB5 Data Warehouse developer guide section 09.3.
    // Key BLL rule enforced here: COLUMNNAME must already exist as a physical column on the fact
    // table's own DDL (checked via INFORMATION_SCHEMA.COLUMNS) before Save — the guide flags this
    // as the check most likely to be skipped, since nothing else in the posting engine validates it
    // until the first PostFact run silently produces a wrong/empty column.
    public class WarehouseMeasureBLL : IWarehouseMeasureBLL
    {
        private const string MeasureEntityCode = "WAREHOUSEMEASURE";
        private static readonly Regex SafeIdentifier = new(@"^[A-Z0-9_]+$", RegexOptions.Compiled);

        private readonly IWarehouseMeasureDAL _WarehouseMeasureDAL;
        private readonly AutoNumber _AutoNumber;
        private readonly IValidation _Validation;
        private readonly KeyInvalidate _KeyInvalidate;

        public WarehouseMeasureBLL(
            IWarehouseMeasureDAL WarehouseMeasureDAL, AutoNumber AutoNumber,
            IValidation Validation, KeyInvalidate KeyInvalidate)
        {
            _WarehouseMeasureDAL = WarehouseMeasureDAL;
            _AutoNumber = AutoNumber;
            _Validation = Validation;
            _KeyInvalidate = KeyInvalidate;
        }

        public async Task<string> GetWarehouseMeasure(int MeasureId, LoginDTO LoginDTO)
        {
            try
            {
                return await _WarehouseMeasureDAL.GetWarehouseMeasure(MeasureId, LoginDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

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

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

            try
            {
                await ValidateMeasureAsync(WarehouseMeasureDTO, LoginDTO).ConfigureAwait(false);

                WarehouseMeasureDTO.MeasureModifiedById = LoginDTO.UserId;
                WarehouseMeasureDTO.MeasureModifiedOn = DateTime.UtcNow;

                int initialId = WarehouseMeasureDTO.MeasureId;
                if (initialId == 0)
                {
                    int count = 1;
                    AutoNumberDTO autoNumberDTO = await _AutoNumber.GetNumberAsync(count, MeasureEntityCode, LoginDTO);
                    WarehouseMeasureDTO.MeasureId = autoNumberDTO.StartNumber;
                    WarehouseMeasureDTO.MeasureCreatedById = LoginDTO.UserId;
                    WarehouseMeasureDTO.MeasureCreatedOn = DateTime.UtcNow;

                    try
                    {
                        await _WarehouseMeasureDAL.SaveWarehouseMeasure(WarehouseMeasureDTO, LoginDTO);
                    }
                    catch
                    {
                        await _AutoNumber.RollbackAutoNumber(MeasureEntityCode, WarehouseMeasureDTO.MeasureId, LoginDTO);
                        throw;
                    }

                    await InvalidateCacheAsync(WarehouseMeasureDTO.MeasureId, LoginDTO).ConfigureAwait(false);
                    return $"{SuccessResponse.SaveSuccessMessage} {WarehouseMeasureDTO.MeasureId}";
                }
                else
                {
                    await _WarehouseMeasureDAL.UpdateWarehouseMeasure(WarehouseMeasureDTO, LoginDTO);

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

        public async Task<string> DeleteWarehouseMeasure(int MeasureId, LoginDTO LoginDTO)
        {
            var result = await _WarehouseMeasureDAL.DeleteWarehouseMeasure(MeasureId, LoginDTO);
            await InvalidateCacheAsync(MeasureId, LoginDTO).ConfigureAwait(false);
            return result;
        }

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

        private async Task ValidateMeasureAsync(WarehouseMeasureDTO dto, LoginDTO loginDTO)
        {
            if (dto.FactId <= 0)
                throw new InvalidOperationException("FactId is required.");

            await _Validation.NotEmpty(dto.MeasureCode, nameof(dto.MeasureCode));
            await _Validation.NotEmpty(dto.MeasureName, nameof(dto.MeasureName));
            await _Validation.NotEmpty(dto.ColumnName, nameof(dto.ColumnName));

            // Same allowlist discipline as WarehouseFactPostingDAL/WarehousePostingRuleQB — a typo'd
            // COLUMNNAME must fail loudly here, not silently build broken SQL downstream in the
            // posting engine.
            var columnName = dto.ColumnName.ToUpperInvariant();
            if (!SafeIdentifier.IsMatch(columnName))
                throw new InvalidOperationException(
                    $"ColumnName '{dto.ColumnName}' is not a safe SQL identifier (expected [A-Z0-9_]+).");
            dto.ColumnName = columnName;

            // Key BLL rule from the developer guide section 09.3: COLUMNNAME must already exist as
            // a physical column in the fact table's DDL — catch a missing-migration mistake here,
            // not at the first PostFact run.
            var factTableName = await _WarehouseMeasureDAL.GetFactTableNameForMeasureCheck(dto.FactId, loginDTO)
                .ConfigureAwait(false);
            if (string.IsNullOrEmpty(factTableName))
                throw new InvalidOperationException($"No active MWAREHOUSEFACT row found for FactId {dto.FactId}.");
            if (!SafeIdentifier.IsMatch(factTableName))
                throw new InvalidOperationException(
                    $"'{factTableName}' is not a safe SQL identifier — check the MWAREHOUSEFACT.FACTTABLENAME value.");

            var columnExists = await _WarehouseMeasureDAL
                .CheckColumnExistsOnTable(factTableName, columnName, loginDTO).ConfigureAwait(false);
            if (!columnExists)
                throw new InvalidOperationException(
                    $"Column '{columnName}' does not exist on fact table '{factTableName}' — add it via migration before creating this measure.");

            // UK_MWAREHOUSEMEASURE_FACTCOLUMN enforces one row per (FACTID, COLUMNNAME) at the DB
            // level, but checked here first for a clear message instead of a raw constraint error.
            var existingMeasureId = await _WarehouseMeasureDAL
                .GetExistingMeasureIdForColumn(dto.FactId, columnName, loginDTO).ConfigureAwait(false);
            if (existingMeasureId.HasValue && existingMeasureId.Value != dto.MeasureId)
                throw new InvalidOperationException(
                    $"FactId {dto.FactId} already has a measure for column '{columnName}' (MeasureId {existingMeasureId.Value}).");
        }
    }
}