using System.Data.Common;
using System.Text.Json;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using MMDAL.DTO.Packing;
using MMDAL.Query.Packing;
using Newtonsoft.Json;
using static GB5Shared.GB5Constant.Constant;

namespace MMDAL.CustomCode.Packing
{
    public class PackingDAL : IPackingDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        public async Task<string> GetSelectListPacking(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = string.Empty;

                switch (LoginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        SQL = PackingQB.GET_SELECTLIST_PACKING_SQL;
                        break;

                    case DBType.PostGre:
                        SQL = PackingQB.GET_SELECTLIST_PACKING_PG;
                        break;

                    case DBType.Oracle:
                        SQL = PackingQB.GET_SELECTLIST_PACKING_ORACLE;
                        break;

                    case DBType.MySQL:
                        SQL = PackingQB.GET_SELECTLIST_PACKING_MYSQL;
                        break;

                    default:
                        throw new Exception("Unsupported database type");
                }

                var Parameters = new
                {
                    firstnumber = FirstNumber,
                    maxresult = MaxResult
                };

                var Result = await _QueryExecutor.QueryAsync<PackingPicklistDTO>(LoginDTO, SQL, Parameters);

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

        public async Task<string> GetPackingProductionSummary(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = LoginDTO.DatabaseType switch
                {
                    DBType.SQL => PackingQB.GET_PACKING_PRODUCTION_SUMMARY_SQL,
                    DBType.PostGre => PackingQB.GET_PACKING_PRODUCTION_SUMMARY_PG,
                    DBType.Oracle => PackingQB.GET_PACKING_PRODUCTION_SUMMARY_ORACLE,
                    DBType.MySQL => PackingQB.GET_PACKING_PRODUCTION_SUMMARY_MYSQL,
                    _ => throw new Exception("Unsupported database type")
                };

                var attrs = CriteriaDTO?.SectionCriteriaList?
                    .SelectMany(s => s.AttributesCriteriaList ?? [])
                    .ToList() ?? [];

                var Parameters = new
                {
                    itemid = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "ItemId")?.FieldValue, -1),
                    skuid = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "SKUId")?.FieldValue, -1),
                    ouid = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "OUId")?.FieldValue, -1),
                    periodfromdate = ParseDate(attrs.FirstOrDefault(a => a.FieldName == "PeriodFromDate")?.FieldValue),
                    periodtodate = ParseDate(attrs.FirstOrDefault(a => a.FieldName == "PeriodToDate")?.FieldValue),
                    biztransactionclassid = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "BIZTransactionClassId")?.FieldValue, -1),
                    biztransactionsubclassid = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "BIZTransactionSubClassId")?.FieldValue, -1),
                    biztransactionid = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "BIZTransactionId")?.FieldValue, -1),
                    biztransactiontypeid = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "BIZTransactionTypeId")?.FieldValue, -1),
                    packtype = ParseInt(attrs.FirstOrDefault(a => a.FieldName == "PackType")?.FieldValue, -1),
                    firstnumber = FirstNumber,
                    maxresult = MaxResult
                };

                var Result = await _QueryExecutor.QueryAsync<MMDAL.DTO.Pack.PackingProductionSummaryDTO>(LoginDTO, SQL, Parameters);

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

        // ── Save header ───────────────────────────────────────────────────────

        public async Task InsertPackingHeader(PackingSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => PackingSaveQB.SAVE_PACKING_SQL,
                DBType.PostGre => PackingSaveQB.SAVE_PACKING_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _QueryExecutor.ExecuteAsync(login, sql,
                new
                {
                    dto.PackingId, dto.OUId, dto.PeriodId, dto.BizTransactionTypeId,
                    dto.PackingNumber, dto.PackingDate, dto.ReferenceNumber, dto.ReferenceDate,
                    dto.ShiftId, dto.StoreId, dto.ToStoreId, dto.PackType, dto.PackId,
                    dto.MaterialType, dto.ItemId, dto.SkuId, dto.PackedById, dto.LotId,
                    dto.UnitQuantity, dto.UnitUomId, dto.UnitsPerPack, dto.NumberOfPacks,
                    dto.GrossWeightPerPack, dto.NetWeightPerPack, dto.TotalGrossWeight,
                    dto.TotalNetWeight, dto.TareWeight, dto.PackNumberId, dto.PackNumber,
                    dto.PackDimension, dto.Remarks, dto.Status, dto.Version,
                    dto.CreatedById, dto.CreatedOn, dto.ModifiedById, dto.ModifiedOn
                }, tx, ct).ConfigureAwait(false);
        }

        public async Task UpdatePackingHeader(PackingSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => PackingSaveQB.UPDATE_PACKING_SQL,
                DBType.PostGre => PackingSaveQB.UPDATE_PACKING_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _QueryExecutor.ExecuteAsync(login, sql,
                new
                {
                    dto.PackingId, dto.OUId, dto.PeriodId, dto.BizTransactionTypeId,
                    dto.ReferenceNumber, dto.ReferenceDate, dto.ShiftId, dto.StoreId, dto.ToStoreId,
                    dto.PackType, dto.PackId, dto.MaterialType, dto.ItemId, dto.SkuId,
                    dto.PackedById, dto.LotId, dto.UnitQuantity, dto.UnitUomId, dto.UnitsPerPack,
                    dto.NumberOfPacks, dto.GrossWeightPerPack, dto.NetWeightPerPack,
                    dto.TotalGrossWeight, dto.TotalNetWeight, dto.TareWeight,
                    dto.PackNumberId, dto.PackNumber, dto.PackDimension, dto.Remarks, dto.Status,
                    dto.ModifiedById, dto.ModifiedOn
                }, tx, ct).ConfigureAwait(false);
        }

        // ── Save pack contents ───────────────────────────────────────────────

        public async Task SavePackDetail(PackDetailSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => PackingSaveQB.SAVE_PACKDETAIL_SQL,
                DBType.PostGre => PackingSaveQB.SAVE_PACKDETAIL_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _QueryExecutor.ExecuteAsync(login, sql,
                new
                {
                    dto.PackDetailId, dto.PackNumberId, dto.BizTransactionTypeId,
                    dto.ObjectHeaderTypeId, dto.ObjectHeaderId, dto.ObjectTypeId, dto.ObjectId, dto.SlNo,
                    dto.ItemId, dto.SkuId, dto.Quantity, dto.Shortage, dto.Excess, dto.NetQuantity,
                    dto.GoodQuantity, dto.RejectedQuantity, dto.ReworkQuantity, dto.OtherQuantity,
                    dto.GrossWeight, dto.NetWeight, dto.TareWeight, dto.PlannedQuantity, dto.Remarks,
                    dto.OUId, dto.StoreId, dto.StockLedgerNumber, dto.StockLedgerDate,
                    dto.ReferenceNumber, dto.ReferenceDate, dto.LocationType, dto.PartyBranchId,
                    dto.StockPostType, dto.MaterialOwnershipType, dto.ExciseSlNo, dto.DocumentDetailId
                }, tx, ct).ConfigureAwait(false);
        }

        public async Task DeletePackDetailsByHeader(int objectHeaderTypeId, int objectHeaderId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => PackingSaveQB.DELETE_PACKDETAILS_BY_HEADER_SQL,
                DBType.PostGre => PackingSaveQB.DELETE_PACKDETAILS_BY_HEADER_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _QueryExecutor.ExecuteAsync(login, sql,
                new { ObjectHeaderTypeId = objectHeaderTypeId, ObjectHeaderId = objectHeaderId },
                tx, ct).ConfigureAwait(false);
        }

        public async Task DeletePackingHeader(int packingId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => PackingSaveQB.DELETE_PACKING_SQL,
                DBType.PostGre => PackingSaveQB.DELETE_PACKING_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _QueryExecutor.ExecuteAsync(login, sql, new { PackingId = packingId }, tx, ct)
                .ConfigureAwait(false);
        }

        // ── Get ───────────────────────────────────────────────────────────────

        public async Task<PackingSaveDTO?> GetPacking(int packingId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => PackingSaveQB.GET_PACKING_SQL,
                DBType.PostGre => PackingSaveQB.GET_PACKING_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            return await _QueryExecutor
                .QuerySingleAsync<PackingSaveDTO>(login, sql, new { PackingId = packingId }, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        public async Task<IEnumerable<PackDetailSaveDTO>> GetPackDetails(int objectHeaderTypeId, int objectHeaderId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => PackingSaveQB.GET_PACKDETAILS_SQL,
                DBType.PostGre => PackingSaveQB.GET_PACKDETAILS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            return await _QueryExecutor
                .QueryAsync<PackDetailSaveDTO>(login, sql,
                    new { ObjectHeaderTypeId = objectHeaderTypeId, ObjectHeaderId = objectHeaderId },
                    cancellationToken: ct)
                .ConfigureAwait(false) ?? [];
        }

        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()!)
        };

        private static DateTime? ParseDate(object? value)
        {
            if (value is null) return null;
            string? text = value is JsonElement je ? je.GetString() : value.ToString();
            return DateTime.TryParse(text, out var result) ? result : null;
        }
    }
}