package org.ensembl.healthcheck.testcase.eg_core;

import java.util.Collection;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.ensembl.healthcheck.DatabaseRegistry;
import org.ensembl.healthcheck.DatabaseRegistryEntry;
import org.ensembl.healthcheck.DatabaseType;
import org.ensembl.healthcheck.ReportManager;
import org.ensembl.healthcheck.Team;
import org.ensembl.healthcheck.testcase.MultiDatabaseTestCase;
import org.ensembl.healthcheck.util.CollectionUtils;
import org.ensembl.healthcheck.util.ConnectionBasedSqlTemplateImpl;
import org.ensembl.healthcheck.util.TemplateBuilder;

/**
 * EG specific test that does not use species to
 * 
 * @author dstaines
 * 
 */
public class MultiDbSpeciesNames extends MultiDatabaseTestCase {

	public MultiDbSpeciesNames() {
		super();
		setTeamResponsible(Team.ENSEMBL_GENOMES);
		addAppliesToType(DatabaseType.CORE);
	}

	private final static String STABLE_ID = "select meta_value from meta where meta_key=?";
	private final static String[] META_KEYS = { "species.production_name",
			"species.alias", "species.db_name" };

	private final static String NAME_HOLDER = "name";
	private final static String KEY_HOLDER = "metaKey";
	private final static String FIX_SQL = "update meta name, meta ass set "
			+ "name.meta_value=concat(name.meta_value,'_',ass.meta_value) "
			+ "where name.species_id=ass.species_id and ass.meta_key='assembly.name' "
			+ "and name.meta_value='$" + NAME_HOLDER
			+ "$' and name.meta_key='$" + KEY_HOLDER + "$'";
	private final static String DELETE_SQL = "delete from meta name where name.meta_value='$"
			+ NAME_HOLDER + "$' and name.meta_key='$" + KEY_HOLDER + "$'";

	@Override
	public boolean run(DatabaseRegistry dbr) {
		boolean result = true;
		for (String metaKey : META_KEYS) {
			Map<String, Collection<String>> names = CollectionUtils
					.createHashMap();
			for (DatabaseRegistryEntry coreDb : dbr.getAll(DatabaseType.CORE)) {
				ReportManager.info(this, coreDb.getConnection(), "Checking "
						+ metaKey + " stable ID for " + coreDb.getName());
				ConnectionBasedSqlTemplateImpl template = new ConnectionBasedSqlTemplateImpl(
						coreDb.getConnection());
				int checked = 0;
				int dups = 0;
				for (String name : template.queryForDefaultObjectList(
						STABLE_ID, String.class, metaKey)) {
					checked++;
					Collection<String> dbs = names.get(name);
					if (dbs == null) {
						dbs = CollectionUtils.createArrayList(1);
						names.put(name, dbs);
					} else {
						dups++;
						ReportManager
								.problem(
										this,
										coreDb.getConnection(),
										"The name "
												+ name
												+ " (meta key "
												+ metaKey
												+ ") has been found in the following core databases :"
												+ StringUtils.join(dbs, ", "));
						ReportManager.info(this,
										coreDb.getConnection(), "SQL to fix: "+TemplateBuilder.template(FIX_SQL, NAME_HOLDER,name, KEY_HOLDER,metaKey));
						ReportManager.info(this,
								coreDb.getConnection(), "SQL to remove: "+TemplateBuilder.template(DELETE_SQL, NAME_HOLDER,name, KEY_HOLDER,metaKey));
						result = false;
					}
					dbs.add(coreDb.getName());
				}
				ReportManager.info(this, coreDb.getConnection(),
						"Checked " + checked + " " + metaKey + " names for "
								+ coreDb.getName() + ": found " + dups
								+ " duplicates");
			}
		}
		return result;
	}

}

@@ -2793,24 +2793,69 @@
 bs, %22, %22));%0A
+%09%09%09%09%09%09if(metaKey.equals(%22species.alias%22)) %7B%0A%09
 %09%09%09%09%09%09Report
@@ -2869,25 +2869,24 @@
 .info(this,%0A
-%09
 %09%09%09%09%09%09%09%09%09cor
@@ -2918,11 +2918,14 @@
  to 
-fix
+remove
 : %22+
@@ -2949,19 +2949,22 @@
 emplate(
-FIX
+DELETE
 _SQL, NA
@@ -2996,25 +2996,48 @@
 R,metaKey));
-%0A
+%09%09%09%09%09%09%09%0A%09%09%09%09%09%09%7D else %7B%0A%09
 %09%09%09%09%09%09Report
@@ -3047,32 +3047,33 @@
 ager.info(this,%0A
+%09
 %09%09%09%09%09%09%09%09coreDb.g
@@ -3088,38 +3088,35 @@
 tion(), %22SQL to 
-remove
+fix
 : %22+TemplateBuil
@@ -3120,38 +3120,35 @@
 uilder.template(
-DELETE
+FIX
 _SQL, NAME_HOLDE
@@ -3176,16 +3176,31 @@
 taKey));
+%09%09%09%09%09%09%09%0A%09%09%09%09%09%09%7D
 %0A%09%09%09%09%09%09r

make useful sql more useful