//#define SQLITE

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Diagnostics;
using System.IO;
using System.Threading;
using System.Runtime.InteropServices;
using System.Deployment.Application;
using System.Text.RegularExpressions;
using System.Xml;
using System.Reflection;

namespace Common
{
    enum DatabaseType { MsSql, SQLite };

    class Database
    {
        public delegate void DatabaseInitializedHandler();       
        public static event DatabaseInitializedHandler DatabaseInitialized;
        public delegate void LogMessageHandler(string text);
        public static event LogMessageHandler LogMessage;
        public static string databaseName = "";
        public static string serverName = "";
        public static string sqliteFileName;
        private static Progress progressVar;
        public static DatabaseType type = DatabaseType.MsSql;

        public static bool InitDb()
        {
            bool result = true;

            // Connect to server
            if (type == DatabaseType.SQLite)
            {
                Directory.CreateDirectory(Path.GetDirectoryName(sqliteFileName));
                databaseName = null;
            }
            using (DbConnection connection = GetConnection())
            {
                try
                {
                    connection.Open();
                    connection.Close();
                }
                catch (DbException ex)
                {
                    if ((ex is SqlException) && ((ex as SqlException).Number == 4060))
                    {
                        // database does not exist, try to create it
                        ExecuteNonQuery("CREATE DATABASE " + databaseName);

                        // Need to wait for the new DB startup in order to continue and be sure it can be accessed
                        if (connection is SqlConnection) SqlConnection.ClearAllPools();
                    }
                    else
                    {
                        result = false;
                        Log("When connecting to DB: " + ex.Message);
                        throw new Exception("When connecting to DB: " + ex.Message);
                    }
                }

            }
            // Check if tables with settings exist, if not create them and fill them with default settings
            if (result)
            {
                if (DatabaseInitialized != null)
                    DatabaseInitialized();
            }

            return result;
        }

        public static string GetConnectionString()
        {
            string connectionString = "";
            if (type == DatabaseType.MsSql)
            {
                connectionString = "server=" + serverName + "; Trusted_Connection=yes; database=" + databaseName + "; connection timeout=5";
            }
            else
            if (type == DatabaseType.SQLite)
            {
                connectionString = "Data Source=" + sqliteFileName + "; Version=3";
            }
            return connectionString;
        }

        private static void Log(string message)
        {
            if (LogMessage != null) LogMessage(message);
        }

        public static DbProviderFactory GetFactory()
        {
            DbProviderFactory factory = null;
            if (type == DatabaseType.MsSql) factory = System.Data.SqlClient.SqlClientFactory.Instance;
            else if (type == DatabaseType.SQLite) factory = System.Data.SQLite.SQLiteFactory.Instance;
            return factory;
        }

        public static DbConnection GetConnection()
        {
            DbConnection connection = GetFactory().CreateConnection();
            connection.ConnectionString = GetConnectionString();
            return connection;
        }

        public static DbParameter CreateParameter()
        {
            return GetFactory().CreateParameter();
        }

        private static DbDataAdapter CreateDataAdapter(DbCommand cmd)
        {
            DbDataAdapter adapter;
            adapter = GetFactory().CreateDataAdapter();
            adapter.SelectCommand = cmd;
            return adapter;
        }

        public static bool Connected()
        {
            using (DbConnection connection = GetConnection())
            {                
                try
                {
                    connection.Open();
                    connection.Close();
                    return true;
                }
                catch (DbException)
                {
                    return false;
                }
            }
        }

        public static bool TableExists(string tableName)
        {
            bool result = false;
            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                DataTable table = connection.GetSchema("Tables", new string[] { databaseName, null, tableName });
                result = table.Rows.Count > 0;
            }
            return result;
        }

        public static bool TableColumnExists(string tableName, string columnName)
        {
            bool result = false;
            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                DataTable table = connection.GetSchema("Columns", new string[] { databaseName, null, tableName, columnName});
                result = table.Rows.Count > 0;
            }
            return result;
        }

        public static bool TableIndexExists(string tableName, string indexName)
        {
            bool result = false;
            using (DbConnection connection = GetConnection())
            {
                connection.Open();
                DataTable table = connection.GetSchema("Indexes", new string[] { databaseName, null, tableName });
                foreach (DataRow row in table.Rows)
                {
                    if (row.ItemArray[0].ToString() == indexName)
                    {
                        result = true;
                        break;
                    }
                }
            }
            return result;
        }

        public static void TableDrop(string tableName)
        {
            if (TableExists(tableName)) ExecuteNonQuery("DROP TABLE " + tableName);
        }

        public static void CopyFile(string sourceFileName, string destFileName, Progress progress)
        {
            byte[] buffer = new byte[1024 * 1024]; // 1MB buffer

            using (FileStream source = new FileStream(sourceFileName, FileMode.Open, FileAccess.Read))
            {
                progress.total = 1000;
                using (FileStream dest = new FileStream(destFileName, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    long totalBytes = 0;
                    int currentBlockSize = 0;

                    while ((currentBlockSize = source.Read(buffer, 0, buffer.Length)) > 0)
                    {
                        totalBytes += currentBlockSize;
                        dest.Write(buffer, 0, currentBlockSize);
                        progress.current = (int)((float)totalBytes / source.Length * progress.total);
                        if (progress.terminated) break;
                    }
                }
            }
            if (progress.terminated) File.Delete(destFileName);
        }

        public static void Backup(string backUpPath, Progress progress)
        {
            if (type == DatabaseType.MsSql)
            {
                progressVar = progress;
                progressVar.total = 100;
                using (DbConnection connection = GetConnection())
                {
                    if (connection is SqlConnection)
                    {
                        (connection as SqlConnection).FireInfoMessageEventOnUserErrors = true;
                        (connection as SqlConnection).InfoMessage += OnInfoMessage;
                    }
                    connection.Open();

                    using (DbCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandText = @"BACKUP DATABASE [" + databaseName + "] TO DISK = N'" + backUpPath + @"' WITH FORMAT, NAME = N'Requirements Flowdown Backup', STATS = 1";
                        cmd.CommandTimeout = 5 * 60;
                        cmd.ExecuteNonQuery();
                    }
                }
            } else
            if (type == DatabaseType.SQLite)
            {
                SQLiteConnection.ClearAllPools();
                CopyFile(sqliteFileName, backUpPath, progress);
            } else
                throw new Exception("Operation not supported.");
        }

        public static void Restore(string backUpPath, Progress progress)
        {
            if (type == DatabaseType.MsSql)
            {
                progressVar = progress;
                progressVar.total = 100;
                using (DbConnection connection = GetConnection())
                {
                    if (connection is SqlConnection)
                    {
                        (connection as SqlConnection).FireInfoMessageEventOnUserErrors = true;
                        (connection as SqlConnection).InfoMessage += OnInfoMessage;
                    }
                    connection.Open();

                    using (DbCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandText = "USE master";
                        cmd.ExecuteNonQuery();
                    }

                    using (DbCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandText = @"ALTER DATABASE [" + databaseName + "] SET Single_User WITH Rollback Immediate";
                        cmd.ExecuteNonQuery();
                    }

                    using (DbCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandText = @"RESTORE DATABASE [" + databaseName + "] FROM DISK = N'" + backUpPath + @"' WITH REPLACE, FILE = 1,  NOUNLOAD,  STATS = 1";
                        cmd.CommandTimeout = 5 * 60;
                        cmd.ExecuteNonQuery();
                    }

                    using (DbCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandText = @"ALTER DATABASE [" + databaseName + "] SET Multi_User";
                        cmd.ExecuteNonQuery();
                    }
                }
            } else
            if (type == DatabaseType.SQLite)
            {
                SQLiteConnection.ClearAllPools();
                CopyFile(backUpPath, sqliteFileName, progress);
            } else
                throw new Exception("Operation not supported.");

        }

        static private void OnInfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            foreach (SqlError error in e.Errors)
                if ((error.Class > 10) && (error.Number != 3013))
                {
                    Log(error.ToString());
                    throw new Exception(error.ToString());
                }

            Match match = Regex.Match(e.Message, "(\\d{1,3}) percent");
            if (match.Success)
            {
                string[] split = Regex.Split(e.Message, "(\\d{1,3}) percent");
                progressVar.current = Int32.Parse(split[1]);
            }
        }

        public static DataTable ExecuteQuery(string query)
        {
            DataTable dbTable = null;
            try
            {
                using (DbConnection connection = GetConnection())
                using (DbCommand cmd = connection.CreateCommand())
                {
                    connection.Open();
                    cmd.CommandText = query;
                    using (DbDataAdapter adapter = CreateDataAdapter(cmd))
                    {
                        dbTable = new DataTable();
                        adapter.Fill(dbTable);
                    }
                }
            }
            catch (DbException ex)
            {
                Log("When executing query sql command:\n" + query + "\nException text: " + ex.Message);
                throw new Exception("When executing query sql command:\n" + query + "\nException text: " + ex.Message);
            }
            return dbTable;
        }

        public static void ExecuteTransaction(Action<DbTransaction> callback)
        {
            try
            {
                using (DbConnection connection = GetConnection())
                {
                    connection.Open();
                    using (var transaction = connection.BeginTransaction())
                    {
                        try
                        {
                            callback(transaction);
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            throw new Exception("When executing sql transaction:\nException text: " + ex.Message);
                        }
                    }
                }
            }
            catch (DbException ex)
            {
                Log("When executing sql transaction:\nException text: " + ex.Message);
                throw new Exception("When executing sql transaction:\nException text: " + ex.Message);
            }
        }

        public static void ExecuteTransactionCommand(DbTransaction transaction, string query, List<DbParameter> parameters = null )
        {
            using (DbCommand cmd = transaction.Connection.CreateCommand())
            {
                cmd.Transaction = transaction;
                cmd.CommandText = query;
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                }
                cmd.ExecuteNonQuery();
            }
        }

        public static int ExecuteScalar(string query, List<DbParameter> parameters = null)
        {
            int result = 0;
            try
            {
                using (DbConnection connection = GetConnection())
                using (DbCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = query;
                    if (parameters != null)
                        foreach (DbParameter parameter in parameters)
                            cmd.Parameters.Add(parameter);
                    connection.Open();
                    object resultObject = cmd.ExecuteScalar();
                    if (resultObject is Int64) result = (int)Convert.ToInt64(resultObject);
                    else result = (int)resultObject;
                }
            }
            catch (DbException ex)
            {
                Log("When executing query sql command:\n" + query + "\nException text: " + ex.Message);
                throw new Exception("When executing query sql command:\n" + query + "\nException text: " + ex.Message);
            }
            return result;
        }

        public static DbDataReader ExecuteQueryContent(string query)
        {
            DbDataReader dbReader = null;
            try
            {
                using (DbConnection connection = GetConnection())
                using (DbCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = query;
                    connection.Open();
                    dbReader = cmd.ExecuteReader();
                }
            }
            catch (DbException ex)
            {
                Log("When executing query sql command:\n" + query + "\nException text: " + ex.Message);
                throw new Exception("When executing query sql command:\n" + query + "\nException text: " + ex.Message);
            }
            return dbReader;
        }

        public static void ExecuteNonQuery(string query, DbParameter parameter)
        {
            ExecuteNonQuery(query, new List<DbParameter> { parameter });
        }

        public static void ExecuteNonQuery(string query, List<DbParameter> parameters = null)
        {
            try
            {
                using (DbConnection connection = GetConnection())
                using (DbCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = query;
                    connection.Open();
                    if (parameters != null)
                        foreach (DbParameter param in parameters)
                            cmd.Parameters.Add(param);
                    int rowsAffected = cmd.ExecuteNonQuery();
                }
            }
            catch (DbException ex)
            {
                Log("When executing non query sql command:\n" + query + "\nException text: " + ex.Message);
                throw new Exception("When executing non query sql command:\n" + query + "\nException text: " + ex.Message);
            }
        }

        public static int Insert(string tableName, Dictionary<string, string> values, List<DbParameter> parameters = null)
        {
            string columnsText = '"' + string.Join("\",\"", values.Keys) + '"';
            string valuesText = string.Join(",", values.Values);
            int result = -1;
            if (type == DatabaseType.MsSql)
                result = ExecuteScalar("INSERT INTO " + tableName + " (" + columnsText + ") OUTPUT INSERTED.ID VALUES (" + valuesText + ")", parameters);
            if (type == DatabaseType.SQLite)
                result = ExecuteScalar("INSERT INTO " + tableName + " (" + columnsText + ") VALUES (" + valuesText + "); SELECT last_insert_rowid()", parameters);
            return result;
        }

        public static void Update(string tableName, string where, Dictionary<string, string> values, List<DbParameter> parameters = null)
        {
            string valuesText = "";
            foreach (var value in values)
            {
                if (valuesText != "") valuesText += ", ";
                valuesText += value.Key + '=' + value.Value;
            }
            ExecuteNonQuery("UPDATE " + tableName + " SET " + valuesText + " WHERE " + where, parameters);
        }

        public static string GetPrimaryKeyText()
        {
            string result = "";
            if (type == DatabaseType.MsSql) result = "int IDENTITY(1, 1) NOT NULL";
            else if (type == DatabaseType.SQLite) result = "INTEGER NOT NULL";
            return result;
        }

        public static string GetVarCharMax()
        {
            string result = "";
            if (type == DatabaseType.MsSql) result = "max";
            else if (type == DatabaseType.SQLite) result = "2147483647";
            return result;           
        }

        public static string GetTopLimit(int limit)
        {
            string result = "";
            if (type == DatabaseType.MsSql) result = "TOP " + limit.ToString();
            else if (type == DatabaseType.SQLite) result = "";
            return result;
        }

        public static string GetEndLimit(int limit)
        {
            string result = "";
            if (type == DatabaseType.MsSql) result = "";
            else if (type == DatabaseType.SQLite) result = " LIMIT " + limit.ToString();
            return result;
        }

        public static void CreateIndexOnColumn(string table, string column, string indexName = null)
        {
            if (indexName == null) indexName = table + "_" + column;
            ExecuteNonQuery("CREATE INDEX " + indexName + " ON " + table + " (" + column + ")");
        }
    }

    public class Query
    {
        public string query;
        public List<DbParameter> parameters;

        public Query(string query, List<DbParameter> data)
        {
            this.query = query;
            this.parameters = data;
        }
    }

    public class DatabaseTransaction
    {
        public List<Query> queries = new List<Query>();
        private List<Query> threadQueries = new List<Query>();
        int queriesPerTransaction = 1000;
        Thread dbThread;
        bool threadTerminated;

        public void ExecuteNonQueryNonBlocking(string query)
        {
            ExecuteNonQueryNonBlocking(query, null);
        }

        public void ExecuteNonQueryNonBlocking(string query, List<DbParameter> parameters = null)
        {
            queries.Add(new Query(query, parameters));
            if (queries.Count > queriesPerTransaction)
            {
                ExecuteQueries();
            }
        }

        public void ExecuteQueries()
        {
#if NO_TRANSACTION
            foreach (Query item in queries)
            {
                Database.ExecuteNonQuery(item.query, item.parameters);
            }
#else
            lock (threadQueries)
            {
                threadQueries.AddRange(queries);
                Monitor.Pulse(threadQueries);
            }
#endif
            queries.Clear();
        }

        public void ThreadStart()
        {
            threadTerminated = false;
            dbThread = new Thread(y =>
            {
                List<Query> localQueries;
                while (!threadTerminated)
                {
                    lock (threadQueries)
                    {
                        Monitor.Wait(threadQueries);
                        localQueries = new List<Query>(threadQueries);
                        threadQueries.Clear();
                    }
                    Database.ExecuteTransaction(x =>
                    {
                        foreach (Query item in localQueries)
                        {
                            Database.ExecuteTransactionCommand(x, item.query, item.parameters);
                        }
                    });
                }
            });
            dbThread.Start();
        }

        public void ThreadStop()
        {
            // Finish SQL insert thread
            threadTerminated = true;
            lock (threadQueries)
            {
                Monitor.Pulse(threadQueries);
            }
            dbThread.Join();
        }
    }
}