package org.fnppl.opensdx.keyserver;

/*
 * Copyright (C) 2010-2015 
 * 							fine people e.V. <opensdx@fnppl.org> 
 * 							Henning Thieß <ht@fnppl.org>
 * 
 * 							http://fnppl.org
*/

/*
 * Software license
 *
 * As far as this file or parts of this file is/are software, rather than documentation, this software-license applies / shall be applied.
 *  
 * This file is part of openSDX
 * openSDX 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.
 * 
 * openSDX 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 Lesser General Public License
 * and GNU General Public License along with openSDX.
 * If not, see <http://www.gnu.org/licenses/>.
 *      
 */

/*
 * Documentation license
 * 
 * As far as this file or parts of this file is/are documentation, rather than software, this documentation-license applies / shall be applied.
 * 
 * This file is part of openSDX.
 * Permission is granted to copy, distribute and/or modify this document 
 * under the terms of the GNU Free Documentation License, Version 1.3 
 * or any later version published by the Free Software Foundation; 
 * with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. 
 * A copy of the license is included in the section entitled "GNU 
 * Free Documentation License" resp. in the file called "FDL.txt".
 * 
 */

import java.io.BufferedReader;
import java.io.File;
import java.io.InputStreamReader;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.Vector;

import org.fnppl.opensdx.common.Util;
import org.fnppl.opensdx.gui.DefaultMessageHandler;
import org.fnppl.opensdx.keyserver.helper.IdGenerator;
import org.fnppl.opensdx.keyserver.helper.SQLStatement;
import org.fnppl.opensdx.security.Identity;
import org.fnppl.opensdx.security.KeyApprovingStore;
import org.fnppl.opensdx.security.KeyLog;
import org.fnppl.opensdx.security.KeyLogAction;
import org.fnppl.opensdx.security.KeyStatus;
import org.fnppl.opensdx.security.MasterKey;
import org.fnppl.opensdx.security.OSDXKey;
import org.fnppl.opensdx.security.SecurityHelper;
import org.fnppl.opensdx.security.Signature;
import org.fnppl.opensdx.security.SubKey;
import org.fnppl.opensdx.xml.Document;
import org.fnppl.opensdx.xml.Element;

public class PostgresBackend implements KeyServerBackend {

	private String DB_DRIVER = "org.postgresql.Driver";
	
	public Connection con;
	private File data_path = null;
	
	private PostgresBackend() {
		con = null;
		
		//Load DB_Driver
		try {
			Class.forName(DB_DRIVER);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static PostgresBackend init(String user, String pw, String dbname, File data_path) {
		PostgresBackend be = new PostgresBackend();
		if (data_path==null) {
			be.data_path = new File(System.getProperty("user.home"), "db_data");
		} else {
			be.data_path = data_path;
		}
		be.data_path.mkdirs();
		
		System.out.println("PostgresBackend::init::using data_path: "+be.data_path.getAbsolutePath());
		be.connect(user, pw, dbname);
		return be;
	}
	
	private File getFileFromID(long id, String ending) {
		String name = ""+id;
		File result = data_path;
		if (name.length()>5) {
			result = new File(result, name.substring(0,name.length()-5));
		}
		if (ending!=null) name += ending;
		return new File(result, name);
	}
	
	public void addKeysAndLogsFromKeyStore(String filename) {
		try {
			File f = new File(filename);
			KeyApprovingStore store = KeyApprovingStore.fromFile(f, new DefaultMessageHandler());
			Vector<OSDXKey> keys = store.getAllKeys();
			for (OSDXKey key : keys) {
				addKey(key);
			}
			Vector<KeyLog> logs =store.getKeyLogs();
			for (KeyLog log : logs) {
				addKeyLog(log);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	
	public boolean hasKey(String keyid) {
		keyid = OSDXKey.getFormattedKeyIDModulusOnly(keyid);
		boolean has = false;
		try {
			SQLStatement sql = new SQLStatement("SELECT keysha1 FROM keys WHERE keysha1=?");
			sql.setString(1, keyid);
			
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());
			if (rs.next()) {
				has = true;
			}
			rs.close();
			stmt.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return has;
	}
	
	public void setupEmptyDB() {
		URL emptyDB = PostgresBackend.class.getResource("resources/setupEmptyDB.txt");
		try {
			Statement stmt = con.createStatement();
            BufferedReader in = new BufferedReader(new InputStreamReader(emptyDB.openStream()));
            String line = null;
            String nextCommand = "";
            while ((line = in.readLine())!=null) {
            	int trenn = line.indexOf(";");
            	if (trenn>=0) {
            		nextCommand += line.substring(0,trenn);
            		//executeCommand
            		if (nextCommand.length()>3) {
    					try {
    					System.out.println("SQL::"+nextCommand);
    					stmt.execute(nextCommand);
    					} catch (SQLException innerEx) {
    						innerEx.printStackTrace();
    					}		
    				}
            		nextCommand = line.substring(trenn+1);
            	} else {
            		nextCommand += line;
            	}
            }
            //execute last command
			if (nextCommand.length()>3) {
				try {
				System.out.println("SQL::"+nextCommand);
				stmt.execute(nextCommand);
				} catch (SQLException innerEx) {
					innerEx.printStackTrace();
				}		
			}
            in.close();
        } catch (Exception ex) {
        	ex.printStackTrace();
        }
        
//        CREATE TABLE "keylogs" (
//        		"id"				BIGINT,
//        		"ipv4"				VARCHAR(15),
//        		"ipv6"				VARCHAR(100),
//        		"keyid_to" 			VARCHAR(200),
//        		"action"			VARCHAR(30),
//        		"action_id"			BIGINT,
//        		"action_msg"		VARCHAR(200),
//        		"sha256_complete"	BYTEA NOT NULL,
//        		"sha256_restricted"	BYTEA NOT NULL,
//        		"asig_md5"			BYTEA NOT NULL,
//        		"asig_sha1"			BYTEA NOT NULL,
//        		"asig_sha256"		BYTEA NOT NULL,
//        		"asig_datetime"		TIMESTAMP,
//        		"asig_dataname"		VARCHAR(200),
//        		"asig_keyid"		VARCHAR(200),
//        		"asig_bytes" 		BYTEA NOT NULL,
//        		"sha256"			BYTEA NOT NULL,
//        		"sig_md5"			BYTEA NOT NULL,
//        		"sig_sha1"			BYTEA NOT NULL,
//        		"sig_sha256"		BYTEA NOT NULL,
//        		"sig_datetime"		TIMESTAMP,
//        		"sig_dataname"		VARCHAR(200),
//        		"sig_keyid"			VARCHAR(200),
//        		"sig_bytes" 		BYTEA NOT NULL,
//        		PRIMARY KEY(id),
//        		FOREIGN KEY (asig_keyid) REFERENCES keys(keyid),
//        		FOREIGN KEY (sig_keyid) REFERENCES keys(keyid)
//        	);;
	}
	
	public void connect(String user, String pw, String dbname) {
		try {
			con = DriverManager.getConnection(dbname, user, pw);
			System.out.println("Connection established DB: "+dbname); 
		} catch (Exception e) {
			con = null;
			e.printStackTrace();
			throw new RuntimeException("Connection to DB could not be established.");
		}
	}
	
	public boolean isConnected() {
		if (con==null) {
			return false;
		} else {
			return true;
		}
	}
	
	
	public void closeDBConnection() {
		try {
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void addKey(OSDXKey key) {
		if (hasKey(key.getKeyID())) return;
		try {
			SQLStatement sql = new SQLStatement("INSERT INTO keys (keysha1, keyserver, level, usage, valid_from, valid_until, algo, bits, modulus, exponent, parentkeysha1, parentkeyserver) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
			sql.setString(1, key.getKeyModulusSHA1());
			sql.setString(2, key.getAuthoritativekeyserver());
			sql.setString(3, key.getLevelName());
			sql.setString(4, key.getUsageName());
			sql.setTimestamp(5, new Timestamp(key.getValidFrom()));
			sql.setTimestamp(6, new Timestamp(key.getValidUntil()));
			sql.setString(7, "RSA");
			sql.setInt(8, key.getPubKey().getBitCount());
			sql.setString(9, SecurityHelper.HexDecoder.encode(key.getPublicModulusBytes(),':',-1));
			sql.setString(10, "0x"+SecurityHelper.HexDecoder.encode(key.getPubKey().getPublicExponentBytes(),':',-1));
			
			if (key instanceof SubKey && ((SubKey)key).getParentKeyID()!=null) {
				String pkid = ((SubKey)key).getParentKeyID();
				String pkid_sha1 = OSDXKey.getFormattedKeyIDModulusOnly(pkid);
				String pkid_ks = OSDXKey.getKeyServerFromKeyID(pkid);
				if (pkid_ks==null || pkid_ks.equals("")) {
					pkid_ks = key.getAuthoritativekeyserver();
				}
				sql.setString(11, pkid_sha1);
				sql.setString(12, pkid_ks);
			} else {
				sql.setString(11, "");
				sql.setString(12, key.getAuthoritativekeyserver());
			}
			
			Statement stmt = con.createStatement();
			System.out.println("addKey:: "+sql.toString());
			stmt.executeUpdate(sql.toString());
			stmt.close();
			
			//add identities for masterkey
			if (key.isMaster()) {
				Vector<Identity> ids = ((MasterKey)key).getIdentities();
				if (ids!=null) {
					for (Identity id : ids) {
						addIdentity(id, key.getKeyID());
					}
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	
	public OSDXKey getKey(String keyid) {
		String keysha1 = OSDXKey.getFormattedKeyIDModulusOnly(keyid);
		OSDXKey key = null;
		try {
			SQLStatement sql = new SQLStatement("SELECT * FROM keys WHERE keysha1=?");
			sql.setString(1, keysha1);
			
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());
			
			if (rs.next()) {
				key = buildKey(rs);
			}
			rs.close();
			stmt.close();
			//con.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return key;
	}
	
	private OSDXKey buildKey(ResultSet rs) {
		try {
			Element pk = new Element("pubkey");
			pk.addContent("keyid", rs.getString("keysha1")+"@"+rs.getString("keyserver"));
			pk.addContent("level", rs.getString("level"));
			pk.addContent("usage", rs.getString("usage"));
			pk.addContent("valid_from", SecurityHelper.getFormattedDate(rs.getTimestamp("valid_from").getTime()));
			pk.addContent("valid_until", SecurityHelper.getFormattedDate(rs.getTimestamp("valid_until").getTime()));
			pk.addContent("algo", rs.getString("algo"));
			pk.addContent("bits", ""+rs.getInt("bits"));
			pk.addContent("modulus", rs.getString("modulus"));
			pk.addContent("exponent", rs.getString("exponent"));
			//Document.buildDocument(pk).output(System.out);
			OSDXKey key = OSDXKey.fromPubKeyElement(pk);
			if (key.isMaster()) {
				//all OR only last IDs??
				
				//all for identities request
				Vector<Identity> ids = getIdentities(key.getKeyID());
				for (Identity idd : ids) {
					((MasterKey)key).addIdentity(idd);
				}
				
//				Identity idd = getLastIdentity(key.getKeyID());
//				if (idd!=null) {
//					((MasterKey)key).addIdentity(idd);
//				}
			}
			if (key instanceof SubKey) {
				String parentkeysha1 = rs.getString("parentkeysha1");
				if (parentkeysha1!=null && parentkeysha1.length()>0) {
					((SubKey)key).setParentKeyID(parentkeysha1+"@"+rs.getString("parentkeyserver"));
				}
			}
			return key;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public Vector<Identity> getIdentities(String keyid) {
		Vector<Identity> ids = new Vector<Identity>();
		try {
			String keysha1 = OSDXKey.getFormattedKeyIDModulusOnly(keyid);
			SQLStatement sql = new SQLStatement("SELECT * FROM identities WHERE keysha1=? ORDER BY identnum");
			sql.setString(1, keysha1);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());
			while (rs.next()) {
				Identity id = buildIdentitiy(rs);
				if (id!=null) {
					ids.add(id);
				}
			}
			rs.close();
			stmt.close();
			//con.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		
		return ids;
	}
	
	public Identity getLastIdentity(String keyid) {
		try {
			String keysha1 = OSDXKey.getFormattedKeyIDModulusOnly(keyid);
			SQLStatement sql = new SQLStatement("SELECT * FROM identities WHERE keysha1=? ORDER BY identnum DESC LIMIT 1");
			sql.setString(1, keysha1);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());
			Identity id = null;
			if (rs.next()) {
				id = buildIdentitiy(rs);
			}
			rs.close();
			stmt.close();
			//con.close();
			return id;
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return null;
	}
	
//	public long addIdentity(Identity id) {
//		return addIdentity(id, null);
//	}

	public long addIdentity(Identity id, String keyid) {
		long idid = IdGenerator.getTimestamp();
		try {
			String keysha1 = OSDXKey.getFormattedKeyIDModulusOnly(keyid);
			SQLStatement sql = new SQLStatement("INSERT INTO identities (keysha1, keyserver, identnum, email, mnemonic, mnemonic_r, company, company_r, unit, unit_r, subunit, subunit_r, function, function_r, surname, surname_r, firstname, firstname_r, middlename, middlename_r, birthday, birthday_r, placeofbirth, placeofbirth_r, city, city_r, postcode, postcode_r, region, region_r, country, country_r, phone, phone_r, fax, fax_r, note, note_r, photo_id, photo_md5, photo_r, most_recent) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			sql.setString(1, keysha1);
			sql.setString(2, OSDXKey.getKeyServerFromKeyID(keyid));
			
			sql.setInt(3, id.getIdentNum());
			sql.setString(4, id.getEmail());
			sql.setString(5, id.getMnemonic());
			sql.setBoolean(6, id.is_mnemonic_restricted());
			sql.setString(7, id.getCompany());
			sql.setBoolean(8, id.is_company_restricted());
			sql.setString(9, id.getUnit());
			sql.setBoolean(10, id.is_unit_restricted());
			sql.setString(11, id.getSubunit());
			sql.setBoolean(12, id.is_subunit_restricted());
			sql.setString(13, id.getFunction());
			sql.setBoolean(14, id.is_function_restricted());
			sql.setString(15, id.getSurname());
			sql.setBoolean(16, id.is_surname_restricted());
			sql.setString(17, id.getFirstNames());
			sql.setBoolean(18, id.is_firstname_s_restricted());
			sql.setString(19, id.getMiddlename());
			sql.setBoolean(20, id.is_middlename_restricted());
			sql.setString(21, id.getBirthdayGMTString());
			sql.setBoolean(22, id.is_birthday_gmt_restricted());
			sql.setString(23, id.getPlaceOfBirth());
			sql.setBoolean(24, id.is_placeofbirth_restricted());
			sql.setString(25, id.getCity());
			sql.setBoolean(26, id.is_city_restricted());
			sql.setString(27, id.getPostcode());
			sql.setBoolean(28, id.is_postcode_restricted());
			sql.setString(29, id.getRegion());
			sql.setBoolean(30, id.is_region_restricted());
			sql.setString(31, id.getCountry());
			sql.setBoolean(32, id.is_country_restricted());
			sql.setString(33, id.getPhone());
			sql.setBoolean(34, id.is_phone_restricted());
			sql.setString(35, id.getFax());
			sql.setBoolean(36, id.is_fax_restricted());
			sql.setString(37, id.getNote());
			sql.setBoolean(38, id.is_note_restricted());
			if (id.getPhotoBytes()!=null) {
				long photoId = IdGenerator.getTimestamp();
				String photoMD5 = SecurityHelper.HexDecoder.encode(SecurityHelper.getMD5(id.getPhotoBytes()),':',-1);
				File f = getFileFromID(photoId, ".png");
				f.getParentFile().mkdirs();
				Util.saveBytesToFile(id.getPhotoBytes(), f);
				sql.setLong(39, photoId);
				sql.setString(40, photoMD5);
			} else {
				sql.setLong(39, -1L);
				sql.setString(40, null);
			}
			sql.setBoolean(41, id.is_photo_restricted());
			sql.setBoolean(42, true); //most_recent
			
			Statement stmt = con.createStatement();
			//System.out.println("add identity:: "+sql.toString());
			stmt.executeUpdate(sql.toString());
			stmt.close();
			
			//set all other most_recent for this keyid to false
			sql = new SQLStatement("UPDATE identities SET most_recent=\'false\' WHERE keysha1=? AND identnum<>?");
			sql.setString(1, keysha1);
			sql.setInt(2, id.getIdentNum());
			
			stmt = con.createStatement();
			stmt.executeUpdate(sql.toString());
			
//			if (keyid!=null) {
//				long kiid = IdGenerator.getTimestamp();
//				sql = new SQLStatement("INSERT INTO key_identity (id, keyid, identity) VALUES (?,?,?)");
//				sql.setLong(1, kiid);
//				sql.setString(2, keyid);
//				sql.setLong(3, idid);
//				stmt = con.createStatement();
//				stmt.executeUpdate(sql.toString());
//				stmt.close();
//			}
			return idid;
		} catch (Exception ex) {
			ex.printStackTrace();
		}	
		return -1L;
	}
	
	private Identity buildIdentitiy(ResultSet rs) {
		try {
			Identity idd = null;
//			SQLStatement sql = new SQLStatement("SELECT * FROM identities WHERE id=?");
//			sql.setLong(1, id);
//			Statement stmt = con.createStatement();
//			ResultSet rs = stmt.executeQuery(sql.toString());
//			if (rs.next()) {
				try {
					idd = Identity.newEmptyIdentity();
					idd.setIdentNum(rs.getInt("identnum"));
					idd.setEmail(rs.getString("email"));
					idd.setMnemonic(rs.getString("mnemonic"));
					idd.set_mnemonic_restricted(rs.getBoolean("mnemonic_r"));
					idd.setCompany(rs.getString("company"));
					idd.set_company_restricted(rs.getBoolean("company_r"));
					idd.setUnit(rs.getString("unit"));
					idd.set_unit_restricted(rs.getBoolean("unit_r"));
					idd.setSubunit(rs.getString("subunit"));
					idd.set_subunit_restricted(rs.getBoolean("subunit_r"));
					idd.setFunction(rs.getString("function"));
					idd.set_function_restricted(rs.getBoolean("function_r"));
					idd.setSurname(rs.getString("surname"));
					idd.set_surname_restricted(rs.getBoolean("surname_r"));
					idd.setMiddlename(rs.getString("middlename"));
					idd.set_middlename_restricted(rs.getBoolean("middlename_r"));
					String bd = rs.getString("birthday");
					if (bd!=null) {
						idd.setBirthday_gmt(bd);
					}
					idd.set_birthday_gmt_restricted(rs.getBoolean("birthday_r"));
					idd.setPlaceofbirth(rs.getString("placeofbirth"));
					idd.set_placeofbirth_restricted(rs.getBoolean("placeofbirth_r"));
					idd.setCity(rs.getString("city"));
					idd.set_city_restricted(rs.getBoolean("city_r"));
					idd.setPostcode(rs.getString("postcode"));
					idd.set_postcode_restricted(rs.getBoolean("postcode_r"));
					idd.setRegion(rs.getString("region"));
					idd.set_region_restricted(rs.getBoolean("region_r"));
					idd.setCountry(rs.getString("country"));
					idd.set_country_restricted(rs.getBoolean("country_r"));
					idd.setPhone(rs.getString("phone"));
					idd.set_phone_restricted(rs.getBoolean("phone_r"));
					idd.setFax(rs.getString("fax"));
					idd.set_fax_restricted(rs.getBoolean("fax_r"));
					idd.setNote(rs.getString("note"));
					idd.set_note_restricted(rs.getBoolean("note_r"));
					long photoId = rs.getLong("photo_id");
					if (photoId!=-1L) {
						File f = getFileFromID(photoId, ".png");
						if (!f.exists()) {	
							throw new RuntimeException("DB DataBackend Error: File "+f.getAbsolutePath()+" does not exist.");
						}
						byte[] calc_md5 = SecurityHelper.getMD5(f);
						byte[] given_md5 = SecurityHelper.HexDecoder.decode(rs.getString("photo_md5"));
						if (!Arrays.equals(calc_md5, given_md5)) {
							throw new RuntimeException("DB DataBackend Error: MD5 Check for file "+f.getAbsolutePath()+" FAILED!");
						}
						idd.setPhoto(f);
					}
					idd.set_photo_restricted(rs.getBoolean("photo_r"));
				} catch (Exception e) {
					e.printStackTrace();
				}
//			}
//			rs.close();
//			stmt.close();
//			//con.close();
			return idd;
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return null;
	}
	
	
	public void removeKey(OSDXKey key) {
		try {
			//TODO remove Identitites to key
			String keysha1 = key.getKeyModulusSHA1();
			SQLStatement sql = new SQLStatement("REMOVE FROM keys WHERE keysha1=?");
			sql.setString(1, key.getKeyID());
			
			Statement stmt = con.createStatement();
			stmt.executeUpdate(sql.toString());
			stmt.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	public void addKeyLog(KeyLog log) {
		addKeyLogWithIdResult(log);
	}
	public long addKeyLogWithIdResult(KeyLog log) {
		Signature asig = log.getActionSignature();
		Signature sig = log.getSignature();
		
		addKey(asig.getKey());
		addKey(sig.getKey());
		
		try {
			long ts = IdGenerator.getTimestamp();
			File f = getFileFromID(ts, "_keylog.xml");
			f.getParentFile().mkdirs();
			Document.buildDocument(log.toElement(true)).writeToFile(f);
			String md5 = SecurityHelper.HexDecoder.encode(SecurityHelper.getMD5(f), ':', -1);
			SQLStatement sql = new SQLStatement("INSERT INTO keylogs (keylogid, keylog_md5, keysha1_to, keyserver_to, keysha1_from, keyserver_from, asig_datetime, sig_datetime) VALUES (?,?,?,?,?,?,?,?)");
			sql.setLong(1, ts);
			sql.setString(2, md5);
			String to_id = log.getKeyIDTo(); 
			sql.setString(3, OSDXKey.getFormattedKeyIDModulusOnly(to_id));
			sql.setString(4, OSDXKey.getKeyServerFromKeyID(to_id));
			String from_id = log.getKeyIDFrom(); 
			sql.setString(5, OSDXKey.getFormattedKeyIDModulusOnly(from_id));
			sql.setString(6, OSDXKey.getKeyServerFromKeyID(from_id));
			
			sql.setTimestamp(7, new Timestamp(asig.getSignDatetime()));
			sql.setTimestamp(8, new Timestamp(sig.getSignDatetime()));
			
			System.out.println("addKeylog:: "+sql.toString());
			Statement stmt = con.createStatement();
			stmt.executeUpdate(sql.toString());
			stmt.close();
			return ts;
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return -1L;
	}
	
//	public void addKeyLog(KeyLog log) {
//		Signature asig = log.getActionSignature();
//		Signature sig = log.getSignature();
//		
//		addKey(asig.getKey());
//		addKey(sig.getKey());
//		
//		try {
//			long ts = IdGenerator.getTimestamp();
//			
//			SQLStatement sql = new SQLStatement("INSERT INTO keylogs (id, ipv4, ipv6, keyid_to, action, action_id, action_msg, " +
//					"sha256_complete, sha256_restricted, asig_md5, asig_sha1, asig_sha256, asig_datetime, asig_dataname, asig_keyid, asig_bytes," +
//												 "sha256, sig_md5,  sig_sha1,  sig_sha256,  sig_datetime,  sig_dataname,  sig_keyid,  sig_bytes)" +
//					" VALUES (?,?,?,?,?,? ,?,?,?,?,?,? ,?,?,?,?,?,? ,?,?,?,?,?,?)");
//			sql.setLong(1, ts);
//			sql.setString(2, log.getIPv4());
//			sql.setString(3, log.getIPv6());
//			sql.setString(4, log.getKeyIDTo());
//			sql.setString(5, log.getAction());
//			Identity idd = log.getIdentity();
//			if (idd!=null) {
//				long idid = addIdentity(idd);
//				sql.setLong(6, idid);
//			}
//			sql.setString(7, log.getMessage());
//			
//			//action signature
//			sql.setBytes(8, log.getActionSha256ProofComplete());
//			sql.setBytes(9, log.getActionSha256ProofRestricted());
//			sql.setBytes(10, asig.getMD5());
//			sql.setBytes(11, asig.getSHA1());
//			sql.setBytes(12, asig.getSHA256());
//			sql.setTimestamp(13, new Timestamp(asig.getSignDatetime()));
//			sql.setString(14, asig.getDataName());
//			sql.setString(15, asig.getKey().getKeyID());
//			sql.setBytes(16, asig.getSignatureBytes());
//			
//			//signature
//			sql.setBytes(17, log.getSHA256LocalProof());
//			sql.setBytes(18, sig.getMD5());
//			sql.setBytes(19, sig.getSHA1());
//			sql.setBytes(20, sig.getSHA256());
//			sql.setTimestamp(21, new Timestamp(sig.getSignDatetime()));
//			sql.setString(22, sig.getDataName());
//			sql.setString(23, sig.getKey().getKeyID());
//			sql.setBytes(24, sig.getSignatureBytes());
//			
//			sql.executeUpdate();
//			sql.close();
//		} catch (Exception ex) {
//			ex.printStackTrace();
//		}
//	}

	
	private long getKeylogIndex(KeyLog log) {
		long index = -1L;
		try {
			Signature asig = log.getActionSignature();
			
			SQLStatement sql = new SQLStatement("SELECT keylogid FROM keylogs WHERE keysha1_to=? AND keysha1_from=? AND asig_datetime=?");
			sql.setString(1, OSDXKey.getFormattedKeyIDModulusOnly(log.getKeyIDTo()));
			sql.setString(2, OSDXKey.getFormattedKeyIDModulusOnly(log.getKeyIDFrom()));
			sql.setTimestamp(3, new Timestamp(asig.getSignDatetime()));
			System.out.println("getKeyLogIndex :: "+sql.toString());
			
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());
			if (rs.next()) {
				try {
					index = rs.getLong(1);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			rs.close();
			stmt.close();
			//con.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return index;
	}
	
	public void addOpenToken(String token, KeyLog log) {
		long klIndex = getKeylogIndex(log);
		if (klIndex<=0) {
			klIndex = addKeyLogWithIdResult(log);
		}
		try {
			SQLStatement sql = new SQLStatement("INSERT INTO approval_token (approvalid, token, keylogid) VALUES (?,?,?)");
			long approvalid = IdGenerator.getTimestamp();
			sql.setLong(1, approvalid);
			sql.setString(2, token);
			sql.setLong(3, klIndex);
			
			Statement stmt = con.createStatement();
			stmt.executeUpdate(sql.toString());
			stmt.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public KeyLog getKeyLogFromTokenId(String id) {
//		long klIndex = -1;
		KeyLog log = null;
		try {
			SQLStatement sql = new SQLStatement("SELECT * FROM approval_token, keylogs WHERE approval_token.keylogid = keylogs.keylogid AND token=?");
			sql.setString(1, id);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());
			if (rs.next()) {
				try {
					log = buildKeylog(rs);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			rs.close();
			stmt.close();
			//con.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
//		if (klIndex<0) {
//			return null;
//		}
//		try {
//			SQLStatement sql = new SQLStatement("SELECT * FROM keylogs WHERE id=?");
//			sql.setLong(1, klIndex);
//			Statement stmt = con.createStatement();
//			ResultSet rs = stmt.executeQuery(sql.toString());
//			if (rs.next()) {
//				try {
//					log = buildKeylog(rs);
//				} catch (Exception e) {
//					e.printStackTrace();
//				}
//			}
//			rs.close();
//			stmt.close();
//			//con.close();
//		} catch (Exception ex) {
//			ex.printStackTrace();
//		}
		return log;
	}
	
	private KeyLog buildKeylog(ResultSet rs) {
		try {
			long id = rs.getLong("keylogid");
			File f = getFileFromID(id, "_keylog.xml");
			if (!f.exists()) {	
				throw new RuntimeException("DB DataBackend Error: File "+f.getAbsolutePath()+" does not exist.");
			}
			byte[] calc_md5 = SecurityHelper.getMD5(f);
			byte[] given_md5 = SecurityHelper.HexDecoder.decode(rs.getString("keylog_md5"));
			if (!Arrays.equals(calc_md5, given_md5)) {
				throw new RuntimeException("DB DataBackend Error: MD5 Check for file "+f.getAbsolutePath()+" FAILED!");
			}
			Element e = Document.fromFile(f).getRootElement();
			KeyLog log = KeyLog.fromElement(e);
			return log;
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return null; 
	}
	
//	private KeyLog buildKeylog(ResultSet rs) {
//		try {
//			OSDXKey asigKey = getKey(rs.getString("asig_keyid"));
//			Element e = new Element("keylog");
//			e.addContent("ipv4",rs.getString("ipv4"));
//			e.addContent("ipv6",rs.getString("ipv6"));
//			
//			Element ea = new Element("keylogaction");
//			//action content
//			ea.addContent("from_keyid", asigKey.getKeyID());
//			ea.addContent("to_keyid", rs.getString("keyid_to"));
//			Element eaa = new Element(rs.getString("action"));
//			Identity idd = getIdentitiy(rs.getLong("action_id"));
//			if (idd!=null) {
//				eaa.addContent(idd.toElement(true));
//			}
//			String msg = rs.getString("action_msg");
//			if (msg!=null) {
//				eaa.addContent("message", msg);
//			}
//			ea.addContent(eaa);
//			ea.addContent("sha256localproof_complete", SecurityHelper.HexDecoder.encode(rs.getBytes("sha256_complete"),':',-1));
//			ea.addContent("sha256localproof_restricted", SecurityHelper.HexDecoder.encode(rs.getBytes("sha256_restricted"),':',-1));
//			
//			Element asig = new Element("signature");
//			Element asigData = new Element("data");
//			asigData.addContent("md5", SecurityHelper.HexDecoder.encode(rs.getBytes("asig_md5"),':',-1));
//			asigData.addContent("sha1", SecurityHelper.HexDecoder.encode(rs.getBytes("asig_sha1"),':',-1));
//			asigData.addContent("sha256", SecurityHelper.HexDecoder.encode(rs.getBytes("asig_sha256"),':',-1));
//			asigData.addContent("signdatetime", SecurityHelper.getFormattedDate(rs.getTimestamp("asig_datetime").getTime()));
//			asigData.addContent("dataname", rs.getString("asig_dataname"));
//			
//			asig.addContent(asigData);
//			asig.addContent(asigKey.getSimplePubKeyElement());
//			asig.addContent("signaturebytes", SecurityHelper.HexDecoder.encode(rs.getBytes("asig_bytes"),'\0',-1));
//			
//			ea.addContent(asig);
//			
//			e.addContent(ea);
//			
//			e.addContent("sha256localproof", SecurityHelper.HexDecoder.encode(rs.getBytes("sha256"),':',-1));
//			Element sig = new Element("signature");
//			Element sigData = new Element("data");
//			sigData.addContent("md5", SecurityHelper.HexDecoder.encode(rs.getBytes("sig_md5"),':',-1));
//			sigData.addContent("sha1", SecurityHelper.HexDecoder.encode(rs.getBytes("sig_sha1"),':',-1));
//			sigData.addContent("sha256", SecurityHelper.HexDecoder.encode(rs.getBytes("sig_sha256"),':',-1));
//			sigData.addContent("signdatetime", SecurityHelper.getFormattedDate(rs.getTimestamp("sig_datetime").getTime()));
//			sigData.addContent("dataname", rs.getString("sig_dataname"));
//			sig.addContent(sigData);
//			sig.addContent(getKey(rs.getString("sig_keyid")).getSimplePubKeyElement());
//			sig.addContent("signaturebytes", SecurityHelper.HexDecoder.encode(rs.getBytes("sig_bytes"),'\0',-1));
//			e.addContent(sig);
//			Document.buildDocument(e).output(System.out);
//			KeyLog log = KeyLog.fromElement(e);
//			return log;
//		} catch (Exception ex) {
//			ex.printStackTrace();
//		}
//		return null; 
//	}

	 
	public Vector<KeyLog> getKeyLogsToID(String keyid) {
		Vector<KeyLog> logs = new Vector<KeyLog>();
		try {
			System.out.println("get keylogs");
			String keysha1 = OSDXKey.getFormattedKeyIDModulusOnly(keyid);
			SQLStatement sql = new SQLStatement("SELECT * FROM keylogs WHERE keysha1_to=? ORDER BY asig_datetime");
			sql.setString(1, keysha1);
			System.out.println("SQL: "+sql.toString());
			
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());
			
			while (rs.next()) {
				try {
					logs.add(buildKeylog(rs));
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			rs.close();
			stmt.close();
			//con.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return logs;
	}

	 
//	public KeyStatus getKeyStatus(String keyid) {
//		Vector<KeyLog> kls = getKeyLogsToID(keyid);
//		if (kls==null || kls.size()==0) return null;
//		for (KeyLog kl : kls) {
//			System.out.println("found keylog... "+kl.getActionDatetimeString());
//		}
//		KeyLog kl = kls.lastElement();
//		String status = kl.getAction();
//		int validity = -1;
//		if (status.equals(KeyLogAction.APPROVAL)) {
//			validity =  KeyStatus.STATUS_VALID;
//		}
//		else if (status.equals(KeyLogAction.DISAPPROVAL)) {
//			validity =  KeyStatus.STATUS_UNAPPROVED;
//		}
//		else if (status.equals(KeyLogAction.APPROVAL_PENDING)) {
//			validity =  KeyStatus.STATUS_UNAPPROVED;
//		}
//		else if (status.equals(KeyLogAction.REVOCATION)) {
//			validity =  KeyStatus.STATUS_REVOKED;
//		}
//		
//		int approvalPoints = 100;
//		OSDXKey key = getKey(keyid);
//		long datetimeValidFrom = key.getValidFrom();
//		long datetimeValidUntil = key.getValidUntil();
//		
//		KeyStatus ks = new KeyStatus(validity, approvalPoints, datetimeValidFrom, datetimeValidUntil, kl);
//		return ks;
//	
//	}
	
	public KeyStatus getKeyStatus(String keyid) {
		return getKeyStatus(keyid, null, System.currentTimeMillis(), null);
	}

	public KeyStatus getKeyStatus(String keyid, String usage, long datetime, String keyidKeyserver) {
		OSDXKey key = getKey(keyid);
		if (key==null) {
			return null;
		}
		Vector<KeyLog> keylogs = getKeyLogsToID(keyid);
		if (keylogs==null) {
			keylogs = new Vector<KeyLog>();
		}
		return KeyStatus.getKeyStatus(key, keylogs, usage, datetime, keyidKeyserver);
	
	}
	 
	public Vector<OSDXKey> getKeysToId(String email) {
		Vector<OSDXKey> keys = new Vector<OSDXKey>();
		try {
			SQLStatement sql = new SQLStatement("SELECT DISTINCT keysha1,keyserver FROM identities WHERE email=?");
			sql.setString(1, email);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());
			while (rs.next()) {
				try {
					OSDXKey key = getKey(rs.getString(1));
					keys.add(key);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			rs.close();
			stmt.close();
			//con.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return keys;
	}

	 
	public Vector<OSDXKey> getSubKeysToId(String parentkeyid) {
		Vector<OSDXKey> keys = new Vector<OSDXKey>();
		try {
			String keysha1 = OSDXKey.getFormattedKeyIDModulusOnly(parentkeyid);
			SQLStatement sql = new SQLStatement("SELECT * FROM keys WHERE parentkeysha1=?");
			sql.setString(1, keysha1);
			System.out.println("SQL: "+sql.toString());
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql.toString());
			while (rs.next()) {
				try {
					OSDXKey key = buildKey(rs);
					if (key!=null) keys.add(key);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			rs.close();
			stmt.close();
			//con.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return keys;
	} 

	 
	public void removeOpenToken(String token) {
		try {
			SQLStatement sql = new SQLStatement("DELETE FROM approval_token WHERE token=?");
			sql.setString(1, token);
			Statement stmt = con.createStatement();
			stmt.executeUpdate(sql.toString());
			stmt.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	 
	public void updateCache(OSDXKey k, KeyLog l) {
		// do nothing
	}

}
