using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Windows.Forms.DataVisualization.Charting;
//MySQL Connection
using MySql.Data.MySqlClient;

namespace Project_3
{
    public partial class Form1 : Form
    {
        public static class Globals
        {
            public static string Gemeente;
            public static string Regiocode;

        }

        public Form1()
        {
            InitializeComponent();
            this.chart1.Series.Add("Blocks");
            this.chart1.Series[0].ChartType = SeriesChartType.Column;

            chart1.ChartAreas[0].AxisX.LabelStyle.Angle = 45;
            chart1.ChartAreas[0].AxisX.Interval = 0.5;

            string connectionString = "datasource=127.0.0.1;port=3306;username=root;password=;database=project3";
            string query = "SELECT Gemeentenaam, Provincienaam FROM Gemeentes";

            MySqlConnection databaseconnection = new MySqlConnection(connectionString);
            MySqlCommand dbquery = new MySqlCommand(query, databaseconnection);
            dbquery.CommandTimeout = 60;
            MySqlDataReader reader;

            try
            {
                databaseconnection.Open();
                reader = dbquery.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        string[] row = { reader.GetString(0), reader.GetString(1)};
                        comboBox1.Items.Add(reader.GetString(0));
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void comboBox1_SelectionChangeCommitted(object sender, EventArgs e)
        {
            Console.WriteLine("Cake");
        }
        public void UpdateChart()
        {

        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if(comboBox1.Text != "")
            {
                string connectionString = "datasource=127.0.0.1;port=3306;username=root;password=;database=project3";
                string gemeentenaam = "SELECT Gemeentecode FROM Gemeentes WHERE Gemeentenaam = '" + comboBox1.Text + "';";

                MySqlConnection databaseConnection = new MySqlConnection(connectionString);
                MySqlCommand dbquery = new MySqlCommand(gemeentenaam, databaseConnection);
                dbquery.CommandTimeout = 60;
                MySqlDataReader reader;
                try
                {
                    databaseConnection.Open();
                    reader = dbquery.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Globals.Gemeente = reader.GetString(0);
                        }
                    }
                    databaseConnection.Close();


                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                databaseConnection.Open();
                string query = "SELECT Regiocode, Regionaam, Gemeente FROM Criminaliteit WHERE Gemeente = '" + Globals.Gemeente + "' AND Regiocode != '" + Globals.Gemeente + "';";
                MySqlCommand criminaliteit = new MySqlCommand(query, databaseConnection);
                MySqlDataReader criminaliteitread;
                criminaliteitread = criminaliteit.ExecuteReader();
                comboBox2.Items.Clear();
                comboBox2.Items.Add("Volledige gemeente");
                if (criminaliteitread.HasRows)
                {
                    while (criminaliteitread.Read())
                    {
                        comboBox2.Items.Add(criminaliteitread.GetString(1));

                    }
                }
                databaseConnection.Close();
            }
        }
        private void comboBox2_SelectedIndexChanged_1(object sender, EventArgs e)
        {
            string query = "";
            if (comboBox2.Text == "Volledige gemeente")
            {
                query = "SELECT * FROM Criminaliteit WHERE Gemeente = '" + Globals.Gemeente + "' AND Regiocode = '" + Globals.Gemeente + "';";
            }
            else
            {
                query = "SELECT * FROM Criminaliteit WHERE Regionaam = '" + comboBox2.Text + "' LIMIT 0, 1";
            }
            string connectionString = "datasource=127.0.0.1;port=3306;username=root;password=;database=project3";
            MySqlConnection databaseConnection = new MySqlConnection(connectionString);
            MySqlCommand dbquery = new MySqlCommand(query, databaseConnection);
            dbquery.CommandTimeout = 60;
            MySqlDataReader reader;

            try
            {
                databaseConnection.Open();
                reader = dbquery.ExecuteReader();
                if (reader.HasRows)
                {
                    int count = reader.FieldCount;
                    while (reader.Read())
                    {
                        List<int> yValues = new List<int>();
                        //List<string> xValues = new List<string>();
                        for (int i = 5; i < 21; i++)
                        {
                            //xValues.Add(reader.GetName(i));
                            yValues.Add(Int32.Parse(reader.GetString(i)));
                            Console.WriteLine(reader.GetValue(i));
                        }
                        string[] xValues = { "Vermogen, vernieling en geweld - Totaal", "Vermogensmisdrijven Totaal", "Diefstal totaal", "Fietsendiefstal", "Diefstal overige vervoersmiddelen", "Diefstal uit of vanaf vervoermiddelen", "Zakkenrollerij, straatroof en beroving", "Woninginbraak", "Diefstal/inbraak uit niet-residentiele gebouwen", "Overige diefstal/inbraak", "Overige vermogensmisdrijven", "Vernielingen en misdrijven tegen openbare orde en gezag", "Gewelds- en seksuele misdrijven - Totaal", "Mishandeling", "Bedreiging en stalking", "Overig geweldsmisdrijven" };
                        chart1.Series[0].Points.DataBindXY(xValues, yValues);
                        label3.Text = "Inwoners: " + reader.GetString(4);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}