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

namespace FreeAir
{
    class FreeAirDatabase
    {
        FreeAirConfig _config;

        private string ConnectionString
        {
            get
            {
                return String.Format("server={0};user={1};port=3306;password={2};",
                    _config.DbUrl, _config.DbUsername, _config.DbPassword);
            }
        }

        public FreeAirDatabase(FreeAirConfig Config)
        {
            if (Config != null)
            {
                _config = Config;
            }
            else
            {
                throw new ArgumentNullException("Need an instance of a configuration to use the database.");
            }
        }

        /// <summary>
        /// Creates a blank database.
        /// </summary>
        /// <param name="overwrite">If you would like to overwrite an existing database by the name provided, set to true.</param>
        /// <returns>Success of the operation.</returns>
        public bool CreateNewDatabase(bool overwrite, bool populate)
        {
            MySqlConnection conn = new MySqlConnection(ConnectionString);
            string query = "";

            if (overwrite)
            {
                query = String.Format("DROP DATABASE IF EXISTS {0}; CREATE DATABASE IF NOT EXISTS {0}", _config.DbDatabase);
            }
            else if (!overwrite)
            {
                query = "CREATE DATABASE IF NOT EXISTS " + _config.DbDatabase;
            }

            try
            {
                conn.Open();
            }
            catch
            {
                // Could not open the connection.
                return false;
            }

            // Create database.
            bool success = false;
            if (overwrite)
            {
                success = ProcessMultiNonQuery(query, conn);
            }
            else if (!overwrite)
            {
                MySqlCommand cmd = new MySqlCommand(query, conn);
                int rCode = cmd.ExecuteNonQuery();

                if (rCode == -1)
                    success = true;
                else
                    success = false;
            }

            // Populate the database if requested.
            if (!success)
                return false;
            else if (success && populate)
                success = PopulateDatabase(conn, overwrite);

            conn.Close();
            conn.Dispose();

            return success;
        }

        /// <summary>
        /// Populates the database with new tables.
        /// </summary>
        /// <param name="overwrite">If true, it will overwrite existing tables.</param>
        /// <returns>Returns true if successful. Returns false if tables already exist or other failure condition occurs.</returns>
        public bool PopulateDatabase(bool overwrite)
        {
            MySqlConnection conn = new MySqlConnection(ConnectionString);

            try
            {
                conn.Open();
            }
            catch
            {
                return false;
            }

            bool success = PopulateDatabase(conn, overwrite);

            conn.Close();
            conn.Dispose();

            return success;
        }

        /// <summary>
        /// Populates the database with new tables.
        /// </summary>
        /// <param name="conn">Existing open connection to the server (not directed to any database).</param>
        /// <param name="overwrite">If set to true, it will overwrite any existing tables.</param>
        /// <returns></returns>
        private bool PopulateDatabase(MySqlConnection conn, bool overwrite)
        {
            string query = "";

            if (overwrite)
                query += "USE " + _config.DbDatabase + "; DROP TABLE IF EXISTS categories;" +
                         "DROP TABLE IF EXISTS media; DROP TABLE IF EXISTS playlist;";

            query +=  "CREATE TABLE categories (c_id INT NOT NULL AUTO_INCREMENT, " +
                      "c_name VARCHAR(10) NOT NULL, c_desc VARCHAR(100), PRIMARY KEY (c_id));" +
                      "CREATE TABLE media (m_id INT NOT NULL AUTO_INCREMENT, c_id INT NOT NULL, " +
                      "m_title VARCHAR(20), m_author VARCHAR(20), m_file VARCHAR(50), " +
                      "m_added DATETIME NOT NULL, m_expires DATETIME, PRIMARY KEY (m_id));" +
                      "CREATE TABLE playlist (p_id INT NOT NULL AUTO_INCREMENT, m_id INT NOT NULL, " +
                      "p_hour INT NOT NULL, p_start DATETIME NOT NULL, p_end DATETIME NOT NULL, " +
                      "p_order INT NOT NULL, p_crossfade INT, PRIMARY KEY (p_id))";

            return ProcessMultiNonQuery(query, conn);
        }

        /// <summary>
        /// Adds a category to the database.
        /// </summary>
        /// <param name="name">Name of the category. Up to 10 characters.</param>
        /// <param name="description">Description of the category. Up to 100 characters.</param>
        /// <returns>The ID of the category once added to the database. Returns -1 if the category could not be added.</returns>
        public int AddCategory(string name, string description)
        {
            if (name.Length > 10 || description.Length > 100)
            {
                return -1;
            }

            MySqlConnection conn = new MySqlConnection(ConnectionString + "database=" + _config.DbDatabase + ";");
            string query = String.Format("INSERT INTO `categories` (c_name, c_desc) VALUES ('{0}', '{1}');", name, description);
            MySqlCommand cmd = new MySqlCommand(query, conn);

            try
            {
                conn.Open();
            }
            catch
            {
                return -1;
            }

            int ind = GetLastInsertId(conn);
            cmd.Connection.Close();
            cmd.Dispose();
            conn.Dispose();
            return ind;
        }

        /// <summary>
        /// Removes a category from the database.
        /// </summary>
        /// <param name="categoryId">ID of the category to remove.</param>
        /// <param name="moveCategoryId">ID of the category to move media items to which might be in this category.</param>
        /// <returns>If the operation was successful.</returns>
        public bool RemoveCategory(int categoryId, int moveCategoryId)
        {
            MySqlConnection conn = new MySqlConnection(ConnectionString + "database=" + _config.DbDatabase + ";");
            string query = String.Format("DELETE FROM `categories` WHERE c_id='{0}'", categoryId);
            MySqlCommand cmd = new MySqlCommand(query, conn);
            bool success = false;

            try
            {
                conn.Open();
            }
            catch
            {
                return false;
            }

            if (cmd.ExecuteNonQuery() == 1)
            {
                cmd.CommandText = String.Format("UPDATE `media` SET c_id='{0}' WHERE c_id='{1}'", moveCategoryId, categoryId);
                if (cmd.ExecuteNonQuery() == 1)
                    success = true;
            }

            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            return success;
        }

        /// <summary>
        /// Gets an array of strings containing information about each category from the database.
        /// </summary>
        /// <returns></returns>
        public string[] GetAllCategories()
        {
            MySqlConnection conn = new MySqlConnection(ConnectionString + "database=" + _config.DbDatabase + ";");
            MySqlCommand cmd = new MySqlCommand("SELECT * FROM `categories`;", conn);
            MySqlDataReader reader = null;
            ArrayList rows = new ArrayList();

            try
            {
                conn.Open();
            }
            catch
            {
                return null;
            }

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                string row = String.Format("{0},{1},{2}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
                rows.Add(row);
            }

            reader.Close();
            reader.Dispose();
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            return (string[])rows.ToArray(typeof(string));
        }

        /// <summary>
        /// Make modifications to a category already stored in the database.
        /// </summary>
        /// <param name="categoryId">The ID of the category to modify.</param>
        /// <param name="categoryName">The new name of the category.</param>
        /// <param name="categoryDescription">The new description of the category.</param>
        /// <returns>If the query was successful or not.</returns>
        public bool ModifyCategory(int categoryId, string categoryName, string categoryDescription)
        {
            MySqlConnection conn = new MySqlConnection(ConnectionString + "database=" + _config.DbDatabase + ";");
            string query = String.Format("UPDATE `categories` SET c_name='{0}', c_desc='{1}' WHERE c_id='{2}';", categoryName, categoryDescription, categoryId);
            MySqlCommand cmd = new MySqlCommand(query, conn);
            bool success = false;

            try
            {
                conn.Open();
            }
            catch
            {
                return false;
            }

            if (cmd.ExecuteNonQuery() == 1)
            {
                success = true;
            }

            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            return success;
        }

        /// <summary>
        /// Adds a single media item to the database.
        /// </summary>
        /// <param name="categoryId">The category this media belongs to. Can't be null.</param>
        /// <param name="title">The title of the media..</param>
        /// <param name="author">Author of the media.</param>
        /// <param name="fileName">The name of the file.</param>
        /// <param name="expires"></param>
        /// <returns></returns>
        public bool AddMedia(int categoryId, string title, string author, string fileName, DateTime expires)
        {
            bool success = false;
            MySqlConnection conn = new MySqlConnection(ConnectionString + "database=" + _config.DbDatabase + ";");
            string query = String.Format("INSERT INTO `media` (c_id, m_title, m_author, m_file, m_added, m_expires) VALUES (" +
                                         "'{0}', '{1}', '{2}', '{3}', '{4}', '{5}');", categoryId, title, author, fileName, GetMySqlDateTime(DateTime.Now), GetMySqlDateTime(expires));
            MySqlCommand cmd = new MySqlCommand(query, conn);

            try
            {
                conn.Open();
            }
            catch
            {
                return false;
            }

            if (cmd.ExecuteNonQuery() == 1)
            {
                success = true;
            }

            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            return success;
        }

        /// <summary>
        /// Gets all media in a specified category from the database.
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public string[] GetMediaByCategory(int categoryId)
        {
            ArrayList media = new ArrayList();
            MySqlConnection conn = new MySqlConnection(ConnectionString + "database=" + _config.DbDatabase + ";");
            string query = "SELECT * FROM `media` WHERE c_id = '" + Convert.ToString(categoryId) + "';";
            MySqlCommand cmd = new MySqlCommand(query, conn);

            try
            {
                conn.Open();
            }
            catch
            {
                return null;
            }

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                string row = String.Format("{0},{1},{2},{3},{4},{5},{6}", reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2),
                                           reader.GetString(3), reader.GetString(4), GetMySqlDateTime(reader.GetDateTime(5)), GetMySqlDateTime(reader.GetDateTime(6)));
                media.Add(row);
            }

            reader.Close();
            reader.Dispose();
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            return (string[])media.ToArray(typeof(string));
        }

        /// <summary>
        /// Gets a DateTime string to enter into a MySQL database from a .NET DateTime object.
        /// </summary>
        /// <param name="dt">The datetime to reference.</param>
        /// <returns></returns>
        private string GetMySqlDateTime(DateTime dt)
        {
            if (dt != null)
            {
                return String.Format("{0}-{1}-{2} {3}:{4}:{5}", dt.Year,
                       Convert.ToString(dt.Month).PadLeft(2, '0'), Convert.ToString(dt.Day).PadLeft(2, '0'),
                       Convert.ToString(dt.Hour).PadLeft(2, '0'), Convert.ToString(dt.Minute).PadLeft(2, '0'),
                       Convert.ToString(dt.Second).PadLeft(2, '0'));
            }
            else
            {
                return "NULL";
            }
        }

        /// <summary>
        /// Gets the last AUTO_INCREMENT row ID created from the MySQL database.
        /// </summary>
        /// <param name="conn">A live connection to MySQL.</param>
        /// <returns>The ID of the last created row.</returns>
        private int GetLastInsertId(MySqlConnection conn)
        {
            MySqlCommand cmd = new MySqlCommand("SELECT LAST_INSERT_ID();", conn);
            MySqlDataReader reader = null;
            int index = -1;

            try
            {
                reader = cmd.ExecuteReader();
            }
            catch
            {
                return -1;
            }

            if (reader != null && reader.Read())
            {
                index = reader.GetInt32(0);
            }
            else
            {
                return -1;
            }

            reader.Close();
            return index;
        }

        /// <summary>
        /// Processes multi-non-query strings.
        /// </summary>
        /// <param name="queryString">Query string containing queries.</param>
        /// <param name="conn">The connection to the database.</param>
        /// <returns>A boolean value indicating total success or partial/full failure.</returns>
        /// <remarks>This function is necessitated by the one query per execution rule most MySQL databases
        /// adhere to religiously. What we do here to get around that is by looping through the non-queries
        /// one at a time while making it appear as one swift instruction on the surface.</remarks>
        private bool ProcessMultiNonQuery(string queryString, MySqlConnection conn)
        {
            ArrayList arrLst = new ArrayList();

            foreach (string query in queryString.Split(';'))
            {
                if (query != "")
                {
                    MySqlCommand cmd = new MySqlCommand(query.Trim(), conn);
                    int rCode = cmd.ExecuteNonQuery();
                    if (rCode == 1)
                        arrLst.Add(true);
                    else
                        arrLst.Add(false);
                }
            }

            bool[] returns = (bool[])arrLst.ToArray(typeof(bool));
            return returns.All(x => x);
        }
    }
}