From ae6f6f7f74fd4df7704f963d2f1fdd1f3100668f Mon Sep 17 00:00:00 2001
From: yyamashita <yyamashita@mosquit.one>
Date: Fri, 8 May 2026 03:50:45 +0900
Subject: Add capacity filter for live houses (~100 / 100~300 / 300~)

- Add capacity field to VenueMeta and all 17 scrapers (researched values)
- Add capacity column to venues table with auto-migration for existing DBs
- Add capacity_range filter to queryEvents (small/medium/large)
- Add capacity selector to FilterBar UI

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
---
 app/lib/db.server.ts             | 36 ++++++++++++++++++++++++++++--------
 app/lib/scraper-runner.server.ts |  4 ++--
 2 files changed, 30 insertions(+), 10 deletions(-)

(limited to 'app/lib')

diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts
index 6da5a1c..a4671b4 100644
--- a/app/lib/db.server.ts
+++ b/app/lib/db.server.ts
@@ -22,10 +22,11 @@ function getDb(): Database.Database {
 function initSchema(db: Database.Database) {
   db.exec(`
     CREATE TABLE IF NOT EXISTS venues (
-      id    TEXT PRIMARY KEY,
-      name  TEXT NOT NULL,
-      url   TEXT NOT NULL,
-      area  TEXT
+      id       TEXT PRIMARY KEY,
+      name     TEXT NOT NULL,
+      url      TEXT NOT NULL,
+      area     TEXT,
+      capacity INTEGER
     );
 
     CREATE TABLE IF NOT EXISTS events (
@@ -63,6 +64,13 @@ function initSchema(db: Database.Database) {
     CREATE INDEX IF NOT EXISTS idx_scrape_logs_run_id   ON scrape_logs(run_id);
     CREATE INDEX IF NOT EXISTS idx_scrape_logs_venue_id ON scrape_logs(venue_id);
   `);
+
+  // Migration: add capacity column to existing venues tables
+  try {
+    db.exec("ALTER TABLE venues ADD COLUMN capacity INTEGER");
+  } catch {
+    // Column already exists — ignore
+  }
 }
 
 export interface Venue {
@@ -70,6 +78,7 @@ export interface Venue {
   name: string;
   url: string;
   area: string | null;
+  capacity: number | null;
   event_count?: number;
 }
 
@@ -110,13 +119,14 @@ export function upsertVenue(
   id: string,
   name: string,
   url: string,
-  area?: string
+  area?: string,
+  capacity?: number
 ) {
   getDb()
     .prepare(
-      "INSERT OR REPLACE INTO venues (id, name, url, area) VALUES (?, ?, ?, ?)"
+      "INSERT OR REPLACE INTO venues (id, name, url, area, capacity) VALUES (?, ?, ?, ?, ?)"
     )
-    .run(id, name, url, area ?? null);
+    .run(id, name, url, area ?? null, capacity ?? null);
 }
 
 export function upsertEvent(raw: EventInput) {
@@ -154,17 +164,20 @@ export function upsertEvent(raw: EventInput) {
     .run(event);
 }
 
+export type CapacityRange = "small" | "medium" | "large";
+
 export interface QueryEventsParams {
   date_from?: string;
   date_to?: string;
   venue_id?: string;
   keyword?: string;
+  capacity_range?: CapacityRange;
   limit?: number;
   offset?: number;
 }
 
 export function queryEvents(params: QueryEventsParams = {}): Event[] {
-  const { date_from, date_to, venue_id, keyword, limit = 60, offset = 0 } =
+  const { date_from, date_to, venue_id, keyword, capacity_range, limit = 60, offset = 0 } =
     params;
 
   const clauses: string[] = [];
@@ -186,6 +199,13 @@ export function queryEvents(params: QueryEventsParams = {}): Event[] {
     clauses.push("(e.title LIKE ? OR e.artist LIKE ?)");
     args.push(`%${keyword}%`, `%${keyword}%`);
   }
+  if (capacity_range === "small") {
+    clauses.push("v.capacity <= 100");
+  } else if (capacity_range === "medium") {
+    clauses.push("v.capacity > 100 AND v.capacity < 300");
+  } else if (capacity_range === "large") {
+    clauses.push("v.capacity >= 300");
+  }
 
   const where = clauses.length ? `WHERE ${clauses.join(" AND ")}` : "";
 
diff --git a/app/lib/scraper-runner.server.ts b/app/lib/scraper-runner.server.ts
index 012ff95..8392ead 100644
--- a/app/lib/scraper-runner.server.ts
+++ b/app/lib/scraper-runner.server.ts
@@ -58,7 +58,7 @@ export async function runAllScrapers(run_id = randomUUID()): Promise<ScrapeResul
 
   for (const scraper of ALL_SCRAPERS) {
     const { venue } = scraper;
-    upsertVenue(venue.id, venue.name, venue.url, venue.area);
+    upsertVenue(venue.id, venue.name, venue.url, venue.area, venue.capacity);
     const logId = insertScrapeLog(run_id, venue.id, venue.name);
 
     try {
@@ -91,7 +91,7 @@ export async function runScraper(venueId: string, run_id = randomUUID()): Promis
   }
 
   const { venue } = scraper;
-  upsertVenue(venue.id, venue.name, venue.url, venue.area);
+  upsertVenue(venue.id, venue.name, venue.url, venue.area, venue.capacity);
   const logId = insertScrapeLog(run_id, venue.id, venue.name);
 
   try {
-- 
cgit v1.2.3