package eu.dnetlib.goldoa.service.dao;

import eu.dnetlib.goldoa.domain.Affiliation;
import eu.dnetlib.goldoa.domain.Organization;
import eu.dnetlib.goldoa.domain.Person;
import eu.dnetlib.goldoa.domain.PersonRole;
import eu.dnetlib.goldoa.domain.Project;
import eu.dnetlib.goldoa.domain.Publisher;
import eu.dnetlib.goldoa.domain.Role;
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.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.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

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

	@Autowired
	private DataSource dataSource;

	private static final String GET_BY_ID =
			"select p.id, email, firstname, lastname, initials, password, telephone, active, orcidid, p.source, p.publisher, array_agg(distinct pro.project) as projects, array_agg(distinct a.organization||'||'||case when a.department is null then '' else a.department end||'||'||case when a.startdate is null then 0 else extract(epoch from a.startdate) end||'||'||case when a.enddate is null then 0 else extract(epoch from a.enddate) end) as affiliations, array_agg(distinct pr.role||'||'||pr.approved) as roles\n" +
					"from person p\n" +
					"left join person_role pr on p.id = pr.person\n" +
					"left join role r on r.id = pr.role\n" +
					"left join affiliation a on a.person = p.id\n" +
					"left join organisation o on o.id = a.organization\n" +
					"left join project_coordinator pro on pro.coordinator=p.id\n" +
					"where p.id = ?\n" +
					"group by p.id, p.email, firstname, lastname, initials, password, telephone, active, orcidid, p.source\n";

	private static final String GET_BY_EMAIL =
			"select p.id, email, firstname, lastname, initials, password, telephone, active, orcidid, p.source, p.publisher, array_agg(distinct pro.project) as projects, array_agg(distinct a.organization||'||'||case when a.department is null then '' else a.department end||'||'||case when a.startdate is null then 0 else extract(epoch from a.startdate) end||'||'||case when a.enddate is null then 0 else extract(epoch from a.enddate) end) as affiliations, array_agg(distinct pr.role||'||'||pr.approved) as roles\n" +
					"from person p\n" +
					"left join person_role pr on p.id = pr.person\n" +
					"left join role r on r.id = pr.role\n" +
					"left join affiliation a on a.person = p.id\n" +
					"left join organisation o on o.id = a.organization\n" +
					"left join project_coordinator pro on pro.coordinator=p.id\n" +
					"where email = ? and password is not null\n" +
					"group by p.id, p.email, firstname, lastname, initials, password, telephone, active, orcidid, p.source\n";

	private static final String GET_USERS =
			"select p.id, email, firstname, lastname, initials, password, telephone, active, orcidid, p.source, p.publisher, array_agg(distinct pro.project) as projects, array_agg(distinct a.organization||'||'||case when a.department is null then '' else a.department end||'||'||case when a.startdate is null then 0 else extract(epoch from a.startdate) end||'||'||case when a.enddate is null then 0 else extract(epoch from a.enddate) end) as affiliations, array_agg(distinct pr.role||'||'||pr.approved) as roles\n" +
					"from person p\n" +
					"left join person_role pr on p.id = pr.person\n" +
					"left join role r on r.id = pr.role\n" +
					"left join affiliation a on a.person = p.id\n" +
					"left join organisation o on o.id = a.organization\n" +
					"left join project_coordinator pro on pro.coordinator=p.id\n" +
					"where password is not null \n" +
					"group by p.id, p.email, firstname, lastname, initials, password, telephone, active, orcidid, p.source\n";

	private static final String INSERT_PERSON = "insert into person (email, firstname, lastname, initials, password, telephone, orcidid, source, publisher, id) values (?, ?, ?, ?, md5(?), ?, ?, ?, ?, ?);";

	private static final String UPDATE_PERSON = "update person set email=?, firstname=?, lastname=?, initials=?, password= (case when password=? then password else md5(?) end) , telephone=?, orcidid=?, source=?, publisher=? where id=?";

	private static final String INSERT_COORDINATORS = "insert into project_coordinator (project, coordinator) values (?, ?)";

	private static final String INSERT_PERSON_ROLE = "insert into person_role (person, role, approved) values (?, ? ,?)";

	private static final String INSERT_AFFILIATION = "insert into affiliation (id, person, organization, startdate, enddate, department) values (?, ?, ?, ?, ?, ?)";

	private static final String INSERT_ACTIVATION_TOKEN = "insert into account_action (\"user\", type, token, date, expires) values (?, ?, ? , ?, ?)";

	private static final String LOGIN = "select 1 from person where email = ? and password = md5(?)";

	private static final String GET_PERSON_TOKEN = "select 1 from person p join account_action ac on ac.user=p.id where p.email=? and ac.token=?";

	private static final String ACTIVATE_USER = "update person set active=true where id = ?";

	private static final String DELETE_ACTIVATION_TOKEN = "delete from account_action where user=? and token=? and type='activation'";

	private static final String UPDATE_PASSWORD = "update person set password=md5(?) where email=?";

	private static final String GET_MODERATORS = "select distinct email, firstname, lastname from person p join person_role pr on pr.person=p.id and pr.role='moderator' and approved=true";

	private static final String GET_ACCOUNTING_OFFICERS = "select distinct email, firstname, lastname from person p join person_role pr on pr.person=p.id and pr.role='accounting' and approved=true";

	/**
	 * Returns the person with the given id. Objects of different type are placeholders containing only their id.
	 * Objects representing relations are fully initialized, with placeholders for other main entities.
	 *
	 * @param personId
	 * @return
	 */
	public Person getPersonById(String personId) {
		return new JdbcTemplate(dataSource).queryForObject(GET_BY_ID, new Object[]{personId}, new int[]{Types.VARCHAR}, new RowMapper<Person>() {
			@Override
			public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
				return readPerson(rs);
			}
		});
	}

	/**
	 * Returns the person with the given email. Objects of different type are placeholders containing only their id.
	 * Objects representing relations are fully initialized, with placeholders for other main entities.
	 *
	 * @param email
	 * @return
	 */
	public Person getPersonByEmail(String email) {
		return new JdbcTemplate(dataSource).queryForObject(GET_BY_EMAIL, new Object[]{email}, new int[]{Types.VARCHAR}, new RowMapper<Person>() {
			@Override
			public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
				return readPerson(rs);
			}
		});
	}

	public void saveToken(final String personId, final String token, final int tokenTTL) {
		new JdbcTemplate(dataSource).update(INSERT_ACTIVATION_TOKEN, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {

				ps.setString(1, personId);
				ps.setString(2, "activation");
				ps.setString(3, token);
				ps.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
				ps.setTimestamp(5, new Timestamp(System.currentTimeMillis() + 1000L * 60L * 60L * 24L * tokenTTL));
			}
		});
	}

	public boolean verifyLogin(String email, String password) {
		return new JdbcTemplate(dataSource).query(LOGIN, new Object[]{email, password}, new int[]{Types.VARCHAR, Types.VARCHAR}, new RowMapper<String>() {
			@Override
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				return "1";
			}
		}).size() > 0;
	}

	public boolean verifyToken(final String email, final String token) {
		return new JdbcTemplate(dataSource).query(GET_PERSON_TOKEN, new Object[]{email, token}, new int[]{Types.VARCHAR, Types.VARCHAR}, new RowMapper<Object>() {
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return 1;
			}
		}).size() > 0;
	}

	public void activateUser(final String personId) {
		new JdbcTemplate(dataSource).update(ACTIVATE_USER, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, personId);
			}
		});
	}

	public void deleteToken(final String personId, final String token) {
		new JdbcTemplate(dataSource).update(DELETE_ACTIVATION_TOKEN, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, personId);
				ps.setString(2, token);
			}
		});
	}

	public boolean updatePassword(final String newPassword, final String email) {
		return new JdbcTemplate(dataSource).update(UPDATE_PASSWORD, new String[]{newPassword, email}, new int[]{Types.VARCHAR, Types.VARCHAR}) > 0;
	}

	public List<Role> getRoles() {
		return new JdbcTemplate(dataSource).query("select id, name from role", new Object[]{}, new int[]{}, new RowMapper<Role>() {
			@Override
			public Role mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new Role(rs.getString("id"), rs.getString("name"));
			}
		});
	}

	public void savePerson(final Person person) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		int updated = jdbcTemplate.update(UPDATE_PERSON, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, person.getEmail());
				ps.setString(2, person.getName());
				ps.setString(3, person.getLastname());
				ps.setString(4, person.getInitials());
				ps.setString(5, person.getPassword());
				ps.setString(6, person.getPassword());
				ps.setString(7, person.getTelephone());
				ps.setString(8, person.getOrcidId());
				ps.setString(9, person.getSource());
				if (person.getPublisher() != null)
					ps.setString(10, person.getPublisher().getId());
				else
					ps.setString(10, null);
				ps.setString(11, person.getId());
			}
		});

		if (updated == 0) {
			jdbcTemplate.update(INSERT_PERSON, new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, person.getEmail());
					ps.setString(2, person.getName());
					ps.setString(3, person.getLastname());
					ps.setString(4, person.getInitials());
					ps.setString(5, person.getPassword());
					ps.setString(6, person.getTelephone());
					ps.setString(7, person.getOrcidId());
					ps.setString(8, person.getSource());
					if (person.getPublisher() != null)
						ps.setString(9, person.getPublisher().getId());
					else
						ps.setString(9, null);
					ps.setString(10, person.getId());
				}
			});
		}
	}

	public void deleteAffiliations(final String personId) {
		new JdbcTemplate(dataSource).update("delete from affiliation where person=?", new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement preparedStatement) throws SQLException {
				preparedStatement.setString(1, personId);
			}
		});
	}

	public void deletePersonRoles(final String personId) {
		new JdbcTemplate(dataSource).update("delete from person_role where person=?", new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement preparedStatement) throws SQLException {
				preparedStatement.setString(1, personId);
			}
		});
	}

	//TODO MOVE TO PROJECT DAO
	public void removeProjectCoordinators(final String personId) {
		new JdbcTemplate(dataSource).update("delete from project_coordinator where coordinator=?", new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement preparedStatement) throws SQLException {
				preparedStatement.setString(1, personId);
			}
		});
	}

	public void saveProjectCoordinators(final String personId, final List<String> projectIds) {
		new JdbcTemplate(dataSource).batchUpdate(INSERT_COORDINATORS, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setString(1, projectIds.get(i));
				ps.setString(2, personId);
			}

			@Override
			public int getBatchSize() {
				return projectIds.size();
			}
		});
	}

	public void savePersonRoles(final String personId, final List<PersonRole> roles) {
		new JdbcTemplate(dataSource).batchUpdate(INSERT_PERSON_ROLE, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				PersonRole pr = roles.get(i);

				ps.setString(1, personId);
				ps.setString(2, pr.getRole().getId());
				ps.setBoolean(3, pr.isApproved());
			}

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

	/**
	 * @param person       included because the GWT serialization returns a different object than the original (perhaps...)
	 * @param affiliations
	 */
	public void saveAffiliations(final Person person, final List<Affiliation> affiliations) {
		new JdbcTemplate(dataSource).batchUpdate(INSERT_AFFILIATION, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Affiliation affiliation = affiliations.get(i);

				ps.setInt(1, PersonManagerImpl.generateId(affiliation));
				ps.setString(2, person.getId());

				if (affiliation.getOrganization() != null)
					ps.setString(3, affiliation.getOrganization().getId());
				else
					ps.setString(3, null);

				if (affiliation.getStart() != null)
					ps.setTimestamp(4, new Timestamp(affiliation.getStart().getTime()));
				else
					ps.setTimestamp(4, null);

				if (affiliation.getEnd() != null)
					ps.setTimestamp(5, new Timestamp(affiliation.getEnd().getTime()));
				else
					ps.setTimestamp(5, null);

				ps.setString(6, affiliation.getDepartment());
			}

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

	public DataSource getDataSource() {
		return dataSource;
	}

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

	public List<Person> getModerators() {
		return new JdbcTemplate(dataSource).query(GET_MODERATORS, new RowMapper<Person>() {
			@Override
			public Person mapRow(ResultSet rs, int i) throws SQLException {
				Person person = new Person();

				person.setEmail(rs.getString("email"));
				person.setName(rs.getString("firstname"));
				person.setLastname(rs.getString("lastname"));

				return person;
			}
		});
	}

	public List<Person> getAccountingOfficers() {
		return new JdbcTemplate(dataSource).query(GET_ACCOUNTING_OFFICERS, new RowMapper<Person>() {
			@Override
			public Person mapRow(ResultSet rs, int i) throws SQLException {
				Person person = new Person();

				person.setEmail(rs.getString("email"));
				person.setName(rs.getString("firstname"));
				person.setLastname(rs.getString("lastname"));

				return person;
			}
		});
	}

	public Role getRole(final String roleId) {
		return new JdbcTemplate(dataSource).queryForObject("select id, name from role where id = ?", new Object[]{roleId}, new int[]{Types.VARCHAR}, new RowMapper<Role>() {
			@Override
			public Role mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new Role(rs.getString("id"), rs.getString("name"));
			}
		});
	}

	public void deactivateUser(String userId) {
		new JdbcTemplate(dataSource).update("update person set active=false where id=?", new Object[]{userId}, new int[]{Types.VARCHAR});
	}

	public void acceptUserRole(String userId, String roleId) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		if (jdbcTemplate.update("update person_role set approved=true where person=? and role=?", new Object[]{userId, roleId}, new int[]{Types.VARCHAR, Types.VARCHAR}) == 0) {
			jdbcTemplate.update("insert into person_role (person, role, approved) values (?, ?, true)", new Object[]{userId, roleId}, new int[]{Types.VARCHAR, Types.VARCHAR});
		}
	}

	public void rejectUserRole(String userId, String roleId) {
		new JdbcTemplate(dataSource).update("delete from person_role where person=? and role=?", new Object[]{userId, roleId}, new int[]{Types.VARCHAR, Types.VARCHAR});
	}

	public List<Person> getUsers() {
		return new JdbcTemplate(dataSource).query(GET_USERS, new RowMapper<Person>() {
			@Override
			public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
				return readPerson(rs);
			}
		});
	}

	private Person readPerson(ResultSet rs) throws SQLException {
		Person person = new Person();

		person.setId(rs.getString("id"));
		person.setEmail(rs.getString("email"));
		person.setName(rs.getString("firstname"));
		person.setLastname(rs.getString("lastname"));
		person.setInitials(rs.getString("initials"));
		person.setTelephone(rs.getString("telephone"));
		person.setActive(rs.getBoolean("active"));
		person.setOrcidId(rs.getString("orcidid"));
		person.setPassword(rs.getString("password"));
		person.setSource(rs.getString("source"));
		person.setPublisher(new Publisher(rs.getString("publisher")));

		person.setCoordinatedProjects(new ArrayList<Project>());
		for (String projectId : (String[]) rs.getArray("projects").getArray()) {
			person.getCoordinatedProjects().add(new Project(projectId));
		}

		person.setAffiliations(new ArrayList<Affiliation>());
		for (String affString : (String[]) rs.getArray("affiliations").getArray()) {
			if (affString != null) {
				String[] parts = affString.split("\\|\\|");
				String org = parts[0];
				String dep = parts[1];
				Date start = parts[2] == "0" ? null : new Date(Long.parseLong(parts[2]));
				Date end = parts[3] == "0" ? null : new Date(Long.parseLong(parts[3]));

				Affiliation affiliation = new Affiliation();

				affiliation.setPerson(person);
				affiliation.setOrganization(new Organization(org));
				affiliation.setDepartment(dep);
				affiliation.setStart(start);
				affiliation.setEnd(end);

				person.getAffiliations().add(affiliation);
			}
		}

		person.setRoles(new ArrayList<PersonRole>());
		for (String rString : (String[]) rs.getArray("roles").getArray()) {
			if (rString != null) {
				String[] parts = rString.split("\\|\\|");
				String role = parts[0];
				boolean approved = Boolean.parseBoolean(parts[1]);

				person.getRoles().add(new PersonRole(person, getRole(role), approved));
			}
		}

		return person;
	}
}