/*
     * Database connector Craig Framework v4
     * Database Connection management - Craig Framework v4
      */

        public class DatabaseConnection {
            private static final Logger log = LoggerFactory.getErrLogger();
            private static final HashMap<Integer, ConWrapper> connections =
                            new HashMap();
        private static String dbDriver, dbUrl, dbUser, dbPass;
            private static boolean propsInited = false;
            private static long connectionTimeOut = 5 * 60 * 1000; // 5 minutes

        private DatabaseConnection() {}

        public static Connection getConnection() {
                    Thread cThread = Thread.currentThread();
                    int threadID = (int) cThread.getId();
                    ConWrapper ret = connections.get(threadID);

                    if (ret == null) {
                            Connection retCon = connectToDB();
                            ret = new ConWrapper(retCon);
                            ret.id = threadID;
                            connections.put(threadID, ret);
                            log.info("[Database] Thread [" + threadID + "] has created a new Database Connection.");
                    }

                    return ret.getConnection();
        }

            private static long getWaitTimeout(Connection con) {
                    Statement stmt = null;
                    ResultSet rs = null;
                    try {
                            stmt = con.createStatement();
                            rs = stmt.executeQuery("SHOW VARIABLES 'wait_timeout'");
                            if (rs.next()) {
                                    return Math.max(1000, rs.getInt(2) * 1000 - 1000);
                            } else {
                                    return -1;
                            }
                    } catch (SQLException ex) {
                            return -1;
                    } finally {
                            if (stmt != null) {
                                    try {
                                            stmt.close();
                                    } catch (SQLException ex) {
                                    } finally {
                                            if (rs != null) {
                                                    try {
                                                            rs.close();
                                                    } catch (SQLException ex1) {}
                                            }
                                    }
                            }
                    }
            }

            private static Connection connectToDB() {
                    if (!propsInited) {
                            PropertyReader dbReader;

                            try {
                                    dbReader = PropertyReader.load("db.cfg");
                            } catch (IOException ex) {
                                    throw new DatabaseException(ex);
                            }

                            dbDriver = dbReader.getProperty("driver");
                            dbUrl = dbReader.getProperty("url");
                            dbUser = dbReader.getProperty("user");
                            dbPass = dbReader.getProperty("password");
                            try {
                                    connectionTimeOut = Long.parseLong(dbReader.getProperty("timeout"));
                            } catch (Exception e) {
                                    log.warn("[Database] Cannot read Connection Timeout Information, using default: " + connectionTimeOut);
                            }
                    }

                    try {
                            Class.forName(dbDriver);    // touch the MySQL driver
                    } catch (ClassNotFoundException e) {
                            throw new DatabaseException(e);
                    }

                    try {
                            Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);
                            if (!propsInited) {
                                    long timeout = getWaitTimeout(con);
                                    if (timeout == -1) {
                                            log.error("[Database] Failed to read Wait_Timeout, using " + connectionTimeOut + " instead.");
                                    } else {
                                            connectionTimeOut = timeout;
                                            log.info("[Database] Timeout is " + connectionTimeOut + " milliseconds.");
                                    }
                                    propsInited = true;
                            }
                            return con;
                    } catch (SQLException e) {
                            throw new DatabaseException(e);
                    }
            }

            static class ConWrapper {
                    private long lastAccessTime = 0;
                    private Connection connection;
                    private int id;

                    public ConWrapper(Connection con) {
                            this.connection = con;
                    }

                    public Connection getConnection() {
                            if (expiredConnection()) {
                                    log.info("[Database] Connection #" + id + " has gone stale. Reconnecting..");
                                    try { // Assume that the connection is stale
                                            connection.close();
                                    } catch (Throwable err) {
                                            // Who cares
                                    }
                                    this.connection = connectToDB();
                            }

                            lastAccessTime = System.currentTimeMillis(); // Record Access
                            return this.connection;
                    }

                    /**
                     * Returns whether this connection has expired
                     * @return
                     */
                    public boolean expiredConnection() {
                            if (lastAccessTime == 0) {
                                    return false;
                            }
                            try {
                                    return System.currentTimeMillis() - lastAccessTime >= connectionTimeOut || connection.isClosed();
                            } catch (Throwable ex) {
                                    return true;
                            }
                    }
            }

        public static void closeAll() throws SQLException {
            for (ConWrapper con : connections.values()) {
                con.connection.close();
            }
                    connections.clear();
        }
    }