import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.HashMap;
 
import org.neo4j.graphdb.DynamicRelationshipType;
import org.neo4j.graphdb.GraphDatabaseService;
import org.neo4j.graphdb.Node;
import org.neo4j.graphdb.index.BatchInserterIndex;
import org.neo4j.graphdb.index.BatchInserterIndexProvider;
import org.neo4j.helpers.collection.MapUtil;
import org.neo4j.index.impl.lucene.LuceneBatchInserterIndexProvider;
import org.neo4j.kernel.impl.batchinsert.BatchInserter;
import org.neo4j.kernel.impl.batchinsert.BatchInserterImpl;
 
public class Importer {
 
    public static void main(String[] argv) {
        BatchInserter inserter = new BatchInserterImpl("target/neo4jdb-batchinsert");
        BatchInserterIndexProvider indexProvider = new LuceneBatchInserterIndexProvider(inserter);
        BatchInserterIndex labels = indexProvider.nodeIndex("labels", MapUtil.stringMap("type", "exact"));
        labels.setCacheCapacity("Label", 100000);
 
 
 
        System.out.println("-------- PostgreSQL "
                + "JDBC Connection Testing ------------");
 
        try {
 
            Class.forName("org.postgresql.Driver");
 
        } catch (ClassNotFoundException e) {
 
            System.out.println("Where is your PostgreSQL JDBC Driver? "
                    + "Include in your library path!");
            e.printStackTrace();
 
        }
 
        System.out.println("PostgreSQL JDBC Driver Registered!");
 
        Connection conn = null;
 
        try {
 
            conn = DriverManager.getConnection(
                    "jdbc:postgresql://127.0.0.1:5432/contractDashboard",
                    "postgres", "snmc");
 
        } catch (SQLException e) {
 
            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
 
        }
 
        if (conn != null) {
            System.out.println("You made it, take control your database now!");
        } else {
            System.out.println("Failed to make connection!");
        }
        try {
            // Print all warnings
            for (SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning()) {
                System.out.println("SQL Warning:");
                System.out.println("State  : " + warn.getSQLState());
                System.out.println("Message: " + warn.getMessage());
                System.out.println("Error  : " + warn.getErrorCode());
            }
 
            // Get a statement from the connection
            Statement stmt = conn.createStatement();
 
            // Execute the query
            ResultSet rs = stmt.executeQuery("SELECT contractnotice.\"agencyName\", "
                    + "  contractnotice.\"supplierABN\",contractnotice.\"supplierName\",sum(value) as sum "
                    + "FROM  public.contractnotice  GROUP BY contractnotice.\"agencyName\", "
                    + "  contractnotice.\"supplierABN\",contractnotice.\"supplierName\"");
            String previousAgency = "";
            GraphDatabaseService gds = inserter.getGraphDbService();
            HashMap<String, Long> supplierIDs = new HashMap<String, Long>();
            HashMap<String, Long> agencyIDs = new HashMap<String, Long>();
 
            // Loop through the result set
            while (rs.next()) {
                long supplierID, agencyID;
                String supplierKey;
                if (agencyIDs.get(rs.getString("agencyName")) == null) {
                    Node myNode = gds.createNode();
                    myNode.setProperty("Label", rs.getString("agencyName"));
                    myNode.setProperty("type", "agency");
                    agencyIDs.put(rs.getString("agencyName"), myNode.getId());
                    if (myNode.getId() % 100 == 0) {
                        System.out.println("Agency " + myNode.getId());
                    }
                }
                agencyID = agencyIDs.get(rs.getString("agencyName"));
 
 
                if (rs.getString("supplierABN") != "0" && rs.getString("supplierABN") != "") {
                    supplierKey = rs.getString("supplierABN");
                } else {
                    supplierKey = rs.getString("supplierName");
                }
                // inject some data 
                if (supplierIDs.get(supplierKey) == null) {
                    Node myNode = gds.createNode();
                    myNode.setProperty("Label", rs.getString("supplierName"));
                    myNode.setProperty("type", "supplier");
                    supplierIDs.put(supplierKey, myNode.getId());
                    if (myNode.getId() % 1000 == 0) {
                        System.out.println("Supplier " + myNode.getId());
                    }
                }
                supplierID = supplierIDs.get(supplierKey);
 
 
                long rel = inserter.createRelationship(agencyID, supplierID,
                        DynamicRelationshipType.withName("KNOWS"), null);
                inserter.setRelationshipProperty(rel, "Weight", rs.getDouble("sum"));
 
            }
            // Close the result set, statement and the connection
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            System.out.println("SQL Exception:");
 
            // Loop through the SQL Exceptions
            while (se != null) {
                System.out.println("State  : " + se.getSQLState());
                System.out.println("Message: " + se.getMessage());
                System.out.println("Error  : " + se.getErrorCode());
 
                se = se.getNextException();
            }
        }
//make the changes visible for reading, use this sparsely, requires IO!
        labels.flush();
 
// Make sure to shut down the index provider
        indexProvider.shutdown();
        inserter.shutdown();
    }
}