package com.roshka.jdbc;

import java.util.Scanner;
import java.sql.*;


public class Q12 {

	public static void main( String [] args ) {
		String url = "jdbc:postgresql://localhost:5433/testdb";
		String user = "test";
		String password = "pqntslc";
		Scanner entrada = new Scanner(System.in);
		String codigo;
		try {
			/* QUERY
			 * SELECT fullname, name, code, paid
			 * FROM (enrollments en
			 * LEFT JOIN courses co
			 * ON en.course_iid = co.course_iid) AS en_co
			 * LEFT JOIN students st
			 * ON en_co.student_iid = st.student_iid
			 * where paid = 'false' and code=codigo;
			 *  */ 
			
			Connection connection = DriverManager.getConnection(url, user,password);
			System.out.println("estoy conectado a la base de datos");
			Statement st = connection.createStatement();
			System.out.println("Ingrese el codigo del curso: ");
			codigo = entrada.nextLine();
			String query = "SELECT fullname, name, enrollment_iid, paid "
							+ "FROM (enrollments en "
							+ "LEFT JOIN courses co "
							+ "ON en.course_iid = co.course_iid) AS en_co "
							+ "LEFT JOIN students st "
							+ "ON en_co.student_iid = st.student_iid "
							+ "WHERE paid = 'false' AND code='"+codigo+"';";
			
			ResultSet rs = st.executeQuery(query);
			while(rs.next() ) {
				String paid = rs.getString("paid");
				String nombre = rs.getString("fullname");
				String enrollment_iid = rs.getString("enrollment_iid");
				System.out.println("EL alumno "+nombre+" ya ha pagado? 's' o 'n'");
				String respuesta = entrada.next();
				if(respuesta.equals("s") ) {
					String update = "UPDATE enrollments "
										+ "SET paid = 'true', "
										+ "payment_date = CURRENT_DATE "
										+ "WHERE enrollment_iid = '"+enrollment_iid+"';";	
					st.executeUpdate(update);
					System.out.println("se ha actualizado la base de datos");
				}
			}
			
			System.out.println("termino la actualizacion");
		}catch(SQLException e) {
			e.printStackTrace();;
		}
	}
}