From ec1581999bb370f116fc64aec092fea35ccbbb04 Mon Sep 17 00:00:00 2001
From: unwox <me@unwox.com>
Date: Fri, 14 Mar 2025 23:08:43 +0600
Subject: order products by creation time

---
 bin/fetch.fnl | 81 ++++++++++++++++++++++++++++++++++++++---------------------
 1 file changed, 52 insertions(+), 29 deletions(-)

(limited to 'bin/fetch.fnl')

diff --git a/bin/fetch.fnl b/bin/fetch.fnl
index d3bbef9..b8d1334 100644
--- a/bin/fetch.fnl
+++ b/bin/fetch.fnl
@@ -46,6 +46,11 @@
     volume REAL NOT NULL,
     price_per REAL NOT NULL,
     archived BOOL NOT NULL,
+    update_time DATETIME NOT NULL
+  );
+
+  CREATE TABLE IF NOT EXISTS permanent_products(
+    url TEXT NOT NULL PRIMARY KEY,
     creation_time DATETIME NOT NULL
   );
 
@@ -115,33 +120,51 @@
     (must (luna.db.exec-tx tx sql vars))))
 
 (fn store-products [tx products]
-  (local sql
-    (.. "INSERT OR REPLACE INTO products"
-        "(url, site, title, description, year, image, price, weight, volume, price_per, archived, creation_time)"
-        " VALUES "
-        (array.join
-          (map (fn [_ _] "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
-               products)
-          ", ")))
-  (local creation-time (now))
-  (local vars
-    (reduce
-      (fn [_ product rest]
-        (array.concat rest
-          [(or product.url "")
-           (or product.site "")
-           (or product.title "")
-           (or product.description "")
-           (or product.year 0)
-           (or product.image "")
-           (or product.price 0)
-           (or product.weight 0)
-           (or product.volume 0)
-           (or product.price-per 0)
-           (or product.archived false)
-           creation-time]))
-      products []))
-  (must (luna.db.exec-tx tx sql vars))
+  (local update-time (now))
+
+  ;; store product
+  (let
+    [sql (.. "INSERT OR REPLACE INTO products"
+             "(url, site, title, description, year, image, price, weight, volume, price_per, archived, update_time)"
+             " VALUES "
+             (array.join
+               (map (fn [_ _] "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
+                    products)
+               ", "))
+     vars (reduce
+            (fn [_ product rest]
+              (array.concat rest
+                [(or product.url "")
+                 (or product.site "")
+                 (or product.title "")
+                 (or product.description "")
+                 (or product.year 0)
+                 (or product.image "")
+                 (or product.price 0)
+                 (or product.weight 0)
+                 (or product.volume 0)
+                 (or product.price-per 0)
+                 (or product.archived false)
+                 update-time]))
+            products
+            [])]
+    (must (luna.db.exec-tx tx sql vars)))
+
+  ;; store permenanent product data
+  (let
+    [sql (.. "INSERT OR IGNORE INTO permanent_products"
+             "(url, creation_time)"
+             " VALUES "
+             (array.join
+               (map (fn [_ _] "(?, ?)") products)
+               ", "))
+     vars (reduce
+            (fn [_ product rest]
+              (array.concat rest
+                [(or product.url "") update-time]))
+            products
+            [])]
+    (must (luna.db.exec-tx tx sql vars)))
 
   ;; store tags
   (store-tags tx (array.unique
@@ -157,8 +180,8 @@
            "UPDATE products
             SET    archived = true
             WHERE  site = ?
-            AND    creation_time < ?"
-           [site creation-time]))))
+            AND    update_time < ?"
+           [site update-time]))))
 
 (fn populate-search-table [db]
   (local tx (must (luna.db.begin db)))
-- 
cgit v1.2.3