#!/usr/bin/env ruby
# coding: utf-8

# Disabled by yuuji 2018-12-02
abort "Use scripts/init-db.sh instead"

require 'sqlite3'
require "json"
require "csv"

db=SQLite3::Database.new("db/viewer_prc.sq3")

db.execute("CREATE TABLE IF NOT EXISTS " +
           "mapitem( ename PRIMARY KEY, name TEXT NOT NULL, addr TEXT, lat REAL, lon REAL)")

db.execute("CREATE TABLE IF NOT EXISTS "+
           "maptype(
ename, maptype TEXT,
FOREIGN KEY(ename) REFERENCES mapitem(ename)
ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE(ename, maptype))")

db.execute("CREATE TABLE IF NOT EXISTS "+
          "attribute(
  ename, attr TEXT, value TEXT,
  FOREIGN KEY(ename) REFERENCES mapitem(ename)
  ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE(ename,attr,value))")

db.execute("CREATE TABLE IF NOT EXISTS "+
           "object(
  ename, objtype TEXT, jsonval TEXT,
  FOREIGN KEY(ename) REFERENCES mapitem(ename)
  ON DELETE CASCADE ON UPDATE CASCADE,
  CHECK (objtype IN (
  'Point', 'MultiPoint', 'LineString',
  'MultiLineString', 'Polygon', 'MultiPolygon')),
  UNIQUE(ename, objtype, jsonval))"
)


mapitem = CSV.read("prc_csv/mapitem.csv", {:headers => true})
mapitem.each{|a|
  db.execute("insert into mapitem values ( ?, ?, ?, ?, ?)",[a["ename"],a["name"],a["addr"],a["lat"],a["lon"]])
}

maptype = CSV.read("prc_csv/maptype.csv", {:headers => true})
maptype.each{|b|
  db.execute("insert into maptype values ( ?, ?)",[b["ename"],b["maptype"]])
}

attri = CSV.read("prc_csv/attr.csv", {:headers => true})
attri.each{|c|
  db.execute("insert into attribute values ( ?, ?,?)",[c["ename"],c["attr"],c["value"]])
}

obj = CSV.read("prc_csv/obj.csv", {:headers => true})
obj.each{|d|
  db.execute("insert into object values ( ?, ?,?)",[d["ename"],d["objtype"],d["jsonval"]])
}

### all join
#all_join = db.execute("SELECT mapitem.ename, mapitem.name, mapitem.addr, mapitem.lat, mapitem.lon, maptype.maptype, attribute.attr ,attribute.value, object.objtype, object.jsonval FROM mapitem JOIN maptype JOIN attribute JOIN object ON mapitem.ename == maptype.ename AND mapitem.ename == attribute.ename AND mapitem.ename == object.ename; ")