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 SwDAL.CustomCode.DdlObjectHistory;
using SwDAL.CustomCode.DdlScript;
using SwDAL.DTO.DdlObjectHistory;
using SwDAL.DTO.DdlScript;
using SwDAL.Query.DbModel;
using SwDAL.Query.DdlScript;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using static GB5Shared.GB5Constant.Constant;

namespace SwBLL.DdlScript;

public class DdlScriptBLL : IDdlScriptBLL
{
    private readonly IDdlScriptDAL                                         _DdlScriptDAL;
    private readonly IDdlObjectHistoryDAL                                  _DdlObjectHistoryDAL;
    private readonly AutoNumber                                            _AutoNumber;
    private readonly IQueryExecutor                                        _QueryExecutor;
    private readonly KeyInvalidate                                         _KeyInvalidate;
    private readonly IListHandler<DdlScriptListQuery, DdlScriptListDTO>   _ListHandler;
    private readonly BaseEntityAppService<DdlScriptDTO>                   _BaseEntityAppService;

    public DdlScriptBLL(
        IDdlScriptDAL ddlScriptDAL,
        IDdlObjectHistoryDAL ddlObjectHistoryDAL,
        AutoNumber autoNumber,
        IQueryExecutor queryExecutor,
        KeyInvalidate keyInvalidate,
        IListHandler<DdlScriptListQuery, DdlScriptListDTO> listHandler,
        BaseEntityAppService<DdlScriptDTO> baseEntityAppService)
    {
        _DdlScriptDAL         = ddlScriptDAL;
        _DdlObjectHistoryDAL  = ddlObjectHistoryDAL;
        _AutoNumber           = autoNumber;
        _QueryExecutor        = queryExecutor;
        _KeyInvalidate        = keyInvalidate;
        _ListHandler          = listHandler;
        _BaseEntityAppService = baseEntityAppService;
    }

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

    public async Task<DdlScriptDTO?> GetById(int ddlScriptId, LoginDTO loginDTO, CancellationToken ct)
        => await _DdlScriptDAL.GetById(ddlScriptId, loginDTO, ct).ConfigureAwait(false);

    public async Task<IEnumerable<DdlScriptDTO>> GetByBranch(int branchId, LoginDTO loginDTO, CancellationToken ct)
        => await _DdlScriptDAL.GetByBranch(branchId, 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<DdlScriptListCriteria>(merged);
        var result = await _ListHandler
            .HandleAsync(new DdlScriptListQuery(c), loginDTO, ct)
            .ConfigureAwait(false);
        return JsonConvert.SerializeObject(result);
    }

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

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

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

        // BLL-owned domain logic — normalise and compute checksum before transaction
        dto.TenantId   = loginDTO.ClientId;
        dto.SchemaName = string.IsNullOrWhiteSpace(dto.SchemaName) ? "dbo" : dto.SchemaName;
        dto.Checksum   = ComputeChecksum(dto.SqlScript);

        // GetNumberAsync now accepts the same Trans this method already opens for the insert
        // (gaps doc §4 — reproduced live this session: with no shared transaction, a failed
        // insert permanently burned an ID, since Trans's own rollback couldn't touch the
        // AutoNumber UPDATE that had already committed on its own separate connection). Enlisting
        // the reservation in Trans makes the two genuinely atomic — a rollback now undoes both —
        // so the RollbackAutoNumber compensating call this method used to need in its catch block
        // is no longer necessary.
        bool isNew = dto.DdlScriptId == 0;

        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            // Object-version-history linking (git-blob-history analogue, 056_MSWDDLOBJECTHISTORY.sql)
            // — only for a genuinely NEW script. Editing an unapproved Draft in place (isNew=false)
            // never gets its own history entry, same as git never versions an uncommitted
            // working-tree edit — only a distinct DdlScriptId is a "commit."
            DdlObjectCurrentTipDTO? previousTip = null;
            if (isNew)
            {
                var ddlScriptAuto = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.SWDDLSCRIPT, loginDTO, Trans);
                dto.DdlScriptId = ddlScriptAuto.StartNumber;
                dto.ScriptStatus = 0; // always Draft on create

                previousTip = await _DdlObjectHistoryDAL.GetCurrentTip(
                    dto.DbModelId, dto.SchemaName, dto.ObjectName, dto.ObjectType, loginDTO, Trans, ct).ConfigureAwait(false);
            }

            await _BaseEntityAppService.ExecuteSaveAsync(
                EntityConstant.OBJECTSWDDLSCRIPT,
                isNew
                    ? EventTypeConstant.SAVEDDLSCRIPTEVENTTYPEID
                    : EventTypeConstant.UPDATEDDLSCRIPTEVENTTYPEID,
                dto,
                loginDTO,
                async tx =>
                {
                    _ = isNew
                        ? await _DdlScriptDAL.SaveDdlScript(dto, loginDTO, tx, ct)
                        : await _DdlScriptDAL.UpdateDdlScript(dto, loginDTO, tx, ct);
                    return dto.DdlScriptId;
                },
                null, -1, -1, Trans);

            if (isNew)
            {
                // The new MSWDDLSCRIPT row now exists (inserted above, same Trans) — safe to
                // reference it from MSWDDLOBJECTHISTORY.DDLSCRIPTID's own FK.
                if (previousTip is not null)
                    await _DdlObjectHistoryDAL.ClearCurrentTip(
                        dto.DbModelId, dto.SchemaName, dto.ObjectName, dto.ObjectType, loginDTO, Trans, ct).ConfigureAwait(false);

                var historyAuto = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.SWDDLOBJECTHISTORY, loginDTO, Trans);
                await _DdlObjectHistoryDAL.InsertVersion(new DdlObjectHistoryDTO
                {
                    DdlObjectHistoryId  = historyAuto.StartNumber,
                    DbModelId           = dto.DbModelId,
                    SchemaName          = dto.SchemaName,
                    ObjectName          = dto.ObjectName,
                    ObjectType          = dto.ObjectType,
                    DdlScriptId         = dto.DdlScriptId,
                    PreviousDdlScriptId = previousTip?.DdlScriptId,
                    Checksum            = dto.Checksum ?? string.Empty,
                    IsCurrentTip        = true,
                }, loginDTO, Trans, ct).ConfigureAwait(false);
            }

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

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

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

    // ── Delete ────────────────────────────────────────────────────────────

    public async Task<string> Delete(int ddlScriptId, LoginDTO loginDTO, CancellationToken ct)
    {
        bool inExecutions = await _DdlScriptDAL.ExistsInExecutions(ddlScriptId, loginDTO)
            .ConfigureAwait(false);
        if (inExecutions)
            throw new InvalidOperationException("Cannot delete a DDL script that has been executed.");

        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            await _BaseEntityAppService.ExecuteSaveAsync(
                EntityConstant.OBJECTSWDDLSCRIPT,
                EventTypeConstant.DELETEDDLSCRIPTEVENTTYPEID,
                new DdlScriptDTO { DdlScriptId = ddlScriptId, TenantId = loginDTO.ClientId },
                loginDTO,
                async tx =>
                {
                    await _DdlScriptDAL.DeleteDdlScript(ddlScriptId, loginDTO, tx, ct);
                    return ddlScriptId; 
                },
            null, -1, -1, Trans);

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

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

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

    // ── Review (Draft → Reviewed) ─────────────────────────────────────────

    public async Task<string> ReviewDdlScript(int ddlScriptId, LoginDTO loginDTO, CancellationToken ct)
    {
        var stub = new DdlScriptDTO { DdlScriptId = ddlScriptId, TenantId = loginDTO.ClientId };

        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            await _BaseEntityAppService.ExecuteSaveAsync(
            EntityConstant.OBJECTSWDDLSCRIPT,
            EventTypeConstant.UPDATEDDLSCRIPTEVENTTYPEID,
            stub,
            loginDTO,
            async tx =>
            {
                await _DdlScriptDAL.UpdateScriptStatus(
                    ddlScriptId,
                    newStatus: 1,   // Reviewed
                    requiredCurrentStatus: 0,   // must be Draft
                    approvedById: -1,
                    approvedOn: null,
                    loginDTO, tx, ct);
                return ddlScriptId; // ✅ return int
            },
            null, -1, -1, Trans);
            await _QueryExecutor.CommitAsync(Trans);
            var keyGen = new CacheKeyGeneration();

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

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

    // ── Approve (Reviewed → Approved) ────────────────────────────────────

    public async Task<string> ApproveDdlScript(int ddlScriptId, int approvedById, LoginDTO loginDTO, CancellationToken ct)
    {
        var stub = new DdlScriptDTO { DdlScriptId = ddlScriptId, TenantId = loginDTO.ClientId };

        var Trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
        try
        {
            await _BaseEntityAppService.ExecuteSaveAsync(
            EntityConstant.OBJECTSWDDLSCRIPT,
            EventTypeConstant.UPDATEDDLSCRIPTEVENTTYPEID,
            stub,
            loginDTO,
            async tx =>
            {
                await _DdlScriptDAL.UpdateScriptStatus(
                    ddlScriptId,
                    newStatus: 2,             // Approved
                    requiredCurrentStatus: 1,             // must be Reviewed
                    approvedById: approvedById,
                    approvedOn: DateTime.UtcNow,
                    loginDTO, tx, ct);
                return ddlScriptId; // ✅ return int
            },
            null, -1, -1, Trans);

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

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

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

    // ── Log Execution (direct QueryExecutor — log table, not M-table) ─────

    public async Task<string> LogExecutionAsync(
        int ddlScriptId, int clientDbId, byte status, string? errorMessage,
        LoginDTO loginDTO, CancellationToken ct)
    {
        var logDto = new DdlExecutionLogDTO
        {
            DdlScriptId  = ddlScriptId,
            ClientDbId   = clientDbId,
            ExecStatus   = status,
            ErrorMessage = errorMessage,
            ExecutedOn   = DateTime.UtcNow,
            CreatedById  = loginDTO.UserId,
            CreatedOn    = DateTime.UtcNow,
            TenantId     = loginDTO.ClientId
        };

        await _QueryExecutor.ExecuteAsync(loginDTO, DdlScriptQB.LOG_INSERT, logDto)
            .ConfigureAwait(false);

        return SuccessResponse.SaveSuccess;
    }

    public async Task<int> CountExecutedScriptsAsync(
        int clientDbId, IEnumerable<int> ddlScriptIds, LoginDTO loginDTO, CancellationToken ct)
    {
        var idList = ddlScriptIds.ToList();
        if (idList.Count == 0) return 0;

        return await _QueryExecutor.ExecuteScalarAsync<int>(
            loginDTO, DdlScriptQB.COUNT_EXECUTIONS_FOR_SCRIPTS,
            new { ClientDbId = clientDbId, DdlScriptIds = idList }, cancellationToken: ct)
            .ConfigureAwait(false);
    }

    // ── Private helpers ───────────────────────────────────────────────────

    // Internal (not private) so RefDataSourceBLL's checksum-diff regeneration check (tracker
    // §36.4) compares against the exact same algorithm a script's own CHECKSUM was computed
    // with — duplicating this two-line hash elsewhere would risk silent drift between the two.
    internal static string ComputeChecksum(string sql)
        => Convert.ToHexString(
               SHA256.HashData(Encoding.UTF8.GetBytes(sql.Trim())))
           .ToLowerInvariant();
}