using ParkingApp.Business.Constants;
using ParkingApp.Business.Entities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace ParkingApp.Data.DBOperations
{

    public class UserDBOperations
    {
        // swap DBs around here

        private static String connectionString = Keys.connectionString;
        private SqlDataAdapter dataAdapter = new SqlDataAdapter();
        private String query;
        private int result;
        private static UserDBOperations instance;
        private UserDBOperations() { }
        public static UserDBOperations SharedInstance
        {
            get
            {
                if (instance == null)
                {
                    instance = new UserDBOperations();
                }
                return instance;
            }
        }

        public bool login(String email, String password)
        {

            SqlConnection connection = new SqlConnection();
            SqlCommand command = new SqlCommand();
            DataTable usersTable = new DataTable();
            try
            {

                connection.ConnectionString = connectionString;
                query = "SELECT * FROM [ParkingAppDB].[dbo].[User] WHERE EMAIL = @email AND PASSWORD = @password";
                command.Parameters.AddWithValue("@email", email);
                command.Parameters.AddWithValue("@password", password);
                connection.Open();
                command.Connection = connection;
                command.CommandText = query;
                dataAdapter.SelectCommand = command;
                dataAdapter.Fill(usersTable);
                connection.Close();
                if (usersTable.Rows.Count > 0)
                {
                    dataAdapter.Dispose();
                    return true;
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                dataAdapter.Dispose();

            }
            return false;
        }

        public bool create(String email, String password, String role, String phoneNb, bool isBlocked, int bookings)
        {
            SqlConnection connection = new SqlConnection();
            SqlCommand command = new SqlCommand();
            connection.ConnectionString = connectionString;
            int isBlk = 0;
            if (isBlocked) {
                isBlk = 1;
            }
            try
            {
                connection.Open();
                query = "INSERT INTO [ParkingAppDB].[dbo].[User] (EMAIL,PASSWORD,ROLE,PHONENB,ISBLOCKED,BOOKINGS) VALUES(@email,@password,@role,@phoneNb,@isBlocked,@bookings)";
                command.Parameters.AddWithValue("@email", email);
                command.Parameters.AddWithValue("@password", password);
                command.Parameters.AddWithValue("@role", role);
                command.Parameters.AddWithValue("@phoneNb", phoneNb);
                command.Parameters.AddWithValue("@isBlocked", isBlk);
                command.Parameters.AddWithValue("@bookings", bookings);
                command.Connection = connection;
                command.CommandText = query;
                result = command.ExecuteNonQuery();
                if (result > 0)
                {
                    Console.WriteLine("User created successfully!");
                    return true;
                }
                else
                {
                    Console.WriteLine("err");
                }
                connection.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return false;
        }

        public void update(User user)
        {
            SqlConnection connection = new SqlConnection();
            SqlCommand command = new SqlCommand();
            connection.ConnectionString = connectionString;
            String userRole = "";
            int isBlocked = 0;
            if (user.IsBlocked)
            {
                isBlocked = 1;
            }
            switch (user.Role) {
                case Business.Constants.Roles.admin:
                    {
                        userRole = "admin";
                        break;
                    }
                case Business.Constants.Roles.powerUser:
                    {
                        userRole = "powerUser";
                        break;
                    }
                case Business.Constants.Roles.user:
                    {
                        userRole = "user";
                        break;
                    }
            }
            try
            {

                connection.Open();
                query = "UPDATE [ParkingAppDB].[dbo].[User] SET EMAIL= @email, PASSWORD = @password, ROLE = @role, PHONENB = @phoneNb, ISBLOCKED = @isBlocked, BOOKINGS = @bookings WHERE ID = @id";
                command.Parameters.AddWithValue("@email", user.EmailAddress);
                command.Parameters.AddWithValue("@password", user.Password);
                command.Parameters.AddWithValue("@id", user.Id);
                command.Parameters.AddWithValue("@role", userRole);
                command.Parameters.AddWithValue("@phoneNb", user.PhoneNumber);
                command.Parameters.AddWithValue("@isBlocked", isBlocked);
                command.Parameters.AddWithValue("@bookings", user.Bookings);
                command.Connection = connection;
                command.CommandText = query;
                result = command.ExecuteNonQuery();
                if (result > 0)
                {
                    Console.WriteLine("updated");
                }
                else
                {
                    Console.WriteLine("err");
                }
                connection.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

        }

        public List<User> readAll()
        {
            SqlConnection connection = new SqlConnection();
            SqlCommand command = new SqlCommand();
            DataTable usersTable = new DataTable();
            List<User> users = new List<User>();
            try
            {

                connection.ConnectionString = connectionString;
                query = "SELECT ID as 'Id',EMAIL as 'Email',PASSWORD as 'Password', ROLE as 'Role', PHONENB as 'PhoneNb', ISBLOCKED as 'IsBlocked', BOOKINGS as 'Bookings' FROM [ParkingAppDB].[dbo].[User]";
                connection.Open();
                command.Connection = connection;
                command.CommandText = query;
                dataAdapter.SelectCommand = command;
                dataAdapter.Fill(usersTable);
                connection.Close();
                foreach (DataRow row in usersTable.Rows)
                {
                    int id = (int)row["Id"];
                    string email = row["Email"].ToString();
                    string password = row["Password"].ToString();
                    string roleStr = row["Role"].ToString();
                    string phoneNb = row["PhoneNb"].ToString();
                    int isBlocked = (int)row["IsBlocked"];
                    int bookings = (int)row["Bookings"];
                    bool isBlk = false;
                    if (isBlocked != 0)
                    {
                        isBlk = true;
                    }
                    Roles role = new Roles();
                    if (roleStr == "admin")
                    {
                        role = Roles.admin;
                    }
                    if (roleStr == "powerUser")
                    {
                        role = Roles.powerUser;
                    }
                    if (roleStr == "user")
                    {
                        role = Roles.user;
                    }
                    User userRow = new User(id, role, email, password, phoneNb, isBlk, bookings);
                    users.Add(userRow);
                }
                return users;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return users;
            }
            finally
            {
                dataAdapter.Dispose();

            }
        }

        public User readById(Nullable<int> id)
        {
            User foundUser = new User();
            SqlConnection connection = new SqlConnection();
            SqlCommand command = new SqlCommand();
            DataTable usersTable = new DataTable();
            List<User> resultUser = new List<User>();
            try
            {

                connection.ConnectionString = connectionString;
                query = "SELECT ID as 'Id',EMAIL as 'Email',PASSWORD as 'Password', ROLE as 'Role', PHONENB as 'PhoneNb', ISBLOCKED as 'IsBlocked', BOOKINGS as 'Bookings', EMAIL as 'Email' FROM [ParkingAppDB].[dbo].[User] WHERE ID = @id";
                command.Parameters.AddWithValue("@id", id);
                connection.Open();
                command.Connection = connection;
                command.CommandText = query;
                dataAdapter.SelectCommand = command;
                dataAdapter.Fill(usersTable);
                connection.Close();
                foreach (DataRow row in usersTable.Rows)
                {
                    string email = row["Email"].ToString();
                    string password = row["Password"].ToString();
                    string roleStr = row["Role"].ToString();
                    string phoneNb = row["PhoneNb"].ToString();
                    int isBlocked = (int)row["IsBlocked"];
                    int bookings = (int)row["Bookings"];
                    bool isBlk = false;
                    if (isBlocked != 0)
                    {
                        isBlk = true;
                    }
                    Roles role = new Roles();
                    switch (roleStr)
                    {
                        case "admin":
                            {
                                role = Roles.admin;
                                break;
                            }
                        case "powerUser":
                            {
                                role = Roles.powerUser;
                                break;
                            }
                        default:
                            {
                                role = Roles.user;
                                break;
                            }
                    }
                    int idStrong = id ?? default(int);
                    foundUser = new User(idStrong, role, email, password, phoneNb, isBlk, bookings);

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                dataAdapter.Dispose();
            }
            return foundUser;
        }

        public List<User> searchByEmail(String email)
        {
            SqlConnection connection = new SqlConnection();
            SqlCommand command = new SqlCommand();
            DataTable usersTable = new DataTable();
            List<User> resultUser = new List<User>();
            try
            {

                connection.ConnectionString = connectionString;
                query = "SELECT ID as 'Id',EMAIL as 'Email',PASSWORD as 'Password', ROLE as 'Role', PHONENB as 'PhoneNb', ISBLOCKED as 'IsBlocked', BOOKINGS as 'Bookings' FROM [ParkingAppDB].[dbo].[User] WHERE EMAIL = @email";
                command.Parameters.AddWithValue("@email", email);
                connection.Open();
                command.Connection = connection;
                command.CommandText = query;
                dataAdapter.SelectCommand = command;
                dataAdapter.Fill(usersTable);
                connection.Close();
                foreach (DataRow row in usersTable.Rows)
                {
                    int id = (int)row["Id"];
                    string password = row["Password"].ToString();
                    string roleStr = row["Role"].ToString();
                    string phoneNb = row["PhoneNb"].ToString();
                    int isBlocked = (int)row["IsBlocked"];
                    int bookings = (int)row["Bookings"];
                    bool isBlk = false;
                    if (isBlocked != 0)
                    {
                        isBlk = true;
                    }
                    Roles role = new Roles();
                    switch (roleStr)
                    {
                        case "admin":
                            {
                                role = Roles.admin;
                                break;
                            }
                        case "powerUser":
                            {
                                role = Roles.powerUser;
                                break;
                            }
                        default:
                            {
                                role = Roles.user;
                                break;
                            }
                    }
                    User userRow = new User(id, role, email, password, phoneNb, isBlk, bookings);
                    resultUser.Add(userRow);
                }
                return resultUser;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return resultUser;
            }
            finally
            {
                dataAdapter.Dispose();
            }
        }

        public bool delete(User user)

        {
            SqlConnection connection = new SqlConnection();
            SqlCommand command = new SqlCommand();
            try
            {
                connection.ConnectionString = connectionString;
                query = "DELETE FROM [ParkingAppDB].[dbo].[User] WHERE ID = @id";
                connection.Open();
                command.Connection = connection;
                command.CommandText = query;
                command.Parameters.AddWithValue("@id", user.Id);
                result = command.ExecuteNonQuery();
                if (result > 0)
                {
                    Console.WriteLine("deleted id: " + user.Id.ToString());
                    return true;
                }
                else
                {
                    Console.WriteLine("not deleted");

                }
                connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return false;
        }

        public DataTable ConvertToDatatable(List<User> list)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Id");
            dt.Columns.Add("Email");
            dt.Columns.Add("Phone No");
            dt.Columns.Add("Role");
            dt.Columns.Add("Blocked");

            foreach (var item in list)
            {
                var row = dt.NewRow();
                row["Id"] = item.Id;
                row["Email"] = item.EmailAddress;
                row["Phone No"] = item.PhoneNumber;
                row["Role"] = item.Role.ToString();
                row["Blocked"] = item.IsBlocked;
                dt.Rows.Add(row);
            }

            return dt;
        }

    }
}