// https://dev.mysql.com/doc/connector-net/en/


using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Text;
using log4net;
using MySql.Data.MySqlClient;

namespace OpenSim.Data.MySQL
{
    public class MySQLRaw
    {
	private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 
	private string m_connectString;

	public MySQLRaw(string connect)
	{
	    m_connectString = connect;
	}

	public int Count(string table)
	{
	    return Count(table, "");
	}
	public int Count(string table, string wher)
	{
	    string query = "SELECT Count(*) FROM " + table;
	    if ("" != wher)
		query = query + " WHERE " + wher;
	    int result = -1;

	    object r = doScalarQuery(query);
        	if (r != null)
        	    result = Convert.ToInt32(r);

	    return result;
	}

	public List< Hashtable > Select(string table, string select, string wher, string order)
	{
	    if ("" == select)
		select = "*";
	    string query = "SELECT " + select + " FROM " + table;
	    if ("" != wher)
		query = query + " WHERE " + wher;
	    if ("" != order)
		query = query + " ORDER BY " + order;

            using (MySqlConnection dbcon = new MySqlConnection(m_connectString))
            {
                dbcon.Open();
		MySqlCommand cmd = new MySqlCommand(query, dbcon);
		MySqlDataReader rdr = cmd.ExecuteReader();
		List<string> names = new List<string>();
		DataTable schema = rdr.GetSchemaTable();
		List< Hashtable > list = new List< Hashtable >();

        	foreach (DataRow row in schema.Rows)
        	{
        	    string tbl = "";
        	    string nm = "";
        	    string tp = "";
        	    foreach (DataColumn col in schema.Columns)
        	    {
        		if ("BaseTableName"	== col.ColumnName)	tbl = row[col].ToString();
        		if ("ColumnName"	== col.ColumnName)	nm  = row[col].ToString();
        		if ("DataType"		== col.ColumnName)	tp  = row[col].ToString();
        	    }
        	    names.Add(nm);
        	}

		while (rdr.Read())
		{
		    Hashtable r = new Hashtable();
		    foreach (string name in names)
		    {
			r[name] = rdr[name];
		    }
		    list.Add(r);
		}

		rdr.Close();
                dbcon.Close();
		return list;
	    }
	}

	private object doScalarQuery(string query)
	{
	    try
	    {
        	using (MySqlConnection dbcon = new MySqlConnection(m_connectString))
        	{
        	    dbcon.Open();
		    MySqlCommand cmd = new MySqlCommand(query, dbcon);
        	    Object ret = cmd.ExecuteScalar();
        	    dbcon.Close();
        	    return ret;
        	}
	    }
	    catch (MySqlException e)
	    {
		m_log.ErrorFormat("[MYSQL RAW]:  Problem connecting to the database {0}", e.Message);
		return null;
	    }
	}

	private void doNonQuery(string query)
	{
            using (MySqlConnection dbcon = new MySqlConnection(m_connectString))
            {
                dbcon.Open();
		MySqlCommand cmd = new MySqlCommand(query, dbcon);
		cmd.ExecuteNonQuery();
                dbcon.Close();
	    }
	}

	public void Insert(string table)
	{
	    string query = "INSERT INTO " + table + " (name, age) VALUES('John Smith', '33')";
	    doNonQuery(query);
	}

	public void Update(string table, string wher)
	{
	    string query = "UPDATE " + table + " SET name='Joe', age='22'";
	    if ("" != wher)
		query = query + " WHERE " + wher;
	    doNonQuery(query);
	}

	public void Delete(string table, string wher)
	{
	    string query = "DELETE FROM " + table;
	    if ("" != wher)
		query = query + " WHERE " + wher;
	    doNonQuery(query);
	}

    }
}