package de.ugoe.cs.swe.exercises.exercisesheet;

import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import org.tmatesoft.svn.core.SVNException;
import org.tmatesoft.svn.core.internal.io.dav.DAVRepositoryFactory;
import org.tmatesoft.svn.core.internal.wc.DefaultSVNOptions;
import org.tmatesoft.svn.core.wc.SVNClientManager;
import org.tmatesoft.svn.core.wc.SVNWCClient;
import org.tmatesoft.svn.core.wc.SVNWCUtil;

import de.ugoe.cs.swe.exercises.exercise.Exercise;
import de.ugoe.cs.swe.exercises.exercise.ExerciseSVN;
import de.ugoe.cs.swe.exercises.lectureevent.LectureEvent;
import de.ugoe.cs.swe.exercises.misc.Model;

public class ExerciseSheet extends Model {
	private int number, revisionNumber, complexityValue, lectureEventId,
			exerciseMaxRevision, numOfExercises;
	private boolean examination;

	@Override
	protected String getTableName() {
		return "exercisesheet";
	}

	public static class OutOfRangeException extends RuntimeException {
		private static final long serialVersionUID = 1L;

		public OutOfRangeException() {
			super();
		}

		public OutOfRangeException(String message) {
			super(message);
		}
	}

	public ExerciseSheet(int id, int number, int revisionNumber,
			int complexityValue, boolean examination, LectureEvent lectureEvent, int numOfExercises) {
		super(id);
		this.number = number;
		this.revisionNumber = revisionNumber;
		this.setComplexityValue(complexityValue);
		this.examination = examination;
		this.lectureEventId = lectureEvent.getId();
		this.numOfExercises = numOfExercises;
	}

	public ExerciseSheet(int number, int revisionNumber, int complexityValue,
			boolean examination, LectureEvent lectureEvent, int numOfExercises) {
		super();
		this.number = number;
		this.revisionNumber = revisionNumber;
		this.setComplexityValue(complexityValue);
		this.examination = examination;
		this.lectureEventId = lectureEvent.getId();
		this.numOfExercises = numOfExercises;
	}
	
	public ExerciseSheet(LectureEvent lectureEvent) {
		super();
		this.number = 1;
		this.numOfExercises = 5;
		this.setComplexityValue(20);
		this.revisionNumber = -1;
		this.lectureEventId = lectureEvent.getId();
		this.examination = false;
	}

	public LectureEvent getLectureEvent() {
		return LectureEvent.get(lectureEventId);
	}

	public void setLectureEvent(LectureEvent lectureEvent) {
		this.lectureEventId = lectureEvent.getId();
	}

	public int getNumber() {
		return number;
	}

	public void setNumber(int number) {
		this.number = number;
	}

	public int getRevisionNumber() {
		return revisionNumber;
	}

	public void setRevisionNumber(int revisionNumber) {
		this.revisionNumber = revisionNumber;
	}

	public int getComplexityValue() {
		return complexityValue;
	}

	public void setComplexityValue(int complexityValue) {
		// if (complexityValue < 0 || complexityValue > 10)
		// throw new OutOfRangeException(
		// "Complexity value must be between 0 and 10.");
		this.complexityValue = complexityValue;
	}

	public boolean isExamination() {
		return examination;
	}

	public void setExamination(boolean examination) {
		this.examination = examination;
	}

	@Override
	protected void update() throws SQLException {
		PreparedStatement statement = prepareStatement("UPDATE exercisesheet SET number=?, examination=?, revisionnumber=?, complexityvalue=?, lectureevent=?, numOfExercises=? WHERE id=?");
		statement.setInt(7, this.getId());
		executeSaveQuery(statement);
	}

	@Override
	protected PreparedStatement insert() throws SQLException {
		PreparedStatement statement = prepareStatement("INSERT INTO exercisesheet (number, examination, revisionnumber, complexityvalue, lectureevent, numOfExercises) VALUES (?,?,?,?,?,?)");
		executeSaveQuery(statement);
		return statement;
	}

	protected void executeSaveQuery(PreparedStatement statement)
			throws SQLException {
		statement.setInt(1, this.getNumber());
		statement.setBoolean(2, this.isExamination());
		statement.setInt(3, this.returnMaxRevision());
//		statement.setInt(3, this.getRevisionNumber());
		statement.setInt(4, this.getComplexityValue());
		if (this.getLectureEvent().getId() == Model.UNDEFINED)
			throw new Model.DoesNotExistException(
					"LectureEvent does not exist in the database, yet");
		statement.setInt(5, this.lectureEventId);
		statement.setInt(6, this.numOfExercises);
		statement.executeUpdate();
	}

	public static ArrayList<ExerciseSheet> all() {
		try {
			return fromStatement(prepareStatement("SELECT * FROM exercisesheet ORDER BY number DESC"));
		} catch (SQLException e) {
			System.err.println("Got an exception in Lecture.all()! ");
			System.err.println(e.getMessage());
		}
		return null;
	}

	public static ExerciseSheet getItem(int id) {
		try {
			return fromStatement(
					prepareStatement("SELECT * FROM exercisesheet WHERE id ="
							+ id)).get(0);
		} catch (SQLException e) {
			System.err.println("Got an exception in Lecture.all()! ");
			System.err.println(e.getMessage());
		}
		return null;
	}

	public static ArrayList<ExerciseSheet> fromStatement(
			PreparedStatement statement) throws SQLException {
		ResultSet resultset = statement.executeQuery();
		ArrayList<ExerciseSheet> lectures = new ArrayList<ExerciseSheet>();
		while (resultset.next()) {
			lectures.add(new ExerciseSheet(resultset.getInt("id"), resultset
					.getInt("number"), resultset.getInt("revisionnumber"),
					resultset.getInt("complexityvalue"), resultset
							.getBoolean("examination"), LectureEvent
							.get(resultset.getInt("lectureevent")), resultset.getInt("numOfExercises")));
		}
		return lectures;
	}

	public int getNumOfExercises() {
		return numOfExercises;
	}

	public void setNumOfExercises(int numOfExercises) {
		this.numOfExercises = numOfExercises;
	}

	public ArrayList<Exercise> getExercises() {
		try {
			PreparedStatement statement = prepareStatement("SELECT DISTINCT exercise.* FROM exercisesheet, exercise, exercisesheet_exercise WHERE exercisesheet.id = ? AND exercisesheet.id = exercisesheet_exercise.exercisesheet AND exercise.id = exercisesheet_exercise.exercise ORDER BY id");
			statement.setInt(1, this.getId());
			return Exercise.fromStatement(statement);
		} catch (SQLException e) {
			System.err
					.println("Got an exception in ExerciseSheet.getExercises()! ");
			System.err.println(e.getMessage());
		}
		return null;
	}

	public void setExerciseMaxRevision(int exerciseMaxRevision) {
		this.exerciseMaxRevision = exerciseMaxRevision;
	}

	public int getExerciseMaxRevision() {
		return exerciseMaxRevision;
	}

	public void addExercise(Exercise exercise) {
		if (this.getId() == Model.UNDEFINED)
			throw new Model.DoesNotExistException(
					"ExerciseSheet does not exist in the database, yet");
		if (exercise.getId() == Model.UNDEFINED)
			throw new Model.DoesNotExistException(
					"Lecture event does not exist in the database, yet");

		PreparedStatement statement;
		try {
			statement = prepareStatement("INSERT INTO exercisesheet_exercise (exercisesheet, exercise, revisionnumber) VALUES (?,?,?)");
			statement.setInt(1, this.getId());
			statement.setInt(2, exercise.getId());
			statement.setInt(3, exercise.getRevisionnumber());
			statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void removeExercise(Exercise exercise) {
		if (this.getId() == Model.UNDEFINED)
			throw new Model.DoesNotExistException(
					"Lecture does not exist in the database, yet");
		if (exercise.getId() == Model.UNDEFINED)
			throw new Model.DoesNotExistException(
					"Lecture event does not exist in the database, yet");

		PreparedStatement statement;
		try {
			statement = prepareStatement("DELETE FROM exercisesheet_exercise WHERE exercisesheet=? AND exercise=?");
			statement.setInt(1, this.getId());
			statement.setInt(2, exercise.getId());
			statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void deleteDuplicates(int id) {
		PreparedStatement statement;
		try {
			statement = prepareStatement("DELETE FROM exercisesheet_exercise WHERE exercisesheet=?");
			statement.setInt(1, id);
			statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public int returnMaxRevision() {
		PreparedStatement statement;
		int temp = 0, max = 0;
		try {
			if(this.getExercises().size()<=0) return -1;
			statement = prepareStatement("SELECT revisionnumber FROM exercisesheet_exercise WHERE exercisesheet=?");
			statement.setInt(1, this.getId());
			// statement.executeUpdate();
			ResultSet resultset = statement.executeQuery();
			resultset.next();
			max = resultset.getInt("revisionnumber");
			while (resultset.next()) {
				temp = resultset.getInt("revisionnumber");
				if (temp > max)
					max = temp;
			}
			return max;
		} catch (SQLException e) {
			e.printStackTrace();
			
		}

		return -1;
	}
	

	public static void delete(File file) {

		DefaultSVNOptions options = SVNWCUtil.createDefaultOptions(false);
		DAVRepositoryFactory.setup();

		SVNClientManager ourClientManager = SVNClientManager.newInstance(
				options, ExerciseSVN.getUserName(), ExerciseSVN.getPassWord());

		SVNWCClient ourWCClient = new SVNWCClient(ourClientManager, options);

		
				try {
					ourWCClient.doDelete(file, true, false);
				} catch (SVNException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
		
	ExerciseSVN.SVNUpdate();
		ExerciseSVN.SVNcommit();

		// System.out.println("filesystem delete");
		// for (int i = 0; i < file.length; i++) {
		//			
		// file[i].delete();
		//			
		// }

	}
}
