using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;

namespace TriggerDemoGui
{
    public class SqlLoader
    {
        public async Task<DataTable> GetDataTableAsync(string connectionString, string dbName)
        {
            DataTable table = new DataTable(dbName);
            await Task.Run(() =>
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    SqlCommand cmd = new SqlCommand($"SELECT * FROM {dbName};", connection);
                    SqlDataReader reader = cmd.ExecuteReader();

                    //create collumns in the DataTable
                    table.Columns.Clear();
                    for (int i = 0; i < reader.FieldCount; i++)
                        table.Columns.Add(new DataColumn(reader.GetName(i), reader.GetFieldType(i)));

                    //copy rows of the DataTable
                    table.Rows.Clear();
                    while (reader.Read())
                    {
                        DataRow row = table.NewRow();
                        for (int i = 0; i < reader.FieldCount; i++)
                            row[i] = reader[i];
                        table.Rows.Add(row);
                    }
                    reader.Close();
                    connection.Close();
                }
            });
            return table;
        }

        public DataTable GetDataTable(string connectionString, string dbName)
        {
            DataTable table = new DataTable(dbName);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                SqlCommand cmd = new SqlCommand($"SELECT * FROM {dbName};", connection);
                SqlDataReader reader = cmd.ExecuteReader();

                //create collumns in the DataTable
                table.Columns.Clear();
                for (int i = 0; i < reader.FieldCount; i++)
                    table.Columns.Add(new DataColumn(reader.GetName(i), reader.GetFieldType(i)));

                //copy rows of the DataTable
                table.Rows.Clear();
                while (reader.Read())
                {
                    DataRow row = table.NewRow();
                    for (int i = 0; i < reader.FieldCount; i++)
                        row[i] = reader[i];
                    table.Rows.Add(row);
                }
                reader.Close();
                connection.Close();

            }
            return table;
        }

        public async Task<ObservableCollection<string>>  GetTables(string connectionString)
        {
            var result = new ObservableCollection<string>();
            await Task.Run(() =>
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    //read schema of database
                    DataTable schema = connection.GetSchema("Tables");
                    //extract names of tables
                    foreach (DataRow row in schema.Rows)
                    {
                        result.Add(row[2].ToString());
                    }
                    connection.Close();
                }
                Thread.Sleep(10000);
            });
            return result;
        }
        
        public bool CheckConnectionString(string connectionString, out string message)
        {
            try
            {
                //correctness of the connection string is checked through to create SqlConnection
                SqlConnection connection = new SqlConnection(connectionString);
                connection.Open();
                connection.Close();
                connection.Dispose();
                message = "Ok";
                return true;
            }
            catch (Exception ex)
            {
                message = ex.Message;
                return false;
            }
        }
    }

}
