package siprp.update.updates;

import static com.evolute.utils.strings.UnicodeLatin1Map.atilde;
import static com.evolute.utils.strings.UnicodeLatin1Map.ccedil;
import static com.evolute.utils.strings.UnicodeLatin1Map.oacute;
import static com.evolute.utils.strings.UnicodeLatin1Map.uacute;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;

import javax.imageio.ImageIO;

import com.evolute.utils.Singleton;
import com.evolute.utils.arrays.Virtual2DArray;
import com.evolute.utils.db.DBManager;
import com.evolute.utils.db.Executer;
import com.evolute.utils.sql.Assignment;
import com.evolute.utils.sql.BlobUpdate;
import com.evolute.utils.sql.Field;
import com.evolute.utils.sql.Insert;
import com.evolute.utils.sql.Select2;
import com.evolute.utils.sql.Update;

public class V11_0_To_V11_1 implements siprp.update.Update
{
	
	public V11_0_To_V11_1()
	{
	}
	
	public String []listChanges()
	{
		return new String[]{
		                    "A actualizar informacao dos tecnicos de higiene e seguranca"
		                    };
	}
	
	public double getStartVersion()
	{
		return 11.0;
	}
	
	public double getEndVersion()
	{
		return 11.1;
	}
	
	public void doUpdate() throws Exception
	{
		DBManager dbm = ( DBManager ) Singleton.getInstance( Singleton.DEFAULT_DBMANAGER );
		Executer executer = dbm.getSharedExecuter();
		
		Update update = new Update(
					"alter table image alter column image_data drop not null; "
		);
		executer.executeQuery( update );
		
		Integer assSoniaID = null;
		Integer assCatarinaID = null;
		String assSonia = "Assinatura(Sonia Filipa Dias Campos)";
		String assCatarina = "Assinatura(Catarina Leonardo)";
		BufferedImage assSoniaImage = ImageIO.read( getClass().getClassLoader().getResourceAsStream( "siprp/update/updates/V11_0_To_V11_1_Assinatura_Sonia.png" ) );
		BufferedImage assCatarinaImage = ImageIO.read( getClass().getClassLoader().getResourceAsStream( "siprp/update/updates/V11_0_To_V11_1_Assinatura_Catarina.png" ) );
		ByteArrayOutputStream assSoniaBytesStream = new ByteArrayOutputStream();
		ByteArrayOutputStream assCatarinaBytesStream = new ByteArrayOutputStream();
		ImageIO.write( assSoniaImage, "PNG", assSoniaBytesStream );
		ImageIO.write( assCatarinaImage, "PNG", assCatarinaBytesStream );
		if( assSoniaImage == null || assSoniaBytesStream.size() == 0 )
		{
			throw new Exception("Erro ao ler assinatura: " + assSonia );
		}
		if( assCatarinaImage == null || assCatarinaBytesStream.size() == 0 )
		{
			throw new Exception("Erro ao ler assinatura: " + assCatarina );
		}
		Insert insertQuery = new Insert( "image", new Assignment[]{
				new Assignment(new Field("name"),assSonia)
		} );
		Virtual2DArray array = executer.executeQuery( insertQuery );
		insertQuery = new Insert( "image", new Assignment[]{
				new Assignment(new Field("name"), assCatarina)
		} );
		executer.executeQuery( insertQuery );
		Select2 selectQuery = new Select2(new String[]{"image"},null,null,new String[]{"id"},new Field("name").isEqual( assSonia ),null,null,null,null);
		array = executer.executeQuery( selectQuery );
		if( array.columnLength() == 1 )
		{
			assSoniaID = (Integer) array.get( 0, 0 );
		}
		selectQuery = new Select2(new String[]{"image"},null,null,new String[]{"id"},new Field("name").isEqual( assCatarina ),null,null,null,null);
		array = executer.executeQuery( selectQuery );
		if( array.columnLength() == 1 )
		{
			assCatarinaID = (Integer) array.get( 0, 0 );
		}
		if( assSoniaID == null )
		{
			throw new Exception("Erro ao inserir assinatura: " + assSonia );
		}
		if( assCatarinaID == null )
		{
			throw new Exception("Erro ao inserir assinatura: " + assCatarina );
		}
		BlobUpdate blobUpdate = new BlobUpdate("image","image_data",assSoniaBytesStream.toByteArray(),new Field("id").isEqual( assSoniaID ) );
		executer.executeQuery( blobUpdate );
		blobUpdate = new BlobUpdate("image","image_data",assCatarinaBytesStream.toByteArray(),new Field("id").isEqual( assCatarinaID ) );
		executer.executeQuery( blobUpdate );
		update = new Update(
					"update marcacoes_tecnicos_hst set cap='0601/6505/02', formacao='Ergonomia', assinatura=" + assSoniaID + " where id = 2; " +
					"update marcacoes_tecnicos_hst set cap='0310/1156/02', formacao='Sa"+uacute+"de Ambiental, P"+oacute+"s Gradua"+ccedil+atilde+"o em Higiene e Seguran"+ccedil+"a do Trabalho e Mestrado em Ergonomia na Seguran"+ccedil+"a do Trabalho', assinatura=" + assCatarinaID + " where id = 1; "
		);
		executer.executeQuery( update );
	}
	
	public String toString()
	{
		return "v" + getStartVersion() + " para v" + getEndVersion();
	}
	
}
