context: // Copyright (c) The University of Dundee 2018-2019
  // This file is part of the Research Data Management Platform (RDMP).
  // RDMP is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
  // RDMP is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
  // You should have received a copy of the GNU General Public License along with RDMP. If not, see <https://www.gnu.org/licenses/>.
  
  using System;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Data.Common;
  using System.Linq;
  using System.Threading;
  using System.Windows.Forms;
  using FAnsi;
  using FAnsi.Discovery;
  using Rdmp.Core.CommandExecution;
  using Rdmp.Core.Curation.Data;
  using Rdmp.Core.Icons.IconProvision;
  using Rdmp.UI.SimpleDialogs;
  using ReusableLibraryCode;
  
  namespace Rdmp.UI.SimpleControls
  {
      public delegate void IntegratedSecurityUseChangedHandler(bool use);
  
  
      /// <summary>
      /// Lets you select a server database or table.  Includes auto population of database/table lists.  This is a reusable component.
      /// </summary>
      public partial class ServerDatabaseTableSelector : UserControl
      {
          private bool _allowTableValuedFunctionSelection;
  
          public string Server
          {
              get { return cbxServer.Text; }
              set { cbxServer.Text = value; }
          }
  
          public string Database
          {
              get { return cbxDatabase.Text; }
              set { cbxDatabase.Text = value; }
          }
  
          public string Table
          {
              private get { return cbxTable.Text; }
              set { cbxTable.Text = value; }
          }
  
          public string Username
          {
              get { return tbUsername.Text; }
              set { tbUsername.Text = value; }
          }
  
          public string Password
          {
              get { return tbPassword.Text; }
              set { tbPassword.Text = value; }
          }
  
          private string TableValuedFunction { get { return cbxTableValueFunctions.Text; } }
  
          public event Action SelectionChanged;
          private IDiscoveredServerHelper _helper;
  
          private BackgroundWorker _workerRefreshDatabases = new BackgroundWorker();
          CancellationTokenSource _workerRefreshDatabasesToken;
          private string[] _listDatabasesAsyncResult;
  
          private BackgroundWorker _workerRefreshTables = new BackgroundWorker();
          CancellationTokenSource _workerRefreshTablesToken;
          private List<DiscoveredTable> _listTablesAsyncResult;
  
          private const string CancelConnection = "Cancel Connection";
          private const string ConnectionFailed = "Connection Failed";
  
          //constructor
          public ServerDatabaseTableSelector()
          {
              InitializeComponent();
  
              if(LicenseManager.UsageMode == LicenseUsageMode.Designtime)
                  return;
  
              _workerRefreshDatabases.DoWork += UpdateDatabaseListAsync;
              _workerRefreshDatabases.WorkerSupportsCancellation = true;
              _workerRefreshDatabases.RunWorkerCompleted+=UpdateDatabaseAsyncCompleted;
  
              _workerRefreshTables.DoWork += UpdateTablesListAsync;
              _workerRefreshTables.WorkerSupportsCancellation = true;
              _workerRefreshTables.RunWorkerCompleted += UpdateTablesAsyncCompleted;
              
              var r = new RecentHistoryOfControls(cbxServer, new Guid("01ccc304-0686-4145-86a5-cc0468d40027"));
              r.AddHistoryAsItemsToComboBox(cbxServer);
  
              var r2 = new RecentHistoryOfControls(cbxDatabase, new Guid("e1a4e7a8-3f7a-4018-8ff5-2fd661ee06a3"));
              r2.AddHistoryAsItemsToComboBox(cbxDatabase);
  
              _helper = DatabaseCommandHelper.For(DatabaseType);
  
              btnPickCredentials.Image = CatalogueIcons.DataAccessCredentials;
              btnPickCredentials.Enabled = false;
          }
  
          
          #region Async Stuff
  
          private void UpdateTablesListAsync(object sender, DoWorkEventArgs e)
          {
              var builder = (DbConnectionStringBuilder)((object[])e.Argument)[0];
              var database = (string)((object[])e.Argument)[1];
  
              var discoveredDatabase = new DiscoveredServer(builder).ExpectDatabase(database);
              IDiscoveredDatabaseHelper databaseHelper = discoveredDatabase.Helper;
  
              _workerRefreshTablesToken = new CancellationTokenSource();
  
              var syntaxHelper = discoveredDatabase.Server.GetQuerySyntaxHelper();
              try
              {
                  using (var con = discoveredDatabase.Server.GetConnection())
                  {
                      var openTask = con.OpenAsync(_workerRefreshTablesToken.Token);
                      openTask.Wait(_workerRefreshTablesToken.Token);
  
                      List<DiscoveredTable> result = new List<DiscoveredTable>();
  
                      result.AddRange(databaseHelper.ListTables(discoveredDatabase, syntaxHelper, con, database, true));
                      result.AddRange(databaseHelper.ListTableValuedFunctions(discoveredDatabase, syntaxHelper, con, database));
  
                      _listTablesAsyncResult = result;
                  }
              }
              catch (OperationCanceledException)//user cancels
              {
                  _listTablesAsyncResult = new List<DiscoveredTable>();
              }
          }
  
          private void UpdateTablesAsyncCompleted(object sender, RunWorkerCompletedEventArgs e)
          {
  
              //success?
              ResetState();
  
              if (e.Error != null)
              {
                  SetState(e.Error);
              }
              else
                  if (!e.Cancelled)
                  {
                      cbxTable.Items.AddRange(_listTablesAsyncResult.Where(t => ! (t is DiscoveredTableValuedFunction)).ToArray());
                      cbxTableValueFunctions.Items.AddRange(_listTablesAsyncResult.Where(t => t is DiscoveredTableValuedFunction).ToArray());
                  }
                  
  
              SetLoading(false);
  
              cbxTable.Focus();
          }
  
          
  
  
          //do work
          private void UpdateDatabaseListAsync(object sender, DoWorkEventArgs e)
          {
              var builder = (DbConnectionStringBuilder)((object[])e.Argument)[0];
  
              ResetState();
  
              _workerRefreshDatabasesToken = new CancellationTokenSource();
              try
              {
                  _listDatabasesAsyncResult = _helper.ListDatabasesAsync(builder, _workerRefreshDatabasesToken.Token);
              }
              catch (OperationCanceledException)
              {
                  _listDatabasesAsyncResult = new string[0];
              }
              catch (AggregateException ex )//user cancels
              {
                  if (ex.GetExceptionIfExists<OperationCanceledException>() != null)
                      _listDatabasesAsyncResult = new string[0];
                  else
                  {
                      SetState(ex);
                      _listDatabasesAsyncResult = new string[0];
                  }   
              }
          }
  
          private void ResetState()
          {
              _exception = null;
              SetText(CancelConnection);
          }
          private void SetState(Exception ex)
          {
              _exception = ex;
              SetText(ConnectionFailed);
          }
  
          private void SetText(string v)
          {
  
              if (llLoading.InvokeRequired)
              {
                  llLoading.Invoke(new MethodInvoker(() => llLoading.Text = v));
              }
              else
              {
                  llLoading.Text = v;
              }
          }
  
          //handle complete
          private void UpdateDatabaseAsyncCompleted(object sender, RunWorkerCompletedEventArgs e)
          {
              if (e.Error != null)
              {
                  SetState(e.Error);
              }
              else if (!e.Cancelled)
                  cbxDatabase.Items.AddRange(_listDatabasesAsyncResult);
              else
              {
                  // user cancelled
                  ResetState();
              }
  
              SetLoading(false);
              cbxDatabase.Focus();
          }
          
          //aborting
          private void AbortWorkers()
          {
              if (_workerRefreshDatabases.IsBusy)
              {
                  _workerRefreshDatabases.CancelAsync();
                  if(_workerRefreshDatabasesToken != null)
                      _workerRefreshDatabasesToken.Cancel();
              }
  
              if (_workerRefreshTables.IsBusy)
              {
                  _workerRefreshTables.CancelAsync();
                  if(_workerRefreshTablesToken != null)
                      _workerRefreshTablesToken.Cancel();
              }
          }
          #endregion
  
          public void SetDefaultServers(string[] defaultServers)
          {
              cbxServer.Items.AddRange(defaultServers);
          }
  
          public bool AllowTableValuedFunctionSelection
          {
              get { return _allowTableValuedFunctionSelection; }
              set
              {
                  _allowTableValuedFunctionSelection = value;
                  
                  lblOr.Visible = value;
                  lblTableValuedFunction.Visible = value;
                  cbxTableValueFunctions.Visible = value;
              }
          }
  
          public DatabaseType DatabaseType {
              get { return databaseTypeUI1.DatabaseType; }
              set { databaseTypeUI1.DatabaseType = value; }
  
          }
  
          public DiscoveredServer Result { get { return new DiscoveredServer(GetBuilder()); } }
  
          public bool TableShouldBeNovel
          {
              set
              {
                  cbxTable.AutoCompleteMode = value ? AutoCompleteMode.None : AutoCompleteMode.Suggest;
                  cbxTableValueFunctions.AutoCompleteMode = value ? AutoCompleteMode.None : AutoCompleteMode.Suggest;
              }
          }
  
          private void cbxServer_SelectedIndexChanged(object sender, EventArgs e)
          {
              //if they have not selected anything selected something
              if (string.IsNullOrWhiteSpace(cbxServer.Text))
                  return;
  
              UpdateDatabaseList();
          }
  
          private void cbxDatabase_SelectedIndexChanged(object sender, EventArgs e)
          {
              UpdateTableList();
          }
  
          private bool _clearingTable = false;
          private void cbxTable_SelectedIndexChanged(object sender, EventArgs e)
          {
              //dont clear both!
              if (_clearingTable)
                  return;
              _clearingTable = true;
  
              cbxTableValueFunctions.Text = null;
              if (SelectionChanged != null)
                  SelectionChanged();
  
              _clearingTable = false;
          }
  
          private void cbxTableValueFunctions_SelectedIndexChanged(object sender, EventArgs e)
          {
              //dont clear both!
              if (_clearingTable)
                  return;
  
              _clearingTable = true;
              cbxTable.Text = null;
              if (SelectionChanged != null)
                  SelectionChanged();
  
              _clearingTable = false;
          }
  
          public void HideTableComponents()
          {
              AllowTableValuedFunctionSelection = false;
              cbxTable.Visible = false;
              lblTable.Visible = false;
              btnRefreshTables.Visible = false;
          }
  
          private void cbxServer_Leave(object sender, EventArgs e)
          {
              UpdateDatabaseList();
          }
          
          private void UpdateTableList()
          {
              if (string.IsNullOrWhiteSpace(cbxServer.Text) || string.IsNullOrWhiteSpace(cbxDatabase.Text))
                  return;
  
              if (SelectionChanged != null)
                  SelectionChanged();
  
              AbortWorkers();
  
              cbxTable.Items.Clear();
              cbxTableValueFunctions.Items.Clear();
  
              SetLoading(true);
  
              if (!_workerRefreshTables.IsBusy)
                  _workerRefreshTables.RunWorkerAsync(new object[]
                      {
                          GetBuilder(),
                          cbxDatabase.Text
                      });
          }
  
          public void SetItemActivator(IBasicActivateItems activator)
          {
              _activator = activator;
              btnPickCredentials.Enabled = _activator != null;
          }
  
          private void UpdateDatabaseList()
          {
              if (string.IsNullOrWhiteSpace(cbxServer.Text) || _helper == null)
                  return;
  
              AbortWorkers();
              cbxDatabase.Items.Clear();
  
              SetLoading(true);
  
              if (SelectionChanged != null)
                  SelectionChanged();
  
  
              if(!_workerRefreshDatabases.IsBusy)
                  _workerRefreshDatabases.RunWorkerAsync(new object[]
                      {
                          GetBuilder()
                      });
          }
  
          private void SetLoading(bool isLoading)
          {
              llLoading.Visible = isLoading || _exception != null;
              pbLoading.Visible = isLoading;
  
              cbxServer.Enabled = !isLoading;
              cbxDatabase.Enabled = !isLoading;
              databaseTypeUI1.Enabled = !isLoading;
              btnRefreshDatabases.Enabled = !isLoading;
              btnRefreshTables.Enabled = !isLoading;
  
          }
  
          public event IntegratedSecurityUseChangedHandler IntegratedSecurityUseChanged;
  
          private string oldUsername = null;
          private IBasicActivateItems _activator;
          private Exception _exception;
  
          private void tbUsername_TextChanged(object sender, EventArgs e)
          {
              //if nobody is listening who cares
              if (IntegratedSecurityUseChanged == null)
                  return;
  
              //if the last value they typed was blank and the new value is not blank
              if (string.IsNullOrWhiteSpace(oldUsername) && !string.IsNullOrWhiteSpace(Username))
                  IntegratedSecurityUseChanged(false);
  
              //if the last value they typed was NOT blank and it is now blank
              if (!string.IsNullOrWhiteSpace(oldUsername) && string.IsNullOrWhiteSpace(Username))
                  IntegratedSecurityUseChanged(true);
  
              oldUsername = tbUsername.Text;
  
          }
  
          public DiscoveredDatabase GetDiscoveredDatabase()
          {
              if (string.IsNullOrWhiteSpace(cbxServer.Text))
                  return null;
  
              if (string.IsNullOrWhiteSpace(cbxDatabase.Text))
                  return null;
  
              return new DiscoveredServer(GetBuilder()).ExpectDatabase(cbxDatabase.Text);
          }
  
          public DiscoveredTable GetDiscoveredTable()
          {
              //if user selected a specific object from the drop down properly
              var tbl = cbxTable.SelectedItem as DiscoveredTable;
              var tblValuedFunction = cbxTableValueFunctions.SelectedItem as DiscoveredTableValuedFunction;
  
  
gt:             if(tbl != null)