cl-deck-builder2/src/web/query.lisp

#|

I didn't want to keep the query stuff in the model as it's not really
got anything to do with the model and more with the database. I tried
the DATABASE-OBJECT approach and that was clumsy too.

I just want to write queries. Maybe I should have used CL-YESQL.

TODO I have multiple of these too: saved-deck-list,
saved-category-list, constructed-deck-list ... where should these go?
Into their respective pacakges?

|#

(in-package #:cl-deck-builder2.web.query)

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(defmacro select-attachment (&body body)
  `(select-dao 'attachment
     (sxql:order-by :created-at :desc)
     ,@body))

(defun select-attachment-by-id (id)
  (first (select-attachment
           (sxql:where (:= :id id)))))

(defmacro select-category (&body body)
  `(select-dao 'category
     ;; Why did I use this?
     ;; (sxql:group-by name)
     (sxql:order-by :created-at :desc)
     ,@body))

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(defmacro select-feedback (&body body)
  `(select-dao 'feedback
     (mito:includes 'user)
     (sxql:order-by :created-at :desc)
     ,@body))

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; TODO Now where does this go? db? toolkit?
(defun %sxql-like (field value)
  "Helper function. Turn \"value\" into \"%value%\" for SQL LIKE queries."
  (list :like field (format nil "%%~a%%" value)))

(defmacro select-ydk-deck (&body body)
  `(select-dao 'ydk-deck
     (sxql:order-by :asc :created-at)
     ,@body))

(defmacro select-deck-item (&body body)
  `(select-dao 'deck-item
     ;; (mito:includes 'ydk-deck)
     (sxql:order-by :asc :created-at)
     ,@body))

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(defmacro select-constructed-decks (&body body)
  `(select-dao 'constructed-deck
     (mito:includes 'ydk-deck)
     (sxql:order-by :asc :created-at)
     ,@body))

(defmacro select-constructed-deck-items (&body body)
  `(select-dao 'constructed-deck-item
     (mito:includes 'ygo-card 'deck-item 'ygo-set-item)
     (sxql:order-by :asc :created-at)
     ,@body))

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; builder.lisp
;; (defun unique-cards-in-decks ()
;;   "Select the unique cards in all DECK-ITEMs"
;;   (let ((deck-items
;;           (with-connection (db)
;;             (mito:select-dao 'deck-item
;;               (sxql:group-by :passcode))))
;;         (ht (make-hash-table)))
;;     (dolist (deck-item deck-items ht)
;;       (push deck-item (gethash (deck-id-of deck-item) ht)))))

;; Asked for this one. For every card in a deck, show what deck the card is in...
(defun cards-in-all-decks ()
  "Select the unique cards in all DECK-ITEMs"
  (let ((deck-items
          (with-connection (db)
            (with-transaction
              (select-deck-item))))
        (ht (make-hash-table)))
    (with-connection (db)
      (with-transaction
        (dolist (deck-item (reverse deck-items) ht)
          (pushnew (list :deck-id (deck-id-of deck-item)
                         :ydk-deck (mito:find-dao 'ydk-deck :id (deck-id-of deck-item))
                         :ygo-card (mito:find-dao 'ygo-card :id (deck-passcode-of deck-item)))
                   (gethash (deck-passcode-of deck-item) ht)
                   :key (lambda (plist) (getf plist :deck-id))))))))

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(defun select-variant-condition ()
  (select-dao 'variant-condition))

(defun ygo-set-item-by-item-id (item-id)
  (with-connection (db)
    (with-transaction
      (let ((set-items
              (mito:select-dao 'ygo-set-item
                (mito:includes 'ygo-set 'variant-condition)
                (sxql:where (:= :item-id item-id)))))
        (dolist (set-item set-items set-items)
          (setf (item-of set-item)
                (ygo-set-by-id (mito:object-id set-item))))))))

(defun ygo-set-item-by-id (id)
  (with-connection (db)
    (with-transaction
      (let ((set-item (mito:select-dao 'ygo-set-item
                      (mito:includes 'ygo-set
                                     'variant-condition)
                      (sxql:where (:= :id id)))))
        (setf (item-of set-item)
              (ygo-set-by-id (mito:object-id set-item)))
        set-item))))

(defun select-ygo-cc-item-variants (id)
  "YGO-CC-ITEM is a relatively new addition, a database relationship between CC-ITEMs and VARIANT. Actually now that I think about it as I write this, YGO-CC-ITEM is a misnomer, and this is actually a CC-ITEM-VARIANT.
SELECT all the variants available for this CC-ITEM ID.

This will generate a blank of 5 items if they don't already have corresponding YGO-SET-ITEMS in the database
It will also fetch all corresponding YGO-SET-ITEMS. If they do exist, REMOVE-DUPLICATES will use MITO:OBJECT-ID to remove them by VARIANT-OF.

TODO FIXME"
  (with-connection (db)
    (with-transaction
      (remove-duplicates
       ;; Append the blanks and the existing data from the database, and remove-duplicates
       (append
        ;; Create a list of blanks
        (mapcar (lambda (v)
                  (make-instance 'ygo-set-item :item-id id
                                               :item (ygo-set-by-id id)
                                               :variant v
                                               :variant-id (mito:object-id v)))
                (select-variant-condition))
        ;; Select what exists from the database, and fill in the YGO-SET info with the macro we wrote
        (ygo-set-item-by-item-id id))
       :key (alexandria:compose #'mito:object-id #'variant-of)))))

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; TODO: old ygoprodeck code
(defun ygo-select-info-by-id (id &rest fields-and-values)
  (apply #'find-dao 'ygo-info :id id fields-and-values))

(defun ygo-select-info-by-passcode (passcode &rest fields-and-values)
  (apply #'find-dao 'ygo-info :passcode passcode fields-and-values))