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 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 ORB orb;

	public YalpInputPluginImpl()
	{
		System.out.println("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);
		}
	}

/*
 * 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 an ArrayList of Results which are matching to the commited Find object
 *
 * @param media
 * 		 Object which describes search criterias
 * @return ArrayList<Result>
 * 		 List with Results matching search criteria
 */
	public void search(String str, MediaType[] types, MediasHolder result, YalpErrorHolder err) {
		/* t.b.d. alter this to new database design
        try{
			ArrayList <String > searchWords=stringCut(media.str);
	        String query= new String();
	        Boolean first;
	        for (int i=0;i<searchWords.size();i++){ 
	            first=true;
	            if (i!=0)query = query+") intersect ";
	            query= query+"select * from \"medien\" where (";
	            if (media.audio){  
	                first=false;
	                query=query+"\"type\" = 'audio' ";
	            }
	            if (media.video){
	                if (first)first=false;
	                else query=query+"or ";
	                query=query+"\"type\" = 'video' ";
	            }
	            if (!first)query=query+") and (";
	            query=query+"\"title\" Ilike '%"+searchWords.get(i)+"%' or \"author\" Ilike '%"+searchWords.get(i)+"%' or \"album\" Ilike '%"+searchWords.get(i)+"%'or \"year\" Ilike '%"+searchWords.get(i)+"%'or \"category\" Ilike '%"+searchWords.get(i)+"%'";
	        }
	        query=query+")order by \"id\";";
	        
	        ArrayList<Result> resultList=new ArrayList<Result>();
	        Statement stat= con.createStatement();
			ResultSet result=stat.executeQuery(query);
	        while(result.next()){
	        	resultList.add( new Result(result.getInt(1),result.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)));
			}
			return resultList;
        }catch(SQLException e){
        	System.out.println("Exception in PGSqlInput.search: "+e);
        	return new ArrayList<Result>();
        }
		*/
	}

/*
 * 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;
	}

/*
 * 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);
	}
}