package gov.va.med.ars.spring.dao;


import gov.va.med.ars.exceptions.ArsDataAccessException;
import gov.va.med.ars.spring.model.ClaimAttachments;
import gov.va.med.ars.spring.model.UserAuthorization;
import gov.va.med.ars.util.DateUtils;
import gov.va.med.ars.util.FileUtils;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.io.FilenameUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
//import gov.va.med.ars.client.OException;

/**
 * An implementation of the claimAttachmentDAO interface.
 *
 */
public class ClaimAttachmentsDAOImpl extends BaseDao implements ClaimAttachmentsDAO {

	private JdbcTemplate jdbcTemplate;
	
	public ClaimAttachmentsDAOImpl(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	static final Logger errorLogger = Logger.getLogger("errorLogger");

	@Override
	public void saveOrUpdate(ClaimAttachments claimAttachment) {
		
		//System.out.println("Inside saveOrUpdate method id = " + claimAttachment.getId());
		
		
		if (claimAttachment.getId() > 0) {
			// update
			String sql = "UPDATE [Claim_Attachments].[dbo].ClaimAttachments SET PROVIDER_NAME=?, PROVIDER_IDENTIFIER_NUMBER=?, PATIENT_FIRST_NAME=?, "
						+ "PATIENT_LAST_NAME=?, REPORT_CODE=?, PATIENT_CONTROL_NUMBER=?, MEDICAL_RECORD_NUMBER=?, PATIENT_IDENTIFIER_NUMBER=?, "
					    + "PROVIDER_NPI=?, CLAIM_IDENTIFIER=?, CLAIM_SERVICE_DATE=?, PAYER_CLAIM_CONTROL_NUMBER=?, "
						+ "ATTACHMENT_CONTROL_NUMBER=?, ATTACHMENT_NAME=?, ATTACHMENT_TYPE=?, VER=? WHERE id=?";
			jdbcTemplate.update(sql, claimAttachment.getProviderName(), claimAttachment.getProviderIdentifier(),
					claimAttachment.getPatientFirstName(), claimAttachment.getPatientLastName(),
					claimAttachment.getReportCode(), claimAttachment.getPatientControlNumber(), 
					claimAttachment.getMedicalRecordNumber(), claimAttachment.getPatientIdentifier(), 
					claimAttachment.getProviderNpi(), claimAttachment.getClaimIdentifier(), 
					claimAttachment.getClaimServiceDate(), claimAttachment.getPayerClaimControlNumber(), 
					claimAttachment.getAttachmentControlNumber(), claimAttachment.getAttachmentName(), 
					claimAttachment.getAttachmentType(), claimAttachment.getVer(), claimAttachment.getId());
		} else {
			// insert
			String sql = "INSERT INTO [Claim_Attachments].[dbo].ClaimAttachments (PROVIDER_NAME, PROVIDER_IDENTIFIER_NUMBER, PATIENT_FIRST_NAME, "
					+ "PATIENT_LAST_NAME, REPORT_CODE, PATIENT_CONTROL_NUMBER, MEDICAL_RECORD_NUMBER, PATIENT_IDENTIFIER_NUMBER, PROVIDER_NPI, "
					+ "CLAIM_IDENTIFIER, CLAIM_SERVICE_DATE, PAYER_CLAIM_CONTROL_NUMBER, ATTACHMENT_CONTROL_NUMBER, ATTACHMENT_NAME, "
					+ "ATTACHMENT_TYPE, VER ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			jdbcTemplate.update(sql, claimAttachment.getProviderName(), claimAttachment.getProviderIdentifier(),
					claimAttachment.getPatientFirstName(), claimAttachment.getPatientLastName(), 
					claimAttachment.getReportCode(), claimAttachment.getPatientControlNumber(), 
					claimAttachment.getMedicalRecordNumber(), claimAttachment.getPatientIdentifier(), 
					claimAttachment.getProviderNpi(), claimAttachment.getClaimIdentifier(), 
					claimAttachment.getClaimServiceDate(), claimAttachment.getPayerClaimControlNumber(), 
					claimAttachment.getAttachmentControlNumber(), claimAttachment.getAttachmentName(), 
					claimAttachment.getAttachmentType(), claimAttachment.getVer());
		}
		
	}

	@Override
/*	public void delete(Integer claimAttachmentId) {
		String sql = "DELETE FROM [Claim_Attachments].[dbo].ClaimAttachments WHERE id=?";
		jdbcTemplate.update(sql, claimAttachmentId);
	}*/
	
	public void delete(Integer claimAttachmentId) {
		String sql = "UPDATE [Claim_Attachments].[dbo].ClaimAttachments SET STATUS='D' WHERE id=?";
		jdbcTemplate.update(sql, claimAttachmentId);
	}
	
	@Override
	public String downloadClaimAttachment(Integer claimAttachmentId) {
	
		String folderName = "";
		String sql = "SELECT id, attachment_name, attachment_type "
		             + "FROM [Claim_Attachments].[dbo].ClaimAttachments WHERE id = " +claimAttachmentId;
		ResultSet rs;

		try {
			
			//Connection conn = jdbcTemplate.getDataSource().getConnection();
			
			Connection conn = jdbcTemplate.getDataSource().getConnection();
			
			rs = getResultSet(conn, sql);
			
			folderName = FileUtils.readPropertiesFile().getProperty("ars.server.save.location");
			
			//System.out.println("folderName = " + folderName);
			
			//This is not required anymore because the file is already saved at the download location
			//saveFiletoServer(rs, folderName);
		} catch (Exception e) {
			String message = "Unable to download attachment : " + e.getMessage();
			errorLogger.error("Exception thrown when downloading attachment : " + e.getMessage() );
			throw new ArsDataAccessException(message ,e);
		}

		return folderName;
		
		//jdbcTemplate.update(sql, claimAttachmentId);
	}
	
	/** This method gets file from database
	 * and saves it to the file system
	 * @param String FileName
	 * @param ResultSet rs
	 * @exception OException
	 * @exception FileNotFoundException
	 * @exception SQLException
	*/

	public void saveFiletoServer(ResultSet rs, String folderName) throws IOException, FileNotFoundException, SQLException
	{
		//Array to hold array of file bytes
		byte[] fileBytes;
		
		String fileName = "";
		String fileType = "";
		Integer id = 0;
		
/*		Random rand = new Random(System.currentTimeMillis()); 
		int suffix = rand.nextInt();*/

		try {
			if (rs.next()) //if there is a record
			{
				id       = rs.getInt(1);
				fileName = rs.getString(2);
				
				String baseUrl = FilenameUtils.getPath(fileName);
				String myFile = FilenameUtils.getBaseName(fileName) + "." + FilenameUtils.getExtension(fileName);
				//System.out.println(baseUrl);
				//System.out.println("Inside saveFiletoServer downloading file : " + myFile);
				fileType = rs.getString(3);
				
				//String[] tokens = fileName.split("\\.(?=[^\\.]+$)");
				//fileName        = folderName + tokens[0] + id + "." + fileType;
				
				fileName        = folderName + myFile;
				System.out.println("Downloading  fileName : " + fileName);
				fileBytes       = rs.getBytes(4);
				OutputStream targetFile=  new FileOutputStream(fileName); // define the output stream
			    
				targetFile.write(fileBytes); //write the array of bytes to file
				targetFile.close(); //close the File stream
			}
		} catch (Exception e) {
			String message = "Unable to download attachment : " + e.getMessage();
			errorLogger.error("Exception thrown when downloading attachment : " + e.getMessage() );
			throw new ArsDataAccessException(message ,e);
		}

	}
	
	@Override
	public List<String> getCodedefinitions() {
		String sql = "SELECT code+'-'+definition FROM [Claim_Attachments].[dbo].ArsReportType";
		
		List<String> listcodedefinitions = jdbcTemplate.query(sql, new RowMapper<String>() {

			@Override
			public String mapRow(ResultSet rs, int rowNum) throws SQLException 
			{
				try {
					String codeDefinition = rs.getString(1);
					
					return codeDefinition;
				} catch (Exception e) {
					String message = "Unable to get code definitions : " + e.getMessage();
					errorLogger.error("Exception thrown when getting report types : " + e.getMessage() );
					throw new ArsDataAccessException(message ,e);
				}
			}
			
		});
		
		return listcodedefinitions;
	}

	@Override
	public List<ClaimAttachments> list() {
		String sql = "SELECT * FROM [Claim_Attachments].[dbo].ClaimAttachments WHERE status != 'D'";
		List<ClaimAttachments> listclaimAttachment = jdbcTemplate.query(sql, new RowMapper<ClaimAttachments>() {

			@Override
			public ClaimAttachments mapRow(ResultSet rs, int rowNum) throws SQLException {
				try {
					ClaimAttachments aclaimAttachment = new ClaimAttachments();

					aclaimAttachment.setId(rs.getInt("id"));
					aclaimAttachment.setProviderName(rs.getString("PROVIDER_NAME"));
					aclaimAttachment.setProviderIdentifier(rs.getString("PROVIDER_IDENTIFIER_NUMBER"));
					aclaimAttachment.setPatientFirstName(rs.getString("PATIENT_FIRST_NAME"));
					aclaimAttachment.setPatientLastName(rs.getString("PATIENT_LAST_NAME"));
					aclaimAttachment.setPatientControlNumber(rs.getString("PATIENT_CONTROL_NUMBER"));
					aclaimAttachment.setMedicalRecordNumber(rs.getString("MEDICAL_RECORD_NUMBER"));
					aclaimAttachment.setPatientIdentifier(rs.getString("PATIENT_IDENTIFIER_NUMBER"));
					aclaimAttachment.setProviderNpi(rs.getString("PROVIDER_NPI"));
					aclaimAttachment.setClaimIdentifier(rs.getString("CLAIM_IDENTIFIER"));

					try {
						aclaimAttachment.setClaimServiceDate(DateUtils.toEnglishDate(rs.getDate("CLAIM_SERVICE_DATE")));
					} catch (Exception e) {
						String message = "Unable to list attachments : " + e.getMessage();
						throw new ArsDataAccessException(message ,e);
					}
								
					aclaimAttachment.setPayerClaimControlNumber(rs.getString("PAYER_CLAIM_CONTROL_NUMBER"));
					aclaimAttachment.setAttachmentControlNumber(rs.getString("ATTACHMENT_CONTROL_NUMBER"));
					aclaimAttachment.setAttachmentName(rs.getString("ATTACHMENT_NAME"));
					aclaimAttachment.setAttachmentType(rs.getString("ATTACHMENT_TYPE"));
					//aclaimAttachment.setAttachment(rs.getBytes("ATTACHMENT"));
					aclaimAttachment.setAttachment(FileUtils.readAttachment(rs.getString("ATTACHMENT_NAME")));
					
					aclaimAttachment.setVer(rs.getString("VER"));
					
					return aclaimAttachment;
				} catch (Exception e) {
					String message = "Unable to list attachments : " + e.getMessage();
					errorLogger.error("Exception thrown when listing attachments : " + e.getMessage() );
					throw new ArsDataAccessException(message ,e);
				}
			}
			
		});
		
		return listclaimAttachment;
	}

	@Override
	public ClaimAttachments get(Integer claimAttachmentId) {
		String sql = "SELECT * FROM [Claim_Attachments].[dbo].ClaimAttachments WHERE id=" + claimAttachmentId;
		return jdbcTemplate.query(sql, new ResultSetExtractor<ClaimAttachments>() {

			@Override
			public ClaimAttachments extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if (rs.next()) {
					ClaimAttachments claimAttachment = new ClaimAttachments();
					claimAttachment.setId(rs.getInt("id"));
					claimAttachment.setProviderName(rs.getString("PROVIDER_NAME"));
					claimAttachment.setProviderIdentifier(rs.getString("PROVIDER_IDENTIFIER_NUMBER"));
					claimAttachment.setPatientFirstName(rs.getString("PATIENT_FIRST_NAME"));
					claimAttachment.setPatientLastName(rs.getString("PATIENT_LAST_NAME"));
					claimAttachment.setReportCode(rs.getString("REPORT_CODE"));
					claimAttachment.setPatientControlNumber(rs.getString("PATIENT_CONTROL_NUMBER"));
					claimAttachment.setMedicalRecordNumber(rs.getString("MEDICAL_RECORD_NUMBER"));
					claimAttachment.setPatientIdentifier(rs.getString("PATIENT_IDENTIFIER_NUMBER"));
					claimAttachment.setProviderNpi(rs.getString("PROVIDER_NPI"));
					claimAttachment.setClaimIdentifier(rs.getString("CLAIM_IDENTIFIER"));
					//claimAttachment.setClaimServiceDate(rs.getDate("CLAIM_SERVICE_DATE"));

					try {
						claimAttachment.setClaimServiceDate(DateUtils.toEnglishDate(rs.getDate("CLAIM_SERVICE_DATE")));
					} catch (Exception e) {
						String message = "Unable to download attachment : " + e.getMessage();
						throw new ArsDataAccessException(message ,e);
					}
					
					claimAttachment.setPayerClaimControlNumber(rs.getString("PAYER_CLAIM_CONTROL_NUMBER"));
					claimAttachment.setAttachmentControlNumber(rs.getString("ATTACHMENT_CONTROL_NUMBER"));
					claimAttachment.setAttachmentName(rs.getString("ATTACHMENT_NAME"));
					claimAttachment.setAttachmentType(rs.getString("ATTACHMENT_TYPE"));
					
					//claimAttachment.setAttachment(rs.getBytes("ATTACHMENT"));
					
					claimAttachment.setAttachment(FileUtils.readAttachment(rs.getString("ATTACHMENT_NAME")));
					
					claimAttachment.setLoincCode(rs.getString("LOINC_CODE"));
					claimAttachment.setStatus(rs.getString("STATUS"));
					claimAttachment.setVer(rs.getString("VER"));
					return claimAttachment;
				}
				
				return null;
			}
			
		});
	}
	
	@Override
	public UserAuthorization getUserAuth(String userId)
	{
		String sql = "SELECT * FROM [Claim_Attachments].[dbo].UserAuthorization WHERE UserId= '" + userId + "'";
		return jdbcTemplate.query(sql, new ResultSetExtractor<UserAuthorization>() {

			@Override
			public UserAuthorization extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if (rs.next()) {
					UserAuthorization userAuthorization = new UserAuthorization();
					userAuthorization.setUserId(rs.getString("UserId"));
					userAuthorization.setDomain(rs.getString("DOMAIN"));
					userAuthorization.setStatus(rs.getString("STATUS"));
					userAuthorization.setDownload(rs.getString("DOWNLOAD"));
					userAuthorization.setEdit(rs.getString("EDIT"));
					userAuthorization.setDelete(rs.getString("DEL"));
					userAuthorization.setAdmin(rs.getString("ADMIN"));

					return userAuthorization;
				}
				
				return null;
			}
			
		});
	}
	
	
	public List<ClaimAttachments> searchClaimAttachments(ClaimAttachments searchCriteria)
	{
		
		String patientControlNumber		= searchCriteria.getPatientControlNumber();
		String medicalRecordNumber      = searchCriteria.getMedicalRecordNumber();
		String patientLastName     		= searchCriteria.getPatientLastName();
		String patientFirstName    		= searchCriteria.getPatientFirstName();
		String patientIdentifier        = searchCriteria.getPatientIdentifier();
		String providerName             = searchCriteria.getProviderName();
		String providerIdentifier       = searchCriteria.getProviderIdentifier();
		String providerNpi              = searchCriteria.getProviderNpi();
		String claimIdentifier          = searchCriteria.getClaimIdentifier();
		String claimServiceStartDate   	= searchCriteria.getClaimServiceStartDate();
		String claimServiceEndDate   	= searchCriteria.getClaimServiceEndDate();
		String payerClaimControlNumber	= searchCriteria.getPayerClaimControlNumber();
		String attachmentControlNumber  = searchCriteria.getAttachmentControlNumber();
		String status                   = searchCriteria.getStatus();
		Integer attachmentId            = searchCriteria.getId();
		String reportCode               = searchCriteria.getReportCode();
		String sql 						= "";
	
		if( !reportCode.equals("ALL"))
		{
			sql = "SELECT a.*, b.definition FROM [Claim_Attachments].[dbo].ClaimAttachments a, [Claim_Attachments].[dbo].ArsReportType b " +
		          " WHERE a.report_code = b.code AND a.report_code = '" + reportCode + "'" + " " ;
		}
		else
		{
			sql = "SELECT a.*, b.definition FROM [Claim_Attachments].[dbo].ClaimAttachments a, [Claim_Attachments].[dbo].ArsReportType b " +
			          " WHERE a.report_code = b.code " ;
		}
		
		if( !status.equals("ALL"))
		{
			sql = sql + " AND STATUS = '" + status + "'";
		}

		if(patientControlNumber != null && !patientControlNumber.equals(""))
		{
				sql = sql + " AND PATIENT_CONTROL_NUMBER = '" + patientControlNumber + "'";

		}

		if( medicalRecordNumber != null && !medicalRecordNumber.equals(""))
		{
				sql = sql + " AND MEDICAL_RECORD_NUMBER = '" + medicalRecordNumber + "'";
		}

		if( patientLastName != null && !patientLastName.equals(""))
		{
				sql = sql + " AND PATIENT_LAST_NAME LIKE '" + patientLastName + "%'";
		}

		if( patientFirstName != null && !patientFirstName.equals(""))
		{
				sql = sql + " AND PATIENT_FIRST_NAME LIKE '" + patientFirstName + "%'";
		}

		if( patientIdentifier != null && !patientIdentifier.equals(""))
		{
				sql = sql + " AND PATIENT_IDENTIFIER_NUMBER = '" + patientIdentifier + "'";
		}
		if( providerName != null && !providerName.equals(""))
		{
				sql = sql + " AND PROVIDER_NAME = '" + providerName + "'";
		}
		if( providerIdentifier != null && !providerIdentifier.equals(""))
		{
				sql = sql + " AND PROVIDER_IDENTIFIER_NUMBER = '" + providerIdentifier + "'";
		}

		if( providerNpi != null && !providerNpi.equals(""))
		{
				sql = sql + " AND PROVIDER_NPI = '" + providerNpi + "'";
		}
		
		if( claimIdentifier != null && !claimIdentifier.equals(""))
		{
				sql = sql + " AND CLAIM_IDENTIFIER = '" + claimIdentifier + "'";
		}

		if( claimServiceStartDate != null && !claimServiceStartDate.equals(""))
		{
				sql = sql + " AND CLAIM_SERVICE_DATE >= '" + claimServiceStartDate + "'";

		}

		if( claimServiceEndDate != null && !claimServiceEndDate.equals(""))
		{
				sql = sql + " AND CLAIM_SERVICE_DATE <= '" + claimServiceEndDate + "'";
		}

		if( payerClaimControlNumber != null && !payerClaimControlNumber.equals(""))
		{
				sql = sql + " AND PAYER_CLAIM_CONTROL_NUMBER = '" + payerClaimControlNumber + "'";
		}

		if( attachmentControlNumber != null && !attachmentControlNumber.equals(""))
		{
				sql = sql + " AND ATTACHMENT_CONTROL_NUMBER = '" + attachmentControlNumber + "'";

		}

		if( attachmentId != null)
		{
				sql = sql + " AND Id = " + attachmentId;
		}

		if( sql.endsWith("AND "))
		{
			//sql = sql.substring(0, sql.length()-5);
			
			sql = sql.substring(0,sql.lastIndexOf("AND "));
		}

		System.out.println("sql query = " + sql);

		List<ClaimAttachments> listclaimAttachment = jdbcTemplate.query(sql, new RowMapper<ClaimAttachments>() {

			@Override
			public ClaimAttachments mapRow(ResultSet rs, int rowNum) throws SQLException {
				ClaimAttachments aclaimAttachment = new ClaimAttachments();

				aclaimAttachment.setId(rs.getInt("id"));
				aclaimAttachment.setReportCode(rs.getString("REPORT_CODE"));
				aclaimAttachment.setDefinition(rs.getString("DEFINITION"));
				aclaimAttachment.setProviderName(rs.getString("PROVIDER_NAME"));
				aclaimAttachment.setProviderIdentifier(rs.getString("PROVIDER_IDENTIFIER_NUMBER"));
				aclaimAttachment.setPatientFirstName(rs.getString("PATIENT_FIRST_NAME"));
				aclaimAttachment.setPatientLastName(rs.getString("PATIENT_LAST_NAME"));
				aclaimAttachment.setPatientControlNumber(rs.getString("PATIENT_CONTROL_NUMBER"));
				aclaimAttachment.setMedicalRecordNumber(rs.getString("MEDICAL_RECORD_NUMBER"));
				aclaimAttachment.setPatientIdentifier(rs.getString("PATIENT_IDENTIFIER_NUMBER"));
				aclaimAttachment.setProviderNpi(rs.getString("PROVIDER_NPI"));
				aclaimAttachment.setClaimIdentifier(rs.getString("CLAIM_IDENTIFIER"));

				try {
					aclaimAttachment.setClaimServiceDate(DateUtils.toEnglishDate(rs.getDate("CLAIM_SERVICE_DATE")));
				} catch (Exception e) {
					String message = "Unable to download attachment : " + e.getMessage();
					throw new ArsDataAccessException(message ,e);
				}

				aclaimAttachment.setPayerClaimControlNumber(rs.getString("PAYER_CLAIM_CONTROL_NUMBER"));
				aclaimAttachment.setLoincCode(rs.getString("LOINC_CODE"));
				aclaimAttachment.setStatus(rs.getString("STATUS"));
				aclaimAttachment.setAttachmentControlNumber(rs.getString("ATTACHMENT_CONTROL_NUMBER"));
				aclaimAttachment.setAttachmentName(rs.getString("ATTACHMENT_NAME"));
				aclaimAttachment.setAttachmentType(rs.getString("ATTACHMENT_TYPE"));
				//aclaimAttachment.setAttachment(rs.getBytes("ATTACHMENT"));
				aclaimAttachment.setVer(rs.getString("VER"));

				return aclaimAttachment;
			}

		});

		return listclaimAttachment;
		
	}

}
