Program.cs

using System.Data;
using System.Data.Common;
using System.Globalization;
using System.Linq.Expressions;
using System.Text.Json;
using Dapper;
using SwiftSQL.Dialects;
using SwiftSQL.Metadata;
using SwiftSQL.Migrations;
using SwiftSQL.Session;
using SwiftSQL.Sql;

namespace SwiftSQL;

/// <summary>
/// Static entry point for SwiftSQL operations. Does not own or manage connections.
/// </summary>
public static partial class Orm
{
    static Orm()
    {
        DefaultTypeMap.MatchNamesWithUnderscores = true;
    }

    /// <summary>
    /// Creates a lightweight session wrapper around an existing connection and dialect.
    /// </summary>
    public static OrmSession Session(IDbConnection connection, ISqlDialect dialect,
        IDbTransaction? transaction = null) =>
        new(connection, dialect, transaction);

    /// <summary>
    /// Inserts an entity. Returns affected rows.
    /// </summary>
    public static Task<int> InsertAsync<T>(
        IDbConnection connection,
        T entity,
        ISqlDialect dialect,
        IDbTransaction? transaction = null,
        CancellationToken cancellationToken = default) where T : class
    {
        var map = EntityMetadata.For<T>();
        var sql = CrudSqlGenerator.Insert(map, dialect);
        var parameters = BuildParameters(entity, map);
        return connection.ExecuteAsync(new CommandDefinition(sql, parameters, transaction,
            cancellationToken: cancellationToken));
    }

    /// <summary>
    /// Updates an entity by its primary key. Returns affected rows.
    /// </summary>
    public static Task<int> UpdateAsync<T>(
        IDbConnection connection,
        T entity,
        ISqlDialect dialect,
        IDbTransaction? transaction = null,
        CancellationToken cancellationToken = default) where T : class
    {
        var map = EntityMetadata.For<T>();
        var sql = CrudSqlGenerator.Update(map, dialect);
        var parameters = BuildParameters(entity, map);
        return connection.ExecuteAsync(new CommandDefinition(sql, parameters, transaction,
            cancellationToken: cancellationToken));
    }

    /// <summary>
    /// Deletes an entity by its primary key. Returns affected rows.
    /// </summary>
    public static Task<int> DeleteAsync<T>(
        IDbConnection connection,
        T entity,
        ISqlDialect dialect,
        IDbTransaction? transaction = null,
        CancellationToken cancellationToken = default) where T : class
    {
        var map = EntityMetadata.For<T>();
        var sql = CrudSqlGenerator.Delete(map, dialect);
        var parameters = BuildParameters(entity, map);
        return connection.ExecuteAsync(new CommandDefinition(sql, parameters, transaction,
            cancellationToken: cancellationToken));
    }

    /// <summary>
    /// Gets an entity by primary key.
    /// </summary>
    public static Task<T?> GetAsync<T>(
        IDbConnection connection,
        object id,
        ISqlDialect dialect,
        IDbTransaction? transaction = null,
        CancellationToken cancellationToken = default) where T : class
    {
        var map = EntityMetadata.For<T>();
        var sql = CrudSqlGenerator.SelectByKey(map, dialect);
        var command = new CommandDefinition(sql, new { id = NormalizeKey(id) }, transaction,
            cancellationToken: cancellationToken);

        if (!HasJsonColumns(map))
        {
            return connection.QuerySingleOrDefaultAsync<T>(command);
        }

        return QuerySingleWithJsonAsync<T>(connection, command, map, cancellationToken);
    }

    /// <summary>
    /// Gets an entity by primary key or returns a default instance without writing to the database.
    /// </summary>
    /// <param name="connection">Open database connection.</param>
    /// <param name="primaryKey">Primary key value.</param>
    /// <param name="defaultFactory">Factory used when the entity is not found.</param>
    /// <param name="dialect">SQL dialect.</param>
    /// <param name="transaction">Optional transaction.</param>
    /// <param name="cancellationToken">Cancellation token.</param>
    public static async Task<T> GetOrDefaultAsync<T>(
        IDbConnection connection,
        object primaryKey,
        Func<T> defaultFactory,
        ISqlDialect dialect,
        IDbTransaction? transaction = null,
        CancellationToken cancellationToken = default) where T : class
    {
        ArgumentNullException.ThrowIfNull(connection);
        ArgumentNullException.ThrowIfNull(defaultFactory);

        var entity = await GetAsync<T>(connection, primaryKey, dialect, transaction, cancellationToken);
        if (entity is not null)
        {
            return entity;
        }

        return defaultFactory();
    }

    /// <summary>
    /// Queries entities using a minimal expression filter.
    /// </summary>
    public static async Task<IReadOnlyList<T>> QueryAsync<T>(
        IDbConnection connection,
        Expression<Func<T, bool>> predicate,
        ISqlDialect dialect,
        IDbTransaction? transaction = null,
        CancellationToken cancellationToken = default) where T : class
    {
        var map = EntityMetadata.For<T>();
        var baseSql = CrudSqlGenerator.SelectAll(map, dialect);
        var where = ExpressionSqlTranslator.TranslateLambda(predicate, map, dialect);

        var sql = $"{baseSql} WHERE {where.Sql};";
        var command = new CommandDefinition(sql, where.Parameters, transaction, cancellationToken: cancellationToken);

        if (!HasJsonColumns(map))
        {
            var result = await connection.QueryAsync<T>(command);
            return result.AsList();
        }

        return await QueryWithJsonAsync<T>(connection, command, map, cancellationToken);
    }

    /// <summary>
    /// Performs an INSERT-or-UPDATE using the dialect's upsert support.
    /// </summary>
    public static Task<int> InsertOrUpdateAsync<T>(
        IDbConnection connection,
        T entity,
        ISqlDialect dialect,
        IDbTransaction? transaction = null,
        CancellationToken cancellationToken = default) where T : class
    {
        var map = EntityMetadata.For<T>();
        var sql = UpsertSqlGenerator.Upsert(map, dialect);
        var parameters = BuildParameters(entity, map);
        return connection.ExecuteAsync(new CommandDefinition(sql, parameters, transaction,
            cancellationToken: cancellationToken));
    }

    /// <summary>
    /// Generates CREATE TABLE IF NOT EXISTS SQL for an entity.
    /// </summary>
    public static string GetCreateTableIfNotExistsSql<T>(ISqlDialect dialect) where T : class
    {
        var map = EntityMetadata.For<T>();
        return CreateTableSqlGenerator.CreateTableIfNotExists(map, dialect);
    }

    /// <summary>
    /// Creates the table for an entity if it does not already exist. Returns affected rows.
    /// </summary>
    public static Task<int> CreateTableIfNotExistsAsync<T>(
        IDbConnection connection,
        ISqlDialect dialect,
        IDbTransaction? transaction = null,
        CancellationToken cancellationToken = default) where T : class
    {
        var sql = GetCreateTableIfNotExistsSql<T>(dialect);
        return connection.ExecuteAsync(new CommandDefinition(sql, transaction: transaction,
            cancellationToken: cancellationToken));
    }

    /// <summary>
    /// Ensures the schema migrations table exists.
    /// </summary>
    public static Task EnsureMigrationsTableAsync(
        IDbConnection connection,
        ISqlDialect dialect,
        IDbTransaction? transaction = null,
        CancellationToken cancellationToken = default) =>
        MigrationRunner.EnsureSchemaTableAsync(connection, dialect, transaction, cancellationToken);

    /// <summary>
    /// Applies pending migrations in ascending version order.
    /// </summary>
    public static Task MigrateAsync(
        IDbConnection connection,
        ISqlDialect dialect,
        IEnumerable<Migration> migrations,
        IDbTransaction? transaction = null,
        CancellationToken cancellationToken = default) =>
        MigrationRunner.ApplyAsync(connection, dialect, migrations, transaction, cancellationToken);

    private static bool HasJsonColumns(EntityMap map) => map.MappedProperties.Any(p => p.IsJson);

    private static object NormalizeKey(object id)
    {
        return id switch
        {
            ulong value => unchecked((long)value),
            uint value => unchecked((int)value),
            ushort value => unchecked((short)value),
            byte value => unchecked((sbyte)value),
            _ => id
        };
    }

    private static object BuildParameters<T>(T entity, EntityMap map) where T : class
    {
        if (!HasJsonColumns(map))
        {
            return entity;
        }

        var parameters = new DynamicParameters();
        foreach (var p in map.MappedProperties)
        {
            var value = p.Property.GetValue(entity);
            if (p.IsJson)
            {
                if (value is null)
                {
                    parameters.Add(p.Property.Name, null);
                }
                else
                {
                    var json = JsonSerializer.Serialize(value, p.Property.PropertyType, p.JsonOptions);
                    parameters.Add(p.Property.Name, json);
                }

                continue;
            }

            parameters.Add(p.Property.Name, value);
        }

        return parameters;
    }

    private static async Task<T?> QuerySingleWithJsonAsync<T>(
        IDbConnection connection,
        CommandDefinition command,
        EntityMap map,
        CancellationToken cancellationToken) where T : class
    {
        using var reader = await connection.ExecuteReaderAsync(command) as DbDataReader
                           ?? throw new InvalidOperationException("ExecuteReaderAsync did not return a DbDataReader.");
        var items = await ReadEntitiesAsync<T>(reader, map, cancellationToken);
        return items.Count == 0 ? null : items[0];
    }

    private static async Task<IReadOnlyList<T>> QueryWithJsonAsync<T>(
        IDbConnection connection,
        CommandDefinition command,
        EntityMap map,
        CancellationToken cancellationToken) where T : class
    {
        using var reader = await connection.ExecuteReaderAsync(command) as DbDataReader
                           ?? throw new InvalidOperationException("ExecuteReaderAsync did not return a DbDataReader.");
        return await ReadEntitiesAsync<T>(reader, map, cancellationToken);
    }

    private static async Task<List<T>> ReadEntitiesAsync<T>(
        DbDataReader reader,
        EntityMap map,
        CancellationToken cancellationToken) where T : class
    {
        var list = new List<T>();
        var ordinals = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
        for (var i = 0; i < reader.FieldCount; i++)
        {
            ordinals[reader.GetName(i)] = i;
        }

        while (await reader.ReadAsync(cancellationToken))
        {
            var entity = Activator.CreateInstance<T>();
            foreach (var p in map.MappedProperties)
            {
                if (!ordinals.TryGetValue(p.ColumnName, out var ordinal))
                {
                    continue;
                }

                object? raw = reader.IsDBNull(ordinal) ? null : reader.GetValue(ordinal);

                if (p.IsJson)
                {
                    if (raw is null)
                    {
                        p.Property.SetValue(entity, null);
                        continue;
                    }

                    var json = raw as string ?? Convert.ToString(raw, CultureInfo.InvariantCulture);
                    var value = json is null
                        ? null
                        : JsonSerializer.Deserialize(json, p.Property.PropertyType, p.JsonOptions);
                    p.Property.SetValue(entity, value);
                    continue;
                }

                var converted = ConvertValue(raw, p.Property.PropertyType);
                if (converted is not null || !p.Property.PropertyType.IsValueType ||
                    Nullable.GetUnderlyingType(p.Property.PropertyType) is not null)
                {
                    p.Property.SetValue(entity, converted);
                }
            }

            list.Add(entity);
        }

        return list;
    }

    private static object? ConvertValue(object? value, Type targetType)
    {
        if (value is null || value is DBNull)
        {
            return null;
        }

        var underlying = Nullable.GetUnderlyingType(targetType) ?? targetType;
        if (underlying.IsInstanceOfType(value))
        {
            return value;
        }

        if (underlying.IsEnum)
        {
            if (value is string s)
            {
                return Enum.Parse(underlying, s, true);
            }

            return Enum.ToObject(underlying, value);
        }

        if (underlying == typeof(Guid))
        {
            return value is Guid g
                ? g
                : Guid.Parse(Convert.ToString(value, CultureInfo.InvariantCulture) ?? string.Empty);
        }

        if (underlying == typeof(DateTimeOffset))
        {
            if (value is DateTimeOffset dto)
            {
                return dto;
            }

            if (value is DateTime dt)
            {
                return new DateTimeOffset(dt);
            }

            return DateTimeOffset.Parse(Convert.ToString(value, CultureInfo.InvariantCulture) ?? string.Empty,
                CultureInfo.InvariantCulture);
        }

        if (underlying == typeof(DateTime))
        {
            if (value is DateTime dt)
            {
                return dt;
            }

            return DateTime.Parse(Convert.ToString(value, CultureInfo.InvariantCulture) ?? string.Empty,
                CultureInfo.InvariantCulture);
        }

        return Convert.ChangeType(value, underlying, CultureInfo.InvariantCulture);
    }
}