public class CommonState implements Serializable {

    public long rid;
    public long id;
    public boolean cf;
    public boolean df;
    public Date arsd;
    public Date ared;
    public long euid;
    public long esid;
    public long tid;    // tenantId

    public CommonState() {
        this.rid = -1;
        this.id = -1;
        this.cf = false;
        this.df = false;
        this.arsd = null;
        this.ared = null;
        this.esid = -1;
        this.euid = -1;
        this.tid = 1;
    }
} public class FdfSystem extends CommonState {

    public String name;
    public String description;
    public String sha256EncodedPassword;

    public FdfSystem() {
        super();

        name = "";
        description = "";
        sha256EncodedPassword = "";

    }
} public class FdfTenant extends CommonState {

    public String name;
    public String description;
    public String webURL;
    public Boolean isPrimary;

    public FdfTenant() {
        super();

        name = "";
        description = "";
        webURL = "";
        isPrimary = false;

    }


} public class SqlStatement {
    private List<String> select;
    private List<WhereClause> where;
    private List<String> groupBy;
    private List<String> orderBy;
    private int limit, offset;

    private SqlStatement() {
        select = new ArrayList<>();
        where = new ArrayList<>();
        groupBy = new ArrayList<>();
        orderBy = new ArrayList<>();
        limit = offset = 0;
    }
    private SqlStatement(SqlStatement split) {
        this();
        select.addAll(split.select);
        where.addAll(split.where);
        groupBy.addAll(split.groupBy);
        orderBy.addAll(split.orderBy);
        limit = split.limit;
        offset = split.offset;
    }

    public static SqlStatement build() {
        return new SqlStatement();
    }
    public SqlStatement split() { return new SqlStatement(this); }

    public SqlStatement select(String selectItem) {
        if(!selectItem.isEmpty()) {
            select.add(selectItem);
        }
        return this;
    }
    public SqlStatement select(List<String> selectList) {
        select.addAll(selectList.stream().filter(selectItem -> !selectItem.isEmpty()).collect(Collectors.toList()));
        return this;
    }

    //Commented out until valueDataType can be set this way.
    /*public SqlStatement where(String clause) {
        return where(new WhereClause(clause));
    }*/
    public SqlStatement where(WhereClause whereClause) {
        if(whereClause != null) {
            where.add(whereClause);
        }
        return this;
    }
    public SqlStatement where(List<WhereClause> whereStatement) {
        where.addAll(whereStatement.stream().filter(Objects::nonNull).collect(Collectors.toList()));
        return this;
    }

    public SqlStatement groupBy(String group) {
        if(!group.isEmpty()) {
            groupBy.add(group);
        }
        return this;
    }
    public SqlStatement groupBy(List<String> groupings) {
        groupBy.addAll(groupings.stream().filter(group -> !group.isEmpty()).collect(Collectors.toList()));
        return this;
    }

    public SqlStatement orderBy(String order) {
        if(!order.isEmpty()) {
            orderBy.add(order);
        }
        return this;
    }
    public SqlStatement orderBy(List<String> orderings) {
        orderBy.addAll(orderings.stream().filter(order -> !order.isEmpty()).collect(Collectors.toList()));
        return this;
    }

    public SqlStatement limit(int resultsPerPage, int currentPage) {
        offset = (currentPage - 1) * (limit = resultsPerPage);
        return this;
    }

    public <S extends CommonState> List<S> run(Class<S> entityState) {
        return FdfCommonServices.sqlStatementSelect(entityState, this);
    }

    public String getSelect() {
        StringBuilder sql = new StringBuilder("SELECT ");
        if(!select.isEmpty()) {
            for(int s=0; s<select.size(); s++) {
                if(s > 0) {
                    sql.append(", ");
                }
                sql.append(select.get(s));
            }
        } else {
            sql.append("*");
        }
        return sql.toString();
    }
    public String getWhere() {
        StringBuilder sql = new StringBuilder();
        for(WhereClause clause : where) {
            sql.append(" ");
            if(where.indexOf(clause) == 0) {
                sql.append("WHERE");
            } else {
                sql.append(clause.conditional.toString());
            }
            sql.append(" ");
            //Check to see if there are any open parenthesis to apply
            clause.groupings.stream().filter(grouping -> grouping.equals(WhereClause.GROUPINGS.OPEN_PARENTHESIS)).forEach(openParen -> sql.append("("));
            //Format clause by datatype
            if(clause.operator != WhereClause.Operators.UNARY) {
                sql.append(clause.name).append(" ").append(clause.getOperatorString()).append(" ");
                if(clause.value.equals(WhereClause.NULL) || Number.class.isAssignableFrom(clause.valueDataType)) {
                    sql.append(clause.value);
                }
                else if(clause.valueDataType == Boolean.class) {
                    sql.append(clause.value.toLowerCase());
                }
                else { //Includes String, Date, and UUID
                    sql.append("'").append(clause.value.replaceAll("'", "''")).append("'");
                }
            }
            //Check to see if there are any closing parenthesis to apply
            clause.groupings.stream().filter(grouping -> grouping.equals(WhereClause.GROUPINGS.CLOSE_PARENTHESIS)).forEach(closeParen -> sql.append(")"));
        }
        return sql.toString();
    }
    public String getGroupBy() {
        StringBuilder sql = new StringBuilder();
        groupBy.forEach(group -> {
            if(groupBy.indexOf(group) == 0) {
                sql.append(" GROUP BY");
            }
            else {
                sql.append(",");
            }
            sql.append(" ").append(group);
        });
        return sql.toString();
    }
    public String getOrderBy() {
        StringBuilder sql = new StringBuilder();
        orderBy.forEach(order -> {
            if(orderBy.indexOf(order) == 0) {
                sql.append(" ORDER BY");
            }
            else {
                sql.append(",");
            }
            sql.append(" ").append(order);
        });
        return sql.toString();
    }
    public String getLimit() {
        StringBuilder sql = new StringBuilder();
        if(limit > 0 && offset > -1) {
            sql.append(" LIMIT ").append(limit);
            if(offset > 0) {
                sql.append(" OFFSET ").append(offset);
            }
        }
        return sql.toString();
    }
    public int[] setForManualLimit() {
        int[] manual = {offset, limit};
        limit = offset = 0;
        return manual;
    }
} public class WhereClause {

    /**
     * field name
     */
    public String name = "";

    /**
     * Data type of primary value
     */
    public Class valueDataType;

    /**
     * Primary value to check against.
     */
    public String value = "";

    /**
     * Data type of secondary value
     */
    public Type value2DataType;

    /**
     * Used for Between opertator (ex. BETWEEN 'value1' AND 'value2')
     */
    public String value2 = null;

    /**
     * Constant value of NULL
     */
    public final static String NULL = "NULL";

    /**
     * Optional conditional used between predicates, not needed for single predicate.  If no conditional is
     * specified, AND is assumed.
     */
    public CONDITIONALS conditional;

    /**
     * Optional operator used to define what type of filtering is to be applied.  Uses the OPERATORS enumeration
     * for available options.  If not definded, EQUAL (=) is assumed.
     */
    public Operators operator;

    /**
     * Optional ist that contains groupings
     * Ex. you might add to Open Parenthesis to the list to represent
     * Where id = ? AND <strong>((</strong> ...
     */
    public List<GROUPINGS> groupings = new ArrayList<>();

    /**
     * ENUM of available Conditionals
     */
    public enum CONDITIONALS {
        AND, OR, NOT
    }

    /**
     * ENUM used to group clauses
     */
    public enum GROUPINGS {
        OPEN_PARENTHESIS, CLOSE_PARENTHESIS
    }

    public enum Operators {
        EQUAL, NOT_EQUAL, GREATER_THAN, LESS_THAN, GREATER_THAN_OR_EQUAL, LESS_THAN_OR_EQUAL, BETWEEN, LIKE, IN, IS, IS_NOT, UNARY
    }

    /**
     * Default constructor
     */
    public WhereClause() {
        conditional = CONDITIONALS.AND;
        operator = Operators.EQUAL;
    }
    /**
     * Optional constructor that takes the String representation of a WhereClause and parses it into a WhereClause.
     * However, there is a problem in the fact that there is no easy way to relay the value type. So for now, this bit
     * of code is commented out.
     *
     * Complex regex is in case the smart people who enter the clause don't understand how to be decent human beings
     * and instead just don't care about styling. This catches the three cases shown here, "AND)(df) (&lt;&gt;) true".
     * Will probably be needed to update for spacing in case someone thinks "AND df&lt;&gt;true" works, which, to their
     * credit, makes sense in this case. If they go "AND dfIS NOTtrue" then they're a hopeless case. If they enter
     * multiple clauses, such as "WHERE df &lt;&gt; true AND cf = true", then this only parses the "WHERE df &lt;&gt; true" due
     * to the fact that you are creating 1 WhereClause in the WhereClause constructor, not 17 thousand.
     */
    /*WhereClause(String where) {
        String[] clause = where.split("(( )?\\(|(\\))? (\\()?|\\)( )?) ");
        switch(clause[0].toUpperCase()) {
            case "NOT":
                conditional = CONDITIONALS.NOT;
                break;
            case "OR":
                conditional = CONDITIONALS.OR;
                break;
            case "AND":
                conditional = CONDITIONALS.AND;
                break;
            default:
                //If case "WHERE" or left out because it is the first clause and they didn't think they needed to add it
                conditional = CONDITIONALS.AND;
        }
        name = clause[1];
        switch(clause[2].toUpperCase()) {
            case "=":
                operator = Operators.EQUAL;
                break;
            case "<>":
                operator = Operators.NOT_EQUAL;
                break;
            case ">":
                operator = Operators.GREATER_THAN;
                break;
            case "<":
                operator = Operators.LESS_THAN;
                break;
            case ">=":
                operator = Operators.GREATER_THAN_OR_EQUAL;
                break;
            case "<=":
                operator = Operators.LESS_THAN_OR_EQUAL;
                break;
            case "IN":
                operator = Operators.IN;
                break;
            case "BETWEEN":
                operator = Operators.BETWEEN;
                break;
            case "LIKE":
                operator = Operators.LIKE;
                break;
            case "IS":
                if(clause[3].toUpperCase().equals("NOT")) {
                    operator = Operators.IS_NOT;
                    value = clause[4];
                    return;
                }
                operator = Operators.IS;
                break;
            default:
                //You know, in case they decide to go "WHERE df true" like the special people they would be...
                operator = Operators.EQUAL;
        }
        value = clause[3];
        if(clause[2].equalsIgnoreCase("BETWEEN")) {
            if(clause[4].equalsIgnoreCase("AND")) {
                value2 = clause[5];
            }
            else {
                value2 = clause[4];
            }
        }
    }*/

    public String getOperatorString() {
        switch (this.operator) {
            case EQUAL:
                return "=";
            case NOT_EQUAL:
                return "<>";
            case GREATER_THAN:
                return ">";
            case LESS_THAN:
                return "<";
            case GREATER_THAN_OR_EQUAL:
                return ">=";
            case LESS_THAN_OR_EQUAL:
                return "<=";
            case IN:
                return "IN";
            case BETWEEN:
                return "BETWEEN";
            case LIKE:
                return "LIKE";
            case IS:
                return "IS";
            case IS_NOT:
                return "IS NOT";
            default:
                return null;
        }
    }
} public class PostgreSqlConnection extends JdbcConnection {

    private static final PostgreSqlConnection INSTANCE = new PostgreSqlConnection();
    static Logger fdfLog = LoggerFactory.getLogger(PostgreSqlConnection.class);

    private PostgreSqlConnection() {

    }

    public static PostgreSqlConnection getInstance() {
        return INSTANCE;
    }

    @Override
    public Connection get4dfDbConnection() throws SQLException {
        return super.get4dfDbConnection();
    }

    @Override
    public Connection get4dfDbRootConnection() throws SQLException {
        return super.get4dfDbRootConnection();
    }

    /**
     * Force lower case for postres
     * @return
     * @throws SQLException
     */
    @Override
    protected Connection getPlainConnection() throws SQLException {
        fdfLog.debug("Establishing mysql connection with regular PostgreSQL credentials");
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            fdfLog.error("Database driver error!");
            fdfLog.error(e.getStackTrace().toString());
        }

        return DriverManager.getConnection(DatabaseUtil.returnDBConnectionString(),
                FdfSettings.DB_USER.toLowerCase(), FdfSettings.DB_PASSWORD);
    }

    /**
     * Force lower case for postres
     * @return
     * @throws SQLException
     */
    @Override
    protected Connection getNoDbPlainConnection() throws SQLException {
        fdfLog.debug("Establishing mysql connection with root PostgreSQL credentials");
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            fdfLog.error("Database driver error!");
            fdfLog.error(e.getStackTrace().toString());
        }

        return DriverManager.getConnection(DatabaseUtil.returnDBConnectionStringWithoutDatabase(),
                FdfSettings.DB_ROOT_USER.toLowerCase(), FdfSettings.DB_ROOT_PASSWORD);
    }

    @Override
    public void close4dfDbSession(Connection connection) throws SQLException {
        super.close4dfDbSession(connection);
    }

    @Override
    protected void initializeHakariDatasource() {
        HikariConfig config = new HikariConfig();
        config.setDataSourceClassName(FdfSettings.HIKARICP_DATASOURCE_CLASSNAME);
        config.setAutoCommit(FdfSettings.HIKARICP_AUTOCOMMIT);
        config.setConnectionTimeout(FdfSettings.HIKARICP_CONNECTION_TIMEOUT_MS);
        config.setIdleTimeout(FdfSettings.HIKARICP_IDLE_TIMEOUT_MS);
        config.setMaxLifetime(FdfSettings.HIKARICP_MAX_LIFETIME_MS);
        config.setConnectionTestQuery(FdfSettings.HIKARICP_CONNECTION_TEST_QUERY);
        config.setMaximumPoolSize(FdfSettings.HIKARICP_MAX_POOL_SIZE);
        config.setMinimumIdle(FdfSettings.HIKARICP_MIN_IDLE_MS);
        /* TODO: Caused by: java.lang.ClassNotFoundException: com.codahale.metrics.MetricRegistry
        if(FdfSettings.HIKARICP_METRIC_REG != null) {
            config.setMetricRegistry(FdfSettings.HIKARICP_METRIC_REG);
        }
        */
        /* TODO: Caused by: java.lang.ClassNotFoundException: com.codahale.metrics.MetricRegistry
        config.setHealthCheckRegistry(FdfSettings.HIKARICP_HEALTH_CHECK_REG);
        */
        config.setPoolName(FdfSettings.HIKARICP_POOL_NAME);
        config.setInitializationFailFast(FdfSettings.HIKARICP_FAIL_FAST);
        config.setIsolateInternalQueries(FdfSettings.HIKARICP_ISOLATE_INTERNAL_QUERIES);
        config.setAllowPoolSuspension(FdfSettings.HIKARICP_ALLOW_POOL_SUSPENSION);
        config.setReadOnly(FdfSettings.HIKARICP_READ_ONLY);
        config.setRegisterMbeans(FdfSettings.HIKARICP_REGISTER_MBEANS);
        /* TODO: IllegalArgumentException: Invalid transaction isolation value: driver default
        config.setCatalog(FdfSettings.HIKARICP_CATALOG);
         */
        config.setConnectionInitSql(FdfSettings.HIKARICP_CONNECTION_INIT_SQL);
        /* TODO: Caused by: java.lang.ClassNotFoundException: com.codahale.metrics.MetricRegistry
        config.setDriverClassName(FdfSettings.HIKARICP_DRIVER_CLASS_NAME);
        */
        /* TODO: IllegalArgumentException: Invalid transaction isolation value: driver default
        config.setTransactionIsolation(FdfSettings.HIKARICP_TRANSATION_ISOLATION);
         */
        config.setValidationTimeout(FdfSettings.HIKARICP_VALIDATION_TIMEOUT);
        config.setLeakDetectionThreshold(FdfSettings.HIKARICP_LEAK_DETECTION_THRESHOLD);
        config.setDataSourceClassName(FdfSettings.HIKARICP_DATA_SOURCE);
        config.setThreadFactory(FdfSettings.HIKARICP_THREAD_FACTORY);

        config.setJdbcUrl(DatabaseUtil.returnDBConnectionString());
        config.setUsername(FdfSettings.DB_USER.toLowerCase());
        config.setPassword(FdfSettings.DB_PASSWORD.toLowerCase());

        hds = new HikariDataSource(config);
    }
} public interface CorePersistenceImpl {
    void checkDatabase() throws SQLException;
    void checkTables() throws SQLException;
    void checkFields() throws SQLException;
    void checkDefaultEntries() throws SQLException;

    <S extends CommonState> List<S> selectQuery(Class c, SqlStatement sqlStatement);
    <S> Long insert(Class<S> entityState, S state);
    <S> void update(Class<S> c, S state);
} public class FdfSystemServices extends FdfCommonServices {

    public FdfEntity<FdfSystem> saveSystem(FdfSystem systemState) {
        return save(FdfSystem.class, systemState);
    }

    public FdfEntity<FdfSystem> deleteSystem(long userId, long tenantId, long callingUserId, long callingSystemId) {
        return setDeleteFlag(FdfSystem.class, userId, callingUserId, callingSystemId, tenantId);
    }

    public FdfEntity<FdfSystem> unDeleteSystem(long userId, long tenantId, long callingUserId, long callingSystemId) {
        return removeDeleteFlag(FdfSystem.class, userId, callingUserId, callingSystemId, tenantId);
    }

    public List<FdfSystem> getAllSystems() {
        return getAllSystems(1);
    }

    public List<FdfSystem> getAllSystems(long tenantId) {
        List<FdfSystem> currentSystems = new ArrayList<>();

        for(FdfEntity<FdfSystem> system: getAllSystemsWithHistory(tenantId)) {
            if(system != null && system.current != null) {
                currentSystems.add(system.current);
            }
        }

        return currentSystems;
    }

    public List<FdfEntity<FdfSystem>> getAllSystemsWithHistory() {
        return getAllSystemsWithHistory(1);
    }

    public List<FdfEntity<FdfSystem>> getAllSystemsWithHistory(long tenantId) {
        return getAll(FdfSystem.class, tenantId);
    }

    public FdfSystem getSystemById(long systemId) {
        return (systemId > 0 ? getEntityCurrentById(FdfSystem.class, systemId) : null);
    }
    public FdfEntity<FdfSystem> getSystemByIdWithHistory(long systemId) {
        return (systemId > 0 ? getEntityById(FdfSystem.class, systemId) : new FdfEntity<>());
    }

    public FdfSystem getDefaultSystem() {
        return getDefaultSystemWithHistory().current;
    }

    public FdfEntity<FdfSystem> getDefaultSystemWithHistory() {
        // build the where statement for the query
        List<WhereClause> whereStatement = new ArrayList<>();

        // check that deleted records are not returned
        WhereClause whereDf = new WhereClause();
        whereDf.name = "df";
        whereDf.operator = WhereClause.Operators.IS_NOT;
        whereDf.value = "true";
        whereDf.valueDataType = Boolean.class;

        // add the id check
        WhereClause whereId = new WhereClause();
        whereId.conditional = WhereClause.CONDITIONALS.AND;
        whereId.name = "id";
        whereId.operator = WhereClause.Operators.EQUAL;
        whereId.value = "1";
        whereId.valueDataType = Long.class;

        // build a List of entities
        return manageReturnedEntity(SqlStatement.build().where(whereDf).where(whereId).run(FdfSystem.class));
    }

    public FdfSystem getTestSystem() {
        return getTestSystemWithHistory().current;
    }

    public FdfEntity<FdfSystem> getTestSystemWithHistory() {
        // build the where statement for the query
        List<WhereClause> whereStatement = new ArrayList<>();

        // check that deleted records are not returned
        WhereClause whereDf = new WhereClause();
        whereDf.name = "df";
        whereDf.operator = WhereClause.Operators.IS_NOT;
        whereDf.value = "true";
        whereDf.valueDataType = Boolean.class;

        // add the id check
        WhereClause whereId = new WhereClause();
        whereId.conditional = WhereClause.CONDITIONALS.AND;
        whereId.name = "id";
        whereId.operator = WhereClause.Operators.EQUAL;
        whereId.value = "2";
        whereId.valueDataType = Long.class;

        // build a List of entities
        return manageReturnedEntity(SqlStatement.build().where(whereDf).where(whereId).run(FdfSystem.class));
    }

    public List<FdfSystem> getSystemsByName(String name) {
        List<FdfSystem> currentSystems = new ArrayList<>();

        for(FdfEntity<FdfSystem> system: getSystemsByNameWithHistory(name)) {
            if(system != null && system.current != null) {
                currentSystems.add(system.current);
            }
        }

        return currentSystems;
    }

    public List<FdfEntity<FdfSystem>> getSystemsByNameWithHistory(String name) {
        // build the where statement for the query
        List<WhereClause> whereStatement = new ArrayList<>();

        // check that deleted records are not returned
        WhereClause whereDf = new WhereClause();
        whereDf.name = "df";
        whereDf.operator = WhereClause.Operators.IS_NOT;
        whereDf.value = "true";
        whereDf.valueDataType = Boolean.class;

        // add the id check
        WhereClause whereId = new WhereClause();
        whereId.conditional = WhereClause.CONDITIONALS.AND;
        whereId.name = "name";
        whereId.operator = WhereClause.Operators.EQUAL;
        whereId.value = name;
        whereId.valueDataType = String.class;

        // build a List of entities
        return manageReturnedEntities(SqlStatement.build().where(whereDf).where(whereId).run(FdfSystem.class));
    }

    /**
     * Returns true if the passed SHA-256 hashed password matched the one saved for the tenant.
     * @param clearTextPassword Clear text password to be hashed
     * @return String representing the hashed password
     */
    public String hashPassword(String clearTextPassword) {
        StringBuffer sb = new StringBuffer();
        try {
            MessageDigest md = MessageDigest.getInstance("SHA-256");
            md.update(clearTextPassword.getBytes("UTF-8"));
            byte[] digest = md.digest();

            for(byte b : digest) {
                sb.append(String.format("%02x", b));
            }

        } catch (NoSuchAlgorithmException e) {
            e.printStackTrace();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return sb.toString();
    }

    /**
     * Checks the passed system Id and SHA-256 hashed password against the system record, returns a true
     * if the credentials are correct and false otherwise.
     *
     * @param systemId System ID to check authentication for
     * @param sha256EncryptedPassword Tenant password (must be SHA-256 hashed) to check authentication for
     * @return True if authentication attempt is successful, false otherwise.
     */
    public Boolean authenticateSystem(long systemId, String sha256EncryptedPassword) {
        //compare the password hashes
        FdfEntity<FdfSystem> system = getEntityById(FdfSystem.class, systemId);
        return Boolean.valueOf(system != null && system.current != null && system.current.sha256EncodedPassword.equals(sha256EncryptedPassword));
    }

} public class FdfTenantServices extends FdfCommonServices {

    public List<FdfTenant> getAllTenants() {
        List<FdfTenant> currentTenants = new ArrayList<>();

        for(FdfEntity<FdfTenant> tenant: getAllTenantsWithHistory()) {
            if(tenant != null && tenant.current != null) {
                currentTenants.add(tenant.current);
            }
        }

        return currentTenants;
    }

    public FdfEntity<FdfTenant> deleteTenant(long userId, long tenantId, long callingUserId, long callingSystemId) {
        return setDeleteFlag(FdfTenant.class, userId, callingUserId, callingSystemId, tenantId);
    }

    public FdfEntity<FdfTenant> unDeleteTenant(long userId, long tenantId, long callingUserId, long callingSystemId) {
        return removeDeleteFlag(FdfTenant.class, userId, callingUserId, callingSystemId, tenantId);

    }

    public List<FdfEntity<FdfTenant>> getAllTenantsWithHistory() {

        return this.getAll(FdfTenant.class);
    }

    public FdfTenant getTenantById(long tenantId) {
        return getTenantByIdWithHistory(tenantId).current;
    }

    public FdfEntity<FdfTenant> getTenantByIdWithHistory(long tenantId) {
        return (tenantId > 0 ? getEntityById(FdfTenant.class, tenantId) : new FdfEntity<>());
    }

    public FdfTenant getDefaultTenant() {
        return getDefaultTenantWithHistory().current;
    }

    public FdfEntity<FdfTenant> getDefaultTenantWithHistory() {
        // check that deleted records are not returned
        WhereClause whereDf = new WhereClause();
        whereDf.name = "df";
        whereDf.operator = WhereClause.Operators.IS_NOT;
        whereDf.value = "true";
        whereDf.valueDataType = Boolean.class;

        // add the id check
        WhereClause whereId = new WhereClause();
        whereId.conditional = WhereClause.CONDITIONALS.AND;
        whereId.name = "id";
        whereId.operator = WhereClause.Operators.EQUAL;
        whereId.value = "1";
        whereId.valueDataType = Long.class;

        return manageReturnedEntity(SqlStatement.build().where(whereDf).where(whereId).run(FdfTenant.class));
    }
    
    public FdfTenant getTenantByName(String tenantName) {
        return getTenantByNameWithHistory(tenantName).current;
    }

    public FdfEntity<FdfTenant> getTenantByNameWithHistory(String tenantName) {
        // check that deleted records are not returned
        WhereClause whereDf = new WhereClause();
        whereDf.name = "df";
        whereDf.operator = WhereClause.Operators.IS_NOT;
        whereDf.value = "true";
        whereDf.valueDataType = Integer.class;

        // check to find results that match the name like condition
        WhereClause nameLike = new WhereClause();
        nameLike.name = "name";
        nameLike.operator = WhereClause.Operators.LIKE;
        nameLike.value = "%" + tenantName + "%";
        nameLike.valueDataType = String.class;

        return manageReturnedEntity(SqlStatement.build().where(nameLike).run(FdfTenant.class));
    }

    /**
     * Save FdfTenant
     * Save FdfTenant, checks to see if there is already a tenant with the same name as names are unique. If the name
     * already exists, it is treated as an update, not a insert.
     * @param tenant to save
     * @return tenant saved with id
     */
    public FdfEntity<FdfTenant> saveTenant(FdfTenant tenant) {
        FdfEntity<FdfTenant> returnEntity = null;
        if(tenant != null) {
            // check to see if there is a user with the passed users id and or username
            FdfTenant existingTenant = null;
            if(tenant.id > -1) {
                existingTenant = this.getTenantById(tenant.id);
            }

            if(existingTenant != null) {
                tenant.id = existingTenant.id;
                tenant.isPrimary = existingTenant.isPrimary;
            }
            returnEntity = this.save(FdfTenant.class, tenant);
        }
        return returnEntity;
    }

} public class FdfSettings {

    private static final FdfSettings INSTANCE = new FdfSettings();

    static Logger fdfLog = LoggerFactory.getLogger(FdfSettings.class);

    public List<Class> modelClasses = new ArrayList<>();
    public static DatabaseUtil.DatabaseType PERSISTENCE = DatabaseUtil.DatabaseType.HSQL;

    public static DatabaseUtil.DatabaseProtocol DB_PROTOCOL = DatabaseUtil.DatabaseProtocol.JDBC_HSQL;
    public static String DB_HOST = "localhost";
    public static String DB_NAME = "4dfapplicationdb";
    public static Integer DB_PORT = 9001;
    public static DatabaseUtil.DatabaseEncoding DB_ENCODING = DatabaseUtil.DatabaseEncoding.UTF8;

    public static Boolean USE_SSL = false;

    // default general user information
    public static String DB_USER = "SA";
    public static String DB_PASSWORD = "";

    // Example database specific with non-root user for general db access.
    //public static String DB_USER = "fdfUser";
    //public static String DB_PASSWORD = "fdfUserPassword";

    // Root user information
    public static Boolean USE_DB_ROOT = true;
    public static String DB_ROOT_USER = "SA";
    public static String DB_ROOT_PASSWORD = "";

    // If set to true HSQL db will be written to a file to persist, if false, it will be an in-memory db only.
    public static Boolean HSQL_DB_FILE = true;
    //HyperSQL database file location
    public static String HQSL_DB_FILE_LOCATION = "hsql/";

    public static String DEFAULT_TENANT_NAME = "Default FdfTenant";
    public static String DEFAULT_TENANT_DESRIPTION = "Default FdfTenant is created by 4dflib, if you do not intent to use "
            + "built in multi-tenancy or only have one FdfTenant, all data is member of this tenant by "
            + "default";
    public static String DEFAULT_TENANT_WEBSITE = "http://www.4dflib.com";
    public static Boolean DEFAULT_TENANT_IS_PRIMARY = true;

    public static String DEFAULT_SYSTEM_NAME = "Default FdfSystem";
    public static String DEFAULT_SYSTEM_DESCRIPTION = "Default system represents the actual application and not"
            + " a registered external system.";
    public static String DEFAULT_SYSTEM_PASSWORD = "4DfPassword";

    public static String TEST_SYSTEM_NAME = "Default Test System";
    public static String TEST_SYSTEM_DESCRIPTION = "Default test system for use connecting to the system for testing";
    public static String TEST_SYSTEM_PASSWORD = "testSystemPassword";

    /**
     * HikariCP settings
     * See: https://github.com/brettwooldridge/HikariCP/wiki/Configuration
     * HikariCP uses milliseconds for all time values
     */

    /**
     * Turn on HIKARICP? If false use regular JDBC
     */
    public static Boolean USE_HIKARICP = false;

    /**
     * This is the name of the DataSource class provided by the JDBC driver Consult the documentation for your specific
     * JDBC driver to get this class name see https://github.com/brettwooldridge/HikariCP#popular-datasource-class-names
     * Note: XA data sources are not supported. XA requires a real transaction manager like bitronix. Note that you do
     * not need this property if you are using jdbcUrl for "old-school" DriverManager-based JDBC driver configuration.
     * Default: none
     */
    public static String HIKARICP_DATASOURCE_CLASSNAME = "";

    /**
     * This property controls the default auto-commit behavior of connections returned from the pool. It is a boolean
     * value. Default: true
     */
    public static Boolean HIKARICP_AUTOCOMMIT = true;

    /**
     * This property controls the maximum number of milliseconds that a client (that's you) will wait for a connection
     * from the pool.
     */

    public static Integer HIKARICP_CONNECTION_TIMEOUT_MS = 30000;

    /**
     * This property controls the maximum amount of time that a connection is allowed to sit idle in the pool. This
     * setting only applies when minimumIdle is defined to be less than maximumPoolSize
     */
    public static Integer HIKARICP_IDLE_TIMEOUT_MS = 60000;

    /**
     * This property controls the maximum lifetime of a connection in the pool. When a connection reaches this timeout
     * it will be retired from the pool
     */
    public static Integer HIKARICP_MAX_LIFETIME_MS = 287400;

    /**
     * This is for "legacy" databases that do not support the JDBC4 Connection.isValid() API HikariCP will log an error
     * if your driver is not JDBC4 compliant to let you know. Default: none
     */
    public static String HIKARICP_CONNECTION_TEST_QUERY = "";

    /**
     * This property controls the maximum size that the pool is allowed to reach, including both idle and in-use
     * connections. Basically this value will determine the maximum number of actual connections to the database
     * backend. A reasonable value for this is best determined by your execution environment. When the pool reaches
     * this size, and no idle connections are available, calls to getConnection() will block for up to connectionTimeout
     * milliseconds before timing out. Default: 10
     */
    public static Integer HIKARICP_MAX_POOL_SIZE = 10;

    /**
     * This property controls the minimum number of idle connections that HikariCP tries to maintain in the pool.
     * Default: same as maximumPoolSize
     */
    public static Integer HIKARICP_MIN_IDLE_MS = HIKARICP_MAX_POOL_SIZE;

    /**
     * This property is only available via programmatic configuration or IoC container. This property allows you to
     * specify an instance of a Codahale/Dropwizard MetricRegistry to be used by the pool to record various metrics.
     * See the Metrics wiki page for details. Default: none
     * TODO: Caused by: java.lang.ClassNotFoundException: com.codahale.metrics.MetricRegistry
     */
    //public static String HIKARICP_METRIC_REG = "";

    /**
     * This property is only available via programmatic configuration or IoC container. This property allows you to
     * specify an instance of a Codahale/Dropwizard HealthCheckRegistry to be used by the pool to report current
     * health information. See the Health Checks wiki page for details. Default: none
     * TODO: Caused by: java.lang.ClassNotFoundException: com.codahale.metrics.MetricRegistry
     */
    //public static String HIKARICP_HEALTH_CHECK_REG = "";

    /**
     * This property represents a user-defined name for the connection pool and appears mainly in logging and JMX
     * management consoles to identify pools and pool configurations. Default: auto-generated
     */

    public static String HIKARICP_POOL_NAME = "auto-generated";

    /**
     * This property controls whether the pool will "fail fast" if the pool cannot be seeded with initial connections
     * successfully. If you want your application to start even when the database is down/unavailable, set this
     * property to false. Default: true
     */
    public static Boolean HIKARICP_FAIL_FAST = true;

    /**
     * This property determines whether HikariCP isolates internal pool queries, such as the connection alive test,
     * in their own transaction. Since these are typically read-only queries, it is rarely necessary to encapsulate
     * them in their own transaction. This property only applies if autoCommit is disabled. Default: false
     */
    public static Boolean HIKARICP_ISOLATE_INTERNAL_QUERIES = false;

    /**
     * This property controls whether the pool can be suspended and resumed through JMX. This is useful for certain
     * failover automation scenarios. When the pool is suspended, calls to getConnection() will not timeout and will
     * be held until the pool is resumed. Default: false
     */
    public static Boolean HIKARICP_ALLOW_POOL_SUSPENSION = false;

    /**
     * This property controls whether Connections obtained from the pool are in read-only mode by default. Note some
     * databases do not support the concept of read-only mode, while others provide query optimizations when the
     * Connection is set to read-only. Whether you need this property or not will depend largely on your application
     * and database. Default: false
     */
    public static Boolean HIKARICP_READ_ONLY = false;

    /**
     * This property controls whether or not JMX Management Beans ("MBeans") are registered or not. Default: false
     */
    public static Boolean HIKARICP_REGISTER_MBEANS = false;

    /**
     * This property sets the default catalog for databases that support the concept of catalogs. If this property
     * is not specified, the default catalog defined by the JDBC driver is used. Default: driver default
     * TODO: IllegalArgumentException: Invalid transaction isolation value: driver default
     */
    //public static String HIKARICP_CATALOG = "driver default";

    /**
     * This property sets a SQL statement that will be executed after every new connection creation before adding it
     * to the pool. If this SQL is not valid or throws an exception, it will be treated as a connection failure and
     * the standard retry logic will be followed. Default: none
     */
    public static String HIKARICP_CONNECTION_INIT_SQL = "";

    /**
     * HikariCP will attempt to resolve a driver through the DriverManager based solely on the jdbcUrl, but for some
     * older drivers the driverClassName must also be specified. Omit this property unless you get an obvious error
     * message indicating that the driver was not found. Default: none
     * TODO: Caused by: java.lang.ClassNotFoundException: com.codahale.metrics.MetricRegistry
     */
    //public static String HIKARICP_DRIVER_CLASS_NAME = "";

    /**
     * This property controls the default transaction isolation level of connections returned from the pool. If this
     * property is not specified, the default transaction isolation level defined by the JDBC driver is used. Only use
     * this property if you have specific isolation requirements that are common for all queries. The value of this
     * property is the constant name from the Connection class such as TRANSACTION_READ_COMMITTED,
     * TRANSACTION_REPEATABLE_READ, etc. Default: driver default
     * TODO: IllegalArgumentException: Invalid transaction isolation value: driver default
     */
    //public static String HIKARICP_TRANSATION_ISOLATION = "driver default";

    /**
     * This property controls the maximum amount of time that a connection will be tested for aliveness. This value
     * must be less than the connectionTimeout. Lowest acceptable validation timeout is 250 ms. Default: 5000
     */
    public static Integer HIKARICP_VALIDATION_TIMEOUT = 5000;

    /**
     * This property controls the amount of time that a connection can be out of the pool before a message is logged
     * indicating a possible connection leak. A value of 0 means leak detection is disabled. Lowest acceptable value
     * for enabling leak detection is 2000 (2 seconds). Default: 0
     */
    public static Integer HIKARICP_LEAK_DETECTION_THRESHOLD = 2000;

    /**
     * This property is only available via programmatic configuration or IoC container. This property allows you to
     * directly set the instance of the DataSource to be wrapped by the pool, rather than having HikariCP construct
     * it via reflection. This can be useful in some dependency injection frameworks. When this property is specified,
     * the dataSourceClassName property and all DataSource-specific properties will be ignored. Default: none
     */
    public static String HIKARICP_DATA_SOURCE = "";

    /**
     * This property is only available via programmatic configuration or IoC container. This property allows you to
     * set the instance of the java.util.concurrent.ThreadFactory that will be used for creating all threads used by
     * the pool. It is needed in some restricted execution environments where threads can only be created through a
     * ThreadFactory provided by the application container. Default: none
     */
    public static ThreadFactory HIKARICP_THREAD_FACTORY = null;



    private FdfSettings() {
    }

    public static FdfSettings getInstance() {
        return INSTANCE;
    }
} public class FdfUtil {

    private static final String TYPE_NAME_PREFIX = "class ";

    public static String getClassName(Class className) {

        String classString = className.toString();

        if (classString.startsWith(TYPE_NAME_PREFIX)) {
            return classString.substring(TYPE_NAME_PREFIX.length());
        }

        return classString;
    }

    public static String getClassName(String className) {

        if (className.startsWith(TYPE_NAME_PREFIX)) {
            return className.substring(TYPE_NAME_PREFIX.length());
        }

        return className;
    }

    public static Class getClassByFullyQualifiedName(String className) {
        try {
            return Class.forName(className);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }
}
import com.fdflib.annotation.FdfIgnore;
import com.fdflib.model.state.CommonState;
import com.fdflib.model.state.FdfSystem;
import com.fdflib.model.state.FdfTenant;
import com.fdflib.model.util.SqlStatement;
import com.fdflib.model.util.WhereClause;
import com.fdflib.persistence.database.PostgreSqlConnection;
import com.fdflib.persistence.impl.CorePersistenceImpl;
import com.fdflib.service.FdfSystemServices;
import com.fdflib.service.FdfTenantServices;
import com.fdflib.util.FdfSettings;
import com.fdflib.util.FdfUtil;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.Date;
                            } else {
                                fdfLog.info("No table created for model object {} class had no valid data members", c.getSimpleName().toLowerCase());
                            }
                        }
                    }
                } catch (SQLException sqlException) {

                    // some other error
                    fdfLog.warn("Error occurred checking or creating a table:::");
                    fdfLog.warn("SQL error \nCode: {},\nState: {}\nMessage" +
                                    ": {}\n", sqlException.getErrorCode(), sqlException.getSQLState(),
                            sqlException.getMessage());

                } catch (Exception ex) {
                    ex.printStackTrace();
                } finally {
                    if (rs != null) {
                        rs.close();
                    }
                    if (stmt != null) {
                        stmt.close();
                    }
                    if(conn != null) {
                        PostgreSqlConnection.getInstance().close4dfDbSession(conn);
                    }
                }
            }

        }
    }

    public void checkFields() throws SQLException {
        // get the 4df data model
        List<Class> classList = FdfSettings.getInstance().modelClasses;

        // build the tables for the model objects
        for(Class c: classList) {

            // determine the number of fields
            int numberOfFields = 0;
            for (Field field : c.getFields()) {

                // check to see if the class has an @fdfIgonre
                if (!field.isAnnotationPresent(FdfIgnore.class)) {
                    numberOfFields++;
                }
            }

            if(numberOfFields > 0) {

                // check to see if the class has an @fdfIgonre
                if(!c.isAnnotationPresent(FdfIgnore.class)) {

                    Connection conn = null;
                    Statement stmt = null;
                    ResultSet rs = null;

                    try {
                        conn = PostgreSqlConnection.getInstance().get4dfDbConnection();
                        stmt = conn.createStatement();

                        for (Field field : c.getFields()) {

                            // check to see if the class has an @fdfIgonre
                            if(!field.isAnnotationPresent(FdfIgnore.class)) {

                                // query for the field in the database
                                // check to see if the table already exists
                                String fieldTest = "select * from information_schema.columns where table_catalog= '"
                                        + FdfSettings.DB_NAME.toLowerCase() + "' and table_name= '"
                                        + c.getSimpleName().toLowerCase() + "' and column_name= '"
                                        + field.getName().toLowerCase() + "';";

                                if (stmt != null) {
                                    rs = stmt.executeQuery(fieldTest);
                                }

                                if (rs != null) {
                                    if (!rs.next()) {
                                        // the field did not exist,
                                        String alterSql = "alter table " + "\"" + c.getSimpleName().toLowerCase() + "\"" + " add column "
                                                + this.getFieldNameAndDataType(field) + ";";

                                        fdfLog.info("Add field sql {} : {}", c.getSimpleName().toLowerCase(), alterSql);

                                        if (stmt != null) {
                                            stmt.executeUpdate(alterSql);
                                        }
                                    }
                                }
                            }
                        }
                    } catch (SQLException sqlException) {

                        // some other error
                        fdfLog.warn("Error occurred checking or creating a field:::");
                        fdfLog.warn("SQL error \nCode: {},\nState: {}\nMessage" +
                                        ": {}\n", sqlException.getErrorCode(), sqlException.getSQLState(),
                                sqlException.getMessage());

                    } catch (Exception ex) {
                        ex.printStackTrace();
                    } finally {
                        if (rs != null) {
                            rs.close();
                        }
                        if (stmt != null) {
                            stmt.close();
                        }
                        if(conn != null) {
                            PostgreSqlConnection.getInstance().close4dfDbSession(conn);
                        }
                    }
                }
            }
        }
    }

    public void checkDefaultEntries() throws SQLException {
        // check to see if the default entry exists for FdfSystem
        FdfSystemServices ss = new FdfSystemServices();