using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Threading;
using System.IO;
using System.Collections;
using System.Globalization;
using System.Diagnostics;
using System.Reflection;
using System.Net;
using System.Security;
using System.Security.Cryptography;
using System.Data.OracleClient;

namespace SE290A_BackOfficeSim_CoreApi
{
    class DatabaseHelper
    {
		public static DBaseType DbFormat = DBaseType.MySQL;
		public static string SqlServer = "localhost";
        public static string SqlDatabase = "esl";
		public static string SqlUserId = "root";
		public static string SqlPassword = "";
		private static string SqlEslStatusTable = "EslStatus";
		private static string SqlEventLogTable = "EslEventLog";
        private static string SqlCultureInfo = "en-GB";

        private static DataTable dtLabelStatus = null;
        private static DataTable dtEventLog = null;
        private static Dictionary<string, bool[]> dtLabelStatusChanges = null;

        private static bool UpdateLabelStatusEnabled = false;
        private static bool EventLoggingEnabled = false;

        public static DataTable GetSqlTable(string TableName, DBaseType dbFormat)
        {
            return GetSqlTable(TableName, null, dbFormat);
        }

        public static DataTable GetSqlTable(string TableName, string Condition, DBaseType dbFormat)
        {
            return GetSqlTable(TableName, Condition, -1, dbFormat);
        }

        public static DataTable GetSqlTable(string Table, string Condition, int MaxRows, DBaseType dbFormat)
        {
            return GetSqlTable(Table, "*", Condition, MaxRows, dbFormat);
        }

        public static DataTable GetSqlTable(string Table, string Selection, string Condition, int MaxRows, DBaseType dbFormat)
        {
            if (dbFormat <= DBaseType.CSV)
                throw new Exception("GetSqlTable: " + Table + " (SQL not enabled)");

            DataTable dtTable = null;

            string DatabaseName = SqlDatabase;

            //
            //  If table name contains a dot, it means we use an alternative SQL database
            //
            if (Table.IndexOf('.') > 0)
            {
                DatabaseName = Table.Substring(0, Table.IndexOf('.'));
                Table = Table.Substring(Table.IndexOf('.') + 1);
            }

            if (string.IsNullOrEmpty(Table))
            {
                throw new Exception("No Table selected");
            }

            using (DoaSqlConnection SqlConn = new DoaSqlConnection(SqlServer,
                                                                    DatabaseName,
                                                                    SqlUserId,
                                                                    SqlPassword,
                                                                    "Charset=utf8;convert zero datetime=True", dbFormat))
            {
                string Query;

                SqlConn.Open();

                switch (SqlConn.DbaseType)
                {
                    case DBaseType.MsSQL:
                        Query = "SELECT" + ((MaxRows > 0) ? (" TOP " + MaxRows) : "")
                                        + " " + Selection + " FROM [" + Table + "]"
                                        + ((Condition != null) ? (" WHERE " + Condition) : "");
                        break;

                    case DBaseType.Oracle:
                        Query = "SELECT " + Selection + " FROM [" + Table + "]"
                                + ((MaxRows > 0 || Condition != null) ? " WHERE " : "")
                                + ((MaxRows > 0) ? ("ROWNUM <= " + MaxRows) : "")
                                + ((MaxRows > 0 && Condition != null) ? " AND " : "")
                                + ((Condition != null) ? Condition : "");
                        break;

                    default:
                        Query = "SELECT " + Selection + " FROM [" + Table + "]"
                                + ((Condition != null) ? (" WHERE " + Condition) : "")
                                + ((MaxRows > 0) ? (" LIMIT " + MaxRows) : "");
                        break;
                }

                using (DoaSqlCommand cmd = new DoaSqlCommand(Query, SqlConn))
                {
                    using (DoaSqlDataAdapter da = new DoaSqlDataAdapter(cmd))
                    {
                        // this will query your database and return the result to your datatable
                        da.Fill(out dtTable);
                    }
                }

                SqlConn.Close();
            }

            return dtTable;
        }

        public static int GetSqlColumnNr(DataTable Table, string ColumnName)
        {
            if (Table == null)
                return -1;

            if (string.IsNullOrEmpty(ColumnName))
                return -1;

            if (Table.Columns[ColumnName] == null)
                return -1;

            return Table.Columns[ColumnName].Ordinal;
        }

        public static void UpdateData(string Table, DataRow Row, DataRowState RowState)
        {
            DataColumn[] PrimaryKey = (Row.Table.PrimaryKey.Length != 0) ? Row.Table.PrimaryKey : null;

            UpdateData(Table, Row, RowState, PrimaryKey, null, null);
        }

        public static void UpdateData(string Table, DataRow Row, DataRowState RowState, DataColumn[] PrimaryKey)
        {
            UpdateData(Table, Row, RowState, PrimaryKey, null, null);
        }

        public static void UpdateData(string Table, DataTable dataTable, Dictionary<string, bool[]> changeList)
        {
            UpdateData(Table, dataTable, changeList, true);
        }

        public static void UpdateData(string Table, DataTable dataTable, Dictionary<string, bool[]> changeList, bool AcceptChanges)
        {
            if (dataTable.Rows.Count == 0)
                return;

            DataTable ClonedTable;

            lock (dataTable)    // To free the table as fast as possible we clone the table before updating it
            {
                ClonedTable = dataTable.Clone();

                foreach (DataRow Row in dataTable.Rows)
                {
                    if (Row.RowState != DataRowState.Unchanged)
                    {
                        ClonedTable.ImportRow(Row);

                        if (AcceptChanges)
                            Row.AcceptChanges();
                    }
                }
            }

            if (ClonedTable.Rows.Count == 0)
                return;

            UpdateData(Table, null, DataRowState.Modified, null, ClonedTable, changeList);
        }

        public static int UpdateData(string Table, DataRow Row, DataRowState RowState, DataColumn[] PrimaryKey, DataTable dataTable, Dictionary<string, bool[]> changeList)
        {
            DBaseType dbFormat = DbFormat;

            if (dbFormat == DBaseType.CSV) // = CSV
                dbFormat = DbFormat;

            bool[] ChangedCells;

            int ChangedRows = 0;

            string DatabaseName = SqlDatabase;

            //
            //  If table name contains a dot, it means we use an alternative SQL database
            //
            if (Table.IndexOf('.') > 0)
            {
                DatabaseName = Table.Substring(0, Table.IndexOf('.'));
                Table = Table.Substring(Table.IndexOf('.') + 1);
            }

            using (DoaSqlConnection SqlConn = new DoaSqlConnection(SqlServer,
                                                        DatabaseName,
                                                        SqlUserId,
                                                        SqlPassword,
                                                        "Charset=utf8;convert zero datetime=True", dbFormat))
            {
                SqlConn.Open();

                if (Row == null)
                {
                    string Query = "";

                    PrimaryKey = (dataTable.PrimaryKey.Length != 0) ? dataTable.PrimaryKey : null;

                    SqlConn.BeginTransaction();

                    foreach (DataRow dRow in dataTable.Rows)
                    {
                        if (changeList != null && PrimaryKey != null)
                            ChangedCells = changeList[dRow.ItemArray[PrimaryKey[0].Ordinal].ToString()];
                        else
                            ChangedCells = null;

                        string ChangeQuery = "";

                        if (RowState == DataRowState.Deleted || dRow.RowState == DataRowState.Deleted)
                        {
                            ChangeQuery = UpdateQuery(DataRowState.Deleted, Table, dRow, PrimaryKey, ChangedCells, SqlConn.DbaseType);
                        }
                        else if (dRow.RowState == DataRowState.Modified)
                        {
                            ChangeQuery = UpdateQuery(DataRowState.Modified, Table, dRow, PrimaryKey, ChangedCells, SqlConn.DbaseType);
                        }
                        else if (dRow.RowState == DataRowState.Added)
                        {
                            ChangeQuery = UpdateQuery(DataRowState.Added, Table, dRow, PrimaryKey, ChangedCells, SqlConn.DbaseType);
                        }

                        if (string.IsNullOrEmpty(ChangeQuery))
                            continue;

                        ++ChangedRows;

                        if (!string.IsNullOrEmpty(Query) && dbFormat != DBaseType.Oracle && dbFormat != DBaseType.SQLite && dbFormat != DBaseType.ODBC)
                            Query += ";\r\n";

                        Query += ChangeQuery;

                        if (Query.Length > 100000 || dbFormat == DBaseType.Oracle || dbFormat == DBaseType.SQLite || dbFormat == DBaseType.ODBC)    // Some SQL server give time outs on large queries
                        {
                            using (DoaSqlCommand cmd = new DoaSqlCommand(Query, SqlConn))
                            {
                                cmd.ExecuteNonQuery();
                            }
                            Query = "";
                        }
                    }

                    if (Query.Length > 0)
                    {
                        using (DoaSqlCommand cmd = new DoaSqlCommand(Query, SqlConn))
                        {
                            cmd.ExecuteNonQuery();
                        }
                    }

                    SqlConn.Commit();
                }
                else
                {
                    using (DoaSqlCommand cmd = new DoaSqlCommand("SELECT * FROM [" + Table + "]", SqlConn))
                    {
                        using (DoaSqlDataAdapter da = new DoaSqlDataAdapter(cmd))
                        {
                            using (DoaSqlCommandBuilder b = new DoaSqlCommandBuilder(da))
                            {
                                if (RowState == DataRowState.Modified)
                                {
                                    da.UpdateCommand(UpdateQuery(DataRowState.Modified, Table, Row, PrimaryKey, null, SqlConn.DbaseType));

                                    if (Row.RowState == DataRowState.Unchanged)
                                        Row.SetModified();
                                }
                                else if (RowState == DataRowState.Added)
                                {
                                    da.InsertCommand(UpdateQuery(DataRowState.Added, Table, Row, PrimaryKey, null, SqlConn.DbaseType));

                                    if (Row.RowState == DataRowState.Unchanged)
                                        Row.SetAdded();
                                }
                                else if (RowState == DataRowState.Deleted)
                                {
                                    da.DeleteCommand(UpdateQuery(DataRowState.Deleted, Table, Row, PrimaryKey, null, SqlConn.DbaseType));
                                    Row.Delete();
                                }

                                da.Update(Row);

                            }
                        }
                    }
                }

                SqlConn.Close();
            }

            return ChangedRows;
        }

        /*private static String UpdateQuery(DataRowState RowState, String Table, DataRow row, DataColumn PrimaryKey, bool[] changedCells)
		{
			return UpdateQuery(RowState, Table, row, new DataColumn[] { PrimaryKey }, changedCells);
		}*/

        private static string UpdateQuery(DataRowState RowState, string Table, DataRow row, DataColumn[] PrimaryKey, bool[] changedCells, DBaseType dBaseType)
        {
            string Condition = "";

            if (RowState == DataRowState.Modified || RowState == DataRowState.Deleted)
            {
                if (PrimaryKey != null)
                {
                    for (int i = 0; i < PrimaryKey.Length; ++i)
                    {
                        if (i != 0)
                            Condition += " AND ";

                        string Key = PrimaryKey[i].ColumnName;
                        object KeyValue = row.ItemArray[PrimaryKey[i].Ordinal];

                        Condition += "[" + Key + "]=";

                        if (KeyValue.GetType() == typeof(string))
                            Condition += "'" + KeyValue + "'";
                        else
                            Condition += KeyValue;
                    }
                }
            }

            if (RowState == DataRowState.Added)
            {
                Insert q = new Insert(Table, dBaseType);

                for (int i = 0; i < row.ItemArray.Length; ++i)
                {
                    if (row.Table.Columns[i].AutoIncrement) // Used for auto incrementing ID
                        continue;

                    if (changedCells != null)
                    {
                        if (!changedCells[i])
                            continue;

                        changedCells[i] = false;
                    }

                    q.Add(row.Table.Columns[i].ColumnName, row.ItemArray[i]);
                }

                return q.ToString();
            }
            else if (RowState == DataRowState.Modified)
            {
                Update q = new Update(Table, Condition, dBaseType);

                for (int i = 0; i < row.ItemArray.Length; ++i)
                {
                    if (PrimaryKey != null)
                    {
                        int j = 0;

                        for (j = 0; j < PrimaryKey.Length; ++j)
                        {
                            if (PrimaryKey[j].ColumnName.Equals(row.Table.Columns[i].ColumnName))
                                break;
                        }

                        if (j != PrimaryKey.Length)
                            continue;
                    }

                    if (changedCells != null)
                    {
                        if (!changedCells[i])
                            continue;

                        changedCells[i] = false;
                    }

                    q.Add(row.Table.Columns[i].ColumnName, row.ItemArray[i]);
                }

                return q.ToString();
            }
            else if (RowState == DataRowState.Deleted)
            {
                return "DELETE FROM [" + Table + "] WHERE " + Condition;
            }
            else
            {
                return null;
            }
        }

        public static void UpdateLabelStatusTable()
        {
            if (dtLabelStatus == null || !UpdateLabelStatusEnabled)
                return;

            try
            {
                lock (dtLabelStatus)
                {
                    UpdateData(SqlEslStatusTable, dtLabelStatus, dtLabelStatusChanges);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Saving Label Status: " + ex.Message);
            }
        }

        public static bool UpdateLabelStatus(EslImageInfo EslInfo, bool UpdateStatus)
        {
            try
            {
                DataRow row = null;
                bool[] rowChanges = null;
                object obj, value;
                TypeCode tc;
                Type type;
                bool Changed = false;

                if (dtLabelStatus == null)
                    return false;

                if (EslInfo.MAC == null)
                    return false;

                lock (dtLabelStatus)
                {
                    if (dtLabelStatus.Rows.Contains(EslInfo.MAC))
                    {
                        row = dtLabelStatus.Rows.Find(EslInfo.MAC);
                        rowChanges = dtLabelStatusChanges[EslInfo.MAC];
                    }

                    if (row == null)
                    {
                        row = dtLabelStatus.NewRow();
                        row["MAC"] = EslInfo.MAC;
                        dtLabelStatus.Rows.Add(row);

                        rowChanges = new bool[dtLabelStatus.Columns.Count];
                        rowChanges[GetSqlColumnNr(dtLabelStatus, "MAC")] = true;    // Mark the primary key field as changed, so it will be added in the SQL query
                        dtLabelStatusChanges.Add(EslInfo.MAC, rowChanges);
                        Changed = true;
                    }

                    row.BeginEdit();

                    for (int i = 0; i < EslImageInfo.COLUMNS.Length; ++i)
                    {
                        int ColumnNr;

                        if (EslImageInfo.COLUMNS[i].Key == null)
                            continue;

                        value = ReflectionHelper.GetPropertyValue(EslInfo, EslImageInfo.COLUMNS[i].Property);

                        if (value == null)
                            continue;

                        if (!UpdateStatus && row.RowState != DataRowState.Added && EslImageInfo.COLUMNS[i].Key.Equals("STATUS"))
                            continue;

                        ColumnNr = GetSqlColumnNr(dtLabelStatus, EslImageInfo.COLUMNS[i].Key);

                        obj = row.ItemArray[ColumnNr];

                        tc = Convert.GetTypeCode(value);

                        bool wasNull = Convert.GetTypeCode(obj) == TypeCode.DBNull;

                        if (obj != null && tc != TypeCode.DBNull)
                        {
                            type = value.GetType();

                            if (type == typeof(DateTime))
                            {
                                DateTime time = (DateTime)value;

                                if (time.ToBinary() == 0)
                                    continue;

                                if (Convert.GetTypeCode(obj) == TypeCode.String)
                                {

                                    if (!DateTime.TryParse(obj.ToString(), out DateTime Time))
                                        continue;

                                    obj = Time;
                                }

                                if (!wasNull && time.ToBinary() == ((DateTime)obj).ToBinary())
                                    continue;
                            }
                            else if (type == typeof(float))
                            {
                                if ((float)value == -1F)
                                    continue;

                                value = Convert.ChangeType(value, TypeCode.Decimal);

                                if (!wasNull)
                                {
                                    obj = Convert.ChangeType(obj, TypeCode.Decimal);

                                    if ((decimal)value == (decimal)obj)
                                        continue;
                                }
                            }
                            else if (type == typeof(decimal))
                            {
                                if (value == null)
                                    continue;

                                if (!wasNull)
                                {
                                    obj = Convert.ChangeType(obj, TypeCode.Decimal);

                                    if ((decimal)value == (decimal)obj)
                                        continue;
                                }
                            }
                            else if (type == typeof(ushort))
                            {
                                if ((ushort)value == 0)
                                    continue;

                                if (obj.GetType() == typeof(decimal))
                                {
                                    value = Convert.ChangeType(value, TypeCode.Decimal);

                                    if (!wasNull)
                                    {
                                        obj = Convert.ChangeType(obj, TypeCode.Decimal);

                                        if ((decimal)value == (decimal)obj)
                                            continue;
                                    }
                                }
                                else
                                {
                                    value = Convert.ChangeType(value, TypeCode.Int32);

                                    if (!wasNull)
                                    {
                                        obj = Convert.ChangeType(obj, TypeCode.Int32);

                                        if ((int)value == (int)obj)
                                            continue;
                                    }
                                }
                            }
                            else if (type == typeof(bool))
                            {
                                value = Convert.ChangeType(value, TypeCode.Int32);

                                if (!wasNull)
                                {
                                    if ((int)value == (int)obj)
                                        continue;
                                }
                            }
                            else
                            {
                                if (!wasNull && value.ToString().Equals(obj.ToString()))
                                    continue;

                                if (type != typeof(string))
                                    value = Convert.ChangeType(value, TypeCode.String);
                            }

                            try
                            {
                                row[ColumnNr] = value;
                                rowChanges[ColumnNr] = true;
                                Changed = true;
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine("UpdateStatusRow: " + ex.Message);
                            }

                        }
                    }

                    row.EndEdit();

                    if (Changed)
                        AddEventLogRecord(EslInfo);

                    return Changed;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error Updating Label Status: " + ex.Message);
            }

            return false;
        }

        public static void ParseLabelStatus(ref EslImageInfo EslInfo, DataRow row)
        {
            FieldInfo fInfo = null;
            PropertyInfo pInfo = null;
            Type type;
            object obj;
            TypeCode tc;

            for (int i = 0; i < EslImageInfo.COLUMNS.Length; ++i)
            {
                if (EslImageInfo.COLUMNS[i].Key == null)
                    continue;

                fInfo = typeof(EslImageInfo).GetField(EslImageInfo.COLUMNS[i].Property);

                if (fInfo != null)
                {
                    type = fInfo.FieldType;
                    obj = row[EslImageInfo.COLUMNS[i].Key];
                }
                else
                {
                    pInfo = typeof(EslImageInfo).GetProperty(EslImageInfo.COLUMNS[i].Property);

                    if (pInfo != null)
                    {
                        type = pInfo.PropertyType;
                        obj = row[EslImageInfo.COLUMNS[i].Key];
                    }
                    else
                        continue;
                }

                tc = Convert.GetTypeCode(obj);

                if (type == typeof(IPAddress))
                {

                    if (IPAddress.TryParse(obj.ToString(), out IPAddress ip))
                        obj = ip;
                    else
                        obj = null;
                }
                else if (type == typeof(DateTime))
                {
                    if (tc == TypeCode.DBNull)
                        continue;

                    if (tc == TypeCode.String)
                    {

                        if (!DateTime.TryParse(obj.ToString(), out DateTime Time))
                            continue;

                        obj = Time;
                    }
                }
                else if (type == typeof(float))
                {
                    if (tc == TypeCode.DBNull)
                        continue;


                    if (!float.TryParse(obj.ToString(), out float Single))
                        continue;

                    obj = Single;
                }
                else if (type == typeof(bool))
                {
                    if (tc == TypeCode.DBNull)
                        continue;

                    obj = Convert.ChangeType(obj, typeof(bool));
                }
                else if (tc == TypeCode.String)
                    obj = obj.ToString();
                else if (tc == TypeCode.Int32)
                {
                    if (type == typeof(ushort))
                        obj = Convert.ChangeType(obj, type);
                    else
                        obj = (int)obj;
                }
                else if (tc == TypeCode.Decimal)
                {
                    if (type == typeof(ushort))
                        obj = Convert.ChangeType(obj, typeof(ushort));
                    else
                        obj = Convert.ChangeType(obj, typeof(int));
                }
                else if (tc == TypeCode.Single)
                {
                    if (type == typeof(ushort))
                        obj = Convert.ChangeType(obj, typeof(ushort));
                    else
                        obj = Convert.ChangeType(obj, typeof(int));
                }
                else if (tc != TypeCode.DBNull)
                {
                    if (obj.GetType() == typeof(decimal))
                        obj = Convert.ChangeType(obj, typeof(int));
                    obj = Convert.ChangeType(obj, type);
                }
                else
                    continue;


                if (fInfo != null)
                {
                    fInfo.SetValue(EslInfo, obj);
                }
                else if (pInfo != null)
                {
                    pInfo.SetValue(EslInfo, obj, null);
                }
            }
            return;
        }

        public static bool ReadLabelStatus(ref EslImageInfo EslInfo, string MAC)
        {
            if (dtLabelStatus == null || !dtLabelStatus.Rows.Contains(MAC))
                return false;

            DataRow row = dtLabelStatus.Rows.Find(MAC);

            ParseLabelStatus(ref EslInfo, row);
            return true;
        }

        public static void UpdateStatusTables()
        {
            UpdateLabelStatusTable();
            UpdateEventLogTable();
        }

        public static void CreateSqlTable(string Table, string Query, DBaseType dbFormat)
        {
            if (dbFormat <= DBaseType.CSV)
                throw new Exception("CreateSqlTable: " + Table + " (SQL not enabled)");

            string DatabaseName = SqlDatabase;

            //
            //  If table name contains a dot, it means we use an alternative SQL database
            //
            if (Table.IndexOf('.') > 0)
            {
                DatabaseName = Table.Substring(0, Table.IndexOf('.'));
                Table = Table.Substring(Table.IndexOf('.') + 1);
            }

            using (DoaSqlConnection SqlConn = new DoaSqlConnection(SqlServer,
                                                        DatabaseName,
                                                        SqlUserId,
                                                        SqlPassword,
                                                        "Charset=utf8;convert zero datetime=True", dbFormat))
            {
                SqlConn.Open();

                dbFormat = SqlConn.DbaseType;   // For ODBC the database format is not known until the connection is opened

                bool AutoIncrementId = Query.Contains("IDENTITY(1,1)");

                string QueryString = "CREATE TABLE [" + Table + "] (" + Query + ")";

                using (DoaSqlCommand cmd = new DoaSqlCommand(QueryString, SqlConn))
                {
                    cmd.ExecuteNonQuery();
                }

                if (AutoIncrementId && dbFormat == DBaseType.Oracle)
                {
                    QueryString = "CREATE SEQUENCE \"" + Table + "_seq\" START WITH 1 INCREMENT BY 1";

                    using (DoaSqlCommand cmd = new DoaSqlCommand(QueryString, SqlConn))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    QueryString = "CREATE OR REPLACE TRIGGER \"" + Table + "_seq_tr\" BEFORE INSERT ON \"" + Table + "\" FOR EACH ROW WHEN (NEW.\"ID\" IS NULL OR NEW.\"ID\"=0) BEGIN SELECT \"" + Table + "_seq\".NEXTVAL INTO :NEW.\"ID\" FROM DUAL; END;";

                    using (DoaSqlCommand cmd = new DoaSqlCommand(QueryString, SqlConn))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }

                SqlConn.Close();
            }
        }

        public static DataTable ReadLabelStatusTable()
        {
            try
            {
                UpdateLabelStatusEnabled = false;

                if (dtLabelStatus != null)
                {
                    lock (dtLabelStatus)
                    {
                        dtLabelStatus.Dispose();
                        dtLabelStatus = GetSqlTable(SqlEslStatusTable, DbFormat);
                    }
                }
                else
                {
                    dtLabelStatus = GetSqlTable(SqlEslStatusTable, DbFormat);
                }

                UpdateLabelStatusEnabled = true;
            }
            catch (Exception ex)
            {
                try
                {
                    CreateLabelStatusTable();

                    dtLabelStatus = GetSqlTable(SqlEslStatusTable, DbFormat);
                    UpdateLabelStatusEnabled = true;
                }
                catch
                {
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine("SQL Database offline: " + ex.Message);

                    UpdateLabelStatusEnabled = false;

                    if (DbFormat != DBaseType.SQLite)
                    {
                        Console.ForegroundColor = ConsoleColor.Green;
                        Console.WriteLine("Backup storage: Switching to SQLite");
                        DbFormat = DBaseType.SQLite;
                        ReadLabelStatusTable();
                        return dtLabelStatus;
                    }
                }
            }
            finally
            {
                if (dtLabelStatus == null)
                {
                    dtLabelStatus = new DataTable();

                    for (int i = 0; i < EslImageInfo.COLUMNS.Length; ++i)
                        dtLabelStatus.Columns.Add(EslImageInfo.COLUMNS[i].Key);

                    dtLabelStatus.PrimaryKey = new DataColumn[] { dtLabelStatus.Columns["MAC"] };
                }
            }

            dtLabelStatusChanges = new Dictionary<string, bool[]>();

            try
            {
                if (dtLabelStatus != null)
                {
                    for (int i = 0; i < dtLabelStatus.Rows.Count; ++i)
                    {
                        string MAC = dtLabelStatus.Rows[i]["MAC"].ToString();

                        if (!dtLabelStatusChanges.ContainsKey(MAC))
                        {
                            bool[] Changes = new bool[dtLabelStatus.Columns.Count];
                            dtLabelStatusChanges.Add(MAC, Changes);
                        }
                    }
                }
            }
            catch { }

            return dtLabelStatus;
        }

        public static void CreateLabelStatusTable()
        {
            string QueryString = "[ID] VARCHAR(40), " +
                                 "[MAC] VARCHAR(16) NOT NULL PRIMARY KEY, " +
                                 "[DESCRIPTION] VARCHAR(100), " +
                                 "[VARIANT] VARCHAR(8), " +
                                 "[ONLINE] INT, " +
                                 "[EVENT] VARCHAR(40), " +
                                 "[VALUE] VARCHAR(40), " +
                                 "[MESSAGE] VARCHAR(100), " +
                                 "[SEQNR] VARCHAR(8)"
            ;

            CreateSqlTable(SqlEslStatusTable, QueryString, DbFormat);
        }

        public static int TableMaxID(DataTable Table)
        {
            int MaxId = 0;

            try
            {
                DataRow[] rows = Table.Select("ID = MAX(ID)");

                if (rows != null && rows.Length > 0)
                    MaxId = int.Parse(rows[0]["ID"].ToString()) + 1;
            }
            catch { }

            return MaxId;
        }

        private static string[] EventLogColumns = new string[]
        {
            "ID",
            "PLU",
            "MAC",
            "DESCRIPTION",
            "ONLINE",
            "EVENT",
            "VALUE",
            "MESSAGE",
            "TIME",
        };

        public static void CreateEventLogTable()
        {
            string QueryString = string.Format("[ID] INT IDENTITY(1,1) PRIMARY KEY," +
                                                "[PLU] VARCHAR(13)," +
                                                "[MAC] VARCHAR(17)," +
                                                "[DESCRIPTION] VARCHAR(100)," +
                                                "[ONLINE] INT," +
                                                "[EVENT] VARCHAR(64)," +
                                                "[VALUE] VARCHAR(64)," +
                                                "[MESSAGE] VARCHAR(255)," +
                                                "[TIME] DATETIME"
                                            );

            CreateSqlTable(SqlEventLogTable, QueryString, DbFormat);
        }

        public static DataTable ReadEventLogTable()
        {
            EventLoggingEnabled = false;

            try
            {
                dtEventLog = GetSqlTable(SqlEventLogTable, "1=1 ORDER BY [ID] DESC", 100, DbFormat);
                dtEventLog.Columns["ID"].AutoIncrement = true;
                dtEventLog.Columns["ID"].AutoIncrementSeed = TableMaxID(dtEventLog) + 1;
                dtEventLog.Columns["ID"].AutoIncrementStep = 1;
                EventLoggingEnabled = true;
            }
            catch (Exception)
            {
                try
                {
                    CreateEventLogTable();

                    dtEventLog = GetSqlTable(SqlEventLogTable, DbFormat);
                    dtEventLog.Columns["ID"].AutoIncrement = true;
                    dtEventLog.Columns["ID"].AutoIncrementSeed = 1;
                    dtEventLog.Columns["ID"].AutoIncrementStep = 1;
                    EventLoggingEnabled = true;
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Creating event log table: " + ex.Message);
                }
            }
            finally
            {
                if (dtEventLog == null)
                {
                    dtEventLog = new DataTable();
                    dtEventLog.Clear();

                    for (int i = 0; i < EventLogColumns.Length; ++i)
                        dtEventLog.Columns.Add(EventLogColumns[i]);

                    dtEventLog.PrimaryKey = new DataColumn[] { dtEventLog.Columns["ID"] };
                    dtEventLog.Columns["ID"].AutoIncrement = true;
                    dtEventLog.Columns["ID"].AutoIncrementSeed = 1;
                    dtEventLog.Columns["ID"].AutoIncrementStep = 1;
                }
            }

            return dtEventLog;
        }

        public static void AddEventLogRecord(EslImageInfo EslInfo)
        {
            try
            {
                DataTable EventLogTable = dtEventLog;

                if (EventLogTable != null)
                {
                    DataRow row = null;

                    lock (EventLogTable)
                    {
                        row = EventLogTable.NewRow();

                        EventLogTable.Rows.Add(row);

                        if (row != null)
                        {
                            row["PLU"] = EslInfo.Id;
                            row["MAC"] = EslInfo.MAC;
                            row["DESCRIPTION"] = EslInfo.Description;
                            row["ONLINE"] = EslInfo.Online;
                            row["EVENT"] = EslInfo.Event;
                            row["VALUE"] = EslInfo.Value;
                            row["MESSAGE"] = EslInfo.Message;
                            row["TIME"] = DateTime.Now;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("AddEventLogRecord failed: " + ex.Message);
            }
        }

        public static void UpdateEventLogTable()
        {
            try
            {
                if (!EventLoggingEnabled || dtEventLog == null)
                    return;

                lock (dtEventLog)
                {
                    UpdateData(SqlEventLogTable, dtEventLog, null);
                }
            }
            catch (Exception ex)
            {
                Trace.WriteLine("Saving Event Log: " + ex.Message);
            }
        }

        public static void DropTable(string Table, bool AutoIncrement)
        {
            if (DbFormat <= (decimal)DBaseType.CSV || string.IsNullOrEmpty(Table)) // SQL
                return;

            try
            {
                using (DoaSqlConnection SqlConn = new DoaSqlConnection(SqlServer,
                                                        SqlDatabase,
                                                        SqlUserId,
                                                        SqlPassword,
                                                        "Charset=utf8", DbFormat))
                {
                    SqlConn.Open();

                    string Query = "DROP TABLE IF EXISTS [" + Table + "]";

                    using (DoaSqlCommand cmd = new DoaSqlCommand(Query, SqlConn))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    if (DbFormat == DBaseType.Oracle && AutoIncrement)
                    {
                        try
                        {
                            using (DoaSqlCommand cmd = new DoaSqlCommand("DROP SEQUENCE \"" + Table + "_seq\"", SqlConn))
                            {
                                cmd.ExecuteNonQuery();
                            }
                        }
                        catch { }
                    }

                    SqlConn.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error dropping table '" + Table + "': " + ex.Message);
            }
        }

        private static bool AppendValue(object key, object value, ref StringBuilder s1, ref StringBuilder s2, DBaseType dBaseType)
        {
            if (value == null)
                return false;

            StringBuilder s = (s1 == s2) ? s1 : s2;

            if (value.GetType() == typeof(DateTime) || key.Equals("TIME"))
            {
                DateTime time;

                if (value.GetType() != typeof(DateTime))
                    DateTime.TryParse((string)value, out time);
                else
                    time = (DateTime)value;

                if (time.ToBinary() == 0)
                    return false;

                s1.Append("[" + key.ToString() + ((s1 == s2) ? "]=" : "]"));

                if (dBaseType == DBaseType.Oracle)
                {
                    s.Append("TO_TIMESTAMP('" + time.ToString("yyyy-MM-dd HH:mm:ss") + "','RRRR-MM-DD HH24:MI:SS')");
                }
                else if (dBaseType == DBaseType.SQLite || dBaseType == DBaseType.DB2)
                {
                    s.Append("'" + time.ToString("yyyy-MM-dd HH:mm:ss") + "'");
                }
                else
                {
                    s.Append("'" + time.ToString("yyyy-MM-ddTHH:mm:ss") + "'");
                }
            }
            else if (value.GetType() == typeof(float))
            {
                if ((float)value == -1F)
                    return false;

                s1.Append("[" + key.ToString() + ((s1 == s2) ? "]=" : "]"));
                s.Append("'" + ((float)value).ToString(CultureInfo.CreateSpecificCulture(SqlCultureInfo)) + "'");
            }
            else if (value.GetType() == typeof(decimal))
            {
                if (value == null)
                    return false;

                s1.Append("[" + key.ToString() + ((s1 == s2) ? "]=" : "]"));
                s.Append("'" + ((decimal)value).ToString(CultureInfo.CreateSpecificCulture(SqlCultureInfo)) + "'");
            }
            else if (value.GetType() == typeof(ushort))
            {
                if ((ushort)value == 0)
                    return false;

                s1.Append("[" + key.ToString() + ((s1 == s2) ? "]=" : "]"));
                s.Append((ushort)value);
            }
            else if (value.GetType() == typeof(DBNull))
            {
                s1.Append("[" + key.ToString() + ((s1 == s2) ? "]=" : "]"));
                s.Append("NULL");
            }
            else if (value.GetType() == typeof(bool))
            {
                s1.Append("[" + key.ToString() + ((s1 == s2) ? "]=" : "]"));
                s.Append("'" + (((bool)value) ? "1" : "0") + "'");
            }
            else if (value.GetType() == typeof(byte[]))
            {
                if (value == null)
                    return false;

                s1.Append("[" + key.ToString() + ((s1 == s2) ? "]=" : "]"));

                byte[] Array = (byte[])value;

                StringBuilder hex = new StringBuilder(Array.Length * 2);

                foreach (byte b in Array)
                    hex.AppendFormat("{0:x2}", b);

                if (dBaseType == DBaseType.MsSQL)
                    s.Append("0x" + hex.ToString());
                else if (dBaseType == DBaseType.PgSQL)
                    s.Append("decode('" + hex.ToString() + "', 'hex')");
                else if (dBaseType == DBaseType.DB2)
                    s.Append("blob(x'" + hex.ToString() + "')");
                else
                    s.Append("x'" + hex.ToString() + "'");
            }
            else
            {
                s1.Append("[" + key.ToString() + ((s1 == s2) ? "]=" : "]"));
                s.Append("'" + value.ToString().Replace("'", "''") + "'");
            }

            s1.Append(",");

            if (s1 != s2)
                s2.Append(",");

            return true;
        }

        public class Insert
        {
            Hashtable args = new Hashtable();
            string table;
            DBaseType dBaseType;

            /// <summary>
            /// Constructs Insert object
            /// </summary>
            /// <param name="table">table name to insert to</param>
            public Insert(string table, DBaseType dBaseType)
            {
                this.table = table;
                this.dBaseType = dBaseType;
            }

            /// <summary>
            /// Adds item to Insert object
            /// </summary>
            /// <param name="name">item name</param>
            /// <param name="val">item value</param>
            public void Add(string name, object val)
            {
                if (name == null || val == null)
                    return;

                args.Add(name, val);
            }

            /// <summary>
            /// Removes item from Insert object
            /// </summary>
            /// <param name="name">item name</param>
            public void Remove(string name)
            {
                try
                {
                    args.Remove(name);
                }
                catch
                {
                    throw new Exception("No such item");
                }
            }

            /// <summary>
            /// Test representatnion of the Insert object (SQL query)
            /// </summary>
            /// <returns>System.String</returns>
            public override string ToString()
            {
                StringBuilder s1 = new StringBuilder();
                StringBuilder s2 = new StringBuilder();

                IDictionaryEnumerator enumInterface = args.GetEnumerator();

                while (enumInterface.MoveNext())
                {
                    AppendValue(enumInterface.Key, enumInterface.Value, ref s1, ref s2, dBaseType);
                }

                s1.Remove(s1.Length - 1, 1);    // Remove last ","
                s2.Remove(s2.Length - 1, 1);    // Remove last ","

                return "INSERT INTO [" + table + "] (" + s1 + ") VALUES (" + s2 + ")";
            }

            /// <summary>
            /// Gets or sets item into Insert object
            /// </summary>
            object this[string key]
            {
                get
                {
                    return args[key];
                }
                set { args[key] = value; }
            }
        }

        public class Update
        {
            Hashtable args = new Hashtable();
            string table;
            string condition;
            DBaseType dBaseType;

            /// <summary>
            /// Constructs Insert object
            /// </summary>
            /// <param name="table">table name to insert to</param>
            public Update(string table, string condition, DBaseType dBaseType)
            {
                this.table = table;
                this.condition = condition;
                this.dBaseType = dBaseType;
            }

            /// <summary>
            /// Adds item to Insert object
            /// </summary>
            /// <param name="name">item name</param>
            /// <param name="val">item value</param>
            public void Add(string name, object val)
            {
                if (name == null || val == null)
                    return;

                args.Add(name, val);
            }

            /// <summary>
            /// Removes item from Insert object
            /// </summary>
            /// <param name="name">item name</param>
            public void Remove(string name)
            {
                try
                {
                    args.Remove(name);
                }
                catch
                {
                    throw new Exception("No such item");
                }
            }

            /// <summary>
            /// Test representatnion of the Insert object (SQL query)
            /// </summary>
            /// <returns>System.String</returns>
            public override string ToString()
            {
                StringBuilder s = new StringBuilder();

                IDictionaryEnumerator enumInterface = args.GetEnumerator();

                while (enumInterface.MoveNext())
                {
                    AppendValue(enumInterface.Key, enumInterface.Value, ref s, ref s, dBaseType);
                }

                if (s.Length == 0)
                    return "";

                s.Remove(s.Length - 1, 1);  // Remove last ","

                return "UPDATE [" + table + "] SET " + s + ((condition != null) ? (" WHERE " + condition) : "");
            }

            /// <summary>
            /// Gets or sets item into Insert object
            /// </summary>
            object this[string key]
            {
                get
                {
                    return args[key];
                }
                set { args[key] = value; }
            }
        }
    }
}