package YalpAuth.YalpPGSqlAuth;

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 YalpAuthPluginImpl extends AuthPluginInterfacePOA{
	private String db;
	private String dbuser;
	private String dbpasswd;
	private Statement stat;
	private Connection con;

	private PluginInfo pluginInfo;

	private String log4jFile = "log4j_auth_plugin.conf";

	private static Logger logger =
		Logger.getLogger("Yalp.AuthPlugins.PGSqlAuth.YalpAuthPluginImpl");

	private ORB orb;

/*
 * Constructor establishes connection to Database Server
 *
 * @param db
 * 	 jdbc Connection to database
 * @param dbYalpUser
 * 	 userName for database
 * @param dbPasswd
 * 	 password for database
 */
	public YalpAuthPluginImpl()
	{
		PropertyConfigurator.configureAndWatch(log4jFile);
		logger.debug("YalpAuthPluginImpl()");
	}

	public void setORB(ORB _orb)
	{
		orb = _orb;

		/* t.b.d. read from config xml */
		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);
		}
	}

	public void setInfo(PluginInfo info)
	{
		logger.debug("interfaceImpl - setInfo(): "+info.id);
		pluginInfo = info;
	}

/*
 * checks if user exists in yalpYalpUser Database and what rights he has
 *
 * @param username
 * 		 username to check
 * @param passwd
 * 		 password to check
 *
 * @return enum privilege level
 */
	public void userVerify(String username, String passwd, YalpErrorHolder err,
		YalpUserHolder user)
	{

		YalpError error = new YalpError ("auth ok", YalpErrorCode.OK,
			YalpErrorLevel.ERROR_LEVEL_INFO, "authentication module working");

		try{
			ResultSet result=stat.executeQuery("select * from \"user\" where \"username\" = '"+username+"' and \"passwd\" = '"+passwd+"';");

			if (result.next()){
				if (result.getBoolean(5)==true)
				{
					user.value.level = AccessRights.ADMIN;
					err.value = error;
					return;
				}
				else
				{
					user.value.level = AccessRights.USER;
					err.value = error;
					return;
				}
			}
			else
			{
				user.value.level = AccessRights.DENY;
				err.value = error;
				return;
			}
		}catch (SQLException e){
			user.value.level = AccessRights.DENY;
			error.code = YalpErrorCode.ERROR_SQL;
			error.msg = "failed to send auth request to pgsql db";
			error.level = YalpErrorLevel.ERROR_LEVEL_ERROR;
			error.descr = e.toString();
			err.value = error;
			return;
		}
	}

/*
 * returns an ArrayList with all yalpYalpUsers and Admins
 *
 * @return ArrayList<YalpUser>
 * 		 list with all YalpYalpUsers and Admins
 */
	public void getUser(UsersHolder list, YalpErrorHolder err) {
		try {
			ArrayList<YalpUser> resultList =new ArrayList<YalpUser>();
			YalpUser actUser = new YalpUser();
			String query = "select * from \"user\"order by \"id\";";
			Statement stat= con.createStatement();
			ResultSet result=stat.executeQuery(query);

			while(result.next())
			{
				/* t.b.d. - create YalpUser according to new database design */
				// result.getInt(1),result.getString(2),result.getString(3),result.getString(4),result.getBoolean(5)))
				resultList.add( actUser );
			}
			YalpUser[] u = new YalpUser[1];
			list = new UsersHolder(resultList.toArray(u));
		} catch (SQLException e) {
			YalpError error = new YalpError();
			error.code = YalpErrorCode.ERROR_SQL;
			error.descr = e.toString();
			error.level = YalpErrorLevel.ERROR_LEVEL_ERROR;
			err = new YalpErrorHolder(error);
		}
	}

/*
 * submits changes to yalpYalpUserDatabase
 *
 * @param change
 * 		 describes the change to commit
 * @return int
 * 		 -1 if failed
 */
	public void changeUser(YalpUser usr, String passwd, Action todo,
		YalpErrorHolder err) {

			/* t.b.d. alter to new db design
        try{
	    	String sql;
	        switch (todo.type){
	        // if updateType is UPDATE 
	        case Action._UPDATE: 
	                sql="update \"user\" set \"username\"='"+usr.name+"', \"passwd\"='"+change.passwd+"', \"realname\"='"+change.realname+"', \"admin\"="+change.admin+" where \"id\"= "+change.id+" ;";
	                break;
	        // if updateType is INSERT INTO
	        case Action._INSERT:
	                sql="insert into \"user\" values(nextval('userId'), '"+usr+"','"+change.passwd+"','"+change.realname+"',"+change.admin+");";
	                break;
	        // if updateType is DELETE
	        case Action._DELETE: 
	                sql="delete from \"user\" where \"id\"= "+change.id+" ;";
	                break;
					default:
					//errorhandling
	        }
	        // perform operation on table an return number of updated rows
	        System.out.println(sql);
	        return stat.executeUpdate(sql); 
        }catch(SQLException e){
        	System.out.println("Exception in DbConnection.changeYalpUser: "+e);
        }
			*/
	}

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