using System.Data;
using System.Text;
using AMREZ.EOP.Abstractions.Applications.Tenancy;
using AMREZ.EOP.Abstractions.Applications.UseCases.ImportData.Location;
using AMREZ.EOP.Abstractions.Infrastructures.Common;
using AMREZ.EOP.Abstractions.Infrastructures.Repositories;
using AMREZ.EOP.Contracts.DTOs.ImportData.Location;
using AMREZ.EOP.Contracts.DTOs.MasterData.District;
using AMREZ.EOP.Contracts.DTOs.MasterData.Province;
using AMREZ.EOP.Contracts.DTOs.MasterData.Subdistrict;
using AMREZ.EOP.Domain.Entities.MasterData;
using AMREZ.EOP.Domain.Entities.Tenancy;
using ClosedXML.Excel;
using Microsoft.AspNetCore.Http;

namespace AMREZ.EOP.Application.UseCases.ImportData.Location;

public sealed class LocationImportUseCase : ILocationImportUseCase
{
    private readonly IProvinceRepository _provinceRepo;
    private readonly IDistrictRepository _districtRepo;
    private readonly ISubdistrictRepository _subdistrictRepo;
    private readonly ITenantRepository _tenants;
    private readonly ITenantResolver _tenantResolver;
    private readonly IHttpContextAccessor _http;
    private readonly IUnitOfWork _uow;

    public LocationImportUseCase(
        IProvinceRepository provinceRepo,
        IDistrictRepository districtRepo,
        ISubdistrictRepository subdistrictRepo,
        ITenantRepository tenants,
        ITenantResolver tenantResolver,
        IHttpContextAccessor http,
        IUnitOfWork uow)
    {
        _provinceRepo = provinceRepo;
        _districtRepo = districtRepo;
        _subdistrictRepo = subdistrictRepo;
        _tenants = tenants;
        _tenantResolver = tenantResolver;
        _http = http;
        _uow = uow;
    }

    public async Task<LocationImportResultDto> ExecuteAsync(IFormFile file, CancellationToken ct = default)
    {
        if (file == null || file.Length == 0)
            throw new InvalidOperationException("Excel file is required");

        var http = _http.HttpContext ?? throw new InvalidOperationException("No HttpContext");
        var tc = _tenantResolver.Resolve(http) ?? throw new InvalidOperationException("No tenant");

        var tn = await _tenants.GetAsync(tc.Id) ?? throw new InvalidOperationException("Tenant config not found");
        var tenantId = tn.TenantId;

        var result = new LocationImportResultDto();

        await _uow.BeginAsync(tc, IsolationLevel.ReadCommitted, ct);
        try
        {
            var provincesByCode = await LoadGlobalProvincesAsync(tenantId, ct);
            var districtsByCode = await LoadGlobalDistrictsAsync(tenantId, ct);
            var subdistrictsByCode = await LoadGlobalSubdistrictsAsync(tenantId, ct);

            using var stream = new MemoryStream();
            await file.CopyToAsync(stream, ct);
            stream.Position = 0;

            using var wb = new XLWorkbook(stream);
            var ws = wb.Worksheets.First();
            var lastRow = ws.LastRowUsed().RowNumber();

            // row 1 = header
            for (var row = 2; row <= lastRow; row++)
            {
                ct.ThrowIfCancellationRequested();
                result.RowsRead++;

                string Get(int col)
                {
                    try
                    {
                        return ws.Cell(row, col).GetString().Trim();
                    }
                    catch
                    {
                        return string.Empty;
                    }
                }

                // 1: CC, 2: AA, 3: TT, 4: CCAATT, 5: จังหวัด, 6: อำเภอ, 7: ตำบล, 8: รหัสไปรษณีย์
                var cc = Get(1);
                var aa = Get(2);
                var tt = Get(3);
                var locCode = Get(4);

                var provinceName = Get(5);
                var districtName = Get(6);
                var subdistrictName = Get(7);
                var postcode = Get(8);

                string? code = null;

                if (!string.IsNullOrWhiteSpace(cc) &&
                    !string.IsNullOrWhiteSpace(aa) &&
                    !string.IsNullOrWhiteSpace(tt))
                {
                    code =
                        cc.PadLeft(2, '0') +
                        aa.PadLeft(2, '0') +
                        tt.PadLeft(2, '0');
                }
                else if (!string.IsNullOrWhiteSpace(locCode))
                {
                    code = locCode;
                }

                if (string.IsNullOrWhiteSpace(code))
                    continue;

                code = code.Trim();
                if (code.Length < 6)
                    code = code.PadLeft(6, '0');
                else if (code.Length > 6)
                    code = code[..6];

                var isProvince = code.EndsWith("0000", StringComparison.Ordinal);
                var isDistrict = !isProvince && code.EndsWith("00", StringComparison.Ordinal);
                var isSubdistrict = !isProvince && !isDistrict;

                if (isProvince)
                {
                    await ImportProvinceAsync(code, provinceName, provincesByCode, tenantId, result, ct);
                }
                else if (isDistrict)
                {
                    await ImportDistrictAsync(
                        code,
                        provinceName,
                        districtName,
                        provincesByCode,
                        districtsByCode,
                        tenantId,
                        result,
                        ct);
                }
                else if (isSubdistrict)
                {
                    await ImportSubdistrictAsync(
                        code,
                        provinceName,
                        districtName,
                        subdistrictName,
                        postcode,
                        provincesByCode,
                        districtsByCode,
                        subdistrictsByCode,
                        tenantId,
                        result,
                        ct);
                }
            }

            await _uow.CommitAsync(ct);
            return result;
        }
        catch
        {
            await _uow.RollbackAsync(ct);
            throw;
        }
    }

    // ========== preload helpers ==========

    private async Task<Dictionary<string, Province>> LoadGlobalProvincesAsync(Guid tenantId, CancellationToken ct)
    {
        var req = new ProvinceListRequest
        {
            Page = 1,
            PageSize = 500,
            IncludeInactive = true,
            Search = null
        };

        var page = await _provinceRepo.SearchEffectiveAsync(tenantId, req, ct);

        return page.Items
            .Where(x => x.Scope == "global" && !string.IsNullOrWhiteSpace(x.Code))
            .GroupBy(x => x.Code!)
            .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase);
    }

    private async Task<Dictionary<string, District>> LoadGlobalDistrictsAsync(Guid tenantId, CancellationToken ct)
    {
        var req = new DistrictListRequest
        {
            Page = 1,
            PageSize = 5000,
            IncludeInactive = true,
            Search = null
        };

        var page = await _districtRepo.SearchEffectiveAsync(tenantId, req, ct);

        return page.Items
            .Where(x => x.Scope == "global" && !string.IsNullOrWhiteSpace(x.Code))
            .GroupBy(x => x.Code!)
            .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase);
    }

    private async Task<Dictionary<string, Subdistrict>> LoadGlobalSubdistrictsAsync(Guid tenantId, CancellationToken ct)
    {
        var req = new SubdistrictListRequest
        {
            Page = 1,
            PageSize = 10000,
            IncludeInactive = true,
            Search = null
        };

        var page = await _subdistrictRepo.SearchEffectiveAsync(tenantId, req, ct);

        return page.Items
            .Where(x => x.Scope == "global" && !string.IsNullOrWhiteSpace(x.Code))
            .GroupBy(x => x.Code!)
            .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase);
    }

    // ========== import helpers ==========

    private async Task ImportProvinceAsync(
        string ccaatt,
        string? provinceName,
        Dictionary<string, Province> provincesByCode,
        Guid tenantId,
        LocationImportResultDto result,
        CancellationToken ct)
    {
        var provCode = ccaatt[..2];

        if (!provincesByCode.TryGetValue(provCode, out var prov))
        {
            prov = new Province
            {
                Id = Guid.NewGuid(),
                TenantId = tenantId,
                Scope = "global",
                Code = provCode,
                Name = string.IsNullOrWhiteSpace(provinceName) ? provCode : provinceName,
                IsActive = true,
                IsSystem = true
            };

            await _provinceRepo.AddAsync(prov, ct);
            provincesByCode[provCode] = prov;
            result.ProvincesCreated++;
            return;
        }

        var updated = false;

        if (!string.IsNullOrWhiteSpace(provinceName) && prov.Name != provinceName)
        {
            prov.Name = provinceName;
            updated = true;
        }

        if (updated)
        {
            await _provinceRepo.UpdateAsync(prov, ct);
            result.ProvincesUpdated++;
        }
    }

    private async Task ImportDistrictAsync(
        string ccaatt,
        string? provinceName,
        string? districtName,
        Dictionary<string, Province> provincesByCode,
        Dictionary<string, District> districtsByCode,
        Guid tenantId,
        LocationImportResultDto result,
        CancellationToken ct)
    {
        var provCode = ccaatt[..2];
        var distCode = ccaatt[..4];

        if (!provincesByCode.TryGetValue(provCode, out var prov))
        {
            await ImportProvinceAsync(provCode + "0000", provinceName, provincesByCode, tenantId, result, ct);
            prov = provincesByCode[provCode];
        }

        if (!districtsByCode.TryGetValue(distCode, out var dist))
        {
            dist = new District
            {
                Id = Guid.NewGuid(),
                TenantId = tenantId,
                Scope = "global",
                Code = distCode,
                Name = string.IsNullOrWhiteSpace(districtName) ? distCode : districtName,
                ProvinceId = prov.Id,
                IsActive = true,
                IsSystem = true
            };

            await _districtRepo.AddAsync(dist, ct);
            districtsByCode[distCode] = dist;
            result.DistrictsCreated++;
            return;
        }

        var updated = false;

        if (!string.IsNullOrWhiteSpace(districtName) && dist.Name != districtName)
        {
            dist.Name = districtName;
            updated = true;
        }

        if (dist.ProvinceId != prov.Id)
        {
            dist.ProvinceId = prov.Id;
            updated = true;
        }

        if (updated)
        {
            await _districtRepo.UpdateAsync(dist, ct);
            result.DistrictsUpdated++;
        }
    }

    private async Task ImportSubdistrictAsync(
        string ccaatt,
        string? provinceName,
        string? districtName,
        string? subdistrictName,
        string? postcode,
        Dictionary<string, Province> provincesByCode,
        Dictionary<string, District> districtsByCode,
        Dictionary<string, Subdistrict> subdistrictsByCode,
        Guid tenantId,
        LocationImportResultDto result,
        CancellationToken ct)
    {
        var provCode = ccaatt[..2];
        var distCode = ccaatt[..4];
        var subCode = ccaatt[..6];

        if (!provincesByCode.TryGetValue(provCode, out var prov))
        {
            await ImportProvinceAsync(provCode + "0000", provinceName, provincesByCode, tenantId, result, ct);
            prov = provincesByCode[provCode];
        }

        if (!districtsByCode.TryGetValue(distCode, out var dist))
        {
            await ImportDistrictAsync(distCode + "00", provinceName, districtName, provincesByCode, districtsByCode, tenantId, result, ct);
            dist = districtsByCode[distCode];
        }

        if (!subdistrictsByCode.TryGetValue(subCode, out var sub))
        {
            sub = new Subdistrict
            {
                Id = Guid.NewGuid(),
                TenantId = tenantId,
                Scope = "global",
                Code = subCode,
                Name = string.IsNullOrWhiteSpace(subdistrictName) ? subCode : subdistrictName,
                DistrictId = dist.Id,
                Postcode = string.IsNullOrWhiteSpace(postcode) ? null : postcode,
                IsActive = true,
                IsSystem = true
            };

            await _subdistrictRepo.AddAsync(sub, ct);
            subdistrictsByCode[subCode] = sub;
            result.SubdistrictsCreated++;
            return;
        }

        var updated = false;

        if (!string.IsNullOrWhiteSpace(subdistrictName) && sub.Name != subdistrictName)
        {
            sub.Name = subdistrictName;
            updated = true;
        }

        if (sub.DistrictId != dist.Id)
        {
            sub.DistrictId = dist.Id;
            updated = true;
        }

        if (!string.IsNullOrWhiteSpace(postcode) && sub.Postcode != postcode)
        {
            sub.Postcode = postcode;
            updated = true;
        }

        if (updated)
        {
            await _subdistrictRepo.UpdateAsync(sub, ct);
            result.SubdistrictsUpdated++;
        }
    }
}