using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DemoVideo.Nbanque;
using DemoVideo.Nutil;
using DemoVideo.NEf;

namespace DemoVideo.Ndal
{
    public class CompteDaoBD : ICompteDao
    {
        private ContextEf context;

        public CompteDaoBD()
        {
            context = new ContextEf();
        }

        public Compte GetCompte(string numero)
        {
            return context.CompteEntities
                .Where(cpt => cpt.Numero.Equals(numero))
                .FirstOrDefault();
        }

        public (string, float) GetNumeroAndSolde(string numero)
        {
            var res = context.CompteEntities
                      .Where(cpt => cpt.Numero.Equals(numero))
                      .Select(cpt => new { cpt.Numero, cpt.Solde })
                      .FirstOrDefault();
            return (res.Numero, res.Solde);
        }

        public Tuple<string, float> GetNumeroAndSoldeTuple(string numero)
        {
            Tuple<string, float> res = context.CompteEntities
                      .Where(cpt => cpt.Numero.Equals(numero))
                      .Select(cpt => new { cpt.Numero, cpt.Solde })
                      .AsEnumerable()
                      .Select(cpt => new Tuple<string, float>(cpt.Numero, cpt.Solde))
                      .FirstOrDefault();
            return (res);
        }

        public string GetInformationCompte()
        {
            StringBuilder res = new StringBuilder();
            res.AppendLine("Information sur les comptes :");
            res.AppendLine("Nombre de comptes :" + context.CompteEntities.Count());
            res.AppendLine("Total des soldes : " + context.CompteEntities.Sum(cpt => cpt.Solde));
            res.AppendLine("Moyenne des soldes : " + context.CompteEntities.Average(cpt => cpt.Solde));
            res.AppendLine("Solde minimum : " + context.CompteEntities.Min(cpt => cpt.Solde));
            res.AppendLine("Solde maximum : " + context.CompteEntities.Max(cpt => cpt.Solde));
            return res.ToString();
        }

        public bool AjouterCompte(Compte cpt)
        {
            context.CompteEntities.Add(cpt);
            int res = context.SaveChanges();
            return res != 0 ? true : false;
        }
        public bool SupprimerCompte(String numero)
        {
            Compte cpt;
            cpt = GetCompte(numero);
            context.CompteEntities.Remove(cpt);
            int res = context.SaveChanges();
            return res != 0 ? true : false;
        }

        public void ModifierCompte(Compte cpt, float montant)
        {
            cpt.Solde += montant;
            context.SaveChanges();
        }

        public Compte GetCompteDirectSql(string numero)
        {
            string [] parametres = { numero };

            string query = "SELECT IdCompte as CompteId," +
                   "C_Numero as Numero," +
                   "C_Solde as Solde," +
                   "C_DateCreation as DateCreation " +
                   "from T_Comptes Where C_Numero=@p0";
            return context.Database.SqlQuery<Compte>(query, parametres).FirstOrDefault<Compte>();

        }


    }
}
