package db;

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




public class Conectare {

    String error;
    Connection con;

    public Conectare() {
    	System.out.println("sa apelat Conectare din db");
    }

    public void connect() throws ClassNotFoundException, SQLException, Exception {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db?useSSL=false", "root", "biceps");
        } catch (ClassNotFoundException cnfe) {
        	System.out.println("eroarea mea este"+cnfe.getMessage());
           // error = "ClassNotFoundException: Nu s-a gasit driverul bazei de date.";
            throw new ClassNotFoundException(error);
        } catch (SQLException cnfe) {
            error = "SQLException: Nu se poate conecta la baza de date.";
            throw new SQLException(error);
        } catch (Exception e) {
            error = "Exception: A aparut o exceptie neprevazuta in timp ce se stabilea legatura la baza de date.";
            throw new Exception(error);
        }
    } // connect()

    public void connect(String bd) throws ClassNotFoundException, SQLException, Exception {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/" + bd, "root", "biceps");
        } catch (ClassNotFoundException cnfe) {
            error = "ClassNotFoundException: Nu s-a gasit driverul bazei de date.";
            throw new ClassNotFoundException(error);
        } catch (SQLException cnfe) {
            error = "SQLException: Nu se poate conecta la baza de date.";
            throw new SQLException(error);
        } catch (Exception e) {
            error = "Exception: A aparut o exceptie neprevazuta in timp ce se stabilea legatura la baza de date.";
            throw new Exception(error);
        }
    } // connect(String bd)

    public void connect(String bd, String ip) throws ClassNotFoundException, SQLException, Exception {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + bd, "root", "biceps");
        } catch (ClassNotFoundException cnfe) {
            error = "ClassNotFoundException: Nu s-a gasit driverul bazei de date.";
            throw new ClassNotFoundException(error);
        } catch (SQLException cnfe) {
            error = "SQLException: Nu se poate conecta la baza de date.";
            throw new SQLException(error);
        } catch (Exception e) {
            error = "Exception: A aparut o exceptie neprevazuta in timp ce se stabilea legatura la baza de date.";
            throw new Exception(error);
        }
    } // connect(String bd, String ip)

    public void disconnect() throws SQLException {
        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException sqle) {
            error = ("SQLException: Nu se poate inchide conexiunea la baza de date.");
            throw new SQLException(error);
        }
    } // disconnect()

    public void adaugaMuzician(String Nume, String Prenume, String Email,String NumarTelefon)
            throws SQLException, Exception {
        if (con != null) {
            try {
// create a prepared SQL statement
                Statement stmt;
                stmt = con.createStatement();
                stmt.executeUpdate("insert into muzician(Nume, Prenume, Email, NumarTelefon) values('" + Nume + "'  , '" + Prenume + "', '" + Email +"','"+NumarTelefon+ "');");

            } catch (SQLException sqle) {
                error = "ExceptieSQL: Reactualizare nereusita; este posibil sa existe duplicate.";
                throw new SQLException(error);
            }
        } else {
            error = "Exceptie: Conexiunea cu baza de date a fost pierduta.";
            throw new Exception(error);
        }
    } // end of adaugaPacient()

    public void adaugaStilmuzical(String CasaCD, String Gen)
            throws SQLException, Exception {
        if (con != null) {
            try {
// create a prepared SQL statement
                Statement stmt;
                stmt = con.createStatement();
                stmt.executeUpdate("insert into stilmuzical(CasaCD , Gen) values('" + CasaCD + "'  , '" + Gen + "');");

            } catch (SQLException sqle) {
                error = "ExceptieSQL: Reactualizare nereusita; este posibil sa existe duplicate.";
                throw new SQLException(error);
            }
        } else {
            error = "Exceptie: Conexiunea cu baza de date a fost pierduta.";
            throw new Exception(error);
        }
    } // end of adaugaMedic()

    public void adaugaAlbum(String idMuzician, String idStilmuzical, String Nume, String Data_lansare, String Nr_melodii,String Pret)
        throws SQLException, Exception {
    	System.out.println("connectDB");
	    if (con != null) {
	        try {

	// create a prepared SQL statement
	            Statement stmt;
	            stmt = con.createStatement();
	            stmt.executeUpdate("insert into album(idMuzician, idStilmuzical, Nume, Datalansare, Nrmelodii, Pret) values('" + Integer.parseInt(idMuzician) + "'  , '" + Integer.parseInt(idStilmuzical) + "', '" + Nume + "', '" + Data_lansare + "', '" + Nr_melodii+ "', '" +Pret+ "');");

	        } catch (SQLException sqle) {
	        	System.out.println(idMuzician+" "+idStilmuzical+" "+Nume+" "+Data_lansare+" "+Nr_melodii+" "+Pret);
	            error = "ExceptieSQL: Reactualizare nereusita; este posibil sa existe duplicate.";
	            System.out.println(sqle.getMessage());
	            throw new SQLException(error);
	        }
	    } else {
	        error = "Exceptie: Conexiunea cu baza de date a fost pierduta.";
	        throw new Exception(error);
	    }
    }	 // end of adaugaConsultatie()

    public ResultSet vedeTabela(String tabel) throws SQLException, Exception {
        ResultSet rs = null;
        try {
            String queryString = ("select * from `db`.`" + tabel + "`;");
            Statement stmt = con.createStatement(/*ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY*/);
            rs = stmt.executeQuery(queryString);
        } catch (SQLException sqle) {
            error = "SQLException: Interogarea nu a fost posibila.";
            throw new SQLException(error);
        } catch (Exception e) {
            error = "A aparut o exceptie in timp ce se extrageau datele.";
            throw new Exception(error);
        }
        return rs;
    } // vedeTabela()

    public ResultSet vedeAlbum() throws SQLException, Exception {
        ResultSet rs = null;
        try {
            String queryString = ("select a.Nume , a.Prenume , a.Email, a.NumarTelefon, "
            		+ "b.CasaCD , b.Gen , c.idAlbum, c.idStilmuzical , c.idMuzician , c.Nume, c.Datalansare, c.Nrmelodii, c.Pret "
            		+ "from muzician a, stilmuzical b, album c "
            		+ "where a.idMuzician = c.idMuzician and b.idStilmuzical = c.idStilmuzical;");
            Statement stmt = con.createStatement(/*ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY*/);
            rs = stmt.executeQuery(queryString);
        } catch (SQLException sqle) {
        	System.out.println(sqle.getMessage());
            error = "SQLException: Interogarea nu a fost posibila.";
            throw new SQLException(error);
        } catch (Exception e) {
            error = "A aparut o exceptie in timp ce se extrageau datele.";
            throw new Exception(error);
        }
        return rs;
    } // vedeConsultatie()

    public void stergeDateTabela(String[] primaryKeys, String tabela, String dupaID) throws SQLException, Exception {
        if (con != null) {
            try {
// create a prepared SQL statement
                long aux;
                PreparedStatement delete;
                delete = con.prepareStatement("DELETE FROM " + tabela + " WHERE " + dupaID + "=?;");
                for (int i = 0; i < primaryKeys.length; i++) {
                    aux = java.lang.Long.parseLong(primaryKeys[i]);
                    delete.setLong(1, aux);
                    delete.execute();
                }
            } catch (SQLException sqle) {
            	System.out.println(sqle.getMessage());
                error = "ExceptieSQL: Reactualizare nereusita; este posibil sa existe duplicate.";
                throw new SQLException(error);
            } catch (Exception e) {
                error = "A aparut o exceptie in timp ce erau sterse inregistrarile.";
                System.out.println(e.getMessage());
                throw new Exception(error);
            }
        } else {
            error = "Exceptie: Conexiunea cu baza de date a fost pierduta.";
            throw new Exception(error);
        }
    } // end of stergeDateTabela()

    public void stergeTabela(String tabela) throws SQLException, Exception {
        if (con != null) {
            try {
// create a prepared SQL statement
                Statement stmt;
                stmt = con.createStatement();
                stmt.executeUpdate("delete from " + tabela + ";");
            } catch (SQLException sqle) {
                error = "ExceptieSQL: Stergere nereusita; este posibil sa existe duplicate.";
                throw new SQLException(error);
            }
        } else {
            error = "Exceptie: Conexiunea cu baza de date a fost pierduta.";
            throw new Exception(error);
        }
    } // end of stergeTabela()

    public void modificaTabela(String tabela, String IDTabela, String ID, String[] campuri, String[] valori) throws SQLException, Exception {
        String update = "update " + tabela + " set ";
        String temp = "";
        System.out.println(IDTabela);
        if (con != null) {
            try {
                for (int i = 0; i < campuri.length; i++) {
                    if (i != (campuri.length - 1)) {
                        temp = temp + campuri[i] + "='" + valori[i] + "', ";
                    } else {
                        temp = temp + campuri[i] + "='" + valori[i] + "' where " + IDTabela + " = '" + Integer.parseInt(ID) + "';";
                    }
                }
                update = update + temp;
// create a prepared SQL statement
                Statement stmt;
                stmt = con.createStatement();
                stmt.executeUpdate(update);
            } catch (SQLException sqle) {
                error = "ExceptieSQL: Reactualizare nereusita; este posibil sa existe duplicate.";
                throw new SQLException(error);
            }
        } else {
            error = "Exceptie: Conexiunea cu baza de date a fost pierduta.";
            throw new Exception(error);
        }
    } // end of modificaTabela()

    public ResultSet intoarceLinie(String tabela, String ID) throws SQLException, Exception {
        ResultSet rs = null;
        try {
// Execute query
            String queryString = ("SELECT * FROM " + tabela + " where idMuzician=" + Integer.parseInt(ID) + ";");
            Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(queryString); //sql exception
        } catch (SQLException sqle) {
            error = "SQLException: Interogarea nu a fost posibila.";
            throw new SQLException(error);
        } catch (Exception e) {
            error = "A aparut o exceptie in timp ce se extrageau datele.";
            throw new Exception(error);
        }
        return rs;
    } // end of intoarceLinie()

    public ResultSet intoarceLinieDupaId(String tabela, String denumireId, String ID) throws SQLException, Exception {
        ResultSet rs = null;
        try {
// Execute query
            String queryString = ("SELECT * FROM " + tabela + " where " + denumireId + "=" + Integer.parseInt(ID) + ";");
            Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(queryString); //sql exception
        } catch (SQLException sqle) {
            error = "SQLException: Interogarea nu a fost posibila.";
            throw new SQLException(error);
        } catch (Exception e) {
            error = "A aparut o exceptie in timp ce se extrageau datele.";
            throw new Exception(error);
        }
        return rs;
    } // end of intoarceLinieDupaId()

    public ResultSet intoarceAlbumId(String ID) throws SQLException, Exception {
        ResultSet rs = null;
        try {
// Execute query
        	System.out.println("conectare db");
        	String queryString = ("select a.Nume , a.Prenume , a.Email, a.NumarTelefon, "
            		+ "b.CasaCD , b.Gen , c.idAlbum, c.idStilmuzical , c.idMuzician , c.Nume, c.Datalansare, c.Nrmelodii, c.Pret "
            		+ "from muzician a, stilmuzical b, album c "
            		+ "where a.idMuzician = c.idMuzician and b.idStilmuzical = c.idStilmuzical and idAlbum = '"+Integer.parseInt(ID)+"';");
        	System.out.println(queryString);
            Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(queryString); //sql exception
        } catch (SQLException sqle) {
            error = "SQLException: Interogarea nu a fost posibila.";
            throw new SQLException(error);
        } catch (Exception e) {
            error = "A aparut o exceptie in timp ce se extrageau datele.";
            throw new Exception(error);
        }
        return rs;
    } // end of intoarceLinieDupaId()
}