

package gov.va.med.cds.persistence.hibernate;


import gov.va.med.cds.exception.ErrorCodeEnum;
import gov.va.med.cds.exceptionframework.ExceptionHandler;
import gov.va.med.cds.filter.EntryFilterInterface;
import gov.va.med.cds.filter.ParameterMapInterface;
import gov.va.med.cds.filter.QueryParameter;
import gov.va.med.cds.persistence.PersistenceException;
import gov.va.med.cds.persistence.PooledConnectionUnwrapperInterface;
import gov.va.med.cds.persistence.QueryAssociationInterface;
import gov.va.med.cds.persistence.QueryWorkInterface;
import gov.va.med.cds.persistence.ReadException;
import gov.va.med.cds.persistence.hibernate.common.PointInTime;
import gov.va.med.cds.response.strategy.VistaStoredProcedureResponseStrategyInterface;
import gov.va.med.cds.util.TimeoutUtil;

import java.net.SocketException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import javax.sql.DataSource;

import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.orm.hibernate4.SessionFactoryUtils;


public class VistaStoredProcedureQueryWork
    implements
        QueryWorkInterface
{
    protected static final String DEFAULT_START_DATE = "18410101000000";
    protected static final String DEFAULT_END_DATE = "30001231235959";
    private static final String EXAM_DOMAIN_ENTRY_POINT = "Exam2507";
    private static final String PATIENT_ALERT = "PatientAlert";
    private static final String CLINICAL_REMINDER = "ClinicalReminder";
    private static final String APPOINTMENT_LETTER = "AppointmentLetter";

    protected Element results = DocumentHelper.createElement( "results" );
    protected List<Exception> exceptions = new ArrayList<Exception>();

    protected PooledConnectionUnwrapperInterface pooledConnectionUnwrapper;
    protected Session session;
    protected QueryAssociationInterface queryAssociation;
    protected EntryFilterInterface entryFilter;
    protected List<String> personIdentifiers;
    protected Map<String, QueryParameterTransformerInterface> parameterTransformerMap;
    protected Map<String, String> queryMap;
    protected VistaStoredProcedureResponseStrategyInterface vistaResponseStrategy;
    protected String applicationName;
    protected String siteId;
    private Map<String, String> templateTimeoutMap;


    public void setTemplateTimeoutMap( Map<String, String> templateTimeoutMap )
    {
        this.templateTimeoutMap = templateTimeoutMap;
    }


    public Map<String, String> getTemplateTimeoutMap( )
    {
        return templateTimeoutMap;
    }


    public void setPooledConnectionUnwrapper( PooledConnectionUnwrapperInterface pooledConnectionUnwrapper )
    {
        this.pooledConnectionUnwrapper = pooledConnectionUnwrapper;
    }


    public VistaStoredProcedureQueryWork( Session session, QueryAssociationInterface queryAssociation, EntryFilterInterface entryFilter,
                    List<String> personIdentifiers, Map<String, QueryParameterTransformerInterface> parameterTransformerMap,
                    Map<String, String> queryMap, VistaStoredProcedureResponseStrategyInterface vistaResponseStrategy, String applicationName,
                    String siteId )
    {
        this.session = session;
        this.queryAssociation = queryAssociation;
        this.entryFilter = entryFilter;
        this.personIdentifiers = personIdentifiers;
        this.parameterTransformerMap = parameterTransformerMap;
        this.queryMap = queryMap;
        this.vistaResponseStrategy = vistaResponseStrategy;
        this.applicationName = applicationName;
        this.siteId = siteId;

    }


    protected String buildQueryString( String associationName, EntryFilterInterface aEntryFilter, List<String> personIdentifiers )
        throws ReadException
    {
        String sqlQuery = null;
        String queryName = String.format( "%s.%s.%s", entryFilter.getTemplateId(), entryFilter.getDomainEntryPoint(), associationName );

        List<String> recordIdentifiers = aEntryFilter.getRecordIdentifiers();
        if ( recordIdentifiers != null && recordIdentifiers.size() > 0 ) // person ids and record identifiers query
        {
            queryName = String.format( "%s.identifiers", queryName );
        }
        else if ( aEntryFilter.useDates() ) // person ids and date query.
        {
            queryName = String.format( "%s.date", queryName );
        }

        ParameterMapInterface parameterMap = aEntryFilter.getAdditionalParametersMap();
        if ( parameterMap != null )
        {
            for ( String filterParameterName : parameterMap.getFilterParameterNames() )
            {
                queryName = String.format( "%s.%s", queryName, filterParameterName );
            }
        }

        sqlQuery = queryMap.get( queryName );

        return sqlQuery;
    }


    protected String extractVistaPatientIdentifier( List<String> personIdentifiers )
    {
        QueryParameterTransformerInterface pidsParameterTransformer = this.parameterTransformerMap.get( "pids" );
        if ( pidsParameterTransformer == null )
        {
            throw new ReadException( ErrorCodeEnum.PARAMETER_TRANSFORMER_EXPECTED_ERROR, "pids" );
        }
        List patientIdentifiers = ( List )pidsParameterTransformer.transform( personIdentifiers );
        /*
         * Defect 272095 - Pathways Web Service is not returning all the Appointments for patients with more than 1 active DFN at a VAMC site
         * JLA The code below creates a '~' separated list of vistaPatientIdentifiers to replace just using the first identifier in the list
         *      return ( patientIdentifiers.get( 0 ) ).toString();
         */
        String vistaPatientIdentifiers = ( patientIdentifiers.get( 0 ) ).toString();
        if ( patientIdentifiers.size() > 1 )
        {
            String vistaPatientIdentifier;
            for ( int i = 1; i < patientIdentifiers.size(); i++ )
            {
                vistaPatientIdentifier = ( patientIdentifiers.get( i ) ).toString();
                if ( ! vistaPatientIdentifiers.contains( vistaPatientIdentifier ) )
                {
                    vistaPatientIdentifiers = vistaPatientIdentifiers + "~" + vistaPatientIdentifier;
                }
            }
        }

        return vistaPatientIdentifiers;
    }


    protected String extractVistaDateFromFilter( Calendar calendar, String defaultDate )
    {

        String dateValue = "";

        QueryParameterTransformerInterface dateTransformer = this.parameterTransformerMap.get( "startDate" );

        // Process start dates
        if ( calendar != null )
        {
            PointInTime pointInTimeStart = ( PointInTime )dateTransformer.transform( calendar );

            if ( !pointInTimeStart.getTimestamp().equals( defaultDate ) )
            {
                dateValue = pointInTimeStart.getTimestamp();
            }
        }

        return dateValue;
    }


    @SuppressWarnings( "rawtypes" )
    protected List<String> buildQueryParameterList( String query, String associationName, EntryFilterInterface aEntryFilter,
                    List<String> personIdentifiers )
    {
        ArrayList<String> namedParameters = new ArrayList<String>();
        namedParameters.add( aEntryFilter.getRequestId() );

        String rpcName = query.substring( 5, query.indexOf( '(' ) );
        namedParameters.add( rpcName );

        namedParameters.add( extractVistaPatientIdentifier( personIdentifiers ) );

        if ( !CLINICAL_REMINDER.equals( aEntryFilter.getDomainEntryPoint() ) )
        {
            namedParameters.add( extractVistaDateFromFilter( aEntryFilter.getStartDate(), DEFAULT_START_DATE ) );
        }

        if ( !PATIENT_ALERT.equals( aEntryFilter.getDomainEntryPoint() ) && !CLINICAL_REMINDER.equals( aEntryFilter.getDomainEntryPoint() )
                        && !APPOINTMENT_LETTER.equals( aEntryFilter.getDomainEntryPoint() ) )
        {
            namedParameters.add( extractVistaDateFromFilter( aEntryFilter.getEndDate(), DEFAULT_END_DATE ) );
        }

        ParameterMapInterface parameterMap = aEntryFilter.getAdditionalParametersMap();
        if ( parameterMap != null )
        {
            if ( !parameterMap.getFilterParameterNames().isEmpty() )
            {
                for ( String filterParameterName : parameterMap.getFilterParameterNames() )
                {
                    QueryParameter queryParameter = parameterMap.getParameterValue( filterParameterName );
                    if ( queryParameter == null )
                    {
                        throw new PersistenceException( ErrorCodeEnum.INVALID_OR_UNEXPECTED_QUERY_PARAM, filterParameterName );
                    }

                    String parameterValue = null;
                    if ( queryParameter.getValue() != null && queryParameter.getValue() instanceof ArrayList )
                    {
                        ArrayList<String> valueList = ( ArrayList )queryParameter.getValue();
                        StringBuilder paramBuilder = new StringBuilder();
                        for ( String value : valueList )
                        {
                            paramBuilder.append( value ).append( "^" );
                        }
                        paramBuilder.deleteCharAt( paramBuilder.lastIndexOf( "^" ) );
                        parameterValue = paramBuilder.toString();
                    }
                    else
                        parameterValue = ( String )queryParameter.getValue();

                    namedParameters.add( parameterValue );
                }
            }
            else
            {
                namedParameters.add( "" );
            }

        }
        else
        {
            namedParameters.add( "" );
        }

        if ( EXAM_DOMAIN_ENTRY_POINT.equals( aEntryFilter.getDomainEntryPoint() ) )
        {
            namedParameters.add( "" );
        }
        else if ( PATIENT_ALERT.equals( aEntryFilter.getDomainEntryPoint() ) )
        {
            namedParameters.add( "" );// 6th element
        }
        else if ( APPOINTMENT_LETTER.equals( aEntryFilter.getDomainEntryPoint() ) )
        {
            namedParameters.remove( ( namedParameters.size() - 1 ) );// The Appointment Letter stored procedure takes 1 less argument than the others
        }

        return namedParameters;
    }


    /* (non-Javadoc)
     * @see java.lang.Runnable#run()
     */
    @Override
    public void run( )
    {
        CallableStatement cstmt = null;
        Connection pooledConnection = null;
        Connection wrappedConnection = null;
        ResultSet rs = null;

        try
        {
            String query = buildQueryString( queryAssociation.getAssociationName(), entryFilter, personIdentifiers );
            List<String> namedParameters = buildQueryParameterList( query, queryAssociation.getAssociationName(), entryFilter, personIdentifiers );
            List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();

            DataSource dataSource = SessionFactoryUtils.getDataSource( session.getSessionFactory() );

            wrappedConnection = dataSource.getConnection();

            pooledConnection = pooledConnectionUnwrapper.unwrap( wrappedConnection );

            cstmt = pooledConnection.prepareCall( query );

            for ( int i = 0; i < namedParameters.size(); i++ )
            {
                cstmt.setString( i + 1, namedParameters.get( i ) );
            }

            long queryTimeout = TimeoutUtil.processTimeout( getTemplateTimeoutMap(), entryFilter, this.applicationName );
            cstmt.setQueryTimeout( ( int )queryTimeout );

            ///TODO wrap performance log around this call - be careful to wrap the finish time in the correct logical place...(end of jdbc blob streaming)
            rs = cstmt.executeQuery();

            while ( rs.next() )
            {
                String returnError = rs.getString( "returnError" );
                if ( ( returnError != null ) && returnError.toLowerCase( Locale.ENGLISH ).startsWith( "error" ) )
                {
                    if ( returnError.toLowerCase( Locale.ENGLISH ).startsWith( "error: no data found." )
                                    || returnError.toLowerCase( Locale.ENGLISH ).startsWith( "error: no records found." ) )
                    {
                        break;
                    }

                    int colCount = rs.getMetaData().getColumnCount();
                    StringBuffer error = new StringBuffer();
                    error.append( returnError ).append( "," );
                    for ( int colIndex = 1; colIndex < colCount; colIndex++ )
                    {
                        if ( colIndex > 1 )
                        {
                            error.append( "," );
                        }
                        error.append( rs.getMetaData().getColumnName( colIndex ) );
                        error.append( "=" ).append( rs.getObject( colIndex ) );
                    }
                    throw new SQLException( error.toString() );
                }
                else
                {
                    if ( ( returnError != null ) && ( returnError.toLowerCase( Locale.ENGLISH ).startsWith( "warning" ) ) )
                    {
                        int colCount = rs.getMetaData().getColumnCount();
                        StringBuffer error = new StringBuffer();
                        error.append( returnError ).append( "," );
                        for ( int colIndex = 1; colIndex < colCount; colIndex++ )
                        {
                            if ( colIndex > 1 )
                            {
                                error.append( "," );
                            }
                            error.append( rs.getMetaData().getColumnName( colIndex ) );
                            error.append( "=" ).append( rs.getObject( colIndex ) );
                        }
                        addException( new Exception( returnError ), applicationName );
                    }

                    DefaultLobHandler lobHandler = new DefaultLobHandler();
                    Map<String, Object> results = new HashMap<String, Object>();
                    byte[] blobBytes = lobHandler.getBlobAsBytes( rs, "stream" );
                    results.put( "stream", blobBytes );
                    results.put( "siteID", rs.getString( "siteID" ) );
                    resultList.add( results );
                }
            }

            if ( ( resultList != null ) && ( resultList.size() > 0 ) && ( resultList.get( 0 ).get( "stream" ) != null ) )
            {
                this.results = vistaResponseStrategy.formatResponse( resultList, entryFilter.getDomainEntryPoint(), entryFilter.getTemplateId() );
            }
        }
        catch ( SocketException se )
        {
            addException( se, applicationName );
        }
        catch ( Exception ex )
        {
            addException( ex, applicationName );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( Exception er )
                {
                    er.printStackTrace();
                }
                finally
                {
                    rs = null;
                }
            }
            if ( cstmt != null )
            {
                try
                {
                    cstmt.close();
                }
                catch ( SQLException ec )
                {
                    ec.printStackTrace();
                }
                finally
                {
                    cstmt = null;
                }
            }
            if ( pooledConnection != null )
            {
                try
                {
                    pooledConnection.close();
                }
                catch ( SQLException ep )
                {
                    ep.printStackTrace();
                }
                finally
                {
                    pooledConnection = null;
                }
            }
            if ( wrappedConnection != null )
            {
                try
                {
                    wrappedConnection.close();
                }
                catch ( SQLException ew )
                {
                    ew.printStackTrace();
                }
                finally
                {
                    wrappedConnection = null;
                }
            }
        }
    }


    public void setVistaResponseStrategy( VistaStoredProcedureResponseStrategyInterface vistaResponseStrategy )
    {
        this.vistaResponseStrategy = vistaResponseStrategy;
    }


    public void setQueryMap( Map<String, String> queryMap )
    {
        this.queryMap = queryMap;
    }


    @Override
    public boolean isDaemon( )
    {
        return false;
    }


    @Override
    public void release( )
    {
        try
        {
            if ( this.session != null && this.session.isOpen() )
            {
                this.session.cancelQuery();
                this.session.close();
            }
        }
        catch ( HibernateException e )
        {
            addException( e, applicationName );
        }
        finally
        {
            session = null;
        }

    }


    @Override
    public QueryAssociationInterface getQueryAssociation( )
    {
        return this.queryAssociation;
    }


    @Override
    public Object getResults( )
    {
        return this.results;
    }


    @Override
    public List<Exception> getExceptions( )
    {
        return this.exceptions;
    }


    @Override
    public void addException( Exception ex, String applicationName )
    {
        ExceptionHandler.logRootException( ex, entryFilter.getTemplateId(), entryFilter.getRequestId(), applicationName );
        this.exceptions.add( ex );
    }

}
