using GB5Shared.DaprCache;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Qualifier;
using GB5Shared.EntityHandler;
using GB5Shared.GB5Library.Qualifier;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.ListQuery;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using Newtonsoft.Json;
using SwBLL.ChangeRequest;
using SwDAL.CustomCode.MetadataSync;
using SwDAL.DTO.ChangeRequest;
using SwDAL.DTO.MetadataSync;
using SwDAL.Enums;
using static GB5Shared.GB5Constant.Constant;

namespace SwBLL.MetadataSync;

public class MetadataSyncBLL : IMetadataSyncBLL
{
    private readonly IMetadataSyncDAL                                           _MetadataSyncDAL;
    private readonly AutoNumber                                                 _AutoNumber;
    private readonly IQueryExecutor                                             _QueryExecutor;
    private readonly KeyInvalidate                                              _KeyInvalidate;
    private readonly IListHandler<MetadataSyncListQuery, MetadataSyncListDTO>   _ListHandler;
    private readonly BaseEntityAppService<MetadataSyncDTO>                      _BaseEntityAppService;
    private readonly IChangeRequestBLL                                          _ChangeRequestBLL;

    public MetadataSyncBLL(
        IMetadataSyncDAL metadataSyncDAL,
        AutoNumber autoNumber,
        IQueryExecutor queryExecutor,
        KeyInvalidate keyInvalidate,
        IListHandler<MetadataSyncListQuery, MetadataSyncListDTO> listHandler,
        BaseEntityAppService<MetadataSyncDTO> baseEntityAppService,
        IChangeRequestBLL changeRequestBLL)
    {
        _MetadataSyncDAL      = metadataSyncDAL;
        _AutoNumber           = autoNumber;
        _QueryExecutor        = queryExecutor;
        _KeyInvalidate        = keyInvalidate;
        _ListHandler          = listHandler;
        _BaseEntityAppService = baseEntityAppService;
        _ChangeRequestBLL     = changeRequestBLL;
    }

    // ── Read — typed pass-through ─────────────────────────────────────────

    public async Task<MetadataSyncDTO?> GetById(int metaVersionId, LoginDTO loginDTO, CancellationToken ct)
        => await _MetadataSyncDAL.GetById(metaVersionId, loginDTO, ct).ConfigureAwait(false);

    public async Task<string> GetList(
        CriteriaDTO criteria, string? searchText, int pageOffset, int pageSize,
        LoginDTO loginDTO, CancellationToken ct = default)
    {
        var merged = CriteriaRouterHelper.WithSearchAndPaging(criteria, searchText, pageOffset, pageSize);
        var c      = CriteriaBinder.Bind<MetadataSyncListCriteria>(merged);
        var result = await _ListHandler
            .HandleAsync(new MetadataSyncListQuery(c), loginDTO, ct)
            .ConfigureAwait(false);
        return JsonConvert.SerializeObject(result);
    }

    public async Task<string> GetSelectListMetadataSync(
        int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        => await _MetadataSyncDAL.GetSelectListMetadataSync(firstNumber, maxResult, loginDTO)
               .ConfigureAwait(false);

    // ── Save (insert or update) ───────────────────────────────────────────

    public async Task<string> Save(MetadataSyncDTO dto, LoginDTO loginDTO, CancellationToken ct)
    {
        if (dto == null) throw new ArgumentNullException(nameof(dto));

        dto.TenantId = loginDTO.ClientId;

        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            bool isNew = dto.MetaVersionId == 0;

            if (isNew)
            {
                var auto         = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.SWMETADATASYNC, loginDTO);
                dto.MetaVersionId = auto.StartNumber;
                dto.MetaStatus   = 0; // Draft
            }

            await _BaseEntityAppService.ExecuteSaveAsync(
                EntityConstant.OBJECTSWMETADATASYNC,
                EventTypeConstant.SAVEMETADATASYNCEVENTTYPEID,
                dto,
                loginDTO,
                async tx =>
                {
                    _ = isNew
                        ? await _MetadataSyncDAL.SaveMetadataSync(dto, loginDTO, tx, ct)
                        : await _MetadataSyncDAL.UpdateMetadataSync(dto, loginDTO, tx, ct);
                    return dto.MetaVersionId;
                },
                null, -1, -1, Trans);

            await _QueryExecutor.CommitAsync(Trans);
            var keyGen = new CacheKeyGeneration();

            var cacheKey = keyGen.KeyGeneration(
                dto.MetaVersionId, EntityConstant.OBJECTSWMETADATASYNC,
                CacheKeyLevel.CLIENT_LEVEL, loginDTO);
            await _KeyInvalidate.AllInvalidateCache(cacheKey);

            return isNew
                ? $"{SuccessResponse.SaveSuccessMessage} {dto.MetaVersionId}"
                : $"{SuccessResponse.UpdateSuccessMessage} {dto.MetaVersionId}";
        }
        catch (Exception)
        {
            await _QueryExecutor.RollbackAsync(Trans);
            throw;
        }
    }

    // ── UpdateStatus ──────────────────────────────────────────────────────

    public async Task<string> UpdateStatus(int metaVersionId, byte status, LoginDTO loginDTO, CancellationToken ct)
    {
        var stub = new MetadataSyncDTO { MetaVersionId = metaVersionId, TenantId = loginDTO.ClientId };

        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            await _BaseEntityAppService.ExecuteSaveAsync(
             EntityConstant.OBJECTSWMETADATASYNC,
             EventTypeConstant.UPDATEMETADATASYNCEVENTTYPEID,
             stub,
             loginDTO,
             async tx =>
             {
                 await _MetadataSyncDAL.UpdateStatus(metaVersionId, status, loginDTO, tx, ct);
                 return metaVersionId; 
             },
             null, -1, -1, Trans);

            await _QueryExecutor.CommitAsync(Trans);
            var keyGen = new CacheKeyGeneration();

            var cacheKey = keyGen.KeyGeneration(
                metaVersionId, EntityConstant.OBJECTSWMETADATASYNC,
                CacheKeyLevel.CLIENT_LEVEL, loginDTO);
            await _KeyInvalidate.AllInvalidateCache(cacheKey);

            return $"{SuccessResponse.UpdateSuccessMessage} {metaVersionId}";
        }
        catch (Exception)
        {
            await _QueryExecutor.RollbackAsync(Trans);
            throw;
        }
    }

    // ── Delete (with Released guard + cascade) ───────────────────────────

    public async Task<string> Delete(int metaVersionId, LoginDTO loginDTO, CancellationToken ct)
    {
        var existing = await _MetadataSyncDAL.GetById(metaVersionId, loginDTO, ct).ConfigureAwait(false);
        if (existing?.MetaStatus == (byte)SwDAL.Enums.MetaVersionStatus.Released)
            throw new InvalidOperationException("Cannot delete a released metadata version.");

        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            await _BaseEntityAppService.ExecuteSaveAsync(
                EntityConstant.OBJECTSWMETADATASYNC,
                EventTypeConstant.DELETEMETADATASYNCEVENTTYPEID,
                new MetadataSyncDTO { MetaVersionId = metaVersionId, TenantId = loginDTO.ClientId },
                loginDTO,
                async tx =>
                {
                    await _MetadataSyncDAL.DeleteAllChanges(metaVersionId, loginDTO, tx, ct);
                    await _MetadataSyncDAL.DeleteMetadataSync(metaVersionId, loginDTO, tx, ct);
                    return metaVersionId;
                },
                null, -1, -1, Trans);

            await _QueryExecutor.CommitAsync(Trans);
            var keyGen = new CacheKeyGeneration();

            var cacheKey = keyGen.KeyGeneration(
                metaVersionId, EntityConstant.OBJECTSWMETADATASYNC,
                CacheKeyLevel.CLIENT_LEVEL, loginDTO);
            await _KeyInvalidate.AllInvalidateCache(cacheKey);

            return SuccessResponse.DeleteSuccessMessage;
        }
        catch (Exception)
        {
            await _QueryExecutor.RollbackAsync(Trans);
            throw;
        }
    }

    // ── MetadataChange methods ────────────────────────────────────────────

    public async Task<MetadataVersionWithChangesDTO?> GetVersionWithChanges(
        int metaVersionId, LoginDTO loginDTO, CancellationToken ct)
    {
        var versionTask = _MetadataSyncDAL.GetById(metaVersionId, loginDTO, ct);
        var changesTask = _MetadataSyncDAL.GetChanges(metaVersionId, loginDTO, ct);
        await Task.WhenAll(versionTask, changesTask).ConfigureAwait(false);

        var version = await versionTask;
        if (version == null) return null;

        return new MetadataVersionWithChangesDTO
        {
            Version = version,
            Changes = await changesTask
        };
    }

    public async Task<string> SaveChange(
        MetadataChangeDTO dto, LoginDTO loginDTO, CancellationToken ct)
    {
        if (dto == null) throw new ArgumentNullException(nameof(dto));

        dto.TenantId = loginDTO.ClientId;

        var stub = new MetadataSyncDTO { MetaVersionId = dto.MetaVersionId, TenantId = loginDTO.ClientId };
        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            await _BaseEntityAppService.ExecuteSaveAsync(
                EntityConstant.OBJECTSWMETADATASYNC,
                EventTypeConstant.UPDATEMETADATASYNCEVENTTYPEID,
                stub, loginDTO,
                async tx =>
                {
                    await _MetadataSyncDAL.InsertChange(dto, loginDTO, tx, ct);
                    return dto.MetaVersionId;
                },
                null, -1, -1, Trans);

            await _QueryExecutor.CommitAsync(Trans);
            var keyGen = new CacheKeyGeneration();

            var cacheKey = keyGen.KeyGeneration(
                dto.MetaVersionId, EntityConstant.OBJECTSWMETADATASYNC,
                CacheKeyLevel.CLIENT_LEVEL, loginDTO);
            await _KeyInvalidate.AllInvalidateCache(cacheKey);

            return SuccessResponse.SaveSuccessMessage;
        }
        catch (Exception)
        {
            await _QueryExecutor.RollbackAsync(Trans);
            throw;
        }
    }

    public async Task<string> DeleteChange(
        int metaVersionId, string tableName, string columnName,
        LoginDTO loginDTO, CancellationToken ct)
    {
        var stub = new MetadataSyncDTO { MetaVersionId = metaVersionId, TenantId = loginDTO.ClientId };
        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            await _BaseEntityAppService.ExecuteSaveAsync(
                EntityConstant.OBJECTSWMETADATASYNC,
                EventTypeConstant.UPDATEMETADATASYNCEVENTTYPEID,
                stub, loginDTO,
                async tx =>
                {
                    await _MetadataSyncDAL.DeleteChange(metaVersionId, tableName, columnName, loginDTO, tx, ct);
                    return metaVersionId;
                },
                null, -1, -1, Trans);

            await _QueryExecutor.CommitAsync(Trans);

            //var cacheKey = KeyGenerator.KeyGeneration(
            //    metaVersionId, EntityConstant.OBJECTSWMETADATASYNC,
            //    CacheKeyLevel.CLIENT_LEVEL, loginDTO);
            //await _KeyInvalidate.AllInvalidateCache(cacheKey);

            return SuccessResponse.DeleteSuccessMessage;
        }
        catch (Exception)
        {
            await _QueryExecutor.RollbackAsync(Trans);
            throw;
        }
    }

    // ── MetadataSync → Change Request generation ──────────────────────────────

    public async Task<string> GenerateCrFromVersion(
        int metadataVersionId, int clientDatabaseId, LoginDTO login, CancellationToken ct)
    {
        var composite = await GetVersionWithChanges(metadataVersionId, login, ct).ConfigureAwait(false);
        if (composite == null)
            throw new InvalidOperationException($"Metadata version {metadataVersionId} not found.");

        var changes = composite.Changes.ToList();
        if (changes.Count == 0)
            throw new InvalidOperationException("No metadata changes found for this version.");

        var sql       = MetadataSyncSqlBuilder.BuildDmlFromChanges(composite.Version.VersionLabel, changes);
        var queryType = DetermineQueryType(changes);

        var dto = new ChangeRequestDTO
        {
            Title            = $"MetadataSync: {composite.Version.VersionLabel}",
            Description      = composite.Version.Description,
            Sql              = sql,
            ClientDatabaseId = clientDatabaseId,
            CrStatus         = ChangeRequestStatus.Draft,
            QueryType        = queryType,
            Category         = ChangeRequestCategory.DataMigration,
            TenantId         = login.ClientId
        };

        return await _ChangeRequestBLL.Save(dto, login, ct).ConfigureAwait(false);
    }

    private static ChangeRequestQueryType DetermineQueryType(IList<MetadataChangeDTO> changes)
    {
        var distinctTypes = changes
            .Select(c => (c.ChangeType ?? string.Empty).Trim().ToUpperInvariant())
            .Where(t => t is "INSERT" or "UPDATE" or "DELETE")
            .Distinct()
            .ToHashSet();

        if (distinctTypes.Count == 1)
        {
            return distinctTypes.First() switch
            {
                "INSERT" => ChangeRequestQueryType.Insert,
                "UPDATE" => ChangeRequestQueryType.Update,
                "DELETE" => ChangeRequestQueryType.Delete,
                _        => ChangeRequestQueryType.Other
            };
        }

        return ChangeRequestQueryType.Other;
    }
}