#|
.tables --show tables and views
SELECT name FROM sqlite_master WHERE type = 'table';
.schema
.schema notes  --show table/view/triggers' create code
.mode column
.headers on
.separator ROW "\n"
.nullvalue NULL
.width 40 40 --...

(ql:quickload :sqlite)
(use-package :sqlite)
(defvar *db* (connect "/Users/Can/Develop/Lisp/others/config/instantly.db"))
(execute-to-list *db* "select * from notes")
(execute-to-list *db* "SELECT group_concat(name), type FROM sqlite_master group by type")
(execute-to-list *db* "PRAGMA table_info(notes)")


|#
;; (defvar *db* (connect "/Users/Can/Develop/Lisp/others/config/instantly.db"))
(use-package :sqlite)

(defun create-notes ()
  (let* ((*db* (connect "/Users/Can/Desktop/instantly.db"))
         (*folder* "/Users/Can/Desktop/Instantly/")
         (notes (execute-to-list *db* "
SELECT id, replace(title, '/', '-'), replace(body, title, ''), need_password, modify_time, create_time, (select title from locations where id = location_id) as location,
	(SELECT GROUP_CONCAT(title, ' . ') FROM tags WHERE id IN (SELECT tag_id FROM note_tag WHERE note_id = n.id)) as tags
FROM notes n
WHERE need_password = 0
ORDER BY need_password ASC, modify_time DESC")))
    (ensure-directories-exist *folder*)
    (mapc (^(note)          
            (destructuring-bind (id title body password? time-last-update time-create location tags) note
              (declare (ignorable id password?))
              (with-open-file (main #"$[*folder*]$[title].txt"#
                                    :direction :output
                                    :if-does-not-exist :create
                                    :if-exists :supersede)
                (when tags
                  (vprint title tags))
                (let* ((from (date-of-universal time-create))
                       (last (date-of-universal time-last-update))
                       (info (fmt "~A字 ~A~A ~A ~A"
                                     (length body)
                                     from
                                     (if (string= from last) ""  (fmt " => ~A" last))
                                     (or tags "")
                                     (if location #/[$[location]]/#  "")))
                       (separator (celwk::string-repeat "=" (+ 3 (max (length title) (length info))))))
                  (format main "~A~2%~A~%~a~2%~a~%"
                          title
                          info
                          separator
                          body)))))
          notes)
    (cmd #"open $*folder*"#)))