package trismegistoplanilla.sqlserverdao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.json.JSONArray;
import org.json.JSONObject;
import trismegistoplanilla.beans.Correo;
import trismegistoplanilla.beans.EstadoFichaBean;
import trismegistoplanilla.beans.PersonaBean;
import trismegistoplanilla.beans.TokenFichaBean;
import trismegistoplanilla.dao.PersonaDAO;
import trismegistoplanilla.utilities.EmailDesign;
import trismegistoplanilla.utilities.Encriptar;
import trismegistoplanilla.utilities.EncryptAction;
import trismegistoplanilla.utilities.GenerateCodeVerification;
import trismegistoplanilla.utilities.ResponseHelper;
import trismegistoplanilla.utilities.Variables;

public class PersonaSqlserverDAO implements PersonaDAO {

	@Override
	public JSONObject validarExistenciaNumeroDocumento(PersonaBean p) {
		System.out.println("PersonaSqlserverDAO: validarExistenciaNumeroDocumento");
		JSONObject jsonObjValidarExistenciaNumeroDocumento = null;
		ResponseHelper response = new ResponseHelper();
		int existeNumeroDocumento = 0;

		String sql = ""
			+ "select "
			+ "count(1) existeNumeroDocumento "
			+ "from persona "
			+ "where estado_registro = 1 and codigo_tipo_documento = ? and numero_documento = ?";

		Connection conexion = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conexion = SqlserverDAOFactory.obtenerConexion(Variables.BD_NAME);
			ps = conexion.prepareStatement(sql);
			ps.setInt(1, p.getCodigoTipoDocumento());
			ps.setString(2, p.getNumeroDocumento());
			rs = ps.executeQuery();
			rs.next();
			existeNumeroDocumento = rs.getInt("existeNumeroDocumento");
			if (existeNumeroDocumento > 0) {
				response.setStatus(false);
				response.setMessage("Error, el número de documento que acaba de ingresar, ya se encuentra registrado en el sistema.");
			} else if (existeNumeroDocumento == 0) {
				response.setStatus(true);
				response.setMessage("El número de documento puede ser registrado.");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			response.setStatus(false);
			response.setMessage("Error: " + e.getMessage() + " \n Error Code: [" + e.getErrorCode() + "]");
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (conexion != null) {
					conexion.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		jsonObjValidarExistenciaNumeroDocumento = new JSONObject(response);
		return jsonObjValidarExistenciaNumeroDocumento;
	}

	@Override
	public JSONObject validarExistenciaCorreoElectronico(PersonaBean p) {
		System.out.println("PersonaSqlserverDAO: validarExistenciaCorreoElectronico");
		JSONObject jsonObjValidarExistenciaCorreoElectronico = null;
		ResponseHelper response = new ResponseHelper();
		int existeCorreoElectronico = 0;

		String sql = ""
			+ "select "
			+ "count(1) existeCorreoElectronico "
			+ "from persona "
			+ "where estado_registro = 1 and correo = ?";

		Connection conexion = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conexion = SqlserverDAOFactory.obtenerConexion(Variables.BD_NAME);
			ps = conexion.prepareStatement(sql);
			ps.setString(1, p.getCorreo());
			rs = ps.executeQuery();
			rs.next();
			existeCorreoElectronico = rs.getInt("existeCorreoElectronico");
			if (existeCorreoElectronico > 0) {
				response.setStatus(false);
				response.setMessage("Error, el correo electrónico que acaba de ingresar, ya se encuentra registrado en el sistema.");
			} else if (existeCorreoElectronico == 0) {
				response.setStatus(true);
				response.setMessage("El correo electrónico puede ser registrado.");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			response.setStatus(false);
			response.setMessage("Error: " + e.getMessage() + " \n Error Code: [" + e.getErrorCode() + "]");
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (conexion != null) {
					conexion.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		jsonObjValidarExistenciaCorreoElectronico = new JSONObject(response);
		return jsonObjValidarExistenciaCorreoElectronico;
	}

	@Override
	public JSONObject registrarPersona(PersonaBean p, EstadoFichaBean ef, TokenFichaBean tf) {
		System.out.println("PersonaSqlserverDAO: registrarPersona");
		JSONObject jsonObjRegistrarPersona = null;
		ResponseHelper response = new ResponseHelper();

		String sqlPersona = ""
			+ "insert into persona "
			+ "(apellido_paterno, "
			+ "apellido_materno, "
			+ "nombre, "
			+ "codigo_tipo_documento, "
			+ "numero_documento, "
			+ "correo, "
			+ "is_default_mail, "
			+ "estado_registro) "
			+ "values "
			+ "("
			+ "upper(rtrim(ltrim(?))), "
			+ "upper(rtrim(ltrim(?))), "
			+ "upper(rtrim(ltrim(?))), "
			+ "upper(rtrim(ltrim(?))), "
			+ "upper(rtrim(ltrim(?))), "
			+ "upper(rtrim(ltrim(?))), "
			+ "upper(rtrim(ltrim(?))), "
			+ "1)";

		Connection conexion = null;
		PreparedStatement psPersona = null, psFicha = null, psEstadoFicha = null, psTokenFicha = null, psObtenerToken = null, psObtenerCodigoFicha = null;
		ResultSet rsPersona = null, rsFicha = null, rsEstadoFicha = null, rsTokenFicha = null, rsObtenerToken = null, rsObtenerCodigoFicha = null;
		try {
			conexion = SqlserverDAOFactory.obtenerConexion(Variables.BD_NAME);
			conexion.setAutoCommit(false);
			psPersona = conexion.prepareStatement(sqlPersona, Statement.RETURN_GENERATED_KEYS);
			psPersona.setString(1, p.getApellidoPaterno());
			psPersona.setString(2, p.getApellidoMaterno());
			psPersona.setString(3, p.getNombre());
			psPersona.setInt(4, p.getCodigoTipoDocumento());
			psPersona.setString(5, p.getNumeroDocumento());
			psPersona.setString(6, p.getCorreo());
			psPersona.setInt(7, p.getIsDefaultMail());
			int resultRegistrarPersona = psPersona.executeUpdate();

			if (resultRegistrarPersona > 0) {
				rsPersona = psPersona.getGeneratedKeys();
				rsPersona.next();
				int codigoPersona = rsPersona.getInt(1);

				/*-************ REGISTRAR FICHA **************-*/
				String sqlFicha = ""
					+ "insert into ficha ( "
					+ "codigo_persona "
					+ ",estado_registro "
					+ ") "
					+ "values ( "
					+ "? "
					+ ",1 "
					+ ")";

				conexion.setAutoCommit(false);
				psFicha = conexion.prepareStatement(sqlFicha, Statement.RETURN_GENERATED_KEYS);
				psFicha.setInt(1, codigoPersona);
				int resultRegistrarFicha = psFicha.executeUpdate();

				if (resultRegistrarFicha > 0) {
					rsFicha = psFicha.getGeneratedKeys();
					rsFicha.next();
					int codigoFicha = rsFicha.getInt(1);

					/*-************ REGISTRAR ESTADO DE FICHA **************-*/
					String sqlEstadoFicha = ""
						+ "insert into estado_ficha ( "
						+ "codigo_ficha "
						+ ",codigo_tipo_estado_ficha "
						+ ",fecha_registro "
						+ ",codigo_usuario "
						+ ",estado_registro "
						+ ") VALUES ( "
						+ "?, "
						+ "1, "
						+ "getdate(), "
						+ "?, "
						+ "1 "
						+ ")";

					conexion.setAutoCommit(false);
					psEstadoFicha = conexion.prepareStatement(sqlEstadoFicha);
					psEstadoFicha.setInt(1, codigoFicha);
					psEstadoFicha.setInt(2, ef.getCodigoUsuario());
					int resultRegistrarEstadoFicha = psEstadoFicha.executeUpdate();

					if (resultRegistrarEstadoFicha > 0) {
						/*-************ REGISTRAR TOKEN DE FICHA **************-*/
						String sqlTokenFicha = ""
							+ "insert into token_ficha "
							+ " ( "
							+ "codigo_ficha "
							+ ",codigo_verificacion "
							+ ",fecha_creacion "
							+ ",fecha_expiracion "
							+ ",token "
							+ ",codigo_sede_area"
							+ ",codigo_area_cargo"
							+ ",estado_registro "
							+ ") "
							+ "values ( "
							+ "? "
							+ ",? "
							+ ",getdate() "
							+ ",dateadd(dd, 1, getdate()) "
							+ ",lower(newid()) "
							+ ",?"
							+ ",?"
							+ ",1 "
							+ ")";

						String CodeVerification = GenerateCodeVerification.randomString(6); // almacenar el codigo de verificacion a enviar
						conexion.setAutoCommit(false);
						psTokenFicha = conexion.prepareStatement(sqlTokenFicha, Statement.RETURN_GENERATED_KEYS);
						psTokenFicha.setInt(1, codigoFicha);
						psTokenFicha.setString(2, CodeVerification);
						psTokenFicha.setInt(3, tf.getCodigoSedeArea());
						psTokenFicha.setInt(4, tf.getCodigoAreaCargo());
						int resultRegistrarTokenFicha = psTokenFicha.executeUpdate();

						if (resultRegistrarTokenFicha > 0) {
							rsTokenFicha = psTokenFicha.getGeneratedKeys();
							rsTokenFicha.next();
							int codigoTokenFicha = rsTokenFicha.getInt(1);

							/*-************ OBTENER TOKEN FICHA **************-*/
							String sqlGetToken = ""
								+ "select "
								+ "token "
								+ "from token_ficha "
								+ "where codigo_token_ficha = ? and estado_registro = 1";

							conexion.setAutoCommit(false);
							psObtenerToken = conexion.prepareStatement(sqlGetToken);
							psObtenerToken.setInt(1, codigoTokenFicha);
							rsObtenerToken = psObtenerToken.executeQuery();
							rsObtenerToken.next();
							String token = rsObtenerToken.getString("token");

							/*-************ ENVIAR CORREO ELECT. **************-*/
							Correo c = new Correo();
							c.setDestino(p.getCorreo());
							c.setAsunto("BIENVENIDO A TRISMEGISTO PLANILLA");

							String encriptarAccion = Encriptar.encriptar("validar-token-ficha", "TR1SM3G1ST0-PL4N1LL4-SO");
							String[] accionArr = Encriptar.reemplazar(encriptarAccion);

							String encriptarID = Encriptar.encriptar(String.valueOf(codigoTokenFicha), "TR1SM3G1ST0-ID-PL4N1LL4-SO");
							String[] idArr = Encriptar.reemplazar(encriptarID);

							c.setMensaje(EmailDesign.correoDesign(CodeVerification, "http://172.16.2.91:8080/TrismegistoPlanilla/TokenFichaServlet?accion=" + EncryptAction.Encriptar("validarTokenFicha", "TR1SM3G1ST0-PL4N1LL4") + "&id=" + EncryptAction.Encriptar(String.valueOf(codigoTokenFicha), "TR1SM3G1ST0-ID-PL4N1LL4") + "&token=" + token));
							CorreoSqlserverDAO correo = new CorreoSqlserverDAO();
							if (correo.enviarCorreo(c)) {
								response.setStatus(true);
								response.setMessage("La ficha se ha registrado con éxito. Hemos enviado un correo electrónico el cuál permitirá proceder con el registro, gracias.");
								conexion.commit();
							} else {
								response.setStatus(false);
								response.setMessage("Ha ocurrido un error, no se ha podido registrar la ficha. Por favor contactarse con el área de sistemas, gracias.");
								conexion.rollback();
							}
						} else {
							conexion.rollback();
						}
					} else {
						conexion.rollback();
					}
				} else {
					conexion.rollback();
				}
			} else {
				conexion.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			response.setStatus(false);
			response.setMessage("Error: " + e.getMessage() + " \n Error Code: [" + e.getErrorCode() + "]");
		} catch (Exception e) {
			e.printStackTrace();
			response.setStatus(false);
			response.setMessage("Error: " + e.getMessage());
		} finally {
			try {

				if (rsPersona != null) {
					rsPersona.close();
				}
				if (rsFicha != null) {
					rsFicha.close();
				}
				if (rsEstadoFicha != null) {
					rsEstadoFicha.close();
				}
				if (rsTokenFicha != null) {
					rsTokenFicha.close();
				}
				if (rsObtenerCodigoFicha != null) {
					rsObtenerCodigoFicha.close();
				}

				if (psPersona != null) {
					psPersona.close();
				}
				if (psFicha != null) {
					psFicha.close();
				}
				if (psEstadoFicha != null) {
					psEstadoFicha.close();
				}
				if (psTokenFicha != null) {
					psTokenFicha.close();
				}
				if (psObtenerCodigoFicha != null) {
					psObtenerCodigoFicha.close();
				}

				if (conexion != null) {
					conexion.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		jsonObjRegistrarPersona = new JSONObject(response);
		return jsonObjRegistrarPersona;
	}

	@Override
	public JSONObject verificarPersona(TokenFichaBean tf, PersonaBean p) {
		System.out.println("PersonaSqlserverDAO: verificarPersona");
		JSONObject jsonObjVerificarPersona = null;
		ResponseHelper response = new ResponseHelper();
		int verificacionPersona = 0;

		String sql = ""
			+ "select "
			+ "count(1) verificacionPersona "
			+ "from token_ficha "
			+ "inner join dbo.ficha ON dbo.ficha.codigo_ficha = dbo.token_ficha.codigo_ficha "
			+ "inner join dbo.persona ON dbo.persona.codigo_persona = dbo.ficha.codigo_persona "
			+ "where token_ficha.codigo_token_ficha = ? and "
			+ "token_ficha.token = ? and "
			+ "token_ficha.codigo_verificacion = ? and "
			+ "persona.numero_documento = ? and "
			+ "persona.estado_registro = 1 and "
			+ "ficha.estado_registro = 1 and "
			+ "token_ficha.estado_registro = 1";
		Connection conexion = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conexion = SqlserverDAOFactory.obtenerConexion(Variables.BD_NAME);
			ps = conexion.prepareStatement(sql);
			ps.setInt(1, tf.getCodigoTokenFicha());
			ps.setString(2, tf.getToken());
			ps.setString(3, tf.getCodigoVerificacion());
			ps.setString(4, p.getNumeroDocumento());
			rs = ps.executeQuery();
			rs.next();
			verificacionPersona = rs.getInt("verificacionPersona");

			if (verificacionPersona > 0) {
				response.setStatus(true);
				response.setMessage("Enhorabuena!.");
			} else {
				response.setStatus(false);
				response.setMessage("Error, las credenciales ingresadas son incorrectas, asegúrese de haber escrito bien e intentelo de nuevo.");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			response.setStatus(false);
			response.setMessage("Error: " + e.getMessage() + " \n Error Code: [" + e.getErrorCode() + "]");
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (conexion != null) {
					conexion.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		jsonObjVerificarPersona = new JSONObject(response);
		return jsonObjVerificarPersona;
	}

	@Override
	public JSONObject modificarCorreoFichaPersona(PersonaBean p) {
		System.out.println("PersonaSqlserverDAO: modificarCorreoFichaPersona");
		JSONObject jsonObjModificarCorreoFichaPersona = null;
		ResponseHelper response = new ResponseHelper();
		int actualizaCorreo = 0;

		String sql = ""
			+ "update persona "
			+ "set correo = ? "
			+ "where codigo_persona = ? and estado_registro = 1";

		Connection conexion = null;
		PreparedStatement psUpdatePersona = null;
		ResultSet rsUpdatePersona = null;
		try {
			conexion = SqlserverDAOFactory.obtenerConexion(Variables.BD_NAME);
			conexion.setAutoCommit(false);
			psUpdatePersona = conexion.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			psUpdatePersona.setString(1, p.getCorreo());
			psUpdatePersona.setInt(2, p.getCodigoPersona());
			actualizaCorreo = psUpdatePersona.executeUpdate();
			if (actualizaCorreo > 0) {
				response.setStatus(true);
				response.setMessage("Enhorabuena!, los datos fueron actualizados con éxito.");
				conexion.commit();
			} else {
				response.setStatus(false);
				response.setMessage("Error, no se pudo actualizar el correo.");
				conexion.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			response.setStatus(false);
			response.setMessage("Error: " + e.getMessage() + " \n Error Code: [" + e.getErrorCode() + "]");
		} finally {
			try {
				if (rsUpdatePersona != null) {
					rsUpdatePersona.close();
				}
				if (psUpdatePersona != null) {
					psUpdatePersona.close();
				}
				if (conexion != null) {
					conexion.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		jsonObjModificarCorreoFichaPersona = new JSONObject(response);
		return jsonObjModificarCorreoFichaPersona;
	}

	@Override
	public JSONObject obtenerCodigoPersona(TokenFichaBean tf) {
		System.out.println("PersonaSqlserverDAO: obtenerCodigoPersona");
		JSONObject jsonObjObtenerCodigoPersona = null;
		ResponseHelper response = new ResponseHelper();

		String sql = ""
			+ "select "
			+ "persona.codigo_persona codigoPersona "
			+ "from persona "
			+ "inner join dbo.ficha ON dbo.ficha.codigo_persona = dbo.persona.codigo_persona "
			+ "inner join dbo.token_ficha ON dbo.token_ficha.codigo_ficha = dbo.ficha.codigo_ficha "
			+ "where token_ficha.codigo_token_ficha = ? and token_ficha.token = ? and "
			+ "persona.estado_registro = 1 and "
			+ "ficha.estado_registro = 1 and "
			+ "token_ficha.estado_registro = 1";

		Connection conexion = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conexion = SqlserverDAOFactory.obtenerConexion(Variables.BD_NAME);
			ps = conexion.prepareStatement(sql);
			ps.setInt(1, tf.getCodigoTokenFicha());
			ps.setString(2, tf.getToken());
			rs = ps.executeQuery();
			rs.next();
			int codigoPersona = rs.getInt("codigoPersona");
			JSONObject objPersona = new JSONObject();
			objPersona.put("getResultedKey", codigoPersona);
			if (codigoPersona > 0) {
				response.setStatus(true);
				response.setMessage("Enhorabuena!, persona identificada");
				response.setData(objPersona);
			} else {
				response.setStatus(false);
				response.setMessage("Error: no se encontró la identificación de la persona");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			response.setStatus(false);
			response.setMessage("Error: " + e.getMessage() + " \n Error Code: [" + e.getErrorCode() + "]");
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (conexion != null) {
					conexion.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		jsonObjObtenerCodigoPersona = new JSONObject(response);
		return jsonObjObtenerCodigoPersona;
	}

	@Override
	public JSONObject obtenerPersonaSession(PersonaBean p) {
		System.out.println("PersonaSqlserverDAO: obtenerPersonaSession");
		JSONObject jsonObjObtenerPersonaSession = null;
		JSONArray jsonArrObtenerPersonaSession = new JSONArray();
		JSONObject objPersona = null;
		ResponseHelper response = new ResponseHelper();

		String sql = ""
			+ "select "
			+ "persona.codigo_persona codigoPersona, "
			+ "tipo_documento.codigo_tipo_documento codigoTipoDocumento, "
			+ "tipo_documento.descripcion_corta nombreTipoDocumento, "
			+ "persona.numero_documento numeroDocumento, "
			+ "persona.apellido_paterno apellidoPaterno, "
			+ "persona.apellido_materno apellidoMaterno, "
			+ "persona.nombre nombre, "
			+ "persona.correo "
			+ "from persona "
			+ "inner join dbo.tipo_documento ON dbo.tipo_documento.codigo_tipo_documento = dbo.persona.codigo_tipo_documento "
			+ "where persona.codigo_persona = ? and "
			+ "persona.estado_registro = 1  and "
			+ "tipo_documento.codigo_tipo_documento = ? and "
			+ "tipo_documento.estado_registro = 1";

		Connection conexion = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conexion = SqlserverDAOFactory.obtenerConexion(Variables.BD_NAME);
			ps = conexion.prepareStatement(sql);
			ps.setInt(1, p.getCodigoPersona());
			ps.setInt(2, p.getCodigoTipoDocumento());
			rs = ps.executeQuery();
			while (rs.next()) {
				p = new PersonaBean();
				p.setCodigoPersona(rs.getInt("codigoPersona"));
				p.setCodigoTipoDocumento(rs.getInt("codigoTipoDocumento"));
				p.setNombreTipoDocumento(rs.getString("nombreTipoDocumento"));
				p.setNumeroDocumento(rs.getString("numeroDocumento"));
				p.setApellidoPaterno(rs.getString("apellidoPaterno"));
				p.setApellidoMaterno(rs.getString("apellidoMaterno"));
				p.setNombre(rs.getString("nombre"));
				p.setCorreo(rs.getString("correo"));
				JSONObject jsonobjPersona = new JSONObject(p);
				jsonArrObtenerPersonaSession.put(jsonobjPersona);
			}
			objPersona = new JSONObject();
			objPersona.put("personas", jsonArrObtenerPersonaSession);
			response.setStatus(true);
			response.setMessage("Enhorabuena!, los datos de la persona se han listado correctamente");
			response.setData(objPersona);
		} catch (SQLException e) {
			e.printStackTrace();
			response.setStatus(false);
			response.setMessage("Error: " + e.getMessage() + " \n Error Code: [" + e.getErrorCode() + "]");
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (conexion != null) {
					conexion.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		jsonObjObtenerPersonaSession = new JSONObject(response);
		return jsonObjObtenerPersonaSession;
	}

}