package com.zenplanner;

import com.google.common.base.Charsets;
import com.google.common.base.Joiner;
import com.google.common.io.Resources;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

public class App {
    private static final String filterCol = "partitionId";
    private static final List<String> smallTypes = Arrays.asList(new String[]{
            "uniqueidentifier", "bigint", "date", "datetime", "datetime2", "smalldatetime", "tinyint", "smallint",
            "int", "decimal", "bit", "money", "smallmoney", "char", "float"
    });
    private static final List<String> bigTypes = Arrays.asList(new String[]{
            "varchar", "nvarchar", "text"
    });

    public static void main(String[] args) throws Exception {
        String filterValue = args[0];
        String srcCon = args[1];
        String dstCon = args[2];

        Class.forName("net.sourceforge.jtds.jdbc.Driver");

        // Get tables and columns
        try (Connection scon = DriverManager.getConnection(srcCon)) {
            try (Connection dcon = DriverManager.getConnection(dstCon)) {
                Map<String, Table> srcTables = filterTables(getTables(scon));
                Map<String, Table> dstTables = filterTables(getTables(dcon));
                for (Table srcTable : srcTables.values()) {
                    if(!dstTables.containsKey(srcTable.getName())) {
                        continue;
                    }
                    Table dstTable = dstTables.get(srcTable.getName());
                    compTables(srcTable, dstTable);
                }
            }
        }
    }

    private static void compTables(Table srcTable, Table dstTable) {
        Table lcd = findLcd(srcTable, dstTable);
        String sql = writeHashedQuery(lcd);
    }

    private static Table findLcd(Table srcTable, Table dstTable) {
        Table table = new Table(srcTable.getName());
        Set<String> colNames = new HashSet<>();
        colNames.addAll(srcTable.keySet());
        colNames.addAll(dstTable.keySet());
        for(String colName : colNames) {
            if(!srcTable.containsKey(colName) || !dstTable.containsKey(colName)) {
                continue;
            }
            table.put(colName, srcTable.get(colName));
        }
        return table;
    }

    private static String writeHashedQuery(Table table) {
        List<String> colNames = new ArrayList<>();
        for (Column col : table.values()) {
            colNames.add(getColSelect(col));
        }
        String selectClause = Joiner.on(",\n\t").join(colNames);
        String sql = String.format("select\n\t%s from [%s]\nwhere [%s]=?", selectClause, table.getName(), filterCol);
        return sql;
    }

    private static String getColSelect(Column col) {
        if (smallTypes.contains(col.getDataType().toLowerCase())) {
            return "[" + col.getColumnName() + "]";
        }
        if (bigTypes.contains(col.getDataType().toLowerCase())) {
            return String.format("HASHBYTES('md5', [%s]) as [%s]", col.getColumnName(), col.getColumnName());
        }
        throw new RuntimeException("Unknown type: " + col.getDataType());
    }

    // Removes tables that don't have a primary key, or aren't related to partitionId
    private static Map<String, Table> filterTables(Map<String, Table> in) {
        Map<String, Table> out = new HashMap<>();
        for (Map.Entry<String, Table> entry : in.entrySet()) {
            boolean hasPk = false;
            boolean hasCol = false;
            String name = entry.getKey();
            Table table = entry.getValue();
            for (Column col : table.values()) {
                if (col.isPrimaryKey()) {
                    hasPk = true;
                }
                if (filterCol.equalsIgnoreCase(col.getColumnName())) {
                    hasCol = true;
                }
            }
            if (hasPk && hasCol) {
                out.put(name, table);
            }
        }
        return out;
    }

    private static Map<String, Table> getTables(Connection con) throws Exception {
        // Collect the tables
        Map<String, Table> tables = new HashMap<>();
        try (Statement stmt = con.createStatement()) {
            String sql = Resources.toString(Resources.getResource("GetTables.sql"), Charsets.UTF_8);
            try (ResultSet rs = stmt.executeQuery(sql)) {
                while (rs.next()) {
                    String tableName = rs.getString("table_name");
                    if (!tables.containsKey(tableName)) {
                        tables.put(tableName, new Table(tableName));
                    }
                    Table table = tables.get(tableName);

                    Column col = new Column();
                    String colName = rs.getString("column_name");
                    col.setColumnName(colName);
                    col.setDataType(rs.getString("data_type"));
                    col.setPrimaryKey(rs.getBoolean("primary_key"));
                    table.put(colName, col);
                }
            }
        }
        return tables;
    }
}

@@ -2461,24 +2461,69 @@
 ayList%3C%3E();%0A
+        List%3CString%3E pk = new ArrayList%3C%3E();%0A
         for 
@@ -2546,32 +2546,140 @@
 ble.values()) %7B%0A
+            if(col.isPrimaryKey()) %7B%0A                pk.add(%22%5B%22 + col.getColumnName() + %22%5D%22);%0A            %7D%0A
             colN
@@ -2782,16 +2782,70 @@
 Names);%0A
+        String orderClause = Joiner.on(%22,%22).join(pk);%0A
         
@@ -2908,18 +2908,47 @@
 e %5B%25s%5D=?
-%22,
+%5Cnorder by %25s%22,%0A               
  selectC
@@ -2980,16 +2980,29 @@
 ilterCol
+, orderClause
 );%0A     
@@ -3326,14 +3326,38 @@
 d5',
+ convert(nvarchar(max),
  %5B%25s%5D)
+)
  as 
@@ -3362,16 +3362,36 @@
 s %5B%25s%5D%22,
+%0A                   
  col.get

Generating better queries