using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace zadanie4
{
    public partial class MainForm : Form
    {
        private MySqlDataAdapter dataAdapter;
        private DataTable prodTable;
        private BindingSource bindingSource = new BindingSource();
        public MainForm()
        {
            InitializeComponent();
            LoadProdData();
            LoadSortOptions();
        }
        private void LoadProdData() // загрузка данных в дгв
        {
            try
            {
                DB db = new DB();
                using (MySqlConnection connection = db.getConnection())
                {
                    connection.Open();
                    string query = "SELECT * FROM Student";

                    dataAdapter = new MySqlDataAdapter(query, connection);
                    prodTable = new DataTable();
                    dataAdapter.Fill(prodTable);
                    dataGridViewProd.RowTemplate.Height = 60;
                    bindingSource.DataSource = prodTable;
                    dataGridViewProd.DataSource = bindingSource;
                    dataGridViewProd.AllowUserToAddRows = false;
                    dataGridViewProd.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
                    dataGridViewProd.ReadOnly = true; // настройки дгв

                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Ошибка при подключении к базе данных или выполнении запроса: " + ex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Произошла общая ошибка: " + ex.Message);
            }

        }

        private void MainForm_Load(object sender, EventArgs e)
        {

        }

        private async void btnAdd_Click(object sender, EventArgs e)
        {
            DB db = new DB();

            DataTable table = new DataTable();

            MySqlDataAdapter adapter = new MySqlDataAdapter();

            db.openConnection();

            MySqlCommand commandAdd = new MySqlCommand("INSERT INTO Student (Student_FirstName, Student_LastName, Student_MiddleName, Student_GroupNum, Student_Special, Student_Phone)" + "VALUES (@Surname, @Name, @Patronymic, @Group, @Special, @Phone);", db.getConnection());
            commandAdd.Parameters.AddWithValue("@Surname", textBoxSurname.Text);
            commandAdd.Parameters.AddWithValue("@Name", textBoxName.Text);
            commandAdd.Parameters.AddWithValue("@Patronymic", textBoxPatronymic.Text);
            commandAdd.Parameters.AddWithValue("@Group", textBoxGroup.Text);
            commandAdd.Parameters.AddWithValue("@Special", textBoxSpecial.Text);
            commandAdd.Parameters.AddWithValue("@Phone", textBoxPhone.Text);

            int rowsAffected = await commandAdd.ExecuteNonQueryAsync();

            if (rowsAffected > 0)
            {
                MessageBox.Show("Запись о студенте добавлена.");
                LoadProdData();

                textBoxSurname.Text = "";
                textBoxName.Text = "";
                textBoxPatronymic.Text = "";
                textBoxGroup.Text = "";
                textBoxSpecial.Text = "";
                textBoxPhone.Text = "";

            }
            else
            {
                MessageBox.Show("Ошибка при добавлении студента");
                db.closeConnection();
            }
        }

        private void dataGridViewProd_Click(object sender, EventArgs e)
        {
            SurnameEdit.Text = dataGridViewProd.CurrentRow.Cells[1].Value.ToString();
            NameEdit.Text = dataGridViewProd.CurrentRow.Cells[2].Value.ToString();
            PatronymicEdit.Text = dataGridViewProd.CurrentRow.Cells[3].Value.ToString();
            GroupEdit.Text = dataGridViewProd.CurrentRow.Cells[4].Value.ToString();
            SpecialEdit.Text = dataGridViewProd.CurrentRow.Cells[5].Value.ToString();
            PhoneEdit.Text = dataGridViewProd.CurrentRow.Cells[6].Value.ToString();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            DB db = new DB();

            DataTable table = new DataTable();

            MySqlDataAdapter adapter = new MySqlDataAdapter();

            db.openConnection();

            MySqlCommand commandEdit = new MySqlCommand(" UPDATE Student SET Student_FirstName = @Surname, Student_LastName = @Name, Student_MiddleName = @Patronymic, Student_GroupNum = @Group, Student_Special = @Special, Student_Phone = @Phone WHERE Student_Id = @id;", db.getConnection());
            commandEdit.Parameters.AddWithValue("@id", Convert.ToInt32(dataGridViewProd.CurrentRow.Cells[0].Value));
            commandEdit.Parameters.AddWithValue("@Surname", SurnameEdit.Text);
            commandEdit.Parameters.AddWithValue("@Name", NameEdit.Text);
            commandEdit.Parameters.AddWithValue("@Patronymic", PatronymicEdit.Text);
            commandEdit.Parameters.AddWithValue("@Group", GroupEdit.Text);
            commandEdit.Parameters.AddWithValue("@Special", SpecialEdit.Text);
            commandEdit.Parameters.AddWithValue("@Phone", PhoneEdit.Text);

            int rowsAffected = commandEdit.ExecuteNonQuery();

            if (rowsAffected > 0)
            {
                MessageBox.Show("Запись о студенте отредактирована.");
                LoadProdData();

                textBoxSurname.Text = "";
                textBoxName.Text = "";
                textBoxPatronymic.Text = "";
                textBoxGroup.Text = "";
                textBoxSpecial.Text = "";
                textBoxPhone.Text = "";

            }
            else
            {
                MessageBox.Show("Ошибка при добавлении студента");
                db.closeConnection();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DB db = new DB();

            DataTable table = new DataTable();

            MySqlDataAdapter adapter = new MySqlDataAdapter();

            db.openConnection();

            MySqlCommand commandDelete = new MySqlCommand(" Delete FROM Student WHERE Student_Id = @id;", db.getConnection());
            commandDelete.Parameters.AddWithValue("@id", Convert.ToInt32(dataGridViewProd.CurrentRow.Cells[0].Value));
            

            int rowsAffected = commandDelete.ExecuteNonQuery();

            if (rowsAffected > 0)
            {
                MessageBox.Show("Запись о студенте удалена.");
                LoadProdData();
            }
            else
            {
                MessageBox.Show("Ошибка при добавлении студента");
                db.closeConnection();
            }
        }

        private void LoadSortOptions()
        {
            comboBoxSort.Items.Add("По умолчанию");
            comboBoxSort.Items.Add("Студенты (А-Я)");
            comboBoxSort.Items.Add("Cтуденты (Я-А)");
            comboBoxSort.Items.Add("Специальность (А-Я)");
            comboBoxSort.Items.Add("Специальность (Я-А)");
            comboBoxSort.Items.Add("Группа (А-Я)");
            comboBoxSort.Items.Add("Группа (Я-А)");

            comboBoxSort.SelectedIndex = 0;
        }

        private void comboBoxSort_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sortOption = comboBoxSort.SelectedItem.ToString();

            switch (sortOption)
            {
                case "По умолчанию":
                    bindingSource.Sort = "";
                    break;
                case "Студенты (А-Я)":
                    bindingSource.Sort = "Student_FirstName ASC";
                    break;
                case "Студенты (Я-А)":
                    bindingSource.Sort = "Student_FirstName DESC";
                    break;
                case "Специальность (А-Я)":
                    bindingSource.Sort = "Student_Special ASC";
                    break;
                case "Специальность (Я-А)":
                    bindingSource.Sort = "Student_Special DESC";
                    break;
                case "Группа (А-Я)":
                    bindingSource.Sort = "Student_GroupNum ASC";
                    break;
                case "Группа (Я-А)":
                    bindingSource.Sort = "Student_GroupNum DESC";
                    break;
            }
        }
    }
}