(ns sqlite3-script
  (:require [babashka.pods :as pods]))

(pods/load-pod 'org.babashka/go-sqlite3 "0.1.0")
(require '[pod.babashka.go-sqlite3 :as sqlite])

(sqlite/execute! "/tmp/foo.db"
  ["create table if not exists foo (the_text TEXT, the_int INTEGER, the_real REAL, the_blob BLOB)"])

;; This pod also supports storing blobs, so lets store a picture.
(def png (java.nio.file.Files/readAllBytes (.toPath (io/file "resources/babashka.png"))))

(sqlite/execute! "/tmp/foo.db"
  ["insert into foo (the_text, the_int, the_real, the_blob) values (?,?,?,?)" "foo" 1 3.14 png])
;;=> {:rows-affected 1, :last-inserted-id 1}

(def results (sqlite/query "/tmp/foo.db" ["select * from foo order by the_int asc"]))
(count results) ;;=> 1

(def row (first results))
(keys row) ;;=> (:the_text :the_int :the_real :the_blob)
(:the_text row) ;;=> "foo"

;; Should be true:
(= (count png) (count (:the_blob row)))

(sqlite/query "/tmp/foo.db" "select * from foo")

(ns honeysql-script
  (:require [babashka.deps :as deps]
            [babashka.pods :as pods]))

;; Load HoneySQL from Clojars:
(deps/add-deps '{:deps {honeysql/honeysql {:mvn/version "1.0.444"}}})

(require '[honeysql.core :as sql]
         '[honeysql.helpers :as helpers])

(pods/load-pod 'org.babashka/go-sqlite3 "0.1.0")
(require '[pod.babashka.go-sqlite3 :as sqlite])

(sqlite/execute! "/tmp/foo.db" ["create table if not exists foo (col1 TEXT, col2 TEXT)"])

(def insert
  (-> (helpers/insert-into :foo)
      (helpers/columns :col1 :col2)
      (helpers/values
       [["Foo" "Bar"]
        ["Baz" "Quux"]])
      sql/format))
;; => ["INSERT INTO foo (col1, col2) VALUES (?, ?), (?, ?)" "Foo" "Bar" "Baz" "Quux"]

(sqlite/execute! "/tmp/foo.db" insert)
;; => {:rows-affected 2, :last-inserted-id 2}

(def sqlmap {:select [:col1 :col2]
             :from   [:foo]
             :where  [:= :col1 "Foo"]})

(def select (sql/format sqlmap))
;; => ["SELECT col1, col2 FROM foo WHERE col1 = ?" "Foo"]

(sqlite/query "/tmp/foo.db" select)
;; => [{:col1 "Foo", :col2 "Bar"}]