package industries.doublehelix.furrynotemanager;

import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBTools {
    private static final String databaseUrlBase = "jdbc:derby:";
    private static final String databaseName = "FurryNoteManager";
    private static String databasePath = "";
    
    public static final int FOLDER_ID_INBOX = 0;
    public static final int FOLDER_ID_OUTBOX = 1;
    public static final int FOLDER_ID_FA_TRASH = 2;
    public static final int FOLDER_ID_FA_ARCHIVE = 3;
    
    public static final int SITE_ID_FURAFFINITY = 0;
    public static final int SITE_ID_SOFURRY = 1;
    public static final int SITE_ID_INKBUNNY = 2;
    public static final int SITE_ID_FURRYNETWORK = 3;
    
    private static DBTools instance = null;
    
    private Connection conn = null;
    
    public static DBTools getInstance() throws SQLException {
    	if (instance == null) {
    		instance = new DBTools();
    	}
		return instance;
	}
    
    public DBTools() throws SQLException {
		init();
	}

	/**
	 * this method germinates the DB if not yet done so
	 * @throws SQLException 
	 */
	private void init() throws SQLException {


		DatabaseMetaData dbm = getConnection().getMetaData();
		// check if "Users" table is there
		ResultSet tables = dbm.getTables(null, null, "USERS", null);
		if (tables.next()) {
		  tables.close();
		  return;
		}
		
		// make the tables
		try (Statement s = getConnection().createStatement()) {
			s.execute("CREATE TABLE SITES("
					+ "SITEID INTEGER NOT NULL CONSTRAINT SITES_PK PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 100, INCREMENT BY 1),"
					+ "NAME VARCHAR(255) NOT NULL"
					+ ")");
			s.execute("INSERT INTO SITES (SITEID, NAME) VALUES (" + SITE_ID_FURAFFINITY + ", 'FurAffinity')");
			s.execute("INSERT INTO SITES (SITEID, NAME) VALUES (" + SITE_ID_SOFURRY + ", 'SoFurry')");
			s.execute("INSERT INTO SITES (SITEID, NAME) VALUES (" + SITE_ID_INKBUNNY + ", 'InkBunny')");
			s.execute("INSERT INTO SITES (SITEID, NAME) VALUES (" + SITE_ID_FURRYNETWORK + ", 'FurryNetwork')");
			
			s.execute("CREATE TABLE USERS("
					+ "USERID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 100, INCREMENT BY 1),"
					+ "SITEID INTEGER NOT NULL CONSTRAINT USERS_SITE_FK REFERENCES SITES ON DELETE RESTRICT,"
					+ "NAME VARCHAR(255) NOT NULL,"
					+ "PASSWORD VARCHAR(255) NOT NULL,"
					+ "CONSTRAINT USERS_PK PRIMARY KEY(NAME, SITEID),"
					+ "CONSTRAINT USERS_UNIQUE UNIQUE (USERID)"
					+ ")");
			s.execute("CREATE TABLE FOLDERS("
					+ "FOLDERID INTEGER NOT NULL CONSTRAINT FOLDER_PK PRIMARY KEY,"
					+ "NAME VARCHAR(255) NOT NULL,"
					+ "SYSTEMFOLDER BOOLEAN DEFAULT FALSE NOT NULL"
					+ ")");
			// add system folders
			s.execute("INSERT INTO FOLDERS (FOLDERID, NAME, SYSTEMFOLDER) VALUES (" + FOLDER_ID_INBOX + ", 'Inbox', TRUE)");
			s.execute("INSERT INTO FOLDERS (FOLDERID, NAME, SYSTEMFOLDER) VALUES (" + FOLDER_ID_OUTBOX + ", 'Outbox', TRUE)");
			s.execute("INSERT INTO FOLDERS (FOLDERID, NAME, SYSTEMFOLDER) VALUES (" + FOLDER_ID_FA_TRASH + ", 'Archive', TRUE)");
			s.execute("INSERT INTO FOLDERS (FOLDERID, NAME, SYSTEMFOLDER) VALUES (" + FOLDER_ID_FA_ARCHIVE + ", 'Trash', TRUE)");
			
			s.execute("CREATE TABLE NOTES("
					+ "NOTEID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 100, INCREMENT BY 1),"
					+ "ORIGINALNOTEID INTEGER NOT NULL," // id on site
					+ "USERID INTEGER NOT NULL CONSTRAINT NOTES_USER_FK REFERENCES USERS(USERID) ON DELETE CASCADE,"
					+ "SENDER VARCHAR(255),"
					+ "RECEIVER VARCHAR(255),"
					+ "TITLE VARCHAR(255) NOT NULL,"
					+ "DATE TIMESTAMP NOT NULL,"
					+ "TEXT CLOB NOT NULL,"
					+ "RAWTEXT CLOB NOT NULL,"
					+ "PRIORITY INTEGER DEFAULT 0 NOT NULL CONSTRAINT PRIO_CONSTRAINT CHECK (PRIORITY IN (0, 1, 2, 3)),"
					+ "FOLDERID INTEGER NOT NULL CONSTRAINT NOTES_FOLDER_FK REFERENCES FOLDERS ON DELETE RESTRICT,"
					+ "UNREAD BOOLEAN NOT NULL DEFAULT FALSE,"
					+ "CONSTRAINT NOTES_PK PRIMARY KEY(ORIGINALNOTEID, USERID),"
					+ "CONSTRAINT NOTES_UNIQUE UNIQUE (NOTEID)"
					+ ")");
			
			s.execute("CREATE TABLE FILTER("
					+ "NAME VARCHAR(255) NOT NULL CONSTRAINT FILTER_PK PRIMARY KEY,"
					+ "SERIALIZEDFILTER BLOB NOT NULL"
					+ ")");
		}
	}
	
	/**
	 * Get a DB connection. Connection should be closed after use.
	 * @return the connection
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException {
		if (conn == null) {
			System.out.println(databaseUrlBase + databasePath + databaseName + ";create=true");
			conn = DriverManager.getConnection(databaseUrlBase + databasePath + databaseName + ";create=true");
		}
		return conn;
	}
	
	public void shutDown() {
		try {
			if (conn != null) conn.close();
			DriverManager.getConnection("jdbc:derby:;shutdown=true");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * @return the databasePath
	 */
	public static String getDatabasePath() {
		return databasePath;
	}

	/**
	 * @param databasePath the databasePath to set
	 */
	public static synchronized void setDatabasePath(String databasePath) {
		DBTools.databasePath = databasePath;
		if (!databasePath.endsWith(File.separator)) DBTools.databasePath += File.separator;
	}
	
//	/**
//	 * gets the result of the given query as a simple array of array (note, all data types are converted to string)
//	 * to save the hassle to close stuff and so on 
//	 * @param query
//	 * @return an array of array with the results of the query
//	 */
//	private ArrayList<ArrayList<String>> getSimpleQueryResult(String query) {
//		try {
//			return getSimpleQueryResult(query, getConnection());
//		} catch (SQLException e) {
//			e.printStackTrace();
//			return null;
//		}
//	}
//	
//	
//	/**
//	 * gets the result of the given query as a simple array of array (note, all data types are converted to string)
//	 * to save the hassle to close stuff and so on 
//	 * @param query
//	 * @param connection the connection to use
//	 * @return an array of array with the results of the query
//	 */
//	private ArrayList<ArrayList<String>> getSimpleQueryResult(String query, Connection connection) {
//		try (Statement s = connection.createStatement()) {
//			try (ResultSet rs = s.executeQuery(query)) {
//				ResultSetMetaData metaData = rs.getMetaData();
//				int columnCount = metaData.getColumnCount();
//				
//				ArrayList<ArrayList<String>> resultList = new ArrayList<>();
//				
//				while (rs.next()) {
//					ArrayList<String> row = new ArrayList<>();
//					for (int i = 1; i <= columnCount; i++) {
//						row.add(rs.getString(i));
//					}
//			        resultList.add(row);
//			    }
//				return resultList;
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//			return null;
//		}
//	}
}
