/*
 * Copyright
 *   2007 axYus - www.axyus.com
 *   2007 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 General Public License as published by
 * the Free Software Foundation; either version 2 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 General Public License for more details.
 *
 * You should have received a copy of the GNU 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.common.config.DocumentModel;
import fr.gouv.finances.cp.xemelios.data.impl.MySqlDataLayer;
import fr.gouv.finances.cp.xemelios.ui.MainWindow;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import org.xml.sax.SAXException;

import fr.gouv.finances.cp.xemelios.common.DematTransform;
import fr.gouv.finances.cp.xemelios.data.ui.PatchProgress;
import fr.gouv.finances.cp.xemelios.utils.FileUtils;

/**
 *
 * @author chm
 */
public class VersementPatch32 {
    public static final transient String PARAM_VERSEMENT_PATCH_32 = "versement.patch.3.2.applied";
    private static final Logger logger = Logger.getLogger(VersementPatch32.class);
    private DocumentBuilderFactory domFactory;
    
    
    private MySqlDataLayer impl = null;
    
    /** Creates a new instance of VersementPatch32 */
    public VersementPatch32(MySqlDataLayer impl) {
        super();
        logger.debug("instanciating");
        this.impl=impl;
        domFactory = DocumentBuilderFactory.newInstance();
        domFactory.setNamespaceAware(true);
    }
    public void run()  {
        logger.debug("run");
        String paramValue = impl.getParameterValue(PARAM_VERSEMENT_PATCH_32);
        if(paramValue==null) {
            Connection con = null;
            try {
                con = impl.getConnection();
                // check if document table exists for paye - etatPaye
                boolean isVersementDocumentTableExists = true;
                try {
                    con.createStatement().executeQuery("SELECT 1 FROM VERS_ETATVERS_DOCUMENTS LIMIT 1");
                } catch(SQLException sqlEx) {
                    if(sqlEx.getErrorCode()==1146) isVersementDocumentTableExists = false;
                    else throw sqlEx;
                }
                boolean isNatureDocumentTableExists = true;
                try {
                    con.createStatement().executeQuery("SELECT 1 FROM VERS_ETATREPARTITION_DOCUMENTS LIMIT 1");
                } catch(SQLException sqlEx) {
                    if(sqlEx.getErrorCode()==1146) isNatureDocumentTableExists = false;
                    else throw sqlEx;
                }
                if(isVersementDocumentTableExists) patchVersementDocuments(con);
                if(isNatureDocumentTableExists) patchRepartitionDocuments(con);
                impl.setParameterValue(PARAM_VERSEMENT_PATCH_32,"applied");
            } catch(Throwable t) {
                logger.error("in run()",t);
            } finally {
                if(con!=null) impl.releaseConnection(con);
            }
            
            
        }
    }
    private String loadXsltBuffer() {
        String ret = null;
// TODO:
        DocumentModel dm = MainWindow.getInstance().getDocModels().getDocumentById("etatVersement");
        File f = new File(new File(dm.getBaseDirectory()),"versement/patch32versement.xsl");
        try {
            String xslEncoding = FileUtils.getFileEncoding(f);
            ret = FileUtils.readTextFile(f,xslEncoding);
        } catch(IOException ioEx) {
            logger.error("in loadXsltBuffer :",ioEx);
        }
        return ret;
    }
    private void patchVersementDocuments(Connection con) throws SQLException {
        logger.debug("patchVersementDocuments");
        patchTableDocuments(con,"VERS_ETATVERS_DOCUMENTS", "Etat de versement");
    }
    private void patchRepartitionDocuments(Connection con) throws SQLException {
        logger.debug("patchRepartitionDocuments");
        patchTableDocuments(con,"VERS_ETATREPARTITION_DOCUMENTS", "Répartition par nature (Versement)");
    }
    private void patchTableDocuments(Connection con,String tableName, String tableLibelle) throws SQLException {
        Statement st = null, st2 = null;;
        PreparedStatement ps = null, ps2 = null, ps3 = null;
        ResultSet rs = null, rs2 = null;
        SQLException sqlExToThrow = null;
        PatchProgress progress;
        Connection con2 = null;
        try {
            st = con.createStatement();
            rs = st.executeQuery("SELECT COUNT(*) FROM "+tableName);
            rs.next();
            long nbRecords = rs.getLong(1);
            progress = impl.getPatchProgress();
            progress.setMaxProgress(nbRecords);
            progress.startProgress(tableLibelle);
            String xsltBuffer = loadXsltBuffer();
            con2 = impl.getConnection();
            rs = st.executeQuery("SELECT COLLECTIVITE, BUDGET, DOC_ID FROM "+tableName);
            st2 = con2.createStatement();
            st2.executeUpdate("LOCK TABLES "+tableName+" WRITE");
            ps2 = con2.prepareStatement("SELECT DOC, ENCODING FROM "+tableName+" WHERE BUDGET=? AND COLLECTIVITE=? AND DOC_ID=?");
            ps3 = con2.prepareStatement("UPDATE "+tableName+" SET DOC=? WHERE BUDGET=? AND COLLECTIVITE=? AND DOC_ID=?");
            while(rs.next()) {
                String collectivite = rs.getString(1);
                String budget = rs.getString(2);
                String docId = rs.getString(3);
//                logger.debug("controlling "+collectivite+" / "+budget+" / "+docId);
                ps2.setString(1,budget);
                ps2.setString(2,collectivite);
                ps2.setString(3,docId);
                rs2 = ps2.executeQuery();
                if(rs2.next()) {
                    String encoding = rs2.getString(2);
                    try {
                        byte[] buff = new byte[512];
                        ByteArrayOutputStream baos = new ByteArrayOutputStream();
                        InputStream is = rs2.getAsciiStream(1);
                        int length=is.read(buff);
                        while(length>0) {
                            baos.write(buff,0,length);
                            length=is.read(buff);
                        }
                        DocumentBuilder builder = domFactory.newDocumentBuilder();
                        byte[] byteArray = baos.toByteArray();
                        InputStream bis = new ByteArrayInputStream(byteArray);
                        Document ret = builder.parse(bis);
                        String localBuffer = xsltBuffer.replaceAll("##output-encoding##",encoding);
                        InputStream transformed = DematTransform.transform(ret,new ByteArrayInputStream(localBuffer.getBytes("ISO-8859-1")));
                        ps3.setString(2,budget);
                        ps3.setString(3,collectivite);
                        ps3.setString(4,docId);
                        ps3.setBinaryStream(1,transformed,transformed.available());
                        ps3.executeUpdate();
                    } catch(IOException ioEx) {
                        logger.error("ioEx",ioEx);
                    } catch(ParserConfigurationException pcEx) {
                        logger.error("pcEx",pcEx);
                    } catch(SAXException saxEx) {
                        logger.error("saxEx",saxEx);
                    }
                }
                progress.pushProgress();
            }
        } catch(SQLException sqlEx) {
            sqlExToThrow = sqlEx;
        } finally {
            if(ps3!=null) try { ps3.close(); } catch(Throwable t) {}
            if(rs2!=null) try { rs2.close(); } catch(Throwable t) {}
            if(ps2!=null) try { ps2.close(); } catch(Throwable t) {}
            if(con2!=null) {
                if(st2 == null) {
                    try { st2 = con2.createStatement(); } catch(Throwable t) {}
                }
                try { st2.executeUpdate("UNLOCK TABLES"); } catch(Throwable t) {}
                try { st2.close(); } catch(Throwable t) {}
                try { con2.commit(); } catch(Throwable t) {}
                impl.releaseConnection(con2);
            }
            if(rs!=null) try { rs.close(); } catch(Throwable t) {}
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
            if(st!=null) try { st.close(); } catch(Throwable t) {}
        }
        if(sqlExToThrow!=null) throw sqlExToThrow;
    }
}
