/*
 * Copyright
 *   2007 axYus - www.axyus.com
 *   2009 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.dgfip.xemelios.common.config.DocumentModel;
import fr.gouv.finances.cp.xemelios.data.impl.MySqlDataLayer;
import fr.gouv.finances.dgfip.xemelios.common.config.Loader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
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.dgfip.xemelios.data.ui.PatchProgress;
import java.io.FileInputStream;
import javax.xml.transform.Result;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;

/**
 *
 * @author chm
 */
public class PayePatch40 {

   public static final transient String PARAM_PAYE_PATCH_40 = "paye.patch.4.0.applied";
   private static final Logger logger = Logger.getLogger(PayePatch40.class);
   private DocumentBuilderFactory domFactory;
   private MySqlDataLayer impl = null;

   /** Creates a new instance of PayePatch32 */
   public PayePatch40(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_PAYE_PATCH_40);
      if (paramValue == null) {
         Connection con = null;
         try {
            con = impl.getConnection();
            // check if document table exists for paye - etatPaye
            boolean isRepositoryTableExists = true;
            try {
               con.createStatement().executeQuery("SELECT 1 FROM PAYE_REPOSITORY LIMIT 1");
            } catch (SQLException sqlEx) {
               if (sqlEx.getErrorCode() == 1146) {
                  isRepositoryTableExists = false;
               } else {
                  throw sqlEx;
               }
            }
            if (isRepositoryTableExists) {
               patchRepositoryDocuments(con);
            }
            impl.setParameterValue(PARAM_PAYE_PATCH_40, "applied");
         } catch (Throwable t) {
            logger.error("in run()", t);
         } finally {
            if (con != null) {
               impl.releaseConnection(con);
            }
         }
      }
   }

   private InputStream loadXslt(String fileName) throws FileNotFoundException {
      String ret = null;
      DocumentModel dm = null;
      try {
         dm = Loader.getDocumentsInfos(null).getDocumentById("documentPaye");
      } catch (Throwable t) {
          
      }
      if (dm == null) {
         return null;
      }
      File f = new File(new File(dm.getBaseDirectory()), fileName);
      return new FileInputStream(f);
   }

   private void patchRepositoryDocuments(Connection con) throws Exception {
      logger.debug("patchRepositoryDocuments");
      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 PAYE_REPOSITORY");
         rs.next();
         long nbRecords = rs.getLong(1);
         progress = impl.getPatchProgress();
         progress.setMaxProgress(nbRecords);
         progress.startProgress("Nomenclatures paye");
         //String xsltBuffer = loadXsltBuffer("paye/nomenclature-import.xsl");
         con2 = impl.getConnection();
         rs = st.executeQuery("SELECT COLLECTIVITE FROM PAYE_REPOSITORY");
         st2 = con2.createStatement();
         st2.executeUpdate("LOCK TABLES PAYE_REPOSITORY WRITE");
         ps2 = con2.prepareStatement("SELECT REPOSITORY, ENCODING FROM PAYE_REPOSITORY WHERE COLLECTIVITE=?");
         ps3 = con2.prepareStatement("UPDATE PAYE_REPOSITORY SET REPOSITORY=? WHERE COLLECTIVITE=?");
         TransformerFactory factory = net.sf.saxon.TransformerFactoryImpl.newInstance();
         Transformer repositoryTrans = factory.newTransformer(new StreamSource(loadXslt("paye/nomenclature-import.xsl")));
         while (rs.next()) {
            String collectivite = rs.getString(1);
            ps2.setString(1, collectivite);
            rs2 = ps2.executeQuery();
            if (rs2.next()) {
               try {
                  InputStream is = rs2.getAsciiStream(1);
                  DocumentBuilder builder = domFactory.newDocumentBuilder();
                  Document ret = builder.parse(is);
                  ByteArrayOutputStream baos = new ByteArrayOutputStream();
                  Result result = new StreamResult(baos);
                  repositoryTrans.transform(new DOMSource(ret), result);
                  ByteArrayInputStream transformed = new ByteArrayInputStream(baos.toByteArray());
                  ps3.setString(2, collectivite);
                  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;
      }
   }
}
