/**
 * MapGenerator Sample Extensions
 */
package mycompany.map.colorscale;

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

import mycompany.map.generator.MapGeneratorExtension;

import org.w3c.dom.Element;

import idea.map.colorscale.ColorForValueRange;
import idea.map.colorscale.ListColorScale;
import idea.map.generator.MapGenerator;
import idea.mapgen.editor.Property;
import idea.mapgen.editor.XmlBean;

/**
 * Color scale - obtained from PostgreSQL DB.
 *  See /SQL/color_scale.sql
 *  
 *  Usage:
 *  
 *  ColorScale class="mycompany.map.colorscale.ColorScaleFromPgSQL" ...
 *  
 *  
 *  See /runsets/RunSet.xml for sample of usage
 *  
 * @author Lumir Vanek, vanek@idea-envi.cz
 */
@XmlBean(icon="ColorScaleFromDb.png")
public class ListColorScaleFromPgSQL extends ListColorScale 
{	
	/**
	 * Scale code in DB
	 */
	@Property(name="code", mandatory=true)
	private final String code;
	
	/**
	 * Text for legend
	 */
	private String text;
	
	/**
	 * Values unit
	 */
	private String unit;
	
	/**
	 * Constructor
	 * 
	 * @param code Scale code in DB
	 */
	public ListColorScaleFromPgSQL(String code)
	{
		super();
		this.code = code;
	}
		
	/**
	 * XML Runset constructor, initialize yourself from given DOM element
	 * 
	 * @param domElement DOM element
	 */
	public ListColorScaleFromPgSQL(Element domElement)
	{
		super(domElement);
		
		code = domElement.getAttribute("code");
	}
	
	/**
	 * Initialize - Load color scale from table map_color_scale
	 * 
	 * @param mapGenerator The map generator
	 */
	public void initialize(final MapGenerator mapGenerator)
	{
		if(mapGenerator instanceof MapGeneratorExtension)
		{
			MapGeneratorExtension mpe = (MapGeneratorExtension) mapGenerator;
			// Connection to PostgreSQL
			final Connection connection =  mpe.getConnection();
			try
			{
				final PreparedStatement stmtSelect = connection.prepareStatement("SELECT * FROM map_color_scale WHERE code=?",
						ResultSet.TYPE_FORWARD_ONLY, 
						ResultSet.CONCUR_READ_ONLY);
					
				stmtSelect.setString(1, code);
					
				try
				{
					final ResultSet resultSet = stmtSelect.executeQuery();
					try
					{
						// Load data from map_color_scale table
						if(resultSet.next())
						{
							text = resultSet.getString("description");
							if(resultSet.wasNull()) text = "";
								
							unit = resultSet.getString("unit");
							if(resultSet.wasNull()) unit = "";
								
							int id = resultSet.getInt("id");
							loadRanges(connection, id);
						}
						else
						{
							throw new RuntimeException("Can't obtain Color Scale with code " + code);
						}
					}
					finally
					{
						resultSet.close();
					}
				}
				finally
				{
					stmtSelect.close();
				}
			}
			catch (SQLException e) 
			{
				throw new RuntimeException(e.getClass().toString() + " - " + e.getLocalizedMessage());
			}
		}
		else
		{
			throw new RuntimeException("Map Generator is not MapGeneratorExtension");
		}
	}
	
	/**
	 * Load scale items from table map_color_range
	 * 
	 * @param connPg PostgreSQL connection
	 * @param id ID of color scale - map_color_range.id_color_scale
	 * @throws SQLException
	 */
	private void loadRanges(final Connection connPg, int id) throws SQLException
	{
		final PreparedStatement stmtSelect = connPg.prepareStatement("SELECT * FROM map_color_range WHERE id_color_scale=? ORDER BY seq",
				ResultSet.TYPE_FORWARD_ONLY, 
				ResultSet.CONCUR_READ_ONLY);
			
		stmtSelect.setInt(1, id);
		
		try
		{
			final ResultSet resultSet = stmtSelect.executeQuery();
			try
			{
				// Load data from map_color_range table
				while(resultSet.next())
				{
					String name = resultSet.getString("name");
					String color = "#" + resultSet.getString("color");
					float valueFrom = resultSet.getFloat("value_from");
					
					
					Float valueTo = resultSet.getFloat("value_to");
					if(resultSet.wasNull()) { valueTo = null; }
					
					int sequence = resultSet.getInt("seq");
						
					getColorScaleItems().add(new ColorForValueRange(name,
							color,
							"#000000",
							valueFrom,
							valueTo,
							sequence));
					
				}
			}
			finally
			{
				resultSet.close();
			}
		}
		finally
		{
			stmtSelect.close();
		}
	}
	
	/**
	 * @return Text for legend
	 */
	public String getText() 
	{
		return text + " [" + unit + "]";
	}
	
	/**
	 * @return Values unit
	 */
	public String getUnit() {
		return unit;
	}
}