summaryrefslogtreecommitdiff
path: root/app/lib
diff options
context:
space:
mode:
Diffstat (limited to 'app/lib')
-rw-r--r--app/lib/db.server.ts192
-rw-r--r--app/lib/markdown-writer.server.ts80
-rw-r--r--app/lib/scraper-runner.server.ts77
-rw-r--r--app/lib/venue-meta.server.ts14
4 files changed, 363 insertions, 0 deletions
diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts
new file mode 100644
index 0000000..0c55991
--- /dev/null
+++ b/app/lib/db.server.ts
@@ -0,0 +1,192 @@
+import Database from "better-sqlite3";
+import path from "path";
+import { fileURLToPath } from "url";
+
+const __dirname = path.dirname(fileURLToPath(import.meta.url));
+const DB_PATH = path.join(__dirname, "../../events.db");
+
+let _db: Database.Database | null = null;
+
+function getDb(): Database.Database {
+ if (!_db) {
+ _db = new Database(DB_PATH);
+ _db.pragma("journal_mode = WAL");
+ _db.pragma("foreign_keys = ON");
+ initSchema(_db);
+ }
+ return _db;
+}
+
+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
+ );
+
+ CREATE TABLE IF NOT EXISTS events (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ venue_id TEXT NOT NULL REFERENCES venues(id),
+ title TEXT NOT NULL,
+ artist TEXT,
+ date TEXT NOT NULL,
+ start_time TEXT,
+ open_time TEXT,
+ ticket_url TEXT,
+ price TEXT,
+ image_url TEXT,
+ description TEXT,
+ source_url TEXT,
+ fetched_at TEXT NOT NULL DEFAULT (datetime('now')),
+ UNIQUE(venue_id, title, date)
+ );
+
+ CREATE INDEX IF NOT EXISTS idx_events_date ON events(date);
+ CREATE INDEX IF NOT EXISTS idx_events_venue_id ON events(venue_id);
+ `);
+}
+
+export interface Venue {
+ id: string;
+ name: string;
+ url: string;
+ area: string | null;
+ event_count?: number;
+}
+
+export interface Event {
+ id: number;
+ venue_id: string;
+ venue_name: string;
+ venue_area: string | null;
+ venue_url?: string;
+ title: string;
+ artist: string | null;
+ date: string;
+ start_time: string | null;
+ open_time: string | null;
+ ticket_url: string | null;
+ price: string | null;
+ image_url: string | null;
+ description: string | null;
+ source_url: string | null;
+ fetched_at: string;
+}
+
+export interface EventInput {
+ venue_id: string;
+ title: string;
+ artist?: string | null;
+ date: string;
+ start_time?: string | null;
+ open_time?: string | null;
+ ticket_url?: string | null;
+ price?: string | null;
+ image_url?: string | null;
+ description?: string | null;
+ source_url?: string | null;
+}
+
+export function upsertVenue(
+ id: string,
+ name: string,
+ url: string,
+ area?: string
+) {
+ getDb()
+ .prepare(
+ "INSERT OR REPLACE INTO venues (id, name, url, area) VALUES (?, ?, ?, ?)"
+ )
+ .run(id, name, url, area ?? null);
+}
+
+export function upsertEvent(event: EventInput) {
+ getDb()
+ .prepare(
+ `INSERT INTO events
+ (venue_id, title, artist, date, start_time, open_time,
+ ticket_url, price, image_url, description, source_url, fetched_at)
+ VALUES
+ (@venue_id, @title, @artist, @date, @start_time, @open_time,
+ @ticket_url, @price, @image_url, @description, @source_url, datetime('now'))
+ ON CONFLICT(venue_id, title, date) DO UPDATE SET
+ artist = excluded.artist,
+ start_time = excluded.start_time,
+ open_time = excluded.open_time,
+ ticket_url = excluded.ticket_url,
+ price = excluded.price,
+ image_url = excluded.image_url,
+ description = excluded.description,
+ source_url = excluded.source_url,
+ fetched_at = excluded.fetched_at`
+ )
+ .run(event);
+}
+
+export interface QueryEventsParams {
+ date_from?: string;
+ date_to?: string;
+ venue_id?: string;
+ keyword?: string;
+ limit?: number;
+ offset?: number;
+}
+
+export function queryEvents(params: QueryEventsParams = {}): Event[] {
+ const { date_from, date_to, venue_id, keyword, limit = 60, offset = 0 } =
+ params;
+
+ const clauses: string[] = [];
+ const args: unknown[] = [];
+
+ if (date_from) {
+ clauses.push("e.date >= ?");
+ args.push(date_from);
+ }
+ if (date_to) {
+ clauses.push("e.date <= ?");
+ args.push(date_to);
+ }
+ if (venue_id) {
+ clauses.push("e.venue_id = ?");
+ args.push(venue_id);
+ }
+ if (keyword) {
+ clauses.push("(e.title LIKE ? OR e.artist LIKE ?)");
+ args.push(`%${keyword}%`, `%${keyword}%`);
+ }
+
+ const where = clauses.length ? `WHERE ${clauses.join(" AND ")}` : "";
+
+ return getDb()
+ .prepare(
+ `SELECT e.*, v.name AS venue_name, v.area AS venue_area
+ FROM events e JOIN venues v ON e.venue_id = v.id
+ ${where}
+ ORDER BY e.date ASC, e.start_time ASC
+ LIMIT ? OFFSET ?`
+ )
+ .all(...args, limit, offset) as Event[];
+}
+
+export function getEvent(id: number): Event | undefined {
+ return getDb()
+ .prepare(
+ `SELECT e.*, v.name AS venue_name, v.url AS venue_url, v.area AS venue_area
+ FROM events e JOIN venues v ON e.venue_id = v.id
+ WHERE e.id = ?`
+ )
+ .get(id) as Event | undefined;
+}
+
+export function getVenues(): Venue[] {
+ return getDb()
+ .prepare(
+ `SELECT v.*, COUNT(e.id) AS event_count
+ FROM venues v LEFT JOIN events e ON v.id = e.venue_id
+ GROUP BY v.id ORDER BY v.name`
+ )
+ .all() as Venue[];
+}
diff --git a/app/lib/markdown-writer.server.ts b/app/lib/markdown-writer.server.ts
new file mode 100644
index 0000000..cfef315
--- /dev/null
+++ b/app/lib/markdown-writer.server.ts
@@ -0,0 +1,80 @@
+/**
+ * Generates a Markdown summary file per venue after scraping.
+ * Files are written to events/<venue-id>.md in the project root.
+ */
+import fs from "fs";
+import path from "path";
+import { fileURLToPath } from "url";
+import { queryEvents } from "./db.server";
+import type { Event } from "./db.server";
+
+const ROOT = path.join(path.dirname(fileURLToPath(import.meta.url)), "../../");
+const EVENTS_DIR = path.join(ROOT, "events");
+
+export function generateVenueMarkdown(venueId: string): void {
+ const events = queryEvents({ venue_id: venueId, limit: 200 });
+ if (events.length === 0) return;
+
+ fs.mkdirSync(EVENTS_DIR, { recursive: true });
+
+ const venueName = events[0].venue_name;
+ const venueArea = events[0].venue_area ?? "";
+ const now = new Date().toISOString().slice(0, 10);
+
+ const lines: string[] = [
+ `# ${venueName}（${venueArea}）イベント情報`,
+ ``,
+ `> 最終更新: ${now} `,
+ `> データソース: スクレイパー自動取得`,
+ ``,
+ `| 日付 | 出演者 | タイトル | 時間 | 料金 | URL |`,
+ `| ---- | ------ | -------- | ---- | ---- | --- |`,
+ ];
+
+ for (const ev of events) {
+ const date = formatDate(ev.date);
+ const artist = escape(ev.artist ?? "未定");
+ const title = escape(ev.title);
+ const time = buildTime(ev.open_time, ev.start_time);
+ const fee = escape(ev.price ?? "");
+ const url = ev.ticket_url
+ ? `[チケット](${ev.ticket_url})`
+ : ev.source_url
+ ? `[詳細](${ev.source_url})`
+ : "";
+
+ lines.push(`| ${date} | ${artist} | ${title} | ${time} | ${fee} | ${url} |`);
+ }
+
+ lines.push(``);
+ lines.push(`---`);
+ lines.push(`*このファイルは自動生成されます。手動編集は次回更新時に上書きされます。*`);
+ lines.push(``);
+
+ const filePath = path.join(EVENTS_DIR, `${venueId}.md`);
+ fs.writeFileSync(filePath, lines.join("\n"), "utf-8");
+}
+
+export function generateAllVenueMarkdown(venueIds: string[]): void {
+ for (const id of venueIds) {
+ generateVenueMarkdown(id);
+ }
+}
+
+function formatDate(iso: string): string {
+ const [y, m, d] = iso.split("-");
+ const days = ["日", "月", "火", "水", "木", "金", "土"];
+ const dayIdx = new Date(`${iso}T00:00:00`).getDay();
+ return `${y}/${m}/${d}（${days[dayIdx]}）`;
+}
+
+function buildTime(open: string | null, start: string | null): string {
+ const parts: string[] = [];
+ if (open) parts.push(`OPEN ${open}`);
+ if (start) parts.push(`START ${start}`);
+ return parts.join(" / ") || "";
+}
+
+function escape(s: string): string {
+ return s.replace(/\|/g, "\\|").replace(/\n/g, " ");
+}
diff --git a/app/lib/scraper-runner.server.ts b/app/lib/scraper-runner.server.ts
new file mode 100644
index 0000000..070a568
--- /dev/null
+++ b/app/lib/scraper-runner.server.ts
@@ -0,0 +1,77 @@
+import { upsertVenue, upsertEvent } from "./db.server";
+import { generateVenueMarkdown, generateAllVenueMarkdown } from "./markdown-writer.server";
+import { ALL_SCRAPERS } from "~/scrapers/index";
+
+export interface ScrapeResult {
+ venue_id: string;
+ venue_name: string;
+ events_saved: number;
+ markdown_path?: string;
+ error?: string;
+}
+
+export async function runAllScrapers(): Promise<ScrapeResult[]> {
+ const results: ScrapeResult[] = [];
+ const successIds: string[] = [];
+
+ for (const scraper of ALL_SCRAPERS) {
+ const { venue } = scraper;
+ upsertVenue(venue.id, venue.name, venue.url, venue.area);
+
+ try {
+ const events = await scraper.scrape();
+ for (const event of events) {
+ upsertEvent(event);
+ }
+ successIds.push(venue.id);
+ results.push({
+ venue_id: venue.id,
+ venue_name: venue.name,
+ events_saved: events.length,
+ });
+ } catch (err) {
+ results.push({
+ venue_id: venue.id,
+ venue_name: venue.name,
+ events_saved: 0,
+ error: err instanceof Error ? err.message : String(err),
+ });
+ }
+ }
+
+ // Generate Markdown files for all venues that scraped successfully
+ generateAllVenueMarkdown(successIds);
+
+ return results;
+}
+
+export async function runScraper(venueId: string): Promise<ScrapeResult> {
+ const scraper = ALL_SCRAPERS.find((s) => s.venue.id === venueId);
+ if (!scraper) {
+ return { venue_id: venueId, venue_name: venueId, events_saved: 0, error: "Scraper not found" };
+ }
+
+ const { venue } = scraper;
+ upsertVenue(venue.id, venue.name, venue.url, venue.area);
+
+ try {
+ const events = await scraper.scrape();
+ for (const event of events) {
+ upsertEvent(event);
+ }
+ generateVenueMarkdown(venue.id);
+ return {
+ venue_id: venue.id,
+ venue_name: venue.name,
+ events_saved: events.length,
+ markdown_path: `events/${venue.id}.md`,
+ };
+ } catch (err) {
+ return {
+ venue_id: venue.id,
+ venue_name: venue.name,
+ events_saved: 0,
+ error: err instanceof Error ? err.message : String(err),
+ };
+ }
+}
diff --git a/app/lib/venue-meta.server.ts b/app/lib/venue-meta.server.ts
new file mode 100644
index 0000000..58743c8
--- /dev/null
+++ b/app/lib/venue-meta.server.ts
@@ -0,0 +1,14 @@
+/**
+ * Server-only module: exposes venue metadata from all registered scrapers.
+ * Importing this in a route loader ensures scraper code never reaches the client bundle.
+ */
+import { ALL_SCRAPERS } from "~/scrapers/index";
+import type { VenueMeta } from "~/scrapers/base";
+
+export function getScraperVenues(): VenueMeta[] {
+ return ALL_SCRAPERS.map((s) => s.venue);
+}
+
+export function getScraperIds(): string[] {
+ return ALL_SCRAPERS.map((s) => s.venue.id);
+}
generated by cgit v1.2.3 (git 2.25.1) at 2026-05-17 14:30:29 +0000