/*
 * Created on 09-Mar-2004
 *
 * To change the template for this generated file go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
package org.ensembl.healthcheck.testcase.generic;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

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.SingleDatabaseTestCase;

/**
 * Check if any chromosomes that have different lengths in karyotype & seq_region tables.
 */
public class Karyotype extends SingleDatabaseTestCase {

	/**
	 * Create a new Karyotype test case.
	 */
	public Karyotype() {

		addToGroup("post_genebuild");
		addToGroup("release");
		addToGroup("post-compara-handover");
		
		setDescription("Check that karyotype and seq_region tables agree");
		setTeamResponsible(Team.CORE);

	}

	/**
	 * This only applies to core and Vega databases.
	 */
	public void types() {

		removeAppliesToType(DatabaseType.OTHERFEATURES);
		removeAppliesToType(DatabaseType.ESTGENE);
		removeAppliesToType(DatabaseType.VEGA);
		removeAppliesToType(DatabaseType.CDNA);
		removeAppliesToType(DatabaseType.RNASEQ);

	}

	/**
	 * Run the test.
	 * 
	 * @param dbre
	 *          The database to use.
	 * @return true if the test passed.
	 * 
	 */
	public boolean run(DatabaseRegistryEntry dbre) {

		boolean result = true;

		Connection con = dbre.getConnection();

		// don't check for empty karyotype table - this is done in EmptyTables
		// meta_coord check also done in MetaCoord

		// The seq_region.length and karyotype.length should always be the
		// same.
		// The SQL returns failures

		String karsql = "SELECT sr.name, max(kar.seq_region_end), sr.length " + "FROM seq_region sr, karyotype kar " + "WHERE sr.seq_region_id=kar.seq_region_id " + "GROUP BY kar.seq_region_id "
				+ "HAVING sr.length <> MAX(kar.seq_region_end)";
		int count = 0;
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(karsql);
			if (rs != null) {
				while (rs.next() && count < 50) {
					count++;
					String chrName = rs.getString(1);
					int karLen = rs.getInt(2);
					int chrLen = rs.getInt(3);
					String prob = "";
					int bp = 0;
					if (karLen > chrLen) {
						bp = karLen - chrLen;
						prob = "longer";
					} else {
						bp = chrLen - karLen;
						prob = "shorter";
					}
					result = false;
					ReportManager.problem(this, con, "Chromosome " + chrName + " is " + bp + "bp " + prob + " in the karyotype table than " + "in the seq_region table");
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (count == 0) {
			ReportManager.correct(this, con, "Chromosome lengths are the same" + " in karyotype and seq_region tables");
		}

		return result;

	} // run

} // Karyotype

@@ -564,16 +564,62 @@
 stCase;%0A
+import org.ensembl.healthcheck.util.DBUtils;%0A%0A
 %0A/**%0A * 
@@ -1601,16 +1601,66 @@
 ion();%0A%0A
+                result &= karyotypeExists(dbre);%0A%0A
 %09%09// don
@@ -3003,16 +3003,16 @@
 esult;%0A%0A
-
 %09%7D // ru
@@ -3014,16 +3014,927 @@
 // run%0A%0A
+%0A        protected boolean karyotypeExists(DatabaseRegistryEntry dbre) %7B%0A                Connection con = dbre.getConnection();%0A                boolean result = true;%0A                String sqlCS = %22SELECT count(*) FROM coord_system WHERE name = 'chromosome'%22;%0A                int karyotype = DBUtils.getRowCount(con, sqlCS);%0A                if (karyotype %3E 0) %7B%0A                        String sqlAttrib = %22SELECT count(*) FROM seq_region_attrib sa, attrib_type at WHERE at.attrib_type_id = sa.attrib_type_id AND code = 'karyotype_rank'%22;%0A                        int attrib = DBUtils.getRowCount(con, sqlAttrib);%0A                        if (attrib == 0) %7B%0A                                result = false;%0A                                ReportManager.problem(this, con, %22Chromosome entry exists but no karyotype attrib is present%22);%0A                        %7D%0A                %7D %0A        return result;%0A        %7D%0A%0A
 %7D // Kar

test added to look for the presence of karyotype sequence attributes