using Microsoft.Data.Sqlite;
using System;

namespace T3k3rg0.Helpers
{
    public static class ReactionRoleStorage
    {
        private static readonly string DbPath = Environment.GetEnvironmentVariable("DB_ROLES_LOC");

        public static ulong? GetRoleId(ulong messageId, string emojiRaw)
        {
            using var conn = new SqliteConnection($"Data Source={DbPath}");
            conn.Open();

            using var cmd = conn.CreateCommand();
            cmd.CommandText = @"
				        SELECT role_id FROM reaction_roles
			    	    WHERE message_id = @msg AND emoji = @emoji";
            cmd.Parameters.AddWithValue("@msg", (long)messageId);
            cmd.Parameters.AddWithValue("@emoji", emojiRaw);

            using var reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                return (ulong)(long)reader["role_id"];
            }

            return null;
        }

        public static bool TryAddReactionRole(ulong messageId, string emojiRaw, ulong roleId)
        {
            using var conn = new SqliteConnection($"Data Source={DbPath}");
            conn.Open();

            using var cmd = conn.CreateCommand();
            cmd.CommandText = @"
        		    INSERT OR IGNORE INTO reaction_roles (message_id, emoji, role_id)
		            VALUES (@msg, @emoji, @role)";
            cmd.Parameters.AddWithValue("@msg", (long)messageId);
            cmd.Parameters.AddWithValue("@emoji", emojiRaw);
            cmd.Parameters.AddWithValue("@role", (long)roleId);

            int rowsAffected = cmd.ExecuteNonQuery();
            return rowsAffected > 0;
        }

        public static bool TryRemoveReactionRole(ulong messageId, string emojiRaw)
        {
            using var conn = new SqliteConnection($"Data Source={DbPath}");
            conn.Open();

            using var cmd = conn.CreateCommand();
            cmd.CommandText = @"
             		DELETE FROM reaction_roles
		            WHERE message_id = @msg AND emoji = @emoji";
            cmd.Parameters.AddWithValue("@msg", (long)messageId);
            cmd.Parameters.AddWithValue("@emoji", emojiRaw);

            int rowsAffected = cmd.ExecuteNonQuery();
            return rowsAffected > 0;
        }
    }
}