package siprp.higiene.gestao.importacao;

import java.io.InputStream;
import java.util.List;
import java.util.Vector;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.input.SAXBuilder;

import com.evolute.utils.arrays.Virtual2DArray;
import com.evolute.utils.db.DBManager;
import com.evolute.utils.db.Executer;
import com.evolute.utils.db.JDBCManager;
import com.evolute.utils.db.keyretrievers.JDBCAutoKeyRetriever;
import com.evolute.utils.sql.Assignment;
import com.evolute.utils.sql.Expression;
import com.evolute.utils.sql.Field;
import com.evolute.utils.sql.Insert;
import com.evolute.utils.sql.Select;
import com.evolute.utils.sql.Select2;
import com.evolute.utils.strings.UnicodeChecker;

public class Importador
{
	private Executer EXECUTER;
	
	public static void main( String args[] )
		throws Exception
	{
		Insert.setDefaultKeyRetriever( JDBCAutoKeyRetriever.DEFAULT );
		UnicodeChecker.setUseDoubleSlash( true );
		String url = "jdbc:postgresql://localhost:5436/siprp_local";
		String user = "postgres";
		String pwd = "Typein";
		DBManager dbm = new JDBCManager( url, user, pwd, 10, 8, 8, null );
		
		Importador importador = new Importador( dbm.getSharedExecuter(Importador.class) );
//		importador.converter();
		importador.importar();
	}
	
	public Importador( Executer executer )
		throws Exception
	{
//		DBManager dbm = ( DBManager ) Singleton.getInstance( Singleton.DEFAULT_DBMANAGER );
//		EXECUTER = dbm.getSharedExecuter();
		EXECUTER = executer;
	}
	
	// NAO APAGAR
//	public void converter()
//		throws Exception
//	{
//		InputStream xsl = getClass().getClassLoader().getResourceAsStream( "siprp/higiene/gestao/importacao/importacao_ods_to_xml.xsl" );
//		InputStream xmlContent = getClass().getClassLoader().getResourceAsStream( "siprp/higiene/gestao/importacao/content.xml" );
//		ByteArrayOutputStream baos = new ByteArrayOutputStream();
//		XSLTransformer.getXSLTransformer().transform( xmlContent, xsl, baos );
//		FileOutputStream fos = new FileOutputStream( "/home/fpalma/projectos2/SIPRP/SIPRPSoft/src/siprp/higiene/gestao/importacao/data.xml" );
//		fos.write( baos.toByteArray() );
//		fos.close();
//	}
	
	public void importar()
		throws Exception
	{
		SAXBuilder builder = new SAXBuilder();
		InputStream fileFoIs = getClass().getClassLoader().getResourceAsStream( "siprp/higiene/gestao/importacao/data.xml" );
		Document fileDocument = builder.build( fileFoIs );
		Element root = fileDocument.getRootElement();
		List<Element> entradas = root.getChildren( "entrada" );
		for( Element entrada : entradas )
		{
			Element temaElement = entrada.getChild( "tema" );
			String tema = getTexto( temaElement );
			Element riscoElement = entrada.getChild( "risco" );
			String risco = getTexto( riscoElement );
			Element requisitoElement = entrada.getChild( "requisito" );
			String requisito = getTexto( requisitoElement );
			Element medidaElement = entrada.getChild( "medida" );
			String medida = getTexto( medidaElement );
			if( tema.length() == 0 || risco.length() == 0 )
			{
				continue;
			}
			Integer idTema = inserirTema( tema );
			Integer idRisco = inserirRisco( idTema, risco );
			inserirMedida( idRisco, medida,	requisito );
		}
	}
	
	protected String getTexto( Element elemento )
	{
		List<Element> linhas = elemento != null ? elemento.getChildren( "linha" ) : new Vector<Element>();
		String texto = limparEspacos( linhas.size() > 0 ? linhas.get( 0 ).getText() : "" );
		for( int l = 1; l < linhas.size(); l++ )
		{
			String linha = limparEspacos( linhas.get( 0 ).getText() );
			texto += "\n" + linha;
		}
		return texto.trim();
	}
	
	protected String limparEspacos( String texto )
	{
		texto = texto.replaceAll( "\n", " " );
		texto = texto.replaceAll( "\t", "" );
		while( texto.indexOf( "  " ) != -1 )
		{
			texto = texto.replaceAll( "  ", " " );
		}
		return texto;
	}
	
	protected Integer inserirTema( String tema )
		throws Exception
	{
		Select select =
			new Select2( 
				new String[]{ "hs_risco_tema" },
				new Integer[]{},
				new Expression[]{},
				new String[]{ "id" },
				new Field( "description" ).isEqual( tema ),
				null,
				null,
				null,
				null );
		Virtual2DArray array = EXECUTER.executeQuery( select );
		Integer id = ( array.columnLength() > 0 && array.get( 0, 0 ) != null ) ? ( Integer ) array.get( 0, 0 ) : null;
		if( id == null )
		{
			Insert insert = 
				new Insert( "hs_risco_tema",
					new Assignment[]{
						new Assignment( "description", tema )
				} );
			EXECUTER.executeQuery( insert );
			id = inserirTema( tema );
		}
		
		return id;
	}
	
	protected Integer inserirRisco( Integer temaId, String risco )
		throws Exception
	{
		Select select =
			new Select2( 
				new String[]{ "hs_risco" },
				new Integer[]{},
				new Expression[]{},
				new String[]{ "id" },
				new Field( "description" ).isEqual( risco ).and( 
					new Field( "tema_id" ).isEqual( temaId ) ),
				null,
				null,
				null,
				null );
		Virtual2DArray array = EXECUTER.executeQuery( select );
		
		Integer id = ( array.columnLength() > 0 && array.get( 0, 0 ) != null ) ? ( Integer ) array.get( 0, 0 ) : null;
		if( id == null )
		{
			Insert insert = 
				new Insert( "hs_risco",
					new Assignment[]{
						new Assignment( "description", risco ),
						new Assignment( "tema_id", temaId )
				} );
			EXECUTER.executeQuery( insert );
			id = inserirRisco( temaId, risco );
		}
		
		return id;
	}
	
	protected Integer inserirMedida( Integer riscoId, String medida, String requisito )
		throws Exception
	{
		Select select =
			new Select2( 
				new String[]{ "hs_medida", "hs_risco_medida" },
				new Integer[]{ Select2.JOIN_LEFT_OUTER },
				new Expression[]{
					new Field( "hs_medida.id" ).isEqual( new Field( "hs_risco_medida.medida_id" ) ).and(
						new Field( "hs_risco_medida.risco_id" ).isEqual( riscoId ) )
				},
				new String[]{ "hs_medida.id" },
				new Field( "hs_medida.description" ).isEqual( medida ).and( 
					new Field( "hs_medida.requesitos_legais" ).isEqual( requisito ) ),
				null,
				null,
				null,
				null );
		Virtual2DArray array = EXECUTER.executeQuery( select );
		Integer id = array.columnLength() > 0 ? ( Integer ) array.get( 0, 0 ) : null;
		if( id == null )
		{
			Insert insert =
				new Insert( "hs_medida",
					new Assignment[]{
						new Assignment( new Field( "description" ), medida ),
						new Assignment( new Field( "requesitos_legais" ), requisito ),
				} );
			EXECUTER.executeQuery( insert );
			id = inserirMedida( null, medida, requisito );
			insert =
				new Insert( "hs_risco_medida",
					new Assignment[]{
						new Assignment( new Field( "medida_id" ), id ),
						new Assignment( new Field( "risco_id" ), riscoId ),
				} );
			EXECUTER.executeQuery( insert );
		}
		return id;
	}
}
