AVS-Carton-Shipment-Verifier/Repositories/CartonVerificationRepository.cs

using System.Text.RegularExpressions;
using AVSCartonShipmentVerifier.Configuration;
using AVSCartonShipmentVerifier.Data;
using Microsoft.Extensions.Options;
using MySqlConnector;

namespace AVSCartonShipmentVerifier.Repositories;

public sealed class CartonVerificationRepository(
    IVerificationDbConnectionFactory connectionFactory,
    IOptions<CartonVerificationOptions> options) : ICartonVerificationRepository
{
    private static readonly Regex SqlIdentifierRegex = new("^[A-Za-z_][A-Za-z0-9_]*$", RegexOptions.Compiled);

    private readonly IVerificationDbConnectionFactory _connectionFactory = connectionFactory;
    private readonly CartonVerificationOptions _options = options.Value;

    public async Task<CartonVerificationStatus> GetCartonStatusByUniqueNumberAsync(string uniqueNumber, CancellationToken cancellationToken = default)
    {
        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(cancellationToken);

        await using var command = connection.CreateCommand();
        command.CommandText = $"""
            SELECT {QuoteIdentifier(_options.MovedToWarehouseColumnName)}
            FROM {QuoteIdentifier(_options.TableName)}
            WHERE {QuoteIdentifier(_options.QrColumnName)} = @uniqueNumber
            LIMIT 1;
            """;
        command.Parameters.AddWithValue("@uniqueNumber", uniqueNumber);

        var result = await command.ExecuteScalarAsync(cancellationToken);
        if (result is null || result is DBNull)
        {
            return CartonVerificationStatus.NotFound;
        }

        var moved = Convert.ToInt32(result);
        return moved != 0 ? CartonVerificationStatus.AlreadyTransferred : CartonVerificationStatus.PendingTransfer;
    }

    public async Task<ShipmentMarkResult> MarkShipmentVerifiedAsync(string uniqueNumber, CancellationToken cancellationToken = default)
    {
        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(cancellationToken);

        await using var command = connection.CreateCommand();
        command.CommandText = $"""
            UPDATE {QuoteIdentifier(_options.TableName)}
            SET {QuoteIdentifier(_options.MovedToWarehouseColumnName)} = 1,
                {QuoteIdentifier(_options.MovedToWarehouseAtColumnName)} = UTC_TIMESTAMP(6)
            WHERE {QuoteIdentifier(_options.QrColumnName)} = @uniqueNumber
              AND {QuoteIdentifier(_options.MovedToWarehouseColumnName)} = 0;
            """;
        command.Parameters.AddWithValue("@uniqueNumber", uniqueNumber);

        var affectedRows = await command.ExecuteNonQueryAsync(cancellationToken);
        if (affectedRows <= 0)
        {
            return new ShipmentMarkResult(false, null);
        }

        await using var timestampCommand = connection.CreateCommand();
        timestampCommand.CommandText = $"""
            SELECT {QuoteIdentifier(_options.MovedToWarehouseAtColumnName)}
            FROM {QuoteIdentifier(_options.TableName)}
            WHERE {QuoteIdentifier(_options.QrColumnName)} = @uniqueNumber
            LIMIT 1;
            """;
        timestampCommand.Parameters.AddWithValue("@uniqueNumber", uniqueNumber);

        var timestamp = await timestampCommand.ExecuteScalarAsync(cancellationToken);
        return new ShipmentMarkResult(true, ToNullableUtcDateTime(timestamp));
    }

    private static DateTime? ToNullableUtcDateTime(object? value)
    {
        if (value is null || value is DBNull)
        {
            return null;
        }

        var dateTime = Convert.ToDateTime(value);
        return DateTime.SpecifyKind(dateTime, DateTimeKind.Utc);
    }

    public async Task<int> GetAvailableCartonCountAsync(CancellationToken cancellationToken = default)
    {
        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(cancellationToken);

        await using var command = connection.CreateCommand();
        command.CommandText = $"""
            SELECT COUNT(*)
            FROM {QuoteIdentifier(_options.TableName)}
            WHERE {QuoteIdentifier(_options.MovedToWarehouseColumnName)} = 0;
            """;

        var result = await command.ExecuteScalarAsync(cancellationToken);
        return Convert.ToInt32(result);
    }

    public async Task<int> GetTotalMovedCartonCountAsync(CancellationToken cancellationToken = default)
    {
        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(cancellationToken);

        await using var command = connection.CreateCommand();
        command.CommandText = $"""
            SELECT COUNT(*)
            FROM {QuoteIdentifier(_options.TableName)}
            WHERE {QuoteIdentifier(_options.MovedToWarehouseColumnName)} = 1;
            """;

        var result = await command.ExecuteScalarAsync(cancellationToken);
        return Convert.ToInt32(result);
    }

    private static string QuoteIdentifier(string identifier)
    {
        if (!SqlIdentifierRegex.IsMatch(identifier))
        {
            throw new InvalidOperationException($"Invalid SQL identifier configured: '{identifier}'.");
        }

        return $"`{identifier}`";
    }
}