using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;
using Tango.BL;
using Tango.BL.Entities;
using Tango.Core.Commands;
using Tango.MachineStudio.Common.Notifications;
using Tango.MachineStudio.UsersAndRoles.Navigation;
using Tango.MachineStudio.UsersAndRoles.Providers;
using Tango.SharedUI;

namespace Tango.MachineStudio.UsersAndRoles.ViewModels
{
    public class MainViewVM : ViewModel
    {
        private ObservablesContext _organizationsContext;
        private ObservablesContext _manageContext;
        private ObservablesContext _userContext;
        private UsersAndRolesNavigationManager _navigation;
        private INotificationProvider _notification;

        private ObservableCollection<Organization> _organizations;
        public ObservableCollection<Organization> Organizations
        {
            get { return _organizations; }
            set { _organizations = value; RaisePropertyChangedAuto(); }
        }

        private Organization _selectedOrganization;
        public Organization SelectedOrganization
        {
            get { return _selectedOrganization; }
            set { _selectedOrganization = value; RaisePropertyChangedAuto(); }
        }

        private Organization _managedOrganization;
        public Organization ManagedOrganization
        {
            get { return _managedOrganization; }
            set { _managedOrganization = value; RaisePropertyChangedAuto(); }
        }

        private ObservableCollection<Role> _roles;
        public ObservableCollection<Role> Roles
        {
            get { return _roles; }
            set { _roles = value; RaisePropertyChangedAuto(); }
        }

        private ObservableCollection<Role> _managedUserRoles;
        public ObservableCollection<Role> ManagedUserRoles
        {
            get { return _managedUserRoles; }
            set { _managedUserRoles = value; RaisePropertyChangedAuto(); }
        }

        private User _selectedUser;
        public User SelectedUser
        {
            get { return _selectedUser; }
            set { _selectedUser = value; RaisePropertyChangedAuto(); }
        }

        private User _managedUser;
        public User ManagedUser
        {
            get { return _managedUser; }
            set { _managedUser = value; RaisePropertyChangedAuto(); }
        }

        private Place _selectedUserPlace;
        public Place SelectedUserPlace
        {
            get { return _selectedUserPlace; }
            set
            {
                _selectedUserPlace = value;

                if (_selectedUserPlace != null && _selectedUserPlace.Address != null)
                {
                    SetUserPlace(value);
                }
            }
        }

        private Place _selectedOrganizationPlace;
        public Place SelectedOrganizationPlace
        {
            get { return _selectedOrganizationPlace; }
            set
            {
                _selectedOrganizationPlace = value;

                if (_selectedOrganizationPlace != null && _selectedOrganizationPlace.Address != null)
                {
                    SetOrganizationPlace(value);
                }
            }
        }

        public RelayCommand ManageOrganizationCommand { get; set; }

        public RelayCommand BackToOrganizationsCommand { get; set; }

        public RelayCommand ManageUserCommand { get; set; }

        public RelayCommand SaveOrganizationCommand { get; set; }

        public RelayCommand AddOrganizationCommand { get; set; }

        public RelayCommand RemoveOrganizationCommand { get; set; }

        public RelayCommand BackToManagedOrganizationCommand { get; set; }

        public RelayCommand<Role> RemoveRoleCommand { get; set; }

        public RelayCommand SaveManagedUserCommand { get; set; }

        public RelayCommand AddUserCommand { get; set; }

        public RelayCommand RemoveUserCommand { get; set; }

        public MainViewVM(UsersAndRolesNavigationManager navigation, INotificationProvider notification)
        {
            _navigation = navigation;
            _notification = notification;

            LoadOrganizations();

            ManageOrganizationCommand = new RelayCommand(LoadSelectedOrganization, () => SelectedOrganization != null);
            BackToOrganizationsCommand = new RelayCommand(BackToOrganizations);
            ManageUserCommand = new RelayCommand(LoadSelectedUser, () => SelectedUser != null);
            SaveOrganizationCommand = new RelayCommand(SaveOrganization);
            AddOrganizationCommand = new RelayCommand(AddOrganization);
            RemoveOrganizationCommand = new RelayCommand(RemoveOrganization, () => SelectedOrganization != null);
            BackToManagedOrganizationCommand = new RelayCommand(BackToManagedOrganization);
            RemoveRoleCommand = new RelayCommand<Role>(RemoveUserRole);
            SaveManagedUserCommand = new RelayCommand(SaveManagedUser);
            AddUserCommand = new RelayCommand(AddNewUser);
            RemoveUserCommand = new RelayCommand(RemoveSelectedUser, () => SelectedUser != null);
        }

        private async void AddOrganization()
        {
            String name = _notification.ShowTextInput("Enter organization name", "Name");

            if (!String.IsNullOrWhiteSpace(name))
            {
                using (_notification.PushTaskItem("Adding new organization..."))
                {
                    Organization org = new Organization();
                    org.Name =pre { line-height: 125%; }
td.linenos .normal { color: inherit; background-color: transparent; padding-left: 5px; padding-right: 5px; }
span.linenos { color: inherit; background-color: transparent; padding-left: 5px; padding-right: 5px; }
td.linenos .special { color: #000000; background-color: #ffffc0; padding-left: 5px; padding-right: 5px; }
span.linenos.special { color: #000000; background-color: #ffffc0; padding-left: 5px; padding-right: 5px; }
.highlight .hll { background-color: #ffffcc }
.highlight .c { color: #888888 } /* Comment */
.highlight .err { color: #a61717; background-color: #e3d2d2 } /* Error */
.highlight .k { color: #008800; font-weight: bold } /* Keyword */
.highlight .ch { color: #888888 } /* Comment.Hashbang */
.highlight .cm { color: #888888 } /* Comment.Multiline */
.highlight .cp { color: #cc0000; font-weight: bold } /* Comment.Preproc */
.highlight .cpf { color: #888888 } /* Comment.PreprocFile */
.highlight .c1 { color: #888888 } /* Comment.Single */
.highlight .cs { color: #cc0000; font-weight: bold; background-color: #fff0f0 } /* Comment.Special */
.highlight .gd { color: #000000; background-color: #ffdddd } /* Generic.Deleted */
.highlight .ge { font-style: italic } /* Generic.Emph */
.highlight .ges { font-weight: bold; font-style: italic } /* Generic.EmphStrong */
.highlight .gr { color: #aa0000 } /* Generic.Error */
.highlight .gh { color: #333333 } /* Generic.Heading */
.highlight .gi { color: #000000; background-color: #ddffdd } /* Generic.Inserted */
.highlight .go { color: #888888 } /* Generic.Output */
.highlight .gp { color: #555555 } /* Generic.Prompt */
.highlight .gs { font-weight: bold } /* Generic.Strong */
.highlight .gu { color: #666666 } /* Generic.Subheading */
.highlight .gt { color: #aa0000 } /* Generic.Traceback */
.highlight .kc { color: #008800; font-weight: bold } /* Keyword.Constant */
.highlight .kd { color: #008800; font-weight: bold } /* Keyword.Declaration */
.highlight .kn { color: #008800; font-weight: bold } /* Keyword.Namespace */
.highlight .kp { color: #008800 } /* Keyword.Pseudo */
.highlight .kr { color: #008800; font-weight: bold } /* Keyword.Reserved */
.highlight .kt { color: #888888; font-weight: bold } /* Keyword.Type */
.highlight .m { color: #0000DD; font-weight: bold } /* Literal.Number */
.highlight .s { color: #dd2200; background-color: #fff0f0 } /* Literal.String */
.highlight .na { color: #336699 } /* Name.Attribute */
.highlight .nb { color: #003388 } /* Name.Builtin */
.highlight .nc { color: #bb0066; font-weight: bold } /* Name.Class */
.highlight .no { color: #003366; font-weight: bold } /* Name.Constant */
.highlight .nd { color: #555555 } /* Name.Decorator */
.highlight .ne { color: #bb0066; font-weight: bold } /* Name.Exception */
.highlight .nf { color: #0066bb; font-weight: bold } /* Name.Function */
.highlight .nl { color: #336699; font-style: italic } /* Name.Label */
.highlight .nn { color: #bb0066; font-weight: bold } /* Name.Namespace */
.highlight .py { color: #336699; font-weight: bold } /* Name.Property */
.highlight .nt { color: #bb0066; font-weight: bold } /* Name.Tag */
.highlight .nv { color: #336699 } /* Name.Variable */
.highlight .ow { color: #008800 } /* Operator.Word */
.highlight .w { color: #bbbbbb } /* Text.Whitespace */
.highlight .mb { color: #0000DD; font-weight: bold } /* Literal.Number.Bin */
.highlight .mf { color: #0000DD; font-weight: bold } /* Literal.Number.Float */
.highlight .mh { color: #0000DD; font-weight: bold } /* Literal.Number.Hex */
.highlight .mi { color: #0000DD; font-weight: bold } /* Literal.Number.Integer */
.highlight .mo { color: #0000DD; font-weight: bold } /* Literal.Number.Oct */
.highlight .sa { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Affix */
.highlight .sb { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Backtick */
.highlight .sc { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Char */
.highlight .dl { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Delimiter */
.highlight .sd { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Doc */
.highlight .s2 { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Double */
.highlight .se { color: #0044dd; background-color: #fff0f0 } /* Literal.String.Escape */
.highlight .sh { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Heredoc */
.highlight .si { color: #3333bb; background-color: #fff0f0 } /* Literal.String.Interpol */
.highlight .sx { color: #22bb22; background-color: #f0fff0 } /* Literal.String.Other */
.highlight .sr { color: #008800; background-color: #fff0ff } /* Literal.String.Regex */
.highlight .s1 { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Single */
.highlight .ss { color: #aa6600; background-color: #fff0f0 } /* Literal.String.Symbol */
.highlight .bp { color: #003388 } /* Name.Builtin.Pseudo */
.highlight .fm { color: #0066bb; font-weight: bold } /* Name.Function.Magic */
.highlight .vc { color: #336699 } /* Name.Variable.Class */
.highlight .vg { color: #dd7700 } /* Name.Variable.Global */
.highlight .vi { color: #3333bb } /* Name.Variable.Instance */
.highlight .vm { color: #336699 } /* Name.Variable.Magic */
.highlight .il { color: #0000DD; font-weight: bold } /* Literal.Number.Integer.Long */
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Threading;
using System.Text.RegularExpressions;
using System.IO;
using Tango.Logging;

namespace Tango.Synchronization.Conversion
{
    /// <summary>
    /// This class is responsible to take a single SQL Server database
    /// and convert it to an SQLite database file.
    /// </summary>
    /// <remarks>The class knows how to convert table and index structures only.</remarks>
    public class SqlServerToSQLiteConverter
    {
        private LogManager LogManager = LogManager.Default;

        #region Public Properties
        /// <summary>
        /// Gets a value indicating whether this instance is active.
        /// </summary>
        /// <value><c>true</c> if this instance is active; otherwise, <c>false</c>.</value>
        public bool IsActive
        {
            get { return _isActive; }
        }

        public bool GenerateForeignKeys { get; set; }

        public bool GenerateIndexes { get; set; }

        #endregion

        #region Public Methods
        /// <summary>
        /// Cancels the conversion.
        /// </summary>
        public void CancelConversion()
        {
            _cancelled = true;
        }

        /// <summary>
        /// This method takes as input the connection string to an SQL Server database
        /// and creates a corresponding SQLite database file with a schema derived from
        /// the SQL Server database.
        /// </summary>
        /// <param name="sqlServerConnString">The connection string to the SQL Server database.</param>
        /// <param name="sqlitePath">The path to the SQLite database file that needs to get created.</param>
        /// <param name="password">The password to use or NULL if no password should be used to encrypt the DB</param>
        /// <param name="handler">A handler delegate for progress notifications.</param>
        /// <param name="selectionHandler">The selection handler that allows the user to select which
        /// tables to convert</param>
        /// <remarks>The method continues asynchronously in the background and the caller returned
        /// immediately.</remarks>
        public void ConvertSqlServerToSQLiteDatabase(string sqlServerConnString,
            string sqlitePath, string password, SqlConversionHandler handler,
            SqlTableSelectionHandler selectionHandler,
            FailedViewDefinitionHandler viewFailureHandler,
            bool createTriggers, bool createViews)
        {
            // Clear canceled flag
            _cancelled = false;

            WaitCallback wc = new WaitCallback(delegate (object state)
            {
                try
                {
                    _isActive = true;
                    ConvertSqlServerDatabaseToSQLiteFile(sqlServerConnString, sqlitePath, password, handler, selectionHandler, viewFailureHandler, createTriggers, createViews);
                    _isActive = false;
                    handler(true, true, 100, "Finished converting database");
                }
                catch (Exception ex)
                {
                    LogManager.Log(ex, "Failed to convert SQL Server database to SQLite database");
                    _isActive = false;
                    handler(true, false, 100, ex.Message);
                } // catch
            });
            ThreadPool.QueueUserWorkItem(wc);
        }
        #endregion

        #region Private Methods
        /// <summary>
        /// Do the entire process of first reading the SQL Server schema, creating a corresponding
        /// SQLite schema, and copying all rows from the SQL Server database to the SQLite database.
        /// </summary>
        /// <param name="sqlConnString">The SQL Server connection string</param>
        /// <param name="sqlitePath">The path to the generated SQLite database file</param>
        /// <param name="password">The password to use or NULL if no password should be used to encrypt the DB</param>
        /// <param name="handler">A handler to handle progress notifications.</param>
        /// <param name="selectionHandler">The selection handler which allows the user to select which tables to 
        /// convert.</param>
        private void ConvertSqlServerDatabaseToSQLiteFile(
            string sqlConnString, string sqlitePath, string password, SqlConversionHandler handler,
            SqlTableSelectionHandler selectionHandler,
            FailedViewDefinitionHandler viewFailureHandler,
            bool createTriggers, bool createViews)
        {
            // Delete the target file if it exists already.
            if (File.Exists(sqlitePath))
                File.Delete(sqlitePath);

            // Read the schema of the SQL Server database into a memory structure
            DatabaseSchema ds = ReadSqlServerSchema(sqlConnString, handler, selectionHandler);

            // Create the SQLite database and apply the schema
            CreateSQLiteDatabase(sqlitePath, ds, password, handler, viewFailureHandler, createViews);

            // Copy all rows from SQL Server tables to the newly created SQLite database
            CopySqlServerRowsToSQLiteDB(sqlConnString, sqlitePath, ds.Tables, password, handler);

            // Add triggers based on foreign key constraints
            if (createTriggers)
                AddTriggersForForeignKeys(sqlitePath, ds.Tables, password, handler);

        }

        /// <summary>
        /// Copies table rows from the SQL Server database to the SQLite database.
        /// </summary>
        /// <param name="sqlConnString">The SQL Server connection string</param>
        /// <param name="sqlitePath">The path to the SQLite database file.</param>
        /// <param name="schema">The schema of the SQL Server database.</param>
        /// <param name="password">The password to use for encrypting the file</param>
        /// <param name="handler">A handler to handle progress notifications.</param>
        private void CopySqlServerRowsToSQLiteDB(
            string sqlConnString, string sqlitePath, List<TableSchema> schema,
            string password, SqlConversionHandler handler)
        {
            CheckCancelled();
            handler(false, true, 0, "Preparing to insert tables...");
            LogManager.Log("preparing to insert tables ...");

            // Connect to the SQL Server database
            using (SqlConnection ssconn = new SqlConnection(sqlConnString))
            {
                ssconn.Open();

                // Connect to the SQLite database next
                string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
                using (SQLiteConnection sqconn = new SQLiteConnection(sqliteConnString))
                {
                    sqconn.Open();

                    // Go over all tables in the schema and copy their rows
                    for (int i = 0; i < schema.Count; i++)
                    {
                        if (schema[i].CopyData)
                        {
                            SQLiteTransaction tx = sqconn.BeginTransaction();
                            try
                            {
                                string tableQuery = BuildSqlServerTableQuery(schema[i]);
                                SqlCommand query = new SqlCommand(tableQuery, ssconn);
                                using (SqlDataReader reader = query.ExecuteReader())
                                {
                                    SQLiteCommand insert = BuildSQLiteInsert(schema[i]);
                                    int counter = 0;
                                    while (reader.Read())
                                    {
                                        insert.Connection = sqconn;
                                        insert.Transaction = tx;
                                        List<string> pnames = new List<string>();
                                        for (int j = 0; j < schema[i].Columns.Count; j++)
                                        {
                                            string pname = "@" + GetNormalizedName(schema[i].Columns[j].ColumnName, pnames);
                                            insert.Parameters[pname].Value = CastValueForColumn(reader[j], schema[i].Columns[j]);
                                            pnames.Add(pname);
                                        }
                                        insert.ExecuteNonQuery();
                                        counter++;
                                        if (counter % 1000 == 0)
                                        {
                                            CheckCancelled();
                                            tx.Commit();
                                            handler(false, true