package com.ibm.ta.dao.db2;

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 com.ibm.ta.dao.EnrollmentDAO;
import com.ibm.ta.dao.DAOException;
import com.ibm.ta.dao.DAOUtil;
import com.ibm.ta.webservice.Course;
import com.ibm.ta.webservice.Student;

public class DB2EnrollmentDAO implements EnrollmentDAO {
	private DB2DAOFactory factory;

	public DB2EnrollmentDAO(DB2DAOFactory factory) {
		this.factory = factory;
	}

	public void insertEnrollment(long studentID, long courseID)
			throws DAOException {
		Connection con = null;
		PreparedStatement ps = null;

		try {
			con = factory.createConnection();

			ps = con
					.prepareStatement("INSERT INTO ta.enrollment (course_id, student_id) VALUES (?, ?)");
			ps.setLong(1, studentID);
			ps.setLong(2, courseID);

			ps.executeUpdate();
		} catch (SQLException sqle) {
			throw new DAOException(sqle.getMessage(), sqle);
		} finally {
			DAOUtil.closeResources(null, ps, null, con);
		}
	}

	public Student[] getEnrolledStudents(long courseID) throws DAOException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List results = new ArrayList();

		try {
			con = factory.createConnection();
			ps = con
					.prepareStatement("SELECT s.student_id, s.student_name from student AS s, enrollment AS e where s.student_id = e.student_id and course_id = ?");

			ps.setLong(1, courseID);

			rs = ps.executeQuery();
			while (rs.next()) {
				Student student = new Student(rs.getLong(1), rs.getString(2));
				results.add(student);
			}
		} catch (SQLException sqle) {
			throw new DAOException(sqle.getMessage(), sqle);
		} finally {
			DAOUtil.closeResources(rs, ps, null, con);
		}
		return (Student[]) results.toArray(new Student[results.size()]);
	}

	public Course[] getCourseEnrollments(long studentID) throws DAOException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List results = new ArrayList();

		try {
			con = factory.createConnection();
			ps = con
					.prepareStatement("SELECT c.course_id, c.course_name from course AS c, enrollment AS e where c.course_id = e.course_id and e.student_id = ?");

			ps.setLong(1, studentID);

			rs = ps.executeQuery();
			while (rs.next()) {
				Course course = new Course(rs.getLong(1), rs.getString(2));
				results.add(course);
			}
		} catch (SQLException sqle) {
			throw new DAOException(sqle.getMessage(), sqle);
		} finally {
			DAOUtil.closeResources(rs, ps, null, con);
		}
		return (Course[]) results.toArray(new Course[results.size()]);
	}
}