/*
 *  Copyright (C) 2006-2015  Ronald Blankendaal
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
 */
package org.dbgl.db;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.dbgl.model.Filter;
import org.dbgl.model.KeyValuePair;
import org.dbgl.model.DosboxVersion;
import org.dbgl.model.NativeCommand;
import org.dbgl.model.Profile;
import org.dbgl.model.Template;
import org.dbgl.model.conf.Settings;
import org.dbgl.util.FileUtils;
import org.dbgl.util.StringRelatedUtils;
import org.dbgl.gui.GeneralPurposeDialogs;
import org.eclipse.swt.widgets.Shell;


public class Database {

    private static Connection con = null;
    private static boolean initializedNewDatabase = false;

    private static final String GET_IDENTITY_QRY = "CALL IDENTITY()";
    private static final String GAME_LIST_QRY = "SELECT GAM.ID, GAM.TITLE, DEV.NAME, PUBL.NAME, GEN.NAME, YR.YEAR, "
    		+ "STAT.STAT, GAM.NOTES, GAM.FAVORITE, "
    		+ "GAM.SETUP, GAM.SETUP_PARAMS, GAM.ALT1, GAM.ALT1_PARAMS, GAM.ALT2, GAM.ALT2_PARAMS, "
    		+ "GAM.CONFFILE, GAM.CAPTURES, GAM.DBVERSION_ID, "
    		+ "GAM.LINK1, GAM.LINK2, GAM.LINK3, GAM.LINK4, GAM.LINK5, GAM.LINK6, GAM.LINK7, GAM.LINK8, "
    		+ "GAM.LINK1_TITLE, GAM.LINK2_TITLE, GAM.LINK3_TITLE, GAM.LINK4_TITLE, GAM.LINK5_TITLE, GAM.LINK6_TITLE, GAM.LINK7_TITLE, GAM.LINK8_TITLE, "
            + "CUST1.VALUE, CUST2.VALUE, CUST3.VALUE, CUST4.VALUE, GAM.CUSTOM5, GAM.CUSTOM6, GAM.CUSTOM7, GAM.CUSTOM8, GAM.CUSTOM9, GAM.CUSTOM10 "
            + "FROM GAMES GAM, DEVELOPERS DEV, PUBLISHERS PUBL, GENRES GEN, PUBLYEARS YR, STATUS STAT, "
            + "CUSTOM1 CUST1, CUSTOM2 CUST2, CUSTOM3 CUST3, CUSTOM4 CUST4 "
            + "WHERE GAM.DEV_ID=DEV.ID AND GAM.PUBL_ID=PUBL.ID AND GAM.GENRE_ID=GEN.ID AND GAM.YEAR_ID=YR.ID AND GAM.STAT_ID=STAT.ID "
            + "AND   GAM.CUST1_ID=CUST1.ID AND GAM.CUST2_ID=CUST2.ID AND GAM.CUST3_ID=CUST3.ID AND GAM.CUST4_ID=CUST4.ID";
    private static final String DUPL_GAME_QRY = "INSERT INTO GAMES("
            + "TITLE, DEV_ID, PUBL_ID, GENRE_ID, YEAR_ID, STAT_ID, NOTES, FAVORITE, "
            + "SETUP, SETUP_PARAMS, ALT1, ALT1_PARAMS, ALT2, ALT2_PARAMS,"
            + "CONFFILE, CAPTURES, DBVERSION_ID, LINK1, LINK2, LINK3, LINK4, LINK5, LINK6, LINK7, LINK8,"
            + "LINK1_TITLE, LINK2_TITLE, LINK3_TITLE, LINK4_TITLE, LINK5_TITLE, LINK6_TITLE, LINK7_TITLE, LINK8_TITLE,"
            + "CUST1_ID, CUST2_ID, CUST3_ID, CUST4_ID, CUSTOM5, CUSTOM6, CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10) "
            + "(SELECT TITLE, DEV_ID, PUBL_ID, GENRE_ID, YEAR_ID, STAT_ID, NOTES, FAVORITE,"
            + "SETUP, SETUP_PARAMS, ALT1, ALT1_PARAMS, ALT2, ALT2_PARAMS, NULL, NULL, DBVERSION_ID,"
            + "LINK1, LINK2, LINK3, LINK4, LINK5, LINK6, LINK7, LINK8, "
            + "LINK1_TITLE, LINK2_TITLE, LINK3_TITLE, LINK4_TITLE, LINK5_TITLE, LINK6_TITLE, LINK7_TITLE, LINK8_TITLE,"  
            + "CUST1_ID, CUST2_ID, CUST3_ID, CUST4_ID, CUSTOM5, CUSTOM6, CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10 "
            + "FROM GAMES WHERE ID = ?)";
    private static final String DUPL_TEMPLATE_QRY = "INSERT INTO TEMPLATES(TITLE, DBVERSION_ID, ISDEFAULT) "
            + "(SELECT TITLE, DBVERSION_ID, FALSE FROM TEMPLATES WHERE ID = ?)";
    private static final String DEV_LIST_QRY = "SELECT ID, NAME FROM DEVELOPERS ORDER BY NAME";
    private static final String PUBL_LIST_QRY = "SELECT ID, NAME FROM PUBLISHERS ORDER BY NAME";
    private static final String GENRE_LIST_QRY = "SELECT ID, NAME FROM GENRES ORDER BY NAME";
    private static final String PUBLYEAR_LIST_QRY = "SELECT ID, YEAR FROM PUBLYEARS ORDER BY YEAR";
    private static final String DBVERS_LIST_QRY = "SELECT ID, TITLE, PATH, CONFFILE, MULTICONF, USINGCURSES, ISDEFAULT, PARAMETERS, VERSION FROM DOSBOXVERSIONS ORDER BY TITLE";
    private static final String FLTRS_LIST_QRY = "SELECT ID, TITLE, FILTER FROM FILTERS";
    private static final String TEMPL_LIST_QRY = "SELECT ID, TITLE, DBVERSION_ID, ISDEFAULT FROM TEMPLATES ORDER BY ID";
    private static final String STATUS_LIST_QRY = "SELECT ID, STAT FROM STATUS ORDER BY STAT";
    private static final String[] CUSTOM_LIST_QRY = {
            "SELECT ID, VALUE FROM CUSTOM1 ORDER BY VALUE", "SELECT ID, VALUE FROM CUSTOM2 ORDER BY VALUE",
            "SELECT ID, VALUE FROM CUSTOM3 ORDER BY VALUE", "SELECT ID, VALUE FROM CUSTOM4 ORDER BY VALUE" };
    private static final String DBV_USE_QRY = "SELECT TITLE FROM GAMES WHERE DBVERSION_ID = ? UNION ALL SELECT TITLE FROM TEMPLATES WHERE DBVERSION_ID = ?";
    private static final String NTVCMD_LIST_QRY_TEMPLATE = "SELECT COMMAND, PARAMETERS, CWD, WAITFOR, ORDERNR FROM NATIVECOMMANDS WHERE GAME_ID IS NULL AND TEMPLATE_ID = ? ORDER BY ORDERNR";
    private static final String NTVCMD_LIST_QRY_GAME = "SELECT COMMAND, PARAMETERS, CWD, WAITFOR, ORDERNR FROM NATIVECOMMANDS WHERE GAME_ID = ? AND TEMPLATE_ID IS NULL ORDER BY ORDERNR";

    private static final String ADD_GAME_QRY = "INSERT INTO GAMES(TITLE, DEV_ID, PUBL_ID, GENRE_ID, YEAR_ID, STAT_ID, NOTES, FAVORITE,"
    		+ "SETUP, SETUP_PARAMS, ALT1, ALT1_PARAMS, ALT2, ALT2_PARAMS, CONFFILE, CAPTURES, DBVERSION_ID,"
    		+ "LINK1, LINK2, LINK3, LINK4, LINK5, LINK6, LINK7, LINK8,"
    		+ "LINK1_TITLE, LINK2_TITLE, LINK3_TITLE, LINK4_TITLE, LINK5_TITLE, LINK6_TITLE, LINK7_TITLE, LINK8_TITLE,"
    		+ "CUST1_ID, CUST2_ID, CUST3_ID, CUST4_ID, CUSTOM5, CUSTOM6, CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10) "
            + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String ADD_DEVELOPER_QRY = "INSERT INTO DEVELOPERS(NAME) VALUES (?)";
    private static final String ADD_PUBLISHER_QRY = "INSERT INTO PUBLISHERS(NAME) VALUES (?)";
    private static final String ADD_GENRE_QRY = "INSERT INTO GENRES(NAME) VALUES (?)";
    private static final String ADD_YEAR_QRY = "INSERT INTO PUBLYEARS(YEAR) VALUES (?)";
    private static final String ADD_DBVERSION_QRY = "INSERT INTO DOSBOXVERSIONS(TITLE, PATH, CONFFILE, MULTICONF, USINGCURSES, ISDEFAULT, PARAMETERS, VERSION) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String ADD_TEMPLATE_QRY = "INSERT INTO TEMPLATES(TITLE, DBVERSION_ID, ISDEFAULT) VALUES (?, ?, ?)";
    private static final String ADD_FILTER_QRY = "INSERT INTO FILTERS(TITLE, FILTER, CONF_FILTER) VALUES (?, ?, '')";
    private static final String ADD_STATUS_QRY = "INSERT INTO STATUS(STAT) VALUES (?)";
    private static final String[] ADD_CUSTOM_QRY = { "INSERT INTO CUSTOM1(VALUE) VALUES (?)",
            "INSERT INTO CUSTOM2(VALUE) VALUES (?)", "INSERT INTO CUSTOM3(VALUE) VALUES (?)",
            "INSERT INTO CUSTOM4(VALUE) VALUES (?)" };
    private static final String ADD_NATIVECOMMAND_QRY = "INSERT INTO NATIVECOMMANDS(COMMAND, PARAMETERS, CWD, WAITFOR, ORDERNR, GAME_ID, TEMPLATE_ID) VALUES (?, ?, ?, ?, ?, ?, ?)";

    private static final String UPD_GAME_QRY = "UPDATE GAMES SET TITLE = ?, DEV_ID = ?, PUBL_ID = ?, GENRE_ID = ?, YEAR_ID = ?, "
            + "STAT_ID = ?, NOTES = ?, FAVORITE = ?, SETUP = ?, SETUP_PARAMS = ?, ALT1 = ?, ALT1_PARAMS = ?, ALT2 = ?, ALT2_PARAMS = ?,"
            + "DBVERSION_ID = ?, LINK1 = ?, LINK2 = ?, LINK3 = ?, LINK4 = ?, LINK5 = ?, LINK6 = ?, LINK7 = ?, LINK8 = ?, "
            + "LINK1_TITLE = ?, LINK2_TITLE = ?, LINK3_TITLE = ?, LINK4_TITLE = ?, LINK5_TITLE = ?, LINK6_TITLE = ?, LINK7_TITLE = ?, LINK8_TITLE = ?,"
            + "CUST1_ID = ?, CUST2_ID = ?, CUST3_ID = ?, CUST4_ID = ?, CUSTOM5 = ?, CUSTOM6 = ?, CUSTOM7 = ?, CUSTOM8 = ?, CUSTOM9 = ?, CUSTOM10 = ? "
            + "WHERE ID = ?";
    private static final String UPD_GAME_CONF_QRY = "UPDATE GAMES SET CONFFILE = ?, CAPTURES = ? WHERE ID = ?";
    private static final String UPD_STPLNKS_QRY = "UPDATE GAMES SET SETUP = ?, ALT1 = ?, ALT2 = ?, "
    		+ "LINK1 = ?, LINK2 = ?, LINK3 = ?, LINK4 = ?, LINK5 = ?, LINK6 = ?, LINK7 = ?, LINK8 = ? WHERE ID = ?";
    private static final String UPD_DBV_NODEFAULT = "UPDATE DOSBOXVERSIONS SET ISDEFAULT = FALSE";
    private static final String UPD_DBVERSION_QRY = "UPDATE DOSBOXVERSIONS SET TITLE = ?, PATH = ?, CONFFILE = ?, MULTICONF = ?, USINGCURSES = ?, ISDEFAULT = ?, PARAMETERS = ?, VERSION = ? WHERE ID = ?";
    private static final String UPD_TEMPLATE_QRY = "UPDATE TEMPLATES SET TITLE = ?, DBVERSION_ID = ?, ISDEFAULT = ? WHERE ID = ?";
    private static final String UPD_FILTER_QRY = "UPDATE FILTERS SET TITLE = ?, FILTER = ? WHERE ID = ?";
    private static final String UPD_TEMPL_NODFLT = "UPDATE TEMPLATES SET ISDEFAULT = FALSE";

    private static final String REMOVE_GAME_QRY = "DELETE FROM GAMES WHERE ID = ?";
    private static final String REMOVE_DBV_QRY = "DELETE FROM DOSBOXVERSIONS WHERE ID = ?";
    private static final String REMOVE_TEMPL_QRY = "DELETE FROM TEMPLATES WHERE ID = ?";
    private static final String REMOVE_FLTR_QRY = "DELETE FROM FILTERS WHERE ID = ?";
    private static final String REMOVE_NTVCMD_QRY_TEMPLATE = "DELETE FROM NATIVECOMMANDS WHERE GAME_ID IS NULL AND TEMPLATE_ID = ?";
    private static final String REMOVE_NTVCMD_QRY_GAME = "DELETE FROM NATIVECOMMANDS WHERE GAME_ID = ? AND TEMPLATE_ID IS NULL";
    
    private static final String TOGGLE_FAV_QRY = "UPDATE GAMES SET FAVORITE = NOT FAVORITE WHERE ID = ?";

    private static final String GET_VERSION = "SELECT MAJORVERSION, MINORVERSION FROM VERSION";
    private static final String UP_TO_V050_QRY = "ALTER TABLE GAMES ADD COLUMN CONFFILE VARCHAR(256);"
            + "ALTER TABLE GAMES ADD COLUMN CAPTURES VARCHAR(256);"
            + "CREATE TABLE VERSION(MAJORVERSION INTEGER NOT NULL, MINORVERSION INTEGER NOT NULL);"
            + "INSERT INTO VERSION VALUES(0, 50);" + "UPDATE GAMES SET" + " CAPTURES = '" + FileUtils.CAPTURES_DIR
            + "' || GAMES.ID," + " CONFFILE = '" + FileUtils.PROFILES_DIR + "' || GAMES.ID || '" + FileUtils.CONF_EXT + "';";
    private static final String UP_TO_V051_QRY = "ALTER TABLE DOSBOXVERSIONS ADD COLUMN PARAMETERS VARCHAR(256) DEFAULT '';"
            + "UPDATE VERSION SET MINORVERSION = 51;";
    private static final String UP_TO_V056_QRY = "ALTER TABLE GAMES ADD COLUMN LINK3 VARCHAR(256) DEFAULT '';"
            + "ALTER TABLE GAMES ADD COLUMN LINK4 VARCHAR(256) DEFAULT '';"
            + "ALTER TABLE GAMES ADD COLUMN CUST1_ID INTEGER DEFAULT 0;"
            + "ALTER TABLE GAMES ADD COLUMN CUST2_ID INTEGER DEFAULT 0;"
            + "ALTER TABLE GAMES ADD COLUMN CUST3_ID INTEGER DEFAULT 0;"
            + "ALTER TABLE GAMES ADD COLUMN CUST4_ID INTEGER DEFAULT 0;"
            + "ALTER TABLE GAMES ADD COLUMN CUSTOM5 VARCHAR(256) DEFAULT '';"
            + "ALTER TABLE GAMES ADD COLUMN CUSTOM6 VARCHAR(256) DEFAULT '';"
            + "ALTER TABLE GAMES ADD COLUMN CUSTOM7 VARCHAR(256) DEFAULT '';"
            + "ALTER TABLE GAMES ADD COLUMN CUSTOM8 VARCHAR(256) DEFAULT '';"
            + "ALTER TABLE GAMES ADD COLUMN CUSTOM9  INTEGER DEFAULT 0;"
            + "ALTER TABLE GAMES ADD COLUMN CUSTOM10 INTEGER DEFAULT 0;"
            + "CREATE TABLE CUSTOM1(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY, VALUE VARCHAR(256) NOT NULL);"
            + "CREATE TABLE CUSTOM2(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY, VALUE VARCHAR(256) NOT NULL);"
            + "CREATE TABLE CUSTOM3(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY, VALUE VARCHAR(256) NOT NULL);"
            + "CREATE TABLE CUSTOM4(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY, VALUE VARCHAR(256) NOT NULL);"
            + "INSERT INTO CUSTOM1(VALUE) VALUES('');" + "INSERT INTO CUSTOM2(VALUE) VALUES('');"
            + "INSERT INTO CUSTOM3(VALUE) VALUES('');" + "INSERT INTO CUSTOM4(VALUE) VALUES('');"
            + "UPDATE VERSION SET MINORVERSION = 56;";
    private static final String UP_TO_V062_QRY = "ALTER TABLE DOSBOXVERSIONS ADD COLUMN VERSION VARCHAR(256) DEFAULT '0.72' NOT NULL;"
            + "UPDATE VERSION SET MINORVERSION = 62;";
    private static final String UP_TO_V065_QRY = 
              "ALTER TABLE GAMES ADD COLUMN LINK1_TITLE VARCHAR(256) DEFAULT '';"
            + "ALTER TABLE GAMES ADD COLUMN LINK2_TITLE VARCHAR(256) DEFAULT '';"
            + "ALTER TABLE GAMES ADD COLUMN LINK3_TITLE VARCHAR(256) DEFAULT '';"
            + "ALTER TABLE GAMES ADD COLUMN LINK4_TITLE VARCHAR(256) DEFAULT '';"
            + "UPDATE VERSION SET MINORVERSION = 65;";
    private static final String UP_TO_V067_QRY = 
		      "ALTER TABLE DOSBOXVERSIONS ADD COLUMN USINGCURSES BOOLEAN;"
		    + "UPDATE VERSION SET MINORVERSION = 67;";
    private static final String UP_TO_V068_QRY = 
        	  "CREATE MEMORY TABLE FILTERS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,TITLE VARCHAR(256) NOT NULL,FILTER VARCHAR(256) NOT NULL,CONF_FILTER VARCHAR(256) NOT NULL);"
    		+ "UPDATE VERSION SET MINORVERSION = 68;";
    private static final String UP_TO_V072_QRY = 
        	  "ALTER TABLE DOSBOXVERSIONS ADD COLUMN CONFFILE VARCHAR(256) DEFAULT '' NOT NULL;"
    		+ "UPDATE DOSBOXVERSIONS SET CONFFILE = CONCAT(PATH, '" + File.separatorChar + FileUtils.DOSBOX_CONF + "');"
    		+ "UPDATE VERSION SET MINORVERSION = 72;";
    private static final String UP_TO_V073_QRY = 
	    	  "ALTER TABLE GAMES ADD COLUMN ALT1 VARCHAR(256) DEFAULT '';"
	    	+ "ALTER TABLE GAMES ADD COLUMN ALT1_PARAMS VARCHAR(256) DEFAULT '';"
	    	+ "ALTER TABLE GAMES ADD COLUMN ALT2 VARCHAR(256) DEFAULT '';"
	    	+ "ALTER TABLE GAMES ADD COLUMN ALT2_PARAMS VARCHAR(256) DEFAULT '';"
	    	+ "ALTER TABLE GAMES ADD COLUMN LINK5 VARCHAR(256) DEFAULT '';"
	        + "ALTER TABLE GAMES ADD COLUMN LINK6 VARCHAR(256) DEFAULT '';"
	        + "ALTER TABLE GAMES ADD COLUMN LINK7 VARCHAR(256) DEFAULT '';"
	        + "ALTER TABLE GAMES ADD COLUMN LINK8 VARCHAR(256) DEFAULT '';"
	        + "ALTER TABLE GAMES ADD COLUMN LINK5_TITLE VARCHAR(256) DEFAULT '';"
	        + "ALTER TABLE GAMES ADD COLUMN LINK6_TITLE VARCHAR(256) DEFAULT '';"
	        + "ALTER TABLE GAMES ADD COLUMN LINK7_TITLE VARCHAR(256) DEFAULT '';"
	        + "ALTER TABLE GAMES ADD COLUMN LINK8_TITLE VARCHAR(256) DEFAULT '';"
	        + "UPDATE VERSION SET MINORVERSION = 73;";
    private static final String UP_TO_V074_QRY =
    		  "ALTER TABLE DOSBOXVERSIONS ALTER COLUMN DEFAULT RENAME TO ISDEFAULT;"
    		+ "ALTER TABLE TEMPLATES ALTER COLUMN DEFAULT RENAME TO ISDEFAULT;"
    		+ "UPDATE VERSION SET MINORVERSION = 74;";
    private static final String UP_TO_V075_QRY =
  		      "CREATE MEMORY TABLE NATIVECOMMANDS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,COMMAND VARCHAR(256) NOT NULL,PARAMETERS VARCHAR(256) NOT NULL,CWD VARCHAR(256) NOT NULL,WAITFOR BOOLEAN,ORDERNR INTEGER,GAME_ID INTEGER,TEMPLATE_ID INTEGER,"
    		+ "CONSTRAINT SYS_FK_180 FOREIGN KEY(GAME_ID) REFERENCES GAMES(ID),CONSTRAINT SYS_FK_181 FOREIGN KEY(TEMPLATE_ID) REFERENCES TEMPLATES(ID));"
  		    + "UPDATE VERSION SET MINORVERSION = 75;";

    private static final String CREATE_INITIAL_DB = "SET WRITE_DELAY 1;"
            + "CREATE MEMORY TABLE VERSION(MAJORVERSION INTEGER NOT NULL,MINORVERSION INTEGER NOT NULL);"
            + "CREATE MEMORY TABLE DEVELOPERS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,NAME VARCHAR(256) NOT NULL);"
            + "CREATE MEMORY TABLE PUBLISHERS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,NAME VARCHAR(256) NOT NULL);"
            + "CREATE MEMORY TABLE GENRES(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,NAME VARCHAR(256) NOT NULL);"
            + "CREATE MEMORY TABLE PUBLYEARS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,YEAR CHAR(4) NOT NULL);"
            + "CREATE MEMORY TABLE STATUS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,STAT VARCHAR(256) NOT NULL);"
            + "CREATE MEMORY TABLE CUSTOM1(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,VALUE VARCHAR(256) NOT NULL);"
            + "CREATE MEMORY TABLE CUSTOM2(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,VALUE VARCHAR(256) NOT NULL);"
            + "CREATE MEMORY TABLE CUSTOM3(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,VALUE VARCHAR(256) NOT NULL);"
            + "CREATE MEMORY TABLE CUSTOM4(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,VALUE VARCHAR(256) NOT NULL);"
            + "CREATE MEMORY TABLE DOSBOXVERSIONS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,TITLE VARCHAR(256) NOT NULL,PATH VARCHAR(256) NOT NULL,CONFFILE VARCHAR(256) NOT NULL,MULTICONF BOOLEAN,ISDEFAULT BOOLEAN,PARAMETERS VARCHAR(256) DEFAULT '',VERSION VARCHAR(256) NOT NULL,USINGCURSES BOOLEAN);"
            + "CREATE MEMORY TABLE TEMPLATES(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,TITLE VARCHAR(256) NOT NULL,DBVERSION_ID INTEGER,ISDEFAULT BOOLEAN,CONSTRAINT SYS_FK_185 FOREIGN KEY(DBVERSION_ID) REFERENCES DOSBOXVERSIONS(ID));"
            + "CREATE MEMORY TABLE GAMES(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"
            + "TITLE VARCHAR(256) NOT NULL,DEV_ID INTEGER,PUBL_ID INTEGER,GENRE_ID INTEGER,YEAR_ID INTEGER,STAT_ID INTEGER,NOTES LONGVARCHAR,FAVORITE BOOLEAN,"
            + "SETUP VARCHAR(256),SETUP_PARAMS VARCHAR(256),ALT1 VARCHAR(256) DEFAULT '',ALT1_PARAMS VARCHAR(256) DEFAULT '',ALT2 VARCHAR(256) DEFAULT '',ALT2_PARAMS VARCHAR(256) DEFAULT '',"
            + "CONFFILE VARCHAR(256),CAPTURES VARCHAR(256),DBVERSION_ID INTEGER,"
            + "LINK1 VARCHAR(256),LINK2 VARCHAR(256),LINK3 VARCHAR(256) DEFAULT '',LINK4 VARCHAR(256) DEFAULT '',"
            + "LINK5 VARCHAR(256) DEFAULT '',LINK6 VARCHAR(256) DEFAULT '',LINK7 VARCHAR(256) DEFAULT '',LINK8 VARCHAR(256) DEFAULT '',"
            + "LINK1_TITLE VARCHAR(256) DEFAULT '',LINK2_TITLE VARCHAR(256) DEFAULT '',LINK3_TITLE VARCHAR(256) DEFAULT '',LINK4_TITLE VARCHAR(256) DEFAULT '',"
            + "LINK5_TITLE VARCHAR(256) DEFAULT '',LINK6_TITLE VARCHAR(256) DEFAULT '',LINK7_TITLE VARCHAR(256) DEFAULT '',LINK8_TITLE VARCHAR(256) DEFAULT '',"
            + "CUST1_ID INTEGER DEFAULT 0,CUST2_ID INTEGER DEFAULT 0,CUST3_ID INTEGER DEFAULT 0,CUST4_ID INTEGER DEFAULT 0,"
            + "CUSTOM5 VARCHAR(256) DEFAULT '',CUSTOM6 VARCHAR(256) DEFAULT '',CUSTOM7 VARCHAR(256) DEFAULT '',CUSTOM8 VARCHAR(256) DEFAULT '',"
            + "CUSTOM9 INTEGER DEFAULT 0,CUSTOM10 INTEGER DEFAULT 0,"
            + "CONSTRAINT SYS_FK_165 FOREIGN KEY(DEV_ID) REFERENCES DEVELOPERS(ID),CONSTRAINT SYS_FK_166 FOREIGN KEY(PUBL_ID) REFERENCES PUBLISHERS(ID),"
            + "CONSTRAINT SYS_FK_167 FOREIGN KEY(GENRE_ID) REFERENCES GENRES(ID),CONSTRAINT SYS_FK_168 FOREIGN KEY(YEAR_ID) REFERENCES PUBLYEARS(ID),"
            + "CONSTRAINT SYS_FK_169 FOREIGN KEY(DBVERSION_ID) REFERENCES DOSBOXVERSIONS(ID),CONSTRAINT SYS_FK_170 FOREIGN KEY(STAT_ID) REFERENCES STATUS(ID));"
            + "CREATE MEMORY TABLE FILTERS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,TITLE VARCHAR(256) NOT NULL,FILTER VARCHAR(256) NOT NULL,CONF_FILTER VARCHAR(256) NOT NULL);"
            + "CREATE MEMORY TABLE NATIVECOMMANDS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,COMMAND VARCHAR(256) NOT NULL,PARAMETERS VARCHAR(256) NOT NULL,CWD VARCHAR(256) NOT NULL,WAITFOR BOOLEAN,ORDERNR INTEGER,GAME_ID INTEGER,TEMPLATE_ID INTEGER,"
    		+ "CONSTRAINT SYS_FK_180 FOREIGN KEY(GAME_ID) REFERENCES GAMES(ID),CONSTRAINT SYS_FK_181 FOREIGN KEY(TEMPLATE_ID) REFERENCES TEMPLATES(ID));"
            + "INSERT INTO CUSTOM1(VALUE) VALUES(''); INSERT INTO CUSTOM2(VALUE) VALUES('');"
            + "INSERT INTO CUSTOM3(VALUE) VALUES(''); INSERT INTO CUSTOM4(VALUE) VALUES('');"
            + "INSERT INTO VERSION VALUES(0,75);";

    private static final String TEST_QRY = "SELECT TOP 1 ID FROM GAMES";
    
    private static final String CLEANUP_QRY =
              "DELETE FROM DEVELOPERS WHERE ID NOT IN (SELECT DISTINCT DEV_ID FROM GAMES);"
            + "DELETE FROM PUBLISHERS WHERE ID NOT IN (SELECT DISTINCT PUBL_ID FROM GAMES);"
            + "DELETE FROM GENRES WHERE ID NOT IN (SELECT DISTINCT GENRE_ID FROM GAMES);"
            + "DELETE FROM PUBLYEARS WHERE ID NOT IN (SELECT DISTINCT YEAR_ID FROM GAMES);"
            + "DELETE FROM STATUS WHERE ID NOT IN (SELECT DISTINCT STAT_ID FROM GAMES);"
            + "DELETE FROM CUSTOM1 WHERE ID NOT IN (SELECT DISTINCT CUST1_ID FROM GAMES);"
            + "DELETE FROM CUSTOM2 WHERE ID NOT IN (SELECT DISTINCT CUST2_ID FROM GAMES);"
            + "DELETE FROM CUSTOM3 WHERE ID NOT IN (SELECT DISTINCT CUST3_ID FROM GAMES);"
            + "DELETE FROM CUSTOM4 WHERE ID NOT IN (SELECT DISTINCT CUST4_ID FROM GAMES)";
    private static final String FIND_INVALID_PROFILES =
    		  GAME_LIST_QRY + " AND (GAM.CONFFILE IS NULL OR GAM.CAPTURES IS NULL)";

    private static final int COMPLEX_STRINGS = CUSTOM_LIST_QRY.length;
    private static final int SIMPLE_STRINGS = 4;


    private Database() {
        init();
    }

    private static class DatabaseHolder {
        private static Database instance = new Database();
    }

    public static Database getInstance() {
        return DatabaseHolder.instance;
    }
    
    public static boolean isInitializedNewDatabase() {
		return initializedNewDatabase;
	}

    private int[] getVersion() {
        try (Statement stmt = con.createStatement(); ResultSet resultset = stmt.executeQuery(GET_VERSION)) {
            resultset.next();
            return new int[] { resultset.getInt(1), resultset.getInt(2) }; // major, minor
        } catch (SQLException e) {
        	return new int[] {0, 0}; // assume version < 0.50 (0.0)
        }
    }

    private void upgradeToVersion(final String query, final int minorVersion) throws SQLException {
        System.out.println(Settings.getInstance().msg("database.notice.upgrade", new Object[] {0, minorVersion}));
        try (Statement stmt = con.createStatement()) {
            for (String s: query.split(";"))
                stmt.addBatch(s);
            stmt.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.upgrade", new Object[] {0, minorVersion}));
        }        
    }

    private void initializeIfNecessary() throws SQLException {
        try (Statement stmt = con.createStatement()) {
            stmt.executeQuery(TEST_QRY);
        } catch (SQLException emptydatabase) {
            // Probably empty database, fill it
            try (Statement stmt2 = con.createStatement()) {
                for (String s: CREATE_INITIAL_DB.split(";"))
                    stmt2.addBatch(s);
                stmt2.executeBatch();
                initializedNewDatabase = true;
            } catch (SQLException e) {
                e.printStackTrace();
                throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"create initial tables"}));
            }
        }
    }

    private void upgradeIfNecessary() throws SQLException {
        int[] version = getVersion();
        if (version[0] <= 0 && version[1] < 50) { upgradeToVersion(UP_TO_V050_QRY, 50);}
        if (version[0] <= 0 && version[1] < 51) { upgradeToVersion(UP_TO_V051_QRY, 51);}
        if (version[0] <= 0 && version[1] < 56) { upgradeToVersion(UP_TO_V056_QRY, 56);}
        if (version[0] <= 0 && version[1] < 62) { upgradeToVersion(UP_TO_V062_QRY, 62);}
        if (version[0] <= 0 && version[1] < 65) { upgradeToVersion(UP_TO_V065_QRY, 65);}
        if (version[0] <= 0 && version[1] < 67) { upgradeToVersion(UP_TO_V067_QRY, 67);}
        if (version[0] <= 0 && version[1] < 68) { upgradeToVersion(UP_TO_V068_QRY, 68);}
        if (version[0] <= 0 && version[1] < 72) { upgradeToVersion(UP_TO_V072_QRY, 72);}
        if (version[0] <= 0 && version[1] < 73) { upgradeToVersion(UP_TO_V073_QRY, 73);}
        if (version[0] <= 0 && version[1] < 74) { upgradeToVersion(UP_TO_V074_QRY, 74);}
        if (version[0] <= 0 && version[1] < 75) { upgradeToVersion(UP_TO_V075_QRY, 75);}
    }

    private void init() {
        Settings settings = Settings.getInstance();
        System.out.println(settings.msg("database.notice.startup"));
        String connString = settings.getSettings().getValue("database", "connectionstring");
        if (connString.contains("file:")) {
            // Some magic on the connection string
            int start = connString.indexOf("file:") + 5; // skip 'file:'
            int end = connString.indexOf(';', start);
            if (end == -1) {
                end = connString.length();
            }
            String databasefile = FileUtils.getDatabaseFile(connString).getPath();
            connString = connString.substring(0, start) + databasefile + connString.substring(end);
        }
        try {
            // Register the JDBC driver for dBase
            Class.forName("org.hsqldb.jdbcDriver");
            con = DriverManager.getConnection(connString,
                    settings.getSettings().getValue("database", "username"), settings.getSettings().getValue("database", "password"));
            initializeIfNecessary();
            upgradeIfNecessary();
        } catch (SQLException e) {
        	Shell shell = new Shell();
            GeneralPurposeDialogs.fatalMessage(shell, settings.msg("database.error.initconnection", new Object[] {StringRelatedUtils.toString(e)}), e);
            try {
                if (con != null)
                    con.close();
            } catch (SQLException exc) {
            	GeneralPurposeDialogs.warningMessage(shell, settings.msg("database.error.connectionclose", new Object[] {StringRelatedUtils.toString(exc)}), exc);
            }
            throw new RuntimeException();
        } catch (ClassNotFoundException e) {
            Shell shell = new Shell();
            GeneralPurposeDialogs.fatalMessage(shell, settings.msg("database.error.registerdriver", new Object[] {StringRelatedUtils.toString(e)}), e);
            throw new RuntimeException();
        }
    }

    private int identity() throws SQLException {
        try (Statement stmt = con.createStatement(); ResultSet resultset = stmt.executeQuery(GET_IDENTITY_QRY)) {
            resultset.next();
            return resultset.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"get identity"}));
        }
    }

    private int addSomething(final String query, final String objectToAdd) throws SQLException {
        try (PreparedStatement stmt = con.prepareStatement(query)) {
            stmt.setString(1, objectToAdd);
            stmt.executeUpdate();
            return identity();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"add " + objectToAdd}));
        }
    }
    
    private void removeSomething(final String query, final String objectToRemove, final int objectId) throws SQLException {
    	try (PreparedStatement stmt = con.prepareStatement(query)) {
            stmt.setInt(1, objectId);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"remove " + objectToRemove}));
        }
    }

    private List<KeyValuePair> readKeyValuePairList(final String query) throws SQLException {
        try (Statement stmt = con.createStatement(); ResultSet resultset = stmt.executeQuery(query)) {
        	List<KeyValuePair> customList = new ArrayList<KeyValuePair>();
            while (resultset.next())
                customList.add(new KeyValuePair(resultset.getInt(1), resultset.getString(2)));
            return customList;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"read KeyValuePairs"}));
        }
    }

    public void shutdown() throws SQLException {
        System.out.println(Settings.getInstance().msg("database.notice.shutdown"));
        try (Statement stmt = con.createStatement()) {
            stmt.execute("SHUTDOWN");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.shutdown"));
        } finally {
            con.close();
        }
    }

    public List<Profile> readProfilesList(final String orderingClause, final String filterClause) throws SQLException {
        StringBuffer qry = new StringBuffer(GAME_LIST_QRY);
        if (filterClause != null && filterClause.length() > 0)
        	qry.append(" AND (").append(filterClause).append(")");
        qry.append(orderingClause);
        
        try (Statement stmt = con.createStatement(); ResultSet rset = stmt.executeQuery(qry.toString())) {
        	List<Profile> profilesList = new ArrayList<Profile>();
            while (rset.next())
                profilesList.add(
                	new Profile(
                		rset.getInt(1), rset.getString(2), rset.getString(3), rset.getString(4),
                        rset.getString(5), rset.getString(6), rset.getString(7), rset.getString(8), rset.getBoolean(9),
                        new String[] { rset.getString(10), rset.getString(12), rset.getString(14) },
                		new String[] { rset.getString(11), rset.getString(13), rset.getString(15) },
                        rset.getString(16), rset.getString(17), rset.getInt(18),
                        new String[] { rset.getString(19), rset.getString(20), rset.getString(21), rset.getString(22),
                					   rset.getString(23), rset.getString(24), rset.getString(25), rset.getString(26) },
                		new String[] { rset.getString(27), rset.getString(28), rset.getString(29), rset.getString(30),
                					   rset.getString(31), rset.getString(32), rset.getString(33), rset.getString(34) },
				   		new String[] { rset.getString(35), rset.getString(36), rset.getString(37), rset.getString(38),
   					   				   rset.getString(39), rset.getString(40), rset.getString(41), rset.getString(42) },
   					   	new int[] { rset.getInt(43), rset.getInt(44) }));
            return profilesList;
        } catch (SQLException e) {
            if (orderingClause.length() > 0) e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"read profiles"}));
        }
    }

    public List<KeyValuePair> readDevelopersList() throws SQLException {
        return readKeyValuePairList(DEV_LIST_QRY);
    }

    public List<KeyValuePair> readPublishersList() throws SQLException {
        return readKeyValuePairList(PUBL_LIST_QRY);
    }

    public List<KeyValuePair> readGenresList() throws SQLException {
        return readKeyValuePairList(GENRE_LIST_QRY);
    }

    public List<KeyValuePair> readYearsList() throws SQLException {
        return readKeyValuePairList(PUBLYEAR_LIST_QRY);
    }

    public List<KeyValuePair> readStatusList() throws SQLException {
        return readKeyValuePairList(STATUS_LIST_QRY);
    }

    public List<KeyValuePair> readCustomList(final int index) throws SQLException {
        return readKeyValuePairList(CUSTOM_LIST_QRY[index]);
    }

    public List<DosboxVersion> readDosboxVersionsList() throws SQLException {
        try (Statement stmt = con.createStatement(); ResultSet resultset = stmt.executeQuery(DBVERS_LIST_QRY);) {
        	List<DosboxVersion> dbversionsList = new ArrayList<DosboxVersion>();
            while (resultset.next())
                dbversionsList.add(new DosboxVersion(resultset.getInt(1), resultset.getString(2), resultset.getString(3), resultset.getString(4), 
                		resultset.getBoolean(5), resultset.getBoolean(6), resultset.getBoolean(7), resultset.getString(8), resultset.getString(9)));
            return dbversionsList;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"read dosboxversions"}));
        }
    }

    public List<Template> readTemplatesList() throws SQLException {
        try (Statement stmt = con.createStatement(); ResultSet resultset = stmt.executeQuery(TEMPL_LIST_QRY);) {
        	List<Template> templatesList = new ArrayList<Template>();
            while (resultset.next())
                templatesList.add(new Template(resultset.getInt(1), resultset.getString(2), resultset.getInt(3), resultset.getBoolean(4)));
            return templatesList;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"read templates"}));
        }
    }

    public List<Filter> readFiltersList() throws SQLException {
        try (Statement stmt = con.createStatement(); ResultSet resultset = stmt.executeQuery(FLTRS_LIST_QRY);) {
        	List<Filter> filtersList = new ArrayList<Filter>();
            while (resultset.next())
            	filtersList.add(new Filter(resultset.getInt(1), resultset.getString(2), resultset.getString(3)));
            return filtersList;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"read filters"}));
        }
    }

    public List<NativeCommand> readNativeCommandsList(final int gameId, final int templateId) throws SQLException {
    	List<NativeCommand> nativeCommandsList = new ArrayList<NativeCommand>();
    	if ((gameId != -1) || (templateId != -1)) {
        	try (PreparedStatement stmt = con.prepareStatement(gameId != -1 ? NTVCMD_LIST_QRY_GAME: NTVCMD_LIST_QRY_TEMPLATE)) {
        		stmt.setInt(1, gameId != -1 ? gameId: templateId);
        		try (ResultSet resultset = stmt.executeQuery()) {
	                while (resultset.next())
	                	nativeCommandsList.add(new NativeCommand(new File(resultset.getString(1)), resultset.getString(2), new File(resultset.getString(3)), resultset.getBoolean(4), resultset.getInt(5)));
		        }
        	} catch (SQLException e) {
	            e.printStackTrace();
	            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"read native commands"}));
	        }
        }
    	NativeCommand.insertDosboxCommand(nativeCommandsList);
        return nativeCommandsList;
    }

    public void saveNativeCommands(final List<NativeCommand> commands, final int gameId, final int templateId) throws SQLException {
    	removeNativeCommands(gameId, templateId);

    	for (int i = 0; i < commands.size(); i++) {
    		NativeCommand cmd = commands.get(i);
    		if (cmd.getCommand() != null) {
	            try (PreparedStatement stmt = con.prepareStatement(ADD_NATIVECOMMAND_QRY)) {
		            stmt.setString(1, cmd.getCommand().getPath());
		            stmt.setString(2, cmd.getParameters());
		            stmt.setString(3, cmd.getCwd().getPath());
		            stmt.setBoolean(4, cmd.isWaitFor());
		            stmt.setInt(5, i);
		            if (gameId != -1)
		            	stmt.setInt(6, gameId);
		            else
		            	stmt.setNull(6, java.sql.Types.INTEGER);
		            if (templateId != -1)
		            	stmt.setInt(7, templateId);
		            else
		            	stmt.setNull(7, java.sql.Types.INTEGER);
		            stmt.executeUpdate();
	            } catch (SQLException e) {
	                e.printStackTrace();
	                throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"save native commands"}));
	            }
    		}
    	}
    }

    public void removeNativeCommands(final int gameId, final int templateId) throws SQLException {
        if (gameId != -1) {
    		try (PreparedStatement stmt = con.prepareStatement(REMOVE_NTVCMD_QRY_GAME)) {
    			stmt.setInt(1, gameId);
    			stmt.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"remove native commands"}));
            }
    	} else if (templateId != -1) {
    		try (PreparedStatement stmt = con.prepareStatement(REMOVE_NTVCMD_QRY_TEMPLATE)) {
    			stmt.setInt(1, templateId);
    			stmt.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"remove native commands"}));
            }
    	}
    }

    public void removeProfile(final int gameId) throws SQLException {
        removeSomething(REMOVE_GAME_QRY, "profile", gameId);
    }

    public void removeDosboxVersion(final int dbversionId) throws SQLException {
        try {
            removeSomething(REMOVE_DBV_QRY, "dosboxversion", dbversionId);
        } catch (SQLException e) {
        	int amount = 0;
            StringBuffer usageList = new StringBuffer();
            try (PreparedStatement stmt = con.prepareStatement(DBV_USE_QRY)) {
                stmt.setInt(1, dbversionId);
                stmt.setInt(2, dbversionId);
                try (ResultSet resultset = stmt.executeQuery()) {
                    while (resultset.next()) {
                        if (amount++ < 10) {
                            if (amount > 1)
                                usageList.append(", ");
                            usageList.append(resultset.getString(1));
                        }
                    }
                }
            }
            throw new SQLException(Settings.getInstance().msg("general.error.profilesandtemplatesusingdbversion", new Object[] {StringRelatedUtils.toString(e), amount, usageList}));
        }
    }

    public void removeTemplate(final int templateId) throws SQLException {
        removeSomething(REMOVE_TEMPL_QRY, "template", templateId);
    }
    
    public void removeFilter(final int filterId) throws SQLException {
        removeSomething(REMOVE_FLTR_QRY, "filter", filterId);
    }

    public int addOrEditProfile(final String title, final String developer, final String publisher, final String genre,
            final String year, final String status, final String notes, final boolean favorite, final String[] setup, final String[] setupParams,
            final int devId, final int publId, final int genId, final int yrId, final int statId, final int dbversionId, 
            final String[] link, final String[] linkTitle, final String[] sCust, final int[] iCust, final int[] custId, final int profileId) throws SQLException {
        try (PreparedStatement pstmt = con.prepareStatement(profileId != -1 ? UPD_GAME_QRY: ADD_GAME_QRY)) {
            pstmt.setString(1, title);
            pstmt.setInt(2, devId == -1? addSomething(ADD_DEVELOPER_QRY, developer): devId);
            pstmt.setInt(3, publId == -1? addSomething(ADD_PUBLISHER_QRY, publisher): publId);
            pstmt.setInt(4, genId == -1? addSomething(ADD_GENRE_QRY, genre): genId);
            pstmt.setInt(5, yrId == -1? addSomething(ADD_YEAR_QRY, year): yrId);
            pstmt.setInt(6, statId == -1? addSomething(ADD_STATUS_QRY, status): statId);
            pstmt.setString(7, notes);
            pstmt.setBoolean(8, favorite);
            for (int i = 0; i < setup.length; i++) {
            	pstmt.setString(i * 2 + 9, setup[i]);
            	pstmt.setString(i * 2 + 10, setupParams[i]);
            }
            pstmt.setInt(15, dbversionId);
            for (int i = 0; i < link.length; i++)
                pstmt.setString(i + 16, link[i]);
            for (int i = 0; i < linkTitle.length; i++)
                pstmt.setString(i + 16 + link.length, linkTitle[i]);
            for (int i = 0; i < custId.length; i++)
                pstmt.setInt(i + 16 + link.length + linkTitle.length, custId[i] == -1? addSomething(ADD_CUSTOM_QRY[i], sCust[i]): custId[i]);
            for (int i = 0; i < SIMPLE_STRINGS; i++)
                pstmt.setString(i + 16 + link.length + linkTitle.length + custId.length, sCust[i + custId.length]);
            for (int i = 0; i < iCust.length; i++)
                pstmt.setInt(i + 16 + link.length + linkTitle.length + custId.length + SIMPLE_STRINGS, iCust[i]);
            if (profileId != -1)
            	pstmt.setInt(16 + link.length + linkTitle.length + custId.length + SIMPLE_STRINGS + iCust.length, profileId);
            pstmt.executeUpdate();
            return profileId != -1 ? profileId: identity();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"add/edit profile"}));
        }
    }

    public synchronized int addOrEditProfile(final String title, final String developer, final String publisher, final String genre,
            final String year, final String status, final String notes, final boolean favorite, final String[] setup, final String[] setupParams,
            final int dbversionId, final String[] link, final String[] linkTitle, final String[] sCust, final int[] iCust, final int profileId) throws SQLException {
    	int devId = KeyValuePair.findIdByValue(readDevelopersList(), developer);
		int publId = KeyValuePair.findIdByValue(readPublishersList(), publisher);
		int genId = KeyValuePair.findIdByValue(readGenresList(), genre);
		int yrId = KeyValuePair.findIdByValue(readYearsList(), year);
		int statId = KeyValuePair.findIdByValue(readStatusList(), status);
		int[] customIds = new int[COMPLEX_STRINGS];
		for (int i = 0; i < COMPLEX_STRINGS; i++)
			customIds[i] = KeyValuePair.findIdByValue(readCustomList(i), sCust[i]);
    	return addOrEditProfile(title, developer, publisher, genre, year, status, notes, favorite, setup, setupParams,
    			devId, publId, genId, yrId, statId, dbversionId, link, linkTitle, sCust, iCust, customIds, profileId);
    }

    public void updateProfileConf(final String confFile, final String captures, final int profileId) throws SQLException {
        try (PreparedStatement stmt = con.prepareStatement(UPD_GAME_CONF_QRY)) {
            stmt.setString(1, confFile);
            stmt.setString(2, captures);
            stmt.setInt(3, profileId);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"update profile conf"}));
        }
    }

    public void updateProfileSetupAndLinks(final String[] setup, final String[] links, final int profileId) throws SQLException {
        try (PreparedStatement stmt = con.prepareStatement(UPD_STPLNKS_QRY)) {
            for (int i = 0; i < setup.length; i++)
            	stmt.setString(i + 1, setup[i]);
            for (int i = 0; i < links.length; i++)
                stmt.setString(i + 4, links[i]);
            stmt.setInt(4 + links.length, profileId);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"update profile setup and links"}));
        }
    }

    public void toggleFavorite(final int profileId) throws SQLException {
        try (PreparedStatement stmt = con.prepareStatement(TOGGLE_FAV_QRY)) {
            stmt.setInt(1, profileId);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"toggle favorite"}));
        }
    }

    public int duplicateProfile(final int profileId) throws SQLException {
        try (PreparedStatement pstmt = con.prepareStatement(DUPL_GAME_QRY)) {
            pstmt.setInt(1, profileId);
            pstmt.executeUpdate();
            return identity();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"duplicate profile"}));
        }
    }

    public int duplicateTemplate(final int templateId) throws SQLException {
        try (PreparedStatement pstmt = con.prepareStatement(DUPL_TEMPLATE_QRY)) {
            pstmt.setInt(1, templateId);
            pstmt.executeUpdate();
            return identity();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"duplicate template"}));
        }
    }

    public int addOrEditDosboxVersion(final String title, final String path, final String conf, final boolean multiConfig, final boolean usingCurses,
    		final boolean defaultVersion, final String parameters, final String version, final int dbversionId) throws SQLException {
        try (Statement stmt = con.createStatement(); PreparedStatement pstmt = con.prepareStatement(dbversionId != -1 ? UPD_DBVERSION_QRY: ADD_DBVERSION_QRY)) {
        	if (defaultVersion)
        		stmt.executeUpdate(UPD_DBV_NODEFAULT);
            pstmt.setString(1, title);
            pstmt.setString(2, path);
            pstmt.setString(3, conf);
            pstmt.setBoolean(4, multiConfig);
            pstmt.setBoolean(5, usingCurses);
            pstmt.setBoolean(6, defaultVersion);
            pstmt.setString(7, parameters);
            pstmt.setString(8, version);
            if (dbversionId != -1)
            	pstmt.setInt(9, dbversionId);
            pstmt.executeUpdate();
            return dbversionId != -1 ? dbversionId: identity();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"add/edit dosboxversion"}));
        }
    }

    public int addOrEditTemplate(final String title, final int dbversionId, final boolean defaultVersion, final int templateId) throws SQLException {
    	try (Statement stmt = con.createStatement(); PreparedStatement pstmt = con.prepareStatement(templateId != -1 ? UPD_TEMPLATE_QRY: ADD_TEMPLATE_QRY)) {
        	if (defaultVersion)
        		stmt.executeUpdate(UPD_TEMPL_NODFLT);
        	pstmt.setString(1, title);
            pstmt.setInt(2, dbversionId);
            pstmt.setBoolean(3, defaultVersion);
            if (templateId != -1)
            	pstmt.setInt(4, templateId);
            pstmt.executeUpdate();
            return templateId != -1 ? templateId: identity();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"add/edit template"}));
        }
    }
    
    public int addOrEditFilter(final String title, final String filter, final int filterId) throws SQLException {
        try (PreparedStatement pstmt = con.prepareStatement(filterId != -1 ? UPD_FILTER_QRY: ADD_FILTER_QRY)) {
            pstmt.setString(1, title);
            pstmt.setString(2, filter);
            if (filterId != -1)
            	pstmt.setInt(3, filterId);
            pstmt.executeUpdate();
            return filterId != -1 ? filterId: identity();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"add/edit filter"}));
        }
    }

    public void startTransaction() {
    	try {
    		con.setAutoCommit(false);
    	} catch (SQLException e) {
    		e.printStackTrace();
    	}
    }

    public void rollbackTransaction() throws SQLException {
    	con.rollback();
    }

    public void commitTransaction() throws SQLException {
    	con.commit();
    }

    public void finishTransaction() {
    	try {
			con.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }

    public int cleanup() throws SQLException {
        try (Statement stmt = con.createStatement()) {
            for (String s: CLEANUP_QRY.split(";"))
                stmt.addBatch(s);
            int[] results = stmt.executeBatch();
            int result = 0;
            for (int uc: results)
                result += uc;
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"cleanup"}));
        }
    }
    
    public List<Profile> findInvalidProfiles() throws SQLException {
        try (Statement stmt = con.createStatement(); ResultSet rset = stmt.executeQuery(FIND_INVALID_PROFILES)) {
        	List<Profile> profilesList = new ArrayList<Profile>();
        	while (rset.next())
                profilesList.add(
                    new Profile(
                		rset.getInt(1), rset.getString(2), rset.getString(3), rset.getString(4),
                        rset.getString(5), rset.getString(6), rset.getString(7), rset.getString(8), rset.getBoolean(9),
                        new String[] { rset.getString(10), rset.getString(12), rset.getString(14) },
                        new String[] { rset.getString(11), rset.getString(13), rset.getString(15) },
                        rset.getString(16), rset.getString(17), rset.getInt(18),
                        new String[] { rset.getString(19), rset.getString(20), rset.getString(21), rset.getString(22),
                					   rset.getString(23), rset.getString(24), rset.getString(25), rset.getString(26) },
                		new String[] { rset.getString(27), rset.getString(28), rset.getString(29), rset.getString(30),
                					   rset.getString(31), rset.getString(32), rset.getString(33), rset.getString(34) },
				   		new String[] { rset.getString(35), rset.getString(36), rset.getString(37), rset.getString(38),
   					   				   rset.getString(39), rset.getString(40), rset.getString(41), rset.getString(42) },
   					   	new int[] { rset.getInt(43), rset.getInt(44) }));
            return profilesList;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException(Settings.getInstance().msg("database.error.query", new Object[] {"remove invalid profiles"}));
        }
    }
}
