public class MainNavigation {

    private NavigationTranslator translator = new NavigationTranslator();
    private static String wpUrl = "sql-hostname";
    private static String wpUser = "user";
    private static String wpPassword = "password";

    public static void main(String[] args) {

        MainNavigation navigation = new MainNavigation();
        Connection connection = navigation.connect(wpUrl, connectionProperties());
        navigation.createNavigationMenu(connection);


    }

    private Connection connect(String host, Properties connectionProperties) {
        try {
            return DriverManager.getConnection(host, connectionProperties);
        } catch (SQLException e) {
            throw Throwables.propagate(e);
        }
    }

    private void createNavigationMenu(Connection connection) {
        DSLContext context = DSL.using(connection);
        createNavigation(context);

    }


    private void createNavigation(DSLContext context) {

        // Fist we will create a new term in stage_terms table and insert it.

        ImmutableMap<Field<?>, Object> navTerm  = translator.navigationTerm();
        context.insertInto(table("stage_terms")).set(navTerm).execute();

        String termIdStatement = "select term_id from stage_terms order by term_id desc limit 1";
        Record termIdRecord = context.fetch(termIdStatement).get(0);
        Integer termId = termIdRecord.getValue("term_id", Integer.class);

        ImmutableMap<Field<?>, Object> navTermTaxonomy  = translator.navigationTermTaxonomy(termId);
        context.insertInto(table("stage_term_taxonomy")).set(navTermTaxonomy).execute();


        String taxonomyIdStatement = "select term_taxonomy_id from stage_term_taxonomy order by term_taxonomy_id desc limit 1";
        Record taxonomyIdRecord = context.fetch(taxonomyIdStatement).get(0);
        Integer taxonomyId = taxonomyIdRecord.getValue("term_taxonomy_id", Integer.class);

        String maxIdStatement = "select ID from stage_posts order by ID desc limit 1";
        Record maxRecord = context.fetch(maxIdStatement).get(0);

        Integer id = maxRecord.getValue("ID", Integer.class) + 1;

        ImmutableList<ImmutableMap<Field<?>, Object>> navigationPosts = translator.navigationPosts(
                id);
        for (ImmutableMap<Field<?>, Object> post : navigationPosts) {
            context.insertInto(table("stage_posts")).set(post).execute();
        }

        ImmutableList<ImmutableMap<Field<?>, Object>> navList = translator.postMetaData();

        for (ImmutableMap<Field<?>, Object> nav : navList) {
            context.insertInto(table("stage_postmeta")).set(nav).execute();
        }

        Field postType = field("post_type");
        Result<Record> records = context
                .select()
                .from(table("stage_posts"))
                .where(postType.equal("nav_menu_item"))
                .fetch();

        records.forEach(navItem -> {
            ImmutableMap<Field<?>, Object> relationship = translator.navigationRelationship(navItem.getValue(
                    "ID",
                    Integer.class
            ),
                    taxonomyId);
            context.insertInto(table("stage_term_relationships")).set(relationship).execute();
        });

    }

    private static Properties connectionProperties() {
        Properties connectionProperties = new Properties();
        connectionProperties.put("user", wpUser);
        connectionProperties.put("password", wpPassword);
        return connectionProperties;
    }