MainWindowViewModel

using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Data;
using MVVM_Adressdatenbank.Entities;

namespace MVVM_Adressdatenbank
{
    class MainWindowViewModel : NotifyableBaseObject
    {

        public MainWindowViewModel()
        {
            this.Abfrage = new DelegateCommand((o) => { Personendatenliste.Clear(); Datenabfrage(); });
            this.Speichern = new DelegateCommand((o) => { Datenänderungspeichern(); });
            this.NeueAdresse = new DelegateCommand((o) => { NeuerDatensatz = true; LoadnewData(); });

            Searchable = Personendatenliste;
            Listable = new CollectionViewSource();
            Listable.Source = this.Searchable;
            Listable.Filter += ApplyFilter;



        }


        public bool NeuerDatensatz;


        public bool Anzeigeändern => NeuerDatensatz != true;
        public bool Anzeigeändern1 => NeuerDatensatz;

        #region Collection für Filter und Filter
        public ObservableCollection<PersonenDaten> Searchable { get; set; }
        internal CollectionViewSource Listable { get; set; }
        public ICollectionView GetListable { get => Listable.View; }

        private void ApplyFilter(object sender, FilterEventArgs e)
        {
            PersonenDaten search = (PersonenDaten)e.Item;
            if (string.IsNullOrEmpty(this.Filter) || this.Filter.Length == 0)
            { e.Accepted = true; }
            else
            {
                e.Accepted = search.Fullname.Contains(Filter);
                RaisePropertyChanged();
            }


        }

        private string filter;
        public string Filter
        {
            get => this.filter;
            set
            {
                if (value != filter)
                {
                    this.filter = value;
                    //RaisePropertyChanged();
                    Listable.View.Refresh();
                    RaisePropertyChanged(nameof(Filter));
                }
            }
        }
        #endregion

        #region Colections für Binding und Zugehörige Listen
        private ObservableCollection<PersonenDaten> personendatenliste = new ObservableCollection<PersonenDaten>();
        public ObservableCollection<PersonenDaten> Personendatenliste
        {
            get => personendatenliste;
            set
            {
                if (value != personendatenliste)
                {
                    personendatenliste = value;
                    RaisePropertyChanged();

                }
            }
        }
        private ObservableCollection<Adressdaten> adressDatenliste = new ObservableCollection<Adressdaten>();
        public ObservableCollection<Adressdaten> Adressdatenliste
        {
            get => adressDatenliste;
            set
            {
                if (value != adressDatenliste)
                {
                    adressDatenliste = value;
                    RaisePropertyChanged();
                }
            }
        }

        private ObservableCollection<MailDaten> maildata = new ObservableCollection<MailDaten>();
        public ObservableCollection<MailDaten> Maildata
        {
            get => maildata;
            set
            {
                if (value != maildata)
                {
                    maildata = value;
                    RaisePropertyChanged();
                }
            }
        }

        private ObservableCollection<TelefonDaten> kontaktdatenliste = new ObservableCollection<TelefonDaten>();
        public ObservableCollection<TelefonDaten> Kontaktdatenliste
        {
            get => kontaktdatenliste;
            set
            {
                if (value != kontaktdatenliste)
                {
                    kontaktdatenliste = value;
                    RaisePropertyChanged();

                }
            }
        }

        private ObservableCollection<Adresstypen> adresstypenliste = new();
        public ObservableCollection<Adresstypen> Adresstypenliste
        {
            get => adresstypenliste;
            set
            {
                if (value != adresstypenliste)
                {
                    adresstypenliste = value;
                    RaisePropertyChanged();
                }
            }
        }

        TelefonDaten kontData = new TelefonDaten();
        public TelefonDaten KontData
        {
            get => kontData;
            set
            {
                if (value != kontData)
                {
                    kontData = value;
                    RaisePropertyChanged();
                }
            }
        }

        /// <summary>
        /// Select a single Name and get more information about it.
        /// Clear the ObservableCollections for Adress, Telefon and Mail
        /// </summary>
        PersonenDaten persData = new PersonenDaten();
        public PersonenDaten PersData
        {
            get => persData;
            set
            {
                if (value != persData)
                {
                    persData = value;
                    RaisePropertyChanged();
                    Adressdatenliste.Clear();
                    Adressabfrage(PersData.ID);
                    Kontaktdatenliste.Clear();
                    KontaktDatenabfrage(PersData.ID);
                    Maildata.Clear();
                    MailAdressDatenabfrage(PersData.ID);

                }
            }
        }

        Adressdaten addData = new Adressdaten();
        public Adressdaten AddData
        {
            get => addData;
            set
            {
                if (value != addData)
                {
                    addData = value;
                    RaisePropertyChanged();
                }
            }
        }
        #endregion

        #region DelegateCommands
        public DelegateCommand Abfrage{ get; set; }

        public DelegateCommand Speichern { get; set; }

        public DelegateCommand NeueAdresse { get; set; }
        #endregion
        public event EventHandler Safesucces;

        Datenbankzugriff zugriff = Datenbankzugriff.GetDBZugriff();


        private void LoadnewData()
        {

            RaisePropertyChanged(nameof(Anzeigeändern));
            RaisePropertyChanged(nameof(Anzeigeändern1));
            //Personendatenliste.Clear();
            //kontaktdatenliste.Clear();
            //Adressdatenliste.Clear();

            PersData.Vorname = " ";
            PersData.Nachname = "";
            PersData.Geburtsdatum = "";
            PersData.ID = "";
            PersData.Geburtsname = "";
            AddData.Straße = "";
            AddData.Hausnummer = "";
            AddData.Plz = "";
            AddData.Ort = "";
            AddData.Land = "";
            KontData.Telefonnummer = "";
            KontData.Nummertyp = "";
            Adressdatenliste.Clear();
            Kontaktdatenliste.Clear();



        }
        /// <summary>
        /// Abfrage der Daten und zuordnung zu den ObservableCollections Personendatenliste und Kontakdatenliste
        /// </summary>

        #region Abfragemethoden
        private void Datenabfrage()
        {

            SqliteConnection connect = zugriff.connect();
            //string datenabfrage = "Select ID_Person, Vorname, Nachname, Geburtsdatum, Geburtsname FROM Person";
            string datenabfrage = "Select p.ID_Person, Vorname, Nachname, Geburtsname, Geburtsdatum From Person p ";
            SqliteCommand com = new(datenabfrage, connect);

            connect.Open();
            SqliteDataReader reader = com.ExecuteReader();
            if (reader.HasRows)
                while (reader.Read())
                {
                    Personendatenliste.Add(new PersonenDaten
                    {
                        ID = reader.GetString(reader.GetOrdinal("ID_Person")),
                        Vorname = reader.GetString(reader.GetOrdinal("Vorname")),
                        Nachname = reader.GetString(reader.GetOrdinal("Nachname")),
                        Geburtsdatum = reader.GetString(reader.GetOrdinal("Geburtsdatum")),
                        Geburtsname = reader.GetString(reader.GetOrdinal("Geburtsname")),

                    });
                }


            connect.Close();
        }

        private void Adressabfrage(string identify)
        {

            string abfrage = "Select a.ID_Adresse, Straße, Hausnummer, Postleitzahl, Ort, Land, at.Typ_Name FROM Adresse a JOIN Person_Adresse pa ON pa.ID_Adresse = a.ID_Adresse JOIN Adress_Typ at ON pa.ID_Adress_Typ = at.ID_Adress_Typ  WHERE ID_Person = @ID_Person";
            SqliteConnection connect = zugriff.connect();
            SqliteCommand com = new(abfrage, connect);

            SqliteParameter p1 = new();
            p1.ParameterName = "@ID_Person";
            p1.Value = identify;
            com.Parameters.Add(p1);

            connect.Open();
            SqliteDataReader reader = com.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Adressdatenliste.Add(new Adressdaten
                    {
                        ID_Adresse = reader.GetString(reader.GetOrdinal("ID_Adresse")),
                        Straße = reader.GetString(reader.GetOrdinal("Straße")),
                        Hausnummer = reader.GetString(reader.GetOrdinal("Hausnummer")),
                        Plz = reader.GetString(reader.GetOrdinal("Postleitzahl")),
                        Ort = reader.GetString(reader.GetOrdinal("Ort")),
                        Land = reader.GetString(reader.GetOrdinal("Land")),
                        AdressTyp = reader.GetString(reader.GetOrdinal("Typ_Name"))
                        //Etage = reader.GetString(reader.GetOrdinal("Etage")),
                        //Abteilung = reader.GetString(reader.GetOrdinal("Abteilung")),
                        //Gebäude = reader.GetString(reader.GetOrdinal("Gebäude")),
                    });
                }
            }
        }

        private void MailAdressDatenabfrage(string identify)
        {
            SqliteConnection connect = zugriff.connect();
            string abfrage = "Select e.Mailadresse, mt.Typ_Art_Mail From  EMail e " +
                "JOIN EMail_Typ mt ON e.ID_Email_Typ = mt.ID_Email_Typ " +
                "Where e.ID_Person = @Person";
            SqliteCommand com = new(abfrage, connect);

            SqliteParameter p1 = new();
            p1.ParameterName = "@Person";
            p1.Value = identify;
            com.Parameters.Add(p1);



            connect.Open();

            SqliteDataReader reader = com.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Maildata.Add(new MailDaten
                    {
                        Mailadresse = reader.GetString(reader.GetOrdinal("Mailadresse")),
                        Mailtyp = reader.GetString(reader.GetOrdinal("Typ_Art_Mail"))
                    });
                }
            }
        }

        private void KontaktDatenabfrage(string identify)
        {
            SqliteConnection connect = zugriff.connect();
            string abfrage = "Select t.Nummer,  Typ_Art From Telefon t " +
                "JOIN Telefon_Typ tt ON t.ID_Telefon_Typ = tt.ID_Telefon_Typ " +
                "Where t.ID_Person = @Person";
            SqliteCommand com = new(abfrage, connect);

            SqliteParameter p1 = new();
            p1.ParameterName = "@Person";
            p1.Value = identify;
            com.Parameters.Add(p1);



            connect.Open();

            SqliteDataReader reader = com.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Kontaktdatenliste.Add(new TelefonDaten
                    {
                        Telefonnummer = reader.GetString(reader.GetOrdinal("Nummer")),
                        Nummertyp = reader.GetString(reader.GetOrdinal("Typ_Art")),

                    });
                }
            }
        }
        /// <summary>
        /// Speichern von Änderungen an Pesonendaten und Adressen
        /// </summary>
        private void Datenänderungspeichern()
        {

            string Update = "Update Person Set Vorname = @Vorname, Nachname = @Nachname, Geburtsdatum = @Geburtsdatum WHERE ID_Person = @ID_Person ";
            SqliteConnection connect = zugriff.connect();
            SqliteCommand com = new(Update, connect);

            SqliteParameter vn = new();
            vn.ParameterName = "@Vorname";
            vn.Value = PersData.Vorname;
            com.Parameters.Add(vn);

            SqliteParameter nn = new();
            nn.ParameterName = "@Nachname";
            nn.Value = PersData.Nachname;
            com.Parameters.Add(nn);

            SqliteParameter gd = new();
            gd.ParameterName = "@Geburtsdatum";
            gd.Value = PersData.Geburtsdatum;
            com.Parameters.Add(gd);

            SqliteParameter id = new();
            id.ParameterName = "@ID_Person";
            id.Value = PersData.ID;
            com.Parameters.Add(id);

            connect.Open();
            com.ExecuteNonQuery();
            connect.Close();

            Safesucces?.Invoke(this, EventArgs.Empty);

        }

        public void Adressänderungspeichern()
        {
            string Update = "Update Adresse Set Straße = @Str, Hausnummer = @HNr, Postleitzahl = @PLZ, Ort = @Ort, Land = @Land WHERE ID_Adresse = @ID_Adresse";
            SqliteConnection con = zugriff.connect();
            SqliteCommand com = new(Update, con);

            SqliteParameter st = new();
            st.ParameterName = "@Str";
            st.Value = AddData.Straße;
            com.Parameters.Add(st);

            SqliteParameter hn = new();
            hn.ParameterName = "@HNr";
            hn.Value = AddData.Hausnummer;
            com.Parameters.Add(hn);

            SqliteParameter pl = new();
            pl.ParameterName = "@PLZ";
            pl.Value = AddData.Plz;
            com.Parameters.Add(pl);

            SqliteParameter or = new();
            or.ParameterName = "@Ort";
            or.Value = AddData.Ort;
            com.Parameters.Add(or);

            SqliteParameter la = new();
            la.ParameterName = "@Land";
            la.Value = AddData.Land;
            com.Parameters.Add(la);

            SqliteParameter id = new();
            id.ParameterName = "@ID_Adresse";
            id.Value = AddData.ID_Adresse;
            com.Parameters.Add(id);

            con.Open();
            com.ExecuteNonQuery();
            con.Close();

            Safesucces?.Invoke(this, EventArgs.Empty);
        }

        public void NeueAdresseSpeichern()
        {
            string insert = "Insert Into Adresse(Straße, Hausnummer, Postleitzahl, Ort, Land) Values(@str, @hnr, @plz, @ort, @land)";
            SqliteConnection connect = zugriff.connect();
            SqliteCommand com = new(insert, connect);

            SqliteParameter param = new();
            param.ParameterName = "@str";
            param.Value = AddData.Straße;
            com.Parameters.Add(param);

            SqliteParameter param2 = new();
            param2.ParameterName = "@hnr";
            param2.Value = AddData.Hausnummer;
            com.Parameters.Add(param2);

            SqliteParameter param3 = new();
            param3.ParameterName = "@plz";
            param3.Value = AddData.Plz;
            com.Parameters.Add(param3);

            SqliteParameter param4 = new();
            param4.ParameterName = "@ort";
            param4.Value = AddData.Ort;
            com.Parameters.Add(param4);

            SqliteParameter param5 = new();
            param5.ParameterName = "@land";
            param5.Value = AddData.Land;
            com.Parameters.Add(param5);


            connect.Open();
            com.ExecuteNonQuery();
            connect.Close();


            GetIDAdress();
            SchreibePersonAdresse();


            Safesucces?.Invoke(this, EventArgs.Empty);



        }

        public string ID_Adresse { get; set; }

        public void GetIDAdress()
        {
            SqliteConnection connect = zugriff.connect();
            string abfrage = "Select ID_Adresse FROM Adresse Where ID_Adresse = (Select max(ID_Adresse) FROM Adresse)";

            SqliteCommand com2 = new(abfrage, connect);
            connect.Open();
            SqliteDataReader reader = com2.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                { ID_Adresse = reader.GetString(reader.GetOrdinal("ID_Adresse")); }
            }
            connect.Close();

        }

        public void SchreibePersonAdresse()
        {
            SqliteConnection connect = zugriff.connect();
            string schreibedaten = "Insert into Person_Adresse(ID_Person, ID_Adresse, ID_Adress_Typ) Values(@idpers, @idaddr, @id_ad_typ)";
            SqliteCommand com3 = new(schreibedaten, connect);
            SqliteParameter p1 = new();
            p1.ParameterName = "@idpers";
            p1.Value = PersData.ID;
            com3.Parameters.Add(p1);

            SqliteParameter p2 = new();
            p2.ParameterName = "@idaddr";
            p2.Value = ID_Adresse;
            com3.Parameters.Add(p2);

            SqliteParameter p3 = new();
            p3.ParameterName = "@id_ad_typ";
            p3.Value = "1";
            com3.Parameters.Add(p3);


            connect.Open();
            com3.ExecuteNonQuery();
            connect.Close();
        }

        public void Datenspeichern()
        {
            if (NeuerDatensatz)
            {

                NeueAdresseSpeichern();
            }
            else
            {
                Datenänderungspeichern();
                Adressänderungspeichern();

            }
        }

        #endregion
    }
}