replace productId with cardId in price history chart plotting

package.json

@@ -20,7 +20,7 @@
     "chart.js": "^4.5.1",
     "csv": "^6.4.1",
     "dotenv": "^17.2.4",
-    "drizzle-orm": "^1.0.0-beta.15-859cf75",
+    "drizzle-orm": "1.0.0-beta.15-859cf75",
     "pg": "^8.20.0",
     "sass": "^1.97.3",
     "typesense": "^3.0.1"
@@ -29,7 +29,7 @@
     "@types/bootstrap": "^5.2.10",
     "@types/node": "^25.2.1",
     "@types/pg": "^8.18.0",
-    "drizzle-kit": "^1.0.0-beta.15-859cf75",
+    "drizzle-kit": "1.0.0-beta.15-859cf75",
     "typescript": "^5.9.3"
   }
 }

scripts/list-missing-images.ts

@@ -12,7 +12,7 @@ async function findMissingImages() {
       .where(sql`${schema.tcgcards.sealed} = false`);
     const missingImages: string[] = [];
     for (const card of cards) {
-        const imagePath = path.join(process.cwd(), 'public', 'cards', `${card.productId}.jpg`);
+        const imagePath = path.join(process.cwd(), 'static', 'cards', `${card.productId}.jpg`);
         try {
             await fs.access(imagePath);
         } catch (err) {

scripts/pokemon-helper.ts

@@ -12,6 +12,9 @@ const DollarToInt = (dollar: any) => {
     return Math.round(dollar * 100);
 }
 
+export type Logger = (msg: string) => void;
+const defaultLogger: Logger = (msg) => console.log(chalk.green(msg));
+
 
 
 export const Sleep = (ms: number) => {
@@ -39,7 +42,7 @@ export const GetNumberOrNull = (value: any): number | null => {
 
 
 // Delete and recreate the 'cards' index
-export const createCardCollection = async () => {
+export const createCardCollection = async (log: Logger = defaultLogger) => {
     try {
         await client.collections('cards').delete();
     } catch (error) {
@@ -56,9 +59,11 @@ export const createCardCollection = async () => {
             { name: 'productLineName', type: 'string', facet: true },
             { name: 'rarityName', type: 'string', facet: true },
             { name: 'setName', type: 'string', facet: true },
+            { name: 'setCode', type: 'string' },
             { name: 'cardType', type: 'string', facet: true },
             { name: 'energyType', type: 'string', facet: true },
-            { name: 'number', type: 'string', sort: true },
+            { name: 'number', type: 'string' },
+            { name: 'inumber', type: 'int32', optional: true, sort: true },
             { name: 'Artist', type: 'string' },
             { name: 'sealed', type: 'bool' },
             { name: 'releaseDate', type: 'int32' },
@@ -67,11 +72,11 @@ export const createCardCollection = async () => {
             // { name: 'sku_id', type: 'string[]', optional: true, reference: 'skus.id', async_reference: true }
         ],
     });
-    console.log(chalk.green('Collection "cards" created successfully.'));
+    log('Collection "cards" created successfully.');
 }
 
 // Delete and recreate the 'skus' index
-export const createSkuCollection = async () => {
+export const createSkuCollection = async (log: Logger = defaultLogger) => {
     try {
         await client.collections('skus').delete();
     } catch (error) {
@@ -88,11 +93,11 @@ export const createSkuCollection = async () => {
             { name: 'card_id', type: 'string', reference: 'cards.id', async_reference: true },
         ]
     });
-    console.log(chalk.green('Collection "skus" created successfully.'));
+    log('Collection "skus" created successfully.');
 }
 
 // Delete and recreate the 'inventory' index
-export const createInventoryCollection = async () => {
+export const createInventoryCollection = async (log: Logger = defaultLogger) => {
     try {
         await client.collections('inventories').delete();
     } catch (error) {
@@ -116,16 +121,24 @@ export const createInventoryCollection = async () => {
             { name: 'cardType', type: 'string' },
         ]
     });
-    console.log(chalk.green('Collection "inventories" created successfully.'));
+    log('Collection "inventories" created successfully.');
 }
 
 
-export const upsertCardCollection = async (db:DBInstance) => {
+export const upsertCardCollection = async (db:DBInstance, log: Logger = defaultLogger) => {
     const pokemon = await db.query.cards.findMany({
         with: { set: true, tcgdata: true, prices: true },
     });
     await client.collections('cards').documents().import(pokemon.map(card => {
-        const marketPrice = card.tcgdata?.marketPrice ? DollarToInt(card.tcgdata.marketPrice) : null;
+        // Use the NM SKU price matching the card's variant (kept fresh by syncPrices)
+        // Fall back to any NM sku, then to tcgdata price
+        const nmSku = card.prices.find(p => p.condition === 'Near Mint' && p.variant === card.variant)
+                   ?? card.prices.find(p => p.condition === 'Near Mint');
+        const marketPrice = nmSku?.marketPrice
+            ? DollarToInt(nmSku.marketPrice)
+            : card.tcgdata?.marketPrice
+                ? DollarToInt(card.tcgdata.marketPrice)
+                : null;
 
         return {
             id: card.cardId.toString(),
@@ -136,21 +149,23 @@ export const upsertCardCollection = async (db:DBInstance) => {
             productLineName: card.productLineName,
             rarityName: card.rarityName,
             setName: card.set?.setName || "",
+            setCode: card.set?.setCode || "",
             cardType: card.cardType || "",
             energyType: card.energyType || "",
             number: card.number,
+            inumber: (card.number !== null) ? parseInt(card.number) : undefined,
             Artist: card.artist || "",
             sealed: card.sealed,
-            content: [card.productName, card.productLineName, card.set?.setName || "", card.number, card.rarityName, card.artist || ""].join(' '),
+            content: [card.productName, card.productLineName, card.set?.setName || "", card.set?.setCode || "", card.number, card.rarityName, card.artist || ""].join(' '),
             releaseDate: card.tcgdata?.releaseDate ? Math.floor(new Date(card.tcgdata.releaseDate).getTime() / 1000) : 0,
             ...(marketPrice !== null && { marketPrice }),
             // sku_id: card.prices.map(price => price.skuId.toString())
         };
     }), { action: 'upsert' });
-    console.log(chalk.green('Collection "cards" indexed successfully.'));
+    log('Collection "cards" indexed successfully.');
 }
 
-export const upsertSkuCollection = async (db:DBInstance) => {
+export const upsertSkuCollection = async (db:DBInstance, log: Logger = defaultLogger) => {
     const skus = await db.query.skus.findMany();
     await client.collections('skus').documents().import(skus.map(sku => ({
         id: sku.skuId.toString(),
@@ -160,10 +175,10 @@ export const upsertSkuCollection = async (db:DBInstance) => {
         marketPrice: DollarToInt(sku.marketPrice),
         card_id: sku.cardId.toString(),
     })), { action: 'upsert' }); 
-    console.log(chalk.green('Collection "skus" indexed successfully.'));
+    log('Collection "skus" indexed successfully.');
 }
 
-export const upsertInventoryCollection = async (db:DBInstance) => {
+export const upsertInventoryCollection = async (db:DBInstance, log: Logger = defaultLogger) => {
     const inv = await db.query.inventory.findMany({
         with: { sku: { with: { card: { with: { set: true } } } } }
     });
@@ -188,25 +203,27 @@ export const upsertInventoryCollection = async (db:DBInstance) => {
             i.sku?.card?.artist || ""
         ].join(' '),
     })), { action: 'upsert' });
-    console.log(chalk.green('Collection "inventories" indexed successfully.'));
+    log('Collection "inventories" indexed successfully.');
 }
 
 
 
 
 
-export const UpdateVariants = async (db:DBInstance) => {
+export const UpdateVariants = async (db:DBInstance, log: Logger = (m) => console.log(m)) => {
     const updates = await db.execute(sql`update cards as c
 set 
     product_name = a.product_name, product_line_name = a.product_line_name, product_url_name = a.product_url_name, rarity_name = a.rarity_name, 
-    sealed = a.sealed, set_id = a.set_id, card_type = a.card_type, energy_type = a.energy_type, number = a.number, artist = a.artist
+    sealed = a.sealed, set_id = a.set_id, card_type = a.card_type, energy_type = a.energy_type, number = a.number, inumber = a.inumber, artist = a.artist
 from (
     select t.product_id, b.variant,
         coalesce(o.product_name, regexp_replace(regexp_replace(coalesce(nullif(t.product_name, ''), t.product_url_name),' \\\\(.*\\\\)',''),' - .*$','')) as product_name,
         coalesce(o.product_line_name, t.product_line_name) as product_line_name, coalesce(o.product_url_name, t.product_url_name) as product_url_name,
         coalesce(o.rarity_name, t.rarity_name) as rarity_name, coalesce(o.sealed, t.sealed) as sealed, coalesce(o.set_id, t.set_id) as set_id,
         coalesce(o.card_type, t.card_type) as card_type, coalesce(o.energy_type, t.energy_type) as energy_type,
-        coalesce(o.number, t.number) as number, coalesce(o.artist, t.artist) as artist
+        coalesce(o.number, regexp_replace(t.number,'^0+','')) as number,
+        nullif(regexp_replace(regexp_replace(coalesce(o.number,t.number),'/.*',''),'[^0-9]','','g'),'')::integer as inumber,
+        coalesce(o.artist, t.artist) as artist
     from tcg_cards t
     join (select distinct product_id, variant from skus) b on t.product_id = b.product_id
     left join tcg_overrides o on t.product_id = o.product_id
@@ -219,22 +236,23 @@ where c.product_id = a.product_id and c.variant = a.variant and
     c.energy_type is distinct from a.energy_type or c."number" is distinct from a."number" or c.artist is distinct from a.artist
 )
 `);
-    console.log(`Updated ${updates.rowCount} rows in cards table`);
+    log(`Updated ${updates.rowCount} rows in cards table`);
 
-    const inserts = await db.execute(sql`insert into cards (product_id, variant, product_name, product_line_name, product_url_name, rarity_name, sealed, set_id, card_type, energy_type, "number", artist)
+    const inserts = await db.execute(sql`insert into cards (product_id, variant, product_name, product_line_name, product_url_name, rarity_name, sealed, set_id, card_type, energy_type, "number", inumber, artist)
 select t.product_id, b.variant, 
 coalesce(o.product_name, regexp_replace(regexp_replace(coalesce(nullif(t.product_name, ''), t.product_url_name),' \\\\(.*\\\\)',''),' - .*$','')) as product_name,
 coalesce(o.product_line_name, t.product_line_name) as product_line_name, coalesce(o.product_url_name, t.product_url_name) as product_url_name, coalesce(o.rarity_name, t.rarity_name) as rarity_name, 
 coalesce(o.sealed, t.sealed) as sealed, coalesce(o.set_id, t.set_id) as set_id, coalesce(o.card_type, t.card_type) as card_type, 
-coalesce(o.energy_type, t.energy_type) as energy_type, coalesce(o.number, t.number) as number, coalesce(o.artist, t.artist) as artist
+coalesce(o.energy_type, t.energy_type) as energy_type, coalesce(o.number, regexp_replace(t.number,'^0+','')) as number, 
+nullif(regexp_replace(regexp_replace(coalesce(o.number,t.number),'/.*',''),'[^0-9]','','g'),'')::integer as inumber, coalesce(o.artist, t.artist) as artist
 from tcg_cards t
 join (select distinct product_id, variant from skus) b on t.product_id = b.product_id
 left join tcg_overrides o on t.product_id = o.product_id
 where not exists (select 1 from cards where product_id=t.product_id and variant=b.variant)
 `);
-    console.log(`Inserted ${inserts.rowCount} rows into cards table`);
+    log(`Inserted ${inserts.rowCount} rows into cards table`);
 
     const skuUpdates = await db.execute(sql`update skus s set card_id = c.card_id from cards c where s.product_id = c.product_id and s.variant = c.variant and s.card_id is distinct from c.card_id`);
-    console.log(`Updated ${skuUpdates.rowCount} rows in skus table`);
+    log(`Updated ${skuUpdates.rowCount} rows in skus table`);
 
 }

scripts/preload-tcgplayer.ts

@@ -1,106 +1,86 @@
 import 'dotenv/config';
 import * as schema from '../src/db/schema.ts';
-import { db, ClosePool } from '../src/db/index.ts';
+import { db, ClosePool, type DBInstance } from '../src/db/index.ts';
 
 import fs from "node:fs/promises";
 import path from "node:path";
-import chalk from 'chalk';
+import { pathToFileURL } from 'node:url';
 import * as helper from './pokemon-helper.ts';
-//import util from 'util';
+
+export type Logger = (msg: string) => void;
+const consoleLogger: Logger = (m) => console.log(m);
+
+export type RunImportOptions = {
+  sets?: string[];
+  log?: Logger;
+  runUpdateVariants?: boolean;
+  runCardUpsert?: boolean;
+};
 
 
-async function syncTcgplayer(cardSets:string[] = []) {
-
-  const productLines = [ "pokemon", "pokemon-japan" ];
-
-  // work from the available sets within the product line
-  for (const productLine of productLines) {
-    const d = {"algorithm":"sales_dismax","from":0,"size":1,"filters":{"term":{"productLineName":[productLine]}},"settings":{"useFuzzySearch":false}};
-
-    const response = await fetch('https://mp-search-api.tcgplayer.com/v1/search/request?q=&isList=false', {
-      method: 'POST',
-      headers: {'Content-Type': 'application/json',},
-      body: JSON.stringify(d),
-    });
-    if (!response.ok) {
-      console.error('Error notifying sync completion:', response.statusText);
-      process.exit(1);
-    }
-    const data = await response.json();
-
-    const setNames = data.results[0].aggregations.setName;
-    for (const setName of setNames) {
-      let processSet = true;
-      if (cardSets.length > 0) {
-        processSet = cardSets.some(set => setName.value.toLowerCase().includes(set.toLowerCase()));
-      }
-      if (processSet) {
-        console.log(chalk.blue(`Syncing product line "${productLine}" with setName "${setName.urlValue}"...`));
-        await syncProductLine(productLine, "setName", setName.urlValue);
-      }
-    }
-
-  }
-
-  console.log(chalk.green('✓ All TCGPlayer data synchronized successfully!'));
-}
-
-
-async function syncProductLine(productLine: string, field: string, fieldValue: string) {
+const syncProductLine = async (
+  database: DBInstance,
+  productLine: string,
+  field: string,
+  fieldValue: string,
+  allProductIds: Set<number>,
+  log: Logger,
+) => {
   let start = 0;
-  let size = 50;
+  const size = 50;
   let total = 1000000;
 
   while (start < total) {
-    console.log(` Fetching items ${start} to ${start + size} of ${total}...`);
+    log(` Fetching items ${start} to ${start + size} of ${total}...`);
 
     const d = {
-          "algorithm":"sales_dismax",
-          "from":start,
-          "size":size,
-          "filters":{
-              "term":{"productLineName":[productLine], [field]:[fieldValue]} ,
-              "range":{},
-              "match":{}
-          },
-          "listingSearch":{
-              "context":{"cart":{}},
-              "filters":{"term":{
-                  "sellerStatus":"Live",
-                  "channelId":0
-                  },
-                  "range":{
-                      "quantity":{"gte":1}
-                  },
-                  "exclude":{"channelExclusion":0}
-              }
-          },
-          "context":{
-              "cart":{},
-              "shippingCountry":"US",
-              "userProfile":{}
-          },
-          "settings":{
-              "useFuzzySearch":false,
-              "didYouMean":{}
-          },
-          "sort":{}
-      };
-
-    //console.log(util.inspect(d, { depth: null }));
-    //process.exit(1);
-
-    const response = await fetch('https://mp-search-api.tcgplayer.com/v1/search/request?q=&isList=false', {
-      method: 'POST',
-      headers: {
-        'Content-Type': 'application/json',
+      "algorithm": "sales_dismax",
+      "from": start,
+      "size": size,
+      "filters": {
+        "term": { "productLineName": [productLine], [field]: [fieldValue] },
+        "range": {},
+        "match": {},
       },
-      body: JSON.stringify(d),
-    });
+      "listingSearch": {
+        "context": { "cart": {} },
+        "filters": {
+          "term": { "sellerStatus": "Live", "channelId": 0 },
+          "range": { "quantity": { "gte": 1 } },
+          "exclude": { "channelExclusion": 0 },
+        },
+      },
+      "context": { "cart": {}, "shippingCountry": "US", "userProfile": {} },
+      "settings": { "useFuzzySearch": false, "didYouMean": {} },
+      "sort": {},
+    };
 
-    if (!response.ok) {
-      console.error('Error notifying sync completion:', response.statusText);
-      process.exit(1);
+    let response: Response | null = null;
+    let lastError: unknown = null;
+    for (let attempt = 1; attempt <= 3; attempt++) {
+      try {
+        const r = await fetch('https://mp-search-api.tcgplayer.com/v1/search/request?q=&isList=false', {
+          method: 'POST',
+          headers: { 'Content-Type': 'application/json' },
+          body: JSON.stringify(d),
+        });
+        if (r.ok) {
+          response = r;
+          break;
+        }
+        lastError = new Error(`TCGPlayer search request failed: ${r.status} ${r.statusText}`);
+      } catch (err) {
+        lastError = err;
+      }
+      if (attempt < 3) {
+        log(`   retry ${attempt}/2 for search request in 5s...`);
+        await helper.Sleep(5000);
+      }
+    }
+    if (!response) {
+      throw lastError instanceof Error
+        ? lastError
+        : new Error(`TCGPlayer search request failed: ${String(lastError)}`);
     }
 
     const data = await response.json();
@@ -108,28 +88,43 @@ async function syncProductLine(productLine: string, field: string, fieldValue: s
 
     for (const item of data.results[0].results) {
 
-      // Check if productId already exists and skip if it does (to avoid hitting the API too much)
       if (allProductIds.size > 0 && allProductIds.has(item.productId)) {
         continue;
       }
 
-      console.log(chalk.blue(` - ${item.productName} (ID: ${item.productId})`));
+      log(` - ${item.productName} (ID: ${item.productId})`);
 
-      // Get product detail
-      const detailResponse = await fetch(`https://mp-search-api.tcgplayer.com/v2/product/${item.productId}/details`, {
-        method: 'GET',
-      });
-      if (!detailResponse.ok) {
-        console.error('Error fetching product details:', detailResponse.statusText);
-        process.exit(1);
+      let detailResponse: Response | null = null;
+      let lastError: unknown = null;
+      for (let attempt = 1; attempt <= 3; attempt++) {
+        try {
+          const r = await fetch(`https://mp-search-api.tcgplayer.com/v2/product/${item.productId}/details`, {
+            method: 'GET',
+          });
+          if (r.ok) {
+            detailResponse = r;
+            break;
+          }
+          lastError = new Error(`Error fetching product details for ${item.productId}: ${r.statusText}`);
+        } catch (err) {
+          lastError = err;
+        }
+        if (attempt < 3) {
+          log(`   retry ${attempt}/2 for product ${item.productId} in 5s...`);
+          await helper.Sleep(5000);
+        }
+      }
+      if (!detailResponse) {
+        throw lastError instanceof Error
+          ? lastError
+          : new Error(`Error fetching product details for ${item.productId}: ${String(lastError)}`);
       }
       const detailData = await detailResponse.json();
 
 
-      await db.insert(schema.tcgcards).values({
+      await database.insert(schema.tcgcards).values({
         productId: item.productId,
         productName: detailData.productName,
-        //productName: cleanProductName(item.productName),
         rarityName: item.rarityName,
         productLineName: detailData.productLineName,
         productLineUrlName: detailData.productLineUrlName,
@@ -167,7 +162,6 @@ async function syncProductLine(productLine: string, field: string, fieldValue: s
         target: schema.tcgcards.productId,
         set: {
           productName: detailData.productName,
-          //productName: cleanProductName(item.productName),
           rarityName: item.rarityName,
           productLineName: detailData.productLineName,
           productLineUrlName: detailData.productLineUrlName,
@@ -204,11 +198,7 @@ async function syncProductLine(productLine: string, field: string, fieldValue: s
         },
       });
 
-      // console.log(`item: ${item.setId}\tdetail: ${detailData.setId}`);
-      // console.log(`item: ${item.setCode}\tdetail: ${detailData.setCode}`);
-      // console.log(`item: ${item.setName}\tdetail: ${detailData.setName}`);
-      // set is...
-      await db.insert(schema.sets).values({
+      await database.insert(schema.sets).values({
         setId: detailData.setId,
         setCode: detailData.setCode,
         setName: detailData.setName,
@@ -222,10 +212,8 @@ async function syncProductLine(productLine: string, field: string, fieldValue: s
         },
       });
 
-      // skus are...
       for (const skuItem of detailData.skus) {
-
-        await db.insert(schema.skus).values({
+        await database.insert(schema.skus).values({
           skuId: skuItem.sku,
           productId: detailData.productId,
           condition: skuItem.condition,
@@ -241,7 +229,6 @@ async function syncProductLine(productLine: string, field: string, fieldValue: s
         });
       }
 
-      // get image if it doesn't already exist
       const imagePath = path.join(process.cwd(), 'static', 'cards', `${item.productId}.jpg`);
       if (!await helper.FileExists(imagePath)) {
         const imageResponse = await fetch(`https://tcgplayer-cdn.tcgplayer.com/product/${item.productId}_in_1000x1000.jpg`);
@@ -249,37 +236,88 @@ async function syncProductLine(productLine: string, field: string, fieldValue: s
           const buffer = await imageResponse.arrayBuffer();
           await fs.writeFile(imagePath, Buffer.from(buffer));
         } else {
-          console.error(chalk.yellow(`Error fetching ${item.productId}: ${item.productName} image:`, imageResponse.statusText));
+          log(`Error fetching ${item.productId}: ${item.productName} image: ${imageResponse.statusText}`);
           await fs.appendFile('missing_images.log', `${item.productId}: ${item.productName}\n`, 'utf-8');
         }
       }
 
-      // be nice to the API and not send too many requests in a short time
       await helper.Sleep(300);
-
     }
 
     start += size;
   }
+};
+
+
+const syncTcgplayer = async (database: DBInstance, cardSets: string[], allProductIds: Set<number>, log: Logger) => {
+  const productLines = ["pokemon", "pokemon-japan"];
+
+  for (const productLine of productLines) {
+    const d = {
+      "algorithm": "sales_dismax", "from": 0, "size": 1,
+      "filters": { "term": { "productLineName": [productLine] } },
+      "settings": { "useFuzzySearch": false },
+    };
+
+    const response = await fetch('https://mp-search-api.tcgplayer.com/v1/search/request?q=&isList=false', {
+      method: 'POST',
+      headers: { 'Content-Type': 'application/json' },
+      body: JSON.stringify(d),
+    });
+    if (!response.ok) {
+      throw new Error(`TCGPlayer setName aggregation failed: ${response.status} ${response.statusText}`);
+    }
+    const data = await response.json();
+
+    const setNames = data.results[0].aggregations.setName;
+    for (const setName of setNames) {
+      let processSet = true;
+      if (cardSets.length > 0) {
+        processSet = cardSets.some(set => setName.value.toLowerCase().includes(set.toLowerCase()));
+      }
+      if (processSet) {
+        log(`Syncing product line "${productLine}" with setName "${setName.urlValue}"...`);
+        await syncProductLine(database, productLine, "setName", setName.urlValue, allProductIds, log);
+      }
+    }
+  }
+
+  log('All TCGPlayer data synchronized successfully!');
+};
+
+
+export const runImport = async (opts: RunImportOptions = {}) => {
+  const { sets = [], log = consoleLogger, runUpdateVariants = true, runCardUpsert = true } = opts;
+
+  await fs.rm('missing_images.log', { force: true });
+
+  // When no set filter is provided, skip productIds already in the cards table
+  // (matches the CLI script's "no args" behavior).
+  const allProductIds = sets.length === 0
+    ? new Set<number>(
+        await db.select({ productId: schema.cards.productId }).from(schema.cards)
+          .then(rows => rows.map(row => row.productId))
+      )
+    : new Set<number>();
+
+  await syncTcgplayer(db, sets, allProductIds, log);
+
+  if (runUpdateVariants) {
+    log('Updating card variants...');
+    await helper.UpdateVariants(db, log);
+  }
+
+  if (runCardUpsert) {
+    log('Reindexing "cards" collection...');
+    await helper.upsertCardCollection(db, log);
+  }
+};
+
+
+// CLI entry point — preserves the original `tsx scripts/preload-tcgplayer.ts [set...]` usage.
+const isCli = process.argv[1] && import.meta.url === pathToFileURL(process.argv[1]).href;
+if (isCli) {
+  const args = process.argv.slice(2);
+  await runImport({ sets: args });
+  await ClosePool();
 }
-
-// clear the log file
-await fs.rm('missing_images.log', { force: true });
-let allProductIds = new Set();
-
-const args = process.argv.slice(2);
-if (args.length === 0) {
-  allProductIds = new Set(await db.select({ productId: schema.cards.productId }).from(schema.cards).then(rows => rows.map(row => row.productId)));
-  await syncTcgplayer();
-}
-else {
-  await syncTcgplayer(args);
-}
-
-// update the card table with new/updated variants
-await helper.UpdateVariants(db);
-
-// index the card updates
-await helper.upsertCardCollection(db);
-
-await ClosePool();

scripts/sync-prices.ts

@@ -154,6 +154,7 @@ const updateLatestSales = async (updatedCards: Set<number>) => {
 const start = Date.now();
 const updatedCards = await syncPrices();
 await helper.upsertSkuCollection(db);
+await helper.upsertCardCollection(db);  
 //console.log(updatedCards);
 //console.log(updatedCards.size);
 //await updateLatestSales(updatedCards);

src/assets/css/_bootstrap.scss

@@ -18,7 +18,7 @@
 @import 'bootstrap/scss/containers';
 @import 'bootstrap/scss/images';
 @import 'bootstrap/scss/nav';
-// @import 'bootstrap/scss/accordion';
+@import 'bootstrap/scss/accordion';
 @import 'bootstrap/scss/alert';
 @import 'bootstrap/scss/badge';
 // @import 'bootstrap/scss/breadcrumb';

src/assets/css/main.scss

@@ -185,6 +185,10 @@ $colors: mauve, lilac, "purple", "orchid", "snow";
   }
 }
 
+.navbar-nav > .nav-item > .nav-link:hover {
+  color: rgba(255, 255, 255, 1);
+}
+
 .nav-tabs {
   .nav-link.active,
   .nav-item.show .nav-link {
@@ -1170,4 +1174,4 @@ input[type="search"]::-webkit-search-cancel-button {
   background-size: 1rem;
   background-image: url(data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAB4AAAAeCAYAAAA7MK6iAAAAn0lEQVR42u3UMQrDMBBEUZ9WfQqDmm22EaTyjRMHAlM5K+Y7lb0wnUZPIKHlnutOa+25Z4D++MRBX98MD1V/trSppLKHqj9TTBWKcoUqffbUcbBBEhTjBOV4ja4l4OIAZThEOV6jHO8ARXD+gPPvKMABinGOrnu6gTNUawrcQKNCAQ7QeTxORzle3+sDfjJpPCqhJh7GixZq4rHcc9l5A9qZ+WeBhgEuAAAAAElFTkSuQmCC);
 }
--------------------------------------------------- */
+-------------------------------------------------- */

src/components/CardGrid.astro

@@ -49,6 +49,13 @@ import BackToTop from "./BackToTop.astro"
 <script is:inline>
 (function () {
 
+  // ── Tooltip initializer ───────────────────────────────────────────────────
+  function initTooltips(root = document) {
+    root.querySelectorAll('[data-bs-toggle="tooltip"]').forEach(el => {
+      bootstrap.Tooltip.getOrCreateInstance(el, { container: 'body' });
+    });
+  }
+
   // ── Price mode helpers ────────────────────────────────────────────────────
   // marketPriceByCondition is injected into the modal HTML via a data attribute
   // on #inventoryEntryList: data-market-prices='{"Near Mint":6.00,...}'
@@ -246,6 +253,9 @@ import BackToTop from "./BackToTop.astro"
 
   // ── Global helpers ────────────────────────────────────────────────────────
   window.copyImage = async function(img) {
+    const isIOS = /iPad|iPhone|iPod/.test(navigator.userAgent) ||
+      (navigator.platform === 'MacIntel' && navigator.maxTouchPoints > 1);
+
     try {
       const canvas = document.createElement("canvas");
       const ctx = canvas.getContext("2d");
@@ -260,10 +270,17 @@ import BackToTop from "./BackToTop.astro"
         clean.src = img.src;
       });
 
+      const blob = await new Promise((resolve, reject) => {
+        canvas.toBlob(b => b ? resolve(b) : reject(new Error('toBlob failed')), 'image/png');
+      });
+
+      if (isIOS) {
+        const file = new File([blob], 'card.png', { type: 'image/png' });
+        await navigator.share({ files: [file] });
+        return;
+      }
+
       if (navigator.clipboard && navigator.clipboard.write) {
-        const blob = await new Promise((resolve, reject) => {
-          canvas.toBlob(b => b ? resolve(b) : reject(new Error('toBlob failed')), 'image/png');
-        });
         await navigator.clipboard.write([new ClipboardItem({ "image/png": blob })]);
         showCopyToast('📋 Image copied!', '#198754');
       } else {
@@ -282,6 +299,7 @@ import BackToTop from "./BackToTop.astro"
         }
       }
     } catch (err) {
+      if (err.name === 'AbortError') return;
       console.error('Failed:', err);
       showCopyToast('❌ Copy failed', '#dc3545');
     }
@@ -414,6 +432,7 @@ import BackToTop from "./BackToTop.astro"
 
       if (typeof htmx !== 'undefined') htmx.process(modal);
       initInventoryForms(modal);
+      initTooltips(modal);
       updateNavButtons(modal);
       initChartAfterSwap(modal);
       switchToRequestedTab();
@@ -509,6 +528,7 @@ import BackToTop from "./BackToTop.astro"
 
         if (typeof htmx !== 'undefined') htmx.process(target);
         initInventoryForms(target);
+        initTooltips(target);
 
         const destImg = target.querySelector('img.card-image');
         if (destImg) {
@@ -638,6 +658,7 @@ import BackToTop from "./BackToTop.astro"
     updateNavButtons(cardModal);
     initChartAfterSwap(cardModal);
     initInventoryForms(cardModal);
+    initTooltips(cardModal);
     switchToRequestedTab();
   });
 
@@ -648,6 +669,7 @@ import BackToTop from "./BackToTop.astro"
 
   document.addEventListener('DOMContentLoaded', () => {
     initInventoryForms();
+    initTooltips();
 
     const pending = sessionStorage.getItem('pendingSearch');
     if (pending) {
@@ -656,7 +678,7 @@ import BackToTop from "./BackToTop.astro"
         if (input) input.value = pending;
         // The form's hx-trigger="load" will fire automatically on page load,
         // picking up the pre-populated input value — no manual trigger needed.
-    }    
+    }
   });
 
 })();

src/components/LeftSidebarDesktop.astro

@@ -0,0 +1,7 @@
+<div class="d-none d-xl-block sticky-top mt-5" style="top: 70px;">
+<ins class="adsbygoogle"
+     style="display:block"
+     data-ad-format="autorelaxed"
+     data-ad-client="ca-pub-1140571217687341"
+     data-ad-slot="8889263515"></ins>
+</div>

src/components/SetIcon.astro

@@ -125,6 +125,7 @@ import destined_rivals from "/src/svg/set/destined_rivals.svg?raw";
 import surging_sparks from "/src/svg/set/surging_sparks.svg?raw";
 import team_rocket from "/src/svg/set/team_rocket.svg?raw";
 import perfect_order from "/src/svg/set/perfect_order.svg?raw";
+import chaos_rising from "/src/svg/set/chaos_rising.svg?raw";
 
 const { set } = Astro.props;
 
@@ -254,6 +255,7 @@ const setMap = {
 "DRI": destined_rivals,
 "SSP": surging_sparks,
 "ME03": perfect_order,
+"CRI": chaos_rising,
 };
 
 const svg = setMap[set as keyof typeof setMap] ?? "";

src/db/schema.ts

@@ -56,6 +56,7 @@ export const cards = pokeSchema.table('cards', {
 	cardType: varchar({ length: 100 }),
 	energyType: varchar({ length: 100 }),
 	number: varchar({ length: 50 }),
+	inumber: integer(),
 	artist: varchar({ length: 255 }),
 },
 (table) => [

src/layouts/Main.astro

@@ -19,6 +19,7 @@ const { title } = Astro.props;
     <meta charset="UTF-8" />
     <meta name="viewport" content="width=device-width, initial-scale=1.0" />
     <meta name="htmx-config" content='{"historyCacheSize": 50}'/>
+    <meta name="google-adsense-account" content="ca-pub-1140571217687341">
     <link rel="icon" type="image/svg+xml" href="/favicon.svg" />
     <link rel="icon" href="/favicon.ico" />
     <title>{title}</title>
@@ -45,4 +46,4 @@ const { title } = Astro.props;
     <script src="../assets/js/main.js"></script>
     <script>import '../assets/js/priceChart.js';</script>
   </body>
-</html>
+</html>

src/middleware.ts

@@ -1,7 +1,6 @@
 import { clerkMiddleware, createRouteMatcher, clerkClient } from '@clerk/astro/server';
-import type { MiddlewareNext } from 'astro';
 import 'dotenv/config';
-
+ 
 declare global {
   namespace App {
     interface Locals {
@@ -9,19 +8,23 @@ declare global {
     }
   }
 }
-
+ 
 const isProtectedRoute = createRouteMatcher(['/pokemon']);
-const isAdminRoute = createRouteMatcher(['/admin']);
+const isAdminRoute = createRouteMatcher(['/admin', '/api/reindex', '/api/preload-tcgplayer']);
 
 const TARGET_ORG_ID = "org_3Baav9czkRLLlC7g89oJWqRRulK";
-
+const ADMIN_ORG_IDS = new Set([
+  "org_3Baav9czkRLLlC7g89oJWqRRulK",
+  "org_3ABdwuK3qD7Saq590ZMQWY7AvVz",
+]);
+ 
 export const onRequest = clerkMiddleware(async (auth, context, next) => {
   const { isAuthenticated, userId, redirectToSignIn, has } = auth();
-
+ 
   if (!isAuthenticated && isProtectedRoute(context.request)) {
     return redirectToSignIn();
   }
-
+ 
   // ── Inventory visibility check ──────────────────────────────────────────────
   // Resolves to true if the user belongs to the target org OR has the feature
   const canAddInventory = process.env.INVENTORY_ACCESS === 'true' ||
@@ -33,27 +36,38 @@ export const onRequest = clerkMiddleware(async (auth, context, next) => {
         (await getUserOrgIds(context, userId)).includes(TARGET_ORG_ID)
       )
     );
-
+ 
   // Expose the flag to your Astro pages via locals
   context.locals.canAddInventory = Boolean(canAddInventory);
-
-  // ── Admin route guard (unchanged) ───────────────────────────────────────────
+ 
+  // ── Admin route guard ───────────────────────────────────────────
   if (isAdminRoute(context.request)) {
     if (!isAuthenticated || !userId) {
       return redirectToSignIn();
     }
-
+ 
     try {
       const client = await clerkClient(context);
-      const memberships = await client.organizations.getOrganizationMembershipList({
-        organizationId: TARGET_ORG_ID,
-      });
-
-      const userMembership = memberships.data.find(
-        (m) => m.publicUserData?.userId === userId
+      const userOrgIds = await getUserOrgIds(context, userId);
+      const matchingOrgIds = userOrgIds.filter((id) => ADMIN_ORG_IDS.has(id));
+ 
+      if (matchingOrgIds.length === 0) {
+        return new Response(null, { status: 404 });
+      }
+ 
+      const membershipLists = await Promise.all(
+        matchingOrgIds.map((orgId) =>
+          client.organizations.getOrganizationMembershipList({ organizationId: orgId })
+        )
       );
-
-      if (!userMembership || userMembership.role !== "org:admin") {
+ 
+      const isAdmin = membershipLists.some((list) =>
+        list.data.some(
+          (m) => m.publicUserData?.userId === userId && m.role === "org:admin"
+        )
+      );
+ 
+      if (!isAdmin) {
         return new Response(null, { status: 404 });
       }
     } catch (e) {
@@ -61,10 +75,10 @@ export const onRequest = clerkMiddleware(async (auth, context, next) => {
       return context.redirect("/");
     }
   }
-
+ 
   return next();
 });
-
+ 
 // ── Helper: fetch all org IDs the current user belongs to ───────────────────
 async function getUserOrgIds(context: any, userId: string): Promise<string[]> {
   try {

src/pages/admin.astro

@@ -0,0 +1,225 @@
+---
+export const prerender = false;
+import Layout from '../layouts/Main.astro';
+import NavItems from '../components/NavItems.astro';
+import NavBar from '../components/NavBar.astro';
+import Footer from '../components/Footer.astro';
+---
+<Layout title="Admin Panel">
+  <NavBar slot="navbar">
+    <NavItems slot="navItems" />
+  </NavBar>
+  <div slot="page">
+    <div class="container my-4">
+      <div class="row mb-4">
+        <div class="col-12">
+          <h1>Admin Panel</h1>
+
+          <div class="accordion" id="adminAccordion">
+          <div class="accordion-item">
+            <h2 class="accordion-header" id="reindexHeading">
+              <button class="accordion-button collapsed" type="button" data-bs-toggle="collapse"
+                      data-bs-target="#reindexCollapse" aria-expanded="false" aria-controls="reindexCollapse">
+                Reindex
+              </button>
+            </h2>
+            <div id="reindexCollapse" class="accordion-collapse collapse" aria-labelledby="reindexHeading"
+                data-bs-parent="#adminAccordion">
+              <div class="accordion-body">
+                <form id="reindexForm">
+                  <div class="mb-3">
+                    <div class="form-text mb-2">Select collections to reindex:</div>
+                    <div class="form-check">
+                      <input class="form-check-input" type="checkbox" id="reindexCards" name="cards" checked />
+                      <label class="form-check-label" for="reindexCards">Cards</label>
+                    </div>
+                    <div class="form-check">
+                      <input class="form-check-input" type="checkbox" id="reindexSkus" name="skus" checked />
+                      <label class="form-check-label" for="reindexSkus">SKUs</label>
+                    </div>
+                    <div class="form-check">
+                      <input class="form-check-input" type="checkbox" id="reindexInventory" name="inventory" checked />
+                      <label class="form-check-label" for="reindexInventory">Inventory</label>
+                    </div>
+                  </div>
+                  <div class="mb-3 form-check">
+                    <input class="form-check-input" type="checkbox" id="reindexRecreate" name="recreate" />
+                    <label class="form-check-label" for="reindexRecreate">
+                      Recreate index (drops and recreates collections; otherwise updates in place)
+                    </label>
+                  </div>
+                  <button type="submit" class="btn btn-purple" id="reindexRun">Run Reindex</button>
+                </form>
+              </div>
+            </div>
+          </div>
+
+          <div class="accordion-item">
+            <h2 class="accordion-header" id="tcgImportHeading">
+              <button class="accordion-button collapsed" type="button" data-bs-toggle="collapse"
+                      data-bs-target="#tcgImportCollapse" aria-expanded="false" aria-controls="tcgImportCollapse">
+                TCG Player Import
+              </button>
+            </h2>
+            <div id="tcgImportCollapse" class="accordion-collapse collapse" aria-labelledby="tcgImportHeading"
+                data-bs-parent="#adminAccordion">
+              <div class="accordion-body">
+                <form id="tcgImportForm">
+                  <div class="mb-3">
+                    <label for="tcgImportSetName" class="form-label">Set Name</label>
+                    <input type="text" class="form-control" id="tcgImportSetName" name="setName"
+                           placeholder="e.g. Surging Sparks" autocomplete="off" required />
+                    <div class="form-text">Matches any set whose name contains this text (case-insensitive).</div>
+                  </div>
+                  <button type="submit" class="btn btn-purple" id="tcgImportRun">Run Import</button>
+                </form>
+              </div>
+            </div>
+          </div>
+        </div>
+        </div>
+      </div>
+    </div>
+
+    <!-- Reusable scrollable progress modal. Open via window.AdminProgress.open(title). -->
+    <div class="modal fade" id="adminProgressModal" tabindex="-1" aria-labelledby="adminProgressLabel" aria-hidden="true"
+         data-bs-backdrop="static" data-bs-keyboard="false">
+    <div class="modal-dialog modal-lg modal-dialog-scrollable">
+      <div class="modal-content">
+        <div class="modal-header">
+          <h5 class="modal-title" id="adminProgressLabel">Progress</h5>
+          <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"
+                  id="adminProgressClose" disabled></button>
+        </div>
+        <div class="modal-body p-0">
+          <pre id="adminProgressLog"
+               class="m-0 p-3 small"
+               style="max-height: 60vh; overflow-y: auto; white-space: pre-wrap; word-break: break-word;"></pre>
+        </div>
+        <div class="modal-footer">
+          <span class="me-auto small text-secondary" id="adminProgressStatus">Idle</span>
+          <button type="button" class="btn btn-secondary" data-bs-dismiss="modal"
+                  id="adminProgressDismiss" disabled>Close</button>
+        </div>
+        </div>
+      </div>
+    </div>
+  </div>
+
+  <Footer slot="footer" />
+</Layout>
+
+<script>
+  // Reusable progress modal. Other admin features can call window.AdminProgress.
+  type ProgressHandle = {
+    append: (line: string) => void;
+    setStatus: (text: string) => void;
+    done: (text?: string) => void;
+  };
+
+  declare global {
+    interface Window {
+      AdminProgress: {
+        open: (title: string) => Promise<ProgressHandle>;
+      };
+    }
+  }
+
+  const getBootstrap = (): any => (window as any).bootstrap;
+
+  const openProgress = async (title: string): Promise<ProgressHandle> => {
+    const modalEl = document.getElementById('adminProgressModal')!;
+    const labelEl = document.getElementById('adminProgressLabel')!;
+    const logEl = document.getElementById('adminProgressLog')!;
+    const statusEl = document.getElementById('adminProgressStatus')!;
+    const closeBtn = document.getElementById('adminProgressClose') as HTMLButtonElement;
+    const dismissBtn = document.getElementById('adminProgressDismiss') as HTMLButtonElement;
+
+    labelEl.textContent = title;
+    logEl.textContent = '';
+    statusEl.textContent = 'Running...';
+    closeBtn.disabled = true;
+    dismissBtn.disabled = true;
+
+    const modal = getBootstrap().Modal.getOrCreateInstance(modalEl);
+    modal.show();
+
+    return {
+      append: (line: string) => {
+        logEl.textContent += (logEl.textContent ? '\n' : '') + line;
+        logEl.scrollTop = logEl.scrollHeight;
+      },
+      setStatus: (text: string) => { statusEl.textContent = text; },
+      done: (text = 'Done') => {
+        statusEl.textContent = text;
+        closeBtn.disabled = false;
+        dismissBtn.disabled = false;
+      },
+    };
+  };
+
+  window.AdminProgress = { open: openProgress };
+
+  // Stream a POST JSON request line-by-line into a progress modal.
+  const streamToProgress = async (url: string, body: unknown, title: string, runBtn: HTMLButtonElement) => {
+    runBtn.disabled = true;
+    const progress = await window.AdminProgress.open(title);
+    try {
+      const resp = await fetch(url, {
+        method: 'POST',
+        headers: { 'Content-Type': 'application/json' },
+        body: JSON.stringify(body),
+      });
+      if (!resp.ok || !resp.body) {
+        progress.append(`Request failed: ${resp.status} ${resp.statusText}`);
+        progress.done('Failed');
+        return;
+      }
+      const reader = resp.body.getReader();
+      const decoder = new TextDecoder();
+      let buf = '';
+      while (true) {
+        const { value, done } = await reader.read();
+        if (done) break;
+        buf += decoder.decode(value, { stream: true });
+        const lines = buf.split('\n');
+        buf = lines.pop() ?? '';
+        for (const line of lines) progress.append(line);
+      }
+      if (buf) progress.append(buf);
+      progress.done('Done');
+    } catch (err: any) {
+      progress.append(`Error: ${err?.message || String(err)}`);
+      progress.done('Failed');
+    } finally {
+      runBtn.disabled = false;
+    }
+  };
+
+  // Reindex form wiring
+  const reindexForm = document.getElementById('reindexForm') as HTMLFormElement | null;
+  if (reindexForm) {
+    reindexForm.addEventListener('submit', (e) => {
+      e.preventDefault();
+      const body = {
+        cards: (document.getElementById('reindexCards') as HTMLInputElement).checked,
+        skus: (document.getElementById('reindexSkus') as HTMLInputElement).checked,
+        inventory: (document.getElementById('reindexInventory') as HTMLInputElement).checked,
+        recreate: (document.getElementById('reindexRecreate') as HTMLInputElement).checked,
+      };
+      streamToProgress('/api/reindex', body, 'Reindex',
+        document.getElementById('reindexRun') as HTMLButtonElement);
+    });
+  }
+
+  // TCG Player import form wiring
+  const tcgImportForm = document.getElementById('tcgImportForm') as HTMLFormElement | null;
+  if (tcgImportForm) {
+    tcgImportForm.addEventListener('submit', (e) => {
+      e.preventDefault();
+      const setName = (document.getElementById('tcgImportSetName') as HTMLInputElement).value.trim();
+      streamToProgress('/api/preload-tcgplayer', { setName }, `TCG Player Import: ${setName || '(none)'}`,
+        document.getElementById('tcgImportRun') as HTMLButtonElement);
+    });
+  }
+</script>

src/pages/api/preload-tcgplayer.ts

@@ -0,0 +1,43 @@
+import type { APIRoute } from 'astro';
+import { runImport } from '../../../scripts/preload-tcgplayer';
+
+export const POST: APIRoute = async ({ request }) => {
+    const { setName } = await request.json().catch(() => ({} as any));
+    const trimmed = typeof setName === 'string' ? setName.trim() : '';
+
+    const encoder = new TextEncoder();
+    const stream = new ReadableStream({
+        async start(controller) {
+            const log = (msg: string) => {
+                controller.enqueue(encoder.encode(msg + '\n'));
+            };
+
+            try {
+                if (!trimmed) {
+                    log('Set name is required.');
+                    return;
+                }
+
+                log(`Starting TCGPlayer import for set: "${trimmed}"`);
+                await runImport({ sets: [trimmed], log });
+                log('TCGPlayer import complete.');
+            } catch (e: any) {
+                const cause = e?.cause;
+                const causeMsg = cause?.message || (cause ? String(cause) : '');
+                log(`Error: ${e?.message || String(e)}`);
+                if (causeMsg) log(`Caused by: ${causeMsg}`);
+                console.error('TCGPlayer import error:', e);
+            } finally {
+                controller.close();
+            }
+        },
+    });
+
+    return new Response(stream, {
+        headers: {
+            'Content-Type': 'text/plain; charset=utf-8',
+            'Cache-Control': 'no-cache, no-transform',
+            'X-Accel-Buffering': 'no',
+        },
+    });
+};

src/pages/api/reindex.ts

@@ -0,0 +1,71 @@
+import type { APIRoute } from 'astro';
+import { db } from '../../db/index';
+import * as Indexing from '../../../scripts/pokemon-helper';
+
+export const POST: APIRoute = async ({ request }) => {
+    const { cards, skus, inventory, recreate } = await request.json().catch(() => ({} as any));
+
+    const encoder = new TextEncoder();
+    const stream = new ReadableStream({
+        async start(controller) {
+            const log = (msg: string) => {
+                controller.enqueue(encoder.encode(msg + '\n'));
+            };
+
+            try {
+                if (!cards && !skus && !inventory) {
+                    log('No collections selected.');
+                    return;
+                }
+
+                if (recreate) {
+                    if (cards) {
+                        log('Recreating "cards" collection...');
+                        await Indexing.createCardCollection(log);
+                    }
+                    if (skus) {
+                        log('Recreating "skus" collection...');
+                        await Indexing.createSkuCollection(log);
+                    }
+                    if (inventory) {
+                        log('Recreating "inventories" collection...');
+                        await Indexing.createInventoryCollection(log);
+                    }
+                }
+
+                if (cards) {
+                    log('Indexing "cards"...');
+                    await Indexing.upsertCardCollection(db, log);
+                }
+                if (skus) {
+                    log('Indexing "skus"...');
+                    await Indexing.upsertSkuCollection(db, log);
+                }
+                if (inventory) {
+                    log('Indexing "inventories"...');
+                    await Indexing.upsertInventoryCollection(db, log);
+                }
+
+                log('Reindex complete.');
+            } catch (e: any) {
+                const cause = e?.cause;
+                const causeMsg = cause?.message || (cause ? String(cause) : '');
+                const causeDetail = cause?.detail ? ` | detail: ${cause.detail}` : '';
+                const causeCode = cause?.code ? ` | code: ${cause.code}` : '';
+                log(`Error: ${e?.message || String(e)}`);
+                if (causeMsg) log(`Caused by: ${causeMsg}${causeCode}${causeDetail}`);
+                console.error('Reindex error:', e);
+            } finally {
+                controller.close();
+            }
+        },
+    });
+
+    return new Response(stream, {
+        headers: {
+            'Content-Type': 'text/plain; charset=utf-8',
+            'Cache-Control': 'no-cache, no-transform',
+            'X-Accel-Buffering': 'no',
+        },
+    });
+};

src/pages/partials/card-modal.astro

@@ -55,9 +55,36 @@ const calculatedAt = (() => {
   return new Date(Math.max(...dates.map(d => d.getTime())));
 })();
 
-// ── Fetch price history + compute volatility ──────────────────────────────
+// ── Spread-based volatility (high - low) / low ────────────────────────────
+// Log-return volatility was unreliable because marketPrice is a smoothed daily
+// value, not transaction-driven. The 30-day high/low spread is a more honest
+// proxy for price movement over the period.
+
+const volatilityByCondition: Record<string, { label: string; spread: number }> = {};
+
+for (const price of card?.prices ?? []) {
+  const condition = price.condition;
+  const low = Number(price.lowestPrice);
+  const high = Number(price.highestPrice);
+  const market = Number(price.marketPrice);
+
+  if (!low || !high || !market || market <= 0) {
+    volatilityByCondition[condition] = { label: '—', spread: 0 };
+    continue;
+  }
+
+  const spread = (high - low) / market;
+
+  const label = spread >= 0.50 ? 'High'
+              : spread >= 0.25 ? 'Medium'
+              : 'Low';
+
+  volatilityByCondition[condition] = { label, spread: Math.round(spread * 100) / 100 };
+}
+
+// ── Price history for chart ───────────────────────────────────────────────
 const cardSkus = card?.prices?.length
-  ? await db.select().from(skus).where(eq(skus.cardId, cardId))
+  ? await db.select().from(skus).where(eq(skus.cardId, card.cardId))
   : [];
 
 const skuIds = cardSkus.map(s => s.skuId);
@@ -76,41 +103,6 @@ const historyRows = skuIds.length
       .orderBy(priceHistory.calculatedAt)
   : [];
 
-// Rolling 30-day cutoff for volatility calculation
-const thirtyDaysAgo = new Date(Date.now() - 30 * 86_400_000);
-
-const byCondition: Record<string, number[]> = {};
-for (const row of historyRows) {
-  if (row.marketPrice == null) continue;
-  if (!row.calculatedAt) continue;
-  if (new Date(row.calculatedAt) < thirtyDaysAgo) continue;
-  const price = Number(row.marketPrice);
-  if (price <= 0) continue;
-  if (!byCondition[row.condition]) byCondition[row.condition] = [];
-  byCondition[row.condition].push(price);
-}
-
-function computeVolatility(prices: number[]): { label: string; monthlyVol: number } {
-  if (prices.length < 2) return { label: '—', monthlyVol: 0 };
-  const returns: number[] = [];
-  for (let i = 1; i < prices.length; i++) {
-    returns.push(Math.log(prices[i] / prices[i - 1]));
-  }
-  const mean = returns.reduce((a, b) => a + b, 0) / returns.length;
-  const variance = returns.reduce((sum, r) => sum + (r - mean) ** 2, 0) / (returns.length - 1);
-  const monthlyVol = Math.sqrt(variance) * Math.sqrt(30);
-  const label = monthlyVol >= 0.30 ? 'High'
-              : monthlyVol >= 0.15 ? 'Medium'
-              : 'Low';
-  return { label, monthlyVol: Math.round(monthlyVol * 100) / 100 };
-}
-
-const volatilityByCondition: Record<string, { label: string; monthlyVol: number }> = {};
-for (const [condition, prices] of Object.entries(byCondition)) {
-  volatilityByCondition[condition] = computeVolatility(prices);
-}
-
-// ── Price history for chart (full history, not windowed) ──────────────────
 const priceHistoryForChart = historyRows.map(row => ({
   condition: row.condition,
   calculatedAt: row.calculatedAt
@@ -119,29 +111,11 @@ const priceHistoryForChart = historyRows.map(row => ({
   marketPrice: row.marketPrice,
 })).filter(r => r.calculatedAt !== null);
 
-// ── Determine which range buttons to show ────────────────────────────────
-const now = Date.now();
-const oldestDate = historyRows.length
-  ? Math.min(...historyRows
-      .filter(r => r.calculatedAt)
-      .map(r => new Date(r.calculatedAt!).getTime()))
-  : now;
-
-const dataSpanDays = (now - oldestDate) / 86_400_000;
-
-const showRanges = {
-  '1m':  dataSpanDays >= 1,
-  '3m':  dataSpanDays >= 60,
-  '6m':  dataSpanDays >= 180,
-  '1y':  dataSpanDays >= 365,
-  'all': dataSpanDays >= 400,
-};
-
 const conditionOrder = ["Near Mint", "Lightly Played", "Moderately Played", "Heavily Played", "Damaged"];
 
 const conditionAttributes = (price: any) => {
   const condition: string = price?.condition || "Near Mint";
-  const vol = volatilityByCondition[condition] ?? { label: '—', monthlyVol: 0 };
+  const vol = volatilityByCondition[condition] ?? { label: '—', spread: 0 };
 
   const volatilityClass = (() => {
     switch (vol.label) {
@@ -154,7 +128,7 @@ const conditionAttributes = (price: any) => {
 
   const volatilityDisplay = vol.label === '—'
     ? '—'
-    : `${vol.label} (${(vol.monthlyVol * 100).toFixed(0)}%)`;
+    : `${vol.label} (${(vol.spread * 100).toFixed(0)}%)`;
 
   return {
     "Near Mint":        { label: "nav-nm",  volatility: volatilityDisplay, volatilityClass, class: "show active" },
@@ -177,7 +151,7 @@ for (const price of card?.prices ?? []) {
 const availableVariants = [...new Set(cardSkus.map(s => s.variant))].sort();
 
 const ebaySearchUrl = (card: any) => {
-  return `https://www.ebay.com/sch/i.html?_nkw=${encodeURIComponent(card?.productUrlName)}+${encodeURIComponent(card?.set?.setUrlName)}+${encodeURIComponent(card?.number)}&LH_Sold=1&Graded=No&_dcat=183454`;
+  return `https://www.ebay.com/sch/i.html?_nkw=${encodeURIComponent(card?.productUrlName)}+${encodeURIComponent(card?.set?.setUrlName)}+${encodeURIComponent(card?.number)}+${encodeURIComponent(card?.variant)}&LH_Sold=1&Graded=No&_dcat=183454`;
 };
 
 const altSearchUrl = (card: any) => {
@@ -205,9 +179,6 @@ const altSearchUrl = (card: any) => {
               <!-- Card image column -->
               <div class="col-sm-12 col-md-3">
                 <div class="position-relative mt-1">
-
-                  <!-- card-image-wrap gives the modal image shimmer effects
-                       without the hover lift/scale that image-grow has in main.scss -->
                   <div
                     class="card-image-wrap rounded-4"
                     data-energy={card?.energyType}
@@ -287,11 +258,11 @@ const altSearchUrl = (card: any) => {
                               <p class="mb-0 mt-1">${price.marketPrice}</p>
                             </div>
                             <div class="alert alert-dark rounded p-2 flex-fill d-flex flex-column mb-0">
-                              <h6 class="mb-auto">Lowest Price</h6>
+                              <h6 class="mb-auto">Low Price <span class="small p text-secondary">(30 day)</span></h6>
                               <p class="mb-0 mt-1">${price.lowestPrice}</p>
                             </div>
                             <div class="alert alert-dark rounded p-2 flex-fill d-flex flex-column mb-0">
-                              <h6 class="mb-auto">Highest Price</h6>
+                              <h6 class="mb-auto">High Price <span class="small p text-secondary">(30 day)</span></h6>
                               <p class="mb-0 mt-1">${price.highestPrice}</p>
                             </div>
                             <div class={`alert rounded p-2 flex-fill d-flex flex-column mb-0 ${attributes?.volatilityClass}`}>
@@ -306,14 +277,14 @@ const altSearchUrl = (card: any) => {
                                   data-bs-trigger="hover focus click"
                                   data-bs-html="true"
                                   data-bs-title={`
-                                    <div class='tooltip-heading fw-bold mb-1'>Monthly Volatility</div>
+                                    <div class='tooltip-heading fw-bold mb-1'>30-Day Price Spread</div>
                                     <div class='small'>
                                       <p class="mb-1">
-                                        <strong>What this measures:</strong> how much the market price tends to move day-to-day,
-                                        scaled up to a monthly expectation.
+                                        <strong>What this measures:</strong> how wide the gap between the 30-day low and high is,
+                                        relative to the market price.
                                       </p>
                                       <p class="mb-0">
-                                        A card with <strong>30% volatility</strong> typically swings ±30% over a month.
+                                        A card with <strong>50%+ spread</strong> has seen significant price swings over the past month.
                                       </p>
                                     </div>
                                   `}
@@ -566,16 +537,18 @@ const altSearchUrl = (card: any) => {
                         </canvas>
                       </div>
                       <div class="btn-group btn-group-sm d-flex flex-row gap-1 justify-content-end mt-2" role="group" aria-label="Time range">
-                        {showRanges['1m']  && <button type="button" class="btn btn-dark price-range-btn active" data-range="1m">1M</button>}
-                        {showRanges['3m']  && <button type="button" class="btn btn-dark price-range-btn" data-range="3m">3M</button>}
-                        {showRanges['6m']  && <button type="button" class="btn btn-dark price-range-btn" data-range="6m">6M</button>}
-                        {showRanges['1y']  && <button type="button" class="btn btn-dark price-range-btn" data-range="1y">1Y</button>}
-                        {showRanges['all'] && <button type="button" class="btn btn-dark price-range-btn" data-range="all">All</button>}
+                        <button type="button" class="btn btn-dark price-range-btn active" data-range="1m">1M</button>
+                        <button type="button" class="btn btn-dark price-range-btn" data-range="3m">3M</button>
+                        <button type="button" class="btn btn-dark price-range-btn" data-range="6m">6M</button>
+                        <button type="button" class="btn btn-dark price-range-btn" data-range="1y">1Y</button>
+                        <button type="button" class="btn btn-dark price-range-btn" data-range="all">All</button>
                       </div>
                     </div>
                   </div>
                 </div>
 
+                
+
               </div>
 
               <!-- External links column -->

src/pages/partials/cards.astro

@@ -22,14 +22,14 @@ const facetFields:any = {
 
 // ── Allowed sort values ───────────────────────────────────────────────────
 const sortMap: Record<string, string> = {
-    'releaseDate:desc,number:asc': '_text_match:asc,releaseDate:desc,number:asc',
-    'releaseDate:asc,number:asc':  '_text_match:asc,releaseDate:asc,number:asc',
-    'marketPrice:desc':            'marketPrice:desc,releaseDate:desc,number:asc',
-    'marketPrice:asc':             'marketPrice:asc,releaseDate:desc,number:asc',
-    'number:asc':                  '_text_match:asc,number:asc',
-    'number:desc':                 '_text_match:asc,number:desc',
+    'releaseDate:desc,number:asc': '_text_match:asc,releaseDate:desc,inumber(missing_values:last):asc',
+    'releaseDate:asc,number:asc':  '_text_match:asc,releaseDate:asc,inumber(missing_values:last):asc',
+    'marketPrice:desc':            'marketPrice:desc,releaseDate:desc,inumber(missing_values:last):asc',
+    'marketPrice:asc':             'marketPrice:asc,releaseDate:desc,inumber(missing_values:last):asc',
+    'number:asc':                  '_text_match:asc,inumber(missing_values:last):asc',
+    'number:desc':                 '_text_match:asc,inumber(missing_values:last):desc',
 };
-const DEFAULT_SORT = '_text_match:asc,releaseDate:desc,number:asc';
+const DEFAULT_SORT = '_text_match:asc,releaseDate:desc,inumber(missing_values:last):asc';
 
 // get the query from post request using form data
 const formData = await Astro.request.formData();
@@ -51,15 +51,53 @@ const languageFilter = language === 'en' ? " && productLineName:=`Pokemon`"
 // synonyms alone (e.g. terms that need to match across multiple set names)
 // and rewrites them into a direct filter, clearing the query so it doesn't
 // also try to text-match against card names.
-const EREADER_SETS = ['Expedition Base Set', 'Aquapolis', 'Skyridge', 'Battle-e'];
-const EREADER_RE = /^(e-?reader|e reader)$/i;
+
+const ALIAS_FILTERS = [
+    // ── Era / set groupings ───────────────────────────────────────────────
+    { re: /^(e-?reader|e reader)$/i, field: 'setName',
+      values: ['Expedition Base Set', 'Aquapolis', 'Skyridge', 'Battle-e'] },
+
+    { re: /^neo$/i, field: 'setName',
+      values: ['Neo Genesis', 'Neo Discovery', 'Neo Revelation', 'Neo Destiny'] },
+
+    { re: /^(wotc|wizards)$/i, field: 'setName',
+      values: ['Base Set', 'Jungle', 'Fossil', 'Base Set 2', 'Team Rocket',
+               'Gym Heroes', 'Gym Challenge', 'Neo Genesis', 'Neo Discovery',
+               'Neo Revelation', 'Neo Destiny', 'Expedition Base Set',
+               'Aquapolis', 'Skyridge', 'Battle-e'] },
+
+    { re: /^(sun\s*(&|and)\s*moon|s(&|and)m|sm)$/i, field: 'setName',
+      values: ['Sun & Moon', 'Guardians Rising', 'Burning Shadows', 'Crimson Invasion',
+               'Ultra Prism', 'Forbidden Light', 'Celestial Storm', 'Dragon Majesty',
+               'Lost Thunder', 'Team Up', 'Unbroken Bonds', 'Unified Minds',
+               'Hidden Fates', 'Cosmic Eclipse', 'Detective Pikachu'] },
+
+    { re: /^(sword\s*(&|and)\s*shield|s(&|and)s|swsh)$/i, field: 'setName',
+      values: ['Sword & Shield', 'Rebel Clash', 'Darkness Ablaze', 'Vivid Voltage',
+               'Battle Styles', 'Chilling Reign', 'Evolving Skies', 'Fusion Strike',
+               'Brilliant Stars', 'Astral Radiance', 'Pokemon GO', 'Lost Origin',
+               'Silver Tempest', 'Crown Zenith'] },
+
+    // ── Card type shorthands ──────────────────────────────────────────────
+    { re: /^trainers?$/i,         field: 'cardType', values: ['Trainer'] },
+    { re: /^supporters?$/i,       field: 'cardType', values: ['Supporter'] },
+    { re: /^stadiums?$/i,         field: 'cardType', values: ['Stadium'] },
+    { re: /^items?$/i,            field: 'cardType', values: ['Item'] },
+    { re: /^(energys?|energies)$/i, field: 'cardType', values: ['Energy'] },
+
+    // ── Rarity shorthands ─────────────────────────────────────────────────
+    { re: /^promos?$/i, field: 'rarityName', values: ['Promo'] },
+];
 
 let resolvedQuery = query;
 let queryFilter = '';
 
-if (EREADER_RE.test(query.trim())) {
-    resolvedQuery = '';
-    queryFilter = `setName:=[${EREADER_SETS.map(s => '`' + s + '`').join(',')}]`;
+for (const alias of ALIAS_FILTERS) {
+    if (alias.re.test(query.trim())) {
+        resolvedQuery = '';
+        queryFilter = `${alias.field}:=[${alias.values.map(s => '`' + s + '`').join(',')}]`;
+        break;
+    }
 }
 
 const filters = Array.from(formData.entries())
@@ -91,15 +129,15 @@ const facetFilter = (facet:string) => {
 
 
 // primary search values (for cards)
-let searchArray = [{
+// Note: no `$skus(...)` join here — see the sku fetch below for why.
+let searchArray: any[] = [{
     collection: 'cards',
-    filter_by: `$skus(id:*) && sealed:false${languageFilter}${queryFilter ? ` && ${queryFilter}` : ''}${filterBy ? ` && ${filterBy}` : ''}`,
+    filter_by: `sealed:false${languageFilter}${queryFilter ? ` && ${queryFilter}` : ''}${filterBy ? ` && ${filterBy}` : ''}`,
     per_page: 20,
     facet_by: '',
     max_facet_values: 0,
     page: Math.floor(start / 20) + 1,
     sort_by: resolvedSort,
-    include_fields: '$skus(*)',
 }];
 
 // on first load (start === 0) we want to get the facets for the filters
@@ -121,7 +159,10 @@ if (start === 0) {
 const searchRequests = { searches: searchArray };
 const commonSearchParams = {
     q: resolvedQuery,
-    query_by: 'content,setName,productLineName,rarityName,energyType,cardType'
+    query_by: 'content,setName,setCode,productName,Artist',
+    query_by_weights: '10,6,8,9,8',
+    num_typos: '2,1,0,1,2',
+    prefix: 'true,true,false,false,false',
 };
 
 // use typesense to search for cards matching the query and return the productIds of the results
@@ -131,6 +172,28 @@ const cardResults = searchResults.results[0] as any;
 const pokemon = cardResults.hits?.map((hit: any) => hit.document) ?? [];
 const totalHits = cardResults?.found;
 
+// Skus aren't used for searching or sorting — they only supply the per-condition
+// prices displayed on each card. Joining them into the primary search via
+// `$skus(id:*)` forces Typesense to materialize the reverse join across the whole
+// filtered result set before pagination (~20x slower). Instead, fetch skus for
+// just the visible cards in one direct, indexed filter and attach them by id.
+if (pokemon.length > 0) {
+    const cardIds = pokemon.map((c: any) => c.id);
+    const skuSearch = await client.collections('skus').documents().search({
+        q: '*',
+        query_by: 'condition',
+        filter_by: `card_id:=[${cardIds.map((id: string) => '`' + id + '`').join(',')}]`,
+        per_page: 250,
+        include_fields: 'condition,marketPrice,card_id',
+    });
+    const skusByCard: Record<string, any[]> = {};
+    for (const hit of (skuSearch.hits ?? []) as any[]) {
+        const sku = hit.document;
+        (skusByCard[sku.card_id] ??= []).push(sku);
+    }
+    for (const card of pokemon) card.skus = skusByCard[card.id] ?? [];
+}
+
 
 // format price to 2 decimal places (or 0 if price >=100) and adds a $ sign, if the price is null it returns "–"
 const formatPrice = (condition:string, skus: any) => {
@@ -280,20 +343,20 @@ const facets = searchResults.results.slice(1).map((result: any) => {
 }
 
 {pokemon.length === 0 && (
-    <div id="notfound" hx-swap-oob="true">
-        Pokemon not found
+    <div id="notfound" class="mt-4 h6" hx-swap-oob="true">
+        No cards found! Please modify your search and try again.
     </div>
 )}
 
 {pokemon.map((card:any) => (
-    <div class="col">
+    <div class="col equal-height-col">
         {canAddInventory && (
             <button type="button" class="btn btn-sm inventory-button position-relative float-end shadow-filter text-center p-2 fw-bold" data-card-id={card.cardId} hx-get={`/partials/card-modal?cardId=${card.cardId}`} hx-target="#cardModal" hx-trigger="click" data-bs-toggle="modal" data-bs-target="#cardModal" onclick="event.stopPropagation(); sessionStorage.setItem('openModalTab', 'nav-vendor');">
                 +/–
             </button>
         )}
         <div class="card-trigger position-relative" data-card-id={card.cardId} hx-get={`/partials/card-modal?cardId=${card.cardId}`} hx-target="#cardModal" hx-trigger="click" data-bs-toggle="modal" data-bs-target="#cardModal" onclick="const cardTitle = this.querySelector('#cardImage').getAttribute('alt'); dataLayer.push({'event': 'virtualPageview', 'pageUrl': this.getAttribute('hx-get'), 'pageTitle': cardTitle, 'previousUrl': '/pokemon'});">
-            <div class="image-grow rounded-4 card-image" data-energy={card.energyType} data-rarity={card.rarityName} data-variant={card.variant} data-name={card.productName}><img src={`/static/cards/${card.productId}.jpg`} alt={card.productName} id="cardImage" loading="lazy" decoding="async" class="img-fluid rounded-4 mb-2 w-100" onerror="this.onerror=null; this.src='/static/cards/default.jpg'; this.closest('.image-grow')?.setAttribute('data-default','true')"/><span class="position-absolute top-50 start-0 d-inline medium-icon"><FirstEditionIcon edition={card?.variant} /></span>
+            <div class="image-grow rounded-4 card-image" data-energy={card.energyType} data-rarity={card.rarityName} data-variant={card.variant} data-name={card.productName}><img src={`/static/cards/${card.productId}.jpg`} alt={card.productName} id="cardImage" loading="lazy" decoding="async" class="img-fluid rounded-4 mb-2 w-100 h-100" onerror="this.onerror=null; this.src='/static/cards/default.jpg'; this.closest('.image-grow')?.setAttribute('data-default','true')"/><span class="position-absolute top-50 start-0 d-inline medium-icon"><FirstEditionIcon edition={card?.variant} /></span>
               <div class="holo-shine"></div>
               <div class="holo-glare"></div>
             </div>
@@ -309,15 +372,15 @@ const facets = searchResults.results.slice(1).map((result: any) => {
         <div class="fs-5 fw-semibold my-0">{card.productName}</div>
         <div class="d-flex flex-row lh-1 mt-1 justify-content-between">
             <div class="text-body-tertiary flex-grow-1 d-none d-lg-flex fst-normal">{card.setName}</div>
+            <div class="text-body-tertiary flex-grow-1 d-flex d-lg-none fst-normal">{card.setCode}</div>
             <div class="text-body-tertiary fst-normal">{card.number}</div>
             <span class="ps-2 small-icon"><RarityIcon rarity={card.rarityName} /></span>
         </div>
         <div class="text-secondary fst-italic">{card.variant}</div><span class="d-none">{card.productId}</span>
     </div>
-
 ))}
 {start + 20 < totalHits &&
     <div hx-post="/partials/cards" hx-trigger="revealed" hx-include="#searchform" hx-target="#cardGrid" hx-swap="beforeend" hx-on--after-request="afterUpdate(event)">
         Loading...
     </div>
-}
+}

src/volatility.ts

@@ -0,0 +1,61 @@
+export interface VolatilityResult {
+  label: 'High' | 'Medium' | 'Low' | '—';
+  monthlyVol: number;
+}
+
+/**
+ * Computes 30-day rolling volatility from an array of prices using
+ * log-return standard deviation scaled to a monthly expectation.
+ *
+ * @param prices - Ordered array of market prices (oldest → newest)
+ * @returns label ('High' | 'Medium' | 'Low' | '—') and monthlyVol (0–1 range)
+ */
+export function computeVolatility(prices: number[]): VolatilityResult {
+  if (prices.length < 2) return { label: '—', monthlyVol: 0 };
+
+  const returns: number[] = [];
+  for (let i = 1; i < prices.length; i++) {
+    returns.push(Math.log(prices[i] / prices[i - 1]));
+  }
+
+  const mean     = returns.reduce((a, b) => a + b, 0) / returns.length;
+  const variance = returns.reduce((sum, r) => sum + (r - mean) ** 2, 0) / (returns.length - 1);
+  const monthlyVol = Math.sqrt(variance) * Math.sqrt(30);
+
+  const label: VolatilityResult['label'] =
+    monthlyVol >= 0.30 ? 'High'
+  : monthlyVol >= 0.15 ? 'Medium'
+  : 'Low';
+
+  return { label, monthlyVol: Math.round(monthlyVol * 100) / 100 };
+}
+
+/**
+ * Groups a list of price-history rows by condition and computes volatility
+ * for each, filtered to a rolling window.
+ *
+ * @param rows      - Array of { condition, calculatedAt, marketPrice }
+ * @param windowMs  - Rolling window in milliseconds (default: 30 days)
+ */
+export function volatilityByCondition(
+  rows: Array<{ condition: string; calculatedAt: string | Date | null; marketPrice: number | string | null }>,
+  windowMs = 30 * 86_400_000,
+): Record<string, VolatilityResult> {
+  const cutoff = new Date(Date.now() - windowMs);
+  const grouped: Record<string, number[]> = {};
+
+  for (const row of rows) {
+    if (row.marketPrice == null || !row.calculatedAt) continue;
+    if (new Date(row.calculatedAt) < cutoff) continue;
+    const price = Number(row.marketPrice);
+    if (price <= 0) continue;
+    if (!grouped[row.condition]) grouped[row.condition] = [];
+    grouped[row.condition].push(price);
+  }
+
+  const result: Record<string, VolatilityResult> = {};
+  for (const [condition, prices] of Object.entries(grouped)) {
+    result[condition] = computeVolatility(prices);
+  }
+  return result;
+}

tsconfig.json

@@ -1,5 +1,5 @@
 {
   "extends": "astro/tsconfigs/strict",
-  "include": [".astro/types.d.ts", "src/**/*"],
+  "include": [".astro/types.d.ts", "src/**/*", "scripts/**/*"],
   "exclude": ["dist"]
 }