PLDpro.Web/Services/Storage/StorageMetaDataRepository.cs

using MySqlConnector;
using System.Data;

namespace Pldpro.Web.Services;

public sealed class StorageMetadataRepository : IStorageMetadataRepository
{
    private readonly string _connStr;
    public StorageMetadataRepository(IConfiguration cfg) =>
        _connStr = cfg.GetConnectionString("StorageDb") ?? throw new InvalidOperationException("ConnectionStrings:StorageDb missing");

    public async Task EnsureSchemaAsync(CancellationToken ct = default)
    {
        const string sql = """
        CREATE TABLE IF NOT EXISTS storage_objects (
            id BIGINT NOT NULL AUTO_INCREMENT,
            bucket VARCHAR(63) NOT NULL,
            file_name VARCHAR(255) NOT NULL,
            path VARCHAR(768) NULL,
            s3_key VARCHAR(1024) NOT NULL,
            size BIGINT NULL,
            content_type VARCHAR(255) NULL,
            created_utc DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
            PRIMARY KEY (id),
            UNIQUE KEY uq_bucket_file (bucket, file_name),
            INDEX ix_bucket_path (bucket, path(255))
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """;

        await using var conn = new MySqlConnection(_connStr);
        await conn.OpenAsync(ct);
        await using var cmd = new MySqlCommand(sql, conn);
        await cmd.ExecuteNonQueryAsync(ct);
    }

    public async Task UpsertAsync(string bucket, string fileName, string? path, string key, long? size, string? contentType, CancellationToken ct = default)
    {
        const string sql = """
        INSERT INTO storage_objects (bucket, file_name, path, s3_key, size, content_type)
        VALUES (@bucket, @name, @path, @key, @size, @ct)
        ON DUPLICATE KEY UPDATE
            path = VALUES(path),
            s3_key = VALUES(s3_key),
            size = VALUES(size),
            content_type = VALUES(content_type);
        """;

        await using var conn = new MySqlConnection(_connStr);
        await conn.OpenAsync(ct);
        await using var cmd = new MySqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@bucket", bucket);
        cmd.Parameters.AddWithValue("@name", fileName);
        cmd.Parameters.AddWithValue("@path", (object?)path ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@key", key);
        cmd.Parameters.AddWithValue("@size", (object?)size ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@ct", (object?)contentType ?? DBNull.Value);
        await cmd.ExecuteNonQueryAsync(ct);
    }

    public async Task<StorageObject?> TryGetAsync(string bucket, string fileName, CancellationToken ct = default)
    {
        const string sql = """
        SELECT id, bucket, file_name, path, s3_key, size, content_type, created_utc
        FROM storage_objects
        WHERE bucket = @bucket AND file_name = @name
        LIMIT 1;
        """;

        await using var conn = new MySqlConnection(_connStr);
        await conn.OpenAsync(ct);

        await using var cmd = new MySqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@bucket", bucket);
        cmd.Parameters.AddWithValue("@name", fileName);

        await using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleRow, ct);
        if (await reader.ReadAsync(ct))
        {
            return new StorageObject(
                reader.GetInt64(0),
                reader.GetString(1),
                reader.GetString(2),
                reader.IsDBNull(3) ? null : reader.GetString(3),
                reader.GetString(4),
                reader.IsDBNull(5) ? null : reader.GetInt64(5),
                reader.IsDBNull(6) ? null : reader.GetString(6),
                reader.GetDateTime(7)
            );
        }
        return null;
    }

    public async Task DeleteByKeyAsync(string bucket, string key, CancellationToken ct = default)
    {
        const string sql = """
        DELETE FROM storage_objects
        WHERE bucket = @bucket AND s3_key = @key;
        """;
        await using var conn = new MySqlConnector.MySqlConnection(_connStr);
        await conn.OpenAsync(ct);
        await using var cmd = new MySqlConnector.MySqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@bucket", bucket);
        cmd.Parameters.AddWithValue("@key", key);
        await cmd.ExecuteNonQueryAsync(ct);
    }

}