package com.fp.bpm.query;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

import org.hibernate.Criteria;
import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;

import com.fp.common.helper.BeanManager;
import com.fp.dto.AbstractDataTransport;
import com.fp.dto.Response;
import com.fp.dto.hb.HibernateBean;
import com.fp.dto.query.QueryBean;
import com.fp.dto.query.QueryCriteria;
import com.fp.dto.query.QueryRequest;
import com.fp.persistence.commondb.PersistenceHelper;
import com.fp.persistence.commondb.db.DataHelper;
import com.fp.persistence.commondb.exception.CommondbException;
import com.fp.persistence.pgeneral.gene.TgeneCatalogDetail;

/**
 * Motor de consultas generico.
 * @author Jorge Vaca
 * @version 2.1
 */
public class QueryHQL {

    public QueryHQL() {
    }

    /**
     * Metodo que se encarga de ejecutar consultas genericas.<br>
     * Proceso.<br>
     * <ul>
     * <li>Obtiene una lista de QueryBean del Objeto QueryRequest </li>
     * <li>Ejecuta la consulta para cada QueryBean</li>
     * <li>Llena la respuesta en el response.</li>
     * </ul>
     * @param pReq
     * @return  boolean
     * @throws CommondbException
     * @throws ClassNotFoundException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws Exception
     */
    public boolean process(QueryRequest pQueryRequest) throws CommondbException, ClassNotFoundException, InstantiationException, IllegalAccessException, Exception {
        Response response = pQueryRequest.getResponse();
        Map<String, QueryBean> bean = pQueryRequest.getBeans();
        for (String k : bean.keySet()) {
            QueryBean qbean = bean.get(k);
            response.put(k, this.processByQueryBean(qbean));
        }
        return true;
    }

    /**
     * Metodo que ejecuta una consulta por QueryBean. <br>
     * Si el QueryType asociado al QueryBean es E,  Crea y entrega un objeto vacio. 
     * @param pQueryBean  Datos del QueryBean
     * @return  Object
     * @throws CommondbException
     * @throws ClassNotFoundException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws Exception
     */
    private Object processByQueryBean(QueryBean pQueryBean) throws CommondbException, ClassNotFoundException, InstantiationException, IllegalAccessException, Exception {
        if (pQueryBean.getQuerytype() != null && pQueryBean.getQuerytype().compareTo("E") == 0) {
            if (pQueryBean.isMultirecord()) {
                List<Object> data = new ArrayList<Object>();
                data.add(this.createObject(pQueryBean));
                return data;
            }
            return this.createObject(pQueryBean);
        }
        Criteria cri = this.prepareCriteria(pQueryBean);
        return this.queryData(cri, pQueryBean);
    }

    /**
     * Metodo que ejecuta la consulta a la base de datos.<br> 
     * Si es un multiregistro obtiene un resultset se recorre y entraga la respuesta, casocontarrio busca un solo
     * objeto en la base. 
     * @param pCriteria  Criteria con la cual se obtiene los datos de la base. 
     * @param pQueryBean  Datos del objeto de consulta. 
     * @return  Object
     * @throws CommondbException
     * @throws ClassNotFoundException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws Exception
     */
    private Object queryData(Criteria pCriteria, QueryBean pQueryBean) throws CommondbException, ClassNotFoundException, InstantiationException, IllegalAccessException, Exception {
        String searchCatalogDetail = (String) pQueryBean.get("catalogdesc");
        String[] nameAttributes = null;
        if (searchCatalogDetail != null) {
            nameAttributes = searchCatalogDetail.split("\\*");
        }

        if (pQueryBean.isMultirecord()) {
            List<Object> data = new ArrayList<Object>();
            ScrollableResults scroll = pCriteria.scroll(ScrollMode.FORWARD_ONLY);
            boolean exists = false;
            while (scroll.next()) {
                exists = true;
                Object[] record = scroll.get();
                data.add(this.fillRecord(record, nameAttributes));
            }
            if (!exists && pQueryBean.getPage() == 1) {
                Object obj = this.createObject(pQueryBean);
                data.add(obj);
            }
            return data;
        } else {
            AbstractDataTransport obj = (AbstractDataTransport) pCriteria.uniqueResult();
            this.completeDescriptionCatalog(obj, nameAttributes);
            return obj;
        }
    }

    /**
     * Metodo que transforma los campos de un registro a un AbstractDataTransport.
     * @param pRecord  Datos de un registro obtenidos de la base de datos. 
     * @param nameAttributes  Atributos adicionales con los cuales se completa la descripcion del catalogo. 
     * @return  Object.
     * @throws Exception
     */
    private Object fillRecord(Object[] pRecord, String[] nameAttributes) throws Exception {
        if (pRecord == null) {
            return null;
        }
        if (pRecord.length == 1) {
            AbstractDataTransport obj = (AbstractDataTransport) pRecord[0];
            this.completeDescriptionCatalog(obj, nameAttributes);
            return obj;
        } else {
            Map<String, Object> m = new HashMap<String, Object>();
            return m;
        }
    }

    /**
     * Metodo que procesa y adiciona los distintos criterios al bean.
     * @param pQueryBean  Objeto al cual se adicionan las restricciones de consulta.
     * @return	Criteria
     * @throws CommondbException
     * @throws ClassNotFoundException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */    
    @SuppressWarnings("rawtypes")
	private Criteria prepareCriteria(QueryBean pQueryBean) throws CommondbException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        Object beanReference = Class.forName(pQueryBean.getBeanName()).newInstance();
        Criteria c = PersistenceHelper.getSession().createCriteria(pQueryBean.getBeanName());

        for (QueryCriteria criteria : pQueryBean.getCriteria()) {
            if (criteria.getOperation() == null) {
                continue;
            }
            Class type = null;
            try {
                type = BeanManager.getBeanGetterMethod(beanReference, criteria.getProperty()).getReturnType();
            } catch (Exception e1) {
            }
            c.add(this.fillCriterionByCriteria(criteria, type));
        }

        for (QueryCriteria order : pQueryBean.getOrder(true)) {
            c.addOrder(Order.asc(order.getProperty()));
        }
        for (QueryCriteria order : pQueryBean.getOrder(false)) {
            c.addOrder(Order.desc(order.getProperty()));
        }
        if (pQueryBean.getPage() > 0 && pQueryBean.getPageSize() > 0) {
            c.setMaxResults(pQueryBean.getPageSize());
            c.setFirstResult((pQueryBean.getPage() - 1) * pQueryBean.getPageSize());
        }        
        return c;
    }

    /**
     * Metodo que crea un Criterion de restriccion a asociar al HQL.
     * @param pCriteria  Objto que contiene las condiciones del criterio. 
     * @param pType  Tipo de dato del campo a adicionar la restriccion. 
     * @return  Criterion
     * @throws CommondbException
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    private Criterion fillCriterionByCriteria(QueryCriteria pCriteria, Class pType) throws CommondbException {
        Property prop = Property.forName(pCriteria.getProperty());
        Object value = pCriteria.getValue();
        try {
            value = BeanManager.convertObject(pCriteria.getValue(), pType);
        } catch (Exception e) {
        }
        if (pCriteria.getOperation().compareTo("<") == 0) {
            return prop.lt(value);
        }
        if (pCriteria.getOperation().compareTo(">") == 0) {
            return prop.gt(value);
        }
        if (pCriteria.getOperation().compareTo("<=") == 0) {
            return prop.le(value);
        }
        if (pCriteria.getOperation().compareTo(">=") == 0) {
            return prop.ge(value);
        }
        if (pCriteria.getOperation().compareTo("=") == 0) {
            return prop.eq(value);
        }
        if (pCriteria.getOperation().compareTo("<>") == 0) {
            return prop.ne(value);
        }
        if (pCriteria.getOperation().compareTo("like") == 0) {
            return prop.like(value);
        }
        if (pCriteria.getOperation().compareTo("isNull") == 0) {
            return prop.isNull();
        }
        if (pCriteria.getOperation().compareTo("isNotNull") == 0) {
            return prop.isNotNull();
        }
        if (pCriteria.getOperation().compareTo("in") == 0) {
            //
            if (pType.getName().compareTo("java.lang.String") == 0) {
                String val = (String) pCriteria.getValue();
                StringTokenizer st = new StringTokenizer(val, ",");
                List<String> param = new ArrayList<String>();
                while (st.hasMoreElements()) {
                    String object = (String) st.nextElement();
                    param.add(object.substring(1, (object.length() - 1)));
                }
                return prop.in(param);
            } else if (pType.getName().compareTo("java.lang.Integer") == 0) {
                String val = (String) pCriteria.getValue();
                StringTokenizer st = new StringTokenizer(val, ",");
                List<Integer> param = new ArrayList<Integer>();
                while (st.hasMoreElements()) {
                    String object = (String) st.nextElement();
                    if (object.startsWith("'") || object.startsWith("\"")) {
                        object = object.substring(1, (object.length() - 1));
                    }
                    param.add(Integer.parseInt(object));
                }
                return prop.in(param);
            } else {
                return prop.in((Collection) pCriteria.getValue());
            }

        }
        if (pCriteria.getOperation().compareTo("between") == 0) {
            Object[] param = ((Collection) pCriteria.getValue()).toArray();
            return prop.between(param[0], param[1]);
        }
        if (pCriteria.getOperation().compareTo("sql") == 0) {
            return Restrictions.sqlRestriction((String) value);
        }
        throw new CommondbException("QRY001", "OPERADOR {0} NO DISPONIBLE", pCriteria.getOperation());
    }

    /**
     * Metodo que crea un objeto de tipo beanname asociado al QueryBean.
     * @param pQueryBean  Datos del QueryBean
     * @return  Object
     */
    private Object createObject(QueryBean pQueryBean) {
        try {
            HibernateBean obj = (HibernateBean) Class.forName(pQueryBean.getBeanName()).newInstance();
            try {
                Object id = BeanManager.getBeanAttributeValue(obj, "pk");
                if (id == null) {
                    Object key = Class.forName(pQueryBean.getBeanName() + "Key").newInstance();
                    BeanManager.setBeanAttributeValue(obj, "pk", key);
                }
            } catch (Exception e) {
                return obj;
            }
            return obj;

        } catch (Exception e) {
            return null;
        }
    }

    /**
     * Metodo que se encarga de completar descripciones de catalogo.
     * @param pObject  Objeto al cual se completa las descripciones de catalogo. 
     * @param nameAttributes  Arreglo con los nombres de catalogo a buscar descripciones ejemlo
     * [baserate,calculationbase]  
     * @throws Exception
     */
    private void completeDescriptionCatalog(AbstractDataTransport pObject, String[] nameAttributes) throws Exception {
        if (nameAttributes == null) {
            return;
        }
        for (int i = 0; i < nameAttributes.length; i++) {
            if (nameAttributes[i].startsWith("pk_")) {
                nameAttributes[i] = nameAttributes[i].replaceAll("_", ".");
            }
            String descCatalog = "catalog";
            String descCatalogCode = "catalogcode";
            if (nameAttributes[i].compareTo("pk.catalog") == 0 || nameAttributes[i].compareTo("catalog") == 0) {
                descCatalog = "";
                descCatalogCode = "code";
            }
            if (pObject == null) {
                break;
            }
            String catalog = (String) BeanManager.getBeanAttributeValue(pObject, nameAttributes[i] + descCatalog);
            String catalogcode = (String) BeanManager.getBeanAttributeValue(pObject, nameAttributes[i] + descCatalogCode);
            String nameNewAtribute = nameAttributes[i] + "desc";
            if (nameNewAtribute.startsWith("pk.")) {
                nameNewAtribute = nameNewAtribute.substring(3, nameNewAtribute.length());
            }
            if (catalog != null && catalogcode != null) {
                TgeneCatalogDetail detail = DataHelper.getInstance().getTgeneCatalogDetail(catalog, catalogcode);
                pObject.addAddtionalInfo(nameNewAtribute, detail.getDescription());
            } else {
                pObject.addAddtionalInfo(nameNewAtribute, null);
            }
        }
    }
}