using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Forum.AppCode
{
    public class UserMapper
    {
        private static String connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        public static User Find(String username, String password)
        {

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                String sql = "select * from USERS where Username=@username and Password=@password";
                SqlCommand command = new SqlCommand(sql, connection);

                // adaug in hash(?)
                command.Parameters.Add(new SqlParameter("@username", TypeCode.String));
                command.Parameters.Add(new SqlParameter("@password", TypeCode.String));

                // setez valoarea parametrilor
                command.Parameters["@username"].Value = username;
                command.Parameters["@password"].Value = password;

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    reader.Read();
                    if (!reader.HasRows)
                        return null;

                    int id = Convert.ToInt32(reader["Id"]);
                    String firstName = reader["First_name"].ToString();
                    String lastName = reader["Last_name"].ToString();
                    int idRole = Convert.ToInt32(reader["Id_role"]);
                    String email = reader["Email"].ToString();
                    String name = reader["Username"].ToString();

                    return new User(id, firstName, lastName, null, idRole, email, name);
                }

            }
        }

        public static Boolean Insert(User user)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                String sql = "select * from USERS where Username=@username";
                SqlCommand command = new SqlCommand(sql, connection);

                // adaug in hash(?)
                command.Parameters.Add(new SqlParameter("@username", TypeCode.String));

                // setez valoarea parametrilor
                command.Parameters["@username"].Value = user.Username;

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    reader.Read();
                    if (reader.HasRows)
                        return false; // username exists in database
                }

                sql = "insert into USERS values(@firstName, @lastName, @password, @idRole, @email, @username)";
                command = new SqlCommand(sql, connection);

                command.Parameters.Add(new SqlParameter("@firstName", TypeCode.String));
                command.Parameters.Add(new SqlParameter("@lastName", TypeCode.String));
                command.Parameters.Add(new SqlParameter("@password", TypeCode.String));
                command.Parameters.Add(new SqlParameter("@idRole", TypeCode.Int32));
                command.Parameters.Add(new SqlParameter("@email", TypeCode.String));
                command.Parameters.Add(new SqlParameter("@username", TypeCode.String));

                command.Parameters["@firstName"].Value = user.FirstName;
                command.Parameters["@lastName"].Value = user.LastName;
                command.Parameters["@password"].Value = user.Password;
                command.Parameters["@idRole"].Value = 1; // aici ar trebui cumva sa fac o cerere pentru tabelul de roluri si sa imi dea id pentru rol = log in :(
                command.Parameters["@email"].Value = user.Email;
                command.Parameters["@username"].Value = user.Username;

                if (command.ExecuteNonQuery() > 0)
                    return true;
                else
                    return false;

            }
        }

        public static Boolean Delete(User user)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                String sql = "delete from USERS where Id=@id";
                SqlCommand command = new SqlCommand(sql, connection);

                // adaug in hash(?)
                command.Parameters.Add(new SqlParameter("@id", TypeCode.Int32));

                // setez valoarea parametrilor
                command.Parameters["@id"].Value = user.Id;

                if (command.ExecuteNonQuery() > 0)
                    return true;
                else
                    return false;
            }
        }

        public static Boolean UpdateRole(User user, int newRole)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                String sql = "select * from USERS where Id=@id";
                SqlCommand command = new SqlCommand(sql, connection);

                // adaug in hash(?)
                command.Parameters.Add(new SqlParameter("@id", TypeCode.Int32));

                // setez valoarea parametrilor
                command.Parameters["@id"].Value = user.Id;

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    reader.Read();
                    if (reader.HasRows)
                        return false; // username exists in database
                }

                sql = "update USERS set Id_role = @role where Id = @id";
                command = new SqlCommand(sql, connection);

                command.Parameters.Add(new SqlParameter("@id", TypeCode.Int32));

                command.Parameters["@firstName"].Value = user.FirstName;
                command.Parameters["@lastName"].Value = user.LastName;
                command.Parameters["@password"].Value = user.Password;
                command.Parameters["@idRole"].Value = 1; // aici ar trebui cumva sa fac o cerere pentru tabelul de roluri si sa imi dea id pentru rol = log in :(
                command.Parameters["@email"].Value = user.Email;
                command.Parameters["@username"].Value = user.Username;

                if (command.ExecuteNonQuery() > 0)
                    return true;
                else
                    return false;

            }
        }
    }
}