using System;
using System.Data;
using System.Reflection;
using System.Diagnostics;

using MySql;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace WmData
{
	/// <summary>
	/// This class is like the handy assistent of the data adapter class
	/// it creates non existent tables in the MySql database.
	/// 
	/// Created 19th of May 2006
	/// </summary>
	public class WmDataBaseCreator {
		public WmDataBaseCreator(){}

		/// <summary>
		/// Checks if a given table is already present in the database
		/// </summary>
		/// <returns>True if exists</returns>
		public static bool isTableExistent(string tableName,WmDataBaseCredentials wmDataBaseCredentials){
			bool exists						= false;
			string query = "show tables from " + wmDataBaseCredentials.Catalog;
			MySqlConnection connection		= new MySqlConnection(wmDataBaseCredentials.getConnectionString());
			MySqlCommand showTableCommand	= new MySqlCommand(query,connection );
			try{
				connection.Open();
				MySqlDataReader	dataReader;
				dataReader					= showTableCommand.ExecuteReader();
				while(dataReader.Read()){
					string dBTableName		= (string) dataReader[0];
					if(dBTableName.Equals(tableName)){
						exists				= true;
						break;
					}
				}
			}catch(Exception error){
				Trace.WriteLine("WmDataBaseCreator: While attempting to find out if a table exits, something went wrong: "+error.Message);
			}finally{
				connection.Close();
			}
			return exists;
		}
   
		/// <summary>
		/// Creates a table in the database for the given objecttype
		/// </summary>
		/// <param name="objectType">ObjectType to create a table for.</param>
		public static void createTable(Type objectType, WmDataBaseCredentials dataBaseCredentials) {
			MySqlConnection connection = new MySqlConnection(dataBaseCredentials.getConnectionString());
			string tableName = objectType.Name.ToLower(); // it would be neater if this was a static call to data.getTableName()
			string sqlCreateTable	= "CREATE TABLE `"+tableName+"` (";

			foreach(PropertyInfo property in objectType.GetProperties()){
				if(WmDataTools.checkDataNeed(property.PropertyType)){
					sqlCreateTable		+= "`"+property.Name.ToLower()+"` "+getMySqlDataType(property)+ ",";
				}
			}
			sqlCreateTable	+= "PRIMARY KEY(`id`) ) ENGINE=InnoDB;";

			MySqlCommand tableCommand = new MySqlCommand(sqlCreateTable,connection);
			
			try{
				connection.Open();
				tableCommand.ExecuteNonQuery();
				Trace.WriteLine("WmDataBaseCreator: Ooh lord, I am soo good, created table "+tableName);
			}catch(Exception error){
				throw new Exception("WmDataBaseCreator: Something went wrong while creating table "+tableName+ ". "+error.Message+" "+Environment.NewLine+Environment.NewLine+" "+sqlCreateTable);
			}finally{
				tableCommand.Connection.Close();
				connection.Close();
			}

		}

        /// <summary>
        /// Returns a DataTable that matches the structure of the SQL table. 
        /// </summary>
        public static DataTable getDataTable(Type objectType, WmDataBaseCredentials databaseCredentials) {
            DataTable returnTable = null;
            MySqlConnection connection = new MySqlConnection(databaseCredentials.getConnectionString());
            string tableName = objectType.Name.ToLower(); // it would be neater if this was a static call to data.getTableName()
            string sqlCommand = "SELECT * FROM " + databaseCredentials.Catalog + "." + tableName + " LIMIT 0";

            MySqlCommand command = new MySqlCommand(sqlCommand, connection);
            try {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();
                DataTable databaseTable = new DataTable();
                databaseTable.Load(reader);


                foreach (DataColumn dataColumn in databaseTable.Columns) {
                    if (dataColumn.MaxLength == 0 && dataColumn.DataType.Equals(typeof(string))) {
                        dataColumn.MaxLength = -1; // this is a MySQL .NET bug
                      //  Trace.WriteLine("WmDataBaseCreator: Circumventing MySQL bug");
                    }
                }


                returnTable = databaseTable.Clone();

                DataColumn[] tableKeys = new DataColumn[1];
                tableKeys[0] = returnTable.Columns["id"];
                returnTable.PrimaryKey = tableKeys;


            } catch (Exception error) {
                Trace.WriteLine("WmDataBaseCreator: While retrieving DataTable from database backend, something went wrong: " + error.Message);
            } finally {
                connection.Close();
            }
            return returnTable;
        }

		protected static string getMySqlDataType(PropertyInfo property){
			Type propType			= property.PropertyType;

			// First step is to find out what kind of datatype where dealing with:
			if(propType.Equals(typeof(int))){
				// Integer
				if(property.Name.ToLower().Equals("id")){
					return "INTEGER NOT NULL DEFAULT 0";
				}
				return "INTEGER";
			}else if(propType.Equals(typeof(double))){
				// double
				return "DOUBLE";
			}else if(propType.Equals(typeof(long))){
				// long
				return "BIGINT";
			}else if(propType.Equals(typeof(bool))){
				// bool
				return "BOOLEAN";
			}else if(propType.Equals(typeof(DateTime))){
				// DateTime
				return "DATETIME";
			}else if(propType.Equals(typeof(string))){
				// Some kind of string
				if(property.Name.ToLower().Equals("description") ||
					property.Name.ToLower().Equals("body") ||
					property.Name.ToLower().Equals("ordercontent")||
					property.Name.ToLower().Equals("legacyordercontent")||
					property.Name.ToLower().Equals("cssstylestring") ||
					property.Name.ToLower().Equals("headerhtml")||
					property.Name.ToLower().Equals("rightcolumnhtml")||
					property.Name.ToLower().Equals("footerhtml") ||
					property.Name.ToLower().Equals("customernumber")||
					property.Name.ToLower().Equals("companyname")||
					property.Name.ToLower().Equals("street")||
					property.Name.ToLower().Equals("housenumber")||
					property.Name.ToLower().Equals("housenumberaddition")||
					property.Name.ToLower().Equals("postalcode")||
					property.Name.ToLower().Equals("city")||
					property.Name.ToLower().Equals("country")||
					property.Name.ToLower().Equals("phone1")||
					property.Name.ToLower().Equals("phone2")||
					property.Name.ToLower().Equals("fax")||
					property.Name.ToLower().Equals("email")||
					property.Name.ToLower().Equals("extramessage")||
                    property.Name.ToLower().Equals("longtext") ||
                    property.Name.ToLower().Equals("shorttext") ||
					property.Name.ToLower().Equals("submitterinfo")||
                    property.Name.ToLower().Equals("specification") ||
                    property.Name.ToLower().Equals("billtext") ||
                    property.Name.ToLower().Equals("introductiontext") ||
                    property.Name.ToLower().Equals("detailtext") ||
                    property.Name.ToLower().Equals("customheading") ||
                    property.Name.ToLower().Equals("customheader") ||
                    property.Name.ToLower().Equals("column1text") ||
                    property.Name.ToLower().Equals("column2text") ||
					property.Name.ToLower().Equals("internalnote")	||
                    property.Name.ToLower().Equals("propertyvalue") ||
                    property.Name.ToLower().StartsWith("str") ||
                    (property.Name.ToLower().IndexOf("text") != -1) ||
                    (property.Name.ToLower().IndexOf("body") != -1) ||
                    (property.Name.ToLower().IndexOf("description") != -1) ||
                    (property.Name.ToLower().IndexOf("note") != -1) ||
                    (property.Name.ToLower().IndexOf("specification") != -1) 
                )
                    
                {
                    
					// Assume that this kind of string will need some space in DB
					// LONGTEXT silles voes plets :)
					return "TEXT";
                } else if (property.Name.ToLower().Equals("namevaluequerystring")) {
                    return "LONGTEXT";
                } else {
                    // Assume this is a normal string. Give it a VARCHAR space of max 1000 chars.
                    return "VARCHAR(1000)";
                }
			}
			return "NULL"; // Will surely cause a nice exception...
		}
	}
}