package org.jcon.df.request;

import org.jcon.df.column.ColumnDef;
import org.jcon.df.Entity;
import org.jcon.df.Schema;
import org.jcon.df.request.*;
import org.jcon.util.DataLib;
import org.jcon.util.GenLib;
import java.util.Vector;

/**
 * Represents a particular type of database translator,
 * such as Sybase, Oracle, MSAccess. It translates
 * logical requests to database dependent requests, such
 * as into SQL.
 * <p>
 * NOTE - "SysUser.LastName Like H*" fails on MSAccess
 * through JDBC but works fine from within Access. 3/19/98 JH
 * To fake "Like H*" use: LastName >= H AND LastName < I
 *
 * @author Jack Harich
 */
public class Translator implements java.io.Serializable {

//---------- Private Fields ------------------------------
private Schema schema;
private TranslatorType translatorType;
private static final boolean monitorOn = true;

//---------- Properties ----------------------------------
public void setSchema(Schema schema) {
    this.schema = schema;
}
public void setTranslatorType(TranslatorType translatorType) {
    this.translatorType = translatorType;
}
public TranslatorType getTranslatorType() {
    return translatorType;
}
//---------- Public Methods ------------------------------
//----- The CRUD fearsome foursome
// Note each buildXXXClause is "" or has a space afterward.

// "INSERT INTO TableName (MID, Code) VALUES ($123, 'ABC')"
public String buildInsertStm(DataRequest request) {
    String primeEntityName = request.getPrimeEntity();
    String sql = "INSERT INTO " + primeEntityName + " (";
    sql += buildInsertColumnNames(request) + ") VALUES (";
    formatColumnValues(request.getInsertColumns());
    sql += buildInsertValues(request) + ")";
    checkMonitor(sql);
    return sql;
}
public String buildSelectStm(DataRequest request) {
    String sql = "SELECT ";
    sql += buildColumnsClause(request);
    sql += buildFromClause(request);
    sql += buildWhereClause(request);
    sql += buildSortByClause(request).trim();
    checkMonitor(sql);
    return sql;
}
// "UPDATE TableName SET Age=35, Code='ABC' WHERE MID=$123"
public String buildUpdateStm(DataRequest request) {
    String sql = "UPDATE ";
    sql += request.getPrimeEntity() + " ";
    sql += buildUpdateColumns(request);
    sql += buildWhereClause(request).trim();
    checkMonitor(sql);
    return sql;
}
public String buildDeleteStm(DataRequest request) {
    String sql = "DELETE ";
    sql += buildFromClause(request);
    sql += buildWhereClause(request).trim();
    checkMonitor(sql);
    return sql;
}
//----- Data reader utilities
public String buildCountStm(DataRequest request) {
    String sql = "SELECT Count(*) ";
    sql += buildFromClause(request);
    sql += buildWhereClause(request).trim();
    checkMonitor(sql);
    return sql;
}
// SELECT MAX FROM TName WHERE...
public String buildAggregateStm(DataRequest request) {
    String sql = "SELECT ";

    String aggType = request.getAggregateType();
    String expression = request.getAggregateExpression();
    sql += translatorType.getAggregateClause(
        aggType, expression).trim() + " ";

    sql += buildFromClause(request);
    sql += buildWhereClause(request).trim();
    checkMonitor(sql);
    return sql;
}
//----- Schema utilities
public String buildDropTableStm(DataRequest request) {
    String sql = "DROP TABLE " + request.getPrimeEntity();
    checkMonitor(sql);
    return sql;
}
/**
 * Returns a create table String. SQL example:
 * "CREATE TABLE TableName (MID money, Code varchar(254))"
 */
public String buildCreateTableStm(DataRequest request) {
    Entity entity = schema.getEntity(request.getPrimeEntity());
    String sql = "CREATE TABLE " + entity.getName() + " (";

    // Build "ColumnName datatype," elements
    Vector columnNames = entity.getColumnNamesInLogicalOrder();
    for (int i = 0; i < columnNames.size(); i++) {
        String columnName = (String)columnNames.elementAt(i);
        ColumnDef def = entity.getColumnDef(columnName);

        String datatype = translatorType
            .getDatatype(def.getType());

        sql += columnName + " " + datatype;

        if (columnName != "MID" && def.getType() != "Boolean") {
            // This is "" for Access, " null" for Sybase
            String nullAllowed = translatorType.getNullAllowed();
            if (nullAllowed != null) nullAllowed = " " + nullAllowed;
            sql += nullAllowed;
        }
        if (i < columnNames.size() - 1) sql += ", ";
    }
    sql += ")";
    checkMonitor(sql);
    return sql;
}
public String buildCreateUniqueIndexStm(DataRequest request) {
    Entity entity = schema.getEntity(request.getPrimeEntity());
    String entityName = entity.getName();

    String sql =  "CREATE UNIQUE INDEX PrimaryKey ON "
        + entityName + " (MID)";

    checkMonitor(sql);
    return sql;
}
//---------- Private Methods -----------------------------
private void checkMonitor(String sql) {
    if (monitorOn) print(" SQL: " + sql + "\n");
}
private String buildColumnsClause(DataRequest request) {
    Vector columnIDs = request.getColumnIDs();
    return DataLib.convertVectorToDelimString(columnIDs, ", ").trim() + " ";
}
// See page 9-17 for Sybase UPDATE syntax. Example:
// "UPDATE TableName SET Age=35, Code='ABC' WHERE MID=$123"
// This method builds the "SET Age=35, Code='ABC' " part
private String buildUpdateColumns(DataRequest request) {
    String primeEntityName = request.getPrimeEntity();
    Vector columnExps = request.getUpdateColumns();
    formatColumnValues(columnExps);
    String clause = "SET ";
    int count = columnExps.size();

    for (int i = 0; i < count; i++) {
        ColumnExp columnExp = (ColumnExp)columnExps.elementAt(i);
        // Include only columns in prime entity
        String entityName = columnExp.getEntityName();
        if (entityName.equalsIgnoreCase(primeEntityName)) {
            clause += columnExp.getFormattedExpression() + ", ";
        }
    }
    // Remove last comma
    clause = clause.substring(0, clause.length() - 2);
    return clause + " ";
}
private String buildFromClause(DataRequest request) {
    Vector entityNames = request.getEntities();
    String clause = DataLib.convertVectorToDelimString(entityNames, ", ").trim();
    return "FROM " + clause + " ";
}
private String buildWhereClause(DataRequest request) {
    if (! request.isFiltered() && ! request.isJoined()) return "";

    // Prepare joinClause and filterClause
    String joinClause = "";
    if (request.isJoined()) {
        Vector joins = request.getJoins();
        for (int i = 0; i < joins.size(); i++) {
            joinClause += (String)joins.elementAt(i);
            if (i < joins.size() - 1) joinClause += " AND ";
        }
    }
    String filterClause = "";
    if (request.isFiltered()) {
        Filter filter = request.getFilter();
        filter.setFirstEntityIfMissing(request.getPrimeEntity());
        formatColumnValues(filter.getExpressions());
        filterClause = filter.buildClause();
    }
    // Build clause
    String clause = "WHERE ";
    if (joinClause != "" && filterClause != "") {
        clause += joinClause + " AND " + filterClause;

    } else if (joinClause != "") {
        clause += joinClause;

    } else if (filterClause != "") {
        clause += filterClause;
    }
    //print(".buildWhereClause() - built " + clause);
    return clause + " ";
}
private String buildSortByClause(DataRequest request) {
    String sortBy = request.getSortBy();
    if (sortBy == null) {
        return "";
    } else {
        return "ORDER BY " + sortBy + " ";
    }
}
private void formatColumnValues(Vector columnExps) {
    for (int i = 0; i < columnExps.size(); i++) {
        ColumnExp exp = (ColumnExp)columnExps.elementAt(i);
        String columnDefType = schema.getColumnDefType(
            exp.getColumnID());
//if (exp.getValue() == null) print(".formatColumnValues() - "
//+ columnDefType + " is null");
        String formattedValue = translatorType.
            formatToSQL(columnDefType, exp.getValue());
//if (exp.getValue() == null) print(".formatColumnValues() - "
//+ formattedValue + " = '" + formattedValue + "'");
        exp.setFormattedValue(formattedValue);
    }
}
// Note these are ColumnName, not EntityName.ColumnName
// *** see F1 ClientStore.getAddRowSQL() for insert format
private String buildInsertColumnNames(DataRequest request) {
    String primeEntityName = request.getPrimeEntity();
    String names = "";
    Vector columnExps = request.getInsertColumns();
    int count = columnExps.size();
    for (int i = 0; i < count; i++) {
        ColumnExp exp = (ColumnExp)columnExps.elementAt(i);
        // Include only columns in prime entity
        String entityName = exp.getEntityName();
        if (entityName.equalsIgnoreCase(primeEntityName)) {
            names += exp.getColumnName() + ", ";
        }
    }
    // Remove last comma
    names = names.substring(0, names.length() - 2);
    return names;
}
private String buildInsertValues(DataRequest request) {
    String primeEntityName = request.getPrimeEntity();
    String values = "";
    Vector columnExps = request.getInsertColumns();
    int count = columnExps.size();
    for (int i = 0; i < count; i++) {
        ColumnExp exp = (ColumnExp)columnExps.elementAt(i);
        // Include only columns in prime entity
        String entityName = exp.getEntityName();
        if (entityName.equalsIgnoreCase(primeEntityName)) {
            values += exp.getFormattedValue() + ", ";
        }
    }
    // Remove last comma
    values = values.substring(0, values.length() - 2);
    return values;
}
//--- Std
private static void print(String text) {
    System.out.println("Translator" + text);
}

} // End class
