/*
Copyright (C) 2000-2010  Ministere de la culture et de la communication (France), AJLSM
See LICENCE file
*/
package fr.gouv.culture.sdx.utils.database;

import fr.gouv.culture.sdx.documentbase.DefaultIDGenerator;
import fr.gouv.culture.sdx.exception.SDXException;
import fr.gouv.culture.sdx.exception.SDXExceptionCode;
import fr.gouv.culture.sdx.utils.Utilities;
import fr.gouv.culture.sdx.utils.constants.ContextKeys;
import fr.gouv.culture.sdx.utils.constants.Node;
import fr.gouv.culture.sdx.utils.rdbms.hsql.HSQLDB;
import fr.gouv.culture.sdx.utils.save.SaveParameters;
import fr.gouv.culture.sdx.utils.save.Saveable;

import org.apache.avalon.framework.configuration.Configuration;
import org.apache.avalon.framework.configuration.ConfigurationException;
import org.apache.avalon.framework.parameters.ParameterException;
import org.apache.avalon.framework.parameters.Parameters;

import java.io.File;
import java.sql.*;

public class HSQLDatabase extends AbstractJDBCDatabase {

    protected String dbDirPath = null;
    protected HSQLDB hsqldb = null;
    protected String DATABASE_DIR_NAME = "_hsql";


    public HSQLDatabase() {
    }

    public String getDatabaseDirectoryName() {
        return DATABASE_DIR_NAME;
    }


    public void configure(Configuration configuration) throws ConfigurationException {
        super.configure(configuration);

        if (Utilities.checkString(super.dsi))
            return;//we have been given a dsi that should point to a server instance of HSQL

        //since we don't want to talk to cocoon for this  we should provide necessary setup
        //for communicating with the same database
        //should be similar to {TOMCAT_HOME}/webapps/{sdx | cocoonAppName}/WEB-INF/sdx/

        try {
            this.hsqldb = (HSQLDB) Utilities.getObjectFromContext(ContextKeys.SDX.Application.HSQL_DATABASE_OBJECT, getContext());
            if (this.hsqldb == null)
                throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_HSQLDB_NULL, null, null);
            if (!Utilities.checkString(this.hsqldb.getDbDirPath())) {
                //setting the path if it isn't already set
                String basePath = Utilities.getStringFromContext(ContextKeys.SDX.Application.DATABASE_DIRECTORY_PATH, super.getContext());
                if (Utilities.checkString(basePath))
                    this.dbDirPath = basePath + getDatabaseDirectoryName() + File.separator;

                if (Utilities.checkString(this.dbDirPath)) {
                    Utilities.checkDirectory(this.dbDirPath, super.getLog());
                    this.hsqldb.setDbDirPath(this.dbDirPath);
                }
            }

            super.tableName = super.getId();//using only the id now as this allows app to be more portable
        } catch (SDXException e) {
            throw new ConfigurationException(e.getMessage(), e);
        }


    }


    private synchronized Connection getSQLConnection() throws SDXException {
        if (this.hsqldb == null)
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_HSQLDB_NULL, null, null);

        return this.hsqldb.getSQLConnection();
    }

    public DatabaseConnection getConnection() throws SDXException {
        if (Utilities.checkString(super.dsi)) {
            return super.getConnection();
        } else {
            DatabaseConnection dbConn = new SQLDatabaseConnection(getSQLConnection());
            dbConn.enableLogging(super.getLog());
            dbConn.setAutoCommit(false);
            return dbConn;
        }

    }

    public void releaseConnection(DatabaseConnection conn) throws SDXException {
        if (Utilities.checkString(super.dsi))
            super.releaseConnection(conn);
        else {
            //with hsqldb in stand alone mode we never want to loose the connection
            //so we do nothing here
        }
    }


    protected String getOptimizeQuery() {
        return "CHECKPOINT";
    }

    public synchronized void optimize() throws SDXException {
        DatabaseConnection sqlDbConn = getConnection();
        Connection conn = sqlDbConn.getConnection();
        String queryString = getOptimizeQuery();
        try {
            Template template = new Template(conn, queryString);
            template.execute(new QueryExecutor() {
            }, Template.MODE_EXECUTE_UPDATE);
        } catch (SDXException e) {
            String[] args = new String[1];
            args[0] = super.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_OPTIMIZE, args, e);
        } finally {
            releaseConnection(sqlDbConn);
        }
    }


    /** Initializes the database.
     *
     * If there are no tables in the database,
     * we create the necessary table
     *
     * @throws fr.gouv.culture.sdx.exception.SDXException  */
    public void init() throws SDXException {
        //verifying that we have a unique table name
        if (this.hsqldb != null) this.hsqldb.registerTableName(this.getTableName());
        /** First try to access the database and see if the tables exist. */
        DatabaseConnection sqldbConn = getConnection();
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            conn = sqldbConn.getConnection();
            //if we don't have a database we can't do much
            //TODO: check for null connection-rbp
            DatabaseMetaData dbmd = conn.getMetaData();
            //TODO: FIXME when hsql bug is correctece, using .toUpperCase() here to compensate for a bug in hsql
            String tableNamePat = "%" + getTableName().toUpperCase();
//            System.out.println("TABLE_NAME Pattern  :" + tableNamePat);
            rs = dbmd.getTables(null, null, tableNamePat.toUpperCase(), null);
            if (!rs.next()) {
                // The table doesn't exist, so we should create it. in the 2.2 format
                createTable(conn);
                //dont need to create indices on each column as hsql does it for us with the primary key constraint
                //createIndicies(conn);
            } else {
                String existingTableName = "";
                boolean tableExists = false;
                do {
                    existingTableName = rs.getString("TABLE_NAME");
//                   System.out.println("TABLE_NAME  :" + existingTableName);
                    //indx underscore must be greater than zero, as an app path was there before
                    if (existingTableName.equals(this.getTableName().toUpperCase())) {
                        tableExists = true;
                        break;//we have the exact table name
                    }
                    String l_tableName = getTableName().toUpperCase();
                    int indxUnderScore = existingTableName.indexOf("_" + l_tableName);
                    //end index must be the end of the table name string for an exact match
                    int endIndx = existingTableName.lastIndexOf("_" + l_tableName);
                    if (!existingTableName.startsWith("sdx_") && indxUnderScore > 0 && endIndx == existingTableName.length()) {
                        int indxTableName = existingTableName.indexOf(l_tableName);
                        if (indxUnderScore == (indxTableName - 1)) {//we ensure that the underscore is before the table name which may contain an underscore
//                    System.out.println("changing table name  :" + existingTableName);
                            //we have an sdx2.1 table name and we will rename it for 2.2 more portable format
                            ps = conn.prepareStatement(getAlterTableNameQuery(existingTableName));
                            ps.executeUpdate();
                            tableExists = true;
                        }
                    }
                } while (rs.next());

                if (!tableExists)
                    createTable(conn);
            }

        } catch (SQLException e) {
            String[] args = new String[1];
            args[0] = this.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_INIT_DATABASE, args, e);
        } finally {

            if (ps != null) {
                try {
                    if (ps != null) ps.close();
                } catch (SQLException e) {
                    String[] args = new String[2];
                    args[0] = this.getId();
                    args[1] = e.getMessage();
                    throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_CLOSE_SQL_PREPARED_STATEMENT, args, e);
                }
            }

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    String[] args = new String[2];
                    args[0] = this.getId();
                    args[1] = e.getMessage();
                    throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_CLOSE_RESULT_SET, args, e);
                }
            }
            sqldbConn.commit();
            releaseConnection(sqldbConn);

        }
    }

    protected String getTableCreationQuery() {
        return "CREATE CACHED TABLE " + getTableName() + " ( " + FIELD_ID + " VARCHAR(255) NOT NULL, " + FIELD_PROPERTY_NAME + " VARCHAR(255) NOT NULL, " + FIELD_PROPERTY_VALUE + " VARCHAR(255) NOT NULL, "
                + "PRIMARY KEY (" + FIELD_ID + ", " + FIELD_PROPERTY_NAME + ", " + FIELD_PROPERTY_VALUE + "))";
    }


    protected String getTableName() {
        return handleUnsupportedTokens(super.getTableName());
    }

    protected String getAlterTableNameQuery(String oldName) {
        return "ALTER TABLE " + handleUnsupportedTokens(oldName) + " RENAME TO " + getTableName();

    }

    protected void finalize() throws Throwable {
        this.optimize();//calling "checkpoint" here
        super.finalize();
    }

    public String[] search(final Parameters params, int mode) throws SDXException {
        if (mode != 0 && mode != 1 && mode != 2)
            return new String[0];
        String modeString = _searchModes[mode];
        //SELECT * FROM tablename WHERE propertyName = paramName propertyValue = paramValue.
        if (params == null) return new String[0];
        String[] entities = new String[0];
        DatabaseConnection sqlDbConn = getConnection();
        Connection conn = sqlDbConn.getConnection();
        String queryString = "";
        //getting the param names
        //TODO: can this be made more efficient, how can we avoid two loops?
        final String[] paramNames = params.getNames();
        //name for a temporary table which will be deleted
        String tempTableName = "";
        try {


            for (int i = 0; i < paramNames.length; i++) {
                //TODONOW: this must be optimized with a better query possibly using temp tables
                if (i == 0) {
                    final String paramName = paramNames[0];
                    final String paramValue = params.getParameter(paramNames[0]);
                    if (Utilities.checkString(paramName) && Utilities.checkString(paramValue)) {
                        tempTableName = getTableName() + "_search_" + new DefaultIDGenerator().generate();
//                        queryString += "SELECT * INTO TEMP " + tempTableName + " FROM " + getTableName() + " WHERE " + FIELD_ID + " IN (SELECT DISTINCT " + FIELD_ID + " FROM " + getTableName() + " WHERE " + FIELD_PROPERTY_NAME + " = ? AND " + FIELD_PROPERTY_VALUE + " = ?)";
                        queryString += "SELECT " + FIELD_ID + " INTO TEMP " + tempTableName + " FROM " + getTableName() + " WHERE " + FIELD_PROPERTY_NAME + " = ? AND " + FIELD_PROPERTY_VALUE + " = ?";
                        Template template = new Template(conn, queryString);
                        QueryExecutor qe = new QueryExecutor() {
                            public void prepare(PreparedStatement ps) throws SQLException, SDXException {
                                //ps.setString(indx++ , FIELD_PROPERTY_NAME);
                                ps.setString(1, paramName);
                                // ps.setString(indx++, FIELD_PROPERTY_VALUE);
                                ps.setString(2, paramValue);

                            }
                        };
                        //using execute because selecting into a temp table gives no result set
                        template.execute(qe, Template.MODE_EXECUTE_UPDATE);
                    }
                } else {
                    if (i > 1)//we take the intersection of the second and third param over the temp table results
                        queryString += " " + modeString + " ";
                    if (mode != SEARCH_MODE_AND)
                        queryString += "SELECT " + FIELD_ID + " INTO TEMP " + tempTableName + " FROM " + getTableName() + " WHERE " + FIELD_PROPERTY_NAME + " = ? AND " + FIELD_PROPERTY_VALUE + " = ?";
                    else
                        queryString += "SELECT " + FIELD_ID + " FROM " + tempTableName + " WHERE " + FIELD_ID + " IN (SELECT DISTINCT " + FIELD_ID + " FROM " + getTableName() + " WHERE " + FIELD_PROPERTY_NAME + " = ? AND " + FIELD_PROPERTY_VALUE + " = ? " + _searchModes[SEARCH_MODE_AND] + " SELECT DISTINCT " + FIELD_ID + " FROM " + tempTableName + " WHERE " + FIELD_ID + " IN (SELECT " + FIELD_ID + " FROM " + tempTableName + "))";
                }
            }

            if (paramNames.length == 1)//only had one parameter so we should take all results from the temp table
                queryString = "SELECT * FROM " + tempTableName;


            if (Utilities.checkString(queryString)) {
                Template template2 = new Template(conn, queryString);
                QueryExecutor qe2 = new QueryExecutor() {
                    String[] l_dbes = null;

                    public void prepare(PreparedStatement ps) throws SQLException, SDXException {
                        if (paramNames.length > 1) {
                            //setting the params
                            int indx = 0;
                            for (int j = 1; j < paramNames.length; j++) {
                                //ps.setString(indx++ , FIELD_PROPERTY_NAME);
                                ps.setString(++indx, paramNames[j]);
                                // ps.setString(indx++, FIELD_PROPERTY_VALUE);
                                try {
                                    ps.setString(++indx, params.getParameter(paramNames[j]));
                                } catch (ParameterException e) {
                                    throw new SDXException(getLog(), SDXExceptionCode.ERROR_GET_PARAMETERS, null, e);
                                }


                            }
                        }
                    }

                    public void collect(ResultSet rs) throws SQLException, SDXException {
                        l_dbes = getEntityIds(rs);
                    }

                    public Object get() {
                        return l_dbes;
                    }
                };
                template2.execute(qe2, Template.MODE_EXECUTE_QUERY);
                entities = (String[]) qe2.get();


            }
            return entities;
        } catch (SDXException e) {
            String[] args = new String[1];
            args[0] = this.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_SEARCH_DATABASE, args, e);
        } catch (ParameterException e) {
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_GET_PARAMETERS, null, e);
        } finally {
            try {
                //is it a good practice to close both the result set and prepared statement?
                //i think the prepared statement closes the corresponding results set?-rbp

                if (Utilities.checkString(tempTableName)) {
                    queryString = "DROP TABLE " + tempTableName + " IF EXISTS";
                    Template template = new Template(conn, queryString);
                    //using execute because selecting into a temp table gives no result set
                    template.execute(new QueryExecutor() {
                    }, Template.MODE_EXECUTE_UPDATE);
                }
            } catch (SDXException e) {
                String[] args = new String[1];
                args[0] = this.getId();
                throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_SEARCH_DATABASE, args, e);
            }

            releaseConnection(sqlDbConn);

        }


    }

	/* (non-Javadoc)
	 * @see fr.gouv.culture.sdx.utils.AbstractSdxObject#initToSax()
	 */
	protected boolean initToSax() {
		this._xmlizable_objects.put("Database_Type","HSQLDatabase");
		this._xmlizable_objects.put("JDBC_Table_Name",this.tableName);
		this._xmlizable_objects.put("Document_Count",String.valueOf(this.size()));
		this._xmlizable_objects.put("Database_Directory_Path",this.dbDirPath);
		this._xmlizable_objects.put("Data_Source_Identifier",this.dsi);
		return true;
	}

	/**Init the LinkedHashMap _xmlizable_volatile_objects with the objects in order to describ them in XML
	 * Some objects need to be refresh each time a toSAX is called*/
	protected void initVolatileObjectsToSax() {
		this._xmlizable_objects.put("Document_Count",String.valueOf(this.size()));
	}

	/** Save the database
	 * @see fr.gouv.culture.sdx.utils.save.Saveable#backup(fr.gouv.culture.sdx.utils.save.SaveParameters)
	 */
	public void backup(SaveParameters save_config) throws SDXException {
		super.backup(save_config);
		if(save_config != null)
			if(save_config.getAttributeAsBoolean(Saveable.ALL_SAVE_ATTRIB,false))
			{
				save_config.setAttribute(Node.Name.TYPE,"HSQL");
			}
	}

	/**
	 * @see fr.gouv.culture.sdx.utils.database.AbstractJDBCDatabase#getAllEntitiesWithLimitQuery(long, long)
	 * HSQL implementation
	 * 	SELECT LIMIT &gt;offset&lt; &gt;number&lt;
	 * 			DISTINCT * FROM &gt;table_name&lt;);
	 */
	protected String getEntriesWithLimitQuery(long offset, long number) {
		String query = "SELECT LIMIT " + String.valueOf(offset) + " " + String.valueOf(number);
		query += " * FROM " + getTableName()+";";
		return query;
	}
	/** Restore the database
	 * @see fr.gouv.culture.sdx.utils.save.Saveable#restore(fr.gouv.culture.sdx.utils.save.SaveParameters)
	 */
	public void restore(SaveParameters save_config) throws SDXException {
		super.restore(save_config);
	}
}
