using FirebirdSql.Data.FirebirdClient;
using Serilog.Core;
using Serilog.Events;
using System;
using System.Data;
using Serilog.Debugging;
using System.Collections.Generic;
using Serilog.Sinks.Extensions;

namespace Serilog.Sinks.Firebird
{
    internal class FirebirdSink : ILogEventSink
    {
        private readonly IFormatProvider _formatProvider;
        private readonly string _database;
        private readonly string _host;
        private readonly int _port;
        private readonly string _user;
        private readonly string _password;

        private readonly string _tableName = "Logs";
        private readonly Func<string> _prefix = null;

        private string TableName => _tableName.ToUpper();


        public FirebirdSink(IFormatProvider formatProvider, FbConnection connection, string tableName = "Logs", Func<string> prefix = null)
            : this(formatProvider, connection.ConnectionString, tableName, prefix)
        {
        }
        public FirebirdSink(IFormatProvider formatProvider, string connectionString, string tableName = "Logs", Func<string> prefix = null)
            : this(formatProvider, new FbConnectionStringBuilder(connectionString), tableName, prefix)
        {
        }

        public FirebirdSink(IFormatProvider formatProvider, FbConnectionStringBuilder connectionStringBuilder, string tableName = "Logs", Func<string> prefix = null)
            : this(formatProvider, connectionStringBuilder.Database, connectionStringBuilder.DataSource, connectionStringBuilder.Port, connectionStringBuilder.UserID, connectionStringBuilder.Password, tableName, prefix)
        {
        }

        public FirebirdSink(IFormatProvider formatProvider, string database, string host, int port = 3050, string user = null, string password = null, string tableName = "Logs", Func<string> prefix = null)
        {
            _formatProvider = formatProvider;
            _database = database;
            _host = host;
            _port = port <= 0 ? 3050 : port;
            _user = user ?? String.Empty;
            _password = password ?? String.Empty;

            _tableName = String.IsNullOrWhiteSpace(tableName) ? "Logs" : tableName;
            _prefix = prefix;

            InitializeDatabase();
        }

        private void InitializeDatabase()
        {
            using (var conn = GetFirebirdConnection())
            {
                if (!SqlTableExists(TableName))
                    CreateSqlTable(conn);
            }
        }

        private FbConnection GetFirebirdConnection()
        {
            var fbConString = new FbConnectionStringBuilder
            {
                DataSource = _host,
                Port = _port,
                Database = _database,
                UserID = _user,
                Password = _password
            }.ConnectionString;

            var fbConnection = new FbConnection(fbConString);
            fbConnection.Open();

            return fbConnection;
        }

        private bool SqlTableExists(string tableName)
        {
            using (var fbConnection = GetFirebirdConnection())
            {
                try
                {
                    using (var tr = fbConnection.BeginTransaction())
                    {
                        using (var fbCommand = CreateSqlTableExistsCommand(fbConnection))
                        {
                            fbCommand.Transaction = tr;

                            fbCommand.Parameters["@tableName"].Value = tableName;

                            object returnValue = fbCommand.ExecuteScalar();

                            int amount = Convert.ToInt32(returnValue);
                            return amount > 0;
                        }
                    }
                }
                catch (FbException e)
                {
                    SelfLog.WriteLine(e.Message);
                    return false;
                }
            }
        }

        private FbCommand CreateSqlTableExistsCommand(FbConnection fbConnection)
        {
            string fbExistsText = "SELECT COUNT(*) FROM rdb$relations WHERE rdb$relation_name = @tableName";

            var fbCommand = fbConnection.CreateCommand();
            fbCommand.CommandText = fbExistsText;
            fbCommand.CommandType = CommandType.Text;

            fbCommand.Parameters.Add(new FbParameter("@tableName", DbType.String));

            return fbCommand;
        }

        private IEnumerable<FbCommand> CreateSqlAutoIncrementTriggerCommand(FbConnection fbConnection)
        {
            //var termStartCommand = fbConnection.CreateCommand();
            //termStartCommand.CommandText = "SET TERM ^^ ;";
            //termStartCommand.CommandType = CommandType.Text;
            //yield return termStartCommand;

            var createTriggerCommand = fbConnection.CreateCommand();
            createTriggerCommand.CommandText = $"CREATE TRIGGER {TableName}_ID FOR {TableName} ACTIVE BEFORE INSERT POSITION 0 AS begin if( (new.ID is null) or (new.ID = 0) ) then new.ID = gen_id({TableName}_GEN, 1); end";
            createTriggerCommand.CommandType = CommandType.Text;
            yield return createTriggerCommand;

            //var termEndCommand = fbConnection.CreateCommand();
            //termEndCommand.CommandText = "SET TERM ; ^^";
            //termEndCommand.CommandType = CommandType.Text;
            //yield return termEndCommand;
        }

        private IEnumerable<FbCommand> CreateSqlAutoIncrementSequenceCommand(FbConnection fbConnection)
        {
            var createSequenceCommand = fbConnection.CreateCommand();
            createSequenceCommand.CommandText = $"CREATE GENERATOR {TableName}_GEN;";
            createSequenceCommand.CommandType = CommandType.Text;
            yield return createSequenceCommand;

            var setSequenceCommand = fbConnection.CreateCommand();
            setSequenceCommand.CommandText = $"SET GENERATOR {TableName}_GEN TO 0;";
            setSequenceCommand.CommandType = CommandType.Text;
            yield return setSequenceCommand;
        }

        private FbCommand CreateSqlCreateTableCommand(FbConnection fbConnection)
        {
            var colDefs = "ID int not null primary key,";
            colDefs += "\"TIMESTAMP\" timestamp,";
            colDefs += "LEVEL varchar(20),";
            colDefs += "EXCEPTION blob sub_type text,";
            colDefs += "RENDEREDMESSAGE blob sub_type text,";
            colDefs += "PROPERTIES blob sub_type text";

            var fbCreateText = $"CREATE TABLE {TableName} ({colDefs})";

            var createCommand = fbConnection.CreateCommand();
            createCommand.CommandText = fbCreateText;
            createCommand.CommandType = CommandType.Text;

            return createCommand;
        }


        private void CreateSqlTable(FbConnection fbConnection)
        {
            try
            {
                using (var tr = fbConnection.BeginTransaction())
                {
                    using (var fbCommand = CreateSqlCreateTableCommand(fbConnection))
                    {
                        fbCommand.Transaction = tr;
                        fbCommand.ExecuteNonQuery();
                    }

                    foreach (FbCommand command in CreateSqlAutoIncrementSequenceCommand(fbConnection))
                    {
                        command.Transaction = tr;
                        command.ExecuteNonQuery();
                    }

                    foreach (FbCommand command in CreateSqlAutoIncrementTriggerCommand(fbConnection))
                    {
                        command.Transaction = tr;
                        command.ExecuteNonQuery();
                    }

                    tr.Commit();
                }
            }
            catch (FbException e)
            {
                SelfLog.WriteLine(e.Message);
            }
        }

        private FbCommand CreateSqlInsertCommand(FbConnection fbConnection)
        {
            var fbInsertText = "INSERT INTO {0} (\"TIMESTAMP\", LEVEL, EXCEPTION, RENDEREDMESSAGE, PROPERTIES)";
            fbInsertText += " VALUES (@timeStamp, @level, @exception, @renderedMessage, @properties)";

            fbInsertText = String.Format(fbInsertText, TableName);


            var fbCommand = fbConnection.CreateCommand();
            fbCommand.CommandText = fbInsertText;
            fbCommand.CommandType = System.Data.CommandType.Text;

            fbCommand.Parameters.Add(new FbParameter("@timeStamp", DbType.DateTime2));
            fbCommand.Parameters.Add(new FbParameter("@level", DbType.String));
            fbCommand.Parameters.Add(new FbParameter("@exception", DbType.String));
            fbCommand.Parameters.Add(new FbParameter("@renderedMessage", DbType.String));
            fbCommand.Parameters.Add(new FbParameter("@properties", DbType.String));

            return fbCommand;
        }

        private bool WriteLogEvent(LogEvent logEvent)
        {
            using (var fbConnection = GetFirebirdConnection())
            {
                try
                {
                    WriteToDatabase(logEvent, fbConnection);
                    return true;
                }
                catch (FbException e)
                {
                    SelfLog.WriteLine(e.Message);
                    return false;
                }
            }
        }

        private void WriteToDatabase(LogEvent logEvent, FbConnection fbConnection)
        {
            using (var tr = fbConnection.BeginTransaction())
            {
                using (var fbCommand = CreateSqlInsertCommand(fbConnection))
                {
                    string prefix = String.Empty;
                    try
                    {
                        prefix = _prefix == null ? String.Empty : _prefix();
                    }
                    catch (Exception e)
                    {
                        SelfLog.WriteLine(e.Message);
                    }

                    fbCommand.Transaction = tr;

                    fbCommand.Parameters["@timeStamp"].Value = logEvent.Timestamp.DateTime;
                    fbCommand.Parameters["@level"].Value = logEvent.Level.ToString();
                    fbCommand.Parameters["@exception"].Value = logEvent.Exception?.ToString() ?? String.Empty;
                    fbCommand.Parameters["@renderedMessage"].Value = $"{prefix}{logEvent.MessageTemplate.Text}";
                    fbCommand.Parameters["@properties"].Value = logEvent.Properties.Count > 0 ? logEvent.Properties.Json() : String.Empty;

                    fbCommand.ExecuteNonQuery();
                }
                tr.Commit();
            }
        }

        public void Emit(LogEvent logEvent)
        {
            WriteLogEvent(logEvent);
        }
    }
}
