package YalpInputs.YalpPGSqlInput;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;
import java.sql.*;

import org.omg.CosNaming.*;
import org.omg.CosNaming.NamingContextPackage.*;
import org.omg.CORBA.*;

import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

import YalpInterfaces.*;

public class YalpInputPluginImpl extends InputPluginInterfacePOA {

	private String db;
	private String dbuser;
	private String dbpasswd;
	private Statement stat;
	private Connection con;

	private PluginInfo pluginInfo;

	private String log4jFile = "log4j_input_plugin.conf";

	private static Logger logger =
		Logger.getLogger("Yalp.InputPlugins.PGSqlInput.YalpInputPluginImpl");

	private ORB orb;

	public YalpInputPluginImpl()
	{
		PropertyConfigurator.configureAndWatch(log4jFile);
		logger.debug("YalpInputPluginImpl()");
	}

	public void setORB(ORB _orb)
	{
		orb = _orb;
		String db = "jdbc:postgresql://localhost:5432/yalp";
		String dbYalpUser = "yalp";
		String dbPasswd = "yalp";

		try{
			dbuser = dbYalpUser;
			dbpasswd = dbPasswd;
			Class.forName("org.postgresql.Driver");
			con = DriverManager.getConnection(db,dbuser,dbpasswd);
			System.out.println("YalpPGSqlInput: db connection established");
				stat= con.createStatement();
		} catch (SQLException e) {
			System.out.println("Exception in PGSqlInput Constructor: "+e);
		} catch (ClassNotFoundException e) {
			System.out.println("Exception in PGSqlInput Constructor: "+e);
		}
	}

/*
 * cuts Strings and returns an ArrayList of the cutted Strings
 *
 * @param str
 * 		String to cut
 * @return ArrayList<String>
 * 		ArrayList with cutted Strings
 */
	private ArrayList <String> stringCut (String str) {

		int i=0,j=0;
		ArrayList<String> list= new ArrayList<String>();

		while( j != -1 ) {
			j=str.indexOf(" ", i);
			if (j!=-1) {
				list.add(new String (str.substring(i,j)));
				i=j+1;
			} else
				list.add( new String( str.substring( i, str.length() ) ) );
		}
		return list;
	}

	public void search( String str, MediaType[] types, MediasHolder result,
		YalpErrorHolder err) {

		logger.debug("search("+str+")");

		ArrayList <String> searchWords = stringCut(str);
		String query = new String();
		Boolean first, doIntersect, sound, video, image;
		doIntersect = false;
		sound = false;
		video = false;
		image = false;

		for( int i = 0; i < types.length; i++ )
		{
			switch( types[i].value() )
			{
				case MediaType._SOUND:
					sound = true;
					break;
				case MediaType._VIDEO:
					video = true;
					break;
				case MediaType._IMAGE:
					image = true;
					break;
			}
		}

		//for( int i = 0; i < searchWords.size(); i++ ) {
		for( String pattern : searchWords ) {
			first = true;

			if( doIntersect )
				query = query+") intersect ";
			else
				doIntersect = true;

			query += "select * from \"Medias\" where (";

			if( sound ) {
				first = false;
				query += "\"type\" = 'a' ";
			}
			if( image ) {
				if( first )
					first = false;
				else
					query += "or ";
				query += "\"type\" = 'i' ";
			}
			if( video ) {
				if( first )
					first = false;
				else
					query += "or ";
				query += "\"type\" = 'v' ";
			}
			if( !first )
				query += ") and (";

			query += "\"name\" Ilike '%";
			query += pattern;
			query += "%' or \"tags\" Ilike '%";
			query += pattern;
			/*
			query += "%' or \"album\" Ilike '%";
			query += pattern;
			query += "%'or \"year\" Ilike '%";
			query += pattern;
			query += "%'or \"category\" Ilike '%";
			query += pattern;
			*/
			query += "%'";
		}
		query += ")order by \"id\";";

		logger.debug("sending SQL request: "+query);

		try {

			Statement stat = con.createStatement();
			ResultSet sqlResult = stat.executeQuery(query);

			System.out.println("found: ");
			while( sqlResult.next() ) {
				System.out.print(sqlResult.getString(3)+": ");
				System.out.print(sqlResult.getString(4)+"\t| ");
				System.out.println(sqlResult.getString(6));
			/*
				result.add(
					new Result( sqlResult.getInt(1),
											sqlResult.getString(2),
											--->
											result.getString(3),
											result.getString(4),
											result.getString(5),
											result.getString(6),
											result.getString(7),
											result.getString(8),
											result.getInt(9),
											result.getInt(10),
											result.getString(11),
											result.getInt(12),
											result.getString(13),
											result.getString(14),
											result.getString(15) ) );
				*/
			}
			System.out.println();

		} catch( SQLException e ) {
			System.out.println("Exception in PGSqlInput.search: "+e);
		}

	}

// == REIMPLEMENTATION NEEDED : == //

/*
 * submits changes to yalpMediaDatabase
 *
 * @param change
 * 	 describes the change to commit
 * @return int
 * 	 -1 if failed
 */
	public void changeMedia (Media toChange, Action todo, YalpErrorHolder err) {
		/* t.b.d. alter this to new database design
    try{
  	String sql1,sql2,sql3;
			int Return;
      switch (change.updateType){
      // if updateType is UPDATE 
      case UPDATE: 
              sql1="update \"medien\" set \"type\"='"+change.type+"', \"title\"='"+change.title+"', \"author\"='"+change.author+"', \"album\"='"+change.album+"', \"category\"='"+change.category+"', \"year\"='"+change.year+"', \"duration\"='"+change.duration+"', \"aBitrate\"="+change.aBitrate+", \"vBitrate\"="+change.vBitrate+", \"resolution\"='"+change.resolution+"', \"lastEdit\"='"+new java.util.Date(System.currentTimeMillis()).toString()+"' where \"id\"= "+change.id+" ;";
              System.out.println(sql1);
              Return=stat.executeUpdate(sql1);
              break;
      // if updateType is INSERT INTO
      case INSERT:
              sql1="insert into \"medien\" values(nextval('medienId'),'"+change.type+"','"+change.title+"','"+change.author+"','"+change.album+"','"+change.category+"','"+change.year+"','"+change.duration+"',"+change.aBitrate+","+change.vBitrate+",'"+change.resolution+"',"+change.ownerId+",'"+new java.util.Date(System.currentTimeMillis()).toString()+"','"+change.path+"','"+change.name+"');";
              System.out.println(sql1);
              Return=stat.executeUpdate(sql1);
              break;
      // if updateType is DELETE
      case DELETE: 
              sql1="delete from \"medien\" where \"id\"= "+change.id+" ;";
              System.out.println(sql1);
              Return=stat.executeUpdate(sql1);
              break;
      default : return -1;
      }
      // perform operation on table an return number of updated rows
    }catch(SQLException e){
    	System.out.println("Exception in PGSqlInput.changeMedia: "+e);
    }
					*/
	}

/*
 * returns number of medias in database
 *
 * @return String
 * 		 Number of medias in database
 */
	public void getNumOfMedias(IntHolder num, YalpErrorHolder err) {
		try{
			ResultSet result=this.stat.executeQuery("select count (\"id\") from \"medien\";");
			result.next();
			num = new IntHolder(new Integer(result.getString(1)));
		}catch(SQLException e){
			System.out.println("exception in PGSql Input getNumOfMedias: "+e);
			YalpError error = new YalpError();
			error.code = YalpErrorCode.ERROR_SQL;
			error.level= YalpErrorLevel.ERROR_LEVEL_ERROR;
			error.descr= e.toString();
			err = new YalpErrorHolder(error);
		}
	}

/*
 * returns plugin information
 * @param PluginInfoHolder info holder for PluginInformation
 * @param YalpErrorHolder err holder for error information
 */
	public void getInfo(PluginInfoHolder info, YalpErrorHolder err)
	{
		info = new PluginInfoHolder(pluginInfo);
	}
}