/*
 *  Copyright 2010 Anyware Services
 *
 *  Licensed 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.ametys.plugins.workflow;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import org.ametys.core.datasource.ConnectionHelper;

import com.opensymphony.module.propertyset.PropertySet;
import com.opensymphony.workflow.StoreException;
import com.opensymphony.workflow.query.Expression;
import com.opensymphony.workflow.query.NestedExpression;

/**
 * JDBC implementation for PropertySet without JNDI.<br>
 * Use a cocoon connection pool instead.
 */
public class JDBCPropertySet extends com.opensymphony.module.propertyset.database.JDBCPropertySet
{
    /** Prefix for osworkflow entries */
    public static final String OSWF_PREFIX = "oswf_";

    private static final String __TABLE_NAME = "OS_PROPERTYENTRY";

    private static final String __GLOBAL_KEY_COL = "GLOBAL_KEY";

    private static final String __ITEM_KEY_COL = "ITEM_KEY";

    private static final String __ITEM_TYPE_COL = "ITEM_TYPE";

    private static final String __STRING_COL = "STRING_VALUE";
    
    // For logging
    private static Log __log = LogFactory.getLog(JDBCPropertySet.class);
    
    /** datasource */
    protected DataSource _ds;
    
    @Override
    public void init(Map config, Map args)
    {
        // args
        globalKey = (String) args.get("globalKey");

        // config
        _ds = (DataSource) config.get("datasource");
        
        tableName = __TABLE_NAME;
        colGlobalKey = __GLOBAL_KEY_COL;
        colItemKey = __ITEM_KEY_COL;
        colItemType = __ITEM_TYPE_COL;
        colString = __STRING_COL;
        colDate = "DATE_VALUE";
        colData = "DATA_VALUE";
        colFloat = "FLOAT_VALUE";
        colNumber = "NUMBER_VALUE";
    }

    @Override
    protected Connection getConnection() throws SQLException
    {
        // Si faux consomme tout le pool de connexions
        closeConnWhenDone = true;
        // Ne pas utiliser JNDI mais un pool de connexions de cocoon
        return _ds.getConnection();
    }

    @Override
    protected void cleanup(Connection connection, Statement statement, ResultSet result)
    {
        ConnectionHelper.cleanup(result);
        ConnectionHelper.cleanup(statement);

        if (closeConnWhenDone)
        {
            ConnectionHelper.cleanup(connection);
        }
    }

    /**
     * Test if an expression contains only nested PropertySet expression.
     * @param expr The nested expression to test.
     * @return <code>true</code> if the expression contains only PropertySet expression, <code>false</code> otherwise.
     * @throws StoreException If an error occurs.
     */
    public static boolean isPropertySetExpressionsNested(NestedExpression expr) throws StoreException
    {
        int count = expr.getExpressionCount();

        for (int i = 0; i < count; i++)
        {
            Expression e = expr.getExpression(i);

            if (e instanceof NestedExpression)
            {
                if (!isPropertySetExpressionsNested((NestedExpression) e))
                {
                    // Il y a une expression imbriqué qui n'est pas de type PropertySet
                    return false;
                }
            }
            else if (!(e instanceof PropertySetExpression))
            {
                // Il y a une FieldExpression
                return false;
            }
        }

        return true;
    }

    /**
     * Process a query from an osworkflow expression.
     * @param ds The datasource
     * @param e The expression.
     * @return The result as a List of Long (Workflow ID).
     * @throws StoreException If an error occurs.
     */
    public static List query(DataSource ds, Expression e) throws StoreException
    {
        List<String> values = new LinkedList<>();
        StringBuffer query = new StringBuffer();

        query.append("SELECT DISTINCT ");
        query.append(__GLOBAL_KEY_COL);
        query.append(" FROM ");
        query.append(__TABLE_NAME);
        query.append(" WHERE ");
        query.append(_getCondition(ds, e, values));

        return __doExpressionQuery(ds, query.toString(), values);
    }

    private static String _getCondition(DataSource ds, Expression e, List<String> values) throws StoreException
    {
        if (e instanceof NestedExpression)
        {
            NestedExpression nestedExpr = (NestedExpression) e;
            int operator = nestedExpr.getExpressionOperator();

            if (operator != NestedExpression.AND && operator != NestedExpression.OR)
            {
                throw new StoreException("Invalid nested operator " + operator);
            }

            StringBuffer condition = new StringBuffer();

            if (nestedExpr.isNegate())
            {
                condition.append("NOT ");
            }

            condition.append("(");

            int count = nestedExpr.getExpressionCount();

            for (int i = 0; i < count; i++)
            {
                Expression expr = nestedExpr.getExpression(i);

                // Ajouter la condition sur l'expression courante
                condition.append(_getCondition(ds, expr, values));

                if (i + 1 != count)
                {
                    if (operator == NestedExpression.AND)
                    {
                        condition.append(" AND ");
                    }
                    else
                    {
                        condition.append(" OR ");
                    }
                }
            }

            condition.append(")");

            return condition.toString();
        }
        else if (e instanceof PropertySetExpression)
        {
            return _getCondition((PropertySetExpression) e, values);
        }
        else
        {
            throw new StoreException("Unsupported expression with mixed expressions");
        }
    }

    private static String _getCondition(PropertySetExpression e, List<String> values) throws StoreException
    {
        String value = null;
        String columnName;

        if (e.getType() == PropertySet.STRING)
        {
            columnName = __STRING_COL;

            if (!(e.getValue() instanceof String))
            {
                throw new StoreException("Query with value: " + e.getValue().getClass() + " is not supported");
            }

            value = (String) e.getValue();
        }
        else
        {
            throw new StoreException("Query of type: " + e.getType() + " is not supported");
        }

        StringBuffer query = new StringBuffer("(");

        query.append(__ITEM_KEY_COL);
        query.append(" = ? AND ");
        query.append(columnName);

        switch (e.getOperator())
        {
            case PropertySetExpression.EQUALS:
                if (e.isNegate())
                {
                    query.append(" <> ");
                }
                else
                {
                    query.append(" = ");
                }
                break;
            case PropertySetExpression.WILDCARD_EQUALS:
                if (e.isNegate())
                {
                    query.append(" NOT LIKE ");
                }
                else
                {
                    query.append(" LIKE ");
                }

                // Remplacer les jokers * par %
                value = value.replace('*', '%');
                break;
            default:
                throw new StoreException("Query with operator: " + e.getOperator() + " is not supported");
        }

        query.append("?)");

        // Ajouter la clé à la liste
        values.add(e.getKey());
        // Ajouter la valeur de test à la liste
        values.add(value);

        return query.toString();
    }

    private static List __doExpressionQuery(DataSource ds, String query, List values) throws StoreException
    {
        List<Long> results = new ArrayList<>();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try
        {
            // Ne pas utiliser JNDI mais un pool de connexions de cocoon
            con = ds.getConnection();
            stmt = con.prepareStatement(query);

            for (int i = 0; i < values.size(); i++)
            {
                stmt.setObject(i + 1, values.get(i));
            }

            if (__log.isDebugEnabled())
            {
                __log.debug("Query is: " + query + "\n" + values);
            }

            rs = stmt.executeQuery();

            while (rs.next())
            {
                Long workflowID = __getWorkflowID(rs.getString(1));

                if (workflowID != null)
                {
                    results.add(workflowID);
                }
            }

            return results;
        }
        catch (SQLException ex)
        {
            throw new StoreException("SQL Exception in query: " + query, ex);
        }
        finally
        {
            ConnectionHelper.cleanup(rs);
            ConnectionHelper.cleanup(stmt);
            ConnectionHelper.cleanup(con);
        }
    }

    private static Long __getWorkflowID(String globalKey)
    {
        if (globalKey == null)
        {
            return null;
        }

        if (globalKey.startsWith(OSWF_PREFIX))
        {
            return new Long(globalKey.substring(OSWF_PREFIX.length()));
        }

        return null;
    }
}