package deleteme;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.io.IOUtils;

public class DeleteMe31 {

    public static void main(String[] args) throws Exception {
        String url = "jdbcurlhere";
        String user = "usernamehere";
        String pass = "passwordhere";
        long start = System.currentTimeMillis();

        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
        Connection con = DriverManager.getConnection(url, user, pass);
        List<Integer> subtypes = new ArrayList<Integer>();
        {
            subtypes.add(144);
            subtypes.add(749);
        }
        int ancestorid = 107297448;
        discover(con, ancestorid, subtypes);
        System.out.printf("time: %s%n", System.currentTimeMillis()-start);
    }

    private static void discover(Connection con, Integer ancestor, List<Integer> subtypes) throws Exception {
        final Map<Integer, Row> mapNodeInfo = new HashMap<Integer, Row>();
        final List<Integer> nodeIds = new ArrayList<Integer>();

        final PreparedStatement stmtAncestorNodes = con.prepareStatement("select d.dataid, d.name, abs(d.parentid) parentid, d.subtype from rmnausr.dtree d join rmnausr.dtreeancestors da on d.dataid = da.dataid where da.ancestorid = ?");
        stmtAncestorNodes.setInt(1, ancestor);

        final ResultSet rs = stmtAncestorNodes.executeQuery();
        int counter = 0;
        long start = System.currentTimeMillis();
        while (rs.next()) {
            final Row row = new Row(rs.getInt("dataid"), rs.getString("name"), rs.getInt("parentid"), rs.getInt("subtype"));
            if (subtypes.contains(row.getSubtype())) {
                nodeIds.add(row.getDataid());
            }
            mapNodeInfo.put(row.getDataid(), row);
            counter = rs.getRow();
            if ((counter % 10000) == 0) {
                long rate = (counter/((System.currentTimeMillis()-start)/1000));
                System.out.printf("ancestor child nodes read: %s items per second: %s%n", counter, rate);
            }
        }
        rs.close();
        stmtAncestorNodes.close();
        con.close();
        System.out.printf("ancestor child nodes read: %s%n", counter);

        final File output = new File("output.csv");
        final OutputStream os = new FileOutputStream(output);
        counter = 0;
        for (Integer nodeId : nodeIds) {
            final String line = String.format("%s,\"%s\"", 
                    nodeId,
                    getPathFromAncestorFor(nodeId, mapNodeInfo).replaceAll("\"", "\"\"")
            );
            IOUtils.write(line, os);
            if ((++counter % 1000) == 0) {
                System.out.printf("path nodes written: %s%n", counter);
            }
        }
        System.out.printf("path nodes written: %s%n", counter);
        os.close();
    }

    private static String getPathFromAncestorFor(final Integer nodeId, final Map<Integer, Row> mapNodeInfo) {
        final StringBuilder sb = new StringBuilder();
        Row row;
        int nextId = nodeId;
        do {
            row = mapNodeInfo.get(nextId);
            if (null == row) {
                break;
            }
            if (sb.length() > 0) {
                sb.insert(0, ":");
            }
            sb.insert(0, row.getName());
            nextId = row.getParentid();
        } while (true);
        return sb.toString();
    }
}