using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using MMDAL.DTO.InspectionType;
using MMDAL.Query.InspectionType;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace MMDAL.CustomCode.InspectionType
{
    public class InspectionTypeDAL : IInspectionTypeDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        public async Task<string> GetInspectionType(int inspectionTypeId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var sql = InspectionTypeQB.GET_INSPECTION_TYPE;
                var param = new { InspectionTypeId = inspectionTypeId };

                var dict = new Dictionary<int, InspectionTypeDTO>();

                await _QueryExecutor.QueryMultiMapAsync<InspectionTypeDTO, InspectionTypeParameterSetDTO, InspectionTypeDTO>(
                    loginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!dict.TryGetValue(parent.InspectionTypeId, out var existing))
                        {
                            existing = parent;
                            existing.InspectionTypeParameterSetArray = new List<InspectionTypeParameterSetDTO>();
                            dict[parent.InspectionTypeId] = existing;
                        }
                        if (child != null && child.InspectionTypeParameterSetId != 0)
                            existing.InspectionTypeParameterSetArray.Add(child);
                        return existing;
                    },
                    param,
                    splitOn: "InspectionTypeParameterSetId"
                );

                return JsonConvert.SerializeObject(dict.Values.FirstOrDefault());
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve InspectionType: {ex.Message}", ex);
            }
        }

        public async Task<string> GetPicklistInspectionType(
    int FirstNumber,
    int MaxResult,
    CriteriaDTO CriteriaDTO,
    LoginDTO loginDTO,
    CancellationToken ct)
        {
            try
            {
                var parameters = new
                {
                    firstnumber = FirstNumber,
                    maxresult = MaxResult,
                    searchtext = CriteriaDTO?.SearchKeyword
                };

                var result = await _QueryExecutor.QueryAsync<InspectionTypePicklistDTO>(
                    loginDTO,
                    InspectionTypeQB.GET_PICKLIST_INSPECTION_TYPE,
                    parameters,
                    cancellationToken: ct);

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve InspectionType picklist: {ex.Message}", ex);
            }
        }

        public async Task<string> GetParameterSetsByInspectionType(int inspectionTypeId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<InspectionTypeParameterSetDTO>(
                    loginDTO,
                    InspectionTypeQB.GET_PARAMETERSETS_BY_INSPECTION_TYPE,
                    new { InspectionTypeId = inspectionTypeId },
                    cancellationToken: ct);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve ParameterSets for InspectionType: {ex.Message}", ex);
            }
        }

        public async Task<int> SaveInspectionType(InspectionTypeDTO dto, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                bool isNew = dto.InspectionTypeId == 0;
                var statements = new List<(string sql, object param)>();

                if (isNew)
                    statements.Add((InspectionTypeQB.SAVE_INSPECTION_TYPE, dto));
                else
                {
                    statements.Add((InspectionTypeQB.UPDATE_INSPECTION_TYPE, dto));
                    // Default-tier-only delete: never touches customer/item override rows
                    // for this InspectionType (those are owned by InspectionParamSetOverride).
                    statements.Add((InspectionTypeQB.DELETE_INSPECTION_TYPE_PARAMETERSET_DEFAULT_BY_TYPE,
                        new { dto.InspectionTypeId, dto.ModifiedById, dto.ModifiedOn }));
                }

                if (dto.InspectionTypeParameterSetArray?.Count > 0)
                {
                    foreach (var ps in dto.InspectionTypeParameterSetArray)
                    {
                        ps.InspectionTypeId = dto.InspectionTypeId;
                        ps.CreatedById  = dto.ModifiedById;
                        ps.CreatedOn    = dto.ModifiedOn;
                        ps.ModifiedById = dto.ModifiedById;
                        ps.ModifiedOn   = dto.ModifiedOn;
                        statements.Add((InspectionTypeQB.SAVE_INSPECTION_TYPE_PARAMETERSET, ps));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(loginDTO, statements);
                return dto.InspectionTypeId;
            }
            catch (Exception ex)
            {
                throw new Exception($"{ErrorResponse.SaveErrorMessage}: {ex.Message}", ex);
            }
        }

        public async Task<int> DeleteInspectionType(int inspectionTypeId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var param = new
                {
                    InspectionTypeId = inspectionTypeId,
                    ModifiedById = loginDTO.UserId,
                    ModifiedOn   = DateTime.UtcNow
                };

                var statements = new List<(string sql, object param)>
                {
                    (InspectionTypeQB.DELETE_INSPECTION_TYPE_PARAMETERSET_BY_TYPE, param),
                    (InspectionTypeQB.DELETE_INSPECTION_TYPE, param)
                };

                await _QueryExecutor.ExecuteInTransactionAsync(loginDTO, statements);
                return inspectionTypeId;
            }
            catch (Exception ex)
            {
                throw new Exception($"Error deleting InspectionType ID {inspectionTypeId}: {ex.Message}", ex);
            }
        }
    }
}