/* === useBrainNotes(householdId) — second per-table hook, mirrors
   useExpenses. Owns the round-trip for brain dump's high-write
   collections: notes + checklist items. tags + pinOrder stay in
   the JSON state blob (rare writes, low collision risk — see
   migration 0007's header comment for the design rationale).

   Returns flat-shape notes (matching the blob's note shape) so
   call sites don't need a different mental model than they have
   today. Internal storage joins brain_notes rows with their
   brain_note_items children at load + on realtime echoes.

   Returns:
     {
       notes,              // [{id, type, title, body, tags, items, ...}] — flat shape
       loading,
       error,

       // Note CRUD
       addNote(noteWithItems),
       updateNote(id, newNote),          // full note object; we diff items
       deleteNote(id),                   // soft-delete note + cascade soft-delete items

       // Item-level (for cheap concurrent toggles)
       updateItem(itemId, patch),

       // Tag cascades — bulk server-side UPDATEs on brain_notes.content
       cascadeTagRename(oldName, newName),
       cascadeTagSubRemove(parentName, subName),
       cascadeTagDelete(name),
     }
*/

// Row → flat blob-shape note. Items array is attached separately
// from the row's children (handled in the load + realtime layer).
function brainNoteFromRow(row, items) {
  const flat = {
    id: row.id,
    authorId: row.author_id || null,
    type: row.type,
    archived: !!row.archived,
    pinned: !!row.pinned,
    privacy: row.privacy,
    source: row.source,
    ...(row.content || {}),
    // Server-stamped (set_updated_at trigger), so it's the honest "last
    // edited" even when another member made the edit. Read-only on the
    // client — brainNoteToRow strips it back off before any write.
    updatedAt: row.updated_at || row.created_at || null,
  };
  if (row.type === 'checklist') {
    flat.items = (items || []).map(brainItemFromRow);
  }
  return flat;
}

// Flat blob-shape note → table row + child items.
// Splits structural fields out of the flat shape; everything else
// (title, body, tags, color, priority, due, target, cadence, mode,
// targetUnit, done-for-todo, createdAt) lands in content.
function brainNoteToRow(note, householdId) {
  const {
    id, authorId, type, archived, pinned, privacy, source,
    items,            // pulled off — lives in child table
    updatedAt,        // server-owned column, never written from the client
    _updatedAt,       // hook-internal, never persisted
    ...content
  } = note;
  return {
    row: {
      id,
      household_id: householdId,
      author_id: authorId || null,
      type: type || 'note',
      archived: !!archived,
      pinned: !!pinned,
      privacy: privacy || 'private',
      source: source || 'app',
      content,
    },
    items: items || null,
  };
}

function brainItemFromRow(row) {
  return {
    id: row.id,
    text: row.text || '',
    done: !!row.done,
    position: row.position,
  };
}

function brainItemToRow(item, noteId, householdId, position) {
  return {
    id: item.id,
    household_id: householdId,
    note_id: noteId,
    text: item.text || '',
    done: !!item.done,
    position: typeof position === 'number' ? position : (item.position || 0),
  };
}

function newBrainNoteId()  { return 'b'  + Date.now().toString(36) + Math.random().toString(36).slice(2, 5); }
function newBrainItemId()  { return 'bi' + Date.now().toString(36) + Math.random().toString(36).slice(2, 5); }

// Echo-guard set with a TTL. A write registers its id here so the
// matching realtime echo is skipped; the echo clears it. But if the
// echo never arrives (channel drop, backgrounded tab, dropped packet)
// a plain Set would keep the id forever and swallow the NEXT genuine
// remote update for that note/item. Entries older than ttlMs are
// treated as expired so a real inbound change is never lost.
function makePendingIds(ttlMs = 10000) {
  const m = new Map();
  return {
    add(id)    { m.set(id, Date.now()); },
    delete(id) { m.delete(id); },
    has(id) {
      const t = m.get(id);
      if (t === undefined) return false;
      if (Date.now() - t > ttlMs) { m.delete(id); return false; }
      return true;
    },
  };
}

function useBrainNotes(householdId) {
  const [notes, setNotes]   = React.useState([]);
  const [loading, setLoading] = React.useState(true);
  const [error, setError]   = React.useState(null);

  // pendingIds for echo guard. Holds both note ids and item ids
  // (their namespaces don't overlap — 'b' vs 'bi' prefix). TTL-backed
  // (see makePendingIds) so a lost echo can't permanently swallow a
  // later genuine remote update for the same id.
  const pendingIds = React.useRef(null);
  if (!pendingIds.current) pendingIds.current = makePendingIds();

  // Mutates notes state to apply an item-level change. Items
  // realtime fires per-row, so we don't get a whole-note refresh;
  // we surgically update items[] on the parent note.
  const applyItemChange = (itemRow, changeType) => {
    setNotes(prev => prev.map(n => {
      if (n.id !== itemRow.note_id) return n;
      const existing = n.items || [];
      if (changeType === 'delete' || itemRow.deleted_at) {
        return { ...n, items: existing.filter(i => i.id !== itemRow.id) };
      }
      const incoming = brainItemFromRow(itemRow);
      const i = existing.findIndex(it => it.id === itemRow.id);
      const next = i < 0
        ? [...existing, incoming]
        : existing.map(it => it.id === itemRow.id ? incoming : it);
      next.sort((a, b) => (a.position || 0) - (b.position || 0));
      return { ...n, items: next };
    }));
  };

  // Initial load: notes + items, then join.
  React.useEffect(() => {
    if (!householdId) return;
    let cancelled = false;
    setLoading(true);
    (async () => {
      const [notesRes, itemsRes] = await Promise.all([
        supabaseClient.from('brain_notes')
          .select('*')
          .eq('household_id', householdId)
          .is('deleted_at', null)
          .order('created_at', { ascending: false }),
        supabaseClient.from('brain_note_items')
          .select('*')
          .eq('household_id', householdId)
          .is('deleted_at', null)
          .order('position', { ascending: true }),
      ]);
      if (cancelled) return;
      if (notesRes.error) { setError(notesRes.error.message); setLoading(false); return; }
      if (itemsRes.error) { setError(itemsRes.error.message); setLoading(false); return; }
      const itemsByNote = new Map();
      (itemsRes.data || []).forEach(row => {
        const arr = itemsByNote.get(row.note_id) || [];
        arr.push(row);
        itemsByNote.set(row.note_id, arr);
      });
      const joined = (notesRes.data || []).map(row =>
        brainNoteFromRow(row, itemsByNote.get(row.id))
      );
      setNotes(joined);
      setError(null);
      setLoading(false);
    })();
    return () => { cancelled = true; };
  }, [householdId]);

  // Realtime: brain_notes INSERT/UPDATE + brain_note_items INSERT/UPDATE.
  // We skip echoes of our own writes (pendingIds set / cleared on echo).
  React.useEffect(() => {
    if (!householdId) return;
    const channel = supabaseClient
      .channel(`brain:${householdId}`)
      .on('postgres_changes',
          { event: 'INSERT', schema: 'public', table: 'brain_notes', filter: `household_id=eq.${householdId}` },
          (payload) => {
            const row = payload.new;
            if (pendingIds.current.has(row.id)) { pendingIds.current.delete(row.id); return; }
            setNotes(prev => {
              if (prev.some(n => n.id === row.id)) return prev;
              return [brainNoteFromRow(row, []), ...prev];
            });
          })
      .on('postgres_changes',
          { event: 'UPDATE', schema: 'public', table: 'brain_notes', filter: `household_id=eq.${householdId}` },
          (payload) => {
            const row = payload.new;
            if (pendingIds.current.has(row.id)) { pendingIds.current.delete(row.id); return; }
            setNotes(prev => {
              if (row.deleted_at) return prev.filter(n => n.id !== row.id);
              const existing = prev.find(n => n.id === row.id);
              const items = existing?.items || [];
              return prev.map(n => n.id === row.id ? brainNoteFromRow(row, items) : n);
            });
          })
      .on('postgres_changes',
          { event: 'INSERT', schema: 'public', table: 'brain_note_items', filter: `household_id=eq.${householdId}` },
          (payload) => {
            if (pendingIds.current.has(payload.new.id)) { pendingIds.current.delete(payload.new.id); return; }
            applyItemChange(payload.new, 'insert');
          })
      .on('postgres_changes',
          { event: 'UPDATE', schema: 'public', table: 'brain_note_items', filter: `household_id=eq.${householdId}` },
          (payload) => {
            if (pendingIds.current.has(payload.new.id)) { pendingIds.current.delete(payload.new.id); return; }
            applyItemChange(payload.new, 'update');
          })
      .subscribe();
    return () => { supabaseClient.removeChannel(channel); };
  }, [householdId]);

  // ----- Mutations -----

  const addNote = React.useCallback(async (note) => {
    if (!householdId) throw new Error('No household');
    const id = note.id || newBrainNoteId();
    // Stamp updatedAt locally so "Last edited" reorders on the spot; the
    // server's own value replaces it on the next load.
    const full = { ...note, id, updatedAt: new Date().toISOString() };
    const { row, items } = brainNoteToRow(full, householdId);
    pendingIds.current.add(id);
    setNotes(prev => [full, ...prev]);
    const { error } = await supabaseClient.from('brain_notes').insert(row);
    if (error) {
      pendingIds.current.delete(id);
      setNotes(prev => prev.filter(n => n.id !== id));
      setError(error.message);
      throw error;
    }
    // Insert child items (checklist). Sequential so partial failure leaves
    // the note without its items rather than the reverse.
    if (full.type === 'checklist' && items?.length) {
      const itemRows = items.map((it, idx) => {
        const itemId = it.id || newBrainItemId();
        pendingIds.current.add(itemId);
        return brainItemToRow({ ...it, id: itemId }, id, householdId, idx);
      });
      const { error: itemsErr } = await supabaseClient.from('brain_note_items').insert(itemRows);
      if (itemsErr) {
        // Don't roll back the note — the user can still see/edit it; the
        // items will be re-attempted on next save. Log and surface.
        itemRows.forEach(r => pendingIds.current.delete(r.id));
        setError('Note saved but items failed: ' + itemsErr.message);
      }
    }
    setError(null);
    return full;
  }, [householdId]);

  // Reconcile child items against the new desired list. Used by
  // updateNote for checklist edits — diffs by id: new ids INSERT,
  // changed ids UPDATE (text/done/position), missing ids soft-delete.
  const reconcileItems = async (noteId, prevItems, nextItems) => {
    const prevById = new Map((prevItems || []).map(i => [i.id, i]));
    const nextById = new Map((nextItems || []).map(i => [i.id, i]));
    const toInsert = [];
    const toUpdate = [];
    const toDelete = [];
    (nextItems || []).forEach((it, idx) => {
      const prev = prevById.get(it.id);
      if (!prev) {
        const id = it.id || newBrainItemId();
        toInsert.push(brainItemToRow({ ...it, id }, noteId, householdId, idx));
      } else if (prev.text !== it.text || prev.done !== it.done || prev.position !== idx) {
        toUpdate.push({ id: it.id, text: it.text || '', done: !!it.done, position: idx });
      }
    });
    (prevItems || []).forEach(prev => {
      if (!nextById.has(prev.id)) toDelete.push(prev.id);
    });
    const ops = [];
    if (toInsert.length) {
      toInsert.forEach(r => pendingIds.current.add(r.id));
      ops.push(supabaseClient.from('brain_note_items').insert(toInsert));
    }
    toUpdate.forEach(p => {
      pendingIds.current.add(p.id);
      ops.push(supabaseClient.from('brain_note_items')
        .update({ text: p.text, done: p.done, position: p.position })
        .eq('id', p.id));
    });
    toDelete.forEach(id => {
      pendingIds.current.add(id);
      ops.push(supabaseClient.from('brain_note_items')
        .update({ deleted_at: new Date().toISOString() })
        .eq('id', id));
    });
    if (!ops.length) return;
    const results = await Promise.all(ops);
    const errs = results.filter(r => r.error).map(r => r.error.message);
    if (errs.length) throw new Error(errs.join('; '));
  };

  const updateNote = React.useCallback(async (id, newNote) => {
    if (!householdId) throw new Error('No household');
    const prevSnapshot = notes.find(n => n.id === id) || null;
    // Optimistic local write — insert if the note isn't in hook state
    // yet (drift self-heal), otherwise replace it in place.
    pendingIds.current.add(id);
    const local = { ...newNote, id, updatedAt: new Date().toISOString() };
    setNotes(prev => prev.some(n => n.id === id)
      ? prev.map(n => n.id === id ? local : n)
      : [local, ...prev]);
    const { row, items: nextItems } = brainNoteToRow(local, householdId);
    // UPSERT, not UPDATE: a bare .update().eq() against a row that isn't
    // in the table (e.g. an earlier addNote INSERT that silently failed)
    // matches zero rows and PostgREST reports SUCCESS — the edit is lost
    // and the note never reappears on the table-sourced wall. Upserting
    // recreates the row instead. household_id stays on the payload so the
    // INSERT branch satisfies NOT NULL + RLS.
    const { error } = await supabaseClient.from('brain_notes').upsert(row);
    if (error) {
      pendingIds.current.delete(id);
      setNotes(prev => prevSnapshot
        ? prev.map(n => n.id === id ? prevSnapshot : n)
        : prev.filter(n => n.id !== id));
      setError(error.message);
      throw error;
    }
    // Reconcile items if this is (or became) a checklist.
    if (row.type === 'checklist' && nextItems !== null) {
      try {
        await reconcileItems(id, prevSnapshot?.items || [], nextItems);
      } catch (itemsErr) {
        setError('Note updated but items failed: ' + itemsErr.message);
      }
    }
    setError(null);
  }, [householdId, notes]);

  const deleteNote = React.useCallback(async (id) => {
    if (!householdId) throw new Error('No household');
    const prevSnapshot = notes.find(n => n.id === id) || null;
    if (prevSnapshot) {
      pendingIds.current.add(id);
      setNotes(prev => prev.filter(n => n.id !== id));
    }
    const now = new Date().toISOString();
    const [noteRes, itemsRes] = await Promise.all([
      supabaseClient.from('brain_notes').update({ deleted_at: now }).eq('id', id),
      supabaseClient.from('brain_note_items').update({ deleted_at: now }).eq('note_id', id),
    ]);
    if (noteRes.error) {
      if (prevSnapshot) {
        pendingIds.current.delete(id);
        setNotes(prev => [prevSnapshot, ...prev]);
      }
      setError(noteRes.error.message);
      throw noteRes.error;
    }
    if (itemsRes.error) setError('Note deleted but items soft-delete failed: ' + itemsRes.error.message);
    else setError(null);
  }, [householdId, notes]);

  // Item-level update — the only sub-row API the screens use directly
  // (everything else flows through updateNote → reconcileItems). Cheap
  // single-row UPDATE — exactly the call you want when two devices are
  // toggling different items in the same checklist.
  const updateItem = React.useCallback(async (itemId, patch) => {
    if (!householdId) throw new Error('No household');
    // Local optimistic update — find the parent note, swap the item.
    let prevSnapshot = null;
    setNotes(prev => prev.map(n => {
      if (!n.items?.length) return n;
      const i = n.items.findIndex(it => it.id === itemId);
      if (i < 0) return n;
      prevSnapshot = n.items[i];
      const next = [...n.items];
      next[i] = { ...next[i], ...patch };
      return { ...n, items: next };
    }));
    if (prevSnapshot) pendingIds.current.add(itemId);
    const columnPatch = {};
    if ('text'     in patch) columnPatch.text     = patch.text || '';
    if ('done'     in patch) columnPatch.done     = !!patch.done;
    if ('position' in patch) columnPatch.position = patch.position;
    const { error } = await supabaseClient.from('brain_note_items').update(columnPatch).eq('id', itemId);
    if (error) {
      // Best-effort rollback — find the parent note again, swap back.
      if (prevSnapshot) {
        pendingIds.current.delete(itemId);
        setNotes(prev => prev.map(n => {
          if (!n.items?.length) return n;
          const i = n.items.findIndex(it => it.id === itemId);
          if (i < 0) return n;
          const next = [...n.items];
          next[i] = prevSnapshot;
          return { ...n, items: next };
        }));
      }
      setError(error.message);
      throw error;
    }
    setError(null);
  }, [householdId]);

  // ----- Tag cascades -----
  // The user-tag library lives in the blob (state.brainDump.tags). When
  // a tag is renamed / a sub-tag removed / a tag deleted there, the
  // change must also cascade into every note's content.tags array. The
  // hook handles this server-side with jsonb manipulation — covers all
  // rows including any not currently in local state.
  //
  // Note: jsonb_path_query etc. would let us filter to only rows that
  // reference the tag, but the simpler approach is to UPDATE all rows
  // for the household with a jsonb_set / array_remove transform; rows
  // that don't carry the tag end up rewriting to identical content.
  // At brain-dump scale (~hundreds of notes per household) this is
  // negligible. If it ever matters we can add a JSONB index on
  // content->'tags' and filter to candidates first.

  const cascadeTagRename = React.useCallback(async (oldName, newName) => {
    if (!householdId) return null;
    // SQL: for each note, update content.tags entries where tag matches.
    // We round-trip through the client because Supabase JS doesn't expose
    // jsonb mutation operators directly. Fetch matching rows' content,
    // patch in JS, write back.
    const { data, error } = await supabaseClient.from('brain_notes')
      .select('id, content')
      .eq('household_id', householdId)
      .is('deleted_at', null);
    if (error) throw error;
    const affected = (data || []).filter(r =>
      Array.isArray(r.content?.tags) && r.content.tags.some(t => t?.tag === oldName)
    );
    if (!affected.length) return 0;
    const writes = affected.map(r => {
      const nextTags = r.content.tags.map(t => t?.tag === oldName ? { ...t, tag: newName } : t);
      const nextContent = { ...r.content, tags: nextTags };
      pendingIds.current.add(r.id);
      return supabaseClient.from('brain_notes').update({ content: nextContent }).eq('id', r.id);
    });
    const results = await Promise.all(writes);
    const errs = results.filter(x => x.error);
    if (errs.length) throw new Error(errs.map(e => e.error.message).join('; '));
    return affected.length;
  }, [householdId]);

  const cascadeTagSubRemove = React.useCallback(async (parentName, subName) => {
    if (!householdId) return null;
    const { data, error } = await supabaseClient.from('brain_notes')
      .select('id, content')
      .eq('household_id', householdId)
      .is('deleted_at', null);
    if (error) throw error;
    const affected = (data || []).filter(r =>
      Array.isArray(r.content?.tags) && r.content.tags.some(t => t?.tag === parentName && t?.sub === subName)
    );
    if (!affected.length) return 0;
    const writes = affected.map(r => {
      const nextTags = r.content.tags.map(t =>
        (t?.tag === parentName && t?.sub === subName) ? { tag: t.tag } : t
      );
      pendingIds.current.add(r.id);
      return supabaseClient.from('brain_notes').update({ content: { ...r.content, tags: nextTags } }).eq('id', r.id);
    });
    const results = await Promise.all(writes);
    const errs = results.filter(x => x.error);
    if (errs.length) throw new Error(errs.map(e => e.error.message).join('; '));
    return affected.length;
  }, [householdId]);

  const cascadeTagDelete = React.useCallback(async (name) => {
    if (!householdId) return null;
    const { data, error } = await supabaseClient.from('brain_notes')
      .select('id, content')
      .eq('household_id', householdId)
      .is('deleted_at', null);
    if (error) throw error;
    const affected = (data || []).filter(r =>
      Array.isArray(r.content?.tags) && r.content.tags.some(t => t?.tag === name)
    );
    if (!affected.length) return 0;
    const writes = affected.map(r => {
      const nextTags = r.content.tags.filter(t => t?.tag !== name);
      pendingIds.current.add(r.id);
      return supabaseClient.from('brain_notes').update({ content: { ...r.content, tags: nextTags } }).eq('id', r.id);
    });
    const results = await Promise.all(writes);
    const errs = results.filter(x => x.error);
    if (errs.length) throw new Error(errs.map(e => e.error.message).join('; '));
    return affected.length;
  }, [householdId]);

  // Seed the pre-written Commonplace guide cards. Separate from addNote
  // for one reason: the ids are deterministic, and two household members
  // opening the app at the same moment would both try to write them. An
  // ignore-duplicates upsert makes the loser of that race a silent no-op
  // instead of a primary-key error and a sync-failure toast.
  //
  // Guide cards are plain notes and never checklists, so there are no
  // child items to insert. Rows the server already had are left alone —
  // including ones the user has since edited.
  const seedNotes = React.useCallback(async (guideNotes) => {
    if (!householdId || !guideNotes?.length) return 0;
    const rows = guideNotes.map(n => brainNoteToRow(n, householdId).row);
    rows.forEach(r => pendingIds.current.add(r.id));
    const { error: seedErr } = await supabaseClient.from('brain_notes')
      .upsert(rows, { onConflict: 'id', ignoreDuplicates: true });
    if (seedErr) {
      rows.forEach(r => pendingIds.current.delete(r.id));
      throw seedErr;
    }
    // Show them immediately rather than waiting on the realtime echo,
    // which the pendingIds guard above is about to swallow anyway.
    setNotes(prev => {
      const have = new Set(prev.map(n => n.id));
      const fresh = guideNotes.filter(n => !have.has(n.id));
      return fresh.length ? [...fresh, ...prev] : prev;
    });
    return rows.length;
  }, [householdId]);

  return {
    notes, loading, error,
    addNote, updateNote, deleteNote,
    updateItem, seedNotes,
    cascadeTagRename, cascadeTagSubRemove, cascadeTagDelete,
  };
}

Object.assign(window, { useBrainNotes });