using GB5Shared.DTO.Framework.Login;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.Resource.Response;
using SwBLL.DbServer;
using SwBLL.DdlScript;
using SwBLL.Provisioning;
using SwBLL.UpgradePackage;
using SwDAL.CustomCode.RefDataSource;
using SwDAL.DTO.DdlScript;
using SwDAL.DTO.RefDataSource;
using SwDAL.DTO.UpgradePackage;
using static GB5Shared.GB5Constant.Constant;

namespace SwBLL.RefDataSource;

public class RefDataSourceBLL : IRefDataSourceBLL
{
    private readonly IRefDataSourceDAL _RefDataSourceDAL;
    private readonly AutoNumber _AutoNumber;
    private readonly IDdlScriptBLL _DdlScriptBLL;
    private readonly IUpgradePackageBLL _UpgradePackageBLL;
    private readonly IDbServerBLL _DbServerBLL;
    private readonly ITargetDbExecutor _TargetDbExecutor;
    private readonly IReferenceDataScriptGenerator _Generator;

    public RefDataSourceBLL(
        IRefDataSourceDAL refDataSourceDAL,
        AutoNumber autoNumber,
        IDdlScriptBLL ddlScriptBLL,
        IUpgradePackageBLL upgradePackageBLL,
        IDbServerBLL dbServerBLL,
        ITargetDbExecutor targetDbExecutor,
        IReferenceDataScriptGenerator generator)
    {
        _RefDataSourceDAL  = refDataSourceDAL;
        _AutoNumber        = autoNumber;
        _DdlScriptBLL      = ddlScriptBLL;
        _UpgradePackageBLL = upgradePackageBLL;
        _DbServerBLL       = dbServerBLL;
        _TargetDbExecutor  = targetDbExecutor;
        _Generator         = generator;
    }

    public async Task<RefDataSourceDTO?> GetById(int refDataSourceId, LoginDTO loginDTO, CancellationToken ct)
        => await _RefDataSourceDAL.GetById(refDataSourceId, loginDTO, ct).ConfigureAwait(false);

    public async Task<IEnumerable<RefDataSourceDTO>> GetByDbModel(int dbModelId, LoginDTO loginDTO, CancellationToken ct)
        => await _RefDataSourceDAL.GetByDbModel(dbModelId, loginDTO, ct).ConfigureAwait(false);

    public async Task<string> Save(RefDataSourceDTO dto, LoginDTO loginDTO, CancellationToken ct)
    {
        if (dto is null) throw new ArgumentNullException(nameof(dto));
        if (dto.DbModelId <= 0) throw new ArgumentException("DbModelId is required.", nameof(dto));
        if (string.IsNullOrWhiteSpace(dto.TableName)) throw new ArgumentException("TableName is required.", nameof(dto));

        // Mirrors CK_MSWREFDATASOURCE_SCOPEVALUE / UpgradePackageBLL's identical check.
        bool isUniversal = dto.ScopeType == (byte)SwDAL.Enums.PackageScopeType.Universal;
        if (isUniversal && !string.IsNullOrWhiteSpace(dto.ScopeValue))
            throw new ArgumentException("ScopeValue must be empty for a Universal-scope reference data source.", nameof(dto));
        if (!isUniversal && string.IsNullOrWhiteSpace(dto.ScopeValue))
            throw new ArgumentException("ScopeValue is required for a non-Universal-scope reference data source.", nameof(dto));

        // Established platform convention (not this session's invention): a "Z_"-prefixed table,
        // script, or function is always custom to one specific client — never part of the
        // universal set every client receives. Registering one as Universal would silently ship
        // one client's custom object to every other client provisioned from this DbModel.
        if (isUniversal && dto.TableName.StartsWith("Z_", StringComparison.OrdinalIgnoreCase))
            throw new ArgumentException(
                $"'{dto.TableName}' starts with the reserved custom-object prefix 'Z_' and must be registered with a non-Universal ScopeType (typically Client), not Universal.",
                nameof(dto));

        dto.TenantId = loginDTO.ClientId;

        bool isNew = dto.RefDataSourceId == 0;
        if (isNew)
        {
            var auto = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.SWREFDATASOURCE, loginDTO);
            dto.RefDataSourceId = auto.StartNumber;
            dto.CreatedById     = loginDTO.UserId;
            dto.CreatedOn       = DateTime.UtcNow;
        }
        dto.ModifiedById = loginDTO.UserId;
        dto.ModifiedOn   = DateTime.UtcNow;

        _ = isNew
            ? await _RefDataSourceDAL.Insert(dto, loginDTO, ct).ConfigureAwait(false)
            : await _RefDataSourceDAL.Update(dto, loginDTO, ct).ConfigureAwait(false);

        return isNew
            ? $"{SuccessResponse.SaveSuccessMessage} {dto.RefDataSourceId}"
            : SuccessResponse.UpdateSuccess;
    }

    public async Task<string> Delete(int refDataSourceId, LoginDTO loginDTO, CancellationToken ct)
    {
        if (refDataSourceId <= 0) throw new ArgumentException("RefDataSourceId is required.", nameof(refDataSourceId));
        await _RefDataSourceDAL.Delete(refDataSourceId, loginDTO, ct).ConfigureAwait(false);
        return SuccessResponse.DeleteSuccessMessage;
    }

    public async Task<int> GenerateAndRegisterAsync(
        int refDataSourceId, int sourceDbServerId, string sourceDatabaseName,
        int branchId, LoginDTO loginDTO, CancellationToken ct)
    {
        var (source, sql) = await GenerateAsync(refDataSourceId, sourceDbServerId, sourceDatabaseName, loginDTO, ct)
            .ConfigureAwait(false);
        return await SaveAsDraftScriptAsync(source, sql, branchId, loginDTO, ct).ConfigureAwait(false);
    }

    public async Task<RefDataRegenerationResultDTO> RegenerateIfChangedAsync(
        int refDataSourceId, int sourceDbServerId, string sourceDatabaseName,
        int branchId, LoginDTO loginDTO, CancellationToken ct)
    {
        var (source, sql) = await GenerateAsync(refDataSourceId, sourceDbServerId, sourceDatabaseName, loginDTO, ct)
            .ConfigureAwait(false);
        string newChecksum = DdlScriptBLL.ComputeChecksum(sql);

        string? priorChecksum = await FindReleasedChecksumForTableAsync(source, loginDTO, ct).ConfigureAwait(false);

        if (priorChecksum == newChecksum)
        {
            return new RefDataRegenerationResultDTO
            {
                Changed       = false,
                PriorChecksum = priorChecksum,
                NewChecksum   = newChecksum,
            };
        }

        int newScriptId = await SaveAsDraftScriptAsync(source, sql, branchId, loginDTO, ct).ConfigureAwait(false);
        return new RefDataRegenerationResultDTO
        {
            Changed        = true,
            NewDdlScriptId = newScriptId,
            PriorChecksum  = priorChecksum,
            NewChecksum    = newChecksum,
        };
    }

    // ── Shared helpers ────────────────────────────────────────────────────

    private async Task<(RefDataSourceDTO Source, string Sql)> GenerateAsync(
        int refDataSourceId, int sourceDbServerId, string sourceDatabaseName, LoginDTO loginDTO, CancellationToken ct)
    {
        var source = await _RefDataSourceDAL.GetById(refDataSourceId, loginDTO, ct)
            .ConfigureAwait(false)
            ?? throw new InvalidOperationException($"RefDataSource {refDataSourceId} not found.");

        var server = await _DbServerBLL.GetByIdWithCredentials(sourceDbServerId, loginDTO, ct)
            .ConfigureAwait(false)
            ?? throw new InvalidOperationException($"DB server {sourceDbServerId} not found.");

        // Resolved server-side into a real connection string — a raw connection string is never
        // accepted as a caller-supplied parameter (see IRefDataSourceBLL's own doc comment).
        string connStr = _TargetDbExecutor.BuildConnectionString(server, sourceDatabaseName);

        var sql = await _Generator.GenerateSeedScriptAsync(
            connStr, server.DbType, source.TableName, source.RowFilterSql, ct)
            .ConfigureAwait(false);

        return (source, sql);
    }

    private async Task<int> SaveAsDraftScriptAsync(
        RefDataSourceDTO source, string sql, int branchId, LoginDTO loginDTO, CancellationToken ct)
    {
        var script = new DdlScriptDTO
        {
            DbModelId     = source.DbModelId,
            BranchId      = branchId,
            ObjectGroupId = -1,
            ObjectType    = 0,
            SchemaName    = "dbo",
            ObjectName    = source.TableName,
            SqlScript     = sql,
        };

        await _DdlScriptBLL.Save(script, loginDTO, ct).ConfigureAwait(false);
        return script.DdlScriptId;
    }

    private async Task<string?> FindReleasedChecksumForTableAsync(
        RefDataSourceDTO source, LoginDTO loginDTO, CancellationToken ct)
    {
        // Walk the same scope this registry row itself is intended for — a Client-scoped ("Z_"
        // custom table) row must compare against that client's own overlay chain, not the
        // Universal one, or it would always look "changed" (no Universal package ever carries a
        // client-custom script) and regenerate needlessly on every call.
        var scope = BuildScopeFilter(source);
        var chain = await _UpgradePackageBLL.GetProvisioningChain(source.DbModelId, scope, loginDTO, ct).ConfigureAwait(false);

        foreach (var package in chain)
        {
            var lines = await _UpgradePackageBLL.GetDdlLines(package.PackageId, loginDTO, ct).ConfigureAwait(false);
            var match = lines.FirstOrDefault(l => string.Equals(l.ObjectName, source.TableName, StringComparison.OrdinalIgnoreCase));
            if (match is null) continue;

            var script = await _DdlScriptBLL.GetById(match.DdlScriptId, loginDTO, ct).ConfigureAwait(false);
            if (script?.Checksum is not null) return script.Checksum;
        }

        return null;
    }

    private static PackageScopeFilterDTO? BuildScopeFilter(RefDataSourceDTO source) =>
        (SwDAL.Enums.PackageScopeType)source.ScopeType switch
        {
            SwDAL.Enums.PackageScopeType.Universal      => null,
            SwDAL.Enums.PackageScopeType.PartnerProduct => new PackageScopeFilterDTO { PartnerProductCode = source.ScopeValue },
            SwDAL.Enums.PackageScopeType.Industry       => new PackageScopeFilterDTO { IndustryCode = source.ScopeValue },
            SwDAL.Enums.PackageScopeType.Country        => new PackageScopeFilterDTO { CountryCode = source.ScopeValue },
            SwDAL.Enums.PackageScopeType.Client         => new PackageScopeFilterDTO { ClientCode = source.ScopeValue },
            _ => null,
        };
}