From 184e6947707ecdf07dfa3a5cbc6e51cf9440e93a Mon Sep 17 00:00:00 2001 From: yyamashita Date: Sun, 10 May 2026 00:21:04 +0900 Subject: Add members table with membership period and note support Replace band_artists + member_periods with a single members table (id, band_id, artist_id, role, since, until, note, order_index). Each row represents one membership period, so rejoining artists get multiple rows. Existing band_artists data is auto-migrated on startup. Export format bumped to version 3. Co-Authored-By: Claude Sonnet 4.6 --- app/app.css | 305 +++++++++++++++--------------------------- app/lib/db.server.ts | 175 ++++++++++++++---------- app/routes/api-bands.tsx | 4 +- app/routes/api-import.tsx | 4 +- app/routes/artist-by-uuid.tsx | 53 ++++++-- app/routes/band-by-uuid.tsx | 63 +++++++-- app/routes/band-edit.tsx | 229 +++++++++++++++++++------------ app/routes/band-history.tsx | 5 +- app/routes/band-new.tsx | 204 ++++++++++++++++++---------- scripts/add.ts | 26 ++-- 10 files changed, 597 insertions(+), 471 deletions(-) diff --git a/app/app.css b/app/app.css index f7c2b1c..1a24300 100644 --- a/app/app.css +++ b/app/app.css @@ -1,243 +1,148 @@ -*, *::before, *::after { box-sizing: border-box; } - -body { - margin: 0; - background: #030712; - color: #e5e7eb; - font-family: ui-sans-serif, system-ui, sans-serif; - line-height: 1.5; - -webkit-font-smoothing: antialiased; -} +/* ── Reset / base ── */ + +*, *::before, *::after { box-sizing: border-box; margin: 0; padding: 0; } +html { font-family: system-ui, sans-serif; background: #0f1117; color: #d1d5db; } a { color: inherit; text-decoration: none; } -p { margin: 0; } -h1, h2 { margin: 0; } -ol, ul { list-style: none; padding: 0; margin: 0; } -hr { border: none; border-top: 1px solid #1f2937; margin: 1.5rem 0; } -section { margin-bottom: 1.5rem; } -pre { background: #111827; border-radius: 6px; padding: 1rem; overflow-x: auto; font-size: .875rem; margin-top: 1rem; } - -/* ── Nav ── */ - -nav { border-bottom: 1px solid #1f2937; } -nav > div { - max-width: 48rem; - margin: 0 auto; - padding: .75rem 1rem; - display: flex; - align-items: center; - gap: 1.5rem; +a:hover { text-decoration: underline; } + +input, select, textarea, button { + font: inherit; + background: #1f2937; + color: #f3f4f6; + border: 1px solid #374151; + border-radius: 4px; + padding: .375rem .625rem; } -nav a { font-size: .875rem; color: #9ca3af; } -nav a:hover { color: #f9fafb; } -nav .logo { font-size: 1rem; font-weight: 700; color: #fff; letter-spacing: -.025em; } +input:focus, select:focus, textarea:focus { outline: 1px solid #6366f1; } +button { cursor: pointer; } -/* ── Main ── */ +/* ── Layout ── */ -main { - max-width: 48rem; - margin: 0 auto; - padding: 2rem 1rem; -} +.nav { background: #111827; border-bottom: 1px solid #1f2937; padding: .75rem 1.5rem; display: flex; gap: 1.5rem; align-items: center; } +.nav-brand { font-weight: 700; color: #f9fafb; font-size: 1.1rem; } +.nav a { font-size: .875rem; color: #9ca3af; } +.nav a:hover { color: #f3f4f6; text-decoration: none; } -h1 { font-size: 1.5rem; font-weight: 700; } -h2 { - font-size: .6875rem; - font-weight: 600; - color: #6b7280; - text-transform: uppercase; - letter-spacing: .06em; - margin-bottom: .75rem; -} +main { max-width: 760px; margin: 2rem auto; padding: 0 1.5rem; } -/* ── Page header (← Title) ── */ +/* ── Page header ── */ -.page-header { - display: flex; - align-items: center; - gap: .75rem; - margin-bottom: 1.5rem; -} -.page-header h1 { font-size: 1.25rem; font-weight: 600; } -.back { color: #6b7280; } -.back:hover { color: #e5e7eb; } +.page-header { display: flex; align-items: center; gap: 1rem; margin-bottom: 1.5rem; } +.page-header h1 { font-size: 1.5rem; font-weight: 700; color: #f9fafb; } +.back { color: #6b7280; font-size: 1.25rem; text-decoration: none; } +.back:hover { color: #9ca3af; } /* ── Detail header ── */ -.detail-header { - display: flex; - justify-content: space-between; - align-items: flex-start; - gap: 1rem; - margin-bottom: 1.5rem; -} -.detail-info { flex: 1; min-width: 0; } -.detail-subtitle { font-size: .875rem; color: #9ca3af; display: flex; gap: .75rem; margin-top: .25rem; } -.detail-desc { font-size: .875rem; color: #d1d5db; line-height: 1.6; margin-top: .75rem; } -.detail-actions { display: flex; align-items: center; gap: .75rem; flex-shrink: 0; } -.detail-actions .history { font-size: .875rem; color: #9ca3af; } -.detail-actions .history:hover { color: #e5e7eb; } -.detail-actions .edit { - font-size: .875rem; - background: #1f2937; - color: #e5e7eb; - padding: .375rem .75rem; - border-radius: 4px; -} -.detail-actions .edit:hover { background: #374151; } +.detail-header { display: flex; justify-content: space-between; align-items: flex-start; gap: 1rem; margin-bottom: 2rem; } +.detail-info h1 { font-size: 1.75rem; font-weight: 700; color: #f9fafb; } +.detail-subtitle { display: flex; gap: .75rem; color: #6b7280; font-size: .875rem; margin-top: .25rem; } +.detail-desc { color: #9ca3af; font-size: .875rem; margin-top: .5rem; white-space: pre-wrap; } +.detail-actions { display: flex; gap: .5rem; flex-shrink: 0; } -/* ── Home band list ── */ +/* ── Sections ── */ -.band-list li { - display: flex; - justify-content: space-between; - align-items: flex-start; - gap: 1.5rem; - padding: 1rem 0; - border-bottom: 1px solid #0f172a; -} -.band-list li:last-child { border-bottom: none; } -.band-list .name-col { min-width: 0; } -.band-list .name-col a { font-weight: 500; } -.band-list .name-col a:hover { color: #fff; } -.band-list .desc { - font-size: .875rem; - color: #6b7280; - margin-top: .125rem; - overflow: hidden; - display: -webkit-box; - -webkit-box-orient: vertical; - -webkit-line-clamp: 1; -} -.band-list .info { font-size: .875rem; color: #6b7280; flex-shrink: 0; display: flex; gap: 1rem; padding-top: .125rem; } +section { margin-bottom: 2rem; } +section h2 { font-size: 1rem; font-weight: 600; color: #9ca3af; text-transform: uppercase; letter-spacing: .05em; margin-bottom: .75rem; } -/* ── Detail lists ── */ +/* ── Member list (detail view) ── */ .member-list { display: flex; flex-direction: column; gap: .5rem; } -.member-list li { display: flex; align-items: center; gap: .75rem; flex-wrap: wrap; } +.member-list li { display: flex; flex-direction: column; gap: .25rem; } +.member-main { display: flex; align-items: center; gap: .75rem; flex-wrap: wrap; } .member-list a { color: #60a5fa; font-weight: 500; } .member-list a:hover { color: #93c5fd; } -.link-list { display: flex; flex-direction: column; gap: .375rem; } -.link-list a { font-size: .875rem; color: #60a5fa; } -.link-list a:hover { color: #93c5fd; } +/* ── Period display (detail view) ── */ -/* ── Badge ── */ - -.badge { - display: inline-flex; - align-items: center; - gap: .25rem; - background: #111827; - color: #9ca3af; - font-size: .75rem; - padding: .125rem .5rem; - border-radius: 3px; -} -.badge button { all: unset; cursor: pointer; color: #6b7280; line-height: 1; } -.badge button:hover { color: #f87171; } +.period-list { list-style: none; display: flex; flex-direction: column; gap: .125rem; padding-left: .75rem; border-left: 2px solid #374151; margin-top: .125rem; } +.period-item { display: flex; gap: .5rem; align-items: baseline; font-size: .8rem; } +.period-range { color: #9ca3af; } +.period-note { color: #6b7280; } -/* ── Meta footer ── */ +/* ── Link list ── */ -.meta { - font-family: ui-monospace, monospace; - font-size: .75rem; - color: #374151; - display: flex; - flex-direction: column; - gap: .25rem; -} -.meta a:hover { color: #9ca3af; } -.meta .updated { font-family: ui-sans-serif, system-ui, sans-serif; color: #6b7280; margin-top: .5rem; } +.link-list { display: flex; flex-direction: column; gap: .375rem; } +.link-list a { color: #60a5fa; font-size: .875rem; } +.link-list a:hover { color: #93c5fd; } -/* ── Forms ── */ +/* ── Band list / home ── */ -form { display: flex; flex-direction: column; gap: 1.25rem; } -label { display: block; font-size: .875rem; color: #d1d5db; margin-bottom: .25rem; } -.req { color: #f87171; } -.muted { font-size: .875rem; color: #6b7280; } -.muted a { color: #60a5fa; } -.muted a:hover { color: #93c5fd; } +.band-list { display: flex; flex-direction: column; gap: .5rem; } +.band-item { display: flex; align-items: center; gap: .75rem; padding: .75rem; background: #111827; border-radius: 6px; } +.band-item a { font-weight: 500; color: #f3f4f6; flex: 1; } +.band-item a:hover { color: #60a5fa; text-decoration: none; } -input, textarea { - display: block; - width: 100%; - background: #1f2937; - border: 1px solid #374151; - border-radius: 4px; - padding: .5rem .75rem; - color: #f3f4f6; - font: inherit; - font-size: .875rem; -} -input[type="hidden"] { display: none; } -select { - background: #1f2937; - border: 1px solid #374151; - border-radius: 4px; - padding: .5rem .75rem; - color: #f3f4f6; - font: inherit; - font-size: .875rem; - width: auto; -} -input:focus, textarea:focus, select:focus { outline: none; border-color: #3b82f6; } -textarea { resize: vertical; } -input.mono { font-family: ui-monospace, monospace; } +/* ── Meta ── */ + +.meta { font-size: .75rem; color: #6b7280; display: flex; flex-direction: column; gap: .25rem; } +.meta a { color: #6b7280; } +.meta a:hover { color: #9ca3af; } +.updated { color: #6b7280; } +.muted { color: #6b7280; font-size: .875rem; } /* ── Buttons ── */ -button { - display: inline-flex; - align-items: center; - padding: .5rem 1rem; - border: none; - border-radius: 4px; - font: inherit; - font-size: .875rem; - cursor: pointer; - background: #1f2937; - color: #d1d5db; -} -button:hover { background: #374151; } -button[type="submit"] { background: #2563eb; color: #fff; font-weight: 500; } -button[type="submit"]:hover { background: #3b82f6; } - -.btn { - display: inline-flex; - align-items: center; - padding: .5rem 1rem; - border-radius: 4px; - font-size: .875rem; - background: #1f2937; - color: #d1d5db; -} -.btn:hover { background: #374151; } +.btn { display: inline-flex; align-items: center; padding: .375rem .875rem; border-radius: 4px; font-size: .875rem; background: #1f2937; border: 1px solid #374151; color: #d1d5db; text-decoration: none; } +.btn:hover { background: #374151; text-decoration: none; } +button[type="submit"], .btn-primary { background: #4f46e5; border-color: #4f46e5; color: #fff; padding: .375rem .875rem; border-radius: 4px; } +button[type="submit"]:hover, .btn-primary:hover { background: #4338ca; } +.btn-text { background: none; border: none; color: #60a5fa; font-size: .8rem; padding: .25rem .125rem; } +.btn-text:hover { color: #93c5fd; } +.btn-icon { background: none; border: none; color: #6b7280; font-size: .875rem; padding: .25rem .375rem; } +.btn-icon:hover { color: #f3f4f6; } +.edit, .history { font-size: .875rem; color: #6b7280; padding: .375rem .75rem; border: 1px solid #374151; border-radius: 4px; } +.edit:hover, .history:hover { color: #d1d5db; border-color: #6b7280; text-decoration: none; } + +/* ── Badges ── */ + +.badge { display: inline-flex; align-items: center; gap: .25rem; background: #1e3a5f; color: #93c5fd; font-size: .75rem; padding: .125rem .5rem; border-radius: 9999px; } +.badge button { background: none; border: none; color: #60a5fa; font-size: .7rem; padding: 0; line-height: 1; } +.badge button:hover { color: #f87171; } -.btn-text { background: none; padding: 0; color: #60a5fa; font-size: .875rem; } -.btn-text:hover { background: none; color: #93c5fd; } +/* ── Form ── */ -.btn-icon { background: none; padding: .125rem .5rem; color: #6b7280; } -.btn-icon:hover { background: none; color: #f87171; } +form { display: flex; flex-direction: column; gap: 1rem; } +form > div { display: flex; flex-direction: column; gap: .375rem; } +form label { font-size: .875rem; color: #9ca3af; } +form input, form select, form textarea { width: 100%; } +.req { color: #f87171; } +.error { font-size: .75rem; color: #f87171; } +.mono { font-family: monospace; } -/* ── Form components ── */ +/* ── Actions row ── */ -.actions { display: flex; gap: .75rem; padding-top: .5rem; } -.error { color: #f87171; font-size: .875rem; margin-top: .25rem; } +.actions { display: flex; gap: .75rem; align-items: center; padding-top: .5rem; } -.links-form { display: flex; flex-direction: column; gap: .5rem; } +/* ── Links form ── */ + +.links-form { display: flex; flex-direction: column; gap: .5rem; margin-bottom: .75rem; } .link-row { display: flex; gap: .5rem; align-items: center; } .link-row input { width: auto; flex: 1; min-width: 0; } .link-row .label-input { flex: 0 0 7rem; } .link-row select { width: 9rem; flex-shrink: 0; } -.members-form { display: flex; flex-direction: column; gap: .5rem; margin-bottom: .75rem; } -.member-card { background: #111827; border-radius: 6px; padding: .75rem; } -.member-card .card-header { display: flex; justify-content: space-between; align-items: center; margin-bottom: .5rem; } -.member-card .card-name { font-size: .875rem; font-weight: 500; color: #e5e7eb; } +/* ── Members form (edit/new) ── */ + +.members-form { display: flex; flex-direction: column; gap: .75rem; margin-bottom: .75rem; } + +.member-group { background: #111827; border-radius: 6px; overflow: hidden; } +.member-group > .card-header { display: flex; justify-content: space-between; align-items: center; padding: .625rem .75rem; border-bottom: 1px solid #1f2937; } + +.member-card { padding: .75rem; border-top: 1px solid #1f2937; } +.member-card:first-of-type { border-top: none; } .member-card .badges { display: flex; flex-wrap: wrap; gap: .375rem; margin-bottom: .5rem; } -.member-card .role-row { display: flex; gap: .5rem; align-items: center; } +.member-card .role-row { display: flex; gap: .5rem; align-items: center; margin-bottom: .5rem; } .member-card .role-row .custom-input { width: 8rem; flex: none; } +.card-name { font-size: .875rem; font-weight: 500; color: #e5e7eb; } + +/* ── Period row (edit/new form) ── */ + +.period-row { display: flex; gap: .5rem; align-items: center; margin-top: .5rem; } +.period-row input { flex: 1; min-width: 0; font-size: .8rem; } +.period-row .period-note { flex: 1.5; } +.period-sep { color: #6b7280; flex-shrink: 0; font-size: .875rem; } /* ── Revision list ── */ diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts index e35bba6..cb51028 100644 --- a/app/lib/db.server.ts +++ b/app/lib/db.server.ts @@ -47,6 +47,7 @@ function initSchema(db: Database.Database) { order_index INTEGER NOT NULL DEFAULT 0 ); + -- legacy table kept for migration only CREATE TABLE IF NOT EXISTS band_artists ( band_id TEXT NOT NULL REFERENCES bands(id) ON DELETE CASCADE, artist_id TEXT NOT NULL REFERENCES artists(id) ON DELETE CASCADE, @@ -55,6 +56,17 @@ function initSchema(db: Database.Database) { PRIMARY KEY (band_id, artist_id) ); + CREATE TABLE IF NOT EXISTS members ( + id TEXT PRIMARY KEY, + band_id TEXT NOT NULL REFERENCES bands(id) ON DELETE CASCADE, + artist_id TEXT NOT NULL REFERENCES artists(id) ON DELETE CASCADE, + role TEXT, + since TEXT NOT NULL DEFAULT '', + until TEXT NOT NULL DEFAULT '', + note TEXT NOT NULL DEFAULT '', + order_index INTEGER NOT NULL DEFAULT 0 + ); + CREATE TABLE IF NOT EXISTS band_revisions ( id TEXT PRIMARY KEY, band_id TEXT NOT NULL REFERENCES bands(id) ON DELETE CASCADE, @@ -79,16 +91,35 @@ function initSchema(db: Database.Database) { // migrations try { db.exec("ALTER TABLE bands ADD COLUMN description TEXT"); } catch { /* already exists */ } try { db.exec("ALTER TABLE bands ADD COLUMN status TEXT NOT NULL DEFAULT 'active'"); } catch { /* already exists */ } + try { db.exec("ALTER TABLE band_artists ADD COLUMN periods TEXT"); } catch { /* already exists */ } + + // one-time data migration: band_artists → members + try { + const mc = (db.prepare("SELECT COUNT(*) as c FROM members").get() as { c: number }).c; + if (mc === 0) { + const rows = db.prepare("SELECT * FROM band_artists").all() as { + band_id: string; artist_id: string; role: string | null; order_index: number; + }[]; + const insert = db.prepare( + "INSERT INTO members (id, band_id, artist_id, role, since, until, note, order_index) VALUES (?, ?, ?, ?, '', '', '', ?)" + ); + for (const r of rows) { + insert.run(crypto.randomUUID(), r.band_id, r.artist_id, r.role, r.order_index); + } + } + } catch { /* band_artists might not have data */ } db.exec(` CREATE INDEX IF NOT EXISTS idx_artist_links_artist_id ON artist_links(artist_id); - CREATE INDEX IF NOT EXISTS idx_band_artists_band_id ON band_artists(band_id); - CREATE INDEX IF NOT EXISTS idx_band_artists_artist_id ON band_artists(artist_id); + CREATE INDEX IF NOT EXISTS idx_members_band_id ON members(band_id); + CREATE INDEX IF NOT EXISTS idx_members_artist_id ON members(artist_id); CREATE INDEX IF NOT EXISTS idx_band_revisions_band_id ON band_revisions(band_id); CREATE INDEX IF NOT EXISTS idx_artist_revisions_artist_id ON artist_revisions(artist_id); `); } +// ── Interfaces ──────────────────────────────────────────────────────────────── + export interface Band { id: string; slug: string; @@ -122,23 +153,35 @@ export interface ArtistLink { order_index: number; } -export interface BandArtistRow { +export interface MemberRaw { + id: string; band_id: string; artist_id: string; role: string | null; + since: string; + until: string; + note: string; order_index: number; +} + +export interface BandMemberRow extends MemberRaw { artist_name: string; artist_slug: string; } -export interface ArtistBandRow { - band_id: string; - artist_id: string; - role: string | null; +export interface ArtistMemberRow extends MemberRaw { band_name: string; band_slug: string; } +export interface MemberInput { + artist_id: string; + role: string | null; + since: string; + until: string; + note: string; +} + export interface BandRevision { id: string; band_id: string; @@ -194,16 +237,16 @@ export function getBandLinks(bandId: string): BandLink[] { .all(bandId) as BandLink[]; } -export function getBandArtists(bandId: string): BandArtistRow[] { +export function getBandMembers(bandId: string): BandMemberRow[] { return getDb() .prepare( - `SELECT ba.*, a.name AS artist_name, a.slug AS artist_slug - FROM band_artists ba - JOIN artists a ON a.id = ba.artist_id - WHERE ba.band_id = ? - ORDER BY ba.order_index` + `SELECT m.*, a.name AS artist_name, a.slug AS artist_slug + FROM members m + JOIN artists a ON a.id = m.artist_id + WHERE m.band_id = ? + ORDER BY m.order_index` ) - .all(bandId) as BandArtistRow[]; + .all(bandId) as BandMemberRow[]; } export function getBandRevisions(bandId: string): BandRevision[] { @@ -220,7 +263,7 @@ export interface CreateBandInput { description: string | null; status: string; links: { label: string; url: string }[]; - artists: { id: string; role: string | null }[]; + members: MemberInput[]; message: string; ip_address: string; } @@ -236,25 +279,17 @@ export function createBand(input: CreateBandInput): Band { "INSERT INTO band_links (id, band_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)" ).run(crypto.randomUUID(), input.id, link.label, link.url, i); }); - input.artists.forEach((artist, i) => { + input.members.forEach((m, i) => { db.prepare( - "INSERT INTO band_artists (band_id, artist_id, role, order_index) VALUES (?, ?, ?, ?)" - ).run(input.id, artist.id, artist.role, i); + "INSERT INTO members (id, band_id, artist_id, role, since, until, note, order_index) VALUES (?, ?, ?, ?, ?, ?, ?, ?)" + ).run(crypto.randomUUID(), input.id, m.artist_id, m.role, m.since, m.until, m.note, i); }); const band = getBandById(input.id)!; const links = getBandLinks(input.id); - const artists = getBandArtists(input.id); - const snapshot = JSON.stringify({ - name: band.name, - area: band.area, - description: band.description, - status: band.status, - links: links.map((l) => ({ label: l.label, url: l.url })), - artists: artists.map((a) => ({ id: a.artist_id, name: a.artist_name, role: a.role })), - }); + const members = getBandMembers(input.id); db.prepare( "INSERT INTO band_revisions (id, band_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)" - ).run(crypto.randomUUID(), input.id, snapshot, input.message, input.ip_address); + ).run(crypto.randomUUID(), input.id, buildSnapshot(band, links, members), input.message, input.ip_address); return band; })() as Band; } @@ -266,7 +301,7 @@ export interface UpdateBandInput { description: string | null; status: string; links: { label: string; url: string }[]; - artists: { id: string; role: string | null }[]; + members: MemberInput[]; message: string; ip_address: string; } @@ -283,29 +318,39 @@ export function updateBand(id: string, input: UpdateBandInput): void { "INSERT INTO band_links (id, band_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)" ).run(crypto.randomUUID(), id, link.label, link.url, i); }); - db.prepare("DELETE FROM band_artists WHERE band_id = ?").run(id); - input.artists.forEach((artist, i) => { + db.prepare("DELETE FROM members WHERE band_id = ?").run(id); + input.members.forEach((m, i) => { db.prepare( - "INSERT INTO band_artists (band_id, artist_id, role, order_index) VALUES (?, ?, ?, ?)" - ).run(id, artist.id, artist.role, i); + "INSERT INTO members (id, band_id, artist_id, role, since, until, note, order_index) VALUES (?, ?, ?, ?, ?, ?, ?, ?)" + ).run(crypto.randomUUID(), id, m.artist_id, m.role, m.since, m.until, m.note, i); }); const band = getBandById(id)!; const links = getBandLinks(id); - const artists = getBandArtists(id); - const snapshot = JSON.stringify({ - name: band.name, - area: band.area, - description: band.description, - status: band.status, - links: links.map((l) => ({ label: l.label, url: l.url })), - artists: artists.map((a) => ({ id: a.artist_id, name: a.artist_name, role: a.role })), - }); + const members = getBandMembers(id); db.prepare( "INSERT INTO band_revisions (id, band_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)" - ).run(crypto.randomUUID(), id, snapshot, input.message, input.ip_address); + ).run(crypto.randomUUID(), id, buildSnapshot(band, links, members), input.message, input.ip_address); })(); } +function buildSnapshot(band: Band, links: BandLink[], members: BandMemberRow[]): string { + return JSON.stringify({ + name: band.name, + area: band.area, + description: band.description, + status: band.status, + links: links.map((l) => ({ label: l.label, url: l.url })), + members: members.map((m) => ({ + artist_id: m.artist_id, + artist_name: m.artist_name, + role: m.role, + since: m.since, + until: m.until, + note: m.note, + })), + }); +} + // ── Artist queries ──────────────────────────────────────────────────────────── export function listArtists(): Artist[] { @@ -326,16 +371,16 @@ export function getArtistLinks(artistId: string): ArtistLink[] { .all(artistId) as ArtistLink[]; } -export function getArtistBands(artistId: string): ArtistBandRow[] { +export function getArtistMembers(artistId: string): ArtistMemberRow[] { return getDb() .prepare( - `SELECT ba.*, b.name AS band_name, b.slug AS band_slug - FROM band_artists ba - JOIN bands b ON b.id = ba.band_id - WHERE ba.artist_id = ? - ORDER BY b.name` + `SELECT m.*, b.name AS band_name, b.slug AS band_slug + FROM members m + JOIN bands b ON b.id = m.band_id + WHERE m.artist_id = ? + ORDER BY b.name, m.order_index` ) - .all(artistId) as ArtistBandRow[]; + .all(artistId) as ArtistMemberRow[]; } export function getArtistRevisions(artistId: string): ArtistRevision[] { @@ -411,21 +456,14 @@ export function updateArtist(id: string, input: UpdateArtistInput): void { // ── Export / Import ─────────────────────────────────────────────────────────── -interface BandArtistRaw { - band_id: string; - artist_id: string; - role: string | null; - order_index: number; -} - export interface DbExport { - version: 1; + version: 3; exported_at: string; bands: Band[]; band_links: BandLink[]; artists: Artist[]; artist_links: ArtistLink[]; - band_artists: BandArtistRaw[]; + members: MemberRaw[]; band_revisions: BandRevision[]; artist_revisions: ArtistRevision[]; } @@ -433,13 +471,13 @@ export interface DbExport { export function exportDb(): DbExport { const db = getDb(); return { - version: 1, + version: 3, exported_at: new Date().toISOString(), bands: db.prepare("SELECT * FROM bands").all() as Band[], band_links: db.prepare("SELECT * FROM band_links ORDER BY band_id, order_index").all() as BandLink[], artists: db.prepare("SELECT * FROM artists").all() as Artist[], artist_links: db.prepare("SELECT * FROM artist_links ORDER BY artist_id, order_index").all() as ArtistLink[], - band_artists: db.prepare("SELECT * FROM band_artists ORDER BY band_id, order_index").all() as BandArtistRaw[], + members: db.prepare("SELECT * FROM members ORDER BY band_id, order_index").all() as MemberRaw[], band_revisions: db.prepare("SELECT * FROM band_revisions ORDER BY created_at").all() as BandRevision[], artist_revisions: db.prepare("SELECT * FROM artist_revisions ORDER BY created_at").all() as ArtistRevision[], }; @@ -450,15 +488,16 @@ export interface ImportResult { artists: number; band_links: number; artist_links: number; - band_artists: number; + members: number; band_revisions: number; artist_revisions: number; } export function importDb(data: DbExport): ImportResult { - if (data.version !== 1) throw new Error("Unsupported export version"); + if (data.version !== 3) throw new Error("Unsupported export version"); const db = getDb(); return db.transaction(() => { + db.prepare("DELETE FROM members").run(); db.prepare("DELETE FROM band_artists").run(); db.prepare("DELETE FROM band_revisions").run(); db.prepare("DELETE FROM artist_revisions").run(); @@ -495,11 +534,11 @@ export function importDb(data: DbExport): ImportResult { insertArtistLink.run(l.id, l.artist_id, l.label, l.url, l.order_index); } - const insertBandArtist = db.prepare( - "INSERT INTO band_artists (band_id, artist_id, role, order_index) VALUES (?, ?, ?, ?)" + const insertMember = db.prepare( + "INSERT INTO members (id, band_id, artist_id, role, since, until, note, order_index) VALUES (?, ?, ?, ?, ?, ?, ?, ?)" ); - for (const ba of data.band_artists) { - insertBandArtist.run(ba.band_id, ba.artist_id, ba.role, ba.order_index); + for (const m of data.members) { + insertMember.run(m.id, m.band_id, m.artist_id, m.role, m.since, m.until, m.note, m.order_index); } const insertBandRev = db.prepare( @@ -521,7 +560,7 @@ export function importDb(data: DbExport): ImportResult { artists: data.artists.length, band_links: data.band_links.length, artist_links: data.artist_links.length, - band_artists: data.band_artists.length, + members: data.members.length, band_revisions: data.band_revisions.length, artist_revisions: data.artist_revisions.length, }; diff --git a/app/routes/api-bands.tsx b/app/routes/api-bands.tsx index 64a9269..68efdeb 100644 --- a/app/routes/api-bands.tsx +++ b/app/routes/api-bands.tsx @@ -1,5 +1,5 @@ import type { ActionFunctionArgs } from "react-router"; -import { createBand, getIpAddress, listBands, toSlug } from "~/lib/db.server"; +import { createBand, getIpAddress, listBands, toSlug, type MemberInput } from "~/lib/db.server"; export function loader() { return Response.json(listBands()); @@ -33,7 +33,7 @@ export async function action({ request }: ActionFunctionArgs) { description: (body.description as string) || null, status: (body.status as string) || "active", links: (body.links as { label: string; url: string }[]) || [], - artists: (body.artists as { id: string; role: string | null }[]) || [], + members: (body.members as MemberInput[]) || [], message: (body.message as string) || "API import", ip_address: getIpAddress(request), }); diff --git a/app/routes/api-import.tsx b/app/routes/api-import.tsx index e28a7db..ca81fc7 100644 --- a/app/routes/api-import.tsx +++ b/app/routes/api-import.tsx @@ -13,8 +13,8 @@ export async function action({ request }: ActionFunctionArgs) { return Response.json({ error: "Invalid JSON body" }, { status: 400 }); } - if (!data || data.version !== 1) { - return Response.json({ error: "Invalid or unsupported export format (expected version 1)" }, { status: 400 }); + if (!data || data.version !== 3) { + return Response.json({ error: "Invalid or unsupported export format (expected version 3)" }, { status: 400 }); } try { diff --git a/app/routes/artist-by-uuid.tsx b/app/routes/artist-by-uuid.tsx index 6eb06a7..a65525b 100644 --- a/app/routes/artist-by-uuid.tsx +++ b/app/routes/artist-by-uuid.tsx @@ -1,18 +1,29 @@ import { data, Link, useLoaderData } from "react-router"; import type { LoaderFunctionArgs } from "react-router"; -import { getArtistBands, getArtistById, getArtistLinks, getArtistRevisions } from "~/lib/db.server"; +import { getArtistById, getArtistLinks, getArtistMembers, getArtistRevisions, type ArtistMemberRow } from "~/lib/db.server"; export async function loader({ params }: LoaderFunctionArgs) { const artist = getArtistById(params.uuid!); if (!artist) throw data("Not found", { status: 404 }); const links = getArtistLinks(artist.id); - const bands = getArtistBands(artist.id); + const memberships = getArtistMembers(artist.id); const revisions = getArtistRevisions(artist.id); - return { artist, links, bands, latest: revisions[0] ?? null }; + return { artist, links, memberships, latest: revisions[0] ?? null }; +} + +function periodLabel(m: ArtistMemberRow): string | null { + if (!m.since && !m.until) return null; + const from = m.since || "?"; + const to = m.until || "現在"; + return `${from} 〜 ${to}`; } export default function ArtistDetail() { - const { artist, links, bands, latest } = useLoaderData(); + const { artist, links, memberships, latest } = useLoaderData(); + + // group by band_id + const bandIds = [...new Set(memberships.map((m) => m.band_id))]; + return (
@@ -23,16 +34,36 @@ export default function ArtistDetail() {
- {bands.length > 0 && ( + {bandIds.length > 0 && (

バンド

    - {bands.map((b) => ( -
  • - {b.band_name} - {b.role && {b.role}} -
    • - ))} + {bandIds.map((bandId) => { + const group = memberships.filter((m) => m.band_id === bandId); + const first = group[0]; + return ( +
  • +
    + {first.band_name} + {first.role && {first.role}} +
    + {group.some((m) => m.since || m.until || m.note) && ( +
      + {group.map((m) => { + const label = periodLabel(m); + if (!label && !m.note) return null; + return ( +
    • + {label && {label}} + {m.note && {m.note}} +
      • + ); + })} +
    + )} +
    • + ); + })}
)} diff --git a/app/routes/band-by-uuid.tsx b/app/routes/band-by-uuid.tsx index 99a8b5c..2335e14 100644 --- a/app/routes/band-by-uuid.tsx +++ b/app/routes/band-by-uuid.tsx @@ -3,8 +3,9 @@ import type { LoaderFunctionArgs } from "react-router"; import { getBandById, getBandLinks, - getBandArtists, + getBandMembers, getBandRevisions, + type BandMemberRow, } from "~/lib/db.server"; import { LINK_TYPE_LABEL } from "~/lib/constants"; @@ -12,9 +13,9 @@ export async function loader({ params }: LoaderFunctionArgs) { const band = getBandById(params.uuid!); if (!band) throw data("Not found", { status: 404 }); const links = getBandLinks(band.id); - const artists = getBandArtists(band.id); + const members = getBandMembers(band.id); const revisions = getBandRevisions(band.id); - return { band, links, artists, latest: revisions[0] ?? null }; + return { band, links, members, latest: revisions[0] ?? null }; } const STATUS_LABEL: Record = { @@ -23,8 +24,19 @@ const STATUS_LABEL: Record = { disbanded: "解散", }; +function periodLabel(m: BandMemberRow): string | null { + if (!m.since && !m.until) return null; + const from = m.since || "?"; + const to = m.until || "現在"; + return `${from} 〜 ${to}`; +} + export default function BandDetail() { - const { band, links, artists, latest } = useLoaderData(); + const { band, links, members, latest } = useLoaderData(); + + // group members by artist_id for display + const artistIds = [...new Set(members.map((m) => m.artist_id))]; + return (
@@ -44,18 +56,43 @@ export default function BandDetail() {
- {artists.length > 0 && ( + {artistIds.length > 0 && (

メンバー

    - {artists.map((a) => ( -
  • - {a.artist_name} - {a.role && a.role.split(", ").filter(Boolean).map((r, i) => ( - {r} - ))} -
    • - ))} + {artistIds.map((artistId) => { + const group = members.filter((m) => m.artist_id === artistId); + const first = group[0]; + return ( +
  • +
    + {first.artist_name} + {group.flatMap((m) => + m.role ? m.role.split(", ").filter(Boolean).map((r, i) => ( + {r} + )) : [] + ).filter((_, i, arr) => { + // deduplicate role badges across periods (keep unique labels) + return true; + })} +
    + {group.some((m) => m.since || m.until || m.note) && ( +
      + {group.map((m) => { + const label = periodLabel(m); + if (!label && !m.note) return null; + return ( +
    • + {label && {label}} + {m.note && {m.note}} +
      • + ); + })} +
    + )} +
    • + ); + })}
)} diff --git a/app/routes/band-edit.tsx b/app/routes/band-edit.tsx index 2e29277..ffba70f 100644 --- a/app/routes/band-edit.tsx +++ b/app/routes/band-edit.tsx @@ -2,12 +2,13 @@ import { useState } from "react"; import { data, Form, Link, redirect, useActionData, useLoaderData } from "react-router"; import type { ActionFunctionArgs, LoaderFunctionArgs } from "react-router"; import { - getBandArtists, getBandById, getBandLinks, + getBandMembers, getIpAddress, listArtists, updateBand, + type MemberInput, } from "~/lib/db.server"; import { ARTIST_ROLES, LINK_TYPES } from "~/lib/constants"; @@ -15,9 +16,9 @@ export async function loader({ params }: LoaderFunctionArgs) { const band = getBandById(params.uuid!); if (!band) throw data("Not found", { status: 404 }); const links = getBandLinks(band.id); - const bandArtists = getBandArtists(band.id); + const members = getBandMembers(band.id); const allArtists = listArtists(); - return { band, links, bandArtists, allArtists }; + return { band, links, members, allArtists }; } export async function action({ params, request }: ActionFunctionArgs) { @@ -31,12 +32,8 @@ export async function action({ params, request }: ActionFunctionArgs) { const description = (fd.get("description") as string).trim() || null; const status = (fd.get("status") as string) || "active"; const message = (fd.get("message") as string).trim(); - const links: { label: string; url: string }[] = JSON.parse( - (fd.get("links") as string) || "[]" - ); - const artists: { id: string; role: string | null }[] = JSON.parse( - (fd.get("artists") as string) || "[]" - ); + const links: { label: string; url: string }[] = JSON.parse((fd.get("links") as string) || "[]"); + const members: MemberInput[] = JSON.parse((fd.get("members") as string) || "[]"); const errors: Record = {}; if (!name) errors.name = "必須です"; @@ -45,7 +42,7 @@ export async function action({ params, request }: ActionFunctionArgs) { if (Object.keys(errors).length > 0) return { errors }; try { - updateBand(band.id, { slug, name, area, description, status, links, artists, message, ip_address: getIpAddress(request) }); + updateBand(band.id, { slug, name, area, description, status, links, members, message, ip_address: getIpAddress(request) }); } catch (e) { if (e instanceof Error && e.message.includes("UNIQUE constraint failed: bands.slug")) { return { errors: { slug: "このslugは既に使用されています" } }; @@ -59,12 +56,21 @@ function toSlug(s: string) { return s.trim().toLowerCase().replace(/\s+/g, "-").replace(/[^\w぀-ヿ一-鿿＀-￯-]/g, "").replace(/^-+|-+$/g, ""); } -type PickedArtist = { id: string; name: string; roles: string[] }; -type PendingRole = { type: string; custom: string }; -const DEFAULT_PENDING: PendingRole = { type: ARTIST_ROLES[0], custom: "" }; +type MemberEntry = { + key: string; + artist_id: string; + artist_name: string; + roles: string[]; + since: string; + until: string; + note: string; + pendingRole: { type: string; custom: string }; +}; + +const DEFAULT_PENDING = { type: ARTIST_ROLES[0], custom: "" }; export default function BandEdit() { - const { band, links: initLinks, bandArtists: initArtists, allArtists } = useLoaderData(); + const { band, links: initLinks, members: initMembers, allArtists } = useLoaderData(); const actionData = useActionData(); const errors = actionData?.errors ?? {}; @@ -72,30 +78,73 @@ export default function BandEdit() { const [slug, setSlug] = useState(band.slug); const [slugManual, setSlugManual] = useState(true); const [links, setLinks] = useState(initLinks.map((l) => ({ label: l.label, url: l.url }))); - const [picked, setPicked] = useState( - initArtists.map((a) => ({ - id: a.artist_id, - name: a.artist_name, - roles: a.role ? a.role.split(", ").filter(Boolean) : [], + const [entries, setEntries] = useState( + initMembers.map((m) => ({ + key: crypto.randomUUID(), + artist_id: m.artist_id, + artist_name: m.artist_name, + roles: m.role ? m.role.split(", ").filter(Boolean) : [], + since: m.since, + until: m.until, + note: m.note, + pendingRole: { ...DEFAULT_PENDING }, })) ); - const [pending, setPending] = useState>( - Object.fromEntries(initArtists.map((a) => [a.artist_id, { ...DEFAULT_PENDING }])) - ); - const available = allArtists.filter((a) => !picked.some((p) => p.id === a.id)); + const usedArtistIds = new Set(entries.map((e) => e.artist_id)); + const available = allArtists.filter((a) => !usedArtistIds.has(a.id)); + const artistIds = [...new Set(entries.map((e) => e.artist_id))]; + + function addArtist(artistId: string) { + const a = allArtists.find((x) => x.id === artistId); + if (!a) return; + setEntries((prev) => [ + ...prev, + { key: crypto.randomUUID(), artist_id: a.id, artist_name: a.name, roles: [], since: "", until: "", note: "", pendingRole: { ...DEFAULT_PENDING } }, + ]); + } + + function addPeriod(artistId: string) { + const ref = entries.find((e) => e.artist_id === artistId); + if (!ref) return; + setEntries((prev) => [ + ...prev, + { key: crypto.randomUUID(), artist_id: ref.artist_id, artist_name: ref.artist_name, roles: [], since: "", until: "", note: "", pendingRole: { ...DEFAULT_PENDING } }, + ]); + } + + function removeArtist(artistId: string) { + setEntries((prev) => prev.filter((e) => e.artist_id !== artistId)); + } + + function removeEntry(key: string) { + setEntries((prev) => prev.filter((e) => e.key !== key)); + } + + function updateEntry(key: string, field: K, value: MemberEntry[K]) { + setEntries((prev) => prev.map((e) => e.key === key ? { ...e, [field]: value } : e)); + } - function addRole(artistId: string) { - const pend = pending[artistId] ?? DEFAULT_PENDING; - const role = pend.type === "other" ? pend.custom.trim() : pend.type; + function addRole(key: string) { + const entry = entries.find((e) => e.key === key); + if (!entry) return; + const role = entry.pendingRole.type === "other" ? entry.pendingRole.custom.trim() : entry.pendingRole.type; if (!role) return; - setPicked((prev) => prev.map((a) => a.id === artistId ? { ...a, roles: [...a.roles, role] } : a)); + setEntries((prev) => prev.map((e) => e.key === key ? { ...e, roles: [...e.roles, role] } : e)); } - function removeRole(artistId: string, idx: number) { - setPicked((prev) => prev.map((a) => a.id === artistId ? { ...a, roles: a.roles.filter((_, i) => i !== idx) } : a)); + function removeRole(key: string, idx: number) { + setEntries((prev) => prev.map((e) => e.key === key ? { ...e, roles: e.roles.filter((_, i) => i !== idx) } : e)); } + const serialized: MemberInput[] = entries.map((e) => ({ + artist_id: e.artist_id, + role: e.roles.join(", ") || null, + since: e.since, + until: e.until, + note: e.note, + })); + return (
@@ -105,11 +154,7 @@ export default function BandEdit() {
- ({ id: p.id, role: p.roles.join(", ") || null })))} - /> +
@@ -171,61 +216,80 @@ export default function BandEdit() {
))}
-
- {picked.length > 0 && ( + {artistIds.length > 0 && (
- {picked.map((p) => { - const pend = pending[p.id] ?? DEFAULT_PENDING; + {artistIds.map((artistId) => { + const group = entries.filter((e) => e.artist_id === artistId); return ( -
+
- {p.name} - + {group[0].artist_name} + +
- {p.roles.length > 0 && ( -
- {p.roles.map((r, ri) => ( - - {r} - - - ))} + {group.map((entry) => ( +
+ {group.length > 1 && ( +
+ +
+ )} + {entry.roles.length > 0 && ( +
+ {entry.roles.map((r, ri) => ( + + {r} + + + ))} +
+ )} +
+ + {entry.pendingRole.type === "other" && ( + updateEntry(entry.key, "pendingRole", { ...entry.pendingRole, custom: e.target.value })} + placeholder="ロール名" + /> + )} + +
+
+ updateEntry(entry.key, "since", e.target.value)} + placeholder="加入 (例: 2020-04)" + /> + 〜 + updateEntry(entry.key, "until", e.target.value)} + placeholder="脱退 (空欄=在籍中)" + /> + updateEntry(entry.key, "note", e.target.value)} + placeholder="ノート" + /> +
- )} -
- - {pend.type === "other" && ( - setPending({ ...pending, [p.id]: { ...pend, custom: e.target.value } })} - placeholder="ロール名" - /> - )} - -
+ ))}
); })} @@ -233,14 +297,7 @@ export default function BandEdit() { )} {available.length > 0 && ( - ({ id: p.id, role: p.roles.join(", ") || null })))} - /> +
@@ -149,61 +193,80 @@ export default function BandNew() {
))}
-
- {picked.length > 0 && ( + {artistIds.length > 0 && (
- {picked.map((p) => { - const pend = pending[p.id] ?? DEFAULT_PENDING; + {artistIds.map((artistId) => { + const group = entries.filter((e) => e.artist_id === artistId); return ( -
+
- {p.name} - + {group[0].artist_name} + +
- {p.roles.length > 0 && ( -
- {p.roles.map((r, ri) => ( - - {r} - - - ))} + {group.map((entry) => ( +
+ {group.length > 1 && ( +
+ +
+ )} + {entry.roles.length > 0 && ( +
+ {entry.roles.map((r, ri) => ( + + {r} + + + ))} +
+ )} +
+ + {entry.pendingRole.type === "other" && ( + updateEntry(entry.key, "pendingRole", { ...entry.pendingRole, custom: e.target.value })} + placeholder="ロール名" + /> + )} + +
+
+ updateEntry(entry.key, "since", e.target.value)} + placeholder="加入 (例: 2020-04)" + /> + 〜 + updateEntry(entry.key, "until", e.target.value)} + placeholder="脱退 (空欄=在籍中)" + /> + updateEntry(entry.key, "note", e.target.value)} + placeholder="ノート" + /> +
- )} -
- - {pend.type === "other" && ( - setPending({ ...pending, [p.id]: { ...pend, custom: e.target.value } })} - placeholder="ロール名" - /> - )} - -
+ ))}
); })} @@ -211,14 +274,7 @@ export default function BandNew() { )} {available.length > 0 ? (