package db.migration;

import org.flywaydb.core.api.migration.spring.SpringJdbcMigration;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Objects;

/**
 * Created by emma on 07/07/2015.
 *
 * @author emma
 *         <p>
 *         Script related to JIRA ticket:https://www.ebi.ac.uk/panda/jira/browse/GOCI-924. Aim is to remove duplicates
 *         from the LOCATION table that were created as a result of V1_9_9_060__Populate_locations_and_snp_location. The
 *         SQL query in V1_9_9_060__Populate_locations_and_snp_location did not take account of duplicate SNPs.
 */
public class V1_9_9_061__Remove_duplicate_locations implements SpringJdbcMigration {

    private static final String SELECT_LOCATION_DUPLICATES =
            "SELECT REGION_ID, CHROMOSOME_NAME, CHROMOSOME_POSITION, COUNT(1) as CNT\n" +
                    "FROM LOCATION\n" +
                    "GROUP BY REGION_ID, CHROMOSOME_NAME, CHROMOSOME_POSITION\n" +
                    "HAVING COUNT(1) > 1\n" +
                    "ORDER BY CNT DESC";

    private static final String SELECT_MATCHING_LOCATIONS =
            "SELECT ID from LOCATION WHERE REGION_ID = ? AND CHROMOSOME_NAME = ? AND CHROMOSOME_POSITION = ? ORDER BY ID";

    private static final String SELECT_SNPS_LINKED_TO_LOCATION =
            "SELECT SNP_ID from SNP_LOCATION WHERE LOCATION_ID = ?";

    private static final String UPDATE_SNP_LOCATION =
            "UPDATE SNP_LOCATION SET LOCATION_ID  = ? WHERE LOCATION_ID = ? AND SNP_ID = ?";

    private static final String DELETE_FROM_LOCATION = "DELETE FROM LOCATION WHERE ID =?";

    @Override public void migrate(JdbcTemplate jdbcTemplate) throws Exception {

        // Query for all duplicates
        jdbcTemplate.query(SELECT_LOCATION_DUPLICATES, (resultSet, i) -> {
            Long regionId = resultSet.getLong(1);
            String chromosomeName = resultSet.getString(2).trim();
            String chromosomePosition = resultSet.getString(3).trim();

            // Get a list of all location ids that have duplicate region_id, chromosome name and chromosome positions
            List<Long> duplicateLocationIds =
                    jdbcTemplate.queryForList(SELECT_MATCHING_LOCATIONS,
                                              Long.class,
                                              regionId,
                                              chromosomeName,
                                              chromosomePosition);

            // Keep the first id, this will be our only remaining location
            Long locationIdToKeep = duplicateLocationIds.get(0);

            for (Long locationId : duplicateLocationIds) {
                if (!Objects.equals(locationId, locationIdToKeep)) {
                    // Get all SNP linked to this location
                    List<Long> snpIds = jdbcTemplate.queryForList(SELECT_SNPS_LINKED_TO_LOCATION,
                                                                  Long.class,
                                                                  locationId);

                    // Link SNP to remaining location
                    for (Long snpId : snpIds) {
                        jdbcTemplate.update(UPDATE_SNP_LOCATION, locationIdToKeep, locationId, snpId);
                    }

                    // Delete location
                    jdbcTemplate.update(DELETE_FROM_LOCATION, locationId);
                }
            }

            return null;
        });
    }
}

@@ -1862,16 +1862,29 @@
 ong(1);%0A
+            %0A
         
@@ -1937,87 +1937,309 @@
 g(2)
-.trim();%0A            String chromosomePosition = resultSet.getString(3).trim();
+;%0A            if (chromosomeName != null) %7B%0A                chromosomeName = chromosomeName.trim();%0A            %7D%0A            String chromosomePosition = resultSet.getString(3);%0A            if (chromosomePosition != null) %7B%0A                chromosomePosition = chromosomePosition.trim();%0A            %7D
 %0A%0A  

Update to deal with null values for chromosome name and position