AIExcelParser.cs

using OfficeOpenXml;
using System.Net.Http;
using System.Text;
using System.Text.Json;
using System.Text.Json.Serialization;

namespace SalesAPI;

public class AIExcelParser
{
    private readonly string _apiKey;
    private static readonly HttpClient _httpClient = new HttpClient();

    public AIExcelParser(string apiKey)
    {
        _apiKey = apiKey;
    }

    public async Task<List<Product>> ParsePriceListAsync(string filePath)
    {
        var preview = GetExcelPreview(filePath);
        var analysis = await AnalyzeStructure(preview, "pricelist");
        return ParseProductsFromAnalysis(filePath, analysis);
    }

    public async Task<List<Client>> ParseClientListAsync(string filePath)
    {
        var preview = GetExcelPreview(filePath);
        var analysis = await AnalyzeStructure(preview, "client list");
        return ParseClientsFromAnalysis(filePath, analysis);
    }

    public async Task<List<Inventory>> ParseInventoryAsync(string filePath)
    {
        var preview = GetExcelPreview(filePath);
        var analysis = await AnalyzeStructure(preview, "inventory/stock");
        return ParseInventoryFromAnalysis(filePath, analysis);
    }

    private string GetExcelPreview(string filePath)
    {
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using var package = new ExcelPackage(new FileInfo(filePath));
        var worksheet = package.Workbook.Worksheets.FirstOrDefault();

        var preview = new StringBuilder();
        var maxRow = Math.Min(20, worksheet.Dimension?.End.Row ?? 0);
        var maxCol = worksheet.Dimension?.End.Column ?? 0;

        for (int row = 1; row <= maxRow; row++)
        {
            var rowData = new List<string>();
            for (int col = 1; col <= maxCol; col++)
            {
                rowData.Add(worksheet.Cells[row, col].Text ?? "");
            }
            preview.AppendLine($"Row {row}: {string.Join(" | ", rowData)}");
        }

        return preview.ToString();
    }

    private async Task<string> AnalyzeStructure(string preview, string fileType)
    {
        string prompt;

        if (fileType == "pricelist")
        {
            // Hardcoded structure for Deli pricelist - no AI needed
            return @"{
  ""headerRow"": 2,
  ""columns"": {
    ""sapCode"": 2,
    ""description"": 13,
    ""priceInclVAT"": 17,
    ""priceExclVAT"": 15,
    ""category"": 3,
    ""barcode"": 11,
    ""casePack"": 19,
    ""innerMOQ"": 20,
    ""refNo"": 6
  }
}";
        }
        else if (fileType == "client list")
        {
            // Customer Base workbook — Sold-to party code is the primary client identity (column 7).
            return @"{
  ""headerRow"": 1,
  ""columns"": {
    ""searchTerm"": 2,
    ""accountId"": 5,
    ""billToName"": 6,
    ""soldToCode"": 7,
    ""companyName"": 8,
    ""address"": 9,
    ""email"": 11,
    ""phoneNumber"": 12,
    ""vatNumber"": 13
  }
}";
        }
        else // inventory
        {
            prompt = $@"Analyze this Excel preview carefully and respond ONLY with valid JSON (no markdown, no explanations, no extra text).

This is a stock-on-hand (inventory/SOH) report from Deli Stationery. The first few rows are headers or titles.

Identify:
- The header row number (usually row 1 or 2 where column names appear)
- The exact column numbers (1-based) for these fields:

Return ONLY this exact JSON structure:

{{
  ""headerRow"": <number>,
  ""columns"": {{
    ""sapCode"": <column number for 'SAP Code', 'Material number', 'SAP', or similar - must be the main material identifier>,
    ""productCode"": <column number for 'Product Code', 'Product', 'Material code', 'Item code', or similar field - return null if no clear match>,
    ""quantity"": <column number for 'Available quantity', 'SOH', 'Stock Qty', 'On Hand', or similar>,
    ""description"": <column number for 'Description', 'Material description', 'Product name', or null if none>
  }}
}}

Preview of the Excel:
{preview}";
        }

        var requestBody = new
        {
            contents = new[]
            {
                new
                {
                    parts = new[]
                    {
                        new { text = prompt }
                    }
                }
            }
        };

        var json = JsonSerializer.Serialize(requestBody);
        var content = new StringContent(json, Encoding.UTF8, "application/json");

        var url = $"https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key={_apiKey}";

        var response = await _httpClient.PostAsync(url, content);
        var responseText = await response.Content.ReadAsStringAsync();

        if (!response.IsSuccessStatusCode)
        {
            throw new Exception($"Gemini API error: {responseText}");
        }

        var geminiResponse = JsonSerializer.Deserialize<GeminiResponse>(responseText);
        var text = geminiResponse?.candidates?[0]?.content?.parts?[0]?.text ?? "";

        Console.WriteLine($"[UPLOAD] Raw Gemini response: {responseText}");
        Console.WriteLine($"[UPLOAD] Extracted text: {text}");

        // Clean up markdown if present
        text = text.Replace("```json", "").Replace("```", "").Trim();

        Console.WriteLine($"[UPLOAD] Cleaned text: {text}");

        return text;
    }

    private List<Product> ParseProductsFromAnalysis(string filePath, string analysisJson)
    {
        var products = new List<Product>();

        try
        {
            Console.WriteLine($"[PARSE] Starting to parse products from: {analysisJson}");

            var analysis = JsonSerializer.Deserialize<ExcelAnalysis>(analysisJson);
            if (analysis == null) throw new Exception("Failed to parse analysis");

            Console.WriteLine($"[PARSE] HeaderRow: {analysis.HeaderRow}");
            Console.WriteLine($"[PARSE] SapCode column: {analysis.Columns.SapCode}");
            Console.WriteLine($"[PARSE] Description column: {analysis.Columns.Description}");
            Console.WriteLine($"[PARSE] PriceInclVAT column: {analysis.Columns.PriceInclVAT}");

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using var package = new ExcelPackage(new FileInfo(filePath));
            var worksheet = package.Workbook.Worksheets.FirstOrDefault();

            var maxRow = worksheet.Dimension?.End.Row ?? 0;
            Console.WriteLine($"[PARSE] Max row in Excel: {maxRow}");

            var cols = analysis.Columns;

            // Build image map: Excel row (0-indexed) -> Base64 string
            var imageMap = new Dictionary<int, string>();
            foreach (var drawing in worksheet.Drawings)
            {
                if (drawing is OfficeOpenXml.Drawing.ExcelPicture pic)
                {
                    try
                    {
                        var fromRow = pic.From.Row; // 0-indexed
                        var imageBytes = pic.Image.ImageBytes;
                        var base64 = Convert.ToBase64String(imageBytes);
                        imageMap[fromRow] = $"data:image/png;base64,{base64}";
                    }
                    catch (Exception imgEx)
                    {
                        Console.WriteLine($"[IMAGE] Error extracting image: {imgEx.Message}");
                    }
                }
            }
            Console.WriteLine($"[IMAGE] Total images extracted: {imageMap.Count}");

            for (int row = analysis.HeaderRow + 1; row <= maxRow; row++)
            {
                var sapCode = GetCellValue(worksheet, row, cols.SapCode);
                if (string.IsNullOrWhiteSpace(sapCode))
                {
                    Console.WriteLine($"[PARSE] Row {row}: Skipping - empty SAP code");
                    continue;
                }

                var desc = GetCellValue(worksheet, row, cols.Description);
                var priceIncl = cols.PriceInclVAT.HasValue
    ? GetNumericValue(worksheet, row, cols.PriceInclVAT.Value)
    : 0;

                // Map image: Excel rows are 1-indexed, imageMap is 0-indexed
                imageMap.TryGetValue(row - 1, out var imageData);

                Console.WriteLine($"[PARSE] Row {row}: SAP={sapCode}, Desc={desc}, Price={priceIncl}");

                if (priceIncl == 0)
                {
                    Console.WriteLine($"[PARSE] Row {row}: Skipping - price is 0");
                    continue;
                }

                products.Add(new Product
                {
                    SAPCode = sapCode,
                    RefNo = cols.RefNo.HasValue ? worksheet.Cells[row, cols.RefNo.Value].Text?.Trim() : null,
                    Description = desc ?? "Unknown Product",
                    Category = cols.Category.HasValue ? GetCellValue(worksheet, row, cols.Category.Value) : null,
                    PriceInclVAT = cols.PriceInclVAT.HasValue
    ? GetNumericValue(worksheet, row, cols.PriceInclVAT.Value)
    : 0,

                    PriceExclVAT = cols.PriceExclVAT.HasValue
    ? (worksheet.Cells[row, cols.PriceExclVAT.Value].Value as double? ?? 0)
    : 0,
                    Barcode = cols.Barcode.HasValue ? GetCellValue(worksheet, row, cols.Barcode.Value) : null,
                    CasePack = cols.CasePack.HasValue ? ParseNullableInt(GetCellValue(worksheet, row, cols.CasePack.Value)) : null,
                    InnerMOQ = cols.InnerMOQ.HasValue ? ParseNullableInt(GetCellValue(worksheet, row, cols.InnerMOQ.Value)) : null,
                    ImageData = imageData,
                    LastUpdated = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                });
            }

            var withImages = products.Count(p => p.ImageData != null);
            Console.WriteLine($"[PARSE] Total products parsed: {products.Count}, with images: {withImages}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[PARSE] Error parsing pricelist: {ex.Message}");
            Console.WriteLine($"[PARSE] Stack trace: {ex.StackTrace}");
        }

        return products;
    }

    private List<Client> ParseClientsFromAnalysis(string filePath, string analysisJson)
    {
        var clients = new List<Client>();

        try
        {
            var analysis = JsonSerializer.Deserialize<ExcelAnalysis>(analysisJson);
            if (analysis == null) throw new Exception("Failed to parse analysis");

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using var package = new ExcelPackage(new FileInfo(filePath));
            var worksheet = package.Workbook.Worksheets.FirstOrDefault();

            var maxRow = worksheet.Dimension?.End.Row ?? 0;
            var cols = analysis.Columns;

            for (int row = analysis.HeaderRow + 1; row <= maxRow; row++)
            {
                var soldToCode = cols.SoldToCode.HasValue ? GetCellValue(worksheet, row, cols.SoldToCode.Value) : null;
                if (string.IsNullOrWhiteSpace(soldToCode)) continue;

                var company = GetCellValue(worksheet, row, cols.CompanyName);
                if (string.IsNullOrWhiteSpace(company) && cols.BillToName.HasValue)
                    company = GetCellValue(worksheet, row, cols.BillToName.Value);
                if (string.IsNullOrWhiteSpace(company))
                    company = soldToCode;

                clients.Add(new Client
                {
                    SearchTerm = cols.SearchTerm.HasValue ? GetCellValue(worksheet, row, cols.SearchTerm.Value) : null,
                    CompanyName = company,
                    SoldToCode = soldToCode,
                    AccountId = cols.AccountId.HasValue ? GetCellValue(worksheet, row, cols.AccountId.Value) : null,
                    PhoneNumber = cols.PhoneNumber.HasValue ? GetCellValue(worksheet, row, cols.PhoneNumber.Value) : null,
                    Email = cols.Email.HasValue ? GetCellValue(worksheet, row, cols.Email.Value) : null,
                    Address = cols.Address.HasValue ? GetCellValue(worksheet, row, cols.Address.Value) : null,
                    VATNumber = cols.VatNumber.HasValue ? GetCellValue(worksheet, row, cols.VatNumber.Value) : null,
                    IsActive = true,
                    CreatedDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                });
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error parsing clients: {ex.Message}");
        }

        return clients;
    }

    private List<Inventory> ParseInventoryFromAnalysis(string filePath, string analysisJson)
    {
        var inventory = new List<Inventory>();

        try
        {
            var analysis = JsonSerializer.Deserialize<ExcelAnalysis>(analysisJson);
            if (analysis == null) throw new Exception("Failed to parse analysis");

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using var package = new ExcelPackage(new FileInfo(filePath));
            var worksheet = package.Workbook.Worksheets.FirstOrDefault();

            var maxRow = worksheet.Dimension?.End.Row ?? 0;
            var cols = analysis.Columns;

            for (int row = analysis.HeaderRow + 1; row <= maxRow; row++)
            {
                var sapCode = GetCellValue(worksheet, row, cols.SapCode);
                var productCode = cols.ProductCode.HasValue ? GetCellValue(worksheet, row, cols.ProductCode.Value)?.Trim() : null;
                if (string.IsNullOrWhiteSpace(sapCode)) continue;

                var qty = ParseInt(GetCellValue(worksheet, row, cols.Quantity));

                inventory.Add(new Inventory
                {
                    SAPCode = sapCode,
                    ProductCode = productCode,  // ← new line
                    Description = cols.Description.HasValue ? GetCellValue(worksheet, row, cols.Description.Value) : null,
                    QuantityAvailable = qty,
                    LastUpdated = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                });
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error parsing inventory: {ex.Message}");
        }

        return inventory;
    }

    private string? GetCellValue(ExcelWorksheet worksheet, int row, int? col)
    {
        if (!col.HasValue) return null;
        return worksheet.Cells[row, col.Value].Text?.Trim();
    }

    private double GetNumericValue(ExcelWorksheet worksheet, int row, int col)
    {
        var cell = worksheet.Cells[row, col];
        if (cell.Value is double d) return d;
        if (cell.Value is int i) return i;
        if (double.TryParse(cell.Text?.Replace("R", "").Replace(",", "").Trim(), out var parsed)) return parsed;
        // If formula, calculate: PriceExcl * (1 + VAT)
        var excl = worksheet.Cells[row, 15].Value as double? ?? 0;
        var vat = worksheet.Cells[row, 16].Value as double? ?? 0;
        if (excl > 0) return excl + (excl * vat);
        return 0;
    }
    private double ParseDouble(string? value)
    {
        if (string.IsNullOrWhiteSpace(value)) return 0;

        // Remove common currency symbols and spaces
        value = value.Replace("R", "")
                     .Replace("$", "")
                     .Replace("€", "")
                     .Replace("£", "")
                     .Replace(",", "")
                     .Replace(" ", "")
                     .Trim();

        if (double.TryParse(value, out var result)) return result;
        return 0;
    }

    private int ParseInt(string? value)
    {
        if (string.IsNullOrWhiteSpace(value)) return 0;

        // Remove commas and spaces from numbers
        value = value.Replace(",", "").Replace(" ", "").Trim();

        // Handle decimal numbers by converting to int
        if (double.TryParse(value, out var doubleResult))
        {
            return (int)doubleResult;
        }

        return 0;
    }

    private int? ParseNullableInt(string? value)
    {
        if (string.IsNullOrWhiteSpace(value)) return null;
        if (int.TryParse(value, out var result)) return result;
        return null;
    }
}

// Gemini API response models
public class GeminiResponse
{
    public Candidate[]? candidates { get; set; }
}

public class Candidate
{
    public Content? content { get; set; }
}

public class Content
{
    public Part[]? parts { get; set; }
}

public class Part
{
    public string? text { get; set; }
}

// Excel analysis models
public class ExcelAnalysis
{
    [JsonPropertyName("headerRow")]
    public int HeaderRow { get; set; }

    [JsonPropertyName("columns")]
    public ColumnMapping Columns { get; set; } = new();
}

public class ColumnMapping
{
    [JsonPropertyName("sapCode")]
    public int? SapCode { get; set; }

    [JsonPropertyName("refNo")]
    public int? RefNo { get; set; }

    [JsonPropertyName("description")]
    public int? Description { get; set; }

    [JsonPropertyName("priceInclVAT")]
    public int? PriceInclVAT { get; set; }

    [JsonPropertyName("priceExclVAT")]
    public int? PriceExclVAT { get; set; }

    [JsonPropertyName("category")]
    public int? Category { get; set; }

    [JsonPropertyName("barcode")]
    public int? Barcode { get; set; }

    [JsonPropertyName("casePack")]
    public int? CasePack { get; set; }

    [JsonPropertyName("innerMOQ")]
    public int? InnerMOQ { get; set; }

    [JsonPropertyName("billToName")]
    public int? BillToName { get; set; }

    [JsonPropertyName("companyName")]
    public int? CompanyName { get; set; }

    [JsonPropertyName("soldToCode")]
    public int? SoldToCode { get; set; }

    [JsonPropertyName("searchTerm")]
    public int? SearchTerm { get; set; }

    [JsonPropertyName("accountId")]
    public int? AccountId { get; set; }

    [JsonPropertyName("phoneNumber")]
    public int? PhoneNumber { get; set; }

    [JsonPropertyName("email")]
    public int? Email { get; set; }

    [JsonPropertyName("address")]
    public int? Address { get; set; }

    [JsonPropertyName("vatNumber")]
    public int? VatNumber { get; set; }

    [JsonPropertyName("quantity")]
    public int? Quantity { get; set; }

    [JsonPropertyName("productCode")]
    public int? ProductCode { get; set; }
}