package eu.dnetlib.data.db;

import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;
import java.util.stream.Collectors;

import org.springframework.beans.factory.annotation.Required;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class AffiliationsDao {

	private JdbcTemplate jdbcTemplate;

	public List<Affiliation> listAffiliations() {
		return jdbcTemplate.query("select * from affiliations", (RowMapper<Affiliation>) (rs, rowNum) -> {
			return new Affiliation(rs.getString("pid"), rs.getString("gid"), rs.getInt("year"));
		});
	}

	public List<Person> listPersonsWithAffiliations() {

		final Map<String, Person> temp = new HashMap<>();

		for (final Map<String, Object> map : jdbcTemplate.queryForList("select * from affiliations_view")) {
			final String pid = (String) map.get("pid");
			final Integer year = (Integer) map.get("year");
			final Group group = new Group((String) map.get("gid"), (String) map.get("gname"), (String) map.get("gtype"));

			temp.putIfAbsent(pid, new Person(pid, (String) map.get("pname")));

			if (year != null) {
				temp.get(pid).getAffiliations().putIfAbsent(year, new TreeSet<>((g1, g2) -> {
					final int n1 = calculateIntegerForGroup(g1.getType());
					final int n2 = calculateIntegerForGroup(g2.getType());
					return (n1 == n2) ? g1.getId().compareTo(g2.getId()) : Integer.compare(n1, n2);
				}));
				temp.get(pid).getAffiliations().get(year).add(group);
			}
		}

		return temp.values()
				.stream()
				.sorted((p1, p2) -> p1.getName().compareTo(p2.getName()))
				.collect(Collectors.toList());

	}

	private int calculateIntegerForGroup(final String type) {
		if (type.equalsIgnoreCase("Laboratorio")) {
			return 0;
		} else if (type.equalsIgnoreCase("Servizio")) {
			return 10;
		} else if (type.equalsIgnoreCase("Centro")) {
			return 20;
		} else {
			return 100;
		}
	}

	public List<Person> listPersons() {
		return jdbcTemplate.query("select * from persons", (RowMapper<Person>) (rs, rowNum) -> {
			return new Person(rs.getString("id"), rs.getString("fullname"));
		});
	}

	public List<Group> listGroups() {
		return jdbcTemplate.query("select * from groups where id != 'UNKNOWN'", (RowMapper<Group>) (rs, rowNum) -> {
			return new Group(rs.getString("id"), rs.getString("name"), rs.getString("type"));
		});
	}

	public Set<String> getPersonIds() {
		return new HashSet<>(jdbcTemplate.query("select id from persons", (RowMapper<String>) (rs, rowNum) -> rs.getString("id")));
	}

	public Set<String> getGroupIds() {
		return new HashSet<>(jdbcTemplate.query("select id from groups", (RowMapper<String>) (rs, rowNum) -> rs.getString("id")));
	}

	public void updatePerson(final String code, final String name) {
		jdbcTemplate.update("UPDATE persons SET fullname = ? WHERE id = ?", name, code);

	}

	public void registerPerson(final String code, final String name) {
		jdbcTemplate.update("INSERT into persons(id, fullname) VALUES (?, ?)", code, name);
	}

	public void updateGroup(final String id, final String name, final String type) {
		jdbcTemplate.update("UPDATE groups SET (name, type) = (?, ?) WHERE id = ?", name, type, id);
	}

	public void registerGroup(final String id, final String name, final String type) {
		jdbcTemplate.update("INSERT into groups(id, name, type) VALUES (?, ?, ?)", id, name, type);
	}

	public void registerAffiliation(final String pid, final String gid, final int year) {
		jdbcTemplate.update("INSERT into affiliations(pid, gid, year) VALUES (?, ?, ?)", pid, gid, year);
	}

	public void updateUnknownAffiliation(final String pid, final String gid, final int year) {
		jdbcTemplate.update("UPDATE affiliations SET gid=? WHERE gid='UNKNOWN' AND pid=? AND year=?", gid, pid, year);
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	@Required
	public void setJdbcTemplate(final JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

}