using System;
using System.Data;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using DotNetEnv;
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 AddGameToUserList(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;
        }

        public void ChangeGameScore(int gameID,int score)
        {
            string query = $"UPDATE user_games SET user_score = @score WHERE game_id = @gameID";

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

        public void DeleteGame(int gameID)
        {
            string query = $"DELETE FROM user_games WHERE game_id = @gameID";
            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@gameID", gameID);
                    cmd.ExecuteNonQuery();
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }
        }
        public Movie GetMovie(string name)
        {
            Movie movie = null;
            string query = $"SELECT * FROM movies 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())
                        {
                            movie = new Movie(
                                reader.GetInt32("id"),
                                reader.GetString("name"),
                                reader.GetString("genre"),
                                reader.GetString("director"),
                                reader.GetInt32("release_year"),
                                (byte[])reader["cover_image"]
                            );
                        }
                    }
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }

            return movie;
        }
        public bool IsMovieInList(int userId, int? movieId)
        {
            string query = $"SELECT COUNT(*) FROM user_movies WHERE user_id = @user_id AND movie_id = @movie_id";
            bool exists = false;

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

                    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 AddMovieToUserList(int userID, int? movieID, int score)
        {
            string query = $"INSERT INTO user_movies (user_id, movie_id, user_score) VALUES (@userID, @movieID, @score)";
            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@userID", userID);
                    cmd.Parameters.AddWithValue("@movieID", movieID);
                    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 AddMovieToDB(Movie movie)
        {
            string query = $"INSERT INTO movies (name, director, genre, release_year, cover_image) VALUES (@name, @director, @genre, @release_year, @cover_image)";
            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@name", movie.Name);
                    cmd.Parameters.AddWithValue("@director", movie.Director);
                    cmd.Parameters.AddWithValue("@genre", movie.Genre);
                    cmd.Parameters.AddWithValue("@release_year", movie.ReleaseYear);
                    cmd.Parameters.AddWithValue("@cover_image", movie.CoverImage);
                    cmd.ExecuteNonQuery();
                    CloseConnection();
                    return true;
                }
                return false;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Database error: " + ex.Message);
                return false;
            }
        }

        public DataTable GetUserMovies(int userID)
        {
            DataTable movies = new DataTable();
            string query = @"
                            SELECT 
                                m.id AS MovieID, 
                                m.name AS MovieName, 
                                m.director AS Director,
                                m.genre AS Genre,
                                m.release_year AS ReleaseYear,
                                um.user_score AS UserScore,
                                m.cover_image AS CoverImage
                            FROM 
                                user_movies um
                            INNER JOIN 
                                movies m
                            ON 
                                um.movie_id = m.id
                            WHERE 
                                um.user_id = @userID";

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

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

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

        public void ChangeMovieScore(int movieID, int score)
        {
            string query = $"UPDATE user_movies SET user_score = @score WHERE movie_id = @movieID";

            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@movieID", movieID);
                    cmd.Parameters.AddWithValue("@score", score);
                    cmd.ExecuteNonQuery();
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }
        }
        public void DeleteMovie(int movieID)
        {
            string query = $"DELETE FROM user_movies WHERE movie_id = @movieID";
            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@movieID", movieID);
                    cmd.ExecuteNonQuery();
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }
        }

        public string GetUserPassword(int userId)
        {

            string query = $"SELECT password from users WHERE id = @userId";
            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@userId", userId);
                    object password = cmd.ExecuteScalar();
                    CloseConnection();
                    return password.ToString();
                }
                return "";
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
                return "";
            }
        }

        public void ChangePassword(int userId, string password)
        {
            string query = $"UPDATE users SET password = @password WHERE id = @userId";
            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@password", password);
                    cmd.Parameters.AddWithValue("@userId", userId);
                    cmd.ExecuteNonQuery();
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }
        }

        public void ChangeProfilePicture(int userId, byte[] profilePicture)
        {
            string query = $"UPDATE users SET profile_picture = @profilePicture WHERE id = @userId";
            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    cmd.Parameters.AddWithValue("@profilePicture", profilePicture);
                    cmd.Parameters.AddWithValue("@userId", userId);
                    cmd.ExecuteNonQuery();
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }
        }

        public byte[] GetProfilePicture(int userId)
        {
            string query = "SELECT profile_picture FROM users WHERE id = @userId";
            byte[] profilePicture = null;

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

                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            profilePicture = reader["profile_picture"] as byte[];
                        }
                    }
                    CloseConnection();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Query error: " + ex.Message);
            }

            return profilePicture;
        }

    }
}