import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

public class BD {
    private Connection c;

    BD() {

        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            c = java.sql.DriverManager.getConnection(
                    "jdbc:mysql://smysql/Bfrayer", "frayer", "frayer");
        } catch (Exception e) {
            System.out.println("Connection ratée: " + e);
            System.exit(-1);
        }
        /*
         * try { Class.forName("com.mysql.jdbc.Driver").newInstance(); c =
         * java.sql.DriverManager.getConnection( "jdbc:mysql://localhost/IUT",
         * "root", "root"); } catch (Exception e) {
         * System.out.println("Connection ratée: " + e); System.exit(-1); }
         */
    }

    private static Connection connectionBase() {
        Connection c = null;
        /*
         * try { try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch
         * (ClassNotFoundException e) { // e.printStackTrace(); } c =
         * DriverManager .getConnection("jdbc:oracle:thin:@servora:1521:oracle",
         * "frayer", "h3vc5d"); } // gestion des exceptions catch (SQLException
         * ex) { System.out.println("Msg:" + ex.getMessage() +
         * ex.getErrorCode()); }
         */
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            c = java.sql.DriverManager.getConnection(
                    "jdbc:mysql://localhost/IUT", "root", "root");
        } catch (Exception e) {
            System.out.println("Connection ratée: " + e);
            System.exit(-1);
        }
        return c;
    }

    public int maxidIndividu(Connection c) throws SQLException {
        Statement s = null;
        int num = 0;
        s = c.createStatement();
        ResultSet rs = null;
        rs = s.executeQuery("Select MAX(code_indiv) from Individu");
        while (rs.next()) {
            num = rs.getInt(1);
        }
        return num + 1;
    }

    public int maxidFilm(Connection c) throws SQLException {
        Statement s = null;
        int num = 0;
        s = c.createStatement();
        ResultSet rs = null;
        rs = s.executeQuery("Select MAX(code_film) from Film");
        while (rs.next()) {
            num = rs.getInt(1);
        }
        return num + 1;
    }

    public void insererPersonne(Connection c, String n, String p,
            String date_n, String date_m, int code_a, int code_r)
            throws SQLException {
        int code = maxidIndividu(c);
        PreparedStatement ps = null;

        ps = c.prepareStatement("insert into Individu values (?,?,?,?,?,?,?)");
        ps.setInt(1, code);
        ps.setString(2, n);
        ps.setString(3, p);
        ps.setString(4, date_n);
        ps.setString(5, date_m);
        ps.setInt(6, code_a);
        ps.setInt(7, code_r);
        ps.executeUpdate();

    }

    public void insererFilm(Connection c, String titreO, String titreF,
            String dateF, int duree, int couleur) throws SQLException {
        int code = maxidFilm(c);
        PreparedStatement ps = null;

        ps = c.prepareStatement("insert into Film values (?,?,?,?,?,?)");
        ps.setInt(1, code);
        ps.setString(2, titreO);
        ps.setString(3, titreF);
        ps.setString(4, dateF);
        ps.setInt(5, duree);
        ps.setInt(6, couleur);
        ps.executeUpdate();

    }

    public void consulterFilmsSimple(Connection c) {
        String titleO, titleFR;
        Date dateF;
        int Duree;
        Statement s = null;
        try {
            s = c.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        ResultSet rs = null;
        try {
            rs = s
                    .executeQuery("Select titre_original, titre_francais, date_film, duree from Film");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // parcours du resultset
        try {
            while (rs.next()) {
                titleO = rs.getString("titre_original");
                titleFR = rs.getString("titre_francais");
                dateF = rs.getDate("date_film");
                Duree = rs.getInt("duree");

                System.out.println(titleO + " " + titleFR + " " + dateF + " ");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public ArrayList<String> getNomsPers(Connection c) throws SQLException {
        ArrayList<String> list = new ArrayList<String>();
        Statement s = null;
        s = c.createStatement();
        ResultSet rs = null;
        rs = s.executeQuery("Select nom from Individu");
        while (rs.next()) {
            list.add(rs.getString("nom"));
        }

        return list;
    }

    public ArrayList<String> getNomsFilm(Connection c) throws SQLException {
        ArrayList<String> list = new ArrayList<String>();
        Statement s = null;
        s = c.createStatement();
        ResultSet rs = null;
        rs = s.executeQuery("Select titre_original from Film");
        while (rs.next()) {
            list.add(rs.getString("titre_original"));
        }

        return list;
    }

    public ArrayList<String> getNomsFilm(Connection c, String cond)
            throws SQLException {
        ArrayList<String> list = new ArrayList<String>();
        Statement s = null;
        s = c.createStatement();
        ResultSet rs = null;
        rs = s
                .executeQuery("Select titre_original from Film where titre_original like '%"
                        + cond + "%' ");
        while (rs.next()) {
            list.add(rs.getString("titre_original"));
        }

        return list;
    }

    public ArrayList<String> getNomsGenre(Connection c) throws SQLException {
        ArrayList<String> list = new ArrayList<String>();
        Statement s = null;
        s = c.createStatement();
        ResultSet rs = null;
        rs = s.executeQuery("Select nom_genre from Genre");
        while (rs.next()) {
            list.add(rs.getString("nom_genre"));
        }

        return list;
    }

    public static ArrayList<String[]> consulterTout(Connection c)
            throws SQLException {
        // Object[][] monRes = new Object[7][3];

        ArrayList<String[]> liste = new ArrayList<String[]>();

        int ligne = 0;
        Statement s = null;
        String rea, film, act, genre, an;
        int codeRea, codeFilm, codeAct, codeGenre, codeAn;
        s = c.createStatement();
        ResultSet rs = null;

        // On séléctionne tous les réalisateurs :
        rs = s
                .executeQuery("Select code_realisateur, nom from Individu where code_acteur = 0;");
        // parcours du resultset

        while (rs.next()) {
            rea = rs.getString("nom");
            codeRea = rs.getInt("code_realisateur");
            Statement sR = null;
            sR = c.createStatement();
            ResultSet rsR = null;
            // On récupère tout les films qu'a fait ce réalisateur :
            rsR = sR
                    .executeQuery("SELECT f.titre_original, f.code_film FROM Film f, Realiser r WHERE f.code_film = r.code_film AND r.code_realisateur = "
                            + codeRea + ";");

            // parcours du resultset
            while (rsR.next()) {
                film = rsR.getString("titre_original");
                codeFilm = rsR.getInt("code_film");

                Statement sF = null;
                sF = c.createStatement();
                ResultSet rsF = null;
                // On séléctionne tous les acteurs qui ont joué dans ce film :
                rsF = sF
                        .executeQuery("SELECT i.nom, i.code_acteur FROM Individu i, Jouer j WHERE j.code_acteur = i.code_acteur AND j.code_film = "
                                + codeFilm + ";");
                while (rsF.next()) {
                    act = rsF.getString("nom");
                    codeAct = rsF.getInt("code_acteur");

                    Statement sG = null;
                    sG = c.createStatement();
                    ResultSet rsG = null;
                    rsG = sG
                            .executeQuery("SELECT g.nom_genre, g.code_genre FROM Genre g, Avoir_un a WHERE a.code_genre = g.code_genre AND a.code_film = "
                                    + codeFilm + "; ");
                    while (rsG.next()) {
                        genre = rsG.getString("nom_genre");
                        codeGenre = rsG.getInt("code_genre");

                        Statement sA = null;
                        sA = c.createStatement();
                        ResultSet rsA = null;
                        rsA = sA
                                .executeQuery("SELECT Year(f.date_film) as annee FROM Film f WHERE f.code_film = "
                                        + codeFilm + "; ");
                        while (rsA.next()) {
                            an = rsA.getString("annee");

                            String[] tmp = { rea, film, act, genre, an };
                            liste.add(tmp);
                            ligne++;
                        }

                    }
                }

            }

        }
        return liste;
    }

    public Connection getConnection() {
        return c;
    }
}