/*
 * $Id: VKCLicenseSerializer.java,v 1.6 2007/10/25 15:00:00 korowa Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 * 
 * $Log: VKCLicenseSerializer.java,v $
 * Revision 1.6  2007/10/25 15:00:00  korowa
 * bugfixing
 *
 * Revision 1.5  2007/08/09 14:38:53  marbobo
 * - 2 falsch zugeordnete LDAP- Parameter entfernt
 * + Löschen und Aktivieren von Lizenzen hinzugefügt
 *
 * Revision 1.4  2007/08/08 12:14:41  marbobo
 * + License-Funktionalität komplett umgeschrieben
 * + Bugfix bzgl. Beschreibungsfelder von Entitäten
 * + Bugfix mailing
 *
 */
package vkc.db.serializer;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Types;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.util.ArrayUtils;
import vkc.util.StringUtils;
import vkc.view.WebShare;
import vkc.db.entities.VKCLanguage;
import vkc.db.entities.VKCLicense;

public class VKCLicenseSerializer {
    private final static int MAX_TEXTCOL_SIZE = 4000;

    private VKCLicenseSerializer() {
    }

    public static final void saveLicense(VKCLicense license) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            saveLicense(license, conn);
            conn.commit();
        } catch (Exception ex) {
            DBHelper.rollBack(conn);
            throw new DBException(ex);
        } finally {
            DBHelper.close(conn);
        }
    }

    public static final void saveLicense(VKCLicense license, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        VKCLicense oldLicense = null;
        Integer newId = null;

        if (license == null || license.isEmpty()) {
            return;
        }

        try {
            if (license.getLicenseId() != null && DBHelper.existsEntry(conn, "license", "licenseId", license.getLicenseId())) {
                oldLicense = retrieveLicense(license.getLicenseId(), conn);
            }

            if (license.isActive()) {
                pStmt = conn.prepareStatement("UPDATE license SET is_active = ?");
                pStmt.setInt(1, 0);
                pStmt.execute();
            }
            
            DBHelper.close(pStmt);
            pStmt = conn.prepareStatement("INSERT INTO license ( licenseId, licensePart, languageId, licenseText, created, creatorId, validFrom, validUntil, is_active ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? )");
            newId = DBHelper.getNewId(conn, "license", "licenseId");
            pStmt.setInt(1, newId.intValue());
            
            List languages = VKCLanguageSerializer.getLanguages();
            
            for (int i = 0; i < languages.size(); i++) {
                VKCLanguage language = (VKCLanguage)languages.get(i);
                String text = license.getLicense(language.getLanguageId());
                
                if (!StringUtils.isEmpty(text)) {
                    int length = (int)Math.ceil((double)text.length() / MAX_TEXTCOL_SIZE);
                    String[] textParts = new String[length];
                    
                    for (int j = 0; j < length; j++) {
                        int start = j * MAX_TEXTCOL_SIZE;
                        int end = (j + 1) * MAX_TEXTCOL_SIZE;
                        
                        if (text.length() < end) {
                            end = text.length();
                        }
                        
                        textParts[j] = text.substring(start, end);
                    }

                    for (int k = 0; k < textParts.length; k++) {
                        pStmt.setInt(2, k + 1);
                        pStmt.setInt(3, language.getLanguageId().intValue());
                        pStmt.setString(4, textParts[k]);
                        
                        if (license.getCreated() != null) {
                            pStmt.setTimestamp(5, license.getCreated());
                        } else {
                            pStmt.setNull(5, Types.TIMESTAMP);
                        }

                        pStmt.setInt(6, license.getCreatorId().intValue());
                        
                        if (license.getValidFrom() != null) {
                            pStmt.setTimestamp(7, license.getValidFrom());
                        } else {
                            pStmt.setNull(7, Types.TIMESTAMP);
                        }

                        if (license.getValidUntil() != null) {
                            pStmt.setTimestamp(8, license.getValidUntil());
                        } else {
                            pStmt.setNull(8, Types.TIMESTAMP);
                        }
                        
                        pStmt.setInt(9, license.isActive() ? 1 : 0);
                        
                        pStmt.execute();
                    }
                }
            }
            
            if (oldLicense != null) {
                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("UPDATE userLicense SET licenseId = ? WHERE licenseId = ?");
                pStmt.setInt(1, newId.intValue());
                pStmt.setInt(2, oldLicense.getLicenseId().intValue());
                pStmt.execute();

                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("DELETE FROM license WHERE licenseId = ?");
                pStmt.setInt(1, oldLicense.getLicenseId().intValue());
                pStmt.execute();
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }

    public static final VKCLicense retrieveActiveLicense() throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            
            return retrieveActiveLicense(conn);
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    public static final VKCLicense retrieveActiveLicense(Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Integer lastId = null;
        String text = null;
        boolean exist = false;

        try {
            pStmt = conn.prepareStatement("SELECT * FROM license WHERE is_active = ? ORDER BY licenseId ASC, languageId ASC, licensePart ASC, created ASC");
            pStmt.setInt(1, 1);
            rs = pStmt.executeQuery();
            VKCLicense license = new VKCLicense();
            
            while (rs.next()) {
                exist = true;
                Integer id = new Integer(rs.getInt("LICENSEID"));
                
                if (lastId == null) {
                    lastId = id;
                }
                
                if (!lastId.equals(id)) {
                    lastId = id;
                    license = new VKCLicense();
                }
                
                license.setLicenseId(id);
                Integer languageId = new Integer(rs.getInt("LANGUAGEID"));
                
                text = license.getLicense(languageId);
                
                if (text == null) {
                    text = "";
                }
                
                license.setLicense(languageId, text + rs.getString("LICENSETEXT"));
                license.setCreated(rs.getTimestamp("CREATED"));
                license.setCreatorId(new Integer(rs.getInt("CREATORID")));
                license.setValidFrom(rs.getTimestamp("VALIDFROM"));
                license.setValidUntil(rs.getTimestamp("VALIDUNTIL"));
                license.setActive(rs.getBoolean("IS_ACTIVE"));
            }
            
            if (exist && license.isValid()) {
                return license;
            }
            
            return null;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }

    public static final List retrieveLicenses() throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            
            return retrieveLicenses(conn);
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    public static final List retrieveLicenses(Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Integer lastId = null;
        String text = null;
        HashMap map = new HashMap();
        Integer[] ids = {};
        List licenses = new ArrayList();

        try {
            pStmt = conn.prepareStatement("SELECT * FROM license ORDER BY is_active DESC, created DESC, licenseId ASC, languageId ASC, licensePart ASC");
            rs = pStmt.executeQuery();

            VKCLicense license = new VKCLicense();
            
            while (rs.next()) {
                Integer id = new Integer(rs.getInt("LICENSEID"));
                
                if (lastId == null) {
                    lastId = id;
                }
                
                if (!lastId.equals(id)) {
                    lastId = id;
                    license = new VKCLicense();
                }
                
                license.setLicenseId(id);
                Integer languageId = new Integer(rs.getInt("LANGUAGEID"));
                
                text = license.getLicense(languageId);
                
                if (text == null) {
                    text = "";
                }
                
                license.setLicense(languageId, text + rs.getString("LICENSETEXT"));
                license.setCreated(rs.getTimestamp("CREATED"));
                license.setCreatorId(new Integer(rs.getInt("CREATORID")));
                license.setValidFrom(rs.getTimestamp("VALIDFROM"));
                license.setValidUntil(rs.getTimestamp("VALIDUNTIL"));
                license.setActive(rs.getBoolean("IS_ACTIVE"));
                map.put(id, license);
                ids = ArrayUtils.addElementUnique(ids, id);
            }
            
            for (int i = 0; i < ids.length; i++) {
                licenses.add(map.get(ids[i]));
            }
            
            return licenses;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }

    public static final VKCLicense retrieveLicense(Integer licenseId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            
            return retrieveLicense(licenseId, conn);
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    public static final VKCLicense retrieveLicense(Integer licenseId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        String text = null;
        
        if (licenseId == null) {
            return null;
        }

        try {
            pStmt = conn.prepareStatement("SELECT * FROM license WHERE licenseId = ? ORDER BY languageId ASC, licensePart ASC, created DESC");
            pStmt.setInt(1, licenseId.intValue());
            rs = pStmt.executeQuery();
            VKCLicense license = new VKCLicense();
            
            while (rs.next()) {
                license.setLicenseId(new Integer(rs.getInt("LICENSEID")));
                Integer languageId = new Integer(rs.getInt("LANGUAGEID"));
                
                text = license.getLicense(languageId);
                
                if (text == null) {
                    text = "";
                }
                
                license.setLicense(languageId, text + rs.getString("LICENSETEXT"));
                license.setCreated(rs.getTimestamp("CREATED"));
                license.setCreatorId(new Integer(rs.getInt("CREATORID")));
                license.setValidFrom(rs.getTimestamp("VALIDFROM"));
                license.setValidUntil(rs.getTimestamp("VALIDUNTIL"));
                license.setActive(rs.getBoolean("IS_ACTIVE"));
            }
            
            if (license.getLicenseId() != null) {
                return license;
            }
            
            return null;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }

    public static final List retrieveUserLicense(Integer userId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            
            return retrieveUserLicense(userId, conn);
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    public static final List retrieveUserLicense(Integer userId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        List licenses = new ArrayList();
        
        if (userId == null) {
            return licenses;
        }

        try {
            pStmt = conn.prepareStatement("SELECT * FROM userlicense WHERE userId = ? ORDER BY accepted DESC, licenseId ASC");
            pStmt.setInt(1, userId.intValue());
            rs = pStmt.executeQuery();

            while (rs.next()) {
                Integer id = new Integer(rs.getInt("LICENSEID"));
                VKCLicense license = retrieveLicense(id, conn);
                
                if (license != null) {
                    license.setAccepted(rs.getTimestamp("ACCEPTED"));

                    if (license.isValid()) {
                        licenses.add(0, license);
                    } else {
                        licenses.add(license);
                    }
                }
            }
            
            return licenses;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }

    public static final boolean hasUserAcceptedLicense(Integer userId, Integer licenseId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            
            return hasUserAcceptedLicense(userId, licenseId, conn);
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    public static final boolean hasUserAcceptedLicense(Integer userId, Integer licenseId, Connection conn)  throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        
        if (userId == null || licenseId == null) {
            return false;
        }

        try {
            pStmt = conn.prepareStatement("SELECT * FROM userlicense WHERE userId = ? AND licenseId = ? ORDER BY accepted DESC, licenseId ASC");
            pStmt.setInt(1, userId.intValue());
            pStmt.setInt(2, licenseId.intValue());
            rs = pStmt.executeQuery();

            if (rs.next()) {
                return true;
            }
            
            return false;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }

    public static final void saveUserLicense(Integer userId, Integer licenseId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            saveUserLicense(userId, licenseId, conn);
            conn.commit();
        } catch (Exception ex) {
            DBHelper.rollBack(conn);
            throw new DBException(ex);
        } finally {
            DBHelper.close(conn);
        }
    }

    public static final void saveUserLicense(Integer userId, Integer licenseId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;

        if (userId == null || licenseId == null) {
            return;
        }

        try {
        	if(hasUserAcceptedLicense(userId,licenseId,conn)){
        		//TODO:Bug beheben: doppelte Ausführung falls License akzeptiert und Password geändert werden muss
        	}else{
	            pStmt = conn.prepareStatement("INSERT INTO userlicense ( licenseId, userId, accepted, created, creatorId ) VALUES ( ?, ?, ?, ?, ? )");
	            Timestamp now = new Timestamp(System.currentTimeMillis());
	            pStmt.setInt(1, licenseId.intValue());
	            pStmt.setInt(2, userId.intValue());
	            pStmt.setTimestamp(3, now);
	            pStmt.setTimestamp(4, now);
	            pStmt.setInt(5, userId.intValue());
	            pStmt.execute();
        	}
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }

    public static final void setLicenseActive(Integer licenseId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            setLicenseActive(licenseId, conn);
            conn.commit();
        } catch (Exception ex) {
            DBHelper.rollBack(conn);
            throw new DBException(ex);
        } finally {
            DBHelper.close(conn);
        }
    }

    public static final void setLicenseActive(Integer licenseId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;

        if (licenseId == null) {
            return;
        }

        try {
            pStmt = conn.prepareStatement("UPDATE license SET is_active = ?");
            pStmt.setInt(1, 0);
            pStmt.execute();

            DBHelper.close(pStmt);
            pStmt = conn.prepareStatement("UPDATE license SET is_active = ? WHERE licenseId = ?");
            pStmt.setInt(1, 1);
            pStmt.setInt(2, licenseId.intValue());
            pStmt.execute();
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }

    public static final void deleteLicense(Integer licenseId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            deleteLicense(licenseId, conn);
            conn.commit();
        } catch (Exception ex) {
            DBHelper.rollBack(conn);
            throw new DBException(ex);
        } finally {
            DBHelper.close(conn);
        }
    }

    public static final void deleteLicense(Integer licenseId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;

        if (licenseId == null) {
            return;
        }

        try {
            pStmt = conn.prepareStatement("DELETE FROM license WHERE licenseId = ?");
            pStmt.setInt(1, licenseId.intValue());
            pStmt.execute();
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }
}