package abot;

import java.io.*;
import java.sql.*;



public class MysqlConnect{
    static String url = "jdbc:mysql://localhost:3306/";
    static String dbName = "abot";
    static String driver = "com.mysql.jdbc.Driver";
    static String userName = "root";
    static String password = "root";
    static String questionsTable = "questions";
  public static void createDatabase() {
    System.out.println("[A-bot]Attempting DataBase Creation...");
    Connection con = null;
    try{
      Class.forName(driver).newInstance();
      con = DriverManager.getConnection(url+"constant",userName,password);
      try{
        Statement st = con.createStatement();
        st.executeUpdate("CREATE DATABASE "+dbName);
        System.out.println("[A-bot]DataBase '" + dbName + "' created.");

      }
      catch (SQLException s){
        System.out.println("[A-bot]DataBase creation aborted. Error:" + s);
      }
    }
    catch (Exception e){
      e.printStackTrace();
    }
  }
  public static void testConnection() {
    System.out.println("[A-Bot]Testing DataBase Connection...");
    Connection conn = null;
    try {
      Class.forName(driver).newInstance();
      conn = DriverManager.getConnection(url+dbName,userName,password);
      System.out.println("[A-Bot]DataBase Connection established.");
      conn.close();
      createQuestionsTable();
      createAnswersTable();
      System.out.println("[A-Bot]DataBase Connection test finished.");
    } catch (Exception e) {
      e.printStackTrace();
    }

  }
  public static void createQuestionsTable() {
    System.out.println("[A-bot]Attempting to Create Question MYSQL Table...");
    Connection con = null;
    try{
      Class.forName(driver).newInstance();
      con = DriverManager.getConnection(url+dbName, userName, password);
      try{
        Statement st = con.createStatement();
        String table = "CREATE TABLE questions(id integer, questions varchar(100))";
        st.executeUpdate(table);
        System.out.println("[A-bot]Question Table successfully created.");
      }
      catch(SQLException s){
        System.out.println("[A-bot]Question table creation aborted. Error:" + s);
      }
      con.close();
    }
    catch (Exception e){
      e.printStackTrace();
    }
    }
  public static void createAnswersTable() {
    System.out.println("[A-bot]Attempting to Create Answer MYSQL Table...");
    Connection con = null;
    try{
      Class.forName(driver).newInstance();
      con = DriverManager.getConnection(url+dbName, userName, password);
      try{
        Statement st = con.createStatement();
        String table = "CREATE TABLE answerss(id integer, answers varchar(100))";
        st.executeUpdate(table);
        System.out.println("[A-bot]Answer Table successfully created.");
      }
      catch(SQLException a){
        System.out.println("[A-bot]Answer table creation aborted. Error:" + a);
      }
      con.close();
    }
    catch (Exception e){
      e.printStackTrace();
    }
    }
     public static void addQuestion(String Question) {
    System.out.println("[A-bot]Attempting to add Question '" + Question + "' to MYSQL");
    Connection con = null;
    try{
      Class.forName(driver);
      con = DriverManager.getConnection(url+dbName, userName, password);
      try{

        Statement st = con.createStatement();
        int val = st.executeUpdate("INSERT questions VALUES("+getRowCountPlusOne(questionsTable)+","+"'"+ Question +"'"+")");
        System.out.println("[A-bot]Question '" + Question + "' added successfully to MYSQL.");
      }
      catch (SQLException s){
        System.out.println("[A-bot]Adding question aborted. Error:" + s);
      }
    }
    catch (Exception e){
      e.printStackTrace();
    }
  }
  public static int getRowCountPlusOne(String table) {
    System.out.println("[A-bot]Attempting to count rows in table '" + table + "'");
    Connection con = null;
    int count = 0;
    try{
      Class.forName(driver);
      con = DriverManager.getConnection(url+dbName, userName, password);
      try{
        Statement st = con.createStatement();
        ResultSet res = st.executeQuery("SELECT COUNT(*) FROM "+table);
        while (res.next()){
          count = res.getInt(1);
        }
        System.out.println("[A-bot]Rows in " + table + ":"+count);
      }
      catch (SQLException s){
        System.out.println("[A-bot]RowCount aborted. Error:" + s);
      }
    }
    catch (Exception e){
      e.printStackTrace();
    }
    return count+1;
  }
}