AipGateway/AipGateway.API/Repositories/AipDbRepository.cs

using AipDatabase.API.Infrastructures;
using AipGateway.API.Application;
using AipGateway.API.Application.Configurations;
using AipGateway.API.Domain.Entities;
using AipGateway.API.Domain.IRepositories.IGenericRepositories;
using AipGateway.API.Domain.Models.Dto;
using AipGateway.API.Domain.Models.Response;
using AipGateway.API.Utils;
using Dapper;
using System.Data;
using System.Diagnostics;
using System.Reflection.Metadata;
using System.Security.Cryptography;

namespace AipGateway.API.Repositories
{
    public class AipDbRepository : IAipDbRepository
    {
        private readonly ILogger<AipDbRepository> _log;
        private readonly IDatabaseFactory _dbContext;
        private readonly AipSettings _aipSetting = new AipSettings();

        public AipDbRepository(ILogger<AipDbRepository> log, IConfiguration configuration, IDatabaseFactory dbContext)
        {
            _log = log;
            _dbContext = dbContext;

            LoadApplicationConfig(configuration);
        }

        private void LoadApplicationConfig(IConfiguration configuration)
        {
            if (!int.TryParse(configuration["Id"], out int aipServerId))
            {
                aipServerId = 1;
            }
            _aipSetting.AipServerId = aipServerId;
            if (!int.TryParse(configuration["Port"], out int port))
            {
                port = 9871;
            }
            _aipSetting.Port = port;

            // appsettings.json 설정정보 로딩
            string? appName = configuration["Name"];
            if (appName == null)
            {
                appName = "AIP Gateway API";
            }
            string? version = configuration["Version"];
            if (version == null)
            {
                version = "1.0.0";
            }
            string? mipData = configuration["MipDataPath"];
            if (mipData == null)
            {
                mipData = "App_Data\\mip_data";
            }
            string? sourceFilePath = configuration["SourceFilePath"];
            if (sourceFilePath == null)
            {
                sourceFilePath = "c:\\Data\\Source\\";
            }
            string? targetFilePath = configuration["TargetFilePath"];
            if (targetFilePath == null)
            {
                targetFilePath = "c:\\Data\\Target\\";
            }
            _aipSetting.SetValue("AppName", appName);
            _aipSetting.SetValue("AppVersion", version);
            _aipSetting.SetValue("MipData", mipData);
            _aipSetting.SetValue("SourceFileDir", sourceFilePath);
            _aipSetting.SetValue("TargetFileDir", targetFilePath);
        }

        public async Task<AipSettings> LoadAipConfig()
        {
            var start = Stopwatch.GetTimestamp();
            _log.LogInformation("Loading System Configurations from database.");

            using var connection = _dbContext.GetDbConnection();

            string sql = "SELECT * FROM TB_AIP_CONFIG WHERE AipServerId = @AipServerId";

            // 시스템 기본 설정 정보 조회
            IEnumerable<TbAipConfig> results = await connection.QueryAsync<TbAipConfig>(
                                    sql,
                                    new
                                    {
                                        AipServerId = 0,
                                    },
                                    commandType: CommandType.Text
                                    );
            foreach (TbAipConfig config in results)
            {
                _log.LogInformation("{0}, {1}, {2}", config.Id, config.ConfigKey, config.ConfigValue);
                _aipSetting.SetValue(config.ConfigKey, config.ConfigValue);
            }
            // 어플리케이션 설정 정보 조회
            results = await connection.QueryAsync<TbAipConfig>(
                                    sql,
                                    new
                                    {
                                        AipServerId = _aipSetting.AipServerId,
                                    },
                                    commandType: CommandType.Text
                                    );
            foreach (TbAipConfig config in results)
            {
               _log.LogInformation("{0}, {1}, {2}", config.Id, config.ConfigKey, config.ConfigValue);
                _aipSetting.SetValue(config.ConfigKey, config.ConfigValue);
            }

            _log.LogInformation("Loading System Configurations from database. Completed {0} ms.", TimeUtils.GetElapsedMilliseconds(start));
            return _aipSetting;
        }

        public async Task<List<LinkedApiKey>> LoadLinkedApiKeys()
        {
            using var connection = _dbContext.GetDbConnection();
            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";
            IEnumerable<LinkedApiKey> results = await connection.QueryAsync<LinkedApiKey>(sql, commandType: CommandType.Text);
            return results.ToList();
        }
        public async Task<List<LinkedDecryptKey>> LoadLinkedDecryptKeys()
        {
            using var connection = _dbContext.GetDbConnection();
            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";
            IEnumerable<LinkedDecryptKey> results = await connection.QueryAsync<LinkedDecryptKey>(sql, commandType: CommandType.Text);
            return results.ToList();
        }


        public async Task<List<TbAipLabel>> LoadAipLabels()
        {
            using var connection = _dbContext.GetDbConnection();
            string sql = "SELECT t.LabelId, t.CreatedAt, t.DeletedAt, t.LabelDesc, t.LabelGuid, t.LabelName, t.UseYn FROM TB_AIP_LABEL AS t";
            IEnumerable<TbAipLabel> results = await connection.QueryAsync<TbAipLabel>(sql,commandType: CommandType.Text);
            return results.ToList();
        }
        public async Task<int> UpdateAipLables(List<TbAipLabel> updLabels)
        {
            int effectedRows = 0;
            using var connection = _dbContext.GetDbConnection();
            string sql = "UPDATE TB_AIP_LABEL SET LabelDesc = @LabelDesc, LabelName = @LabelName WHERE LabelId = @LabelId";
            foreach (TbAipLabel obj in updLabels)
            {
                effectedRows += await connection.ExecuteAsync(
                    sql,
                    new
                    {
                        LabelDesc = obj.LabelDesc,
                        LabelName = obj.LabelName,
                        LabelId = obj.LabelId,
                    },
                    commandType: CommandType.Text
                    );
            }
            return effectedRows;
        }
        public async Task<int> InsertAipLables(List<TbAipLabel> newLabels)
        {
            int effectedRows = 0;
            using var connection = _dbContext.GetDbConnection();
            string sql = "INSERT INTO TB_AIP_LABEL(LabelGuid, LabelName, LabelDesc, UseYn) VALUES(@LabelGuid, @LabelName, @LabelDesc, 1)";
            foreach (TbAipLabel obj in newLabels)
            {
                effectedRows += await connection.ExecuteAsync(
                    sql,
                    new
                    {
                        LabelGuid = obj.LabelGuid,
                        LabelName = obj.LabelName,
                        LabelDesc = obj.LabelDesc,
                    },
                    commandType: CommandType.Text
                    );
            }
            return effectedRows;
        }

        public async Task<List<TbAipPolicy>> LoadAipPolicies()
        {
            using var connection = _dbContext.GetDbConnection();
            string sql = "SELECT t.PolicyId, t.CreatedAt, t.DeletedAt, t.PolicyDesc, t.PolicyGuid, t.PolicyName, t.UseYn FROM TB_AIP_POLICY AS t";
            IEnumerable<TbAipPolicy> results = await connection.QueryAsync<TbAipPolicy>(sql, commandType: CommandType.Text);
            return results.ToList();
        }
        public async Task<int> UpdateAipPolicies(List<TbAipPolicy> updLabels)
        {
            int effectedRows = 0;
            using var connection = _dbContext.GetDbConnection();
            string sql = "UPDATE TB_AIP_POLICY SET PolicyDesc = @PolicyDesc, PolicyName = @PolicyName WHERE PolicyId = @PolicyId";
            foreach (TbAipPolicy obj in updLabels)
            {
                effectedRows += await connection.ExecuteAsync(
                    sql,
                    new
                    {
                        PolicyDesc = obj.PolicyDesc,
                        PolicyName = obj.PolicyName,
                        PolicyId = obj.PolicyId,
                    },
                    commandType: CommandType.Text
                    );
            }
            return effectedRows;
        }
        public async Task<int> InsertAipPolicies(List<TbAipPolicy> newLabels)
        {
            int effectedRows = 0;
            using var connection = _dbContext.GetDbConnection();
            string sql = "INSERT INTO TB_AIP_POLICY(PolicyGuid, PolicyName, PolicyDesc, UseYn) VALUES(@PolicyGuid, @PolicyName, @PolicyDesc, 1)";
            foreach (TbAipPolicy obj in newLabels)
            {
                effectedRows += await connection.ExecuteAsync(
                    sql,
                    new
                    {
                        PolicyGuid = obj.PolicyGuid,
                        PolicyName = obj.PolicyName,
                        PolicyDesc = obj.PolicyDesc,
                    },
                    commandType: CommandType.Text
                    );
            }
            return effectedRows;
        }

        public async Task<List<TbAipProtection>> LoadAipTemplates()
        {
            using var connection = _dbContext.GetDbConnection();
            string sql = "SELECT t.ProtectionId, t.CreatedAt, t.DeletedAt, t.ProtectionDesc, t.ProtectionGuid, t.ProtectionName, t.UseYn FROM TB_AIP_PROTECTION AS t";
            IEnumerable<TbAipProtection> results = await connection.QueryAsync<TbAipProtection>(sql, commandType: CommandType.Text);
            return results.ToList();
        }
        public async Task<int> UpdateAipTemplates(List<TbAipProtection> updLabels)
        {
            int effectedRows = 0;
            using var connection = _dbContext.GetDbConnection();
            string sql = "UPDATE TB_AIP_PROTECTION SET ProtectionDesc = @ProtectionDesc, ProtectionName = @ProtectionName WHERE ProtectionId = @ProtectionId";
            foreach (TbAipProtection obj in updLabels)
            {
                effectedRows += await connection.ExecuteAsync(
                    sql,
                    new
                    {
                        ProtectionDesc = obj.ProtectionDesc,
                        ProtectionName = obj.ProtectionName,
                        ProtectionId = obj.ProtectionId,
                    },
                    commandType: CommandType.Text
                    );
            }
            return effectedRows;
        }
        public async Task<int> InsertAipTemplates(List<TbAipProtection> newLabels)
        {
            int effectedRows = 0;
            using var connection = _dbContext.GetDbConnection();
            string sql = "INSERT INTO TB_AIP_PROTECTION(ProtectionGuid, ProtectionName, ProtectionDesc, UseYn) VALUES(@ProtectionGuid, @ProtectionName, @ProtectionDesc, 1)";
            foreach (TbAipProtection obj in newLabels)
            {
                effectedRows += await connection.ExecuteAsync(
                    sql,
                    new
                    {
                        ProtectionGuid = obj.ProtectionGuid,
                        ProtectionName = obj.ProtectionName,
                        ProtectionDesc = obj.ProtectionDesc,
                    },
                    commandType: CommandType.Text
                    );
            }
            return effectedRows;
        }

        public async Task<GeneralResponse> ReloadDatabase()
        {
#if false
            try
            {
                var task = Task.Run(() =>
                {
                    int result = _apiAuthService.LoadAuthInformation();
                    return result;
                });
                int result = await task;
                return new GeneralResponse
                {
                    errorCode = 0,
                    errorMessage = GlobalConstants.API_RESULT_SUCCESS,
                    effectCount = result,
                };
            }
            catch (Exception)
            {
                throw;
            }
#endif
            return null;
        }

        public async Task<List<LinkedSystemDto>> GetLinkedSystems()
        {
            using var connection = _dbContext.GetDbConnection();
            string sql = "SELECT t.SystemId, t.SystemName, t.CreatedAt, t.UseYn, t.SystemDesc, t.DeletedAt FROM TB_LINKED_SYSTEM AS t";
            IEnumerable<LinkedSystemDto> results = await connection.QueryAsync<LinkedSystemDto>(sql, commandType: CommandType.Text);
            return results.ToList();
        }

        public async Task<List<LinkedServerDto>> GetLinkedServers()
        {
            using var connection = _dbContext.GetDbConnection();
            string sql = "SELECT t.ServerId, t.SystemId, t.ServerIpAddr, t.ServerDesc, t.CreatedAt, t.UseYn, t.DeletedAt FROM TB_LINKED_SERVER AS t";
            IEnumerable<LinkedServerDto> results = await connection.QueryAsync<LinkedServerDto>(sql, commandType: CommandType.Text);
            return results.ToList();
        }

        public async Task<List<LinkedApiKeyDto>> GetLinkedApiKeys()
        {
            using var connection = _dbContext.GetDbConnection();
            string sql = "SELECT t.Id, t.ServerId, t.ApiKey, t.ExpiredAt, t.CreatedAt, t.UseYn, t.DeletedAt FROM TB_LINKED_API_KEY AS t";
            IEnumerable<LinkedApiKeyDto> results = await connection.QueryAsync<LinkedApiKeyDto>(sql, commandType: CommandType.Text);
            return results.ToList();
        }

        public async Task<List<LinkedDecryptKeyDto>> GetLinkedDecryptKeys()
        {
            using var connection = _dbContext.GetDbConnection();
            string sql = "SELECT t.Id, t.ServerId, t.DecryptKey, t.ExpiredAt, t.CreatedAt, t.UseYn, t.DeletedAt FROM TB_LINKED_DECRYPT_KEY AS t";
            IEnumerable<LinkedDecryptKeyDto> results = await connection.QueryAsync<LinkedDecryptKeyDto>(sql, commandType: CommandType.Text);
            return results.ToList();
        }
    }
}