using CRMDAL.DTO.Call;
using CRMDAL.Query.Call;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using static GB5Shared.GB5Constant.Constant;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.Json;
using System.Text.Json.Serialization;
using System.Threading.Tasks;

namespace CRMDAL.CustomCode.Call
{
    public class CallDAL : ICallDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public CallDAL(IQueryExecutor queryExecutor, IValidation Validation)
        {
            _QueryExecutor = queryExecutor;
            _Validation = Validation;
        }

        // ═════════════════════════════════════════════════════════════════════════
        // PRE-QUERY PARAMETER LOGGER
        // ═════════════════════════════════════════════════════════════════════════

        private static readonly string _LogPath =
            Path.Combine(Path.GetTempPath(), "CRMDAL_CallDAL.log");

        private static readonly bool _FileLogEnabled =
            !string.Equals(
                Environment.GetEnvironmentVariable("CRMDAL_CALL_LOG"), "0",
                StringComparison.OrdinalIgnoreCase);

        private static void LogParameters(string operation, int callId, object parameters)
        {
            try
            {
                if (parameters is null)
                {
                    string nullLog =
                        $"[{DateTime.UtcNow:yyyy-MM-dd HH:mm:ss.fff}] {operation} | CallId={callId} | Parameters = NULL";

                    Debug.WriteLine(nullLog);

                    return;
                }

                var options = new JsonSerializerOptions
                {
                    WriteIndented = true,
                    DefaultIgnoreCondition = JsonIgnoreCondition.Never
                };

                string json = System.Text.Json.JsonSerializer.Serialize(parameters, options);

                var sb = new StringBuilder();
                sb.AppendLine();
                //sb.AppendLine(new string('═', 120));
                //sb.AppendLine($"CALL DAL LOG : {operation}");
                //sb.AppendLine($"CallId        : {callId}");
                //sb.AppendLine($"Timestamp     : {DateTime.UtcNow:yyyy-MM-dd HH:mm:ss.fff}");
                //sb.AppendLine(new string('═', 120));
                //sb.AppendLine(json);
                //sb.AppendLine(new string('═', 120));
                sb.AppendLine();

                string logText = sb.ToString();

                Debug.WriteLine(logText);
            }
            catch (Exception ex)
            {
                Debug.WriteLine($"LogParameters Error: {ex}");
            }
        }

        // ─────────────────────────────────────────────────────────────────────────

        public async Task<string> GetCall(int callId, LoginDTO loginDTO)
        {
            try
            {
                var sql = CallQB.GET_CALL_ID;
                var parameters = new { callid = callId };
                var callDict = new Dictionary<int, CallDTO>();

                await _QueryExecutor.QueryMultiMapAsync<CallDTO, CallVsDefectsDTO, CallDTO>(
                    loginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!callDict.TryGetValue(parent.CallId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.CallVsDefectsArray = new List<CallVsDefectsDTO>();
                            callDict[parent.CallId] = existingParent;
                        }
                        if (child != null && child.CallVsDefectsId != 0)
                            existingParent.CallVsDefectsArray.Add(child);
                        return existingParent;
                    },
                    parameters,
                    splitOn: "CallVsDefectsId"
                );

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

        public async Task<int> SaveCall(CallDTO dto, LoginDTO login, DbTransaction tx)
        {
            try
            {
                var parameters = BuildCallParameters(dto, isInsert: true);
                LogParameters("SAVE_CALL  [INSERT]", dto.CallId, parameters);
                return await _QueryExecutor.ExecuteAsync(login, CallQB.SAVE_CALL, parameters, tx);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateCall(CallDTO dto, LoginDTO login, DbTransaction tx)
        {
            try
            {
                var parameters = BuildCallParameters(dto, isInsert: false);
                LogParameters("UPDATE_CALL  [UPDATE]", dto.CallId, parameters);
                return await _QueryExecutor.ExecuteAsync(login, CallQB.UPDATE_CALL, parameters, tx);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async System.Threading.Tasks.Task SaveCallVsDefects(
            List<CallVsDefectsDTO> items, LoginDTO login, DbTransaction tx)
        {
            try
            {
                foreach (var item in items)
                {
                    if (item.ProblemId <= 0) continue;

                    var parameters = new
                    {
                        CallVsDefectsId = item.CallVsDefectsId,
                        CallId = item.CallId,
                        CallVsDefectsSlNo = item.CallVsDefectsSlNo,
                        ProblemId = item.ProblemId
                    };

                    await _QueryExecutor.ExecuteAsync(login, CallQB.SAVE_CALL_VS_DEFECTS, parameters, tx);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error saving CallVsDefects", ex);
            }
        }

        public async System.Threading.Tasks.Task DeleteCallVsDefects(
            int callId, LoginDTO login, DbTransaction tx)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(login,
                    CallQB.DELETE_CALL_VS_DEFECTS, new { CallId = callId }, tx);
            }
            catch (Exception ex)
            {
                throw new Exception($"Error deleting CallVsDefects for CallId: {callId}", ex);
            }
        }

        public async System.Threading.Tasks.Task UpdateCompletedStatusFields(
            int callId, int completedById, int completedOnTime, LoginDTO login, DbTransaction tx)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(login, CallQB.UPDATE_COMPLETED_STATUS_FIELDS,
                    new { CallId = callId, CompletedById = completedById, CompletedOnTime = completedOnTime },
                    tx);
            }
            catch (Exception ex)
            {
                throw new Exception($"Error updating completed status for CallId: {callId}", ex);
            }
        }

        public async System.Threading.Tasks.Task UpdateClosedStatusFields(
            int callId, int closedById, int closedOnTime, LoginDTO login, DbTransaction tx)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(login, CallQB.UPDATE_CLOSED_STATUS_FIELDS,
                    new { CallId = callId, ClosedById = closedById, ClosedOnTime = closedOnTime },
                    tx);
            }
            catch (Exception ex)
            {
                throw new Exception($"Error updating closed status for CallId: {callId}", ex);
            }
        }

        public async Task<int?> GetCallCreatedById(int callId, LoginDTO login)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<int>(
                    login,
                    CallQB.GET_CALL_CREATED_BY_ID,
                    new { CallId = callId });

                return result.FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw new Exception($"Error fetching CreatedById for CallId: {callId}", ex);
            }
        }

        public async Task<EmployeeInfoDTO?> GetEmployeeInfo(int employeeId, LoginDTO login)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<EmployeeInfoDTO>(
                    login, CallQB.GET_EMPLOYEE_INFO, new { EmployeeId = employeeId });
            }
            catch (Exception ex)
            {
                throw new Exception($"Error fetching employee info for EmployeeId: {employeeId}", ex);
            }
        }

        public async Task<AssetContactInfoDTO?> GetAssetContactInfo(int assetId, LoginDTO login)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<AssetContactInfoDTO>(
                    login, CallQB.GET_ASSET_CONTACT_INFO, new { AssetId = assetId });
            }
            catch (Exception ex)
            {
                throw new Exception($"Error fetching asset contact info for AssetId: {assetId}", ex);
            }
        }

        public async Task<ContactInfoDTO?> GetContactInfo(int contactId, LoginDTO login)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<ContactInfoDTO>(
                    login, CallQB.GET_CONTACT_INFO, new { ContactId = contactId });
            }
            catch (Exception ex)
            {
                throw new Exception($"Error fetching contact info for ContactId: {contactId}", ex);
            }
        }

        public async Task<UserContactInfoDTO?> GetUserContactInfo(int userId, LoginDTO login)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<UserContactInfoDTO>(
                    login, CallQB.GET_USER_CONTACT_INFO, new { UserId = userId });
            }
            catch (Exception ex)
            {
                throw new Exception($"Error fetching user contact info for UserId: {userId}", ex);
            }
        }

        public async Task<bool> CheckPartyProductExists(int partyId, int itemId, LoginDTO login)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<int>(login, CallQB.GET_PARTY_PRODUCT,
                    new { PartyId = partyId, ItemId = itemId });
                return result.Any();
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"Error checking party product for PartyId: {partyId}, ItemId: {itemId}", ex);
            }
        }

        public async Task<bool> BizTransactionTypeExists(int bizTransactionTypeId, int ouId, int periodId, LoginDTO login)
        {
            try
            {
                int count = await _QueryExecutor.ExecuteScalarAsync<int>(login, CallQB.CHECK_BIZTRANSACTIONTYPE_EXISTS,
                    new { BizTransactionTypeId = bizTransactionTypeId, OUId = ouId, PeriodId = periodId });
                return count > 0;
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"Error validating BizTransactionType {bizTransactionTypeId} for OU {ouId}, Period {periodId}", ex);
            }
        }

        public async System.Threading.Tasks.Task InsertPartyProduct(
            int callId, LoginDTO login, DbTransaction tx)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(login,
                    CallQB.INSERT_PARTY_PRODUCT, new { CallId = callId }, tx);
            }
            catch (Exception ex)
            {
                throw new Exception($"Error inserting party product for CallId: {callId}", ex);
            }
        }

        public async System.Threading.Tasks.Task UpdateCallAfterPartyProduct(
            int callId, LoginDTO login, DbTransaction tx)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(login,
                    CallQB.UPDATE_CALL_AFTER_PARTY_PRODUCT, new { CallId = callId }, tx);
            }
            catch (Exception ex)
            {
                throw new Exception($"Error updating call after party product for CallId: {callId}", ex);
            }
        }

        public async Task<PartyProductAddressDTO?> ValidatePartyProductAddress(
            int partyProductId, LoginDTO login)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<PartyProductAddressDTO>(
                    login, CallQB.VALIDATE_PARTY_PRODUCT_ADDRESS,
                    new { PartyProductId = partyProductId });
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"Error validating party product address for PartyProductId: {partyProductId}", ex);
            }
        }

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

                switch (LoginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        SQL = CallPicklistQB.GET_SELECTLIST_CALL_SQL;
                        break;

                    case DBType.PostGre:
                        SQL = CallPicklistQB.GET_SELECTLIST_CALL_PG;
                        break;

                    case DBType.Oracle:
                        SQL = CallPicklistQB.GET_SELECTLIST_CALL_ORACLE;
                        break;

                    case DBType.MySQL:
                        SQL = CallPicklistQB.GET_SELECTLIST_CALL_MYSQL;
                        break;

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

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

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

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

        // ─────────────────────────────────────────────────────────────────────────────
        // BuildCallParameters
        //
        // GB4 parity: NHibernate with int properties sent the EXACT value to SQL.
        //   -1 → SQL receives -1
        //    0 → SQL receives 0
        //  1234 → SQL receives 1234
        //
        // It NEVER converted -1→0 or 0→NULL. NULL was only sent for int? (nullable)
        // properties set to null. ALL FK fields in CallDTO are `int`, not `int?`.
        //
        // Therefore: pass all int values directly. No FK() or NOT_NULL_FK wrappers.
        // Only STR() for nullable text columns (empty/whitespace → DBNull).
        // Only REQ() for NOT NULL text columns (empty/whitespace → "").
        //
        // FK() and NOT_NULL_FK have been REMOVED — they were the root cause of
        // both the original NULL crash and the wrong-value issues.
        // ─────────────────────────────────────────────────────────────────────────────
        private static object BuildCallParameters(CallDTO dto, bool isInsert)
        {
            // Nullable text: empty/whitespace → DBNull.Value
            static object STR(string? value) =>
                string.IsNullOrWhiteSpace(value)
                    ? DBNull.Value
                    : value.Trim();

            // NOT NULL text: empty/whitespace → ""
            static string REQ(string? value) =>
                string.IsNullOrWhiteSpace(value)
                    ? string.Empty
                    : value.Trim();

            return new
            {
                // Primary Key
                CallId = dto.CallId,

                // Foreign Keys — passed AS-IS from POST data (GB4 parity)
                OUId = dto.OUId,
                BIZTransactionTypeId = dto.BIZTransactionTypeId,
                PeriodId = dto.PeriodId,

                // Header
                CallNumber = REQ(dto.CallNumber),
                CallDate = dto.CallDate,
                CallReferenceNumber = STR(dto.CallReferenceNumber),
                CallReferenceDate = dto.CallReferenceDate,
                CallReportedByName = REQ(dto.CallReportedByName),

                // Object Link
                ObjectTypeId = dto.ObjectTypeId,
                CallObjectId = dto.CallObjectId,

                // Foreign Keys — passed AS-IS from POST data (GB4 parity)
                PartyId = dto.PartyId,
                PartyBranchId = dto.PartyBranchId,
                EmployeeId = dto.EmployeeId,
                ContactId = dto.ContactId,
                AddressId = dto.AddressId,
                ReportedById = dto.ReportedById,
                ReceivedById = dto.ReceivedById,
                CallTypeId = dto.CallTypeId,
                CallContract = dto.CallContract,
                CallNatureId = dto.CallNatureId,
                CallPriorityId = dto.CallPriorityId,
                AllotedToEmployeeId = dto.AllotedToEmployeeId,
                AllotedToContactId = dto.AllotedToContactId,
                AllotedToPartyId = dto.AllotedToPartyId,
                CallOnObjectTypeId = dto.CallOnObjectTypeId,
                CallCallOnObjectId = dto.CallCallOnObjectId,
                ProblemNatureId = dto.ProblemNatureId,
                EstimatedById = dto.EstimatedById,
                PersonId = dto.PersonId,
                StandByItemId = dto.StandByItemId,
                CompletedById = dto.CompletedById,
                ClosedById = dto.ClosedById,
                ReasonId = dto.ReasonId,
                RootCauseId = dto.RootCauseId,
                AllocationId = dto.AllocationId,

                // Required Text
                CallReportedProblem = REQ(dto.CallReportedProblem),
                CallActionTaken = REQ(dto.CallActionTaken),
                CallCustomerRemarks = REQ(dto.CallCustomerRemarks),
                CallItemReceivedRemarks = REQ(dto.CallItemReceivedRemarks),
                CallStandByRemarks = REQ(dto.CallStandByRemarks),
                CallCustomerFeedback = REQ(dto.CallCustomerFeedback),
                CallManufacturerSlno = REQ(dto.CallManufacturerSlno),

                // Optional Text
                CallCallOnName = STR(dto.CallCallOnName),
                RootCauseDescription = STR(dto.RootCauseDescription),

                // Status / Flags
                CallCallReceivedMode = dto.CallCallReceivedMode,
                CallCallGenerationType = dto.CallCallGenerationType,
                CallContractCycleNumber = dto.CallContractCycleNumber,
                CallAllotedToType = dto.CallAllotedToType,
                CallCallOriginalStatus = dto.CallCallOriginalStatus,
                CallCoverageStatus = dto.CallCoverageStatus,
                CallIndoorOutdoor = dto.CallIndoorOutdoor,
                CallIsItemReceived = dto.CallIsItemReceived,
                CallIsStandByGiven = dto.CallIsStandByGiven,
                CallIsClosed = dto.CallIsClosed,
                CallIsAccounted = dto.CallIsAccounted,
                CallCallonObjectStatus = dto.CallCallonObjectStatus,
                CallAllotedMode = dto.CallAllotedMode,

                // Dates
                CallProblemDate = dto.CallProblemDate,
                CallProblemTime = dto.CallProblemTime,
                CallPromissedDate = dto.CallPromissedDate,
                CallPlanDate = dto.CallPlanDate,
                CallPossibleDate = dto.CallPossibleDate,
                CallCompletedOn = dto.CallCompletedOn,
                CallCompletedOnTime = dto.CallCompletedOnTime,
                CallClosedOn = dto.CallClosedOn,
                CallClosedOnTime = dto.CallClosedOnTime,

                // Amounts
                CallEstimatedLabourAmount = dto.CallEstimatedLabourAmount,
                CallEstimatedSparesAmount = dto.CallEstimatedSparesAmount,
                CallEstimatedOthersAmount = dto.CallEstimatedOthersAmount,
                CallEstimatedTotalAmount = dto.CallEstimatedTotalAmount,
                CallLabourAmount = dto.CallLabourAmount,
                CallSparesAmount = dto.CallSparesAmount,
                CallOtherAmount = dto.CallOtherAmount,
                CallTotalAmount = dto.CallTotalAmount,

                // Record State
                CallVersion = dto.CallVersion,
                CallStatus = dto.CallStatus,

                // Audit
                CallCreatedById = dto.CallCreatedById,
                CallCreatedOn = dto.CallCreatedOn,
                CallModifiedById = dto.CallModifiedById,
                CallModifiedOn = dto.CallModifiedOn
            };
        }
    }
}