using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using MMDAL.DTO.NDTReport;
using MMDAL.Query.NDTReport;
using Newtonsoft.Json;
using System.Data;

namespace MMDAL.CustomCode.NDTReport
{
    public class NDTReportDAL : INDTReportDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public NDTReportDAL(IQueryExecutor QueryExecutor, IValidation Validation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = Validation;
        }

        public async Task<NDTReportDTO> GetNDTReport(int NDTReportId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = NDTReportQB.GET_NDTREPORT;
                var parameters = new { ndtreportid = NDTReportId, tenantid = LoginDTO.ClientId };

                NDTReportDTO currentReport = null;
                var condParamSeen = new HashSet<int>();
                var itemResultSeen = new HashSet<int>();

                var result = await _QueryExecutor.QueryMultiMapAsync<NDTReportDTO, NDTConditionParamDTO, NDTItemResultDTO, NDTReportDTO>(
                    LoginDTO, sql,
                    (report, condParam, itemResult) =>
                    {
                        if (currentReport == null)
                            currentReport = report;

                        if (condParam != null && condParam.NDTCondParamId > 0 && condParamSeen.Add(condParam.NDTCondParamId))
                            currentReport.NDTConditionParamArray.Add(condParam);

                        if (itemResult != null && itemResult.NDTItemResultId > 0 && itemResultSeen.Add(itemResult.NDTItemResultId))
                            currentReport.NDTItemResultArray.Add(itemResult);

                        return currentReport;
                    },
                    parameters,
                    splitOn: "NDTCondParamId,NDTItemResultId");

                return currentReport;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListNDTReport(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                if (IsCount == false)
                {
                    string sql = NDTReportQB.GET_SELECTLIST_NDTREPORT;
                    var parameters = new { firstnumber = FirstNumber, maxresult = MaxResult, tenantid = LoginDTO.ClientId };
                    var result = await _QueryExecutor.QueryAsync<NDTReportPickListDTO>(LoginDTO, sql, parameters);
                    return JsonConvert.SerializeObject(result);
                }
                else
                {
                    return 0.ToString();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveNDTReport(NDTReportDTO dto, LoginDTO login)
        {
            try
            {
                // -----------------------------------
                // STEP 1: INSERT PARENT WITH ALL FIELDS
                // -----------------------------------
                var reportParam = new
                {
                    dto.NDTReportOuId,
                    dto.NDTReportPeriodId,
                    dto.NDTReportBizTransactionId,
                    dto.NDTReportNdtMethodId,
                    dto.NDTReportParameterSet,
                    dto.NDTReportReportNo,
                    dto.NDTReportReportDate,
                    dto.NDTReportReferenceNo,
                    dto.NDTReportReferenceDate,
                    dto.NDTReportTestDate,
                    dto.NDTReportCustomerId,
                    dto.NDTReportHeatId,
                    dto.NDTReportNdtObjectTypeId,
                    dto.NDTReportNdtObjectId,
                    dto.NDTReportStageOfTestId,
                    dto.NDTReportSurfaceConditionId,
                    dto.NDTReportAcceptanceStandardId,
                    dto.NDTReportProcedureReference,
                    dto.NDTReportTestStartDateTime,
                    dto.NDTReportTestEndDateTime,
                    dto.NDTReportTotalTestHours,
                    dto.NDTReportIsThirdParty,
                    dto.NDTReportNdtAgencyId,
                    dto.NDTReportTestedById,
                    dto.NDTReportWitnessedById,
                    dto.NDTReportApprovedById,
                    dto.NDTReportCertificationLevel,
                    dto.NDTReportOverallResult,
                    dto.NDTReportRemarks,
                    // NEW NDT FIELDS - THESE WERE MISSING!
                    dto.NDTReportTypeOfTest,
                    dto.NDTReportMagneticMethod,
                    dto.NDTReportTypeOfMagnetisation,
                    dto.NDTReportDemagnetization,
                    dto.NDTReportMagnetizationMedium,
                    dto.NDTReportLptMethod,
                    dto.NDTReportApplicationOfPenetrant,
                    dto.NDTReportRadiationSource,
                    dto.NDTReportUtMethod,
                    dto.NDTReportProbeType,
                    // MT Fields
                    dto.NDTReportMagnetizingCurrent,
                    dto.NDTReportEquipmentDetails,
                    dto.NDTReportProdSpacingMM,
                    dto.NDTReportMaterialThicknessMM,
                    dto.NDTReportLightEquipment,
                    dto.NDTReportLightingIntensity,
                    dto.NDTReportTemperature,
                    // LPT Fields
                    dto.NDTReportJobThickness,
                    dto.NDTReportTimingDetails,
                    dto.NDTReportPenetrationTime,
                    dto.NDTReportDwellTime,
                    dto.NDTReportDevelopmentTime,
                    dto.NDTReportIlluminationLight,
                    dto.NDTReportPenetrant,
                    dto.NDTReportPenetrantBatchNo,
                    dto.NDTReportDeveloper,
                    dto.NDTReportDeveloperBatchNo,
                    dto.NDTReportCleaner,
                    dto.NDTReportCleanerBatchNo,
                    dto.NDTReportLightingEquipment,
                    dto.NDTReportCleaningMethod,
                    // RT Fields
                    dto.NDTReportEquipmentMakeModel,
                    dto.NDTReportSourceSize,
                    dto.NDTReportEnergy,
                    dto.NDTReportFilmTypeBrand,
                    dto.NDTReportSFD,
                    dto.NDTReportIQITypeSensitivity,
                    dto.NDTReportExposureTime,
                    dto.NDTReportDevelopmentProcess,
                    // VT Fields
                    dto.NDTReportLightingIntensityVT,
                    dto.NDTReportProcedureRef,
                    dto.NDTReportAcceptanceStandard,
                    // UT Fields
                    dto.NDTReportApparatusMake,
                    dto.NDTReportModel,
                    dto.NDTReportSerialNo,
                    dto.NDTReportAreasOfCoverage,
                    dto.NDTReportCouplantUsed,
                    dto.NDTReportCalibrationBlock,
                    dto.NDTReportSizeMM,
                    dto.NDTReportRangeMM,
                    dto.NDTReportFrequencyMHz,
                    dto.NDTReportGainDB,
                    // Standard fields
                    dto.NDTReportSortOrder,
                    dto.NDTReportStatus,
                    dto.NDTReportVersion,
                    dto.NDTReportSourceType,
                    dto.NDTReportCreatedById,
                    dto.NDTReportCreatedOn,
                    dto.NDTReportModifiedById,
                    dto.NDTReportModifiedOn,
                    tenantid = login.ClientId
                };

                int reportId = await _QueryExecutor.ExecuteScalarAsync<int>(
                    login,
                    NDTReportQB.SAVE_NDTREPORT + "; SELECT CAST(SCOPE_IDENTITY() AS INT);",
                    reportParam
                );

                dto.NDTReportId = reportId;

                // -----------------------------------
                // STEP 2: CHILD INSERTS
                // -----------------------------------
                var statements = new List<(string sql, object param)>();

                if (dto.NDTConditionParamArray != null)
                {
                    foreach (var cp in dto.NDTConditionParamArray)
                    {
                        statements.Add((NDTReportQB.SAVE_NDTCONDITIONPARAM, new
                        {
                            NDTReportId = reportId,
                            cp.SlNo,
                            cp.MethodParamId,
                            cp.NumValue,
                            cp.TextValue,
                            cp.SelectValue,
                            cp.BoolValue,
                            cp.DateTimeValue,
                            cp.Remarks
                        }));
                    }
                }

                if (dto.NDTItemResultArray != null)
                {
                    foreach (var ir in dto.NDTItemResultArray)
                    {
                        statements.Add((NDTReportQB.SAVE_NDTITEMRESULT, new
                        {
                            NDTReportId = reportId,
                            ir.SlNo,
                            ir.ItemId,
                            ir.SkuId,
                            ir.PoNo,
                            ir.PoDate,
                            ir.ItemDescription,
                            ir.MaterialGrade,
                            ir.HeatCode,
                            ir.DateCode,
                            ir.SerialNumber,
                            ir.CavityNo,
                            ir.Qty,
                            ir.Result,
                            ir.ResultText,
                            ir.IndicationDetail,
                            ir.Remarks
                        }));
                    }
                }

                if (statements.Count > 0)
                {
                    await _QueryExecutor.ExecuteInTransactionAsync(login, statements);
                }

                return reportId;
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, "Error saving NDT Report");
                throw new Exception(error);
            }
        }

        public async Task<int> UpdateNDTReport(NDTReportDTO dto, LoginDTO login)
        {
            try
            {
                var statements = new List<(string sql, object param)>();

                // Delete existing children first
                statements.Add((NDTReportQB.DELETE_NDTCONDITIONPARAM, new { ndtreportid = dto.NDTReportId }));
                statements.Add((NDTReportQB.DELETE_NDTITEMRESULT, new { ndtreportid = dto.NDTReportId }));

                // UPDATE WITH ALL FIELDS
                var reportParam = new
                {
                    dto.NDTReportId,
                    dto.NDTReportOuId,
                    dto.NDTReportPeriodId,
                    dto.NDTReportBizTransactionId,
                    dto.NDTReportNdtMethodId,
                    dto.NDTReportParameterSet,
                    dto.NDTReportReportNo,
                    dto.NDTReportReportDate,
                    dto.NDTReportReferenceNo,
                    dto.NDTReportReferenceDate,
                    dto.NDTReportTestDate,
                    dto.NDTReportCustomerId,
                    dto.NDTReportHeatId,
                    dto.NDTReportNdtObjectTypeId,
                    dto.NDTReportNdtObjectId,
                    dto.NDTReportStageOfTestId,
                    dto.NDTReportSurfaceConditionId,
                    dto.NDTReportAcceptanceStandardId,
                    dto.NDTReportProcedureReference,
                    dto.NDTReportTestStartDateTime,
                    dto.NDTReportTestEndDateTime,
                    dto.NDTReportTotalTestHours,
                    dto.NDTReportIsThirdParty,
                    dto.NDTReportNdtAgencyId,
                    dto.NDTReportTestedById,
                    dto.NDTReportWitnessedById,
                    dto.NDTReportApprovedById,
                    dto.NDTReportCertificationLevel,
                    dto.NDTReportOverallResult,
                    dto.NDTReportRemarks,
                    // NEW NDT FIELDS
                    dto.NDTReportTypeOfTest,
                    dto.NDTReportMagneticMethod,
                    dto.NDTReportTypeOfMagnetisation,
                    dto.NDTReportDemagnetization,
                    dto.NDTReportMagnetizationMedium,
                    dto.NDTReportLptMethod,
                    dto.NDTReportApplicationOfPenetrant,
                    dto.NDTReportRadiationSource,
                    dto.NDTReportUtMethod,
                    dto.NDTReportProbeType,
                    // MT Fields
                    dto.NDTReportMagnetizingCurrent,
                    dto.NDTReportEquipmentDetails,
                    dto.NDTReportProdSpacingMM,
                    dto.NDTReportMaterialThicknessMM,
                    dto.NDTReportLightEquipment,
                    dto.NDTReportLightingIntensity,
                    dto.NDTReportTemperature,
                    // LPT Fields
                    dto.NDTReportJobThickness,
                    dto.NDTReportTimingDetails,
                    dto.NDTReportPenetrationTime,
                    dto.NDTReportDwellTime,
                    dto.NDTReportDevelopmentTime,
                    dto.NDTReportIlluminationLight,
                    dto.NDTReportPenetrant,
                    dto.NDTReportPenetrantBatchNo,
                    dto.NDTReportDeveloper,
                    dto.NDTReportDeveloperBatchNo,
                    dto.NDTReportCleaner,
                    dto.NDTReportCleanerBatchNo,
                    dto.NDTReportLightingEquipment,
                    dto.NDTReportCleaningMethod,
                    // RT Fields
                    dto.NDTReportEquipmentMakeModel,
                    dto.NDTReportSourceSize,
                    dto.NDTReportEnergy,
                    dto.NDTReportFilmTypeBrand,
                    dto.NDTReportSFD,
                    dto.NDTReportIQITypeSensitivity,
                    dto.NDTReportExposureTime,
                    dto.NDTReportDevelopmentProcess,
                    // VT Fields
                    dto.NDTReportLightingIntensityVT,
                    dto.NDTReportProcedureRef,
                    dto.NDTReportAcceptanceStandard,
                    // UT Fields
                    dto.NDTReportApparatusMake,
                    dto.NDTReportModel,
                    dto.NDTReportSerialNo,
                    dto.NDTReportAreasOfCoverage,
                    dto.NDTReportCouplantUsed,
                    dto.NDTReportCalibrationBlock,
                    dto.NDTReportSizeMM,
                    dto.NDTReportRangeMM,
                    dto.NDTReportFrequencyMHz,
                    dto.NDTReportGainDB,
                    // Standard fields
                    dto.NDTReportSortOrder,
                    dto.NDTReportStatus,
                    dto.NDTReportVersion,
                    dto.NDTReportSourceType,
                    dto.NDTReportModifiedById,
                    dto.NDTReportModifiedOn,
                    tenantid = login.ClientId
                };
                statements.Add((NDTReportQB.UPDATE_NDTREPORT, reportParam));

                // Re-insert children
                if (dto.NDTConditionParamArray != null)
                {
                    foreach (var cp in dto.NDTConditionParamArray)
                    {
                        cp.NDTReportId = dto.NDTReportId;
                        statements.Add((NDTReportQB.SAVE_NDTCONDITIONPARAM, cp));
                    }
                }

                if (dto.NDTItemResultArray != null)
                {
                    foreach (var ir in dto.NDTItemResultArray)
                    {
                        ir.NDTReportId = dto.NDTReportId;
                        statements.Add((NDTReportQB.SAVE_NDTITEMRESULT, ir));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(login, statements);
                return 1;
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(error);
            }
        }

        public async Task<int> DeleteNDTConditionParam(int NDTReportId, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new { ndtreportid = NDTReportId };
                return await _QueryExecutor.ExecuteAsync(LoginDTO, NDTReportQB.DELETE_NDTCONDITIONPARAM, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> DeleteNDTItemResult(int NDTReportId, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new { ndtreportid = NDTReportId };
                return await _QueryExecutor.ExecuteAsync(LoginDTO, NDTReportQB.DELETE_NDTITEMRESULT, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> DeleteNDTReport(int NDTReportId, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new { ndtreportid = NDTReportId, tenantid = LoginDTO.ClientId };
                var result = await _QueryExecutor.ExecuteAsync(LoginDTO, NDTReportQB.DELETE_NDTREPORT, parameters);
                if (result > 0)
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                else
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(error);
            }
        }
    }
}