// olivia-supabase.jsx
// Supabase client + data fetchers for the Olivia admin.

const SUPABASE_URL      = 'https://kxdauedixfrnlmquazjm.supabase.co';
const SUPABASE_ANON_KEY = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6Imt4ZGF1ZWRpeGZybmxtcXVhemptIiwicm9sZSI6ImFub24iLCJpYXQiOjE3ODA1NjEzMjgsImV4cCI6MjA5NjEzNzMyOH0.RO6FTbnnQ1liwd2aeFIUuTAPWGaro0rWsxLwrF3wKWA';

const db = (typeof supabase !== 'undefined' && supabase.createClient)
  ? supabase.createClient(SUPABASE_URL, SUPABASE_ANON_KEY)
  : null;

async function fetchOverviewStats() {
  if (!db) return null;
  const [statsRes, trendRes, audienceRes] = await Promise.all([
    db.rpc('get_overview_stats'),
    db.rpc('get_growth_trend', { p_days: 365 }),
    db.rpc('get_audience_breakdown'),
  ]);
  if (statsRes.error || !statsRes.data) return null;
  const data     = statsRes.data;
  const trend    = trendRes.data || [];
  const audience = audienceRes.data || {};
  return {
    signupCount:        data.signup_count        ?? 0,
    childrenCount:      data.children_count      ?? 0,
    avgStreak:          parseFloat(data.avg_streak)       || 0,
    longestStreak:      data.longest_streak      ?? 0,
    activeCount:        data.active_count        ?? 0,
    prevSignupCount:    data.prev_signup_count   ?? 0,
    prevChildrenCount:  data.prev_children_count ?? 0,
    prevAvgStreak:      parseFloat(data.prev_avg_streak)  || 0,
    prevActiveCount:    data.prev_active_count   ?? 0,
    trendRaw:           trend,
    byAgeBand:          audience.by_age_band    || [],
    byRelationship:     audience.by_relationship || [],
    signupTrend:        trend.length >= 2 ? trend.map(r => r.signup_count) : null,
    activeTrend:        trend.length >= 2 ? trend.map(r => r.active_count) : null,
  };
}

// Polls every 30 seconds. Returns a stop function.
function pollOverviewStats(onUpdate, intervalMs = 30000) {
  if (!db) return () => {};
  const run = () => fetchOverviewStats().then(stats => { if (stats) onUpdate(stats); }).catch(() => {});
  run();
  const id = setInterval(run, intervalMs);
  return () => clearInterval(id);
}

// ─── Admin auth ───────────────────────────────────────────────
async function checkIsAdmin() {
  if (!db) return false;
  const { data: { user } } = await db.auth.getUser();
  if (!user) return false;
  const { data } = await db.from('profiles').select('is_admin').eq('id', user.id).single();
  return !!(data && data.is_admin);
}

async function adminSignIn(email, password) {
  if (!db) return { error: 'No Supabase client.' };
  const { data, error } = await db.auth.signInWithPassword({ email, password });
  if (error) return { error: error.message };
  if (!(await checkIsAdmin())) {
    await db.auth.signOut();
    return { error: 'This account is not an admin.' };
  }
  return { user: data.user };
}

async function adminSignOut() { if (db) await db.auth.signOut(); }

async function getAdminSession() {
  if (!db) return null;
  const { data: { session } } = await db.auth.getSession();
  if (!session) return null;
  return { user: session.user, isAdmin: await checkIsAdmin() };
}

// ─── Content CRUD (topics / tracks / course_days) ────────────
async function fetchTopics() {
  const { data } = await db.from('topics').select('*').order('title');
  return data || [];
}
async function createTopic(t) {
  const { data, error } = await db.from('topics').insert(t).select().single();
  return error ? { error: error.message } : data;
}
async function updateTopic(id, patch) {
  const { error } = await db.from('topics').update(patch).eq('id', id);
  return error ? { error: error.message } : {};
}
async function deleteTopic(id) {
  const { error } = await db.from('topics').delete().eq('id', id);
  return error ? { error: error.message } : {};
}

async function fetchTracksByTopic(topicId) {
  const { data } = await db.from('courses').select('*').eq('topic_id', topicId).order('created_at');
  return data || [];
}
async function createTrack(c) {
  const { data, error } = await db.from('courses').insert(c).select().single();
  return error ? { error: error.message } : data;
}
async function updateTrack(id, patch) {
  const { error } = await db.from('courses').update(patch).eq('id', id);
  return error ? { error: error.message } : {};
}
async function deleteTrack(id) {
  const { error } = await db.from('courses').delete().eq('id', id);
  return error ? { error: error.message } : {};
}
async function deleteAllDraftTracks() {
  const { error } = await db.from('courses').delete().eq('is_published', false);
  return error ? { error: error.message } : {};
}
async function fetchUsedUnsplashIds() {
  const { data } = await db.from('courses').select('unsplash_photo_id').not('unsplash_photo_id', 'is', null);
  return new Set((data || []).map(r => r.unsplash_photo_id));
}

// ─── Storage ──────────────────────────────────────────────────
async function uploadTopicIcon(file, topicId) {
  if (!db) return { error: 'No Supabase client.' };
  const ext  = (file.name.split('.').pop() || 'png').toLowerCase();
  const path = `topic-icons/${topicId}.${ext}`;
  const { data, error } = await db.storage
    .from('avatar')
    .upload(path, file, { contentType: file.type || 'image/png', upsert: true });
  if (error) return { error: error.message };
  const { data: urlData } = db.storage.from('avatar').getPublicUrl(path);
  return { url: `${urlData.publicUrl}?t=${Date.now()}` };
}

async function uploadAuthorImage(file, trackId) {
  if (!db) return { error: 'No Supabase client.' };
  const ext  = (file.name.split('.').pop() || 'jpg').toLowerCase();
  const path = `author-photos/${trackId}.${ext}`;
  const { error } = await db.storage
    .from('avatar')
    .upload(path, file, { contentType: file.type || 'image/jpeg', upsert: true });
  if (error) return { error: error.message };
  const { data: urlData } = db.storage.from('avatar').getPublicUrl(path);
  return { url: `${urlData.publicUrl}?t=${Date.now()}` };
}

async function uploadTrackImage(file, trackId) {
  if (!db) return { error: 'No Supabase client.' };
  const ext  = (file.name.split('.').pop() || 'jpg').toLowerCase();
  const path = `track-covers/${trackId}.${ext}`;
  const { error } = await db.storage
    .from('avatar')
    .upload(path, file, { contentType: file.type || 'image/jpeg', upsert: true });
  if (error) return { error: error.message };
  const { data: urlData } = db.storage.from('avatar').getPublicUrl(path);
  return { url: `${urlData.publicUrl}?t=${Date.now()}` };
}

async function fetchTrackDays(trackId) {
  const { data } = await db.from('course_days').select('*').eq('course_id', trackId).order('day_number');
  return data || [];
}
// Replace-all save. Also syncs tracks.duration_days to the task count so the
// app's completion trigger (advance_enrollment) marks the track done correctly.
async function saveTrackDays(trackId, days) {
  await db.from('course_days').delete().eq('course_id', trackId);
  if (days.length > 0) {
    const rows = days.map((d, i) => ({
      course_id: trackId, day_number: i + 1, type: d.type, title: d.title || 'Untitled',
      body: d.body || null, scripture_ref: d.scripture_ref || null, scripture_text: d.scripture_text || null,
      parent_prompt: d.parent_prompt || null, est_minutes: d.est_minutes || null, media_url: d.media_url || null,
    }));
    const { error } = await db.from('course_days').insert(rows);
    if (error) return { error: error.message };
  }
  await db.from('courses').update({ duration_days: days.length }).eq('id', trackId);
  return {};
}

// ─── Contributor applications ─────────────────────────────────
async function fetchContributorApplications() {
  const { data, error } = await db
    .from('profiles')
    .select('id, name, photo_url, bio, contributor_status, created_at')
    .in('contributor_status', ['pending', 'approved', 'rejected'])
    .order('created_at', { ascending: false });
  return error ? [] : (data || []);
}
async function approveContributor(profileId) {
  const { error } = await db.from('profiles')
    .update({ contributor_status: 'approved' }).eq('id', profileId);
  return error ? { error: error.message } : {};
}
async function rejectContributor(profileId) {
  const { error } = await db.from('profiles')
    .update({ contributor_status: 'rejected' }).eq('id', profileId);
  return error ? { error: error.message } : {};
}

// ─── Submitted tracks review ──────────────────────────────────
async function fetchSubmittedTracks() {
  const { data, error } = await db
    .from('courses')
    .select('id, title, about, cover_image, age_band, duration_days, status, review_note, created_at, topic_id, author_id, profiles(name, avatar_url)')
    .eq('status', 'submitted')
    .order('created_at', { ascending: true });
  return error ? [] : (data || []);
}
async function publishTrack(courseId) {
  const { error } = await db.from('courses')
    .update({ status: 'published' }).eq('id', courseId);
  return error ? { error: error.message } : {};
}
async function rejectTrackWithNote(courseId, note) {
  const { error } = await db.from('courses')
    .update({ status: 'rejected', review_note: note }).eq('id', courseId);
  return error ? { error: error.message } : {};
}

Object.assign(window, {
  db, fetchOverviewStats, pollOverviewStats,
  adminSignIn, adminSignOut, getAdminSession, checkIsAdmin,
  uploadTopicIcon, uploadTrackImage, uploadAuthorImage,
  fetchTopics, createTopic, updateTopic, deleteTopic,
  fetchTracksByTopic, createTrack, updateTrack, deleteTrack, deleteAllDraftTracks, fetchUsedUnsplashIds,
  fetchTrackDays, saveTrackDays,
  fetchContributorApplications, approveContributor, rejectContributor,
  fetchSubmittedTracks, publishTrack, rejectTrackWithNote,
});