/* global React, sbx, useDemoMode, rngFor, pageAll, chunk, todayStr, addCalendarDays, seatsOf, dayKeyOf */
// Network data layer — the whole partner network in one shape.
//
// The per-course hooks in manager-metrics-data.jsx page through bookings a
// course at a time. Fanning those out naively across every partner would be
// one round trip per course per table on every page load, so this does the
// opposite: it reads the network's slots once, its bookings once, and buckets
// by course in memory. Two queries plus chunked pages, not two per course.
//
// Everything degrades. Columns added by sql/admin-overhaul.sql may not exist
// yet, so reads ask for them optimistically and fall back to a narrower
// select when Postgres complains — the pattern useUsers already uses for the
// missing `tier` column. Nothing white-screens on an un-migrated database.

const PARTNER_TIERS = {
  pilot:  { label: 'Pilot',  hint: 'Year 1' },
  growth: { label: 'Growth', hint: 'Year 2' },
  embed:  { label: 'Embed',  hint: 'Year 3+' },
};

// Columns the overhaul migration adds. Requested first; dropped on failure.
const COURSE_EXTRA = 'partnership_tier, contract_start, contract_end, founding_partner, lat, lng';
// hero_img/render_img are here for the map's hover card. They were missing,
// which is why that preview had no picture: the column was never selected,
// so every course arrived without one.
const COURSE_BASE = 'id, name, short_name, city, state, address, status, suggested_price, sandbox_take_pct, created_at, hero_img, render_img';

// Read every course, with the partnership columns if the database has them.
// `migrated` tells the UI whether it is looking at real partnership data or
// at the fallback the old rule produces.
async function fetchCourses() {
  let { data, error } = await sbx.from('courses').select(`${COURSE_BASE}, ${COURSE_EXTRA}`).order('name');
  if (!error) return { rows: data || [], migrated: true };
  const res = await sbx.from('courses').select(COURSE_BASE).order('name');
  return { rows: res.data || [], migrated: false };
}

// Tier without the migration: the rule the partnership screen has always
// applied, so an un-migrated database still shows something truthful.
function derivedTier(course) {
  const take = course.sandbox_take_pct != null ? course.sandbox_take_pct : 15;
  if (take <= 12) return 'embed';
  if (take <= 14) return 'growth';
  return 'pilot';
}

function tierOf(course, migrated) {
  const t = migrated && course.partnership_tier ? course.partnership_tier : derivedTier(course);
  return PARTNER_TIERS[t] ? t : 'pilot';
}

// ─── Demo network ─────────────────────────────────────────────────────
// Deterministic per course id, like the rest of demo mode — seeded values
// only, never Math.random(), so the numbers survive a refresh identically
// and never jump between renders.
//
// Coordinates scatter around Miami-Dade so the map is populated on a cold
// database. These are demo positions, not claims about where anything is;
// real coordinates come from the course record once it has been placed.
const DADE = { lat: 25.7617, lng: -80.1918 };

function demoCourseStats(course) {
  const r = rngFor(course.id, 'network');
  const util = 0.34 + r() * 0.52;
  const roundsToday = Math.round(6 + r() * 34);
  const gross = Math.round((3200 + r() * 12800));
  const takePct = (course.sandbox_take_pct != null ? course.sandbox_take_pct : 15) / 100;
  const spark = Array.from({ length: 14 }, () => Math.round(gross / 30 * (0.55 + r() * 0.9)));
  return {
    utilization: util,
    roundsToday,
    grossMtd: gross,
    takeMtd: Math.round(gross * takePct),
    playersMtd: Math.round(gross / 22),
    spark,
    demo: true,
  };
}

function demoGeo(course) {
  const r = rngFor(course.id, 'geo');
  return {
    lat: DADE.lat + (r() - 0.5) * 0.42,
    lng: DADE.lng + (r() - 0.5) * 0.46,
    demo: true,
  };
}

// ─── Real network rollup ──────────────────────────────────────────────
// One pass over the network's slots and bookings for the trailing window,
// bucketed by course. `days` sets the window; the month-to-date figures are
// filtered out of the same result rather than fetched again.
async function fetchNetworkStats(courses, days = 30) {
  const since = new Date(); since.setDate(since.getDate() - days); since.setHours(0, 0, 0, 0);
  const ids = courses.map(c => c.id);
  const empty = () => ({ utilization: null, roundsToday: 0, grossMtd: 0, takeMtd: 0, playersMtd: 0, spark: [], demo: false });
  const byCourse = {};
  ids.forEach(id => { byCourse[id] = empty(); });
  if (!ids.length) return byCourse;

  // Slots for every course at once, chunked so no .in() list gets too long.
  let slots = [];
  for (const group of chunk(ids, 40)) {
    // eslint-disable-next-line no-await-in-loop
    const page = await pageAll(() => sbx.from('tee_slots')
      .select('id, course_id, starts_at, price, capacity')
      .in('course_id', group).gte('starts_at', since.toISOString()));
    slots = slots.concat(page);
  }
  const slotById = {};
  slots.forEach(s => { slotById[s.id] = s; });

  // Then the bookings against those slots, again in chunks.
  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, created_at')
      .in('slot_id', group));
    bookings = bookings.concat(page);
  }

  const monthStart = new Date(); monthStart.setDate(1); monthStart.setHours(0, 0, 0, 0);
  const today = todayStr();
  const seats = {}, booked = {}, players = {}, sparks = {};
  ids.forEach(id => { seats[id] = 0; booked[id] = 0; players[id] = new Set(); sparks[id] = new Array(14).fill(0); });

  slots.forEach(s => {
    if (byCourse[s.course_id]) seats[s.course_id] += seatsOf(s);
  });

  const dayIndex = (iso) => {
    const d = Math.floor((Date.now() - new Date(iso).getTime()) / 864e5);
    return d >= 0 && d < 14 ? 13 - d : -1;
  };

  bookings.forEach(b => {
    const slot = slotById[b.slot_id];
    if (!slot || !byCourse[slot.course_id]) return;
    if (b.status === 'cancelled' || b.status === 'no_show') return;
    const cid = slot.course_id;
    // Utilization, players and today's round count are activity — every live
    // reserved seat counts, whether or not it's been paid for yet.
    booked[cid] += 1;
    if (b.user_id) players[cid].add(b.user_id);
    if (slot.starts_at.slice(0, 10) === today) byCourse[cid].roundsToday += 1;
    // Money is different: price_charged is captured at check-in now, not at
    // booking, so a reserved-but-uncharged seat contributes nothing to gross
    // until it actually has a charge.
    if (b.price_charged == null) return;
    const gross = b.price_charged;
    const startsAt = new Date(slot.starts_at);
    if (startsAt >= monthStart) byCourse[cid].grossMtd += gross;
    const i = dayIndex(slot.starts_at);
    if (i >= 0) sparks[cid][i] += gross;
  });

  courses.forEach(c => {
    const stat = byCourse[c.id];
    const takePct = (c.sandbox_take_pct != null ? c.sandbox_take_pct : 15) / 100;
    stat.utilization = seats[c.id] ? booked[c.id] / seats[c.id] : null;
    stat.takeMtd = Math.round(stat.grossMtd * takePct);
    stat.playersMtd = players[c.id].size;
    stat.spark = sparks[c.id];
  });
  return byCourse;
}

// Which courses actually have a Sandbox 9 laid out on them — mats down,
// yardages set — as opposed to being a golf course we merely list. The
// evidence is course_holes carrying sandbox_yards; a course with no such
// rows has never been configured.
async function fetchSbxCourses(ids) {
  const out = new Set();
  if (!ids.length) return out;
  try {
    for (const group of chunk(ids, 40)) {
      // eslint-disable-next-line no-await-in-loop
      const { data } = await sbx.from('course_holes')
        .select('course_id').in('course_id', group).not('sandbox_yards', 'is', null);
      (data || []).forEach(r => out.add(r.course_id));
    }
  } catch (_) { /* un-migrated or empty table just means nothing is set up */ }
  return out;
}

// The same flag on its own, for screens that need to know which courses are
// set up without paying for the whole network rollup.
function useSbxCourses() {
  const demo = useDemoMode();
  const [ids, setIds] = React.useState(null);
  React.useEffect(() => {
    let live = true;
    (async () => {
      const { data } = await sbx.from('courses').select('id');
      const all = (data || []).map(c => c.id);
      const set = demo
        ? new Set(all.filter(id => rngFor(id, 'sbx-setup')() > 0.34))
        : await fetchSbxCourses(all);
      if (live) setIds(set);
    })();
    return () => { live = false; };
  }, [demo]);
  return ids;
}

// ─── Health checks ────────────────────────────────────────────────────
// What actually needs a human. Each returns null when fine, or a reason.
// Severity is 'warn' or 'critical' — rendered as shape as well as colour,
// the way match results are, so it reads without relying on hue.
function courseAlerts(course, stat, ops) {
  const out = [];
  if (course.status !== 'active') {
    out.push({ severity: 'warn', code: 'inactive', text: `Marked ${String(course.status || 'unknown').replace('_', ' ')}` });
  }
  if (ops && ops.tomorrowSlots === 0) {
    out.push({ severity: 'critical', code: 'no-times', text: 'No tee times published for tomorrow' });
  }
  if (ops && !ops.yardagesToday) {
    out.push({ severity: 'warn', code: 'no-yardages', text: 'No yardages set today' });
  }
  if (stat && stat.utilization != null && stat.utilization < 0.15 && !stat.demo) {
    out.push({ severity: 'warn', code: 'low-util', text: `Utilization at ${Math.round(stat.utilization * 100)}%` });
  }
  if (!course.lat && !course.lng) {
    out.push({ severity: 'warn', code: 'no-location', text: 'No map location set' });
  }
  return out;
}

// Cheap per-course operational reads: does tomorrow have times, and were
// yardages set today. Two small queries for the whole network, not per course.
async function fetchOps(ids) {
  const out = {};
  ids.forEach(id => { out[id] = { tomorrowSlots: 0, yardagesToday: false }; });
  if (!ids.length) return out;
  const tomorrow = dayKeyOf(addCalendarDays(new Date(), 1));
  const from = new Date(`${tomorrow}T00:00:00`); const to = new Date(`${tomorrow}T23:59:59`);
  try {
    for (const group of chunk(ids, 40)) {
      // eslint-disable-next-line no-await-in-loop
      const { data } = await sbx.from('tee_slots').select('course_id')
        .in('course_id', group).gte('starts_at', from.toISOString()).lte('starts_at', to.toISOString());
      (data || []).forEach(r => { if (out[r.course_id]) out[r.course_id].tomorrowSlots += 1; });
    }
    for (const group of chunk(ids, 40)) {
      // eslint-disable-next-line no-await-in-loop
      const { data } = await sbx.from('course_hole_days').select('course_id')
        .in('course_id', group).eq('play_date', todayStr());
      (data || []).forEach(r => { if (out[r.course_id]) out[r.course_id].yardagesToday = true; });
    }
  } catch (_) { /* an un-migrated or empty table just leaves the defaults */ }
  return out;
}

// ─── useNetwork ───────────────────────────────────────────────────────
// The one hook the admin screens read. Returns null while loading, then
//   { courses: [{ ...course, tier, stat, alerts, geo }], totals, migrated, demo }
function useNetwork() {
  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 () => {
      const { rows, migrated } = await fetchCourses();
      if (!live) return;
      const stats = demo
        ? Object.fromEntries(rows.map(c => [c.id, demoCourseStats(c)]))
        : await fetchNetworkStats(rows);
      const ops = demo
        ? Object.fromEntries(rows.map(c => [c.id, { tomorrowSlots: 4, yardagesToday: true }]))
        : await fetchOps(rows.map(c => c.id));
      // In demo mode, treat roughly two thirds as configured so the map has
      // both pin colours to show rather than one flat wall of forest.
      const sbxSet = demo
        ? new Set(rows.filter(c => rngFor(c.id, 'sbx-setup')() > 0.34).map(c => c.id))
        : await fetchSbxCourses(rows.map(c => c.id));
      if (!live) return;

      const courses = rows.map(c => {
        const stat = stats[c.id] || {};
        // A real coordinate always wins; demo mode only fills the gap, and
        // says so, so a placed course never gets moved by the toggle.
        const placed = c.lat != null && c.lng != null;
        const geo = placed
          ? { lat: Number(c.lat), lng: Number(c.lng), demo: false }
          : (demo ? demoGeo(c) : null);
        return {
          ...c,
          tier: tierOf(c, migrated),
          founding: migrated ? !!c.founding_partner : (c.sandbox_take_pct != null ? c.sandbox_take_pct <= 12 : false),
          stat,
          geo,
          placed,
          // True = a Sandbox 9 is laid out here (an official partner course).
          // False = a golf course we list, with no SBX setup on the ground.
          hasSbx: sbxSet.has(c.id),
          alerts: courseAlerts(c, stat, ops[c.id]),
        };
      });

      const sum = (k) => courses.reduce((n, c) => n + (c.stat[k] || 0), 0);
      const utils = courses.map(c => c.stat.utilization).filter(v => v != null);
      setState({
        courses,
        migrated,
        demo,
        totals: {
          courses: courses.length,
          active: courses.filter(c => c.status === 'active').length,
          grossMtd: sum('grossMtd'),
          takeMtd: sum('takeMtd'),
          roundsToday: sum('roundsToday'),
          playersMtd: sum('playersMtd'),
          utilization: utils.length ? utils.reduce((a, b) => a + b, 0) / utils.length : null,
          alerts: courses.reduce((n, c) => n + c.alerts.length, 0),
          spark: courses.reduce((acc, c) => {
            (c.stat.spark || []).forEach((v, i) => { acc[i] = (acc[i] || 0) + v; });
            return acc;
          }, new Array(14).fill(0)),
        },
      });
    })().catch(() => { if (live) setState({ courses: [], totals: null, migrated: false, demo, error: true }); });
    return () => { live = false; };
  }, [demo, nonce]);

  return [state, reload];
}

// ─── Geocoding ────────────────────────────────────────────────────────
// Courses already carry a street address; what the map needs is a
// coordinate. Nominatim (OpenStreetMap's own geocoder) does that for free
// with no key and permissive CORS, which is what makes it usable from a
// portal that ships the anon key and has no server to proxy through.
//
// Its usage policy is the constraint that shapes this: at most one request
// a second, and no bulk geocoding. So this is never called on page load —
// only when someone asks for it — and the "place them all" path walks the
// list with a real delay between each rather than firing them in parallel.
const NOMINATIM = 'https://nominatim.openstreetmap.org/search';
const GEOCODE_GAP_MS = 1100;

// The most specific query the record supports. A street address alone is
// ambiguous across cities, so the city and state ride along — but only when
// the address does not already carry them. Most addresses on file are
// entered full ("1802 NW 37th Ave, Miami, FL"), and appending the city
// again gives the geocoder "…, Miami, FL, Miami, FL", which reads as a
// worse match than the clean string.
function addressQuery(course) {
  const addr = (course.address || '').trim();
  const has = (part) => {
    if (!part) return true;
    return new RegExp(`(^|[,\\s])${part.trim().replace(/[.*+?^${}()|[\]\\]/g, '\\$&')}([,\\s]|$)`, 'i').test(addr);
  };
  const parts = [
    addr || (course.name || course.short_name || ''),
    addr && has(course.city) ? null : course.city,
    addr && has(course.state) ? null : course.state,
  ].filter(Boolean);
  return parts.join(', ').trim();
}

async function geocodeCourse(course) {
  const q = addressQuery(course);
  if (!q) throw new Error('No address to look up. Add one in the course details first.');
  const url = `${NOMINATIM}?format=jsonv2&limit=1&countrycodes=us&q=${encodeURIComponent(q)}`;
  let res;
  try {
    res = await fetch(url, { headers: { Accept: 'application/json' } });
  } catch (_) {
    throw new Error('Could not reach the geocoder. Check the connection, or drop the pin by hand.');
  }
  if (!res.ok) throw new Error(`The geocoder returned ${res.status}. Try again shortly, or drop the pin by hand.`);
  const hits = await res.json();
  if (!hits || !hits.length) {
    throw new Error(`Nothing found for "${q}". Check the address, or drop the pin by hand.`);
  }
  return {
    lat: Number(hits[0].lat),
    lng: Number(hits[0].lon),
    label: hits[0].display_name,
  };
}

// Geocode and save in one step. Returns the coordinate it placed.
async function placeFromAddress(course) {
  const hit = await geocodeCourse(course);
  await saveCourseLocation(course.id, hit.lat, hit.lng);
  return hit;
}

const sleep = (ms) => new Promise(r => setTimeout(r, ms));

// Walk a list, one lookup at a time, reporting progress as it goes. One
// course failing does not stop the rest — the failures come back for the UI
// to show, because "3 of 5 placed, here are the two that didn't" is more
// useful than a single red banner.
async function placeAllFromAddress(courses, onProgress) {
  const done = [], failed = [];
  for (let i = 0; i < courses.length; i += 1) {
    const c = courses[i];
    if (onProgress) onProgress({ i, total: courses.length, course: c });
    try {
      // eslint-disable-next-line no-await-in-loop
      await placeFromAddress(c);
      done.push(c);
    } catch (e) {
      failed.push({ course: c, message: e.message });
    }
    // eslint-disable-next-line no-await-in-loop
    if (i < courses.length - 1) await sleep(GEOCODE_GAP_MS);
  }
  return { done, failed };
}

// ─── Placing a course on the map ──────────────────────────────────────
// Writes real coordinates. Fails loudly if the migration has not been run,
// because silently doing nothing when someone drops a pin is worse than an
// error that explains itself.
async function saveCourseLocation(courseId, lat, lng) {
  const payload = lat == null || lng == null
    ? { lat: null, lng: null }
    : { lat: Number(Number(lat).toFixed(6)), lng: Number(Number(lng).toFixed(6)) };
  const { error } = await sbx.from('courses').update(payload).eq('id', courseId);
  if (error) {
    if (/lat|lng|column .* does not exist/i.test(error.message || '')) {
      throw new Error('Map locations need sql/admin-overhaul.sql to be run first.');
    }
    throw new Error(error.message || 'Could not save the location.');
  }
}

Object.assign(window, {
  useNetwork, useSbxCourses, saveCourseLocation, PARTNER_TIERS, tierOf, derivedTier, courseAlerts, demoGeo, DADE,
  geocodeCourse, placeFromAddress, placeAllFromAddress, addressQuery,
});