using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows;

namespace MySQL_DB_Listen_And_Notify_Demo
{
    public class DbManager : ObservableObject, IDisposable
    {
        bool disposed = false;
        private MySqlConnection _conn;
        private CancellationTokenSource _cancel = new CancellationTokenSource();
        private Task _openAndPollTableChangeTask;

        private bool _isConnected;
        public bool IsConnected
        {
            get { return _isConnected; }
            set { SetField(ref _isConnected, value, "IsConnected"); }
        }

        private DataView _users;
        public DataView Users
        {
            get { return _users; }
            set
            {
                //SetField(ref _users, value, "Users");
                // class ref is the same when we update...
                // call the property change manually each time the value is set!
                _users = value;
                OnPropertyChanged("Users");
            }
        }

        public DataSet DBDataSet = new DataSet();
        public MySql.Data.MySqlClient.MySqlDataAdapter UsersDataAdapter;


        public DbManager(string connectionString)
        {
            _conn = new MySqlConnection(connectionString);

            _openAndPollTableChangeTask = Task.Factory.StartNew(async() =>
            {
                string users_checksum = "";
                string current_checksum = "";
                while (true)
                {
                    if (_cancel.IsCancellationRequested) return;
                    try
                    {
                        _conn.Open();
                        IsConnected = _conn.State == System.Data.ConnectionState.Open;

                        InitDB();

                        MySql.Data.MySqlClient.MySqlCommand cmd = new MySqlCommand("CHECKSUM TABLE `users`", _conn);
                        MySql.Data.MySqlClient.MySqlDataReader reader;

                        while (true)
                        {
                            if (_cancel.IsCancellationRequested) return;
                            if (!_isConnected) throw new Exception("Database disconnection detected...");

                            using (reader = cmd.ExecuteReader())
                            {
                                var t = reader.ReadAsync();
                                await t;
                                if (t.Result)
                                {
                                    current_checksum = reader.GetString(1);
                                }
                            }
                            if (users_checksum != current_checksum)
                            {
                                await UsersDataAdapter.FillAsync(DBDataSet, "users");
                                Users = DBDataSet.Tables["users"].DefaultView;
                                users_checksum = current_checksum;
                            }


                            // wait 5sec or until cancel token is raised
                            try { Task.Delay(5000, _cancel.Token).Wait(_cancel.Token); } catch { }
                        }

                    }
                    catch (Exception ex)
                    {
                        var exxx = ex.Message;
                        IsConnected = false;
                        try { _conn.Close(); } catch { }
                    }

                    // retry in 5sec or quit on cancel token
                    try { Task.Delay(5000, _cancel.Token).Wait(_cancel.Token); } catch { }
                }

            }, _cancel.Token, TaskCreationOptions.LongRunning, TaskScheduler.Default);
        }

        ~DbManager()
        {
            Dispose();
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposed)
            {
                return;
            }

            if (disposing)
            {
                try { _cancel.Cancel(); } catch { }
                try { _conn.Close(); } catch { }
                try { _conn.Dispose(); } catch { }
            }
            disposed = true;
        }

        public static async Task TestDb(string connectionString)
        {
            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();
            }
        }

        public static void ControlBlink(System.Windows.Controls.Control control, int delay, System.Windows.Media.Brush color1, System.Windows.Media.Brush color2, CancellationToken cancel)
        {
            Task.Run(async () =>
            {
                while (!cancel.IsCancellationRequested)
                {
                    await Task.Delay(delay, cancel);
                    if (cancel.IsCancellationRequested) return;
                    App.Current.Dispatcher.Invoke(() => { control.Background = control.Background == color1 ? color2 : color1; });
                }
            });
        }

        public static string GetDatabaseConnectionString()
        {
            return string.Format("Server={0:s};Port={1:0};Database={2:s};Uid={3:s};Pwd={4:s};",
                Properties.Settings.Default.SqlServerHostname,
                Properties.Settings.Default.SqlServerPort,
                Properties.Settings.Default.SqlServerDbName,
                Properties.Settings.Default.SqlServerUserName,
                System.Text.ASCIIEncoding.UTF32.GetString(System.Convert.FromBase64String(Properties.Settings.Default.SqlServerUserPassword)));
        }

        //private async Task<System.Data.DataSet> GetUsers()
        //{
        //    using (MySqlConnection conn = new MySqlConnection(GetDatabaseConnectionString()))
        //    {
        //        using (MySqlDataAdapter adapter = new MySqlDataAdapter())
        //        {
        //            string query = "SELECT * FROM `users` ORDER BY `name`";
        //            adapter.SelectCommand = new MySqlCommand(query, conn);
        //            System.Data.DataSet ds = new System.Data.DataSet();
        //            await adapter.FillAsync(ds);
        //            return ds;
        //        }
        //    }
        //}

        private void InitDB()
        {
            UsersDataAdapter = new MySqlDataAdapter();
            UsersDataAdapter.SelectCommand = new MySqlCommand("SELECT `id`,`name`,`birthdate` FROM `users`", _conn);
            UsersDataAdapter.InsertCommand = new MySqlCommand("INSERT INTO `users` (`name`, `birthdate`) VALUES (@name, @birthdate)", _conn);
            UsersDataAdapter.UpdateCommand = new MySqlCommand("UPDATE `users` SET `name` = @name, `birthdate` = @birthdate WHERE `id` = @id", _conn);
            UsersDataAdapter.DeleteCommand = new MySqlCommand("DELETE `users` WHERE `id` = @id", _conn);
            // activate primary key
            UsersDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        }
    }
}