/**
 * 
 */
package com.ejie.ad74a.dao.iml;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.stereotype.Repository;

import com.ejie.ad74a.dao.CopagoAytoDao;
import com.ejie.ad74a.model.Ayuntamiento;
import com.ejie.ad74a.model.FacturacionAyuntamiento;
import com.ejie.ad74a.model.PisoTutelado;
import com.ejie.ad74a.model.PrecioPublico;
import com.ejie.ad74a.model.Solicitante;

/**
 * Implementacion de capa de persistencia para el procesamiento y emision de facturas a ayuntamientos
 * @author BICUGUAL
 *
 */
@Repository
public class CopagoAytoDaoImpl implements CopagoAytoDao {

	@Autowired
	DefaultLobHandler lobhandler;

	JdbcTemplate jdbcTemplate;
	
	public static final String T_AYUNTAMIENTOS = "AD74A.AD74A31T00";
	public static final String T_FACTURACION_AYUNTAMIENTOS = "AD74A.AD74A90T00";
	public static final String T_PROVINCIA = "AD74A.T17_PROVINCIA";
	public static final String T_MUNICIPIO = "AD74A.T17_MUNICIPIO";
	public static final String T_PISOS_TUTELADOS = "AD74A.AD74A32T00";
	public static final String T_PISOS_TUTE_SOLI = "AD74A.AD74A32T01";
	public static final String T_SOLICITANTES = "AD74A.AD74A01T00";
	public static final String T_PRECIOS_PUBLICOS = "AD74A.AD74A77T00";
	public static final String T_PRECIOS_BENEFICIARIO = "AD74A.AD74A91T00";
	public static final String SEQUENCE_FACTURACION_AYTOS = "SEQ_FACTURACION_AYUNTAMIENTOS"; 
	
	 
	@Resource
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	@Override
	public List<Ayuntamiento> findAyuntamientosConPisosTutelados() {
		
		String sql = "SELECT a.ID_AYUNTAMIENTO_31, "
				+ "a.DESC_ES_31, "
				+ "a.DESC_EU_31, "
				+ "a.DIRECCION_31, "
				+ "a.CP_31, "
				+ "a.ID_MUNICIPIO_31, "
				+ "m.DS_O AS MUNICIPIO, "
				+ "a.ID_PROVINCIA_31, "
				+ "p.DS_O AS PROVINCIA, "
				+ "a.CIF_31, "
				+ "a.TELEFONO_31, "
				+ "a.NUM_CUENTA_31, "
				+ "a.PERIODICIDAD_31, "
				+ "a.DIA_FACTURACION_31, "
				+ "a.FORMA_PAGO_31,"
				+ "a.F_AUTORIZACION_31, "
				+ "fa.MES_HASTA_90, "
				+ "fa.ANYO_HASTA_90 "
			+ " FROM " + T_AYUNTAMIENTOS + " a " 
				+ "LEFT JOIN " + T_FACTURACION_AYUNTAMIENTOS + " fa ON a.ID_AYUNTAMIENTO_31 = fa.ID_AYUNTAMIENTO_90, "
				+ T_PROVINCIA + " p, " + T_MUNICIPIO + "  m "
			+ "WHERE EXISTS (SELECT 1 FROM " + T_PISOS_TUTELADOS + " pt WHERE a.ID_AYUNTAMIENTO_31 = pt.ID_AYUNTAMIENTO_32) "
				+ "AND a.ID_PROVINCIA_31 = p.ID "
				+ "AND a.ID_MUNICIPIO_31 = m.ID "
				+ "AND a.ID_PROVINCIA_31 = m.PROVINCIA_ID "
				+ "AND a.PERIODICIDAD_31 IS NOT NULL "
				+ "AND a.DIA_FACTURACION_31 IS NOT NULL "
				+ "AND (fa.ID_FACTURACION_90 = (SELECT max(fa2.ID_FACTURACION_90) FROM " + T_FACTURACION_AYUNTAMIENTOS	+ " fa2 WHERE fa.ID_AYUNTAMIENTO_90 = fa2.ID_AYUNTAMIENTO_90) OR fa.ID_FACTURACION_90 IS NULL) "
				+ "ORDER BY ID_AYUNTAMIENTO_31";
		
		return jdbcTemplate.query(sql, new AyuntamientoMapper());
	}
	
	@Override
	public Ayuntamiento findAyuntamientoByIdFacturacion(Long idFacturacion) {
		
		String sql = "SELECT a.ID_AYUNTAMIENTO_31, "
				+ "a.DESC_ES_31, "
				+ "a.DESC_EU_31, "
				+ "a.DIRECCION_31, "
				+ "a.CP_31, "
				+ "a.ID_MUNICIPIO_31, "
				+ "m.DS_O AS MUNICIPIO, "
				+ "a.ID_PROVINCIA_31, "
				+ "p.DS_O AS PROVINCIA, "
				+ "a.CIF_31, "
				+ "a.TELEFONO_31, "
				+ "a.NUM_CUENTA_31, "
				+ "a.PERIODICIDAD_31, "
				+ "a.DIA_FACTURACION_31, "
				+ "a.FORMA_PAGO_31,"
				+ "a.F_AUTORIZACION_31, "
				+ "fa.MES_HASTA_90, "
				+ "fa.ANYO_HASTA_90 "
			+ " FROM " + T_AYUNTAMIENTOS + " a " 
				+ "LEFT JOIN " + T_FACTURACION_AYUNTAMIENTOS + " fa ON a.ID_AYUNTAMIENTO_31 = fa.ID_AYUNTAMIENTO_90, "
				+ T_PROVINCIA + " p, " + T_MUNICIPIO + "  m "
			+ "WHERE fa.ID_FACTURACION_90 = ? "
				+ "AND a.ID_PROVINCIA_31 = p.ID "
				+ "AND a.ID_MUNICIPIO_31 = m.ID "
				+ "AND a.ID_PROVINCIA_31 = m.PROVINCIA_ID "
				+ "AND a.PERIODICIDAD_31 IS NOT NULL "
				+ "AND a.DIA_FACTURACION_31 IS NOT NULL "
				+ "ORDER BY ID_AYUNTAMIENTO_31";
		
		return jdbcTemplate.queryForObject(sql, new Object[] { idFacturacion }, new AyuntamientoMapper());
	}
	
	
	@Override
	public List<PisoTutelado> findPisosTuteladosByIdAyuntamientoAndMensualidad(Long idAyuntamiento,
			Date fInicioMensualidad, Date fFinMensualidad) {
		
		String sql = "SELECT DISTINCT pt.ID_PISO_32, "
				+ "pt.DENOMINACION_32 , "
				+ "null as F_DESDE_32, "
				+ "null as F_HASTA_32 "
			+ "FROM " + T_PISOS_TUTELADOS + " pt, " 
				+ T_AYUNTAMIENTOS + " a, "
				+ T_PISOS_TUTE_SOLI + " ps "
			+ "WHERE pt.ID_AYUNTAMIENTO_32 = a.ID_AYUNTAMIENTO_31 "
				+ "AND pt.ID_PISO_32 = ps.ID_PISO_32 "
				+ "AND a.ID_AYUNTAMIENTO_31 = :idAyuntamiento "
				+ "AND ps.F_DESDE_32 <= :fFinMensualidad "
				+ "AND (ps.F_HASTA_32 IS NULL OR ps.F_HASTA_32 > :fInicioMensualidad) "
			+ "ORDER BY pt.ID_PISO_32";
		
		MapSqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("idAyuntamiento", idAyuntamiento)
				.addValue("fInicioMensualidad", fInicioMensualidad)
				.addValue("fFinMensualidad", fFinMensualidad);

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
		return namedParameterJdbcTemplate.query(sql, parameters, new PisoTuteladoMapper());
	}
	

	
	@Override
	public List<Solicitante> findSolicitantesByIdPisoTuteladoAndMensualidad(Long idPisoTutelado,
			Date fInicioMensualidad, Date fFinMensualidad) {
		
		String sql = "SELECT ps.ID_PISO_32, "
				+ "s.ID_SOLICITANTE_01, "
				+ "s.F_INICIO_01, "
				+ "s.F_BAJA_01, "
				+ "s.NOMBRE_01, "
				+ "s.APELLIDO1_01, "
				+ "s.APELLIDO2_01, "
				+ "s.IDENTIFICADOR_01, "
				+ "(SELECT PRECIO_PUBLICO_77 FROM " + T_PRECIOS_PUBLICOS + " WHERE F_VIGENCIA_DESDE_77 <= :fFinMensualidad AND (F_VIGENCIA_HASTA_77  IS NULL OR F_VIGENCIA_HASTA_77 > :fInicioMensualidad) ) AS PRECIO_PUBLICO_77, "
				+ "(SELECT count(1) FROM " + T_PRECIOS_BENEFICIARIO + " pb WHERE pb.ID_SOLICITANTE_91 = s.ID_SOLICITANTE_01) AS NPRECIOS_BENEFICIARIO "
			+ "FROM " + T_SOLICITANTES + " s, " + T_PISOS_TUTE_SOLI + " ps "
			+ "WHERE s.ID_SOLICITANTE_01 = ps.ID_SOLICITANTE_32 "
				+ "AND ps.ID_PISO_32 = :idPisoTutelado "
				+ "AND ps.F_DESDE_32 <= :fFinMensualidad "
				+ "AND (ps.F_HASTA_32 IS NULL OR ps.F_HASTA_32 > :fInicioMensualidad)"
				+ "AND s.F_ALTA_01 <= :fFinMensualidad "
				+ "AND (s.F_BAJA_01 IS NULL OR s.F_BAJA_01 > :fInicioMensualidad) "
				+ "AND s.F_INICIO_01 IS NOT NULL";

		MapSqlParameterSource parameters = new MapSqlParameterSource().addValue("idPisoTutelado", idPisoTutelado)
				.addValue("fInicioMensualidad", fInicioMensualidad)
				.addValue("fFinMensualidad", fFinMensualidad);
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
		return namedParameterJdbcTemplate.query(sql, parameters, new SolicitanteMapper());
	}
	
	@Override
	public Long createFacturacionAyuntamiento(FacturacionAyuntamiento factura) {
		GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
		
		String query = "INSERT INTO " + T_FACTURACION_AYUNTAMIENTOS +" (ID_FACTURACION_90, ID_AYUNTAMIENTO_90, ANYO_DESDE_90, MES_DESDE_90, "
				+ " ANYO_HASTA_90, MES_HASTA_90, F_EMISION_90, IMPORTE_FACTURADO_90, PROCESADO_90, REFERENCIA_RECIBO_90) "
				+ "	VALUES(" + SEQUENCE_FACTURACION_AYTOS + ".NEXTVAL, :idAyuntamiento, :anyoDesde, :mesDesde, :anyoHasta, :mesHasta, "
				+ " CURRENT_DATE, :importeFacturado, 1, :referenciaRecibo)";
		
		MapSqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("idAyuntamiento", factura.getIdAyuntamiento())
				.addValue("anyoDesde", factura.getAnyoDesde())
				.addValue("mesDesde", factura.getMesDesde())
				.addValue("anyoHasta", factura.getAnyoHasta())
				.addValue("mesHasta", factura.getMesHasta())
				.addValue("importeFacturado", factura.getImporteFacturado())
				.addValue("referenciaRecibo", "SIN CALCULAR");
		
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
		namedParameterJdbcTemplate.update(query, parameters, generatedKeyHolder, new String[] { "ID_FACTURACION_90" });
		
		return generatedKeyHolder.getKey().longValue();
	}
	
	@Override
	public FacturacionAyuntamiento findFacturacionAyuntamientoById(Long idFactura) {
		
		String sql = "SELECT ID_FACTURACION_90, ID_AYUNTAMIENTO_90, ANYO_DESDE_90, MES_DESDE_90, "
				+ "ANYO_HASTA_90, MES_HASTA_90, F_EMISION_90, IMPORTE_FACTURADO_90, PROCESADO_90, REFERENCIA_RECIBO_90 "
				+ "FROM "+ T_FACTURACION_AYUNTAMIENTOS + " fa "
				+ "WHERE ID_FACTURACION_90=?"; 
		return jdbcTemplate.queryForObject(sql, new FacturacionAyuntamientoMapper(), idFactura);
	}

	@Override
	public void saveInformeFacturacion(Long idFactura, byte[] informeArrBytes) throws IOException {

		final InputStream blobIs = new ByteArrayInputStream(informeArrBytes);
		
		jdbcTemplate.execute(
				"UPDATE " + T_FACTURACION_AYUNTAMIENTOS + " SET INFORME_90 = ? WHERE ID_FACTURACION_90 = ?",
				new AbstractLobCreatingPreparedStatementCallback(lobhandler) {
					protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
						lobCreator.setBlobAsBinaryStream(ps, 1, blobIs, informeArrBytes.length);
						ps.setLong(2, idFactura);
					}
				});
		blobIs.close();

	}

	
	/**
	 * Mapper para ayuntamientos
	 * @author BICUGUAL
	 *
	 */
	private class AyuntamientoMapper implements RowMapper<Ayuntamiento> {
		
		public Ayuntamiento mapRow(ResultSet rs, int rowNum) throws SQLException {
			
			Ayuntamiento item = new Ayuntamiento();
			item.setIdAyuntamiento(rs.getLong("ID_AYUNTAMIENTO_31"));
			item.setDescEs(rs.getString("DESC_ES_31"));
			item.setDescEu(rs.getString("DESC_EU_31"));
			item.setDireccion(rs.getString("DIRECCION_31"));
			item.setCp(rs.getString("CP_31"));
			item.setIdMunicipio(rs.getString("ID_MUNICIPIO_31"));
			item.setMunicipio(rs.getString("MUNICIPIO"));
			item.setIdProvincia(rs.getString("ID_PROVINCIA_31"));
			item.setProvincia(rs.getString("PROVINCIA"));
			item.setCif(rs.getString("CIF_31"));
			item.setTelefono(rs.getString("TELEFONO_31"));
			item.setNumCuenta(rs.getString("NUM_CUENTA_31"));
			item.setfAutorizacion(rs.getDate("F_AUTORIZACION_31"));
			
			Integer periodicidad = rs.getInt("PERIODICIDAD_31");
			item.setPeriodicidad(!rs.wasNull() ? periodicidad : null);
			Integer diaFacturacion = rs.getInt("DIA_FACTURACION_31");
			item.setDiaFacturacion(!rs.wasNull() ? diaFacturacion : null);
			Integer formaPago = rs.getInt("FORMA_PAGO_31");
			item.setFormaPago(!rs.wasNull() ? formaPago : null);
			Integer mesHasta = rs.getInt("MES_HASTA_90");
			item.setMesHastaUltimaFacturacion(!rs.wasNull() ? mesHasta : null);
			Integer anyoHasta = rs.getInt("ANYO_HASTA_90");
			item.setAnyoHastaUltimaFacturacion(!rs.wasNull() ? anyoHasta : null);
			
			return item;
		}
	}
	
	/**
	 * Mapper para pisos tutelados
	 * @author BICUGUAL
	 *
	 */
	private class PisoTuteladoMapper implements RowMapper<PisoTutelado> {
		
		public PisoTutelado mapRow(ResultSet rs, int rowNum) throws SQLException {
			
			PisoTutelado item = new PisoTutelado();
			item.setIdPisoTutelado(rs.getLong("ID_PISO_32"));
			item.setDenominacion(rs.getString("DENOMINACION_32"));
			item.setfDesde(null != rs.getDate("F_DESDE_32") ? rs.getDate("F_DESDE_32").toLocalDate() : null);
			item.setfHasta(null != rs.getDate("F_HASTA_32") ? rs.getDate("F_HASTA_32").toLocalDate() : null);
			
			return item;
		}
	}
	
	/**
	 * Mapper para pisos tutelados
	 * @author BICUGUAL
	 *
	 */
	private class SolicitanteMapper implements RowMapper<Solicitante> {
		
		public Solicitante mapRow(ResultSet rs, int rowNum) throws SQLException {
			
			Solicitante item = new Solicitante();

			item.setIdSolicitante(rs.getLong("ID_SOLICITANTE_01"));
			item.setNombreTitCuenta(rs.getString("NOMBRE_01"));
			item.setApellido1TitCuenta(rs.getString("APELLIDO1_01"));
			item.setApellido2TitCuenta(rs.getString("APELLIDO2_01"));
			item.setIdenTitular(rs.getString("IDENTIFICADOR_01"));
			item.setnPreciosBeneficiario(rs.getInt("NPRECIOS_BENEFICIARIO"));
			item.setfInicio(null != rs.getDate("F_INICIO_01") ? rs.getDate("F_INICIO_01").toLocalDate() : null);

			//El dia de la baja no cuenta como activo por eso resto un dia a la fecha
			//item.setfBaja(null != rs.getDate("F_BAJA_01") ? rs.getDate("F_BAJA_01").toLocalDate().minusDays(1) : null);
			item.setfBaja(null != rs.getDate("F_BAJA_01") ? rs.getDate("F_BAJA_01").toLocalDate() : null);
			PrecioPublico precio = new PrecioPublico();
			precio.setImporte(rs.getBigDecimal("PRECIO_PUBLICO_77"));
			item.setPrecioPublico(precio);
			
			return item;
		}
	}


	/**
	 * Mapper para entidad Facturacion Ayuntamiento
	 * @author BICUGUAL
	 *
	 */
	private class FacturacionAyuntamientoMapper implements RowMapper<FacturacionAyuntamiento> {
		
		public FacturacionAyuntamiento mapRow(ResultSet rs, int rowNum) throws SQLException {
			
			FacturacionAyuntamiento item = new FacturacionAyuntamiento();

			item.setIdFacturacion(rs.getLong("ID_FACTURACION_90"));
			item.setIdAyuntamiento(rs.getLong("ID_AYUNTAMIENTO_90"));
			item.setAnyoDesde(rs.getInt("ANYO_DESDE_90"));
			item.setMesDesde(rs.getInt("MES_DESDE_90"));
			item.setAnyoHasta(rs.getInt("ANYO_HASTA_90"));
			item.setMesHasta(rs.getInt("MES_HASTA_90"));
			item.setfEmision(rs.getDate("F_EMISION_90"));
			item.setImporteFacturado(rs.getBigDecimal("IMPORTE_FACTURADO_90"));
			item.setProcesado(rs.getInt("PROCESADO_90"));
			item.setReferenciaRecibo(rs.getString("REFERENCIA_RECIBO_90"));
			
			return item;
		}
	}
}
