using JtkMainServer.ApiModels;
using MySql.Data.MySqlClient;
using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data.Common;

namespace JtkMainServer.Managers {
    static class DbExtension {
        public static void AddWithValue(this DbParameterCollection collection, string parameterName, object value) {
            switch (collection) {
                case MySqlParameterCollection c:
                    c.AddWithValue(parameterName, value);
                    return;
                case SqliteParameterCollection c:
                    c.AddWithValue(parameterName, value);
                    return;
            }
        }
        public static string GetString(this DbDataReader reader, string column) =>
            reader.GetString(reader.GetOrdinal(column));

        public static double GetDouble(this DbDataReader reader, string column) =>
            reader.GetDouble(reader.GetOrdinal(column));

        public static DateTime GetDateTime(this DbDataReader reader, string column) =>
            reader.GetDateTime(reader.GetOrdinal(column));
            /*switch (reader) {
                case MySqlDataReader r:
                    return r.GetDateTime(column);
                case SqliteDataReader r:
                    return r.GetDateTime(r.GetOrdinal(column));
                default:
                    return default(DateTime);
            }*/
        
            
    }

    /// <summary>
    /// JTK PC방 메인 서버에 종속적으로 설계된 DB 관리용 정적 클래스입니다.
    /// </summary>
    public class DbManager {
        public enum DbType {
            MySql, Sqlite
        }

        public static DbType Type;

        public static string ConnectionString { get; set; }

        private static DbConnection GetConnection() {
            switch (Type) {
                case DbType.MySql:
                    return new MySqlConnection(ConnectionString);
                case DbType.Sqlite:
                    return new SqliteConnection(ConnectionString);
                default:
                    return null;
            }
        }

        private static DbCommand GetCommand(string sql, DbConnection connection) {
            switch (Type) {
                case DbType.MySql:
                    return new MySqlCommand(sql, connection as MySqlConnection);
                case DbType.Sqlite:
                    return new SqliteCommand(sql, connection as SqliteConnection);
                default:
                    return null;
            }
        }
        

        #region Model Object Pickers

        private static Place PickPlaceFromReader(DbDataReader reader, int offset = 0) => new Place {
            Id = reader.GetString(offset++),
            Password = reader.GetString(offset++),
            PcMap = reader.GetString(offset++),
            Name = reader.GetString(offset++),
            RegisteredTime = reader.GetDateTime(offset++),
            PaymentHour = reader.GetInt32(offset++),
            PaymentMinute = reader.GetInt32(offset++),
            Location = reader.GetString(offset++),
            Address = reader.GetString(offset++),
            Introduction = reader.GetString(offset++),
            AccountNumber = reader.GetString(offset++),
            Royalty = reader.GetDouble(offset++),
            OrderMenu = reader.GetString(offset++)
        };

        private static User PickUserFromReader(DbDataReader reader, int offset = 0) => new User {
            Id = reader.GetString(offset++),
            Password = reader.GetString(offset++),
            Cash = reader.GetDouble(offset++)
        };

        #endregion


        #region Basic Getters

        /// <summary>
        /// Place ID로부터 해당하는 Place를 DB에서 가져옵니다.
        /// </summary>
        /// <param name="placeId">Place ID</param>
        public static Place GetPlace(string placeId) {
            try {
                using (var conn = GetConnection()) {
                    conn.Open();
                    var cmd = GetCommand(@"select * from places where id=@place_id", conn);
                    cmd.Parameters.AddWithValue("@place_id", placeId);
                    using (var reader = cmd.ExecuteReader()) {
                        if (reader.Read())
                            return PickPlaceFromReader(reader);
                        else
                            return null;
                    }
                }
            } catch {
                return null;
            }
        }

        /// <summary>
        /// 모든 Place를 DB에서 가져옵니다.
        /// </summary>
        public static Place[] GetAllPlaces() {
            try {
                var list = new List<Place>();
                using (var conn = GetConnection()) {
                    conn.Open();
                    var cmd = GetCommand(@"select * from places order by name", conn);
                    using (var reader = cmd.ExecuteReader()) {
                        while (reader.Read()) {
                            list.Add(PickPlaceFromReader(reader));
                        }
                    }
                    return list.ToArray();
                }
            } catch (Exception e) {
                Console.WriteLine(e.ToString());
                return null;
            }
        }

        /// <summary>
        /// User ID로부터 해당하는 User를 DB에서 가져옵니다.
        /// </summary>
        /// <param name="userId">User ID</param>
        public static User GetUser(string userId) {
            try {
                using (var conn = GetConnection()) {
                    conn.Open();
                    var cmd = GetCommand(@"select * from users where id=@user_id", conn);
                    cmd.Parameters.AddWithValue("@user_id", userId);
                    using (var reader = cmd.ExecuteReader()) {
                        if (reader.Read())
                            return PickUserFromReader(reader);
                        else
                            return null;
                    }
                }
            } catch {
                return null;
            }
        }

        #endregion


        #region Log

        private static DateTime GetLastTimeOfDay(DateTime date) 
            => DateTime.Parse($"{date.Year:0000}-{date.Month:00}-{date.Day:00} 23:59:59");

        public static bool LogUserPing(DateTime loginTime, string userId, string placeId, double totalCashUse, TimeSpan usedTime, bool end=false) {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"update user_logs set value2=@ttu, value3=@ut, type=@newtype where time=@time and type=@type and user_id=@user_id and value1=@place_id", conn);
                cmd.Parameters.AddWithValue("@ttu", totalCashUse.ToString("G"));
                cmd.Parameters.AddWithValue("@ut", usedTime.TotalSeconds.ToString("G"));
                cmd.Parameters.AddWithValue("@newtype", end ? "user_ping" : "user_ping*");
                cmd.Parameters.AddWithValue("@time", loginTime);
                cmd.Parameters.AddWithValue("@type", "user_ping*");
                cmd.Parameters.AddWithValue("@user_id", userId);
                cmd.Parameters.AddWithValue("@place_id", placeId);
                if (cmd.ExecuteNonQuery() > 0) {
                    return true;
                } else {
                    cmd.CommandText = @"insert into user_logs values(@time, @newtype, @user_id, @place_id, @ttu, @ut)";
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }

        public static bool LogUserOrder(string userId, string placeId, double price) {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"insert into user_logs values(@time, @type, @user_id, @place_id, @price, null)", conn);
                cmd.Parameters.AddWithValue("@time", DateTime.Now);
                cmd.Parameters.AddWithValue("@type", "user_order");
                cmd.Parameters.AddWithValue("@user_id", userId);
                cmd.Parameters.AddWithValue("@place_id", placeId);
                cmd.Parameters.AddWithValue("@price", price.ToString("G"));
                
                return cmd.ExecuteNonQuery() > 0;
                //) {
                //    return true;
                //} else {
                //    cmd.CommandText = @"insert into user_logs values(@time, @newtype, @user_id, @place_id, @ttu, null)";
                //    return cmd.ExecuteNonQuery() > 0;
                //}
            }
        }

        private static readonly Dictionary<string, string> placeNames = new Dictionary<string, string>();
        private static string GetPlaceName(string placeId) {
            lock (placeNames) {
                if (placeNames.TryGetValue(placeId, out var placeName) == false) {
                    placeNames[placeId] = placeName = GetPlace(placeId)?.Name ?? string.Empty;
                }
                return placeName;
            }
        }
        
        public static (DateTime loginTime, string placeName, string placeId, double totalCashUse, TimeSpan usedTime, bool isEnd)[] GetUserPingLogs(string userId) {
            var result = new List<(DateTime loginTime, string placeName, string placeId, double totalCashUse, TimeSpan usedTime, bool isEnd)>();
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select * from user_logs where user_id=@user_id and (type=@type_end or type=@type) order by time desc", conn);
                cmd.Parameters.AddWithValue("@user_id", userId);
                cmd.Parameters.AddWithValue("@type", "user_ping*");
                cmd.Parameters.AddWithValue("@type_end", "user_ping");
                using (var reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        var placeId = reader.GetString("value1");
                        result.Add((reader.GetDateTime("time"), GetPlaceName(placeId), placeId, reader.GetDouble("value2"), TimeSpan.FromSeconds(reader.GetDouble("value3")), reader.GetString("type") == "user_ping"));
                    }
                    return result.ToArray();
                }
            }
        }

        public static (DateTime loginTime, DateTime logoutTime, double totalCashUse, string userId)[] GetPlacePingLogs(string placeId, DateTime from = default(DateTime), DateTime to = default(DateTime), bool searchLogoutTime = false) {
            var result = new List<(DateTime loginTime, DateTime logoutTime, double totalCashUse, string userId)>();
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select * from user_logs where value1=@place_id and type=@type order by time desc", conn);
                if (from != default(DateTime) && to != default(DateTime) && searchLogoutTime == false) {
                    to = GetLastTimeOfDay(to);
                    cmd.CommandText = @"select * from user_logs where value1=@place_id and type=@type and time between @from and @to order by time desc";
                    cmd.Parameters.AddWithValue("@from", from);
                    cmd.Parameters.AddWithValue("@to", to);
                }
                cmd.Parameters.AddWithValue("@place_id", placeId);
                cmd.Parameters.AddWithValue("@type", "user_ping");
                using (var reader = cmd.ExecuteReader()) {
                    if (from != default(DateTime) && to != default(DateTime) && searchLogoutTime) {
                        while (reader.Read()) {
                            var time1 = reader.GetDateTime("time");
                            var time2 = time1 + TimeSpan.FromSeconds(reader.GetDouble("value3"));
                            if (from <= time2 && time2 <= to) { 
                                result.Add((time1, time2, reader.GetDouble("value2"), reader.GetString("user_id")));
                            }
                        }
                    } else {
                        while (reader.Read()) {
                            result.Add((reader.GetDateTime("time"), reader.GetDateTime("time") + TimeSpan.FromSeconds(reader.GetDouble("value3")), reader.GetDouble("value2"), reader.GetString("user_id")));
                        }
                    }
                    return result.ToArray();
                }
            }
        }

        public static bool LogUserCharge(DateTime chargedTime, string userId, double chargedCash, double totalCash) {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"insert into user_logs values(@time, @type, @user_id, @charged_cash, @total_cash, null)", conn);
                cmd.Parameters.AddWithValue("@time", chargedTime);
                cmd.Parameters.AddWithValue("@type", "user_charge");
                cmd.Parameters.AddWithValue("@user_id", userId);
                cmd.Parameters.AddWithValue("@charged_cash", chargedCash.ToString("0"));
                cmd.Parameters.AddWithValue("@total_cash", totalCash.ToString("0"));
                return cmd.ExecuteNonQuery() > 0;
            }
        }

        public static (DateTime chargedTime, double chargedCash, double totalCash)[] GetUserChargeLogs(string userId) {
            var result = new List<(DateTime chargedTime, double chargedCash, double totalCash)>();
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select * from user_logs where user_id=@user_id and type=@type order by time desc", conn);
                cmd.Parameters.AddWithValue("@user_id", userId);
                cmd.Parameters.AddWithValue("@type", "user_charge");
                using (var reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        result.Add((reader.GetDateTime("time"), reader.GetDouble("value1"), reader.GetDouble("value2")));
                    }
                    return result.ToArray();
                }
            }
        }

        public static int GetUsersCount() {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select COUNT(*) from users", conn);
                using (var reader = cmd.ExecuteReader()) {
                    reader.Read();
                    return reader.GetInt32(0);
                }
            }
        }

        public static int GetUsersCount(DateTime from, DateTime to) {
            to = GetLastTimeOfDay(to);
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select COUNT(*) from users where registered_time between @from and @to", conn);
                cmd.Parameters.AddWithValue("@from", from);
                cmd.Parameters.AddWithValue("@to", to);
                using (var reader = cmd.ExecuteReader()) {
                    reader.Read();
                    return reader.GetInt32(0);
                }
            }
        }

        public static int GetPlacesCount() {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select COUNT(*) from places", conn);
                using (var reader = cmd.ExecuteReader()) {
                    reader.Read();
                    return reader.GetInt32(0);
                }
            }
        }

        public static int GetPlacesCount(DateTime from, DateTime to) {
            to = GetLastTimeOfDay(to);
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select COUNT(*) from places where registered_time between @from and @to", conn);
                cmd.Parameters.AddWithValue("@from", from);
                cmd.Parameters.AddWithValue("@to", to);
                using (var reader = cmd.ExecuteReader()) {
                    reader.Read();
                    return reader.GetInt32(0);
                }
            }
        }

        public static bool LogSaleUserCharge(DateTime time, string userId, double chargedCash) {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"insert into sales values(@time, @type, @user_id, null, @charged_cash)", conn);
                cmd.Parameters.AddWithValue("@time", time);
                cmd.Parameters.AddWithValue("@type", "user_charge");
                cmd.Parameters.AddWithValue("@user_id", userId);
                cmd.Parameters.AddWithValue("@charged_cash", chargedCash);
                return cmd.ExecuteNonQuery() > 0;
            }
        }
        /*
        public static bool LogSaleUserPing(DateTime time, string userId, string placeId, double usedCash) {
            using (MySqlConnection conn = GetConnection()) {
                conn.Open();
                var cmd = new MySqlCommand(@"insert into sales values(@time, @type, @user_id, @place_id, @used_cash)", conn);
                cmd.Parameters.AddWithValue("@time", time);
                cmd.Parameters.AddWithValue("@type", "user_ping");
                cmd.Parameters.AddWithValue("@user_id", userId);
                cmd.Parameters.AddWithValue("@place_id", placeId);
                cmd.Parameters.AddWithValue("@used_cash", -usedCash);
                return cmd.ExecuteNonQuery() > 0;
            }
        }
        */
        public static bool LogSalePlacePaymentExpense(DateTime time, string placeId, double payment) {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"insert into sales values(@time, @type, null, @place_id, @cash)", conn);
                cmd.Parameters.AddWithValue("@time", time);
                cmd.Parameters.AddWithValue("@type", "place_payment");
                cmd.Parameters.AddWithValue("@place_id", placeId);
                cmd.Parameters.AddWithValue("@cash", payment);
                return cmd.ExecuteNonQuery() > 0;
            }
        }

        public static double GetSalesUserChargeIncome() {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select SUM(cash) from sales where type=@type", conn);
                cmd.Parameters.AddWithValue("@type", "user_charge");
                using (var reader = cmd.ExecuteReader()) {
                    reader.Read();
                    return reader.IsDBNull(0) ? 0 : reader.GetDouble(0);
                }
            }
        }

        public static double GetSalesUserChargeIncome(DateTime from, DateTime to) {
            to = GetLastTimeOfDay(to);
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select SUM(cash) from sales where type=@type and time between @from and @to", conn);
                cmd.Parameters.AddWithValue("@type", "user_charge");
                cmd.Parameters.AddWithValue("@from", from);
                cmd.Parameters.AddWithValue("@to", to);
                using (var reader = cmd.ExecuteReader()) {
                    reader.Read();
                    return reader.IsDBNull(0) ? 0 : reader.GetDouble(0);
                }
            }
        }

        public static (DateTime time, string userId, double cash)[] GetSalesUserChargeIncomesList(DateTime from, DateTime to) {
            var result = new List<(DateTime time, string userId, double cash)>();
            to = GetLastTimeOfDay(to);
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select * from sales where type=@type and time between @from and @to order by time desc", conn);
                cmd.Parameters.AddWithValue("@type", "user_charge");
                cmd.Parameters.AddWithValue("@from", from);
                cmd.Parameters.AddWithValue("@to", to);
                using (var reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        result.Add((reader.GetDateTime("time"), reader.GetString("user_id"), reader.GetDouble("cash")));
                    }
                    return result.ToArray();
                }
            }
        }
        
        public static double GetSalesPlacePaymentExpense() {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select SUM(cash) from sales where type=@type", conn);
                cmd.Parameters.AddWithValue("@type", "place_payment");
                using (var reader = cmd.ExecuteReader()) {
                    reader.Read();
                    return reader.IsDBNull(0) ? 0 : reader.GetDouble(0);
                }
            }
        }

        public static double GetSalesPlacePaymentExpense(DateTime from, DateTime to) {
            to = GetLastTimeOfDay(to);
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select SUM(cash) from sales where type=@type and time between @from and @to", conn);
                cmd.Parameters.AddWithValue("@type", "place_payment");
                cmd.Parameters.AddWithValue("@from", from);
                cmd.Parameters.AddWithValue("@to", to);
                using (var reader = cmd.ExecuteReader()) {
                    reader.Read();
                    return reader.IsDBNull(0) ? 0 : reader.GetDouble(0);
                }
            }
        }

        public static (DateTime time, DateTime prevTime, string placeName, string placeId, double cash)[] GetSalesPlacePaymentExpensesList(DateTime from, DateTime to, string placeId = null, bool exceptZero = false) {
            var result = new List<(DateTime time, DateTime prevTime, string placeName, string placeId, double cash)>();
            to = GetLastTimeOfDay(to);
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand($"select * from sales where type=@type and time<=@to {(string.IsNullOrWhiteSpace(placeId) ? "" : "and place_id=@place_id")} order by time", conn);
                cmd.Parameters.AddWithValue("@type", "place_payment");
                cmd.Parameters.AddWithValue("@to", to);
                if (string.IsNullOrWhiteSpace(placeId) == false) {
                    cmd.Parameters.AddWithValue("@place_id", placeId);
                }
                using (var reader = cmd.ExecuteReader()) {
                    if (string.IsNullOrWhiteSpace(placeId) == false) {
                        while (reader.Read()) {
                            if (reader.GetDouble("cash") > 0 || exceptZero == false) {
                                result.Add((reader.GetDateTime("time"), result.Count > 0 ? result.Last().time : default(DateTime), GetPlaceName(reader.GetString("place_id")), reader.GetString("place_id"), reader.GetDouble("cash")));
                            }
                        }
                    } else {
                        while (reader.Read()) {
                            var lastTime = default(DateTime);
                            foreach (var item in result) {
                                if (item.placeId == reader.GetString("place_id")) {
                                    lastTime = item.time;
                                }
                            }
                            if (reader.GetDouble("cash") > 0 || exceptZero == false) {
                                result.Add((reader.GetDateTime("time"), lastTime, GetPlaceName(reader.GetString("place_id")), reader.GetString("place_id"), reader.GetDouble("cash")));
                            }
                        }
                    }
                    for (var i = 0; i < result.Count; i++) {
                        if (result[i].time < from) {
                            result.RemoveAt(i--);
                        }
                    }
                    result.Reverse();
                    return result.ToArray();
                }
            }
        }

        public static (string placeName, string placeId, double totalExpense, double expense)[] GetPlacesExpense(DateTime from, DateTime to) {
            to = GetLastTimeOfDay(to);
            var result = new Dictionary<string, (string name, double totalExpense, double expense)>();
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select * from user_logs where type=@type", conn);
                cmd.Parameters.AddWithValue("@type", "user_ping");
                using (var reader = cmd.ExecuteReader()) {
                    // 먼저 가맹점 목록 생성
                    var places = GetAllPlaces();
                    foreach (var place in places) {
                        result[place.Id] = (place.Name, 0.0, 0.0);
                    }

                    // 가맹점 목록 내용 채우기
                    while (reader.Read()) {
                        var placeId = reader.GetString("value1");
                        var placeName = GetPlaceName(placeId);
                        var cash = reader.GetDouble("value2");
                        var time = reader.GetDateTime("time");
                        if (result.TryGetValue(placeId, out var tuple)) {
                            tuple.totalExpense += cash;
                            if (from <= time && time <= to) {
                                tuple.expense += cash;
                            }
                            result[placeId] = tuple;
                        } else {
                            // 없기 힘들지만 없는 경우
                            result[placeId] = (placeName, cash, cash);
                        }
                    }
                    
                    // 리스트화하고 반환
                    var list = new (string placeName, string placeId, double totalExpense, double expense)[result.Count];
                    var i = 0;
                    foreach (var pair in result) {
                        list[i++] = (pair.Value.name, pair.Key, pair.Value.totalExpense, pair.Value.expense);
                    }
                    return list.OrderBy(item => item.placeName).ToArray();
                }
            }
        }

        #endregion


        #region Review

        public static (DateTime time, string placeName, string placeId, string nickname, double score, string text)[] GetUserReviews(string userId) {
            var result = new List<(DateTime time, string placeName, string placeId, string nickname, double score, string text)>();
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select * from reviews where user_id=@user_id order by time desc", conn);
                cmd.Parameters.AddWithValue("@user_id", userId);
                using (var reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        var placeId = reader.GetString("place_id");
                        result.Add((reader.GetDateTime("time"), GetPlaceName(placeId), placeId, reader.GetString("nickname"), reader.GetDouble("score"), reader.GetString("text")));
                    }
                    return result.ToArray();
                }
            }
        }

        public static (DateTime time, string userId, string nickname, double score, string text)[] GetPlaceReviews(string placeId, int count = 0) {
            var result = new List<(DateTime time, string userId, string nickname, double score, string text)>();
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"select * from reviews where place_id=@place_id order by time desc" + (count > 0 ? $" limit {count}" : ""), conn);
                cmd.Parameters.AddWithValue("@place_id", placeId);
                using (var reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        result.Add((reader.GetDateTime("time"), reader.GetString("user_id"), reader.GetString("nickname"), reader.GetDouble("score"), reader.GetString("text")));
                    }
                    return result.ToArray();
                }
            }
        }

        public static bool WriteReview(string userId, string placeId, string nickname, double score, string text) {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"insert into reviews values(@time, @user_id, @place_id, @nickname, @score, @text)", conn);
                cmd.Parameters.AddWithValue("@time", DateTime.Now);
                cmd.Parameters.AddWithValue("@user_id", userId);
                cmd.Parameters.AddWithValue("@place_id", placeId);
                cmd.Parameters.AddWithValue("@nickname", nickname);
                cmd.Parameters.AddWithValue("@score", score);
                cmd.Parameters.AddWithValue("@text", text);

                return cmd.ExecuteNonQuery() > 0;
            }
        }

        #endregion


        #region Corresponding Getters
        #endregion


        #region Workers

        private static string HashSHA256(string plainText)
            => BitConverter.ToString(System.Security.Cryptography.SHA256.Create().ComputeHash(System.Text.Encoding.UTF8.GetBytes(plainText))).Replace("-", "");
        public static bool ComparePasswords(string plainPassword, string encodedPassword) =>
            HashSHA256(plainPassword) == encodedPassword;
        
        public static bool AddUser(string userId, string plainPassword) {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"insert into users(id, password, cash, registered_time) values(@id, @password, @cash, @reg_time)", conn);
                cmd.Parameters.AddWithValue("@id", userId);
                cmd.Parameters.AddWithValue("@password", HashSHA256(plainPassword));
                cmd.Parameters.AddWithValue("@cash", 0.0);
                cmd.Parameters.AddWithValue("@reg_time", DateTime.Now);
                //bool result = false;
                //try {
                //    result = cmd.ExecuteNonQuery() > 0;
                //} catch (MySqlException ex) {
                //    Console.WriteLine($"코드{ex.Code} 에러코드{ex.ErrorCode} 메시지{ex.Message}");
                //}

                //return result;
                try {
                    return cmd.ExecuteNonQuery() > 0;
                } catch { }
                return false;
            }
        }

        public static bool AddPlace(string placeId, string plainPassword, string placeName, string location, string address, string introduction, int payment_hour, int payment_minute, string accountNumber, double royalty) {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"insert into places(id, password, pc_map, name, registered_time, payment_hour, payment_minute, location, address, introduction, account_number, royalty, order_menu) values(@id, @password, @pc_map, @name, @registered_time, @payment_hour, @payment_minute, @location, @address, @introduction, @account_number, @royalty, @order_menu)", conn);
                cmd.Parameters.AddWithValue("@id", placeId);
                cmd.Parameters.AddWithValue("@password", HashSHA256(plainPassword));
                cmd.Parameters.AddWithValue("@pc_map", "{\"pc001\": {\"num\": 1, \"row\": 0, \"col\": 0 }}");
                cmd.Parameters.AddWithValue("@name", placeName);
                cmd.Parameters.AddWithValue("@registered_time", DateTime.Now);
                cmd.Parameters.AddWithValue("@payment_hour", payment_hour);
                cmd.Parameters.AddWithValue("@payment_minute", payment_minute);
                cmd.Parameters.AddWithValue("@location", location);
                cmd.Parameters.AddWithValue("@address", address);
                cmd.Parameters.AddWithValue("@introduction", introduction);
                cmd.Parameters.AddWithValue("@account_number", accountNumber);
                cmd.Parameters.AddWithValue("@royalty", royalty);
                cmd.Parameters.AddWithValue("@order_menu", "{}");
                try {
                    return cmd.ExecuteNonQuery() > 0;
                } catch { }
                return false;
            }
        }

        public static bool DeletePlace(string placeId) {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"delete from places where id=@id", conn);
                cmd.Parameters.AddWithValue("@id", placeId);
                try {
                    return cmd.ExecuteNonQuery() > 0;
                } catch { }
                return false;
            }
        }

        public static bool SetPlacePaymentTime(string placeId, int hour, int minute, string accountNumber, double royalty) {
            if (hour < 0 || hour >= 24 || minute < 0 || minute >= 60) {
                return false;
            }
            using (var conn = GetConnection()) {
                conn.Open();   
                var cmd = GetCommand(@"update places set payment_hour=@hour, payment_minute=@minute, account_number=@accnum, royalty=@royalty where id=@place_id", conn);
                cmd.Parameters.AddWithValue("@hour", hour);
                cmd.Parameters.AddWithValue("@minute", minute);
                cmd.Parameters.AddWithValue("@accnum", accountNumber);
                cmd.Parameters.AddWithValue("@royalty", royalty);
                cmd.Parameters.AddWithValue("@place_id", placeId);
                return cmd.ExecuteNonQuery() > 0;
            }
        }

        public static bool ChangePassword(string userId, string newPlainPassword) {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"update users set password=@password where id=@user_id", conn);
                cmd.Parameters.AddWithValue("@password", HashSHA256(newPlainPassword));
                cmd.Parameters.AddWithValue("@user_id", userId);
                return cmd.ExecuteNonQuery() > 0;
            }
        }

        public static bool SetPcMap(string placeId, string pcMap) {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"update places set pc_map=@pc_map where id=@place_id", conn);
                cmd.Parameters.AddWithValue("@pc_map", pcMap);
                cmd.Parameters.AddWithValue("@place_id", placeId);
                return cmd.ExecuteNonQuery() > 0;
            }
        }

        public static bool SetOrderMenu(string placeId, string orderMenuJson) {
            using (var conn = GetConnection()) {
                conn.Open();
                var cmd = GetCommand(@"update places set order_menu=@order_menu where id=@place_id", conn);
                cmd.Parameters.AddWithValue("@order_menu", orderMenuJson);
                cmd.Parameters.AddWithValue("@place_id", placeId);
                return cmd.ExecuteNonQuery() > 0;
            }
        }

        private static object CashLocker = new object();
        /// <summary>
        /// User의 Cash 값을 상대적으로 변경합니다.
        /// </summary>
        /// <param name="userId">대상 User ID</param>
        /// <param name="relativeValue">Cash 상대값</param>
        public static double? AdjustUserCash(string userId, double relativeValue) {
            lock (CashLocker) {
                try {
                    using (var conn = GetConnection()) {
                        conn.Open();
                        var cmd = GetCommand(@"select cash from users where id=@user_id", conn);
                        cmd.Parameters.AddWithValue("@user_id", userId);
                        double cash;
                        using (var reader = cmd.ExecuteReader()) {
                            if (reader.Read())
                                cash = reader.GetDouble(0);
                            else
                                return null;
                        }
                        cash += relativeValue;
                        cmd = GetCommand(@"update users set cash=@cash where id=@user_id", conn);
                        cmd.Parameters.AddWithValue("@cash", cash);
                        cmd.Parameters.AddWithValue("@user_id", userId);
                        cmd.ExecuteNonQuery();
                        return cash;
                    }
                } catch {
                    return null;
                }
            }
        }

        #endregion
    }
}
