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

namespace MySQL
{
    class Program
    {
        static MySqlConnection conn;
        static MySqlCommand cmd;

        static void Main(string[] args)
        {
            string dbHost = "127.0.0.1";
            string dbUser = "sabpprook";
            string dbPass = "s0931222509";
            string dbName = "TOTP";
            string connStr = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass + ";database=" + dbName;
            conn = new MySqlConnection(connStr);
            cmd = conn.CreateCommand();
            try
            {
                Console.WriteLine("正在連結 MySQL 資料庫\n\nHost:{0}\nUser:{1}\nPassword:{2}\nDatabase:{3}\n", dbHost, dbUser, dbPass, dbName);
                conn.Open();
                Console.WriteLine("成功連結!\n");
                CreateTable();
                Console.WriteLine("新增 OTP 題庫\n");
                Insert();
                Show();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadKey();
        }

        static void CreateTable()
        {
            try
            {
                cmd.CommandText = "SELECT `index` FROM `question`";
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                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");
            }
        }

        static void Insert()
        {
            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)
                {

                }
            }
            Console.Write("新增問題(Y/N):");
            string flag = Console.ReadLine().ToLower();
            while (flag == "y")
            {
                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();
            }
        }

        static void Show()
        {
            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)
                {

                }
            }
        }
    }
}