(module load-sqlite ()

(import scheme)
(import (chicken base))
(import (chicken process-context))
(import (srfi 1))
(import medea)
(import sql-de-lite)

(define pragma-foreign-keys-query
  "PRAGMA foreign_keys=ON")
(define insert-species-query
  "INSERT INTO species(page_id, title) VALUES(?, ?)")
(define select-attrs-query
  "SELECT id FROM attrs WHERE key = ? AND value = ?")
(define insert-attrs-query
  "INSERT INTO attrs(key, value) VALUES(?, ?)")
(define insert-species-attrs-query
  "INSERT INTO species_attrs(species_id, attr_id) VALUES(?, ?)")

(define (symbol->integer x)
  (string->number (symbol->string x)))

(define (vector-for-each proc vec)
  (let ((len (vector-length vec)))
    (let loop ((i 0))
      (when (< i len)
        (proc (vector-ref vec i))
        (loop (add1 i))))))

(define (select-or-insert db select-s insert-s #!rest args)
  (or (apply query fetch-value select-s args)
      (begin
        (apply exec insert-s args)
        (last-insert-rowid db))))

(define (main json-path db-path)
  (let ((json (call-with-input-file json-path read-json)))
    (call-with-database db-path
      (lambda (db)
        (with-transaction db
          (lambda ()
            (exec (sql db pragma-foreign-keys-query))
            (for-each
             (lambda (item)
               (let* ((page-id (symbol->integer (car item)))
                      (attrs (cdr item))
                      (title (alist-ref 'title attrs))
                      (attrs (alist-delete 'title attrs)))
                 (exec (sql db insert-species-query) page-id title)
                 (let ((species-id (last-insert-rowid db)))
                   (for-each
                    (lambda (attr)
                      (let ((key (symbol->string (car attr)))
                            (values (cdr attr)))
                        (vector-for-each
                         (lambda (value)
                           (let* ((select-s (sql db select-attrs-query))
                                  (insert-s (sql db insert-attrs-query))
                                  (attr-id (select-or-insert db
                                                             select-s
                                                             insert-s
                                                             key value)))
                             (exec (sql db insert-species-attrs-query)
                                   species-id attr-id)))
                         values)))
                    attrs))))
             json)))))))

(apply main (command-line-arguments))

)