package de.fh_lu.o4s.beans;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import de.fh_lu.o4s.jdbc.DB2Access;
import de.fh_lu.o4s.jdbc.JDBCAccess;

public class UserBean {
	String userid;
	String username;
	String password;
	String email;
	String active;
	String admin;
	JDBCAccess jdbcAcc;

	public UserBean() {
		super();
		jdbcAcc = new DB2Access();
		userid = "";
		username = "";
		password = "";
		email = "";
	}

	public int checkUseridPassword(){
		//  1 - User existiert nicht
		//  0 - User existiert, alles ok
		// 98 - keine Connection
		// 99 - sonstiger Fehler

		String sql = "SELECT userid, password FROM user" +
		 		 "WHERE userid = ? AND password = ?";
		System.out.println(sql);


		java.sql.Connection dbConn = jdbcAcc.getConnection();
		if (dbConn == null){
			return 98;
		}else{
			try {
				PreparedStatement prepStat = dbConn.prepareStatement(sql);
				prepStat.setString(1, this.userid);
				prepStat.setString(2, this.password);
				ResultSet dbRes = prepStat.executeQuery();
				if(dbRes.next()){
					System.out.println("User & Pw gefunden");
					return 0;
			}else{
					System.out.println("user & Pw nicht gefunden");
					return 1;
//				return (debRes.next())? 0 : 1;
			}
				} catch (SQLException e) {
				e.printStackTrace();
				int rc = e.getErrorCode();
				if (rc < 0) return rc;
				else return 99;
			}
	}

	}

	public int readComparePassword(){
		//  1 - User existiert nicht
		//  0 - User existiert, alles ok
		// 98 - keine Connection
		// 99 - sonstiger Fehler

		String sql = "SELECT userid, password FROM user" +
		 		 "WHERE userid = ? AND password = ?";
		System.out.println(sql);


		java.sql.Connection dbConn = jdbcAcc.getConnection();
		if (dbConn == null){
			return 98;
		}else{
			try {
				PreparedStatement prepStat = dbConn.prepareStatement(sql);
				prepStat.setString(1, this.userid);
				prepStat.setString(2, this.password);
				ResultSet dbRes = prepStat.executeQuery();
				if(dbRes.next()){
					System.out.println("User gefunden");
//					getString(2) = hol den String aus dem 2. Feld!
					if (this.password.equals(dbRes.getString(2).trim())) {
						return 0;
					}else{
						return 1;
					}
				}else{
					System.out.println("user nicht gefunden");
					return 1;
//				return (debRes.next())? 0 : 1;
			}
				} catch (SQLException e) {
				e.printStackTrace();
				int rc = e.getErrorCode();
				if (rc < 0) return rc;
				else return 99;
			}
	}

	}


	public int checkUserExists(){
		return this.checkUserExists1();
	}
	public int checkUserExists1(){
		//  0 - User existiert nicht
		//  1 - User existiert
		// 98 - keine Connection
		// 99 - sonstiger Fehler
		// <SQL-Code> - SQL-Fehler < 0

		// mit SELECT * ... WHERE

		String tabelle = "User";
		String stringwert = this.getUserid();
		String spalte ="userid";

		String sql = "SELECT * FROM " + tabelle+ " WHERE " + spalte +" = '"+ stringwert+"'";
		System.out.println(sql);


		java.sql.Connection dbConn = jdbcAcc.getConnection();
		if (dbConn == null){
			return 98;
		}else{
			try {
				Statement myStat = dbConn.createStatement();
				myStat.execute(sql);
				ResultSet x = myStat.getResultSet();
				if (x.equals(null)){
				System.out.println("User existiert nicht");
				return 0;
			}else{ System.out.println("User existiert");
				return 1;
			}
			} catch (SQLException e) {
				e.printStackTrace();
				int rc = e.getErrorCode();
				if (rc < 0) return rc;
				else return 99;
			}
	}
	}
	public int checkUserExists2(){
		//  0 - User existiert nicht
		//  1 - User existiert
		// 98 - keine Connection
		// 99 - sonstiger Fehler
		// <SQL-Code> - SQL-Fehler < 0

		// mit SELECT COUNT(*) ... WHERE
		String tabelle = "User";
		String stringwert = this.getUserid();
		String spalte ="userid";

		String sql = "SELECT * FROM " + tabelle+ " WHERE " + spalte +" = '"+ stringwert+"'";
		System.out.println(sql);


		java.sql.Connection dbConn = jdbcAcc.getConnection();
		if (dbConn == null){
			return 98;
		}else{
			try {
				Statement myStat = dbConn.createStatement();
				myStat.execute(sql);
				ResultSet x = myStat.getResultSet();
				if (x.equals(null)){
				System.out.println("User existiert nicht");
				return 0;
			}else{ System.out.println("User existiert");
				return 1;
			}
			} catch (SQLException e) {
				e.printStackTrace();
				int rc = e.getErrorCode();
				if (rc < 0) return rc;
				else return 99;
			}

		}
	}
	public int checkUserExists3(){
		//  0 - User existiert nicht
		//  1 - User existiert
		// 98 - keine Connection
		// 99 - sonstiger Fehler
		// <SQL-Code> - SQL-Fehler < 0

		// SELECT * ... ohne WHERE
		// mit ResultSet durchsuchen

		String tabelle = "User";
		String stringwert = this.getUserid();
		String spalte ="userid";

		String sql = "SELECT * FROM " + tabelle+ "'";
		System.out.println(sql);

		java.sql.Connection dbConn = jdbcAcc.getConnection();
		if (dbConn == null){
			return 98;
		}else{
			try {
				Statement myStat = dbConn.createStatement();
				myStat.execute(sql);
				ResultSet x = myStat.getResultSet();
				if (x.equals(null)){
				System.out.println("User existiert nicht");
				return 0;
			}else{
//				resultset durchlaufen
				while (x.next()) {
//					user suchen
					if (x.getArray(spalte).equals(stringwert)) {
						System.out.println("User existiert:" );
						return 0;
				}

				}}}


			 catch (SQLException e) {
				e.printStackTrace();
				int rc = e.getErrorCode();
				if (rc < 0) return rc;
				else return 99;
			 }}
		return 99;
	}

	public int insertuserifnew(){
		int rc = this.checkUserExists1();
		if (rc != 0) return rc;
		return this.insertUserNoCheck();
		}

	public int getNumberOfUsers(){
		// >=0 - Befehl ok, Anzahl der User
		//  <0 - SQL-Code
		// -999998 - keine Connection
		// -999999 - sonstiger Fehler

		String sql = "SELECT COUNT(*) FROM USER";
		System.out.println(sql);

		java.sql.Connection dbConn = jdbcAcc.getConnection();
		if (dbConn == null){
			return -999998;
		}else{
			try {
				ResultSet dbRes = dbConn.createStatement().executeQuery(sql);
				if (dbRes.next()){
					int num = dbRes.getInt(1);
					return num;
				}else{
					return -999999;
				}
			} catch (SQLException e) {
				e.printStackTrace();
				int rc = e.getErrorCode();
				if (rc < 0) return rc;
				else return -999999;
			}
		}
	}
	public void prepareAttributesForDB(){
		if (this.userid.length() > 16) userid = userid.substring(0,16);
		if (this.username.length() > 256) username = username.substring(0,256);
		if (this.email.length() > 256) email = email.substring(0,256);
		if (this.password.length() > 32) password = password.substring(0,32);

		if (admin  == null) admin  = "N";
		if (active == null) active = "N";

		if (admin.equals("Y")
				|| admin.equalsIgnoreCase("yes")
				|| admin.equalsIgnoreCase("j")
				|| admin.equalsIgnoreCase("ja")) admin = "Y";
		else admin = "N";
		if (active.equals("Y")
				|| active.equalsIgnoreCase("yes")
				|| active.equalsIgnoreCase("j")
				|| active.equalsIgnoreCase("ja")) active = "Y";
		else active = "N";
	}
	public int updateUserNoCheck(){
		//  0 - alles ok
		// 98 - keine Connection
		// 99 - sonstiger Fehler
		// <SQL-Code> - SQL-Fehler < 0
		this.prepareAttributesForDB();

		String sql = "UPDATE USER " +
					 "SET (username, email, password, " +
								"active, admin) " +
						"= (?, ?, ?, ?, ?) " +
					 "WHERE userid = ?";
		System.out.println(sql);

		java.sql.Connection dbConn = jdbcAcc.getConnection();
		if (dbConn == null){
			return 98;
		}else{
			try {
//				Statement myStat = dbConn.createStatement();
				PreparedStatement myStat = dbConn.prepareStatement(sql);

				myStat.setString(1, this.getUsername());
				myStat.setString(2, this.getEmail());
				myStat.setString(3, this.getPassword());
				myStat.setString(4, this.getActive());
				myStat.setString(5, this.getAdmin());
				myStat.setString(6, this.getUserid());

				myStat.executeUpdate();

				System.out.println("Update-Befehl erfolgreich");
				return 0;
			} catch (SQLException e) {
				e.printStackTrace();
				int rc = e.getErrorCode();
				if (rc < 0) return rc;
				else return 99;
			}
		}
	}
	public int updateUserIfExists(){
		//  0 - alles ok, user wurde geupdated
		//	1 - User Existiert noch nicht, nichts gemacht
		// 98 - keine Connection
		// 99 - sonstiger Fehler
		// <SQL-Code> - SQL-Fehler < 0
		int ret = this.checkUserExists();
		if (ret == 0)return 1;
		if (ret != 1)return ret;
//		nutzer existiert, weil checkuserExists() ==1;
		String tabelle = "User";
		String stringwert = this.getUserid();
		String spalte ="userid";

		String sql = "SELECT USERNAME, PASSWORD, EMAIL, ACTIVE,ADMIN FROM USER WHERE userid = ?";
		System.out.println(sql);
		java.sql.Connection dbConn = jdbcAcc.getConnection();

		if (dbConn == null){
			return 98;
		}else{
			try {
//				Statement myStat = dbConn.createStatement();
				PreparedStatement prepStat = dbConn.prepareStatement(sql);
				prepStat.setString(1, this.getUserid());
				ResultSet dbRes = prepStat.executeQuery();
				if (!dbRes.next())return 99;
//				jetzt steht pointer auf einzigem richtigen datensatz
				this.prepareAttributesForDB();

				dbRes.updateString(username, username);
				dbRes.updateString(password, password);
				dbRes.updateString(email, email);
				dbRes.updateString(active, active);
				dbRes.updateString(admin, admin);

			} catch (SQLException e) {
				e.printStackTrace();
				int rc = e.getErrorCode();
				if (rc < 0) return rc;
				else return 99;
			}
		}
		return 99;
		}




	public int deleteUserNoCheck(String string){
		//  0 - alles ok
		// 98 - keine Connection
		// 99 - sonstiger Fehler
		// <SQL-Code> - SQL-Fehler < 0
		String sql = "DELETE FROM USER " +
						"WHERE userid = '" + this.getUserid() + "'";
		System.out.println(sql);
		java.sql.Connection dbConn = jdbcAcc.getConnection();
		if (dbConn == null){
			return 98;
		}else{
			try {
				dbConn.createStatement().executeUpdate(sql);
				System.out.println("Delete-Befehl erfolgreich");
				return 0;
			} catch (SQLException e) {
				e.printStackTrace();
				int rc = e.getErrorCode();
				if (rc < 0) return rc;
				else return 99;
			}
		}
	}
	public int insertUserNoCheck(){
		//  0 - alles ok
		// 98 - keine Connection
		// 99 - sonstiger Fehler
		// <SQL-Code> - SQL-Fehler < 0
		this.prepareAttributesForDB();
		String sql = "INSERT INTO USER " +
				"(userid, username, email, password, active, admin) " +
				"VALUES ('" +
				this.getUserid()   + "', '" +
				this.getUsername() + "', '" +
				this.getEmail()    + "', '" +
				this.getPassword() + "', '" +
				this.getActive()   + "', '" +
				this.getAdmin()    + "')";
		System.out.println(sql);
		java.sql.Connection dbConn = jdbcAcc.getConnection();
		if (dbConn == null){
			return 98;
		}else{
			try {
				dbConn.createStatement().executeUpdate(sql);
				System.out.println("Insert-Befehl erfolgreich");
				return 0;
			} catch (SQLException e) {
				e.printStackTrace();
				int rc = e.getErrorCode();
				if (rc < 0) return rc;
				else return 99;
			}
		}
	}

	public String getUserid() {
		return userid;
	}
	public void setUserid(String userid) {
		this.userid = userid;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getActive() {
		return active;
	}
	public void setActive(String active) {
		this.active = active;
	}
	public String getAdmin() {
		return admin;
	}
	public void setAdmin(String admin) {
		this.admin = admin;
	}
}