using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using LogisticsDAL.DTO.Booking;
using LogisticsDAL.Query.Booking;
using Newtonsoft.Json;
using static GB5Shared.GB5Constant.Constant;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LogisticsDAL.CustomCode.Booking
{
    public class BookingDAL : IBookingDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public BookingDAL(IQueryExecutor IQueryExecutor)
        {
            _QueryExecutor = IQueryExecutor;
        }

        public async Task<string> GetBooking(int BookingId, LoginDTO LoginDTO)
        {
            var Sql = BookingQB.GET_BOOKING;
            var Parameters = new { bookingid = BookingId };
            var BookingDict = new Dictionary<int, BookingDTO>();

            var result = await _QueryExecutor.QueryMultiMapAsync<BookingDTO, BookingDetailDTO, BookingChargesDTO, BookingDTO>(
                LoginDTO,
                Sql,
                (Booking, Detail, Charges) =>
                {
                    if (!BookingDict.TryGetValue(Booking.BookingId, out var CurrentBooking))
                    {
                        CurrentBooking = Booking;
                        CurrentBooking.BookingDetailArray = new List<BookingDetailDTO>();
                        CurrentBooking.BookingChargesArray = new List<BookingChargesDTO>();
                        BookingDict.Add(CurrentBooking.BookingId, CurrentBooking);
                    }

                    if (Detail != null &&
                        !CurrentBooking.BookingDetailArray.Any(d => d.BookingDetailId == Detail.BookingDetailId))
                        CurrentBooking.BookingDetailArray.Add(Detail);

                    if (Charges != null &&
                        !CurrentBooking.BookingChargesArray.Any(c => c.BookingChargesId == Charges.BookingChargesId))
                        CurrentBooking.BookingChargesArray.Add(Charges);

                    return CurrentBooking;
                },
                Parameters,
                splitOn: "BookingDetailId,BookingChargesId"
            );

            var booking = BookingDict.Values.FirstOrDefault();

            // serialize to JSON
            return JsonConvert.SerializeObject(booking, Formatting.None);
        }

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

                switch (LoginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        SQL = BookingPicklistQB.GET_BOOKING_SELECTLIST_SQL;
                        break;

                    case DBType.PostGre:
                        SQL = BookingPicklistQB.GET_BOOKING_SELECTLIST_PG;
                        break;

                    case DBType.Oracle:
                        SQL = BookingPicklistQB.GET_BOOKING_SELECTLIST_ORACLE;
                        break;

                    case DBType.MySQL:
                        SQL = BookingPicklistQB.GET_BOOKING_SELECTLIST_MYSQL;
                        break;

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

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

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

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

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

                switch (LoginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        SQL = BookingPicklistQB.GET_DELIVERY_RUNNUMBER_PICKLIST_SQL;
                        break;

                    case DBType.PostGre:
                        SQL = BookingPicklistQB.GET_DELIVERY_RUNNUMBER_PICKLIST_PG;
                        break;

                    case DBType.Oracle:
                        SQL = BookingPicklistQB.GET_DELIVERY_RUNNUMBER_PICKLIST_ORACLE;
                        break;

                    case DBType.MySQL:
                        SQL = BookingPicklistQB.GET_DELIVERY_RUNNUMBER_PICKLIST_MYSQL;
                        break;

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

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

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

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

        // GB4 parity: Booking.svc/DRSSelectList — ParcelBookingBLL.DRSPicklist
        public async Task<string> GetDRSSelectList(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string dsrNumber = null!, number = null!, bizTransactionTypeId = null!, id = null!, bookingStatus = null!;

                foreach (var section in CriteriaDTO?.SectionCriteriaList ?? Enumerable.Empty<SectionCriteriaDTO>())
                {
                    foreach (var attr in section.AttributesCriteriaList ?? new List<AttributesCriteriaDTO>())
                    {
                        switch (attr.FieldName?.Trim().ToLower())
                        {
                            case "dsrnumber": dsrNumber = GetCriteriaString(attr.FieldValue); break;
                            case "number": number = GetCriteriaString(attr.FieldValue); break;
                            case "deliverybiztransactiontype": bizTransactionTypeId = GetCriteriaString(attr.FieldValue); break;
                            case "id": id = GetCriteriaString(attr.FieldValue); break;
                            case "bookingstatus": bookingStatus = GetCriteriaString(attr.FieldValue); break;
                        }
                    }
                }

                string SQL = LoginDTO.DatabaseType switch
                {
                    DBType.SQL => BookingPicklistQB.GET_DRS_SELECTLIST_SQL,
                    DBType.PostGre => BookingPicklistQB.GET_DRS_SELECTLIST_PG,
                    DBType.Oracle => BookingPicklistQB.GET_DRS_SELECTLIST_ORACLE,
                    DBType.MySQL => BookingPicklistQB.GET_DRS_SELECTLIST_MYSQL,
                    _ => throw new Exception("Unsupported database type")
                };

                var Parameters = new
                {
                    dsrnumber = string.IsNullOrWhiteSpace(dsrNumber) ? null : $"%{dsrNumber}%",
                    number = string.IsNullOrWhiteSpace(number) ? null : $"%{number}%",
                    biztransactiontypeid = string.IsNullOrWhiteSpace(bizTransactionTypeId) ? null : $"%{bizTransactionTypeId}%",
                    id = string.IsNullOrWhiteSpace(id) ? null : $"%{id}%",
                    bookingstatus = string.IsNullOrWhiteSpace(bookingStatus) ? null : $"%{bookingStatus}%",
                    firstnumber = FirstNumber,
                    maxresult = MaxResult
                };

                var Result = await _QueryExecutor.QueryAsync<DRSPicklistDTO>(LoginDTO, SQL, Parameters);
                return JsonConvert.SerializeObject(Result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static string GetCriteriaString(object value)
        {
            if (value == null) return string.Empty;
            if (value is System.Text.Json.JsonElement je) return je.ToString();
            return Convert.ToString(value) ?? string.Empty;
        }
    }
}