using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public static string user = "***";
        public static string pass = "**";
        public static string ip = "***";
        public static string database = "***";
        public static MySqlConnection conn = new MySqlConnection();

        /*
        Připojování do databaze
        */
        public static void startMYsql()
        {
            string data = "server=" + Form1.ip + ";uid=" + Form1.user + ";pwd=" + Form1.pass + ";database=" + Form1.database + ";";

            if (conn.State != ConnectionState.Open)
            {
                conn.ConnectionString = data;
                conn.Open();
            }
        }
        public Form1()
        {
            InitializeComponent();
            startMYsql();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            refreshList();
        }
        /*
        Načítání prvků z databaze a vložení do listu
        Udělano jako funkce zdůvodu volání v dalších částech programu


        */
        private void refreshList()
        {
            try
            {
                listBox1.Items.Clear();
                MySqlDataReader cti = null;
                string dotaz = "SELECT * FROM country";
                MySqlCommand cmd = new MySqlCommand(dotaz, Form1.conn);
                cti = cmd.ExecuteReader();
                while (cti.Read())
                {
                    listBox1.Items.Add(new Country(cti.GetInt16(0), cti.GetString(1)));
                }
                cti.Close();
            }
            catch
            {
                MessageBox.Show("Někde nastala chyba");
            }
        }
        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            show_cities.Enabled = true;
            try
            {
                Country coun = (Country)listBox1.SelectedItem;
                textBox1.Text = coun.nazev;
                id.Text = coun.id.ToString();
            }
            catch
            {

            }
        }

        /*
        Přidání prvku do databaze
        */
        private void add_country_Click(object sender, EventArgs e)
        {
            try
            {
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "INSERT INTO country(nazev) VALUES(?nazev)";
                command.Parameters.AddWithValue("?nazev", textBox1.Text);
                command.ExecuteNonQuery();
                MessageBox.Show("Hotovo");
                refreshList();
            }
            catch
            {
                MessageBox.Show("Někde nastala chyba");
            }
        }
        /*
        Aktualizace nazvu země
        */
        private void edit_country_Click(object sender, EventArgs e)
        {
            try
            {
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "UPDATE country SET nazev = ?nazev WHERE PK_idc = ?id";
                command.Parameters.AddWithValue("?nazev", textBox1.Text);
                command.Parameters.AddWithValue("?id", id.Text);
                command.ExecuteNonQuery();
                MessageBox.Show("Hotovo");
                refreshList();
            }
            catch
            {
                MessageBox.Show("Někde nastala chyba");
            }
        }

        private void delete_country_Click(object sender, EventArgs e)
        {
            try
            {
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "DELETE FROM country WHERE PK_idc = ?id";
                command.Parameters.AddWithValue("?id", id.Text);
                command.ExecuteNonQuery();
                MessageBox.Show("Hotovo");
                refreshList();
            }
            catch
            {
                MessageBox.Show("Někde nastala chyba");
            }
        }

        private void show_cities_Click(object sender, EventArgs e)
        {
            Country coun = (Country)listBox1.SelectedItem;
            new Form2(coun.id).Show();
        }
    }
    class Country
    {
        public int id;
        public string nazev;
        public Country(int id, string nazev)
        {
            this.id =  id;
            this.nazev = nazev;
        }
        public override string ToString()
        {
            return this.nazev;
        }
    }
}