namespace BrokerBP
{
    public class BrokerBP
    {
        private SqlConnection connection;

        public void Connect()
        {
            try
            {
                connection = new SqlConnection("Data Source=ALEXA;Initial Catalog=0000_ProSoft;Integrated Security=True;Connect Timeout=30;Encrypt=True;Trust Server Certificate=True;Application Intent=ReadWrite;Multi Subnet Failover=False");
                connection.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Nije uspostavljena veza");
                throw;
            }
        }
        public void Disconect()
        {
            connection.Close();
        }

        public BindingList<MuzickiInstrument> VratiInstrumente()
        {
            string upit = "SELECT * FROM INSTRUMENT";
            SqlCommand cmd = new SqlCommand(upit, connection);
            SqlDataReader dr = cmd.ExecuteReader();
            BindingList<MuzickiInstrument> instrumenti = new BindingList<MuzickiInstrument>();  
            while (dr.Read())
            {
                MuzickiInstrument instrument = new MuzickiInstrument();
                instrument.sifra = (int)dr["id"];
                instrument.naziv = (string)dr["naziv"];
                instrumenti.Add(instrument);
            }
            return instrumenti;
        }

        public BindingList<Osoba> VratiOsobe()
        {
            string upit = "SELECT * FROM Osoba ORDER BY IME";
            SqlCommand cmd = new SqlCommand(upit, connection);
            SqlDataReader dr = cmd.ExecuteReader();
            BindingList<Osoba> osobe = new BindingList<Osoba>();
            while (dr.Read())
            {
                Osoba osoba = new Osoba();
                osoba.jmbg = (string)dr["jmbg"];
                osoba.ime = (string)dr["ime"];
                osoba.prezime = (string)dr["prezime"];
                osobe.Add(osoba);
            }
            return osobe;
        }

        public BindingList<Osoba> OsobeKojeSvirajuInstrument(string nazivInstrumenta)
        {
            string upit = "SELECT jmbg, ime, prezime FROM Osoba INNER JOIN OsobaInstrument ON Osoba.jmbg = OsobaInstrument.idOsoba INNER JOIN Instrument ON OsobaInstrument.idIntrument = Instrument.id WHERE INSTRUMENT.NAZIV = @nazivInstrumenta";            
            SqlCommand cmd = new SqlCommand(upit, connection);
            cmd.Parameters.AddWithValue("@nazivInstrumenta", nazivInstrumenta);
            SqlDataReader dr = cmd.ExecuteReader();
            BindingList<Osoba> osobe = new  BindingList<Osoba>();   
            while (dr.Read())
            {
                Osoba osoba = new Osoba();
                osoba.jmbg = (string)dr["jmbg"];
                osoba.ime = (string)dr["ime"];
                osoba.prezime = (string)dr["prezime"];
                osobe.Add(osoba);
            }
            return osobe;
        }

        public Boolean SviraInstrument(string ime, string instrument)
        {
            string upit = "SELECT jmbg, ime, prezime\r\nFROM Osoba\r\nINNER JOIN OsobaInstrument ON Osoba.jmbg = OsobaInstrument.idOsoba\r\nINNER JOIN Instrument ON OsobaInstrument.idIntrument = Instrument.id\r\nWHERE Instrument.naziv = @instrument AND Osoba.ime = @ime";
            SqlCommand cmd = new SqlCommand(upit, connection);
            cmd.Parameters.AddWithValue("@instrument", instrument);
            cmd.Parameters.AddWithValue("@ime", ime);
            if (cmd.ExecuteScalar() ==null) 
                return false;
            return true;
        }

        public BindingList<MuzickiInstrument> vratiInstrumenteKojiSviraOsoba(string osoba)
        {
            string upit = "SELECT Instrument.naziv, Instrument.id\r\nFROM Osoba\r\nINNER JOIN OsobaInstrument ON Osoba.jmbg = OsobaInstrument.idOsoba\r\nINNER JOIN Instrument ON OsobaInstrument.idIntrument = Instrument.id\r\nWHERE Osoba.ime = @ime";
            SqlCommand cmd = new SqlCommand(upit, connection);
            cmd.Parameters.AddWithValue("@ime", osoba);
            SqlDataReader dr = cmd.ExecuteReader();
            BindingList<MuzickiInstrument> isntrumenti = new BindingList<MuzickiInstrument>();
            while (dr.Read())
            {
                MuzickiInstrument mi = new MuzickiInstrument();
                mi.sifra = (int)dr["id"];
                mi.naziv = (string)dr["naziv"];
                isntrumenti.Add(mi);    
            }
            return isntrumenti;
        }

        public void obrisiInstrumentKojiSvira(string instrument,string osoba)
        {
            string upit = "DELETE OsobaInstrument\r\nFROM OsobaInstrument\r\nINNER JOIN Osoba on Osoba.jmbg = OsobaInstrument.idOsoba\r\nINNER JOIN Instrument on Instrument.id = OsobaInstrument.idIntrument\r\nWHERE idOsoba = @jmbg AND idIntrument = @sifra";
            SqlCommand cmd = new SqlCommand(upit, connection);
            cmd.Parameters.AddWithValue("@jmbg", osoba);
            cmd.Parameters.AddWithValue("@sifra", instrument);  
            cmd.ExecuteNonQuery();
        }

        public void dodajInstrument(string idOsobe,string idInstrumenta)
        {
            string upit = "insert into OsobaInstrument (idOsoba,idIntrument) values(@jmbg,@sifra)";
            SqlCommand cmd = new SqlCommand(upit, connection);
            cmd.Parameters.AddWithValue("@jmbg", idOsobe);
            cmd.Parameters.AddWithValue("@sifra", idInstrumenta);
            cmd.ExecuteNonQuery();
        }
    }
}

namespace PoslovnaLogika
{
    using BrokerBP;
    using Domen;
    using System.ComponentModel;
    using System.Numerics;

    public class Kontroler
    {
        BrokerBP broker = new BrokerBP();

        public BindingList<MuzickiInstrument> VratiInstrumente()
        {
            try
            {
                broker.Connect();
                return broker.VratiInstrumente();
            }
            catch(Exception e)
            {
                throw;
            }
            finally
            {
                broker.Disconect();
            }
        }
}

namespace VezbaInstrument
{
    public partial class Instrimenti : Form
    {
        BindingList<MuzickiInstrument> instrumenti = new BindingList<MuzickiInstrument>();
        BindingList<MuzickiInstrument> instrumentiSvi = new BindingList<MuzickiInstrument>();
        Kontroler kontroler = new Kontroler();
        Osoba osoba = new Osoba();
        public Instrimenti(Osoba osoba)
        {
            this.osoba = osoba;
            InitializeComponent();
            tbOsoba.Text = osoba.ToString();
            tbOsoba.Enabled = false;
            instrumenti = kontroler.VratiInstrumenteKojeSviraOsoba(osoba.ime);
            instrumentiSvi = kontroler.VratiInstrumente();
            foreach (MuzickiInstrument inst in instrumentiSvi)
            {
                cbIntrumenti.Items.Add(inst);
            }
            dgv.DataSource = instrumenti;
        }

        private void btObrisi_Click(object sender, EventArgs e)
        {
            DialogResult result = MessageBox.Show("Jeste li sigurni da želite da obrišete?",
                                      "Potvrda brisanja",
                                      MessageBoxButtons.YesNo,
                                      MessageBoxIcon.Question);

            if (dgv.SelectedRows.Count > 0 && result == DialogResult.Yes)
            {
                int n = dgv.SelectedRows.Count;
                for (int i = 0; i < n; i++)
                {
                    int j = dgv.SelectedRows[0].Index;
                    string idInstrumenta = instrumenti[j].vratiSifru();
                    kontroler.ObrisiInstrument(idInstrumenta, osoba.jmbg);
                    instrumenti.RemoveAt(j);
                }
            }
        }

        private void btDodaj_Click_1(object sender, EventArgs e)
        {
            int index = cbIntrumenti.SelectedIndex + 1;
            string idInstrumenta = index.ToString();
            kontroler.DodajInstrrument(idInstrumenta, osoba.jmbg);
            instrumenti.Add(instrumentiSvi[cbIntrumenti.SelectedIndex]);
        }

        private void dgv_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
    }
}

namespace VezbaInstrument
{
    public partial class Form1 : Form
    {
        BindingList<MuzickiInstrument> instrumenti = new BindingList<MuzickiInstrument>();
        BindingList<Osoba> osobeInstrument = new BindingList<Osoba>();
        BindingList<Osoba> osobe = new BindingList<Osoba>();
        Kontroler kontroler = new Kontroler();
        public Form1()
        {
            InitializeComponent();
            instrumenti = kontroler.VratiInstrumente();
            foreach (MuzickiInstrument mi in instrumenti)
            {
                cbInstrumenti.Items.Add(mi.naziv);
            }
            osobe = kontroler.VratiOsobe();
            foreach (Osoba osoba in osobe)
            {
                cbOsobe.Items.Add(osoba.ime);
            }
        }

        private void cbInstrumenti_SelectedIndexChanged(object sender, EventArgs e)
        {
            osobeInstrument = kontroler.VratiOsobeKojeSvirajuInstrument(cbInstrumenti.Text);
            dgv.DataSource = osobeInstrument;
            tbBrojLjudi.Text = osobeInstrument.Count.ToString();
        }

        private void cbOsobe_SelectedIndexChanged(object sender, EventArgs e)
        {
            cbxSvira.Checked = false;
            if (kontroler.DaLiSvira(cbOsobe.Text, cbInstrumenti.Text))
            {
                cbxSvira.Checked = true;
            }
        }

        private void btObrisi_Click(object sender, EventArgs e)
        {
            Osoba os = new Osoba();
            foreach (Osoba osoba in osobe)
            {
                if (osoba.ime == cbOsobe.Text)
                    os = osoba;
            }
            Instrimenti inst = new Instrimenti(os);
            inst.ShowDialog();
        }

        private void cbxSvira_CheckedChanged(object sender, EventArgs e)
        {

        }
    }
}

namespace Forma1.Utills
{
    internal class InzenjerUtil
    {
        public static List<Inzenjer> Iznenjeri { get; set; } = new List<Inzenjer> {
            new Inzenjer
            {
                ime = "Aleksa",
                prezime = "Ljujic",
                korisnickoIme = "aleksa",
                lozinka = "1234"
            },
            new Inzenjer
            {
                ime = "Krisitna",
                prezime = "Ljujic",
                korisnickoIme = "krisitna",
                lozinka = "KristinaKrava"
            },
             new Inzenjer
            {
                ime = "Stipe",
                prezime = "Miocic",
                korisnickoIme = "stipee",
                lozinka = "JonesKurva"
            },
              new Inzenjer
            {
                ime = "Jon",
                prezime = "Jones",
                korisnickoIme = "jonbitch",
                lozinka = "duckingAspinal"
            }
        };

    }
}