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

namespace CRUD {
    class SQL {

        public void delete(string table, string where) {
            MySqlConnection con = connection();
            string sQuery = "DELETE FROM " + table + " WHERE " + where;
            using (MySqlCommand query = new MySqlCommand(sQuery, con)) {
                con.Open();
                int result = query.ExecuteNonQuery();
                if (result < 0) {
                    Console.WriteLine("ERRO!");
                } else {
                    Console.WriteLine("OK!");
                }
                con.Close();
            }
        }

        public void update(string table, string[] columns, string[] values, string where) {
            MySqlConnection con = connection();
            string aux = "";
            string sQuery = "";
            for (int i = 0; i < columns.Length; i++) {
                if (i < (columns.Length - 1)) {
                    aux += columns[i] + "='" + values[i] + "',";
                } else {
                    aux += columns[i] + "='" + values[i] + "'";
                }
            }

            sQuery = "UPDATE " + table + " SET " + aux + " WHERE " + where;
            using (MySqlCommand query = new MySqlCommand(sQuery, con)) {
                con.Open();
                int result = query.ExecuteNonQuery();
                if (result < 0) {
                    Console.WriteLine("ERRO!");
                } else {
                    Console.WriteLine("OK!");
                }
                con.Close();
            }

        }

        public void insert(string table, string[] columns, string[] values) {
            MySqlConnection con = connection();
            string sQuery = "";
            string auxColumns = "";
            string auxValues = "";


            for (int i = 0; i < columns.Length; i++) {
                if (i < (columns.Length - 1)) {
                    auxColumns += "'" + columns[i] + "',";
                } else {
                    auxColumns += columns[i];
                }
            }
            for (int i = 0; i < values.Length; i++) {
                if (i < (values.Length - 1)) {
                    if (values[i].Contains("int*") == true) {
                        auxValues += "" + values[i] + ",";
                    } else {
                        auxValues += "'" + values[i] + ",";
                    }
                } else {
                    auxValues += "'" + values[i] + "'";
                }
            }

            sQuery = "INSERT INTO " + table + " (" + auxColumns + ") VALUES (" + auxValues + ")";
            using (MySqlCommand query = new MySqlCommand(sQuery, con)) {
                con.Open();
                int result = query.ExecuteNonQuery();
                if (result < 0) {
                    Console.WriteLine("ERRO!");
                } else {
                    Console.WriteLine("SUCESSO!");
                }
                con.Close();
            }

        }

        public List<string> Select(string table, string[] columns, string where) {
            MySqlConnection con = connection();
            List<string> results = new List<string>();
            string sQuery = "";
            string auxColumns = "";

            if (columns[0] == "*") {
                auxColumns = "*";
            } else {
                for (int i = 0; i < columns.Length; i++) {
                    if (i < (columns.Length - 1)) {
                        auxColumns += columns[i] + ",";
                    } else {
                        auxColumns += columns[i];
                    }
                }
            }

            if (where != "") {
                sQuery = "SELECT " + auxColumns + " FROM " + table + " WHERE " + where;
            } else {
                sQuery = "SELECT " + auxColumns + " FROM " + table;
            }
            con.Open();
            using (MySqlCommand query = new MySqlCommand(sQuery, con)) {
                using (MySqlDataReader reader = query.ExecuteReader()) {
                    int x = 0;
                    while (reader.Read()) {
                        string aux = "";
                        for (int i = 0; i < reader.FieldCount; i++) {
                            if (i == (reader.FieldCount - 1)) {
                                aux += Convert.ToString(reader.GetValue(i));
                            } else {
                                aux += Convert.ToString(reader.GetValue(i)) + '|';
                            }
                        }
                        results.Add(aux);;
                        x++;
                    }
                    con.Close();
                }
                return results;
            }
        }

        public int intSelect(string table, string[] columns, string where) {
            MySqlConnection con = connection();
            int x = 0;
            string auxColumns = "";

            if (columns[0] == "*") {
                auxColumns = "*";
            } else {
                for (int i = 0; i < columns.Length; i++) {
                    if (i < (columns.Length - 1)) {
                        auxColumns += columns[i] + ",";
                    } else {
                        auxColumns += columns[i];
                    }
                }
            }

            string sQuery = "SELECT " + auxColumns + " FROM " + table + " WHERE " + where;
            MessageBox.Show(sQuery);
            con.Open();
            using (MySqlCommand query = new MySqlCommand(sQuery, con)) {
                using (MySqlDataReader reader = query.ExecuteReader()) {
                    while (reader.Read()) {
                        x++;
                    }
                    con.Close();
                    return x;
                }
            }


        }

    	public MySqlConnection connection() {
    		string host = "localhost";
    		string user = "root";
    		string pass = "";
    		string database = "maco";

    		string sCon = "host='" + host + "';user='" + user + "';password='" + pass + "';database='" + database + "'";
    		MySqlConnection con = new MySqlConnection(sCon);
    		return con;
    	}
    }
}