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/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 +++++++++++++++++++++++-------------- 7 files changed, 372 insertions(+), 190 deletions(-) (limited to 'app/routes') 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 ? (