using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Xml.Linq;
using MySql.Data.MySqlClient;
using Percent___Qualification_work.Classes;

namespace Percent___Qualification_work.Classes
{
    public class DatabaseConnection
    {
        // Singleton instance
        private static DatabaseConnection _instance;
        private static readonly object _lock = new object();

        // Database connection
        private MySqlConnection connection;
        private string connectionString;

        // Private constructor to prevent external instantiation
        private DatabaseConnection()
        {
            connectionString = "server=62.60.236.69;database=percent;user=root;password=UYfe32uUYFghKF23";
            connection = new MySqlConnection(connectionString);
        }

        // Public property to get the singleton instance
        public static DatabaseConnection Instance
        {
            get
            {
                lock (_lock)
                {
                    if (_instance == null)
                    {
                        _instance = new DatabaseConnection();
                    }
                    return _instance;
                }
            }
        }

        // Open the database connection
        public bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Connection error: " + ex.Message);
                return false;
            }
        }

        // Close the database connection
        public bool CloseConnection()
        {
            try
            {
                connection.Close(); //Closes the connection
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Closing connection error: " + ex.Message); // Catch and show errors
                return false;
            }
        }

        // Execute a query and return the result as a DataTable
        public DataTable ExecuteQuery(string query)
        {
            DataTable dataTable = new DataTable();
            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                    adapter.Fill(dataTable);
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }
            return dataTable;
        }

        // Add a new user to the database
        public bool AddUser(string username, string hashedPassword)
        {
            string query = "INSERT INTO users (username, password) VALUES (@username, @password)";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@username", username);
                    cmd.Parameters.AddWithValue("@password", hashedPassword); // Add parameters for the query
                    cmd.ExecuteNonQuery();                                    // execute the query
                    CloseConnection();
                    return true;
                }
                return false;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Database error: " + ex.Message);
                return false;
            }
        }

        // Add a new plan to the database
        public bool AddPlan(int userID, string name, string date, int priority)
        {
            string query = "INSERT INTO plans (user_id, name, date, priority) VALUES (@userID, @name, @date, @priority)";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@userID", userID);
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@date", date);
                    cmd.Parameters.AddWithValue("@priority", priority); // Add parameters for the query
                    cmd.ExecuteNonQuery();                              // Execute the query
                    CloseConnection();
                    return true;
                }
                return false;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Database error: " + ex.Message);
                return false;
            }
        }

        // Get the hashed password for a user
        public string GetPasswordForUser(string username)
        {
            string query = "SELECT password FROM users WHERE username = @username";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@username", username);
                    MySqlDataReader reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        string hashedPassword = reader["password"].ToString();
                        CloseConnection();
                        return hashedPassword;
                    }
                    CloseConnection();
                    return null;
                }
                return null;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
                return null;
            }
        }

        // Check if a username is already taken
        public bool IsUsernameTaken(string username)
        {
            string query = "SELECT COUNT(*) FROM users WHERE username = @username";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@username", username);
                    int userCount = Convert.ToInt32(cmd.ExecuteScalar());
                    CloseConnection();
                    return userCount > 0;
                }
                return false;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
                return false;
            }
        }

        // Get the user ID for a username
        public int GetUserID(string username)
        {
            string query = "SELECT id FROM users WHERE username = @username";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@username", username);
                    int id = Convert.ToInt32(cmd.ExecuteScalar());
                    CloseConnection();
                    return id;
                }
                return 0;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
                return 0;
            }
        }

        // Get plans for a user, ordered by the specified column
        public DataTable GetPlans(int userID, string order)
        {
            DataTable plans = new DataTable();
            string query = $"SELECT id, name, date, priority FROM plans WHERE user_id = @userID ORDER BY {order} DESC";

            if (order == "date") query = $"SELECT id, name, date, priority FROM plans WHERE user_id = @userID ORDER BY {order}";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@userID", userID);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                    adapter.Fill(plans);  // Fill the DataTable with the query result
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }

            return plans;
        }

        public DataTable GetPlansByDate(string date, int userID)
        {
            DataTable plans = new DataTable();
            string query = $"SELECT id, name, date, priority FROM plans WHERE user_id = @userID AND date = @date ORDER BY priority DESC";
            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@userID", userID);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                    adapter.Fill(plans);  // Fill the DataTable with the query result
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }

            return plans;
        }



        // Get a plan by its ID
        public DataTable GetPlanByID(int planID)
        {
            DataTable plan = new DataTable();
            string query = $"SELECT * FROM plans WHERE id = @planID";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@planID", planID);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                    adapter.Fill(plan);  // Fill the DataTable with the query result
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }

            return plan;
        }

        // Update a plan's details
        public void UpdatePlan(int planID, string name, string date, int priority)
        {
            string query = $"UPDATE plans SET name = @name, date = @date, priority = @priority WHERE id = @planID";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@planID", planID);
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@date", date);
                    cmd.Parameters.AddWithValue("@priority", priority);
                    cmd.ExecuteNonQuery();
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }
        }

        // Delete a plan from the database
        public void DeletePlan(int planID)
        {
            string query = $"DELETE FROM plans WHERE id = @planID";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@planID", planID);
                    cmd.ExecuteNonQuery();
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }
        }

        public bool NewNote(int userID, string name, string text)
        {
            string query = $"INSERT INTO notes (user_id,name,text) VALUES (@userID,@name,@text)";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@userID", userID);
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@text", text);
                    cmd.ExecuteNonQuery();
                    CloseConnection();
                    return true;
                }
                return false;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Database error: " + ex.Message);
                return false;
            }
        }

        public int NoteCount(int userID)
        {
            string query = $"SELECT MAX(id) FROM notes WHERE user_id = @userID";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@userID", userID);

                    // ExecuteScalar might return null if there are no rows
                    object result = cmd.ExecuteScalar();
                    CloseConnection();

                    if (result == null || result == DBNull.Value)
                        return 0;
                    else
                        return Convert.ToInt32(result);
                }
                return 0;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
                return 0;
            }
        }

        public DataTable GetNotes(int userID)
        {
            DataTable notes = new DataTable();
            string query = $"SELECT id,name FROM notes WHERE user_id = @userID";
            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@userID", userID);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                    adapter.Fill(notes);  // Fill the DataTable with the query result
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }

            return notes;
        }

        public void DeleteNote(int noteID)
        {
            string query = $"DELETE FROM notes WHERE id = @noteID";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@noteID", noteID);
                    cmd.ExecuteNonQuery();
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }
        }

        public DataTable GetNoteByID(int noteID)
        {
            DataTable note = new DataTable();
            string query = $"SELECT * FROM notes WHERE id = @noteID";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@noteID", noteID);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                    adapter.Fill(note);  // Fill the DataTable with the query result
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }

            return note;
        }

        public void UpdateNote(int noteID, string name, string text)
        {
            string query = $"UPDATE notes SET name = @name, text = @text WHERE id = @noteID";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@noteID", noteID);
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@text", text);
                    cmd.ExecuteNonQuery();
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }
        }

        public string[] GetNoteText(int noteID)
        {
            string query = "SELECT name, text FROM notes WHERE id = @noteID";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@noteID", noteID);

                    MySqlDataReader reader = cmd.ExecuteReader();
                    string[] result = new string[2];

                    if (reader.Read())
                    {
                        result[0] = reader["name"].ToString();
                        result[1] = reader["text"].ToString();
                    }

                    reader.Close();
                    CloseConnection();
                    return result;
                }
                return null;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
                return null;
            }
        }

        public Game GetGame(string name)
        {
            Game game = null;
            string query = $"SELECT * FROM games WHERE name = @name";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@name", name);
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            game = new Game(
                                reader.GetInt32("id"),
                                reader.GetString("name"),
                                reader.GetString("genre"),
                                reader.GetString("developer"),
                                reader.GetInt32("release_year"),
                                null
                            );
                        }
                    }
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }

            return game;
        }


        public bool IsGameInList(int userId, int? gameId)
        {
            string query = $"SELECT COUNT(*) FROM user_games WHERE user_id = @user_id AND game_id = @game_id";
            bool exists = false;

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@user_id", userId);
                    cmd.Parameters.AddWithValue("@game_id", gameId);

                    // ExecuteScalar returns the first column of the first row
                    object result = cmd.ExecuteScalar();
                    int count = Convert.ToInt32(result);
                    exists = count > 0;

                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }

            return exists;
        }
        public bool AddToUserList(int userID, int? gameID, int score)
        {
            string query = $"INSERT INTO user_games (user_id,game_id,user_score) VALUES (@userID,@gameID,@score)";
            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@userID", userID);
                    cmd.Parameters.AddWithValue("@gameID", gameID);
                    cmd.Parameters.AddWithValue("@score", score);
                    cmd.ExecuteNonQuery();
                    CloseConnection();
                    return true;
                }
                return false;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Database error: " + ex.Message);
                return false;
            }
        }

        public bool addGameToDB(Game game)
        {
            string query = $"INSERT INTO games (name,developer,genre,release_year,cover_image) VALUE (@name,@developer,@genre,@release_year,@cover_image)";
            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@name", game.Name);
                    cmd.Parameters.AddWithValue("@developer", game.Developer);
                    cmd.Parameters.AddWithValue("@genre", game.Genre);
                    cmd.Parameters.AddWithValue("@release_year", game.ReleaseYear);
                    cmd.Parameters.AddWithValue("@cover_image", game.CoverImage);
                    cmd.ExecuteNonQuery();
                    CloseConnection();
                    return true;
                }
                return false;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Database error: " + ex.Message);
                return false;
            }
        }

        public DataTable GetUserGames(int userID)
        {
            DataTable games = new DataTable();
            string query = @"
        SELECT 
            g.id AS GameID, 
            g.name AS GameName, 
            g.developer AS Developer, 
            g.genre AS Genre, 
            g.release_year AS ReleaseYear, 
            ug.user_score AS UserScore,
            g.cover_image AS CoverImage
        FROM 
            user_games ug
        INNER JOIN 
            games g
        ON 
            ug.game_id = g.id
        WHERE 
            ug.user_id = @userID";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@userID", userID);

                    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                    adapter.Fill(games);

                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }

            return games;
        }
    }
}
