using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Npgsql;

namespace Milestone1
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public class Business
        {
            public string name { get; set; }
            public string state { get; set; }
            public string city { get; set; }
        }

        public MainWindow()
        {
            InitializeComponent();
            getStates();
            addColumnsToGrid();

        }

        public void getStates()
        {
            using (var conn = new NpgsqlConnection("Host=localhost; Username=postgres; Password=reasel111; Database=milestone1db"))
            {
                conn.Open();
                using (var cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "SELECT DISTINCT state FROM business ORDER BY state";
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            stateDropDown.Items.Add(reader.GetString(0));
                        }
                    }
                }
                conn.Close();
            }
        }

        public void addColumnsToGrid()
        {
            DataGridTextColumn col1 = new DataGridTextColumn();
            col1.Header = "Business Name";
            col1.Binding = new Binding("name");
            col1.Width = 255;
            businessGrid.Columns.Add(col1);

            DataGridTextColumn col2 = new DataGridTextColumn();
            col2.Header = "State";
            col2.Binding = new Binding("state");
            businessGrid.Columns.Add(col2);

            DataGridTextColumn col3 = new DataGridTextColumn();
            col3.Header = "City";
            col3.Binding = new Binding("city");
            businessGrid.Columns.Add(col3);


            //businessGrid.Items.Add(new Business() { name = "Tanner", state = "WA", city = "Pullman" });
        }

        private void stateDropDown_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            businessGrid.Items.Clear();
            cityDropDown.Items.Clear();
            if (stateDropDown.SelectedIndex >= 0)
            {
                using (var conn = new NpgsqlConnection("Host=localhost; Username=postgres; Password=reasel111; Database=milestone1db"))
                {
                    conn.Open();
                    using (var cmd = new NpgsqlCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = "SELECT name,state,city FROM business WHERE state = '" + stateDropDown.SelectedItem.ToString() + "';";
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                businessGrid.Items.Add(new Business() { name = reader.GetString(0), state = reader.GetString(1), city = reader.GetString(2) });
                                if(!cityDropDown.Items.Contains(reader.GetString(2)))
                                {
                                    cityDropDown.Items.Add(reader.GetString(2));
                                }

                            }
                        }
                    }
                    conn.Close();
                }
            }
        }

        private void cityDropDown_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            businessGrid.Items.Clear();
            if(stateDropDown.SelectedIndex >= 0 && cityDropDown.SelectedIndex >= 0)
            {
                using (var conn = new NpgsqlConnection("Host=localhost; Username=postgres; Password=reasel111; Database=milestone1db"))
                {
                    conn.Open();
                    using (var cmd = new NpgsqlCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = "SELECT name,state,city FROM business WHERE city = '" + cityDropDown.SelectedItem.ToString() + "' AND state = '" + stateDropDown.SelectedItem.ToString() + "';";
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                businessGrid.Items.Add(new Business() { name = reader.GetString(0), state = reader.GetString(1), city = reader.GetString(2) });
                            }
                        }
                    }
                    conn.Close();
                }
            }
        }
    }
}