using Oracle.ManagedDataAccess.Client;

using System;
using System.Data;
using System.Windows.Forms;


namespace Stipendia
{

    public partial class Form1 : Form
    {
        string user = "Akivk0";
        string pwd = "system";
        string db = "localhost/";

        string conStringUser;


        public Form1()
        {
            InitializeComponent();
            conStringUser = $"User Id={user};Password={pwd};Data Source={db}/;";
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            PrintMainTable();
        }

        public void PrintMainTable()
        {
            using (OracleConnection connection = new OracleConnection(conStringUser))
            {
                try
                {
                    connection.Open();
                    using (OracleCommand cmd = new OracleCommand("SELECT * FROM AKSENOV", connection))
                    {
                        using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))
                        {
                            DataTable dataTable = new DataTable();
                            adapter.Fill(dataTable);

                            // Привязываем данные к DataGridView
                            mainDataBaseGrid.DataSource = dataTable;
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"Сообщение: {ex.Message}", "Ошибка!");
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (OracleConnection connection = new OracleConnection(conStringUser))
            {
                connection.Open();

                string query =
                @"UPDATE AKSENOV
                SET СТИПЕНДИЯ =
                    CASE
                        WHEN (МАТ + ИНФ + ИН_ЯЗ) / 3 > 4.5 THEN СТИПЕНДИЯ * 1.5
                        WHEN (МАТ + ИНФ + ИН_ЯЗ) / 3 < 4.5 AND (МАТ + ИНФ + ИН_ЯЗ) / 3 > 3.5 THEN СТИПЕНДИЯ * 1.25
                        WHEN (МАТ + ИНФ + ИН_ЯЗ) / 3 < 3.5 AND (МАТ + ИНФ + ИН_ЯЗ) / 3 > 2.5 THEN СТИПЕНДИЯ
                        ELSE 0
                END";

                OracleCommand cmd = new OracleCommand(query, connection);

                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"{ex.Message}", $"Ошибка! Запись не удалена!");
                }

                connection.Close();

                PrintMainTable();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            using (OracleConnection connection = new OracleConnection(conStringUser))
            {
                connection.Open();

                string query =
                @"DELETE FROM aksenov";

                OracleCommand cmd = new OracleCommand(query, connection);

                string query1 =
                @"INSERT INTO AKSENOV SELECT * FROM AKSENOV_COPY";

                OracleCommand cmd1 = new OracleCommand(query1, connection);

                try
                {
                    cmd.ExecuteNonQuery();
                    cmd1.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    MessageBox.Show($"{ex.Message}", $"Ошибка! Запись не удалена!");
                }

                connection.Close();

                PrintMainTable();
            }
        }
    }
}