From 184e6947707ecdf07dfa3a5cbc6e51cf9440e93a Mon Sep 17 00:00:00 2001
From: yyamashita <yyamashita@mosquit.one>
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 <noreply@anthropic.com>
---
 app/routes/band-edit.tsx | 229 +++++++++++++++++++++++++++++------------------
 1 file changed, 143 insertions(+), 86 deletions(-)

(limited to 'app/routes/band-edit.tsx')

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<string, string> = {};
   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<typeof loader>();
+  const { band, links: initLinks, members: initMembers, allArtists } = useLoaderData<typeof loader>();
   const actionData = useActionData<typeof action>();
   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<PickedArtist[]>(
-    initArtists.map((a) => ({
-      id: a.artist_id,
-      name: a.artist_name,
-      roles: a.role ? a.role.split(", ").filter(Boolean) : [],
+  const [entries, setEntries] = useState<MemberEntry[]>(
+    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<Record<string, PendingRole>>(
-    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<K extends keyof MemberEntry>(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 (
     <main>
       <div className="page-header">
@@ -105,11 +154,7 @@ export default function BandEdit() {
 
       <Form method="post">
         <input type="hidden" name="links" value={JSON.stringify(links)} />
-        <input
-          type="hidden"
-          name="artists"
-          value={JSON.stringify(picked.map((p) => ({ id: p.id, role: p.roles.join(", ") || null })))}
-        />
+        <input type="hidden" name="members" value={JSON.stringify(serialized)} />
 
         <div>
           <label>バンド名 <span className="req">*</span></label>
@@ -171,61 +216,80 @@ export default function BandEdit() {
               </div>
             ))}
           </div>
-          <button
-            type="button"
-            className="btn-text"
-            onClick={() => setLinks([...links, { label: LINK_TYPES[0].value, url: "" }])}
-          >
+          <button type="button" className="btn-text" onClick={() => setLinks([...links, { label: LINK_TYPES[0].value, url: "" }])}>
             + リンクを追加
           </button>
         </div>
 
         <div>
           <label>メンバー</label>
-          {picked.length > 0 && (
+          {artistIds.length > 0 && (
             <div className="members-form">
-              {picked.map((p) => {
-                const pend = pending[p.id] ?? DEFAULT_PENDING;
+              {artistIds.map((artistId) => {
+                const group = entries.filter((e) => e.artist_id === artistId);
                 return (
-                  <div key={p.id} className="member-card">
+                  <div key={artistId} className="member-group">
                     <div className="card-header">
-                      <span className="card-name">{p.name}</span>
-                      <button
-                        type="button"
-                        className="btn-icon"
-                        onClick={() => setPicked(picked.filter((a) => a.id !== p.id))}
-                      >
-                        削除
-                      </button>
+                      <span className="card-name">{group[0].artist_name}</span>
+                      <button type="button" className="btn-text" onClick={() => addPeriod(artistId)}>+ 期間追加</button>
+                      <button type="button" className="btn-icon" onClick={() => removeArtist(artistId)}>削除</button>
                     </div>
-                    {p.roles.length > 0 && (
-                      <div className="badges">
-                        {p.roles.map((r, ri) => (
-                          <span key={ri} className="badge">
-                            {r}
-                            <button type="button" onClick={() => removeRole(p.id, ri)}>×</button>
-                          </span>
-                        ))}
+                    {group.map((entry) => (
+                      <div key={entry.key} className="member-card">
+                        {group.length > 1 && (
+                          <div style={{ textAlign: "right" }}>
+                            <button type="button" className="btn-icon" onClick={() => removeEntry(entry.key)}>×</button>
+                          </div>
+                        )}
+                        {entry.roles.length > 0 && (
+                          <div className="badges">
+                            {entry.roles.map((r, ri) => (
+                              <span key={ri} className="badge">
+                                {r}
+                                <button type="button" onClick={() => removeRole(entry.key, ri)}>×</button>
+                              </span>
+                            ))}
+                          </div>
+                        )}
+                        <div className="role-row">
+                          <select
+                            value={entry.pendingRole.type}
+                            onChange={(e) => updateEntry(entry.key, "pendingRole", { ...entry.pendingRole, type: e.target.value })}
+                          >
+                            {ARTIST_ROLES.map((r) => <option key={r} value={r}>{r}</option>)}
+                            <option value="other">その他...</option>
+                          </select>
+                          {entry.pendingRole.type === "other" && (
+                            <input
+                              className="custom-input"
+                              value={entry.pendingRole.custom}
+                              onChange={(e) => updateEntry(entry.key, "pendingRole", { ...entry.pendingRole, custom: e.target.value })}
+                              placeholder="ロール名"
+                            />
+                          )}
+                          <button type="button" className="btn-text" onClick={() => addRole(entry.key)}>+ 追加</button>
+                        </div>
+                        <div className="period-row">
+                          <input
+                            value={entry.since}
+                            onChange={(e) => updateEntry(entry.key, "since", e.target.value)}
+                            placeholder="加入 (例: 2020-04)"
+                          />
+                          <span className="period-sep">〜</span>
+                          <input
+                            value={entry.until}
+                            onChange={(e) => updateEntry(entry.key, "until", e.target.value)}
+                            placeholder="脱退 (空欄=在籍中)"
+                          />
+                          <input
+                            className="period-note"
+                            value={entry.note}
+                            onChange={(e) => updateEntry(entry.key, "note", e.target.value)}
+                            placeholder="ノート"
+                          />
+                        </div>
                       </div>
-                    )}
-                    <div className="role-row">
-                      <select
-                        value={pend.type}
-                        onChange={(e) => setPending({ ...pending, [p.id]: { ...pend, type: e.target.value } })}
-                      >
-                        {ARTIST_ROLES.map((r) => <option key={r} value={r}>{r}</option>)}
-                        <option value="other">その他...</option>
-                      </select>
-                      {pend.type === "other" && (
-                        <input
-                          className="custom-input"
-                          value={pend.custom}
-                          onChange={(e) => setPending({ ...pending, [p.id]: { ...pend, custom: e.target.value } })}
-                          placeholder="ロール名"
-                        />
-                      )}
-                      <button type="button" className="btn-text" onClick={() => addRole(p.id)}>+ 追加</button>
-                    </div>
+                    ))}
                   </div>
                 );
               })}
@@ -233,14 +297,7 @@ export default function BandEdit() {
           )}
           {available.length > 0 && (
             <select
-              onChange={(e) => {
-                const a = allArtists.find((x) => x.id === e.target.value);
-                if (a) {
-                  setPicked([...picked, { id: a.id, name: a.name, roles: [] }]);
-                  setPending({ ...pending, [a.id]: { ...DEFAULT_PENDING } });
-                  e.target.value = "";
-                }
-              }}
+              onChange={(e) => { if (e.target.value) { addArtist(e.target.value); e.target.value = ""; } }}
               defaultValue=""
             >
               <option value="">+ アーティストを追加...</option>
-- 
cgit v1.2.3