package donnee;

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.List;

import modele.Exoplanete;

public class ExoplanetesDAO {
	public String SQL_LISTER_EXOPLANETES = "SELECT * from exoplanete";
	public String SQL_AJOUTER_EXOPLANETE = "INSERT into exoplanete(planete,etoile, masse, rayon, flux, temperature, periode, distance) values(?,?,?,?,?,?,?,?)";		
	public String SQL_EDITER_EXOPLANETE = "UPDATE exoplanete SET planete = ?, etoile = ?, masse=?, rayon=?, flux=?, temperature=?, periode=?, distance=? WHERE id = ?";		
	public String SQL_EFFACER_EXOPLANETE = "DELETE from exoplanete WHERE id = ?";		

	public List<Exoplanete> listerExoplanetes()
	{
		List<Exoplanete> listeExoplanetes =  new ArrayList<Exoplanete>();			
		Connection connection = BaseDeDonnees.getInstance().getConnection();
		
		Statement requeteListeExoplanetes;
		try {
			requeteListeExoplanetes = connection.createStatement();
			ResultSet curseurListeExoplanetes = requeteListeExoplanetes.executeQuery(SQL_LISTER_EXOPLANETES);
			while(curseurListeExoplanetes.next())
			{
				int id = curseurListeExoplanetes.getInt("id");
				String nom = curseurListeExoplanetes.getString("planete");
				String etoile = curseurListeExoplanetes.getString("etoile");
				String masse = curseurListeExoplanetes.getString("masse");
				String rayon = curseurListeExoplanetes.getString("rayon");
				String flux = curseurListeExoplanetes.getString("flux");
				String temperature = curseurListeExoplanetes.getString("temperature");
				String periode = curseurListeExoplanetes.getString("periode");
				String distance = curseurListeExoplanetes.getString("distance");
				
				Exoplanete exoplanete = new Exoplanete();
				exoplanete.setId(id);
				exoplanete.setNom(nom);
				exoplanete.setEtoile(etoile);
				exoplanete.setMasse(masse);
				exoplanete.setRayon(rayon);
				exoplanete.setFlux(flux);
				exoplanete.setTemperature(temperature);
				exoplanete.setPeriode(periode);
				exoplanete.setDistance(distance);
				
				listeExoplanetes.add(exoplanete);
			}
		} catch (SQLException e) {
				e.printStackTrace();
		}
		
		return listeExoplanetes;
	}
	
	public void ajouterExoplanete(Exoplanete exoplanete)
	{		
		Connection connection = BaseDeDonnees.getInstance().getConnection();
		try {
			PreparedStatement requeteAjouterExoplanete;
			requeteAjouterExoplanete = connection.prepareStatement(SQL_AJOUTER_EXOPLANETE);
			requeteAjouterExoplanete.setString(1, exoplanete.getNom());
			requeteAjouterExoplanete.setString(2, exoplanete.getEtoile());
			requeteAjouterExoplanete.setString(3, exoplanete.getMasse());
			requeteAjouterExoplanete.setString(4, exoplanete.getRayon());
			requeteAjouterExoplanete.setString(5, exoplanete.getFlux());
			requeteAjouterExoplanete.setString(6, exoplanete.getTemperature());
			requeteAjouterExoplanete.setString(7, exoplanete.getPeriode());
			requeteAjouterExoplanete.setString(8, exoplanete.getDistance());
			
			requeteAjouterExoplanete.execute();
		} catch (SQLException e) {
				e.printStackTrace();
		}
	}
	
	public void editerExoplanete(Exoplanete exoplanete)
	{		
		System.out.println("editer " + exoplanete.getNom());
		Connection connection = BaseDeDonnees.getInstance().getConnection();
		try {
			PreparedStatement requeteEditerExoplanete;
			requeteEditerExoplanete = connection.prepareStatement(SQL_EDITER_EXOPLANETE);
			requeteEditerExoplanete.setString(1, exoplanete.getNom());
			requeteEditerExoplanete.setString(2, exoplanete.getEtoile());
			requeteEditerExoplanete.setString(3, exoplanete.getMasse());
			requeteEditerExoplanete.setString(4, exoplanete.getRayon());
			requeteEditerExoplanete.setString(5, exoplanete.getFlux());
			requeteEditerExoplanete.setString(6, exoplanete.getTemperature());
			requeteEditerExoplanete.setString(7, exoplanete.getPeriode());
			requeteEditerExoplanete.setString(8, exoplanete.getDistance());
			requeteEditerExoplanete.setInt(9, exoplanete.getId());
			
			requeteEditerExoplanete.execute();
		} catch (SQLException e) {
				e.printStackTrace();
		}
	}
	public void effacerExoplanete(Exoplanete exoplanete)
	{		
		Connection connection = BaseDeDonnees.getInstance().getConnection();
		try {
			PreparedStatement requeteEffacerExoplanete;
			requeteEffacerExoplanete = connection.prepareStatement(SQL_EFFACER_EXOPLANETE);
			requeteEffacerExoplanete.setInt(1, exoplanete.getId());
			requeteEffacerExoplanete.execute();
		} catch (SQLException e) {
				e.printStackTrace();
		}
	}	
}