UV_SAIC_DATA/src/main/java/es/uv/saic/service/DataService.java

package es.uv.saic.service;

import java.io.IOException;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URI;
import java.net.http.HttpClient;
import java.net.http.HttpRequest;
import java.net.http.HttpResponse;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

import jakarta.mail.MessagingException;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.LinkedCaseInsensitiveMap;

import es.uv.saic.domain.IndicadorEnquestaTmp;
import es.uv.saic.domain.Usuari;
import es.uv.saic.dto.EmailDTO;
import es.uv.saic.dto.IndicadorEnquestaTmpDup;
import es.uv.saic.dto.OrganDTO;
import es.uv.saic.feing.CoreClient;
import es.uv.saic.domain.Datasource;


@Service
public class DataService {
	
	@Autowired
	private DatasourceService dss;

	@Autowired
	private CoreClient cc;
	
	private HashMap<String, String> titsMap;
	private HashMap<String, String> centresMap;
	
	public DataService() {
		titsMap = new HashMap<String, String>();
		titsMap.put("TRAD", "1099");
		centresMap = new HashMap<String, String>();
		centresMap.put("INDV", "121");
		centresMap.put("POST", "121");
		centresMap.put("EDOC", "62");
	}

	public String doImport(InputStreamReader in, Usuari usuari, String enquesta, String ambit, String estudi, 
						   String locale, String delim, String clau, List<String> ignoredColumns) throws IOException, MessagingException {
		
		CSVFormat format =  CSVFormat.DEFAULT.builder()
											 .setHeader()
											 .setIgnoreHeaderCase(true)
											 .setSkipHeaderRecord(true)
											 .setIgnoreEmptyLines(true)
											 .setIgnoreSurroundingSpaces(true)
											 .setDelimiter(delim)
											 .setRecordSeparator('\n')
											 .build();
		CSVParser parser = format.parse(in);
		List<CSVRecord> records = parser.getRecords();
		List<String> header = parser.getHeaderNames();
		List<String> headerInds = new ArrayList<>(header);
		StringBuilder errors = new StringBuilder();
				
		/*  1) Comprobar columnas obligatorias  */
		List<String> mandatoryColumns = new ArrayList<String>();
		List<String> knownColumns = new ArrayList<String>();
		if(clau.equals("ruct")) {
			this.setColumnsRuct(ambit, knownColumns, mandatoryColumns);
		}
		else {
			this.setColumnsCod(ambit, knownColumns, mandatoryColumns);
		}
		
		if(!this.checkColumns(header, mandatoryColumns, errors, locale)) {
			return errors.append("[ERROR] " + (locale.equals("es") ? "Se han encontrado errores en el fichero proporcionado. No se ha importado ningún registro. " : "S'han trobat errors al fitxer proporcionat. No s'ha importat cap registre.")).append("<br>").toString();
		}
		
		/*  2) Eliminar las columnas obligatorias, conocidas e ignoradas. El resto se consideran indicadores a importar */
		knownColumns.addAll(mandatoryColumns);
		knownColumns.addAll(ignoredColumns);
		for(String c : knownColumns) {
			headerInds.removeIf(v->v.equalsIgnoreCase(c));
		}
		
		/*  3) Comprobar valores y tipos  */	
		if(!this.checkValues(records, ambit, clau, errors, locale)) {
			return errors.append(("[ERROR] " + (locale.equals("es") ? "Se han encontrado errores en el fichero proporcionado. No se ha importado ningún registro. " : "S'han trobat errors al fitxer proporcionat. No s'ha importat cap registre."))).append("<br>").toString();
		}
		
		/*  4) Importar datos  */
		Integer numRecords = 0;
		Integer numTuples = 0;
		for (CSVRecord record : records) {
			Map<String, String> recordMap = new LinkedCaseInsensitiveMap<>();
			recordMap.putAll(record.toMap());
			String c = ambit.equals("T") || ambit.equals("C") ? translateCentre(recordMap.get("centre")) : null;
			String t = ambit.equals("T") ? translateTitulacio(recordMap.get("titulacio")) : null;  
			String type = recordMap.get("tipus");
			Integer curs = Integer.parseInt(recordMap.get("curs").replaceAll("\\D+",""));
			
			String cursEnquesta = recordMap.containsKey("curs_enquesta") ? recordMap.get("curs_enquesta") : null;
	    	String titulacioOrigen = recordMap.containsKey("titulacio_origen") ? recordMap.get("titulacio_origen") : null;
	    	String centreOrigen = recordMap.containsKey("centre_origen") ? recordMap.get("centre_origen") : null;
	    	Integer nenq = recordMap.containsKey("nenq") ? Integer.parseInt(recordMap.get("nenq").replaceAll("\\D+","")) : null;
	    	String ructStr = recordMap.containsKey("ruct") ? recordMap.get("ruct").replaceAll("\\D+","") : null;
			String cursd = recordMap.containsKey("cursd") ? recordMap.get("cursd") : null;
	    	Integer ruct = null;
	    	
	    	if(ructStr != null) {
		    	if(!(ructStr.isBlank() || ructStr.isEmpty() || ructStr.equals("NULL") || ructStr.equals("null"))) {
		    		ruct = Integer.parseInt(ructStr);
				}
	    	}
			
			Integer centre;
	    	Integer titulacio;
			if(clau.equals("ruct") && ambit.equals("T") && ruct != null) {
				OrganDTO o = findOrgByRuct(ruct);
				if(o == null) {
					errors.append("[WARNING] " + (locale.equals("es") ? "No se ha encontrado el RUCT "+ructStr+", se omiten los registros de esta entrada." : "No s'ha trobat el RUCT "+ructStr+", s'ometen els registres d'aquesta entrada.")).append("<br>").toString();
					continue;
				}
				titulacio = o.getLugar();
				centre = o.getLugar2();
			}
			else{
				centre = ambit.equals("T") || ambit.equals("C") ? Integer.parseInt(c.replaceAll("\\D+","")) : null;
		    	titulacio = ambit.equals("T") ? Integer.parseInt(t) : null;
				if(!existsOrg("T", titulacio)){
					errors.append("[WARNING] " + (locale.equals("es") ? "No se ha encontrado la titulación con código "+titulacio+" pero se importa igualmente." : "No s'ha trobat la titulació amb codi "+titulacio+" però s'importa igualment")).append("<br>").toString();
				}
			}
	    	
	    	if(header.contains("cursd")) {
	    		cursd = recordMap.get("cursd").replaceAll("\\D+","");
	    	}
			for(String r : headerInds) {
				IndicadorEnquestaTmp ie = new IndicadorEnquestaTmp();
	        	ie.setEnquesta(enquesta);
	    		ie.setTitulacio(titulacio);
	    		ie.setCentre(centre);
	    		ie.setCurs(curs);
	    		ie.setAmbit(ambit); 
	    		ie.setEstudi(estudi);
	        	ie.setIndicador(r.replace("_min", "").replace("_max", "").toLowerCase());
	        	ie.setNum(null);
	        	ie.setCursd(cursd);
	        	ie.setTipus(type);
	        	ie.setUsuari(usuari.getUsuari());
	        	ie.setData(new Timestamp(System.currentTimeMillis()));
	        	ie.setCursEnquesta(cursEnquesta);
	        	ie.setTitulacioOrigen(titulacioOrigen);
	        	ie.setCentreOrigen(centreOrigen);
	        	ie.setNenq(nenq);
	        	ie.setRuct(ruct);
	        	
	        	try {
	        		ie.setValor(record.get(r).replace(",", "."));
	        	}
	        	catch(Exception ex) {
	        		ie.setValor(null);
	        	}
	        	
	        	save(ie);
	        	numRecords++;
			}
			numTuples++;
		}

		/*  5) Notificar administradores y usuario implicado  */
		this.sendNotificacion(usuari, enquesta, ambit, estudi);
		if(!usuari.isAdmin()){
			this.sendConfirmation(usuari.getUsuari(), enquesta, ambit, estudi);
		}

		errors.append("[INFO] " + (locale.equals("es") ? ("Se han importado "+numRecords.toString()+" nuevos registros para un total de "+numTuples.toString()+" titulaciones") : ("[INFO] S'han importat "+numRecords.toString()+" nous registres per a un total de "+numTuples.toString()+" titulacions")));
		return errors.toString();
	}

	public String doDbImport(Integer dbOrigen, String vista, Integer srcCurs, Integer dstCurs, Usuari usuari, String enquesta, String ambit, 
							 String estudi, String locale, String clau, List<String> ignoredColumns) throws SQLException, ClassNotFoundException {

		StringBuilder errors = new StringBuilder();
		Datasource source = this.dss.findById(dbOrigen);
		if(source == null){
			return errors.append("[ERROR] " + (locale.equals("es") ? "No se ha proporcionado un origen de datos válido." : "No s'ha proporcionat un origen de dades vàlid.")).append("<br>").toString();
		}
		String query = "SELECT * FROM "+vista+ " WHERE curs = "+srcCurs;
		Class.forName(source.getDriver());
		Integer numRecords = 0;
		Integer numTuples = 0;
		try (Connection connection = DriverManager.getConnection(source.getConn())) {

			/*  1) Ejecutar consulta  */
			Statement statement = connection.createStatement();
			ResultSet resultset = statement.executeQuery(query);
			
			ResultSetMetaData resultMetadata = resultset.getMetaData();
			int columns = resultMetadata.getColumnCount();

			/*  2) Obtener resultados para procesar online y cerrar conexión  */
			RowSetFactory factory = RowSetProvider.newFactory();
			CachedRowSet result = factory.createCachedRowSet();
			result.populate(resultset);
			connection.close();

			/*  3) Comprobar columnas obligatorias  */
			List<String> mandatoryColumns = new ArrayList<String>();
			List<String> knownColumns = new ArrayList<String>();
			if(clau.equals("ruct")) {
				this.setColumnsRuct(ambit, knownColumns, mandatoryColumns);
			}
			else {
				this.setColumnsCod(ambit, knownColumns, mandatoryColumns);
			}

			List<String> allColNames = new ArrayList<String>();
			for(int i = 1; i <= columns; i++){
				allColNames.add(resultMetadata.getColumnLabel(i).toLowerCase());
			}
			List<String> colnames = new ArrayList<String>(allColNames);
			
			if(!this.checkColumns(colnames, mandatoryColumns, errors, locale)) {
				return errors.append("[ERROR] " + (locale.equals("es") ? "Se han encontrado errores al procesar la vista seleccionada. No se ha importado ningún registro. " : "S'han trobat errors al processar la vista seleccionada. No s'ha importat cap registre.")).append("<br>").toString();
			}

			knownColumns.addAll(mandatoryColumns);
			knownColumns.addAll(ignoredColumns);
			for(String c : knownColumns) {
				colnames.removeIf(v->v.equalsIgnoreCase(c));
			}

			if(ambit.equals("T") && !clau.equals("ruct")){
				return errors.append("[ERROR] " + (locale.equals("es") ? "Los datos de titulación deben de importarse por código RUCT" : "Les dades de titulació s'han d'importar per códi RUCT.")).append("<br>").toString();
			}
			
			/*  4) Importar datos  */
			while(result.next()) {
				Integer ruct = null;
				Integer titulacio = null;
				Integer centre = null;
				String c = ambit.equals("C") ? translateCentre(result.getString("centre")) : null;
				boolean isValid = true; 

				if(ambit.equals("T")) {
					String ructStr = result.getString("ruct");
					if(ructStr != null) {
						if(!(ructStr.isBlank() || ructStr.isEmpty() || ructStr.equals("NULL") || ructStr.equals("null"))) {
							ruct = Integer.parseInt(ructStr);
						}
						else{
							isValid = false;
						}
					}
					else{
						isValid = false;
					}
					OrganDTO o = findOrgByRuct(ruct);
					if(o == null) {
						errors.append("[WARNING] " + (locale.equals("es") ? "No se ha encontrado el RUCT "+ructStr+", se omiten los registros de esta entrada." : "No s'ha trobat el RUCT "+ructStr+", s'ometen els registres d'aquesta entrada.")).append("<br>").toString();
						continue;
					}
					titulacio = o.getLugar();
					centre = o.getLugar2();
				}
				else if(ambit.equals("C")) {
					centre = ambit.equals("T") || ambit.equals("C") ? Integer.parseInt(c.replaceAll("\\D+","")) : null;
					if(centre == null){
						isValid = false;
					}
				}
								
				if(isValid) {				
					Integer curs = dstCurs == null ? result.getInt("curs") : dstCurs;
					String tipus = allColNames.contains("tipus") ? result.getString("tipus") : "avg";
					String cursEnquesta = allColNames.contains("curs_enquesta") ? result.getString("curs_enquesta") : null;
					String titulacioOrigen = allColNames.contains("titulacio_origen") ? result.getString("titulacio_origen") : null;
					String centreOrigen = allColNames.contains("centre_origen") ? result.getString("centre_origen") : null;
					Integer nenq = allColNames.contains("nenq") ? result.getInt("nenq") : null;
					String cursd = allColNames.contains("cursd") ? result.getString("cursd") : null;
					for(String colName : colnames) {
						IndicadorEnquestaTmp ie = new IndicadorEnquestaTmp();
	
						ie.setEnquesta(enquesta);
						ie.setTitulacio(titulacio);
						ie.setCentre(centre);
						ie.setCurs(curs);
						ie.setAmbit(ambit); 
						ie.setEstudi(estudi);
						ie.setIndicador(colName.replace("_min", "").replace("_max", "").toLowerCase());
						ie.setNum(null);
						ie.setCursd(cursd);
						ie.setTipus(tipus);
						ie.setUsuari(usuari.getUsuari());
						ie.setData(new Timestamp(System.currentTimeMillis()));
						ie.setCursEnquesta(cursEnquesta);
						ie.setTitulacioOrigen(titulacioOrigen);
						ie.setCentreOrigen(centreOrigen);
						ie.setNenq(nenq);
						ie.setRuct(ruct);
	
						try {
							ie.setValor(result.getString(colName).replace(",", "."));
						}
						catch(Exception ex) {
							ie.setValor(null);
						}
						
						save(ie);
						numRecords++;
					} 
					numTuples++;  
				}
			}
		}

		/*  5) Notificar administradores y usuario implicado  */
		this.sendNotificacion(usuari, enquesta, ambit, estudi);
		if(!usuari.isAdmin()){
			this.sendConfirmation(usuari.getUsuari(), enquesta, ambit, estudi);
		}
		errors.append("[INFO] " + (locale.equals("es") ? ("Se han importado "+numRecords.toString()+" nuevos registros para un total de "+numTuples.toString()+" titulaciones") : ("[INFO] S'han importat "+numRecords.toString()+" nous registres per a un total de "+numTuples.toString()+" titulacions")));
		return errors.toString();
	}

	public List<String> listTableColumns(Integer dbOrigen, String vista, String locale) throws SQLException, ClassNotFoundException {
		
		Datasource source = this.dss.findById(dbOrigen);
		String query = "SELECT * FROM "+vista+" LIMIT 1;";
		Class.forName(source.getDriver());
		List<String> colNames = new ArrayList<String>();
		try (Connection connection = DriverManager.getConnection(source.getConn())) {

			Statement statement = connection.createStatement();
			ResultSet resultset = statement.executeQuery(query);
			
			ResultSetMetaData resultMetadata = resultset.getMetaData();
			int columns = resultMetadata.getColumnCount();

			for(int i = 1; i <= columns; i++){
				colNames.add(resultMetadata.getColumnLabel(i));
			}

		}

		return colNames;
	}
	
	public List<IndicadorEnquestaTmpDup> checkDuplicates(String enquesta) {
		return checkDuplicatesAux(enquesta);
	}

	public Integer consolidateByEnquesta(String enquesta) {
		if(checkDuplicates(enquesta).size() > 0) {
			return -1;
		}
		else {
			Integer i = cc.consolidateByEnquesta(enquesta);
			deleteByEnquesta(enquesta);
			return i;
		}
	}

    public Integer countByEnquesta(String enquesta) {
		return countByEnquestaAux(enquesta);
	}

	public List<IndicadorEnquestaTmpDup> checkIntegrity(String enquesta){
		return cc.checkIntegrity(enquesta);
	}

	public Integer deleteFromCurrent(String enquesta){
		return cc.deleteFromCurrent(enquesta);
	}

	public Integer deleteFromPending(String enquesta){
		return cc.deleteFromPending(enquesta);
	}

	private void setColumnsCod(String ambit, List<String> knownColumns, List<String> mandatoryColumns) {
		knownColumns.addAll(Arrays.asList("ruct", "curs_enquesta", "centre_origen", "titulacio_origen", "nenq", "cursd"));	
		if(ambit.equals("U")) {
			mandatoryColumns.addAll(Arrays.asList("curs", "tipus"));
		}
		else if(ambit.equals("C")) {
			mandatoryColumns.addAll(Arrays.asList("curs", "centre", "tipus"));
		}
		else {
			mandatoryColumns.addAll(Arrays.asList("curs", "titulacio", "centre", "tipus"));
		}
	}
	
	private void setColumnsRuct(String ambit, List<String> knownColumns, List<String> mandatoryColumns) {
		knownColumns.addAll(Arrays.asList("curs_enquesta", "centre_origen", "titulacio_origen", "nenq", "cursd"));	
		if(ambit.equals("U")) {
			mandatoryColumns.addAll(Arrays.asList("curs", "tipus"));
		}
		else if(ambit.equals("C")) {
			mandatoryColumns.addAll(Arrays.asList("curs", "tipus", "ruct"));
			knownColumns.add("centre");
		}
		else {
			mandatoryColumns.addAll(Arrays.asList("curs", "tipus", "ruct"));
			knownColumns.addAll(Arrays.asList("centre", "titulacio"));
		}
	}
	
	private boolean checkColumns(List<String> header, List<String> mandatoryColumns, StringBuilder errors, String locale) {
		boolean isvalid = true;
		for(String c : mandatoryColumns) {			
			if(!header.stream().anyMatch(c::equalsIgnoreCase)) {
				isvalid = false;
		    	errors.append((locale.equals("es") ? "[ERROR] No se ha encontrado la columna " : "[ERROR] No s'ha trobat la columna ") +c).append("<br>");
		    }
		}
		return isvalid;
	}
	
	private boolean checkValues(List<CSVRecord> records, String ambit, String clau, StringBuilder errors, String locale) {
		boolean isvalid = true;
		Integer numRecords = 0;
		for (CSVRecord record : records) {
			Map<String, String> recordMap = new LinkedCaseInsensitiveMap<>();
			recordMap.putAll(record.toMap());
			
			String type = recordMap.get("tipus");
			numRecords++;
			
			if((ambit.equals("T") || ambit.equals("C")) && !clau.equals("ruct")) {
				String c = recordMap.get("centre");
				c = translateCentre(c);
				if(!StringUtils.isNumeric(c.replace("C", ""))) {
					isvalid = false;
					errors.append("[ERROR] " + (locale.equals("es") ? "En la linea " : "A la línia ") + numRecords.toString() + ": " + (locale.equals("es") ? c+" no es un valor válido para la columna centre." : c+" no es un valor vàlid per la columna centre.")).append("<br>");
				} 
			}
			
			if(ambit.equals("T")) {
				String t = recordMap.get("titulacio");
				if(!clau.equals("ruct")) {
					t = translateTitulacio(t);
					if(!StringUtils.isNumeric(t)) {
						isvalid = false;
						errors.append("[ERROR] " + (locale.equals("es") ? "En la linea " : "A la línia ") + numRecords.toString() + ": " + (locale.equals("es") ? t+" no es un valor válido para la columna titulacio." : t+" no es un valor vàlid per la columna titulacio.")).append("<br>");
					}
				}
				else {
					String ructStr = recordMap.get("ruct");
					if(!ructStr.isBlank() && ructStr.isEmpty() && ructStr.equals("NULL") && ructStr.equals("null") && !StringUtils.isNumeric(t)) {
						isvalid = false;
						errors.append("[ERROR] " + (locale.equals("es") ? "En la linea " : "A la línia ") + numRecords.toString() + ": " + (locale.equals("es") ? ructStr+" no es un valor válido para la columna ruct." : ructStr+" no es un valor vàlid per la columna ruct.")).append("<br>");
					}
				}
			}
			
			if(!type.equals("avg") && !type.equals("min") && !type.equals("max")) {
				isvalid = false;
				errors.append("[ERROR] " + (locale.equals("es") ? "En la linea " : "A la línia ") + numRecords.toString() + ": " + (locale.equals("es") ? type+" no es un valor válido para la columna tipus." : type+" no es un valor vàlid per la columna tipus.")).append("<br>");
			}
		}
		return isvalid;
	}

	private void sendNotificacion(Usuari usuari, String enquesta, String ambit, String estudi) {
		sendMail("saic@uv.es", "[SYS] Datos pendientes de consolidar", "Estimado/a administrador: \n"
				+ "\n"
				+ "El usuario "+usuari.getUsuari()+" ha importado nuevos datos referentes a: \n"
				+ "Origen: "+enquesta+"\n"
				+ "Ámbito: "+ambit+"\n"
				+ "Tipo titulación: "+estudi+"\n"
				+ "\n"
				+ "Acceda a https://saic.uv.es y realice las acciones pertinentes para consolidar los datos. ");

	}

	private void sendConfirmation(String to, String enquesta, String ambit, String estudi) {
		sendMail(to, "[SYS] Nuevos datos importados", "Estimado/a usuario: \n"
				+ "\n"
				+ "Se han importado correctamente nuevos datos referentes a: \n"
				+ "Origen: "+enquesta+"\n"
				+ "Ámbito: "+ambit+"\n"
				+ "Tipo titulación: "+estudi+"\n"
				+ "\n"
				+ "Los datos se quedarán en estado pendiente de consolidar hasta que un administrador inicie el proceso de consolidación.");

	}
	
	/* Exepciones en el código de titulación que hay que traducir a código SAIC */
	private String translateTitulacio(String t) {
		if(!StringUtils.isNumeric(t)) {
			if(this.titsMap.containsKey(t)) {
				return this.titsMap.get(t); 
			}
		}
		return t;
	}
	
	/* Exepciones en el código de centro que hay que traducir a código SAIC */
	private String translateCentre(String c) {
		if(!StringUtils.isNumeric(c)) {
			if(this.centresMap.containsKey(c)) {
				return this.centresMap.get(c); 
			}
		}
		return c;
	}

	private OrganDTO findOrgByRuct(Integer ruct) {
		return cc.getOrgByRuct(ruct);
	}

	private boolean existsOrg(String tlugar, Integer titulacio) {
		return cc.existsOrg(tlugar, titulacio);
	}

	private void deleteByEnquesta(String enquesta) {
		cc.deleteByEnquestaC(enquesta);
	}

	private void save(IndicadorEnquestaTmp ie) {
		cc.save(ie);
	}

	private List<IndicadorEnquestaTmpDup> checkDuplicatesAux(String enquesta) {
		return cc.checkDuplicates(enquesta);
	}


	private Integer countByEnquestaAux(String enquesta) {
		return cc.countByEnquesta(enquesta);
	}

	private void sendMail(String string, String string2, String string3) {
		EmailDTO email = new EmailDTO(string, string2, string3);
		cc.sendEmail(email);
	}
}