import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;


public class BanqueJDBC {
		
	Connection conn;
	
	public BanqueJDBC(String nomBD) {
		try {
		    Class.forName("org.h2.Driver");
		    conn = DriverManager.getConnection("jdbc:h2:"+nomBD+";IGNORECASE=TRUE", "sa", "");
		    // on cree un objet Statement qui va permettre l'execution des requetes
	        Statement s = conn.createStatement();
	
	        // On regarde si la table existe deja
	        String query = "select id from BANQUE limit 1";
	        try {
	        	s.executeQuery(query);
	        } catch(Exception e) {
	        	// sinon on l'a cree
	        	s.execute("create table BANQUE  ( " +
	        			" id VARCHAR( 256 ) NOT NULL PRIMARY KEY, " +
	        			" solde REAL , " +
	        			" dateDerniereOperation TIMESTAMP)");
	        	// on ajoute des entrees de test
	        	s.executeUpdate("insert into BANQUE values ('Toto', 1000.5, CURRENT_TIMESTAMP())");
	        	s.executeUpdate("insert into BANQUE values ('Titi', 500.5, CURRENT_TIMESTAMP())");
	        	s.executeUpdate("insert into BANQUE values ('Tata', 20.0, CURRENT_TIMESTAMP())");
	        	s.executeUpdate("insert into BANQUE values ('Tutu', 2000.0, CURRENT_TIMESTAMP())");
	        }
		} catch(Exception e) {
			// il y a eu une erreur
			e.printStackTrace();
		}
	}
	
	public boolean creerCompte(String id, double somme) {
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery("select solde from BANQUE where id = '"+id+"'");
	        if (rs.next()) {
	        	// un compte existe deja avec cet id
	        	return false;
	        } else {
	        	s.executeUpdate("insert into BANQUE values ('"+id+"', "+somme+", CURRENT_TIMESTAMP())");
	        	return true;
	        }
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public boolean ajouter(String id, double somme) {	
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery("select solde from BANQUE where id = '"+id+"'");
	        if (rs.next()) {
	        	double solde = rs.getDouble("solde");
	        	solde+=somme;
	        	if (s.executeUpdate("update BANQUE set solde="+solde+",dateDerniereOperation=CURRENT_TIMESTAMP() where id = '"+id+"'")==1)
					return true;
	        	else
	        		return false;
	        } else {
	        	return false;
	        }
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}	
	}
	
	public boolean retirer(String id, double somme) {
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery("select solde from BANQUE where id = '"+id+"'");
	        if (rs.next()) {
	        	double solde = rs.getDouble("solde");
	        	solde-=somme;
	        	if (s.executeUpdate("update BANQUE set solde="+solde+",dateDerniereOperation=CURRENT_TIMESTAMP() where id = '"+id+"'")==1)
					return true;
	        	else
	        		return false;
	        } else {
	        	return false;
	        }
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}	
	}

	public Position position(String id) {
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery("select solde,dateDerniereOperation from BANQUE where id = '"+id+"'");
	        if (rs.next()) {
	        	double solde = rs.getDouble("solde");
	        	Date date = rs.getTimestamp("dateDerniereOperation");
	        	Position p = new Position(solde);
	        	p.setDerniereOperation(date);
	        	return p;
	        } else {
	        	return null;
	        }
		} catch(Exception ex) {
			// il y a eu une erreur
			ex.printStackTrace();
			return null;
		}
	}
	
	public void fermer() throws Exception {		
		try {
			conn.close();
		} catch(Exception ex) {
			// il y a eu une erreur
			ex.printStackTrace();
		}
	}
	
	public static void main(String[] args) throws Exception {
		BanqueJDBC banque = new BanqueJDBC("banque");
		
		banque.creerCompte("Bobby", 1000);
		System.out.println(" Compte Bobby : "+banque.position("Bobby"));
		banque.creerCompte("Bob", 1000);
		System.out.println(" Compte Bob : "+banque.position("Bob"));
		banque.ajouter("Bobby", 100);
		banque.retirer("Bobby", 150);
		System.out.println(" Compte Bobby : "+banque.position("Bobby"));

		banque.fermer();		
	}

}