﻿using LojaNinja.Dominio.Usuarios;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace LojaNinja.RepositorioEF
{
    public class RepositorioUsuariosADO : IUsuarioRepositorio
    {
        private static string ConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["SQLExemploConexao"].ConnectionString;
            }
        }

        public Usuario BuscarUsuarioPorAutenticacao(string email, string senha)
        {

            using (var conexao = new SqlConnection(ConnectionString))
            {

                string sql = "SELECT * FROM Usuario WHERE email=@p_email and senha=@p_senha";

                var comando = new SqlCommand(sql, conexao);
                comando.Parameters.Add(new SqlParameter("p_email", email));
                comando.Parameters.Add(new SqlParameter("p_senha", senha));

                conexao.Open();

                SqlDataReader leitor = comando.ExecuteReader();

                Usuario usuario = null;


                if (leitor.Read())
                {
                    int id = Convert.ToInt32(leitor["Id"]);
                    string senhaL = leitor["senha"] as string;
                    string emailL = leitor["email"] as string;
                    string nome = leitor["nome"] as string;
                    IList<Permissao> permissoes = ObterPermissoesUsuarios(usuario.Id);
       
                    usuario = new Usuario(id, emailL, nome, senhaL, permissoes);
                }

                conexao.Close();

                return usuario;
            }
        }

        public void CadastrarUsuario(Usuario usuario)
        {
            using (TransactionScope scope = new TransactionScope()) { 

                using (var conexao = new SqlConnection(ConnectionString))
                {
                    try { 
                    string sql = "INSERT INTO Usuario (nome, email, senha) VALUES (@p_nome, @p_email, @p_senha); INSERT INTO UsuarioPermissao (Usuario, Permissao) VALUES(IDENT_CURRENT('Usuario'), 2)";

                    var comando = new SqlCommand(sql, conexao);
                    comando.Parameters.Add(new SqlParameter("p_nome", usuario.Nome));
                    comando.Parameters.Add(new SqlParameter("p_email", usuario.Email));
                    comando.Parameters.Add(new SqlParameter("p_senha", usuario.Senha));

                    conexao.Open();
                    comando.ExecuteNonQuery();

                    scope.Complete();

                    }
                    catch (NullReferenceException)
                    {
                        throw;
                    }
                }
            }
        }

        public static List<Usuario> ObterTodosOsUsuarios()
        {
            List<Usuario> usuarios = new List<Usuario>();

            using (var conexao = new SqlConnection(ConnectionString))
            {

                string sql = "SELECT * FROM Usuario";

                var comando = new SqlCommand(sql, conexao);

                conexao.Open();

                SqlDataReader leitor = comando.ExecuteReader();

                Usuario usuario = null;

                while (leitor.Read())
                {
                    string senha = leitor["senha"] as string;
                    string email = leitor["email"] as string;
                    string nome = leitor["nome"] as string;
                    usuario = new Usuario(email, nome, senha);
                    usuarios.Add(usuario);
                }

                conexao.Close();

                return usuarios;
            }

        }

        private IList<Permissao> ObterPermissoesUsuarios(int id)
        {
            IList<Permissao> permissoes = new List<Permissao>();
            using (var conexao = new SqlConnection(ConnectionString))
            {
                using (conexao)
                {
                    string sql = "SELECT * FROM Permissao p JOIN UsuarioPermissao ON Permissao = p.Id JOIN Usuario u ON Usuario = u.Id  WHERE UsuarioPermissao.Usuario = @p_userId";
                    var comando = new SqlCommand(sql, conexao);
                    comando.Parameters.Add(new SqlParameter("p_userId", id));
                    conexao.Open();
                    SqlDataReader leitor = comando.ExecuteReader();
                    while (leitor.Read())
                        permissoes.Add(new Permissao(Convert.ToInt32(leitor["Id"]), leitor["ValorPermissao"].ToString()));
                };
                return permissoes;
            }
        }
    }
}
