package eu.dnetlib.data.espas.dataprovider;

import eu.dnetlib.domain.data.espas.HarvestSchedule;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * Created by antleb on 6/24/14.
 */
@Transactional(propagation=Propagation.REQUIRED)
public class ScheduleDao extends AbstractDao {

	private static final String UPDATE_SCHEDULE = "update \"harvest\".schedule set types=?, dataproviderid=?, cronexpression=?, initialdate=?, lastdate=?, status=? where id=?";
	private static final String INSERT_SCHEDULE = "insert into \"harvest\".schedule (id, types, dataproviderid, cronexpression, initialdate, lastdate, status) values (?, ?, ?, ?, ?, ?, ?)";
	private static final String DELETE_SCHEDULE = "delete from \"harvest\".schedule where id=?";
	private static final String GET_SCHEDULES_BY_DATAPROVIDER = "select * from \"harvest\".schedule where dataproviderid=?";
	private static final String GET_SCHEDULES = "select * from \"harvest\".schedule";
	private static final String GET_RUNNING_SCHEDULES = "select * from \"harvest\".schedule where status='RUNNING'";
	private static final String GET_SCHEDULE = "select * from \"harvest\".schedule where id=?";
	private static final String UPDATE_SCHEDULE_STATUS = "update \"harvest\".schedule  set status=? where id=?";

	public void saveSchedule(HarvestSchedule schedule) throws SQLException {
		Connection con = getConnection();
		PreparedStatement stmt = con.prepareStatement(UPDATE_SCHEDULE);

		stmt.setArray(1, con.createArrayOf("text", schedule.getTypes().toArray()));
		stmt.setString(2, schedule.getDataProviderId());
		stmt.setString(3, schedule.getCronExpression());
		stmt.setTimestamp(4, new Timestamp(schedule.getInitialModificationDate().getTime()));
		stmt.setTimestamp(5, schedule.getLastExecutionDate()!= null?new Timestamp(schedule.getLastExecutionDate().getTime()):null);
		stmt.setString(6, schedule.getScheduleStatus().getStatus());
		stmt.setString(7, schedule.getScheduleId());

		if (stmt.executeUpdate() == 0) {
			stmt.close();
			stmt = con.prepareStatement(INSERT_SCHEDULE);

			stmt.setString(1, schedule.getScheduleId());
			stmt.setArray(2, con.createArrayOf("text", schedule.getTypes().toArray()));
			stmt.setString(3, schedule.getDataProviderId());
			stmt.setString(4, schedule.getCronExpression());
			stmt.setTimestamp(5, new Timestamp(schedule.getInitialModificationDate().getTime()));
			stmt.setTimestamp(6, schedule.getLastExecutionDate()!= null?new Timestamp(schedule.getLastExecutionDate().getTime()):null);
			stmt.setString(7, schedule.getScheduleStatus().getStatus());

			stmt.executeUpdate();
		}

		stmt.close();
	}

	public void deleteSchedule(String scheduleId) throws SQLException {
		Connection con = getConnection();
		PreparedStatement stmt = con.prepareStatement(DELETE_SCHEDULE);

		stmt.setString(1, scheduleId);

		stmt.executeUpdate();

		stmt.close();
	}

	public List<HarvestSchedule> getSchedules(String dataProviderId) throws SQLException {
		Connection con = getConnection();
		PreparedStatement stmt;

		if (dataProviderId != null) {
			stmt = con.prepareStatement(GET_SCHEDULES_BY_DATAPROVIDER);
			stmt.setString(1, dataProviderId);
		} else {
			stmt = con.prepareStatement(GET_SCHEDULES);
		}

		List<HarvestSchedule> res = new ArrayList<HarvestSchedule>();
		ResultSet rs = stmt.executeQuery();

		while (rs.next()) {
			HarvestSchedule schedule = new HarvestSchedule();

			schedule.setScheduleId(rs.getString("id"));
			schedule.setTypes(Arrays.asList((String[]) rs.getArray("types").getArray()));
			schedule.setDataProviderId(rs.getString("dataproviderid"));
			schedule.setInitialModificationDate(rs.getDate("initialdate"));
			schedule.setLastExecutionDate(rs.getDate("lastdate"));
			schedule.setCronExpression(rs.getString("cronexpression"));
			schedule.setScheduleStatus(HarvestSchedule.ScheduleStatus.valueOf(rs.getString("status")));

			res.add(schedule);
		}

		stmt.close();

		return res;
	}

	public void pauseSchedule(String scheduleId) throws SQLException {
		Connection con = getConnection();
		PreparedStatement stmt = con.prepareStatement(UPDATE_SCHEDULE_STATUS);

		stmt.setString(1, HarvestSchedule.ScheduleStatus.PAUSED.getStatus());
		stmt.setString(2, scheduleId);

		stmt.executeUpdate();

		stmt.close();
	}

	public void resumeSchedule(String scheduleId) throws SQLException {
		Connection con = getConnection();
		PreparedStatement stmt = con.prepareStatement(UPDATE_SCHEDULE_STATUS);

		stmt.setString(1, HarvestSchedule.ScheduleStatus.RUNNING.getStatus());
		stmt.setString(2, scheduleId);

		stmt.executeUpdate();

		stmt.close();
	}

	public HarvestSchedule getSchedule(String scheduleId) throws SQLException {
		Connection con = getConnection();
		PreparedStatement stmt = con.prepareStatement(GET_SCHEDULE);
		HarvestSchedule schedule = new HarvestSchedule();

		stmt.setString(1, scheduleId);

		ResultSet rs = stmt.executeQuery();

		if (rs.next()) {

			schedule.setScheduleId(rs.getString("id"));
			schedule.setTypes(Arrays.asList((String[]) rs.getArray("types").getArray()));
			schedule.setDataProviderId(rs.getString("dataproviderid"));
			schedule.setInitialModificationDate(rs.getTimestamp("initialdate"));
			schedule.setLastExecutionDate(rs.getTimestamp("lastdate"));
			schedule.setCronExpression(rs.getString("cronexpression"));
			schedule.setScheduleStatus(HarvestSchedule.ScheduleStatus.valueOf(rs.getString("status")));
		}

		stmt.close();

		return schedule;
	}

	public List<HarvestSchedule> getRunningSchedules() throws SQLException {
		Connection con = getConnection();
		PreparedStatement stmt = con.prepareStatement(GET_RUNNING_SCHEDULES);
		List<HarvestSchedule> res = new ArrayList<HarvestSchedule>();

		ResultSet rs = stmt.executeQuery();

		while (rs.next()) {
			HarvestSchedule schedule = new HarvestSchedule();

			schedule.setScheduleId(rs.getString("id"));
			schedule.setTypes(Arrays.asList((String[]) rs.getArray("types").getArray()));
			schedule.setDataProviderId(rs.getString("dataproviderid"));
			schedule.setInitialModificationDate(rs.getDate("initialdate"));
			schedule.setLastExecutionDate(rs.getDate("lastdate"));
			schedule.setCronExpression(rs.getString("cronexpression"));
			schedule.setScheduleStatus(HarvestSchedule.ScheduleStatus.valueOf(rs.getString("status")));

			res.add(schedule);
		}

		stmt.close();

		return res;
	}
}