;; TODO: Make a postmodern version??
;; (=> (:limit ~ 10)
;;	   (:order-by ~ 'tag)
;;	   (:select 'name 'id :from ...)


(defmacro sql-> (first &rest others)
  (unless others
    (return-from sql-> `(query-json ,(deep-map $(if (or (keyword? *) (cons? *))  * `',*) first))))
  (destructuring-bind (next . rest) others
    `(sql-> ,(substitute first ~ next) ,@rest)))

(defmacro <-sql (&rest codes)
  `(sql-> ,@(reverse codes)))
          

(query (:order-by
        (:select (:as (:unnest 'tags) 'tag) :distinct
                 :from 'receipes)
        'tag))

(sql-> (:select * :from client)
       (:order-by ~ (:desc id))
       (:limit ~ 3 1))
;; (sql-> (:limit (:order-by (:select * :from client) (:desc id)) 3 1))
;; SELECT * FROM client ORDER BY id DESC) LIMIT 3 OFFSET 1
(<-sql (:limit ~ 3 1) 
       (:order-by ~ (:desc id))
       (:select id title :distinct :from product))


(json
 (apply 'vector
        (query (:limit (:order-by (:select '* :from 'agent) (:desc 'id)) 3 1)
               :plists)))



(query (:order-by
        (:with
         (:as 'p
              (:select (:as (:unnest 'tags) 'tag)
                       :from 'receipes))
         (:select 'tag (:as (:count 'tag) 'cnt)
                  :from 'p
                  :group-by 'tag))
        (:desc 'cnt) 'tag))

(<-sql (:order-by ~ (:desc cnt) tag)
       (:with (:as p (:select (:as (:unnest tags) tag)
                              :from receipes))
              ~)
       (:select tag (:as (:count tag) cnt)
                :from p
                :group-by tag))
       
              

(defvar *format* :alists)
(defvar *default-format* :lists)


(defun beautify-sql (statement &optional (prev "  "))
  (regex-replace-all "FROM|WHERE|GROUP|ORDER" statement
                     λ(input "~%~a~a" prev _)
                     :simple-calls t))



(defmacro query=> (&rest code-list)
  "For multiple level statement (query=> (:select '* .. ~) (:limit ~ 5) ..."
  (labels ((sql=> (&rest codes)
             (let ((first (car codes))
                   (others (cdr codes)))
               (unless others
                 (return-from sql=> first))
               (destructuring-bind (next . rest) others
                 (apply #'sql=> (substitute first ~ next) rest)))))

    (let ((type (last code-list))
          (codes code-list)
          statement)
      (if (keyword? (car type))
          (setf codes (butlast codes))
          (setf type '(:plists))) ;; Default type :plists 
      (setf statement (apply #'sql=> codes))
      `(query ,statement ,@type))))

(defmacro <=query (&rest codes)
  (let ((type (last1 codes)))
    (if (keyword? type)
        `(query=> ,@(reverse (butlast codes)) ,type)
        `(query=> ,@(reverse codes)))))