package org.ibai.app.webui.edm;

import org.dspace.core.ConfigurationManager;
import org.dspace.core.Context;

import java.util.HashMap;
import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.ArrayList;
import java.sql.SQLException;

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

import org.dspace.storage.rdbms.DatabaseManager;
import org.dspace.storage.rdbms.TableRow;
import org.dspace.storage.rdbms.TableRowIterator;

import org.apache.log4j.Logger;
	public class EDMAdapter{
		/** log4j logger */
		private static Logger log = Logger.getLogger(EDMAdapter.class);
		private HashMap<String,String> subjectMap;
		private ArrayList<String> authorList;
		private ArrayList<String> subjectList;
		private String subjectFile;
		private Context context;
		private String edmCommunities;
		private String edmCollections;
		private static EDMAdapter INSTANCE = null;
		private EDMAdapter(Context context){
			this.subjectMap = new HashMap<String,String>(); 
			this.subjectFile = ConfigurationManager.getProperty("edm.path")+ConfigurationManager.getProperty("edm.subjectfile").trim();
			this.context = context;
			this.edmCommunities = ConfigurationManager.getProperty("edm.hdl_communities").trim();
			this.edmCollections = ConfigurationManager.getProperty("edm.hdl_collections").trim();
			this.authorList = new ArrayList<String>();
			this.subjectList = new ArrayList<String>();
			String[] listHandleCommunities = edmCommunities.split(",");
			String[] listHandleCollections = edmCollections.split(",");
			
			loadEDMSubjectFile();
			loadEDMAuthorsCommunity(listHandleCommunities);
			loadEDMAuthorsCollection(listHandleCollections);
			loadEDMSubjectsCommunity(listHandleCommunities);
			loadEDMSubjectsCollection(listHandleCollections);
		}
		
		public static EDMAdapter getInstance(Context context){
		
			if(INSTANCE == null){
				INSTANCE = new EDMAdapter(context);
			}
			return INSTANCE;
		}
		public boolean isEDMSubject(String subject){
			return subjectList.contains(subject);
		}
		public boolean isEDMAuthor(String author){
			return authorList.contains(author);
		}
		public boolean isMinisterioSubject(String subject){
			
			subject = subject.trim();
			if (subject.endsWith(".")){
				subject = subject.replace(".", "");
			}
			return subjectMap.containsKey(subject);
		}
		public String getUrlMinisterio(String materia) {
			materia = materia.trim();
			if (materia.endsWith(".")){
				materia = materia.replace(".", "");
			}
			String materia_ministerio = (String)subjectMap.get(materia);
			if (materia_ministerio == null){
				return "";
			}else{
				return materia_ministerio.toString();
			}	
		}
		private void loadEDMAuthorsCommunity(String[] communities){
		
			String handles = buildHandles(communities);
			String select ="";
			if(ConfigurationManager.getProperty("db.name").compareTo("oracle")==0){
				select = "select distinct(dbms_lob.substr(text_value,4000,1)) as text_value from metadatavalue inner join metadatafieldregistry on metadatafieldregistry.metadata_field_id = metadatavalue.metadata_field_id"
				+" where (metadatafieldregistry.element = 'contributor' or metadatafieldregistry.element = 'creator') and item_id in("
				+"select item_id from item where owning_collection in ("
				+"select collection_id from community2collection where community_id in ("
				+"select resource_id from handle where handle in ("+handles+")))) order by dbms_lob.substr(text_value,4000,1)";
			}else{
				select = "select distinct(text_value) from metadatavalue inner join metadatafieldregistry on metadatafieldregistry.metadata_field_id = metadatavalue.metadata_field_id"
				+" where (metadatafieldregistry.element = 'contributor' or metadatafieldregistry.element = 'creator') and item_id in("
				+"select item_id from item where owning_collection in ("
				+"select collection_id from community2collection where community_id in ("
				+"select resource_id from handle where handle in ("+handles+")))) order by text_value";
			}
           
			TableRowIterator tri = null;
			try{
				tri = DatabaseManager.query(context, select);
				while (tri.hasNext()){
					TableRow tr = tri.next();
					authorList.add(tr.getStringColumn("text_value"));
				}
				
			}catch(SQLException e){
				log.error(e.getMessage());
			}finally{
				if (tri != null)
				{
					tri.close();
				}
			}
		}
		
		private void loadEDMSubjectsCommunity(String[] communities){
			
			String handles = buildHandles(communities);
			String select ="";
			if(ConfigurationManager.getProperty("db.name").compareTo("oracle")==0){
				select = "select distinct(dbms_lob.substr(text_value,4000,1)) as text_value from metadatavalue where metadata_field_id = 57 and item_id in("
				+"select item_id from item where owning_collection in ("
				+"select collection_id from community2collection where community_id in ("
				+"select resource_id from handle where handle in ("+handles+")))) order by dbms_lob.substr(text_value,4000,1)";
			}else{
				select = "select distinct(text_value) from metadatavalue where metadata_field_id = 57 and item_id in("
				+"select item_id from item where owning_collection in ("
				+"select collection_id from community2collection where community_id in ("
				+"select resource_id from handle where handle in ("+handles+")))) order by text_value";
			}
            

			TableRowIterator tri = null;
			try{
				tri = DatabaseManager.query(context, select);
				while (tri.hasNext()){
					TableRow tr = tri.next();
					subjectList.add(tr.getStringColumn("text_value"));
				}
			}catch(SQLException e){
				log.error(e.getMessage());
			}finally{
				if (tri != null)
				{
					tri.close();
				}
			}
			
		}
		private void loadEDMAuthorsCollection(String[] collections){
			
			String handles = buildHandles(collections);
			String select ="";
			if(ConfigurationManager.getProperty("db.name").compareTo("oracle")==0){
				select = "select distinct(dbms_lob.substr(text_value,4000,1))as text_value from metadatavalue inner join metadatafieldregistry on metadatafieldregistry.metadata_field_id = metadatavalue.metadata_field_id"
				+" where (metadatafieldregistry.element = 'contributor' or metadatafieldregistry.element = 'creator') and item_id in("
				+"select item_id from item where owning_collection in ("
				+"select resource_id from handle where handle in ("+handles+"))) order by dbms_lob.substr(text_value,4000,1)";
			}else{
				select = "select distinct(text_value) from metadatavalue inner join metadatafieldregistry on metadatafieldregistry.metadata_field_id = metadatavalue.metadata_field_id"
				+" where (metadatafieldregistry.element = 'contributor' or metadatafieldregistry.element = 'creator') and item_id in("
				+"select item_id from item where owning_collection in ("
				+"select resource_id from handle where handle in ("+handles+"))) order by text_value";
			}
			
            
		
			TableRowIterator tri = null;
			try{
				tri = DatabaseManager.query(context, select);
				while (tri.hasNext()){
					TableRow tr = tri.next();
					authorList.add(tr.getStringColumn("text_value"));
				}
			}catch(SQLException e){
				log.error(e.getMessage());
			}finally{
				if (tri != null)
				{
					tri.close();
				}
			}
		}
		
		private void loadEDMSubjectsCollection(String[] collections){
			
			
			String handles = buildHandles(collections);
			String select ="";
			if(ConfigurationManager.getProperty("db.name").compareTo("oracle")==0){
				select = "select distinct(dbms_lob.substr(text_value,4000,1))as text_value from metadatavalue where metadata_field_id = 57 and item_id in("
				+"select item_id from item where owning_collection in ("
				+"select resource_id from handle where handle in ("+handles+"))) order by dbms_lob.substr(text_value,4000,1)";
			}else{
				select = "select distinct(text_value) from metadatavalue where metadata_field_id = 57 and item_id in("
				+"select item_id from item where owning_collection in ("
				+"select resource_id from handle where handle in ("+handles+"))) order by text_value";
			}

			TableRowIterator tri = null;
			try{
				tri = DatabaseManager.query(context, select);
				while (tri.hasNext()){
					TableRow tr = tri.next();
					subjectList.add(tr.getStringColumn("text_value"));
				}
			}catch(SQLException e){
				log.error(e.getMessage());
			}finally{
				if (tri != null)
				{
					tri.close();
				}
			}
		}

		private void loadEDMSubjectFile(){
			SAXBuilder builder = new SAXBuilder();
			File xmlFile = new File(subjectFile);
 
			try {
				
				Document document = (Document) builder.build(xmlFile);
				List materiaElements = document.getRootElement().getChildren("materia");
				
				for (int i = 0; i < materiaElements.size(); i++) {
			
				   Element materiaElement = (Element) materiaElements.get(i);
				   String nombre = materiaElement.getAttributeValue("nombre");
				   String url = materiaElement.getChild("url").getText();
				   subjectMap.put(nombre,url);
				}
		 
			  } catch (IOException io) {
				System.out.println(io.getMessage());
			  } catch (JDOMException jdomex) {
				System.out.println(jdomex.getMessage());
			  }
			
		}
		
		private String buildHandles(String[] handles){
			String handlesString ="";
			for(int i = 0; i < handles.length-1;i++){
				handlesString +="'"+handles[i]+"',";
			}
			handlesString+="'"+handles[handles.length-1]+"'";
			
			return handlesString;
		
		}
		public static String stringForURL(String nombre) {
			if (nombre == null) return "";
			String resultado = nombre;
			resultado = resultado.replace(" ", "_");
			
			resultado = resultado.replace("&quot;", "");
			return resultado;
		}

		public static String nombre_materia(String nombre) {
			if (nombre == null) return "";
			String resultado = nombre.toLowerCase();
			
			resultado = resultado.replace("\"", "");
			resultado = resultado.replace("'", "");
			resultado = resultado.replace("    ", " ");
			resultado = resultado.replace("   ", " ");
			resultado = resultado.replace("  ", " ");
			resultado = resultado.replace("/", "");
			resultado = resultado.replace("|", "");
			resultado = resultado.replace(":", "");
			resultado = resultado.replace("ñ", "n");
			resultado = resultado.replace(" ", "_");
			resultado = resultado.replace("á", "a");
			resultado = resultado.replace("é", "e");
			resultado = resultado.replace("í", "i");
			resultado = resultado.replace("ó", "o");
			resultado = resultado.replace("ú", "u");
			resultado = resultado.replace("ü", "u");
			resultado = resultado.replace("*", "");
			if(resultado.endsWith("..."))
				resultado = resultado.substring(0, resultado.length()-3);
			if(resultado.endsWith("."))
				resultado = resultado.substring(0, resultado.length()-1);
	
			resultado = resultado.replace("?", "_");

			return resultado;
		}
	}