package pl_sql;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.*;
import java.sql.Connection;

public class DaoMarca{
	static Connection conn; //attributo statico
	ArrayList<Marca> tabellaMarche = new ArrayList<>(); //strutura dati di appoggio

	public static void apriConnessione() {
		try {
			 Class.forName("oracle.jdbc.driver.OracleDriver");
			 conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "gonzalo");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void chiudiConnessione() {
		try {
			 conn.close();
		} catch(SQLException e) {
			e.printStackTrace();
		}
	}



	public void inserisci(Marca m) {

		CallableStatement cs;
		String insertStoreProc = "{call inserimentoMarche(?,?)}";
		DaoMarca.apriConnessione();

		try {
			cs = (CallableStatement) conn.prepareCall(insertStoreProc);
			cs.setInt(1, m.getId_marca());
			cs.setString(2, m.getNome_marca());
		        cs.executeQuery();
		        cs.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

       DaoMarca.chiudiConnessione();;

        }




	public Marca leggi(int index) {
		return this.tabellaMarche.get(index);
	}

	public ArrayList<Marca> leggi() {
		CallableStatement cs;
		String query = "{call leggiMarche}";
		DaoMarca.apriConnessione();;

		try {
			cs = (CallableStatement) conn.prepareCall(query);
			 ResultSet rs = cs.executeQuery(query);
			 while(rs.next()) {
				 Marca m = new Marca(); //genera nuovo prodotto da inserirei nell'arraylist
				 m.setId_marca(rs.getInt("ID"));
				 m.setNome_marca(rs.getString("NOMEMARCA"));
				 tabellaMarche.add(m); //aggiunge
			 }
			 cs.close();
			 rs.close();
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return tabellaMarche;
	}

	public void modifica(Marca m) {
		CallableStatement cs;
		DaoMarca.apriConnessione();

		try {
			cs = (CallableStatement) conn.prepareCall("{call modificaMarche(?,?)}");

			cs.setInt(1, m.getId_marca());
			cs.setString(2, m.getNome_marca());
			cs.executeUpdate();
		    cs.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}


       DaoMarca.chiudiConnessione();
		}


	public void cancella(Marca m) {
		CallableStatement cs;
		String deleteStoreProc = "{call cancellamentoMarche(?,?)}";
		DaoMarca.apriConnessione();


		try {
			cs = (CallableStatement) conn.prepareCall(deleteStoreProc);
			cs.setInt(1, (m.getId_marca()));
			cs.setString(2, m.getNome_marca());
		        cs.executeQuery();
		        cs.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

       DaoMarca.chiudiConnessione();;

        }


	public DaoMarca(){
	}
}