/* global React, sbx, slotHM, audit, auditSnapshot */
// Data layer for the course-partner portal + the admin "course access" panel.
// Everything here is scoped to a single course and relies on the RLS added in
// v1/sql/course-managers.sql (managers can only touch their own course's rows).

// ─── Who does this user manage? (drives the gate + admin panel) ─────────────
//   undefined = loading · [] = none · [{ id, course_id, role, course }] = managed
function useManagedCourses(userId) {
  const [rows, setRows] = React.useState(undefined);
  const load = React.useCallback(async () => {
    if (!userId) { setRows(undefined); return; }
    const { data, error } = await sbx
      .from('course_managers')
      .select('id, course_id, role, course:courses(*)')
      .eq('user_id', userId);
    if (error) { setRows([]); return; }
    setRows((data || []).filter(r => r.course)); // drop any orphaned links
  }, [userId]);
  React.useEffect(() => { load(); }, [load]);
  return [rows, load];
}

// Set of user_ids who manage at least one course (for the Users role split).
function useManagerIds() {
  const [ids, setIds] = React.useState(null);
  React.useEffect(() => {
    let on = true;
    sbx.from('course_managers').select('user_id').then(({ data }) => {
      if (on) setIds(new Set((data || []).map(r => r.user_id)));
    });
    return () => { on = false; };
  }, []);
  return ids;
}

// ─── Admin: assign / remove a course manager ────────────────────────────────
async function addCourseManager({ userId, courseId, createdBy }) {
  const { error } = await sbx.from('course_managers')
    .insert({ user_id: userId, course_id: courseId, created_by: createdBy });
  if (error) {
    if (error.code === '23505') throw new Error('They already manage that course.');
    if (/row-level security|permission/i.test(error.message || '')) throw new Error('Only admins can assign course managers.');
    throw new Error(error.message || 'Could not assign.');
  }
  await audit('course_access_grant', 'course_managers', userId, null, { course_id: courseId });
}
async function removeCourseManager(id) {
  const { data: was } = await sbx.from('course_managers').select('user_id, course_id').eq('id', id).maybeSingle();
  const { error } = await sbx.from('course_managers').delete().eq('id', id);
  if (error) throw new Error(error.message || 'Could not remove.');
  await audit('course_access_revoke', 'course_managers', id,
    auditSnapshot(was, ['user_id', 'course_id']), null);
}

// Create a brand-new manager LOGIN (email + password) and link it to a course,
// via the create-manager Edge Function (which safely holds the service-role key
// server-side). Requires the function to be deployed.
async function createManagerAccount({ email, password, courseId, firstName, lastName, handle }) {
  const { data, error } = await sbx.functions.invoke('create-manager', {
    body: { email, password, courseId, firstName, lastName, handle },
  });
  if (error) {
    let msg = error.message || 'Could not create the account.';
    // FunctionsHttpError carries the response; pull our JSON error out of it.
    try { const body = await error.context.json(); if (body && body.error) msg = body.error; } catch (_) { /* noop */ }
    if (/Failed to send a request|Function not found|404/i.test(msg)) {
      msg = 'The create-manager function isn’t deployed yet. Deploy it in Supabase, then try again.';
    }
    throw new Error(msg);
  }
  if (data && data.error) throw new Error(data.error);
  return data;
}

// ─── Tee slots for one course (manager-editable window) ─────────────────────
//   Loads slots from `from` (a Date) forward, soonest first.
function useCourseSlots(courseId, fromISO) {
  const [rows, setRows] = React.useState(null);
  const load = React.useCallback(async () => {
    if (!courseId) { setRows([]); return; }
    let q = sbx.from('tee_slots').select('*').eq('course_id', courseId).order('starts_at');
    if (fromISO) q = q.gte('starts_at', fromISO);
    const { data } = await q;
    setRows(data || []);
  }, [courseId, fromISO]);
  React.useEffect(() => { load(); }, [load]);
  return [rows, load];
}

async function saveSlot(slot) {
  const payload = {
    course_id:     slot.course_id,
    starts_at:     slot.starts_at,
    capacity:      4, // a tee time is always a foursome
    price:         Number(slot.price) || 0,
    type:          slot.type || 'open',
    title:         slot.title ? slot.title.trim() : null,
    status:        slot.status || 'open',
    includes_cart: !!slot.includes_cart,
  };
  if (slot.id) {
    const { error } = await sbx.from('tee_slots').update(payload).eq('id', slot.id);
    if (error) throw slotError(error);
  } else {
    const { error } = await sbx.from('tee_slots').insert(payload);
    if (error) throw slotError(error);
  }
}
async function deleteSlot(id) {
  const { error } = await sbx.from('tee_slots').delete().eq('id', id);
  if (error) throw slotError(error);
}

// Publish a day's tee times: make every time in `times` (['16:30',…]) a public,
// bookable open slot at the given price + cart flag. Nothing is public until
// this runs (it's the manager's "Save changes" action). Upsert UPDATES existing
// slots at those times (so the price/cart edits apply) and inserts new ones.
// Returns the number of public times after saving.
async function publishDayTimes({ courseId, dateStr, times, price, includesCart }) {
  if (!times.length) return 0;
  const rows = times.map(t => ({
    course_id:     courseId,
    starts_at:     new Date(`${dateStr}T${t}:00`).toISOString(),
    capacity:      4,
    price:         Number(price) || 0,
    type:          'open',
    status:        'open',
    includes_cart: !!includesCart,
  }));
  const { data, error } = await sbx.from('tee_slots')
    .upsert(rows, { onConflict: 'course_id,starts_at' })
    .select('id');
  if (error) throw slotError(error);
  return (data || []).length;
}

// Make MULTIPLE days' live schedules match `times` exactly — same rule as a
// single day's Save: publish every time, then remove any live slot that
// ISN'T in the list, unless it already has a real booking (always
// protected). This is what makes "Apply to next 10 days" actually UPDATE a
// day (e.g. a changed interval prunes the old off-interval slots) instead
// of just layering the new times on top of whatever was already live.
async function applyScheduleAcrossDays({ courseId, dates, times, price, includesCart }) {
  let daysChanged = 0, protectedCount = 0;
  for (const dateStr of dates) {
    const start = new Date(`${dateStr}T00:00:00`);
    const end = new Date(start.getTime() + 864e5);
    const { data: daySlots } = await sbx.from('tee_slots').select('id, starts_at, status') // eslint-disable-line no-await-in-loop
      .eq('course_id', courseId).gte('starts_at', start.toISOString()).lt('starts_at', end.toISOString());
    const live = (daySlots || []).filter(s => s.status === 'open');
    const targetSet = new Set(times);
    const toRemove = live.filter(s => !targetSet.has(slotHM(s.starts_at)));

    let removable = toRemove;
    if (toRemove.length) {
      const ids = toRemove.map(s => s.id);
      const { data: bk } = await sbx.from('bookings').select('slot_id') // eslint-disable-line no-await-in-loop
        .in('slot_id', ids).neq('status', 'cancelled');
      const bookedIds = new Set((bk || []).map(b => b.slot_id));
      removable = toRemove.filter(s => !bookedIds.has(s.id));
      protectedCount += toRemove.length - removable.length;
    }

    if (times.length) await publishDayTimes({ courseId, dateStr, times, price, includesCart }); // eslint-disable-line no-await-in-loop
    for (const s of removable) await deleteSlot(s.id); // eslint-disable-line no-await-in-loop
    daysChanged++;
  }
  return { daysChanged, protectedCount };
}

// All tee slots on one calendar day (local), soonest first.
function useDaySlots(courseId, dateStr) {
  const [rows, setRows] = React.useState(null);
  const load = React.useCallback(async () => {
    if (!courseId || !dateStr) { setRows([]); return; }
    const start = new Date(`${dateStr}T00:00:00`);
    const end = new Date(start.getTime() + 864e5);
    const { data } = await sbx.from('tee_slots').select('*')
      .eq('course_id', courseId)
      .gte('starts_at', start.toISOString())
      .lt('starts_at', end.toISOString())
      .order('starts_at');
    setRows(data || []);
  }, [courseId, dateStr]);
  // Drop the old day's rows the INSTANT the date changes, before the new
  // fetch lands. Holding them was not just a flash of stale times: the tee
  // sheet's window-reconstruction runs off `slots` and only runs once per
  // date, so it would rebuild the new day's windows from the PREVIOUS day's
  // slots, mark itself done, and then skip the real data when it arrived —
  // leaving yesterday's shape stuck on today. Going to null makes that
  // effect wait, because it already guards on null.
  //
  // Only on a key change, not inside load(), so a manual reload after a
  // save doesn't blank the grid.
  React.useEffect(() => { setRows(null); }, [courseId, dateStr]);
  React.useEffect(() => { load(); }, [load]);
  return [rows, load];
}

// The signed-up field per tee time on a day → { slot_id: [players] }.
// A player = { id, first_name, last_name, handle, avatar_url, status }.
// Realtime on bookings so faces appear/disappear as golfers sign up live.
function useDayFields(courseId, dateStr) {
  const [bySlot, setBySlot] = React.useState({});
  const load = React.useCallback(async () => {
    if (!courseId || !dateStr) { setBySlot({}); return; }
    const start = new Date(`${dateStr}T00:00:00`);
    const end = new Date(start.getTime() + 864e5);
    const { data: slots } = await sbx.from('tee_slots').select('id')
      .eq('course_id', courseId)
      .gte('starts_at', start.toISOString()).lt('starts_at', end.toISOString());
    const ids = (slots || []).map(s => s.id);
    if (!ids.length) { setBySlot({}); return; }
    // partner_id / match_id / tier are here for the tee-time preview: whether
    // two people booked TOGETHER or were seated together by the sift is a
    // different fact from "they share a tee time", and it changes whether
    // splitting them up is rude or routine.
    const { data: bk } = await sbx.from('bookings')
      .select('slot_id, status, created_at, partner_id, match_id, match_type, needs_partner, user:profiles!bookings_user_id_fkey(id, first_name, last_name, handle, avatar_url, tier)')
      .in('slot_id', ids).neq('status', 'cancelled')
      .order('created_at');
    // Who arrived together, from the golfer app's own party record
    // (slot_booking_parties, 2026-08-26). This is the ONLY reliable signal:
    // match_type describes the FORMAT a group ends up playing, not who
    // booked with whom, and the two disagree in both directions.
    //
    // Only locked parties carry a slot_id, so a party that hasn't locked yet
    // simply isn't here — absence means "not known", never "booked alone".
    // A party of 2 or 3 is as real as a foursome; party_size counts filled
    // seats only, so an empty seat never inflates it.
    const partyOf = {};
    const { data: parties, error: partyErr } = await sbx.from('slot_booking_parties')
      .select('slot_id, party_id, user_id, party_size').in('slot_id', ids);
    if (partyErr) {
      // View missing or not readable — degrade to "unknown" rather than
      // silently claiming everyone booked alone.
      // eslint-disable-next-line no-console
      console.warn('slot_booking_parties unreadable', partyErr.message);
    }
    (parties || []).forEach(r => { partyOf[`${r.slot_id}|${r.user_id}`] = r; });

    const m = {};
    (bk || []).forEach(b => {
      if (!b.user) return;
      const party = partyOf[`${b.slot_id}|${b.user.id}`] || null;
      (m[b.slot_id] = m[b.slot_id] || []).push({
        ...b.user,
        status: b.status,
        partnerId: b.partner_id || null,
        matchId: b.match_id || null,
        matchType: b.match_type || null,
        needsPartner: b.needs_partner === true,
        partyId: party ? party.party_id : null,
        partySize: party ? Number(party.party_size) : null,
        bookedAt: b.created_at || null,
      });
    });
    setBySlot(m);
  }, [courseId, dateStr]);

  // Same reason as useDaySlots: don't leave the previous day's golfers
  // attached to this day's tee times while the new read is in flight.
  React.useEffect(() => { setBySlot({}); }, [courseId, dateStr]);
  React.useEffect(() => { load(); }, [load]);
  React.useEffect(() => {
    if (!courseId || !dateStr) return;
    const ch = sbx.channel(`fields-${courseId}-${dateStr}`)
      .on('postgres_changes', { event: '*', schema: 'public', table: 'bookings' }, () => load())
      .subscribe();
    return () => { sbx.removeChannel(ch); };
  }, [courseId, dateStr, load]);

  return [bySlot, load];
}

// ─── What actually happened on a tee time ────────────────────────────────────
// For times already played: when the group teed off, when they finished, how
// long they took, and how each board ended.
//
// Kept separate from useDayFields rather than folded into it — that hook's
// shape (slotId → players[]) is read in several places, and this is only
// needed for times in the past. A 1v1v1 is three match rows sharing one
// session_id, so boards are grouped by session; otherwise a trio reads as
// three unrelated results.
//   → { [slotId]: { startedAt, completedAt, durationMin, boards: [...] } }
function useDayPlay(courseId, dateStr) {
  const [bySlot, setBySlot] = React.useState({});
  const load = React.useCallback(async () => {
    if (!courseId || !dateStr) { setBySlot({}); return; }
    const start = new Date(`${dateStr}T00:00:00`);
    const end = new Date(start.getTime() + 864e5);
    const { data: slots } = await sbx.from('tee_slots').select('id')
      .eq('course_id', courseId)
      .gte('starts_at', start.toISOString()).lt('starts_at', end.toISOString());
    const ids = (slots || []).map(s => s.id);
    if (!ids.length) { setBySlot({}); return; }

    const { data: bk } = await sbx.from('bookings')
      .select('slot_id, match_id').in('slot_id', ids)
      .not('match_id', 'is', null).neq('status', 'cancelled');
    const matchIds = [...new Set((bk || []).map(b => b.match_id).filter(Boolean))];
    if (!matchIds.length) { setBySlot({}); return; }

    const { data: ms } = await sbx.from('matches')
      .select('id, status, result, final_margin, total_holes, session_id, started_at, completed_at, player_a, player_a2, player_b, player_b2')
      .in('id', matchIds);
    const byId = {}; (ms || []).forEach(m => { byId[m.id] = m; });

    const out = {};
    (bk || []).forEach(b => {
      const m = byId[b.match_id];
      if (!m) return;
      const rec = out[b.slot_id] = out[b.slot_id] || { boards: [], seen: new Set() };
      // One entry per session (or per match when it has none), so the two
      // bookings pointing at the same board don't list it twice.
      const key = m.session_id || m.id;
      if (rec.seen.has(key)) return;
      rec.seen.add(key);
      rec.boards.push(m);
    });

    Object.values(out).forEach(rec => {
      delete rec.seen;
      const starts = rec.boards.map(m => m.started_at).filter(Boolean).map(x => new Date(x).getTime());
      const ends = rec.boards.map(m => m.completed_at).filter(Boolean).map(x => new Date(x).getTime());
      rec.startedAt = starts.length ? new Date(Math.min(...starts)).toISOString() : null;
      // Only call it finished when every board is in — one board still open
      // means the group hasn't actually walked off.
      rec.completedAt = (ends.length && ends.length === rec.boards.length)
        ? new Date(Math.max(...ends)).toISOString() : null;
      rec.durationMin = (rec.startedAt && rec.completedAt)
        ? Math.round((new Date(rec.completedAt) - new Date(rec.startedAt)) / 60000) : null;
    });
    setBySlot(out);
  }, [courseId, dateStr]);

  React.useEffect(() => { setBySlot({}); }, [courseId, dateStr]);
  React.useEffect(() => { load(); }, [load]);
  React.useEffect(() => {
    if (!courseId || !dateStr) return undefined;
    // Rounds finish while a manager has this open; a minute is plenty.
    const iv = setInterval(load, 60000);
    return () => clearInterval(iv);
  }, [courseId, dateStr, load]);

  return [bySlot, load];
}

// Historical fill rate for this course → drives the live revenue projection.
//   Looks at past slots (last 90d) and what share of their seats got booked.
//   { rate: 0..1|null, seats, booked, sampleSlots }
function useCourseFillRate(courseId) {
  const [info, setInfo] = React.useState(null);
  const load = React.useCallback(async () => {
    if (!courseId) { setInfo(null); return; }
    const since = new Date(Date.now() - 90 * 864e5).toISOString();
    const nowISO = new Date().toISOString();
    const { data: slots } = await sbx.from('tee_slots')
      .select('id, capacity').eq('course_id', courseId)
      .gte('starts_at', since).lt('starts_at', nowISO);
    const ids = (slots || []).map(s => s.id);
    let booked = 0;
    if (ids.length) {
      const { data: bk } = await sbx.from('bookings').select('status').in('slot_id', ids);
      booked = (bk || []).filter(b => b.status !== 'cancelled' && b.status !== 'no_show').length;
    }
    const seats = (slots || []).reduce((s, x) => s + (x.capacity || 4), 0);
    setInfo({ rate: seats ? Math.min(1, booked / seats) : null, seats, booked, sampleSlots: (slots || []).length });
  }, [courseId]);
  React.useEffect(() => { load(); }, [load]);
  return [info, load];
}

function slotError(error) {
  const msg = (error && error.message) || 'Could not save the tee time.';
  if (error && error.code === '23505') return new Error('A slot already exists at that time.');
  if (/row-level security|permission/i.test(msg)) return new Error('Not allowed — you can only manage your own course.');
  return new Error(msg);
}

// ─── Daily yardages: base layout merged with a date's overrides ─────────────
//   Returns [{ hole_number, par, base_yards, yards, override }] for 9 holes.
function useDailyYardages(courseId, dateStr) {
  const [holes, setHoles] = React.useState(null);
  const load = React.useCallback(async () => {
    if (!courseId || !dateStr) { setHoles([]); return; }
    const [{ data: base }, { data: day }] = await Promise.all([
      sbx.from('course_holes').select('hole_number, par, sandbox_yards, quadrant_yards').eq('course_id', courseId).order('hole_number'),
      sbx.from('course_hole_days').select('hole_number, sandbox_yards, quadrant').eq('course_id', courseId).eq('play_date', dateStr),
    ]);
    const byHole = {};
    (day || []).forEach(d => { byHole[d.hole_number] = d; });
    setHoles((base || []).map(h => {
      const d = byHole[h.hole_number];
      return {
        hole_number: h.hole_number,
        par: h.par,
        base_yards: h.sandbox_yards,
        yards: d ? d.sandbox_yards : h.sandbox_yards,
        override: !!d,
        quadrants: Array.isArray(h.quadrant_yards) ? h.quadrant_yards : null,
        selectedQuadrant: (d && d.quadrant != null) ? d.quadrant : null,
      };
    }));
  }, [courseId, dateStr]);
  React.useEffect(() => { load(); }, [load]);
  return [holes, load];
}

// Save a day's yardages (array of { hole_number, yards }). Upserts overrides.
async function saveDailyYardages(courseId, dateStr, holes) {
  const rows = holes.map(h => ({
    course_id: courseId,
    play_date: dateStr,
    hole_number: Number(h.hole_number),
    sandbox_yards: (h.yards === '' || h.yards == null) ? null : Number(h.yards),
    quadrant: null,
    updated_at: new Date().toISOString(),
  }));
  const { error } = await sbx.from('course_hole_days')
    .upsert(rows, { onConflict: 'course_id,play_date,hole_number' });
  if (error) {
    if (/row-level security|permission/i.test(error.message || '')) throw new Error('Not allowed — you can only manage your own course.');
    throw new Error(error.message || 'Could not save yardages.');
  }
}

// Save a hole's measured 3×3 pin quadrants (array of 9 ints/nulls) onto
// course_holes — the pre-measured distances the pin-placement flow reads.
async function saveHoleQuadrants(courseId, holeNumber, quadrants) {
  const { error } = await sbx.from('course_holes')
    .update({ quadrant_yards: quadrants })
    .eq('course_id', courseId).eq('hole_number', Number(holeNumber));
  if (error) {
    if (/row-level security|permission/i.test(error.message || '')) throw new Error('Not allowed — you can only manage your own course.');
    if (/column .*quadrant_yards/i.test(error.message || '')) throw new Error('Run the hole-quadrants migration first (v1/sql/hole-quadrants.sql).');
    throw new Error(error.message || 'Could not save quadrants.');
  }
}

// Admin: create-or-update a hole's row with its measured quadrants. Unlike
// saveHoleQuadrants (update-only) this UPSERTS, so the admin can fill in a
// course's back-nine holes (10-18) that don't exist yet.
async function upsertHoleQuadrants(courseId, holeNumber, par, quadrants) {
  const { error } = await sbx.from('course_holes').upsert({
    course_id: courseId, hole_number: Number(holeNumber), par: Number(par) || 3, quadrant_yards: quadrants,
  }, { onConflict: 'course_id,hole_number' });
  if (error) {
    if (/row-level security|permission/i.test(error.message || '')) throw new Error('Admins only.');
    if (/column .*quadrant_yards/i.test(error.message || '')) throw new Error('Run the hole-quadrants migration first (v1/sql/hole-quadrants.sql).');
    throw new Error(error.message || 'Could not save quadrants.');
  }
}

// Set today's pin for one hole by quadrant — resolves to that quadrant's
// measured yardage (also stored, so the app path is identical to manual).
async function setDailyPin(courseId, dateStr, holeNumber, quadrant, yards) {
  const { error } = await sbx.from('course_hole_days').upsert({
    course_id: courseId, play_date: dateStr, hole_number: Number(holeNumber),
    sandbox_yards: (yards === '' || yards == null) ? null : Number(yards),
    quadrant: quadrant == null ? null : Number(quadrant),
    updated_at: new Date().toISOString(),
  }, { onConflict: 'course_id,play_date,hole_number' });
  if (error) throw new Error(error.message || 'Could not set the pin.');
}

// Reset a day back to the base layout (delete its overrides).
async function clearDailyYardages(courseId, dateStr) {
  const { error } = await sbx.from('course_hole_days').delete()
    .eq('course_id', courseId).eq('play_date', dateStr);
  if (error) throw new Error(error.message || 'Could not reset.');
}

// ─── Live on course: active matches + per-group current hole ─────────────────
//   Returns [{ id, match_type, players:[names], holesDone, currentHole, total,
//              status, startedAt, leader }]. Subscribes to realtime so it moves
//   as golfers score. One row per physical group on the course — a 1v1v1's
//   three pairwise match rows (A-vs-B, A-vs-C, B-vs-C) collapse into one
//   group via session_id, so `players` can hold 2 or 3 people and `leader`
//   is null for a trio (no combined-standing math here yet).
function useLiveOnCourse(courseId) {
  const [groups, setGroups] = React.useState(null);
  const load = React.useCallback(async () => {
    if (!courseId) { setGroups([]); return; }
    // This course's bookings that belong to a match — filtered SERVER-SIDE by the
    // slot's course (inner join). Avoids slot-id / match-id IN-lists that overflow
    // the request URL, and the client-side course check that silently failed when
    // the tee_slots embed came back as an array.
    const since = new Date(Date.now() - 12 * 3600e3).toISOString();
    const cols = 'id, match_type, status, started_at, completed_at, total_holes, player_a, player_a2, player_b, player_b2, session_id';
    // Active matches ALWAYS; recent completed for the Completed section.
    const [{ data: act }, { data: comp }] = await Promise.all([
      sbx.from('matches').select(cols).eq('status', 'active'),
      sbx.from('matches').select(cols).eq('status', 'completed').gte('started_at', since),
    ]);
    const recent = [...(act || []), ...(comp || [])];
    const recentIds = recent.map(m => m.id);
    if (!recentIds.length) { setGroups([]); return; }
    // Keep the ones whose booking slot is on THIS course. The tee_slots embed can
    // come back as an object OR an array — handle both (that was the bug).
    const { data: bks } = await sbx.from('bookings')
      .select('match_id, slot_id, tee_slots(course_id, starts_at)')
      .in('match_id', recentIds);
    const slotOf = {}; const here = new Set();
    (bks || []).forEach(b => {
      const slot = Array.isArray(b.tee_slots) ? b.tee_slots[0] : b.tee_slots;
      if (b.match_id && slot && slot.course_id === courseId) {
        here.add(b.match_id);
        if (!slotOf[b.match_id]) slotOf[b.match_id] = { slotId: b.slot_id, teeISO: slot.starts_at };
      }
    });
    // A 1v1v1 trio is three match rows sharing one session_id, everyone-plays-
    // everyone — but bookings.match_id only points at two of the three boards
    // (A-vs-B gets two bookings, A-vs-C one, B-vs-C none). Once any sibling is
    // confirmed at this course, pull the rest of its session in too, or the
    // third board — and the third player — silently vanishes from the group.
    const recentById = {}; recent.forEach(m => { recentById[m.id] = m; });
    const sessionsHere = new Set();
    here.forEach(id => { const m = recentById[id]; if (m && m.session_id) sessionsHere.add(m.session_id); });
    recent.forEach(m => { if (m.session_id && sessionsHere.has(m.session_id)) here.add(m.id); });

    const ms = recent.filter(m => here.has(m.id)).sort((x, y) => new Date(x.started_at) - new Date(y.started_at));
    if (!ms.length) { setGroups([]); return; }

    // Player names + avatars.
    const ids = [...new Set(ms.flatMap(m => [m.player_a, m.player_a2, m.player_b, m.player_b2]).filter(Boolean))];
    const { data: profs } = await sbx.from('profiles').select('id, first_name, last_name, handle, avatar_url, sbx').in('id', ids);
    const profOf = {};
    (profs || []).forEach(p => { profOf[p.id] = p; });
    const nameOf = (id) => {
      const p = profOf[id];
      if (!p) return 'Player';
      return [p.first_name, p.last_name].filter(Boolean).join(' ')
        || (p.handle ? '@' + String(p.handle).replace(/^@/, '') : 'Player');
    };
    const playerOf = (id) => {
      const p = profOf[id] || {};
      const a = (p.first_name || '').trim(), b = (p.last_name || '').trim();
      return {
        id,
        first_name: p.first_name, last_name: p.last_name,
        name: a ? `${a[0]}. ${b || ''}`.trim() : nameOf(id),
        initials: (a || b) ? ((a[0] || '') + (b[0] || '')).toUpperCase() : ((p.handle || '?').replace(/^@/, '')[0] || '?').toUpperCase(),
        avatar_url: p.avatar_url || null,
        sbx: p.sbx != null ? Number(p.sbx) : null,
        member: true, // membership isn't in the DB yet — all live accounts are members
      };
    };
    // Per-match hole progress + the live stage of the hole in play, read
    // straight from the players' shot logs (what the golfer app tracks):
    //   no log yet → walking up ("tee") · shot events logged → "fairway"
    //   · anyone on the green / putting → "green".
    const { data: holes } = await sbx.from('match_holes')
      .select('match_id, hole_number, result, shot_log_a, shot_log_b')
      .in('match_id', ms.map(m => m.id));
    const prog = {};
    (holes || []).forEach(h => {
      const p = prog[h.match_id] || { done: 0, a: 0, b: 0, byHole: {} };
      if (h.result) {
        p.done += 1;
        if (h.result === 'A') p.a += 1;
        else if (h.result === 'B') p.b += 1;
      }
      p.byHole[h.hole_number] = h;
      prog[h.match_id] = p;
    });
    const stageOfHole = (h) => {
      if (!h) return 'tee';
      const log = [...(h.shot_log_a || []), ...(h.shot_log_b || [])];
      if (!log.length) return 'tee';
      const onGreen = log.some(ev => ev.phase === 'putt'
        || Object.values(ev.outcomes || {}).some(o => o && (o.reached === true || o.reached === 'holed')));
      return onGreen ? 'green' : 'fairway';
    };

    // Group by session — a 1v1/2v2 match is its own group of one; a 1v1v1
    // trio's three pairwise boards (A-vs-B, A-vs-C, B-vs-C) collapse into the
    // single physical group of golfers actually walking the course together.
    const bySession = {};
    ms.forEach(m => { (bySession[m.session_id || m.id] = bySession[m.session_id || m.id] || []).push(m); });

    setGroups(Object.values(bySession).map(rows => {
      const primary = rows.find(r => r.status === 'active')
        || rows.slice().sort((a, b) => new Date(a.started_at) - new Date(b.started_at))[0];
      const total = primary.total_holes || 9;

      // Every player across every board in the group, deduped — for a trio
      // this recovers the third player, who has no board of their own in
      // `here` unless a sibling match pulled it in above.
      const seen = new Set(); const playerIds = [];
      rows.forEach(m => [m.player_a, m.player_a2, m.player_b, m.player_b2].filter(Boolean).forEach(id => {
        if (!seen.has(id)) { seen.add(id); playerIds.push(id); }
      }));

      // The group plays one shared physical round — take whichever board's
      // logged the most holes rather than trusting any single one, so a
      // board that's lagging its own writes can't hide the group's real
      // position.
      let done = 0, doneRowId = rows[0].id;
      rows.forEach(m => { const p = prog[m.id]; if (p && p.done > done) { done = p.done; doneRowId = m.id; } });
      const currentHole = primary.status === 'active' ? Math.min(done + 1, total) : null;
      const link = rows.map(m => slotOf[m.id]).find(Boolean) || {};

      // A head-to-head "leader" line only makes sense for a single pairwise
      // board. A trio's combined standing across two boards each isn't
      // computed here — better silent than wrong on a live manager board.
      let leader = null;
      if (rows.length === 1) {
        const p = prog[primary.id] || { done: 0, a: 0, b: 0 };
        const teamAIds = [primary.player_a, primary.player_a2].filter(Boolean);
        const teamBIds = [primary.player_b, primary.player_b2].filter(Boolean);
        const diff = p.a - p.b;
        leader = primary.status === 'active'
          ? (diff === 0 ? 'All square' : `${diff > 0 ? nameOf(teamAIds[0]) : nameOf(teamBIds[0])} ${Math.abs(diff)} up`)
          : null;
      }

      const status = rows.some(r => r.status === 'active') ? 'active'
        : rows.every(r => r.status === 'completed') ? 'completed' : primary.status;

      return {
        id: primary.session_id || primary.id,
        match_type: rows.length > 1 ? '1v1v1' : primary.match_type,
        status,
        startedAt: primary.started_at,
        completedAt: rows.every(r => r.completed_at) ? rows.map(r => r.completed_at).sort().slice(-1)[0] : null,
        players: playerIds.map(playerOf),
        slotId: link.slotId || null,
        teeISO: link.teeISO || primary.started_at,
        holesDone: done,
        currentHole,
        stage: currentHole ? stageOfHole(prog[doneRowId] && prog[doneRowId].byHole[currentHole]) : 'tee',
        total,
        leader,
      };
    }));
  }, [courseId]);

  React.useEffect(() => { load(); }, [load]);

  // Realtime + 30s backstop so the board stays fresh without manual refresh.
  React.useEffect(() => {
    if (!courseId) return;
    const ch = sbx.channel(`live-${courseId}`)
      .on('postgres_changes', { event: '*', schema: 'public', table: 'match_holes' }, () => load())
      .on('postgres_changes', { event: '*', schema: 'public', table: 'matches' }, () => load())
      .subscribe();
    const iv = setInterval(load, 30000);
    return () => { sbx.removeChannel(ch); clearInterval(iv); };
  }, [courseId, load]);

  return [groups, load];
}

// ─── Average round completion time for a course ──────────────────────────────
//   From completed SBX matches' started_at → completed_at (the data is already
//   stored on every match). Throws out nonsense samples (<5 min or >5 h, e.g.
//   rounds finished the next day). → { avgMin, rounds } | null while loading.
function useAvgRoundTime(courseId) {
  const [data, setData] = React.useState(null);
  React.useEffect(() => {
    if (!courseId) { setData({ avgMin: null, rounds: 0 }); return undefined; }
    let on = true;
    (async () => {
      // matches.course_id isn't set by the window matcher — resolve this
      // course's matches through their bookings' slots instead.
      const { data: cSlots } = await sbx.from('tee_slots').select('id').eq('course_id', courseId);
      const cSlotIds = (cSlots || []).map(s => s.id);
      if (!cSlotIds.length) { if (on) setData({ avgMin: null, rounds: 0 }); return; }
      const { data: cBks } = await sbx.from('bookings').select('match_id').in('slot_id', cSlotIds).not('match_id', 'is', null);
      const matchIds = [...new Set((cBks || []).map(b => b.match_id).filter(Boolean))];
      if (!matchIds.length) { if (on) setData({ avgMin: null, rounds: 0 }); return; }
      const { data: ms } = await sbx.from('matches')
        .select('started_at, completed_at')
        .in('id', matchIds).eq('status', 'completed')
        .not('started_at', 'is', null).not('completed_at', 'is', null)
        .order('completed_at', { ascending: false }).limit(200);
      const mins = (ms || [])
        .map(m => (new Date(m.completed_at) - new Date(m.started_at)) / 60000)
        .filter(x => x >= 5 && x <= 300);
      if (!on) return;
      setData({
        avgMin: mins.length ? Math.round(mins.reduce((s, x) => s + x, 0) / mins.length) : null,
        rounds: mins.length,
      });
    })();
    return () => { on = false; };
  }, [courseId]);
  return data;
}

// ─── Coming soon: today's booked tee times that haven't started playing ─────
//   [{ slotId, teeISO, players: [{id, name, initials, avatar_url, sbx, member}] }]
//   Derived from BOOKINGS (single source of truth; tee_assignments retired).
function useComingSoon(courseId) {
  const [rows, setRows] = React.useState(null);
  const load = React.useCallback(async () => {
    if (!courseId) { setRows([]); return; }
    // All of today's booked groups that haven't started. A group whose tee time
    // already passed but hasn't teed off is still "coming up" (late); started /
    // finished ones are removed by the board's busySlots filter.
    const fromD = new Date(); fromD.setHours(0, 0, 0, 0);
    const from = fromD.toISOString();
    const to = new Date(); to.setHours(23, 59, 59, 999);
    const toISO = to.toISOString();

    const { data: slots } = await sbx.from('tee_slots').select('id, starts_at')
      .eq('course_id', courseId)
      .gte('starts_at', from).lte('starts_at', toISO)
      .order('starts_at');
    const ids = (slots || []).map(s => s.id);
    if (!ids.length) { setRows([]); return; }
    const slotStart = {}; (slots || []).forEach(s => { slotStart[s.id] = s.starts_at; });

    const { data: bk } = await sbx.from('bookings')
      .select('slot_id, created_at, user:profiles!bookings_user_id_fkey(id, first_name, last_name, handle, avatar_url, sbx)')
      .in('slot_id', ids).in('status', ['reserved', 'booked', 'checked_in'])
      .order('created_at');
    const bySlot = {};
    (bk || []).forEach(b => {
      if (!b.user) return;
      const p = b.user;
      const a = (p.first_name || '').trim(), l = (p.last_name || '').trim();
      const player = {
        id: p.id,
        name: a ? `${a[0]}. ${l || ''}`.trim() : (p.handle ? '@' + String(p.handle).replace(/^@/, '') : 'Player'),
        initials: (a || l) ? ((a[0] || '') + (l[0] || '')).toUpperCase() : ((p.handle || '?').replace(/^@/, '')[0] || '?').toUpperCase(),
        avatar_url: p.avatar_url || null,
        sbx: p.sbx != null ? Number(p.sbx) : null,
        member: true, // membership isn't in the DB yet
      };
      const cur = bySlot[b.slot_id] = bySlot[b.slot_id] || { teeISO: slotStart[b.slot_id], players: [] };
      if (cur.players.length < 4) cur.players.push(player);
    });

    setRows(Object.keys(bySlot)
      .map(k => ({ slotId: k, teeISO: bySlot[k].teeISO, players: bySlot[k].players }))
      .filter(r => r.players.length && r.teeISO)
      .sort((x, y) => new Date(x.teeISO) - new Date(y.teeISO)));
  }, [courseId]);

  React.useEffect(() => { load(); }, [load]);
  React.useEffect(() => {
    if (!courseId) return;
    // bookings isn't realtime-published; matches is, so a new foursome pings us
    // instantly and the 30s poll catches raw booking changes.
    const ch = sbx.channel(`coming-${courseId}`)
      .on('postgres_changes', { event: '*', schema: 'public', table: 'matches' }, () => load())
      .subscribe();
    const iv = setInterval(load, 30000);
    return () => { sbx.removeChannel(ch); clearInterval(iv); };
  }, [courseId, load]);

  return [rows, load];
}

// ─── Waitlist demand for a course + date ─────────────────────────────────────
//   Every golfer's requested tee-off window ("I can play 5–8pm") for the day.
//   → null while loading, else [{ id, startMin, endMin, priority, createdAt,
//     provisionalSlotId, bookedElsewhere, user: { name, initials, avatar_url } }].
//   Realtime on tee_waitlist.
function useCourseWaitlist(courseId, dateStr) {
  const [rows, setRows] = React.useState(null);
  const load = React.useCallback(async () => {
    if (!courseId || !dateStr) { setRows([]); return; }
    // Names come from a second, explicit read rather than an embed.
    //
    // This used to be `profiles!tee_waitlist_user_id_fkey(...)`, which only
    // resolves if that foreign key is named exactly that. When it isn't,
    // PostgREST fails the whole query and the line below turns that into an
    // empty list — so a demand panel with four people waiting on it renders
    // blank, with nothing anywhere saying why. useStaff already avoids named
    // embeds for this reason; this one had been missed.
    const { data, error } = await sbx.from('tee_waitlist')
      .select('id, user_id, start_min, end_min, created_at, provisional_slot_id')
      .eq('course_id', courseId).eq('play_date', dateStr)
      .order('created_at');
    if (error) {
      // Genuinely unreadable now means something worth seeing, not silence.
      // eslint-disable-next-line no-console
      console.warn('waitlist read failed', error.message);
      setRows([]);
      return;
    }

    const ids = [...new Set((data || []).map(r => r.user_id).filter(Boolean))];
    const people = {};
    if (ids.length) {
      const { data: profs } = await sbx.from('profiles')
        .select('id, first_name, last_name, handle, avatar_url, sbx, tier').in('id', ids);
      (profs || []).forEach(pr => { people[pr.id] = pr; });
    }

    // Who already has a real seat at some OTHER course today — sandbox_sift
    // excludes these people before it ever pencils anyone in, so a row still
    // sitting here for one of them is dead, not "still looking." Gated RPC
    // (is_admin() or manages_course(p_course_id)), added in PR #299 on
    // danielmanzii/sandbox — not yet applied everywhere, so a missing-function
    // error here means "not deployed yet," not a real failure: treat it as
    // "nobody flagged" rather than surfacing it.
    let elsewhere = new Set();
    if (ids.length) {
      const { data: ew, error: ewError } = await sbx.rpc('golfers_booked_elsewhere', {
        p_user_ids: ids, p_play_date: dateStr, p_course_id: courseId,
      });
      if (!ewError) elsewhere = new Set((ew || []).map(row => row.user_id));
    }

    setRows((data || []).map(r => {
      const p = people[r.user_id] || {};
      const a = (p.first_name || '').trim(), l = (p.last_name || '').trim();
      return {
        id: r.id,
        startMin: r.start_min, endMin: r.end_min,
        priority: p.tier === 'plus', // real now — sandbox_sift seats members first
        sbx: p.sbx != null ? Number(p.sbx) : null, // drives SBX-banded foursome fill
        createdAt: new Date(r.created_at).getTime(),
        // Ground truth from the real engine, not a guess: sandbox_sift already
        // pencilled this person into a slot if it's set. Null just means the
        // sift hasn't found them a home yet — it runs on the half hour and can
        // still add, move, or displace people until sandbox_lock finalizes
        // things at T-1h.
        provisionalSlotId: r.provisional_slot_id || null,
        // Distinct from provisionalSlotId on purpose: pencilled means the
        // sift is actively working this row and it can still move. This
        // means the row is dead — the golfer already has a seat elsewhere
        // and this request should be retired, not shown as waiting.
        bookedElsewhere: elsewhere.has(r.user_id),
        user: {
          id: p.id || null,          // real profile id → drives app-side assignments
          handle: p.handle || null,  // → clickable profile on the golfer app
          name: a ? `${a[0]}. ${l || ''}`.trim() : (p.handle ? '@' + String(p.handle).replace(/^@/, '') : 'Player'),
          initials: (a || l) ? ((a[0] || '') + (l[0] || '')).toUpperCase() : ((p.handle || '?').replace(/^@/, '')[0] || '?').toUpperCase(),
          avatar_url: p.avatar_url || null,
        },
      };
    }));
  }, [courseId, dateStr]);

  React.useEffect(() => { load(); }, [load]);
  React.useEffect(() => {
    if (!courseId) return;
    const ch = sbx.channel(`waitlist-${courseId}-${dateStr}`)
      .on('postgres_changes', { event: '*', schema: 'public', table: 'tee_waitlist' }, () => load())
      .subscribe();
    const iv = setInterval(load, 30000);
    return () => { sbx.removeChannel(ch); clearInterval(iv); };
  }, [courseId, dateStr, load]);

  return [rows, load];
}

// ─── Tee assignments: push the board's booked foursomes to the golfers ──────
// One row per REAL golfer per course per day (ghosts ride along in group_json
// only). Diffed, not blindly rewritten:
//   new row            → status 'booked'      (app: "You're in!")
//   slot changed       → status 'rescheduled' (app: "Your tee time moved")
//   fell out of a four → status 'cut'         (app: "Didn't make the cut")
//   clearTeeAssignments (demo wipe) → rows deleted, app card just disappears.
async function syncTeeAssignments({ courseId, dateStr, desired }) {
  const { data: existing, error } = await sbx.from('tee_assignments')
    .select('id, user_id, slot_id, status, group_json')
    .eq('course_id', courseId).eq('play_date', dateStr);
  if (error) return; // table not migrated yet — sync is a no-op
  const exByUser = {};
  (existing || []).forEach(r => { exByUser[r.user_id] = r; });
  const desByUser = {};
  (desired || []).forEach(d => { desByUser[d.userId] = d; });
  const nowISO = new Date().toISOString();

  for (const userId in desByUser) {
    const d = desByUser[userId];
    const ex = exByUser[userId];
    if (!ex) {
      await sbx.from('tee_assignments').insert({ // eslint-disable-line no-await-in-loop
        user_id: userId, course_id: courseId, slot_id: d.slotId,
        play_date: dateStr, tee_time: d.teeISO, status: 'booked',
        group_json: d.group, updated_at: nowISO,
      });
    } else if (ex.slot_id !== d.slotId) {
      await sbx.from('tee_assignments').update({ // eslint-disable-line no-await-in-loop
        slot_id: d.slotId, tee_time: d.teeISO, status: 'rescheduled',
        group_json: d.group, updated_at: nowISO,
      }).eq('id', ex.id);
    } else if (ex.status === 'cut' || JSON.stringify(ex.group_json) !== JSON.stringify(d.group)) {
      await sbx.from('tee_assignments').update({ // eslint-disable-line no-await-in-loop
        status: ex.status === 'cut' ? 'booked' : ex.status,
        group_json: d.group, updated_at: nowISO,
      }).eq('id', ex.id);
    }
  }
  for (const r of (existing || [])) {
    if (!desByUser[r.user_id] && r.status !== 'cut') {
      await sbx.from('tee_assignments').update({ status: 'cut', updated_at: nowISO }).eq('id', r.id); // eslint-disable-line no-await-in-loop
    }
  }
}

// Demo wipe: remove the day's assignments outright (no 'cut' notifications).
async function clearTeeAssignments(courseId, dateStr) {
  try {
    await sbx.from('tee_assignments').delete()
      .eq('course_id', courseId).eq('play_date', dateStr);
  } catch (_) { /* table may not exist yet */ }
}

// Bump: move a booked slot's whole foursome to the next open tee time.
// Server-side reschedule (reschedule_foursome RPC) so it moves the golfers'
// real bookings + assignments atomically. Returns the new slot id, or null if
// the slot had no foursome to move (e.g. it was never really booked).
// Returns the new slot id when the group moved, or null when there was no
// later time with room — in which case the golfer app cancels those
// bookings and notifies the players (cases C/D). It no longer raises for
// that case, so a null result is a real outcome, not a failure.
//
// p_reason rides along so the app can tell a manager closure apart from an
// engine reshuffle and pick the right notification.
async function rescheduleFoursome(slotId, reason = 'manager_override') {
  const { data, error } = await sbx.rpc('reschedule_foursome', { p_slot: slotId, p_reason: reason });
  if (error) throw new Error(error.message || 'Could not bump this tee time.');
  return data;
}

// ─── Financials: roll up bookings → revenue / take / net + fill metrics ──────
function useCourseFinancials(courseId, course) {
  const [data, setData] = React.useState(null);
  const load = React.useCallback(async () => {
    if (!courseId) { setData(null); return; }
    // Slots (capacity + price) and their bookings, last 30d → next 14d window.
    const from = new Date(Date.now() - 30 * 864e5).toISOString();
    const { data: slots } = await sbx.from('tee_slots')
      .select('id, starts_at, capacity, price, status').eq('course_id', courseId).gte('starts_at', from);
    const slotIds = (slots || []).map(s => s.id);
    let bookings = [];
    if (slotIds.length) {
      const { data: bk } = await sbx.from('bookings')
        .select('id, slot_id, status, price_charged, created_at').in('slot_id', slotIds);
      bookings = bk || [];
    }
    const live = bookings.filter(b => b.status !== 'cancelled' && b.status !== 'no_show');
    // price_charged is captured at check-in, not at booking — bookedAhead /
    // fillRate below still count every live reserved seat (that's occupancy,
    // not money), but revenue only counts a seat that's actually been
    // charged. A course full of unplayed reservations isn't revenue yet.
    const revenue = live.reduce((s, b) => s + (b.price_charged != null ? b.price_charged : 0), 0);
    const takePct = (course && course.sandbox_take_pct != null) ? course.sandbox_take_pct : 15;
    const sandboxTake = Math.round(revenue * takePct / 100);

    const now = Date.now();
    const upcoming = (slots || []).filter(s => new Date(s.starts_at).getTime() >= now && s.status === 'open');
    const capacityAhead = upcoming.reduce((s, x) => s + (x.capacity || 0), 0);
    const bookedAhead = live.filter(b => {
      const s = (slots || []).find(z => z.id === b.slot_id);
      return s && new Date(s.starts_at).getTime() >= now;
    }).length;

    setData({
      revenue,
      sandboxTake,
      courseNet: revenue - sandboxTake,
      takePct,
      bookingsCount: live.length,
      rounds: Math.round(live.length / 2), // ~2 golfers per match
      upcomingSlots: upcoming.length,
      capacityAhead,
      bookedAhead,
      fillRate: capacityAhead ? Math.round(bookedAhead / capacityAhead * 100) : 0,
    });
  }, [courseId, course]);
  React.useEffect(() => { load(); }, [load]);
  return [data, load];
}

Object.assign(window, {
  useManagedCourses, useManagerIds, addCourseManager, removeCourseManager, createManagerAccount,
  useCourseSlots, useDaySlots, useDayFields, useDayPlay, saveSlot, deleteSlot, publishDayTimes, applyScheduleAcrossDays, useCourseFillRate,
  useDailyYardages, saveDailyYardages, clearDailyYardages, saveHoleQuadrants, upsertHoleQuadrants, setDailyPin,
  useLiveOnCourse, useComingSoon, useAvgRoundTime, useCourseWaitlist, useCourseFinancials,
  syncTeeAssignments, clearTeeAssignments, rescheduleFoursome,
});