package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.naming.NamingException;
import javax.servlet.http.HttpServletRequest;
import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;
import data.DegreeData;

import com.sun.rowset.CachedRowSetImpl;

import db.DbException;



public class StudentModel {
	private static String server_info = "jdbc:postgresql://localhost/postgres";
	private static String server_user = "postgres";
	private static String server_pass = "1111";
	private static String countries = "SELECT id, myname FROM countries ORDER BY id";
	private static String location = "SELECT * FROM schoolfrom ORDER BY id";
	private static String schools = "SELECT school FROM school_schoolfrom WHERE sfrom = ?";
	private static String schoolNames = "SELECT myname FROM schools WHERE id = ?";

	public static RowSet getAllCountries() throws Exception{
		try{

		Class.forName("org.postgresql.Driver");

        // Open a connection to the database using DriverManager
        Connection conn = DriverManager.getConnection(
            server_info,server_user, server_pass);
        PreparedStatement pStmt = conn.prepareStatement(countries);
		ResultSet allCountires = pStmt.executeQuery();

		CachedRowSet crsStudents = new CachedRowSetImpl();
		crsStudents.populate(allCountires);

		allCountires.close();
		pStmt.close();
		conn.close();
		return crsStudents;
		}	catch (SQLException ex) {
			throw new Exception(ex);
		}

	}

	public static RowSet getAllLocations() throws Exception{
		try{

		Class.forName("org.postgresql.Driver");

        // Open a connection to the database using DriverManager
        Connection conn = DriverManager.getConnection(
            server_info,server_user, server_pass);
        PreparedStatement pStmt = conn.prepareStatement(location);
		ResultSet allCountires = pStmt.executeQuery();

		CachedRowSet location = new CachedRowSetImpl();
		location.populate(allCountires);

		allCountires.close();
		pStmt.close();
		conn.close();
		return location;
		}	catch (SQLException ex) {
			throw new Exception(ex);
		}
	}

		public static Vector<String> getSchools(DegreeData degreeData) throws Exception{
			try{

			Vector<String> school = new Vector<String>();
			Vector<String> schoolname = new Vector<String>();

			Class.forName("org.postgresql.Driver");
	        // Open a connection to the database using DriverManager
	        Connection conn = DriverManager.getConnection(
	            server_info,server_user, server_pass);

	        String locationID = degreeData.getLocationID();
	        System.err.println("locationID is:"+ locationID);

	        PreparedStatement pStmt = conn.prepareStatement(schools);
	        pStmt.setString(1,locationID);
	    	ResultSet rs = pStmt.executeQuery();
			if(rs.next()){
				school.add(rs.getString(1));
			}

			pStmt = conn.prepareStatement(schoolNames);
			for(int i = 0; i< school.size(); i++){
				pStmt.setString(1, school.get(i));
				rs = pStmt.executeQuery();
				if(rs.next()){
					schoolname.add(rs.getString(1));
					System.out.println(rs.getString(1));
				}
			}

			pStmt.close();
			conn.close();
			return schoolname;
			}	catch (SQLException ex) {
				throw new Exception(ex);
			}
	}


}