/* global React, sbx, useDemoMode, rngFor, pageAll, chunk, monthKeyOf, monthLabel, audit, auditSnapshot */
// Money data layer — what the network billed, what Sandbox kept, and what
// each course is owed.
//
// UNITS. This file straddles a boundary and it matters. The booking tables
// predate it and store plain dollars (`tee_slots.price`, `bookings
// .price_charged`), while `payments` and `payouts` store integer cents,
// because money that gets summed, split and reconciled should never be a
// float. Conversion happens once, at the edge, in toCents/fromCents —
// nothing in between should be mixing the two.
//
// WHERE REVENUE COMES FROM. Ideally from `payments`: one row per charge,
// with the split recorded at charge time. Stripe isn't live, so that table
// is empty, and the honest figure today is bookings.price_charged times the
// course's take percentage. Both paths are implemented and the UI is told
// which one it got, because "computed from bookings" and "this is what we
// actually charged" are different claims and shouldn't look alike.
//
// price_charged itself is captured at check-in (rule 24), not at the moment
// a seat is booked — so a booking still sitting at 'reserved' has no charge
// yet, and fetchRevenue below skips it rather than substituting the slot's
// list price. Counting an unplayed, uncharged round as billed would have
// overstated gross and, worse, what a payout says a course is owed.

const toCents = (dollars) => Math.round((Number(dollars) || 0) * 100);
const fromCents = (cents) => (Number(cents) || 0) / 100;

const PAYOUT_STATUS = {
  pending:    { label: 'Pending',    hint: 'Owed, not sent' },
  processing: { label: 'Processing', hint: 'Sent, not settled' },
  paid:       { label: 'Paid',       hint: 'Settled' },
  failed:     { label: 'Failed',     hint: 'Needs another attempt' },
};

// Calendar month bounds as 'YYYY-MM-DD'. Months are the default grain
// because the cadence is still an open question; the schema stores an
// explicit start and end, so switching to fortnightly or per-event later
// needs no migration and no backfill.
function monthBounds(year, monthIdx) {
  const p = (n) => String(n).padStart(2, '0');
  const start = `${year}-${p(monthIdx + 1)}-01`;
  const last = new Date(year, monthIdx + 1, 0).getDate();
  return { start, end: `${year}-${p(monthIdx + 1)}-${p(last)}` };
}

// The last `n` months, newest first, as pickable periods.
function recentMonths(n = 6) {
  const out = [];
  const now = new Date();
  for (let i = 0; i < n; i += 1) {
    const d = new Date(now.getFullYear(), now.getMonth() - i, 1);
    const b = monthBounds(d.getFullYear(), d.getMonth());
    out.push({
      key: monthKeyOf(d),
      label: `${monthLabel(monthKeyOf(d))} ${d.getFullYear()}`,
      ...b,
    });
  }
  return out;
}

// ─── Booking-derived revenue ──────────────────────────────────────────
// One pass over the network's slots and bookings in a window, bucketed by
// course and by month. Same shape as the network rollup: two chunked
// queries for the whole network rather than two per course.
async function fetchRevenue(courseIds, fromDate, toDate) {
  const from = new Date(`${fromDate}T00:00:00`);
  const to = new Date(`${toDate}T23:59:59.999`);
  const byCourse = {};
  const byMonth = {};
  courseIds.forEach(id => { byCourse[id] = { grossDollars: 0, rounds: 0, players: new Set() }; });
  if (!courseIds.length) return { byCourse, byMonth };

  let slots = [];
  for (const group of chunk(courseIds, 40)) {
    // eslint-disable-next-line no-await-in-loop
    const page = await pageAll(() => sbx.from('tee_slots')
      .select('id, course_id, starts_at, price')
      .in('course_id', group)
      .gte('starts_at', from.toISOString())
      .lte('starts_at', to.toISOString()));
    slots = slots.concat(page);
  }
  const slotById = {};
  slots.forEach(s => { slotById[s.id] = s; });

  let bookings = [];
  for (const group 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, user_id, status, price_charged')
      .in('slot_id', group));
    bookings = bookings.concat(page);
  }

  bookings.forEach(b => {
    const slot = slotById[b.slot_id];
    if (!slot || !byCourse[slot.course_id]) return;
    // A cancellation or a no-show was never billed, so neither belongs in
    // what a course is owed.
    if (b.status === 'cancelled' || b.status === 'no_show') return;
    // price_charged is captured at check-in, not at booking — a reserved
    // seat that hasn't checked in yet has no charge to bill a course for.
    // The list-price fallback used to count that seat as billed the moment
    // it was booked, which overstated gross (and what a payout owed) for
    // every round that hadn't actually happened yet.
    if (b.price_charged == null) return;
    const gross = b.price_charged;
    const rec = byCourse[slot.course_id];
    rec.grossDollars += gross;
    rec.rounds += 1;
    if (b.user_id) rec.players.add(b.user_id);

    const mk = monthKeyOf(new Date(slot.starts_at));
    byMonth[mk] = byMonth[mk] || {};
    byMonth[mk][slot.course_id] = (byMonth[mk][slot.course_id] || 0) + gross;
  });

  Object.values(byCourse).forEach(r => { r.players = r.players.size; });
  return { byCourse, byMonth };
}

// Real charges, when there are any. Returns null if the table is missing
// so callers can tell "no Stripe yet" apart from "Stripe but no sales".
async function fetchPayments(fromDate, toDate) {
  const { data, error } = await sbx.from('payments')
    .select('id, course_id, user_id, amount_cents, sandbox_fee_cents, course_amount_cents, status, refunded_cents, stripe_payment_intent_id, created_at')
    .gte('created_at', `${fromDate}T00:00:00Z`)
    .lte('created_at', `${toDate}T23:59:59Z`)
    .order('created_at', { ascending: false })
    .limit(500);
  if (error) return null;
  return data || [];
}

// ─── Payout ledger ────────────────────────────────────────────────────
async function fetchPayouts(periodStart, periodEnd) {
  const { data, error } = await sbx.from('payouts')
    .select('*')
    .eq('period_start', periodStart)
    .eq('period_end', periodEnd);
  if (error) {
    if (/relation .* does not exist|payouts/i.test(error.message || '')) return null;
    throw new Error(error.message);
  }
  return data || [];
}

// Compute the period from bookings and write it, one row per course with
// revenue in the window.
//
// Rebuilding is deliberately non-destructive on anything a human has acted
// on: amounts refresh (a late booking should move the number), but status,
// paid_at and the Stripe transfer id are carried forward. Recomputing a
// ledger should never quietly un-pay a course.
async function buildLedger(courses, period) {
  const ids = courses.map(c => c.id);
  const { byCourse } = await fetchRevenue(ids, period.start, period.end);
  const existing = (await fetchPayouts(period.start, period.end)) || [];
  const priorByCourse = {};
  existing.forEach(p => { priorByCourse[p.course_id] = p; });

  const rows = [];
  courses.forEach(c => {
    const rec = byCourse[c.id];
    if (!rec || rec.grossDollars <= 0) return;   // nothing billed, nothing owed
    const grossCents = toCents(rec.grossDollars);
    const takePct = (c.sandbox_take_pct != null ? c.sandbox_take_pct : 15) / 100;
    // Round the fee, then take the remainder as the course's, so the two
    // always add back to gross exactly — rounding both independently loses
    // or invents a cent.
    const takeCents = Math.round(grossCents * takePct);
    const prior = priorByCourse[c.id];
    rows.push({
      ...(prior ? { id: prior.id } : {}),
      course_id: c.id,
      period_start: period.start,
      period_end: period.end,
      gross_cents: grossCents,
      sandbox_take_cents: takeCents,
      course_net_cents: grossCents - takeCents,
      status: prior ? prior.status : 'pending',
      stripe_transfer_id: prior ? prior.stripe_transfer_id : null,
      paid_at: prior ? prior.paid_at : null,
      note: prior ? prior.note : null,
    });
  });

  if (!rows.length) return { written: 0 };
  const { error } = await sbx.from('payouts')
    .upsert(rows, { onConflict: 'course_id,period_start,period_end' });
  if (error) {
    if (/relation .* does not exist/i.test(error.message || '')) {
      throw new Error('The payouts table is missing. Run sql/admin-overhaul.sql in Supabase first.');
    }
    throw new Error(error.message || 'Could not write the ledger.');
  }
  // period is { key, label, start, end } — see recentMonths().
  await audit('payout_ledger_build', 'payouts', `${period.start}..${period.end}`, null,
    { rows: rows.length, period: period.label });
  return { written: rows.length };
}

async function setPayoutStatus(id, status, extra = {}) {
  const patch = { status, ...extra };
  // Settling stamps the time; reopening clears it, so a reopened row never
  // claims to have been paid at some point in the past.
  if (status === 'paid') patch.paid_at = new Date().toISOString();
  if (status === 'pending') { patch.paid_at = null; patch.stripe_transfer_id = null; }
  const { data: was } = await sbx.from('payouts')
    .select('course_id, period_start, period_end, course_net_cents, status').eq('id', id).maybeSingle();
  const { error } = await sbx.from('payouts').update(patch).eq('id', id);
  if (error) throw new Error(error.message || 'Could not update the payout.');
  // Marking a payout paid is an assertion that money left the building.
  await audit('payout_status', 'payouts', id,
    auditSnapshot(was, ['course_id', 'period_start', 'period_end', 'course_net_cents', 'status']),
    { status });
}

// ─── Demo ledger ──────────────────────────────────────────────────────
function demoLedger(courses, period) {
  return courses.map((c, i) => {
    const r = rngFor(c.id, `payout|${period.start}`);
    const grossCents = toCents(Math.round(2600 + r() * 14000));
    const takePct = (c.sandbox_take_pct != null ? c.sandbox_take_pct : 15) / 100;
    const takeCents = Math.round(grossCents * takePct);
    const roll = r();
    const status = roll > 0.72 ? 'paid' : roll > 0.55 ? 'processing' : 'pending';
    return {
      id: `demo-${c.id}-${period.start}`,
      course_id: c.id,
      period_start: period.start,
      period_end: period.end,
      gross_cents: grossCents,
      sandbox_take_cents: takeCents,
      course_net_cents: grossCents - takeCents,
      status,
      stripe_transfer_id: status === 'paid' ? `tr_demo${String(i + 1).padStart(4, '0')}` : null,
      paid_at: status === 'paid' ? new Date(`${period.end}T18:00:00Z`).toISOString() : null,
      note: null,
      demo: true,
    };
  });
}

// ─── usePayoutLedger ──────────────────────────────────────────────────
// Returns [state, reload]; state is null while loading, then
//   { rows, byCourse, totals, demo, missing }
function usePayoutLedger(courses, period) {
  const demo = useDemoMode();
  const [state, setState] = React.useState(null);
  const [nonce, setNonce] = React.useState(0);
  const reload = React.useCallback(() => setNonce(n => n + 1), []);

  React.useEffect(() => {
    let live = true;
    setState(null);
    (async () => {
      let rows, missing = false;
      if (demo) {
        rows = demoLedger(courses, period);
      } else {
        const got = await fetchPayouts(period.start, period.end);
        missing = got === null;
        rows = got || [];
      }
      if (!live) return;
      const byCourse = {};
      rows.forEach(r => { byCourse[r.course_id] = r; });
      const sum = (k) => rows.reduce((n, r) => n + (r[k] || 0), 0);
      const owed = rows.filter(r => r.status !== 'paid');
      setState({
        rows, byCourse, demo, missing,
        totals: {
          gross: sum('gross_cents'),
          take: sum('sandbox_take_cents'),
          net: sum('course_net_cents'),
          outstanding: owed.reduce((n, r) => n + (r.course_net_cents || 0), 0),
          outstandingCount: owed.length,
          paidCount: rows.length - owed.length,
        },
      });
    })().catch(e => { if (live) setState({ rows: [], byCourse: {}, totals: null, demo, error: e.message }); });
    return () => { live = false; };
  }, [demo, nonce, period.start, period.end, courses]);

  return [state, reload];
}

// ─── useRevenueHistory ────────────────────────────────────────────────
// Monthly gross and Sandbox take across the network, for the trend chart.
function useRevenueHistory(courses, months = 12) {
  const demo = useDemoMode();
  const [state, setState] = React.useState(null);

  React.useEffect(() => {
    let live = true;
    setState(null);
    (async () => {
      const now = new Date();
      const first = new Date(now.getFullYear(), now.getMonth() - (months - 1), 1);
      const keys = [];
      for (let i = 0; i < months; i += 1) {
        const d = new Date(first.getFullYear(), first.getMonth() + i, 1);
        keys.push(monthKeyOf(d));
      }
      const takeOf = (c) => (c.sandbox_take_pct != null ? c.sandbox_take_pct : 15) / 100;

      if (demo) {
        // Build the per-course-per-month grid once and derive every total
        // from it. Generating the monthly series and the per-course ranking
        // from two separate formulas is how a demo ends up showing a
        // 12-month total that does not equal the sum of its own bars.
        const grid = courses.map(c => ({
          course: c,
          months: keys.map((k, i) => {
            const r = rngFor(c.id, `rev|${k}`);
            return Math.round((2200 + r() * 11000) * (0.7 + i / (months * 1.6)));
          }),
        }));
        const gross = keys.map((_, i) => grid.reduce((n, row) => n + row.months[i], 0));
        const take = keys.map((_, i) => grid.reduce((n, row) => n + Math.round(row.months[i] * takeOf(row.course)), 0));
        const byCourse = grid.map(row => {
          const total = row.months.reduce((a, b) => a + b, 0);
          return { course: row.course, gross: total, take: Math.round(total * takeOf(row.course)) };
        }).sort((a, b) => b.gross - a.gross);
        if (live) setState({ keys, gross, take, byCourse, demo: true });
        return;
      }

      const b = monthBounds(first.getFullYear(), first.getMonth());
      const end = monthBounds(now.getFullYear(), now.getMonth()).end;
      const { byCourse: perCourse, byMonth } = await fetchRevenue(courses.map(c => c.id), b.start, end);
      if (!live) return;
      const gross = keys.map(k => Object.values(byMonth[k] || {}).reduce((n, v) => n + v, 0));
      const take = keys.map(k => courses.reduce((n, c) => n + ((byMonth[k] || {})[c.id] || 0) * takeOf(c), 0));
      const ranked = courses.map(c => {
        const g = (perCourse[c.id] || {}).grossDollars || 0;
        return { course: c, gross: g, take: g * takeOf(c) };
      }).sort((a, b2) => b2.gross - a.gross);
      setState({ keys, gross: gross.map(Math.round), take: take.map(Math.round), byCourse: ranked, demo: false });
    })().catch(() => { if (live) setState({ keys: [], gross: [], take: [], byCourse: [], demo: false, error: true }); });
    return () => { live = false; };
  }, [demo, months, courses]);

  return state;
}

Object.assign(window, {
  usePayoutLedger, useRevenueHistory, buildLedger, setPayoutStatus, fetchPayments,
  recentMonths, monthBounds, toCents, fromCents, PAYOUT_STATUS,
});