package services;

import services.utils.Logger;
import java.sql.*;
/**
 * This class provides safe access to our database, which
 * is shared among the modules.
 */
public class Database {

    /**
     * Driver name (hardcoded to Derby at this time)
     */
    private String drivername = "org.apache.derby.jdbc.EmbeddedDriver";

    /**
     * Connection URL (hardcodes the database name at the moment)
     */
    private String connection = "jdbc:derby:services_db;create=true";


    /**
     * Represents the database connection
     */
    private Connection conn;
    /**
     * The shared Database instance
     */
    private static final Database instance;

    static {
        instance = new Database();
    }

    /**
     * Nobody can construct this class but us, allowing for safe
     * startup and shutdown, avoiding corruption and safe shared
     * access among the modules
     */
    private Database() {
        Logger.log(Logger.INFO, "Probing database driver..");
        try {
            Class.forName(drivername);
        } catch (ClassNotFoundException ex) {
            Logger.log(Logger.FATAL, "Database driver not installed! Aborting!");
            System.exit(-1);
        }

        Logger.log(Logger.INFO, "Connecting to database..");
        try {
            conn = DriverManager.getConnection(connection);
        } catch (SQLException ex) {
            Logger.log(Logger.FATAL, "Database could not be initialised, aborting!");
            System.exit(-1);
        }
        // We can only make it this far if we've got the driver ;)
        Runtime.getRuntime().addShutdownHook(new Thread() {
            public void run() {
                shutdown();
            }
        });
        Logger.log(Logger.INFO, "Database initialised");

        // check sanity of database.
        try {
            Statement s = conn.createStatement();
            ResultSet res = s.executeQuery("SELECT * FROM accounts");
        } catch (SQLException ex) {
            Logger.log(Logger.ERROR, "Tables not found - recreating");
            createTables();
            createHostsTable();
        }
    }

    /**
     * (Re)create the account tables in the database
     */
    private void createTables() {
        String create_accounts = ""
        + "CREATE TABLE accounts"
        + "("
        + "ID int NOT NULL GENERATED ALWAYS AS IDENTITY,"
        + "NAME varchar(40) NOT NULL,"
        + "EMAIL varchar(255) NOT NULL,"
        + "PASS varchar(50) NOT NULL,"
        + "PRIMARY KEY (ID)"
        + ")";
        try {
            Statement s = conn.createStatement();
            s.execute(create_accounts);
        } catch (SQLException ex) {
            Logger.log(Logger.FATAL, "Could not create account tables, aborting!");
            System.exit(-1);
            ex.printStackTrace();
        }
    }

    /**
     * (Re)create the vhosts table
     */
    private void createHostsTable() {
        String create_hosts = ""
        + "CREATE TABLE hosts"
        + "("
        + "ID int NOT NULL,"
        + "HOST varchar(255) NOT NULL,"
        + "CONFIRMED CHAR NOT NULL"
        + ")";
        try {
            Statement s = conn.createStatement();
            s.execute(create_hosts);
        } catch (SQLException ex) {
            Logger.log(Logger.FATAL, "Could not create vhost tables, aborting!");
            ex.printStackTrace();
        }
    }

    /**
     * Returns the safe-shared Database instance
     */
    public static final Database getInstance() {
        return instance;
    }

    /**
     * Returns a ResultSet or null, tries to execute the query
     * making Database a factory class
     * @param query the query to execute
     * @return ResultSet or null
     * @throws java.net.SQLException if there is an error.
     */
    public ResultSet executeQuery(String query) throws SQLException {
        Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        return st.executeQuery(query);
    }

    /**
     * Shorthand access to Statement.executeUpdate
     * @param sql The SQL to send to the DB
     * @return an integer showing how many (if any) rows were updated
     * @throws java.net.SQLException if there is any sql error
     */
    public int executeUpdate(String sql) throws SQLException {
        Statement st = conn.createStatement();
        return st.executeUpdate(sql);
    }
    /**
     * Shutdown the database.
     */
    private void shutdown() {
        boolean clean = false;
        try {
            Connection c = DriverManager.getConnection("jdbc:derby:;shutdown=true");
        } catch (SQLException ex) {
            if(ex.getSQLState().equals("XJ015")) {
                clean = true;
            }
        }
        if (!clean) {
            System.out.println("Database did not shut down normally");
            Logger.log(Logger.FATAL, "Database did not shut down cleanly");
        }  else  {
            Logger.log(Logger.INFO, "Database shut down");
        }
    }
}