import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLWarning;
import java.sql.SQLException;
import java.io.IOException;
import java.util.Vector;


class  CDataBaseManager {

    // *** modify this constants as necessary
    final static String DB_NAME			= "wicqdb";
    final static String DB_USER			= "admin";
    final static String DB_PASSWORD		= "sa";

    private final static String dbUrl	= "jdbc:odbc:" + DB_NAME;
    private Connection	connection		= null;


	protected synchronized Vector sp_RetrieveAllUsers ()  {
		Statement retrieveAllUsers = null;
		try {
			// Load the sun jdbc-odbc bridge driver
			// Register JDBC/ODBC Driver in jdbc DriverManager
			// On some platforms with some java VMs, newInstance() is necessary...
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
			// connect to the jdbc-odbc bridge driver
			connection = DriverManager.getConnection(dbUrl, DB_USER, DB_PASSWORD);
			// Check for, and display and warnings generated by the connect
			checkForWarning( connection.getWarnings ());
			// Get the DatabaseMetaData object and display some information about the connection
			//DatabaseMetaData dma = connection.getMetaData (); //System.out.println("\nConnected to " + dma.getURL() );System.out.println("Driver       "   + dma.getDriverName() );System.out.println("Version      "   + dma.getDriverVersion() );
			retrieveAllUsers = connection.createStatement();
			ResultSet resultSet = retrieveAllUsers.executeQuery("select * from User");
			Vector users = new Vector();
			while (resultSet.next())	{
				users.addElement( new CUser(resultSet.getString("userLogin"),resultSet.getString("userPassword"),resultSet.getInt("userId")));
			}
			resultSet.close();
			retrieveAllUsers.close();
			connection.close();
			return users;
		} catch (SQLException ex) {
			// Error, a SQLException was generated. Display the error information
			System.out.println ("--------- SQLException caught ------------");
			System.out.println(ex); 
		} catch (java.lang.Exception ex) {   
			// All other types of exceptions
			ex.printStackTrace();
		}
		return null;
	}

	protected synchronized Vector sp_RetrieveUserFriends (int userid) {
		Statement RetrieveUserFriends = null;
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
			connection = DriverManager.getConnection(dbUrl, DB_USER, DB_PASSWORD);
			RetrieveUserFriends = connection.createStatement();

			// OBS friends is comming only with a valid Id for a while
			ResultSet resultSet = RetrieveUserFriends.executeQuery("select friendid from Friends where userid = " + userid);
			Vector friend = new Vector();
			while (resultSet.next())	{
				friend.addElement( new CUser(resultSet.getInt("friendId")));
			}
			resultSet.close();
			RetrieveUserFriends.close();
			connection.close();
			return friend;
		} catch (SQLException ex) {
			// Error, a SQLException was generated. Display the error information
			System.out.println ("--------- SQLException caught ------------");
			System.out.println(ex); 
		} catch (java.lang.Exception ex) {   
			// All other types of exceptions
			ex.printStackTrace();
		}
		return null;
	}

	protected synchronized boolean sp_insertUser (CUser auser) {
		Statement insertUser = null;
		try {
			// connect to the jdbc-odbc bridge driver
			connection = DriverManager.getConnection(dbUrl, DB_USER, DB_PASSWORD);
			insertUser = connection.createStatement();
			insertUser.executeQuery("insert into user ( userLogin , userPassword) Values('" + auser.getName()+"','"+auser.getPassword()+"')" );
			insertUser.close();
			connection.close();
			return true;
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (java.lang.Exception ex){   
			// All other types of exceptions
			ex.printStackTrace();
		}
		return false;
	}
	
	public synchronized boolean sp_DeleteUser (int userID)  {
		Statement DeleteUser = null;
		try {
			// connect to the jdbc-odbc bridge driver
			connection = DriverManager.getConnection(dbUrl, DB_USER, DB_PASSWORD);
			DeleteUser = connection.createStatement();
			DeleteUser.executeQuery("delete from user where userId = " + userID );
			DeleteUser.close();
			connection.close();
			return true;
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (java.lang.Exception ex){   
			// All other types of exceptions
			ex.printStackTrace();
		}
		return false;
	}
	public synchronized boolean sp_UpdateUser (CUser auser) {
		Statement UpdateUser = null;
		try {
			// connect to the jdbc-odbc bridge driver
			connection = DriverManager.getConnection(dbUrl, DB_USER, DB_PASSWORD);
			UpdateUser = connection.createStatement();
			UpdateUser.executeQuery("update from user set(userLogin='"+ auser.getName() +"',userPassword='"+auser.getPassword()+"') where userId = " + auser.getId());
			UpdateUser.close();
			connection.close();
			return true;
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (java.lang.Exception ex){   
			ex.printStackTrace();
		}
		return false;
	}
	public boolean sp_SaveMessage(int from,int to, String message) {
		Statement SaveMessage = null;
		try {
			// connect to the jdbc-odbc bridge driver
			connection = DriverManager.getConnection(dbUrl, DB_USER, DB_PASSWORD);
			SaveMessage = connection.createStatement();
			SaveMessage.executeQuery("insert into MessageQueue (userId,Message,userFromId ) values('" + to +"','" + message +"', '"+from+"')" );
			SaveMessage.close();
			connection.close();
			return true;
		} catch (SQLException ex) {
			//ex.printStackTrace();
			System.out.println("TEnho que me lembrar de ver isto CDataBaseManager!!!");
		} catch (java.lang.Exception ex){   
			// All other types of exceptions
			ex.printStackTrace();
		}
		return false;
	}

	//-------------------------------------------------------------------
	// Check any SQL warning. Returns true if a warning occurs
	//-------------------------------------------------------------------
	private boolean checkForWarning (SQLWarning warn ) throws SQLException, IOException
	{
		boolean result = false;

		// any warning ?
		if (warn != null)
		{
			System.out.println ("--------- SQL Warning ---------");
			result = true;
			while (warn != null)
			{
				System.out.println ("ErrorCode: " + warn.getErrorCode () );
				System.out.println ("SQLState:  " + warn.getSQLState () );
				System.out.println ("Message:   " + warn.getMessage () );
				warn = warn.getNextWarning ();
			}
		}
		return result;
	}

}