package eu.dnetlib.goldoa.service.dao;

import eu.dnetlib.goldoa.domain.Currency;
import eu.dnetlib.goldoa.domain.Journal;
import eu.dnetlib.goldoa.domain.Publisher;
import eu.dnetlib.goldoa.domain.Vocabulary;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

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

/**
 * Created by antleb on 3/18/15.
 */
public class JournalDAO {

	@Autowired
	private DataSource dataSource;

	private static final String SEARCH_JOURNALS = "select id, title, issn from journal where lower(title) like lower(?) or lower(alttitle) like lower(?) or issn like lower(?)";

	private static final String GET_JOURNAL = "select id, title, alttitle, url, publisher, language, issn, country, subjects, licence, apc, apc_currency, status, discount, source from journal where id=?";

	private static final String GET_JOURNAL_BY_TITLE = "select id, title, alttitle, url, publisher, language, issn, country, subjects, licence, apc, apc_currency, status, discount, source from journal where title=?";

	private static final String UPDATE_JOURNAL = "update journal set title=?, alttitle=?, url=?, publisher=?, language=?, issn=?, country=?, subjects=?, licence=?, apc=?, apc_currency=?, status=?, discount=?, source=? where id=?";

	private static final String INSERT_JOURNAL = "insert into journal(title, alttitle, url, publisher, language, issn, country, subjects, licence, apc, apc_currency, status, discount, source, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	private RowMapper<Journal> journalRowMapper = new RowMapper<Journal>() {
		@Override
		public Journal mapRow(ResultSet rs, int rowNum) throws SQLException {
			Publisher publisher = rs.getString("publisher") != null ? new Publisher(rs.getString("publisher")) : null;

			return new Journal(
					rs.getString("id"),
					rs.getString("title"),
					rs.getString("alttitle"),
					rs.getString("url"),
					publisher,
					rs.getString("language"),
					rs.getString("issn"),
					rs.getString("country"),
					rs.getString("subjects"),
					rs.getString("licence"),
					rs.getFloat("apc"),
					Currency.valueOf(rs.getString("apc_currency")),
					rs.getFloat("discount"),
					rs.getString("status"),
					rs.getString("source"));
		}
	};

	public List<Vocabulary> search(String term) {
		return new JdbcTemplate(dataSource).query(SEARCH_JOURNALS, new String[]{"%" + term + "%", "%" + term + "%", "%" + term + "%"}, new int[]{Types.VARCHAR, Types.VARCHAR, Types.VARCHAR}, new RowMapper<Vocabulary>() {
			@Override
			public Vocabulary mapRow(ResultSet resultSet, int i) throws SQLException {
				return new Vocabulary(resultSet.getString("id"), resultSet.getString("title") + " (" + resultSet.getString("issn") + ")");
			}
		});
	}

	public Journal getJournal(String id) {
		return new JdbcTemplate(dataSource).queryForObject(GET_JOURNAL, new Object[]{id}, new int[]{Types.VARCHAR}, journalRowMapper);
	}

	public Journal getJournalByTitle(String title) {
		return new JdbcTemplate(dataSource).queryForObject(GET_JOURNAL_BY_TITLE, new Object[]{title}, new int[]{Types.VARCHAR}, journalRowMapper);
	}

	public Journal saveJournal(Journal journal) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		Object[] args = {journal.getTitle(), journal.getAlternativeTitle(),
				journal.getUrl(), journal.getPublisher() != null ? journal.getPublisher().getId() : null, journal.getLanguages(),
				journal.getIssn(), journal.getCountry(), journal.getSubjects(), journal.getLicence(),
				journal.getApc(), journal.getCurrency().name(), journal.getStatus(), journal.getDiscount(), journal.getSource(), journal.getId()};
		int[] types = {
				Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
				Types.VARCHAR, Types.VARCHAR, Types.REAL, Types.VARCHAR, Types.VARCHAR, Types.REAL, Types.VARCHAR, Types.VARCHAR};

		if (jdbcTemplate.update(UPDATE_JOURNAL, args, types) == 0) {
			jdbcTemplate.update(INSERT_JOURNAL, args, types);
		}

		return journal;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

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