using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
using MySql.Data.MySqlClient;

public class SystemDatabase
{
    #region Global Variables
    //GENERIC Database Attributes
    string databaseName;
    string username;
    string password;
    string connectionString;
    string dataSource;
    bool isMySQL;

    //MySQL Database Attributes
    public MySqlConnection sqlConnection;
    MySqlCommand sqlCommand;
    MySqlDataReader sqlReader;

    //MSAccess Database Attributes
    public OleDbConnection accessConnection;
    OleDbCommand accessCommand;
    OleDbDataReader accessReader;
    #endregion

    //Default Database Constructor
    public SystemDatabase(string databaseName, string dataSource, string username, string password, bool isMySQL)
    {
        this.databaseName = databaseName;
        this.dataSource = dataSource;
        this.username = username;
        this.password = password;
        this.isMySQL = isMySQL;

        if (isMySQL)
        {
            connectionString =
                "Database = " + databaseName + ";" +
                "Server = " + dataSource + ";" +
                "uid = " + username + ";" +
                "pwd = " + password;
            sqlConnection = new MySqlConnection(connectionString);
        }
        else
        {
            connectionString =
                "Provider = Microsoft.Jet.OLEDB.4.0;" +
                "Data Source = " + dataSource +
                "Jet OLEDB:Database Password = " + password;
            accessConnection = new OleDbConnection();
        }
    }

    //Database Constructor without password
    public SystemDatabase(string databaseName, string dataSource, string username, bool isMySQL)
    {
        this.databaseName = databaseName;
        this.dataSource = dataSource;
        this.username = username;
        this.password = "";
        this.isMySQL = isMySQL;

        if (isMySQL)
        {
            connectionString =
                "Database = " + databaseName + ";" +
                "Data Source = " + dataSource + ";" +
                "User Id = " + username + ";" +
                "Password = " + password;
            sqlConnection = new MySqlConnection(connectionString);
        }
        else
        {
            connectionString =
                "Provider = Microsoft.Jet.OLEDB.4.0;" +
                "Data Source = " + dataSource;
            accessConnection = new OleDbConnection();
        }

    }

    //Use this for testing the database connection
    public bool testConnection()
    {
        if (isMySQL)
        {
            try
            {
                sqlConnection.Open();
                sqlConnection.Close();
            }
            catch (Exception ex)
            {
                sqlConnection.Close();
                MessageBox.Show(ex.Message);
                return false;
            }
        }
        else
        {
            try
            {
                accessConnection.Open();
                accessConnection.Close();
            }
            catch (Exception ex)
            {
                accessConnection.Close();
                MessageBox.Show(ex.Message);
                return false;
            }

        }
        return true;
    }

    //Use this for executing sql commands
    public bool executeCommand(string sql)
    {
        if (isMySQL)
        {
            try
            {
                sqlCommand = new MySqlCommand(sql, sqlConnection);

                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Dispose();
                sqlConnection.Close();
                return true;
            }
            catch (Exception ex)
            {
                sqlConnection.Close();
                MessageBox.Show(ex.Message);
                return false;
            }
        }
        else
        {
            try
            {
                accessCommand = new OleDbCommand(sql, accessConnection);

                accessConnection.Open();
                accessCommand.ExecuteNonQuery();
                accessCommand.Dispose();
                accessConnection.Close();
                return true;
            }
            catch (Exception ex)
            {
                accessConnection.Close();
                MessageBox.Show(ex.Message);
                return false;
            }
        }
    }

    //Use this to retrieve the first result of a query
    public object retrieveVariable(string sql, string colName)
    {
        object returnObject = null;
        if (isMySQL)
        {
            try
            {
                sqlCommand = new MySqlCommand(sql, sqlConnection);

                sqlConnection.Open();
                sqlReader = sqlCommand.ExecuteReader();

                if (sqlReader.Read())
                {
                    returnObject = sqlReader[colName];
                    sqlReader.Close();
                    sqlReader.Dispose();
                    sqlConnection.Close();
                    sqlCommand.Dispose();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.StackTrace);
                MessageBox.Show(ex.Message);
            }
            sqlReader.Close();
            sqlReader.Dispose();
            sqlConnection.Close();
            sqlCommand.Dispose();
        }
        else
        {
            try
            {
                accessCommand = new OleDbCommand(sql, accessConnection);

                accessConnection.Open();
                accessReader = accessCommand.ExecuteReader();

                if (accessReader.Read())
                {
                    returnObject = accessReader.GetValue(0);
                }


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            accessReader.Close();
            accessReader.Dispose();
            accessConnection.Close();
            accessCommand.Dispose();
        }
        return returnObject;
    }

    //Use this to retrieve specific columns in a row of query result
    public object[] retrieveList(string sql, string[] columns)
    {
        object varTemp;
        List<object> objectList = new List<object>();
        if (isMySQL)
        {
            try
            {
                sqlCommand = new MySqlCommand(sql, sqlConnection);

                sqlConnection.Open();
                sqlReader = sqlCommand.ExecuteReader();

                foreach (string column in columns)
                {
                    sqlReader.Read();
                    varTemp = sqlReader[column];
                    objectList.Add(varTemp);
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            sqlReader.Close();
            sqlConnection.Close();
        }
        else
        {
            try
            {
                accessCommand = new OleDbCommand(sql, accessConnection);

                accessConnection.Open();
                accessReader = accessCommand.ExecuteReader();

                foreach (string column in columns)
                {
                    varTemp = accessReader[column];
                    objectList.Add(varTemp);
                }

            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            accessReader.Close();
            accessConnection.Close();
        }
        return objectList.ToArray();
    }

    //Use this to retrieve all rows in the first column of the query result
    public object[] retrieveList(string sql)
    {
        object varTemp;
        List<object> objectList = new List<object>();
        if (isMySQL)
        {
            try
            {
                sqlCommand = new MySqlCommand(sql, sqlConnection);

                sqlConnection.Open();
                sqlReader = sqlCommand.ExecuteReader();

                while (sqlReader.Read())
                {
                    varTemp = sqlReader[0];
                    objectList.Add(varTemp);
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message +"\n"+sql);
            }

            sqlReader.Close();
            sqlConnection.Close();
        }
        else
        {
            try
            {
                accessCommand = new OleDbCommand(sql, accessConnection);

                accessConnection.Open();
                accessReader = accessCommand.ExecuteReader();

                while (accessReader.Read())
                {
                    varTemp = accessReader[0];
                    objectList.Add(varTemp);
                }

            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            accessReader.Close();
            accessConnection.Close();
        }
        return objectList.ToArray();
    }

    //Use this to get all the values(rows and columns) of the query result
    public object[][] retrieveTableValues(string sql, string[] columns)
    {
        object varTemp;
        List<object> _objectRow = new List<object>();
        List<List<object>> objectColumn = new List<List<object>>();
        object[][] value = null;
        try
        {
            sqlCommand = new MySqlCommand(sql, sqlConnection);

            sqlConnection.Open();
            sqlReader = sqlCommand.ExecuteReader();

            while (sqlReader.Read())
            {
                _objectRow = new List<object>();
                foreach (string column in columns)
                {
                    varTemp = sqlReader[column];
                    _objectRow.Add(varTemp);

                }
                objectColumn.Add(_objectRow);
            }

            object[,] sample = new object[1, 2];


            value = new object[objectColumn.Count][];
            for (int i = 0; i < value.Length; i++)
            {
                value[i] = new object[_objectRow.Count];
            }

            for (int i = 0; i < objectColumn.Count; i++)
            {
                _objectRow = objectColumn.ElementAt(i);
                for (int j = 0; j < _objectRow.Count; j++)
                {
                    value[i][j] = _objectRow.ElementAt(j);
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            sqlReader.Close();
            sqlConnection.Close();
        }
        return value;
    }

    //Database table syncing
    public static void syncTables(MySqlConnection sqlConnection, OleDbConnection accessConnection, string sqlTable, string accessTable, byte columnCount)
    {
        #region Variable Declaration

        //MySQL Variables and Objects
        string sql;
        MySqlCommand sqlCommand;
        MySqlDataReader sqlReader;

        //MSAccess Variables and Objects
        string access;
        OleDbCommand accessCommand;
        OleDbDataReader accessReader;

        //Generic Variables
        //object varTemp;
        bool prelimCheck = false;
        #endregion

        #region Preliminary Checking
        //Check if all values in the table are equal;

        sql = "SELECT * FROM " + sqlTable;
        access = "SELECT * FROM " + accessTable;
        try
        {
            //Initialize Commands
            sqlCommand = new MySqlCommand(sql, sqlConnection);
            accessCommand = new OleDbCommand(access, accessConnection);

            //Open Connection
            sqlConnection.Open();
            accessConnection.Open();

            //Initialize Readers
            sqlReader = sqlCommand.ExecuteReader();
            accessReader = accessCommand.ExecuteReader();


            //Check values;
            while (sqlReader.Read())    //loop through all rows
            {
                if (accessReader.Read())    //returns true if sql rows = access rows
                {
                    for (int i = 0; i < columnCount; i++)   //if true, the loop through each column
                    {
                        if (sqlReader[i] == accessReader[i])    //if current row in both databases are equal
                        {
                            prelimCheck = true;
                        }
                        else
                        {
                            prelimCheck = false;
                        }
                    }
                }
                else
                {
                    prelimCheck = false;
                }
            }

            if (accessReader.Read())    //if sql has no more rows and access has more
            {
                prelimCheck = false;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        sqlConnection.Close();
        accessConnection.Close();
        #endregion
    }

    //Extra Feature: One Way Encryption
    public static string encryptOneWay(string thing)
    {
        string that = null;


        return that;
    }
}