﻿using System;
using System.Threading.Tasks;

using Carbon.Data;
using Carbon.Data.Sequences;
using Carbon.Data.Sql;

using Dapper;

namespace Carbon.Rds.Management
{
    public class DbManager
    {
        private readonly IDbContext context;

        public DbManager(IDbContext context)
        {
            this.context = context ?? throw new ArgumentNullException(nameof(context));
        }

        
        public async Task DropAsync(string name)
        {
            if (name is null)
                throw new ArgumentNullException(nameof(name));

            using (var connection = await context.GetConnectionAsync())
            {
                await connection.ExecuteAsync($"DROP DATABASE IF EXISTS `{name}`;");
            }
        }

        public async Task CreateAsync(string name, params TableInfo[] tables)
        {
            if (name is null)
                throw new ArgumentNullException(nameof(name));

            if (tables is null)
                throw new ArgumentNullException(nameof(tables));

            var createDatabaseCommand = new CreateDatabaseCommand(name, new CreateDatabaseOptions(
                characterSet: "utf8mb4"
            ), context.SqlAdapter);

            using (var connection = await context.GetConnectionAsync())
            {
                await connection.ExecuteAsync(createDatabaseCommand.CommandText);

                connection.ChangeDatabase(name);

                foreach (var table in tables)
                {
                    var command = new CreateTableCommand(table, null, context.SqlAdapter);

                    try
                    {
                        await connection.ExecuteAsync(command.CommandText);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("Error creating table: " + command.CommandText, ex);
                    }
                }
            }
        }

        public async Task CreateAsync(string name, DbSchema schema)
        {
            if (string.IsNullOrEmpty(name))
                throw new ArgumentException("Required", nameof(name));

            if (schema is null)
                throw new ArgumentNullException(nameof(schema));

            using (var connection = await context.GetConnectionAsync())
            {
                await connection.ExecuteAsync($"DROP DATABASE IF EXISTS `{name}`;");
                await connection.ExecuteAsync(new CreateDatabaseCommand(name, new CreateDatabaseOptions(
                    characterSet: "utf8mb4"
                ), context.SqlAdapter).CommandText);

                connection.ChangeDatabase(name);

                foreach (var table in schema.Tables)
                {
                    var command = new CreateTableCommand(table, null, context.SqlAdapter);

                    try
                    {
                        await connection.ExecuteAsync(command.CommandText);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("Error creating table: " + command.CommandText, ex);
                    }
                }

                // Create the sequence & function
                if (true)
                {
                    var command = new CreateTableCommand(
                        table: TableInfo.Create(DatasetInfo.Get<SequenceInfo>(), context.Types),
                        options: default,
                        sqlAdapter: context.SqlAdapter
                    );

                    await connection.ExecuteAsync(command.CommandText); // Create the table

                    await connection.ExecuteAsync(nextvalFunction); // create the function
                }
            }
        }

        // note: requires Allow User Variables=True to be set on the connection string

        // match postgresql functions: nextval(), currval() and setval()

        // For SET, either = or := can be used as the assignment operator.

        const string nextvalFunction =
@"CREATE FUNCTION `nextval`(vname varchar(100)) RETURNS BIGINT(20)
BEGIN
UPDATE Sequences
SET currentValue = @currentValue := (currentValue + increment)
WHERE name = vname;
RETURN @currentValue;
END";
    }
}