From 538fd636e25595d88a958344d285c0e7cf44e530 Mon Sep 17 00:00:00 2001
From: yyamashita <yyamashita@mosquit.one>
Date: Wed, 6 May 2026 22:24:38 +0900
Subject: Async scraping, scrape_logs, and CLI
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Background scraping:
- POST /api/scrape returns 202 immediately with run_id; scraping runs async
- GET  /api/scrape-status?run_id=xxx polls for results per venue
- scrape_logs table: per-venue status (running/ok/error), events_saved, error, timestamps

CLI (npm run scrape):
- npm run scrape              — 全会場をスクレイプ、結果を色付きで出力
- npm run scrape liquid-room  — 特定会場のみ
- npm run scrape -- --list    — 登録済み会場一覧を表示
- エラー時は exit code 1 + エラーメッセージを dim 表示

Venues page:
- 最終スクレイプ日時・成否をインラインで表示
- 会場ごとの「更新」ボタンを追加

Bug fix: upsertEvent に description/optional fields のデフォルト値を設定し
better-sqlite3 の "Missing named parameter" エラーを解消

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
---
 app/routes/venues.tsx | 115 ++++++++++++++++++++++++++++++++++++--------------
 1 file changed, 84 insertions(+), 31 deletions(-)

(limited to 'app/routes/venues.tsx')

diff --git a/app/routes/venues.tsx b/app/routes/venues.tsx
index 23b052f..affa72a 100644
--- a/app/routes/venues.tsx
+++ b/app/routes/venues.tsx
@@ -1,17 +1,19 @@
-import { useLoaderData, Link } from "react-router";
+import { useLoaderData, Link, Form } from "react-router";
 import type { Route } from "./+types/venues";
-import { getVenues } from "~/lib/db.server";
+import { getVenues, getLastScrapePerVenue, type ScrapeLog } from "~/lib/db.server";
 import { getScraperIds } from "~/lib/venue-meta.server";
 
 export async function loader(_: Route.LoaderArgs) {
   const venues = getVenues();
   const scraperIds = getScraperIds();
-  return { venues, scraperIds };
+  const scrapeStatus = getLastScrapePerVenue();
+  return { venues, scraperIds, scrapeStatus };
 }
 
 export default function Venues() {
-  const { venues, scraperIds: scraperIdList } = useLoaderData<typeof loader>();
+  const { venues, scraperIds: scraperIdList, scrapeStatus } = useLoaderData<typeof loader>();
   const scraperIds = new Set(scraperIdList);
+  const statusMap = new Map<string, ScrapeLog>(scrapeStatus.map((s) => [s.venue_id, s]));
 
   return (
     <div className="min-h-screen bg-gray-950 text-gray-100">
@@ -26,43 +28,94 @@ export default function Venues() {
       </header>
 
       <main className="max-w-4xl mx-auto px-4 py-10">
-        <div className="mb-8">
-          <h1 className="text-2xl font-bold">会場一覧</h1>
-          <p className="mt-1 text-sm text-gray-400">
-            現在 {scraperIdList.length} 会場のスクレイパーが登録されています。
-            新しい会場を追加するには <code className="bg-gray-800 px-1 rounded">app/scrapers/</code> に
-            モジュールを追加して <code className="bg-gray-800 px-1 rounded">index.ts</code> に登録してください。
-          </p>
+        <div className="mb-8 flex items-start justify-between gap-4 flex-wrap">
+          <div>
+            <h1 className="text-2xl font-bold">会場一覧</h1>
+            <p className="mt-1 text-sm text-gray-400">
+              現在 {scraperIdList.length} 会場のスクレイパーが登録されています。
+            </p>
+          </div>
+          <Form method="post" action="/api/scrape">
+            <button
+              type="submit"
+              className="rounded-md bg-indigo-600 px-4 py-2 text-sm font-medium hover:bg-indigo-500 transition-colors"
+            >
+              全会場を更新
+            </button>
+          </Form>
         </div>
 
         {venues.length === 0 ? (
-          <p className="text-gray-500">まだ会場データがありません。「情報を更新」してください。</p>
+          <p className="text-gray-500">まだ会場データがありません。「全会場を更新」してください。</p>
         ) : (
-          <div className="grid gap-4 sm:grid-cols-2">
-            {venues.map((v) => (
-              <Link
-                key={v.id}
-                to={`/events?venue_id=${v.id}`}
-                className="flex items-center justify-between rounded-xl bg-gray-800/60 p-4 hover:bg-gray-800 transition-colors border border-gray-700/50"
-              >
-                <div>
-                  <p className="font-semibold">{v.name}</p>
-                  {v.area && <p className="text-sm text-gray-400">{v.area}</p>}
+          <div className="grid gap-3">
+            {venues.map((v) => {
+              const log = statusMap.get(v.id);
+              return (
+                <div
+                  key={v.id}
+                  className="flex items-center gap-4 rounded-xl bg-gray-800/60 border border-gray-700/40 p-4"
+                >
+                  {/* 会場名 + エリア */}
+                  <div className="flex-1 min-w-0">
+                    <Link
+                      to={`/events?venue_id=${v.id}`}
+                      className="font-semibold hover:text-indigo-300 transition-colors"
+                    >
+                      {v.name}
+                    </Link>
+                    {v.area && <p className="text-xs text-gray-400">{v.area}</p>}
+                  </div>
+
+                  {/* イベント件数 */}
+                  <span className="text-sm text-gray-400 whitespace-nowrap">
+                    <span className="text-lg font-bold text-gray-200">{v.event_count ?? 0}</span> 件
+                  </span>
+
+                  {/* 最終スクレイプ状態 */}
+                  {log ? (
+                    <ScrapeStatus log={log} />
+                  ) : (
+                    <span className="text-xs text-gray-600 whitespace-nowrap">未実行</span>
+                  )}
+
+                  {/* 個別更新ボタン */}
                   {scraperIds.has(v.id) && (
-                    <span className="mt-1 inline-block rounded-full bg-emerald-700/40 px-2 py-0.5 text-xs text-emerald-300">
-                      スクレイパー登録済
-                    </span>
+                    <Form method="post" action="/api/scrape">
+                      <input type="hidden" name="venue_id" value={v.id} />
+                      <button
+                        type="submit"
+                        className="rounded bg-gray-700 px-3 py-1 text-xs hover:bg-gray-600 transition-colors whitespace-nowrap"
+                      >
+                        更新
+                      </button>
+                    </Form>
                   )}
                 </div>
-                <span className="text-2xl font-bold text-gray-500">
-                  {v.event_count ?? 0}
-                  <span className="text-sm font-normal ml-1">件</span>
-                </span>
-              </Link>
-            ))}
+              );
+            })}
           </div>
         )}
       </main>
     </div>
   );
 }
+
+function ScrapeStatus({ log }: { log: ScrapeLog }) {
+  if (log.status === "running") {
+    return <span className="text-xs text-yellow-400 whitespace-nowrap">⟳ 実行中...</span>;
+  }
+  if (log.status === "error") {
+    return (
+      <span className="text-xs text-red-400 whitespace-nowrap" title={log.error ?? ""}>
+        ✖ エラー
+      </span>
+    );
+  }
+  const time = log.finished_at?.slice(0, 16).replace("T", " ") ?? "";
+  return (
+    <span className="text-xs text-emerald-400 whitespace-nowrap" title={time}>
+      ✔ {time}
+    </span>
+  );
+}
-- 
cgit v1.2.3