package org.javaldap.server.backend;

/*
The JavaLDAP Server
Copyright (C) 2000  Clayton Donley (donley@linc-dev.com) - All Rights Reserved

This program 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.

This program 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 this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.

*/

/**
 * BackendJDBC is a Backend implementation that uses JDBC to manage information
 * in a particular set of tables within an RDBMS. Note that BackendDB is the one that
 * will eventually do LDAP-to-RDBMS mapping. This backend expects and/or creates a
 * particular set of tables that map easily to the LDAP protocol and information
 * model.
 *
 * @author: Clayton Donley
 */

import java.util.*;
import java.io.*;
import java.sql.*;

import org.javaldap.ldapv3.*;
import org.javaldap.server.Entry;
import org.javaldap.server.EntryChange;
import org.javaldap.server.EntrySet;
import org.javaldap.server.schema.SchemaChecker;

import org.javaldap.server.util.ServerConfig;
import org.javaldap.server.util.DirectorySchemaViolation;
import org.javaldap.server.util.DirectoryException;
import org.javaldap.server.util.InvalidDNException;
import org.javaldap.server.util.Logger;

import org.javaldap.server.syntax.DirectoryString;


public class BackendJDBC implements Backend {

	Vector exactIndexes = null;
	long idCounter = 0;

	public BackendJDBC() {
	
		

		try {
			Class.forName((String)ServerConfig.getInstance().get(ServerConfig.JAVALDAP_BACKENDJDBC_DBDRIVER)).newInstance();
		} catch (Exception e) {
			Logger.getInstance().log(Logger.LOG_NORMAL,"Error: " + e.getMessage());
		}

		// The indexes we will create. Hardcoded at the moment.
		this.exactIndexes = new Vector();
		this.exactIndexes.addElement(new DirectoryString("cn"));
		this.exactIndexes.addElement(new DirectoryString("sn"));
		this.exactIndexes.addElement(new DirectoryString("l"));
		this.exactIndexes.addElement(new DirectoryString("ou"));
		this.exactIndexes.addElement(new DirectoryString("o"));
		this.exactIndexes.addElement(new DirectoryString("objectclass"));
		this.exactIndexes.addElement(new DirectoryString("description"));
		this.exactIndexes.addElement(new DirectoryString("seealso"));
		
		/*
		Hashtable transTable = new Hashtable();

		String[] sn = {"person","first_name"};
		transTable.put("sn",sn);
		
		String[] givenName = {"person","last_name"};
		transTable.put("givenName",givenName);
		
		String[] employeeNumber = {"person","employee_id"};
		transTable.put("employeeNumber",employeeNumber);
		
		String[] postalAddress = {"address","address"};
		transTable.put("postalAddress", postalAddress);
		
		String[] l = {"address","city"};
		transTable.put("l",l);
		
		String[] st = {"address","state"};
		transTable.put("st",st);
		
		String[] zip = {"address","zip"};
		transTable.put("zip",zip);
		*/
		
	}
	public LDAPResultEnum add(Entry entry) {

		Statement s = null;
		ResultSet rs = null;

		Connection dbcon = null;
		try {
			dbcon = (Connection)BackendJDBCConnPool.getInstance().checkOut();
			s = dbcon.createStatement();

			PreparedStatement tmpPS = dbcon.prepareStatement("SELECT entryid FROM entry where dn = ?");
			tmpPS.setString(1,entry.getName().toString());
			rs = tmpPS.executeQuery();
			
			if (rs.next()) {
				// Entry with this name already exists
				BackendJDBCConnPool.getInstance().checkIn(dbcon);
				return new LDAPResultEnum(68);
			}
		} catch (SQLException se) {
			se.printStackTrace();
			initialize();
		}  catch (Exception e) {
			e.printStackTrace();
			if (dbcon != null) {
				BackendJDBCConnPool.getInstance().checkIn(dbcon);
				return new LDAPResultEnum(53);
			}
		}
		
		try {

			rs = s.executeQuery("SELECT value FROM keyTable WHERE keyValue = 'entryCount'");
			rs.next();
			idCounter = new Long(rs.getString(1)).longValue();
			idCounter++;
			entry.setID(idCounter);
			s.execute("UPDATE keyTable SET value = '" + idCounter + "' WHERE keyValue = 'entryCount'");

			byte[] byteEntry = entry.getAsByteArray();


			PreparedStatement ps = dbcon.prepareStatement("INSERT into entry VALUES(?,?,?,?,?,?,?,?)");
			ps.setLong(1,idCounter);
			ps.setString(2,entry.getName().toString());
			ps.setString(3,entry.getBase().toString());
			ps.setBytes(4,byteEntry);
			ps.setString(5,"cn=Root");
			ps.setString(6,"cn=Root");
			ps.setString(7,"timestampHere");
			ps.setString(8,"ModifyTimestampHere");
			ps.execute();
			

			for (Enumeration indexEnum = exactIndexes.elements(); indexEnum.hasMoreElements();) {
				DirectoryString attr = (DirectoryString)indexEnum.nextElement();
				if (entry.containsKey(attr)) {
					for (Enumeration valsEnum = ((Vector)entry.get(attr)).elements(); valsEnum.hasMoreElements(); ) {
						ps = dbcon.prepareStatement("INSERT INTO " + attr + " VALUES (?,?)");
						ps.setLong(1,idCounter);
						ps.setString(2,((DirectoryString)valsEnum.nextElement()).normalize());
						ps.execute();
					}
				}
			}
			dbcon.commit();
			BackendJDBCConnPool.getInstance().checkIn(dbcon);
		} catch (SQLException se) {
			se.printStackTrace();
			try {
				dbcon.rollback();
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			}
			BackendJDBCConnPool.getInstance().checkIn(dbcon);
			return new LDAPResultEnum(53);
		}
		return new LDAPResultEnum(0);
	}
	private String constructFilter(Filter currentFilter) {

		
		switch (currentFilter.choiceId) {
		case Filter.EQUALITYMATCH_CID:
			DirectoryString matchType = new DirectoryString(currentFilter.equalityMatch.attributeDesc);
			DirectoryString matchVal = new DirectoryString(currentFilter.equalityMatch.assertionValue);
			if (exactIndexes.contains(matchType)) {
			//	return new String("SELECT entryid FROM " + matchType + " WHERE UPPER(value) = UPPER('" + matchVal + "')");
				return new String("SELECT " + matchType + ".entryid FROM " + matchType + " WHERE " + matchType + ".value = '" + matchVal.normalize() + "'");
			}
			break;

		case Filter.PRESENT_CID:
			matchType = new DirectoryString(currentFilter.present);
			if (exactIndexes.contains(matchType)) {
				return new String("SELECT " + matchType + ".entryid FROM " + matchType);
			}
			break;

		case Filter.SUBSTRINGS_CID:
			matchType = new DirectoryString(currentFilter.substrings.type);
		    String subfilter = new String();
			for (Enumeration substrEnum = currentFilter.substrings.substrings.elements(); substrEnum.hasMoreElements();) {
				SubstringFilterSeqOfChoice oneSubFilter = (SubstringFilterSeqOfChoice)substrEnum.nextElement();
				if (oneSubFilter.choiceId == oneSubFilter.INITIAL_CID) {
					subfilter = subfilter.concat(new String(oneSubFilter.initial) + "%");
				} else if (oneSubFilter.choiceId == oneSubFilter.ANY_CID) {
					if (subfilter.length() == 0) {
						subfilter = subfilter.concat("%");
					}
					subfilter = subfilter.concat(new String(oneSubFilter.any) + "%");
				} else if (oneSubFilter.choiceId == oneSubFilter.FINAL1_CID) {
					if (subfilter.length() == 0) {
						subfilter = subfilter.concat("%");
					}
					subfilter = subfilter.concat(new String(oneSubFilter.final1));
				}
			}
			if (exactIndexes.contains(matchType)) {
				// return new String("SELECT entryid FROM " + matchType + " WHERE UPPER(value) LIKE UPPER('" + subfilter + "')");
				return new String("SELECT " + matchType + ".entryid FROM " + matchType + " WHERE " + matchType + ".value LIKE '" + new DirectoryString(subfilter).normalize() + "'");

			}
			break;
		case Filter.AND_CID:
			String strFilt = new String();
			
			for (Enumeration andEnum = currentFilter.and.elements(); andEnum.hasMoreElements();) {

				//strFilt = strFilt.concat("(" + constructFilter((Filter)andEnum.nextElement()) + ")");
				strFilt = strFilt.concat(constructFilter((Filter)andEnum.nextElement()));
				if (andEnum.hasMoreElements()) {
					strFilt = strFilt.concat(" INTERSECT ");
				}
			}
			return strFilt;

		case Filter.OR_CID:
			strFilt = new String();
			
			for (Enumeration orEnum = currentFilter.or.elements(); orEnum.hasMoreElements();) {

				//strFilt = strFilt.concat("(" + constructFilter((Filter)orEnum.nextElement()) + ")");
				strFilt = strFilt.concat(constructFilter((Filter)orEnum.nextElement()));
				if (orEnum.hasMoreElements()) {
					strFilt = strFilt.concat(" UNION ");
				}
			}
			return strFilt;

		case Filter.NOT_CID:
			// Need to fix this...Not a correct implementation
			//Vector matched = evaluateFilter(currentFilter.not,base,scope);
			//matchThisFilter.removeAll(matched);

			break;


		}
		return new String();
	}
	public LDAPResultEnum delete(DirectoryString name) {
		Connection dbcon = null;
		try {
			dbcon = (Connection)BackendJDBCConnPool.getInstance().checkOut();
			Statement s = dbcon.createStatement();
			ResultSet rs = s.executeQuery("SELECT entryid FROM ENTRY WHERE dn = '" + name + "'");
			if (rs.next()) {
				long entryid = rs.getLong(1);
				s.execute("DELETE FROM entry WHERE entryid = " + entryid + "");
				Enumeration indexEnum = exactIndexes.elements();
				while (indexEnum.hasMoreElements()) {
					s.execute("DELETE FROM " + indexEnum.nextElement() + " WHERE entryid = " + entryid);
				}
			}
			dbcon.commit();
			BackendJDBCConnPool.getInstance().checkIn(dbcon);
		} catch (Exception e) {
			e.printStackTrace();
			try {
				dbcon.rollback();
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			}
			BackendJDBCConnPool.getInstance().checkIn(dbcon);
		}
		return new LDAPResultEnum(0);
	}
private EntrySet evaluateFilter(Filter currentFilter, DirectoryString base, int scope) {
	EntrySet results = null;
	String filtStr = constructFilter(currentFilter);
	String scopeQuery = null;
	
	if (scope == SearchRequestEnum.WHOLESUBTREE) {
		scopeQuery = "entry.base LIKE '%" + base + "'";
	} else if (scope == SearchRequestEnum.SINGLELEVEL) {
		scopeQuery = "entry.base = '" + base + "'";
	} else {
		scopeQuery = "entry.dn = '" + base + "'";
	}
	
	//String fullQuery = new String("SELECT entry.entrydata FROM entry WHERE entry.entryid IN (SELECT DISTINCT entry.entryid FROM entry WHERE " + scopeQuery + 
	//	" AND entry.entryid IN (" + filtStr + "))");


	//String fullQuery = new String("SELECT entry.entrydata FROM entry WHERE entry.entryid IN (" +
	//	filtStr + ") AND " + scopeQuery);
	String fullQuery = new String("SELECT entry.entryid FROM entry WHERE entry.entryid in (" + filtStr + ") AND " + scopeQuery);

	Connection dbcon = null;
	ResultSet rs = null;
	Statement s = null;
	try {
		dbcon = (Connection)BackendJDBCConnPool.getInstance().checkOut();
		s = dbcon.createStatement();
		rs = s.executeQuery(fullQuery);
		//results = new JDBCEntrySet(this,rs,dbcon);
		Vector entryids = new Vector();
		while (rs.next()) {
		    entryids.addElement(new Long(rs.getLong(1)));
		}
		results = new GenericEntrySet(this,entryids);
		rs.close();
		s.close();
		BackendJDBCConnPool.getInstance().checkIn(dbcon);
	} catch (Exception e) {
		e.printStackTrace();
		try {
		  s.close();
		  rs.close();
		} catch (SQLException se) {
		}
		BackendJDBCConnPool.getInstance().checkIn(dbcon);
		results = new GenericEntrySet();
	}
	return results;
}
	public EntrySet get(DirectoryString base, int scope, Filter filter,
					  boolean typesOnly, Vector attributes) {

		return (EntrySet)evaluateFilter(filter,base, scope);
	}
	public Entry getByDN(DirectoryString dn) throws DirectoryException {
		Entry entry = null;
		Connection dbcon = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			dbcon = (Connection)BackendJDBCConnPool.getInstance().checkOut();
			ps = dbcon.prepareStatement("SELECT entryData from entry where dn = ?");
			ps.setString(1,dn.toString());

			rs = ps.executeQuery();
			rs.next();