package eu.dnetlib.goldoa.service.dao;

import eu.dnetlib.goldoa.domain.Budget;
import eu.dnetlib.goldoa.domain.Organization;
import eu.dnetlib.goldoa.domain.Vocabulary;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
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.Arrays;
import java.util.List;

/**
 * Created by antleb on 3/13/15.
 */
public class OrganizationDAO {
	@Autowired
	private DataSource dataSource;

	private static final String SEARCH_ORGANIZATIONS = "select id, name from organisation where lower(name) like lower(?)";
	private static final String GET_ORGANIZATION = "select o.id, name, shortname, array_agg(distinct budget) as budgets from organisation o left join organization_budget ob on ob.organization=o.id where o.id = ? group by o.id, name, shortname";
	private static final String GET_AFFILIATED_WITH_USER = "select o.id, name, shortname, array_agg(distinct budget) as budgets \n" +
			"from organisation o \n" +
			"left join organization_budget ob on ob.organization=o.id \n" +
			"join affiliation a on a.organization=o.id\n" +
			"where a.person=?\n" +
			"group by o.id, name, shortname";

	private final String INSERT_ORGANIZATION = "insert into organisation (name, shortname, source, id) values (?,?,?,?)";
	private final String UPDATE_ORGANIZATION = "update organisation set name=?, shortname=?, source=? where id=?";

	private final String INSERT_ORGANIZATION_BUDGET = "insert into organization_budget (organization, budget) values (?, ?)";

	public Organization getOrganization(String id) {
		return new JdbcTemplate(dataSource).queryForObject(GET_ORGANIZATION, new Object[]{id}, new int[]{Types.VARCHAR}, new RowMapper<Organization>() {

			@Override
			public Organization mapRow(ResultSet rs, int rowNum) throws SQLException {
				Organization org = new Organization();

				org.setId(rs.getString("id"));
				org.setName(rs.getString("name"));
				org.setShortName(rs.getString("shortname"));

				org.setBudgets(new ArrayList<Budget>());
				for (String budget : Arrays.asList((String[]) rs.getArray("budgets").getArray()))
					if (budget != null)
						org.getBudgets().add(new Budget(budget));

				return org;
			}
		});
	}

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

	public Organization saveOrganization(Organization organization) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Object[] args = {organization.getName(), organization.getShortName(), organization.getSource(), organization.getId()};
		int[] types = {Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR};

		if (jdbcTemplate.update(UPDATE_ORGANIZATION, args, types) == 0)
			jdbcTemplate.update(INSERT_ORGANIZATION, args, types);

		return organization;
	}

	public void insertOrganization(final Organization organization) {
		new JdbcTemplate(dataSource).update(INSERT_ORGANIZATION, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, organization.getId());
				ps.setString(2, organization.getName());
				ps.setString(3, organization.getShortName());
			}
		});
	}

	public void insertOrganizationBudgets(final Organization organization) {
		new JdbcTemplate(dataSource).batchUpdate(INSERT_ORGANIZATION_BUDGET, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setString(1, organization.getId());
				ps.setString(2, organization.getBudgets().get(i).getId());
			}

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

	public List<Organization> getAffiliatedWithUser(String userId) {
		return new JdbcTemplate(dataSource).query(GET_AFFILIATED_WITH_USER, new Object[]{userId}, new int[]{Types.VARCHAR}, new RowMapper<Organization>() {

			@Override
			public Organization mapRow(ResultSet rs, int rowNum) throws SQLException {
				Organization org = new Organization();

				org.setId(rs.getString("id"));
				org.setName(rs.getString("name"));
				org.setShortName(rs.getString("shortname"));

				org.setBudgets(new ArrayList<Budget>());
				for (String budget : Arrays.asList((String[]) rs.getArray("budgets").getArray()))
					if (budget != null)
						org.getBudgets().add(new Budget(budget));

				return org;
			}
		});
	}

	public void deleteOrganizationBudgets(Organization organization) {
		new JdbcTemplate(dataSource).update("delete from organization_budget where organization = ?", new String[]{organization.getId()}, new int[]{Types.VARCHAR});
	}
}