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

using Dapper;
using EVCB_OCPP.DBAPI.ConnectionFactory;
using EVCB_OCPP.DBAPI.Models.DBContext;
using EVCB_OCPP.DBAPI.Services.DbService;
using EVCB_OCPP.Domain.Models.MainDb;
using Microsoft.Data.Sqlite;
using Newtonsoft.Json;
using System.Data;

namespace EVCB_OCPP.DBAPI.Services.ServerMessageServices;

public class MemDbServerMessageService : IServerMessageService
{
    public MemDbServerMessageService(
        ISqliteConnectionConnectionFactory<MemDBContext> memDbConnectionFactory,
        IMainDbService mainDbService,
        ILogger<MemDbServerMessageService> logger)
    {
        this.memDbConnectionFactory = memDbConnectionFactory;
        this.mainDbService = mainDbService;
        this.logger = logger;
    }
    private readonly ISqliteConnectionConnectionFactory<MemDBContext> memDbConnectionFactory;
    private readonly IMainDbService mainDbService;
    private readonly ILogger<MemDbServerMessageService> logger;

    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 AddServerMessage(data, token: token);
        return SerialNo;
    }

    public async Task<List<ServerMessage>> GetNeedSendToClientServerMessages()
    {
        DateTime dt = new DateTime(1991, 1, 1);
        DateTime dateTimeNow = DateTime.UtcNow;
        DateTime startDt = dateTimeNow.AddSeconds(-30);

        var parms = new DynamicParameters();
        parms.Add("ReceivedOn", dt);
        parms.Add("UpdatedOn", dt);
        parms.Add("CreatedOnStart", startDt);
        parms.Add("CreatedOnEnd", dateTimeNow);

        var cmd = """
            SELECT * FROM ServerMessage
            WHERE
            ReceivedOn = @ReceivedOn and
            UpdatedOn = @UpdatedOn and
            CreatedOn >= @CreatedOnStart and
            CreatedOn <= @CreatedOnEnd
            """;

        using var connection = await memDbConnectionFactory.CreateAsync();
        var datas = await connection.QueryAsync<ServerMessage>(cmd, parms);
        return datas.ToList();
    }

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

        var parms = new DynamicParameters();
        parms.Add("Id", id);
        parms.Add("InMessage", InMessage);
        parms.Add("ReceivedOn", _ReceivedOn);

        var cmd = """
            UPDATE ServerMessage
            SET InMessage = @InMessage , ReceivedOn = @ReceivedOn
            WHERE Id = @Id
            """;

        using var connection = await memDbConnectionFactory.CreateAsync();
        var cnts = await connection.ExecuteAsync(cmd, parms);
        var success = cnts > 0;
        if (!success)
        {
            logger.LogError("SetServerMessageResponseReceived failed {msg}", InMessage);
        }
        return success;
    }

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

        var parms = new DynamicParameters();
        parms.Add("Id", id);
        parms.Add("UpdatedOn", _UpdatedOn);

        var cmd = """
            UPDATE ServerMessage
            SET UpdatedOn = @UpdatedOn
            WHERE Id = @Id
            """;

        using var connection = await memDbConnectionFactory.CreateAsync();
        var cnts = await connection.ExecuteAsync(cmd, parms);
        var success = cnts > 0;
        if (!success)
        {
            logger.LogError("SetServerMessageServerHandling failed {msgid}", id);
        }
        return success;
    }

    public async Task<List<ServerMessage>> GetServerMessages()
    {
        string cmd = """
            SELECT * FROM ServerMessage
            """;
        using var connection = await memDbConnectionFactory.CreateAsync();
        var datas = await connection.QueryAsync<ServerMessage>(cmd);
        return datas.ToList();
    }

    public static async Task IinitMemDbAsync(SqliteConnection connection)
    {
        var createTableResult = await connection.ExecuteAsync("""
            CREATE TABLE IF NOT EXISTS ServerMessage (
            Id INTEGER PRIMARY KEY NOT NULL,
            SerialNo VARCHAR(36),
            OutAction VARCHAR(30),
            OutRequest TEXT,
            InMessage TEXT,
            CreatedOn DATE TIME NOT NULL,
            CreatedBy VARCHAR(36),
            ReceivedOn DATE TIME NOT NULL,
            ChargeBoxId VARCHAR(50),
            UpdatedOn DATE TIME NOT NULL
            )
            """);
    }

    public async Task SaveCompletedMessageToDb()
    {
        List<ServerMessage> completedMessages = await GetCompletedServerMessageFromMemDb();
        List<Task<int>> addServerMessageTasks = new List<Task<int>>();
        foreach (var msg in completedMessages)
        {
            addServerMessageTasks.Add(AsyncAddServerMessage(msg));
        }
        await Task.WhenAll(addServerMessageTasks);
        var addedServerMessageId = addServerMessageTasks.Select(x => x.Result).Where(x => x > 0).ToList();
        var removeResult = await RmoveSavedServerMessageFromMemDb(addedServerMessageId);
    }

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

    private Task<string> AddServerMessage(ServerMessage message, CancellationToken token = default)
    {
        return AddServerMessageDapper(message);
    }

    private async Task<string> AddServerMessageDapper(ServerMessage message)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@SerialNo", message.SerialNo, DbType.String, ParameterDirection.Input, 36);
        parameters.Add("@OutAction", message.OutAction, DbType.String, ParameterDirection.Input, 30);
        parameters.Add("@OutRequest", message.OutRequest, DbType.String, ParameterDirection.Input);
        parameters.Add("@InMessage", message.InMessage, DbType.String, ParameterDirection.Input);
        parameters.Add("@CreatedOn", message.CreatedOn, DbType.DateTime, ParameterDirection.Input);
        parameters.Add("@CreatedBy", message.CreatedBy, DbType.String, ParameterDirection.Input, 36);
        parameters.Add("@ReceivedOn", message.ReceivedOn, DbType.DateTime, ParameterDirection.Input);
        parameters.Add("@ChargeBoxId", message.ChargeBoxId, DbType.String, ParameterDirection.Input, 30);
        parameters.Add("@UpdatedOn", message.UpdatedOn, DbType.DateTime, ParameterDirection.Input);

        using var conn = await memDbConnectionFactory.CreateAsync();
        var resultCnt = await conn.ExecuteAsync("""
        INSERT INTO ServerMessage
        (SerialNo, OutAction, OutRequest, InMessage, CreatedOn, CreatedBy, ReceivedOn, ChargeBoxId, UpdatedOn)
        VALUES (@SerialNo, @OutAction, @OutRequest, @InMessage, @CreatedOn, @CreatedBy, @ReceivedOn, @ChargeBoxId, @UpdatedOn)
        """, parameters);
        if (resultCnt != 1)
        {
            logger.LogError("AddServerMessageDapper failed msg:{msg}", JsonConvert.SerializeObject(message));
            return string.Empty;
        }
        return message.SerialNo;
    }

    private async Task<List<ServerMessage>> GetCompletedServerMessageFromMemDb()
    {
        DateTime dateTimeNow = DateTime.UtcNow;
        DateTime startDt = dateTimeNow.AddSeconds(-30);
        DateTime dt = new DateTime(1991, 1, 1);

        var cmd = """
            SELECT * FROM ServerMessage
            WHERE ReceivedOn != @NullDateTime or CreatedOn < @CreatedOnStart
            """;

        var parm = new DynamicParameters();
        parm.Add("NullDateTime", dt);
        parm.Add("CreatedOnStart", startDt);

        using var connection = await memDbConnectionFactory.CreateAsync();
        var datas = await connection.QueryAsync<ServerMessage>(cmd, parm);
        return datas.ToList();
    }

    private async Task<bool> RmoveSavedServerMessageFromMemDb(List<int> ids)
    {
        var cmd = """
            DELETE FROM ServerMessage
            WHERE Id in @Ids
            """;

        var parm = new DynamicParameters();
        parm.Add("Ids", ids);

        using var connection = await memDbConnectionFactory.CreateAsync();
        var datasCnt = await connection.ExecuteAsync(cmd, parm);
        var success = datasCnt == ids.Count;
        if (!success)
        {
            logger.LogError("RmoveSavedServerMessageFromMemDb failed {ids}", JsonConvert.SerializeObject(ids));
        }
        return datasCnt == ids.Count;
    }
}