/*
 * $Id: ConversionStatus.java,v 1.22 2007/08/16 11:10:56 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: ConversionStatus.java,v $
 * Revision 1.22  2007/08/16 11:10:56  marbobo
 * + Bugfixes
 * + Refactorings
 *
 * Revision 1.21  2006/07/11 23:40:05  marbobo
 * + PostgreSQL ist pingelig und erlaubt kein get/setBoolean auf Int-Spalten
 *
 */
package vkc.db.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import vkc.converter.common.Status;
import vkc.db.PersistenceManager;
import vkc.db.helper.DBHelper;
import vkc.db.serializer.VKCDocumentSerializer;
import vkc.exception.DBException;
import vkc.misc.VKCLogManager;
import vkc.view.WebShare;

/**
 * This class encapsulates some useful information about a conversion status.
 * 
 * @created 07.01.2004
 * @author Andreas Koop
 */
public class ConversionStatus extends PersistentObject {
    private boolean sendmail = true;

    private int reiterations = 0;

    private String comment = null;

    private String documentString = null;

    private String status = Status.CONVERSION_OUTSTANDING;

    private Timestamp finished = null;

    private Timestamp started = null;

    /**
     * Construtcs a new ConversionStatus object corresponding to
     * <code>CONVERSIONSTATI</code> table.
     */
    public ConversionStatus() {
        super("CONVERSIONSTATI", "DOCUMENTID");
    }

    public String getComment() {
        return comment == null ? "" : comment;
    }

    public Integer getDocumentId() {
        return getId();
    }

    public String getDocumentString() {
        if (documentString == null) {
            if (getId() == null) {
                return "docId=null";
            }

            try {
                documentString = VKCDocumentSerializer.getDocumentRepresentation(getId());
            } catch (DBException e) {
                VKCLogManager.errorLog(this, "", e.getMessage(), e);
                documentString = getId().toString();
            }
        }

        return documentString;
    }

    public String getDocumentName() {
        String s = getDocumentString();

        if (s.indexOf('/') < 0) {
            return s;
        }

        return s.substring(s.lastIndexOf('/') + 1);
    }

    public void setDocumentId(Integer documentId) {
        setId(documentId);
    }

    public Timestamp getFinished() {
        return finished;
    }

    public int getReiterations() {
        return reiterations;
    }

    public Timestamp getStarted() {
        return started;
    }

    public String getStatus() {
        return status;
    }

    public String getLocalizedStatus(Locale locale) {
        return WebShare.resources.getMessage(locale, "vkc.administration.systemmaintenance.conversions." + status);
    }

    public void setComment(String string) {
        comment = string;
    }

    public void setFinished(Timestamp timestamp) {
        finished = timestamp;
    }

    public void setReiterations(int i) {
        reiterations = i;
    }

    public void setStarted(Timestamp timestamp) {
        started = timestamp;
    }

    public void setStatus(String string) {
        status = string;
    }

    public boolean isSendmail() {
        return sendmail;
    }

    public void setSendmail(boolean sendmail) {
        this.sendmail = sendmail;
    }

    /**
     * Returns an outstanding conversion whose reiterations does not exeed a
     * given limit and documenttypes satisfies the requested doctypes.
     * 
     * @param converterInfo
     * 
     * @return null, if no outstanding conversions queued
     * @throws DBException
     */
    public static final ConversionStatus getOutstandingConversion(ConverterInfo converterInfo) throws DBException {
        PersistenceManager pm = PersistenceManager.getInstance();
        Connection conn = pm.getConnection(converterInfo.getAssocDbUrl());
        PreparedStatement pStmt = null;
        PreparedStatement pStmt1 = null;
        ResultSet rs = null;
        ResultSet rs1 = null;

        try {
            while (true) {
                // Nehme die Konvertierung mit den wenigsten Wiedeholungen
                pStmt = conn.prepareStatement("SELECT * FROM CONVERSIONSTATI WHERE STATUS = ? OR STATUS = ? AND REITERATIONS < ? ORDER BY REITERATIONS ASC");
                pStmt.setString(1, Status.CONVERSION_OUTSTANDING);
                pStmt.setString(2, Status.CONVERSION_SUSPENDED);
                pStmt.setInt(3, converterInfo.getMaxReiterations());
                rs = pStmt.executeQuery();

                pStmt1 = conn.prepareStatement("SELECT DOCUMENTTYPES.EXTENSION AS EXT FROM DOCUMENTS, DOCUMENTTYPES" + 
                                               " WHERE DOCUMENTS.DOCUMENTID = ? AND DOCUMENTS.TYPEID = DOCUMENTTYPES.DOCTYPEID " + 
                                               " AND DOCUMENTTYPES.CONVERTIBLE = 1");

                while (rs.next()) {
                    int documentId = rs.getInt("DOCUMENTID");
                    pStmt1.setInt(1, documentId);
                    rs1 = pStmt1.executeQuery();

                    if (!rs1.next() || converterInfo.getDocumentTypes().indexOf(rs1.getString("EXT").toLowerCase()) < 0) {
                        DBHelper.close(rs1);
                        rs1 = null;
                        continue;
                    }

                    DBHelper.close(rs1);
                    rs1 = null;

                    ConversionStatus c = new ConversionStatus();
                    c.associateWithDbUrl(converterInfo.getAssocDbUrl());
                    c.setDocumentId(new Integer(documentId));
                    c.setReiterations(rs.getInt("REITERATIONS"));
                    c.setStatus(Status.CONVERSION_STARTED);
                    c.setStarted(new Timestamp(System.currentTimeMillis()));
                    c.setComment("Started by " + converterInfo.toString());
                    c.setSendmail(rs.getInt("SENDMAIL") != 0);
                    c.makePersistent(conn);

                    conn.commit();

                    return c;
                }

                return null;
            }
        } catch (Exception e) {
            VKCLogManager.errorLog(ConversionStatus.class, "", e.getMessage(), e);
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(rs1);
            DBHelper.close(pStmt);
            DBHelper.close(pStmt1);
            DBHelper.close(conn);
        }
    }

    /**
     * Gibt alle Konvertierungsstatus als Liste zurück. (Wird vom VKC-Server
     * benutzt.)
     * 
     * @return list of ConversionStatus-Objects
     * @throws DBException
     */
    public static final List getAllConversionStatus() throws DBException {
        PersistenceManager pm = PersistenceManager.getInstance();
        Connection conn = pm.getConnection();
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        List conversionStatus = new ArrayList();

        try {
            pStmt = conn.prepareStatement("SELECT * FROM CONVERSIONSTATI ORDER BY STATUS ASC");
            rs = pStmt.executeQuery();

            while (rs.next()) {
                ConversionStatus c = new ConversionStatus();
                c.setDocumentId(new Integer(rs.getInt("DOCUMENTID")));
                c.setReiterations(rs.getInt("REITERATIONS"));
                c.setStatus(rs.getString("STATUS"));
                c.setStarted(rs.getTimestamp("STARTED"));
                c.setFinished(rs.getTimestamp("FINISHED"));
                c.setComment(rs.getString("REMARK"));
                c.setSendmail(rs.getInt("SENDMAIL") != 0);

                conversionStatus.add(c);
            }

            return conversionStatus;
        } catch (Exception e) {
            VKCLogManager.errorLog(ConversionStatus.class, "", "Error while retrieving all conversion status.", e);
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }

    /**
     * Setzt den Status der "started"-Jobs auf "suspended" und erhöht die Anzahl
     * der Wiederholungen. Wird einmalig beim Starten des Konverterservers
     * gemacht.
     * 
     * @param conf
     *            Konverter Info
     * @throws DBException
     */
    public static final void checkAndResetStartedJobs(ConverterInfo conf) throws DBException {
        PersistenceManager pm = PersistenceManager.getInstance();
        Connection conn = pm.getConnection();
        PreparedStatement updateStmt = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            updateStmt = conn.prepareStatement("UPDATE CONVERSIONSTATI SET STATUS = ?, REITERATIONS = ? WHERE DOCUMENTID = ?");
            pStmt = conn.prepareStatement("SELECT DOCUMENTID, REMARK, REITERATIONS FROM CONVERSIONSTATI WHERE STATUS = ?");
            pStmt.setString(1, Status.CONVERSION_STARTED);
            rs = pStmt.executeQuery();

            while (rs.next()) {
                String remark = rs.getString("REMARK");

                if (remark == null || remark.indexOf(conf.getHostname() + ":" + conf.getPort()) > -1) {
                    updateStmt.setString(1, Status.CONVERSION_SUSPENDED);
                    updateStmt.setInt(2, rs.getInt("REITERATIONS") + 1);
                    updateStmt.setInt(3, rs.getInt("DOCUMENTID"));
                    updateStmt.execute();
                }
            }

            conn.commit();
        } catch (Exception e) {
            VKCLogManager.errorLog(ConversionStatus.class, "", "Error while retrieving all conversion status.", e);
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(updateStmt);
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }

    /**
     * Löscht die gewünschten Einträge aus der CONVERSIONSTATI-Tabelle
     * 
     * @param docIds
     * @throws DBException
     */
    public static final void deleteStatusForDocumentIds(Integer[] docIds) throws DBException {
        if (docIds == null || docIds.length == 0) {
            return;
        }

        PersistenceManager pm = PersistenceManager.getInstance();
        Connection conn = pm.getConnection();
        PreparedStatement pStmt = null;

        try {
            pStmt = conn.prepareStatement("DELETE FROM CONVERSIONSTATI WHERE DOCUMENTID = ?");

            for (int i = 0; i < docIds.length; i++) {
                Integer docId = docIds[i];
                pStmt.setInt(1, docId.intValue());
                pStmt.execute();
            }

            conn.commit();
        } catch (Exception e) {
            VKCLogManager.errorLog(ConversionStatus.class, "", "Error while deleting conversionstatus.", e);
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }

    /**
     * Setzt die gewünschten Einträge aus der CONVERSIONSTATI-Tabelle zurück,
     * also "outstanding, null, null, 0, null"
     * 
     * @param docIds
     * @throws DBException
     */
    public static final void resetStatusForDocumentIds(Integer[] docIds) throws DBException {
        if (docIds == null || docIds.length == 0) {
            return;
        }

        PersistenceManager pm = PersistenceManager.getInstance();
        Connection conn = pm.getConnection();
        PreparedStatement pStmt = null;

        try {
            pStmt = conn.prepareStatement("UPDATE CONVERSIONSTATI SET STATUS = ?, STARTED = ?, FINISHED = ?, REITERATIONS = 0, REMARK = ? WHERE DOCUMENTID = ?");

            for (int i = 0; i < docIds.length; i++) {
                Integer docId = docIds[i];
                pStmt.setString(1, Status.CONVERSION_OUTSTANDING);
                pStmt.setNull(2, Types.TIMESTAMP);
                pStmt.setNull(3, Types.TIMESTAMP);
                pStmt.setNull(4, Types.VARCHAR);
                pStmt.setInt(5, docId.intValue());
                pStmt.execute();
            }

            conn.commit();
        } catch (Exception e) {
            VKCLogManager.errorLog(ConversionStatus.class, "", "Error while deleting conversionstatus.", e);
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }

    /**
     * Increases the reiteration counter
     */
    public void incReiterations() {
        reiterations++;

    }

    /**
     * (non-Javadoc)
     * 
     * @see vkc.db.common.PersistentObject#makePersistent(java.sql.Connection)
     */
    public void makePersistent(Connection conn) throws DBException {
        PreparedStatement pStmt = null;

        try {
            if (exists(conn)) {
                // UPDATE
                pStmt = conn.prepareStatement("UPDATE CONVERSIONSTATI SET STATUS = ?, STARTED = ?, FINISHED = ?, REITERATIONS = ?, REMARK = ?, SENDMAIL = ? WHERE DOCUMENTID = ?");
            } else {
                // INSERT
                if (getId() == null) {
                    return;
                }

                pStmt = conn.prepareStatement("INSERT INTO CONVERSIONSTATI ( STATUS, STARTED, FINISHED, REITERATIONS, REMARK, SENDMAIL, DOCUMENTID ) VALUES ( ?, ?, ?, ?, ?, ?, ? )");
            }

            pStmt.setString(1, getStatus());
            pStmt.setTimestamp(2, getStarted());
            pStmt.setTimestamp(3, getFinished());
            pStmt.setInt(4, getReiterations());
            pStmt.setString(5, getComment());
            pStmt.setInt(6, isSendmail() ? 1 : 0);
            pStmt.setInt(7, getDocumentId().intValue());
            pStmt.execute();
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }

    /**
     * Veranlasst die Neuerstellung von Konvertierungen für den übergebenen
     * Dokumententyp.
     * 
     * @param docTypeId
     *            ID des Dokumententyps
     * @return Anzahl der Dokumente, für die eine neue Konvertierung veranlasst
     *         wurde
     * @throws DBException
     */
    public static final int recreateConversionsForDocTypeId(Integer docTypeId) throws DBException {
        PersistenceManager pm = PersistenceManager.getInstance();
        Connection conn = pm.getConnection();
        PreparedStatement pStmt = null;
        PreparedStatement deleteStmt = null;
        PreparedStatement countStmt = null;
        ResultSet rs = null;
        ResultSet rs1 = null;

        try {
            deleteStmt = conn.prepareStatement("DELETE FROM ADDITIONALDOCUMENTFORMATS WHERE DOCUMENTID = ?");
            countStmt = conn.prepareStatement("SELECT COUNT(*) AS CNT FROM CONVERSIONSTATI WHERE DOCUMENTID = ?");
            pStmt = conn.prepareStatement("SELECT DOCUMENTID FROM DOCUMENTS WHERE TYPEID = ?");
            pStmt.setInt(1, docTypeId.intValue());
            rs = pStmt.executeQuery();

            int docCount = 0;

            while (rs.next()) {
                int documentId = rs.getInt("DOCUMENTID");

                countStmt.setInt(1, documentId);
                rs1 = countStmt.executeQuery();

                if (rs1.next() && rs1.getInt("CNT") == 1) {
                    DBHelper.close(rs1);
                    rs1 = null;
                    continue;
                }

                DBHelper.close(rs1);
                rs1 = null;

                deleteStmt.setInt(1, documentId);
                deleteStmt.execute();

                ConversionStatus s = new ConversionStatus();
                s.setDocumentId(new Integer(documentId));
                s.setSendmail(false);
                s.makePersistent(conn);

                docCount++;
            }

            conn.commit();

            return docCount;
        } catch (Exception e) {
            VKCLogManager.errorLog(ConversionStatus.class, "", e.getMessage(), e);
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(rs1);
            DBHelper.close(countStmt);
            DBHelper.close(deleteStmt);
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }

    /**
     * Veranlasst die Erstellung von Konvertierungen für Dokumente, die noch
     * nicht konvertiert sind
     * 
     * @return Anzahl der Dokumente, für die eine neue Konvertierung veranlasst
     *         wurde
     * @throws DBException
     */
    public static final int createConversionsForUnconverted() throws DBException {
        PersistenceManager pm = PersistenceManager.getInstance();
        Connection conn = pm.getConnection();
        PreparedStatement getDocIdStmt = null;
        PreparedStatement countStmt = null;
        ResultSet rs = null;
        ResultSet rs1 = null;

        try {
            countStmt = conn.prepareStatement("SELECT COUNT(*) AS CNT FROM CONVERSIONSTATI WHERE DOCUMENTID = ?");
            getDocIdStmt = conn.prepareStatement("SELECT DOCUMENTS.DOCUMENTID AS DOCID FROM DOCUMENTS, DOCUMENTTYPES" + 
                                                 " WHERE DOCUMENTS.TYPEID = DOCUMENTTYPES.DOCTYPEID" + 
                                                 " AND DOCUMENTTYPES.CONVERTIBLE = 1" + 
                                                 " AND DOCUMENTS.DOCUMENTID NOT IN (SELECT DOCUMENTID FROM ADDITIONALDOCUMENTFORMATS)");
            rs = getDocIdStmt.executeQuery();

            int docCount = 0;

            while (rs.next()) {
                int documentId = rs.getInt("DOCID");

                countStmt.setInt(1, documentId);
                rs1 = countStmt.executeQuery();

                if (rs1.next() && rs1.getInt("CNT") == 1) {
                    DBHelper.close(rs1);
                    rs1 = null;
                    continue;
                }

                DBHelper.close(rs1);
                rs1 = null;

                ConversionStatus s = new ConversionStatus();
                s.setDocumentId(new Integer(documentId));
                s.setSendmail(false);
                s.makePersistent(conn);

                docCount++;
            }

            conn.commit();

            return docCount;
        } catch (Exception e) {
            VKCLogManager.errorLog(ConversionStatus.class, "", e.getMessage(), e);
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(rs1);
            DBHelper.close(countStmt);
            DBHelper.close(getDocIdStmt);
            DBHelper.close(conn);
        }
    }

    /**
     * Gibt die Anzahl der ausstehenden Konvertierungen zurük
     * 
     * @throws DBException
     */
    public static final int getOutstandingConversions() throws DBException {
        PersistenceManager pm = PersistenceManager.getInstance();
        Connection conn = pm.getConnection();
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            pStmt = conn.prepareStatement("SELECT COUNT(*) AS CNT FROM CONVERSIONSTATI WHERE STATUS = ?");
            pStmt.setString(1, Status.CONVERSION_OUTSTANDING);
            rs = pStmt.executeQuery();

            int jobCount = 0;

            if (rs.next()) {
                jobCount = rs.getInt("CNT");
            }

            return jobCount;
        } catch (Exception e) {
            VKCLogManager.errorLog(ConversionStatus.class, "", e.getMessage(), e);
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
            DBHelper.close(conn);
        }
    }

    public String toString() {
        return "ConvStatus for DocumentID=" + getDocumentId() + ":" + getStatus() + ":started at " + getStarted() + ": reiters=" + reiterations + ": comment=" + comment;
    }
}