import java.sql.*;
import java.util.Scanner;

public class Connecter {
    static final String CONN_URL = "jdbc:oracle:thin:@ensibm.imag.fr:1521:ensi2";
    static final String USER = "leroyo";
    static final String PASSWD = "leroyo";
    static Connection conn;
    protected static PreparedStatement stmt;
    protected static ResultSet rset;


    public Connecter() {
        try {
            // Enregistrement du driver Oracle
            System.out.print("Loading Oracle driver... ");
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            System.out.println("loaded");

            // Etablissement de la connection
            System.out.print("Connecting to the database... ");
            conn = DriverManager.getConnection(CONN_URL, USER, PASSWD);
            System.out.println("connected");

            // Desactivation des commits auto
            conn.setAutoCommit(false);
            conn.setTransactionIsolation(conn.TRANSACTION_SERIALIZABLE);

        } catch (SQLException e) {
            System.err.println("Connexion failed");
            e.printStackTrace(System.err);
        }
    }

    public static Connection getConnec(){
        return conn;
    }

    public void fermer() {
        try {
            conn.close();
        } catch (SQLException e) {
            System.err.println("Connexion failed");
            e.printStackTrace(System.err);
        }
    }

    public static void dumpResultSet(ResultSet rset) throws SQLException {
        ResultSetMetaData rsetmd = rset.getMetaData();
        int i = rsetmd.getColumnCount();
        for (int k=1;k<=i;k++)
            System.out.print(rsetmd.getColumnName(k) + "\t");
        System.out.println();
        while (rset.next()) {
            for (int j = 1; j <= i; j++) {
                System.out.print(rset.getString(j) + "\t");
            }
            System.out.println();
        }
        rset.close();
    }


    public static void main(String args[]) {
        new Connecter();
        System.out.println("");
        System.out.println("Que voulez-vous faire ?");
        System.out.println("1 : Initialiser la base");
        System.out.println("2 : Gérer les restaurants");
        System.out.println("3 : Gérer les cartes");
        System.out.println("4 : Gérer les réservations");
        System.out.println("Veuillez saisir votre choix");
        Scanner sc = new Scanner(System.in);
        int choix = sc.nextInt();
        sc.nextLine();
        if(choix ==1){
        String path = "/home/ensi2a/leroy/Ensimag_2B";
                    String query = "start " + path +"/projetBD/creationTables.sql";
        System.out.println(query);
        try {
            stmt = getConnec().prepareStatement(query);
            stmt.executeUpdate();
            stmt.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        query = "start " + path +"/projetBD/creationSequences.sql";
        System.out.println(query);
        try {
            stmt = getConnec().prepareStatement(query);
            stmt.execute();
            stmt.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        query = "start " + path +"/projetBD/insertionExemple.sql";
        System.out.println(query);
        try {
            stmt = getConnec().prepareStatement(query);
            stmt.execute();
            stmt.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        }
        else if(choix ==2){
            //CreerRestaurants.main(null);
        }
        else if (choix == 3){
            System.out.println("TO DO");
        }
        else if (choix == 4){
            System.out.println("TO DO");
        }
        //Puis on ferme la connexion
        try {
            conn.close();
        } catch (SQLException e) {
            System.err.println("Connexion failed");
            e.printStackTrace(System.err);
        }
    }
}