using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using MySql.Data.MySqlClient;

namespace WmData {
    /// <summary>
    /// This class was designed to do the same as WmDataIdManager, only better.
    /// Created on 11th of july 2009 @ 11.28hrs
    /// </summary>
    public class WmDataIdManager {
        protected object lockObject;
        protected string idTableName = "wmdataidmanager";
        protected WmDataBaseCredentials databaseCredentials;

        public WmDataIdManager(WmDataBaseCredentials databaseCredentials) {
            this.databaseCredentials = databaseCredentials;

            // check if the table is present, create it if nescesary
            if (!WmDataBaseCreator.isTableExistent(idTableName, databaseCredentials)) { // Id Table does not exist, create one
                createSqlIdTable();
            }

            lockObject = new object(); 
        }

        /// <summary>
        /// Creates and saves a new id for a new record in the table.
        /// </summary>
        public int getNewId(string tableName) {
            int returnId = -1;

            string selectMaxIdSql = "SELECT currentmaxid FROM " + databaseCredentials.Catalog + "." + idTableName + " WHERE tablename='" + tableName + "';";


            object maxIdValue = null;

            using (TimedLock.Lock(lockObject)) { // We lock this section to prevent duplicate id's 
                MySqlConnection connection = new MySqlConnection(databaseCredentials.getConnectionString());
                MySqlCommand currentMaxIdCommand = new MySqlCommand(selectMaxIdSql, connection);
 
                try {
                    connection.Open();
                    maxIdValue = currentMaxIdCommand.ExecuteScalar();
                } catch (Exception error) {
                    Trace.WriteLine("WmDirectDataIdManager: Exception while retrieving current maximum id for table " + tableName + " from the " + idTableName + ". " + error.Message);
                }

                MySqlCommand newIdCommand = new MySqlCommand();
                newIdCommand.Parameters.Add("?tablename", MySqlDbType.VarChar);
                newIdCommand.Parameters.Add("?lastupdate", MySqlDbType.DateTime);
                newIdCommand.Parameters.Add("?currentmaxid", MySqlDbType.Int32);
                newIdCommand.Parameters["?tablename"].Value = tableName;
                newIdCommand.Parameters["?lastupdate"].Value = DateTime.Now;

                if (!(maxIdValue is Int32)) { // there is no record matching this tablename, therefore we'll have to lookup the current maximum id and cache it
                    returnId = getCurrentMaximumId(tableName) + 1; // get the current maximum id from the 'real' table

                    newIdCommand.CommandText = "INSERT INTO " + databaseCredentials.Catalog + "." + idTableName + " (" + idTableName + ".tablename, " + idTableName + ".lastupdate, " +
                                                idTableName + ".currentmaxid) VALUES (?tablename,?lastupdate,?currentmaxid)";
                } else { // update the cached id value
                    returnId = (int)maxIdValue + 1;

                    newIdCommand.CommandText = "UPDATE " + databaseCredentials.Catalog + "." + idTableName + " SET " + idTableName + ".tablename=?tablename, " +
                                                idTableName + ".lastupdate=?lastupdate, " + idTableName + ".currentmaxid=?currentmaxid WHERE tablename=?tablename";

                }
                newIdCommand.Parameters["?currentmaxid"].Value = returnId;
                newIdCommand.Connection = connection;

                try {
                    newIdCommand.ExecuteNonQuery();
                } catch (Exception error) {
                    Trace.WriteLine("WmDirectDataIdManager: Exception while saving the new current maximum id for table " + tableName + ". " + error.Message);
                } finally {
                    connection.Close();
                }

            }

            return returnId;

        }

        /// <summary>
        /// Returns the current maximum id that is actually used in the table
        /// </summary>
        protected int getCurrentMaximumId(string tableName) {
            string selectMaxIdSql = "SELECT MAX(" + tableName + ".id) FROM " + databaseCredentials.Catalog + "." + tableName + ";";
			
            object maxIdValue = null;
            MySqlConnection connection = new MySqlConnection(databaseCredentials.getConnectionString());
			MySqlCommand command = new MySqlCommand(selectMaxIdSql, connection);
			try {
				connection.Open();
				maxIdValue = command.ExecuteScalar();
			} catch (Exception error) {
				Trace.WriteLine("WmDirectDataIdManager: Exception while retrieving current maximum id for table "+tableName+". " + error.Message);
			} finally {
				connection.Close();
			}
			
			if (!(maxIdValue is Int32)) {
				// it might be that the table does not exists, check that:
				// then it is logical that we return 0, since the current max id is 0
				return 0;
			} else {
				return (int)maxIdValue;
			}
		}

        /// <summary>
        /// Creates the table where Id's are cached.
        /// </summary>
        protected void createSqlIdTable() {
            MySqlConnection connection = new MySqlConnection(databaseCredentials.getConnectionString());
            string createTableSql = "CREATE TABLE `" + databaseCredentials.Catalog + "`.`wmdataidmanager` ( " +
                            "`tablename` VARCHAR(200) NOT NULL, " +
                            "`lastupdate` DATETIME NOT NULL, " +
                            "`currentmaxid` INTEGER NOT NULL DEFAULT 0, " +
                            "PRIMARY KEY (`tablename`) " +
                            ")ENGINE = InnoDB;";
            MySqlCommand tableCommand = new MySqlCommand(createTableSql, connection);
            try {
                connection.Open();
                tableCommand.ExecuteNonQuery();
            } catch (Exception error) {
                throw new Exception("WmDirectDataIdManager: Could not create WmDataIdManager SQL Table. " + error.Message);
            } finally {
                connection.Close();
            }
        }

        /// <summary>
        /// Will purge the id manager cache (and force new id's to be looked up based upon existing records)
        /// </summary>
        public void clearIdManagerCache() {
            MySqlConnection connection = new MySqlConnection(databaseCredentials.getConnectionString());
            string createTableSql = "DELETE FROM `" + databaseCredentials.Catalog + "`.`wmdataidmanager`;";
            MySqlCommand tableCommand = new MySqlCommand(createTableSql, connection);
            try {
                connection.Open();
                tableCommand.ExecuteNonQuery();
            } catch (Exception error) {
                throw new Exception("WmDataIdManager: Could not purge WmDataIdManager cache. " + error.Message);
            } finally {
                connection.Close();
            }
            Trace.WriteLine("WmDataIdManager: Purged the id cache.");

        }

    }
}