/*
 * Copyright Stuart Rex King 2010
 *
 */

package com.wenzani.mongo;

import com.mongodb.*;
import com.mongodb.util.JSON;
import org.apache.commons.lang.exception.ExceptionUtils;
import org.codehaus.jackson.map.ObjectMapper;

import java.io.IOException;
import java.net.UnknownHostException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PlacesMain {

    private static final String PLACE_SELECT = "SELECT * FROM places LIMIT %d, %d;";
    private static final String TAGS_SELECT = "SELECT tag FROM tags where place_id = ?;";
    private static final String CATEGORIES_SELECT = "SELECT category FROM categories where place_id = ?;";
    private static final String WENZANI = "wenzani";
    private static final String PLACES = "places";
    private final ObjectMapper objectMapper = new ObjectMapper();

    public static void main(String[] args) {
        PlacesMain placesMain = new PlacesMain();
        placesMain.createIndexes();
        placesMain.run();
    }

    private void createIndexes() {
        Mongo mongo = null;
        try {
            mongo = new Mongo();
            DB db = mongo.getDB(WENZANI);
            DBCollection collection = db.getCollection(PLACES);
            collection.ensureIndex(new BasicDBObject("lat_lng", "2d"));
        } catch (UnknownHostException e) {
            System.out.println(ExceptionUtils.getFullStackTrace(e));
        }

        mongo.close();
    }

    public void run() {
        insertPlace();
    }

    public void insertPlace() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/places?user=places&password=places");
            for (int i = 0; i < 4769976; i = i + 500) {
                int start = i;
                int end = 499;
                String sql = String.format(PLACE_SELECT, start, end);
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(sql);
                List<Place> places = new ArrayList<Place>();
                while (resultSet.next()) {
                    int id = resultSet.getInt(1);
                    Place place = new Place();
                    place.setSource_id(resultSet.getInt(2));
                    place.setTitle(resultSet.getString(3));

                    List<Double> latLng = new ArrayList<Double>();
                    latLng.add(Double.valueOf(resultSet.getString(4)));
                    latLng.add(Double.valueOf(resultSet.getString(5)));
                    place.setLat_long(latLng);

                    place.setType(resultSet.getString(6));
                    place.setStreetAddress(resultSet.getString(7));
                    place.setLocality(resultSet.getString(8));
                    place.setRegion(resultSet.getString(9));
                    place.setPostalCode(resultSet.getString(10));
                    place.setPhoneNumber(resultSet.getString(11));
                    place.setSource(resultSet.getString(12));

                    List<String> tags = getTags(id, connection);
                    place.setTags(tags);

                    List<String> categories = getCategories(id, connection);
                    place.setCategories(categories);

                    System.out.println(place);
                    places.add(place);
                }
                resultSet.close();
                statement.close();
                addPlacesToMongo(places);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            if (null != connection) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    System.out.println(e);
                }
            }
        }
    }

    private void addPlacesToMongo(List<Place> places) {
        try {
            Mongo mongo = new Mongo();
            DB db = mongo.getDB(WENZANI);
            DBCollection collection = db.getCollection(PLACES);

            List<DBObject> dbObjects = createObject(places);
            collection.insert(dbObjects);

            mongo.close();
        } catch (UnknownHostException e) {
            System.out.println(ExceptionUtils.getFullStackTrace(e));
        }
    }

    private List<DBObject> createObject(List<Place> places) {
        List<DBObject> dbObjects = new ArrayList<DBObject>();

        for (Place place : places) {
            try {
                String json = objectMapper.writeValueAsString(place);
                DBObject dbObject = (DBObject) JSON.parse(json);
                dbObjects.add(dbObject);
            } catch (IOException e) {
                System.out.println(ExceptionUtils.getFullStackTrace(e));
            }
        }

        return dbObjects;
    }

    private List<String> getCategories(int id, Connection connection) {
        List<String> categories = new ArrayList<String>();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(CATEGORIES_SELECT);
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                categories.add(resultSet.getString(1));
            }
            resultSet.close();
            preparedStatement.close();
        } catch (SQLException e) {
            System.out.println(e);
        }
        return categories;
    }

    private List<String> getTags(int id, Connection connection) {
        List<String> tags = new ArrayList<String>();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(TAGS_SELECT);
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                tags.add(resultSet.getString(1));
            }
            resultSet.close();

            preparedStatement.close();
        } catch (SQLException e) {
            System.out.println(e);
        }
        return tags;
    }

}