#!/usr/bin/ruby
require 'postgres'
 
require 'highline.rb'
include HighLine
 
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(/'/, "''")
                        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?")
        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
                        matching_nodes = connosm.exec("Select * FROM (SELECT * from current_node_tags,
                        (Select id as ctagid FROM current_node_tags WHERE  v LIKE '%#{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
        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
        end
end