package fr.ifremer.adagio.synchro.meta;

/*
 * #%L
 * Tutti :: Persistence
 * $Id: ReferentialSynchroTableMetadata.java 1573 2014-02-04 16:41:40Z tchemit $
 * $HeadURL: http://svn.forge.codelutin.com/svn/tutti/trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroTableMetadata.java $
 * %%
 * Copyright (C) 2012 - 2014 Ifremer
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 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 Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 * #L%
 */

import static org.nuiton.i18n.I18n.t;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import oracle.sql.TIMESTAMP;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.dialect.Dialect;
import org.hibernate.internal.util.StringHelper;
import org.hibernate.mapping.ForeignKey;
import org.hibernate.tool.hbm2ddl.ColumnMetadata;
import org.hibernate.tool.hbm2ddl.ForeignKeyMetadata;
import org.hibernate.tool.hbm2ddl.IndexMetadata;
import org.hibernate.tool.hbm2ddl.TableMetadata;

import com.google.common.base.Preconditions;
import com.google.common.base.Predicate;
import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;

import fr.ifremer.adagio.synchro.SynchroTechnicalException;
import fr.ifremer.adagio.synchro.config.SynchroConfiguration;
import fr.ifremer.adagio.synchro.dao.DaoUtils;
import fr.ifremer.adagio.synchro.intercept.SynchroInterceptor;
import fr.ifremer.adagio.synchro.service.SynchroContext;

/**
 * Overrides of the {@link TableMetadata} with some improvements:
 * <ul>
 * <li>Obtains number of columns via {@link #getColumnsCount()}</li>
 * <li>Obtains all columns names available via {@link #getColumnNames()}</li>
 * <li>Obtains primary key column names via {@link #getPkNames()}</li>
 * </ul>
 * <p/>
 * And others methods used to synchronize referentials:
 * <ul>
 * <li>Obtains query to update a row of the table (column names order is the one introduced by method
 * {@link #getColumnNames()}: {@link #getUpdateQuery()}</li>
 * <li>Obtains query to insert a row in the table (column names order is the one introduced by method
 * {@link #getColumnNames()}: {@link #getInsertQuery()}</li>
 * </ul>
 * Created on 1/14/14.
 * 
 * @author Tony Chemit <chemit@codelutin.com>
 * @since 3.0
 */
public class SynchroTableMetadata {
    private static final Log log = LogFactory.getLog(SynchroTableMetadata.class);

    public enum TableInsertStrategy {
        GENERATE_ID_FIRST,
        INLINE_INSERT
    };

    public static final String PK_SEPARATOR = "~~";

    public static final String COLUMN_SYNCHRONIZATION_STATUS = "synchronization_status";

    public static final String COLUMN_ID = "id";

    public static final String COLUMN_REMOTE_ID = "remote_id";

    public static final String COLUMN_UPDATE_DATE = "update_date";

    public static final String SEQUENCE_SUFFIX = "_seq";

    public static Set<String> PROTECTED_COLUMN_NAMES = ImmutableSet.<String> builder().add(
            SynchroTableMetadata.COLUMN_REMOTE_ID,
            SynchroTableMetadata.COLUMN_SYNCHRONIZATION_STATUS
            ).build();

    private static Field delegateTableMetadataColumnsField = null;

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public static Map<String, ColumnMetadata> getColumns(TableMetadata delegate) {
        try {
            if (delegateTableMetadataColumnsField == null) {
                delegateTableMetadataColumnsField = TableMetadata.class.getDeclaredField("columns");
                delegateTableMetadataColumnsField.setAccessible(true);
            }
            return Maps.<String, ColumnMetadata> newLinkedHashMap((Map) delegateTableMetadataColumnsField.get(delegate));
        } catch (Exception e) {
            throw new SynchroTechnicalException(e.getMessage(), e);
        }
    }

    protected final String selectPrimaryKeysAsStringQuery;

    protected final String selectPrimaryKeysQuery;

    protected final String selectMaxUpdateDateQuery;

    protected final String countQuery;

    protected final TableMetadata delegate;

    protected final Map<String, SynchroColumnMetadata> columns;

    protected List<SynchroJoinMetadata> childJoins;

    protected List<SynchroJoinMetadata> parentJoins;

    protected List<SynchroJoinMetadata> joins;

    protected boolean hasJoins;

    protected boolean hasChildJoins;

    protected final List<String> protectedColumnNames;

    protected final List<String> columnNames;

    protected final Set<String> pkNames;

    protected final int[] pkIndexs;

    protected String insertQuery;

    protected String insertWithGeneratedIdQuery;

    protected String updateQuery;

    protected final boolean withUpdateDateColumn;

    protected final boolean withSynchronizationStatusColumn;

    protected final boolean withIdColumn;

    protected final boolean withRemoteIdColumn;

    protected boolean isRoot;

    protected final String countDataToUpdateQuery;

    protected final String selectDataToUpdateQuery;

    protected final String sequenceName;

    protected final String selectSequenceNextValueString;

    protected final String sequenceNextValString;

    protected final String selectAllQuery;

    protected final String selectDataQueryFromPk;

    protected final Map<String, String> selectDataFromFkQueries;

    protected final Map<String, String> selectDataFromFkQueriesBigParams;

    protected final String selectIdFromRemoteIdQuery;

    protected final String selectRemoteIdsQuery;

    protected List<SynchroInterceptor> interceptors;

    protected final SynchroContext context;

    protected TableInsertStrategy insertStrategy;

    protected final SynchroDatabaseMetadata dbMeta;

    protected SynchroTableMetadata(
            SynchroDatabaseMetadata dbMeta,
            TableMetadata delegate,
            List<SynchroInterceptor> interceptors,
            String tableName,
            Set<String> availableSequences,
            Predicate<SynchroColumnMetadata> columnFilter) {

        Preconditions.checkNotNull(delegate);
        Preconditions.checkNotNull(dbMeta);

        this.delegate = delegate;
        this.interceptors = interceptors != null ? interceptors : Lists.<SynchroInterceptor> newArrayList();
        this.dbMeta = dbMeta;
        this.context = dbMeta.getContext();

        try {
            this.columns = initColumns(tableName, dbMeta, columnFilter);
            this.columnNames = initColumnNames(columns);
            this.protectedColumnNames = initProtectedColumnNames(columns);
            this.pkNames = initPrimaryKeys(dbMeta);
            Preconditions.checkNotNull(pkNames);
            this.joins = Lists.newArrayList();
            this.hasJoins = !joins.isEmpty();
            this.withUpdateDateColumn = columnNames.contains(COLUMN_UPDATE_DATE);
            this.withSynchronizationStatusColumn = protectedColumnNames.contains(COLUMN_SYNCHRONIZATION_STATUS);
            this.withIdColumn = columnNames.contains(COLUMN_ID);
            this.withRemoteIdColumn = protectedColumnNames.contains(COLUMN_REMOTE_ID);
            this.sequenceName = initSequenceName(availableSequences);
            Preconditions
                    .checkArgument(!withRemoteIdColumn || sequenceName != null,
                            String.format("Columns %s and %s found on table %s, but unable to retrieve a sequence !", COLUMN_REMOTE_ID, COLUMN_ID,
                                    tableName));
            Preconditions.checkArgument(!withRemoteIdColumn || (pkNames.size() == 1 && withIdColumn),
                    String.format("Columns %s found on table %s, but more than one PK columns exists. Only %s column must be a PK.",
                            COLUMN_REMOTE_ID, tableName, COLUMN_ID));

            // Default values (could be override using interceptor)
            this.isRoot = false;
        } catch (Exception e) {
            throw new SynchroTechnicalException(t("adagio.persistence.tableMetadata.instanciation.error", this), e);
        }

        this.pkIndexs = createPkIndex();
        this.selectSequenceNextValueString = createSelectSequenceNextValString(dbMeta.getDialect());
        this.sequenceNextValString = createSequenceNextValString(dbMeta.getDialect());
        this.insertQuery = createInsertQuery();
        this.insertWithGeneratedIdQuery = createInsertWithGeneratedIdQuery();
        this.updateQuery = createUpdateQuery();
        this.selectMaxUpdateDateQuery = createSelectMaxUpdateDateQuery();
        this.selectPrimaryKeysAsStringQuery = createSelectPrimaryKeysAsStringQuery();
        this.selectPrimaryKeysQuery = createSelectPrimaryKeysQuery();
        this.selectRemoteIdsQuery = createSelectRemoteIdsQuery();
        this.selectAllQuery = createSelectAllQuery();
        this.selectDataQueryFromPk = createSelectDataFromPkQuery();
        this.selectDataToUpdateQuery = createSelectDataToUpdateQuery();
        this.selectDataFromFkQueries = createSelectDataFromFkQueries();
        this.selectDataFromFkQueriesBigParams = createSelectDataFromFkQueriesBigParams(dbMeta);
        this.selectIdFromRemoteIdQuery = createSelectIdFromRemoteIdQuery();
        this.countQuery = createCountQuery();
        this.countDataToUpdateQuery = createCountDataToUpdateQuery();
        this.insertStrategy = TableInsertStrategy.INLINE_INSERT;
    }

    // for tests purposes
    SynchroTableMetadata() {

        delegate = null;
        context = null;
        dbMeta = null;
        columns = null;
        columnNames = null;
        protectedColumnNames = null;
        joins = null;
        pkNames = null;
        pkIndexs = null;
        withUpdateDateColumn = false;
        withSynchronizationStatusColumn = false;
        withIdColumn = false;
        withRemoteIdColumn = false;
        isRoot = false;
        insertQuery = null;
        insertWithGeneratedIdQuery = null;
        updateQuery = null;
        countQuery = null;
        countDataToUpdateQuery = null;
        selectPrimaryKeysAsStringQuery = null;
        selectPrimaryKeysQuery = null;
        selectRemoteIdsQuery = null;
        selectMaxUpdateDateQuery = null;
        selectDataToUpdateQuery = null;
        selectDataFromFkQueries = null;
        selectDataFromFkQueriesBigParams = null;
        selectIdFromRemoteIdQuery = null;
        selectSequenceNextValueString = null;
        sequenceNextValString = null;
        sequenceName = null;
        selectAllQuery = null;
        selectDataQueryFromPk = null;
    }

    /**
     * Initialize Join metadata. this need to have already loaded all tables.
     * 
     * @param dbMeta
     *            the Database metadata, with some preloaded tables inside
     */
    public void initJoins(SynchroDatabaseMetadata dbMeta) {
        try {
            this.joins = initJoins(getName(), dbMeta, this.columns, this.interceptors);
            this.hasJoins = !joins.isEmpty();
            this.childJoins = initChildJoins(joins);
            this.parentJoins = initParentJoins(joins);
            this.hasChildJoins = !childJoins.isEmpty();
            this.insertQuery = createInsertQuery();
            this.insertStrategy = !hasJoins || !withRemoteIdColumn
                    ? TableInsertStrategy.INLINE_INSERT
                    : TableInsertStrategy.GENERATE_ID_FIRST;
        } catch (Exception e) {
            throw new SynchroTechnicalException(t("adagio.persistence.tableMetadata.instanciation.error", this), e);
        }
    }

    public Set<String> getPkNames() {
        return pkNames;
    }

    public boolean isWithUpdateDateColumn() {
        return withUpdateDateColumn;
    }

    public boolean isWithSynchronizationStatusColumn() {
        return withSynchronizationStatusColumn;
    }

    public boolean isWithIdColumn() {
        return withIdColumn;
    }

    public boolean isWithRemoteIdColumn() {
        return withRemoteIdColumn;
    }

    public boolean isRoot() {
        return isRoot;
    }

    public int getColumnsCount() {
        return columnNames.size();
    }

    public Set<String> getColumnNames() {
        return ImmutableSet.copyOf(columnNames);
    }

    public int getColumnIndex(String name) {
        return columnNames.indexOf(name.toLowerCase());
    }

    public TableMetadata getDelegate() {
        return delegate;
    }

    public SynchroDatabaseMetadata getDatabaseMetadata() {
        return dbMeta;
    }

    public String getName() {
        return delegate.getName();
    }

    public ForeignKeyMetadata getForeignKeyMetadata(ForeignKey fk) {
        return delegate.getForeignKeyMetadata(fk);
    }

    public SynchroColumnMetadata getColumnMetadata(String columnName) {
        return columns.get(StringHelper.toLowerCase(columnName));
    }

    public String getSchema() {
        return delegate.getSchema();
    }

    public String getCatalog() {
        return delegate.getCatalog();
    }

    public ForeignKeyMetadata getForeignKeyMetadata(String keyName) {
        return delegate.getForeignKeyMetadata(keyName);
    }

    public IndexMetadata getIndexMetadata(String indexName) {
        return delegate.getIndexMetadata(indexName);
    }

    public SynchroColumnMetadata getColumn(String columnName) {
        return columns.get(columnName);
    }

    public List<SynchroJoinMetadata> getJoins() {
        return joins;
    }

    public List<SynchroJoinMetadata> getChildJoins() {
        return childJoins;
    }

    public List<SynchroJoinMetadata> getParentJoins() {
        return parentJoins;
    }

    public boolean hasJoins() {
        return hasJoins;
    }

    public boolean hasChildJoins() {
        return hasChildJoins;
    }

    public String getTableLogPrefix() {
        return "[" + getName() + "]";
    }

    public TableInsertStrategy getInsertStrategy() {
        return this.insertStrategy;
    }

    @Override
    public String toString() {
        return delegate.toString();
    }

    // ------------------------------------------------------------------------//
    // -- queries methods --//
    // ------------------------------------------------------------------------//

    public String getInsertQuery() {
        return insertQuery;
    }

    public String getInsertWithGeneratedIdQuery() {
        return insertWithGeneratedIdQuery;
    }

    public String getUpdateQuery() {
        return updateQuery;
    }

    /**
     * Obtains a SQL with one column output : a concatenation of all PK.
     * 
     * @return a SQL select, with only one result column
     */
    public String getSelectPrimaryKeysAsStringQuery() {
        return selectPrimaryKeysAsStringQuery;
    }

    public String getSelectPrimaryKeysQuery() {
        return selectPrimaryKeysQuery;
    }

    public String getSelectRemoteIdsQuery() {
        return selectRemoteIdsQuery;
    }

    public String getSequenceNextValString() {
        return sequenceNextValString;
    }

    public String getSelectMaxUpdateDateQuery() {
        return selectMaxUpdateDateQuery;
    }

    public String getSelectDataQueryFromPk() {
        return selectDataQueryFromPk;
    }

    public String getSelectDataToUpdateQuery(Date fromDate) {
        String sql;
        if (fromDate == null) {
            sql = selectAllQuery;
        }
        else {
            sql = selectDataToUpdateQuery;
        }

        return sql;
    }

    public String getSelectDataFromFkQuery(String columnName, int nbValues) {
        String sql = selectDataFromFkQueries.get(columnName.toLowerCase());
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < nbValues; i++) {
            builder.append(",?");
        }
        return String.format(sql,
                builder.substring(1));
    }

    public String getSelectDataFromFkQueryBigParams(String columnName) {
        return selectDataFromFkQueriesBigParams.get(columnName.toLowerCase());
    }

    public String getCountQuery() {
        return countQuery;
    }

    public String getCountDataToUpdateQuery(Date fromDate) {
        String sql;
        if (fromDate == null) {
            sql = countQuery;
        }
        else {
            sql = countDataToUpdateQuery;
        }
        return sql;
    }

    public String getSelectIdFromRemoteIdQuery(String tableName) {
        return String.format(selectIdFromRemoteIdQuery, tableName);
    }

    public Object[] getData(ResultSet incomingData) throws SQLException {
        Object[] result = new Object[columnNames.size()];
        for (int c = 1; c <= columnNames.size(); c++) {
            Object object = getObject(incomingData, c);

            result[c - 1] = object;
        }

        return result;
    }

    public Object getObject(ResultSet incomingData, int index) throws SQLException {
        Object object = incomingData.getObject(index);
        if (object instanceof TIMESTAMP) {
            object = ((TIMESTAMP) object).timestampValue();
        }
        if (object instanceof BigDecimal) {
            object = ((BigDecimal) object).intValue();
        }
        return object;
    }

    // ------------------------------------------------------------------------//
    // -- PK methods --//
    // ------------------------------------------------------------------------//

    public int[] getPkIndexs() {
        return pkIndexs;
    }

    public boolean isSimpleKey() {
        return pkIndexs.length == 1;
    }

    public List<Object> getPk(ResultSet incomingData) throws SQLException {
        List<Object> result = Lists.newArrayListWithCapacity(pkIndexs.length);
        for (int pkIndex : pkIndexs) {
            Object pk = getObject(incomingData, pkIndex);
            result.add(pk);
        }
        return result;
    }

    public boolean isSelectPrimaryKeysAsStringQueryEnable() {
        return selectPrimaryKeysAsStringQuery != null;
    }

    public List<Object> getPk(Object[] incomingData) throws SQLException {
        List<Object> result = Lists.newArrayListWithCapacity(pkIndexs.length);
        for (int pkIndex : pkIndexs) {
            Object pk = incomingData[pkIndex - 1];
            result.add(pk);
        }
        return result;
    }

    public Integer getId(ResultSet incomingData) throws SQLException {
        return incomingData.getInt(pkIndexs[0]);
    }

    /**
     * Serialize into a String a list of PK value. Usefull when more than one PK column in a table
     * 
     * @param pkList
     * @return
     */
    public String toPkStr(List<Object> pkList) {
        StringBuilder sb = new StringBuilder();
        for (Object pk : pkList) {
            sb.append(PK_SEPARATOR).append(pk);
        }
        return sb.substring(PK_SEPARATOR.length());
    }

    public List<Object> fromPkStr(String pk) {
        List<Object> pkList = Lists.newArrayList();
        String[] split = pk.split(PK_SEPARATOR);
        for (String s : split) {
            if ("null".equals(s)) {
                s = null;
            }
            pkList.add(s);
        }
        return pkList;
    }

    // ------------------------------------------------------------------------//
    // -- Protected methods --//
    // ------------------------------------------------------------------------//

    protected Map<String, SynchroColumnMetadata> initColumns(
            String tableName,
            SynchroDatabaseMetadata dbMeta,
            Predicate<SynchroColumnMetadata> columnFilter
            ) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {

        Map<String, ColumnMetadata> delegateColumns = getColumns(delegate);
        Map<String, SynchroColumnMetadata> columns = Maps.newHashMap();
        int columnIndex = 1;
        for (String columnName : delegateColumns.keySet()) {
            ColumnMetadata delegateColumn = delegateColumns.get(columnName);
            SynchroColumnMetadata column = new SynchroColumnMetadata(
                    delegateColumn,
                    tableName,
                    columnIndex,
                    PROTECTED_COLUMN_NAMES.contains(columnName));
            if (columnFilter == null || columnFilter.apply(column)) {
                columns.put(columnName, column);
                columnIndex++;
            }
        }

        return columns;
    }

    protected List<String> initProtectedColumnNames(Map<String, SynchroColumnMetadata> columns) {
        List<String> result = Lists.newArrayListWithExpectedSize(PROTECTED_COLUMN_NAMES.size());
        for (String name : columns.keySet()) {
            if (PROTECTED_COLUMN_NAMES.contains(name.toLowerCase())) {
                result.add(name.toLowerCase());
            }
        }
        return result;
    }

    protected List<String> initColumnNames(Map<String, SynchroColumnMetadata> columns) {
        List<String> result = Lists.newArrayListWithExpectedSize(columns.size());
        for (String name : columns.keySet()) {
            if (PROTECTED_COLUMN_NAMES.contains(name.toLowerCase()) == false) {
                result.add(name.toLowerCase());
            }
        }
        return result;
    }

    protected Set<String> initPrimaryKeys(SynchroDatabaseMetadata dbMeta) throws SQLException {

        Set<String> result = Sets.newLinkedHashSet();
        ResultSet rs = dbMeta.getPrimaryKeys(getCatalog(), getSchema(), getName());
        try {

            while (rs.next()) {
                result.add(rs.getString("COLUMN_NAME").toLowerCase());
            }
            return ImmutableSet.copyOf(result);
        } finally {
            DaoUtils.closeSilently(rs);
        }
    }

    protected int[] createPkIndex() {

        int[] result = new int[pkNames.size()];

        int pkI = 0;
        for (String pkName : pkNames) {
            String pkColumnName = pkName.toLowerCase();

            int i = 1;

            int index = -1;
            for (String columnName : columnNames) {
                if (pkColumnName.equals(columnName)) {
                    index = i;
                } else {
                    i++;
                }
            }
            result[pkI++] = index;
        }
        return result;
    }

    protected List<SynchroJoinMetadata> initJoins(
            String tableName,
            SynchroDatabaseMetadata dbMeta,
            Map<String, SynchroColumnMetadata> columns,
            List<SynchroInterceptor> interceptors
            ) throws SQLException {

        List<SynchroJoinMetadata> result = Lists.newArrayList();

        // Exported keys (primary keys referenced by another table)
        ResultSet rs = dbMeta.getExportedKeys(delegate.getCatalog(), delegate.getSchema(), tableName);
        while (rs.next()) {
            String columnName = rs.getString("PKCOLUMN_NAME");
            if (columns.get(columnName.toLowerCase()) != null) {
                SynchroJoinMetadata join = new SynchroJoinMetadata(rs, this, dbMeta);

                // Fire event to interceptors
                fireOnJoinLoad(join);

                if (join.isValid()) {
                    result.add(join);
                }
            }
        }

        // Imported keys (foreign keys that references another table)
        rs = dbMeta.getImportedKeys(delegate.getCatalog(), delegate.getSchema(), tableName);
        while (rs.next()) {
            String columnName = rs.getString("FKCOLUMN_NAME");
            if (columns.get(columnName.toLowerCase()) != null) {
                SynchroJoinMetadata join = new SynchroJoinMetadata(rs, this, dbMeta);

                // Fire event to interceptors
                fireOnJoinLoad(join);

                if (join.isValid()) {
                    result.add(join);
                }
            }
        }

        return result;
    }

    protected List<SynchroJoinMetadata> initChildJoins(
            List<SynchroJoinMetadata> joins
            ) throws SQLException {

        List<SynchroJoinMetadata> result = Lists.newArrayListWithExpectedSize(joins.size());

        for (SynchroJoinMetadata join : joins) {
            if (join.isChild()) {
                result.add(join);
            }
        }
        return result;
    }

    protected List<SynchroJoinMetadata> initParentJoins(
            List<SynchroJoinMetadata> joins
            ) throws SQLException {

        List<SynchroJoinMetadata> result = Lists.newArrayListWithExpectedSize(joins.size());

        for (SynchroJoinMetadata join : joins) {
            if (!join.isChild()) {
                result.add(join);
            }
        }
        return result;
    }

    protected String createInsertQuery() {
        return createInsertQuery(false);
    }

    protected String createInsertWithGeneratedIdQuery() {
        if (!withRemoteIdColumn) {
            return null;
        }
        return createInsertQuery(true);
    }

    protected String createInsertQuery(boolean generateId) {
        if (CollectionUtils.isEmpty(columnNames)) {
            return null;
        }

        StringBuilder queryParams = new StringBuilder();
        StringBuilder valueParams = new StringBuilder();

        for (String columnName : columnNames) {
            if (withRemoteIdColumn && COLUMN_ID.equals(columnName)) {
                queryParams.append(", ").append(COLUMN_REMOTE_ID);
            }
            else {
                queryParams.append(", ").append(columnName);
            }
            valueParams.append(", ?");
        }

        if (withRemoteIdColumn) {
            queryParams.append(", ").append(COLUMN_ID);
            if (generateId) {
                valueParams.append(", ").append(selectSequenceNextValueString);
            }
            else {
                valueParams.append(", ?");
            }
        }

        if (withSynchronizationStatusColumn) {
            queryParams.append(", ").append(COLUMN_SYNCHRONIZATION_STATUS);
            valueParams.append(", '")
                    .append(SynchroConfiguration.getInstance().getSynchronizationStatusSynchronized())
                    .append("'");
        }

        String result = String.format("INSERT INTO %s (%s) VALUES (%s)",
                getName(),
                queryParams.substring(2),
                valueParams.substring(2));
        return result;
    }

    protected String createUpdateQuery() {
        // Could not update, because no PK found
        if (CollectionUtils.isEmpty(pkNames)) {
            return null;
        }
        if (CollectionUtils.isEmpty(columnNames)) {
            return null;
        }

        StringBuilder updateParams = new StringBuilder();

        for (String columnName : columnNames) {
            if (withRemoteIdColumn && COLUMN_ID.equals(columnName)) {
                updateParams.append(", ").append(COLUMN_REMOTE_ID);
            }
            else {
                updateParams.append(", ").append(columnName);
            }
            updateParams.append(" = ?");
        }

        if (withSynchronizationStatusColumn) {
            updateParams.append(", ").append(COLUMN_SYNCHRONIZATION_STATUS)
                    .append(" = '")
                    .append(SynchroConfiguration.getInstance().getSynchronizationStatusSynchronized())
                    .append("'");
        }

        String result = String.format("UPDATE %s SET %s WHERE %s",
                getName(),
                updateParams.substring(2),
                createPkWhereClause());
        return result;
    }

    protected String createSelectDataFromPkQuery() {
        // Could not update, because no PK found
        if (CollectionUtils.isEmpty(pkNames)) {
            return null;
        }

        String sql = String.format("SELECT %s FROM %s WHERE %s",
                createSelectParams(),
                getName(),
                createPkWhereClause());

        sql = fireOnCreateSelectQuery("selectDataFromPkQuery", sql);
        return sql;
    }

    protected Map<String, String> createSelectDataFromFkQueries() {
        Map<String, String> result = Maps.newHashMap();

        for (SynchroColumnMetadata column : columns.values()) {
            String sql = String.format("SELECT %s FROM %s WHERE %s IN (%s)",
                    createSelectParams(),
                    getName(),
                    column.getName(),
                    "%s");

            sql = fireOnCreateSelectQuery("selectDataFromFkQuery", sql);
            result.put(column.getName().toLowerCase(), sql);
        }

        return result;
    }

    protected Map<String, String> createSelectDataFromFkQueriesBigParams(SynchroDatabaseMetadata dbMeta) {
        Map<String, String> result = Maps.newHashMap();

        for (SynchroColumnMetadata column : columns.values()) {
            String sql = String.format(
                    "SELECT %s FROM %s t INNER JOIN TEMP_QUERY_PARAMETER p on t.%s = p.NUMERICAL_VALUE AND p.PARAMETER_NAME=? AND p.PERSON_FK=?",
                    createSelectParams("t"),
                    getName(),
                    column.getName());

            sql = fireOnCreateSelectQuery("selectDataFromFkQueryBigParams", sql);
            result.put(column.getName().toLowerCase(), sql);
        }

        return result;
    }

    protected String createSelectPrimaryKeysAsStringQuery() {
        // Could not update, because no PK found
        if (CollectionUtils.isEmpty(pkNames)) {
            return null;
        }

        String prefix = " || '" + PK_SEPARATOR + "' || ";
        StringBuilder pkParams = new StringBuilder();

        boolean allowUniqueOutputColumn = true;

        for (String columnName : pkNames) {

            // If some date conversion => not database independent
            SynchroColumnMetadata column = columns.get(columnName);

            // For date/timestamp : make sure the convertion to char give the same result
            // -> add a cast to Timestamp
            if (column.getTypeCode() == Types.TIMESTAMP
                    || column.getTypeCode() == Types.DATE) {
                allowUniqueOutputColumn = false;
            }
            pkParams.append(prefix).append(columnName);
        }

        if (!allowUniqueOutputColumn) {
            return null;
        }

        String sql = String.format("SELECT %s FROM %s",
                pkParams.substring(prefix.length()),
                getName());

        sql = fireOnCreateSelectQuery("selectPrimaryKeysAsStringQuery", sql);
        return sql;
    }

    protected String createSelectPrimaryKeysQuery() {
        // Could not update, because no PK found
        if (CollectionUtils.isEmpty(pkNames)) {
            return null;
        }

        StringBuilder pkParams = new StringBuilder();

        for (String columnName : pkNames) {
            pkParams.append(", ").append(columnName);
        }

        String sql = String.format("SELECT %s FROM %s",
                pkParams.substring(2),
                getName());

        sql = fireOnCreateSelectQuery("selectPrimaryKeysQuery", sql);

        return sql;
    }

    protected String createSelectRemoteIdsQuery() {
        // Could not update, because no PK found
        if (!withRemoteIdColumn || !withIdColumn) {
            return null;
        }

        String sql = String.format("SELECT %s, %s FROM %s WHERE %s IS NOT NULL",
                COLUMN_ID,
                COLUMN_REMOTE_ID,
                getName(),
                COLUMN_REMOTE_ID);

        sql = fireOnCreateSelectQuery("selectRemoteIdsQuery", sql);

        return sql;
    }

    protected String createSelectAllQuery() {
        String sql = String.format("SELECT %s FROM %s t",
                createSelectParams("t"),
                getName()
                );

        sql = fireOnCreateSelectQuery("selectAllQuery", sql);

        return sql;
    }

    protected String createSelectMaxUpdateDateQuery() {
        if (!withUpdateDateColumn) {
            return null;
        }
        String sql = String.format("SELECT max(%s) FROM %s",
                COLUMN_UPDATE_DATE,
                getName());

        sql = fireOnCreateSelectQuery("selectMaxUpdateDateQuery", sql);

        return sql;
    }

    protected String createSelectDataToUpdateQuery() {
        String sql = String.format("SELECT %s FROM %s t%s",
                createSelectParams("t"),
                getName(),
                createWithUpdateDateWhereClause("t"));

        sql = fireOnCreateSelectQuery("selectDataToUpdateQuery", sql);

        return sql;
    }

    protected String createCountQuery() {
        String sql = String.format("SELECT count(*) FROM %s t",
                getName());

        sql = fireOnCreateSelectQuery("countQuery", sql);

        return sql;
    }

    protected String createSelectIdFromRemoteIdQuery() {
        if (!withIdColumn || withRemoteIdColumn) {
            return null;
        }
        String sql = String.format("SELECT %s FROM %s WHERE %s=?",
                SynchroTableMetadata.COLUMN_ID,
                "%s",
                SynchroTableMetadata.COLUMN_REMOTE_ID
                );

        sql = fireOnCreateSelectQuery("selectIdFromRemoteIdQuery", sql);

        return sql;
    }

    protected String createCountDataToUpdateQuery() {
        String sql = String.format("SELECT count(*) FROM %s t%s",
                getName(),
                createWithUpdateDateWhereClause("t"));

        sql = fireOnCreateSelectQuery("countDataToUpdateQuery", sql);

        return sql;
    }

    protected String createPkWhereClause() {
        Preconditions.checkArgument(CollectionUtils.isNotEmpty(pkNames));
        StringBuilder pkParams = new StringBuilder();

        for (String columnName : pkNames) {
            pkParams.append("AND ").append(columnName).append(" = ?");
        }

        return pkParams.substring(4);
    }

    protected String createWithUpdateDateWhereClause() {
        return createWithUpdateDateWhereClause(null);
    }

    protected String createWithUpdateDateWhereClause(String tableAlias) {
        String whereClause;

        if (isWithUpdateDateColumn()) {
            String prefix = tableAlias != null ? tableAlias + "." : "";
            // add a filter
            whereClause = String.format(
                    " WHERE (%supdate_date IS NULL OR %supdate_date > ?)",
                    prefix,
                    prefix);
        } else {
            whereClause = "";
        }
        return whereClause;
    }

    protected String createSelectParams() {
        return createSelectParams(null);
    }

    protected String createSelectParams(String tableAlias) {
        Preconditions.checkArgument(CollectionUtils.isNotEmpty(columnNames),
                String.format("No column found for table: %s", delegate.getName()));

        StringBuilder queryParams = new StringBuilder();

        for (String columnName : columnNames) {
            queryParams.append(", ");
            if (tableAlias != null) {
                queryParams.append(tableAlias)
                        .append(".");
            }
            queryParams.append(columnName);
        }

        return queryParams.substring(2);
    }

    protected String initSequenceName(Set<String> availableSequences) {

        String tableName = getName().toLowerCase();
        String sequenceName = tableName + SEQUENCE_SUFFIX;
        if (availableSequences.contains(sequenceName.toLowerCase())) {
            return sequenceName;
        }

        int maxLengthWithoutSuffix = 30 - SEQUENCE_SUFFIX.length();
        if (tableName.length() > maxLengthWithoutSuffix) {
            sequenceName = tableName.substring(0, maxLengthWithoutSuffix) + SEQUENCE_SUFFIX;
            if (availableSequences.contains(sequenceName.toLowerCase())) {
                return sequenceName;
            }
        }

        return null;
    }

    public void setIsRoot(boolean isRoot) {
        this.isRoot = isRoot;
    }

    protected String createSelectSequenceNextValString(Dialect dialect) {
        if (StringUtils.isBlank(sequenceName)) {
            return null;
        }
        return dialect.getSelectSequenceNextValString(sequenceName);
    }

    protected String createSequenceNextValString(Dialect dialect) {
        if (StringUtils.isBlank(sequenceName)) {
            return null;
        }
        return dialect.getSequenceNextValString(sequenceName);
    }

    public List<SynchroInterceptor> getInterceptors() {
        return this.interceptors;
    }

    protected String fireOnCreateSelectQuery(String queryName, String sql) {
        if (CollectionUtils.isNotEmpty(interceptors)) {
            for (SynchroInterceptor interceptor : interceptors) {
                String newSql = interceptor.onCreateSelectQuery(this, queryName, sql);
                if (newSql != null) {
                    sql = newSql;
                }
            }
        }
        return sql;
    }

    protected void fireOnJoinLoad(SynchroJoinMetadata join) {
        if (CollectionUtils.isNotEmpty(interceptors)) {
            for (SynchroInterceptor interceptor : interceptors) {
                interceptor.onJoinLoad(this, join);
            }
        }
    }
}