#!/usr/bin/ruby

require 'csv'

tags = Hash.new{ |h,k| h[k] = [] }

reader = CSV.open('coresp.csv', 'r')
header = reader.shift
reader.each{ |l|
    next if !l[0] or l[0] == '-'
    tags[l[0]] << l[1..2]
}

puts tags.collect{ |tag,insee|
    stats = "SELECT SUM(" + insee.collect{ |i| "#{i[0]}" }.join('+') + ") FROM equip_serv"
    lib = insee.collect{ |i| i[1] }.join('\n')
    ts = tag.split("\n")
    nodes = "SELECT COUNT(*) FROM nodes WHERE " + ts.collect{ |kv|
        (k,v) = kv.split('=')
        "(tags?'#{k}' AND tags->'#{k}' = '#{v}')"
    }.join(' OR ')
    ways = "SELECT COUNT(*) FROM ways WHERE " + ts.collect{ |kv|
        (k,v) = kv.split('=')
        "(tags?'#{k}' AND tags->'#{k}' = '#{v}')"
    }.join(' OR ')
#    "SELECT (#{nodes})+(#{ways}) AS osm, (#{stats}) AS insee, '#{lib}' AS lib;"
    "SELECT (#{ways}) AS osm, (#{stats}) AS insee, '#{lib}' AS lib;"
}