using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ClosedXML.Excel;
using FrameworkDAL.CustomCode.GOP;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.GOP;
using Microsoft.AspNetCore.Http;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace FrameworkBLL.GOP
{
    public class GopFileUploadBLL : IGopFileUploadBLL
    {
        private readonly IGopQueueBLL  _GopQueueBLL;
        private readonly IGopQueueDAL  _GopQueueDAL;

        public GopFileUploadBLL(IGopQueueBLL GopQueueBLL, IGopQueueDAL GopQueueDAL)
        {
            _GopQueueBLL = GopQueueBLL;
            _GopQueueDAL = GopQueueDAL;
        }

        // ═══════════════════════════════════════════════════════════
        // UPLOAD AND SUBMIT
        // 1. Validate file
        // 2. Record TGopSourceFile
        // 3. Parse file → List<string> payloads
        // 4. SubmitExecution per row
        // 5. Update source file status, return summary
        // ═══════════════════════════════════════════════════════════
        public async Task<GopFileUploadResponseDTO> UploadAndSubmit(
            IFormFile File,
            string    SourceType,
            string    Environment,
            LoginDTO  LoginDTO)
        {
            if (File == null || File.Length == 0)
                throw new ArgumentException("No file provided or file is empty.");
            if (string.IsNullOrWhiteSpace(SourceType))
                throw new ArgumentException("SourceType is required.");

            // 1. Register source file record
            var fileDTO = new GopSourceFileDTO
            {
                ClientId         = LoginDTO.ClientId,
                SourceCode       = SourceType,
                OriginalFileName = File.FileName,
                FileSizeBytes    = File.Length,
                MimeType         = File.ContentType,
                UploadedBy       = LoginDTO.UserCode,
                Status           = "Uploaded"
            };

            int fileId = await _GopQueueDAL.InsertSourceFile(fileDTO, LoginDTO);

            // 2. Parse file into individual JSON payloads
            List<string> payloads;
            try
            {
                payloads = await ParseFileAsync(File);
            }
            catch (Exception ex)
            {
                await _GopQueueDAL.UpdateSourceFileStatus(LoginDTO.ClientId, fileId, "ParseFailed", null, LoginDTO);
                throw new Exception($"File parse error: {ex.Message}");
            }

            if (payloads.Count == 0)
                throw new InvalidOperationException("The uploaded file contains no records.");

            // 3. Submit each row as a GOP execution
            var results = new List<GopFileUploadItemResultDTO>(payloads.Count);
            int firstQueueId = 0;

            for (int i = 0; i < payloads.Count; i++)
            {
                var item = new GopFileUploadItemResultDTO { RowIndex = i + 1 };
                try
                {
                    var response = await _GopQueueBLL.SubmitExecution(
                        new GopSubmitRequestDTO
                        {
                            SourceCode  = SourceType,
                            PayloadJson = payloads[i],
                            Priority    = 5
                        },
                        LoginDTO);

                    item.QueueId     = response.QueueId;
                    item.ExecutionId = response.ExecutionId;
                    item.Status      = "Queued";

                    if (i == 0) firstQueueId = response.QueueId;
                }
                catch (Exception ex)
                {
                    item.Status = "Failed";
                    item.Error  = ex.Message;
                }

                results.Add(item);
            }

            // 4. Update source file to Queued (link first QueueId for traceability)
            int submitted = results.Count(r => r.Status == "Queued");
            int failed    = results.Count(r => r.Status == "Failed");

            string fileStatus = failed == 0          ? "Queued"
                              : submitted == 0        ? "Failed"
                                                      : "PartialFailed";

            await _GopQueueDAL.UpdateSourceFileStatus(
                LoginDTO.ClientId, fileId, fileStatus,
                firstQueueId > 0 ? firstQueueId : (int?)null,
                LoginDTO);

            return new GopFileUploadResponseDTO
            {
                FileId         = fileId,
                FileName       = File.FileName,
                SourceType     = SourceType,
                TotalRecords   = payloads.Count,
                SubmittedCount = submitted,
                FailedCount    = failed,
                Status         = fileStatus,
                Results        = results
            };
        }

        // ═══════════════════════════════════════════════════════════
        // FILE PARSER  —  routes to format-specific parser
        // Supported: .json  .txt  .csv  .xlsx  .xls
        // ═══════════════════════════════════════════════════════════
        private async Task<List<string>> ParseFileAsync(IFormFile file)
        {
            string ext = Path.GetExtension(file.FileName).ToLowerInvariant();

            return ext switch
            {
                ".json" => await ParseJsonFileAsync(file),
                ".txt"  => await ParseJsonFileAsync(file),   // TXT treated as JSON
                ".csv"  => await ParseCsvFileAsync(file),
                ".xlsx" => ParseExcelFile(file),
                ".xls"  => ParseExcelFile(file),
                _       => throw new NotSupportedException(
                                $"File type '{ext}' is not supported. Allowed: .json, .txt, .csv, .xlsx, .xls")
            };
        }

        // ── JSON / TXT parser ─────────────────────────────────────
        // Single JObject  → one payload
        // JArray          → one payload per element
        // NDJSON (one JSON object per line) → one payload per line
        private async Task<List<string>> ParseJsonFileAsync(IFormFile file)
        {
            string content;
            using (var reader = new StreamReader(file.OpenReadStream(), Encoding.UTF8))
                content = await reader.ReadToEndAsync();

            content = content.Trim();
            var payloads = new List<string>();

            if (content.StartsWith("["))
            {
                // JSON array
                var array = JArray.Parse(content);
                foreach (var token in array)
                    payloads.Add(token.ToString(Formatting.None));
            }
            else if (content.StartsWith("{"))
            {
                // Single JSON object
                payloads.Add(JObject.Parse(content).ToString(Formatting.None));
            }
            else
            {
                // Try NDJSON: one JSON object per non-empty line
                foreach (var line in content.Split('\n'))
                {
                    string trimmed = line.Trim();
                    if (string.IsNullOrWhiteSpace(trimmed)) continue;
                    payloads.Add(JObject.Parse(trimmed).ToString(Formatting.None));
                }
            }

            return payloads;
        }

        // ── CSV parser ────────────────────────────────────────────
        // Row 1  = header names  (column keys)
        // Row 2+ = data rows     → each becomes a JObject
        private async Task<List<string>> ParseCsvFileAsync(IFormFile file)
        {
            var lines = new List<string>();
            using (var reader = new StreamReader(file.OpenReadStream(), Encoding.UTF8))
            {
                string? line;
                while ((line = await reader.ReadLineAsync()) != null)
                    lines.Add(line);
            }

            if (lines.Count < 2)
                throw new InvalidOperationException("CSV must contain a header row and at least one data row.");

            string[] headers = SplitCsvLine(lines[0]);
            var payloads = new List<string>(lines.Count - 1);

            for (int i = 1; i < lines.Count; i++)
            {
                if (string.IsNullOrWhiteSpace(lines[i])) continue;

                string[] values = SplitCsvLine(lines[i]);
                var obj = new JObject();

                for (int h = 0; h < headers.Length; h++)
                {
                    string key   = headers[h].Trim();
                    string value = h < values.Length ? values[h].Trim() : string.Empty;

                    // Try to infer numeric / boolean types
                    if (long.TryParse(value, out long lv))
                        obj[key] = lv;
                    else if (double.TryParse(value, System.Globalization.NumberStyles.Any,
                                 System.Globalization.CultureInfo.InvariantCulture, out double dv))
                        obj[key] = dv;
                    else if (bool.TryParse(value, out bool bv))
                        obj[key] = bv;
                    else
                        obj[key] = value;
                }

                payloads.Add(obj.ToString(Formatting.None));
            }

            return payloads;
        }

        /// <summary>RFC 4180-compliant CSV line splitter supporting quoted fields.</summary>
        private static string[] SplitCsvLine(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('"');   // escaped double-quote
                        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();
        }

        // ── Excel parser (ClosedXML) ──────────────────────────────
        // Row 1  = header names
        // Row 2+ = data rows → each becomes a JObject
        private static List<string> ParseExcelFile(IFormFile file)
        {
            using var stream   = file.OpenReadStream();
            using var workbook = new XLWorkbook(stream);
            var sheet = workbook.Worksheets.First();

            var rows     = sheet.RowsUsed().ToList();
            if (rows.Count < 2)
                throw new InvalidOperationException("Excel file must contain a header row and at least one data row.");

            // Extract headers from row 1
            var headerRow = rows[0];
            var headers   = headerRow.CellsUsed()
                                     .Select(c => c.GetString().Trim())
                                     .ToList();

            var payloads = new List<string>(rows.Count - 1);

            for (int r = 1; r < rows.Count; r++)
            {
                var row = rows[r];
                var obj = new JObject();

                for (int h = 0; h < headers.Count; h++)
                {
                    var cell  = row.Cell(h + 1);
                    string key = headers[h];

                    obj[key] = cell.DataType switch
                    {
                        XLDataType.Number  => new JValue(cell.GetDouble()),
                        XLDataType.Boolean => new JValue(cell.GetBoolean()),
                        XLDataType.DateTime => new JValue(
                            cell.GetDateTime().ToString("yyyy-MM-ddTHH:mm:ss")),
                        _ => new JValue(cell.GetString())
                    };
                }

                payloads.Add(obj.ToString(Formatting.None));
            }

            return payloads;
        }
    }
}