SQLite.cs

using System;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;

namespace DBAClientX;

/// <summary>
/// Provides the SQLite-specific implementation of <see cref="DatabaseClientBase"/> exposing
/// convenience helpers for executing commands, queries, and bulk operations against a local
/// or remote SQLite database file.
/// </summary>
public partial class SQLite : DatabaseClientBase
{
    private static readonly HashSet<string> ConnectionStringSourceKeys = new(StringComparer.OrdinalIgnoreCase)
    {
        "Data Source",
        "DataSource",
        "Filename",
        "FullUri"
    };

    /// <summary>
    /// Default busy timeout used for operational commands when a per-instance value is not overridden.
    /// </summary>
    public const int DefaultBusyTimeoutMs = 5000;

    /// <summary>
    /// Default upper bound for concurrent query execution in <see cref="RunQueriesInParallel"/>.
    /// </summary>
    public const int DefaultMaxParallelQueries = 8;

    private readonly object _syncRoot = new();
    private SqliteConnection? _transactionConnection;
    private SqliteTransaction? _transaction;
    private string? _transactionConnectionString;
    private bool _transactionInitializing;
    private volatile int _busyTimeoutMs = DefaultBusyTimeoutMs;

    /// <summary>
    /// Gets a value indicating whether an explicit transaction scope is currently active.
    /// </summary>
    /// <remarks>
    /// The flag is toggled by the <see cref="BeginTransaction(string)"/>,
    /// <see cref="BeginTransactionAsync(string, CancellationToken)"/> and related overloads
    /// and resets after invoking <see cref="Commit"/>, <see cref="Rollback"/> or their asynchronous counterparts.
    /// </remarks>
    public bool IsInTransaction => _transaction != null;

    /// <summary>
    /// Gets or sets the busy timeout (in milliseconds) applied to operational SQLite connections.
    /// </summary>
    /// <remarks>
    /// Set to <c>0</c> to use provider defaults and disable explicit timeout injection.
    /// </remarks>
    public int BusyTimeoutMs
    {
        get => _busyTimeoutMs;
        set
        {
            if (value < 0)
            {
                throw new ArgumentOutOfRangeException(nameof(value), "BusyTimeoutMs cannot be negative.");
            }

            _busyTimeoutMs = value;
        }
    }

    /// <summary>
    /// Builds a connection string suitable for <see cref="SqliteConnection"/> instances using a database file path.
    /// </summary>
    /// <param name="database">Absolute or relative path of the SQLite database file.</param>
    /// <returns>A connection string that targets <paramref name="database"/>.</returns>
    /// <remarks>
    /// Pooling is disabled by default so short-lived file workflows and <c>:memory:</c> databases do not outlive
    /// the logical connection boundary. Callers that need pooling can still append it explicitly to the returned
    /// connection string.
    /// </remarks>
    public static string BuildConnectionString(string database)
        => BuildConnectionString(database, readOnly: false, busyTimeoutMs: null);

    /// <summary>
    /// Builds a connection string suitable for <see cref="SqliteConnection"/> instances using a database file path.
    /// </summary>
    /// <param name="database">Absolute or relative path of the SQLite database file.</param>
    /// <param name="readOnly">When true, opens the connection in read-only mode.</param>
    /// <param name="busyTimeoutMs">Optional busy timeout applied via the connection string (milliseconds).</param>
    /// <returns>A connection string that targets <paramref name="database"/>.</returns>
    public static string BuildConnectionString(string database, bool readOnly, int? busyTimeoutMs)
    {
        ValidateDatabasePath(database);
        if (busyTimeoutMs.HasValue && busyTimeoutMs.Value < 0)
        {
            throw new ArgumentOutOfRangeException(nameof(busyTimeoutMs), "Busy timeout cannot be negative.");
        }

        var builder = new SqliteConnectionStringBuilder
        {
            DataSource = database,
            Pooling = false
        };

        if (readOnly)
        {
            builder.Mode = SqliteOpenMode.ReadOnly;
        }

        if (busyTimeoutMs.HasValue && busyTimeoutMs.Value > 0)
        {
            builder.DefaultTimeout = Math.Max(1, (int)Math.Ceiling(busyTimeoutMs.Value / 1000d));
        }

        return builder.ConnectionString;
    }

    /// <summary>
    /// Builds a read-only connection string for the supplied SQLite database.
    /// </summary>
    /// <param name="database">Absolute or relative path of the SQLite database file.</param>
    /// <param name="busyTimeoutMs">Optional busy timeout applied via the connection string (milliseconds).</param>
    /// <returns>A read-only connection string.</returns>
    public static string BuildReadOnlyConnectionString(string database, int? busyTimeoutMs = null)
        => BuildConnectionString(database, readOnly: true, busyTimeoutMs: busyTimeoutMs);

    private static string BuildOperationalConnectionString(string database, bool readOnly = false) =>
        BuildConnectionString(database, readOnly, busyTimeoutMs: null);

    private static string NormalizeConnectionString(string connectionString, bool readOnly = false)
    {
        var builder = new SqliteConnectionStringBuilder(TranslateSQLiteFullUri(connectionString));
        if (readOnly &&
            builder.Mode != SqliteOpenMode.Memory &&
            !string.Equals(builder.DataSource, ":memory:", StringComparison.OrdinalIgnoreCase))
        {
            builder.Mode = SqliteOpenMode.ReadOnly;
            builder.Pooling = false;
        }

        return builder.ToString();
    }

    internal static bool IsConnectionString(string connectionStringOrPath)
    {
        foreach (var segment in connectionStringOrPath.Split(';'))
        {
            var separator = segment.IndexOf('=');
            if (separator > 0 && ConnectionStringSourceKeys.Contains(segment.Substring(0, separator).Trim()))
            {
                return true;
            }
        }

        return false;
    }

    internal static string TranslateSQLiteFullUri(string connectionString)
    {
        var builder = new DbConnectionStringBuilder
        {
            ConnectionString = connectionString
        };

        if (TryTranslateSQLiteSourceUri(builder, "FullUri", removeSourceKey: true))
        {
            return builder.ConnectionString;
        }

        foreach (var sourceKey in new[] { "Data Source", "DataSource", "Filename" })
        {
            if (TryTranslateSQLiteSourceUri(builder, sourceKey, removeSourceKey: false))
            {
                return builder.ConnectionString;
            }
        }

        return connectionString;
    }

    private static bool TryTranslateSQLiteSourceUri(DbConnectionStringBuilder builder, string sourceKey, bool removeSourceKey)
    {
        if (!builder.TryGetValue(sourceKey, out var value) || value == null)
        {
            return false;
        }

        var uriText = value.ToString();
        if (Uri.TryCreate(uriText, UriKind.Absolute, out var uri) && uri.IsFile)
        {
            if (removeSourceKey)
            {
                builder.Remove(sourceKey);
            }

            builder["Data Source"] = uri.LocalPath;
            ApplySQLiteFullUriQueryOptions(builder, uri);
            return true;
        }

        if (removeSourceKey)
        {
            builder.Remove(sourceKey);
            builder["Data Source"] = uriText;
            return true;
        }

        return false;
    }

    private static void ApplySQLiteFullUriQueryOptions(DbConnectionStringBuilder builder, Uri uri)
    {
        if (string.IsNullOrEmpty(uri.Query) || uri.Query.Length <= 1)
        {
            return;
        }

        var query = uri.Query.Substring(1).Split(new[] { '&' }, StringSplitOptions.RemoveEmptyEntries);
        foreach (var part in query)
        {
            var separator = part.IndexOf('=');
            var key = separator < 0 ? part : part.Substring(0, separator);
            var value = separator < 0 ? string.Empty : part.Substring(separator + 1);
            ApplySQLiteFullUriOption(
                builder,
                Uri.UnescapeDataString(key),
                Uri.UnescapeDataString(value));
        }
    }

    private static void ApplySQLiteFullUriOption(DbConnectionStringBuilder builder, string key, string value)
    {
        if (string.Equals(key, "mode", StringComparison.OrdinalIgnoreCase))
        {
            if (builder.ContainsKey("Mode"))
            {
                return;
            }

            if (string.Equals(value, "ro", StringComparison.OrdinalIgnoreCase))
            {
                builder["Mode"] = SqliteOpenMode.ReadOnly;
            }
            else if (string.Equals(value, "rw", StringComparison.OrdinalIgnoreCase))
            {
                builder["Mode"] = SqliteOpenMode.ReadWrite;
            }
            else if (string.Equals(value, "rwc", StringComparison.OrdinalIgnoreCase))
            {
                builder["Mode"] = SqliteOpenMode.ReadWriteCreate;
            }
            else if (string.Equals(value, "memory", StringComparison.OrdinalIgnoreCase))
            {
                builder["Mode"] = SqliteOpenMode.Memory;
            }

            return;
        }

        if (!string.Equals(key, "cache", StringComparison.OrdinalIgnoreCase) || builder.ContainsKey("Cache"))
        {
            return;
        }

        if (string.Equals(value, "shared", StringComparison.OrdinalIgnoreCase))
        {
            builder["Cache"] = SqliteCacheMode.Shared;
        }
        else if (string.Equals(value, "private", StringComparison.OrdinalIgnoreCase))
        {
            builder["Cache"] = SqliteCacheMode.Private;
        }
    }

    private int ResolveBusyTimeoutMs(int? busyTimeoutMs)
    {
        var effectiveTimeout = busyTimeoutMs ?? BusyTimeoutMs;
        if (effectiveTimeout < 0)
        {
            throw new ArgumentOutOfRangeException(nameof(busyTimeoutMs), "Busy timeout cannot be negative.");
        }

        return effectiveTimeout;
    }

    private static int? ResolveConnectionBusyTimeout(string connectionString, int? busyTimeoutMs)
    {
        if (busyTimeoutMs.HasValue)
        {
            return busyTimeoutMs;
        }

        var builder = new DbConnectionStringBuilder
        {
            ConnectionString = connectionString
        };
        return builder.ContainsKey("Default Timeout") ||
               builder.ContainsKey("Command Timeout")
            ? 0
            : null;
    }

    private void ApplyBusyTimeout(SqliteConnection connection, int? busyTimeoutMs = null)
    {
        var effectiveTimeout = ResolveBusyTimeoutMs(busyTimeoutMs);
        if (effectiveTimeout <= 0)
        {
            return;
        }

        using var command = connection.CreateCommand();
        command.CommandText = $"PRAGMA busy_timeout = {effectiveTimeout};";
        command.ExecuteNonQuery();
    }

    private async Task ApplyBusyTimeoutAsync(SqliteConnection connection, int? busyTimeoutMs, CancellationToken cancellationToken)
    {
        var effectiveTimeout = ResolveBusyTimeoutMs(busyTimeoutMs);
        if (effectiveTimeout <= 0)
        {
            return;
        }

        using var command = connection.CreateCommand();
        command.CommandText = $"PRAGMA busy_timeout = {effectiveTimeout};";
#if NETSTANDARD2_1_OR_GREATER || NETCOREAPP3_0_OR_GREATER || NET5_0_OR_GREATER
        await AwaitWithCallerCancellationAsync(
            () => command.ExecuteNonQueryAsync(cancellationToken),
            cancellationToken).ConfigureAwait(false);
#else
        await Task.Yield();
        command.ExecuteNonQuery();
#endif
    }

    /// <summary>
    /// Performs a lightweight connectivity test against the supplied SQLite database file.
    /// </summary>
    /// <param name="database">Absolute or relative path of the SQLite database file.</param>
    /// <returns><see langword="true"/> when executing <c>SELECT 1</c> succeeds; otherwise <see langword="false"/>.</returns>
    /// <remarks>
    /// Exceptions are intentionally swallowed so the probe can be used in health-check scenarios. When detailed
    /// error information is required, invoke <see cref="ExecuteScalar(string, string, System.Collections.Generic.IDictionary{string, object?}?, bool, System.Collections.Generic.IDictionary{string, SqliteType}?, System.Collections.Generic.IDictionary{string, System.Data.ParameterDirection}?)"/>
    /// to receive the specific <see cref="Exception"/> that occurred.
    /// </remarks>
    public virtual bool Ping(string database)
    {
        try
        {
            ExecuteScalar(database, "SELECT 1");
            return true;
        }
        catch
        {
            return false;
        }
    }

    /// <summary>
    /// Asynchronously performs a connectivity test against the supplied SQLite database file.
    /// </summary>
    /// <param name="database">Absolute or relative path of the SQLite database file.</param>
    /// <param name="cancellationToken">Token used to cancel the underlying command execution.</param>
    /// <returns><see langword="true"/> when executing <c>SELECT 1</c> succeeds; otherwise <see langword="false"/>.</returns>
    /// <remarks>
    /// Mirrors the synchronous <see cref="Ping(string)"/> implementation while relying on asynchronous I/O to avoid
    /// blocking the caller's thread. Recommended for UI or ASP.NET workloads.
    /// </remarks>
    public virtual async Task<bool> PingAsync(string database, CancellationToken cancellationToken = default)
    {
        try
        {
            await ExecuteScalarAsync(database, "SELECT 1", cancellationToken: cancellationToken).ConfigureAwait(false);
            return true;
        }
        catch
        {
            return false;
        }
    }

    private static void ValidateDatabasePath(string database)
    {
        if (string.IsNullOrWhiteSpace(database))
        {
            throw new ArgumentException("Database path cannot be null or whitespace.", nameof(database));
        }
    }
}