package eu.dnetlib.goldoa.service.dao;

import eu.dnetlib.goldoa.domain.Affiliation;
import eu.dnetlib.goldoa.domain.Journal;
import eu.dnetlib.goldoa.domain.Organization;
import eu.dnetlib.goldoa.domain.Person;
import eu.dnetlib.goldoa.domain.Publication;
import eu.dnetlib.goldoa.domain.PublicationIdentifier;
import eu.dnetlib.goldoa.domain.Publisher;
import eu.dnetlib.goldoa.service.PersonManagerImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

public class PublicationDAO {

	@Autowired
	private DataSource dataSource;

	private static final String UPDATE_PUBLICATION = "update publication set title=?, languages=?, subjects=?, doi=?, source=?, date=?, type=?, journal=?, publisher=?, repository=?, acceptancedate=?, alternativetitle=?, alternativedoi=?, license=?, eventdetails=? where id=?";

	private static final String INSERT_PUBLICATION = "insert into publication (title, languages, subjects, doi, source, date, type, journal, publisher, repository, acceptancedate, alternativetitle, alternativedoi, license, eventdetails, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	private static final String GET_PUBLICATION =
			"select p.id, title, languages, subjects, doi, source, date, p.type, journal, publisher, repository, acceptancedate, alternativetitle, alternativedoi, license, eventdetails, array_agg(distinct pa.affiliation) as authors, array_agg(distinct i.type || '||' || i.identifier) as identifiers\n" +
					"from publication p\n" +
					"left join publication_affiliation pa on pa.publication=p.id\n" +
					"left join publication_identifier i on i.publication = p.id\n" +
					"where p.id=?\n" +
					"group by p.id, title, languages, subjects, doi, source, date, p.type, journal, publisher, repository, acceptancedate, alternativetitle, alternativedoi, license, eventdetails;";

	private static final String INSERT_PUBLICATION_AFFILIATIONS =
			"with aff as (insert into affiliation (id, person, organization, startdate, enddate, department) values (?, ?, ?, ?, ?, ?) returning id as affid) insert into publication_affiliation (publication, affiliation) select ?, affid from aff";

	private static final String UPDATE_PUBLICATION_AFFILIATIONS =
			"with aff as (select id from affiliation where person=? and organization=?) insert into publication_affiliation (publication, affiliation) select ?, aff.id from aff";

	private static final String LOAD_AFFILIATIONS = "select a.id, startdate, enddate, department, p.id || '||' || p.email || '||' || p.firstname || '||' || p.lastname || '||' || case when p.initials is null then '' else p.initials end as person, o.id || '||' || o.name as organisation\n" +
			"from affiliation a\n" +
			"left join person p on a.person=p.id\n" +
			"left join organisation o on o.id=a.organization\n" +
			"join publication_affiliation pa on pa.affiliation=a.id and pa.publication=?\n";

	public Publication getPublication(final String publicationId) {
		return new JdbcTemplate(dataSource).queryForObject(GET_PUBLICATION, new String[]{publicationId}, new int[]{Types.VARCHAR}, new RowMapper<Publication>() {
			@Override
			public Publication mapRow(ResultSet rs, int i) throws SQLException {

				Publication publication = new Publication();

				publication.setId(rs.getString("id"));
				publication.setTitle(rs.getString("title"));
				publication.setLanguages(rs.getString("languages"));
				publication.setSubjects(rs.getString("subjects"));
				publication.setDoi(rs.getString("doi"));
				publication.setSource(rs.getString("source"));
				publication.setPublicationDate(rs.getDate("date"));
				publication.setAcceptanceDate(rs.getDate("acceptancedate"));
				publication.setType(Publication.Type.valueOf(rs.getString("type")));
				publication.setJournal(new Journal(rs.getString("journal")));
				publication.setPublisher(new Publisher(rs.getString("publisher")));
				publication.setRepository(rs.getString("repository"));
				publication.setAlternativeTitle(rs.getString("alternativetitle"));
				publication.setAlternativeDoi(rs.getString("alternativedoi"));
				publication.setLicense(rs.getString("license"));
				publication.setEventDetails(rs.getString("eventdetails"));

				publication.setAuthors(new ArrayList<Affiliation>());
				for (Integer aff : (Integer[]) rs.getArray("authors").getArray()) {
					if (aff != null)
						publication.getAuthors().add(new Affiliation(aff));
				}

				publication.setIdentifiers(new ArrayList<PublicationIdentifier>());
				for (String s : (String[]) rs.getArray("identifiers").getArray()) {
					if (s != null) {
						String[] parts = s.split("\\|\\|");

						publication.getIdentifiers().add(new PublicationIdentifier(parts[0], parts[1]));
					}
				}

				return publication;
			}
		});
	}

	public Publication savePublication(Publication publication) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Object[] args = {publication.getTitle(), publication.getLanguages(), publication.getSubjects(), publication.getDoi(), publication.getSource(), publication.getPublicationDate(), publication.getType().toString(), publication.getJournal() != null ? publication.getJournal().getId() : null, publication.getPublisher() != null ? publication.getPublisher().getId() : null, publication.getRepository(), publication.getAcceptanceDate(), publication.getAlternativeTitle(), publication.getAlternativeDoi(), publication.getLicense(), publication.getEventDetails(), publication.getId()};
		int[] types = {Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.DATE, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.DATE, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR};

		if (jdbcTemplate.update(UPDATE_PUBLICATION, args, types) == 0) {
			jdbcTemplate.update(INSERT_PUBLICATION, args, types);
		}

		return publication;
	}

	public void insertIdentifiers(final Publication publication) {

		new JdbcTemplate(dataSource).batchUpdate("insert into publication_identifier (publication, type, identifier) values (?, ?, ?)", new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				PublicationIdentifier identifier = publication.getIdentifiers().get(i);

				ps.setString(1, publication.getId());
				ps.setString(2, identifier.getType());
				ps.setString(3, identifier.getValue());
			}

			@Override
			public int getBatchSize() {
				return publication.getIdentifiers() != null ? publication.getIdentifiers().size() : 0;
			}
		});
	}

	public void deleteAffiliations(Publication publication) {
		new JdbcTemplate(dataSource).update("delete from publication_affiliation where publication=?", new String[]{publication.getId()}, new int[]{Types.VARCHAR});
	}

	public void deleteIdentifiers(Publication publication) {
		new JdbcTemplate(dataSource).update("delete from publication_identifier where publication=?", new String[]{publication.getId()}, new int[]{Types.VARCHAR});
	}

	public void saveAffiliations(final Publication publication) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		if (publication.getAuthors() != null) {
			for (Affiliation affiliation : publication.getAuthors()) {
				if (jdbcTemplate.update(UPDATE_PUBLICATION_AFFILIATIONS, new String[]{affiliation.getPerson().getId(), affiliation.getOrganization() != null ? affiliation.getOrganization().getId() : null, publication.getId()}, new int[]{Types.VARCHAR, Types.VARCHAR, Types.VARCHAR}) == 0) {
					jdbcTemplate.update(INSERT_PUBLICATION_AFFILIATIONS,
							new Object[]{PersonManagerImpl.generateId(affiliation), affiliation.getPerson().getId(), affiliation.getOrganization() != null ? affiliation.getOrganization().getId() : null, affiliation.getStart(), affiliation.getEnd(), affiliation.getDepartment(), publication.getId()},
							new int[]{Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.TIMESTAMP, Types.VARCHAR, Types.VARCHAR});
				}
			}
		}
	}

	public void loadAffiliations(Publication publication) {
		List<Affiliation> affiliations = new JdbcTemplate(dataSource).query(LOAD_AFFILIATIONS, new String[]{publication.getId()}, new int[]{Types.VARCHAR}, new RowMapper<Affiliation>() {
			@Override
			public Affiliation mapRow(ResultSet rs, int rowNum) throws SQLException {
				Affiliation aff = new Affiliation();

				aff.setId(rs.getInt("id"));
				aff.setDepartment(rs.getString("department"));
				aff.setStart(rs.getDate("startdate"));
				aff.setEnd(rs.getDate("enddate"));

				aff.setPerson(this.parsePerson(rs.getString("person")));
				aff.setOrganization(this.parseOrganisation(rs.getString("organisation")));

				return aff;
			}

			private Organization parseOrganisation(String organisation) {
				if (organisation != null) {
					Organization org = new Organization();
					String[] parts = organisation.split("\\|\\|");

					org.setId(parts[0]);
					org.setName(parts[1]);

					return org;
				} else
					return null;
			}

			private Person parsePerson(String pStr) {
				String[] parts = pStr.split("\\|\\|");
				Person person = new Person();

				person.setId(parts[0]);
				person.setEmail(parts[1]);
				person.setName(parts[2]);
				person.setLastname(parts[3]);
				if (parts.length > 4)
					person.setInitials(parts[4]);

				return person;
			}
		});

		publication.setAuthors(affiliations);
	}
}