package core;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;



/**
 * An example data base that manages key-value pairs.
 *
 * @author Jayson Falkner - jfalkner@umich.edu
 */
public class Database {
    // the directory where the database files are stored
    private static final String DATABASE_LOCATION = "jdbc:hsqldb:file:/example-database";
    private static final String DATABASE_DRIVER = "org.hsqldb.jdbcDriver";
    private static final String DATABASE_USER = "sa";
    private static final String DATABASE_PASSWORD = "";
    
    // static reference for the connection
    private static Connection c = null;
    
    public static synchronized Connection getConnection() throws Exception {
        lazyLoad();
        return c;
    }
    
    public static String escapeText(String text) {
        // replace all ' with \'
        return text.replace("'", "''");
    }
    
    public static synchronized void close() throws Exception {
        // if null, or already closed, skip
        if (c == null || c.isClosed()) {
            return;
        }
        
        // shutdown the database
        PreparedStatement ps = c.prepareStatement("SHUTDOWN");
        try {
            ps.execute();
        } finally {
            ps.close();
        }
        
        // close the connection
        if (!c.isClosed()) {
            c.close();
        }
    }
    
    // lazy load the database
    public static synchronized Connection lazyLoad() throws Exception {
        // skip if loaded
        if (c != null && !c.isClosed()) {
            return c;
        }
        
        // connect to the database
        Class.forName(DATABASE_DRIVER).newInstance();
        Connection c = DriverManager.getConnection(DATABASE_LOCATION, DATABASE_USER, DATABASE_PASSWORD);
        // if all went well, set the connection
        Database.c = c;
        
        // check for the database
        DatabaseMetaData meta = c.getMetaData();
        ResultSet rs = meta.getTables(null, null, "%", null);
        
        // tables to check for
        String[] tablesToCheckFor = new String[] {
            "ENTRIES",
            "TAGS"
        };
        boolean[] tablesExist = new boolean[tablesToCheckFor.length];
        // check for those tables
        try {
            while (rs.next()) {
                for (int i=0;i<tablesToCheckFor.length;i++) {
                    if (rs.getString("TABLE_NAME").equals(tablesToCheckFor[i])) {
                        tablesExist[i] = true;
                    }
                }
            }
        } finally {
            try { rs.close(); } catch (Exception e){}
        }
        
        // if the table doesn't exist, make it
        boolean tablesAlreadyExist = true;
        for (boolean b : tablesExist) {
            tablesAlreadyExist &= b;
        }

        // if not true, lazy load the tables
        if (!tablesAlreadyExist) {
            // read the file of SQL commands and execute it
            InputStream is = Database.class.getClassLoader().getResourceAsStream("lazy-load-database.sql");
            InputStreamReader isr = new InputStreamReader(is);
            BufferedReader br = new BufferedReader(isr);
            for (String s= br.readLine(); s != null; s = br.readLine()) {
                execute(s);
            }
            br.close();
            isr.close();
            is.close();
        }
        
        // return the newly made connection
        return Database.c;
    }
    
    // helper method to throw SQL at the database
    static boolean execute(final String sql) throws Exception {
        lazyLoad();
        
        PreparedStatement ps = c.prepareStatement(sql);
        boolean status = false;
        try {
            status = ps.execute();
        } finally {
            try { ps.close(); } catch (Exception e){}
        }
        
        return status;
    }
    
}