using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Data.Sqlite;
using Migrato.Core;

namespace Migrato.Sqlite;

/// <summary>
///     SQLite implementation of <see cref="IMigrationProvider"/>
/// </summary>
public class SqliteMigrationProvider : IMigrationProvider
{
    private const string VersionTableName = "migrato_version";

    /// <inheritdoc />
    public IDbConnection CreateConnection(string connectionString)
    {
        var connection = new SqliteConnection(connectionString);
        connection.Open();
        return connection;
    }

    /// <inheritdoc />
    public void EnsureVersionTable(IDbConnection connection)
    {
        using var command = connection.CreateCommand();
        command.CommandText = $@"CREATE TABLE IF NOT EXISTS {VersionTableName} (
    version INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    applied_at TEXT NOT NULL
);";
        command.ExecuteNonQuery();
    }

    /// <inheritdoc />
    public IReadOnlyList<AppliedMigration> GetAppliedMigrations(IDbConnection connection)
    {
        using var command = connection.CreateCommand();
        command.CommandText = $"SELECT version, name, applied_at FROM {VersionTableName};";

        var results = new List<AppliedMigration>();
        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            results.Add(new AppliedMigration
            {
                Version = reader.GetInt64(0),
                Name = reader.GetString(1),
                AppliedAt = DateTimeOffset.Parse(reader.GetString(2)),
            });
        }

        return results;
    }

    /// <inheritdoc />
    public void RecordMigration(IDbConnection connection, IDbTransaction transaction, Migration migration)
    {
        using var command = connection.CreateCommand();
        command.Transaction = transaction;
        command.CommandText = $"INSERT INTO {VersionTableName} (version, name, applied_at) VALUES ($version, $name, $appliedAt);";
        AddParameter(command, "$version", migration.Version);
        AddParameter(command, "$name", migration.Name);
        AddParameter(command, "$appliedAt", DateTimeOffset.UtcNow.ToString("O"));
        command.ExecuteNonQuery();
    }

    /// <inheritdoc />
    public void RemoveMigration(IDbConnection connection, IDbTransaction transaction, long version)
    {
        using var command = connection.CreateCommand();
        command.Transaction = transaction;
        command.CommandText = $"DELETE FROM {VersionTableName} WHERE version = $version;";
        AddParameter(command, "$version", version);
        command.ExecuteNonQuery();
    }

    private static void AddParameter(IDbCommand command, string name, object value)
    {
        var parameter = command.CreateParameter();
        parameter.ParameterName = name;
        parameter.Value = value;
        command.Parameters.Add(parameter);
    }
}