using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;

namespace Shopper
{
    class Database
    {
    private MySqlConnection connection;
    private string server;
    private string database;
    private string uid;
    private string password;

    public Database()
    {
        Initialize();
    }

    private void Initialize()
    {
        string connectionString;
        connectionString = "SERVER=" + Config.Mysql[0] + ";" + "DATABASE=" + Config.Mysql[3] + ";" + "UID=" + Config.Mysql[1] + ";" + "PASSWORD=" + Config.Mysql[2] + ";";
        connection = new MySqlConnection(connectionString);
    }

    private bool OpenConnection()
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (MySqlException ex)
        {
            switch (ex.Number)
            {
                case 0:
                    MessageBox.Show("Cannot connect to server.  Contact administrator");
                    break;

                case 1045:
                    MessageBox.Show("Mysql Invalid username/password, please try again");
                    break;
            }
            return false;
        }
    }

    private bool CloseConnection()
    {
        try
        {
            connection.Close();
            return true;
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);
            return false;
        }
    }

    private string FilterInjectionChars(string Input)
    {
        Input = Input.Replace(Convert.ToChar(1), ' ');
        Input = Input.Replace(Convert.ToChar(2), ' ');
        Input = Input.Replace(Convert.ToChar(3), ' ');
        Input = Input.Replace(Convert.ToChar(9), ' ');
        return Input;
    }

    private bool Login(string username, string password)
    {
        string query = "SELECT * FROM accounts WHERE Username = '" + FilterInjectionChars(username) + "' and Password = '" + FilterInjectionChars(password) + "'";
        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand(query, connection);
            MySqlDataReader dataReader = cmd.ExecuteReader();
            if (dataReader.NextResult())
            {
                dataReader.Close();
                this.CloseConnection();
                return true;
            }
            else
            {
                dataReader.Close();
                this.CloseConnection();
                return false;
            }
        }
        else
        {
            return false;
        }
    }

    public void InsertProduct(string name, int price)
    {
        string query = "INSERT INTO products (Name, Price) VALUES('" + name + "', '" + price + "')";
        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand(query, connection);
            cmd.ExecuteNonQuery();
            this.CloseConnection();
        }
    }

    public void UpdateProduct(int id, string name, int price)
    {
        string query = "UPDATE product SET Name='" + name + "', Price='" + price + "' WHERE ID='" + id + "'";
        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = query;
            cmd.Connection = connection;
            cmd.ExecuteNonQuery();
            this.CloseConnection();
        }
    }

    public List <string> [] SelectProducts(string query)
    {
        List<string>[] list = new List<string>[3];
        list[0] = new List<string>();
        list[1] = new List<string>();
        list[2] = new List<string>();

        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand(query, connection);
            MySqlDataReader dataReader = cmd.ExecuteReader();
            while (dataReader.Read())
            {
                list[0].Add(dataReader["ID"] + "");
                list[1].Add(dataReader["Name"] + "");
                list[2].Add(dataReader["Price"] + "");
            }
            dataReader.Close();
            this.CloseConnection();
            return list;
        }
        else
        {
            return list;
        }
    }

    public void DeleteProduct(int id)
    {
        string query = "DELETE FROM product WHERE ID='" + id + "'";

        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand(query, connection);
            cmd.ExecuteNonQuery();
            this.CloseConnection();
        }
    }
}