AipGateway/Aip.Service/Repositories/AipDbRepository.cs

using Aip.Service.Configurations;
using Aip.Service.Entities;
using Aip.Service.Infrastructures;
using Aip.Service.Models.Dto;
using Aip.Service.Models.Response;
using Aip.Service.Utils;
using Dapper;
using System.Data;
using System.Diagnostics;

namespace Aip.Service.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;


        if (!int.TryParse(configuration["AipPort"], out int aipPort))
        {
            aipPort = 7000;
        }
        _aipSetting.AipPort = aipPort;

        int coreCount = Environment.ProcessorCount;
        if (!int.TryParse(configuration["AipBindings"], out int aipBindings))
        {
            aipBindings = (coreCount * 2);
        }
        _aipSetting.AipBindings = aipBindings;


        // 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
                                {
                                    _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
                {
                    obj.LabelDesc,
                    obj.LabelName,
                    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
                {
                    obj.LabelGuid,
                    obj.LabelName,
                    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
                {
                    obj.PolicyDesc,
                    obj.PolicyName,
                    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
                {
                    obj.PolicyGuid,
                    obj.PolicyName,
                    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
                {
                    obj.ProtectionDesc,
                    obj.ProtectionName,
                    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
                {
                    obj.ProtectionGuid,
                    obj.ProtectionName,
                    obj.ProtectionDesc,
                },
                commandType: CommandType.Text
                );
        }
        return effectedRows;
    }

    public async Task<GeneralResponse> ReloadDatabase()
    {
        await Task.Run(() => Thread.Sleep(100));
        return new GeneralResponse
        {
            errorCode = 0,
            errorMessage = GlobalConstants.API_RESULT_SUCCESS,
            effectCount = 0,
        };
    }

    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();
    }
}