using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace DBManagerWin
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        /// Login info variables
        String server = null;
        String database = null;
        String username = null;
        String passwd = null;
        //Database utils
        String connectionInfo = null;
        MySqlDataReader reader = null;
        MySqlConnection connection;
        //////////////////



        public MainWindow()
        {
            InitializeComponent();
            sendQuery.IsEnabled = false;
            queryTxt.IsEnabled = false;
        }

        private void onClose(object sender, System.ComponentModel.CancelEventArgs e)
        {
            connection.Close();
        }

        private void button_Click(object sender, RoutedEventArgs e)
        {
            server = serverTxt.Text;
            database = databaseTxt.Text;
            username = userTxt.Text;
            passwd = passwdTxt.Text;

            if((server.Length < 3)||(database.Length < 1)||(username.Length<1))
            {
                MessageBox.Show("Fields are not filled correctly!");
                return;
            }
            connectionInfo = "Data Source = "+server+"; Initial Catalog = "+database+"; User ID = "+username+"; Password = "+passwd+";";
            connection = new MySqlConnection(connectionInfo);
            try { connection.Open(); }
            catch (MySqlException ex){
                MessageBox.Show("Error - Can't connect to database\nMake sure all fields are filled correctly");
                return;
            }

            MessageBox.Show("Connection good");
            sendQuery.IsEnabled = true;
            queryTxt.IsEnabled = true;
        }

        private void sendQueryButton(object sender, RoutedEventArgs e)
        {
            try{
                String query = queryTxt.Text;
                if (query.Length >= 6)
                {
                    if ((query[0] == 'S' || query[0] == 's') && (query[1] == 'e' || query[1] == 'E') && (query[2] == 'l' || query[2] == 'L') && (query[3] == 'e' || query[3] == 'E') && (query[4] == 'C' || query[4] == 'c') && (query[5] == 't' || query[5] == 'T')){
                        showSelect(query);
                        return;
                    }
                }

                else if(query.Length==0)
                {
                    MessageBox.Show("Query is empty!");
                    return;
                }

                MessageBox.Show(query);
                MySqlCommand mysqlQueryToSend = connection.CreateCommand();
                mysqlQueryToSend.CommandText = query;
                mysqlQueryToSend.ExecuteNonQuery();
                MessageBox.Show("Done!");
            }
            catch(MySqlException ex){
                MessageBox.Show(ex.Message);
                return;
            }
            catch(System.NullReferenceException ex){
                MessageBox.Show(ex.Message);
                return;
            }
        }

        private void showSelect(string query)
        {
            MySqlCommand mysqlQueryToSend = connection.CreateCommand();
            mysqlQueryToSend.CommandText = query;
            reader = mysqlQueryToSend.ExecuteReader();
            MessageBox.Show(reader.FieldCount.ToString());
            DataTable schema = null;
            int rowCount = reader.FieldCount;
            schema = reader.GetSchemaTable();
            string[] rows = new string[rowCount];
            string[] columns = new string[rowCount];
            int index = 0;
            foreach(DataRow x in schema.Rows)
            {
                rows[index]=x.Field<String>("ColumnName");
                index++;
            }
            string exit = null;

            foreach(var x in rows)
            {
                exit += " | "+x + " |    ";
            }

            exit += "\n";

            for (int fi = 0; fi < rowCount; fi++)
            {
                while (reader.Read())
                {
                    exit += "\n";
                    foreach (var x in rows)
                    exit += " | "+reader[x].ToString()+" |    ";
               }
           }

            resoult.Text = exit;
            reader.Close();
        }

    }

}