import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;

public class DB_Statements {
//    Declare a Statement
    private static Statement stmt = null;

//    Declare & create a connection
    private static Connection con = DB_Connector.connect();

//    Declare a result set
    private static ResultSet rs = null;

//    Method to create a new Database
    public void createDB() {

//    SQL statement
        String query = "create database ThisDatabase";
        try {

//    create statement
            stmt = con.createStatement();

//    execute statement
            stmt.executeUpdate(query);
            System.out.println("\n---Database was created---");
        }

//    handle sql exceptions
        catch (SQLException ex) {
            System.out.println("\n---Statement did not execute---");
            ex.printStackTrace();
        }
    }

//    method to use a database
    public void useDB() {

//    SQL statement
    String query = "use ThisDatabase";
    try {

//    create statement
        stmt = con.createStatement();

//    execute statement
        stmt.executeUpdate(query);
        System.out.println("\n---Using ThisDatabase---");
    }

//    handle sql exceptions
        catch(SQLException ex) {
            System.out.println("\n---Statement did not execute---");
            ex.printStackTrace();
        }
    }

//    method to create a table
    public void createTable () {

//    SQL statement
        String query = "create table if not exists ThisTable(" +
            "id int NOT NULL AUTO_INCREMENT," +
            "name varchar(28)," +
            "address varchar(28), " +
            "PRIMARY KEY(id)" +
            ")";
        try {

//    create statement
            stmt = con.createStatement();

//    execute statement
            stmt.executeUpdate(query);
            System.out.println("\n---Table was created---");
        }
//    handle sql exceptions
        catch (SQLException ex) {
            System.out.println("\n---Statement did not execute---");
            ex.printStackTrace();
        }
    }

//    method to insert data
    public void insertData () {

//    SQL statement
        String query = "INSERT INTO ThisTable " +
                "(name, address) " +
                "VALUES ('Douglas', 'Home'), " +
                "('John', 'At Home'), " +
                "('Bobby', 'His Home')";
        try {

//    create statement
            stmt = con.createStatement();

//    execute statement
            stmt.executeUpdate(query);
            System.out.println("\n---Data inserted---");
        }

//    handle sql exceptions
        catch (SQLException ex) {
            System.out.println("\n---Statement did not execute---");
            ex.printStackTrace();
        }
    }

//    method to read data from table
    public void queryThisTable () {

//    SQL statement
        String query = "SELECT * FROM ThisTable";

        try {

//    create statement
            stmt = con.createStatement();

//    execute statement
            rs = stmt.executeQuery(query);
            System.out.println("\nid\t\tname\t\taddress\n_________________________________");

//    return next_row in the table while true
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString("name");
                String address = rs.getString("address");
                System.out.println(id + "\t\t" + name + "\t\t" + address);
            }
            System.out.println("\n---Select query was executed---");
        }

//    handle sql exceptions
        catch (SQLException ex) {
            System.out.println("\n---Statement did not execute---");
            ex.printStackTrace();
        }
    }

//    method to check for user credentials
    public boolean checkLogin(String username, String password) {
        boolean check = false;

//    SQL statement
        String query = "select * from ThisDatabase.users where username = '" + username + "' and password = '" + password + "'";

        try {
//    create statement
            stmt = con.createStatement();

//    create result set
            rs = stmt.executeQuery(query);

//    return next row in the table while true
            while (rs.next()) {
                check = true;
                System.out.println("\n---YooHoo!!!---");
            }
            if (check == false) {
                System.out.println("\n---No good---");
            }
        }
//    handle sql exceptions
        catch (Exception e) {
            e.printStackTrace();
        }
        return check;

    }
}