using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;



public class DatabaseConnector
{

    private MySqlConnection conn;
    private MySqlConnectionStringBuilder conn_string;
    private MySqlCommand cmd;
    private List<String> user = new List<String>();

    private void startConnection()
    {
        conn_string = new MySqlConnectionStringBuilder();
        conn_string.Server = "localhost";
        conn_string.UserID = "root";
        conn_string.Password = "root";
        conn_string.Database = "lab2";
    }

    private void createCommand(String sqlCommand)
    {
        using (conn = new MySqlConnection(conn_string.ToString()))
        using (cmd = conn.CreateCommand())
            cmd.CommandText = string.Format(sqlCommand);
        conn.Open();
    }

    public void insertUser(String name, String email, String street, String password)
    {
        startConnection();
        createCommand("INSERT INTO customers (name, emailAddress, street, password) VALUES('" + name + "','" + email + "','" + street + "','" + password + "')");
        cmd.ExecuteNonQuery();
        conn.Close();
    }
    public void insertBook(String title, String ISBN, String author, String category, float price)
    {
        startConnection();
        createCommand("INSERT INTO books (title, ISBN, author, category, price) VALUES('" + title + "','" + ISBN + "','" + author + "','" + category + "','" + price + "')");
        cmd.ExecuteNonQuery();
        conn.Close();
    }
    public void removeUser(String email) {
        startConnection();
        createCommand("DELETE FROM customers WHERE emailAddress='" + email + "'");
        cmd.ExecuteNonQuery();
        conn.Close();
    }

    public List<String> getUser(String name)
    {
        startConnection();

        createCommand("SELECT name, emailAddress, street FROM customers WHERE name='" + name + "'");

        MySqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            user.Add(reader.GetString(0));
            user.Add(reader.GetString(1));
            user.Add(reader.GetString(2));
        }
        conn.Close();
        return user;
    }
    public Boolean checkUserCredentials(String email, String password)
    {
        startConnection();
        createCommand("SELECT emailAddress, password FROM customers");
        MySqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            if (reader.GetString(0).Equals(email) && reader.GetString(1).Equals(password))
            {
                conn.Close();
                return true;
            }
        }

        conn.Close();
        return false;
    }
    public List<Customer> getCustomers()
    {
        startConnection();
        createCommand("SELECT * FROM customers");
        List<Customer> customers = new List<Customer>();
        MySqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            Customer customer = new Customer(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4));
            customers.Add(customer);
        }
        conn.Close();
        return customers;
    }
    public List<Book> getBooks(String searchResult, String category)
    {
        startConnection();
        if (category == "All" || category == "") {
            createCommand("SELECT * FROM books WHERE title LIKE '%" + searchResult + "%'" + " OR " + "author LIKE '%" + searchResult + "%'");
        }
        else if(category != null){
            createCommand("SELECT * FROM books WHERE (title LIKE '%" + searchResult + "%'" + " OR " + "author LIKE '%" + searchResult + "%')" + " AND category LIKE '" + category + "'");
        }
            List<Book> books = new List<Book>();
            MySqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            Book book = new Book(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetFloat(4));
            books.Add(book);
        }
        conn.Close();
        return books;
    }
    public Boolean checkAdminCredentials(String email, String password)
    {
        startConnection();
        createCommand("SELECT emailAddress, password FROM administrators");
        MySqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            if (reader.GetString(0).Equals(email) && reader.GetString(1).Equals(password))
            {
                conn.Close();
                return true;
            }
        }

        conn.Close();
        return false;
    }

}