/* global React, sbx, minutesOfDay, TIME_WINDOWS, todayStr,
   useDemoMode, demoWindowDays, demoMonthlyTakes, demoBusinessDays, demoDemandCurve */
// Metrics data layer for the manager dashboard's business panels.
// Real mode reads existing tables only (no schema changes); every historical
// query pages through PostgREST's 1000-row cap and chunks .in() lists.
// Demo Mode swaps in the deterministic generators from demo-data.jsx and
// tags the result { demo: true } so panels can show the DEMO DATA chip.

const monthKeyOf = (d) => `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, '0')}`;
const MONTH_SHORT = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
const monthLabel = (key) => MONTH_SHORT[parseInt(key.slice(5), 10) - 1];
const isTwilight = (iso) => { const m = minutesOfDay(iso); return m >= 960 && m < 1200; };

// Page through a PostgREST query builder factory until a short page.
async function pageAll(makeQuery, pageSize = 1000) {
  const out = [];
  for (let i = 0; ; i += pageSize) {
    const { data, error } = await makeQuery().range(i, i + pageSize - 1);
    if (error) throw error;
    out.push(...(data || []));
    if (!data || data.length < pageSize) return out;
  }
}
const chunk = (arr, n = 200) => {
  const out = [];
  for (let i = 0; i < arr.length; i += n) out.push(arr.slice(i, i + n));
  return out;
};

// ─── useMoneyData — everything the money/business panels render ───────
// { revenue30, courseNet30, sandboxTake30, takePct, foundMoney12, trend30,
//   receipts, monthlyNet: {monthKey: yourNet$}, monthsOfHistory, demo }
// sandboxTake30/takePct are computed for internal math (courseNet30) only —
// the UI never displays Sandbox's cut, just the course's own net dollars.
function useMoneyData(courseId, course) {
  const demo = useDemoMode();
  const [real, setReal] = React.useState(null);
  const takePct = course && course.sandbox_take_pct != null ? course.sandbox_take_pct : 15;

  React.useEffect(() => {
    if (!courseId || demo) return undefined;
    let on = true;
    (async () => {
      try {
        const since = new Date(); since.setMonth(since.getMonth() - 13); since.setDate(1); since.setHours(0, 0, 0, 0);
        const slots = await pageAll(() => sbx.from('tee_slots')
          .select('id, starts_at, price')
          .eq('course_id', courseId).gte('starts_at', since.toISOString()).order('starts_at'));
        const slotById = {}; slots.forEach(s => { slotById[s.id] = s; });
        let bookings = [];
        for (const ids of chunk(slots.map(s => s.id))) {
          // eslint-disable-next-line no-await-in-loop
          const page = await pageAll(() => sbx.from('bookings')
            .select('id, slot_id, status, price_charged, created_at, user:profiles!bookings_user_id_fkey(first_name, last_name, avatar_url)')
            .in('slot_id', ids).order('created_at'));
          bookings = bookings.concat(page);
        }
        // Every figure below is money (revenue, take, receipts) — price_charged
        // is captured at check-in now, not at booking, so only a booking that
        // actually has a charge belongs here. The slot's list price used to
        // stand in for uncharged bookings, which counted unplayed reservations
        // as revenue and produced a "receipt" for money nobody was charged.
        const live = bookings.filter(b => b.status !== 'cancelled' && b.status !== 'no_show'
          && slotById[b.slot_id] && b.price_charged != null);
        const grossOf = (b) => b.price_charged;

        const now = Date.now();
        const d30 = now - 30 * 864e5;
        const d365 = now - 365 * 864e5;
        let revenue30 = 0, take30 = 0, found12 = 0;
        const monthlyRevenue = {};
        const trendByDay = {};
        for (const b of live) {
          const s = slotById[b.slot_id];
          const t = new Date(s.starts_at).getTime();
          const gross = grossOf(b);
          const take = Math.round(gross * takePct / 100);
          const mk = monthKeyOf(new Date(s.starts_at));
          monthlyRevenue[mk] = (monthlyRevenue[mk] || 0) + gross;
          if (t >= d30 && t <= now) {
            revenue30 += gross; take30 += take;
            const day = s.starts_at.slice(0, 10);
            trendByDay[day] = (trendByDay[day] || 0) + gross;
          }
          if (t >= d365 && t <= now && isTwilight(s.starts_at)) found12 += gross;
        }
        const monthlyNet = {};
        for (const mk in monthlyRevenue) monthlyNet[mk] = monthlyRevenue[mk] - Math.round(monthlyRevenue[mk] * takePct / 100);
        const trend30 = [];
        for (let i = 29; i >= 0; i--) {
          const day = new Date(now - i * 864e5).toISOString().slice(0, 10);
          trend30.push(trendByDay[day] || 0);
        }
        const receipts = live
          .filter(b => new Date(slotById[b.slot_id].starts_at).getTime() <= now)
          .sort((a, b2) => new Date(slotById[b2.slot_id].starts_at) - new Date(slotById[a.slot_id].starts_at))
          .slice(0, 30)
          .map(b => {
            const s = slotById[b.slot_id];
            const gross = grossOf(b);
            const take = Math.round(gross * takePct / 100);
            const u = b.user || {};
            const nm = [u.first_name, (u.last_name || '')[0] ? `${u.last_name[0]}.` : ''].filter(Boolean).join(' ') || 'Golfer';
            return { id: b.id, whenISO: s.starts_at, golfer: nm, gross, take, net: gross - take, twilight: isTwilight(s.starts_at) };
          });
        const monthsOfHistory = course && course.created_at
          ? Math.max(0, (now - new Date(course.created_at).getTime()) / (30.44 * 864e5))
          : 0;
        if (on) setReal({ revenue30, sandboxTake30: take30, courseNet30: revenue30 - take30, takePct, foundMoney12: found12, trend30, receipts, monthlyNet, monthsOfHistory, demo: false });
      } catch (_) {
        if (on) setReal({ revenue30: 0, sandboxTake30: 0, courseNet30: 0, takePct, foundMoney12: 0, trend30: [], receipts: [], monthlyNet: {}, monthsOfHistory: 0, demo: false });
      }
    })();
    return () => { on = false; };
  }, [courseId, demo, takePct]);

  return React.useMemo(() => {
    if (!demo) return real;
    // ── Demo: derive the same shape from the deterministic generators ──
    const useTake = takePct;
    const days = demoWindowDays(courseId);
    const last30 = days.filter(d => new Date(d.dayStr) >= new Date(Date.now() - 30 * 864e5));
    const revenue30 = last30.reduce((s, d) => s + d.revenue, 0);
    const take30 = Math.round(revenue30 * useTake / 100);
    const found12 = Math.round(days.filter(d => d.windowKey === 'twilight').reduce((s, d) => s + d.revenue, 0) * (365 / 90));
    const byDay = {};
    last30.forEach(d => { byDay[d.dayStr] = (byDay[d.dayStr] || 0) + d.revenue; });
    const trend30 = Object.keys(byDay).sort().map(k => byDay[k]);
    const months = demoMonthlyTakes(courseId);
    const monthlyNet = {};
    months.forEach(m => { monthlyNet[m.monthKey] = m.revenue - Math.round(m.revenue * useTake / 100); });
    const roster = (typeof GHOST_ROSTER !== 'undefined' ? GHOST_ROSTER : []);
    const rng = rngFor(courseId, 'receipts');
    const receipts = Array.from({ length: 14 }, (_, i) => {
      const p = roster[Math.floor(rng() * roster.length)] || { first: 'M', last: 'Bell' };
      const gross = 20 + Math.floor(rng() * 7);
      const take = Math.round(gross * useTake / 100);
      const at = new Date(Date.now() - (i * 0.6 + rng()) * 864e5);
      at.setHours(16 + Math.floor(rng() * 4), rng() < 0.5 ? 0 : 30, 0, 0);
      return { id: `demo-${i}`, whenISO: at.toISOString(), golfer: `${p.first[0]}. ${p.last}`, gross, take, net: gross - take, twilight: true };
    });
    return { revenue30, sandboxTake30: take30, courseNet30: revenue30 - take30, takePct: useTake, foundMoney12: found12, trend30, receipts, monthlyNet, monthsOfHistory: 14, demo: true };
  }, [demo, real, courseId, takePct]);
}

// ─── floorFromRevenue — the month-matched floor view ──────────────────
// Guarantee is framed on the COURSE's own net dollars, never Sandbox's
// cut — "your revenue via Sandbox doesn't go backwards YoY," not a
// statement about what Sandbox keeps.
// months: last 12 entries {label, a: net(m−12), b: net(m), below, pending}
// active when ≥ 12 months of history, else 'baseline'.
function floorFromRevenue(monthlyNet, monthsOfHistory) {
  const now = new Date();
  const months = [];
  let shortfallQuarter = 0;
  const curQuarterStart = Math.floor(now.getMonth() / 3) * 3;
  for (let i = 11; i >= 0; i--) {
    const d = new Date(now.getFullYear(), now.getMonth() - i, 1);
    const prev = new Date(d.getFullYear() - 1, d.getMonth(), 1);
    const b = monthlyNet[monthKeyOf(d)] || 0;
    const a = monthlyNet[monthKeyOf(prev)] || 0;
    const pending = i === 0; // current month still accruing
    const below = !pending && a > 0 && b < a;
    if (below && d.getFullYear() === now.getFullYear() && d.getMonth() >= curQuarterStart) {
      shortfallQuarter += a - b;
    }
    months.push({ label: monthLabel(monthKeyOf(d)), a, b, below, pending });
  }
  const belowCount = months.filter(m => m.below).length;
  return {
    mode: monthsOfHistory >= 12 ? 'active' : 'baseline',
    months, belowCount, shortfallQuarter,
  };
}

// ─── useUtilizationWindows — 90-day fill by time-of-day window ────────
// { windows: [{key, label, seats, booked, rate}], sampleDays, demo }
function useUtilizationWindows(courseId) {
  const demo = useDemoMode();
  const [real, setReal] = React.useState(null);
  React.useEffect(() => {
    if (!courseId || demo) return undefined;
    let on = true;
    (async () => {
      try {
        const since = new Date(Date.now() - 90 * 864e5).toISOString();
        const nowISO = new Date().toISOString();
        const slots = await pageAll(() => sbx.from('tee_slots')
          .select('id, starts_at, capacity')
          .eq('course_id', courseId).gte('starts_at', since).lt('starts_at', nowISO).order('starts_at'));
        let booked = [];
        for (const ids of chunk(slots.map(s => s.id))) {
          // eslint-disable-next-line no-await-in-loop
          const page = await pageAll(() => sbx.from('bookings')
            .select('id, slot_id, status').in('slot_id', ids).order('created_at'));
          booked = booked.concat(page.filter(b => b.status !== 'cancelled' && b.status !== 'no_show'));
        }
        const bySlot = {};
        booked.forEach(b => { bySlot[b.slot_id] = (bySlot[b.slot_id] || 0) + 1; });
        const windows = TIME_WINDOWS.filter(w => w.key !== 'all').map(w => {
          const ws = slots.filter(s => { const m = minutesOfDay(s.starts_at); return m >= w.start && m < w.end; });
          const seats = ws.reduce((s, x) => s + (x.capacity || 4), 0);
          const bk = ws.reduce((s, x) => s + Math.min(bySlot[x.id] || 0, x.capacity || 4), 0);
          return { key: w.key, label: w.label, seats, booked: bk, rate: seats ? bk / seats : null };
        });
        if (on) setReal({ windows, sampleDays: 90, demo: false });
      } catch (_) { if (on) setReal({ windows: [], sampleDays: 0, demo: false }); }
    })();
    return () => { on = false; };
  }, [courseId, demo]);

  return React.useMemo(() => {
    if (!demo) return real;
    const days = demoWindowDays(courseId);
    const agg = {};
    days.forEach(d => {
      const a = agg[d.windowKey] || (agg[d.windowKey] = { seats: 0, booked: 0 });
      a.seats += d.seats; a.booked += d.booked;
    });
    const windows = TIME_WINDOWS.filter(w => w.key !== 'all').map(w => {
      const a = agg[w.key] || { seats: 0, booked: 0 };
      return { key: w.key, label: w.label, seats: a.seats, booked: a.booked, rate: a.seats ? a.booked / a.seats : null };
    });
    return { windows, sampleDays: 90, demo: true };
  }, [demo, real, courseId]);
}

// ─── useDemandCurve — upcoming waitlist windows by half-hour ──────────
// { buckets: [{min, count}], total, demo }
function useDemandCurve(courseId) {
  const demo = useDemoMode();
  const [real, setReal] = React.useState(null);
  React.useEffect(() => {
    if (!courseId || demo) return undefined;
    let on = true;
    (async () => {
      try {
        const { data } = await sbx.from('tee_waitlist')
          .select('start_min, end_min, play_date')
          .eq('course_id', courseId).gte('play_date', todayStr());
        const entries = data || [];
        const buckets = [];
        for (let m = 360; m <= 1320; m += 30) {
          buckets.push({ min: m, count: entries.filter(e => e.start_min <= m && m < e.end_min).length });
        }
        if (on) setReal({ buckets, total: entries.length, demo: false });
      } catch (_) { if (on) setReal({ buckets: [], total: 0, demo: false }); }
    })();
    return () => { on = false; };
  }, [courseId, demo]);
  return demo ? { ...demoDemandCurve(courseId), demo: true } : real;
}

// ─── Rack rate — what the course charges the public in that window ────
// Sandbox's price recommendation is a share of the course's own rate, so the
// rate has to come from somewhere. It is the course's number rather than
// something Sandbox sets, and this layer makes no schema changes, so the
// manager enters it in the portal and it is kept per course on the device —
// the same way tee-time templates are. A `rack_rate` column is honoured if
// one ever lands, so moving this to the database later needs no UI change.
const RACK_RATE_KEY = 'spp_course_rack_rate';
const PRICE_MAX = 75;              // the Price Per Golfer slider's ceiling
const TWILIGHT_SHARE = 0.40;       // of rack — the share Sandbox recommends

function loadRackRate(course) {
  if (!course || !course.id) return null;
  try {
    const v = Number(JSON.parse(localStorage.getItem(RACK_RATE_KEY) || '{}')[course.id]);
    if (v > 0) return v;
  } catch (_) { /* private mode */ }
  const col = Number(course.rack_rate);
  return col > 0 ? col : null;
}

function saveRackRate(courseId, rate) {
  try {
    const m = JSON.parse(localStorage.getItem(RACK_RATE_KEY) || '{}');
    const n = Number(rate);
    if (n > 0) m[courseId] = n; else delete m[courseId];
    localStorage.setItem(RACK_RATE_KEY, JSON.stringify(m));
  } catch (_) { /* private mode */ }
}

// Two panels show a suggestion built on this rate, so an edit in either one
// has to reach the other. localStorage fires no event in the tab that wrote
// it, hence the subscriber list.
const rackRateSubs = new Set();

function useRackRate(course) {
  const id = course && course.id;
  const col = course && course.rack_rate;
  const [rate, setRate] = React.useState(() => loadRackRate(course));
  React.useEffect(() => {
    const sync = () => setRate(loadRackRate({ id, rack_rate: col }));
    sync();
    rackRateSubs.add(sync);
    return () => { rackRateSubs.delete(sync); };
  }, [id, col]);
  const commit = React.useCallback((v) => {
    if (!id) return;
    saveRackRate(id, v);
    rackRateSubs.forEach(fn => fn());
  }, [id]);
  return [rate, commit];
}

// ─── suggestedPrice — what Sandbox recommends charging ────────────────
// The course always sets its own price; this is only a recommendation.
//
// Two bases, and which one is in play changes what the UI is allowed to
// claim. When the course has told us its normal rate the suggestion is a
// straight share of it — deliberately with no fill-rate or demand nudge on
// top, because the whole point is that the number matches the one-line rule
// we show beside it. Without a rate we fall back to the old anchor
// heuristic, which has no such promise to keep.
//
// Returns { suggested, basis: 'rack'|'sandbox', rack, share, anchor, ... }.
function suggestedPrice(course, utilization, demand, rackRate) {
  const rack = rackRate != null ? Number(rackRate) : loadRackRate(course);
  if (rack > 0) {
    const raw = Math.round(rack * TWILIGHT_SHARE);
    return {
      suggested: Math.min(PRICE_MAX, Math.max(1, raw)),
      basis: 'rack', rack, share: TWILIGHT_SHARE,
      anchor: rack, twilightRate: null, factor: 1, kicker: 0,
      clamped: raw > PRICE_MAX,
    };
  }
  const anchor = (course && course.suggested_price) || 22;
  const tw = utilization && utilization.windows ? utilization.windows.find(w => w.key === 'twilight') : null;
  const r = tw && tw.seats >= 32 ? tw.rate : null; // need a real sample
  const factor = r == null ? 1 : Math.min(1.2, Math.max(0.8, 0.8 + 0.5 * r));
  const kicker = demand && demand.total >= 12 ? 2 : 0;
  const suggested = Math.min(PRICE_MAX, Math.max(10, Math.round(anchor * factor) + kicker));
  return { suggested, basis: 'sandbox', rack: null, share: TWILIGHT_SHARE,
    anchor, twilightRate: r, factor, kicker, clamped: false };
}

// ─── useBusinessMetrics — per-day KPI rows for an arbitrary date range ─
// { days: [{ dayStr, revenue, seatsAvail, seatsBooked, rounds, players,
//   newPlayers, returningPlayers, noShows }], avgRoundMin, avgRoundSamples, demo }
// "day" = the slot's tee-time date, not the booking's created date.
// revenue is GROSS captured money (price_charged, set at check-in) — this
// dashboard is built entirely on the course's own numbers, never Sandbox's
// rev-share cut. seatsBooked/players/rounds are activity and count every
// live booking regardless of charge status; only revenue waits for one.
function useBusinessMetrics(courseId, fromISO, toISO) {
  const demo = useDemoMode();
  const [real, setReal] = React.useState(null);
  React.useEffect(() => {
    if (!courseId || demo) return undefined;
    let on = true;
    (async () => {
      try {
        const slots = await pageAll(() => sbx.from('tee_slots')
          .select('id, starts_at, capacity, price')
          .eq('course_id', courseId).gte('starts_at', fromISO).lt('starts_at', toISO).order('starts_at'));
        const slotById = {}; slots.forEach(s => { slotById[s.id] = s; });

        let bookings = [];
        for (const ids of chunk(slots.map(s => s.id))) {
          // eslint-disable-next-line no-await-in-loop
          const page = await pageAll(() => sbx.from('bookings')
            .select('id, slot_id, status, price_charged, user_id, match_id')
            .in('slot_id', ids));
          bookings = bookings.concat(page);
        }

        // First-ever booking date per user, across ALL history — a golfer's
        // very first Sandbox visit may predate this range's slot window.
        const { data: allBk } = await sbx.from('bookings')
          .select('user_id, created_at, slot:tee_slots!inner(course_id)')
          .eq('slot.course_id', courseId);
        const firstSeen = {};
        (allBk || []).forEach(b => {
          if (!b.user_id) return;
          if (!firstSeen[b.user_id] || b.created_at < firstSeen[b.user_id]) firstSeen[b.user_id] = b.created_at;
        });

        const matchIds = [...new Set(bookings.map(b => b.match_id).filter(Boolean))];
        let matches = [];
        for (const ids of chunk(matchIds)) {
          // eslint-disable-next-line no-await-in-loop
          const page = await pageAll(() => sbx.from('matches')
            .select('id, status, started_at, completed_at').in('id', ids));
          matches = matches.concat(page);
        }
        const matchById = {}; matches.forEach(m => { matchById[m.id] = m; });

        const dayOf = (iso) => iso.slice(0, 10);
        const byDay = {};
        const ensure = (day) => byDay[day] || (byDay[day] = {
          dayStr: day, revenue: 0, seatsAvail: 0, seatsBooked: 0,
          players: 0, newPlayers: 0, returningPlayers: 0, noShows: 0, rounds: 0,
        });
        slots.forEach(s => { ensure(dayOf(s.starts_at)).seatsAvail += (s.capacity || 4); });

        const countedRounds = new Set();
        bookings.forEach(b => {
          const s = slotById[b.slot_id];
          if (!s) return;
          const row = ensure(dayOf(s.starts_at));
          if (b.status === 'no_show') { row.noShows++; return; }
          if (b.status === 'cancelled') return;
          row.seatsBooked++; row.players++;
          if (b.price_charged != null) row.revenue += b.price_charged;
          if (firstSeen[b.user_id] && dayOf(firstSeen[b.user_id]) === dayOf(s.starts_at)) row.newPlayers++;
          else row.returningPlayers++;
          const m = b.match_id && matchById[b.match_id];
          if (m && m.status === 'completed' && !countedRounds.has(m.id)) { countedRounds.add(m.id); row.rounds++; }
        });

        const roundTimes = matches.filter(m => m.status === 'completed' && m.started_at && m.completed_at)
          .map(m => (new Date(m.completed_at) - new Date(m.started_at)) / 60000)
          .filter(x => x >= 5 && x <= 300);
        const avgRoundMin = roundTimes.length ? Math.round(roundTimes.reduce((s, x) => s + x, 0) / roundTimes.length) : null;

        const days = Object.values(byDay).sort((a, b) => (a.dayStr < b.dayStr ? -1 : 1));
        if (on) setReal({ days, avgRoundMin, avgRoundSamples: roundTimes.length, demo: false });
      } catch (_) {
        if (on) setReal({ days: [], avgRoundMin: null, avgRoundSamples: 0, demo: false });
      }
    })();
    return () => { on = false; };
  }, [courseId, demo, fromISO, toISO]);

  return React.useMemo(() => {
    if (!demo) return real;
    const fromStr = fromISO.slice(0, 10), toStr = toISO.slice(0, 10);
    const days = demoBusinessDays(courseId).filter(d => d.dayStr >= fromStr && d.dayStr < toStr);
    const totalRounds = days.reduce((s, d) => s + d.rounds, 0);
    const avgRoundMin = totalRounds ? Math.round(days.reduce((s, d) => s + d.avgRoundMin * d.rounds, 0) / totalRounds) : null;
    return { days: days.map(({ avgRoundMin: _a, ...d }) => d), avgRoundMin, avgRoundSamples: totalRounds, demo: true };
  }, [demo, real, courseId, fromISO, toISO]);
}

Object.assign(window, {
  useMoneyData, floorFromRevenue, pageAll, chunk, monthKeyOf, monthLabel,
  useUtilizationWindows, useDemandCurve, suggestedPrice, useBusinessMetrics,
  useRackRate, loadRackRate, TWILIGHT_SHARE,
});