using Basket.model;
using Basket.Utils;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Basket.Repository
{
    public class AngajatRepository
    {

        public Angajat FindOne(int id)
        {
            if (id < 0)
                return null;
            SQLiteConnection connection = DatabaseConnection.getConnection();
            string select = "SELECT * FROM Angajat WHERE IdAngajat=@IdAngajat";
            connection.Open();
            using (var cmd = new SQLiteCommand(select, connection))
            {
                var param = cmd.CreateParameter();
                param.ParameterName = "IdAngajat";
                param.Value = id;
                cmd.Parameters.Add(param);

                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        Angajat angj = new Angajat(int.Parse(reader["IdAngajat"].ToString()), (string)reader["Username"].ToString(), (string)reader["Password"].ToString());
                        connection.Close();
                        return angj;
                    }
                    else
                    {
                        connection.Close();
                        return null;
                    }
                }
            }

        }

        public Angajat Delete(int id)
        {
            Angajat angajat = FindOne(id);
            SQLiteConnection connection = DatabaseConnection.getConnection();
            connection.Open();
            if (angajat == null)
            {
                connection.Close();
                return null;
            }
            string select = "DELETE FROM Angajat WHERE IdAngajat=@IdAngajat";
            using (var cmd = new SQLiteCommand(select, connection))
            {
                var param1 = cmd.CreateParameter();
                {
                    param1.ParameterName = "@IdAngajat";
                    param1.Value = id;

                    cmd.Parameters.Add(param1);
                    cmd.ExecuteNonQuery();
                    connection.Close();
                    return angajat;
                }
            }

        }

        public Angajat Save(Angajat angajat)
        {
            SQLiteConnection connection = DatabaseConnection.getConnection();
            connection.Open();
            var select = "INSERT INTO Angajat(Username,Password) VALUES(@Username,@Password)";

            using (var cmd = new SQLiteCommand(select, connection))
            {
                var param1 = cmd.CreateParameter();
                param1.ParameterName = "@Username";
                param1.Value = angajat.Username;

                var param2 = cmd.CreateParameter();
                param2.ParameterName = "@Password";
                param2.Value = angajat.Password;

                cmd.Parameters.Add(param1);
                cmd.Parameters.Add(param2);

                int affectedRows = cmd.ExecuteNonQuery();
                if (affectedRows != 0)
                {
                    string sql = @"select last_insert_rowid()";
                    int lastId = -1;
                    using (var cmd2 = new SQLiteCommand(sql, connection))
                    {
                        lastId = int.Parse(cmd2.ExecuteScalar().ToString());
                    }
                    Angajat ang = new Angajat(lastId, angajat.Username, angajat.Password);
                    connection.Close();
                    return ang;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }


        }


        public Angajat Update(int id, Angajat angj)
        {
            Angajat clon = angj;
            if (id < 0 || angj == null)
            {
                return null;
            }
            SQLiteConnection connection = DatabaseConnection.getConnection();
            connection.Open();
            string select = "UPDATE Angajat set Username=@Username,Password=@Password WHERE IdAngajat=@IdAngajat";
            using (var cmd = new SQLiteCommand(select, connection))
            {
                var param1 = cmd.CreateParameter();
                param1.ParameterName = "@Username";
                param1.Value = angj.Username;

                var param2 = cmd.CreateParameter();
                param2.ParameterName = "@Password";
                param2.Value = angj.Password;

                var param3 = cmd.CreateParameter();
                param3.ParameterName = "@IdAngajat";
                param3.Value = angj.Id;

                cmd.Parameters.Add(param1);
                cmd.Parameters.Add(param2);
                cmd.Parameters.Add(param3);
                int affectedRows = cmd.ExecuteNonQuery();
                if (affectedRows != 0)
                {
                    connection.Close();
                    return clon;
                }
                else
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public List<Angajat> getAll()
        {
            List<Angajat> angajati = new List<Angajat>();
            var querry = "SELECT * FROM Angajat";
            SQLiteConnection connection = DatabaseConnection.getConnection();
            connection.Open();
            using (var cmd = new SQLiteCommand(querry, connection))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Angajat angaj = new Angajat(int.Parse(reader["IdAngajat"].ToString()), (string)reader["Username"].ToString(), (string)reader["Password"].ToString());
                        angajati.Add(angaj);
                    }
                    connection.Close();
                    return angajati;
                }
            }
        }
    }
}