// Required NuGet packages (add to consuming *DAL.csproj if missing):
//   ExcelDataReader         — Excel file reading
//   ExcelDataReader.DataSet — .AsDataSet() extension
//   System.Text.Encoding.CodePages — CodePagesEncodingProvider
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using ExcelDataReader;

namespace GB5Shared.FileImport
{
    // Promoted from AdminDAL/CustomCode/FileImport (Phase 0 of the Ice Import migration) —
    // shared by BulkIceImport and the new IceImport execution engine.
    public class FileToDataTableService : IFileToDataTableService
    {
        public async Task<DataTable> ReadAsync(
            string filePath,
            ImportFileTypeEnum fileType,
            CancellationToken ct = default)
        {
            byte[] bytes = await File.ReadAllBytesAsync(filePath, ct).ConfigureAwait(false);
            return await ReadFromBytesAsync(bytes, fileType, ct).ConfigureAwait(false);
        }

        public Task<DataTable> ReadFromBytesAsync(
            byte[] bytes,
            ImportFileTypeEnum fileType,
            CancellationToken ct = default)
        {
            return fileType switch
            {
                ImportFileTypeEnum.Excel => ReadExcelFromBytesAsync(bytes, ct),
                ImportFileTypeEnum.Csv   => ReadCsvFromBytesAsync(bytes, ct),
                _                        => throw new ArgumentOutOfRangeException(nameof(fileType), fileType, null)
            };
        }

        // ── Excel ────────────────────────────────────────────────────────────

        private static Task<DataTable> ReadExcelFromBytesAsync(byte[] bytes, CancellationToken ct)
        {
            System.Text.Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

            using var ms     = new MemoryStream(bytes);
            using var reader = ExcelReaderFactory.CreateReader(ms);

            var ds = reader.AsDataSet(new ExcelDataSetConfiguration
            {
                ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = false }
            });

            if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                return Task.FromResult(new DataTable());

            var sheet = ds.Tables[0];

            // Merge-header detection — preserved from legacy ExcelDataReader.ProcessHeaders.
            // If row-0 is empty for a column while the previous column had a value,
            // the template uses two-row merged headers.
            bool   mergeHeader = false;
            string prevGroup   = string.Empty;

            for (int col = 0; col < sheet.Columns.Count; col++)
            {
                string header1 = sheet.Rows[0][col]?.ToString()?.Trim() ?? string.Empty;
                if (string.IsNullOrEmpty(header1) && !string.IsNullOrEmpty(prevGroup))
                {
                    mergeHeader = true;
                    break;
                }
                if (!string.IsNullOrEmpty(header1))
                    prevGroup = header1;
            }

            return Task.FromResult(mergeHeader
                ? ReadWithMergeHeaders(sheet)
                : ReadWithoutMergeHeaders(sheet));
        }

        // Single-row header: row 0 = column names, rows 1+ = data.
        private static DataTable ReadWithoutMergeHeaders(DataTable sheet)
        {
            var table = new DataTable();
            for (int col = 0; col < sheet.Columns.Count; col++)
            {
                string name = sheet.Rows[0][col]?.ToString()?.Trim().ToUpperInvariant() ?? string.Empty;
                table.Columns.Add(string.IsNullOrEmpty(name) ? $"COL{col}" : name, typeof(string));
            }
            for (int row = 1; row < sheet.Rows.Count; row++)
            {
                var dr = table.NewRow();
                for (int col = 0; col < table.Columns.Count; col++)
                    dr[col] = FormatCellValue(table.Columns[col].ColumnName, sheet.Rows[row][col]);
                table.Rows.Add(dr);
            }
            return table;
        }

        // Two-row merged header: row 0 = group, row 1 = sub-column.
        // Column name = "{GROUP}_{SUBCOLUMN}" when group is non-empty.
        private static DataTable ReadWithMergeHeaders(DataTable sheet)
        {
            var table = new DataTable();
            if (sheet.Rows.Count < 2) return table;

            string currentGroup = string.Empty;
            for (int col = 0; col < sheet.Columns.Count; col++)
            {
                string group = sheet.Rows[0][col]?.ToString()?.Trim().ToUpperInvariant() ?? string.Empty;
                string sub   = sheet.Rows[1][col]?.ToString()?.Trim().ToUpperInvariant() ?? $"COL{col}";
                if (!string.IsNullOrEmpty(group)) currentGroup = group;
                string colName = string.IsNullOrEmpty(currentGroup) ? sub : $"{currentGroup}_{sub}";
                table.Columns.Add(string.IsNullOrEmpty(colName) ? $"COL{col}" : colName, typeof(string));
            }
            for (int row = 2; row < sheet.Rows.Count; row++)
            {
                var dr = table.NewRow();
                for (int col = 0; col < table.Columns.Count; col++)
                    dr[col] = FormatCellValue(table.Columns[col].ColumnName, sheet.Rows[row][col]);
                table.Rows.Add(dr);
            }
            return table;
        }

        // Numeric values in DATE columns are OA dates — convert to "dd-MMM-yyyy".
        private static string FormatCellValue(string columnName, object? raw)
        {
            if (raw is null || raw == DBNull.Value) return string.Empty;
            string str = raw.ToString() ?? string.Empty;
            if (columnName.Contains("DATE", StringComparison.OrdinalIgnoreCase)
                && double.TryParse(str, out double oaDate))
            {
                return DateTime.FromOADate(oaDate).ToString("dd-MMM-yyyy");
            }
            return str;
        }

        // ── CSV ──────────────────────────────────────────────────────────────

        private static async Task<DataTable> ReadCsvFromBytesAsync(byte[] bytes, CancellationToken ct)
        {
            var table = new DataTable();
            using var reader = new StreamReader(new MemoryStream(bytes), Encoding.UTF8);

            string? headerLine = await reader.ReadLineAsync(ct).ConfigureAwait(false);
            if (headerLine is null) return table;

            foreach (var h in ParseCsvLine(headerLine))
                table.Columns.Add(h.ToUpperInvariant(), typeof(string));

            string? line;
            while ((line = await reader.ReadLineAsync(ct).ConfigureAwait(false)) is not null)
            {
                ct.ThrowIfCancellationRequested();
                if (string.IsNullOrWhiteSpace(line)) continue;
                var values = ParseCsvLine(line);
                var dr     = table.NewRow();
                for (int i = 0; i < table.Columns.Count; i++)
                    dr[i] = i < values.Length ? values[i] : string.Empty;
                table.Rows.Add(dr);
            }
            return table;
        }

        // Minimal RFC 4180 parser — handles quoted fields and embedded commas.
        private static string[] ParseCsvLine(string line)
        {
            var    fields   = new List<string>();
            var    current  = new StringBuilder();
            bool   inQuotes = false;

            for (int i = 0; i < line.Length; i++)
            {
                char c = line[i];
                if (inQuotes)
                {
                    if (c == '"' && i + 1 < line.Length && line[i + 1] == '"')
                    { current.Append('"'); i++; }
                    else if (c == '"')
                    { inQuotes = false; }
                    else
                    { current.Append(c); }
                }
                else
                {
                    if (c == '"')       { inQuotes = true; }
                    else if (c == ',')  { fields.Add(current.ToString()); current.Clear(); }
                    else                { current.Append(c); }
                }
            }
            fields.Add(current.ToString());
            return fields.ToArray();
        }
    }
}