// File: EIPFlowDAL.cs
using FrameworkDAL.DTO.EIPConversation;
using FrameworkDAL.Query.EIPConversation.EIPFlow;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace FrameworkDAL.CustomCode.EIPConversation.EIPFlow
{
    public class EIPFlowDAL : IEIPFlowDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly ILogger<EIPFlowDAL> _logger;

        public EIPFlowDAL(
            IQueryExecutor queryExecutor,
            ILogger<EIPFlowDAL> logger)
        {
            _queryExecutor = queryExecutor ?? throw new ArgumentNullException(nameof(queryExecutor));
            _logger = logger ?? throw new ArgumentNullException(nameof(logger));
        }

        // =========================================================
        // GET FLOW DEFINITION
        // =========================================================
        public async Task<EIPFlowDTO?> GetFlowDefinitionAsync(
            string tenantId,
            string flowCode,
            LoginDTO loginDTO,
            CancellationToken cancellationToken = default)
        {
            if (string.IsNullOrWhiteSpace(tenantId))
                throw new ArgumentException("TenantId must be provided.", nameof(tenantId));

            if (string.IsNullOrWhiteSpace(flowCode))
                throw new ArgumentException("FlowCode must be provided.", nameof(flowCode));

            cancellationToken.ThrowIfCancellationRequested();

            var normalizedTenant = tenantId.Trim();
            var normalizedFlow = flowCode.Trim().ToUpperInvariant();

            const string sql = @"
SELECT TOP 1 
       F.FLOWDEFINITIONID AS FlowDefinitionId,
       F.TENANTID         AS TenantId,
       F.FLOWCODE         AS FlowCode,
       F.FLOWVERSION      AS FlowVersion,
       F.FLOWSTATUS       AS FlowStatus,
       F.VERSION          AS Version,
       F.STATUS           AS Status,
       F.JSONDEFINITION   AS JsonDefinition,
       F.CREATEDON        AS CreatedOn,
       F.MODIFIEDON       AS ModifiedOn
FROM MEIPFLOWDEFINITION F
WHERE 
    F.FLOWCODE = @FlowCode
    AND F.STATUS = 1
    AND (F.TENANTID = @TenantId OR F.TENANTID = -1)
ORDER BY 
    CASE WHEN F.TENANTID = @TenantId THEN 0 ELSE 1 END,
    F.FLOWVERSION DESC";

            try
            {
                _logger.LogInformation(
                    "DAL | GetFlow | Tenant={Tenant} | Flow={Flow}",
                    normalizedTenant,
                    normalizedFlow);

                var flow = await _queryExecutor.QuerySingleAsync<EIPFlowDTO>(
                    loginDTO,
                    sql,
                    new
                    {
                        TenantId = normalizedTenant,
                        FlowCode = normalizedFlow
                    });

                if (flow == null)
                {
                    _logger.LogWarning(
                        "DAL | GetFlow | NotFound | Tenant={Tenant} | Flow={Flow}",
                        normalizedTenant,
                        normalizedFlow);
                    return null;
                }

                // Initialize steps dictionary
                flow.Steps ??= new Dictionary<string, EIPFlowStepDTO>();

                // Deserialize JSON safely (array or dictionary)
                if (!string.IsNullOrWhiteSpace(flow.JsonDefinition))
                {
                    DeserializeStepsSafe(flow);
                }

                _logger.LogInformation(
                    "DAL | GetFlow | Loaded | FlowDefinitionId={FlowId} | StepCount={Count}",
                    flow.FlowDefinitionId,
                    flow.Steps.Count);

                return flow;
            }
            catch (OperationCanceledException)
            {
                _logger.LogWarning(
                    "DAL | GetFlow | Cancelled | Tenant={Tenant} | Flow={Flow}",
                    normalizedTenant,
                    normalizedFlow);
                throw;
            }
            catch (Exception ex)
            {
                _logger.LogError(
                    ex,
                    "DAL | GetFlow | Error | Tenant={Tenant} | Flow={Flow}",
                    normalizedTenant,
                    normalizedFlow);
                throw;
            }
        }

        // =========================================================
        // SAFE JSON DESERIALIZATION (ARRAY + DICTIONARY)
        // =========================================================
        /// <summary>
        /// Deserialize JSON flow definition safely.
        /// Supports both array and dictionary formats.
        /// Injects StepCode dynamically if missing.
        /// Normalizes all step data.
        /// </summary>
        private void DeserializeStepsSafe(EIPFlowDTO flow)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(flow.JsonDefinition))
                {
                    flow.Steps = new Dictionary<string, EIPFlowStepDTO>();
                    return;
                }

                var json = flow.JsonDefinition.Trim();
                var token = JsonConvert.DeserializeObject<object>(json);

                // =========================================================
                // CASE 1: ARRAY FORMAT
                // =========================================================
                if (token is Newtonsoft.Json.Linq.JArray)
                {
                    var stepsArray = JsonConvert.DeserializeObject<List<EIPFlowStepDTO>>(json)
                                     ?? new List<EIPFlowStepDTO>();

                    flow.Steps = new Dictionary<string, EIPFlowStepDTO>();

                    foreach (var step in stepsArray)
                    {
                        if (string.IsNullOrWhiteSpace(step.StepCode))
                            step.StepCode = Guid.NewGuid().ToString(); // fallback unique key

                        var key = step.StepCode.Trim();

                        NormalizeStep(step);
                        flow.Steps[key] = step;
                    }

                    // Deterministic StartStep fallback
                    if (string.IsNullOrWhiteSpace(flow.StartStepCode) && flow.Steps.Count > 0)
                        flow.StartStepCode = flow.Steps.Keys.OrderBy(k => k).First();

                    _logger.LogInformation(
                        "DAL | Deserialize | ARRAY→DICT | Steps={Count} | Start={Start}",
                        flow.Steps.Count,
                        flow.StartStepCode);

                    return;
                }

                // =========================================================
                // CASE 2: DICTIONARY FORMAT
                // =========================================================
                if (token is Newtonsoft.Json.Linq.JObject)
                {
                    var temp = JsonConvert.DeserializeObject<TempFlowDTO>(json);

                    flow.StartStepCode = temp?.StartStepCode?.Trim() ?? string.Empty;
                    flow.Steps = temp?.Steps ?? new Dictionary<string, EIPFlowStepDTO>();

                    // ✅ Inject StepCode from dictionary keys
                    InjectStepCodeFromDictionary(flow.Steps);

                    _logger.LogInformation(
                        "DAL | Deserialize | DICTIONARY | Steps={Count} | Start={Start}",
                        flow.Steps.Count,
                        flow.StartStepCode);

                    return;
                }

                // =========================================================
                // UNKNOWN FORMAT
                // =========================================================
                flow.Steps = new Dictionary<string, EIPFlowStepDTO>();
                _logger.LogWarning("DAL | Deserialize | Unknown JSON format");
            }
            catch (JsonException ex)
            {
                _logger.LogError(ex, "DAL | Deserialize | JSON parsing error");
                flow.Steps = new Dictionary<string, EIPFlowStepDTO>();
            }
        }

        // =========================================================
        // HELPER: Inject StepCode from Dictionary Keys
        // =========================================================
        /// <summary>
        /// Ensures every step has StepCode populated from the dictionary key.
        /// Also normalizes step content.
        /// </summary>
        private void InjectStepCodeFromDictionary(Dictionary<string, EIPFlowStepDTO> steps)
        {
            foreach (var kvp in steps.ToList())
            {
                var key = kvp.Key?.Trim();
                if (string.IsNullOrWhiteSpace(key))
                {
                    steps.Remove(kvp.Key);
                    continue;
                }

                var step = kvp.Value;

                // Populate StepCode from dictionary key if missing
                step.StepCode = string.IsNullOrWhiteSpace(step.StepCode) ? key : step.StepCode.Trim();

                NormalizeStep(step);

                // Correct dictionary key if needed
                if (key != kvp.Key)
                {
                    steps.Remove(kvp.Key);
                    steps[key] = step;
                }
            }
        }

        // =========================================================
        // NORMALIZE SINGLE STEP
        // =========================================================
        private void NormalizeStep(EIPFlowStepDTO step)
        {
            if (step == null) return;

            step.StepCode = step.StepCode?.Trim() ?? string.Empty;
            step.StepType = step.StepType?.Trim().ToUpperInvariant() ?? "UNKNOWN";
            step.NextStepCode = step.NextStepCode?.Trim();

            step.DecisionMap ??= new Dictionary<string, object>();
            step.Metadata ??= new Dictionary<string, object>();
            step.Actions ??= new List<EIPFlowActionDTO>();
        }

        // =========================================================
        // SAVE FLOW DEFINITION
        // =========================================================
        public async Task<int> SaveFlowDefinitionAsync(
            EIPFlowDTO flowDTO,
            LoginDTO loginDTO,
            CancellationToken cancellationToken = default)
        {
            if (flowDTO == null)
                throw new ArgumentNullException(nameof(flowDTO));

            cancellationToken.ThrowIfCancellationRequested();

            try
            {
                _logger.LogInformation(
                    "DAL | SaveFlow | Tenant={Tenant} | Flow={Flow}",
                    flowDTO.TenantId,
                    flowDTO.FlowCode);

                var result = await _queryExecutor.ExecuteAsync(
                    loginDTO,
                    EIPFlowQB.SAVE_EIPFLOW,
                    flowDTO);

                _logger.LogInformation(
                    "DAL | SaveFlow | Completed | RowsAffected={Rows}",
                    result);

                return result;
            }
            catch (OperationCanceledException)
            {
                _logger.LogWarning(
                    "DAL | SaveFlow | Cancelled | Flow={Flow}",
                    flowDTO.FlowCode);
                throw;
            }
            catch (Exception ex)
            {
                _logger.LogError(
                    ex,
                    "DAL | SaveFlow | Error | Flow={Flow}",
                    flowDTO.FlowCode);
                throw;
            }
        }

        // =========================================================
        // TEMP DTO (Dictionary JSON Support)
        // =========================================================
        private sealed class TempFlowDTO
        {
            public string StartStepCode { get; set; } = string.Empty;
            public Dictionary<string, EIPFlowStepDTO> Steps { get; set; } = new Dictionary<string, EIPFlowStepDTO>();
        }
    }
}