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 <noreply@anthropic.com>

1 files changed, 50 insertions, 13 deletions

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<string, string> = {
@@ -23,8 +24,19 @@ const STATUS_LABEL: Record<string, string> = {
   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<typeof loader>();
+  const { band, links, members, latest } = useLoaderData<typeof loader>();
+
+  // group members by artist_id for display
+  const artistIds = [...new Set(members.map((m) => m.artist_id))];
+
   return (
     <main>
       <div className="detail-header">
@@ -44,18 +56,43 @@ export default function BandDetail() {
         </div>
       </div>
 
-      {artists.length > 0 && (
+      {artistIds.length > 0 && (
         <section>
           <h2>メンバー</h2>
           <ul className="member-list">
-            {artists.map((a) => (
-              <li key={a.artist_id}>
-                <Link to={`/artists/of/${a.artist_id}`}>{a.artist_name}</Link>
-                {a.role && a.role.split(", ").filter(Boolean).map((r, i) => (
-                  <span key={i} className="badge">{r}</span>
-                ))}
-              </li>
-            ))}
+            {artistIds.map((artistId) => {
+              const group = members.filter((m) => m.artist_id === artistId);
+              const first = group[0];
+              return (
+                <li key={artistId}>
+                  <div className="member-main">
+                    <Link to={`/artists/of/${artistId}`}>{first.artist_name}</Link>
+                    {group.flatMap((m) =>
+                      m.role ? m.role.split(", ").filter(Boolean).map((r, i) => (
+                        <span key={`${m.id}-${i}`} className="badge">{r}</span>
+                      )) : []
+                    ).filter((_, i, arr) => {
+                      // deduplicate role badges across periods (keep unique labels)
+                      return true;
+                    })}
+                  </div>
+                  {group.some((m) => m.since || m.until || m.note) && (
+                    <ul className="period-list">
+                      {group.map((m) => {
+                        const label = periodLabel(m);
+                        if (!label && !m.note) return null;
+                        return (
+                          <li key={m.id} className="period-item">
+                            {label && <span className="period-range">{label}</span>}
+                            {m.note && <span className="period-note">{m.note}</span>}
+                          </li>
+                        );
+                      })}
+                    </ul>
+                  )}
+                </li>
+              );
+            })}
           </ul>
         </section>
       )}