using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AdminDAL.DTO.Calendar;
using AdminDAL.Query.Calendar;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using Microsoft.VisualBasic;
using Newtonsoft.Json;

namespace AdminDAL.CustomCode.Calendar
{
    public class CalendarDAL : ICalendarDAL
    {
        private readonly IQueryExecutor _queryexecutor;

        public CalendarDAL(IQueryExecutor IQueryExecutor)
        {
            _queryexecutor = IQueryExecutor;
        }

        public async Task<string> GetCalendar(int CalendarId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = CalendarQB.GET_CALENDAR;
                var parameters = new { calendarid = CalendarId };

                var CalendarDict = new Dictionary<int, CalendarDTO>();

                var result = await _queryexecutor.QueryMultiMapAsync<CalendarDTO, CalendarDetailDTO, CalendarDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!CalendarDict.TryGetValue(parent.CalendarId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.CalendarDetailArray = new List<CalendarDetailDTO>();
                            CalendarDict[parent.CalendarId] = existingParent;
                        }

                        if (child != null && child.CalendarDetailId != 0)
                        {
                            existingParent.CalendarDetailArray.Add(child);
                        }

                        return existingParent;
                    },
                    parameters,
                    splitOn: "CalendarDetailId"
                );

                var finalResult = CalendarDict.Values.FirstOrDefault();
                return JsonConvert.SerializeObject(finalResult);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve Calendar data: {ex.Message}", ex);
            }
        }

        public async Task<int> SaveCalendar(CalendarDTO CalendarDTO, LoginDTO loginDTO)
        {
            try
            {

                string CalendarSql = CalendarQB.SAVE_CALENDAR;
                string childSql = CalendarQB.SAVE_CALENDARDETAIL;

                var statements = new List<(string sql, object param)>
                {
                    (CalendarSql, CalendarDTO)
                };

                if (CalendarDTO.CalendarDetailArray != null && CalendarDTO.CalendarDetailArray.Count > 0)
                {
                    foreach (var child in CalendarDTO.CalendarDetailArray)
                    {
                        child.CalendarId = CalendarDTO.CalendarId;
                        statements.Add((childSql, child));
                    }
                }

                await _queryexecutor.ExecuteInTransactionAsync(loginDTO, statements);

                return CalendarDTO.CalendarId;
            }
            catch (Exception ex)
            {
                throw new Exception($"{ErrorResponse.SaveErrorMessage}: {ex.Message}", ex);
            }
        }

        public async Task<int> UpdateCalendar(CalendarDTO CalendarDTO, LoginDTO loginDTO)
        {
            try
            {
                var statements = new List<(string sql, object param)>();

                statements.Add((
                    CalendarQB.UPDATE_CALENDAR,
                    CalendarDTO
                ));

                var parameters = new { calendarid = CalendarDTO.CalendarId };
                var maxId = await _queryexecutor.ExecuteScalarAsync<int>(
                    loginDTO,
                    CalendarQB.GET_MAX_CALENDARDETAILID,
                    parameters
                );

                maxId = maxId - 1;

                if (CalendarDTO.CalendarDetailArray != null)
                {
                    foreach (var child in CalendarDTO.CalendarDetailArray)
                    {
                        if (child.CalendarDetailId == 0)
                        {
                            child.CalendarDetailId = maxId--;
                            statements.Add((
                                CalendarQB.SAVE_CALENDARDETAIL,
                                child
                            ));
                        }
                        else
                        {
                            statements.Add((
                                CalendarQB.UPDATE_CALENDARDETAIL,
                                child
                            ));
                        }
                    }
                }

                await _queryexecutor.ExecuteInTransactionAsync(loginDTO, statements);

                return CalendarDTO.CalendarId;
            }
            catch (Exception ex)
            {
                throw new Exception($"{ErrorResponse.UpdateErrorMessage}: {ex.Message}", ex);
            }
        }

        public async Task<Result<string>> DeleteCalendar(int CalendarId, LoginDTO LoginDTO)
        {
            try
            {
                string childsql = CalendarQB.DELETE_CALENDARDETAIL;
                string parentSql = CalendarQB.DELETE_CALENDAR;
                var parameter = new { calendarid = CalendarId };
                var childresult = await _queryexecutor.ExecuteAsync(LoginDTO, childsql, parameter);
                var parentresult = await _queryexecutor.ExecuteAsync(LoginDTO, parentSql, parameter);

                if (parentresult > 0)
                {
                    return Result<string>.Success("Calendar Details Deleted Successfully.");
                }
                else
                {
                    return Result<string>.Failure("Calendar not found or could not be deleted.");
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error while deleting Calendar Details", ex);
            }
        }

        public async Task<string> GetSelectListCalendar(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = CalendarQB.GET_SELECTLIST_CALENDAR;
                var Result = await _queryexecutor.QueryAsync<CalendarPicklistDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}