using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Npgsql;
using NpgsqlTypes;
using OptimaJet.Workflow.Core.Entities;

namespace OptimaJet.Workflow.PostgreSQL.Models;

public class WorkflowForm : DbObject<WorkflowFormEntity>
{
    public WorkflowForm(string schemaName, int commandTimeout) : base(schemaName, nameof(WorkflowForm), commandTimeout)
    {
        DBColumns.AddRange([
            new ColumnInfo { Name = nameof(WorkflowFormEntity.Id), IsKey = true, Type = NpgsqlDbType.Uuid },
            new ColumnInfo { Name = nameof(WorkflowFormEntity.Name) },
            new ColumnInfo { Name = nameof(WorkflowFormEntity.Version), Type = NpgsqlDbType.Integer },
            new ColumnInfo { Name = nameof(WorkflowFormEntity.CreationDate), Type = NpgsqlDbType.Timestamp },
            new ColumnInfo { Name = nameof(WorkflowFormEntity.UpdatedDate), Type = NpgsqlDbType.Timestamp },
            new ColumnInfo { Name = nameof(WorkflowFormEntity.Definition) },
            new ColumnInfo { Name = nameof(WorkflowFormEntity.Lock), Type = NpgsqlDbType.Integer }
        ]);
    }

    public async Task<List<string>> GetFormNamesAsync(NpgsqlConnection connection)
    {
        string query = $"SELECT DISTINCT \"{nameof(WorkflowFormEntity.Name)}\" FROM {ObjectName}";
        WorkflowFormEntity[] result = await SelectAsync(connection, query).ConfigureAwait(false);
        return result.Select(f => f.Name).ToList();
    }

    public async Task<WorkflowFormEntity> GetFormAsync(NpgsqlConnection connection, string name, int? version = null)
    {
        string query = version is null
            ? $"""
               SELECT *
               FROM {ObjectName}
               WHERE "{nameof(WorkflowFormEntity.Name)}" = @name
               ORDER BY "{nameof(WorkflowFormEntity.Version)}" DESC
               LIMIT 1
               """
            : $"""
               SELECT *
               FROM {ObjectName}
               WHERE "{nameof(WorkflowFormEntity.Name)}" = @name
               AND "{nameof(WorkflowFormEntity.Version)}" = @version
               """;

        var parameters = new List<NpgsqlParameter> { CreateParameter(nameof(WorkflowFormEntity.Name), "name", name) };

        if (version.HasValue)
        {
            parameters.Add(CreateParameter(nameof(WorkflowFormEntity.Version), "version", version));
        }

        WorkflowFormEntity[] forms = await SelectAsync(connection, query, parameters.ToArray()).ConfigureAwait(false);
        return forms.FirstOrDefault();
    }

    public async Task<List<int>> GetFormVersionsAsync(NpgsqlConnection connection, string name)
    {
        string query =
            $"SELECT \"{nameof(WorkflowFormEntity.Version)}\" FROM {ObjectName} WHERE \"{nameof(WorkflowFormEntity.Name)}\" = @name";

        NpgsqlParameter[] parameters =
        [
            CreateParameter(nameof(WorkflowFormEntity.Name), "name", name)
        ];

        WorkflowFormEntity[] forms = await SelectAsync(connection, query, parameters).ConfigureAwait(false);
        return forms.Select(f => f.Version).ToList();
    }

    public async Task<WorkflowFormEntity> CreateNewFormVersionAsync(NpgsqlConnection connection, DateTime creationDate, string name,
        string defaultDefinition, int? version = null)
    {
        if (connection.State != ConnectionState.Open)
        {
            await connection.OpenAsync().ConfigureAwait(false);
        }

        await using NpgsqlTransaction transaction = connection.BeginTransaction();
        string selectLastDefinitionQuery = $"""
                                            SELECT "{nameof(WorkflowFormEntity.Version)}", "{nameof(WorkflowFormEntity.Definition)}"
                                            FROM {ObjectName}
                                            WHERE "{nameof(WorkflowFormEntity.Name)}" = @name
                                            ORDER BY "{nameof(WorkflowFormEntity.Version)}" DESC
                                            LIMIT 1
                                            FOR UPDATE
                                            """;

        WorkflowFormEntity[] formEntries = await SelectAsync(connection, selectLastDefinitionQuery, transaction,
            CreateParameter(nameof(WorkflowFormEntity.Name), "name", name)).ConfigureAwait(false);

        WorkflowFormEntity lastFormEntry = formEntries.FirstOrDefault();
        string definition = lastFormEntry?.Definition ?? defaultDefinition;
        int newVersion = lastFormEntry != null ? lastFormEntry.Version + 1 : 0;

        if (version is not null)
        {
            string selectSpecificDefinitionQuery = $"""
                                                    SELECT "{nameof(WorkflowFormEntity.Definition)}"
                                                    FROM {ObjectName}
                                                    WHERE "{nameof(WorkflowFormEntity.Name)}" = @name
                                                    AND "{nameof(WorkflowFormEntity.Version)}" = @version
                                                    LIMIT 1
                                                    FOR UPDATE
                                                    """;

            formEntries = await SelectAsync(connection, selectSpecificDefinitionQuery, transaction,
                CreateParameter(nameof(WorkflowFormEntity.Name), "name", name),
                CreateParameter(nameof(WorkflowFormEntity.Version), "version", version.Value))
                .ConfigureAwait(false);
            WorkflowFormEntity specificFormEntry = formEntries.FirstOrDefault();
            if (specificFormEntry is null)
            {
                return null;
            }

            definition = specificFormEntry.Definition;
        }

        string insertQuery = $"""
                              INSERT INTO {ObjectName} ("{nameof(WorkflowFormEntity.Id)}", "{nameof(WorkflowFormEntity.Name)}",
                              "{nameof(WorkflowFormEntity.Version)}", "{nameof(WorkflowFormEntity.CreationDate)}",
                              "{nameof(WorkflowFormEntity.UpdatedDate)}", "{nameof(WorkflowFormEntity.Definition)}",
                              "{nameof(WorkflowFormEntity.Lock)}")
                              VALUES (gen_random_uuid(), @name, @version, @creationDate, @creationDate, @definition, 0)
                              RETURNING *;
                              """;

        NpgsqlParameter[] parameters =
        [
            CreateParameter(nameof(WorkflowFormEntity.Name), "name", name),
            CreateParameter(nameof(WorkflowFormEntity.Version), "version", newVersion),
            CreateParameter(nameof(WorkflowFormEntity.CreationDate), "creationDate", creationDate),
            CreateParameter(nameof(WorkflowFormEntity.Definition), "definition", definition)
        ];

        WorkflowFormEntity[] result = await SelectAsync(connection, insertQuery, transaction, parameters).ConfigureAwait(false);
        
        if (result.Length != 1)
        {
            throw new Exception($"There was an error inserting {ObjectName} to the database");
        }

        await transaction.CommitAsync().ConfigureAwait(false);
        return result.First();
    }


    public async Task<WorkflowFormEntity> CreateNewFormIfNotExistsAsync(NpgsqlConnection connection, DateTime creationDate, string name,
        string defaultDefinition)
    {
        string query = $"""
                        INSERT INTO {ObjectName} ("{nameof(WorkflowFormEntity.Id)}", "{nameof(WorkflowFormEntity.Name)}",
                        "{nameof(WorkflowFormEntity.Version)}", "{nameof(WorkflowFormEntity.CreationDate)}",
                        "{nameof(WorkflowFormEntity.UpdatedDate)}", "{nameof(WorkflowFormEntity.Definition)}",
                        "{nameof(WorkflowFormEntity.Lock)}")
                        SELECT gen_random_uuid(), @name, 0, @date, @date, @definition, 0
                        WHERE NOT EXISTS (
                            SELECT 1 FROM {ObjectName} WHERE "{nameof(WorkflowFormEntity.Name)}" = @name
                        );

                        SELECT *
                        FROM {ObjectName}
                        WHERE "{nameof(WorkflowFormEntity.Name)}" = @name
                        ORDER BY "{nameof(WorkflowFormEntity.Version)}" DESC
                        LIMIT 1;
                        """;

        NpgsqlParameter[] parameters =
        [
            CreateParameter(nameof(WorkflowFormEntity.Name), "name", name),
            CreateParameter(nameof(WorkflowFormEntity.CreationDate), "date", creationDate),
            CreateParameter(nameof(WorkflowFormEntity.Definition), "definition", defaultDefinition)
        ];

        WorkflowFormEntity[] forms = await SelectAsync(connection, query, parameters).ConfigureAwait(false);
        return forms.FirstOrDefault();
    }

    public async Task<int> UpdateFormAsync(NpgsqlConnection connection, string name, int version, long oldLock, long newLock,
        string definition, DateTime updatedDate)
    {
        string query = $"""
                        UPDATE {ObjectName}
                        SET "{nameof(WorkflowFormEntity.Definition)}"  = @definition,
                            "{nameof(WorkflowFormEntity.Lock)}"        = @newLock,
                            "{nameof(WorkflowFormEntity.UpdatedDate)}" = @date
                        WHERE "{nameof(WorkflowFormEntity.Name)}" = @name
                          AND "{nameof(WorkflowFormEntity.Version)}" = @version
                          AND "{nameof(WorkflowFormEntity.Lock)}" = @oldLock
                        """;

        NpgsqlParameter[] parameters =
        [
            CreateParameter(nameof(WorkflowFormEntity.Definition), "definition", definition),
            CreateParameter(nameof(WorkflowFormEntity.Lock), "oldLock", oldLock),
            CreateParameter(nameof(WorkflowFormEntity.Lock), "newLock", newLock),
            CreateParameter(nameof(WorkflowFormEntity.Name), "name", name),
            CreateParameter(nameof(WorkflowFormEntity.Version), "version", version),
            CreateParameter(nameof(WorkflowFormEntity.UpdatedDate), "date", updatedDate)
        ];

        return await ExecuteCommandNonQueryAsync(connection, query, parameters).ConfigureAwait(false);
    }

    public async Task DeleteFormVersionAsync(NpgsqlConnection connection, string name, int version)
    {
        string query =
            $"DELETE FROM {ObjectName} WHERE \"{nameof(WorkflowFormEntity.Name)}\" = @name AND \"{nameof(WorkflowFormEntity.Version)}\" = @version";

        NpgsqlParameter[] parameters =
        [
            CreateParameter(nameof(WorkflowFormEntity.Name), "name", name),
            CreateParameter(nameof(WorkflowFormEntity.Version), "version", version)
        ];

        await ExecuteCommandNonQueryAsync(connection, query, parameters).ConfigureAwait(false);
    }

    public async Task DeleteFormAsync(NpgsqlConnection connection, string name)
    {
        await DeleteByAsync(connection, f => f.Name, name).ConfigureAwait(false);
    }

    private NpgsqlParameter CreateParameter(string columnName, string parameterName, object value)
    {
        NpgsqlDbType type = DBColumns.Find(c => c.Name == columnName).Type;
        return new NpgsqlParameter(parameterName, type) { Value = value };
    }
}
