package com.ejie.y41e.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import org.apache.commons.lang.StringUtils;
import org.springframework.context.i18n.LocaleContextHolder;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.ejie.x38.security.UserCredentials;
import com.ejie.y41e.common.Y41eConstants;
import com.ejie.y41e.model.Y41eBloque;
import com.ejie.y41e.utils.Y41eSqlUtils;

/**
 * Y41eBloqueDaoImpl
 *  
 */

@Repository
@Transactional
public class Y41eBloqueDaoImpl extends Y41eGenericoDaoImpl<Y41eBloque> implements Y41eBloqueDao {

    public static final String TABLA="Y41E02S01";
    public static final String SECUENCIA="Y41E02Q00";
    public static final String ID_BLOQUE_002 ="ID_BLOQUE_002";
    public static final String DESC_PUBL_ES_002 ="DESC_PUBL_ES_002";
    public static final String DESC_PUBL_EU_002 ="DESC_PUBL_EU_002";
    public static final String DESC_INT_ES_002 ="DESC_INT_ES_002";
    public static final String DESC_INT_EU_002 ="DESC_INT_EU_002";
    public static final String ACTIVA_002 ="ACTIVA_002";
    public static final String OBLIGATORIO_002 ="OBLIGATORIO_002";
    public static final String FEC_ALTA_002 ="FEC_ALTA_002";
    public static final String FEC_ULTMODIF_002 ="FEC_ULTMODIF_002";
    public static final String USU_ALTA_002 ="USU_ALTA_002";
    public static final String USU_MODIF_002 ="USU_MODIF_002";
    protected static final String[] ORDER_BY_WHITE_LIST = new String[] { ID_BLOQUE_002, DESC_PUBL_ES_002, DESC_PUBL_EU_002, DESC_INT_ES_002,
            DESC_INT_EU_002, ACTIVA_002, OBLIGATORIO_002, FEC_ALTA_002, FEC_ULTMODIF_002, USU_ALTA_002, USU_MODIF_002};

    /**
     * Y41eBloqueDaoImpl
     */
    public Y41eBloqueDaoImpl() {
        super(Y41eBloque.class);
    }

    /*
     * ROW_MAPPERS
     */
    private final RowMapper<Y41eBloque> rwMap = new RowMapper<Y41eBloque>() {
        @Override
        public Y41eBloque mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            Y41eBloque bloque = new Y41eBloque();
            bloque.setIdBloque(resultSet.getLong(ID_BLOQUE_002));
            bloque.setDescPublEs(resultSet.getString(DESC_PUBL_ES_002));
            bloque.setDescPublEu(resultSet.getString(DESC_PUBL_EU_002));
            bloque.setDescIntEs(resultSet.getString(DESC_INT_ES_002));
            bloque.setDescIntEu(resultSet.getString(DESC_INT_EU_002));
            bloque.setActiva(resultSet.getString(ACTIVA_002));
            bloque.setObligatorio(resultSet.getString(OBLIGATORIO_002));
            bloque.setFecAlta(resultSet.getDate(FEC_ALTA_002));
            bloque.setFecUltModif(resultSet.getDate(FEC_ULTMODIF_002));
            bloque.setUsuAlta(resultSet.getString(USU_ALTA_002));
            bloque.setUsuModif(resultSet.getString(USU_MODIF_002));
            return bloque;
        }
    };

    private final RowMapper<Y41eBloque> rwMapPK = new RowMapper<Y41eBloque>() {
        @Override
        public Y41eBloque mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            return new Y41eBloque(resultSet.getLong(ID_BLOQUE_002)); 
        }
    };

    /*
     * OPERACIONES CRUD
     */

    /**
     * Inserts a single row in the Y41eBloque table.
     *
     * @param bloque Y41eBloque
     * @return Y41eBloque
     */
    @Override
    public Y41eBloque add(Y41eBloque bloque) {

        bloque.setIdBloque(this.getNextVal(SECUENCIA));

        StringBuilder insert = new StringBuilder(Y41eSqlUtils.INSERT)
                .append(TABLA).append(" (")
                .append(ID_BLOQUE_002).append(",")
                .append(DESC_PUBL_ES_002).append(",")
                .append(DESC_PUBL_EU_002).append(",")
                .append(DESC_INT_ES_002).append(",")
                .append(DESC_INT_EU_002).append(",")
                .append(ACTIVA_002).append(",")
                .append(OBLIGATORIO_002).append(",")
                .append(FEC_ALTA_002).append(",")
                .append(FEC_ULTMODIF_002).append(",")
                .append(USU_ALTA_002).append(",")
                .append(USU_MODIF_002).append(")")
                .append("VALUES (?,")
                .append(Y41eSqlUtils.SUBSTR_250).append(",")
                .append(Y41eSqlUtils.SUBSTR_250).append(",")
                .append(Y41eSqlUtils.SUBSTR_250).append(",")
                .append(Y41eSqlUtils.SUBSTR_250).append(",")
                .append("?,?, SYSDATE, SYSDATE,?,?)");

        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
        final UserCredentials  credentials = (UserCredentials) authentication.getCredentials();

        this.getJdbcTemplate().update(insert.toString(), bloque.getIdBloque(), bloque.getDescPublEs(), bloque.getDescPublEu(), bloque.getDescIntEs(), bloque.getDescIntEu(),
                bloque.getActiva(), StringUtils.isBlank(bloque.getObligatorio())?Y41eConstants.NO:bloque.getObligatorio(), credentials.getNif(),credentials.getNif());

        return bloque;
    }

    /**
     * Updates a single row in the Y41eBloque table.
     *
     * @param bloque Y41eBloque
     * @return Y41eBloque
     */
    @Override
    public Y41eBloque update(Y41eBloque bloque) {
        StringBuilder update = new StringBuilder(Y41eSqlUtils.UPDATE)
                .append(TABLA).append(" SET ")
                .append(DESC_PUBL_ES_002).append("=").append(Y41eSqlUtils.SUBSTR_250).append(",")
                .append(DESC_PUBL_EU_002).append("=").append(Y41eSqlUtils.SUBSTR_250).append(",")
                .append(DESC_INT_ES_002).append("=").append(Y41eSqlUtils.SUBSTR_250).append(",")
                .append(DESC_INT_EU_002).append("=").append(Y41eSqlUtils.SUBSTR_250).append(",")
                .append(ACTIVA_002).append("=?,")
                .append(OBLIGATORIO_002).append("=?,")
                .append(FEC_ULTMODIF_002).append("= SYSDATE,")
                .append(USU_MODIF_002).append("=?")
                .append(Y41eSqlUtils.WHERE)
                .append(ID_BLOQUE_002).append("=?");

        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
        final UserCredentials  credentials = (UserCredentials) authentication.getCredentials();

        this.getJdbcTemplate().update(update.toString(), bloque.getDescPublEs(), bloque.getDescPublEu(), bloque.getDescIntEs(), bloque.getDescIntEu(),
                bloque.getActiva(), StringUtils.isBlank(bloque.getObligatorio())?Y41eConstants.NO:bloque.getObligatorio(), 
                        credentials.getNif(), bloque.getIdBloque());

        return bloque;
    }

    /**
     * Removes a single row in the Y41eBloque table.
     *
     * @param bloque Y41eBloque
     * @return
     */
    @Override
    public void remove(Y41eBloque bloque) {
        StringBuilder delete = new StringBuilder(Y41eSqlUtils.DELETE).append(TABLA).append(Y41eSqlUtils.WHERE).append(ID_BLOQUE_002).append("=?");
        this.getJdbcTemplate().update(delete.toString(), bloque.getIdBloque());
    }

    /* (non-Javadoc)
     * @see com.ejie.y41e.dao.Y41eGenericoDaoImpl#getSelect()
     */
    @Override
    protected String getSelect() {
        StringBuilder select = new StringBuilder("SELECT ")
                .append(ID_BLOQUE_002).append(",")
                .append(DESC_PUBL_ES_002).append(",")
                .append(DESC_PUBL_EU_002).append(",")
                .append(DESC_INT_ES_002).append(",")
                .append(DESC_INT_EU_002).append(",")
                .append(ACTIVA_002).append(",")
                .append(OBLIGATORIO_002).append(",")
                .append(FEC_ALTA_002).append(",")
                .append(FEC_ULTMODIF_002).append(",")
                .append(USU_ALTA_002).append(",")
                .append(USU_MODIF_002);
        return select.toString();
    }

    /* (non-Javadoc)
     * @see com.ejie.y41e.dao.Y41eGenericoDaoImpl#getFrom()
     */
    @Override
    protected String getFrom() {
        StringBuilder from = new StringBuilder(" FROM ").append(TABLA);
        return from.toString();
    }

    /* (non-Javadoc)
     * @see com.ejie.y41e.dao.Y41eGenericoDaoImpl#getRwMap()
     */
    @Override
    protected RowMapper<Y41eBloque> getRwMap() {
        return this.rwMap;
    }

    /* (non-Javadoc)
     * @see com.ejie.y41e.dao.Y41eGenericoDaoImpl#getPK()
     */
    @Override
    protected String getPK() {
        return ID_BLOQUE_002;
    }

    /* (non-Javadoc)
     * @see com.ejie.y41e.dao.Y41eGenericoDaoImpl#getRwMapPK()
     */
    @Override
    protected RowMapper<Y41eBloque> getRwMapPK() {
        return this.rwMapPK;
    }

    /* (non-Javadoc)
     * @see com.ejie.y41e.dao.Y41eGenericoDaoImpl#getWherePK(java.lang.Object, java.util.List)
     */
    @Override
    protected String getWherePK(Y41eBloque bean, List<Object> params) {
        StringBuilder wherePK = new StringBuilder();
        wherePK.append(Y41eSqlUtils.WHERE).append(ID_BLOQUE_002).append("=?");
        params.add(bean.getIdBloque());
        return wherePK.toString();

    }

    /* (non-Javadoc)
     * @see com.ejie.y41e.dao.Y41eGenericoDaoImpl#getWhere(java.lang.Object, java.util.List)
     */
    @Override
    protected String getWhere(Y41eBloque bean, List<Object> params) {
        StringBuilder where = new StringBuilder(Y41eBloqueDaoImpl.STRING_BUILDER_INIT);

        if (bean  != null){
            where.append(Y41eSqlUtils.generarWhereIgual(ID_BLOQUE_002, bean.getIdBloque(), params));
            where.append(Y41eSqlUtils.generarWhereIgual(DESC_PUBL_ES_002, bean.getDescPublEs(), params));
            where.append(Y41eSqlUtils.generarWhereIgual(DESC_PUBL_EU_002, bean.getDescPublEu(), params));
            where.append(Y41eSqlUtils.generarWhereIgual(DESC_INT_ES_002, bean.getDescIntEs(), params));
            where.append(Y41eSqlUtils.generarWhereIgual(DESC_INT_EU_002, bean.getDescPublEu(), params));
            where.append(Y41eSqlUtils.generarWhereIgual(ACTIVA_002, bean.getActiva(), params));
            where.append(Y41eSqlUtils.generarWhereIgual(OBLIGATORIO_002, bean.getObligatorio(), params));
            where.append(Y41eSqlUtils.generarWhereIgual(FEC_ALTA_002, bean.getFecAlta(), params));
            where.append(Y41eSqlUtils.generarWhereIgual(FEC_ULTMODIF_002, bean.getFecUltModif(), params));
        }

        return where.toString();
    }

    /* (non-Javadoc)
     * @see com.ejie.y41e.dao.Y41eGenericoDaoImpl#getWhereLike(java.lang.Object, java.lang.Boolean, java.util.List, java.lang.Boolean)
     */
    @Override
    protected String getWhereLike(Y41eBloque bean, Boolean startsWith, List<Object> params, Boolean search) {
        StringBuilder where = new StringBuilder(Y41eBloqueDaoImpl.STRING_BUILDER_INIT);
        Locale locale = LocaleContextHolder.getLocale();
        if (bean  != null){
            where.append(Y41eSqlUtils.generarWhereIgual(ID_BLOQUE_002, bean.getIdBloque(), params));
            where.append(Y41eSqlUtils.generarWhereLikeNormalizado(DESC_PUBL_ES_002, bean.getDescPublEs(), params, startsWith));
            where.append(Y41eSqlUtils.generarWhereLikeNormalizado(DESC_PUBL_EU_002, bean.getDescPublEu(), params, startsWith));
            where.append(Y41eSqlUtils.generarWhereLikeNormalizado(DESC_INT_ES_002, bean.getDescIntEs(), params, startsWith));
            where.append(Y41eSqlUtils.generarWhereLikeNormalizado(DESC_INT_EU_002, bean.getDescPublEu(), params, startsWith));
            where.append(Y41eSqlUtils.generarWhereLikeNormalizado("DESC_PUBL_"+ locale.getLanguage().toUpperCase() +"_002", bean.getDescPubl(), params, startsWith));
            where.append(Y41eSqlUtils.generarWhereLikeNormalizado("DESC_INT_"+ locale.getLanguage().toUpperCase() +"_002", bean.getDescInt(), params, startsWith));
            where.append(Y41eSqlUtils.generarWhereIgual(ACTIVA_002, bean.getActiva(), params));
            where.append(Y41eSqlUtils.generarWhereIgual(OBLIGATORIO_002, bean.getObligatorio(), params));
            where.append(Y41eSqlUtils.generarWhereIgual(FEC_ALTA_002, bean.getFecAlta(), params));
            where.append(Y41eSqlUtils.generarWhereIgual(FEC_ULTMODIF_002, bean.getFecUltModif(), params));

            if(bean.getIdEncuesta()!=null && bean.getIdEncuesta()>0){
                where.append(" AND NOT EXISTS (SELECT 1 FROM Y41E03S01 WHERE ID_BLOQUE_003= ID_BLOQUE_002 AND ID_ENCUESTA_003=?)");
                params.add(bean.getIdEncuesta());
            }

            if(bean.getSelectedAll() != null) {
                if(bean.getSelectedAll()){
                    where.append(Y41eSqlUtils.generarWhereNotIn(ID_BLOQUE_002, bean.getSelectedIds(), params));
                }else{
                    where.append(Y41eSqlUtils.generarWhereIn(ID_BLOQUE_002, bean.getSelectedIds(), params));
                }
            }
        }
        return where.toString();
    }

    /**
     * Comprueba que no existe ninguna encuesta enviada o respondida para el id del bloque.
     * @param idBloque Long
     * @return Integer
     */
    @Override
    public Integer validarBloqueEnviada(Long idBloque){
        StringBuilder select = new StringBuilder(Y41eResultadosDaoImpl.STRING_BUILDER_INIT);
        select.append("SELECT COUNT(1) ")
        .append(" FROM Y41E01S01")
        .append(" JOIN Y41E10S01 ON ID_ENCUESTA_001=ID_ENCUESTA_010")
        .append(" JOIN Y41E03S01 ON ID_ENCUESTA_003=ID_ENCUESTA_001")
        .append(" WHERE FEC_RESPUESTA_010 IS NOT NULL ")
        .append(" AND ID_BLOQUE_003=?");
        return this.getJdbcTemplate().queryForObject(select.toString(), Integer.class, idBloque);
    }

    /**
     * Comprueba que no existe ninguna encuesta enviada o respondida para el id del bloque.
     * @param idBloque Long
     * @return Integer
     */
    @Override
    public Integer validarBloqueEncuesta(Long idBloque){
        StringBuilder select = new StringBuilder(Y41eResultadosDaoImpl.STRING_BUILDER_INIT);
        select.append("SELECT COUNT(1) ")
        .append(" FROM Y41E01S01")
        .append(" JOIN Y41E03S01 ON ID_ENCUESTA_003=ID_ENCUESTA_001")
        .append(" WHERE ID_BLOQUE_003=?");
        return this.getJdbcTemplate().queryForObject(select.toString(), Integer.class, idBloque);
    }

    /**
     * Obtiene los bloques para el suggest
     * @param bloque Y41eBloque
     * @param startsWith boolean
     * @return List<Y41eBloque>
     */
    @Override
    public List<Y41eBloque> findAllSuggest(Y41eBloque bloque, boolean startsWith){
        List<Object> params = new ArrayList<Object>();
        StringBuilder sqlSuggest = new StringBuilder();
        sqlSuggest.append(this.getSelect()); 
        sqlSuggest.append(this.getFrom());
        sqlSuggest.append(Y41eSqlUtils.DEFAULT_WHERE);
        sqlSuggest.append(this.getWhereLike(bloque, startsWith, params, false));
        return this.getJdbcTemplate().query(sqlSuggest.toString(), this.getRwMap(), params.toArray());
    }

    /* (non-Javadoc)
     * @see com.ejie.y41e.dao.Y41eGenericoDaoImpl#getOrderBy()
     */
    @Override
    protected String[] getOrderBy() {
        return Y41eBloqueDaoImpl.ORDER_BY_WHITE_LIST;
    }


    /**
     * StringBuilder initilization value
     */
    public static final int STRING_BUILDER_INIT = 4096;
}
