using System;
using System.Data;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;

namespace HorseRacing
{
    public partial class Form1 : Form
    {
        private MySqlConnection conn;
        private string connectionString = "server=localhost;port=3306;username=root;password=root;database=HorseRacingDB";
        public MySqlConnection GetDBConnection()
        {
            return conn;
        }

        public string GetDBConnectionString()
        {
            return connectionString;
        }

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            conn = new MySqlConnection(connectionString);
            conn.Open();
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            conn.Close();
        }

        private void ExecuteQuery(string query)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand(query, conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                dataGridView1.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error executing query: " + ex.Message);
            }
        }

        private void BtnAddOwner_Click(object sender, EventArgs e)
        {
            OwnerForm ownerForm = new OwnerForm(this);  // Create OwnerForm
            ownerForm.ShowDialog();  // Display the OwnerForm as a modal dialog
        }

        private void BtnAddHorses_Click(object sender, EventArgs e)
        {
            HorsesForm horsesForm = new HorsesForm(this);  // Create HorsesForm
            horsesForm.ShowDialog();
        }

        private void BtnAddJockeys_Click(object sender, EventArgs e)
        {
            JockeysForm jockeysForm = new JockeysForm(this);  // Create JockeysForm
            jockeysForm.ShowDialog();
        }

        private void BtnAddRaces_Click(object sender, EventArgs e)
        {
            RacesForm racesForm = new RacesForm(this);  // Create RacesForm
            racesForm.ShowDialog();
        }

        private void BtnAddResults_Click(object sender, EventArgs e)
        {
            ResultsForm resultsForm = new ResultsForm(this);  // Create ResultsForm
            resultsForm.ShowDialog();
        }

        private void Button1_Click(object sender, EventArgs e)
        {
            string query = "SELECT * FROM Owners " +
                           "WHERE OwnerID IN(" +
                           "SELECT OwnerID FROM Horses " +
                           "GROUP BY OwnerID " +
                           "HAVING COUNT(*) > 1)";
            ExecuteQuery(query);
        }

        private void Button2_Click(object sender, EventArgs e)
        {
            string userInput = textBox2.Text;
            string query = "SELECT * FROM Jockeys " +
                           "WHERE Rating > " + userInput;
            ExecuteQuery(query);
        }

        private void Button3_Click(object sender, EventArgs e)
        {
            DateTime startDate = startDateBox3.Value;
            DateTime endDate = startDateBox4.Value;
            string query = "SELECT Races.RaceName, Results.Position, Horses.HorseName, Races.Location " +
                           "FROM Results " +
                           "JOIN Races ON Results.RaceID = Races.RaceID " +
                           "JOIN Horses ON Results.HorseID = Horses.HorseID " +
                           "WHERE Races.RaceDate BETWEEN '" + startDate.Date + "’ AND '" +endDate.Date + "'";
            ExecuteQuery(query);
        }

        private void Button4_Click(object sender, EventArgs e)
        {
            string userInput = textBox5.Text;
            string query = "SELECT Jockeys.JockeyName, SUM(CASE WHEN Results.Position = ‘I’ THEN 1 ELSE 0 END) AS FirstPlaceCount " +
                           "FROM Results " +
                           "JOIN Jockeys ON Results.JockeyID = Jockeys.JockeyID " +
                           "JOIN Races ON Results.RaceID = Races.RaceID " +
                           "WHERE Races.Location = '" + userInput + "' " +
                           "AND Races.RaceDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE() " +
                           "GROUP BY Jockeys.JockeyID " +
                           "ORDER BY FirstPlaceCount DESC " +
                           "LIMIT 3";
            ExecuteQuery(query);
        }
    }
}