EVCB_OCPP.OCPPServer.16J/EVCB_OCPP.WSServer/Service/DbService/MainDbService.cs

using Dapper;
using EVCB_OCPP.Domain;
using EVCB_OCPP.Domain.ConnectionFactory;
using EVCB_OCPP.Domain.Models.MainDb;
using EVCB_OCPP.Packet.Features;
using EVCB_OCPP.Packet.Messages.Core;
using EVCB_OCPP.Packet.Messages.FirmwareManagement;
using EVCB_OCPP.Packet.Messages.LocalAuthListManagement;
using EVCB_OCPP.Packet.Messages.RemoteTrigger;
using EVCB_OCPP.Packet.Messages.Reservation;
using EVCB_OCPP.Packet.Messages.Security;
using EVCB_OCPP.Packet.Messages.SmartCharging;
using EVCB_OCPP.WSServer.Helper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using OCPPPackage.Profiles;
using System.Data;

namespace EVCB_OCPP.WSServer.Service.DbService;

public interface IMainDbService
{
    Task<string> GetMachineAuthorizationKey(string ChargeBoxId, CancellationToken token = default);
    Task<string> GetMachineConfiguration(string ChargeBoxId, string configName, CancellationToken token = default);
    Task<string> GetMachineHeartbeatInterval(string ChargeBoxId);
    Task<MachineAndCustomerInfo> GetMachineIdAndCustomerInfo(string ChargeBoxId, CancellationToken token = default);
    Task<string> GetMachineSecurityProfile(string ChargeBoxId, CancellationToken token = default);
    Task UpdateMachineBasicInfo(string ChargeBoxId, Machine machine);
    Task AddOCMF(Ocmf oCMF);
    ValueTask<ConnectorStatus> GetConnectorStatus(string ChargeBoxId, int ConnectorId);
    Task UpdateConnectorStatus(string Id, ConnectorStatus connectorStatus);
    ValueTask AddConnectorStatus(string ChargeBoxId, byte ConnectorId, DateTime CreatedOn, int Status,
        int ChargePointErrorCodeId, string ErrorInfo, string VendorId, string VendorErrorCode);
    Task<string> AddServerMessage(ServerMessage message);
    Task<string> AddServerMessage(string ChargeBoxId, string OutAction, object OutRequest, string CreatedBy = "", DateTime? CreatedOn = null, string SerialNo = "", string InMessage = "");
    ValueTask AddMachineError(byte ConnectorId, DateTime CreatedOn, int Status, string ChargeBoxId, int ErrorCodeId, string ErrorInfo, int PreStatus, string VendorErrorCode, string VendorId);
	ValueTask FillupFinishedTimetoMachineError(string ChargeBoxId, byte ConnectorId, DateTime FinishedOn);

	ValueTask<Customer> GetCustomer(string id, CancellationToken token = default);
    ValueTask<Customer> GetCustomer(Guid id, CancellationToken token = default);
    Task<Guid> GetCustomerIdByChargeBoxId(string chargeboxId);
    Task<int?> TryGetDuplicatedTransactionId(string chargeBoxId, Guid customerId, int connectorId, DateTime timestamp);
    Task<int> AddNewTransactionRecord(TransactionRecord newTransaction);
    Task<TransactionRecord> GetTransactionForStopTransaction(int transactionId, string chargeBoxId);
    Task UpdateTransaction(int transactionId, int meterStop, DateTime stopTime, int stopReasonId, string stopReason, string stopIdTag, string receipt, int cost);
    Task<bool> UpdateHeartBeats(IEnumerable<Machine> heartBeatsData);
    Task<bool> UpdateHeartBeats(List<string> machineIds);
    Task UpdateTransactionSOC(int id, string startsoc, string stopsoc);
    Task UpdateMachineConnectionType(string chargeBoxId, int v);
    Task<string> GetMachineConnectorType(string chargeBoxId, CancellationToken token = default);
    Task SetMachineConnectionType(string chargeBoxId, int connectionType, CancellationToken token = default);
    Task UpdateServerMessageUpdateTime(int table_id);
    Task AddMachineConfiguration(string chargeBoxId, string key, string value, bool isReadOnly, bool isExist = true);
    Task UpdateMachineConfiguration(string chargeBoxId, string item, string empty, bool isReadOnly, bool isExists = true);
    Task<List<MachineConfigurations>> GetMachineConfiguration(string chargeBoxId);
    Task<object> TryGetResponseFromDb(string msgId, CancellationToken token = default);

    Task InsertOrUpdateTransactionPeriodEnergy(int txId, Dictionary<string, decimal> periodEnergy);

    Task<Dictionary<string, decimal>> GetTransactionPeriodEnergy(int transactionId);

    Task<bool> UpdateCustomId(string customId, string chargeboxId);

}

public class MainDbService : IMainDbService
{
    public MainDbService(
        IDbContextFactory<MainDBContext> contextFactory,
        ISqlConnectionFactory<MainDBContext> sqlConnectionFactory,
        IMemoryCache memoryCache,
        IConfiguration configuration,
        ILoggerFactory loggerFactory,
        ILogger<MainDbService> logger)
    {
        this.contextFactory = contextFactory;
        this.sqlConnectionFactory = sqlConnectionFactory;
        this.memoryCache = memoryCache;
        this.loggerFactory = loggerFactory;
        this.logger = logger;
        var startupLimit = GetStartupLimit(configuration);
        //this.connectionString = configuration.GetConnectionString("MainDBContext");
        startupSemaphore = new(startupLimit);

        var opLimit = GetOpLimit(configuration);
        opSemaphore = new SemaphoreSlim(opLimit);

        InitUpdateConnectorStatusHandler();
        InitUpdateMachineBasicInfoHandler();
        InitAddServerMessageHandler();
        InitUpdateServerMessageUpdateOnHandler();
        InitGetMachineConfigurationHandler();
        InitUpdateErrorFinishedOnHandler();
    }

    private const string CustomerMemCacheKeyFromat = "Customer_{0}";
    //private const string ChargeBoxConnectorIdMemCacheKeyFromat = "Connector_{0}{1}";

    private readonly IDbContextFactory<MainDBContext> contextFactory;
    private readonly ISqlConnectionFactory<MainDBContext> sqlConnectionFactory;
    private readonly IMemoryCache memoryCache;
    private readonly ILoggerFactory loggerFactory;
    private readonly ILogger<MainDbService> logger;

    //private string connectionString;
    private readonly QueueSemaphore startupSemaphore;
    private readonly SemaphoreSlim opSemaphore;
    private GroupHandler<StatusNotificationParam> statusNotificationHandler;
    private GroupHandler<UpdateMachineBasicInfoParam> updateMachineBasicInfoHandler;
    private GroupHandler<ServerMessage, string> addServerMessageHandler;
    private GroupHandler<int> updateServerMessageUpdateOnHandler;
    private GroupHandler<string, List<MachineConfigurations>> getMachineConfigurationHandler;
    private GroupHandler<UpdateErrofFinishedOnParam> updateErrorFinishedOnHandler;

    public async Task<MachineAndCustomerInfo> GetMachineIdAndCustomerInfo(string ChargeBoxId, CancellationToken token = default)
    {
        using var semaphoreWrapper = await startupSemaphore.GetToken();
        using var db = await contextFactory.CreateDbContextAsync(token);

        var machine = await db.Machine.Where(x => x.ChargeBoxId == ChargeBoxId && x.IsDelete == false).Select(x => new { x.CustomerId, x.Id }).AsNoTracking().FirstOrDefaultAsync(token);
        if (machine == null)
        {
            return new MachineAndCustomerInfo(string.Empty, Guid.Empty, "Unknown");
        }
        //var customerName = await db.Customer.Where(x => x.Id == machine.CustomerId).Select(x => x.Name).FirstOrDefaultAsync();
        var customer = await GetCustomer(machine.CustomerId, token);
        var customerName = customer?.Name;
        return new MachineAndCustomerInfo(machine.Id, machine.CustomerId, customerName);
    }

    public Task<List<MachineConfigurations>> GetMachineConfiguration(string chargeBoxId)
    {
        return getMachineConfigurationHandler.HandleAsync(chargeBoxId);
    }

    public async Task<string> GetMachineConfiguration(string ChargeBoxId, string configName, CancellationToken token = default)
    {
        using var semaphoreWrapper = await startupSemaphore.GetToken();
        using var db = await contextFactory.CreateDbContextAsync(token);
        return await db.MachineConfigurations
            .Where(x => x.ChargeBoxId == ChargeBoxId && x.ConfigureName == configName)
            .Select(x => x.ConfigureSetting).FirstOrDefaultAsync(token);
    }

    public Task<string> GetMachineSecurityProfile(string ChargeBoxId, CancellationToken token = default)
    {
        return GetMachineConfiguration(ChargeBoxId, StandardConfiguration.SecurityProfile, token);
    }

    public Task<string> GetMachineAuthorizationKey(string ChargeBoxId, CancellationToken token = default)
    {
        return GetMachineConfiguration(ChargeBoxId, StandardConfiguration.AuthorizationKey, token);
    }

    public Task<string> GetMachineHeartbeatInterval(string ChargeBoxId)
    {
        return GetMachineConfiguration(ChargeBoxId, StandardConfiguration.HeartbeatInterval);
    }

    public Task UpdateMachineBasicInfo(string ChargeBoxId, Machine machine)
    {
        //return UpdateMachineBasicInfoEF(ChargeBoxId, machine);
        return updateMachineBasicInfoHandler.HandleAsync(new UpdateMachineBasicInfoParam(ChargeBoxId, machine));
    }

    public async Task AddOCMF(Ocmf oCMF)
    {
        using var db = await contextFactory.CreateDbContextAsync();
        await db.Ocmf.AddAsync(oCMF);
        await db.SaveChangesAsync();
    }

    public async ValueTask AddConnectorStatus(
        string ChargeBoxId, byte ConnectorId, DateTime CreatedOn, int Status,
        int ChargePointErrorCodeId, string ErrorInfo, string VendorId, string VendorErrorCode)
    {
        using var db = await contextFactory.CreateDbContextAsync();
        var _currentStatus = new ConnectorStatus()
        {
            ChargeBoxId = ChargeBoxId,
            ConnectorId = ConnectorId,
            CreatedOn = CreatedOn,
            Status = Status,
            ChargePointErrorCodeId = ChargePointErrorCodeId,
            ErrorInfo = ErrorInfo,
            VendorId = VendorId,
            VendorErrorCode = VendorErrorCode,
            Id = Guid.NewGuid().ToString()
        };
        await db.ConnectorStatus.AddAsync(_currentStatus);

        await db.SaveChangesAsync();

        //memoryCache.Set(
        //    string.Format(ChargeBoxConnectorIdMemCacheKeyFromat, ChargeBoxId, ConnectorId)
        //    , _currentStatus, TimeSpan.FromHours(12));
    }

    public async ValueTask<ConnectorStatus> GetConnectorStatus(string ChargeBoxId, int ConnectorId)
    {
        //var key = string.Format(ChargeBoxConnectorIdMemCacheKeyFromat, ChargeBoxId, ConnectorId);
        //if (memoryCache.TryGetValue<ConnectorStatus>(key, out var status))
        //{
        //    return status;
        //}

        using var db = await contextFactory.CreateDbContextAsync();
        var statusFromDb = await db.ConnectorStatus.Where(x => x.ChargeBoxId == ChargeBoxId
                            && x.ConnectorId == ConnectorId).AsNoTracking().FirstOrDefaultAsync();

        //memoryCache.Set(key, statusFromDb, TimeSpan.FromHours(12));
        return statusFromDb;
    }

    public async Task UpdateConnectorStatus(string Id, ConnectorStatus Status)
    {
        //await statusNotificationHandler.HandleAsync(new StatusNotificationParam(Id, Status));
        //await UpdateConnectorStatusEF(Id, Status);
        await UpdateConnectorStatusDapper(Id, Status);

        //var key = string.Format(ChargeBoxConnectorIdMemCacheKeyFromat, Status.ChargeBoxId, Status.ConnectorId);
        //memoryCache.Set(key, Status, TimeSpan.FromHours(12));
        return;
    }

    public Task<Guid> GetCustomerIdByChargeBoxId(string chargeboxId)
    {
        //return GetCustomerIdByChargeBoxIdEF(chargeboxId);
        return GetCustomerIdByChargeBoxIdDapper(chargeboxId);
    }

    public Task<int?> TryGetDuplicatedTransactionId(string chargeBoxId, Guid customerId, int connectorId, DateTime timestamp)
    {
        //return TryGetDuplicatedTransactionIdEF(chargeBoxId, customerId, connectorId, timestamp);
        return TryGetDuplicatedTransactionIdDapper(chargeBoxId, customerId, connectorId, timestamp);
    }


    public ValueTask AddMachineError(byte ConnectorId, DateTime CreatedOn, int Status, string ChargeBoxId,
        int ErrorCodeId, string ErrorInfo, int PreStatus, string VendorErrorCode, string VendorId)
    {
        //return AddMachineErrorEF(ConnectorId, CreatedOn, Status, ChargeBoxId, ErrorCodeId, ErrorInfo, PreStatus, VendorErrorCode, VendorId);
        return AddMachineErrorDapper(ConnectorId, CreatedOn, Status, ChargeBoxId, ErrorCodeId, ErrorInfo, PreStatus, VendorErrorCode, VendorId);
    }

    public ValueTask FillupFinishedTimetoMachineError(string ChargeBoxId, byte ConnectorId, DateTime FinishedOn)
    {

        return AddFinishedTimetoMachineErrorDapper(ChargeBoxId, ConnectorId, FinishedOn);
    }

    public async Task<string> AddServerMessage(string ChargeBoxId, string OutAction, object OutRequest, string CreatedBy, DateTime? CreatedOn = null, string SerialNo = "", string InMessage = "")
    {
        if (string.IsNullOrEmpty(CreatedBy))
        {
            CreatedBy = "Server";
        }

        if (string.IsNullOrEmpty(SerialNo))
        {
            SerialNo = Guid.NewGuid().ToString();
        }
        var _CreatedOn = CreatedOn ?? DateTime.UtcNow;

        string _OutRequest = "";
        if (OutRequest is not null)
        {
            _OutRequest = JsonConvert.SerializeObject(
                OutRequest,
                new JsonSerializerSettings()
                {
                    NullValueHandling = NullValueHandling.Ignore,
                    Formatting = Formatting.None
                });
        }

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

        await AddServerMessage(data);
        return SerialNo;
    }

    public Task<string> AddServerMessage(ServerMessage message)
    {
        //return AddServerMessageEF(message);
        return addServerMessageHandler.HandleAsync(message);
        //var id = message.SerialNo;
        //await AddServerMessageDapper(message);
        //return id;
    }

    public ValueTask<Customer> GetCustomer(string id, CancellationToken token = default)
        => GetCustomer(new Guid(id), token);

    public async ValueTask<Customer> GetCustomer(Guid id, CancellationToken token = default)
    {
        var key = string.Format(CustomerMemCacheKeyFromat, id);
        if (memoryCache.TryGetValue<Customer>(key, out var customer))
        {
            return customer;
        }

        Customer toReturn = null;
        using (var db = await contextFactory.CreateDbContextAsync(token))
        {
            toReturn = await db.Customer.FirstOrDefaultAsync(x => x.Id == id, token);
        }

        if (toReturn is not null)
        {
            memoryCache.Set(key, toReturn, TimeSpan.FromSeconds(15));
        }

        return toReturn;
    }

    public Task<int> AddNewTransactionRecord(TransactionRecord newTransaction)
    {
        //return AddNewTransactionRecordEF(newTransaction);
        return AddNewTransactionRecordDapper(newTransaction);
    }

    public Task<TransactionRecord> GetTransactionForStopTransaction(int transactionId, string chargeBoxId)
    {
        //return GetTransactionForStopTransactionEF(transactionId, chargeBoxId);
        return GetTransactionForStopTransactionDapper(transactionId, chargeBoxId);
    }

    public Task UpdateTransaction(int transactionId, int meterStop, DateTime stopTime, int stopReasonId, string stopReason, string stopIdTag, string receipt, int cost)
    {
        //return UpdateTransactionEF(transactionId, meterStop, stopTime, stopReasonId, stopReason, stopIdTag, receipt, cost);
        return UpdateTransactionDapper(transactionId, meterStop, stopTime, stopReasonId, stopReason, stopIdTag, receipt, cost);
    }

    public async Task UpdateTransactionSOC(int id, string startSOC, string stopSOC)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@TransactionId", id, DbType.Int32, ParameterDirection.Input);
        parameters.Add("@StartSOC", startSOC, DbType.String, ParameterDirection.Input, 3);
        parameters.Add("@StopSOC", stopSOC, DbType.String, ParameterDirection.Input, 3);

        using var conn = await sqlConnectionFactory.CreateAsync();
        var resultCnt = await conn.ExecuteAsync("""
            UPDATE TransactionRecord
            SET StartSOC = @StartSOC, StopSOC = @StopSOC
            WHERE Id = @TransactionId
            """, parameters);
        if (resultCnt != 1)
        {
            throw new Exception("Update over one columes");
        }
        return;
    }

    public Task UpdateServerMessageUpdateTime(int table_id)
    {
        return updateServerMessageUpdateOnHandler.HandleAsync(table_id);
    }

    public async Task AddMachineConfiguration(string chargeBoxId, string key, string value, bool isReadOnly, bool isExists = true)
    {
        using var db = await contextFactory.CreateDbContextAsync();

        await db.MachineConfigurations.AddAsync(new MachineConfigurations()
        {
            ChargeBoxId = chargeBoxId,
            ConfigureName = key,
            ReadOnly = isReadOnly,
            ConfigureSetting = string.IsNullOrEmpty(value) ? string.Empty : value,
            Exists = isExists
        });

        await db.SaveChangesAsync();
    }

    private async Task UpdateTransactionEF(int transactionId, int meterStop, DateTime stopTime, int stopReasonId, string stopReason, string stopIdTag, string receipt, int cost)
    {
        using var db = await contextFactory.CreateDbContextAsync();

        var _transaction = db.TransactionRecord.Where(x => x.Id == transactionId //&& x.ChargeBoxId == session.ChargeBoxId
            ).FirstOrDefault();

        _transaction.MeterStop = meterStop;
        _transaction.StopTime = stopTime;
        _transaction.StopReasonId = stopReasonId;
        _transaction.StopReason = stopReason;
        _transaction.StopIdTag = stopIdTag;
        _transaction.Receipt = receipt;
        _transaction.Cost = cost;

        //await db.SaveChangesAsync();
        await db.SaveChangesAsync();
    }

    public async Task UpdateMachineConfiguration(string chargeBoxId, string item, string value, bool isReadonly, bool isExists = true)
    {
        using var db = await contextFactory.CreateDbContextAsync();
        var config = await db.MachineConfigurations.FirstOrDefaultAsync(x => x.ChargeBoxId == chargeBoxId && x.ConfigureName == item);
        if (config is null)
        {
            return;
        }
        config.ConfigureSetting = value;
        config.ReadOnly = isReadonly;
        config.Exists = isExists;
        await db.SaveChangesAsync();
    }

    private async Task UpdateTransactionDapper(int transactionId, int meterStop, DateTime stopTime, int stopReasonId, string stopReason, string stopIdTag, string receipt, int cost)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@TransactionId", transactionId, DbType.Int32, ParameterDirection.Input);
        parameters.Add("@MeterStop", meterStop, DbType.Decimal, ParameterDirection.Input, precision: 18, scale: 2);
        parameters.Add("@StopTime", stopTime, DbType.DateTime, ParameterDirection.Input);
        parameters.Add("@StopReasonId", stopReasonId, DbType.Int32, ParameterDirection.Input);
        parameters.Add("@StopReason", stopReason, DbType.String, ParameterDirection.Input, 60);
        parameters.Add("@StopIdTag", stopIdTag, DbType.String, ParameterDirection.Input, 20);
        parameters.Add("@Receipt", receipt, DbType.String, ParameterDirection.Input, 3000);
        parameters.Add("@Cost", cost, DbType.Decimal, ParameterDirection.Input, precision: 18, scale: 2);

        using var conn = await sqlConnectionFactory.CreateAsync();
        var resultCnt = await conn.ExecuteAsync("""
            UPDATE TransactionRecord
            SET MeterStop = @MeterStop, StopTime = @StopTime, StopReasonId = @StopReasonId,
            StopReason = @StopReason, StopIdTag = @StopIdTag, Receipt = @Receipt, Cost = @Cost
            WHERE Id = @TransactionId
            """, parameters);
        if (resultCnt != 1)
        {
            throw new Exception("Update over one columes");
        }
        return;
    }

    public Task<bool> UpdateHeartBeats(IEnumerable<Machine> heartBeatsData)
    {
        //return UpdateHeartBeatsEF(heartBeatsData);
        return UpdateHeartBeatsDapper(heartBeatsData);
    }
    public Task<bool> UpdateHeartBeats(List<string> machineIds)
    {
        return UpdateHeartBeatsDapper(machineIds);
    }

	public async Task<bool> UpdateCustomId(string customId,string chargeboxId)
	{
		using var conn = await sqlConnectionFactory.CreateAsync();
		try
		{
			var parameters = new DynamicParameters();
			parameters.Add("@ChargeBoxId", chargeboxId, size: 50);
			parameters.Add("@CustomId", customId, size: 25);
			var resultCnt = await conn.ExecuteAsync("""
                UPDATE Machine
                SET CustomId = @CustomId
                WHERE ChargeBoxId = @ChargeBoxId
                """, parameters);
		}
		catch (Exception e)
		{
			logger.LogError(e.Message);
			logger.LogCritical("UpdateCustomId update fail");
			return false;
		}
		return true;
	}

	public async Task UpdateMachineConnectionType(string chargeBoxId, int connectionType)
    {
        using var semaphoreWrapper = await startupSemaphore.GetToken();
        using var db = await contextFactory.CreateDbContextAsync();

        var machine = await db.Machine.Where(x => x.ChargeBoxId == chargeBoxId).FirstOrDefaultAsync();
        if (machine != null)
        {
            machine.ConnectionType = connectionType;
            await db.SaveChangesAsync();
        }
    }

    public async Task<object> TryGetResponseFromDb(string msgId, CancellationToken token = default)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@MID", msgId, DbType.String, size: 36);
        parameters.Add("@MT", DateTime.UtcNow.AddSeconds(-5), DbType.DateTime);

        var sql = """
            SELECT [OutAction],[InMessage]
            FROM [ServerMessage]
            WHERE [SerialNo] = @MID AND CreatedOn > @MT
            """;
        using var conn = await sqlConnectionFactory.CreateAsync();
        ServerMessage item = null;
        item = await conn.QueryFirstOrDefaultAsync<ServerMessage>(new CommandDefinition(sql, parameters: parameters, cancellationToken: token));

        Actions action = Actions.None;
        if (item is null ||
            !Enum.TryParse(item.OutAction, out action))
        {
            return null;
        }

        switch (action)
        {
            case Actions.GetConfiguration:
                return JsonConvert.DeserializeObject<GetConfigurationConfirmation>(item.InMessage);
            case Actions.ChangeConfiguration:
                return JsonConvert.DeserializeObject<ChangeConfigurationConfirmation>(item.InMessage);
            case Actions.RemoteStartTransaction:
                return JsonConvert.DeserializeObject<RemoteStartTransactionConfirmation>(item.InMessage);
            case Actions.RemoteStopTransaction:
                return JsonConvert.DeserializeObject<RemoteStopTransactionConfirmation>(item.InMessage);
            case Actions.ChangeAvailability:
                return JsonConvert.DeserializeObject<ChangeAvailabilityConfirmation>(item.InMessage);
            case Actions.ClearCache:
                return JsonConvert.DeserializeObject<ClearCacheConfirmation>(item.InMessage);
            case Actions.DataTransfer:
                return JsonConvert.DeserializeObject<DataTransferConfirmation>(item.InMessage);
            case Actions.Reset:
                return JsonConvert.DeserializeObject<ResetConfirmation>(item.InMessage);
            case Actions.UnlockConnector:
                return JsonConvert.DeserializeObject<UnlockConnectorConfirmation>(item.InMessage);
            case Actions.TriggerMessage:
                return JsonConvert.DeserializeObject<TriggerMessageConfirmation>(item.InMessage);
            case Actions.GetDiagnostics:
                return JsonConvert.DeserializeObject<GetDiagnosticsConfirmation>(item.InMessage);
            case Actions.UpdateFirmware:
                return JsonConvert.DeserializeObject<UpdateFirmwareConfirmation>(item.InMessage);
            case Actions.GetLocalListVersion:
                return JsonConvert.DeserializeObject<GetLocalListVersionConfirmation>(item.InMessage);
            case Actions.SendLocalList:
                return JsonConvert.DeserializeObject<SendLocalListConfirmation>(item.InMessage);
            case Actions.SetChargingProfile:
                return JsonConvert.DeserializeObject<SetChargingProfileConfirmation>(item.InMessage);
            case Actions.ClearChargingProfile:
                return JsonConvert.DeserializeObject<ClearChargingProfileConfirmation>(item.InMessage);
            case Actions.GetCompositeSchedule:
                return JsonConvert.DeserializeObject<GetCompositeScheduleConfirmation>(item.InMessage);
            case Actions.ReserveNow:
                return JsonConvert.DeserializeObject<ReserveNowConfirmation>(item.InMessage);
            case Actions.CancelReservation:
                return JsonConvert.DeserializeObject<CancelReservationConfirmation>(item.InMessage);
            case Actions.ExtendedTriggerMessage:
                return JsonConvert.DeserializeObject<ExtendedTriggerMessageConfirmation>(item.InMessage);
            default:
                return null;
        }
    }

    private void InitUpdateConnectorStatusHandler()
    {
        if (statusNotificationHandler is not null)
        {
            throw new Exception($"{nameof(InitUpdateConnectorStatusHandler)} should only called once");
        }

        statusNotificationHandler = new GroupHandler<StatusNotificationParam>(
            handleFunc: BundleUpdateConnectorStatusDapper,
            logger: loggerFactory.CreateLogger("StatusNotificationHandler"),
            workerCnt: 1);
    }

    private void InitAddServerMessageHandler()
    {
        if (addServerMessageHandler is not null)
        {
            throw new Exception($"{nameof(InitAddServerMessageHandler)} should only called once");
        }

        addServerMessageHandler = new GroupHandler<ServerMessage, string>(
            handleFunc: BundleAddServerMessage,
            logger: loggerFactory.CreateLogger("AddServerMessageHandler"));
    }

    private void InitUpdateMachineBasicInfoHandler()
    {
        if (updateMachineBasicInfoHandler is not null)
        {
            throw new Exception($"{nameof(InitUpdateMachineBasicInfoHandler)} should only called once");
        }

        updateMachineBasicInfoHandler = new GroupHandler<UpdateMachineBasicInfoParam>(
            handleFunc: BundelUpdateMachineBasicInfo,
            logger: loggerFactory.CreateLogger("UpdateMachineBasicInfoHandler"),
            workerCnt: 1);
    }

    private void InitUpdateServerMessageUpdateOnHandler()
    {
        if (updateServerMessageUpdateOnHandler is not null)
        {
            throw new Exception($"{nameof(InitUpdateMachineBasicInfoHandler)} should only called once");
        }

        updateServerMessageUpdateOnHandler = new GroupHandler<int>(
            handleFunc: BundelUpdateServerMessageUpdateOn,
            logger: loggerFactory.CreateLogger("UpdateServerMessageUpdateOnHandler"),
            workerCnt: 10);
    }

    private void InitGetMachineConfigurationHandler()
    {
        if (getMachineConfigurationHandler is not null)
        {
            throw new Exception($"{nameof(InitUpdateMachineBasicInfoHandler)} should only called once");
        }

        getMachineConfigurationHandler = new GroupHandler<string, List<MachineConfigurations>>(
            handleFunc: BundelGetMachineConfiguration,
            logger: loggerFactory.CreateLogger("GetMachineConfigurationHandler"),
            workerCnt: 10);
    }

    private void InitUpdateErrorFinishedOnHandler()
    {
        if (updateErrorFinishedOnHandler is not null)
        {
            throw new Exception($"{nameof(InitUpdateErrorFinishedOnHandler)} should only called once");
        }

        updateErrorFinishedOnHandler = new GroupHandler<UpdateErrofFinishedOnParam>(
            handleFunc: BundelUpdateErrorFinishedOn,
            logger: loggerFactory.CreateLogger("UpdateErrorFinishedOnHandler"),
            workerCnt: 1);
    }

    private async Task UpdateMachineBasicInfoEF(string chargeBoxId, Machine machine)
    {
        using var semaphoreWrapper = await startupSemaphore.GetToken();
        using var db = await contextFactory.CreateDbContextAsync();

        var _machine = await db.Machine.FirstOrDefaultAsync(x => x.ChargeBoxId == chargeBoxId);
        _machine.ChargeBoxSerialNumber = machine.ChargeBoxSerialNumber;
        _machine.ChargePointSerialNumber = machine.ChargePointSerialNumber;
        _machine.ChargePointModel = machine.ChargePointModel;
        _machine.ChargePointVendor = machine.ChargePointVendor;
        _machine.FwCurrentVersion = machine.FwCurrentVersion;
        _machine.Iccid = machine.Iccid;
        _machine.Imsi = machine.Imsi;
        _machine.MeterSerialNumber = machine.MeterSerialNumber;
        _machine.MeterType = machine.MeterType;

        await db.SaveChangesAsync();

        //using var semaphoreWrapper = await startupSemaphore.GetToken();
    }

    private async Task BundelUpdateMachineBasicInfo(BundleHandlerData<UpdateMachineBasicInfoParam> bundleHandlerData)
    {
        using var db = await contextFactory.CreateDbContextAsync();
        using var trans = await db.Database.BeginTransactionAsync();

        var pams = bundleHandlerData.Datas.DistinctBy(x => x.ChargeBoxId);

        foreach (var pam in pams)
        {
            var _machine = db.Machine.FirstOrDefault(x => x.ChargeBoxId == pam.ChargeBoxId);
            _machine.ChargeBoxSerialNumber = pam.machine.ChargeBoxSerialNumber;
            _machine.ChargePointSerialNumber = pam.machine.ChargePointSerialNumber;
            _machine.ChargePointModel = pam.machine.ChargePointModel;
            _machine.ChargePointVendor = pam.machine.ChargePointVendor;
            _machine.FwCurrentVersion = pam.machine.FwCurrentVersion;
            _machine.Iccid = pam.machine.Iccid;
            _machine.Imsi = pam.machine.Imsi;
            _machine.MeterSerialNumber = pam.machine.MeterSerialNumber;
            _machine.MeterType = pam.machine.MeterType;
        }

        await db.SaveChangesAsync();
        await trans.CommitAsync();

        bundleHandlerData.CompletedDatas.AddRange(bundleHandlerData.Datas);
    }

    private async Task BundelUpdateServerMessageUpdateOn(BundleHandlerData<int> bundleHandlerData)
    {
        var ids = bundleHandlerData.Datas;
        var sql = """
            UPDATE [dbo].[ServerMessage]
            SET UpdatedOn = @DateTimeNow
            WHERE Id in @Ids
            """;
        DynamicParameters parameters = new DynamicParameters();
        parameters.Add("DateTimeNow", DateTime.UtcNow, DbType.DateTime);
        parameters.Add("Ids", ids);

        using SqlConnection sqlConnection = await sqlConnectionFactory.CreateAsync();
        var cnt = await sqlConnection.ExecuteAsync(sql, parameters);
        if (cnt != 0 || ids.Count == 0)
        {
            bundleHandlerData.CompletedDatas.AddRange(ids);
        }
    }

    private async Task BundelGetMachineConfiguration(BundleHandlerData<string, List<MachineConfigurations>> bundleHandlerData)
    {
        var chargeboxIds = bundleHandlerData.Datas;
        var sql = """
            SELECT [ChargeBoxId], [ConfigureName], [ConfigureSetting], [ReadOnly], [Exists]
            FROM [dbo].[MachineConfigurations]
            WHERE ChargeBoxId IN @ChargeBoxIds
            """;
        DynamicParameters parameters = new DynamicParameters();
        parameters.Add("@ChargeBoxIds", chargeboxIds, direction: ParameterDirection.Input, size: 25);

        using SqlConnection sqlConnection = await sqlConnectionFactory.CreateAsync();
        var result = await sqlConnection.QueryAsync<MachineConfigurations>(sql, parameters);
        var gReult = result.GroupBy(x => x.ChargeBoxId);
        foreach (var g in gReult)
        {
            var originKey = chargeboxIds.FirstOrDefault(x => x.ToLower() == g.Key.ToLower());
            if (string.IsNullOrEmpty(originKey))
            {
                continue;
            }
            bundleHandlerData.AddCompletedData(originKey, g.ToList());
        }
        var getConfigChargeboxIds = gReult.Select(x => x.Key);
        var emptyConfigChargeboxIds = chargeboxIds.Except(getConfigChargeboxIds);
        foreach (var chargeboxId in emptyConfigChargeboxIds)
        {
            bundleHandlerData.AddCompletedData(chargeboxId, new List<MachineConfigurations>());
        }
    }

    private async Task UpdateConnectorStatusEF(string Id, ConnectorStatus Status)
    {
        using var db = await contextFactory.CreateDbContextAsync();

        ConnectorStatus status = new() { Id = Id };

        db.ChangeTracker.AutoDetectChangesEnabled = false;
        db.ConnectorStatus.Attach(status);


        status.CreatedOn = Status.CreatedOn;
        status.Status = Status.Status;
        status.ChargePointErrorCodeId = Status.ChargePointErrorCodeId;
        status.ErrorInfo = Status.ErrorInfo;
        status.VendorId = Status.VendorId;
        status.VendorErrorCode = Status.VendorErrorCode;


        db.Entry(status).Property(x => x.CreatedOn).IsModified = true;
        db.Entry(status).Property(x => x.Status).IsModified = true;
        db.Entry(status).Property(x => x.ChargePointErrorCodeId).IsModified = true;
        db.Entry(status).Property(x => x.ErrorInfo).IsModified = true;
        db.Entry(status).Property(x => x.VendorId).IsModified = true;
        db.Entry(status).Property(x => x.VendorErrorCode).IsModified = true;

        await db.SaveChangesAsync();
    }

    private async Task UpdateConnectorStatusDapper(string Id, ConnectorStatus Status)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@Id", Id, DbType.String, ParameterDirection.Input, 36);
        parameters.Add("@CreatedOn", Status.CreatedOn, DbType.DateTime, ParameterDirection.Input);
        parameters.Add("@Status", Status.Status, DbType.Int32, ParameterDirection.Input);
        parameters.Add("@ChargePointErrorCodeId", Status.ChargePointErrorCodeId, DbType.Int32, ParameterDirection.Input);
        parameters.Add("@ErrorInfo", Status.ErrorInfo, DbType.String, ParameterDirection.Input, 50);
        parameters.Add("@VendorId", Status.VendorId, DbType.String, ParameterDirection.Input, 255);
        parameters.Add("@VendorErrorCode", Status.VendorErrorCode, DbType.String, ParameterDirection.Input, 100);

        using var conn = await sqlConnectionFactory.CreateAsync();
        await conn.ExecuteAsync("""
            update ConnectorStatus
            set
            CreatedOn = @CreatedOn,
            Status = @Status,
            ChargePointErrorCodeId = @ChargePointErrorCodeId,
            ErrorInfo = @ErrorInfo,
            VendorId = @VendorId,
            VendorErrorCode = @VendorErrorCode
            where Id = @Id
            """, parameters);
    }

    private async Task<Guid> GetCustomerIdByChargeBoxIdEF(string chargeboxId)
    {
        using var db = await contextFactory.CreateDbContextAsync();
        var _CustomerId = await db.Machine.Where(x => x.ChargeBoxId == chargeboxId).Select(x => x.CustomerId).FirstOrDefaultAsync();
        return _CustomerId;
    }

    private async Task<Guid> GetCustomerIdByChargeBoxIdDapper(string chargeboxId)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@ChargeBoxId", chargeboxId, DbType.String, ParameterDirection.Input, 50);

        using var conn = await sqlConnectionFactory.CreateAsync();
        var _existedTx = await conn.QueryFirstOrDefaultAsync<Guid>("""
            select CustomerId
            from dbo.Machine
            where
            ChargeBoxId = @ChargeBoxId
            """, parameters);

        return _existedTx;
    }

    private async Task<int?> TryGetDuplicatedTransactionIdEF(string chargeBoxId, Guid customerId, int connectorId, DateTime timestamp)
    {
        using var db = await contextFactory.CreateDbContextAsync();
        var _existedTx = await db.TransactionRecord.Where(x => x.CustomerId == customerId && x.ChargeBoxId == chargeBoxId
                               && x.ConnectorId == connectorId && x.StartTime == timestamp).Select(x => x.Id).FirstOrDefaultAsync();
        return _existedTx;
    }

    private async Task<int?> TryGetDuplicatedTransactionIdDapper(string chargeBoxId, Guid customerId, int connectorId, DateTime timestamp)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
        parameters.Add("@CustomerId", customerId, DbType.Guid, ParameterDirection.Input);
        parameters.Add("@ConnectorId", connectorId, DbType.Int16, ParameterDirection.Input);
        parameters.Add("@TimeStamp", timestamp, DbType.DateTime, ParameterDirection.Input);

        using var conn = await sqlConnectionFactory.CreateAsync();
        var _existedTx = await conn.QueryFirstOrDefaultAsync<int?>("""
            SELECT Id
            FROM dbo.TransactionRecord
            WHERE
            ChargeBoxId = @ChargeBoxId and
            CustomerId = @CustomerId and
            ConnectorId = @ConnectorId and
            StartTime = @TimeStamp
            """, parameters);

        return _existedTx;
    }

    private async ValueTask AddMachineErrorEF(byte connectorId, DateTime createdOn, int status, string chargeBoxId, int errorCodeId, string errorInfo, int preStatus, string vendorErrorCode, string vendorId)
    {
        using var db = await contextFactory.CreateDbContextAsync();
        await db.MachineError.AddAsync(new MachineError()
        {
            ConnectorId = connectorId,
            CreatedOn = createdOn,
            Status = status,
            ChargeBoxId = chargeBoxId,
            ErrorCodeId = errorCodeId,
            ErrorInfo = errorInfo,
            PreStatus = preStatus,
            VendorErrorCode = vendorErrorCode,
            VendorId = vendorId
        });

        await db.SaveChangesAsync();
    }

    private async ValueTask AddMachineErrorDapper(byte connectorId, DateTime createdOn, int status, string chargeBoxId, int errorCodeId, string errorInfo, int preStatus, string vendorErrorCode, string vendorId)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@ConnectorId", connectorId, DbType.Int16, ParameterDirection.Input);
        parameters.Add("@PreStatus", preStatus, DbType.Int32, ParameterDirection.Input);
        parameters.Add("@Status", status, DbType.Int32, ParameterDirection.Input);
        parameters.Add("@ErrorInfo", errorInfo, DbType.String, ParameterDirection.Input, 50);
        parameters.Add("@VendorId", vendorId, DbType.String, ParameterDirection.Input, 255);
        parameters.Add("@CreatedOn", createdOn, DbType.DateTime, ParameterDirection.Input);
        parameters.Add("@ErrorCodeId", errorCodeId, DbType.Int32, ParameterDirection.Input);
        parameters.Add("@VendorErrorCode", vendorErrorCode, DbType.String, ParameterDirection.Input, 100);
        parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);

        using var conn = await sqlConnectionFactory.CreateAsync();
        await conn.ExecuteAsync("""
            INSERT INTO MachineError
            (ConnectorId, PreStatus, Status, ErrorInfo, VendorId, CreatedOn, ErrorCodeId, VendorErrorCode, ChargeBoxId)
            VALUES (@ConnectorId, @PreStatus, @Status, @ErrorInfo, @VendorId, @CreatedOn, @ErrorCodeId, @VendorErrorCode, @ChargeBoxId)
            """, parameters);
    }

    private async ValueTask AddFinishedTimetoMachineErrorDapper(string chargeBoxId, byte connectorId, DateTime finishedTime)
    {
        var getCommand = """
            SELECT TOP(1) Id
            FROM [dbo].[MachineError]
            where ChargeBoxId=@ChargeBoxId and ConnectorId=@ConnectorId
            Order by Id desc
            """;

        var parameters = new DynamicParameters();
        parameters.Add("@ConnectorId", connectorId, DbType.Int16, ParameterDirection.Input);
        parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
        //parameters.Add("@CreatedOn", previousErrorOn, DbType.DateTime, ParameterDirection.Input, 50);

        int? recordId = null;
        using (var conn = await sqlConnectionFactory.CreateAsync())
        {
            recordId = await conn.QueryFirstOrDefaultAsync<int?>(getCommand, parameters);
            if (recordId is null)
            {
                return;
            }
        }

        await updateErrorFinishedOnHandler.HandleAsync(new UpdateErrofFinishedOnParam(recordId.Value, finishedTime));
    }

    private async Task BundelUpdateErrorFinishedOn(BundleHandlerData<UpdateErrofFinishedOnParam> bundleHandlerData)
    {
        var updateCommand = """
            Update MachineError 
            set FinishedOn=@FinishedOn 
            where Id=@Id
            """;

        var pams = bundleHandlerData.Datas;
        using var conn = await sqlConnectionFactory.CreateAsync();
        using var trans = await conn.BeginTransactionAsync();

        DynamicParameters parameters = null;

        foreach (var pam in pams)
        {

            parameters = new DynamicParameters();
            parameters.Add("@Id", pam.Id, DbType.Int32, ParameterDirection.Input);
            parameters.Add("@FinishedOn", pam.finishedOn, DbType.DateTime, ParameterDirection.Input);

            await conn.ExecuteAsync(new CommandDefinition(
                updateCommand,
                parameters: parameters,
                transaction: trans
                ));
        }

        await trans.CommitAsync();

        bundleHandlerData.CompletedDatas.AddRange(bundleHandlerData.Datas);
    }

    private async Task BundleUpdateConnectorStatus(IEnumerable<StatusNotificationParam> statusNotifications)
    {
        using var db = await contextFactory.CreateDbContextAsync();
        using var trans = await db.Database.BeginTransactionAsync();

        statusNotifications = statusNotifications.OrderBy(x => x.Status.CreatedOn).DistinctBy(x => x.Id);

        foreach (var param in statusNotifications)
        {
            ConnectorStatus status = new() { Id = param.Id };

            //db.ChangeTracker.AutoDetectChangesEnabled = false;
            db.ConnectorStatus.Attach(status);


            status.CreatedOn = param.Status.CreatedOn;
            status.Status = param.Status.Status;
            status.ChargePointErrorCodeId = param.Status.ChargePointErrorCodeId;
            status.ErrorInfo = param.Status.ErrorInfo;
            status.VendorId = param.Status.VendorId;
            status.VendorErrorCode = param.Status.VendorErrorCode;


            db.Entry(status).Property(x => x.CreatedOn).IsModified = true;
            db.Entry(status).Property(x => x.Status).IsModified = true;
            db.Entry(status).Property(x => x.ChargePointErrorCodeId).IsModified = true;
            db.Entry(status).Property(x => x.ErrorInfo).IsModified = true;
            db.Entry(status).Property(x => x.VendorId).IsModified = true;
            db.Entry(status).Property(x => x.VendorErrorCode).IsModified = true;

            //await db.SaveChangesAsync();
        }

        await db.SaveChangesAsync();
        await trans.CommitAsync();
        //db.ChangeTracker.Clear();
    }



    private async Task BundleUpdateConnectorStatusDapper(BundleHandlerData<StatusNotificationParam> bundleHandlerData)
    {
        using var conn = await sqlConnectionFactory.CreateAsync();
        foreach (var status in bundleHandlerData.Datas)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@Id", status.Id, DbType.String, ParameterDirection.Input, 36);
            parameters.Add("@CreatedOn", status.Status.CreatedOn, DbType.DateTime, ParameterDirection.Input);
            parameters.Add("@Status", status.Status.Status, DbType.Int32, ParameterDirection.Input);
            parameters.Add("@ChargePointErrorCodeId", status.Status.ChargePointErrorCodeId, DbType.Int32, ParameterDirection.Input);
            parameters.Add("@ErrorInfo", status.Status.ErrorInfo, DbType.String, ParameterDirection.Input, 50);
            parameters.Add("@VendorId", status.Status.VendorId, DbType.String, ParameterDirection.Input, 255);
            parameters.Add("@VendorErrorCode", status.Status.VendorErrorCode, DbType.String, ParameterDirection.Input, 100);

            await conn.ExecuteAsync("""
                    update ConnectorStatus
                    set
                    CreatedOn = @CreatedOn,
                    Status = @Status,
                    ChargePointErrorCodeId = @ChargePointErrorCodeId,
                    ErrorInfo = @ErrorInfo,
                    VendorId = @VendorId,
                    VendorErrorCode = @VendorErrorCode
                    where Id = @Id
                    """, parameters);
            bundleHandlerData.AddCompletedData(status);
        }
    }

    private async Task BundleAddServerMessage(BundleHandlerData<ServerMessage, string> bundleHandlerData)
    {
        //var sql = """
        //    INSERT INTO [ServerMessage] ([ChargeBoxId], [CreatedBy], [CreatedOn], [InMessage], [OutAction], [OutRequest], [ReceivedOn], [SerialNo], [UpdatedOn])
        //    OUTPUT INSERTED.Id
        //    VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)
        //    """;

        //using var conn = await sqlConnectionFactory.CreateAsync();

        //foreach(var data in bundleHandlerData.Datas)
        //{
        //    var dymparam = new DynamicParameters();
        //    dymparam.Add("@p0", data.ChargeBoxId);
        //    dymparam.Add("@p1", data.CreatedBy);
        //    dymparam.Add("@p2", data.CreatedOn);
        //    dymparam.Add("@p3", data.InMessage);
        //    dymparam.Add("@p4", data.OutAction);
        //    dymparam.Add("@p5", data.OutRequest);
        //    dymparam.Add("@p6", data.ReceivedOn);
        //    dymparam.Add("@p7", data.SerialNo);
        //    dymparam.Add("@p8", data.UpdatedOn);
        //}

        using var db = await contextFactory.CreateDbContextAsync();
        using var trans = await db.Database.BeginTransactionAsync();

        foreach (var message in bundleHandlerData.Datas)
        {
            await db.ServerMessage.AddAsync(message);
        }

        await db.SaveChangesAsync();
        await trans.CommitAsync();

        bundleHandlerData.CompletedDatas.AddRange(bundleHandlerData.Datas.Select(x => new KeyValuePair<ServerMessage, string>(x, x.SerialNo)));
    }

    private async Task AddServerMessageEF(ServerMessage message)
    {
        using var db = await contextFactory.CreateDbContextAsync();
        using var trans = await db.Database.BeginTransactionAsync();

        await db.ServerMessage.AddAsync(message);

        await db.SaveChangesAsync();
        await trans.CommitAsync();
        //db.ChangeTracker.Clear();
    }

    private async Task 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 sqlConnectionFactory.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)
        {
            throw new Exception("Insert failed");
        }
        return;
    }

    private async Task<int> AddNewTransactionRecordEF(TransactionRecord newTransaction)
    {
        using var db = await contextFactory.CreateDbContextAsync();

        await db.TransactionRecord.AddAsync(newTransaction);

        await db.SaveChangesAsync();

        return newTransaction.Id;
    }

    private async Task<int> AddNewTransactionRecordDapper(TransactionRecord newTransaction)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@ChargeBoxId", newTransaction.ChargeBoxId, DbType.String, ParameterDirection.Input, 50);
        parameters.Add("@ConnectorId", newTransaction.ConnectorId, DbType.Int16, ParameterDirection.Input);
        parameters.Add("@CreatedOn", newTransaction.CreatedOn, DbType.DateTime, ParameterDirection.Input);
        parameters.Add("@UpdatedOn", newTransaction.UpdatedOn, DbType.DateTime, ParameterDirection.Input);
        parameters.Add("@StartTransactionReportedOn", newTransaction.StartTransactionReportedOn, DbType.DateTime, ParameterDirection.Input);
        parameters.Add("@StopTransactionReportedOn", newTransaction.StopTransactionReportedOn, DbType.DateTime, ParameterDirection.Input);
        parameters.Add("@StartIdTag", newTransaction.StartIdTag, DbType.String, ParameterDirection.Input, 20);
        parameters.Add("@MeterStart", newTransaction.MeterStart, DbType.Decimal, ParameterDirection.Input, precision: 18, scale: 2);
        parameters.Add("@MeterStop", newTransaction.MeterStop, DbType.Decimal, ParameterDirection.Input, precision: 18, scale: 2);
        parameters.Add("@CustomerId", newTransaction.CustomerId, DbType.Guid, ParameterDirection.Input);
        parameters.Add("@StartTime", newTransaction.StartTime, DbType.DateTime, ParameterDirection.Input);
        parameters.Add("@StopTime", newTransaction.StopTime, DbType.DateTime, ParameterDirection.Input);
        parameters.Add("@ReservationId", newTransaction.ReservationId, DbType.Int32, ParameterDirection.Input);
        parameters.Add("@RetryStartTransactionTimes", newTransaction.RetryStartTransactionTimes, DbType.Int32, ParameterDirection.Input);
        parameters.Add("@RetryStopTransactionTimes", newTransaction.RetryStopTransactionTimes, DbType.Int32, ParameterDirection.Input);
        parameters.Add("@Fee", newTransaction.Fee, DbType.String, ParameterDirection.Input, 1500);

        using var conn = await sqlConnectionFactory.CreateAsync();
        var id = await conn.QuerySingleAsync<int>("""
            INSERT INTO TransactionRecord
            (ChargeBoxId, ConnectorId, CreatedOn, UpdatedOn, StartTransactionReportedOn, StopTransactionReportedOn,
            StartIdTag, MeterStart, MeterStop, CustomerId, StartTime, StopTime, ReservationId, RetryStartTransactionTimes, RetryStopTransactionTimes, Fee)
            OUTPUT INSERTED.Id
            VALUES (@ChargeBoxId, @ConnectorId, @CreatedOn, @UpdatedOn, @StartTransactionReportedOn, @StopTransactionReportedOn,
            @StartIdTag, @MeterStart, @MeterStop, @CustomerId, @StartTime, @StopTime, @ReservationId, @RetryStartTransactionTimes, @RetryStopTransactionTimes, @Fee)
            """, parameters);
        return id;
    }

    private async Task<TransactionRecord> GetTransactionForStopTransactionEF(int transactionId, string chargeBoxId)
    {
        using var db = await contextFactory.CreateDbContextAsync();
        return await db.TransactionRecord.Where(x => x.Id == transactionId
             && x.ChargeBoxId == chargeBoxId).FirstOrDefaultAsync();
    }

    private async Task<TransactionRecord> GetTransactionForStopTransactionDapper(int transactionId, string chargeBoxId)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@TransactionId", transactionId, DbType.Int32, ParameterDirection.Input);
        parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);

        using var conn = await sqlConnectionFactory.CreateAsync();
        var record = await conn.QuerySingleAsync<TransactionRecord>("""
            SELECT Id, ConnectorId, MeterStop, MeterStart, StartTime, StopTime FROM TransactionRecord
            WHERE Id = @TransactionId and ChargeBoxId = @ChargeBoxId 
            """, parameters);
        return record;
    }

    private Task BulkInsertServerMessage(IEnumerable<ServerMessage> messages)
    {
        var table = new DataTable();
        table.Columns.Add("ChargeBoxId");
        table.Columns.Add("SerialNo");
        table.Columns.Add("OutAction");
        table.Columns.Add("OutRequest");
        table.Columns.Add("InMessage");
        table.Columns.Add("CreatedOn");
        table.Columns.Add("CreatedBy");
        table.Columns.Add("UpdatedOn");
        table.Columns.Add("ReceivedOn");

        foreach (var param in messages)
        {
            var row = table.NewRow();
            row["ChargeBoxId"] = param.ChargeBoxId;
            row["SerialNo"] = param.SerialNo;
            row["OutAction"] = param.OutAction;
            row["OutRequest"] = param.OutRequest;
            row["InMessage"] = param.InMessage;
            row["CreatedOn"] = param.CreatedOn;
            row["CreatedBy"] = param.CreatedBy;
            row["UpdatedOn"] = param.UpdatedOn;
            row["ReceivedOn"] = param.ReceivedOn;

            table.Rows.Add(row);
        }

        using SqlConnection sqlConnection = sqlConnectionFactory.Create();
        using SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnection);

        sqlBulkCopy.BatchSize = messages.Count();
        sqlBulkCopy.DestinationTableName = "ServerMessage";

        sqlBulkCopy.ColumnMappings.Add("ChargeBoxId", "ChargeBoxId");
        sqlBulkCopy.ColumnMappings.Add("SerialNo", "SerialNo");
        sqlBulkCopy.ColumnMappings.Add("OutAction", "OutAction");
        sqlBulkCopy.ColumnMappings.Add("OutRequest", "OutRequest");
        sqlBulkCopy.ColumnMappings.Add("InMessage", "InMessage");
        sqlBulkCopy.ColumnMappings.Add("CreatedOn", "CreatedOn");
        sqlBulkCopy.ColumnMappings.Add("CreatedBy", "CreatedBy");
        sqlBulkCopy.ColumnMappings.Add("UpdatedOn", "UpdatedOn");
        sqlBulkCopy.ColumnMappings.Add("ReceivedOn", "ReceivedOn");

        return sqlBulkCopy.WriteToServerAsync(table);
    }

    private int GetStartupLimit(IConfiguration configuration)
    {
        var limitConfig = configuration["MainDbStartupLimit"];
        int limit = 5;
        if (limitConfig != default)
        {
            int.TryParse(limitConfig, out limit);
        }
        return limit;
    }

    private int GetOpLimit(IConfiguration configuration)
    {
        var limitConfig = configuration["MainDbOpLimit"];
        int limit = 500;
        if (limitConfig != default)
        {
            int.TryParse(limitConfig, out limit);
        }
        return limit;
    }

    private async Task<bool> UpdateHeartBeatsDapper(IEnumerable<Machine> heartBeatsData)
    {
        using var conn = await sqlConnectionFactory.CreateAsync();
        using var trans = await conn.BeginTransactionAsync();

        try
        {
            foreach (var data in heartBeatsData)
            {
                var parameters = new DynamicParameters();
                parameters.Add("@Id", data.Id, DbType.String, ParameterDirection.Input, 36);
                parameters.Add("@HeartbeatUpdatedOn", data.HeartbeatUpdatedOn, DbType.DateTime, ParameterDirection.Input);
                parameters.Add("@ConnectionType", data.ConnectionType, DbType.Int32, ParameterDirection.Input);

                var resultCnt = await conn.ExecuteAsync("""
                    UPDATE Machine
                    SET HeartbeatUpdatedOn = @HeartbeatUpdatedOn, ConnectionType = @ConnectionType
                    WHERE Id = @Id
                    """, parameters, trans);
                if (resultCnt != 1)
                {
                    throw new Exception("Update over one columes");
                }
            }
            await trans.CommitAsync();
        }
        catch
        {
            logger.LogCritical("HeartBeatCheckTrigger update fail, roll back");
            await trans.RollbackAsync();
            return false;
        }

        return true;
    }

    private async Task<bool> UpdateHeartBeatsDapper(List<string> machineIds)
    {
        using var conn = await sqlConnectionFactory.CreateAsync();
        try
        {
            var parameters = new DynamicParameters();
            parameters.Add("@Ids", machineIds, size: 36);
            parameters.Add("@HeartbeatUpdatedOn", DateTime.UtcNow, DbType.DateTime, ParameterDirection.Input);
            var resultCnt = await conn.ExecuteAsync("""
                UPDATE Machine
                SET HeartbeatUpdatedOn = @HeartbeatUpdatedOn
                WHERE Id in @Ids
                """, parameters);
        }
        catch (Exception e)
        {
            logger.LogError(e.Message);
            logger.LogCritical("HeartBeatCheckTrigger update fail");
            return false;
        }
        return true;
    }

    private async Task<bool> UpdateHeartBeatsEF(IEnumerable<Machine> heartBeatsData)
    {
        using var db = await contextFactory.CreateDbContextAsync();
        using var transaction = await db.Database.BeginTransactionAsync();

        try
        {
            foreach (var data in heartBeatsData)
            {
                var machine = new Machine() { Id = data.Id };
                if (machine != null)
                {
                    db.Machine.Attach(machine);
                    machine.HeartbeatUpdatedOn = DateTime.UtcNow;
                    machine.ConnectionType = data.ConnectionType;
                    db.Entry(machine).Property(x => x.HeartbeatUpdatedOn).IsModified = true;
                    db.Entry(machine).Property(x => x.ConnectionType).IsModified = true;
                }
            }

            await db.SaveChangesAsync();
            await transaction.CommitAsync();
            db.ChangeTracker.Clear();
        }
        catch (Exception ex)
        {
            logger.LogCritical(ex, "HeartBeatCheckTrigger update fail, roll back");
            transaction.Rollback();
            return false;
        }

        return true;
    }

    public async Task<string> GetMachineConnectorType(string chargeBoxId, CancellationToken token = default)
    {
        using var db = await contextFactory.CreateDbContextAsync(token);

        var machine = await db.Machine.Where(x => x.ChargeBoxId == chargeBoxId).FirstOrDefaultAsync(token);
        if (machine == null)
        {
            return null;
        }
        return machine.ConnectorType;
    }

    public async Task SetMachineConnectionType(string chargeBoxId, int v, CancellationToken token = default)
    {
        using var db = await contextFactory.CreateDbContextAsync(token);

        var machine = await db.Machine.Where(x => x.ChargeBoxId == chargeBoxId).FirstOrDefaultAsync(token);
        if (machine == null)
        {
            return;
        }
        machine.ConnectionType = v;
        return;
    }



    public async Task<Dictionary<string, decimal>> GetTransactionPeriodEnergy(int transactionId)
    {
        var parms = new DynamicParameters();
        parms.Add("@TransactionId", transactionId);

        var cmd = """
                SELECT TOP(1) 
                [H00],[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10]
                ,[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20]
                ,[H21],[H22],[H23]
                FROM [dbo].[TransactionPeriodEnergy]
                WHERE TransactionId = @TransactionId
                """;

        using var dbConn = await sqlConnectionFactory.CreateAsync();
        dynamic queryResult = await dbConn.QueryFirstOrDefaultAsync(cmd, parms);
        if (queryResult is not IDictionary<string, object> queryResultPairs)
        {
            return null;
        }

        var toReturn = new Dictionary<string, decimal>();
        for (int hour = 0; hour < 24; hour++)
        {
            var key = GetColName(hour);
            if (queryResultPairs[key] is decimal value)
            {
                toReturn.Add(hour.ToString(), value);
            }
        }

        return toReturn;

    }

    public async Task InsertOrUpdateTransactionPeriodEnergy(int txId, Dictionary<string, decimal> periodEnergy)
    {
        try
        {
            var isExists = await GetTransactionPeriodEnergyExists(txId);
            var param = CreateParameters(txId, periodEnergy);

            if (isExists)
            {
                await UpdateTransactionPeriodEnergy(param);
                return;
            }
            await InsertTransactionPeriodEnergy(param);
        }
        catch (Exception e)
        {
            logger.LogError(e.Message);
        }
        return;

        async Task<bool> GetTransactionPeriodEnergyExists(int txId)
        {
            var param = new DynamicParameters();
            param.Add("@TransactionId", txId, DbType.Int32, ParameterDirection.Input);

            var cmd = """
            SELECT TransactionId
            FROM TransactionPeriodEnergy
            WHERE TransactionId = @TransactionId
            """;
            using var conn = await sqlConnectionFactory.CreateAsync();
            return await conn.QueryFirstOrDefaultAsync<int?>(cmd, param) != null;
        }

        async Task<int> UpdateTransactionPeriodEnergy(DynamicParameters param)
        {
            var cmd = """
            UPDATE TransactionPeriodEnergy
            SET H00 = @H00, H01 = @H01, H02 = @H02, H03 = @H03, H04 = @H04, H05 = @H05, H06 = @H06, H07 = @H07, H08 = @H08,
            H09 = @H09, H10 = @H10, H11 = @H11, H12 = @H12, H13 = @H13, H14 = @H14, H15 = @H15, H16 = @H16, H17 = @H17,
            H18 = @H18, H19 = @H19, H20 = @H20, H21 = @H21, H22 = @H22, H23 = @H23
            WHERE TransactionId = @TransactionId
            """;
            using var conn = await sqlConnectionFactory.CreateAsync();
            return await conn.ExecuteAsync(cmd, param);
        }

        async Task InsertTransactionPeriodEnergy(DynamicParameters param)
        {
            var cmd = """
            INSERT INTO TransactionPeriodEnergy
            (TransactionId, H00, H01, H02, H03, H04, H05, H06, H07, H08, H09, H10, H11, H12, H13, H14, H15, H16, H17, H18, H19, H20, H21, H22, H23)
            VALUES (@TransactionId, @H00, @H01, @H02, @H03, @H04, @H05, @H06, @H07, @H08, @H09, @H10, @H11, @H12, @H13, @H14, @H15, @H16, @H17, @H18, @H19, @H20, @H21, @H22, @H23)
            """;
            using var conn = await sqlConnectionFactory.CreateAsync();
            await conn.ExecuteAsync(cmd, param);
        }

        DynamicParameters CreateParameters(int txId, Dictionary<string, decimal> periodEnergy)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@TransactionId", txId, DbType.Int32, ParameterDirection.Input);

            for (int hour = 0; hour < 24; hour++)
            {
                var key = hour.ToString();
                if (!periodEnergy.ContainsKey(key))
                {
                    parameters.Add(GetColName(hour), 0, DbType.Decimal, ParameterDirection.Input, precision: 18, scale: 4);
                    continue;
                }

                parameters.Add(GetColName(hour), periodEnergy[key], DbType.Decimal, ParameterDirection.Input, precision: 18, scale: 4);
            }
            return parameters;
        }

        string GetColName(int hour)
        {
            return $"@H{hour.ToString("00")}";
        }



    }

	string GetColName(int hour)
	{
		return $"H{hour.ToString("00")}";
	}
}

public record MachineAndCustomerInfo(string MachineId, Guid CustomerId, string CustomerName);
public record StatusNotificationParam(string Id, ConnectorStatus Status);
public record UpdateMachineBasicInfoParam(string ChargeBoxId, Machine machine);
public record UpdateErrofFinishedOnParam(int Id, DateTime finishedOn);