#|
.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)
(defvar *db* (connect "/Users/Can/Desktop/instantly.db"))
(alias sql execute-to-list *db*)
(setf notes (sql "
SELECT id, title, body, 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
ORDER BY need_password ASC, modify_time DESC
LIMIT 200"))

(defconstant +line-width+ (length (celwk::now)))

(with-open-file (main "/Users/Can/Desktop/instantly.org"
                      :direction :output
                      :if-does-not-exist :create
                      :if-exists :supersede)	;; :append
  (mapcar (^(note) 
            (destructuring-bind (id title body password? time-last-update time-create location tags) note
              (setf tags (uiop:split-string
                          (remove-if (^(char) (position char "🌎&!")) (substitute #\: #\Space tags))
                          :separator ",")) ;; maybe nil
              (when (= 1 password?) (push "hidden" tags))
              (setf tags (if tags (concat ":" (celwk::string-join tags ":") ":") ""))
              (let ((separator (celwk::string-repeat "=" (+ 8 +line-width+))))
                (format main "* ~a~a~a~%~a~2%~a~%Update: ~a ~%Create: ~a~%At ~a [id:~a]~%~a~%"
                        title
                        (if (zerop (length tags)) ""
                            (celwk::string-repeat " " 10))
                        tags
                        body
                        separator
                        (time-of-universal time-last-update :from 1970)
                        (time-of-universal time-create :from 1970)
                        (or location "Nowhere")
                        id
                        separator))))
          notes)
  (values))
;; 0.006 second
;; C-u C-c C-q  to reoganize the tags of Org
;; (ppcre:split "\\* " (read-file "/Users/Can/Develop/Lisp/me/instantly.org"))