using AipGateway.AIP;
using AipGateway.API.Configurations;
using AipGateway.API.Domain.Entities;
using AipGateway.API.Domain.IRepositories.IGenericRepositories;
using AipGateway.API.Infrastructure.Persistence;
using AipGateway.API.Models;
using AipGateway.API.Utils;
using System.Collections;
using System.Runtime.CompilerServices;

namespace AipGateway.API.Services
{
    public class AipFileApiService
    {
        private readonly ILogger<AipFileJobService> _log;
        private readonly AipSettings _aipSetting;
        private readonly ApplicationDbContext _dbContext;
        private readonly IUnitOfWork _unitOfWork;

        private int _ServerId;


        public AipFileApiService(ILogger<AipFileJobService> logger, IConfiguration configuration, ApplicationDbContext dbContext, IUnitOfWork unitOfWork)
        {
            _log = logger;
            _dbContext = dbContext;
            _unitOfWork = unitOfWork;

            _aipSetting = new();
            configuration.GetSection(nameof(AipSettings)).Bind(_aipSetting);

            if (!int.TryParse(configuration["ServerId"], out _ServerId))
            {
                _ServerId = 0;
            }
            _log.LogError("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx: AipFileDbService");
        }

        public void LoadConfig()
        {
            _log.LogError("Before: {0}", _aipSetting.ToString());

            List<TbAipConfig> result = _dbContext.AipConfigs.Where(p => p.AipServerId == _ServerId).ToList();
            foreach(TbAipConfig config in result)
            {
                _log.LogInformation("{0}, {1}, {2}", config.Id, config.ConfigKey, config.ConfigValue);
                _aipSetting.SetValue(config.ConfigKey, config.ConfigValue);
            }
            _log.LogError(" After: {0}", _aipSetting.ToString());
        }

        public AipSettings GetAipSettings()
        {
            return _aipSetting;
        }

        private void DownloadAipFileLabels()
        {
            //using var transaction = _dbContext.Database.BeginTransaction();
            try
            {
                _log.LogInformation("AipFileApiService.DownloadAipFileLabels: Start.");

                Hashtable keyMap = new Hashtable();

                List<TbAipLabel> newLabels = new List<TbAipLabel>();
                List<TbAipLabel> updLabels = new List<TbAipLabel>();
                Hashtable labelMap = new Hashtable();

                List<AipLabel>? lavels = ContainerService.aipFileManager.SensitivityLabels();

                _log.LogInformation("AipFileApiService.DownloadAipFileLabels.SensitivityLabels(): {} EA.", lavels?.Count);
                if (lavels == null)
                {
                    return;
                }

                FormattableString sql = $"SELECT t.LabelId, t.CreatedAt, t.DeletedAt, t.LabelDesc, t.LabelGuid, t.LabelName, t.UseYn FROM TB_AIP_LABEL AS t;";
                var result = _dbContext.SqlQuery<TbAipLabel>(sql).ToList();
                if (result != null)
                {
                    foreach (TbAipLabel label in result)
                    {
                        labelMap.Add(label.LabelGuid, label);
                    }
                }

                foreach (AipLabel label in lavels)
                {
                    keyMap.Add(label.Id, label);

                    if (labelMap.ContainsKey(label.Id))
                    {
                        TbAipLabel? orgLabel = labelMap[label.Id] as TbAipLabel;
                        if (orgLabel != null)
                        {
                            if (orgLabel.IsChanged(label))
                            {
                                _log.LogInformation("변경된 레벨 데이터: {0}", label.Id);
                                orgLabel.LabelName = label.Name;
                                orgLabel.LabelDesc = label.Description;
                                updLabels.Add(orgLabel);
                            }
                            else
                            {
                                _log.LogInformation("동일한 레벨 데이터: {0}", label.Id);
                            }
                        }
                        else
                        {
                            _log.LogInformation("Not Found Label In Map: {0}", label.Id);
                        }
                    }
                    else
                    {
                        _log.LogInformation("새로운 레벨 데이터: {0}", label.Id);
                        TbAipLabel obj = new TbAipLabel()
                        {
                            LabelGuid = label.Id,
                            LabelName = label.Name,
                            LabelDesc = label.Description,
                            CreatedAt = DateTime.Now,
                            DeletedAt = null,
                            UseYn = true,
                        };
                        newLabels.Add(obj);
                    }
                }
                ContainerService.aipLableMap = keyMap;

                _log.LogInformation("AipFileApiService.DownloadAipFileLabels: UPDATE {0}, NEW {1}.", updLabels.Count, newLabels.Count);

                int updateCount = 0;
                foreach (TbAipLabel label in updLabels)
                {
                    FormattableString usql = $"UPDATE TB_AIP_LABEL SET LabelDesc = {label.LabelDesc}, LabelName = {label.LabelName} WHERE LabelId = {label.LabelId};";
                    updateCount += _dbContext.ExecuteSql(usql);
                }
                int insertCount = 0;
                foreach (TbAipLabel label in newLabels)
                {
                    FormattableString isql = $"INSERT INTO TB_AIP_LABEL(LabelGuid, LabelName, LabelDesc, UseYn) VALUES({label.LabelGuid}, {label.LabelName}, {label.LabelDesc}, 1);";
                    insertCount += _dbContext.ExecuteSql(isql);
                }
                _log.LogInformation("AipFileApiService.DownloadAipFileLabels: ..END. UPDATE {0}/{1}, NEW {2}/{3}.",
                    updLabels.Count, updateCount, newLabels.Count, insertCount);
            }
            finally
            {
                //transaction.Commit();
            }            
#if false
            //var newLables = new List<TbAipLabel>();
            foreach(AipLabel label in lavels)
            {
                try
                {
                    var val = _dbContext.AipLabels.AsNoTracking().Where(t => t.LabelGuid == label.Id).FirstOrDefault();
                    if (val != null)
                    {
                        var m = _dbContext.Find<TbAipLabel>(val.LabelId);
                        if (m != null)
                        {
                            _log.LogError("XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX: {0}, {1}", m.ToString(), label.Id);
                            m.LabelDesc = label.Description;
                            m.LabelName = label.Name;
                            _dbContext.AipLabels.Update(m);
                        }
                        else
                        {
                            val.LabelDesc = label.Description;
                            val.LabelName = label.Name;
                            _dbContext.AipLabels.Update(val);
                        }
                        _dbContext.SaveChanges();
                    }
                    else
                    {
                        TbAipLabel obj = new TbAipLabel()
                        {
                            LabelGuid = label.Id,
                            LabelName = label.Name,
                            LabelDesc = label.Description,
                            CreatedAt = DateTime.Now,
                            DeletedAt = null,
                            UseYn = true,
                        };

                        //newLables.Add(obj);
                        //await _unitOfWork.AipLabelRepository.AddAsync(obj);
                        //_unitOfWork.Complete();
                        //_log.LogError("{0}", obj.ToString());
                        _dbContext.Add<TbAipLabel>(obj);
                        _dbContext.SaveChanges();
                    }
                }
                catch(Exception ex)
                {
                    _log.LogError(ex.ToString(), ex);
                }
            }            
#endif
            //if (newLables.Count > 0)
            //{
            //    _dbContext.AipLabels.AddRange(newLables);
            //    _dbContext.SaveChanges();
            //}
        }

        private void DownloadAipFilePolicies()
        {
            //using var transaction = _dbContext.Database.BeginTransaction();
            try
            {
                _log.LogInformation("AipFileApiService.DownloadAipFilePolicies: Start.");

                Hashtable keyMap = new Hashtable();

                List<TbAipPolicy> newLabels = new List<TbAipPolicy>();
                List<TbAipPolicy> updLabels = new List<TbAipPolicy>();
                Hashtable labelMap = new Hashtable();

                List<AipLabel>? lavels = ContainerService.aipFileManager.ListSensitivityLabels();

                _log.LogInformation("AipFileApiService.DownloadAipFilePolicies.ListSensitivityLabels(): {} EA.", lavels?.Count);
                if (lavels == null)
                {
                    return;
                }

                FormattableString sql = $"SELECT t.PolicyId, t.CreatedAt, t.DeletedAt, t.PolicyDesc, t.PolicyGuid, t.PolicyName, t.UseYn FROM TB_AIP_POLICY AS t;";
                var result = _dbContext.SqlQuery<TbAipPolicy>(sql).ToList();
                if (result != null)
                {
                    foreach (TbAipPolicy policy in result)
                    {
                        labelMap.Add(policy.PolicyGuid, policy);
                    }
                }

                foreach (AipLabel label in lavels)
                {
                    keyMap.Add(label.Id, label);

                    if (labelMap.ContainsKey(label.Id))
                    {
                        TbAipPolicy? orgPolicy = labelMap[label.Id] as TbAipPolicy;
                        if (orgPolicy != null)
                        {
                            if (orgPolicy.IsChanged(label))
                            {
                                _log.LogInformation("변경된 정책 데이터: {0}", label.Id);
                                orgPolicy.PolicyName = label.Name;
                                orgPolicy.PolicyDesc = label.Description;
                                updLabels.Add(orgPolicy);
                            }
                            else
                            {
                                _log.LogInformation("동일한 정책 데이터: {0}", label.Id);
                            }
                        }
                        else
                        {
                            _log.LogInformation("Not Found Policy In Map: {0}", label.Id);
                        }
                    }
                    else
                    {
                        _log.LogInformation("새로운 정책 데이터: {0}", label.Id);
                        TbAipPolicy obj = new TbAipPolicy()
                        {
                            PolicyGuid = label.Id,
                            PolicyName = label.Name,
                            PolicyDesc = label.Description,
                            CreatedAt = DateTime.Now,
                            DeletedAt = null,
                            UseYn = true,
                        };
                        newLabels.Add(obj);
                    }
                }
                ContainerService.aipPolicyMap = keyMap;

                _log.LogInformation("AipFileApiService.DownloadAipFilePolicies: UPDATE {0}, NEW {1}.", updLabels.Count, newLabels.Count);

                int updateCount = 0;
                foreach (TbAipPolicy label in updLabels)
                {
                    FormattableString usql = $"UPDATE TB_AIP_POLICY SET PolicyDesc = {label.PolicyDesc}, PolicyName = {label.PolicyName} WHERE PolicyId = {label.PolicyId};";
                    updateCount += _dbContext.ExecuteSql(usql);
                }
                int insertCount = 0;
                foreach (TbAipPolicy label in newLabels)
                {
                    FormattableString isql = $"INSERT INTO TB_AIP_POLICY(PolicyGuid, PolicyName, PolicyDesc, UseYn) VALUES({label.PolicyGuid}, {label.PolicyName}, {label.PolicyDesc}, 1);";
                    insertCount += _dbContext.ExecuteSql(isql);
                }
                _log.LogInformation("AipFileApiService.DownloadAipFilePolicies: ..END. UPDATE {0}/{1}, NEW {2}/{3}.",
                    updLabels.Count, updateCount, newLabels.Count, insertCount);
            }
            finally
            {
                //transaction.Commit();
            }            
        }

        private void DownloadAipFileProtections()
        {
            //using var transaction = _dbContext.Database.BeginTransaction();
            try
            {
                _log.LogInformation("AipFileApiService.DownloadAipFileProtections: Start.");

                Hashtable keyMap = new Hashtable();

                List<TbAipProtection> newLabels = new List<TbAipProtection>();
                List<TbAipProtection> updLabels = new List<TbAipProtection>();
                Hashtable labelMap = new Hashtable();

                List<AipTemplate>? templates = ContainerService.aipFileManager.GetTemplates();

                _log.LogInformation("AipFileApiService.DownloadAipFileProtections.GetTemplates(): {} EA.", templates?.Count);
                if (templates == null)
                {
                    return;
                }

                FormattableString sql = $"SELECT t.ProtectionId, t.CreatedAt, t.DeletedAt, t.ProtectionDesc, t.ProtectionGuid, t.ProtectionName, t.UseYn FROM TB_AIP_PROTECTION AS t;";
                var result = _dbContext.SqlQuery<TbAipProtection>(sql).ToList();
                if (result != null)
                {
                    foreach (TbAipProtection policy in result)
                    {
                        labelMap.Add(policy.ProtectionGuid, policy);
                    }
                }

                foreach (AipTemplate template in templates)
                {
                    keyMap.Add(template.Id, template);

                    if (labelMap.ContainsKey(template.Id))
                    {
                        TbAipProtection? orgProtection = labelMap[template.Id] as TbAipProtection;
                        if (orgProtection != null)
                        {
                            if (orgProtection.IsChanged(template))
                            {
                                _log.LogInformation("변경된 정책 데이터: {0}", template.Id);
                                orgProtection.ProtectionName = template.Name;
                                orgProtection.ProtectionDesc = template.Description;
                                updLabels.Add(orgProtection);
                            }
                            else
                            {
                                _log.LogInformation("동일한 정책 데이터: {0}", template.Id);
                            }
                        }
                        else
                        {
                            _log.LogInformation("Not Found Policy In Map: {0}", template.Id);
                        }
                    }
                    else
                    {
                        _log.LogInformation("새로운 정책 데이터: {0}", template.Id);
                        TbAipProtection obj = new TbAipProtection()
                        {
                            ProtectionGuid = template.Id,
                            ProtectionName = template.Name,
                            ProtectionDesc = template.Description,
                            CreatedAt = DateTime.Now,
                            DeletedAt = null,
                            UseYn = true,
                        };
                        newLabels.Add(obj);
                    }
                }
                ContainerService.aipProtectionMap = keyMap;

                _log.LogInformation("AipFileApiService.DownloadAipFileProtections: UPDATE {0}, NEW {1}.", updLabels.Count, newLabels.Count);

                int updateCount = 0;
                foreach (TbAipProtection protection in updLabels)
                {
                    FormattableString usql = $"UPDATE TB_AIP_PROTECTION SET ProtectionDesc = {protection.ProtectionDesc}, ProtectionName = {protection.ProtectionName} WHERE ProtectionId = {protection.ProtectionId};";
                    updateCount += _dbContext.ExecuteSql(usql);
                }
                int insertCount = 0;
                foreach (TbAipProtection protection in newLabels)
                {
                    FormattableString isql = $"INSERT INTO TB_AIP_PROTECTION(ProtectionGuid, ProtectionName, ProtectionDesc, UseYn) VALUES({protection.ProtectionGuid}, {protection.ProtectionName}, {protection.ProtectionDesc}, 1);";
                    insertCount += _dbContext.ExecuteSql(isql);
                }
                _log.LogInformation("AipFileApiService.DownloadAipFileProtections: ..END. UPDATE {0}/{1}, NEW {2}/{3}.",
                    updLabels.Count, updateCount, newLabels.Count, insertCount);
            }
            finally
            {
                //transaction.Commit();
            }
        }

        public void DownloadAipFileInformations()
        {
            DownloadAipFileLabels();
            DownloadAipFilePolicies();
            DownloadAipFileProtections();
        }

        public void LoadLinkedApiKeys()
        {
            Hashtable keyMap = new Hashtable();

            string sql = @"SELECT A.Id AS ApiKeyId, 
                                  A.ApiKey, 
                                  A.policyLookupYn,
                                  A.fileInfoLookupYn,
                                  A.applyLabelYn,
                                  A.releaseLabelYn,
                                  A.encryptionFileYn,
                                  A.decryptionFileYn,
                                  A.ExpiredAt, 
                                  B.ServerId, B.ServerIpAddr, B.ServerDesc, 
                                  C.SystemId, C.SystemName  
                             FROM TB_LINKED_API_KEY A  
                            INNER JOIN TB_LINKED_SERVER B  
                               ON A.ServerId = B.ServerId 
                              AND A.UseYn = 1  
                              AND B.UseYn = 1
                            INNER JOIN TB_LINKED_SYSTEM C
                               ON B.SystemId  = C.SystemId 
                              AND C.UseYn = 1;";

            List<LinkedApiKey> result = (List<LinkedApiKey>)_dbContext.SqlQuery<LinkedApiKey>(FormattableStringFactory.Create(sql)).ToList();
            if (result != null)
            {
                foreach (LinkedApiKey key in result)
                {
                    LinkedApiKey? apiKey = keyMap[key.ApiKey] as LinkedApiKey;
                    if (apiKey == null) 
                    {
                        // SERVER 추가
                        key.serverMap = new Hashtable();
                        key.serverMap.Add(key.ServerIpAddr, key);

                        // API KEY 추가
                        keyMap.Add(key.ApiKey, key);
                    }
                    else
                    {
                        LinkedApiKey? server = apiKey.serverMap[key.ServerIpAddr] as LinkedApiKey;
                        if (server == null)
                        {
                            apiKey.serverMap.Add(key.ServerIpAddr, key);
                        }
                    }
                }
            }

            ContainerService.apiKeyMap = keyMap;
            foreach(string key in ContainerService.apiKeyMap.Keys)
            {
                LinkedApiKey? apiKey = ContainerService.apiKeyMap[key] as LinkedApiKey;
                if (apiKey != null)
                {
                    _log.LogError("       API KEY: {0}", apiKey.ApiKey);
                    foreach (string serverIp in apiKey.serverMap.Keys)
                    {
                        LinkedApiKey? server = apiKey.serverMap[serverIp] as LinkedApiKey;
                        if (server != null)
                        {
                            _log.LogError("API KEY SERVER: {0}, {1}", server.ApiKey, server.ServerIpAddr);
                        }
                    }
                }
            }
        }

        public void LoadLinkedDecryptKeys()
        {
            Hashtable keyMap = new Hashtable();

            string sql = @"SELECT A.Id AS DecryptKeyId, A.DecryptKey, A.ExpiredAt, 
                                  B.ServerId, B.ServerIpAddr, B.ServerDesc, 
                                  C.SystemId, C.SystemName  
                             FROM TB_LINKED_DECRYPT_KEY A  
                            INNER JOIN TB_LINKED_SERVER B  
                               ON A.ServerId = B.ServerId 
                              AND A.UseYn = 1  
                              AND B.UseYn = 1
                            INNER JOIN TB_LINKED_SYSTEM C
                               ON B.SystemId  = C.SystemId 
                              AND C.UseYn = 1;";

            List<LinkedDecryptApiKey> result = (List<LinkedDecryptApiKey>)_dbContext.SqlQuery<LinkedDecryptApiKey>(FormattableStringFactory.Create(sql)).ToList();
            if (result != null)
            {
                foreach (LinkedDecryptApiKey key in result)
                {
                    LinkedDecryptApiKey? apiKey = keyMap[key.DecryptKey] as LinkedDecryptApiKey;
                    if (apiKey == null)
                    {
                        // SERVER 추가
                        key.serverMap = new Hashtable();
                        key.serverMap.Add(key.ServerIpAddr, key);

                        // DESCRYPT KEY 추가
                        keyMap.Add(key.DecryptKey, key);
                    }
                    else
                    {
                        LinkedDecryptApiKey? server = apiKey.serverMap[key.ServerIpAddr] as LinkedDecryptApiKey;
                        if (server == null)
                        {
                            apiKey.serverMap.Add(key.ServerIpAddr, key);
                        }
                    }
                }
            }

            ContainerService.decryptKeyMap = keyMap;
            foreach (string key in ContainerService.decryptKeyMap.Keys)
            {
                LinkedDecryptApiKey? apiKey = ContainerService.apiKeyMap[key] as LinkedDecryptApiKey;
                if (apiKey != null)
                {
                    _log.LogError("       DESCRYPT KEY: {0}", apiKey.DecryptKey);
                    foreach (string serverIp in apiKey.serverMap.Keys)
                    {
                        LinkedDecryptApiKey? server = apiKey.serverMap[serverIp] as LinkedDecryptApiKey;
                        if (server != null)
                        {
                            _log.LogError("DESCRYPT KEY SERVER: {0}, {1}", server.DecryptKey, server.ServerIpAddr);
                        }
                    }
                }
            }
        }

    }

}