package search;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;

import chemaxon.jchem.db.JChemSearch;
import chemaxon.sss.SearchConstants;
import chemaxon.sss.search.JChemSearchOptions;
import chemaxon.util.ConnectionHandler;

/**
 * Example codes for filtering search results based on other (possibly not
 * chemical) database tables
 * 
 * @author Peter Kovacs
 * @author Tamas Csizmazia
 * @version 5.0.3, 04/26/2008
 * 
 */
public class SearchCombinedWithSqlQuery {

    private static final String stockTableName = "stock";
    private static final int maxQuantityOnStock = 10;

    /**
     * Creates a table which holds the amount in stock 
     * for each structure in the structure table. The stock table is created 
     * such that it can be joined with the structure table 
     * through the cd_id column.
     * 
     * @param connectionHandler
     * @param strTable
     * @throws SQLException
     */
    private static void createPopulateStockTable(
	    ConnectionHandler connectionHandler, String strTable)
    throws SQLException {

	Statement stmt = connectionHandler.getConnection().createStatement();
	try {
	    String sql = "drop table " + stockTableName;
	    stmt.execute(sql);
	} catch (SQLException sqlException) {
	    // The stock table doesn't exist yet
	} finally {
	    stmt.close();
	}
	stmt = connectionHandler.getConnection().createStatement();
	try {
	    String sql = "create table " + stockTableName
	    + " (cd_id numeric(10,0), " + "quantity numeric(10,2))";
	    stmt.execute(sql);
	} finally {
	    stmt.close();
	}

	stmt = connectionHandler.getConnection().createStatement();
	try {
	    String sql = "select cd_id from " + strTable;
	    ResultSet rs = stmt.executeQuery(sql);

	    Random r = new Random(System.currentTimeMillis());
	    String stockPopulatorSql = "insert into " + stockTableName
	    + " (cd_id, quantity) values(?, ?)";
	    PreparedStatement ps = connectionHandler.getConnection()
	    .prepareStatement(stockPopulatorSql);
	    try {
		while (rs.next()) {
		    int cdId = rs.getInt(1);
		    float qOnStock = (float) r.nextInt(10 * maxQuantityOnStock)
		    / (float) 10;
		    ps.setInt(1, cdId);
		    ps.setFloat(2, qOnStock);
		    ps.execute();
		}
	    } finally {
		ps.close();
	    }
	} finally {
	    stmt.close();
	}
    }

    public static void main(String[] args) {
	try {
	    SearchExampleBase.tableSetup();
	    
	    ConnectionHandler connectionHandler = Connection
	    	.getConnectionHandler(1);
	    String strTable = Util.structureTableName;

	    createPopulateStockTable(connectionHandler, strTable);

	    JChemSearchOptions jcSearchOptions = new JChemSearchOptions();
	    jcSearchOptions.setSearchType(SearchConstants.SUBSTRUCTURE);

	    // Include into the substructure search only the substances of
	    // which we have less then 2 (grams) in stock.
	    jcSearchOptions.setFilterQuery("select cd_id from "
		    + stockTableName + " where quantity < 2");

	    JChemSearch jChemSearch = InitializingSearch.createJChemSearch(
		    connectionHandler, "Brc1ccccc1", Util.structureTableName,
		    jcSearchOptions);

	    jChemSearch.run();
	    System.out.println(jChemSearch.getResultCount() 
		    + " hit(s) found");
	} catch (Throwable throwable) {
	    throwable.printStackTrace();
	}
    }
}