using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using System.Data;

namespace ZymonicServices;

public static class ZymonicDbSchemaSynchronizer
{
    private static readonly object SyncLock = new();
    private static readonly HashSet<string> CompletedSyncs = new();

    public static void Synchronize(ZymonicDbContext context)
    {
        if (!context.Database.IsRelational())
        {
            return;
        }

        var syncKey = string.Join(
            "|",
            context.Database.ProviderName,
            context.Database.GetConnectionString(),
            ZymonicDbContext.EntityModelVersion);

        lock (SyncLock)
        {
            if (!CompletedSyncs.Add(syncKey))
            {
                return;
            }

            if (!string.Equals(context.Database.ProviderName, "Microsoft.EntityFrameworkCore.Sqlite", StringComparison.Ordinal))
            {
                context.Database.EnsureCreated();
                return;
            }

            context.Database.EnsureCreated();
            SynchronizeTablesAndColumns(context);
            SynchronizeIndexes(context);
        }
    }

    private static void SynchronizeTablesAndColumns(ZymonicDbContext context)
    {
        foreach (var entityType in context.Model.GetEntityTypes())
        {
            var tableName = entityType.GetTableName();
            if (string.IsNullOrWhiteSpace(tableName))
            {
                continue;
            }

            var schema = entityType.GetSchema();
            var storeObject = StoreObjectIdentifier.Table(tableName, schema);
            var properties = entityType.GetProperties()
                .Where(property => property.GetColumnName(storeObject) is not null)
                .ToList();

            if (TableExists(context, tableName))
            {
                var existingColumns = GetExistingColumns(context, tableName);
                foreach (var property in properties)
                {
                    var columnName = property.GetColumnName(storeObject);
                    if (columnName is null || existingColumns.Contains(columnName))
                    {
                        continue;
                    }

                    var sql = $"ALTER TABLE {QuoteIdentifier(tableName)} ADD COLUMN {ColumnDefinition(property, storeObject, forExistingTable: true)}";
                    context.Database.ExecuteSqlRaw(sql);
                }
            }
            else
            {
                var columnDefinitions = properties
                    .Select(property => ColumnDefinition(property, storeObject, forExistingTable: false))
                    .ToList();

                var primaryKey = entityType.FindPrimaryKey();
                if (primaryKey is not null)
                {
                    var primaryKeyColumns = primaryKey.Properties
                        .Select(property => property.GetColumnName(storeObject))
                        .Where(columnName => columnName is not null)
                        .Select(columnName => QuoteIdentifier(columnName!));

                    columnDefinitions.Add($"PRIMARY KEY ({string.Join(", ", primaryKeyColumns)})");
                }

                var sql = $"CREATE TABLE {QuoteIdentifier(tableName)} ({string.Join(", ", columnDefinitions)})";
                context.Database.ExecuteSqlRaw(sql);
            }
        }
    }

    private static void SynchronizeIndexes(ZymonicDbContext context)
    {
        foreach (var entityType in context.Model.GetEntityTypes())
        {
            var tableName = entityType.GetTableName();
            if (string.IsNullOrWhiteSpace(tableName) || !TableExists(context, tableName))
            {
                continue;
            }

            var schema = entityType.GetSchema();
            var storeObject = StoreObjectIdentifier.Table(tableName, schema);
            var existingIndexes = GetExistingIndexes(context, tableName);

            foreach (var index in entityType.GetIndexes())
            {
                var indexName = index.GetDatabaseName(storeObject);
                if (string.IsNullOrWhiteSpace(indexName) || existingIndexes.Contains(indexName))
                {
                    continue;
                }

                var columns = index.Properties
                    .Select(property => property.GetColumnName(storeObject))
                    .Where(columnName => columnName is not null)
                    .Select(columnName => QuoteIdentifier(columnName!))
                    .ToList();

                if (columns.Count == 0)
                {
                    continue;
                }

                var unique = index.IsUnique ? "UNIQUE " : "";
                var sql =
                    $"CREATE {unique}INDEX IF NOT EXISTS {QuoteIdentifier(indexName)} ON {QuoteIdentifier(tableName)} ({string.Join(", ", columns)})";
                context.Database.ExecuteSqlRaw(sql);
            }
        }
    }

    private static string ColumnDefinition(IProperty property, StoreObjectIdentifier storeObject, bool forExistingTable)
    {
        var columnName = property.GetColumnName(storeObject)
            ?? throw new InvalidOperationException($"Column name not found for property {property.Name}.");
        var columnType = property.GetColumnType() ?? property.GetRelationalTypeMapping().StoreType;

        var requiredColumnSuffix = property.IsNullable
            ? ""
            : forExistingTable
                ? $" NOT NULL DEFAULT {DefaultLiteral(property.ClrType)}"
                : " NOT NULL";

        return $"{QuoteIdentifier(columnName)} {columnType}{requiredColumnSuffix}";
    }

    private static string DefaultLiteral(Type clrType)
    {
        var type = Nullable.GetUnderlyingType(clrType) ?? clrType;

        if (type == typeof(string))
        {
            return "''";
        }

        if (type == typeof(bool))
        {
            return "0";
        }

        if (type == typeof(DateTime))
        {
            return "'0001-01-01T00:00:00.0000000'";
        }

        if (type == typeof(DateTimeOffset))
        {
            return "'0001-01-01T00:00:00.0000000+00:00'";
        }

        if (type == typeof(Guid))
        {
            return "'00000000-0000-0000-0000-000000000000'";
        }

        if (type.IsEnum)
        {
            return "0";
        }

        return "0";
    }

    private static bool TableExists(ZymonicDbContext context, string tableName)
    {
        using var command = context.Database.GetDbConnection().CreateCommand();
        command.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table' AND name = @tableName";
        var parameter = command.CreateParameter();
        parameter.ParameterName = "@tableName";
        parameter.Value = tableName;
        command.Parameters.Add(parameter);

        OpenConnectionIfNeeded(context);
        return command.ExecuteScalar() is not null;
    }

    private static HashSet<string> GetExistingColumns(ZymonicDbContext context, string tableName)
    {
        using var command = context.Database.GetDbConnection().CreateCommand();
        command.CommandText = $"PRAGMA table_info({QuoteIdentifier(tableName)})";

        OpenConnectionIfNeeded(context);
        using var reader = command.ExecuteReader();
        var columns = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        while (reader.Read())
        {
            columns.Add(reader.GetString(reader.GetOrdinal("name")));
        }

        return columns;
    }

    private static HashSet<string> GetExistingIndexes(ZymonicDbContext context, string tableName)
    {
        using var command = context.Database.GetDbConnection().CreateCommand();
        command.CommandText = $"PRAGMA index_list({QuoteIdentifier(tableName)})";

        OpenConnectionIfNeeded(context);
        using var reader = command.ExecuteReader();
        var indexes = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        while (reader.Read())
        {
            indexes.Add(reader.GetString(reader.GetOrdinal("name")));
        }

        return indexes;
    }

    private static void OpenConnectionIfNeeded(ZymonicDbContext context)
    {
        var connection = context.Database.GetDbConnection();
        if (connection.State != ConnectionState.Open)
        {
            connection.Open();
        }
    }

    private static string QuoteIdentifier(string identifier)
    {
        return "\"" + identifier.Replace("\"", "\"\"") + "\"";
    }
}