package org.medias.eccad.persistance.jdbc;

import java.awt.Color;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.medias.eccad.modele.ColorMap;
import org.medias.eccad.modele.ColorTab;
import org.medias.eccad.persistance.dao.ColorTabDAO;
import org.medias.eccad.persistance.exception.PersistanceException;

public class ColorTabDAOjdbc extends GeneriqueDAOjdbc implements ColorTabDAO {
	
	public ColorTab getColorMapByGrille() throws PersistanceException {
		ColorTab colortab = new ColorTab();
		colortab = getTableDefault(colortab);
		//colortab = getMinMax(oid, colortab);
		
		return colortab;
	}
	
	public List<ColorMap> getListClassMap() throws PersistanceException {
		String sql = " select clm_id, clm_name, num_order from class_map order by clm_id";		
		
		Statement requete = null;
		ResultSet resultat = null;
		Connection conn = getConnection();
		
//		LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, " récupération liste de colortab " + sql);
		
		try {
			requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			resultat = requete.executeQuery(sql);
		} catch (SQLException e) {
			closeConnection();
			throw new PersistanceException(e);
		}
		
		List<ColorMap> liste_colormap = new LinkedList<ColorMap>();
		
		try {
			while (resultat.next()) {
				liste_colormap.add(new ColorMap(resultat.getLong("clm_id"), resultat.getString("clm_name"), resultat.getInt("num_order")));
			}
		}
		catch (Exception e) {
			throw new PersistanceException(e);
		}
		finally {
			closeConnection();
		}
		
		return liste_colormap;
	}
	
	public List<ColorMap> getListColorTabByGrille() throws PersistanceException {
		/*String sql = " select distinct cm_id, cm_name, pc_defaut" 
			   + " from colormap join param_color using (cm_id) "
			   + " join grille using (id_param) "
			   + " where id_grille = " + id_grille + " order by pc_defaut desc;";*/
		
		String sql = " select clm_id as cm_id, clm_name as cm_name, num_order as pc_defaut from class_map order by clm_id";
		
		Statement requete = null;
		ResultSet resultat = null;
		Connection conn = getConnection();
		
//		LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, " récupération liste de colortab " + sql);
		
		try {
			requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			resultat = requete.executeQuery(sql);
		} catch (SQLException e) {
			closeConnection();
			throw new PersistanceException(e);
		}
		
		List<ColorMap> liste_colormap = new LinkedList<ColorMap>();
		
		try {
			while (resultat.next()) {
				liste_colormap.add(new ColorMap(resultat.getLong("cm_id"), resultat.getString("cm_name"), resultat.getInt("pc_defaut")));
			}
		}
		catch (Exception e) {
			throw new PersistanceException(e);
		}
		finally {
			closeConnection();
		}
		
		return liste_colormap;
	}
	
	private ColorTab getTableDefault(ColorTab colortab) throws PersistanceException {
		Statement requete = null;
		/*String sql = " select distinct col_r, col_g, col_b, ci_min, ci_max" 
				   + " from color join color_index using (col_id)"
				   + " join param_color using (cm_id) "
				   + " join grille   using (id_param) "
				   + " where id_grille = " + id_grille +" and pc_defaut = 1 order by ci_max;";*/

		String sql = " select distinct col_r, col_g, col_b, class_index.num_order"
				   + " from color join class_index using (col_id)"
				   + " join class_map using (clm_id) "
				   + " where class_map.num_order=1 order by class_index.num_order;";
		Connection conn = getConnection();
		
		ResultSet resultat = null;
		
//		LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, "*************************************REQUETE = " + sql);
		try {
			requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			resultat = requete.executeQuery(sql);
		} catch (SQLException e) {
			closeConnection();
			throw new PersistanceException(e);
		}
		
		List<Color> liste_couleur = new LinkedList<Color>();
		List<Float> liste_min = new LinkedList<Float>();
		int i=0;

		float ci_min=-1;
		float ci_max=0;
		
		try {
			while (resultat.next()) {
				liste_couleur.add(new Color(resultat.getInt("col_r"), resultat.getInt("col_g"), resultat.getInt("col_b")));
				//liste_min.add(resultat.getFloat("ci_min"));
				//colortab.setMax(resultat.getFloat("ci_max"));
				
				liste_min.add(ci_min);
				colortab.setMax(ci_max);
				
				if (ci_min==-1) ci_min=0;
				else ci_min=ci_min+10;
				
				ci_max=ci_max+10;
				i++;
			}
		}
		catch (SQLException sqle) {
			throw new PersistanceException(sqle);
		}
		finally {
			closeConnection();	
		}
		Color[] color_tab = new Color[i];
		float[] tab_min   = new float[i];
		

		for (i=0; i<liste_min.size(); i++) {
			tab_min[i] = liste_min.get(i);
			color_tab[i] = liste_couleur.get(i);
		}
		colortab.setTab_min(tab_min);
		colortab.setTab_couleur(color_tab);
		
		return colortab;
	}

	public ColorTab getColorMapByParam(int id_param) {
		// TODO Auto-generated method stub
		return null;
	}

	public ColorTab getColorMapByID(long id, long idClass) throws PersistanceException {
		Statement requete = null;
		ColorTab colortab = new ColorTab();
		
		/*String sql = " select distinct col_r, col_g, col_b, ci_min, ci_max, cm_name" 
				   + " from color join color_index using (col_id) join colormap using (cm_id)"
				   + " where cm_id = " + id +" order by ci_min;";*/

		String sql="select distinct col_r, col_g, col_b, clm_name as cm_name, ci.num_order, val_min, val_max" 
			+" from color as co, class_index as ci, class_map as cm, class_distribution as cd" 
			+" where co.col_id=ci.col_id and ci.clm_id=cm.clm_id and cm.clm_id="+id+" and cd.scl_id="+idClass
			+" and cd.num_order=ci.num_order order by ci.num_order;";
		Connection conn = getConnection();
		
		ResultSet resultat = null;

//		LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, "REQUETE = " + sql);
		try {
			requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			resultat = requete.executeQuery(sql);
		} catch (SQLException e) {
			closeConnection();
			throw new PersistanceException(e);
		}
		
		List<Color> liste_couleur = new LinkedList<Color>();
		List<Float> liste_min = new LinkedList<Float>();
		int i=0;

		try {
			while (resultat.next()) {
				colortab.setName(resultat.getString("cm_name"));
				liste_couleur.add(new Color(resultat.getInt("col_r"), resultat.getInt("col_g"), resultat.getInt("col_b")));
				liste_min.add(resultat.getFloat("val_min"));
				colortab.setMax(resultat.getFloat("val_max"));
//				LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, "NAME="+resultat.getString("cm_name")+", RANGE = " +resultat.getFloat("val_min")+" - "+resultat.getFloat("val_max"));
				i++;
			}
		}
		catch (SQLException sqle) {
			throw new PersistanceException(sqle);
		}
		finally {
			closeConnection();	
		}
		Color[] color_tab = new Color[i];
		float[] tab_min   = new float[i];
		

		for (i=0; i<liste_min.size(); i++) {
			tab_min[i] = liste_min.get(i);
			color_tab[i] = liste_couleur.get(i);
		}
		colortab.setTab_min(tab_min);
		colortab.setTab_couleur(color_tab);
		
		return colortab;
	}

/*	public List<ColorMap> getListColorMapDynamique() throws PersistanceException {
		String sql = " select distinct clm_id, clm_name" 
			   + " from class_map order by clm_id";
			   //+ " where clm_name like '%exponential%' or clm_name like '%linear%' or clm_name like '%logarithm%' order by clm_name;";
		
		
		Statement requete = null;
		ResultSet resultat = null;
		Connection conn = getConnection();
		
//		LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, " récupération liste de colortab " + sql);
		
		try {
			requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			resultat = requete.executeQuery(sql);
		} catch (SQLException e) {
			closeConnection();
			throw new PersistanceException(e);
		}
		
		List<ColorMap> liste_colormap = new LinkedList<ColorMap>();
		
		try {
			while (resultat.next()) {
				liste_colormap.add(new ColorMap(resultat.getLong("clm_id"), resultat.getString("clm_name"), 0));
			}
		}
		catch (Exception e) {
			throw new PersistanceException(e);
		}
		finally {
			closeConnection();
		}
		
		return liste_colormap;
	}*/

}