(local sqlite (require :lsqlite3))
(local lume (require :lume))
(local { : view} (require :fennel))

(fn table-exists? [db]
  (accumulate [ret false
               r (db:nrows "select name from sqlite_master where type = 'table' and name= 'db_version'")]
    (or ret true)))

(fn migrate [db serial statement]
  (if (= (db:exec statement) 0)
      (db:exec (.. "update db_version set serial = " serial))
      (assert false (.. "db failed: " statement))))

(fn migrate-all [db]
  (when (not (table-exists? db "db_version"))
    (db:exec "create table db_version (serial integer)")
    (db:exec "insert into db_version values (1)"))
  (let [version (accumulate [v 0
                             r (db:nrows "select serial from db_version")]
                  r.serial)]
    (when (< version 2)
      (migrate db 2 "create table visits (url text, timestamp integer)"))
    (when (< version 3)
      (migrate db 3 "create table page_titles (url text primary key, title text)"))))

(fn visit [self url timestamp]
  (let [s (self.db:prepare "insert into visits (url, timestamp) values (?,?)")]
    (assert (= 0 (s:bind_values url timestamp)) (view [url timestamp]))
    (s:step)
    (s:reset)))

(fn title [self url title]
  (let [s (self.db:prepare "insert into page_titles (url, title) values (?,?) on conflict(url) do update set title = excluded.title")]
    (assert (= 0 (s:bind_values url title)) url)
    (s:step)
    (s:reset)))

(fn find [self term]
  (let [s (self.db:prepare "select v.url,pt.title,v.timestamp from visits v left join page_titles pt on v.url = pt.url where instr(v.url, ?) >0")]
    (assert (= 0 (s:bind_values term)))
    (s:nrows)))

(fn find-distinct [self term]
  (let [s (self.db:prepare "select distinct v.url,pt.title from visits v left join page_titles pt on v.url = pt.url where instr(v.url, ?) >0")]
    (assert (= 0 (s:bind_values term)))
    (s:nrows)))

(fn open [pathname]
  (let [db (if pathname (sqlite.open pathname) (sqlite.open_memory))]
    (migrate-all db)
    {
     : db
     : visit
     : title
     : find
     : find-distinct
     }))

{
 : open
 }