using System;
using System.Linq;
using System.Collections.Generic;

using System.Windows.Forms;
using MySql.Data.MySqlClient;


	public class Database
	{

		private static MySqlConnection connection = null;

		private static MySqlConnection getConnection(){
			if(connection == null){
				connection = new MySqlConnection("Database=danilko_ola;SslMode=none;Data Source=mysql.danilko.myjino.ru;User Id=045254552_ola;Password=olaola");
			}
			return connection;
		}

		public static QueryResult getQueryResult(MySqlCommand cmd){
			cmd.Connection.Open();
			QueryResult qr;

			try{
				List<String[]> result = new List<String[]>();
				MySqlDataReader reader = cmd.ExecuteReader();

				qr = new QueryResult(reader);
			}finally{
				cmd.Connection.Close();
			}

			return qr;
		}

		public static QueryResult getTablesList(){
			MySqlCommand cmd = new MySqlCommand("SHOW TABLES", getConnection());
			return getQueryResult(cmd);
		}

		public static QueryResult query(String query){
			MySqlCommand cmd = new MySqlCommand(query, getConnection());
			return getQueryResult(cmd);
		}

		public static void updateData(String table, Dictionary<String, String> columns, DataGridViewRow row, String[] values){
			MySqlCommand cmd = new MySqlCommand("UPDATE `"+table+"` SET "+BuildSet(columns, values)+" WHERE "+BuildWhere(columns,row), getConnection());

			cmd.Connection.Open();

			try{
				cmd.ExecuteNonQuery();
			}finally{
				cmd.Connection.Close();
			}
		}

		public static void insertData(String table, Dictionary<String, String> columns, string[] row){
			String cols = "";

			foreach(String column in columns.Keys){
				if(cols.Length > 0){
					cols += ", ";
				}
				cols += "`"+column+"`";
			}

			String values = "";
			foreach(String cell in row){
				if(values.Length > 0){
					values += ", ";
				}


				String data =  cell;
				if(columns.Values.ToList()[row.ToList().IndexOf(cell)].ToLower().Equals("varchar")){
					data = "\""+data+"\"";
				}else if(data == null || data.Equals("")){
					data = "NULL";
				}

				values += data;
			}

			MySqlCommand cmd = new MySqlCommand("INSERT INTO `"+table+"` ("+cols+") VALUES ("+values+")", getConnection());

			cmd.Connection.Open();

			try{
				cmd.ExecuteNonQuery();
			}finally{
				cmd.Connection.Close();
			}

		}

		public static void deleteData(String table, Dictionary<String,String> columns, DataGridViewRow row){
			MySqlCommand cmd = new MySqlCommand("DELETE FROM `"+table+"` WHERE "+BuildWhere(columns, row), getConnection());

			cmd.Connection.Open();
			try{
				cmd.ExecuteNonQuery();
			}finally{
				cmd.Connection.Close();
			}
		}

		public static String BuildWhere(Dictionary<String,String> columns, DataGridViewRow row){
			String query = "";
			for(int i = 0; i < columns.Keys.Count; i++){
				if(query.Length != 0){
					query += " AND ";
				}

				String columnName = columns.Keys.ToList()[i];
				String data = ((String)row.Cells[i].Value);
				if(columns[columnName].ToLower().Equals("varchar") || columns[columnName].ToLower().Equals("datetime")){
					query += "`"+columnName+"` = \""+data+"\"";
				}else if(data == null || data.Equals("")){
					query += "`"+columnName+"` IS NULL";
				}else{
					query += "`"+columnName+"` = "+data;
				}
			}
			return query;
		}

		public static String BuildSet(Dictionary<String,String> columns, String[] row){
			String query = "";
			for(int i = 0; i < columns.Keys.Count; i++){
				String columnName = columns.Keys.ToList()[i];

				String data = row[i];
				if(columns[columnName].ToLower().Equals("varchar") || columns[columnName].ToLower().Equals("datetime")){
					data = "\""+data+"\"";
				}else if(data == null || data.Equals("")){
					data = "NULL";
				}

				if(query.Length != 0){
					query += ", ";
				}

				query += "`"+columnName+"` = "+data;
			}
			return query;
		}

	}