using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using MMDAL.DTO.Lot;
using MMDAL.DTO.LotDetail;
using MMDAL.Query.Allocation;
using MMDAL.Query.Lot;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;

namespace MMDAL.CustomCode.Lot
{
    public class LotDAL : ILotDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public LotDAL(IQueryExecutor QueryExecutor)
        {
            _QueryExecutor = QueryExecutor;
        }

        // ── SELECT LIST ───────────────────────────────────────────────
        public async Task<string> GetSelectListLot(
            int FirstNumber,
            int MaxResult,
            CriteriaDTO CriteriaDTO,
            LoginDTO LoginDTO,
            bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    string SQL = LotQB.GET_SELECTLIST_LOT;
                    var parameter = new { firstnumber = FirstNumber, maxresult = MaxResult };
                    var Result = await _QueryExecutor.QueryAsync<LotPicklistDTO>(LoginDTO, SQL, parameter);
                    Json = JsonConvert.SerializeObject(Result);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// Inserts a new row into TLOT.
        ///
        /// FK CONSTRAINTS SATISFIED:
        ///   ITEMID       → MITEM        (from InspectionDetailDTO)
        ///   SKUID        → MSKU         (from InspectionDetailDTO)
        ///   LOTTYPEID    → MLOTTYPE     (must be valid — passed from BLL)
        ///   PROCESSID    → MPROCESS     (must be valid — passed from BLL)
        ///   ALLOCATIONID → MALLOCATION  (default -1, DB default exists)
        ///   CREATEDBYID  → MUSER        (loginDTO.UserId)
        ///   MODIFIEDBYID → MUSER        (loginDTO.UserId)
        ///
        /// UNIQUE KEY: ITEMID + SKUID + LOTNUMBER + LOTTYPEID
        ///   LotNumber = "LOT-0000001" from AutoNumber — guaranteed unique.
        /// </summary>
        public async Task CreateLotAsync(
    LotDTO lotDTO,
    LoginDTO loginDTO,
    CancellationToken ct)
        {
            try
            {
                var parameter = new
                {
                    lotid = lotDTO.LotId,
                    lotnumber = lotDTO.LotLotNumber,
                    itemid = lotDTO.ItemId,
                    skuid = lotDTO.SKUId,
                    lotdate = lotDTO.LotLotDate,
                    lotexpirydate = lotDTO.LotLotExpiryDate,
                    remarks = lotDTO.LotRemarks ?? "",
                    status = lotDTO.LotStatus,
                    version = lotDTO.LotVersion,
                    createdbyid = lotDTO.LotCreatedById,
                    createdon = lotDTO.LotCreatedOn,
                    modifiedbyid = lotDTO.LotModifiedById,
                    modifiedon = lotDTO.LotModifiedOn,
                    processid = lotDTO.ProcessId,
                    lottypeid = lotDTO.LotTypeId,
                    mrprate = lotDTO.LotMRPRate,
                    itempostedcost = lotDTO.LotItemPostedCost
                };

                await _QueryExecutor.ExecuteAsync(loginDTO, LotQB.INSERT_LOT, parameter);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListLotReservation(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
=> await _QueryExecutor.QueryWithCriteriaJsonAsync<dynamic>(
    loginDTO,
    LotQB.GET_SELECTLIST_LOT_RESERVATION,
    new { TenantId = loginDTO.ClientId },
    criteriaDTO);

        // ── LOT DETAIL SELECT LIST (quantity/available-stock picklist) ─
        public async Task<string> GetLotSelectListForQuantity(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var attrs = CriteriaDTO?.SectionCriteriaList?
                    .SelectMany(s => s.AttributesCriteriaList ?? [])
                    .ToList() ?? [];

                int type = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "Type")?.FieldValue, 0);
                int ouId = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "OUId")?.FieldValue, LoginDTO.WorkOUId);
                string? lotNumber = attrs.FirstOrDefault(a => a.FieldName == "LotNumber")?.FieldValue?.ToString();

                var param = new
                {
                    ouid = ouId,
                    itemid = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "ItemId")?.FieldValue, -1),
                    skuid = ParseInt(attrs.FirstOrDefault(a => a.FieldName is "SKUId" or "SkuId")?.FieldValue, -1),
                    processid = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "ProcessId")?.FieldValue, -1),
                    lottypeid = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "LotTypeId")?.FieldValue, -1),
                    lotnumber = string.IsNullOrWhiteSpace(lotNumber) ? null : lotNumber,
                    objectheaderid = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "ObjectHeaderId")?.FieldValue, -1),
                    objectid = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "ObjectId")?.FieldValue, -1),
                    storeid = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "StoreId")?.FieldValue, -1),
                    isstock = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "IsStock")?.FieldValue, 0)
                };

                // GB4 parity: Type selects among the base / LoadingSheet / DeliveryFeedback
                // variants of the same select list — see LotQB comments for each.
                string SQL = type switch
                {
                    1 => LotQB.GET_LOTSELECTLIST_NEW_FORLOADINGSHEET,
                    2 => LotQB.GET_LOTSELECTLIST_NEW_FORDELIVERYFEEDBACK,
                    _ => LotQB.GET_LOTSELECTLIST_NEW
                };

                var Result = await _QueryExecutor.QueryAsync<LotDetailPendingPicklistDTO>(LoginDTO, SQL, param);

                return JsonConvert.SerializeObject(Result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static int ParseInt(object? value, int defaultValue) => value switch
        {
            null => defaultValue,
            JsonElement je => je.ValueKind == JsonValueKind.Number ? je.GetInt32() : int.Parse(je.GetString()!),
            IConvertible ic => Convert.ToInt32(ic),
            _ => int.Parse(value.ToString()!)
        };
    }
}