using System.Data;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using System.Data.Common;
using Newtonsoft.Json;
using SwDAL.DTO.UpgradePackage;
using SwDAL.Query.UpgradePackage;

namespace SwDAL.CustomCode.UpgradePackage;

public class UpgradePackageDAL : IUpgradePackageDAL
{
    private readonly IQueryExecutor _QueryExecutor;

    public UpgradePackageDAL(IQueryExecutor queryExecutor) => _QueryExecutor = queryExecutor;

    public async Task<UpgradePackageDTO?> GetById(int packageId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<UpgradePackageDTO>(
               loginDTO, UpgradePackageQB.GET_BY_ID,
               new { PackageId = packageId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

    public async Task<IEnumerable<UpgradePackageDdlDTO>> GetDdlLines(int packageId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<UpgradePackageDdlDTO>(
               loginDTO, UpgradePackageQB.GET_DDL_LINES,
               new { PackageId = packageId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

    public async Task<IEnumerable<UpgradePackageMetaDTO>> GetMetaLines(int packageId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<UpgradePackageMetaDTO>(
               loginDTO, UpgradePackageQB.GET_META_LINES,
               new { PackageId = packageId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

    public async Task<IEnumerable<PackageChainItemDTO>> GetProvisioningChain(
        int dbModelId, PackageScopeFilterDTO? scope, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<PackageChainItemDTO>(
               loginDTO, UpgradePackageQB.GET_PROVISIONING_CHAIN,
               new
               {
                   DbModelId          = dbModelId,
                   PartnerProductCode = scope?.PartnerProductCode,
                   IndustryCode       = scope?.IndustryCode,
                   CountryCode        = scope?.CountryCode,
                   ClientCode         = scope?.ClientCode,
               })
               .ConfigureAwait(false);

    public async Task<IEnumerable<LineageNodeDTO>> GetLineage(int dbModelId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<LineageNodeDTO>(
               loginDTO, UpgradePackageQB.GET_LINEAGE,
               new { DbModelId = dbModelId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

    public async Task<string> GetSelectListUpgradePackage(int first, int max, LoginDTO loginDTO)
    {
        var result = await _QueryExecutor.QueryAsync<UpgradePackagePickListDTO>(
            loginDTO, UpgradePackageQB.GET_SELECT_LIST,
            new { firstnumber = first, maxresult = max, TenantId = loginDTO.ClientId })
            .ConfigureAwait(false);
        return JsonConvert.SerializeObject(result);
    }

    public async Task<Result<string>> SaveUpgradePackage(
        UpgradePackageDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        dto.CreatedById  = loginDTO.UserId;
        dto.CreatedOn    = DateTime.UtcNow;
        dto.ModifiedById = loginDTO.UserId;
        dto.ModifiedOn   = DateTime.UtcNow;

        int rows = await _QueryExecutor.ExecuteAsync(loginDTO, UpgradePackageQB.INSERT, dto, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Upgrade package saved.")
            : Result<string>.Failure("Failed to save upgrade package.");
    }

    public async Task<Result<string>> UpdateUpgradePackage(
        UpgradePackageDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        dto.ModifiedById = loginDTO.UserId;
        dto.ModifiedOn   = DateTime.UtcNow;

        int rows = await _QueryExecutor.ExecuteAsync(loginDTO, UpgradePackageQB.UPDATE, dto, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Upgrade package updated.")
            : Result<string>.Failure("Failed to update upgrade package.");
    }

    public async Task<Result<string>> ReplaceDdlLines(
        int packageId, IEnumerable<UpgradePackageDdlDTO> lines,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        await _QueryExecutor.ExecuteAsync(loginDTO, UpgradePackageQB.DELETE_DDL_LINES,
            new { PackageId = packageId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);

        int seq = 1;
        int nextId = packageId * 1000; // simple key generation for detail rows
        foreach (var line in lines)
        {
            line.PackageDdlId = ++nextId;
            line.PackageId    = packageId;
            line.TenantId     = loginDTO.ClientId;
            line.Sequence     = (short)seq++;
            await _QueryExecutor.ExecuteAsync(loginDTO, UpgradePackageQB.INSERT_DDL_LINE, line, tx)
                .ConfigureAwait(false);
        }

        return Result<string>.Success("DDL lines replaced.");
    }

    public async Task<Result<string>> ReplaceMetaLines(
        int packageId, IEnumerable<UpgradePackageMetaDTO> lines,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        await _QueryExecutor.ExecuteAsync(loginDTO, UpgradePackageQB.DELETE_META_LINES,
            new { PackageId = packageId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);

        int nextId = packageId * 1000;
        foreach (var line in lines)
        {
            line.PackageMetaId = ++nextId;
            line.PackageId     = packageId;
            line.TenantId      = loginDTO.ClientId;
            await _QueryExecutor.ExecuteAsync(loginDTO, UpgradePackageQB.INSERT_META_LINE, line, tx)
                .ConfigureAwait(false);
        }

        return Result<string>.Success("Meta lines replaced.");
    }

    public async Task<Result<string>> DeleteUpgradePackage(
        int packageId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        // Delete detail rows first
        await _QueryExecutor.ExecuteAsync(loginDTO, UpgradePackageQB.DELETE_DDL_LINES,
            new { PackageId = packageId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);
        await _QueryExecutor.ExecuteAsync(loginDTO, UpgradePackageQB.DELETE_META_LINES,
            new { PackageId = packageId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);

        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, UpgradePackageQB.DELETE,
            new { PackageId = packageId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Upgrade package deleted.")
            : Result<string>.Failure("Upgrade package not found or cannot be deleted.");
    }
}