using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
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();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        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())
            {
                result.Read();
                index = result.GetInt32(0) + 1;
            }
            while (true)
            {
                Console.Write("請輸入問題:");
                string descript = Console.ReadLine();
                Console.Write("請輸入答案:");
                string answer = Console.ReadLine();
                cmd.CommandText = "INSERT INTO `question` VALUES(" + index + ",'" + descript + "','" + answer + "')";
                cmd.ExecuteNonQuery();
            }
        }
    }
}