package YalpAuth.YalpPGSqlAuth;

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

import YalpInterfaces.*;

public class YalpAuthInterfaceImpl {
	private String db;
	private String dbuser;
	private String dbpasswd;
	private Statement stat;
	private Connection con;

/*
 * Constructor establishes connection to Database Server
 *
 * @param db
 * 	 jdbc Connection to database
 * @param dbYalpUser
 * 	 userName for database
 * @param dbPasswd
 * 	 password for database
 */
	public YalpAuthInterfaceImpl(String db, String dbYalpUser, String dbPasswd) {
		try{
			this.dbuser = dbYalpUser;
			this.dbpasswd = dbPasswd;
			Class.forName("org.postgresql.Driver");
			con = DriverManager.getConnection(db,dbuser,dbpasswd);
			System.out.println("YalpAuth: PGSQL connection established");
			this.stat= con.createStatement();
		} catch (SQLException e) {
			System.out.println("Exception in DbConnection Constructor: "+e);
		} catch (ClassNotFoundException e) {
			System.out.println("Exception in DbConnection Constructor: "+e);
		}
	}

/*
 * 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 int userVerify(String username,String passwd) {
		try{
			ResultSet result=stat.executeQuery("select * from \"user\" where \"username\" = '"+username+"' and \"passwd\" = '"+passwd+"';");

			if (result.next()){
				if (result.getBoolean(5)==true){
					return 2;
				}else return 1;
			}else return 0;
		}catch (SQLException e){
			 System.out.println("Exception in DbConnection.userVerify: "+e);
			 return 0;
		}
	}

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