dbapi/EVCB_OCPP.DBAPI/Services/ServerMessageServices/SourceDbServerMessageService.cs

using Dapper;
using EVCB_OCPP.DBAPI.Services.DbService;
using EVCB_OCPP.Domain;
using EVCB_OCPP.Domain.ConnectionFactory;
using EVCB_OCPP.Domain.Models.MainDb;
using Microsoft.EntityFrameworkCore;

namespace EVCB_OCPP.DBAPI.Services.ServerMessageServices
{
    public class SourceDbServerMessageService : IServerMessageService
    {
        private readonly IMainDbService mainDbService;
        private readonly ISqlConnectionFactory<MainDBContext> sqlConnectionFactory;

        public SourceDbServerMessageService(
            IMainDbService mainDbService, 
            ISqlConnectionFactory<MainDBContext> sqlConnectionFactory)
        {
            this.mainDbService = mainDbService;
            this.sqlConnectionFactory = sqlConnectionFactory;
        }

        public async Task<string> AddServerMessage(
            string ChargeBoxId,
            string OutAction,
            string OutRequest,
            string CreatedBy, 
            DateTime? CreatedOn = null, 
            string SerialNo = "", 
            string InMessage = "", 
            CancellationToken token = default)
        {
            if (string.IsNullOrEmpty(SerialNo))
            {
                SerialNo = Guid.NewGuid().ToString();
            }
            var _CreatedOn = CreatedOn ?? DateTime.UtcNow;

            string _OutRequest = OutRequest is not null ? OutRequest : "";

            var data = new ServerMessage()
            {
                ChargeBoxId = ChargeBoxId,
                CreatedBy = CreatedBy,
                CreatedOn = _CreatedOn,
                OutAction = OutAction,
                OutRequest = _OutRequest,
                SerialNo = SerialNo,
                InMessage = InMessage
            };

            await StoreIntoRTTable(data);

            return SerialNo;
        }

        public async Task<List<ServerMessage>> GetNeedSendToClientServerMessages()
        {
            var allServerMessage = await GetServerMessages();
            DateTime startDt = DateTime.UtcNow.AddSeconds(-30);

            var seperatedServerMessage = allServerMessage.GroupBy(x => x.CreatedOn < startDt).ToDictionary(x => x.Key, x => x?.ToList());
            var deprecatedServerMessage = seperatedServerMessage.GetValueOrDefault(true, null);

            if (deprecatedServerMessage is not null)
            {

                foreach (var servermessage in deprecatedServerMessage)
                {
                    _ = RemoveFromRTTable(servermessage.Id);
                    _ = AddServerMessageToDbAsync(servermessage);
                }
            }

            return seperatedServerMessage.GetValueOrDefault(false, new List<ServerMessage>())!;
        }

        public Task<List<ServerMessage>> GetServerMessages()
        {
            return GetServerMessagesFromRTTable();
        }

        public Task SaveCompletedMessageToDb()
        {
            return Task.CompletedTask;
        }

        public async Task<bool> SetServerMessageResponseReceived(int id, string InMessage = "", DateTime ReceivedOn = default)
        {
            DateTime _ReceivedOn = ReceivedOn == default ? DateTime.UtcNow : ReceivedOn;

            ServerMessage msg = await GetServerMessageFromRTTable(id);
            if (msg is null)
            {
                return false;
            }
            await RemoveFromRTTable(id);

            msg.Id = 0;
            msg.InMessage = InMessage;
            msg.ReceivedOn = _ReceivedOn;

            var sn = await mainDbService.AddServerMessage(msg);
            return string.IsNullOrEmpty(sn);
        }

        public Task<bool> SetServerMessageServerHandling(int id, DateTime UpdatedOn = default)
        {
            DateTime _UpdatedOn = UpdatedOn == default ? DateTime.UtcNow : UpdatedOn;
            return SetServerMessageServerHandlingInRTTable(id, _UpdatedOn);
        }

        private async Task<int> AddServerMessageToDbAsync(ServerMessage message)
        {
            var memMessageId = message.Id;
            var addServerMessageResult = await mainDbService.AddServerMessage(message);
            if (!string.IsNullOrEmpty(addServerMessageResult))
            {
                return memMessageId;
            }
            return -1;
        }

        private async Task<bool> StoreIntoRTTable(ServerMessage data)
        {
            var cmd = """
                INSERT INTO RTServerMessage ([SerialNo],[OutAction],[OutRequest],[InMessage],[CreatedOn],[CreatedBy],[ReceivedOn],[ChargeBoxId],[UpdatedOn])
                VALUES (@SerialNo,@OutAction,@OutRequest,@InMessage,@CreatedOn,@CreatedBy,@ReceivedOn,@ChargeBoxId,@UpdatedOn)
                """;

            var pams = new DynamicParameters();
            pams.Add("SerialNo", data.SerialNo, System.Data.DbType.String, size: 36);
            pams.Add("OutAction", data.OutAction, System.Data.DbType.String, size:30);
            pams.Add("OutRequest", data.OutRequest, System.Data.DbType.String);
            pams.Add("InMessage", data.InMessage, System.Data.DbType.String);
            pams.Add("CreatedOn", data.CreatedOn, System.Data.DbType.DateTime);
            pams.Add("CreatedBy", data.CreatedBy, System.Data.DbType.String,size:36);
            pams.Add("ReceivedOn", data.ReceivedOn, System.Data.DbType.DateTime);
            pams.Add("ChargeBoxId", data.ChargeBoxId, System.Data.DbType.String, size:50);
            pams.Add("UpdatedOn", data.UpdatedOn, System.Data.DbType.DateTime, size:50);

            using var con = await sqlConnectionFactory.CreateAsync();
            var cnts = await con.ExecuteAsync(cmd, pams);
            return cnts > 0;
        }

        private async Task<List<ServerMessage>> GetServerMessagesFromRTTable()
        {
            var cmd = """
                SELECT * FROM RTServerMessage
                """;
            using var con = await sqlConnectionFactory.CreateAsync();
            var datas = await con.QueryAsync<ServerMessage>(cmd);
            return datas?.ToList();
        }

        private async Task<ServerMessage> GetServerMessageFromRTTable(int id)
        {
            var cmd = """
                SELECT * 
                FROM RTServerMessage
                WHERE [Id] = @Id
                """;

            var pams = new DynamicParameters();
            pams.Add("Id", id, System.Data.DbType.Int32);

            using var con = await sqlConnectionFactory.CreateAsync();
            var data = await con.QueryFirstOrDefaultAsync<ServerMessage>(cmd, pams);
            return data;
        }

        private async Task<bool> RemoveFromRTTable(int id)
        {
            var cmd = """
                DELETE FROM RTServerMessage
                WHERE [Id] = @Id
                """;

            var pams = new DynamicParameters();
            pams.Add("Id", id, System.Data.DbType.Int32);

            using var con = await sqlConnectionFactory.CreateAsync();
            var cnts = await con.ExecuteAsync(cmd, pams);
            return cnts > 0;
        }

        private async Task<bool> SetServerMessageServerHandlingInRTTable(int id, DateTime updatedOn)
        {
            var cmd = """
                UPDATE RTServerMessage
                SET [UpdatedOn] = @UpdatedOn
                WHERE [Id] = @Id
                """;

            var pams = new DynamicParameters();
            pams.Add("Id", id, System.Data.DbType.Int32);
            pams.Add("UpdatedOn", updatedOn, System.Data.DbType.DateTime);

            using var con = await sqlConnectionFactory.CreateAsync();
            var cnts = await con.ExecuteAsync(cmd, pams);
            return cnts > 0;
        }
    }
}