using System.Globalization;
using System.Text.RegularExpressions;
using DiunaBI.Domain.Entities;
using DiunaBI.Infrastructure.Data;
using DiunaBI.Infrastructure.Plugins;
using DiunaBI.Infrastructure.Services.Calculations;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace DiunaBI.Plugins.Morska.Processors;

public class MorskaD6Processor : BaseDataProcessor
{
    public override string ProcessorType => "Morska.Process.D6";

    private readonly AppDbContext _db;
    private readonly SpreadsheetsResource.ValuesResource _googleSheetValues;
    private readonly ILogger<MorskaT1R3Processor> _logger;

    // Configuration properties loaded from layer records
    private int Year { get; set; }
    private string? CostSource { get; set; }
    private string? SellSource { get; set; }
    private List<string> SellCodesConfiguration { get; set; } = new();

    public MorskaD6Processor(
        AppDbContext db,
        SpreadsheetsResource.ValuesResource googleSheetValues,
        ILogger<MorskaT1R3Processor> logger)
    {
        _db = db;
        _googleSheetValues = googleSheetValues;
        _logger = logger;
    }

    public override void Process(Layer processWorker)
    {
        try
        {
            _logger.LogInformation("{ProcessorType}: Starting processing for {ProcessWorkerName} ({ProcessWorkerId})",
                ProcessorType, processWorker.Name, processWorker.Id);

            // Load configuration from layer records
            LoadConfiguration(processWorker);

            // Validate required configuration
            ValidateConfiguration();

            // Perform the actual processing
            PerformProcessing(processWorker);

            _logger.LogInformation("{ProcessorType}: Successfully completed processing for {ProcessWorkerName}",
                ProcessorType, processWorker.Name);
        }
        catch (Exception e)
        {
            _logger.LogError(e, "{ProcessorType}: Failed to process {ProcessWorkerName} ({ProcessWorkerId})",
                ProcessorType, processWorker.Name, processWorker.Id);
            throw;
        }
    }
    private void LoadConfiguration(Layer processWorker)
    {
        if (processWorker.Records == null)
        {
            throw new InvalidOperationException("ProcessWorker has no records");
        }

        var yearStr = GetRecordValue(processWorker.Records, "Year");
        if (string.IsNullOrEmpty(yearStr) || !int.TryParse(yearStr, out var year))
        {
            throw new InvalidOperationException("Year record not found or invalid");
        }

        Year = year;

        CostSource = GetRecordValue(processWorker.Records, "SourceLayerCost");
        if (string.IsNullOrEmpty(CostSource))
        {
            throw new InvalidOperationException("SourceLayerCost record not found");
        }

        SellSource = GetRecordValue(processWorker.Records, "SourceLayerSell");
        if (string.IsNullOrEmpty(SellSource))
        {
            throw new InvalidOperationException("SourceLayerCosts record not found");
        }
        
        SellCodesConfiguration = processWorker.Records
                                     .Where(x => string.Equals(x.Code, "Sell-Code", StringComparison.OrdinalIgnoreCase))
                                     .Select(x => x.Desc1!.Trim())
                                     .Where(x => !string.IsNullOrWhiteSpace(x))
                                     .Distinct(StringComparer.Ordinal)
                                     .ToList();

        if (!SellCodesConfiguration.Any())
        {
            throw new InvalidOperationException("Sell-Code records not found");       
        }

        _logger.LogDebug(
            "{ProcessorType}: Configuration loaded - Year: {Year}, SourceCost: {CostSource}, SourceSell: {SellSource}",
            ProcessorType, Year, CostSource, SellSource);
    }
    private void ValidateConfiguration()
    {
        var errors = new List<string>();

        if (Year < 2000 || Year > 3000) errors.Add($"Invalid year: {Year}");
        if (string.IsNullOrEmpty(CostSource)) errors.Add("CostSource is required");
        if (string.IsNullOrEmpty(SellSource)) errors.Add("SellSource is required");

        if (errors.Any())
        {
            throw new InvalidOperationException($"Configuration validation failed: {string.Join(", ", errors)}");
        }

        _logger.LogDebug("{ProcessorType}: Configuration validation passed", ProcessorType);
    }
    private void PerformProcessing(Layer processWorker)
    {
        _logger.LogDebug(
            "{ProcessorType}: Processing data for Year: {Year}, CostSource: {CostSource}, SellSource: {SellSource}",
            ProcessorType, Year, CostSource, SellSource);

        var processedLayer = GetOrCreateProcessedLayer(processWorker);

        var dataSources = GetDataSources();

        var newRecords = ProcessRecords(dataSources);

        SaveProcessedLayer(processedLayer, newRecords);

        UpdateGoogleSheetReport(processedLayer.Id);

        _logger.LogInformation(
            "{ProcessorType}: Successfully processed {RecordCount} records for layer {LayerName} ({LayerId})",
            ProcessorType, newRecords.Count, processedLayer.Name, processedLayer.Id);
    }
    private Layer GetOrCreateProcessedLayer(Layer processWorker)
    {
        var processedLayer = _db.Layers
            .Where(x => x.ParentId == processWorker.Id && !x.IsDeleted && !x.IsCancelled)
            .OrderByDescending(x => x.CreatedAt)
            .FirstOrDefault();

        if (processedLayer == null)
        {
            processedLayer = new Layer
            {
                Id = Guid.NewGuid(),
                Type = LayerType.Processed,
                ParentId = processWorker.Id,
                Number = _db.Layers.Count() + 1,
                CreatedById = Guid.Parse("F392209E-123E-4651-A5A4-0B1D6CF9FF9D"),
                ModifiedById = Guid.Parse("F392209E-123E-4651-A5A4-0B1D6CF9FF9D"),
                CreatedAt = DateTime.UtcNow,
                ModifiedAt = DateTime.UtcNow
            };
            processedLayer.Name = $"L{processedLayer.Number}-P-{Year}-D6";

            _logger.LogDebug("{ProcessorType}: Created new processed layer {LayerName}",
                ProcessorType, processedLayer.Name);
        }
        else
        {
            processedLayer.ModifiedById = Guid.Parse("F392209E-123E-4651-A5A4-0B1D6CF9FF9D");
            processedLayer.ModifiedAt = DateTime.UtcNow;

            _logger.LogDebug("{ProcessorType}: Using existing processed layer {LayerName}",
                ProcessorType, processedLayer.Name);
        }

        return processedLayer;
    }
    private List<Layer> GetDataSources()
    {
        var costDataSource = _db.Layers
            .Include(layer => layer.Records!)
            .AsNoTracking()
            .FirstOrDefault(x => x.Name == CostSource && !x.IsDeleted && !x.IsCancelled);

        if (costDataSource == null)
        {
            throw new InvalidOperationException($"CostDataSource not found {CostSource}");
        }

        var sellDataSource = _db.Layers
            .Include(layer => layer.Records!)
            .AsNoTracking()
            .FirstOrDefault(x => x.Name == SellSource && !x.IsDeleted && !x.IsCancelled);

        if (sellDataSource == null)
        {
            throw new InvalidOperationException($"SellDataSource not found {SellSource}");
        }

        _logger.LogDebug("{ProcessorType}: Found both data sources data sources: {CostSource}, {SellSource}",
            ProcessorType, CostSource, SellSource);
        ;

        return [costDataSource, sellDataSource];
    }
    private List<Record> ProcessRecords(List<Layer> dataSources)
    {
        var newRecords = new List<Record>();

        // L8542-D-DEPARTMENTS
        var dictionary = _db.Layers.Include(x => x.Records).FirstOrDefault(x => x.Number == 8542);

        var departmentLookup = new Dictionary<string, string>();
        if (dictionary?.Records != null)
        {
            foreach (var dictRecord in dictionary.Records)
            {
                if (!string.IsNullOrEmpty(dictRecord.Desc1) && !string.IsNullOrEmpty(dictRecord.Code))
                {
                    departmentLookup[dictRecord.Desc1] = dictRecord.Code;
                }
            }

            _logger.LogDebug("{ProcessorType}: Loaded {DictCount} department mappings from dictionary",
                ProcessorType, departmentLookup.Count);
        }
        else
        {
            _logger.LogWarning("{ProcessorType}: Department dictionary (layer 8542) not found or has no records",
                ProcessorType);
        }

        // COSTS 
        
        var firstDataSource = dataSources.First();

        if (firstDataSource.Records == null || !firstDataSource.Records.Any())
        {
            _logger.LogWarning("{ProcessorType}: First data source has no records to process", ProcessorType);
            return newRecords;
        }

        var groupedData = firstDataSource.Records
            .Where(record => record is { Code: { Length: >= 8 }, Value1: not null })
            .Select(record => new
            {
                Month = record.Code!.Substring(4, 2),
                Type = record.Code.Substring(6, 2),
                OriginalDepartment = record.Desc1 ?? string.Empty,
                Value = record.Value1!.Value,
                Department = GetDepartmentByType(record.Code.Substring(6, 2), record.Desc1 ?? string.Empty)
            })
            .Select(x => new
            {
                x.Month,
                x.Type,
                x.Value,
                x.Department,
                DepartmentCode = departmentLookup.TryGetValue(x.Department, out var value1)
                    ? value1
                    : x.Department,
                FinalCode =
                    $"2{(departmentLookup.TryGetValue(x.Department, out var value) ? value : x.Department)}{x.Type}{x.Month}"
            })
            .GroupBy(x => x.FinalCode)
            .Select(group => new
            {
                FinalCode = group.Key,
                Department = group.First().Department,
                DepartmentCode = group.First().DepartmentCode,
                Type = group.First().Type,
                Month = group.First().Month,
                TotalValue = group.Sum(x => x.Value)
            })
            .ToList();

        foreach (var groupedRecord in groupedData)
        {
            // hack for 2206 ([2206]=[2206]+[2203]
            double value = groupedRecord.TotalValue;
            if (groupedRecord.FinalCode.StartsWith("2206"))
            {
                var month = groupedRecord.FinalCode.Substring(4, 2);
                var toSumUp = groupedData.FirstOrDefault(x => x.FinalCode == $"2203{month}");
                if (toSumUp == null)
                {
                    _logger.LogWarning("{ProcessorType}: 2203{month} not found (to sum up with 2206{month}", ProcessorType, month, month);
                }
                else
                {
                    value+= toSumUp.TotalValue;
                }
            }
            var newRecord = new Record
            {
                Id = Guid.NewGuid(),
                Code = groupedRecord.FinalCode,
                CreatedAt = DateTime.UtcNow,
                ModifiedAt = DateTime.UtcNow,
                Value1 = value,
                Desc1 = groupedRecord.Department
            };

            newRecords.Add(newRecord);

            _logger.LogDebug(
                "{ProcessorType}: Created summed record {NewRecordCode} with total value {Value} for department {Department} (code: {DepartmentCode}), type {Type}, month {Month}",
                ProcessorType, newRecord.Code, newRecord.Value1, groupedRecord.Department, groupedRecord.DepartmentCode,
                groupedRecord.Type, groupedRecord.Month);
        }
        
        // SELLS
        
        var secondDataSource = dataSources.Last();
        
        if (secondDataSource.Records == null || !secondDataSource.Records.Any())
        {
            _logger.LogWarning("{ProcessorType}: Second data source has no records to process", ProcessorType);
            return newRecords;
        }

        
        foreach (var sellCodeConfig in SellCodesConfiguration)
        {
            var calc = new BaseCalc(sellCodeConfig);
            if (!calc.IsFormulaCorrect())
            {
                _logger.LogDebug("{ProcessorType}: Invalid formula: {SellCodeConfig}", ProcessorType, sellCodeConfig);
                continue;
            }
            var codes = calc.GetCodes();
            var resultCode = calc.GetResultCode();

            for (var i = 1; i <= 12; i++)
            {
                var monthRecords = secondDataSource.Records
                    .Where(x => x.Code is { Length: 6 } && x.Code.EndsWith($"{i:D2}") && x.Value1.HasValue)
                    .ToList();
                if (monthRecords.Count == 0)
                {
                    continue;
                }
                var ingredients = monthRecords.ToDictionary(x => x.Code!.Substring(0, 4), x => x.Value1!.Value);
                double result = 0;
                try
                {
                    result = calc.Calculate(ingredients);
                }
                catch (Exception e)
                {
                    _logger.LogError(e, "{ProcessorType}: Failed to calculate sell code {ResultCode} for month {Month}",
                        ProcessorType, resultCode, i);
                }

                var newRecord = new Record
                {
                    Id = Guid.NewGuid(),
                    Code = $"{resultCode}{i:D2}",
                    CreatedAt = DateTime.UtcNow,
                    ModifiedAt = DateTime.UtcNow,
                    Value1 = result
                };
                newRecords.Add(newRecord);
            }
        }
        
        /*
        // Process records from secondDataSource
        foreach (var record in secondDataSource.Records)
        {
            if (string.IsNullOrEmpty(record.Code) || record.Code.Length < 6 || !record.Value1.HasValue)
            {
                continue;
            }
            
            // Extract month from last two digits
            var monthStr = record.Code.Substring(record.Code.Length - 2);
            if (!int.TryParse(monthStr, out var month) || month < 1 || month > 13)
            {
                _logger.LogDebug("{ProcessorType}: Invalid month in code {Code}", ProcessorType, record.Code);
                continue;
            }

            // Extract base code (without month)
            var baseCode = record.Code.Substring(0, record.Code.Length - 2);
            
            // Check if we have mapping for this code
            if (mappingDictionary.TryGetValue(baseCode, out var targetCode))
            {
                // Create target code with month
                var targetCodeWithMonth = $"{targetCode}{monthStr}";
                
                // Add/sum value
                if (sellResults.ContainsKey(targetCodeWithMonth))
                {
                    sellResults[targetCodeWithMonth] += record.Value1.Value;
                    _logger.LogDebug("{ProcessorType}: Added to existing record {TargetCode}: {Value} (total: {Total})",
                        ProcessorType, targetCodeWithMonth, record.Value1.Value, sellResults[targetCodeWithMonth]);
                }
                else
                {
                    sellResults[targetCodeWithMonth] = record.Value1.Value;
                    _logger.LogDebug("{ProcessorType}: Created new record {TargetCode}: {Value}",
                        ProcessorType, targetCodeWithMonth, record.Value1.Value);
                }
            }
            else
            {
                _logger.LogDebug("{ProcessorType}: No mapping found for code {BaseCode}", ProcessorType, baseCode);
            }
        }

        // Add results to newRecords
        foreach (var sellResult in sellResults)
        {
            var newRecord = new Record
            {
                Id = Guid.NewGuid(),
                Code = sellResult.Key,
                CreatedAt = DateTime.UtcNow,
                ModifiedAt = DateTime.UtcNow,
                Value1 = sellResult.Value
            };

            newRecords.Add(newRecord);

            _logger.LogDebug("{ProcessorType}: Added sell record {Code} with value {Value}",
                ProcessorType, newRecord.Code, newRecord.Value1);
        }

        _logger.LogInformation("{ProcessorType}: Processed {SellRecordCount} sell records from {OriginalSellCount} source records",
            ProcessorType, sellResults.Count, secondDataSource.Records.Count);

        _logger.LogInformation(
            "{ProcessorType}: Processed {GroupCount} unique grouped records from {OriginalCount} original records",
            ProcessorType, newRecords.Count, firstDataSource.Records.Count);
*/
        return newRecords;
    }
    private void SaveProcessedLayer(Layer processedLayer, List<Record> newRecords)
    {
        var existsInDb = _db.Layers.Any(x => x.Id == processedLayer.Id);

        if (!existsInDb)
        {
            _db.Layers.Add(processedLayer);
            _logger.LogDebug("{ProcessorType}: Added new processed layer to database", ProcessorType);
        }
        else
        {
            _db.Layers.Update(processedLayer);
            _logger.LogDebug("{ProcessorType}: Updated existing processed layer in database", ProcessorType);
        }

        SaveRecords(processedLayer.Id, newRecords);
        _db.SaveChanges();

        _logger.LogDebug("{ProcessorType}: Saved {RecordCount} records for layer {LayerId}",
            ProcessorType, newRecords.Count, processedLayer.Id);
    }
    private void SaveRecords(Guid layerId, ICollection<Record> records)
    {
        // Remove existing records for this layer
        var toDelete = _db.Records.Where(x => x.LayerId == layerId).ToList();
        if (toDelete.Count > 0)
        {
            _db.Records.RemoveRange(toDelete);
            _logger.LogDebug("{ProcessorType}: Removed {DeletedCount} existing records for layer {LayerId}",
                ProcessorType, toDelete.Count, layerId);
        }

        // Add new records
        foreach (var record in records)
        {
            record.CreatedById = Guid.Parse("F392209E-123E-4651-A5A4-0B1D6CF9FF9D");
            record.CreatedAt = DateTime.UtcNow;
            record.ModifiedById = Guid.Parse("F392209E-123E-4651-A5A4-0B1D6CF9FF9D");
            record.ModifiedAt = DateTime.UtcNow;
            record.LayerId = layerId;
            _db.Records.Add(record);
        }

        _logger.LogDebug("{ProcessorType}: Added {RecordCount} new records for layer {LayerId}",
            ProcessorType, records.Count, layerId);
    }
    private string? GetRecordValue(ICollection<Record> records, string code)
    {
        return records.FirstOrDefault(x => x.Code == code)?.Desc1;
    }
    private string GetDepartmentByType(string type, string originalDepartment)
    {
        var typesThatUseDepartment = new[] { "02", "09", "10", "11", "12", "13", "14", "15" };
        var typesThatUseAK = new[] { "03", "06", "07", "08" };

        if (typesThatUseDepartment.Contains(type))
        {
            return string.IsNullOrEmpty(originalDepartment) ? "OTHER" : originalDepartment;
        }

        if (typesThatUseAK.Contains(type))
        {
            return "AK";
        }

        if (type == "04")
        {
            return "PU";
        }

        if (type == "05")
        {
            return "OTHER";
        }

        {
            _logger.LogWarning("{ProcessorType}: Unknown type {Type}, using {Department}",
                nameof(MorskaD6Processor), type,
                string.IsNullOrEmpty(originalDepartment) ? "OTHER" : originalDepartment);
            return string.IsNullOrEmpty(originalDepartment) ? "OTHER" : originalDepartment;
        }
    }

    // Export to Google

    private void UpdateGoogleSheetReport(Guid sourceId)
    {
        const string googleSheetName = "Raport_R6_DRAFT_2025";
        try
        {
            const string sheetId = "19AljwrZRg2Rc5hagkfK3u8LIo3x9_GmFNnZEeOJ5g_g";
        
            _logger.LogDebug("{ProcessorType}: Updating Google Sheet report {SheetName}",
                ProcessorType, googleSheetName);
        
            // Get processed layer data
            var processedLayer = _db.Layers
                .Where(x => x.Id == sourceId)
                .Include(x => x.Records)
                .AsNoTracking()
                .FirstOrDefault();

            if (processedLayer == null)
            {
                throw new InvalidOperationException($"Processed layer {sourceId} not found");
            }

            // Get codes from sheet header (row 4, columns C to AA)
            var codesResponse = _googleSheetValues.Get(sheetId, $"{googleSheetName}!C4:AD4").Execute();
            var codesRow = codesResponse.Values[0];

            // Update data based on 6-digit codes from processedLayer
            UpdateMonthlyDataFromCodes(sheetId, codesRow, processedLayer);
            Thread.Sleep(1000);

            // Update yearly summary data (row 20)
            UpdateYearlySummaryData(sheetId, codesRow, processedLayer);
            Thread.Sleep(1000);

            // Update timestamps
            UpdateTimestamps(sheetId, processedLayer);
            Thread.Sleep(1000);

            _logger.LogInformation("{ProcessorType}: Successfully updated Google Sheet report {SheetName}",
                ProcessorType, googleSheetName);
        }
        catch (Exception e)
        {
            _logger.LogError(e, "{ProcessorType}: Failed to update Google Sheet report {SheetName}",
                ProcessorType, googleSheetName);
            throw;
        }
    }

    private void UpdateYearlySummaryData(string sheetId, IList<object> codesRow, Layer processedLayer)
    {
        const string googleSheetName = "Raport_R6_DRAFT_2025";
    
        if (processedLayer.Records == null)
        {
            _logger.LogWarning("{ProcessorType}: No records found in processed layer for yearly summary", ProcessorType);
            return;
        }

        var summaryValues = new List<object>();

        foreach (string fourDigitCode in codesRow)
        {
            // Calculate sum for all months (01-12) for this 4-digit code
            double yearlySum = 0;
        
            for (var month = 1; month <= 12; month++)
            {
                var sixDigitCode = $"{fourDigitCode}{month:D2}";
                var record = processedLayer.Records.FirstOrDefault(x => x.Code == sixDigitCode);
            
                if (record?.Value1.HasValue == true)
                {
                    yearlySum += record.Value1.Value;
                }
            }

            summaryValues.Add(yearlySum.ToString(CultureInfo.GetCultureInfo("pl-PL")));

            _logger.LogDebug("{ProcessorType}: Calculated yearly sum for code {FourDigitCode}: {YearlySum}",
                ProcessorType, fourDigitCode, yearlySum);
        }

        // Update row 20 with yearly sums
        var valueRange = new ValueRange
        {
            Values = new List<IList<object>> { summaryValues }
        };

        var columnStart = "C";
        var columnEnd = GetColumnLetter(codesRow.Count + 1); // +1 because we start from C (index 2)
        var range = $"{googleSheetName}!{columnStart}20:{columnEnd}20";

        var update = _googleSheetValues.Update(valueRange, sheetId, range);
        update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
        update.Execute();

        _logger.LogInformation("{ProcessorType}: Updated yearly summary data in row 20 with {CodeCount} totals",
            ProcessorType, codesRow.Count);
    }

    private void UpdateMonthlyDataFromCodes(string sheetId, IList<object> codesRow, Layer processedLayer)
    {
        const string googleSheetName = "Raport_R6_DRAFT_2025";

        if (processedLayer.Records == null)
        {
            _logger.LogWarning("{ProcessorType}: No records found in processed layer", ProcessorType);
            return;
        }

        // Create a dictionary to store updates for each cell
        var cellUpdates = new Dictionary<string, string>();

        foreach (var record in processedLayer.Records)
        {
            if (string.IsNullOrEmpty(record.Code) || record.Code.Length != 6)
            {
                _logger.LogWarning("{ProcessorType}: Invalid code format in record {RecordId}: {Code}",
                    ProcessorType, record.Id, record.Code);
                continue;
            }

            // Extract 4-digit code and month from 6-digit code
            var fourDigitCode = record.Code.Substring(0, 4);
            var monthStr = record.Code.Substring(4, 2);

            if (!int.TryParse(monthStr, out var month) || month < 1 || month > 12)
            {
                _logger.LogWarning("{ProcessorType}: Invalid month in code {Code}", ProcessorType, record.Code);
                continue;
            }

            // Find column index for the 4-digit code
            var columnIndex = -1;
            for (var i = 0; i < codesRow.Count; i++)
            {
                if (codesRow[i]?.ToString() == fourDigitCode)
                {
                    columnIndex = i;
                    break;
                }
            }

            if (columnIndex == -1)
            {
                _logger.LogWarning("{ProcessorType}: Code {FourDigitCode} not found in sheet header",
                    ProcessorType, fourDigitCode);
                continue;
            }

            // Calculate row (month 01 = row 7, month 02 = row 8, etc.)
            var targetRow = 6 + month; // row 7 for month 01, row 8 for month 02, etc.

            // Calculate column letter (C = index 0, D = index 1, etc.)
            var targetColumn = GetColumnLetter(columnIndex + 2); // +2 because C is the first column (index 0)

            var cellAddress = $"{targetColumn}{targetRow}";
            var value = record.Value1?.ToString(CultureInfo.GetCultureInfo("pl-PL")) ?? "0";

            cellUpdates[cellAddress] = value;

            _logger.LogDebug(
                "{ProcessorType}: Mapping code {SixDigitCode} (base: {FourDigitCode}, month: {Month}) to cell {CellAddress} with value {Value}",
                ProcessorType, record.Code, fourDigitCode, month, cellAddress, value);
        }

        // Batch update all cells
        if (cellUpdates.Any())
        {
            var batchUpdateRequest = new BatchUpdateValuesRequest
            {
                ValueInputOption = "USER_ENTERED",
                Data = cellUpdates.Select(kvp => new ValueRange
                {
                    Range = $"{googleSheetName}!{kvp.Key}",
                    Values = new List<IList<object>> { new List<object> { kvp.Value } }
                }).ToList()
            };

            var batchUpdate = _googleSheetValues.BatchUpdate(batchUpdateRequest, sheetId);
            batchUpdate.Execute();

            _logger.LogInformation("{ProcessorType}: Updated {CellCount} cells in Google Sheet",
                ProcessorType, cellUpdates.Count);
        }
        else
        {
            _logger.LogWarning("{ProcessorType}: No valid data found to update in Google Sheet", ProcessorType);
        }
    }

    private string GetColumnLetter(int columnIndex)
    {
        var columnLetter = string.Empty;
        while (columnIndex >= 0)
        {
            columnLetter = (char)('A' + (columnIndex % 26)) + columnLetter;
            columnIndex = columnIndex / 26 - 1;
        }

        return columnLetter;
    }

    private void UpdateTimestamps(string sheetId, Layer processedLayer)
    {
        const string googleSheetName = "Raport_R6_DRAFT_2025";

        var timeUtc = processedLayer.ModifiedAt.ToString("dd.MM.yyyy HH:mm:ss", CultureInfo.GetCultureInfo("pl-PL"));

        var warsawTimeZone = TimeZoneInfo.FindSystemTimeZoneById("Central European Standard Time");
        var warsawTime = TimeZoneInfo.ConvertTimeFromUtc(processedLayer.ModifiedAt.ToUniversalTime(), warsawTimeZone);
        var timeWarsaw = warsawTime.ToString("dd.MM.yyyy HH:mm:ss", CultureInfo.GetCultureInfo("pl-PL"));

        var valueRangeTime = new ValueRange
        {
            Values = new List<IList<object>>
            {
                new List<object> { timeUtc },
                new List<object> { timeWarsaw }
            }
        };

        var updateTime = _googleSheetValues.Update(valueRangeTime, sheetId, $"{googleSheetName}!G1:G2");
        updateTime.ValueInputOption =
            SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
        updateTime.Execute();

        _logger.LogDebug("{ProcessorType}: Updated timestamps in Google Sheet - UTC: {TimeUtc}, Warsaw: {TimeWarsaw}",
            ProcessorType, timeUtc, timeWarsaw);
    }
}