import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;

import org.junit.Before;
import org.junit.*;
import java.sql.*;


/**
 * TestDBsetup class creates a suite of tests to check if the DatabaseSetup class makes the
 * connection to the server, creates and drops tables, but also if the Database can be queried.
 *
 * @author Dragos Preda and Stephen House
 *
 */
public class TestDBsetup {
  DatabaseSetup db;
  String username;
  String password;
  String dbUrl;
  String tableDesc;
  String tableDesc2;
  String tableDesc3;
  String print;
  Statement st;

  /**
   * Initalises the username, password and dbUrl fields with the appropriate heroku db login
   * details.
   */
  @Before
  public void setUp() {
    username = "pukdztdrlwizrd";
    password = "9fcf6d1c0c337ebe8d481aa56f8657b23b0020d403150c76e22ba58763d31fd3";
    dbUrl = "jdbc:postgresql://ec2-54-217-214-201.eu-west-1.compute.amazonaws.com:"
      + "5432/d8cvilvhht35us?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory";
  }

  /**
   * Tests if the getConnection method works. Done using JUnits assertNotNull method to
   * check if the connection returned from the getConnection method is null or not.
   * Not null meaning a connection was made.
   */
  @Test
  public void testConnection1() {
    DatabaseSetup db = new DatabaseSetup(username, password, dbUrl);
    assertNotNull(db.getConnection(username, password, dbUrl));
  }

  /**
   * Tests to see if the constructor that calls the getConnection method makes the connection with
   * the server.
   */
  @Test
  public void testConnection2() {
    DatabaseSetup db = new DatabaseSetup(username, password, dbUrl);
    assertNotNull(db.conn);
  }


  @Test
  /**
   * Tests to see if tables can be dropped and created. The tables are dropped and then created.
   * How: If something is wrong, usually an SQLException is thrown. Execute method will return false
   * if it's an update to the Database. Also in the console prints will show when tables are created
   * and dropped.
   *
   * @throws SQLException
   */
  public void testCreateDropTable() throws SQLException {
    DatabaseSetup db = new DatabaseSetup(username, password, dbUrl);
    st = null;
    st = db.conn.createStatement();
    tableDesc = "orders(ORDER_NUMBER int primary key, TABLE_NUMBER int, PRODUCT_ID varchar(10),"
        + " STAFF_ID varchar(10), STATUS varchar(10), TIME TIME, foreign key(PRODUCT_ID)"
        + " references menu, foreign key(STAFF_ID) references staff);";
    tableDesc2 = "menu(PRODUCT_ID varchar(10) primary key, PRODUCT_NAME varchar(30),"
        + "DESCRIPTION varchar(100), PRICE float);";
    tableDesc3 = "staff(STAFF_ID varchar(10) primary key, NAME varchar(30), ROLE varchar(10));";
    DatabaseMetaData dbm = db.conn.getMetaData();
    ResultSet tableOrders = dbm.getTables(null, null, "orders", null);
    ResultSet tableMenu = dbm.getTables(null, null, "menu", null);
    ResultSet tableStaff = dbm.getTables(null, null, "staff", null);
    System.out.println("------testCreateDropTable-------");
    if (tableOrders.next()) {
      System.out.println(">Table orders already in the database. =>Table Dropped");
      st.execute("DROP TABLE " + "orders");
    }
    if (tableMenu.next()) {
      System.out.println(">Table menu already in the database. =>Table Dropped");
      st.execute("DROP TABLE " + "menu");
    }
    if (tableStaff.next()) {
      System.out.println(">Table staff already in the database. =>Table Dropped");
      st.execute("DROP TABLE " + "staff");
    }
    if ((st.execute("CREATE TABLE " + tableDesc2) == false)
        && (st.execute("CREATE TABLE " + tableDesc3) == false)) {
      System.out.println(">Tables orders, menu, staff succsesfully created.");
    } else {
      System.out.println(">Tables failed to create.");
    }
    assertEquals(st.execute("CREATE TABLE " + tableDesc), false);
    st.close();
  }

  @Test
  /**
   * Tests to see if data can be inserted into tables and also tests if a SELECT query can be
   * executed on the Database. 1 row for each table is inserted, then the orders table is SELECT *
   * queried. The numbers in the single Orders table row represent orderNr,tableNr,productID,status
   * and time(7). How: If something is wrong, usually an SQLException is thrown. Asserts the single
   * row in table Orders with a similar String.
   *
   * @throws SQLException
   */
  public void testInsertAndQueryData() throws SQLException {
    System.out.println("------testInsertAndQueryData-------");
    DatabaseSetup db = new DatabaseSetup(username, password, dbUrl);
    db.tableSetup(db.conn);
    st = null;
    st = db.conn.createStatement();
    st.executeUpdate("INSERT INTO staff VALUES(" + "'" + Integer.parseInt("4024") + "','" + "John"
        + "','Waiter')");
    st.executeUpdate("INSERT INTO menu VALUES(" + "'" + Integer.parseInt("101")
        + "','Waffle','Dessert','" + Float.parseFloat("4") + "')");
    st.executeUpdate("INSERT INTO orders VALUES(" + "'" + Integer.parseInt("13") + "'" + "," + "'"
        + Integer.parseInt("4") + "'" + "," + "'" + Integer.parseInt("101") + "'" + "," + "'"
        + Integer.parseInt("4024") + "'" + "," + "'" + "not-ready" + "'" + "," + "'"
        + "2018-01-13 12:35" + "')");

    String query = "SELECT * FROM orders";
    ResultSet rs = null;
    rs = st.executeQuery(query);
    if (rs.next() == true) {
      print = rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " "
          + rs.getString(4) + " " + rs.getString(5) + " " + rs.getString(6);
      System.out.println(print);
    }

    assertEquals(print, "13 4 101 4024 not-ready 12:35:00");
    st.close();

  }

  @Test
  /**
   * Tests the checkTable method. How: If something is wrong, usually an SQLException is thrown; If
   * table is dropped it prints: "The 'tablename' has been dropped" or if it's in the database"The
   * table 'tablename' does not exist". Then the table is check if created with checkTable method:
   * false if not in the database, true if it is.
   *
   * @throws SQLException
   */
  public void testCheckTableDropTable() throws SQLException {
    DatabaseSetup db = new DatabaseSetup(username, password, dbUrl);
    db.tableSetup(db.conn);
    System.out.println("-----testCheckTabledropTable-----");
    db.dropTable(db.conn, "orders");
    assertEquals(db.checkTable(db.conn, "orders"), false);

  }

  @Test
  /**
   * Tests the createTable method. How: If something is wrong, usually an SQLException is thrown;
   * Checking if the table was created with checkTable method (tested above).
   */
  public void testCreateTable() {
    DatabaseSetup db = new DatabaseSetup(username, password, dbUrl);
    db.dropTable(db.conn, "orders");
    System.out.println("-----testCreateTable-----");
    String tableDesc =
        "orders(ORDER_NUMBER int primary key, TABLE_NUMBER int, PRODUCT_ID varchar(10),"
            + " STAFF_ID varchar(10), STATUS varchar(10), TIME TIME,"
            + " foreign key(PRODUCT_ID) references menu , foreign key(STAFF_ID)"
            + " references staff);";
    db.createTable(db.conn, tableDesc);
    assertEquals(db.checkTable(db.conn, "orders"), true);
  }

  @Test
  /**
   * Tests the executeQuery method, to see if queries can be made and tableSetup to create all the
   * tables at once. How:If something is wrong, usually an SQLException is thrown. Creating tables
   * with tableSetup then, Inserting data into the tables, then using the result of a SELECT query
   * to assertEquals with the string it should produce.
   *
   * @throws SQLException Thrown so that the SQL methods can be used.
   */
  public void testExecuteQuery() throws SQLException {
    DatabaseSetup db = new DatabaseSetup(username, password, dbUrl);
    db.tableSetup(db.conn);
    Statement st = db.conn.createStatement();
    st.executeUpdate("INSERT INTO staff VALUES(" + "'" + Integer.parseInt("4024") + "','" + "John"
        + "','Waiter')");
    st.executeUpdate("INSERT INTO menu VALUES(" + "'" + Integer.parseInt("101")
        + "','Waffle','Dessert','" + Float.parseFloat("4") + "')");
    st.executeUpdate("INSERT INTO orders VALUES(" + "'" + Integer.parseInt("13") + "'" + "," + "'"
        + Integer.parseInt("4") + "'" + "," + "'" + Integer.parseInt("101") + "'" + "," + "'"
        + Integer.parseInt("4024") + "'" + "," + "'" + "not-ready" + "'" + "," + "'"
        + "2018-01-13 12:35" + "')");
    String query = "SELECT STATUS FROM orders WHERE STATUS = 'not-ready'";
    ResultSet rs = db.executeQuery(db.conn, query);
    String result = null;
    if (rs.next()) {
      result = rs.getString(1);
    }
    assertEquals(result, "not-ready");
  }


  /**
   * Tests the ReadBasicFileMenu method to see if the file is read.
   * Done by using the readBasicFileMenu method  and then querying the database
   * to see if the file was read and then comparing the result of the query against
   *  data that should be in the database using assertEquals. Tested using the testMenu.txt file.
   * @throws SQLException Thrown so that the JDBC SQL result set methods can be used.
   */
  @Test
  public void testReadBasicFileMenu() throws SQLException {
    System.out.println("------testReadBasicMenu------");
    DatabaseSetup db = new DatabaseSetup(username, password, dbUrl);
    db.tableSetup(db.conn);
    String filePath = "testMenu.txt";
    db.readBasicMenuFile(db.conn, filePath);
    String query = "SELECT PRODUCT_ID FROM menu WHERE PRODUCT_ID = '1111'";
    ResultSet rs = db.executeQuery(db.conn, query);
    String result = null;
    if (rs.next()) {
      result = rs.getString(1);
    }
    assertEquals(result, "1111");
  }

}