using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.Composition;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Media;
using DTS.Common.Events;
using DTS.Common.Events.Database;
using Prism.Events;
using Prism.Regions;
using Unity;
using DTS.Common.Interactivity;
using Prism.Commands;
using DTS.Common.Interface.Database;
using DTS.Common.Storage;
using DTS.Common.Utilities.Logging;
using DTS.Common.Interface;

// ReSharper disable CheckNamespace
// ReSharper disable MemberCanBePrivate.Global
// ReSharper disable InconsistentNaming

namespace DatabaseServices
{
    /// <summary>
    /// this class handles DatabaseCopy functionality
    /// </summary>
    [PartCreationPolicy(CreationPolicy.Shared)]
    public class DatabaseCopyViewModel : IDatabaseCopyViewModel
    {
        /// <summary>
        /// The DatabaseCopy view
        /// </summary>
        public IDatabaseCopyView View { get; set; }

        private IEventAggregator _eventAggregator { get; }
        private IUnityContainer UnityContainer { get; }

        public InteractionRequest<Notification> NotificationRequest { get; }
        public InteractionRequest<Confirmation> ConfirmationRequest { get; }

        /// <inheritdoc />
        /// <summary>
        /// Occurs when a property value changes.
        /// </summary>
        public event PropertyChangedEventHandler PropertyChanged;

        public void OnPropertyChanged(string propertyName)
        {
            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
        }

        #region constructors and initializers

        /// <summary>
        /// Creates a new instance of the DatabaseCopyViewModel
        /// </summary>
        /// <param name="view"></param>
        /// <param name="regionManager">The logical placeholder defined within the application's UI (in the shell or within views) into which views are displayed.</param>
        /// <param name="eventAggregator">The EventAggregator which allows different components to publish/subscribe to events without being coupled to each other.</param>
        /// <param name="unityContainer">The unityContainer.</param>
        public DatabaseCopyViewModel(IDatabaseCopyView view, IRegionManager regionManager,
            IEventAggregator eventAggregator, IUnityContainer unityContainer)
        {
            View = view;
            View.DataContext = this;

            NotificationRequest = new InteractionRequest<Notification>();
            ConfirmationRequest = new InteractionRequest<Confirmation>();

            _eventAggregator = eventAggregator;
            UnityContainer = unityContainer;
            _eventAggregator.GetEvent<RaiseNotification>().Subscribe(OnRaiseNotification);
            _eventAggregator.GetEvent<BusyIndicatorChangeNotification>().Subscribe(OnBusyIndicatorNotification);
        }

        #endregion

        #region Methods

        public void Unset()
        {
        }

        public void CopyDatabase()
        {
            _eventAggregator.GetEvent<AppStatusEvent>().Publish(AppStatusArg.Busy);
            _ = Task.Run(() => { CopyFunc(); });
        }

        public void InitializeState(DTS.Common.Enums.Database.DbType dbType, string dbName)
        {
            DbName = dbName;
            DatabaseType = dbType;
            _eventAggregator.GetEvent<ProgressBarEvent>().Publish(new ProgressBarEventArg(CurrentTaskBar, Colors.White,
                System.Windows.Visibility.Collapsed, 0D, ""));
            _eventAggregator.GetEvent<ProgressBarEvent>().Publish(new ProgressBarEventArg(OverallTaskBar, Colors.White,
                System.Windows.Visibility.Collapsed, 0D, ""));
            OnPropertyChanged("CopyEnabled");
        }

        private void SetStatus(string bar, double percentage, string text)
        {
            _eventAggregator.GetEvent<ProgressBarEvent>()
                .Publish(new ProgressBarEventArg(bar, Colors.White, System.Windows.Visibility.Visible, percentage, text));
        }
        private static readonly List<string> _allDBTables = new List<string>()
        {
            "AddOrRemove",
            "AnalogDiagnostics",
            "CalculatedChannelOperation",
            "CalculatedChannels",
            "CalibrationType",
            "ChannelCodes",
            "ChannelCodeType",
            "Channels",
            "ChannelSettings",
            "CustomerDetails",
            "DAS",
            "DASChannels",
            "DataPRODbVersion",
            "DbTableVersions",
            "DefaultProperties",
            "DiagnosticRuns",
            "GroupChannelSettings",
            "GroupHardware",
            "Groups",
            "LabratoryDetails",
            "LastUsedHardware",
            "LevelTriggers",
            "LockedItemCategories",
            "LockedItems",
            "RecordingMode",
            "RecordingModes",
            "ROIPeriodChannels",
            "SensorBridgeLegMode",
            "SensorBridgeType",
            "SensorCalibrationRecord",
            "SensorCalibrationRecordIRTracc",
            "SensorCalibrationRecordPolynomial",
            "SensorCalibrations",
            "SensorChangeHistory",
            "SensorChangeType",
            "SensorCouplingMode",
            "SensorDigitalOutputMode",
            "SensorModels",
            "Sensors",
            "SensorsAnalog",
            "SensorsDigitalIn",
            "SensorsDigitalOut",
            "SensorSettingMode",
            "SensorShunt",
            "SensorSquibFireMode",
            "SensorSquibMeasurementType",
            "SensorsSquib",
            "SensorsStreamInput",
            "SensorsStreamOutput",
            "SensorStatus",
            "SensorsType",
            "SensorsUART",
            "SensorTestHistory",
            "Settings",
            "SoftwareFilters",
            "StoredProcedureVersions",
            "TagAssignments",
            "TagObjectType",
            "Tags",
            "tblDataPRODbVersion",
            "TestEngineerDetails",
            "TestGraphs",
            "TestHistory",
            "TestSetupGroups",
            "TestSetupHardware",
            "TestSetupObjectMetaData",
            "TestSetupROIs",
            "TestSetups",
            "TestSetupSettings",
            "UIItems",
            "UIItemSettings",
            "UserProperties",
            "Users",
            "UsersRoles"
        };
        private static readonly HashSet<string> _tablesWithIdentities = new HashSet<string>()
        {
            "AnalogDiagnostics",
            "CalculatedChannels",
            "ChannelCodes",
            "ChannelCodeType",
            "Channels",
            "ChannelSettings",
            "CustomerDetails",
            "DAS",
            "DASChannels",
            "DiagnosticRuns",
            "GroupHardware",
            "Groups",
            "LabratoryDetails",
            "LastUsedHardware",
            "LevelTriggers",
            "LockedItemCategories",
            "LockedItems",
            "ROIPeriodChannels",
            "SensorCalibrationRecord",
            "SensorCalibrationRecordIRTracc",
            "SensorCalibrationRecordPolynomial",
            "SensorCalibrations",
            "SensorChangeHistory",
            "SensorChangeType",
            "SensorModels",
            "Sensors",
            "SensorsAnalog",
            "SensorsDigitalIn",
            "SensorsDigitalOut",
            "SensorsSquib",
            "SensorsStreamInput",
            "SensorsStreamOutput",
            "SensorsUART",
            "SensorTestHistory",
            "Settings",
            "SoftwareFilters",
            "Tags",
            "TestEngineerDetails",
            "TestGraphs",
            "TestHistory",
            "TestSetupGroups",
            "TestSetupHardware",
            "TestSetupObjectMetaData",
            "TestSetupROIs",
            "TestSetups",
            "UIItems",
            "UIItemSettings",
            "Users"
        };
        private void CopyFunc()
        {
            try
            {
                DTS.Common.Storage.DatabaseServices.BackupLocalDatabase(DbName);
            }
            catch (System.IO.FileNotFoundException ex)
            {
                _eventAggregator.GetEvent<DbStatusEvent>()
                    .Publish(new DbStatusArg(DbStatusArg.EventTypes.FailedToBackupLocalFileNotFound, ex));
                APILogger.Log(ex);
                return;
            }
            catch (Exception ex)
            {
                _eventAggregator.GetEvent<DbStatusEvent>()
                    .Publish(new DbStatusArg(DbStatusArg.EventTypes.FailedToBackupLocal, null));
                APILogger.Log(ex);
                return;
            }
            try
            {
                //get list of all tables
                //I'd like to do this through SQL, but ran into problems
                var allTables = _allDBTables;

                SetStatus(OverallTaskBar, 0, "");
                SetStatus(CurrentTaskBar, 0, "");
                double steps = 2 + allTables.Count;
                //turn off all constraints
                SetStatus(OverallTaskBar, 0, Resources.StringResources.ClearingLocalDb);
                using (var cmd = LocalOnlyOperations.GetSQLCommand(true))
                {
                    try
                    {
                        cmd.CommandText = "EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'";
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        cmd.Connection.Dispose();
                    }
                }
                double currentStep = 1;
                SetStatus(OverallTaskBar, 100D * currentStep / steps, "");

                //Copy remote table to local)
                foreach (var table in allTables)
                {
                    SetStatus(OverallTaskBar, 100D * currentStep / steps,
                        $"{Resources.StringResources.CopyingTable} {table}");
                    SetStatus(CurrentTaskBar, 0D, "");
                    CopyRemoteTable(table, _tablesWithIdentities.Contains(table));
                    currentStep++;
                }

                //Change any DASId columns in the Channels table from 0 to NULL
                try
                {
                    using (var cmd = LocalOnlyOperations.GetSQLCommand(true))
                    {
                        try
                        {
                            cmd.CommandText = "UPDATE Channels SET DASId = NULL WHERE DASId = 0";
                            cmd.CommandType = CommandType.Text;
                            cmd.ExecuteNonQuery();
                        }
                        finally { cmd.Connection.Dispose(); }
                    }
                }
                catch (Exception ex)
                {
                    APILogger.Log(ex);
                }

                //turn on all constraints
                SetStatus(OverallTaskBar, 100D * currentStep / steps, Resources.StringResources.EnablingConstraints);
                try
                {
                    using (var cmd = LocalOnlyOperations.GetSQLCommand(true))
                    {
                        try
                        {
                            cmd.CommandText = "EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'";
                            cmd.CommandType = CommandType.Text;
                            cmd.ExecuteNonQuery();
                            _eventAggregator.GetEvent<DbStatusEvent>().Publish(new DbStatusArg(DbStatusArg.EventTypes.Complete, null));
                        }
                        finally
                        {
                            cmd.Connection.Dispose();
                        }
                    }
                }
                catch (Exception ex)
                {
                    APILogger.Log(ex);
                }
            }
            catch (Exception ex)
            {
                APILogger.Log(ex);
                _eventAggregator.GetEvent<DbStatusEvent>()
                    .Publish(new DbStatusArg(DbStatusArg.EventTypes.FailedToCopy, ex));
                try
                {
                    DTS.Common.Storage.DatabaseServices.RestoreLocalDatabase(DbName);
                }
                catch (Exception)
                {
                    APILogger.Log(ex);
                    _eventAggregator.GetEvent<DbStatusEvent>().Publish(
                        new DbStatusArg(DbStatusArg.EventTypes.FailedToRestoreLocal, null));
                }
            }
            finally
            {
                _eventAggregator.GetEvent<ProgressBarEvent>().Publish(new ProgressBarEventArg(OverallTaskBar,
                    Colors.White, System.Windows.Visibility.Collapsed, 0D, ""));
                _eventAggregator.GetEvent<ProgressBarEvent>().Publish(new ProgressBarEventArg(CurrentTaskBar,
                    Colors.White, System.Windows.Visibility.Collapsed, 0D, ""));
                _eventAggregator.GetEvent<AppStatusEvent>().Publish(AppStatusArg.Available);
            }
        }

        private void CopyRemoteTable(string tableName, bool bIndentityTable)
        {
            using (var cmd = LocalOnlyOperations.GetSQLCommand(true))
            {
                cmd.CommandText = $"DELETE FROM [dbo].[{tableName}]";
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                cmd.Connection.Dispose();
            }

            DataColumnCollection columns = null;
            var objects = new List<object[]>();
            using (var cmd = DbOperations.GetSQLCommand(true))
            {
                cmd.CommandText = $"SELECT * from [dbo].[{tableName}]";
                using (var ds = DbOperations.Connection.QueryDataSet(cmd))
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        columns = ds.Tables[0].Columns;
                        foreach (DataRow row in ds.Tables[0].Rows)
                        {
                            var rowObjects = new object[columns.Count];
                            for (var i = 0; i < rowObjects.Length; i++)
                            {
                                rowObjects[i] = row[i];
                            }
                            objects.Add(rowObjects);
                        }
                    }
                }
                cmd.Connection.Dispose();
            }
            if (null != columns)
            {
                InsertIntoLocalTable(tableName, columns, objects, bIndentityTable);
            }
        }

        private void InsertIntoLocalTable(string tableName, DataColumnCollection columns, List<object[]> data, bool bIdentity)
        {
            SqlCommand cmd = null;
            var index = 0;
            StringBuilder sb = null;
            var countSoFar = 0;
            foreach (var row in data)
            {
                if (null == cmd)
                {
                    index = 0;
                    cmd = LocalOnlyOperations.GetSQLCommand(true);
                    sb = new StringBuilder(500);
                    sb.Append(LocalOnlyOperations.BeginStatement);
                    if (bIdentity)
                    {
                        sb.Append($"SET IDENTITY_INSERT [dbo].[{tableName}] ON;");
                    }
                }

                sb.Append($"INSERT INTO [dbo].[{tableName}] (");
                for (var i = 0; i < columns.Count; i++)
                {
                    if (i > 0)
                    {
                        sb.Append(",");
                    }
                    sb.AppendFormat("[{0}]", columns[i].ColumnName);
                }
                sb.Append(") VALUES (");

                for (var i = 0; i < columns.Count; i++)
                {
                    if (i > 0)
                    {
                        sb.Append(",");
                    }
                    var key = $"@{index}_{i}";
                    sb.Append(key);
                    var dataType = TypeConvertor.ToSqlDbType(columns[i].DataType);
                    LocalOnlyOperations.CreateParam(cmd, key, dataType, row[i]);
                }
                sb.Append(");");

                index++;
                //18744 To avoid "The incoming request has too many parameters. The server supports a
                //maximum of 2100 parameters. Reduce the number of parameters and resend the request."
                //ensure that the number of parameters will not exceed 2100 if another row is added to sb.
                if ((index * columns.Count >= (2100 - columns.Count)) ||
                    (MAX_BATCH_SIZE == index))
                {
                    countSoFar += index;
                    if (bIdentity)
                    {
                        sb.Append($"SET IDENTITY_INSERT [dbo].[{tableName}] OFF;");
                    }
                    sb.Append(LocalOnlyOperations.CommitStatement);
                    cmd.CommandText = sb.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Dispose();
                    cmd.Dispose();
                    cmd = null;
                    SetStatus(CurrentTaskBar, 100D * countSoFar / data.Count, $"{countSoFar}/{data.Count}");
                }
            }
            if (null != cmd)
            {
                if (bIdentity)
                {
                    sb.Append($"SET IDENTITY_INSERT [dbo].[{tableName}] OFF;");
                }
                sb.Append(LocalOnlyOperations.CommitStatement);
                cmd.CommandText = sb.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                cmd.Connection.Dispose();
                cmd.Dispose();
                cmd = null;
                SetStatus(CurrentTaskBar, 100D, "");
            }
        }

        public void Cleanup()
        {
        }

        public Task CleanupAsync()
        {
            return Task.CompletedTask;
        }

        public void Initialize()
        {
        }

        public void Initialize(object parameter)
        {
        }

        public void Initialize(object parameter, object model)
        {
        }

        public Task InitializeAsync()
        {
            //this is existing code that was here, I was worried if I did make it async it might have issues
            //so I'm just correcting a warning by not returning null at the end
            try
            {
                var view = UnityContainer.Resolve<IStatusAndProgressBarView>();
                var viewModel = UnityContainer.Resolve<IStatusAndProgressBarViewModel>();
                viewModel.ProgressBarName = OverallTaskBar;
                view.DataContext = viewModel;
                viewModel.Initialize(this);
                OverallProgressBarView = view;

                view = UnityContainer.Resolve<IStatusAndProgressBarView>();
                viewModel = UnityContainer.Resolve<IStatusAndProgressBarViewModel>();
                view.DataContext = viewModel;
                viewModel.ProgressBarName = CurrentTaskBar;
                viewModel.Initialize(this);
                CurrentTaskProgressBarView = view;
            }
            catch (Exception ex)
            {
                APILogger.Log(ex);
            }
            return Task.CompletedTask;
        }

        public Task InitializeAsync(object parameter)
        {
            return Task.CompletedTask;
        }

        public void Activated()
        {
        }

        /// <summary>
        /// Private Event handler for RaiseNotification event.
        /// </summary>
        private void OnBusyIndicatorNotification(bool eventArg)
        {
            IsBusy = eventArg;
        }

        /// <summary>
        /// Private Event handler for RaiseNotification event.
        /// </summary>
        private void OnRaiseNotification(NotificationContentEventArgs eventArgsWithTitle)
        {
            // The NotificationRequest.Raise triggers the Invoke() method of the PopupWindowAction object to show the NotificationWindow window
            // Notification object expects a NotificationContentEventArgsWithoutTitle object and a Title string.
            var eventArgsWithoutTitle = new NotificationContentEventArgs(eventArgsWithTitle.Message, "",
                eventArgsWithTitle.Image, string.Empty);

            NotificationRequest.Raise(new Notification
            {
                Content = eventArgsWithoutTitle,
                Title = eventArgsWithTitle.Title
            });
        }

        #endregion

        #region Properties  
        public DTS.Common.Enums.Database.DbType DatabaseType { get; private set; }

        public bool CopyEnabled => DatabaseType == DTS.Common.Enums.Database.DbType.RemoteLocalHybrid &&
                                   DbOperations._usingCentralizedDB;

        public bool IsDirty { get; private set; }
        private bool _isBusy = false;
        public bool IsBusy
        {
            get => _isBusy;
            set
            {
                _isBusy = value;
                OnPropertyChanged("IsBusy");
            }
        }

        private bool _isMenuIncluded;
        public bool IsMenuIncluded
        {
            get => _isMenuIncluded;
            set
            {
                _isMenuIncluded = value;
                OnPropertyChanged("IsMenuIncluded");
            }
        }

        private bool _isNavigationIncluded;
        public bool IsNavigationIncluded
        {
            get => _isNavigationIncluded;
            set
            {
                _isNavigationIncluded = value;
                OnPropertyChanged("IsNavigationIncluded");
            }
        }
        public IStatusAndProgressBarView OverallProgressBarView { get; private set; }
        public IStatusAndProgressBarView CurrentTaskProgressBarView { get; private set; }
        private const string CurrentTaskBar = "CurrentTaskStatus";
        private const string OverallTaskBar = "OverallStatus";
        /// <summary>
        /// the size of each batch of inserts
        /// I played around with this for efficiency, but it seemed smaller sizes in general did better
        /// the max size would be 2100 parameters (so column count*rows must be less than 2100 )
        /// </summary>
        private const int MAX_BATCH_SIZE = 20;
        public string DbName { get; private set; }
        private bool _isCopyVisible = true;
        public bool IsCopyVisible
        {
            get => _isCopyVisible;
            set { _isCopyVisible = value; OnPropertyChanged("IsCopyVisible"); }
        }
        #endregion Properties

        #region Commands

        #endregion
    }
}