/*
 * ECatalog is a database front-end, with two main features:
 * 1. Use of preferences
 *  A preference-based approach, where the user is allowed to define the importance of each criterion.
 *  Then the items are ranked accordingly to his criteria.
 * 2. Trade-off analysis
 *  A cooperative database approach, where the system "argues" with the user about his criteria.
 *  When there are no matching items, the system explains the minimal conflicting set and
 *  give some possible strong and weak relaxations about his criteria.
 * This package also containts the software and the set-up details used for our User Study,
 * comparing the use or not of the two previous features mentioned above.
 *
 * Copyright (C) 2006 David Portabella Clotet, Artificial Intelligence Laboratory, EPFL
 * 
 * This file is part of ecatalog-1.0.zip
 * 
 * ECatalog is free software and a free user study set-up;
 * you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 * 
 * ECatalog 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 General Public License
 * along with ECatalog; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 * 
 * @version 1.0
 * @author David Portabella
 * To contact the author:
 * email: david@portabella.name and david.portabella@epfl.ch
 * 
 * More information about ECatalog:
 *  http://sourceforge.net/projects/ecatalog/
 *  http://icwww.epfl.ch/~portabel/ecatalogs/
 */

package ecatalog.db;

import ecatalog.ECatalogLog;

import java.sql.*;
import java.util.Vector;
import java.util.Iterator;
import java.util.HashMap;
import java.util.StringTokenizer;
import ecatalog.jaxb.ecatalogConfig.ECatalogConfigType;
import ecatalog.jaxb.ecatalogConfig.AttributeType;

import org.apache.bsf.BSFManager;
import org.apache.bsf.BSFException;
//import org.mozilla.javascript.*;

import dpc.utils.SqlUtil;
import dpc.utils.DebugUtil;

/* The Database class
 * Database manages the database and the constraints/preferences selected by the user.
 *
 * An example (for the config file, use preferencesAllowed=false)
 *
 * INITIALIZATION:
 * Database db = new Database();
 * db.init(config, log);
 * Attribute[] attributes = db.getAttributes();
 *
 * // Print the attributes
 * for (Attribute at : attributes)
 *   System.out.println("Attribute. Label: " + at.getLabel());
 *
 * // Add a "Equal" constraint for each attribute:
 * // To know more about the different types of results, look at ecatalog.gui.criteriaSelection.CriteriaSelectionGui.createConstraintGui()
 * EqualConstraint constraints = new EqualConstraint[attributes.length];
 * for (int i = 0; i < attributes.length; i++)
 *   constraints[i] = new EqualConstraint(attributes[i], db, WeightType.MUST, null);
 *
 * // Getting all the possible values for an attribute:
 * at.getNbrDistinctValues();           // the number of distinc values
 * at.getValueString(int valueIndex);   // get the String for a value, given its index [0, distinctValues-1]
 *
 * USING:
 * // Set a value for a constraint (the user set a value to one of the equal constraint)
 * constraint[i].setValueIndex(valueIndex);
 * constraint[i].removeDetails();
 *
 * constraint[i].areDetailsDefined();
 * ((SimpleAttributeConstraint) constraint[i]).getValueIndex();
 *
 *
 * // Get all the matching items
 * int nbrItems = SqlUtil.getUniqueInt(db.executeQuery("SELECT COUNT(*) FROM " + db.getFromClause() + " WHERE " + db.getMustOnlyConstraintsClause()));
 * String query = "SELECT " + db.getAttributesClause() + " FROM " + db.getFromClause() + " WHERE " + db.getMustOnlyConstraintsClause();
 * if (!preferencesAllowed && sortAttributeIndex != -1)
 *    query += " ORDER BY " + db.getAttribute(sortAttributeIndex).getId() 
 *          + ((sortDirection == ItemTableModel.SortDirection.ASCENDING) ? " ASC" : " DESC");
 * ResultSet rs = db.executeQuery(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
 * // and show the results...
 * 
 * ANALYSIS:
 * DBAnalysis analysis = new DBAnalysis(db, db.getConstraints());
 * DBAnalysis.PossibleRelaxations possibleRelaxations = analysis.getPossibleRelaxations(relaxationListMaxSize, maxNbrOfCombinedRelaxations, "There are items <b>if you relax</b> ", "-1 ");
 * DBAnalysis.MinimalUnsolvableSubproblem minimalUnsolvableSubproblem = analysis.getMinimalUnsolvableSubproblem(minimalUnsolvableSubproblemMaxSize);
 * print the message: possibleRelaxations.text and minimalUnsolvableSubproblem.text
 *
 * //To execute a possible relaxation:
 * DBAnalysis.executeRelaxation(relaxationCode, possibleRelaxations, minimalUnsolvableSubproblem, log);
 */

public class Database {
    String fromClause, whereClause;
    String attributesClause;
    Connection conn;

    int totalNbrSolutions;
    int currentNbrSolutions;
    
    int nbrAttributes;
    Attribute[] attributes;
    Vector<Constraint> constraints;
    HashMap<String, Integer>attributeIdMap;

    ECatalogConfigType config;
    ECatalogLog log;
    //BSFManager bsfmanager;  // used to execute script code

    public String getFromClause() { return fromClause; }
    public String getWhereClause() { return whereClause; }
    public String getAttributesClause() { return attributesClause; }
    public int getTotalNbrSolutions() { return totalNbrSolutions; }
    public int getCurrentNbrSolutions() { return currentNbrSolutions; }

    public int getNbrAttributes() { return nbrAttributes; }

    public Attribute getAttribute(int index) { 
	return attributes[index]; 
    }

    public Attribute getAttributeById(String id) { 
	Integer index = attributeIdMap.get(id);
	if (index == null)
	    return null;
	return attributes[index]; 
    }

    public Attribute[] getAttributes() { return attributes; }

    public int getNbrConstraints() { return constraints.size(); }
    public Constraint getConstraint(int index) { return constraints.get(index); }
    public Vector<Constraint> getConstraints() { return constraints; }


    public boolean isItemCompatible(Item item) throws SQLException {
	for (Constraint c : constraints)
	    if (!c.doesItemSatisfyConstraint(item))
		return false;
	return true;
    }

    /* Init the database. log can be null */
    public void init(ECatalogConfigType config, ECatalogLog log) throws Exception {
	this.config = config;
	this.log = log;

	// Initialize the script code features
	scriptInit();

	// Initialize the Database
	Class.forName("org.gjt.mm.mysql.Driver").newInstance();
	Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();

	//Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
	conn = DriverManager.getConnection(config.getDBURL());

	fromClause = config.getFromClause();
	whereClause = config.getWhereClause();
	if (whereClause == null)
	    whereClause = "1=1";
	else
	    whereClause = "(" + whereClause + ")";

	// Initialize the attributes
	nbrAttributes = config.getAttribute().size();
	attributes = new Attribute[nbrAttributes];
	attributeIdMap = new HashMap<String, Integer>();
	int i = 0;
	for (Iterator it = config.getAttribute().iterator(); it.hasNext(); ) {
	    AttributeType attributeConfig = (AttributeType) it.next();
	    String type = attributeConfig.getType();
	    Attribute attribute;

	    if (type == null || "text".equals(type)) {
		attribute = new TextAttribute(attributeConfig, this, i);
	    } else if ("number".equals(type)) {
		attribute = new NumberAttribute(attributeConfig, this, i);
	    } else if ("boolean".equals(type)) {
		attribute = new BooleanAttribute(attributeConfig, this, i);
	    } else {
		throw new Exception("attribute type not known");
	    }

	    attribute.init();
	    attributes[i] = attribute;
	    attributeIdMap.put(attribute.getId(), new Integer(i));
	    i++;
	}

	// Construct the attributesClause
	attributesClause = attributes[0].getId();
	for (i = 1; i < nbrAttributes; i++)
	    attributesClause += ", " + attributes[i].getId();

	// Get the total number of solutions
	totalNbrSolutions = SqlUtil.getUniqueInt(executeQuery("SELECT COUNT(*) FROM " + fromClause + " WHERE " + whereClause));

	// Initialize the constraints
	constraints = new Vector<Constraint>();
    }

    public void addConstraint(Constraint c) {
	constraints.add(c);
    }

    public void removeConstraint(Constraint c) {
	constraints.remove(c);
    }

    
    public void updateContext() throws SQLException {
	currentNbrSolutions = SqlUtil.getUniqueInt(executeQuery("SELECT COUNT(*) FROM " + fromClause + " WHERE " + getConstraintsClause()));

	//update attributes context
	for (Attribute at : attributes)
	    at.update();

	//set attributes example
	if (currentNbrSolutions > 0) {
	    String query = "SELECT " + attributesClause + " FROM " + fromClause + " WHERE " + getConstraintsClause();
	    ResultSet rsExample = executeQuery(query, 1);
	    if (!rsExample.next()) {
		throw new Error("overconstrained. cannot retrieve an example");
	    }

	    updateExample(new ResultSetItem(this, rsExample));
	}

	//update constraints context
	for (Constraint c : constraints)
	    c.update();
    }

    /* Updates the example from the current row in the provided ResultSet */
    public void updateExample(Item item) throws SQLException {
	for (Attribute at : attributes)
	    at.updateExample(item);
    }


    /** Returns a SQL WHERE clause from among all the constraints which have the details defined,
     *  ignoring the type of weight (MUST, PREFER...)
     */
    public String getConstraintsClause() {
	String clause = whereClause;
	for (Constraint c : constraints) {
	    if (c.areDetailsDefined() == false)
		continue;
	    clause += " AND " + c.getSqlClauseTerm();
	}
	return clause;
    }


    /** Returns a SQL WHERE clause from among all the constraints which have the details defined,
     *  ignoring the type of weight (MUST, PREFER...)
     *  and excluding the one provided as a parameter
     */
    public String getConstraintsClauseExcept(Constraint constraintException) {
	String clause = whereClause;
	for (Constraint c : constraints) {
	    if (c == constraintException || c.areDetailsDefined() == false)
		continue;
	    clause += " AND " + c.getSqlClauseTerm();
	}
	return clause;
    }

    /** Returns a SQL WHERE clause from among all the constraints which have the details defined,
     *  and the weight is of type MUST 
     */
    public String getMustOnlyConstraintsClause() {
	String clause = whereClause;
	for (Constraint c : constraints) {
	    if (c.areDetailsDefined() == false || c.getWeight().getWeightValue() != -1)
		continue;
	    clause += " AND " + c.getSqlClauseTerm();
	}
	return clause;
    }
    
    public ResultSet executeQuery(String query) throws SQLException {
	//DebugUtil.printTrace(2, query, false);
	return conn.createStatement().executeQuery(query);
    }

    public ResultSet executeQuery(String query, int maxRows) throws SQLException {
	//DebugUtil.printTrace(2, query, false);
	Statement stmt = conn.createStatement();
	stmt.setMaxRows(maxRows);
	return stmt.executeQuery(query);
    }

    public ResultSet executeQuery(String query, int resultSetType, int resultSetConcurrency) throws SQLException {
	//DebugUtil.printTrace(2, query, false);
	return conn.createStatement(resultSetType, resultSetConcurrency).executeQuery(query);
    }

    public ResultSet executeQuery(String query, int maxRows, int resultSetType, int resultSetConcurrency) throws SQLException {
	//DebugUtil.printTrace(2, query, false);
	Statement stmt = conn.createStatement(resultSetType, resultSetConcurrency);
	stmt.setMaxRows(maxRows);
	return stmt.executeQuery(query);
    }


    BSFManager bsfmanager2;  // used to execute script code
    void scriptInit() throws Exception {
	bsfmanager2 = new BSFManager();
	bsfmanager2.declareBean("utils", new ScriptUtils(), ScriptUtils.class);        

	String initFunction = config.getInitFunction();
	if (initFunction != null)
	    bsfmanager2.exec("javascript", "Test", 0, 0, initFunction);
    }

    public Object scriptEval(String function) throws Exception {
	Object result = bsfmanager2.eval("javascript", "Test", 0, 0, function);
	return result;
    }


    /*
    Context cx;
    Scriptable scope;
    public void scriptInit() throws Exception {
	cx = Context.enter();
	scope = cx.initStandardObjects();

	Object jsOut = Context.javaToJS(new ScriptUtils(), scope);
	ScriptableObject.putProperty(scope, "utils", jsOut);


	String initFunction = ecatalog.getConfig().getInitFunction();
	if (initFunction != null) {
	    //System.out.println("initFunction: " + initFunction);
	    cx.evaluateString(scope, initFunction, "<cmd>", 1, null);
	}

	//cx = null;  //to close
    }

    public Object scriptEval(String function) throws Exception {
	//System.out.println("function: " + function);
	Object result = cx.evaluateString(scope, function, "<cmd>", 1, null);
	return result;
    }
    */
}