package eu.dnetlib.goldoa.service.dao;

import eu.dnetlib.goldoa.domain.Organization;
import eu.dnetlib.goldoa.domain.Person;
import eu.dnetlib.goldoa.domain.Project;
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.ArrayList;
import java.util.List;

/**
 * Created by antleb on 3/13/15.
 */
public class ProjectDAO {

	@Autowired
	private DataSource dataSource;

	private long expirationOk;

	private final String SEARCH_PROJECTS = "select id, acronym, title, \"grant\" from project where lower(acronym) like lower(?) or lower(title) like lower(?) or lower(\"grant\") like lower(?)";
	private final String GET_PROJECT =
			"select id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, array_agg(distinct pc.coordinator) as coordinators, array_agg(distinct po.organization) as organizations\n" +
					"from project  p\n" +
					"left join project_organization po on po.project=p.id\n" +
					"left join project_coordinator pc on pc.project=p.id\n" +
					"where p.id=?\n" +
					"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source";
	private final String GET_PROJECTS_FOR_ORGANIZATION =
			"select id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, array_agg(distinct pc.coordinator) as coordinators, array_agg(distinct po.organization) as organizations\n" +
					"from project  p\n" +
					"left join project_organization po on po.project=p.id\n" +
					"left join project_coordinator pc on pc.project=p.id\n" +
					"where po.organization=? and p.enddate > now() - (? || ' months')::interval and p.enddate < now() \n" +
					"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source";
	private final String GET_FUTURE_PROJECTS_FOR_ORGANIZATION =
			"select id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, array_agg(distinct pc.coordinator) as coordinators, array_agg(distinct po.organization) as organizations\n" +
					"from project  p\n" +
					"left join project_organization po on po.project=p.id\n" +
					"left join project_coordinator pc on pc.project=p.id\n" +
					"where po.organization=? and p.enddate > now() \n" +
					"group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source";

	private RowMapper<Project> rowMapper = new RowMapper<Project>() {
		@Override
		public Project mapRow(ResultSet rs, int rowNum) throws SQLException {
			Project project = new Project();

			project.setId(rs.getString("id"));
			project.setTitle(rs.getString("title"));
			project.setAcronym(rs.getString("acronym"));
			project.setFunder(rs.getString("funder"));
			project.setFundingString(rs.getString("fundingstream"));
			project.setScientificArea(rs.getString("scientificarea"));
			project.setCall(rs.getString("call"));
			project.setGrant(rs.getString("grant"));
			project.setStartDate(rs.getTimestamp("startdate"));
			project.setEndDate(rs.getTimestamp("enddate"));
			project.setSc39(rs.getBoolean("sc39"));
			project.setUrl(rs.getString("url"));


			project.setCoordinators(new ArrayList<Person>());
			for (String coordinatorId : (String[]) rs.getArray("coordinators").getArray())
				project.getCoordinators().add(new Person(coordinatorId));

			project.setOrganizations(new ArrayList<Organization>());
			for (String organizationId : (String[]) rs.getArray("organizations").getArray())
				project.getOrganizations().add(new Organization(organizationId));

			return project;
		}
	};

	public List<Project> getProjectsCoordinatedBy(String personId) {
		return new ArrayList<Project>();
	}

	public List<Vocabulary> search(String term) {
		return new JdbcTemplate(dataSource).query(SEARCH_PROJECTS, new Object[]{"%" + term + "%", "%" + term + "%", "%" + term + "%"}, new int[]{Types.VARCHAR, Types.VARCHAR, Types.VARCHAR}, new RowMapper<Vocabulary>() {
			@Override
			public Vocabulary mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new Vocabulary(rs.getString("id"), rs.getString("acronym") + " - " + rs.getString("grant") + " (" + rs.getString("title") + ")");
			}
		});
	}

	public Project getProject(String projectId) {
		return new JdbcTemplate(dataSource).queryForObject(GET_PROJECT, new Object[]{projectId}, new int[]{Types.VARCHAR}, rowMapper);
	}

	public DataSource getDataSource() {
		return dataSource;
	}

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

	public List<Project> getEligibleProjectsForOrganization(String organizationId) {
		return new JdbcTemplate(dataSource).query(GET_PROJECTS_FOR_ORGANIZATION, new Object[]{organizationId, this.expirationOk}, new int[]{Types.VARCHAR, Types.INTEGER}, rowMapper);
	}

	public List<Project> getFutureEligibleProjectsForOrganization(String organizationId) {
		return new JdbcTemplate(dataSource).query(GET_FUTURE_PROJECTS_FOR_ORGANIZATION, new String[]{organizationId}, new int[]{Types.VARCHAR}, rowMapper);
	}

	public long getExpirationOk() {
		return expirationOk;
	}

	public void setExpirationOk(long expirationOk) {
		this.expirationOk = expirationOk;
	}
}