using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace DeadlockMemberOrder
{
    public class Program
    {
        public static string testdb = "server=localhost;port=3306;database=test;uid=mathem_user;password=mathem;logging=true;";

        static void Main(string[] args)
        {
            Console.WriteLine("---------------------------------------");
            Console.WriteLine("- Testing deadlocks                   -");
            Console.WriteLine("---------------------------------------");
            Console.WriteLine("");

            const int memberCount = 200;

            Console.WriteLine("Setup:");
            Console.WriteLine("======");
            Console.WriteLine("# test members: {0}", memberCount);
            Console.WriteLine("");

            var memberIds = SetUp(memberCount);

            Console.WriteLine("Executing test...");
            Console.WriteLine("");

            RunTest(memberIds);
        }

        static List<int> SetUp(int memberCount)
        {
            SetUpOrders();

            return SetUpMembers(memberCount);
        }

        static void SetUpOrders()
        {
            const string sql_truncate = @"TRUNCATE TABLE `order`;";

            using (var connection = new MySqlConnection(testdb))
            {
                connection.Open();
                var cmd = new MySqlCommand(sql_truncate, connection);

                cmd.ExecuteNonQuery();
            }
        }

        static List<int> SetUpMembers(int memberCount)
        {
            const string sql_truncate = @"DELETE FROM member;";

            using (var connection = new MySqlConnection(testdb))
            {
                connection.Open();
                var cmd = new MySqlCommand(sql_truncate, connection);

                cmd.ExecuteNonQuery();
            }

            var members = new List<int>();

            for (var i = 0; i < memberCount; ++i)
            {
                const string sql = @"INSERT INTO member (CreationDate) VALUES (NOW()); SELECT LAST_INSERT_ID();";

                using (var connection = new MySqlConnection(testdb))
                {
                    connection.Open();
                    var cmd = new MySqlCommand(sql, connection);

                    members.Add((int) (ulong) cmd.ExecuteScalar());
                }
            }

            return members;
        }

        static void RunTest(List<int> memberIds)
        {
            memberIds.ForEach(mid =>
                Parallel.Invoke(
                    () => SetLoggedIn(mid),
                    () => SetLoggedIn(mid),
                    () => SetLoggedIn(mid),
                    () => SetLoggedIn(mid), 
                    () => SetLoggedIn(mid),
                    () => SetLoggedIn(mid),
                    () => SetLoggedIn(mid),
                    () => SetLoggedIn(mid),
                    () => SetLoggedIn(mid),
                    () => SaveCart(mid),
                    () => Console.WriteLine(mid)
                    )
                );
        }

        static void SetLoggedIn(int memberId)
        {
            const string sql = @"UPDATE member SET LastLoginDate=NOW(), LoginCount=IFNULL(LoginCount,0)+1, LastLoginIP='127.0.0.1' WHERE MemberID=?p_MemberId and RemovedDate IS NULL";

            var para = new[]
                       {
                           new MySqlParameter("?p_MemberId", MySqlDbType.Int32) {Value = memberId}
                       };

            using (var connection = new MySqlConnection(testdb))
            {
                connection.Open();
                var cmd = new MySqlCommand(sql, connection);

                foreach (var p in para)
                {
                    cmd.Parameters.Add(p);
                }

                cmd.ExecuteNonQuery();
            }
        }

        static void SaveCart(int memberId)
        {
            using (var connection = new MySqlConnection(testdb))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    var orderId = InsertOrder(connection, transaction, memberId);

                    UpdateMemberLastUsedOrderId(connection, transaction, memberId, orderId);

                    transaction.Commit();
                }
            }
        }

        static int InsertOrder(IDbConnection connection, IDbTransaction transaction, int memberId)
        {
            const string sql = @"INSERT INTO `order` (MemberId) VALUES (?p_MemberId); SELECT LAST_INSERT_ID();";

            var para = new[]
                       {
                           new MySqlParameter("?p_MemberId", MySqlDbType.Int32) {Value = memberId}
                       };

            var cmd = new MySqlCommand(sql, (MySqlConnection)connection, (MySqlTransaction)transaction);

            foreach (var p in para)
            {
                cmd.Parameters.Add(p);
            }

            return (int)(ulong) cmd.ExecuteScalar();
        }

        static void UpdateMemberLastUsedOrderId(IDbConnection connection, IDbTransaction transaction, int memberId, int orderId)
        {
            const string sql = @"UPDATE member SET LastUsedOrderId = ?p_OrderId WHERE MemberId = ?p_MemberId AND RemovedDate IS NULL;";

            var para = new[]
                       {
                           new MySqlParameter("?p_MemberId", MySqlDbType.Int32) {Value = memberId},
                           new MySqlParameter("?p_OrderId", MySqlDbType.Int32) {Value = orderId}
                       };

            var cmd = new MySqlCommand(sql, (MySqlConnection)connection, (MySqlTransaction)transaction);

            foreach (var p in para)
            {
                cmd.Parameters.Add(p);
            }

            cmd.ExecuteNonQuery();
        }
    }
}