using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;

namespace MySQL
{
    class Program
    {
        static MySqlConnection conn;
        static MySqlCommand cmd;
        static string dbHost = "127.0.0.1";
        static string dbUser = "";
        static string dbPass = "";

        static void Main(string[] args)
        {
            try
            {
                Console.WriteLine("正在連結 MySQL 資料庫\n\nHost:{0}\nUser:{1}\nPassword:{2}\nDatabase:TOTP\n", dbHost, dbUser, dbPass);
                if (OpenDatabase())
                {
                    Console.WriteLine("連結成功!\n");
                    while (true)
                    {
                        Console.WriteLine("1. 建立 question, user 資料表");
                        Console.WriteLine("2. 新增使用者");
                        Console.WriteLine("3. 顯示使用者");
                        Console.WriteLine("4. 新增 OTP 題庫");
                        Console.WriteLine("5. 顯示 OTP 題庫");
                        Console.WriteLine("6. 刪除使用者");
                        Console.Write("input:");
                        switch (Console.ReadLine())
                        {
                            case "1":
                                CreateTable();
                                break;
                            case "2":
                                InsertUser();
                                break;
                            case "3":
                                ShowUser();
                                break;
                            case "4":
                                InsertQuestion();
                                break;
                            case "5":
                                ShowQuestion();
                                break;
                            case "6":
                                DeleteUser();
                                break;
                            default:
                                break;
                        }
                    }
                }
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadKey();
        }

        static bool OpenDatabase()
        {
            string connStr = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass + ";database=TOTP";
            try
            {
                conn = new MySqlConnection(connStr);
                cmd = conn.CreateCommand();
                conn.Open();
                return true;
            }
            catch (Exception ex)
            {
                if (ex.Message.Contains("Unknown database"))
                {
                    Console.WriteLine("建立 TOTP 資料庫");
                    string connStr2 = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass;
                    conn = new MySqlConnection(connStr2);
                    cmd = conn.CreateCommand();
                    conn.Open();
                    cmd.CommandText = "CREATE DATABASE TOTP";
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    conn = new MySqlConnection(connStr);
                    cmd = conn.CreateCommand();
                    conn.Open();
                    return true;
                }
                else if (ex.Message.Contains("Access denied"))
                {
                    Console.WriteLine("無法登入");
                }
            }
            return false;
        }

        static void CreateTable()
        {
            try
            {
                cmd.CommandText = "SELECT `index` FROM `question`";
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
                cmd.CommandText = "CREATE TABLE `totp`.`question` ( `index` INT(4) NOT NULL , `descript` VARCHAR(80) NULL , `answer` VARCHAR(32) NULL , PRIMARY KEY (`index`)) ENGINE = InnoDB;";
                cmd.ExecuteNonQuery();
                Console.WriteLine("新增資料表 question\n");
            }
            try
            {
                cmd.CommandText = "SELECT `account` FROM `user`";
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
                cmd.CommandText = "CREATE TABLE `totp`.`user` ( `account` VARCHAR(20) NOT NULL , `password` VARCHAR(32) NOT NULL , PRIMARY KEY (`account`)) ENGINE = InnoDB;";
                cmd.ExecuteNonQuery();
                Console.WriteLine("新增資料表 user\n");
            }
        }

        static void InsertUser()
        {
            bool flag = true;
            while (flag)
            {
                Console.Write("\n帳號:");
                string account = Console.ReadLine();
                Console.Write("密碼:");
                string password = MD5Hash(Console.ReadLine());
                cmd.CommandText = string.Format("INSERT INTO `user` VALUES('{0}','{1}')", account, password);
                try
                {
                    cmd.ExecuteNonQuery();
                    Console.Write("新增使用者成功\n");
                }
                catch (Exception)
                {
                    Console.Write("新增使用者失敗\n");
                }
                Console.Write("繼續新增使用者(Y/N):");
                flag = Console.ReadLine().ToLower() == "y";
            }
        }

        static string MD5Hash(string data)
        {
            using (MD5 md5 = MD5.Create())
            {
                return BitConverter.ToString(md5.ComputeHash(Encoding.ASCII.GetBytes(data))).Replace("-", "");
            }
        }

        static void ShowUser()
        {
            StreamWriter file = new StreamWriter(File.Create("User.txt"));
            cmd.CommandText = "SELECT * FROM `user`";
            using (var result = cmd.ExecuteReader())
            {
                try
                {
                    Console.WriteLine("\nAccount\tPassword");
                    while (result.Read())
                    {
                        string account = result.GetString(0);
                        string password = result.GetString(1);
                        Console.WriteLine("{0}\t{1}", account, password);
                        file.WriteLine(account + ", " + password);
                    }
                    file.Close();
                }
                catch (Exception)
                {

                }
            }
        }

        static void InsertQuestion()
        {
            int index = 1;
            cmd.CommandText = "SELECT MAX(`index`) FROM `question`";
            using (var result = cmd.ExecuteReader())
            {
                try
                {
                    result.Read();
                    index = result.GetInt32(0) + 1;
                }
                catch (Exception)
                {

                }
            }
            bool flag = true;
            while (flag)
            {
                Console.Write("請輸入問題:");
                string descript = Console.ReadLine();
                Console.Write("請輸入答案:");
                string answer = Console.ReadLine();
                cmd.CommandText = "INSERT INTO `question` VALUES(" + index++ + ",'" + descript + "','" + answer + "')";
                cmd.ExecuteNonQuery();
                Console.Write("繼續新增問題(Y/N):");
                flag = Console.ReadLine().ToLower() == "y";
            }
        }

        static void ShowQuestion()
        {
            StreamWriter file = new StreamWriter(File.Create("Question.txt"));
            cmd.CommandText = "SELECT * FROM `question`";
            using (var result = cmd.ExecuteReader())
            {
                try
                {
                    Console.WriteLine("\nIndex\tDescript\tAnswer");
                    while (result.Read())
                    {
                        int index = result.GetInt32(0);
                        string descript = result.GetString(1);
                        string answer = result.GetString(2);
                        Console.WriteLine("{0}\t{1}\t{2}", index, descript, answer);
                        file.WriteLine(index + ", " + descript + ", " + answer);
                    }
                    file.Close();
                }
                catch (Exception)
                {

                }
            }
        }

        static void DeleteUser()
        {
            Console.Write("請輸入使用者名稱:");
            string User = Console.ReadLine();
            cmd.CommandText = "DELETE FROM `user` WHERE `account`='" + User + "'";
            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                    Console.WriteLine("刪除使用者" + User + "成功\n");
                else
                    Console.WriteLine("無使用者" + User + "\n");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }
    }
}