package eu.dnetlib.openaire.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.StringReader;
import java.io.StringWriter;
import java.net.URL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.sql.DataSource;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.apache.commons.io.IOUtils;
import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

public class RepositoryImport {
	private DataSource dataSource = null;
	private String doarUrl = "http://www.opendoar.org/api13.php?co=Europe&sort=co,rname&show=max";
	private static Logger logger = Logger.getLogger(RepositoryImport.class);
	
	private Map<String, String> countries = new HashMap<String, String>();
	private Map<String, String> organizations = new HashMap<String, String>();
	private Map<String, String> orgRepoRelations = new HashMap<String, String>();

	public void init() throws SQLException {
		countries.clear();
		organizations.clear();
		orgRepoRelations.clear();
		
		this.loadOrganizationsFromDb();
		this.loadRelationsFromDb();
		
		countries.put("GB", "UK");
	}
	
	public Iterable<String> run() {
		List<String> rows = null;
		
		try {
			logger.debug("Getting data from openDoar");
			String repos = this.getDoarRepos();
			
			rows = createRows(repos);
		} catch (Exception e) {
			logger.error("Error running", e);
		}
		
		return rows;
	}
	
	private Connection getConnection() throws SQLException {
		return DataSourceUtils.getConnection(dataSource);
	}

	private String getDoarRepos() throws IOException {
		URL u = new URL(this.doarUrl);
		
		InputStream is = (InputStream) u.getContent();
		StringWriter sw = new StringWriter();
		
		IOUtils.copy(is, sw, "UTF-8");
		
		return sw.toString();
	}
	
	private List<String> createRows(String repoString) throws SAXException, IOException, ParserConfigurationException, SQLException {
		DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
		DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
		Document doc = dBuilder.parse(new InputSource(new StringReader(repoString)));
		
		doc.getDocumentElement().normalize();
		
		NodeList repos = doc.getElementsByTagName("repository");
		
		List<String> rows = new ArrayList<String>();
		
		for (int i = 0; i < repos.getLength(); i++) {
			Node nNode = repos.item(i);	    
	       if (nNode.getNodeType() == Node.ELEMENT_NODE) {
	           Element repo = (Element) nNode;
				String id = "opendoar::" + repo.getAttribute("rID");
				String orgName = getTagValue("oName", repo);

				Row row = createRepositoryRow(repo, id);
				
				rows.add(row.toString());
				
				row = createOrganizationRow(repo);
				
				if (row != null)
					rows.add(row.toString());
				
				// now add repo-organization relation
				boolean relationExists = repoOrganizationRelationExists(id,	organizations.get(orgName));
				
				if (!relationExists) {
					Row relRow = new Row("datasources_organizations", false);
					
					relRow.addColumn("organization", organizations.get(orgName));
					relRow.addColumn("datasource", id);
					
					rows.add(relRow.toString());
				}
	       }
       }
		
		return rows;
	}

	private Row createRepositoryRow(Element repo, String id) {
		String name = getTagValue("rAcronym", repo);
		String lat = getTagValue("paLatitude", repo);
		String lon = getTagValue("paLongitude", repo);
		String officialName = getTagValue("rName", repo);
		String url = getTagValue("rUrl", repo);
		String type = getTagValue("rSoftWareName", repo);
		String contact_email = getTagValue("pEmail", repo);
		String oaiUrl = getTagValue("rOaiBaseUrl", repo);
		
		String desc = getTagValue("rDescription", repo);
		String count = getTagValue("rNumOfItems", repo);
		String countDate = getTagValue("rDateHarvested", repo);
		String subjects = readMultiTagValue(repo, "class", "clTitle", ", ");
		String languages = readMultiTagValue(repo, "language", "lName", ", ");
		String content = readMultiTagValue(repo, "contentTypes", "contentType", " - ");
		
		Row row = new Row("datasources", false);
		row.addColumn("datasourceid", id);
		row.addColumn("_dnet_resource_identifier_", id);
		row.addColumn("contact_email", contact_email);
		row.addColumn("english_name", name);
		row.addColumn("official_name", officialName);
		row.addColumn("web_site_url", url);
//		row.addColumn("openairecompliant", false);
		row.addColumn("optional2", type);
		row.addColumn("od_description", desc);
		row.addColumn("od_numberofitems", count);
		row.addColumn("od_numberofitemsdate", countDate);
		row.addColumn("od_subjects", subjects);
		row.addColumn("od_languages", languages);
		row.addColumn("od_contenttypes", content);
		row.addColumn("optional1", oaiUrl);
		
		if (lat != null && !lat.equals(""))
			row.addColumn("latitude", Float.parseFloat(lat));
		else
			row.addColumn("latitude", 0);
		if (lon != null && !lon.equals(""))
			row.addColumn("longitude", Float.parseFloat(lon));
		else
			row.addColumn("longitude", 0);
		return row;
	}
	
	private Row createOrganizationRow(Element repo) {
		String name = getTagValue("oName", repo);
		String shortName = getTagValue("oAcronym", repo);
		String url = getTagValue("oUrl", repo);
		String country = getTagValue("cIsoCode", repo);
		
		country = this.replaceCountryCode(country);
		
		String id = organizations.get(name);
		
		if (id == null) {
			Row row = new Row("organizations", false);

			id = UUID.randomUUID().toString();
			row.addColumn("organizationId", id);
			row.addColumn("legal_name", name);
			row.addColumn("legal_short_name", shortName);
			row.addColumn("web_site_url", url);
			row.addColumn("country_of_origin", country);
			
			organizations.put(name, id);
		
			return row;
		} else {
			return null;
		}
	}

	private boolean repoOrganizationRelationExists(String repoId, String orgId) throws SQLException {
		String rel = orgRepoRelations.get(repoId);
		
		return (rel != null && rel.equals(orgId));
	}
	
	private static String readMultiTagValue(Element parent, String listTag, String itemTag, String separator) {
		String res = "";// od_subjects  classes  clTitle

		for (int j = 0; j < parent.getElementsByTagName(listTag).getLength(); j++) {
			Node nnNode = parent.getElementsByTagName(listTag).item(j);

			if (nnNode.getNodeType() == Node.ELEMENT_NODE) {
				Element clazz = (Element) nnNode;
				String value = getTagValue(itemTag, clazz);
				
				if (j > 0 )
					res += separator;
				
				res += value;
			}
		}
		
		return res;
	}
	
	
	private static String getTagValue(String sTag, Element eElement) {
		
		if (eElement.getElementsByTagName(sTag).getLength() > 0) {
			NodeList nlList = eElement.getElementsByTagName(sTag).item(0)
					.getChildNodes();
			
			Node nValue = (Node) nlList.item(0);

			if (nValue != null)
				return nValue.getNodeValue();
			else
				return null;
		} else {
			return null;
		}
	}
	
	private void loadOrganizationsFromDb() throws SQLException {
		Connection con = null;
		
		try {
			con = getConnection();
			ResultSet rs = con.createStatement().executeQuery("Select legal_name, organizationId from organizations");
			
			while (rs.next()) {
				organizations.put(rs.getString(1), rs.getString(2));
			}
		} catch (SQLException e) {
			logger.error("Error executing query", e);
		} finally {
			if (con != null)
				con.close();
		}
	}
	
	private void loadRelationsFromDb() throws SQLException {
		Connection con = null;
		
		try {
			con = getConnection();
			ResultSet rs = con.createStatement().executeQuery("select datasource, organization from datasources_organizations");
			
			while (rs.next()) {
				orgRepoRelations.put(rs.getString(1), rs.getString(2));
			}
		} catch (SQLException e) {
			logger.error("Error executing query", e);
		} finally {
			if (con != null)
				con.close();
		}
	}
	
	private String replaceCountryCode(String doarCode) {
		if (countries.containsKey(doarCode))
			return countries.get(doarCode);
		else
			return doarCode;
	}
	
	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public String getDoarUrl() {
		return doarUrl;
	}

	public void setDoarUrl(String doarUrl) {
		this.doarUrl = doarUrl;
	}

	private class Row {
		private String tableName = null;
		private boolean deleted = false;
		private List<Column> columns = new ArrayList<Column>();
		
		public Row(String tableName, boolean deleted) {
			this.tableName = tableName;
			this.deleted = deleted;
		}
		
		public void addColumn(String columnName, String value) {
			this.columns.add(new Column(columnName, "string", value));
		}
		
		public void addColumn(String columnName, Integer value) {
			columns.add(new Column(columnName, "int", value.toString()));
		}
		
		public void addColumn(String columnName, Float value) {
			columns.add(new Column(columnName, "float", value.toString()));
		}
		
		public void addColumn(String columnName, Boolean value) {
			columns.add(new Column(columnName, "boolean", value.toString()));
		}
		
		public String toString() {
			StringBuilder sb = new StringBuilder();
			
			sb.append("<rows><row table=\"").append(tableName).append("\"");
			
			if (deleted)
				sb.append(" status=\"deleted\"");
			
			sb.append(">");
			
			for (Column c:columns) {
				sb.append("<field name=\"").append(c.getName()).append("\" ");
				sb.append("type=\"").append(c.getType()).append("\">");
				sb.append(c.getValue()).append("</field>");
			}
			
			sb.append("</row></rows>");
			
			return sb.toString();
		}
		
		private class Column {
			private String name = null;
			private String type = null;
			private String value = null;

			public Column(String name, String type, String value) {
				super();
				this.name = name;
				this.type = type;
				this.value = value;
			}

			public String getName() {
				return name;
			}

			public String getType() {
				return type;
			}

			public String getValue() {
				return value;
			}
		}
	}
	
	public static void main(String[] args) {
//		BasicConfigurator.configure();
		
		ApplicationContext ctx = new ClassPathXmlApplicationContext("classpath:/eu/dnetlib/openaire/util/springContext.xml");
		RepositoryImport ri = (RepositoryImport) ctx.getBean("repositoryImport");
		
		int i = 0;
		System.out.println("sd");
		for (String s:ri.run()) {
			System.out.println(s);
			i++;
		}
		
//		System.out.println(ri.organizations);
		
		System.out.println(i);
	}
}