/*
 * Copyright 2006-2007 Queplix Corp.
 *
 * Licensed under the Queplix Public License, Version 1.1.1 (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.queplix.com/solutions/commercial-open-source/queplix-public-license/
 *
 * 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 com.queplix.core.modules.eql.parser.generic;

import com.queplix.core.integrator.security.LogonSession;
import com.queplix.core.jxb.entity.Dataset;
import com.queplix.core.jxb.entity.Efield;
import com.queplix.core.jxb.entity.Entity;
import com.queplix.core.jxb.entity.Listref;
import com.queplix.core.jxb.entity.types.JoinSType;
import com.queplix.core.modules.config.utils.EntityHelper;
import com.queplix.core.modules.eql.EQLDReq;
import com.queplix.core.modules.eql.EQLDRes;
import com.queplix.core.modules.eql.EQLERes;
import com.queplix.core.modules.eql.EQLObject;
import com.queplix.core.modules.eql.EQLReq;
import com.queplix.core.modules.eql.EQLReqField;
import com.queplix.core.modules.eql.EQLReqMetaData;
import com.queplix.core.modules.eql.EQLReqSelect;
import com.queplix.core.modules.eql.EQLReqSelectAttr;
import com.queplix.core.modules.eql.EQLRes;
import com.queplix.core.modules.eql.EQLResCell;
import com.queplix.core.modules.eql.EQLResRecord;
import com.queplix.core.modules.eql.EQLSession;
import com.queplix.core.modules.eql.EQLSql;
import com.queplix.core.modules.eql.ejb.EQLManagerLocal;
import com.queplix.core.modules.eql.ejb.EQLManagerLocalHome;
import com.queplix.core.modules.eql.error.EQLException;
import com.queplix.core.modules.eql.error.EQLSystemException;
import com.queplix.core.modules.eql.parser.SQLExecutor;
import com.queplix.core.modules.eql.utils.EQLUtils;
import com.queplix.core.utils.JNDINames;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;

/**
 * <p>SQL executor generic implementation class</p>
 *
 * @author [ALB] Baranov Andrey
 * @version $Revision: 1.2 $ $Date: 2006/01/20 15:19:10 $
 */

public class SQLExecutorGenericImpl
        extends SQLExecutor {

    // ----------------------------------------------------- CONSTANTS

    private final static int MAX_FEETCH_SIZE = 1000;

    public final static String PAGE_PARAM = "page";
    public final static String PAGE_SIZE_PARAM = "pageSize";
    public final static String HAS_MORE_PARAM = "hasMore";

    // ----------------------------------------------------- VARIABLES

    // cache for DatasetKeys objects
    private Map datasetKeysMap = Collections.synchronizedMap(new HashMap());

    // cache for list EQLReqField object
    private Map listReqFieldMap = Collections.synchronizedMap(new HashMap());

    // ----------------------------------------------------- PUBLIC OVERRIDED METHODS

    /*
     * No javadoc
     * @see SQLExecutor#doSelect
     */

    public EQLRes doSelect(EQLReq req, EQLSql eqlSql)
            throws EQLException {

        // Initialization
        String sql = eqlSql.getMainSql();
        EQLObject[] sqlParams = eqlSql.getSqlParameters();
        int datasets = eqlSql.datasetEqlSqlSize();
        Integer page = getPage(req);
        Integer pageSize = getPageSize(req);

        // Build new response object
        EQLRes res = newEQLRes(req, eqlSql);

        if(getLogger().isInfoEnabled()) {
            INFO(eqlSql.toString());
            DEBUG("      page=" + page);
            DEBUG("      pageSize=" + pageSize);
            DEBUG("      datasets=" + datasets);
        }

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        int isolationLevel = -1;
        try {
            // open sql connection
            con = getConnection();
            isolationLevel = con.getTransactionIsolation();

            if(sql != null) {
                //
                // Have SQL query - execute it
                //

                // make sql statement
                ps = getStatement(con, sql, sqlParams);

                // set fetch block size
                if(page != null && pageSize != null) {
                    int fetchSize = (page.intValue() + 1) * pageSize.intValue();
                    // not more than MAX_FEETCH_SIZE
                    if(fetchSize > MAX_FEETCH_SIZE) {
                        fetchSize = MAX_FEETCH_SIZE;
                    }
                    if(fetchSize > 0) {
                        // 0 -> retrive all records
                        // !0 -> always retrive one record more to detect
                        // if we have next result set
                        ps.setFetchSize(fetchSize + 1);
                    }
                }

                // execute query
                rs = sqlWrapper.executeQuery(ps);
            }

            // available mor records
            boolean availableMore = false;

            // rows retrived
            int rows = 0;

            // start and end positions in ResultSet
            int start = 0;
            int end = 0;
            if(page != null && pageSize != null) {
                start = page.intValue() * pageSize.intValue();
                end = start + pageSize.intValue();
            }

            // result set cursor
            ResultSetCursor cursor = new ResultSetCursor(rs);

            // current position in ResultSet
            int i = 0;
            while((rs != null && rs.next()) || (rs == null && i == 0)) {

                // check start position
                if(start > i) {
                    i++;
                    continue;
                }

                // check rows limit
                if(end > 0 && i >= end) {
                    // mark that we have more records...
                    availableMore = true;
                    break;
                }

                // read a record
                EQLResRecord record = getRecord(cursor, req);

                // read a datasets
                if(datasets > 0) {
                    for(int j = 0; j < datasets; j++) {
                        EQLDReq dReq = req.getDReq(j);
                        EQLSql datasetEqlSql = eqlSql.getDatasetEqlSql(j);
                        EQLDRes dRes = getDatasetData(con, dReq, datasetEqlSql,
                                record);
                        record.addDRes(dRes);
                    }
                }

                // add record to response
                res.addRecord(record);

                // reset cursor
                cursor.reset();

                rows++;
                i++;
            }

            if(getLogger().isDebugEnabled()) {
                DEBUG("Select completed: rows=" + rows + " more?="
                        + availableMore);
            }

            // put information into response meta data
            res.getMetaData().setParam(HAS_MORE_PARAM, new Boolean(
                    availableMore));

        } catch (SQLException ex) {
            Exception e = new EQLSystemException(
                    "Got SQL exception during execution:\n" +
                            sql + "\nIsolation level: " + isolationLevel,
                    ex);

            throw new EQLSystemException(ex.getMessage(), e);

        } finally {
            sqlWrapper.closeConnection(con, ps);
        }

        return res;
    }

    /*
     * No javadoc
     * @see SQLExecutor#doCount
     */
    public int doCount(EQLSql eqlSql)
            throws EQLException {

        String sql = eqlSql.getMainSql();
        if(sql == null) {
            // No SQL - always return 1
            return 1;
        }

        EQLObject[] sqlParams = eqlSql.getSqlParameters();

        // build full sql COUNT query
        String countSql = eqlSql.getCountSql();

        Connection con = null;
        PreparedStatement ps = null;
        int isolationLevel = -1;
        try {

            // open sql XA connection
            con = getConnection();
            isolationLevel = con.getTransactionIsolation();

            // make sql statement
            ps = getStatement(con, countSql, sqlParams);

            // execute statement
            ResultSet rs = sqlWrapper.executeQuery(ps);
            rs.next();
            return rs.getInt(1);

        } catch (SQLException ex) {
            Exception e = new EQLSystemException(
                    "Got SQL exception during execution:\n" +
                            sql + "\nIsolation level: " + isolationLevel,
                    ex);

            throw new EQLSystemException(ex.getMessage(), e);

        } finally {
            sqlWrapper.closeConnection(con, ps);
        }
    }

    /*
     * No javadoc
     * @see SQLExecutor#doUpdate
     */
    public int doUpdate(EQLERes res, EQLSql eqlSql)
            throws EQLException {

        String sql = eqlSql.getMainSql();
        if(getLogger().isInfoEnabled()) {
            INFO(eqlSql.toString());
        }

        // Execute SQL
        Connection con = null;
        PreparedStatement stat = null;
        int isolationLevel = -1;
        try {

            // open sql XA connection
            con = getConnection();
            isolationLevel = con.getTransactionIsolation();

            // make sql statement
            stat = getStatement(con, sql, eqlSql.getSqlParameters());

            // execute update query
            return sqlWrapper.executeUpdate(stat);

        } catch (SQLException ex) {

            throw new EQLSystemException(
                    "Got SQL exception during execution:\n" +
                            sql + "\nIsolation level: " + isolationLevel,
                    ex);
        } finally {
            sqlWrapper.closeConnection(con, stat);
        }
    }

    // ----------------------------------------------------- STATIC METHODS

    /**
     * Get page attribute
     *
     * @param req EQL request
     * @return int or NULL
     */
    public static final Integer getPage(EQLReq req) {

        EQLReqMetaData reqMetaData = req.getMetaData();
        if(reqMetaData == null) {
            return null;
        }

        Object o = reqMetaData.getParam(PAGE_PARAM);
        try {
            return (Integer) o;
        } catch (ClassCastException ex) {
            throw new EQLSystemException("Bad parameter '" + PAGE_PARAM +
                    "' = " + o);
        }
    }

    /**
     * Get page size attribute
     *
     * @param req EQL request
     * @return int or NULL
     */
    public static final Integer getPageSize(EQLReq req) {

        EQLReqMetaData reqMetaData = req.getMetaData();
        if(reqMetaData == null) {
            return null;
        }

        Object o = reqMetaData.getParam(PAGE_SIZE_PARAM);
        try {
            return (Integer) o;
        } catch (ClassCastException ex) {
            throw new EQLSystemException("Bad parameter '" + PAGE_SIZE_PARAM +
                    "' = " + o);
        }
    }

    // ----------------------------------------------------- PROTECTED METHODS

    //
    // Read one record from database
    //

    protected EQLResRecord getRecord(ResultSetCursor cursor, EQLReq req)
            throws EQLException {

        // create new record
        EQLReqSelect reqSelect = req.getSelect();
        int reqSelectSize = reqSelect.size();
        EQLResRecord record = new EQLResRecord(false, reqSelectSize);

        // select all fields
        int i = 0;
        while(i < reqSelectSize) {
            EQLReqSelectAttr reqSelectAttr = reqSelect.getAttr(i);
            EQLResCell resCell = getField(req, reqSelectAttr, cursor);
            record.addData(resCell, i);
            i++;
        }

        return record;
    }

    //
    // Read dataset <code>dReq</code> for given record <code>resRecord</code>
    //
    protected EQLDRes getDatasetData(Connection con,
                                     EQLDReq dReq,
                                     EQLSql datasetEqlSql,
                                     EQLResRecord resRecord)
            throws EQLException {

        // Init.
        EQLDRes dRes = null;
        String datasetSql = datasetEqlSql.getMainSql();
        EQLObject[] datasetSqlParams = datasetEqlSql.getSqlParameters();
        int params = (datasetSqlParams == null) ? 0:datasetSqlParams.length;

        // Get DatasetKeys object for given dataset.
        DatasetKeys dk = getDatasetKeys(dReq);

        PreparedStatement ps = null;
        try {

            // make sql statement
            ps = getStatement(con, datasetSql, datasetSqlParams);

            // build dataset EQL response object
            dRes = (EQLDRes) newEQLRes(dReq, datasetEqlSql);

            // set foreign keys
            String[] fKeyNames = dk.getFKeyNames();
            EQLReqField[] fkReqFields = dk.getFKeyReqFields();
            for(int k = 0; k < fKeyNames.length; k++) {
                EQLResCell resCell = resRecord.getResCell(fkReqFields[k]);
                ps.setObject(params + 1 + k,
                        resCell.getEQLObject().getObject());
            }

            // execute query
            ResultSet rs = sqlWrapper.executeQuery(ps);

            // result set cursor
            ResultSetCursor cursor = new ResultSetCursor(rs);

            // get data
            while(rs.next()) {
                EQLResRecord record = getRecord(cursor, dReq);
                dRes.addRecord(record);

                // reset cursor
                cursor.reset();
            }

        } catch (SQLException ex) {
            throw new EQLSystemException(ex);

        } finally {
            sqlWrapper.closeConnection(ps);
        }

        // Ok.
        return dRes;
    }

    //
    // Read one field (with listfield)
    //
    protected EQLResCell getField(EQLReq req,
                                  EQLReqSelectAttr reqSelectAttr,
                                  ResultSetCursor cursor)
            throws EQLException {

        EQLReqField reqField = reqSelectAttr.getReqField();
        boolean isLazy = req.isLazy(reqField.getField());

        EQLResCell resCell;
        if(reqSelectAttr.isConstant()) {
            EQLObject constObj = reqSelectAttr.getConstant();
            // filed value is constant
            resCell = new EQLResCell(reqField, constObj);
            cursor.next();
        } else {

            if(isLazy) {
                // field for lazy loading
                resCell = getLazyCell(req, reqField, cursor);

            } else {

                // retrive field value
                resCell = getCell(req, reqField, cursor);

            }
        }

        if (!isLazy) {
            // retrive listfield value
            Listref listref = reqField.getField().getListref();
            if(listref != null) {
                EQLReqSelectAttr listReqSelectAttr = reqSelectAttr
                            .getListFieldSelectAttr();
                EQLResCell listResCell = getListFieldValue(
                            req,
                            reqField,
                            listReqSelectAttr,
                            resCell.getEQLObject().getObject(),
                            cursor);

                resCell.addListField(listResCell);
            }
        }
        return resCell;
    }

    //
    // Read list field result cell
    //
    protected EQLResCell getListFieldValue(EQLReq req,
                                           EQLReqField masterReqField,
                                           EQLReqSelectAttr listReqSelectAttr,
                                           Object pkey,
                                           ResultSetCursor cursor)
            throws EQLException {

        Listref listref = masterReqField.getField().getListref();
        EQLResCell listResCell = null;

        // check list field JOIN type
        JoinSType joinType = listref.getJointype();
        switch(joinType.getType()) {

            case JoinSType.USE_CACHE_TYPE:

                // take value from cache
                if(pkey != null) {
                    EQLReqField listReqField = getListReqField(
                            masterReqField.getField(), listref);
                    Entity lrefEntity = listReqField.getReqEntity().getEntity();
                    Efield lrefField = listReqField.getField();
                    listResCell = getCachedCell(lrefEntity, lrefField, pkey);
                }
                break;

            default:

                // default procedure
                if(listReqSelectAttr != null) {
                    EQLReqField listReqField = listReqSelectAttr.getReqField();
                    listResCell = getCell(req, listReqField, cursor);
                }
        }

        return listResCell;
    }

    //
    // Read one column
    //
    protected EQLResCell getCell(EQLReq req,
                                 EQLReqField reqField,
                                 ResultSetCursor cursor)
            throws EQLException {

        Efield field = reqField.getField();
        int sqlType = field.getSqltype().getType();

        try {
            Object o = sqlWrapper.getParser(sqlType).
                    getObject(cursor.rs(), cursor.next());
            return new EQLResCell(reqField, EQLObject.getInstance(o));

        } catch (SQLException ex) {
            ERROR("Can't get cell for field '" + reqField.getField().getId() +
                    "' with type '" + field.getSqltype() + "'. Pos: " + cursor
                    .current(), ex);
            throw new EQLSystemException(ex);
        }
    }

    //
    // Lazy read one column
    //
    protected EQLResCell getLazyCell(EQLReq req,
                                     EQLReqField reqField,
                                     ResultSetCursor cursor)
            throws EQLException {

        Efield field = reqField.getField();
        int sqlType = field.getSqltype().getType();

        try {
            Object o = sqlWrapper.getParser(sqlType).
                    getObject(cursor.rs(), cursor.next());
            return new EQLResCell(reqField, (o == null));

        } catch (SQLException ex) {
            throw new EQLSystemException(ex);
        }
    }

    //
    // Build SQL Connection object
    //
    protected Connection getConnection() {
        // return XA connection
        return sqlWrapper.doConnection();
    }

    //
    // Build SQL PreparedStatement object
    //
    protected PreparedStatement getStatement(Connection con,
                                             String sql,
                                             EQLObject[] sqlParams)
            throws EQLException {

        PreparedStatement ps = sqlWrapper.doPreparedStatement(con, sql);
        if(sqlParams != null) {
            for(int i = 0; i < sqlParams.length; i++) {
                EQLObject param = sqlParams[i];

                if(getLogger().isDebugEnabled()) {
                    // Cut long string.
                    DEBUG("Set parameter '" + param.toShortString()
                            + "' in position #" + i +
                            " of statement");
                }

                try {
                    sqlWrapper.getParser(param.getSqlType()).
                            setObject(ps, i + 1, param.getObject());

                } catch (SQLException ex) {
                    throw new EQLSystemException(ex);
                }
            }
        }

        return ps;
    }

    //
    // Get DatasetKeys object for <code>datasetReq</code>.
    //
    protected DatasetKeys getDatasetKeys(EQLDReq dReq) {

        String key = dReq.getReqDataset().getDataset().getId();
        DatasetKeys dk = (DatasetKeys) datasetKeysMap.get(key);
        if(dk == null) {
            DEBUG("Create new DatasetKeys object for: " + key);
            dk = new DatasetKeys(getSession(), dReq);
            datasetKeysMap.put(key, dk);
        }

        return dk;
    }

    //
    // Get EQLReqField for list field
    //
    protected EQLReqField getListReqField(Efield masterField, Listref listref) {

        String key = masterField.getId();
        EQLReqField reqField = (EQLReqField) listReqFieldMap.get(key);
        if(reqField == null) {
            DEBUG("Create new EQLReqField object for: " + key);
            Entity lrefEntity = getSession().findEntity(listref.getEntity());
            Efield lrefField = EntityHelper.getEfield(listref.getEfield(),
                    lrefEntity);
            reqField = new EQLReqField(lrefEntity, lrefField);
            listReqFieldMap.put(key, reqField);
        }

        return reqField;
    }

    //
    // Get cached cell
    //
    protected EQLResCell getCachedCell(Entity entity, Efield field, Object pkey)
            throws EQLException {

        LogonSession ls = getSession().getLogonSession();
        EQLManagerLocal local = (EQLManagerLocal) getSession().getCOM().
                getLocalObject(JNDINames.EQLManager, EQLManagerLocalHome.class);

        return EQLUtils.getCachedCell(entity, field, pkey, ls, local);
    }

    // ----------------------------------------------------- INNER CLASSES

    /**
     * <p>Class - result set cursor</p>
     *
     * @author [ALB] Baranov Andrey
     * @version $Revision: 1.2 $ $Date: 2006/01/20 15:19:10 $
     */

    public static class ResultSetCursor {

        private ResultSet rs;
        private int pos = 1;

        public ResultSetCursor(ResultSet rs) {
            this.rs = rs;
        }

        public ResultSet rs() {
            return rs;
        }

        public int current() {
            return pos;
        }

        public int next() {
            return pos++;
        }

        public void reset() {
            pos = 1;
        }

    } //-- end inner class


    /**
     * <p>Class - special class to store dataset keys</p>
     *
     * @author [ALB] Baranov Andrey
     * @version $Revision: 1.2 $ $Date: 2006/01/20 15:19:10 $
     */

    public static class DatasetKeys {

        private Dataset baseDataset;
        private String[] fKeys;
        private EQLReqField[] fkReqFields;

        // Constructor: build array of EQLReqField foreign keys
        public DatasetKeys(EQLSession session, EQLDReq dReq) {

            baseDataset = dReq.getReqDataset().getDataset();
            Entity baseEntity = session.findEntity(baseDataset.getEntityName());

            fKeys = baseDataset.getDataschema().getTable(0).getKey();
            fkReqFields = new EQLReqField[fKeys.length];
            for(int k = 0; k < fKeys.length; k++) {
                Efield fkField = EntityHelper.getEfieldBySrc(fKeys[k],
                        baseEntity);
                fkReqFields[k] = new EQLReqField(baseEntity, fkField);
            }
        }

        public String[] getFKeyNames() {
            return fKeys;
        }

        public EQLReqField[] getFKeyReqFields() {
            return fkReqFields;
        }

        public int hashCode() {
            return baseDataset.getId().hashCode();
        }

        public boolean equals(Object o) {
            if(o == null || !(o instanceof DatasetKeys)) {
                return false;
            }

            DatasetKeys dk = (DatasetKeys) o;
            return dk.baseDataset.getId().equals(baseDataset.getId());
        }

    } //-- end inner class
}
