using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace FICMigrator
{
    public partial class MainForm : Form
    {
        private string _connFromString;
        private string _connToString;
        private bool _isFromTested;
        private bool _isToTested;

        public MainForm()
        {
            InitializeComponent();
        }

        private void textbox_TextChanged(object sender, EventArgs e)
        {
            _isFromTested = false;
        }
        private void textboxTo_TextChanged(object sender, EventArgs e)
        {
            _isToTested = false;
        }

        private string SafeString(string str)
        {
            if (string.IsNullOrEmpty(str))
                return str;
            return str.Replace(';', '_');
        }

        private void GetConnFromString()
        {
            _connFromString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=false;User ID={2};Password={3}",
                SafeString(textServerFrom.Text),
                SafeString(textDBFrom.Text),
                SafeString(textUserFrom.Text),
                SafeString(textPassFrom.Text));
        }

        private void GetConnToString()
        {
            _connToString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=false;User ID={2};Password={3}",
                SafeString(textServerTo.Text),
                SafeString(textDBTo.Text),
                SafeString(textUserTo.Text),
                SafeString(textPassTo.Text));
        }

        private void EnableUI(bool flag)
        {
            this.panel1.Enabled = flag;
            this.Cursor = flag ? Cursors.Default : Cursors.AppStarting;
            this.progressBar1.Visible = false;
        }

        private void buttonTestFrom_Click(object sender, EventArgs e)
        {
            EnableUI(false);
            new Thread(new ThreadStart(async delegate
            {
                var result = await Task.Run(new Func<bool>(TestFromConnection));
                if (result)
                {
                    this.BeginInvoke(new Action(delegate
                    {
                        MessageBox.Show(this, "Test successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }));
                }
                this.BeginInvoke(new Action(delegate
                {
                    EnableUI(true);
                }));
            })).Start();
        }

        private void buttonTestTo_Click(object sender, EventArgs e)
        {
            EnableUI(false);
            new Thread(new ThreadStart(async delegate
            {
                var result = await Task.Run(new Func<bool>(TestToConnection));
                if (result)
                {
                    this.BeginInvoke(new Action(delegate
                    {
                        MessageBox.Show(this, "Test successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }));
                }
                this.BeginInvoke(new Action(delegate
                {
                    EnableUI(true);
                }));
            })).Start();
        }

        private void buttonReloadTables_Click(object sender, EventArgs e)
        {
            listView1.Items.Clear();
            EnableUI(false);
            new Thread(LoadTables).Start();
        }

        private void buttonMigrator_Click(object sender, EventArgs e)
        {
            EnableUI(false);
            textMessage.Text = "";
            progressBar1.Visible = true;
            progressBar1.Value = 0;
            progressBar1.Maximum = Definitions.TABLES.Length;

            new Thread(MigrateTables).Start();
        }




        private bool TestFromConnection()
        {
            try
            {
                GetConnFromString();

                using (var conn = new SqlConnection(_connFromString))
                {
                    conn.Open();
                }
                _isFromTested = true;
            }
            catch (Exception ex)
            {
                this.BeginInvoke(new Action<object>(o =>
                {
                    MessageBox.Show(this, "Error occurs:\n" + o, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }), ex);

                return false;
            }

            return true;
        }

        private bool TestToConnection()
        {
            try
            {
                GetConnToString();

                using (var conn = new SqlConnection(_connToString))
                {
                    conn.Open();
                }
                _isToTested = true;
            }
            catch (Exception ex)
            {
                this.BeginInvoke(new Action<object>(o =>
                {
                    MessageBox.Show(this, "Error occurs:\n" + o, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }), ex);

                return false;
            }

            return true;
        }

        private void LoadTables()
        {
            if (!_isFromTested)
            {
                this.BeginInvoke(new Action(delegate
                {
                    MessageBox.Show(this, "Please test connection first.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    EnableUI(true);
                }));
                return;
            }

            try
            {
                var list = new List<ListViewItem>();

                using (var conn = new SqlConnection(_connFromString))
                {
                    conn.Open();

                    foreach (var tb in Definitions.TABLES)
                    {
                        var cmd = conn.CreateCommand();
                        cmd.CommandText = "select 1 from sys.objects where object_id=object_id(@tb) and type='U'";
                        cmd.Parameters.AddWithValue("@tb", tb);

                        var o = cmd.ExecuteScalar();
                        var notexist = (o == null || o == DBNull.Value);

                        list.Add(new ListViewItem(new[] { tb, notexist ? "×" : "" }));
                    }
                }

                var arr = list.ToArray();
                this.BeginInvoke(new Action(delegate
                {
                    this.listView1.Items.AddRange(arr);
                }));

            }
            catch (Exception ex)
            {
                this.BeginInvoke(new Action<object>(o =>
                {
                    MessageBox.Show(this, "Error occurs:\n" + o, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }), ex);
            }
            finally
            {
                this.BeginInvoke(new Action(delegate
                {
                    EnableUI(true);
                }));
            }
        }

        private void MigrateTables()
        {
            var result = TestFromConnection();
            if (!result)
            {
                return;
            }
            result = TestToConnection();
            if (!result)
            {
                return;
            }

            if (!_isFromTested || !_isToTested)
            {
                this.BeginInvoke(new Action(delegate
                {
                    MessageBox.Show(this, "Please test all connections first.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    EnableUI(true);
                }));
                return;
            }

            try
            {
                var exclude_tables = Properties.Settings.Default.ExcludeTables.Split(',');

                // init structure
                var fieldsDict = new Dictionary<string, string[]>();
                using (var conn = new SqlConnection(_connToString))
                {
                    conn.Open();

                    //using (var cmd = conn.CreateCommand())
                    //{
                    //    cmd.CommandText = Properties.Resources.Create;
                    //    cmd.CommandType = CommandType.Text;
                    //    cmd.ExecuteNonQuery();
                    //}

                    var truncate = new StringBuilder();
                    foreach (var tb in Definitions.TABLES)
                    {
                        if (exclude_tables.Any(s => string.Compare(s, tb, true) == 0))
                        {
                            this.BeginInvoke(new Action<object>(o =>
                            {
                                textMessage.Text += string.Format("[{0:HH:mm:ss}] - {1}\r\n\r\n", DateTime.Now, o);
                            }), string.Format("Exclude truncate table \"{0}\"", tb));
                            continue;
                        }

                        using (var cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=@pn";
                            cmd.Parameters.AddWithValue("@pn", tb);

                            using (var reader = cmd.ExecuteReader())
                            {
                                var fields = new List<string>();
                                while (reader.Read())
                                {
                                    fields.Add(reader["COLUMN_NAME"].ToString());
                                }

                                fieldsDict[tb] = fields.ToArray();
                            }
                        }
                        truncate.AppendLine("truncate table " + tb);
                    }

                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = truncate.ToString();
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                }

                // copy data
                this.BeginInvoke(new Action(delegate
                {
                    progressBar1.Style = ProgressBarStyle.Blocks;
                }));

                var bulk = new SqlBulkCopy(_connToString);
                bulk.BatchSize = 1000;
                bulk.BulkCopyTimeout = 600;

                var i = 0;
                foreach (var tb in Definitions.TABLES)
                {
                    if (exclude_tables.Any(s => string.Compare(s, tb, true) == 0))
                    {
                        this.BeginInvoke(new Action<object>(o =>
                        {
                            textMessage.Text += string.Format("[{0:HH:mm:ss}] - {1}\r\n\r\n", DateTime.Now, o);
                        }), string.Format("Exclude table \"{0}\"", tb));
                        continue;
                    }

                    using (var conn = new SqlConnection(_connFromString))
                    {
                        conn.Open();
                        using (var cmd = conn.CreateCommand())
                        {
                            var fields = fieldsDict[tb];

                            cmd.CommandText = string.Format("select [{0}] from [{1}]", string.Join("],[", fields), tb);

                            try
                            {
#if READER
                                var reader = cmd.ExecuteReader();
                                bulk.DestinationTableName = tb;
                                bulk.WriteToServer(reader);
#else
                                var ds = new DataSet();
                                using (var adapter = new SqlDataAdapter(cmd))
                                {
                                    adapter.Fill(ds);
                                }
                                bulk.DestinationTableName = tb;
                                var table = ds.Tables[0];
                                bulk.ColumnMappings.Clear();
                                //foreach (DataColumn col in table.Columns)
                                foreach (var field in fields)
                                {
                                    //bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName.ToUpper());
                                    bulk.ColumnMappings.Add(field, field);
                                }
                                bulk.WriteToServer(table);
#endif
                                this.BeginInvoke(new Action<object>(o =>
                                {
                                    textMessage.Text += string.Format("[{0:HH:mm:ss}] table \"{1}\" copied.\r\n\r\n", DateTime.Now, o);
                                }), tb);
                            }
                            catch (Exception ex)
                            {
                                this.BeginInvoke(new Action<object, object>((o1, o2) =>
                                {
                                    textMessage.Text += string.Format("[{0:HH:mm:ss}] table \"{1}\" - {2}\r\n\r\n", DateTime.Now, o2, o1);
                                }), ex, tb);
                            }
                            finally
                            {
                                this.BeginInvoke(new Action<int>(o =>
                                {
                                    progressBar1.Value = o;
                                }), ++i);

                            }
                        }
                    }
                }
                bulk.Close();

                // change the system params
                using (var conn = new SqlConnection(_connToString))
                {
                    conn.Open();

                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "update SYSTEMPARAMS set PARAMVALUE=@v where PARAMNAME='ExtDBName'";
                        cmd.Parameters.AddWithValue("@v", textDBTo.Text + "EXT"); //Properties.Settings.Default.ExtDBName
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                }


                this.BeginInvoke(new Action(delegate
                {
                    MessageBox.Show(this, "All done.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }));
            }
            catch (Exception ex)
            {
                this.BeginInvoke(new Action<object>(o =>
                {
                    textMessage.Text = "Error occurs:\r\n" + o;
                    MessageBox.Show(this, "Error occurs, please see the detail.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }), ex);
            }
            finally
            {
                this.BeginInvoke(new Action(delegate
                {
                    EnableUI(true);
                }));
            }
        }

    }

}
