#!/usr/bin/ruby
require 'postgres'
 
require 'highline.rb'
include HighLine
 
require 'rubygems'
require 'json'
require 'net/http'
def cbr_geocode(query)
   base_url = "http://geocoding.cloudmade.com/daa03470bb8740298d4b10e3f03d63e6/geocoding/v2/find.js?query="
   url = "#{base_url}#{URI.encode(query)}&bbox=-35.47,148.83,-35.16,149.25&return_location=true"
   resp = Net::HTTP.get_response(URI.parse(url))
   data = resp.body
 
   # we convert the returned JSON data to native Ruby
   # data structure - a hash
   result = JSON.parse(data)
 
   # if the hash has 'Error' as a key, we raise an error
   if result.has_key? 'Error'
      raise "web service error"
   end
   return result
end
class Array
 
   def find_dups
      inject(Hash.new(0)) { |h,e| h[e] += 1; h }.select { |k,v| v > 1 }.collect { |x| x.first }
   end
end
 
require 'yaml'
require 'pp'
Dir.chdir("output")
 
def getTimePoints()
        $time_points = []
        $time_points_sources = Hash.new([])
        Dir.glob("*.yml") { |file|
                timetable = YAML::load_file(file)
                $time_points = $time_points | timetable["time_points"]
                timetable["time_points"].each do |timepoint|
                        $time_points_sources[timepoint] = $time_points_sources[timepoint] | [ file ]
                end
        }
end
 
getTimePoints()
$time_points.sort!
 
connbus = PGconn.connect("localhost", 5432, '', '', "bus", "postgres", 
"snmc")
connosm = PGconn.connect("localhost", 5432, '', '', "openstreetmap", 
"postgres", "snmc")
 
if ask_if("Insert Timing Point names to database?")
        $time_points.each do |time_point|
                begin
                        time_point = time_point.gsub(/\\/, '\&\&').gsub(/'/, "''").gsub("St", "%")
                        res = connbus.exec("INSERT INTO timing_point (name) VALUES ('#{time_point}')")
                        puts "Put '#{time_point}' into DB"
                rescue PGError => e
                        puts "Error inserting '#{time_point}' to DB #{e}"
                        #conn.close() if conn
                end
        end
end
 
 
if ask_if("Fill null Timing Points from OSM bus_stop database?")
# TODO Where there's a "Cnr" or a \/ or a &, Look for 2 ways or nodes and average the closest two!
        begin
                null_points = connbus.exec('SELECT name FROM timing_point WHERE lat IS null OR lng IS null;')
        rescue PGError => e
                puts "Error selecting null points from DB #{e}"
                #conn.close() if conn
        end
 
        null_points.each do |null_point_name|
                begin
                        name = null_point_name.to_s.gsub(/\\/, '\&\&').gsub(/'/, "''")
                        pp name
                        search_name = ask("Hmm, if we're still looking, the name is probably wrong. What's the right name?", :string, :default => name)
                        matching_nodes = connosm.exec("Select * FROM (SELECT * from current_node_tags,
                        (Select id as ctagid FROM current_node_tags WHERE  v LIKE '%#{search_name}%') as a 
                        where a.ctagid = current_node_tags.id) as ctags INNER JOIN current_nodes ON 
                        ctags.id=current_nodes.id")
                rescue PGError => e
                        puts "Error selecting matching bus stops from DB #{e}"
                        #conn.close() if conn
                end
                suggested_nodes = Hash.new()
 
                matching_nodes.each do |matching_node_row|
                        #pp matching_node_row
                        # 0 = id
                        # 1 = k
                        # 2 = v
                        # 3,4 = redundant ids
                        # 5 = lat*100000
                        # 6 = lng*100000
                        suggested_node = suggested_nodes.fetch(matching_node_row[0], {'lat' => Float(matching_node_row[5])/10000000,
                                                                                      'lng' => Float(matching_node_row[6])/10000000})
                        if matching_node_row[1] == "ref"
                                matching_node_row[1] = "loc_ref"
                        end
                        suggested_node[matching_node_row[1]] = matching_node_row[2]
                        suggested_nodes[matching_node_row[0]] = suggested_node
                end
                pp suggested_nodes
                nodeID = ask("Enter selected node ID:", :string) 
                if suggested_nodes.has_key?(nodeID)
                        node = suggested_nodes.fetch(nodeID)
                        guess = ask_if("Is this a guess?")
                        puts "Location #{node["lat"]},#{node["lng"]} for #{null_point_name}"
                        begin
                                res = connbus.exec("UPDATE timing_point SET lat = #{node["lat"]*10000000}, lng = 
#{node["lng"]*10000000},osm_node = #{nodeID}" + (node.has_key?("loc_ref") ? ",loc_ref = #{node["loc_ref"]}" : "") + ",guess = #{guess} WHERE name 
= '#{name}'")
                                puts "Put '#{null_point_name}' into DB"
                        rescue PGError => e
                                puts "Error inserting '#{null_point_name}' to DB #{e}"
                                ask_if("Continue?")
                                #conn.close() if conn
                        end
                else
                        puts "Uhh, there was no suggestion ID like that. Try again next time!"
                end
                
                puts "Hmm, so maybe this isn't a point? Maybe it's a way.... like a street or something? Is it a 'street' or a 'corner' or nothing?"
                whatisit = ask("So what is it:", :string, :default => "corner") 
                if whatisit == "street"
                        begin
                                name = null_point_name.to_s.gsub(/\\/, '\&\&').gsub(/'/, "''")
                                pp "#{name} (ways)"
                                search_name = ask("Streets tend to have pretty bad quality data, What's the real name of the street?", :string, :default => name)
                                matching_ways = connosm.exec("Select avg(latitude), avg(longitude), name FROM (
                                SELECT * from current_way_nodes,(Select id as ctagid, v as name FROM current_way_tags WHERE k = 'name' AND v LIKE 
                                '%#{search_name}%') as a where a.ctagid = current_way_nodes.id) as ctags INNER JOIN current_nodes ON ctags.node_id=current_nodes.id
                                GROUP BY name")
                        rescue PGError => e
                                puts "Error selecting matching ways from DB #{e}"
                                #conn.close() if conn
                        end
                        suggested_ways = Hash.new()
 
                        matching_ways.each do |matching_way_row|
                                #pp matching_way_row
                                # 0 = lat*100000
                                # 1 = lng*100000
                                # 2 = name
                                suggested_way = suggested_ways.fetch(matching_way_row[2], {'lat' => Float(matching_way_row[0])/10000000,
                                                                                              'lng' => Float(matching_way_row[1])/10000000})
                                suggested_way['name'] = suggested_way['name'] 
                                suggested_ways[matching_way_row[2]] = suggested_way
                        end
                        pp suggested_ways
                        wayID = ask("Enter selected way ID:", :string) 
                        if suggested_ways.has_key?(wayID)
                                way = suggested_ways.fetch(wayID)
                                guess = ask_if("Is this a guess?")
                                puts "Location #{way["lat"]},#{way["lng"]} for #{null_point_name}"
                                begin
                                        res = connbus.exec("UPDATE timing_point SET lat = #{way["lat"]*10000000}, lng = 
        #{way["lng"]*10000000},guess = #{guess} WHERE name = '#{name}'")
                                        puts "Put '#{null_point_name}' into DB"
                                rescue PGError => e
                                        puts "Error inserting '#{null_point_name}' to DB #{e}"
                                        ask_if("Continue?")
                                        #conn.close() if conn
                                end
                        else
                                puts "Uhh, there was no suggestion ID like that. Try again next time!"
                        end
                end
                if whatisit == "corner"
                        # Where there's a "Cnr" or a \/ or a &, look for 2 ways and find the intersections
                        
                                name = null_point_name.to_s.gsub(/\\/, '\&\&').gsub(/'/, "''")
                                search_name = ask("I need this to look like STREETNAME1/STREETNAME2, okay? Can you do that for me?", :string, :default => name)
                                search_pieces = search_name.split("/")
                                pp search_pieces
                                if search_pieces.length == 2
                                        begin
                                                matching_ways = connosm.exec("SELECT w.way_id, latitude, longitude, w.node_id from (Select current_way_nodes.id as way_id, * from current_nodes inner join current_way_nodes on current_nodes.id=current_way_nodes.node_id inner join current_ways on current_way_nodes.id=current_ways.id) as w, (select node_id, count(node_id) from 
                (Select * FROM (SELECT * from current_way_nodes, (Select id as ctagid, v as name FROM current_way_tags WHERE k = 'name' AND v LIKE '%#{search_pieces[0]}%') as a where a.ctagid = current_way_nodes.id ) as ctags INNER JOIN current_nodes ON   ctags.node_id=current_nodes.id where sequence_id = 1 union Select * FROM ( SELECT * from current_way_nodes,  (Select id as ctagid, v as name FROM current_way_tags WHERE k = 'name' AND v LIKE '%#{search_pieces[0]}%') as a where a.ctagid = current_way_nodes.id ) as ctags INNER JOIN current_nodes ON   ctags.node_id=current_nodes.id where sequence_id = (select max(sequence_id)   from current_way_nodes cnodes   where cnodes.id = ctags.id) union Select * FROM ( SELECT * from current_way_nodes,  (Select id as ctagid, v as name FROM current_way_tags WHERE k = 'name' AND v LIKE '%#{search_pieces[1]}%') as a      where a.ctagid = current_way_nodes.id ) as ctags INNER JOIN current_nodes ON   ctags.node_id=current_nodes.id where sequence_id = 1 union Select * FROM ( SELECT * from current_way_nodes, (Select id as ctagid, v as name FROM current_way_tags WHERE k = 'name' AND v LIKE '%#{search_pieces[1]}%') as a   where a.ctagid = current_way_nodes.id ) as ctags INNER JOIN current_nodes ON   ctags.node_id=current_nodes.id where sequence_id = (select max(sequence_id)   from current_way_nodes cnodes   where cnodes.id = ctags.id) ) as t GROUP BY node_id HAVING ( COUNT(node_id) > 1 ) ) as s  where s.node_id = w.node_id")
                                        rescue PGError => e
                                                puts "Error selecting matching bus stops from DB #{e}"
                                                #conn.close() if conn
                                        end
                                        suggested_ways = Hash.new()
 
                                        matching_ways.each do |matching_way_row|
                                                pp matching_way_row
                                                # 0 = way_id                            
                                                # 1 = lat*100000
                                                # 2 = lng*100000
                                                # 3 = node_id           
                                                suggested_way = suggested_ways.fetch(matching_way_row[3], {'lat' => Float(matching_way_row[1])/10000000,
                                                                                                              'lng' => Float(matching_way_row[2])/10000000})
                                                suggested_way['way_id'] =  " " + matching_way_row[0]
                                                suggested_way['node_id'] = matching_way_row[3]
                                                suggested_ways[matching_way_row[3]] = suggested_way
                                        end
                                        pp suggested_ways
                                        wayID = ask("Enter selected way ID:", :string) 
                                        if suggested_ways.has_key?(wayID)
                                                way = suggested_ways.fetch(wayID)
                                                guess = ask_if("Is this a guess?")
                                                puts "Location #{way["lat"]},#{way["lng"]} for #{null_point_name}"
                                                begin
                                                        res = connbus.exec("UPDATE timing_point SET lat = #{way["lat"]*10000000}, lng = 
                        #{way["lng"]*10000000},osm_node = #{wayID} ,guess = #{guess} WHERE name 
                        = '#{name}'")
                                                        puts "Put '#{null_point_name}' into DB"
                                                rescue PGError => e
                                                        puts "Error inserting '#{null_point_name}' to DB #{e}"
                                                        ask_if("Continue?")
                                                        #conn.close() if conn
                                                end
                                        else
                                                puts "Uhh, there was no suggestion ID like that. Try again next time!"
                                        end
                        end
                end
        end
end
if ask_if("Fill null Timing Points from geocoder?")
        begin
                null_points = connbus.exec('SELECT name FROM timing_point WHERE lat IS null OR lng IS null;')
        rescue PGError => e
                puts "Error selecting null points from DB #{e}"
                #conn.close() if conn
        end
 
        null_points.each do |null_point_name|
                pp null_point_name
                name = null_point_name.to_s.gsub(/\\/, '\&\&').gsub(/'/, "''")
                results = cbr_geocode(null_point_name[0])
                if !results.empty? 
                        results['features'].each_with_index { |feature,index|
                                print "#{index}: #{feature['properties']['name']} (#{feature['location']}) => #{feature['centroid']['coordinates']}\n"
                        }
                        nodeID = ask("Enter selected node ID:", :integer) 
                        if results['features'].at(nodeID) != nil
                                node = results['features'][nodeID]
                                puts "Location #{node['centroid']['coordinates'][0]},#{node['centroid']['coordinates'][1]} for #{null_point_name}"
                                begin
                                        res = connbus.exec("UPDATE timing_point SET lat = #{node['centroid']['coordinates'][0]*10000000}, lng = 
        #{node['centroid']['coordinates'][1]*10000000},guess = true WHERE name = '#{name}'")
                                        puts "Put '#{null_point_name}' into DB"
                                rescue PGError => e
                                        puts "Error inserting '#{null_point_name}' to DB #{e}"
                                        ask_if("Continue?")
                                        #conn.close() if conn
                                end
                        else
                                puts "Uhh, there was no suggestion ID like that. Try again next time!"
                        end
                else
                        puts "Uhh, there were no geocoding results. Try again next time!"
                end             
        end
end