/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package org.apache.ode.bpel.extvar.jdbc;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import javax.swing.text.StyleContext.SmallAttributeSet;
import javax.xml.namespace.QName;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.ode.bpel.extvar.jdbc.DbExternalVariable.Column;
import org.apache.ode.bpel.extvar.jdbc.DbExternalVariable.RowKey;
import org.apache.ode.bpel.extvar.jdbc.DbExternalVariable.RowVal;
import org.apache.ode.utils.DOMUtils;
import org.apache.ode.utils.ObjectPrinter;
import org.apache.ode.bpel.evar.ExternalVariableModule;
import org.apache.ode.bpel.evar.ExternalVariableModuleException;
import org.apache.ode.bpel.evar.IncompleteKeyException;
import org.w3c.dom.Element;

public class JdbcExternalVariableModule implements ExternalVariableModule {

    private static final Logger __log = LoggerFactory.getLogger(JdbcExternalVariableModule.class);

    public static final String JDBC_NS = "http://ode.apache.org/externalVariables/jdbc";

    /** Unique QName for the engine, this should be the element used for the external-variable configuration. */
    public static final QName NAME = new QName(JDBC_NS, "jdbc");

    /** Manually configured data sources. */
    private final HashMap<String, DataSource> _dataSources = new HashMap<String, DataSource>();

    /** Variables we know about via configure() method calls. */
    private final HashMap<EVarId, DbExternalVariable> _vars = new HashMap<EVarId, DbExternalVariable>();

    public void configure(QName pid, String extVarId, Element config) throws ExternalVariableModuleException {
        EVarId evarId = new EVarId(pid, extVarId);
        DataSource ds = null;

        Element jndiDs = DOMUtils.findChildByName(config, new QName(JDBC_NS, "datasource-jndi"));
        Element jndiRef = DOMUtils.findChildByName(config, new QName(JDBC_NS, "datasource-ref"));
        Element initMode = DOMUtils.findChildByName(config, new QName(JDBC_NS, "init-mode"));
        if (jndiRef != null) {
            String refname = jndiRef.getTextContent().trim();
            ds = _dataSources.get(refname);
            if (ds == null)
                throw new ExternalVariableModuleException("Data source reference \"" + refname
                        + "\" not found for external variable " + evarId
                        + "; make sure to register the data source with the engine!");
        } else if (jndiDs != null) {
            String name = jndiDs.getTextContent().trim();
            Object dsCandidate;
            InitialContext ctx;
            try {
                ctx = new InitialContext();
            } catch (Exception ex) {
                throw new ExternalVariableModuleException("Unable to access JNDI context for external variable " + evarId, ex);
            }

            try {
                dsCandidate = ctx.lookup(name);
            } catch (Exception ex) {
                throw new ExternalVariableModuleException("Lookup of data source for " + evarId + "  failed.", ex);
            } finally {
                try {
                    ctx.close();
                } catch (NamingException e) { /* ignore */ }
            }

            if (dsCandidate == null)
                throw new ExternalVariableModuleException("Data source \"" + name + "\" not found in JNDI!");

            if (!(dsCandidate instanceof DataSource))
                throw new ExternalVariableModuleException("JNDI object \"" + name + "\" does not implement javax.sql.DataSource");

            ds = (DataSource) dsCandidate;
        }

        if (ds == null) {
            throw new ExternalVariableModuleException("No valid data source configuration for JDBC external varible " + evarId);
        }

        Connection conn = null;
        DatabaseMetaData metaData;
        try {
            conn = ds.getConnection();
            metaData = conn.getMetaData();
        } catch (Exception ex) {
            try {
                if (conn != null) conn.close();
            } catch (SQLException e) {
                // ignore
            }
            throw new ExternalVariableModuleException("Unable to open database connection for external variable " + evarId, ex);
        }


        try {
            DbExternalVariable dbev = new DbExternalVariable(evarId, ds);
            if (initMode != null)
                try {
                    dbev._initType = InitType.valueOf(initMode.getTextContent().trim());
                } catch (Exception ex) {
                    throw new ExternalVariableModuleException("Invalid <init-mode> value: " + initMode.getTextContent().trim());
                }

            Element tableName = DOMUtils.findChildByName(config, new QName(JDBC_NS, "table"));
            if (tableName == null || tableName.getTextContent().trim().equals(""))
                throw new ExternalVariableModuleException("Must specify <table> for external variable " + evarId);
            String table = tableName.getTextContent().trim();
            String schema = null;
            if (table.indexOf('.') != -1) {
                schema = table.substring(0, table.indexOf('.'));
                table = table.substring(table.indexOf('.') + 1);
            }

            if (metaData.storesLowerCaseIdentifiers()) {
                table = table.toLowerCase();
                if (schema != null)
                    schema = table.toLowerCase();
            } else if (metaData.storesUpperCaseIdentifiers()) {
                table = table.toUpperCase();
                if (schema != null)
                    schema = schema.toUpperCase();
            }

            dbev.generatedKeys = metaData.supportsGetGeneratedKeys();
            ResultSet tables = metaData.getTables(null, schema, table, null);
            if (tables.next()) {
                dbev.table = tables.getString("TABLE_NAME");
                dbev.schema = tables.getString("TABLE_SCHEM");
            } else
                throw new ExternalVariableModuleException("Table \"" + table + "\" not found in database.");

            tables.close();

            List<Element> columns = DOMUtils.findChildrenByName(config, new QName(JDBC_NS, "column"));

            for (Element col : columns) {
                String name = col.getAttribute("name");
                String colname = col.getAttribute("column-name");
                String key = col.getAttribute("key");
                String gentype = col.getAttribute("generator");
                String expression = col.getAttribute("expression");

                if (key == null || "".equals(key))
                    key = "no";
                if (gentype == null || "".equals(gentype))
                    gentype = GenType.none.toString();
                if (colname == null || "".equals(colname))
                    colname = name;

                if (name == null || "".equals(name))
                    throw new ExternalVariableModuleException("External variable " + evarId
                            + " <column> element must have \"name\" attribute. ");

                if (metaData.storesLowerCaseIdentifiers())
                    colname = colname.toLowerCase();
                else if (metaData.storesUpperCaseIdentifiers())
                    colname = colname.toUpperCase();

                GenType gtype;
                try {
                    gtype = GenType.valueOf(gentype);
                } catch (Exception ex) {
                    throw new ExternalVariableModuleException("External variable " + evarId + " column \"" + name
                            + "\" generator type \"" + gentype + "\" is unknown.");

                }

                if (gtype == GenType.expression && (expression == null || "".equals(expression)))
                    throw new ExternalVariableModuleException("External variable " + evarId + " column \"" + name
                            + "\" used \"expression\" generator, but did not specify an expression");

                Column c = dbev.new Column(name, colname, key.equalsIgnoreCase("yes"), gtype, expression);
                ResultSet cmd = metaData.getColumns(null, dbev.schema, dbev.table, colname);
                try {
                    if (cmd.next()) {
                        c.dataType = cmd.getInt("DATA_TYPE");
                        c.nullok = cmd.getInt("NULLABLE") != 0;
                    } else
                        throw new ExternalVariableModuleException("External variable " + evarId + " referenced "
                            + "non-existant column \"" + colname + "\"!");
                } finally {
                    cmd.close();
                }

                dbev.addColumn(c);
            }

            if (dbev.numColumns() == 0)
                throw new ExternalVariableModuleException("External variable " + evarId + " did not have any <column> elements!");

            _vars.put(evarId, dbev);
        } catch (SQLException se) {
            throw new ExternalVariableModuleException("SQL Error", se);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
    }

    public QName getName() {
        return NAME;
    }

    public boolean isTransactional() {
        return true;
    }

    public void shutdown() {
    }

    public void start() {
    }

    public void stop() {
    }

    public Value writeValue(QName varType, Value newval) throws ExternalVariableModuleException {
        EVarId evarId = new EVarId(newval.locator.pid, newval.locator.varId);
        DbExternalVariable evar = _vars.get(evarId);
        if (evar == null)
            throw new ExternalVariableModuleException("No such variable. "); // todo

        RowKey key = evar.keyFromLocator(newval.locator);
        RowVal val = evar.parseXmlRow(evar.new RowVal(), (Element) newval.value);
        if (__log.isDebugEnabled())
            __log.debug("JdbcExternalVariable.writeValue() RowKey: " + key + " RowVal: " + val);

        if (!key.missingValues() && evar._initType == InitType.delete_insert) {
            // do delete...
            throw new ExternalVariableModuleException("Delete not implemented. "); // todo
        }

        // should we try an update first? to do this we need to have all the required keys
        // and there should be some keys
        boolean tryupdatefirst = (evar._initType == InitType.update || evar._initType == InitType.update_insert)
                && !evar._keycolumns.isEmpty() && !key.missingDatabaseGeneratedValues();

        boolean insert = evar._initType != InitType.update;

        if (__log.isDebugEnabled())
            __log.debug("tryUpdateFirst: " + tryupdatefirst
                        + " insert: " + insert
                        + " initType: " + evar._initType
                        + " key.isEmpty: " + evar._keycolumns.isEmpty()
                        + " key.missingValues: " + key.missingValues()
                        + " key.missingDBValues: " + key.missingDatabaseGeneratedValues());

        try {
            if (tryupdatefirst)
                insert = execUpdate(evar, key, val) == 0;
            if (insert) {
                key = execInsert(evar, newval.locator, key, val);
                // Transfer the keys obtained from the db.
                key.write(varType, newval.locator);
            }
        } catch (SQLException se) {
            throw new ExternalVariableModuleException("Error updating row.", se);
        }

        return newval;
    }

    public Value readValue(QName varType, Locator locator) throws ExternalVariableModuleException {
        EVarId evarId = new EVarId(locator.pid, locator.varId);
        DbExternalVariable evar = _vars.get(evarId);
        if (evar == null)
            throw new ExternalVariableModuleException("No such variable: "+evarId);

        Element val;
        try {
            RowVal rowval = execSelect(evar, locator);
            val = evar.renderXmlRow(locator, varType, rowval);
        } catch (SQLException se) {
            throw new ExternalVariableModuleException("SQL Error.", se);
        }

        return new Value(locator, val, null);
    }

    /**
     * Manually register a data source. Handy if you don't want to use JNDI to look these up.
     *
     * @param dsName
     * @param ds
     */
    public void registerDataSource(String dsName, DataSource ds) {
        _dataSources.put(dsName, ds);
    }

    int execUpdate(DbExternalVariable dbev, RowKey key, RowVal values) throws SQLException {
        Connection conn = dbev.dataSource.getConnection();
        PreparedStatement stmt = null;
        try {
            if (__log.isDebugEnabled()) {
                __log.debug("execUpdate: key=" + key + " values=" + values);
                __log.debug("Prepare statement: " + dbev.update);
            }
            stmt = conn.prepareStatement(dbev.update);
            int idx = 1;
            for (Column c : dbev._updcolumns) {
                Object val = values.get(c.name);
                if (__log.isDebugEnabled()) __log.debug("Set value parameter "+idx+": "+val);
                if (val == null)
                    stmt.setNull(idx, c.dataType);
                else
                    stmt.setObject(idx, downcastValue(val, c.dataType));
                idx++;
            }

            for (Column ck : dbev._keycolumns) {
                Object val = key.get(ck.name);
                if (__log.isDebugEnabled()) __log.debug("Set key parameter "+idx+": "+val);
                if (val == null)
                    stmt.setNull(idx, ck.dataType);
                else
                    stmt.setObject(idx, downcastValue(val, ck.dataType));
                idx++;
            }
            return stmt.executeUpdate();
        } finally {
            if (stmt != null) stmt.close();
            try {
                conn.close();
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    private Object downcastValue(Object value, int dataType) {
        if (value == null) {
            return null;
        }
        // Try down casting the value as per its column type.
        try {
            // Some JDBC 4.0 types have been ignored to avoid compilation errors
            switch (dataType) {
            case Types.ARRAY:
                break;
            case Types.BIGINT:
                if (!(value instanceof BigInteger)) {
                    value = new BigDecimal(value.toString()).longValue();
                }
                break;
            case Types.BINARY:
                break;
            case Types.BIT:
                if (!(value instanceof Boolean)) {
                    value = new Boolean(value.toString());
                }
                break;
            case Types.BLOB:
                break;
            case Types.BOOLEAN:
                if (!(value instanceof Boolean)) {
                    value = new Boolean(value.toString());
                }
                break;
            case Types.CHAR:
                break;
            case Types.CLOB:
                break;
            case Types.DATALINK:
                break;
            case Types.DATE:
                break;
            case Types.DECIMAL:
                //ODE-872: Oracle 9g and 10g has problems with BigDecimal on Java1.5
                value = new BigDecimal(new BigDecimal(value.toString()).toPlainString());
                break;
            case Types.DISTINCT:
                break;
            case Types.DOUBLE:
                if (!(value instanceof Double)) {
                    value = Double.valueOf(value.toString()).doubleValue();
                }
                break;
            case Types.FLOAT:
                if (!(value instanceof Float)) {
                    value = Float.valueOf(value.toString()).floatValue();
                }
                break;
            case Types.INTEGER:
                if (!(value instanceof Integer)) {
                    value = Double.valueOf(value.toString()).intValue();
                }
                break;
            case Types.JAVA_OBJECT:
                break;
//	    	case Types.LONGNVARCHAR:
//	    		break;
            case Types.LONGVARBINARY:
                break;
            case Types.LONGVARCHAR:
                break;
//	    	case Types.NCHAR:
//	    		break;
//	    	case Types.NCLOB:
//	    		break;
            case Types.NUMERIC:
                //ODE-872: Oracle 9g and 10g has problems with BigDecimal on Java1.5
                value = new BigDecimal(new BigDecimal(value.toString()).toPlainString());
                break;
//	    	case Types.NVARCHAR:
//	    		break;
            case Types.OTHER:
                break;
            case Types.REAL:
                if (!(value instanceof Double)) {
                    value = Float.valueOf(value.toString()).floatValue();
                }
                break;
            case Types.REF:
                break;
//	    	case Types.ROWID:
//	    		break;
            case Types.SMALLINT:
                if (!(value instanceof Short)) {
                    value = new Short(value.toString()).shortValue();
                }
                break;
//	    	case Types.SQLXML:
//	    		break;
            case Types.STRUCT:
                break;
            case Types.TIME:
                break;
            case Types.TIMESTAMP:
                break;
            case Types.TINYINT:
                if (!(value instanceof Short)) {
                    value = new Short(value.toString()).shortValue();
                }
                break;
            case Types.VARBINARY:
                break;
            case Types.VARCHAR:
                break;
            default:
                break;
            }
        } catch (Exception e) {
            // couldn't cast... let's just use original value object
        }
        return value;
    }

    RowVal execSelect(DbExternalVariable dbev, Locator locator) throws SQLException, ExternalVariableModuleException {
        RowKey rowkey = dbev.keyFromLocator(locator);
        if (__log.isDebugEnabled()) __log.debug("execSelect: " + rowkey);

        if (rowkey.missingDatabaseGeneratedValues()) {
            return null;
        }

        if (rowkey.missingValues()) {
            throw new IncompleteKeyException(rowkey.getMissing());
        }

        RowVal ret = dbev.new RowVal();
        Connection conn = dbev.dataSource.getConnection();
        PreparedStatement stmt = null;
        try {
            if (__log.isDebugEnabled()) __log.debug("Prepare statement: " + dbev.select);
            stmt = conn.prepareStatement(dbev.select);
            int idx = 1;
            for (Object k : rowkey) {
                if (__log.isDebugEnabled()) __log.debug("Set key parameter "+idx+": "+k);
                stmt.setObject(idx++, k);
            }

            ResultSet rs = stmt.executeQuery();
            try {
                if (rs.next()) {
                    for (Column c : dbev._columns)  {
                        Object val;
                        int i = c.idx+1;
                        if (c.isDate()) val = rs.getDate(i);
                        else if (c.isTimeStamp()) val = rs.getTimestamp(i);
                        else if (c.isTime()) val = rs.getTime(i);
                        else if (c.isInteger()) val = new Long(rs.getLong(i));
                        else if (c.isReal()) val = new Double(rs.getDouble(i));
                        else if (c.isBoolean()) val = new Boolean(rs.getBoolean(i));
                        else val = rs.getObject(i);
                        if (__log.isDebugEnabled()) __log.debug("Result column index "+c.idx+": "+val);
                        ret.set(c.idx,val);
                    }
                } else
                    return null;
            } finally {
                rs.close();
            }
        } finally {
            if (stmt != null) stmt.close();
            try {
                conn.close();
            } catch (SQLException e) {
                // ignore
            }
        }

        return ret;
    }

    RowKey execInsert(DbExternalVariable dbev, Locator locator, RowKey keys, RowVal values) throws SQLException {
        Connection conn = dbev.dataSource.getConnection();
        PreparedStatement stmt = null;
        try {
            if (__log.isDebugEnabled()) {
                __log.debug("execInsert: keys=" + keys + " values=" + values);
                __log.debug("Prepare statement: " + dbev.insert);
                __log.debug("missingDatabaseGeneratedValues: " + keys.missingDatabaseGeneratedValues());
                __log.debug("_autoColNames: " + ObjectPrinter.stringifyNvList(dbev._autoColNames));
            }

            stmt = keys.missingDatabaseGeneratedValues()
                ? conn.prepareStatement(dbev.insert, dbev._autoColNames)
                : conn.prepareStatement(dbev.insert);

            int idx = 1;
            for (Column c : dbev._inscolumns) {
                Object val = c.getValue(c.name, keys, values, locator.iid);
                values.put(c.name, val);
                if (__log.isDebugEnabled()) __log.debug("Set parameter "+idx+": "+val);
                if (val == null)
                    stmt.setNull(idx, c.dataType);
                else
                    stmt.setObject(idx, val);
                idx++;
            }

            stmt.execute();

            for (Column ck : keys._columns) {
                Object val = values.get(ck.name);
                if (__log.isDebugEnabled()) __log.debug("Key "+ck.name+": "+val);
                keys.put(ck.name,val);
            }

            if (keys.missingDatabaseGeneratedValues() ) {
                // With JDBC 3, we can get the values of the key columns (if the db supports it)
                ResultSet keyRS = stmt.getGeneratedKeys();
                try {
                    if (keyRS == null)
                        throw new SQLException("Database did not return generated keys");
                    keyRS.next();
                    for (Column ck : keys._columns) {
                        Object value = keyRS.getObject(ck.idx+1);
                        if (__log.isDebugEnabled()) __log.debug("Generated key "+ck.name+": "+value);
                        keys.put(ck.name, value);
                    }
                } finally {
                    keyRS.close();
                }
            }
            return keys;
        } finally {
            if (stmt != null) stmt.close();
            try {
                conn.close();
            } catch (SQLException e) {
                // ignore
            }
        }
    }

}