using System.Data.Common;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using PayRollDAL.DTO.GateEntry;
using PayRollDAL.Query.GateEntry;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.Json;
using System.Threading.Tasks;

namespace PayRollDAL.CustomeCode.GateEntry
{
    public class GateEntryDAL : IGateEntryDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public GateEntryDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        // ── Get header + all detail rows ─────────────────────────────────

        public async Task<GateEntryDTO> GetGateEntry(int gateEntryId, LoginDTO loginDTO)
        {
            var param = new { gateentryid = gateEntryId };

            var header = await _QueryExecutor.QuerySingleAsync<GateEntryDTO>(
                loginDTO, GateEntryQB.GET_GATEENTRY, param).ConfigureAwait(false);

            if (header != null)
            {
                var details = await _QueryExecutor.QueryAsync<GateEntryDetailDTO>(
                    loginDTO, GateEntryQB.GET_GATEENTRYDETAIL, param).ConfigureAwait(false);

                header.GateEntryDetailArray = details?.ToList() ?? new List<GateEntryDetailDTO>();
            }

            return header!;
        }

        // ── Insert header + detail rows (transaction provided by BLL) ────

        public async Task<int> SaveGateEntry(
            GateEntryDTO gateEntryDTO, LoginDTO loginDTO, DbTransaction transaction)
        {
            int rows = await _QueryExecutor.ExecuteAsync(
                loginDTO, GateEntryQB.SAVE_GATEENTRY, gateEntryDTO, transaction).ConfigureAwait(false);

            if (rows > 0 && gateEntryDTO.GateEntryDetailArray?.Count > 0)
            {
                short slNo = 1;
                foreach (var detail in gateEntryDTO.GateEntryDetailArray)
                {
                    detail.GateEntryId = gateEntryDTO.GateEntryId;
                    detail.SlNo        = slNo++;
                    await _QueryExecutor.ExecuteAsync(
                        loginDTO, GateEntryQB.SAVE_GATEENTRYDETAIL, detail, transaction).ConfigureAwait(false);
                }
            }

            return rows;
        }

        // ── Update header + replace detail rows (transaction provided by BLL) ─

        public async Task<int> UpdateGateEntry(
            GateEntryDTO gateEntryDTO, LoginDTO loginDTO, DbTransaction transaction)
        {
            int rows = await _QueryExecutor.ExecuteAsync(
                loginDTO, GateEntryQB.UPDATE_GATEENTRY, gateEntryDTO, transaction).ConfigureAwait(false);

            // Delete existing detail rows then re-insert
            await _QueryExecutor.ExecuteAsync(
                loginDTO, GateEntryQB.DELETE_GATEENTRYDETAIL,
                new { gateentryid = gateEntryDTO.GateEntryId }, transaction).ConfigureAwait(false);

            if (gateEntryDTO.GateEntryDetailArray?.Count > 0)
            {
                short slNo = 1;
                foreach (var detail in gateEntryDTO.GateEntryDetailArray)
                {
                    detail.GateEntryId = gateEntryDTO.GateEntryId;
                    detail.SlNo        = slNo++;
                    await _QueryExecutor.ExecuteAsync(
                        loginDTO, GateEntryQB.SAVE_GATEENTRYDETAIL, detail, transaction).ConfigureAwait(false);
                }
            }

            return rows;
        }

        // ── Soft delete header (STATUS = 2) ──────────────────────────────

        public async Task<int> DeleteGateEntry(int gateEntryId, LoginDTO loginDTO)
        {
            return await _QueryExecutor.ExecuteAsync(
                loginDTO, GateEntryQB.DELETE_GATEENTRY,
                new { gateentryid = gateEntryId }).ConfigureAwait(false);
        }

        // ── Picklist for dropdowns ────────────────────────────────────────

        public async Task<string> GetSelectListGateEntry(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            int ouId  = GetCriteriaValue(criteriaDTO, "OUId", -1);
            var param = new { firstnumber = firstNumber, maxresult = maxResult, ouid = ouId };
            var result = await _QueryExecutor.QueryAsync<GateEntryPicklistDTO>(
                loginDTO, GateEntryQB.GET_SELECTLIST_GATEENTRY, param).ConfigureAwait(false);

            return JsonConvert.SerializeObject(result);
        }

        // ── Resolve raw scanner inputs into full GateEntryDetailDTOs ────────
        //
        // Flow:
        //   1. Expand  – one object with comma-encoded fields → N flat objects (one per item)
        //   2. Resolve – look up each flat object's IDs from master tables
        //   3. Return  – one GateEntryDetailResolveResultDTO per expanded row

        public async Task<List<GateEntryDetailResolveResultDTO>> ResolveGateEntryDetails(
            List<GateEntryDetailRawInputDTO> inputs, LoginDTO loginDTO)
        {
            // Step 1 — expand multi-item inputs before any DB calls
            var flatInputs = inputs.SelectMany(ExpandInput).ToList();

            var results = new List<GateEntryDetailResolveResultDTO>(flatInputs.Count);

            for (int i = 0; i < flatInputs.Count; i++)
            {
                var input  = flatInputs[i];
                var result = new GateEntryDetailResolveResultDTO
                {
                    InvoiceNumber = input.InvoiceNumber,
                    InvoiceDate   = input.InvoiceDate,
                };
                var detail = result.DetailRow;

                // ── Supplier → Party (header field) ──────────────────────────
                if (!string.IsNullOrWhiteSpace(input.SupplierName))
                {
                    try
                    {
                        var party = await _QueryExecutor.QuerySingleAsync<PartyLookupDTO>(
                            loginDTO, GateEntryQB.GET_PARTY_BY_NAME,
                            new { PartyName = input.SupplierName.Trim() }).ConfigureAwait(false);

                        if (party == null)
                        {
                            // Fallback: partial LIKE match (handles case/abbreviation differences)
                            party = await _QueryExecutor.QuerySingleAsync<PartyLookupDTO>(
                                loginDTO, GateEntryQB.GET_PARTY_BY_NAME_LIKE,
                                new { PartyPattern = "%" + input.SupplierName.Trim() + "%" }).ConfigureAwait(false);
                        }

                        if (party != null)
                        {
                            result.PartyId   = party.PartyId;
                            result.PartyCode = party.PartyCode;
                            result.PartyName = party.PartyName;
                        }
                        else
                        {
                            result.Errors.Add($"Supplier not found: '{input.SupplierName}'");
                        }
                    }
                    catch (Exception ex)
                    {
                        result.Errors.Add($"Supplier lookup failed: {ex.Message}");
                    }
                }

                // ── Material code → Item ──────────────────────────────────────
                // QR scanners may send the item name instead of the item code,
                // so we try code first, then fall back to a name match.
                if (!string.IsNullOrWhiteSpace(input.MaterialCode))
                {
                    try
                    {
                        var item = await _QueryExecutor.QuerySingleAsync<ItemLookupDTO>(
                            loginDTO, GateEntryQB.GET_ITEM_BY_CODE,
                            new { ItemCode = input.MaterialCode.Trim() }).ConfigureAwait(false);

                        if (item == null)
                        {
                            item = await _QueryExecutor.QuerySingleAsync<ItemLookupDTO>(
                                loginDTO, GateEntryQB.GET_ITEM_BY_NAME,
                                new { ItemName = input.MaterialCode.Trim() }).ConfigureAwait(false);
                        }

                        if (item != null)
                        {
                            detail.ItemId   = item.ItemId;
                            detail.ItemCode = item.ItemCode;
                            detail.ItemName = item.ItemName;
                        }
                        else
                        {
                            result.Errors.Add($"Material not found: '{input.MaterialCode}'");
                        }
                    }
                    catch (Exception ex)
                    {
                        result.Errors.Add($"Material lookup failed: {ex.Message}");
                    }
                }


                //detail.LotId     = lot.LotId;
                detail.LotNumber = input.BatchNo;

                // ── Batch number → Lot ───────────────────────────────────────
                // After expansion each input carries exactly one batch number.
                //if (!string.IsNullOrWhiteSpace(input.BatchNo))
                //{
                //    try
                //    {
                //        var lot = await _QueryExecutor.QuerySingleAsync<LotLookupDTO>(
                //            loginDTO, GateEntryQB.GET_LOT_BY_NUMBER,
                //            new { LotNumber = input.BatchNo.Trim() }).ConfigureAwait(false);

                //        if (lot != null)
                //        {
                //            detail.LotId     = lot.LotId;
                //            detail.LotNumber = lot.LotNumber;
                //        }
                //        else
                //        {
                //            result.Errors.Add($"Batch/Lot not found: '{input.BatchNo}'");
                //        }
                //    }
                //    catch (Exception ex)
                //    {
                //        result.Errors.Add($"Batch/Lot lookup failed: {ex.Message}");
                //    }
                //}

                // ── Quantities ───────────────────────────────────────────────
                // BatchQty / InvoiceQty are strings after expansion; parse safely.
                detail.LotQuantity   = ParseDecimal(input.BatchQty);
                detail.DocumentValue = ParseDecimal(input.InvoiceQty);

                // ── PO number → Document ─────────────────────────────────────
                // QR codes often use 2-digit fiscal years (e.g. "VFG/PO-III/3109/26-27")
                // while the DB stores 4-digit years ("VFG/PO-III/3109/2026-27").
                // Try exact match first, then expand 2-digit years and retry with LIKE.
                if (!string.IsNullOrWhiteSpace(input.PONumber))
                {
                    try
                    {
                        var poNumber = input.PONumber.Trim();

                        var doc = await _QueryExecutor.QuerySingleAsync<DocumentLookupDTO>(
                            loginDTO, GateEntryQB.GET_DOCUMENT_BY_NUMBER,
                            new { DocumentNumber = poNumber }).ConfigureAwait(false);

                        if (doc == null)
                        {
                            // Expand 2-digit fiscal year suffix (e.g. /26-27 → /2026-27)
                            var expanded = System.Text.RegularExpressions.Regex.Replace(
                                poNumber,
                                @"/(\d{2})-(\d{2})$",
                                m => $"/20{m.Groups[1].Value}-20{m.Groups[2].Value}");

                            var pattern = "%" + (expanded != poNumber ? expanded : poNumber) + "%";

                            doc = await _QueryExecutor.QuerySingleAsync<DocumentLookupDTO>(
                                loginDTO, GateEntryQB.GET_DOCUMENT_BY_NUMBER_LIKE,
                                new { DocumentPattern = pattern }).ConfigureAwait(false);
                        }

                        if (doc != null)
                        {
                            detail.DocumentId     = doc.DocumentId;
                            detail.DocumentNumber = doc.DocumentNumber;
                        }
                        else
                        {
                            result.Errors.Add($"PO not found: '{input.PONumber}'");
                        }
                    }
                    catch (Exception ex)
                    {
                        result.Errors.Add($"PO lookup failed: {ex.Message}");
                    }
                }

                // ── Allocation name → Allocation ─────────────────────────────
                // "NONE" is a QR sentinel meaning not applicable — skip silently.
                {
                    var allocName = input.AllocationName?.Trim();
                    if (!string.IsNullOrWhiteSpace(allocName) &&
                        !allocName.Equals("NONE", StringComparison.OrdinalIgnoreCase))
                    {
                        try
                        {
                            var allocation = await _QueryExecutor.QuerySingleAsync<AllocationLookupDTO>(
                                loginDTO, GateEntryQB.GET_ALLOCATION_BY_NAME,
                                new { AllocationName = allocName }).ConfigureAwait(false);

                            if (allocation != null)
                            {
                                detail.AllocationId = allocation.AllocationId;
                            }
                            else
                            {
                                result.Errors.Add($"Allocation not found: '{allocName}'");
                            }
                        }
                        catch (Exception ex)
                        {
                            result.Errors.Add($"Allocation lookup failed: {ex.Message}");
                        }
                    }
                }

                // ── UOM name → UOM ───────────────────────────────────────────
                // Priority:
                //   1. Explicit UOMName from the request (if not null/NONE)
                //   2. Unit suffix extracted from BatchQty ("3150 Kgs" → "Kgs")
                //   3. Skip — no UOM, no error (not every QR carries a UOM field)
                {
                    string uomLookupName = null;

                    var uomRaw = input.UOMName?.Trim();
                    if (!string.IsNullOrWhiteSpace(uomRaw) &&
                        !uomRaw.Equals("NONE", StringComparison.OrdinalIgnoreCase))
                    {
                        uomLookupName = uomRaw;
                    }
                    else
                    {
                        // QR label embeds the unit in the qty string (e.g. "3150 Kgs")
                        uomLookupName = ExtractUomFromQty(input.BatchQty);
                    }

                    if (!string.IsNullOrWhiteSpace(uomLookupName))
                    {
                        try
                        {
                            var uom = await _QueryExecutor.QuerySingleAsync<UOMLookupDTO>(
                                loginDTO, GateEntryQB.GET_UOM_BY_NAME,
                                new { UOMName = uomLookupName }).ConfigureAwait(false);

                            if (uom != null)
                            {
                                detail.UOMId   = uom.UOMId;
                                detail.UOMName = uom.UOMName;
                            }
                            else
                            {
                                result.Errors.Add($"UOM not found: '{uomLookupName}'");
                            }
                        }
                        catch (Exception ex)
                        {
                            result.Errors.Add($"UOM lookup failed: {ex.Message}");
                        }
                    }
                }

                // ── Remarks ──────────────────────────────────────────────────
                detail.GateEntryDetailRemarks = input.Remarks;

                results.Add(result);
            }

            return results;
        }

        // ── Multi-item expansion helpers ─────────────────────────────────
        //
        // ExpandInput takes one raw input (which may carry N items encoded as
        // comma-separated values in its per-item fields) and returns N flat inputs —
        // each with exactly one value per field, ready for a single DB resolution pass.
        //
        // Rules:
        //   • Header fields (SupplierName, PONumber, InvoiceNumber, InvoiceDate)
        //     are copied unchanged to every expanded row.
        //   • Per-item fields (MaterialCode, BatchNo, BatchQty, InvoiceQty,
        //     AllocationName, UOMName, Remarks) are split by comma.
        //   • Count = max token count across all per-item fields.
        //   • A field with fewer tokens than Count reuses its last token for the
        //     remaining rows (safe broadcast — e.g. one UOM shared by all items).
        //   • If Count == 1 the original object is returned as-is (no allocation).

        private static List<GateEntryDetailRawInputDTO> ExpandInput(GateEntryDetailRawInputDTO src)
        {
            var materialCodes   = SplitField(src.MaterialCode);
            var batchNos        = SplitField(src.BatchNo);

            // Qty fields need a smarter splitter — QR labels embed units and
            // may omit the comma separator between values (see SplitQtyField).
            var batchQtys       = SplitQtyField(src.BatchQty,   batchNos.Count);
            var invoiceQtys     = SplitQtyField(src.InvoiceQty, batchNos.Count);

            var allocationNames = SplitField(src.AllocationName);
            var uomNames        = SplitField(src.UOMName);
            var remarks         = SplitField(src.Remarks);

            int count = Math.Max(1, new[]
            {
                materialCodes.Count, batchNos.Count, batchQtys.Count,
                invoiceQtys.Count, allocationNames.Count, uomNames.Count, remarks.Count,
            }.Max());

            // Single item — return original to avoid any allocation
            if (count == 1)
                return new List<GateEntryDetailRawInputDTO> { src };

            var expanded = new List<GateEntryDetailRawInputDTO>(count);
            for (int j = 0; j < count; j++)
            {
                expanded.Add(new GateEntryDetailRawInputDTO
                {
                    // Shared header fields
                    SupplierName  = src.SupplierName,
                    PONumber      = src.PONumber,
                    InvoiceNumber = src.InvoiceNumber,
                    InvoiceDate   = src.InvoiceDate,

                    // Per-item fields — index-specific value, fallback to last if count mismatch
                    MaterialCode   = Pick(materialCodes,   j),
                    BatchNo        = Pick(batchNos,        j),
                    BatchQty       = Pick(batchQtys,       j),
                    InvoiceQty     = Pick(invoiceQtys,     j),
                    AllocationName = Pick(allocationNames, j),
                    UOMName        = Pick(uomNames,        j),
                    Remarks        = Pick(remarks,         j),
                });
            }
            return expanded;
        }

        // Split a raw field value by comma; always returns at least one element.
        private static List<string> SplitField(string value)
        {
            if (string.IsNullOrWhiteSpace(value))
                return new List<string> { value };

            return value.Split(',')
                        .Select(s => s.Trim())
                        .ToList();
        }

        // Split a quantity field that the QR scanner may encode in three different ways:
        //
        //   Strategy 1 — comma-separated (most common, handles units too):
        //     "3150 Kgs, 3600 Kgs"  →  ["3150 Kgs", "3600 Kgs"]
        //     "14158.6,14155"        →  ["14158.6",  "14155"]
        //
        //   Strategy 2 — space-separated with unit text (no comma between values):
        //     "3150 Kgs 3600 Kgs"   →  ["3150 Kgs", "3600 Kgs"]
        //     (regex finds every <number><space><unit> token)
        //
        //   Strategy 3 — pure concatenated digits (frontend strips units & separators):
        //     "31503600" with expectedCount=2, equal 4-char groups → ["3150", "3600"]
        //     Only applied when the string is all digits and divides evenly.
        //
        //   Passthrough — single decimal or unrecognised format:
        //     "14158.6"  →  ["14158.6"]   (broadcast to all rows)
        //     "6750 Kgs" →  ["6750 Kgs"]  (total InvoiceQty, broadcast)
        //
        // expectedCount = number of batchNo tokens — used as a hint for Strategy 3.
        private static List<string> SplitQtyField(string value, int expectedCount)
        {
            if (string.IsNullOrWhiteSpace(value))
                return new List<string> { value };

            // Strategy 1: comma-split
            var byComma = value.Split(',')
                               .Select(s => s.Trim())
                               .Where(s => s.Length > 0)
                               .ToList();
            if (byComma.Count > 1)
                return byComma;

            // Strategy 2: find <number><whitespace><unit> patterns without a comma
            // e.g. "3150 Kgs 3600 Kgs" → 2 matches even though there is no comma
            var withUnit = System.Text.RegularExpressions.Regex.Matches(
                               value, @"\d+(?:\.\d+)?\s+[A-Za-z]+");
            if (withUnit.Count > 1)
                return withUnit.Cast<System.Text.RegularExpressions.Match>()
                               .Select(m => m.Value.Trim())
                               .ToList();

            // Strategy 3: pure-digit string that is N quantities concatenated
            // e.g. "31503600" with expectedCount=2 → equal 4-digit groups → ["3150","3600"]
            if (expectedCount > 1)
            {
                var digits = value.Trim();
                if (System.Text.RegularExpressions.Regex.IsMatch(digits, @"^\d+$")
                    && digits.Length % expectedCount == 0)
                {
                    int partLen = digits.Length / expectedCount;
                    return Enumerable.Range(0, expectedCount)
                                     .Select(k => digits.Substring(k * partLen, partLen))
                                     .ToList();
                }
            }

            // Single value — broadcast to all rows
            return byComma.Count > 0 ? byComma : new List<string> { value };
        }

        // Pick the value at index; if index exceeds the list, reuse the last value.
        private static string Pick(List<string> parts, int index)
            => parts.Count == 1    ? parts[0]
             : index < parts.Count ? parts[index]
             : parts[parts.Count - 1];

        // Parses a quantity string that may carry a unit suffix from the QR label.
        // "3150 Kgs"  → 3150      "14158.6" → 14158.6      null/blank → 0
        private static decimal ParseDecimal(string value)
        {
            if (string.IsNullOrWhiteSpace(value)) return 0m;

            // Extract the leading numeric part, ignoring any trailing unit text
            var m = System.Text.RegularExpressions.Regex.Match(
                        value.Trim(), @"^-?\d+(\.\d+)?");

            return m.Success && decimal.TryParse(
                       m.Value,
                       System.Globalization.NumberStyles.Any,
                       System.Globalization.CultureInfo.InvariantCulture,
                       out var d)
                   ? d : 0m;
        }

        // Extracts the unit text that follows the number in a qty string.
        // "3150 Kgs" → "Kgs"     "14158.6" → null     null/blank → null
        private static string ExtractUomFromQty(string qtyValue)
        {
            if (string.IsNullOrWhiteSpace(qtyValue)) return null;
            var m = System.Text.RegularExpressions.Regex.Match(
                        qtyValue.Trim(), @"^-?\d+(\.\d+)?\s+([A-Za-z]+)");
            return m.Success ? m.Groups[2].Value : null;
        }

        private int GetCriteriaValue(CriteriaDTO criteriaDTO, string fieldName, int defaultValue)
        {
            try
            {
                var attr = criteriaDTO?.SectionCriteriaList?
                    .SelectMany(s => s.AttributesCriteriaList)
                    .FirstOrDefault(a => string.Equals(a.FieldName, fieldName, StringComparison.OrdinalIgnoreCase));

                if (attr?.FieldValue is JsonElement je)
                    return je.ValueKind == JsonValueKind.Number ? je.GetInt32()
                         : int.TryParse(je.GetString(), out int p) ? p : defaultValue;

                return attr != null ? Convert.ToInt32(attr.FieldValue) : defaultValue;
            }
            catch { return defaultValue; }
        }
    }
}