package dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetProvider;
import javax.swing.table.TableModel;
import model.Criterion;
import model.CriterionEditor;
import model.Student;
import ui.CriterionPanel;

/**
 *
 * @author Mark George <mark.george@otago.ac.nz>
 */
public class ResultDAO {

	private static final Logger logger = Logger.getLogger(ResultDAO.class.getName());

	private static Map<String, CriterionEditor> criteria = new HashMap<>();

	public void addEditor(Criterion criterion, CriterionPanel criterionEditor) {
		criteria.put(criterion.getCriterion(), criterionEditor);
	}

	public Boolean isComplete() {
		for (CriterionEditor editor : criteria.values()) {
			if (!editor.isComplete()) {
				return Boolean.FALSE;
			}
		}

		return Boolean.TRUE;
	}

	public void save(String submissionId) {

		try {

			try (
				 Connection con = JdbcConnection.getConnection();  PreparedStatement stmt = con.prepareStatement("merge into result (submission_id, criterion, result, comment) values (?,?,?,?)")) {

				for (Map.Entry<String, CriterionEditor> entry : criteria.entrySet()) {
					CriterionEditor editor = entry.getValue();

					if (!editor.isComplete()) {
						continue;
					}

					String criterion = entry.getKey();
					Double result = editor.getResult();
					String comment = editor.getComment();

					stmt.setString(1, submissionId);
					stmt.setString(2, criterion);
					stmt.setDouble(3, result);
					stmt.setString(4, comment);

					stmt.addBatch();
				}

				stmt.executeBatch();

				clear();
			}

		} catch (SQLException e) {
			logger.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}

	}

	public void load(String submissionId) {

		try {

			try (
				 Connection con = JdbcConnection.getConnection();  PreparedStatement stmt = con.prepareStatement("select * from result where submission_id = ?")) {

				stmt.setString(1, submissionId);

				ResultSet rs = stmt.executeQuery();

				while (rs.next()) {

					String criterion = rs.getString("criterion");
					Double result = rs.getDouble("result");
					String comment = rs.getString("comment");

					CriterionEditor editor = criteria.get(criterion);

					editor.setResult(result);
					editor.setComment(comment);
				}

			}

		} catch (SQLException e) {
			logger.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}

	}

	public TableModel getSubmissions() {
		try (
			 Connection con = JdbcConnection.getConnection();  Statement stmt = con.createStatement()) {

			ResultSet rs = stmt.executeQuery("select * from all_marks order by \"Student\"");

			CachedRowSet crs = RowSetProvider.newFactory().createCachedRowSet();

			crs.populate(rs);

			return new RowSetTableModel(crs);

		} catch (SQLException ex) {
			logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
		}
		return null;
	}

	public Collection<Map<String, ?>> getResult(String studentId) {
		String sql = "SELECT * FROM report where student_id = ?";

		Collection<Map<String, ?>> data = new ArrayList<>();

		try (
			 Connection con = JdbcConnection.getConnection();  PreparedStatement s = con.prepareStatement(sql);) {

			s.setString(1, studentId);

			ResultSet rs = s.executeQuery();

			while (rs.next()) {
				Map<String, Object> result = new HashMap<>();

				ResultSetMetaData metaData = rs.getMetaData();

				int columnCount = metaData.getColumnCount();

				for (int i = 1; i <= columnCount; i++) {
					String columnName = metaData.getColumnName(i);
					Object value = rs.getObject(columnName);
					if (value instanceof Number) {
						value = new BigDecimal(String.valueOf(value));
					}
					result.put(columnName, value);
				}

				data.add(result);
			}

			return data;

		} catch (SQLException ex) {
			logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
			return null;
		}
	}

	public Student findStudent(String submissionId) {

		try (
			 Connection con = JdbcConnection.getConnection();  PreparedStatement stmt = con.prepareStatement("select * from student where submission_id = ?")) {

			stmt.setString(1, submissionId);

			ResultSet rs = stmt.executeQuery();

			if (rs.next()) {

				String id = rs.getString("student_id");
				String username = rs.getString("username");
				String firstName = rs.getString("first_name");
				String lastName = rs.getString("last_name");
				String email = rs.getString("email");
				Boolean sendFeedback = rs.getBoolean("send_feedback");
				Boolean emailSent = rs.getBoolean("email_sent");
				Student s = new Student(id, submissionId, username, firstName, lastName, email, sendFeedback, emailSent);

				return s;
			}

		} catch (SQLException ex) {
			logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
		}

		return null;

	}

	private void clear() {
		criteria = new HashMap<>();
	}

	public Collection<Student> getStudents() {
		List<Student> students = new ArrayList<>();
		try (
			 Connection con = JdbcConnection.getConnection();  PreparedStatement studs = con.prepareStatement("select * from student where student_id in (select distinct student_id from report) order by username")) {

			ResultSet studsRs = studs.executeQuery();

			while (studsRs.next()) {
				String id = studsRs.getString("student_id");
				String submissionId = studsRs.getString("submission_id");
				String firstName = studsRs.getString("first_name");
				String lastName = studsRs.getString("last_name");
				String username = studsRs.getString("username");
				String email = studsRs.getString("email");
				Boolean sendFeedback = studsRs.getBoolean("send_feedback");
				Boolean emailSent = studsRs.getBoolean("email_sent");
				Student s = new Student(id, submissionId, username, firstName, lastName, email, sendFeedback, emailSent);
				students.add(s);
			}
		} catch (SQLException ex) {
			throw new RuntimeException(ex);
		}
		return students;
	}

	public void mailSent(Student student) {

		try {

			try (
				 Connection con = JdbcConnection.getConnection();  PreparedStatement stmt = con.prepareStatement("update student set email_sent = true where student_id = ?");) {

				stmt.setString(1, student.getId());

				stmt.executeUpdate();
				con.commit();

			}

		} catch (SQLException ex) {
			throw new RuntimeException(ex);
		}

	}

}