TheShopCritics2/TSC2/Components/CSharp/DatabaseManager.cs

using MySql.Data.MySqlClient;
using System;
using System.Data;
using Telerik.SvgIcons;
using TSC2.Components.Layout;
using static Telerik.Blazor.ThemeConstants;

namespace TSC2.Components.CSharp
{
    public class DatabaseManager
    {
        private static string _connectionString = MainLayout.isDev ? "Server=127.0.0.1;User ID=root;Password=root;Database=tsc2" : "PROD_CONNECTION_STRING";

        private static Random rng = new Random();

        /**********************************************/
        // SHOP INFORMATION
        /**********************************************/
        public static List<Tuple<double[], string, string>> LoadAllShops()
        {
            List<Tuple<double[], string, string>> results = new List<Tuple<double[], string, string>>();
            MySqlConnection conn = new MySqlConnection(_connectionString);
            conn.Open();

            using (var cmd = new MySqlCommand("SELECT * FROM shopinformation", conn))
            {
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while(reader.Read())
                    {
                        var uniqueID = reader.GetString(0);
                        var shopName = reader.GetString(1);
                        //var address = reader.GetString(2);
                        //var phone = reader.GetString(3);
                        //var website = reader.GetString(4);
                        //var hours = reader.GetString(5);
                        //var about = reader.GetString(6);
                        double latitude = reader.GetDouble(7);
                        double longitude = reader.GetDouble(8);

                        double[] coords = [latitude, longitude];
                        Tuple<double[], string, string> tuple = new Tuple<double[], string, string>(coords, shopName, uniqueID); 

                        results.Add(tuple);
                    }
                }
            }

            return results;
        }



        public static List<Tuple<string, string, string, float>> FilterByState(string state)
        {
            List<Tuple<string, string, string, float>> results = new();
            MySqlConnection conn = new MySqlConnection(_connectionString);
            conn.Open();

            using (var cmd = new MySqlCommand("SELECT * FROM shopinformation WHERE address LIKE CONCAT('%', @state, ',%');", conn))
            {
                cmd.Parameters.AddWithValue("@state", state);
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var uniqueID = reader.GetString(0);
                        var shopName = reader.GetString(1);
                        var address = reader.GetString(2);

                        //var phone = reader.GetString(3);
                        //var website = reader.GetString(4);
                        //var hours = reader.GetString(5);
                        //var about = reader.GetString(6);
                        //double latitude = reader.GetDouble(7);
                        //double longitude = reader.GetDouble(8);
                        var score = reader.GetInt32(9); // TODO: replace with average lookup. Will require a query or new field on each shop in the db
                        //var reviewCount = reader.GetInt32(10);
                        Tuple<string, string, string, float> tuple = new Tuple<string, string, string, float>(uniqueID, shopName, address, score);

                        results.Add(tuple);
                    }
                }
            }
            return results;
        }



        public static void UpdateShopScore(string shopID)
        {
            List<int> scores = GetShopReviewScores(shopID);
            int count = scores.Count;

            int shopScore = 0;
            foreach (int score in scores)
            {
                shopScore += score;
            }
            shopScore /= count;

            if (count > 0)
            {
                try
                {
                    MySqlConnection conn = new MySqlConnection(_connectionString);
                    conn.Open();

                    var cmd = new MySqlCommand();

                    cmd.CommandText = "UPDATE shopinformation SET ReviewScore = @shopScore, ReviewCount = @count WHERE UniqueID=@uniqueid;";
                    cmd.Connection = conn;
                    
                    cmd.Parameters.AddWithValue("@shopScore", shopScore);
                    cmd.Parameters.AddWithValue("@count", count);
                    cmd.Parameters.AddWithValue("@uniqueid", shopID);

                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }



        /**********************************************/
        // LOGIN FLOW
        /**********************************************/
        public async static Task SignInGoogle()
        {
            MySqlConnection conn = new MySqlConnection(_connectionString);
            conn.Open();

            using (var count_cmd = new MySqlCommand("SELECT COUNT(*) FROM (SELECT * FROM userinformation WHERE Platform='Google' AND Token=@Token) AS result", conn))
            {
                count_cmd.Parameters.AddWithValue("@Token", MainLayout.Session["id"]);
                int count = Convert.ToInt32(count_cmd.ExecuteScalar());

                if (count == 0) // User is not already in our database
                {
                    try
                    {
                        // Generate guid
                        Guid guid = Guid.NewGuid();

                        var insert_cmd = new MySqlCommand();

                        insert_cmd.CommandText = "INSERT INTO userinformation(UniqueID,FullName,Platform,Token) VALUES(@uniqueid,@fullname,@platform,@token)";
                        insert_cmd.Connection = conn;

                        insert_cmd.Parameters.AddWithValue("@uniqueid", guid.ToString()); 
                        insert_cmd.Parameters.AddWithValue("@fullname", MainLayout.Session["name"]);
                        insert_cmd.Parameters.AddWithValue("@platform", "Google");
                        insert_cmd.Parameters.AddWithValue("@token", MainLayout.Session["id"]);

                        insert_cmd.ExecuteNonQuery();
                        MainLayout.Session.Add("unique_id", guid.ToString());
                    }
                    catch(Exception ex)
                    {
                        await Console.Out.WriteLineAsync(ex.Message);
                    }
                }
            }
            conn.Close();
        }

        public async static Task SignInFacebook()
        {
            MySqlConnection conn = new MySqlConnection(_connectionString);
            conn.Open();

            using (var count_cmd = new MySqlCommand("SELECT COUNT(*) FROM (SELECT * FROM userinformation WHERE Platform='Facebook' AND Token=@Token) AS result", conn))
            {
				count_cmd.Parameters.AddWithValue("@Token", MainLayout.Session["id"]);
				int count = Convert.ToInt32(count_cmd.ExecuteScalar());

                if (count == 0) // User is not already in our database
                {
                    try
                    {
                        // Generate guid
                        Guid guid = Guid.NewGuid();

                        var insert_cmd = new MySqlCommand();

                        insert_cmd.CommandText = "INSERT INTO userinformation(UniqueID,FullName,Platform,Token) VALUES(@uniqueid,@fullname,@platform,@token)";
                        insert_cmd.Connection = conn;

                        insert_cmd.Parameters.AddWithValue("@uniqueid", guid.ToString());
                        insert_cmd.Parameters.AddWithValue("@fullname", MainLayout.Session["name"]);
                        insert_cmd.Parameters.AddWithValue("@platform", "Facebook");
                        insert_cmd.Parameters.AddWithValue("@token", MainLayout.Session["id"]);

                        insert_cmd.ExecuteNonQuery();
                        MainLayout.Session.Add("unique_id", guid.ToString());
                    }
                    catch (Exception ex)
                    {
                        await Console.Out.WriteLineAsync(ex.Message);
                    }
                }
            }
            conn.Close();
        }



		/**********************************************/
		// REVIEWS
		/**********************************************/
        public async static void AddReview(string shopID)
        {
            if (MainLayout.Session.Count == 0) // We only want to proceed if the user is signed in
                return;

			MySqlConnection conn = new MySqlConnection(_connectionString);
			conn.Open();

			// Set review ID to a combination of the user's unique id and the shop's
			var reviewID = MainLayout.Session["id"] + "~~" + shopID;

			using (var count_cmd = new MySqlCommand("SELECT COUNT(*) FROM (SELECT * FROM reviews WHERE ReviewID=@reviewID) AS result", conn))
            {
                count_cmd.Parameters.AddWithValue("@reviewID", reviewID);
				int count = Convert.ToInt32(count_cmd.ExecuteScalar());
                if (count == 0)
                {
					try
					{
						

						// Check if a review already exists by this ID

						var insert_cmd = new MySqlCommand();

						insert_cmd.CommandText = "INSERT INTO reviews(ReviewID,ReviewText,ReviewScore) VALUES(@reviewid,@reviewtext,@reviewscore)";
						insert_cmd.Connection = conn;

						insert_cmd.Parameters.AddWithValue("@reviewid", reviewID);
						insert_cmd.Parameters.AddWithValue("@reviewtext", "This is a review! Surely this will work on the Second Try!");
                        insert_cmd.Parameters.AddWithValue("@reviewscore", rng.Next(1, 6));  

						insert_cmd.ExecuteNonQuery();

                        UpdateShopScore(shopID);
					}
					catch (Exception ex)
					{
						await Console.Out.WriteLineAsync("An exception occured when adding review.");
					}
				}
                else
                {
                    await Console.Out.WriteLineAsync("Review already exists for this user on this shop.");
                }
			}			
		}



        public static List<string> GetShopReviews(string shopID)
        {
			MySqlConnection conn = new MySqlConnection(_connectionString);
			conn.Open();

			var cmd = new MySqlCommand("SELECT * FROM reviews WHERE ReviewID LIKE CONCAT('%~~', @shopID, '%');", conn);
			cmd.Parameters.AddWithValue("@shopID", shopID);
			MySqlDataReader reader = cmd.ExecuteReader();

            List<string> reviews = new();
            while (reader.Read())
            {
                var reviewText = reader.GetString(1);
                reviews.Add(reviewText);
            }
			return reviews;
        }



		public static List<int> GetShopReviewScores(string shopID)
		{
			MySqlConnection conn = new MySqlConnection(_connectionString);
			conn.Open();

			var cmd = new MySqlCommand("SELECT * FROM reviews WHERE ReviewID LIKE CONCAT('%~~', @shopID, '%');", conn);
            cmd.Parameters.AddWithValue("@shopID", shopID);
			MySqlDataReader reader = cmd.ExecuteReader();

            List<int> scores = new();
			while (reader.Read())
            {
                var reviewScore = Convert.ToInt32(reader.GetInt32(2));
                scores.Add(reviewScore);
			}
			return scores;
		}
	}
}