package org.celllife.idart.database;

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

import liquibase.change.custom.CustomSqlChange;
import liquibase.database.Database;
import liquibase.database.jvm.JdbcConnection;
import liquibase.exception.CustomChangeException;
import liquibase.exception.DatabaseException;
import liquibase.exception.LiquibaseException;
import liquibase.exception.SetupException;
import liquibase.exception.ValidationErrors;
import liquibase.resource.ResourceAccessor;
import liquibase.sql.visitor.SqlVisitor;
import liquibase.statement.SqlStatement;
import liquibase.statement.core.RawSqlStatement;
import liquibase.statement.core.UpdateStatement;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

/**
 * This class fixes this bug: http://jira.cell-life.org/browse/IDART-290
 * 
 * <p>The class has two steps:
 * <p>First it looks for patients with duplicate patientid fields in the patient table
 * and renames them to id-duplicate.
 * <p>Secondly it looks for all patients that do not have any patientidentifers and adds
 * patientidentifiers for them.
 */
public class FixMissingIdentifiers implements CustomSqlChange {
	
	private static Logger log = Logger.getLogger(FixMissingIdentifiers.class);

	@Override
	public String getConfirmationMessage() {
		return "Fix patients with duplicate patientid's or missing patientidentifier's";
	}

	@Override
	public void setUp() throws SetupException {
	}

	@Override
	public void setFileOpener(ResourceAccessor arg0) {
	}

	@Override
	public ValidationErrors validate(Database arg0) {
		 return new ValidationErrors();
	}

	@Override
	public SqlStatement[] generateStatements(Database arg0)
			throws CustomChangeException {
		List<SqlStatement> statements = new ArrayList<SqlStatement>();
		
		JdbcConnection con = (JdbcConnection) arg0.getConnection();
		try {
			renameDuplicatePatientIds(statements, con);
			
			// need to execute these statements to avoid missing some missing elements in the next step
			arg0.execute(statements.toArray(new SqlStatement[] {}), new ArrayList<SqlVisitor>());
			
			statements = new ArrayList<SqlStatement>();
			insertMissingIdentifiers(statements, con);
			return statements.toArray(new SqlStatement[] {});
		} catch (DatabaseException e) {
			throw new CustomChangeException(e);
		} catch (SQLException e) {
			throw new CustomChangeException(e);
		} catch (LiquibaseException e) {
			throw new CustomChangeException(e);
		}
	}

	private void renameDuplicatePatientIds(List<SqlStatement> statements,
			JdbcConnection con) throws SQLException, DatabaseException {
		
		ResultSet rs_dupPatIds = con.prepareStatement("select patientid from patient group by patientid having count(patientid) > 1").executeQuery();
		while (rs_dupPatIds.next()){
			String patientid = rs_dupPatIds.getString("patientid");
			PreparedStatement statement = con.prepareStatement("select id from patient where patientid = ? order by id desc");
			statement.setString(1, patientid);
			ResultSet rs_patIds = statement.executeQuery();
			int duplicateCount = 0;
			while (rs_patIds.next()){
				int p_id = rs_patIds.getInt("id");
				PreparedStatement s_pid = con.prepareStatement("select id from patientidentifier where patient_id = ?" +
						" and value = ?");
				s_pid.setInt(1, p_id);
				s_pid.setString(2, patientid);
				ResultSet rs_patIdents = s_pid.executeQuery();
				
				markPatientIdAsDuplicate(statements, patientid, p_id, duplicateCount);
				if (rs_patIdents.next()){
					int ident_id = rs_patIdents.getInt("id");
					// there is an identifier for this patient with this value
					markPatientIdentifierAsDuplicate(statements, patientid, ident_id, duplicateCount);
				}
				duplicateCount++;
			}
		}
	}

	private void insertMissingIdentifiers(List<SqlStatement> statements,
			JdbcConnection con) throws SQLException, DatabaseException {
		ResultSet rs_patMissingIds = con.prepareStatement("select id, patientid from patient where patientid not in (select value from patientidentifier)").executeQuery();
		while (rs_patMissingIds.next()){
			int id = rs_patMissingIds.getInt("id");
			String patientid = rs_patMissingIds.getString("patientid");
			insertNewPatientIdentifier(statements, patientid, id);
		}
	}

	private void insertNewPatientIdentifier(List<SqlStatement> statements,
			String patientid, int p_id) {
		log.info("Creating new patient identifier for patient: id=" + p_id + " identifier= " + patientid);
		RawSqlStatement is = new RawSqlStatement("insert into patientidentifier (id, patient_id, value, type_id)" +
				" values (nextval('hibernate_sequence'), "+p_id+", '"
				+ patientid+"', 1)");
		statements.add(is);
	}

	private void markPatientIdentifierAsDuplicate(
			List<SqlStatement> statements, String patientid, int ident_id, int count) {
		
		if (count == 0){
			return;
		}
		
		log.info("Marking patient identifier as duplicate: " + patientid);
		UpdateStatement us = new UpdateStatement(null, "patientidentifier");
		us.addNewColumnValue("value", patientid + StringUtils.repeat("-duplicate", count));
		us.setWhereClause("id = ?");
		us.addWhereParameter(ident_id);
		statements.add(us);
	}

	private void markPatientIdAsDuplicate(List<SqlStatement> statements, String patientid,
			int id, int count) {
		if (count == 0){
			return;
		}
		log.info("Marking patientid as duplicate: " + patientid);
		UpdateStatement us = new UpdateStatement(null, "patient");
		us.addNewColumnValue("patientid", patientid + StringUtils.repeat("-duplicate", count));
		us.setWhereClause("id = ?");
		us.addWhereParameter(id);
		statements.add(us);
	}
}