// Need to review this tutorial still: https://www.youtube.com/watch?v=_PCBTiXL884
// CURRENT WORKING MYSQL DB:
// https://remotemysql.com/dashboard.php
// Username: 2Ds6YxqaDW | Database name: 2Ds6YxqaDW | Password: VJKMThFkcO
// Server: remotemysql.com Port: 3306
//
using System;
using System.Data;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace CSharp_GeographyGame
{
    public partial class dbscoresForm : Form
    {
        string connectionString;
        SqlConnection connection;
        private int originalWidth;
        private int originalHeight;

        public dbscoresForm()
        {
            InitializeComponent();
            originalWidth = this.Width;
            originalHeight = this.Height;

            connectionString = ConfigurationManager.ConnectionStrings["CSharp_GeographyGame.Properties.Settings.userScoresConnectionString"].ConnectionString;
        }
        private void dbscoresForm_Load(object sender, EventArgs e)
        {
            this.Width = 618;
            this.Height = 205;
        }

        private void btnUSscoresLocal_Click(object sender, EventArgs e)
        {
            populateUSDataLocal();
            lblLocal.Text = "Local Database (US Map):";
            this.Width = 618;
            this.Height = 452;
        }

        private void btnCapitalscoresLocal_Click(object sender, EventArgs e)
        {
            populateUScapitalDataLocal();
            lblLocal.Text = "Local Database (Capitals):";
            this.Width = 618;
            this.Height = 452;
        }

        private void btnWorldscoresLocal_Click(object sender, EventArgs e)
        {
            populateUSworldDataLocal();
            lblLocal.Text = "Local Database (World Map):";
            this.Width = 618;
            this.Height = 452;
        }

        private void btnPresscoresLocal_Click(object sender, EventArgs e)
        {
            populatePresscoresLocal();
            lblLocal.Text = "Local Database (Presidents):";
            this.Width = 618;
            this.Height = 452;
        }

        private void btnUSscoresOnline_Click(object sender, EventArgs e)
        {
            populateUSDataOnline();
            lblOnline.Text = "Online Database (US Map):";
            this.Width = 618;
            this.Height = 708;
        }

        private void btnCapitalscoresOnline_Click(object sender, EventArgs e)
        {
            populateUScapitalDataOnline();
            lblOnline.Text = "Online Database (Capitals):";
            this.Width = 618;
            this.Height = 708;
        }

        private void btnWorldscoresOnline_Click(object sender, EventArgs e)
        {
            populateUSwolrdDataOnline();
            lblOnline.Text = "Online Database (World Map):";
            this.Width = 618;
            this.Height = 708;
        }

        private void btnPresscoresOnline_Click(object sender, EventArgs e)
        {
            populatePresscoresOnline();
            lblOnline.Text = "Online Database (Presidents):";
            this.Width = 618;
            this.Height = 708;
        }

        private void populateUSDataLocal()
        {
            using (connection = new SqlConnection(connectionString))
            using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM USMapscores", connection))
            {
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                DataTable dbScores = new DataTable();
                adapter.Fill(dbScores);

                dgDB.DataSource = dbScores;
                connection.Close();
            }
        }

        private void populateUSDataOnline()
        {
            string conWeb = "Server = remotemysql.com; Port = 3306; database = 2Ds6YxqaDW; username = 2Ds6YxqaDW; password = VJKMThFkcO";
            MySqlConnection connTwo = new MySqlConnection(conWeb);
            // Another Method
            //MySqlCommand commWeb = connTwo.CreateCommand();
            //commWeb.CommandType = System.Data.CommandType.Text;
            //commWeb.CommandText = "SELECT '*' FROM testScores";

            using (MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM onUSmapscores", conWeb))
            {
                try
                {
                    connTwo.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                DataTable dbScores = new DataTable();
                adapter.Fill(dbScores);
                dgOnlineDB.DataSource = dbScores;

                connTwo.Close();
            }
        }

        private void populateUScapitalDataLocal()
        {
            using (connection = new SqlConnection(connectionString))
            using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM USCapitalscores", connection))
            {
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                DataTable dbScores = new DataTable();
                adapter.Fill(dbScores);

                dgDB.DataSource = dbScores;
                connection.Close();
            }
        }

        private void populateUScapitalDataOnline()
        {
            string conWeb = "Server = remotemysql.com; Port = 3306; database = 2Ds6YxqaDW; username = 2Ds6YxqaDW; password = VJKMThFkcO";
            MySqlConnection connTwo = new MySqlConnection(conWeb);

            using (MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM onUScapitalscores", conWeb))
            {
                try
                {
                    connTwo.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                DataTable dbScores = new DataTable();
                adapter.Fill(dbScores);
                dgOnlineDB.DataSource = dbScores;

                connTwo.Close();
            }
        }

        private void populateUSwolrdDataOnline()
        {
            string conWeb = "Server = remotemysql.com; Port = 3306; database = 2Ds6YxqaDW; username = 2Ds6YxqaDW; password = VJKMThFkcO";
            MySqlConnection connTwo = new MySqlConnection(conWeb);

            using (MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM onUSworldscores", conWeb))
            {
                try
                {
                    connTwo.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                DataTable dbScores = new DataTable();
                adapter.Fill(dbScores);
                dgOnlineDB.DataSource = dbScores;

                connTwo.Close();
            }
        }

        private void populateUSworldDataLocal()
        {
            using (connection = new SqlConnection(connectionString))
            using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM USWorldscores", connection))
            {
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                DataTable dbScores = new DataTable();
                adapter.Fill(dbScores);

                dgDB.DataSource = dbScores;
                connection.Close();
            }
        }

        private void populatePresscoresLocal()
        {
            using (connection = new SqlConnection(connectionString))
            using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM USPresscores", connection))
            {
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                DataTable dbScores = new DataTable();
                adapter.Fill(dbScores);

                dgDB.DataSource = dbScores;
                connection.Close();
            }
        }

        private void populatePresscoresOnline()
        {
            string conWeb = "Server = remotemysql.com; Port = 3306; database = 2Ds6YxqaDW; username = 2Ds6YxqaDW; password = VJKMThFkcO";
            MySqlConnection connTwo = new MySqlConnection(conWeb);

            using (MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM onUSpresscores", conWeb))
            {
                try
                {
                    connTwo.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                DataTable dbScores = new DataTable();
                adapter.Fill(dbScores);
                dgOnlineDB.DataSource = dbScores;

                connTwo.Close();
            }
        }

    }
}