package org.ibai.app.webui.prestamo_digital;

import org.dspace.core.ConfigurationManager;
import org.dspace.storage.rdbms.DatabaseManager;

import java.io.File;
import java.sql.*;
import java.sql.SQLException;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;

import java.util.Date;
import java.util.Properties;  
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import javax.mail.Message;  
import javax.mail.MessagingException;  
import javax.mail.Session;  
import javax.mail.Transport;  
import javax.mail.internet.InternetAddress;  
import javax.mail.internet.MimeMessage;  

import org.apache.log4j.Logger;

public class PDActualizarLicencias {
	
		private static final Properties properties = new Properties();  
		
		private static final String texto_mail_es = "<br/><br/>Tiene de plazo el día de hoy para cogerlo en préstamo, pasado este tiempo, el préstamo estará disponible para otro lector.<br/><br/>Un saludo";
		private static final String texto_mail_eu = "<br/><br/>Gaurko eguna duzu mailegatzeko. Denbora hori pasa eta gero mailegua beste irakurle batentzat egongo da erabilgarri.<br/><br/>Agur bat";
		private static final String texto_mail_en = "<br/><br/>The deadline to borrow it is today, after this time limit, the loan will be available for another reader.<br/><br/>Best regards";
      
    private static Session session;  
    
    /** log4j logger */
		private static Logger log = Logger.getLogger(PDActualizarLicencias.class);
		
		/* Estados de las reservas: pendiente,correo_enviado, finalizada */
		/* Estados de las licencias:  libre, ocupada, standby, agotada */
		/* Estados del prestamo: activo, inactivo */
  
    private static void init() {  
        properties.put("mail.smtp.host", ConfigurationManager.getProperty("mail.server"));  
        properties.put("mail.smtp.mail.sender",ConfigurationManager.getProperty("ibai.pd.direccion_mailing"));  
        properties.put("mail.smtp.user", ConfigurationManager.getProperty("mail.server.username"));  
        properties.put("mail.smtp.pass", ConfigurationManager.getProperty("mail.server.password"));  
        session = Session.getDefaultInstance(properties);  
    }  
	
		private static Connection conecta() { 
				try {
					Class.forName("oracle.jdbc.OracleDriver");
					String bd      = ConfigurationManager.getProperty("db.url");
					String usuario = ConfigurationManager.getProperty("db.username");
					String pass    = ConfigurationManager.getProperty("db.password");
					return DriverManager.getConnection(bd, usuario, pass);
				}
				catch (Exception er) {
					System.out.println("Error conecta: " + er.getMessage());
					log.info("Error conecta: " + er.getMessage());
					return null;
				} 
		}
		
		private static void mandar_mail(String direccion, String asunto, String cuerpo) {
  		try {
  			MimeMessage message = new MimeMessage(session);
        message.setFrom(new InternetAddress((String)properties.get("mail.smtp.mail.sender")));
        message.addRecipient(Message.RecipientType.TO, new InternetAddress(direccion));  
        message.setSubject(asunto);  
        message.setContent(cuerpo, "text/html; charset=utf-8");
        Transport t = session.getTransport("smtp");  
        t.connect((String)properties.get("mail.smtp.user"), (String)properties.get("mail.smtp.pass"));  
        t.sendMessage(message, message.getAllRecipients());
        t.close();
        System.out.println("Email a " + direccion + " enviado.");
        log.info("Email a " + direccion + " enviado.");
      }
			catch(Exception ex) {
				/*if (ex instanceof SQLException || ex instanceof MessagingException) {*/
					System.out.println("Email error: " + ex.getMessage());
					log.info("Email error: "+ ex.getMessage());
				/*}*/
			}
  	}
	
		private static String corregirCaracteres(String cadena) {
				String result = cadena;
				result = result.replace("'", "");
				return result;
		}
	
		public static void main(String[] argv) throws Exception {
				init();
				System.out.println("......................");
				log.info("......................");
				System.out.println("Actualizar licencias standby...");
				log.info("Actualizar licencias standby...");
				actualizar_licencias_standby();
				
				System.out.println("");
				System.out.println("Actualizar licencias ocupadas...");
				log.info("");
				log.info("Actualizar licencias ocupadas...");
				actualizar_licencias_ocupadas();
				log.info("");
				log.info("Actualizar licencias poner agotadas...");
				actualizar_licencias_poner_agotada();
    }
    
    
    private static void actualizar_licencias_standby() throws Exception {
				Connection conexion = null;
				PreparedStatement st_update_estado = null;
				PreparedStatement ps = null;
				PreparedStatement ps_reservas = null;
				PreparedStatement ps_mail_usuario = null;
				PreparedStatement sentencia = null;
				PreparedStatement sentencia_update = null;
				ResultSet rs = null;
				ResultSet rs_reservas = null;
				ResultSet rs_mail_usuario = null;
				ResultSet resultado = null;
				int prestamos_restantes = 0;
				int licencia_id = 0;
				int libro       = 0;
				try {
						conexion = DatabaseManager.getConnection();
						//String consulta = "SELECT ID_LICENCIAS,LIBRO, NUMERO_PRESTAMOS_RESTANTES, TO_CHAR(TO_DATE(TO_CHAR( FECHA_ULTIMA_ADJUDICACION, 'DD/MM/YYYY' ))+21, 'DD/MM/YYYY') FECHA_HASTA FROM PD_LICENCIAS WHERE ESTADO='standby'";
						String consulta = "SELECT ID_LICENCIAS,LIBRO, NUMERO_PRESTAMOS_RESTANTES, TO_CHAR(TO_DATE(TO_CHAR( FECHA_ULTIMA_ADJUDICACION, 'DD/MM/YYYY' ))+DURACION_PRESTAMO, 'DD/MM/YYYY') FECHA_HASTA FROM PD_LICENCIAS LICENCIAS, PD_LIBROS LIBROS, PD_EDITORIALES EDITORIALES WHERE LICENCIAS.LIBRO=LIBROS.ID_LIBROS AND LIBROS.EDITORIAL=EDITORIALES.ID_EDITORIALES AND ESTADO='standby'";
						ps = conexion.prepareStatement(consulta);
						rs = ps.executeQuery();
						while (rs.next())  {
								prestamos_restantes = rs.getInt("NUMERO_PRESTAMOS_RESTANTES");
								licencia_id         = rs.getInt("ID_LICENCIAS");
								libro               = rs.getInt("LIBRO");
								System.out.println("************** " + libro + " " + licencia_id + "************** ");
								log.info("************** " + libro + " " + licencia_id + "************** ");
								String consulta_reservas = "SELECT ID_RESERVAS,RES.USUARIO USUARIO,RES.ESTADO ESTADO,LIB.TITULO TITULO,LIB.AUTOR AUTOR FROM PD_RESERVAS RES, PD_LIBROS LIB WHERE RES.LIBRO=LIB.ID_LIBROS AND LIBRO=" + libro + " AND ESTADO!='finalizada' ORDER BY PRIORIDAD"; 
								ps_reservas = conexion.prepareStatement(consulta_reservas);
								rs_reservas = ps_reservas.executeQuery();
								boolean pasa_al_siguiente = false;
								String estado_reserva = "";
								String direccion_email = "";
								String libro_titulo = "";
								String libro_autor  = "";
								PreparedStatement st_cambiar_estado = null;
								int cambiar_estado = 0;
								boolean reserva_finalizada = false;
								while ((rs_reservas.next()) && (!pasa_al_siguiente))  {
										estado_reserva = rs_reservas.getString("ESTADO");
										if ((estado_reserva.equals("correo_enviado")) && (!reserva_finalizada)) {
												//Cambiar estado a 'finalizada' o borrarla
												/*
												System.out.println("Cambiar estado a 'finalizada'");
												st_cambiar_estado = conexion.prepareStatement("UPDATE PD_RESERVAS SET ESTADO='finalizada' WHERE ID_RESERVAS=?");
												st_cambiar_estado.setInt(1, rs_reservas.getInt("ID_RESERVAS"));
											 	cambiar_estado = st_cambiar_estado.executeUpdate();
											 	*/
											 	
											 	System.out.println("Borrar reserva");
											 	log.info("Borrar reserva");
												st_cambiar_estado = conexion.prepareStatement("DELETE PD_RESERVAS WHERE ID_RESERVAS=?");
												st_cambiar_estado.setInt(1, rs_reservas.getInt("ID_RESERVAS"));
											 	cambiar_estado = st_cambiar_estado.executeUpdate();
											 	conexion.commit();
											 	
											 	//Actualizar prioridades del resto
											 	if (cambiar_estado==1) {
														int prioridad_nueva = 0;
														int registro = 0;
											  		sentencia = conexion.prepareStatement("SELECT ID_RESERVAS FROM PD_RESERVAS WHERE LIBRO=? ORDER BY PRIORIDAD ASC");
											  		sentencia.setInt(1, libro);
											  		resultado = sentencia.executeQuery();
											  		while (resultado.next()) {
											  				prioridad_nueva++;
											  				sentencia_update = conexion.prepareStatement("UPDATE PD_RESERVAS SET PRIORIDAD=? WHERE ID_RESERVAS=?");
															  sentencia_update.setInt(1, prioridad_nueva);
															  sentencia_update.setInt(2, resultado.getInt("ID_RESERVAS"));
																registro = sentencia_update.executeUpdate();
																conexion.commit();
											  		}
									  		}
											 	
											 	
											 	reserva_finalizada = true;
										}
										if (estado_reserva.equals("pendiente")) {
												//Cambiar estado a 'correo_enviado'
												System.out.println("Cambiar estado a 'correo_enviado'");
												log.info("Cambiar estado a 'correo_enviado'");
												st_cambiar_estado = conexion.prepareStatement("UPDATE PD_RESERVAS SET ESTADO='correo_enviado' WHERE ID_RESERVAS=?");
												st_cambiar_estado.setInt(1, rs_reservas.getInt("ID_RESERVAS"));
											 	cambiar_estado = st_cambiar_estado.executeUpdate();
											 	conexion.commit();
												
												
												libro_titulo = rs_reservas.getString("TITULO");
											  libro_autor  = rs_reservas.getString("AUTOR");
												ps_mail_usuario = conexion.prepareStatement("SELECT EMAIL FROM PD_USUARIOS_ABSYS WHERE USUARIO=?");
												ps_mail_usuario.setString(1, rs_reservas.getString("USUARIO"));
												rs_mail_usuario = ps_mail_usuario.executeQuery();
												if (rs_mail_usuario.next()) direccion_email = rs_mail_usuario.getString("EMAIL");
												
												/* AZKUNA */
												if ((direccion_email == null) || (direccion_email.equals(""))) {
														ps_mail_usuario = conexion.prepareStatement("SELECT EMAIL FROM PD_USUARIOS_AZKUNA WHERE USUARIO=?");
														ps_mail_usuario.setString(1, rs_reservas.getString("USUARIO"));
														rs_mail_usuario = ps_mail_usuario.executeQuery();
														if (rs_mail_usuario.next()) direccion_email = rs_mail_usuario.getString("EMAIL");
												}
												/* AZKUNA */
												
												/* DONOSTI */
												if ((direccion_email == null) || (direccion_email.equals(""))) {
														ps_mail_usuario = conexion.prepareStatement("SELECT EMAIL FROM PD_USUARIOS_DK WHERE USUARIO=?");
														ps_mail_usuario.setString(1, rs_reservas.getString("USUARIO"));
														rs_mail_usuario = ps_mail_usuario.executeQuery();
														if (rs_mail_usuario.next()) direccion_email = rs_mail_usuario.getString("EMAIL");
												}
												/* DONOSTI */
												
												if ((direccion_email != null) && (!direccion_email.equals(""))) {
														mandar_mail(direccion_email, "Libro disponible", "Estimado usuario:<br/><br/>El libro: <b>" + libro_titulo + "</b> de " + libro_autor + " ya está disponible." + texto_mail_es + "<br/><br/><br/><br/>" +
																																						 "Erabiltzaile hori:<br/><br/>" + libro_autor + "-en <b>" + libro_titulo + "</b> liburua erabilgarri dago jada." + texto_mail_eu + "<br/><br/><br/><br/>" +
																																						 "Dear user:<br/><br/>The book <b>" + libro_titulo + "</b> of " + libro_autor + " is already available." + texto_mail_en);
												}
												
												pasa_al_siguiente = true;
										}
								}
								if (!pasa_al_siguiente) {
										if (prestamos_restantes > 0) {
												//Cambiar estado de la licencia a 'libre'
												System.out.println("Cambiar estado de la licencia a 'libre'");
												log.info("Cambiar estado de la licencia a 'libre'");
												st_cambiar_estado = conexion.prepareStatement("UPDATE PD_LICENCIAS SET ESTADO='libre' WHERE ID_LICENCIAS=?");
												st_cambiar_estado.setInt(1, licencia_id);
											 	cambiar_estado = st_cambiar_estado.executeUpdate();
											 	conexion.commit();
										}
										else {
											 //Cambiar estado de la licencia a 'agotada'
											 	System.out.println("Cambiar estado de la licencia a 'agotada'");
											 	log.info("Cambiar estado de la licencia a 'agotada'");
											 	st_cambiar_estado = conexion.prepareStatement("UPDATE PD_LICENCIAS SET ESTADO='agotada' WHERE ID_LICENCIAS=?");
												st_cambiar_estado.setInt(1, licencia_id);
											 	cambiar_estado = st_cambiar_estado.executeUpdate();
											 	conexion.commit();
										}
								}
						}
				}
				catch(SQLException ex) {
						System.out.println("Error: " + ex.getMessage());
						log.info("Error: "+ ex.getMessage());
				}	
				finally {
					if (rs != null) rs.close();
					if (ps != null) ps.close();
					if (sentencia != null) sentencia.close();
					if (sentencia_update != null) sentencia_update.close();
					if (resultado != null) resultado.close();
					if (conexion != null) conexion.close();
				}
		}
		
		private static void actualizar_licencias_ocupadas() throws Exception {
				Connection conexion = null;
				PreparedStatement st_update_estado = null;
				PreparedStatement ps = null;
				PreparedStatement ps_reservas = null;
				PreparedStatement ps_mail_usuario = null;
				ResultSet rs = null;
				ResultSet rs_reservas = null;
				ResultSet rs_mail_usuario = null;
				int total_modificados = 0;
				int prestamos_restantes = 0;
				int licencia_id = 0;
				int libro       = 0;
				try {
						conexion = DatabaseManager.getConnection();
						//String consulta = "SELECT ID_LICENCIAS,LIBRO, NUMERO_PRESTAMOS_RESTANTES, TO_CHAR(TO_DATE(TO_CHAR( FECHA_ULTIMA_ADJUDICACION, 'DD/MM/YYYY' ))+21, 'DD/MM/YYYY') FECHA_HASTA FROM PD_LICENCIAS WHERE ESTADO='ocupada' AND TO_CHAR(TO_DATE(TO_CHAR( FECHA_ULTIMA_ADJUDICACION, 'DD/MM/YYYY' ))+21, 'DD/MM/YYYY')<=SYSDATE"; 
						String consulta = "SELECT ID_LICENCIAS,LIBRO, NUMERO_PRESTAMOS_RESTANTES, TO_CHAR(TO_DATE(TO_CHAR( FECHA_ULTIMA_ADJUDICACION, 'DD/MM/YYYY' ))+DURACION_PRESTAMO, 'DD/MM/YYYY') FECHA_HASTA FROM PD_LICENCIAS LICENCIAS, PD_LIBROS LIBROS, PD_EDITORIALES EDITORIALES WHERE LICENCIAS.LIBRO=LIBROS.ID_LIBROS AND LIBROS.EDITORIAL=EDITORIALES.ID_EDITORIALES AND ESTADO='ocupada' AND TO_CHAR(TO_DATE(TO_CHAR( FECHA_ULTIMA_ADJUDICACION, 'DD/MM/YYYY' ))+DURACION_PRESTAMO, 'DD/MM/YYYY')<=SYSDATE"; 
						ps = conexion.prepareStatement(consulta);
						rs = ps.executeQuery();
						while (rs.next())  {
								prestamos_restantes = rs.getInt("NUMERO_PRESTAMOS_RESTANTES");
								licencia_id         = rs.getInt("ID_LICENCIAS");
								libro               = rs.getInt("LIBRO");
								System.out.println("************** " + libro + " " + licencia_id + "************** ");
								log.info("************** " + libro + " " + licencia_id + "************** ");
								if (prestamos_restantes <= 0) {
										st_update_estado  = conexion.prepareStatement("UPDATE PD_LICENCIAS SET ESTADO='agotada' WHERE ID_LICENCIAS=" + licencia_id);
									 	total_modificados = st_update_estado.executeUpdate();
									 	conexion.commit();	
								}
								else {
										int numero_reservas = 0;
										String consulta_reservas = "SELECT ID_RESERVAS,RES.USUARIO,RES.ESTADO ESTADO,LIB.TITULO TITULO,LIB.AUTOR AUTOR FROM PD_RESERVAS RES, PD_LIBROS LIB WHERE RES.LIBRO=LIB.ID_LIBROS AND LIBRO=" + libro + " AND ESTADO='pendiente' ORDER BY PRIORIDAD"; 
										//log.info("Query 1: " + consulta_reservas);
										ps_reservas = conexion.prepareStatement(consulta_reservas);
										rs_reservas = ps_reservas.executeQuery();
										String direccion_email = "";
										String libro_titulo = "";
										String libro_autor  = "";
										while (rs_reservas.next())  {
												numero_reservas++;
												if (numero_reservas == 1) {
													  libro_titulo = rs_reservas.getString("TITULO");
													  libro_autor  = rs_reservas.getString("AUTOR");
														ps_mail_usuario = conexion.prepareStatement("SELECT EMAIL FROM PD_USUARIOS_ABSYS WHERE USUARIO=?");
														ps_mail_usuario.setString(1, rs_reservas.getString("USUARIO"));
														rs_mail_usuario = ps_mail_usuario.executeQuery();
														if (rs_mail_usuario.next()) direccion_email = rs_mail_usuario.getString("EMAIL");
														
														/* AZKUNA */
														if ((direccion_email == null) || (direccion_email.equals(""))) {
																ps_mail_usuario = conexion.prepareStatement("SELECT EMAIL FROM PD_USUARIOS_AZKUNA WHERE USUARIO=?");
																ps_mail_usuario.setString(1, rs_reservas.getString("USUARIO"));
																rs_mail_usuario = ps_mail_usuario.executeQuery();
																if (rs_mail_usuario.next()) direccion_email = rs_mail_usuario.getString("EMAIL");
														}
														/* AZKUNA */
														
														/* DONOSTI */
														if ((direccion_email == null) || (direccion_email.equals(""))) {
																ps_mail_usuario = conexion.prepareStatement("SELECT EMAIL FROM PD_USUARIOS_DK WHERE USUARIO=?");
																ps_mail_usuario.setString(1, rs_reservas.getString("USUARIO"));
																rs_mail_usuario = ps_mail_usuario.executeQuery();
																if (rs_mail_usuario.next()) direccion_email = rs_mail_usuario.getString("EMAIL");
														}
														/* DONOSTI */
														
														if ((direccion_email != null) && (!direccion_email.equals(""))) {
																mandar_mail(direccion_email, "Libro disponible", "Estimado usuario:<br/><br/>El libro: <b>" + libro_titulo + "</b> de " + libro_autor + " ya está disponible." + texto_mail_es + "<br/><br/><br/><br/>" +
																																								 "Erabiltzaile hori:<br/><br/>" + libro_autor + "-en <b>" + libro_titulo + "</b> liburua erabilgarri dago jada." + texto_mail_eu + "<br/><br/><br/><br/>" +
																																						 		 "Dear user:<br/><br/>The book <b>" + libro_titulo + "</b> of " + libro_autor + " is already available." + texto_mail_en);
														}
														
														
														//Cambiar estado a 'correo_enviado'
														PreparedStatement st_cambiar_estado = conexion.prepareStatement("UPDATE PD_RESERVAS SET ESTADO='correo_enviado' WHERE ID_RESERVAS=?");
														st_cambiar_estado.setInt(1, rs_reservas.getInt("ID_RESERVAS"));
													 	int cambiar_estado = st_cambiar_estado.executeUpdate();
													 	conexion.commit();
													 	if (st_cambiar_estado != null) st_cambiar_estado.close();
														System.out.println("Cambiar estado a correo_enviado: update " + cambiar_estado);
														log.info("Cambiar estado a correo_enviado: update " + cambiar_estado);
												}
										}
										PreparedStatement st_estado_licencias = null;
										int estado_licencias = 0;
										if (numero_reservas >= 1) {
												//Cambiar estado licencia a standby
												System.out.println("Cambiar estado licencia a standby");
												log.info("Cambiar estado licencia a standby");
												st_estado_licencias = conexion.prepareStatement("UPDATE PD_LICENCIAS SET ESTADO='standby' WHERE ID_LICENCIAS=?");
												st_estado_licencias.setInt(1, licencia_id);
											 	estado_licencias = st_estado_licencias.executeUpdate();
											 	conexion.commit();
											 	if (st_estado_licencias != null) st_estado_licencias.close();
										}
										else {
												//Cambiar estado licencia a libre
												System.out.println("Cambiar estado licencia a libre");
												log.info("Cambiar estado licencia a libre");
												st_estado_licencias = conexion.prepareStatement("UPDATE PD_LICENCIAS SET ESTADO='libre' WHERE ID_LICENCIAS=?");
												st_estado_licencias.setInt(1, licencia_id);
											 	estado_licencias = st_estado_licencias.executeUpdate();
											 	conexion.commit();
											 	if (st_estado_licencias != null) st_estado_licencias.close();
										}
								}
						}
						System.out.println("Licencias actualizadas");
				    log.info("Licencias actualizadas");
				}
				catch(SQLException ex) {
						System.out.println("Error: " + ex.getMessage());
						log.info("Error: "+ ex.getMessage());
				}	
				finally {
					if (rs != null) rs.close();
					if (ps != null) ps.close();
					if (rs_reservas != null) rs_reservas.close();
					if (ps_reservas != null) ps_reservas.close();
					if (ps_mail_usuario != null) ps_mail_usuario.close();
					if (rs_mail_usuario != null) rs_mail_usuario.close();
					if (st_update_estado != null) st_update_estado.close();
					if (conexion != null) conexion.close();
				}
		}
		
		private static void actualizar_licencias_poner_agotada() throws Exception {
				Connection conexion = null;
				PreparedStatement sentencia_update = null;

				int registro = 0;
				try {
						conexion = DatabaseManager.getConnection();
						sentencia_update = conexion.prepareStatement("UPDATE PD_LICENCIAS SET ESTADO='agotada' WHERE NUMERO_PRESTAMOS_RESTANTES<=0 AND ESTADO<>'agotada'");
						registro = sentencia_update.executeUpdate();
						conexion.commit();
						System.out.println("Poner licencias agotadas");
				    log.info("Poner licencias agotadas");
				}
				catch(SQLException ex) {
						System.out.println("Error: " + ex.getMessage());
						log.info("Error: "+ ex.getMessage());
				}	
				finally {
					if (sentencia_update != null) sentencia_update.close();
					if (conexion != null) conexion.close();
				}
		}
    
    
}