/*
 * Copyright
 *   2007 axYus - www.axyus.com
 *   2005 C.Marchand - christophe.marchand@axyus.com
 *
 * This file is part of XEMELIOS.
 *
 * XEMELIOS is free software; you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * XEMELIOS is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with XEMELIOS; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

package fr.gouv.finances.cp.xemelios.data.impl.mysql;

import fr.gouv.finances.cp.xemelios.common.Constants;
import fr.gouv.finances.cp.xemelios.data.DocumentInfos;
import fr.gouv.finances.cp.xemelios.data.impl.MySqlDataLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.LinkedList;
import java.util.NoSuchElementException;
import java.util.StringTokenizer;

import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathFactory;

import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import fr.gouv.finances.cp.utils.Amount;
import fr.gouv.finances.cp.utils.Pair;
import fr.gouv.finances.cp.utils.xml.xpath.FunctionResolver;
import fr.gouv.finances.cp.xemelios.common.config.ChampModel;
import fr.gouv.finances.cp.xemelios.common.config.ElementModel;
import fr.gouv.finances.cp.xemelios.common.config.EtatModel;
import fr.gouv.finances.cp.xemelios.common.config.ListeResultatModel;
import fr.gouv.finances.cp.xemelios.data.DataAccessException;
import fr.gouv.finances.cp.xemelios.data.DataConfigurationException;
import fr.gouv.finances.cp.xemelios.data.DataResultSet;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TDocument;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TElement;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TEtat;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TSqlIndex;
import fr.gouv.finances.cp.xemelios.ui.resulttable.CachedData;
import fr.gouv.finances.cp.xemelios.ui.resulttable.DataHandler;

/**
 * MySql specific implementation of {@link fr.gouv.finances.cp.xemelios.data.DataResultSet}
 * @author chm
 */
public class MySqlDataResultSet implements DataResultSet {
    private static Logger logger = Logger.getLogger(MySqlDataResultSet.class);
    // TODO : set cache size from outside parameters
    private static final int CACHE_SIZE = 1;
    
//    private DocumentModel documentModel = null;
    private EtatModel etatModel = null;
    private ElementModel elementModel = null;
    private Pair collectivite=null, budget=null;
    private String xPath = null, sql = null;
    private String whereClause = "", fromClause = "", selectClause = "";
    private MySqlDataLayer implementation = null;
    private int pageSize=0;
    private int currentPage = 0;
    private ResultSet rs = null;
    private boolean hasNextPage=false, hasPreviousPage=false;
    private int estimatedSize = 0;
    private int estimatedPageCount = -1;
    private int estimatedMainElementSize = 0;
    
    // environment
    private ListeResultatModel lrm = null;
    private CachedData cache = null;
    private DocumentCache documentCache = null;
    
    // received data
    private ArrayList<String> docIds = null;
    
    // iterating data
    private int currentDocIndex = -1;
    private NodeList currentList = null;
    private int currentItemInList = -1;
    private boolean hasNextItem = false;
    private String currentDocId=null;
    
    private XPathFactory xpf = null;
    
    public MySqlDataResultSet(ElementModel elementModel,Pair collectivite,Pair budget,String xPath,String sql,ListeResultatModel lrm,MySqlDataLayer impl) throws SQLException, DataConfigurationException {
        this();
        this.elementModel=elementModel;
        this.etatModel=elementModel.getParent();
//        this.documentModel=etatModel.getParent();
        this.collectivite=collectivite;
        this.budget=budget;
        this.xPath=xPath;
        this.sql=sql;
        this.lrm=lrm;
        this.implementation=impl;
        this.pageSize=elementModel.getMaxDisplay();
        try {
            xpf = XPathFactory.newInstance("http://java.sun.com/jaxp/dom");
//    		xpf = XPathFactory.newInstance();
        } catch(Throwable t) {
            xpf = XPathFactory.newInstance();
        }
        xpf.setXPathFunctionResolver(new FunctionResolver());
        if(lrm==null)
            logger.debug("lrm is null");
        else if(lrm.getTriDefaut()==null)
            logger.debug("lrm.getTriDefaut() id null");
        loadIds();
    }
    
    public int getCount() {
        return docIds.size();
    }
    public int getEstimatedSize() {
        return estimatedSize;
    }
    public MySqlDataResultSet() {
        super();
        documentCache = new DocumentCache(CACHE_SIZE);
    }
    protected void loadIds() throws SQLException, DataConfigurationException {
        cleanAllCaches();
        StringBuilder currentRequest = new StringBuilder(sql);
        
        selectClause = sql.substring(0, sql.indexOf("FROM"));
        fromClause = sql.substring(sql.indexOf("FROM"), sql.indexOf("WHERE"));
        whereClause = sql.substring(sql.indexOf("WHERE"));
        
        TDocument spc = implementation.getPersistenceConfig(etatModel.getParent()).getLayer(implementation.getLayerName()).getDocument(etatModel.getParent().getId());
        TEtat sEtatc = spc.getEtat(etatModel.getId());
        TElement sec = sEtatc.getElement(elementModel.getId());
        if(sec!=null && sec.getSpecialCond()!=null) {
            // check if special cond references a table that is not in FROM clause
            String sCond = sec.getSpecialCond().trim();
            // for the moment, all special conds are TABLE.COLUMN IS NOT NULL like
            String tableName = sCond.substring(0,sCond.indexOf('.'));
            if(fromClause.indexOf(tableName)<0) {
                // so, we add it
                String sTmp = fromClause.substring("FROM".length()).trim();
                String mainTableName = null;
                if(sTmp.indexOf(' ')>=0) mainTableName = sTmp.substring(0,sTmp.indexOf(' ')).trim();
                else mainTableName = sTmp;
                StringBuffer sb = new StringBuffer(fromClause);
                sb.append(" LEFT OUTER JOIN ").append(tableName).append(" ON ").append(mainTableName).append(".DOC_ID=").append(tableName).append(".DOC_ID AND ").append(mainTableName).append(".COLLECTIVITE=").append(tableName).append(".COLLECTIVITE AND ").append(mainTableName).append(".BUDGET=").append(tableName).append(".BUDGET");
                fromClause = sb.toString();
                currentRequest = new StringBuilder();
                currentRequest.append(selectClause).append(fromClause).append(" ").append(whereClause);
            }
            currentRequest.append(" AND ").append(sCond);
        }
        if(lrm!=null && lrm.getTriDefaut()!=null) {
            String tris = lrm.getTriDefaut();
            String ordres = lrm.getOrdre();
            
            StringTokenizer trisTokenizer = new StringTokenizer(tris,",");
            StringTokenizer ordresTokenizer = new StringTokenizer(ordres,",");
            if(currentRequest.indexOf("ORDER BY")<0) {
                currentRequest.append(" ORDER BY ");
            } else {
                // correction bug 278
                currentRequest.append(", ");
            }
            while(trisTokenizer.hasMoreElements()) {
                String tri = trisTokenizer.nextToken();
                String ordre = ordresTokenizer.hasMoreTokens()?ordresTokenizer.nextToken():"ASC";
                if(ordre==null || ordre.length()==0) ordre="ASC";
                ChampModel cm = lrm.getChamps().get(tri);
                if(cm!=null) {
                    // index pathes are not prefixed, so drop any prefixes
                    String path = elementModel.getPath().getPath()+"/"+cm.getPath().getPath();
                    path = path.replaceAll("[a-zA-Z0-9]:","");
                    try {
                        TSqlIndex si = sEtatc.getSqlIndexByPath(path);
                        if(si==null) {
                            logger.error("index not found for path "+path+"\n won't sort !");
                            continue;
                        }
                        currentRequest.append(sEtatc.getIndexTable(si.getTableId())).append(".").append(si.getColumn()).append(" ").append(ordre).append(",");
                        
                        // check if table name is in from clause
                        String tableName = sEtatc.getIndexTable(si.getTableId()).getBaseName();
                        if(fromClause.indexOf(tableName)<0) {
                            // so, we add it
                            String sTmp = fromClause.substring("FROM".length()).trim();
                            String mainTableName = null;
                            if(sTmp.indexOf(' ')>=0) mainTableName = sTmp.substring(0,sTmp.indexOf(' ')).trim();
                            else mainTableName = sTmp;
                            StringBuffer sb = new StringBuffer(fromClause);
                            sb.append(" LEFT OUTER JOIN ").append(tableName).append(" ON ").append(mainTableName).append(".DOC_ID=").append(tableName).append(".DOC_ID AND ").append(mainTableName).append(".COLLECTIVITE=").append(tableName).append(".COLLECTIVITE AND ").append(mainTableName).append(".BUDGET=").append(tableName).append(".BUDGET");
                            fromClause = sb.toString();
                            String saveOrderBy = currentRequest.toString();
                            saveOrderBy = saveOrderBy.substring(saveOrderBy.indexOf(" ORDER BY"));
                            currentRequest = new StringBuilder();
                            currentRequest.append(selectClause).append(fromClause).append(" ").append(whereClause).append(saveOrderBy);
                        }
                    } catch(Throwable t) {
                        // TODO
                    }
                } else {
                    throw new DataConfigurationException("order by clause of "+elementModel.getId()+" references an unknown champ :"+tri);
                }
            }
            if(currentRequest.substring(currentRequest.length()-1).equals(",")) currentRequest.deleteCharAt(currentRequest.length()-1);
            if(currentRequest.substring(currentRequest.length()-9).equals("ORDER BY ")) currentRequest.delete(currentRequest.length()-10,currentRequest.length());
        }
        currentRequest.append(" LIMIT ").append(currentPage*pageSize).append(", ").append(pageSize);
        logger.debug(currentRequest);
        Connection conToRelease = null;
        try {
            rs=implementation.execute(currentRequest.toString());
            conToRelease = rs.getStatement().getConnection();
            //estimatedSize = rsc.getEstimatedSize();
            docIds = new ArrayList<String>();
            while(rs.next()) {
                docIds.add(rs.getString(1));
            }
            Statement st = rs.getStatement().getConnection().createStatement();
            String sqlCount = null;
            if(sec!=null) {
                String sTmp = sec.getSqlCount();
                if(sTmp!=null) {
                    String tmp = currentRequest.toString();
                    tmp = tmp.substring(tmp.indexOf("FROM"));
                    String fromClause = tmp.substring(0,tmp.indexOf("LIMIT"));
                    sqlCount = "SELECT "+sTmp+" "+fromClause;
                }
            }
//            if(sqlCount==null) sqlCount = "SELECT FOUND_ROWS()";
//            String sqlCount = "SELECT COUNT( DISTINCT(CGM14_BC_IX_LIGNES.SERIAL)) "+fromClause;
            //ResultSet rs2 = st.executeQuery("SELECT FOUND_ROWS()");
            ResultSet rs2 = st.executeQuery("SELECT FOUND_ROWS()");
            if(rs2.next()) {
                estimatedMainElementSize=rs2.getInt(1);
            }
            if(sqlCount!=null) {
                logger.debug("sqlCount="+sqlCount);
                rs2 = st.executeQuery(sqlCount);
                if(rs2.next()) estimatedSize = rs2.getInt(1);
                else estimatedSize = estimatedMainElementSize;
            } else estimatedSize = estimatedMainElementSize;
            st.close();
            hasNextPage=(docIds.size()==pageSize);
            hasPreviousPage=(currentPage>0);
            hasNextItem = docIds.size()>0;
            currentDocIndex=-1;
            
            rs.getStatement().close();
        } catch(SQLException sqlEx) {
            throw sqlEx;
        } finally {
            // must release connection because implementation.execute do not release it anymore
            if(conToRelease!=null) implementation.releaseConnection(conToRelease);
        }
        if(cache!=null && logger.isDebugEnabled()) {
            logger.debug("cache contains "+cache.getSize()+" entries");
        }
        cleanAllCaches();
    }
    public boolean hasNextPage() { return hasNextPage; }
    public boolean hasPreviousPage() { return hasPreviousPage; }
    public boolean hasNext() {
        return hasNextItem;
    }
    public DataHandler next() {
        if(!hasNextItem) throw new NoSuchElementException("iterating has ended");
        if(currentList==null) {
            currentDocId = docIds.get(++currentDocIndex);
            try {
                Document document = getDocument(currentDocId);
                XPath xp = xpf.newXPath();
                xp.setNamespaceContext(etatModel.getParent().getNamespaces());
                currentList = (NodeList)xp.evaluate(xPath,document,XPathConstants.NODESET);
                currentItemInList=-1;
            } catch(Exception ex) {
                logger.error("in next():",ex);
                throw new NoSuchElementException("an error occurs. See logs for details.");
            }
        }
        Element el = (Element)currentList.item(++currentItemInList);
        if(currentItemInList==currentList.getLength()-1) {
            currentList=null;
            hasNextItem=((currentDocIndex+1)<docIds.size());
        }
        if(el!=null)
            return new DataHandler(lrm,el,this,currentDocId,cache);
        else {
            logger.fatal("SQL REQUEST RETURNS MORE THAN XPATH DOES");
//            if(logger.isDebugEnabled()) {
//            	try {
//            		logger.debug(implementation.getDocumentAsString(etatModel,collectivite,budget,currentDocId));
//            		logger.debug("XPATH="+xPath);
//            	} catch(Throwable t) {}
//            }
            hasNextItem=false;
            return null;
        }
    }
    public Document getDocument(String docId) throws DataConfigurationException, DataAccessException {
        Document ret = documentCache.getDocument(docId);
        if(ret==null) {
            DocumentInfos di = implementation.getDocumentInfos(etatModel,collectivite,budget,docId);
            addDocumentToCache(docId,di);
            ret = di.getDoc();
        }
        return ret;
    }
    public String getDocumentEncoding(String docId) throws DataConfigurationException, DataAccessException {
        return documentCache.getEncoding(docId);
    }
    public void setEnvironment(ListeResultatModel lrm, CachedData cache) {
        this.lrm=lrm;
        this.cache=cache;
    }
    public void remove() {
        throw new UnsupportedOperationException("this operation is not allowed on MySqlDataResultSet");
    }
    public Pair getBudget() {
        return budget;
    }
    public void setBudget(Pair budget) {
        this.budget = budget;
    }
    public Pair getCollectivite() {
        return collectivite;
    }
    public void setCollectivite(Pair collectivite) {
        this.collectivite = collectivite;
    }
    public EtatModel getEtatModel() {
        return etatModel;
    }
    public void setEtatModel(EtatModel etatModel) {
        this.etatModel = etatModel;
    }
    public void nextPage() throws DataAccessException, DataConfigurationException {
        if(hasNextPage()) {
            currentPage++;
            try {
                loadIds();
            } catch(SQLException sqlEx) {
                throw new DataAccessException(sqlEx);
            }
        }
    }
    public void previousPage() throws DataAccessException, DataConfigurationException {
        if(hasPreviousPage()) {
            currentPage--;
            try {
                loadIds();
            } catch(SQLException sqlEx) {
                throw new DataAccessException(sqlEx);
            }
        }
    }
    public void firstPage() throws DataAccessException, DataConfigurationException {
//        if(hasPreviousPage()) {
        currentPage=0;
        try {
            loadIds();
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        }
//        }
    }
    public void lastPage() throws DataAccessException, DataConfigurationException {
        if(hasNextPage()) {
            currentPage=getEstimatedPageCount()-1;
            try {
                loadIds();
            } catch(SQLException sqlEx) {
                throw new DataAccessException(sqlEx);
            }
        }
    }
    public void setPage(int page) throws DataAccessException, DataConfigurationException {
        currentPage=page;
        try {
            loadIds();
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        }
    }
    public int getEstimatedPageCount() {
        if(estimatedPageCount==(-1)) {
            if(pageSize==0 || estimatedSize==0) {
                estimatedPageCount=0;
            } else {
//                estimatedPageCount=estimatedSize/pageSize;
                estimatedPageCount=estimatedMainElementSize/pageSize;
                if(estimatedSize % pageSize >0) estimatedPageCount++;
            }
        }
        return estimatedPageCount;
    }
    public int getCurrentPage() { return currentPage; }
    public ListeResultatModel getListeResultatModel() { return lrm; }
    public void setListeResultatModel(ListeResultatModel l) { this.lrm = l; }
    public void clear() {
        cleanAllCaches();
    }
    protected void cleanAllCaches() {
        if(cache!=null) cache.flush();
        if(documentCache!=null) documentCache.clear();
    }
    protected void addDocumentToCache(String docId,DocumentInfos doc) {
        documentCache.putDocument(docId,doc);
    }
    
    /**
     * Actually performs the operation on the data of the given column. Operations can
     * be average, sum, max, min, etc...
     * Needs the datatype, because there is a special case for the dates: we need to
     * convert them to number to do the computation and then back to dates for formatting,
     * which requires 2 additional closing parenthesis.
     * @param columnName the name of the column in the persistence model (database)
     * @param tableName Name of the table in which the column is to be found
     * @param operator the Operator to apply on the data (e.g. AVG, SUM, MAX...)
     * @param datatype The type (in the ChampModel constants) of data found in this column
     */
    public String calculateAggregate(String columnName, String tableName, String operator, ChampModel cm) {
        String result = "";
        ResultSet rset = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ").append(operator).append("(").append(columnName).append(")");
        if(cm.getDatatype().equals(ChampModel.DATATYPE_DATE)) {
            sb.append("))");
        }
        sb.append(" FROM ").append(tableName).append(" WHERE ").append(tableName).append(".COLLECTIVITE='");
        sb.append(collectivite.key).append("' AND ").append(tableName).append(".BUDGET='").append(budget.key).append("' AND DOC_ID IN (\n");
        sb.append(selectClause.replaceAll("SQL_CALC_FOUND_ROWS ","")).append(" ").append(fromClause).append(" ").append(whereClause).append("\n)");
        logger.debug(sb.toString());
        
        Connection conn = null;
//        Object computed = null;
        try {
            rset = implementation.execute(sb.toString());
            if(rset.getStatement()!=null)
                conn = rset.getStatement().getConnection();
            if(rset.next()) {
                result = rset.getString(1);
                Class classToReturn;
                classToReturn = cm.getDataClass();
                if(classToReturn.equals(Float.class)) {
                    try {
                        Float computed = new Float(Float.parseFloat((String)result));
                    } catch(NumberFormatException nfEx) { }
                } else if(classToReturn.equals(Integer.class)) {
                    try {
                        Integer computed = new Integer(Integer.parseInt((String)result));
                    } catch(NumberFormatException nfEx) { }
                } else if(classToReturn.equals(Amount.class)) {
                    try {
                        Amount computed = new Amount((String)result);
                        result = computed.stringRepresentation();
                    } catch(NumberFormatException nfEx) { }
                } else if(classToReturn.equals(java.util.Date.class)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    Date computed = sdf.parse((String)result);
                    sdf = new SimpleDateFormat(System.getProperty(Constants.SYS_PROP_DATE_FORMAT,"yyyy-MM-dd"));
                    result = sdf.format(computed);
                }
            }
            rset.close();
            logger.debug("resultat : "+result);
            if(cm.getDatatype().equals(ChampModel.DATATYPE_DATE)) {
                // TODO formater la donn�e en fonction du type de donn�e
                //SimpleDateFormat sdf = new SimpleDateFormat(cm.get);
            }
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        } catch(ParseException pEx) {
            pEx.printStackTrace();
        } finally {
            // must release connection because implementation.execute do not release it anymore
            if(conn!=null) implementation.releaseConnection(conn);
        }
        
        return result;
    }
    
    private class DocumentCache {
        private int maxCacheSize;
        private int actualCacheSize=0;
        private Hashtable<String,Document> cache;
        private Hashtable<String,String> encodings;
        private LinkedList<String> chain;
        private Object locker = new Object();
        public DocumentCache(int cacheSize) {
            super();
            this.maxCacheSize=cacheSize;
            cache=new Hashtable<String,Document>();
            chain=new LinkedList<String>();
            encodings = new Hashtable<String,String>();
        }
        public void clear() {
            synchronized(locker) {
                cache.clear();
                chain.clear();
                actualCacheSize=0;
            }
        }
        public Document getDocument(String docId) {
            Document ret = cache.get(docId);
            if(ret!=null) {
                synchronized(locker) {
                    chain.remove(docId);
                    chain.addFirst(docId);
                }
            }
            return ret;
        }
        public void putDocument(String docId,DocumentInfos doc) {
            if(cache.contains(docId)) {
                synchronized(locker) {
                    cache.remove(docId);
                    chain.remove(docId);
                    actualCacheSize--;
                }
            }
            synchronized(locker) {
                while(actualCacheSize>=maxCacheSize) {
                    String docToRemove = chain.removeLast();
                    cache.remove(docToRemove);
                    actualCacheSize--;
                }
            }
            synchronized(locker) {
                cache.put(docId,doc.getDoc());
                chain.addFirst(docId);
                encodings.put(docId,doc.getEncoding());
                actualCacheSize++;
            }
        }
        public String getEncoding(String docId) {
            String encoding = encodings.get(docId);
            if(encoding==null) {
                getDocument(docId);
                encoding = encodings.get(docId);
            }
            return encoding;
        }
    }
    
    public ElementModel getElementModel() {
        return elementModel;
    }
    
    public MySqlDataLayer getImplementation() {
        return implementation;
    }
    
    public ListeResultatModel getLrm() {
        return lrm;
    }
    
    public String getSql() {
        return sql;
    }
    
    public String getXPath() {
        return xPath;
    }
    
}
