using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using PayRollDAL.DTO.Employee;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace PayRollDAL.CustomeCode.Employee
{

    public class DynamicAddonService : IDynamicAddonService
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly ILogger<DynamicAddonService> _logger;
        private static readonly object _lock = new object();
        private static Dictionary<string, List<string>> _addonFieldCache = new Dictionary<string, List<string>>();

        public DynamicAddonService(IQueryExecutor queryExecutor, ILogger<DynamicAddonService> logger)
        {
            _queryExecutor = queryExecutor;
            _logger = logger;
        }

        public async Task<List<string>> GetAddonFieldNamesAsync(LoginDTO loginDTO)
        {
            string cacheKey = $"{loginDTO.ClientId}_EMPLOYEEADDON";

            lock (_lock)
            {
                if (_addonFieldCache.TryGetValue(cacheKey, out var cachedFields))
                    return cachedFields;
            }

            try
            {
                // Step 1: Get ENTITYID for EMPLOYEEADDON
                var entityId = await _queryExecutor.QuerySingleAsync<int?>(
                    loginDTO,
                    "SELECT TOP 1 ENTITYID FROM MENTITY WHERE ENTITYCODE = 'EMPLOYEEADDON' AND STATUS = 1",
                    null);

                if (entityId == null || entityId == 0)
                {
                    lock (_lock)
                        _addonFieldCache[cacheKey] = new List<string>();
                    return new List<string>();
                }

                // Step 2: Get configured fields from MADDONFIELDS
                var fieldNames = await _queryExecutor.QueryAsync<string>(
                    loginDTO,
                    @"SELECT FIELDNAME FROM MADDONFIELDS 
                      WHERE ENTITYID = @EntityId AND STATUS = 1 
                      ORDER BY SORTORDER",
                    new { EntityId = entityId });

                var fieldList = fieldNames.ToList();

                lock (_lock)
                    _addonFieldCache[cacheKey] = fieldList;

                return fieldList;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "GetAddonFieldNames failed");
                return new List<string>();
            }
        }

        public async Task<Dictionary<string, object>> GetAddonDataDictionaryAsync(LoginDTO loginDTO, int employeeId)
        {
            try
            {
                var fieldNames = await GetAddonFieldNamesAsync(loginDTO);

                if (fieldNames == null || fieldNames.Count == 0)
                    return new Dictionary<string, object>();

                // Build dynamic SQL with only the configured fields
                var selectClause = string.Join(", ", fieldNames.Select(f => $"[{f}]"));

                var sql = $@"
                    SELECT 
                        EMPLOYEEID,
                        {selectClause}
                    FROM MEMPLOYEEADDON 
                    WHERE EMPLOYEEID = @EmployeeId";

                var result = await _queryExecutor.QuerySingleAsync<dynamic>(
                    loginDTO,
                    sql,
                    new { EmployeeId = employeeId });

                if (result == null)
                    return new Dictionary<string, object>();

                var dict = (IDictionary<string, object>)result;
                var addonData = new Dictionary<string, object>();

                foreach (var kvp in dict)
                {
                    if (kvp.Key != "EMPLOYEEID" && kvp.Value != null && kvp.Value != DBNull.Value)
                    {
                        addonData[kvp.Key] = kvp.Value;
                    }
                }

                return addonData;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "GetAddonDataDictionary failed for employee {EmployeeId}", employeeId);
                return new Dictionary<string, object>();
            }
        }

        public async Task<string> GetAddonFieldsForEmployeeAsync(LoginDTO loginDTO, int employeeId)
        {
            var addonData = await GetAddonDataDictionaryAsync(loginDTO, employeeId);

            if (addonData == null || addonData.Count == 0)
                return "{}";

            var formattedData = new Dictionary<string, object>();
            foreach (var kvp in addonData)
            {
                if (kvp.Value is DateTime dateTime)
                {
                    var epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
                    var millis = (long)(dateTime.ToUniversalTime() - epoch).TotalMilliseconds;
                    formattedData[kvp.Key] = $"/Date({millis})/";
                }
                else
                {
                    formattedData[kvp.Key] = kvp.Value;
                }
            }

            return JsonConvert.SerializeObject(formattedData);
        }

        //public async Task<EmployeeAddon> GetEmployeeAddonAsync(LoginDTO loginDTO, int employeeId)
        //{
        //    var employeeAddon = new EmployeeAddon
        //    {
        //        AddonEmployeeId = employeeId,
        //        GetAddon = "{}"
        //    };

        //    try
        //    {
        //        var addonData = await GetAddonDataDictionaryAsync(loginDTO, employeeId);

        //        if (addonData == null || addonData.Count == 0)
        //        {
        //            employeeAddon.FeAddon = "{}";
        //            employeeAddon.Attributes = new Dictionary<string, object>();
        //            return employeeAddon;
        //        }

        //        employeeAddon.Attributes = addonData;

        //        var feAddonData = new Dictionary<string, object>();
        //        foreach (var kvp in addonData)
        //        {
        //            if (kvp.Value is DateTime dateTime)
        //            {
        //                var epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
        //                var millis = (long)(dateTime.ToUniversalTime() - epoch).TotalMilliseconds;
        //                feAddonData[kvp.Key] = $"/Date({millis})/";
        //            }
        //            else
        //            {
        //                feAddonData[kvp.Key] = kvp.Value;
        //            }
        //        }

        //        employeeAddon.FeAddon = JsonConvert.SerializeObject(feAddonData);

        //        return employeeAddon;
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine($"Error creating EmployeeAddon: {ex.Message}");
        //        employeeAddon.FeAddon = "{}";
        //        employeeAddon.Attributes = new Dictionary<string, object>();
        //        return employeeAddon;
        //    }
        //}

        public void ClearCache()
        {
            lock (_lock)
                _addonFieldCache.Clear();
        }
    }
}