package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Criterion;

/**
 *
 * @author mark
 */
public class ScheduleDAO {

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

	public List<String> getCategories() {

		final List<String> categories = new ArrayList<>();

		try {

			try (
				Connection con = JdbcConnection.getConnection();
				PreparedStatement stmt = con.prepareStatement("select category from category order by sort_order");
				ResultSet rs = stmt.executeQuery()) {

				while (rs.next()) {
					String category = rs.getString("category");
					categories.add(category);
				}

			}

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

		return categories;
	}

	public List<Criterion> getCriteria(String category) {
		List<Criterion> criteria = new ArrayList<>();

		try {

			try (
				Connection con = JdbcConnection.getConnection();
				PreparedStatement stmt = con.prepareStatement("select * from criterion where category = ? order by sort_order");) {

				stmt.setString(1, category);

				ResultSet rs = stmt.executeQuery();

				criteria = loadCriteria(rs);

			}

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

		return criteria;
	}

	static List<Criterion> loadCriteria(ResultSet rs) {
		final List<Criterion> criteria = new ArrayList<>();

		try {

			while (rs.next()) {
				String criterion = rs.getString("criterion");
				String description = rs.getString("description");
				Integer min = rs.getInt("min");
				Integer max = rs.getInt("max");
				Boolean special = rs.getBoolean("special");
				criteria.add(new Criterion(criterion, description, min, max, special));
			}

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

		return criteria;
	}

	public String getAssessmentName() {
		try {

			try (
				Connection con = JdbcConnection.getConnection();
				PreparedStatement stmt = con.prepareStatement("select name from assessment");) {

				ResultSet rs = stmt.executeQuery();

				rs.next();

				String name = rs.getString("name");

				return name;

			}

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

		return "Assessment not defined";
	}

		public Integer getComplete() {
		try {

			try (
				Connection con = JdbcConnection.getConnection();
				PreparedStatement stmt = con.prepareStatement("select cast((round(cast ((select count(distinct student) * 100 from result) as real)/ select count(student) from submission)) as integer) as complete");) {

				ResultSet rs = stmt.executeQuery();

				rs.next();

				Integer complete = rs.getInt("complete");

				return complete;

			}

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

		return 0;
	}

}