/* Operaciones admin — CRUD en Supabase (requiere sesión admin) */

function requireSb () {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  return sb;
}

function sanitizePhoneForStorage (phone) {
  if (phone == null || String(phone).trim() === '' || phone === '—') return null;
  if (typeof formatPhoneForStorage === 'function') return formatPhoneForStorage(phone);
  return String(phone).trim();
}

function sanitizeStudentPhonePayload (payload = {}) {
  const out = { ...payload };
  if ('tutor_phone' in out) out.tutor_phone = sanitizePhoneForStorage(out.tutor_phone);
  if ('student_phone' in out) out.student_phone = sanitizePhoneForStorage(out.student_phone);
  return out;
}

/** Corrige tutor_phone y student_phone de todos los alumnos (una vez por navegador). */
async function migrateAllStudentPhonesOnce () {
  const sb = requireSb();
  const { data, error } = await sb.from('students').select('id, code, tutor_phone, student_phone');
  if (error) throw error;
  const rows = data || [];
  let updated = 0;
  let unchanged = 0;
  const details = [];

  for (const s of rows) {
    const patch = {};
    const curTutor = s.tutor_phone?.trim() || null;
    const curStudent = s.student_phone?.trim() || null;
    const nextTutor = sanitizePhoneForStorage(s.tutor_phone);
    const nextStudent = sanitizePhoneForStorage(s.student_phone);
    if (curTutor !== nextTutor) patch.tutor_phone = nextTutor;
    if (curStudent !== nextStudent) patch.student_phone = nextStudent;
    if (!Object.keys(patch).length) {
      unchanged += 1;
      continue;
    }
    const { error: uerr } = await sb.from('students').update(patch).eq('id', s.id);
    if (uerr) throw uerr;
    updated += 1;
    details.push({ code: s.code, ...patch });
  }

  if (updated > 0) {
    window.dispatchEvent(new CustomEvent('tecos:students-changed'));
  }
  return { total: rows.length, updated, unchanged, details };
}

function studentCodeConflictMessage (code, err) {
  const msg = String(err?.message || err?.details || '').trim();
  if (/Prueba con\s+TEC\d{3}/i.test(msg)) return msg;
  if (/ya está en uso por:/i.test(msg)) return msg;
  if (msg && msg.includes(code)) return msg;
  if (/ya fue usado|no puede reutilizarse|student_codes_reserved/i.test(msg)) {
    return msg || `El ID ${code} ya fue usado antes. Usa otro número.`;
  }
  if (err?.code === '23505' || /duplicate key.*students_code/i.test(msg)) {
    return `El ID ${code} ya está registrado por otro alumno.`;
  }
  return msg || 'No se pudo validar el ID del alumno.';
}

async function explainStudentCodeConflict (code, err) {
  const base = studentCodeConflictMessage(code, err);
  if (/ya está en uso por:/i.test(base)) return base;
  const norm = typeof normalizeTecStudentCode === 'function'
    ? normalizeTecStudentCode(code)
    : String(code || '').trim().toUpperCase();
  if (!norm) return base;
  try {
    const sb = requireSb();
    const { data: exact } = await sb.from('students')
      .select('code, full_name, status')
      .eq('code', norm)
      .maybeSingle();
    if (exact) {
      return `El ID ${norm} ya existe: ${exact.full_name} (${exact.status}). Ábrelo en Alumnos para editarlo.`;
    }
    const { data: rows } = await sb.from('students').select('code, full_name, status');
    const clash = (rows || []).find((s) => (
      typeof normalizeTecStudentCode === 'function'
        ? normalizeTecStudentCode(s.code) === norm
        : String(s.code || '').toUpperCase() === norm
    ));
    if (clash) {
      return `El ID ${norm} coincide con el alumno «${clash.code}» (${clash.full_name}). Normaliza o edita ese registro.`;
    }
    if (typeof fetchNextStudentCodeDigits === 'function') {
      const next = await fetchNextStudentCodeDigits();
      const nextCode = typeof buildTecStudentCode === 'function'
        ? buildTecStudentCode(next)
        : `TEC${next}`;
      if (nextCode && nextCode !== norm) {
        return `${base} Siguiente ID libre sugerido: ${nextCode}.`;
      }
    }
  } catch (lookupErr) {
    console.warn('[Tecos] explainStudentCodeConflict', lookupErr);
  }
  return base;
}

async function assertStudentCodeAvailable (code, forStudentId = null) {
  const sb = requireSb();
  const { error } = await sb.rpc('assert_student_code_available', {
    p_code: code,
    p_for_student_id: forStudentId,
  });
  if (error) throw new Error(await explainStudentCodeConflict(code, error));
}

async function fetchMyProfile () {
  const sb = requireSb();
  const { data: { user } } = await sb.auth.getUser();
  if (!user) return null;
  const { data, error } = await sb.from('profiles').select('id, email, full_name, role').eq('id', user.id).maybeSingle();
  if (error) throw error;
  return data ? { ...data, email: data.email || user.email } : null;
}

async function updateAdminProfileName (fullName) {
  const sb = requireSb();
  const { data: { user } } = await sb.auth.getUser();
  if (!user) throw new Error('Sesión no válida. Vuelve a iniciar sesión.');
  const name = String(fullName || '').trim();
  if (!name) throw new Error('Escribe el nombre del administrador.');
  const { error } = await sb.from('profiles').update({
    full_name: name,
    updated_at: new Date().toISOString(),
  }).eq('id', user.id);
  if (error) throw error;
  return { full_name: name };
}

async function updateAdminAuthEmail (newEmail) {
  const sb = requireSb();
  const email = String(newEmail || '').trim().toLowerCase();
  if (!email || !email.includes('@')) throw new Error('Correo no válido.');
  const { data, error } = await sb.auth.updateUser({ email });
  if (error) throw error;
  const { data: { user } } = await sb.auth.getUser();
  if (user?.id) {
    await sb.from('profiles').update({ email, updated_at: new Date().toISOString() }).eq('id', user.id);
  }
  return data;
}

async function updateAdminAuthPassword (newPassword) {
  const sb = requireSb();
  const pwd = String(newPassword || '');
  if (pwd.length < 8) throw new Error('La contraseña debe tener al menos 8 caracteres.');
  const { data, error } = await sb.auth.updateUser({ password: pwd });
  if (error) throw error;
  return data;
}

async function lookupStudentCode (code) {
  const sb = requireSb();
  const { data, error } = await sb.rpc('get_student_by_code', { p_student_code: code });
  if (error) throw error;
  return data;
}

async function fetchStudentPortalByCode (code) {
  const sb = requireSb();
  const [studentRes, payRes, ordRes] = await Promise.all([
    sb.rpc('get_student_by_code', { p_student_code: code }),
    sb.rpc('get_student_payments_by_code', { p_student_code: code }),
    sb.rpc('get_student_orders_by_code', { p_student_code: code }),
  ]);
  if (studentRes.error) throw studentRes.error;
  if (payRes.error) throw payRes.error;
  if (ordRes.error) throw ordRes.error;
  if (!studentRes.data) return null;
  const joinedAt = studentRes.data.joined_at;
  const payments = typeof filterPaymentsForJoin === 'function'
    ? filterPaymentsForJoin(payRes.data || [], joinedAt)
    : (payRes.data || []);
  return {
    student: studentRes.data,
    payments,
    orders: ordRes.data || [],
  };
}

async function rollbackStudentCreate (studentId, reason) {
  if (!studentId) throw new Error(reason);
  const sb = requireSb();
  const { error: delErr } = await sb.from('students').delete().eq('id', studentId);
  if (delErr) {
    console.warn('[Tecos] no se pudo deshacer alta de alumno', delErr);
    throw new Error(`${reason} El alumno quedó guardado sin completar el proceso; elimínalo manualmente en la lista o edítalo.`);
  }
  throw new Error(reason);
}

async function createStudent (payload) {
  payload = sanitizeStudentPhonePayload(payload);
  const sb = requireSb();
  const code = typeof buildTecStudentCode === 'function'
    ? buildTecStudentCode(payload.code_digits || payload.code)
    : String(payload.code || '').trim().toUpperCase();
  if (!code) throw new Error('ID inválido. Usa 3 dígitos después de TEC (ej. 001 → TEC001).');

  const portalPwd = payload.portal_password?.trim() || '';
  if (!portalPwd || portalPwd.length < 8) {
    throw new Error('La contraseña temporal debe tener al menos 8 caracteres.');
  }

  const rpcPayload = {
    p_code: code,
    p_full_name: payload.full_name?.trim(),
    p_password: portalPwd,
    p_birth_date: payload.birth_date || null,
    p_category: payload.category?.trim() || null,
    p_status: payload.status === 'inactivo' ? 'inactivo' : 'activo',
    p_joined_at: payload.joined_at || null,
    p_notes: payload.notes?.trim() || null,
    p_tutor_name: payload.tutor_name?.trim() || null,
    p_tutor_phone: payload.tutor_phone?.trim() || null,
    p_tutor_email: payload.tutor_email?.trim() || null,
    p_student_phone: payload.student_phone?.trim() || null,
  };

  const { data: rpcData, error: rpcErr } = await sb.rpc('admin_create_student', rpcPayload);
  if (!rpcErr && rpcData) {
    const parsed = typeof rpcData === 'string' ? JSON.parse(rpcData) : rpcData;
    if (parsed?.ok === false) throw new Error(parsed.error || 'No se pudo crear el alumno.');
    const row = parsed?.student || parsed;
    const joined = row.joined_at || payload.joined_at || new Date().toISOString().slice(0, 10);
    if (payload.prorate_first_month && row.id) {
      await sb.from('students').update({
        prorate_enrollment_month: true,
        prorate_enrollment_unit: payload.prorate_unit === 'week' ? 'week' : 'day',
      }).eq('id', row.id);
      row.prorate_enrollment_month = true;
      row.prorate_enrollment_unit = payload.prorate_unit === 'week' ? 'week' : 'day';
    }
    try {
      const seedOpts = typeof studentProrateSeedOptions === 'function'
        ? studentProrateSeedOptions({ ...row, prorate_first_month: payload.prorate_first_month, prorate_unit: payload.prorate_unit })
        : { prorateFirstMonth: !!payload.prorate_first_month, prorateUnit: payload.prorate_unit === 'week' ? 'week' : 'day' };
      await generateStudentBillingCyclePayments(row.id, joined, {
        onCreate: true,
        paidMonths: payload.paid_months,
        year: new Date().getFullYear(),
        joinedAt: joined,
        ...seedOpts,
      });
    } catch (e) {
      console.warn('[Tecos] mensualidades al crear alumno', e);
    }
    return row;
  }

  const rpcMissing = rpcErr && /admin_create_student|schema cache|PGRST202/i.test(rpcErr.message || '');
  if (rpcErr && !rpcMissing) {
    throw new Error(await explainStudentCodeConflict(code, rpcErr));
  }

  await assertStudentCodeAvailable(code, null);
  const row = {
    code,
    full_name: payload.full_name?.trim(),
    birth_date: payload.birth_date || null,
    category: payload.category?.trim() || null,
    status: payload.status === 'inactivo' ? 'inactivo' : 'activo',
    joined_at: payload.joined_at || null,
    notes: payload.notes?.trim() || null,
    tutor_name: payload.tutor_name?.trim() || null,
    tutor_phone: payload.tutor_phone?.trim() || null,
    tutor_email: payload.tutor_email?.trim() || null,
    student_phone: payload.student_phone?.trim() || null,
    prorate_enrollment_month: !!payload.prorate_first_month,
    prorate_enrollment_unit: payload.prorate_first_month && payload.prorate_unit === 'week' ? 'week' : 'day',
  };
  const { data, error } = await sb.from('students').insert(row).select().single();
  if (error) throw new Error(await explainStudentCodeConflict(code, error));

  const { error: pwErr } = await sb.rpc('admin_set_student_portal_password', {
    p_student_id: data.id,
    p_password: portalPwd,
    p_must_change: true,
  });
  if (pwErr) {
    const msg = pwErr.message || 'No se pudo configurar la contraseña del portal.';
    const hint = /gen_salt/i.test(msg)
      ? 'Ejecuta la migración 041 en Supabase (pgcrypto) e intenta de nuevo.'
      : msg;
    await rollbackStudentCreate(data.id, hint);
  }

  const joined = data.joined_at || payload.joined_at || new Date().toISOString().slice(0, 10);
  try {
    await generateStudentBillingCyclePayments(data.id, joined, {
      onCreate: true,
      paidMonths: payload.paid_months,
      year: new Date().getFullYear(),
      joinedAt: joined,
      prorateFirstMonth: !!payload.prorate_first_month,
      prorateUnit: payload.prorate_unit === 'week' ? 'week' : 'day',
    });
  } catch (e) {
    console.warn('[Tecos] mensualidades al crear alumno', e);
  }
  return data;
}

/** Elimina cobros (incl. pagados) en meses anteriores al ingreso o fuera del periodo activo. */
async function pruneStudentPreJoinPayments (studentId, joinedAt) {
  const sb = requireSb();
  if (!studentId || !joinedAt) return 0;
  if (typeof firstBillableMonthInYear !== 'function') return 0;
  const { data, error } = await sb.from('payments')
    .select('id, due_date')
    .eq('student_id', studentId);
  if (error) throw error;
  const join = typeof parseJoinedMonthStart === 'function' ? parseJoinedMonthStart(joinedAt) : null;
  const ids = (data || []).filter((p) => {
    const d = new Date(p.due_date);
    if (Number.isNaN(d.getTime())) return false;
    const y = d.getFullYear();
    const m = d.getMonth();
    if (join && y < join.getFullYear()) return true;
    if (m < firstBillableMonthInYear(y, joinedAt)) return true;
    return typeof isMonthInBillingCycle === 'function' && !isMonthInBillingCycle(y, m, joinedAt);
  }).map((p) => p.id);
  if (!ids.length) return 0;
  const { error: delErr } = await sb.from('payments').delete().in('id', ids);
  if (delErr) throw delErr;
  return ids.length;
}

/** Quita mensualidades pendientes/vencidas fuera del ciclo de ingreso o en meses futuros (evita “fantasmas”). */
async function pruneStudentOutOfCyclePendingPayments (studentId, joinedAt) {
  const sb = requireSb();
  if (!studentId || typeof isMonthInBillingCycle !== 'function') return 0;
  const now = new Date();
  const cy = now.getFullYear();
  const cm = now.getMonth();
  const { data, error } = await sb.from('payments')
    .select('id, due_date, status')
    .eq('student_id', studentId)
    .in('status', ['pendiente', 'vencido']);
  if (error) throw error;
  const ids = (data || []).filter((p) => {
    const d = new Date(p.due_date);
    if (Number.isNaN(d.getTime())) return true;
    const y = d.getFullYear();
    const m = d.getMonth();
    if (!isMonthInBillingCycle(y, m, joinedAt)) return true;
    return y === cy && m > cm;
  }).map((p) => p.id);
  if (!ids.length) return 0;
  const { error: delErr } = await sb.from('payments').delete().in('id', ids);
  if (delErr) throw delErr;
  return ids.length;
}

/** Solo meses marcados pagados + mensualidad pendiente del mes en curso (cobro por periodo). */
async function ensureStudentCurrentMonthPayment (studentId, options = {}) {
  const sb = requireSb();
  if (!studentId) return 0;
  const settings = options.settings || await fetchAcademySettings();
  const { data: st } = await sb.from('students')
    .select('joined_at, prorate_enrollment_month, prorate_enrollment_unit')
    .eq('id', studentId)
    .maybeSingle();
  const joinedAt = options.joinedAt || st?.joined_at || null;
  const now = new Date();
  const year = now.getFullYear();
  const month = now.getMonth();

  if (typeof isMonthInBillingCycle === 'function' && !isMonthInBillingCycle(year, month, joinedAt)) {
    return 0;
  }

  const { data: existing, error: exErr } = await sb.from('payments')
    .select('id, due_date')
    .eq('student_id', studentId);
  if (exErr) throw exErr;

  const monthKey = `${year}-${month}`;
  const hasMonth = (existing || []).some((p) => {
    const d = new Date(p.due_date);
    return `${d.getFullYear()}-${d.getMonth()}` === monthKey;
  });
  if (hasMonth) return 0;

  const seedOpts = typeof studentProrateSeedOptions === 'function'
    ? studentProrateSeedOptions(st)
    : {
      prorateFirstMonth: !!st?.prorate_enrollment_month,
      prorateUnit: st?.prorate_enrollment_unit === 'week' ? 'week' : 'day',
    };
  const line = typeof paymentAmountAndConceptForSeedMonth === 'function'
    && joinedAt
    && isEnrollmentMonth(year, month, joinedAt)
    && seedOpts.prorateFirstMonth
    ? paymentAmountAndConceptForSeedMonth(year, month, joinedAt, settings, seedOpts)
    : {
      amount: Number(settings.monthly_fee) || 0,
      concept: mensualidadConceptForMonth(year, month),
    };

  const { error } = await sb.from('payments').insert({
    student_id: studentId,
    concept: line.concept,
    amount: line.amount,
    due_date: dueDateForBillingMonth(settings, year, month),
    status: 'pendiente',
    paid_at: null,
  });
  if (error) throw error;
  return 1;
}

async function seedStudentPaymentsOnCreate (studentId, options = {}) {
  const sb = requireSb();
  if (!studentId) return 0;
  const settings = options.settings || await fetchAcademySettings();
  const joinedAt = options.joinedAt || null;
  const year = options.year || new Date().getFullYear();
  const nowMonth = new Date().getMonth();
  const first = typeof firstBillableMonthInYear === 'function'
    ? firstBillableMonthInYear(year, joinedAt)
    : 0;
  const inCycle = (i) => (
    i >= first
    && (typeof isMonthInBillingCycle !== 'function' || isMonthInBillingCycle(year, i, joinedAt))
  );
  const paidSet = new Set(
    (options.paidMonths || []).map((m) => Number(m)).filter((m) => m >= 0 && m <= 11 && inCycle(m))
  );
  const seedPayOpts = {
    prorateFirstMonth: !!options.prorateFirstMonth,
    prorateUnit: options.prorateUnit === 'week' ? 'week' : 'day',
  };
  const lineForMonth = (monthIndex) => (
    typeof paymentAmountAndConceptForSeedMonth === 'function'
      ? paymentAmountAndConceptForSeedMonth(year, monthIndex, joinedAt, settings, seedPayOpts)
      : { amount: Number(settings.monthly_fee) || 0, concept: mensualidadConceptForMonth(year, monthIndex) }
  );

  const { data: existing, error: exErr } = await sb.from('payments')
    .select('id, due_date')
    .eq('student_id', studentId);
  if (exErr) throw exErr;

  const existingKeys = new Set((existing || []).map((p) => {
    const d = new Date(p.due_date);
    return `${d.getFullYear()}-${d.getMonth()}`;
  }));

  const toInsert = [];
  paidSet.forEach((i) => {
    if (!inCycle(i)) return;
    const key = `${year}-${i}`;
    if (existingKeys.has(key)) return;
    const dueDate = dueDateForBillingMonth(settings, year, i);
    const { amount, concept } = lineForMonth(i);
    toInsert.push({
      student_id: studentId,
      concept,
      amount,
      due_date: dueDate,
      status: 'pagado',
      paid_at: dueDate,
    });
  });

  const pendingMonths = new Set();
  if (inCycle(nowMonth)) pendingMonths.add(nowMonth);
  const joinDate = typeof parseJoinDateLocal === 'function' ? parseJoinDateLocal(joinedAt) : null;
  if (joinDate && joinDate.getFullYear() === year && inCycle(joinDate.getMonth())) {
    pendingMonths.add(joinDate.getMonth());
  }

  pendingMonths.forEach((i) => {
    if (paidSet.has(i) || existingKeys.has(`${year}-${i}`)) return;
    const dueDate = dueDateForBillingMonth(settings, year, i);
    const { amount, concept } = lineForMonth(i);
    toInsert.push({
      student_id: studentId,
      concept,
      amount,
      due_date: dueDate,
      status: 'pendiente',
      paid_at: null,
    });
  });

  if (!toInsert.length) return 0;
  const { error } = await sb.from('payments').insert(toInsert);
  if (error) throw error;
  return toInsert.length;
}

async function generateStudentBillingCyclePayments (studentId, joinedAt, options = {}) {
  const merged = { ...options, joinedAt: options.joinedAt || joinedAt };
  if (options.onCreate || options.paidMonths != null) {
    const n = await seedStudentPaymentsOnCreate(studentId, merged);
    await pruneStudentPreJoinPayments(studentId, joinedAt);
    await pruneStudentOutOfCyclePendingPayments(studentId, joinedAt);
    return n;
  }
  const n = await ensureStudentCurrentMonthPayment(studentId, merged);
  await pruneStudentOutOfCyclePendingPayments(studentId, joinedAt);
  return n;
}

async function updateStudent (uuid, payload) {
  payload = sanitizeStudentPhonePayload(payload);
  const sb = requireSb();
  const row = {
    full_name: payload.full_name?.trim(),
    birth_date: payload.birth_date || null,
    category: payload.category?.trim() || null,
    status: payload.status || 'activo',
    joined_at: payload.joined_at || null,
    notes: payload.notes?.trim() || null,
    tutor_name: payload.tutor_name?.trim() || null,
    tutor_phone: payload.tutor_phone?.trim() || null,
    tutor_email: payload.tutor_email?.trim() || null,
    student_phone: payload.student_phone?.trim() || null,
    photo_path: payload.photo_path?.trim() || null,
  };
  if (payload.code_digits || payload.code) {
    const c = typeof buildTecStudentCode === 'function'
      ? buildTecStudentCode(payload.code_digits || payload.code)
      : String(payload.code).trim().toUpperCase();
    if (c) {
      const current = await sb.from('students').select('code').eq('id', uuid).maybeSingle();
      if (current.error) throw current.error;
      if (c !== current.data?.code) await assertStudentCodeAvailable(c, uuid);
      row.code = c;
    }
  }
  if (payload.portal_password?.trim()) {
    await sb.rpc('admin_set_student_portal_password', {
      p_student_id: uuid,
      p_password: payload.portal_password.trim(),
      p_must_change: payload.portal_must_change !== false,
    });
  }
  const { data, error } = await sb.from('students').update(row).eq('id', uuid).select().single();
  if (error) throw new Error(await explainStudentCodeConflict(row.code || payload.code, error));
  if (data?.joined_at) {
    try {
      await pruneStudentPreJoinPayments(uuid, data.joined_at);
      await pruneStudentOutOfCyclePendingPayments(uuid, data.joined_at);
      await generateStudentBillingCyclePayments(uuid, data.joined_at, { joinedAt: data.joined_at });
    } catch (e) {
      console.warn('[Tecos] mensualidades tras editar alumno', e);
    }
  }
  return data;
}

async function deleteStudent (uuid) {
  const sb = requireSb();
  let deletedCode = null;
  const { data: row } = await sb.from('students').select('code').eq('id', uuid).maybeSingle();
  deletedCode = row?.code || null;
  const { error } = await sb.from('students').delete().eq('id', uuid);
  if (error) throw error;
  if (typeof notifyStudentsChanged === 'function') {
    notifyStudentsChanged({ deletedUuid: uuid, deletedCode });
  } else {
    window.dispatchEvent(new CustomEvent('tecos:students-changed', {
      detail: { deletedUuid: uuid, deletedCode },
    }));
  }
  if (typeof notifyPaymentsChanged === 'function') notifyPaymentsChanged();
}

async function fetchStudentDocuments (studentId) {
  const sb = requireSb();
  const { data, error } = await sb.rpc('list_student_documents', { p_student_id: studentId });
  if (error) throw error;
  return data || [];
}

async function fetchStudentsDocumentCounts () {
  const sb = requireSb();
  const { data, error } = await sb.rpc('list_students_document_counts');
  if (error) {
    if (/list_students_document_counts|schema cache|PGRST202/i.test(error.message || '')) return {};
    throw error;
  }
  const map = {};
  const fallbackTotal = typeof STUDENT_DOCUMENT_TYPES !== 'undefined' ? STUDENT_DOCUMENT_TYPES.length : 10;
  (data || []).forEach((row) => {
    if (!row?.student_id) return;
    map[row.student_id] = {
      loaded: Number(row.loaded_count) || 0,
      required: Number(row.required_count) || fallbackTotal,
    };
  });
  return map;
}

async function fetchStudentDocumentRequirements (studentId) {
  const sb = requireSb();
  const { data, error } = await sb.from('students')
    .select('document_requirements')
    .eq('id', studentId)
    .maybeSingle();
  if (error) throw error;
  return typeof mergeDocumentRequirements === 'function'
    ? mergeDocumentRequirements(data?.document_requirements)
    : (data?.document_requirements || {});
}

async function saveStudentDocumentRequirements (studentId, requirements) {
  const sb = requireSb();
  const { error } = await sb.from('students')
    .update({ document_requirements: requirements })
    .eq('id', studentId);
  if (error) throw error;
  if (typeof notifyStudentDocumentsChanged === 'function') {
    notifyStudentDocumentsChanged({ studentId });
  } else {
    window.dispatchEvent(new CustomEvent('tecos:student-documents-changed', { detail: { studentId } }));
  }
}

async function uploadStudentDocument ({ studentId, studentCode, docType, file, label }) {
  const sb = requireSb();
  const ext = (file.name.split('.').pop() || 'pdf').toLowerCase();
  const path = await uploadGalleryFile(file, `student-docs/${studentCode}/${docType}`);
  const { data, error } = await sb.rpc('upsert_student_document', {
    p_student_id: studentId,
    p_doc_type: docType,
    p_storage_path: path,
    p_file_name: file.name,
    p_mime_type: file.type || null,
    p_label: label || null,
  });
  if (error) throw error;
  if (typeof notifyStudentDocumentsChanged === 'function') {
    notifyStudentDocumentsChanged({ studentId, studentCode, docType });
  } else {
    window.dispatchEvent(new CustomEvent('tecos:student-documents-changed', {
      detail: { studentId, studentCode, docType },
    }));
  }
  return { id: data, path };
}

async function removeStudentDocument (documentId) {
  const sb = requireSb();
  const { data: oldPath, error } = await sb.rpc('delete_student_document', { p_document_id: documentId });
  if (error) throw error;
  if (oldPath) {
    try { await sb.storage.from('gallery').remove([oldPath]); } catch (e) { console.warn('[Tecos] doc storage', e); }
  }
  if (typeof notifyStudentDocumentsChanged === 'function') {
    notifyStudentDocumentsChanged({ documentId });
  } else {
    window.dispatchEvent(new CustomEvent('tecos:student-documents-changed', { detail: { documentId } }));
  }
}

async function createPayment (payload) {
  const sb = requireSb();
  const { data, error } = await sb.from('payments').insert({
    student_id: payload.student_id,
    concept: payload.concept?.trim() || 'Mensualidad',
    amount: Number(payload.amount) || 0,
    due_date: payload.due_date,
    status: payload.status || 'pendiente',
    transfer_reference: payload.transfer_reference?.trim() || null,
  }).select().single();
  if (error) throw error;
  return data;
}

async function updatePayment (id, payload) {
  const sb = requireSb();
  const row = {};
  if (payload.concept != null) row.concept = payload.concept;
  if (payload.amount != null) row.amount = Number(payload.amount);
  if (payload.due_date != null) row.due_date = payload.due_date;
  if (payload.status != null) row.status = payload.status;
  if (payload.paid_at !== undefined) row.paid_at = payload.paid_at;
  const { data, error } = await sb.from('payments').update(row).eq('id', id).select().single();
  if (error) throw error;
  return data;
}

async function deletePayment (id) {
  const sb = requireSb();
  const { error } = await sb.from('payments').delete().eq('id', id);
  if (error) throw error;
}

async function markPaymentPaid (id) {
  return updatePayment(id, { status: 'pagado', paid_at: new Date().toISOString() });
}

/** Crea o devuelve la mensualidad pendiente de un mes (para subir comprobante desde el portal). */
async function ensurePaymentForBillingMonth (studentId, year, monthIndex, options = {}) {
  const sb = requireSb();
  if (!studentId || monthIndex < 0 || monthIndex > 11) {
    throw new Error('Datos de alumno o mes inválidos.');
  }
  const settings = options.settings || await fetchAcademySettings();
  const monthKey = `${year}-${monthIndex}`;

  const { data: existing, error: exErr } = await sb.from('payments')
    .select('id, status, due_date, amount, concept')
    .eq('student_id', studentId);
  if (exErr) throw exErr;

  const row = (existing || []).find((p) => {
    const d = new Date(p.due_date);
    return `${d.getFullYear()}-${d.getMonth()}` === monthKey;
  });

  if (row) {
    if (row.status === 'pagado') throw new Error('Este mes ya está pagado.');
    return row;
  }

  const dueDate = dueDateForBillingMonth(settings, year, monthIndex);
  const base = Number(settings.monthly_fee) || 0;
  const isAdvance = typeof isFutureBillingMonth === 'function' && isFutureBillingMonth(year, monthIndex);

  let studentCode = options.studentCode;
  let joinedAt = options.joinedAt || null;
  let prorateFlags = null;
  if (studentId) {
    const { data: st } = await sb.from('students')
      .select('code, joined_at, prorate_enrollment_month, prorate_enrollment_unit')
      .eq('id', studentId)
      .maybeSingle();
    studentCode = studentCode || st?.code;
    joinedAt = joinedAt || st?.joined_at;
    prorateFlags = st;
  }

  let concept = mensualidadConceptForMonth(year, monthIndex);
  let amount = options.amount != null && options.amount !== ''
    ? Number(options.amount)
    : null;
  if (amount == null) {
    const seedOpts = typeof studentProrateSeedOptions === 'function'
      ? studentProrateSeedOptions(prorateFlags)
      : { prorateFirstMonth: false, prorateUnit: 'day' };
    if (
      joinedAt
      && isEnrollmentMonth(year, monthIndex, joinedAt)
      && seedOpts.prorateFirstMonth
      && typeof paymentAmountAndConceptForSeedMonth === 'function'
    ) {
      const line = paymentAmountAndConceptForSeedMonth(year, monthIndex, joinedAt, settings, seedOpts);
      concept = line.concept;
      amount = line.amount;
    } else if (isAdvance) {
      amount = base;
    } else {
      amount = typeof computeLateFees === 'function'
        ? computeLateFees(base, dueDate, settings).total
        : base;
    }
  }
  const normalized = typeof normalizeTecStudentCode === 'function'
    ? normalizeTecStudentCode(studentCode)
    : String(studentCode || '').trim().toUpperCase();

  if (normalized) {
    const { data: ensured, error: rpcErr } = await sb.rpc('ensure_payment_for_billing_month_by_code', {
      p_student_code: normalized,
      p_year: year,
      p_month_index: monthIndex,
      p_amount: amount,
    });
    if (rpcErr) {
      const msg = rpcErr.message || '';
      if (/ensure_payment_for_billing_month_by_code|schema cache|PGRST202/i.test(msg)) {
        throw new Error(
          'Falta la migración 045_student_payment_receipt_portal.sql en Supabase. Ejecútala en SQL Editor y vuelve a intentar.'
        );
      }
      throw rpcErr;
    }
    const parsed = typeof ensured === 'string' ? JSON.parse(ensured) : ensured;
    return {
      id: parsed.id,
      status: parsed.status,
      concept: parsed.concept,
      amount: parsed.amount,
      due_date: parsed.due_date,
    };
  }

  const { data, error } = await sb.from('payments').insert({
    student_id: studentId,
    concept,
    amount,
    due_date: dueDate,
    status: 'pendiente',
    paid_at: null,
  }).select('id, status, concept, amount, due_date').single();
  if (error) throw error;
  return data;
}

/** Registra pago de un mes (efectivo/admin): crea fila pagada o marca pendiente como pagado. */
async function registerStudentMonthPayment (studentId, year, monthIndex, options = {}) {
  const sb = requireSb();
  if (!studentId || monthIndex < 0 || monthIndex > 11) {
    throw new Error('Datos de alumno o mes inválidos.');
  }
  const joinedAt = options.joinedAt || null;
  if (joinedAt && typeof firstBillableMonthInYear === 'function') {
    const first = firstBillableMonthInYear(year, joinedAt);
    if (monthIndex < first) {
      throw new Error('No se puede cobrar un mes anterior a la fecha de ingreso del alumno.');
    }
    if (typeof isMonthInBillingCycle === 'function' && !isMonthInBillingCycle(year, monthIndex, joinedAt)) {
      throw new Error('Este mes está fuera del ciclo de mensualidades del alumno.');
    }
  }
  const settings = options.settings || await fetchAcademySettings();
  const monthKey = `${year}-${monthIndex}`;

  const { data: existing, error: exErr } = await sb.from('payments')
    .select('id, status, amount, due_date, receipt_path')
    .eq('student_id', studentId);
  if (exErr) throw exErr;

  const row = (existing || []).find((p) => {
    const d = new Date(p.due_date);
    return `${d.getFullYear()}-${d.getMonth()}` === monthKey;
  });

  if (row?.status === 'pagado') throw new Error('Este mes ya está registrado como pagado.');
  if (row?.status === 'en_revision' && row.receipt_path) {
    throw new Error('Hay un comprobante en revisión. Apruébalo en Verificar comprobantes.');
  }

  const nowIso = new Date().toISOString();
  const dueDate = dueDateForBillingMonth(settings, year, monthIndex);
  const base = Number(settings.monthly_fee) || 0;
  const isAdvance = options.advancePay === true
    || (typeof isFutureBillingMonth === 'function' && isFutureBillingMonth(year, monthIndex));
  const fees = isAdvance
    ? { total: base }
    : (typeof computeLateFees === 'function'
      ? computeLateFees(base, dueDate, settings)
      : { total: base });

  const payAmount = options.amount != null ? Number(options.amount) : fees.total;
  const finalAmount = payAmount > 0 ? payAmount : base;

  if (row) {
    return updatePayment(row.id, {
      status: 'pagado',
      paid_at: nowIso,
      amount: finalAmount,
    });
  }

  const { data, error } = await sb.from('payments').insert({
    student_id: studentId,
    concept: mensualidadConceptForMonth(year, monthIndex),
    amount: finalAmount,
    due_date: dueDate,
    status: 'pagado',
    paid_at: nowIso,
  }).select().single();
  if (error) throw error;
  return data;
}

async function fetchPaymentDetailAdmin (paymentId) {
  const sb = requireSb();
  const { data, error } = await sb.from('payments')
    .select(`
      id, concept, amount, due_date, paid_at, status, receipt_path,
      transfer_reference, admin_notes, created_at, updated_at,
      students (
        id, code, full_name, birth_date, category, status, joined_at,
        tutor_name, tutor_phone, tutor_email, student_phone, photo_path
      )
    `)
    .eq('id', paymentId)
    .single();
  if (error) throw error;
  return data;
}

async function upsertAnnouncement (payload, id) {
  const sb = requireSb();
  const row = {
    title: payload.title?.trim(),
    body: payload.body?.trim(),
    priority: payload.priority || 'normal',
    published_at: payload.published_at || null,
    starts_at: payload.starts_at || null,
    ends_at: payload.ends_at || null,
    multi_day: payload.multi_day === true,
    location: payload.location?.trim() || null,
    maps_url: payload.maps_url?.trim() || null,
    image_path: payload.image_path?.trim() || null,
    image_focus_x: Math.max(0, Math.min(100, Number(payload.image_focus_x) || 50)),
    image_focus_y: Math.max(0, Math.min(100, Number(payload.image_focus_y) || 50)),
  };
  const write = async (body) => {
    if (id) return sb.from('announcements').update(body).eq('id', id).select().single();
    return sb.from('announcements').insert(body).select().single();
  };
  let { data, error } = await write(row);
  if (error && /starts_at|ends_at|multi_day|maps_url|image_path|image_focus|location|column/i.test(error.message || '')) {
    throw new Error(
      'Faltan columnas de anuncios en Supabase. Ejecuta supabase/migrations/025_announcements_extended.sql en el SQL Editor y vuelve a guardar. '
      + `(Detalle: ${error.message})`
    );
  }
  if (error) throw error;
  if (typeof notifyAnnouncementsChanged === 'function') notifyAnnouncementsChanged();
  return data;
}

async function deleteAnnouncement (id) {
  const sb = requireSb();
  const { error } = await sb.from('announcements').delete().eq('id', id);
  if (error) throw error;
  if (typeof notifyAnnouncementsChanged === 'function') notifyAnnouncementsChanged();
}

async function fetchAnnouncementRegistrations (announcementId) {
  const sb = requireSb();
  const { data, error } = await sb.from('announcement_registrations')
    .select('id, announcement_id, student_id, student_code, student_name, registered_at')
    .eq('announcement_id', announcementId)
    .order('registered_at', { ascending: false });
  if (error && /announcement_registrations/i.test(error.message || '')) return [];
  if (error) throw error;
  return data || [];
}

async function deleteAnnouncementRegistration (id) {
  const sb = requireSb();
  const { error } = await sb.from('announcement_registrations').delete().eq('id', id);
  if (error) throw error;
}

/** Interesados (no alumnos) vinculados a un aviso o evento */
async function fetchInterestedLeadsForContext ({ announcementId, eventId }) {
  const sb = requireSb();
  if (!announcementId && !eventId) return [];
  let q = sb.from('interested_leads')
    .select('id, name, phone, email, message, status, source, created_at')
    .order('created_at', { ascending: false });
  if (announcementId) q = q.eq('announcement_id', announcementId);
  else q = q.eq('event_id', eventId);
  const { data, error } = await q;
  if (error && /announcement_id|event_id/i.test(error.message || '')) return [];
  if (error) throw error;
  return data || [];
}

async function fetchStudentBriefForReceipt (studentId) {
  const sb = requireSb();
  if (!studentId) return null;
  const { data, error } = await sb.from('students')
    .select('id, code, full_name, category, tutor_name, tutor_phone, photo_path')
    .eq('id', studentId)
    .maybeSingle();
  if (error) return null;
  return data;
}

async function upsertEvent (payload, id) {
  const sb = requireSb();
  const row = {
    title: payload.title?.trim(),
    description: payload.description?.trim() || null,
    starts_at: payload.starts_at,
    ends_at: payload.ends_at || null,
    location: payload.location?.trim() || null,
    category: payload.category?.trim() || 'general',
    maps_url: payload.maps_url?.trim() || null,
    multi_day: payload.multi_day === true,
    image_path: payload.image_path?.trim() || null,
    image_focus_x: Math.max(0, Math.min(100, Number(payload.image_focus_x) || 50)),
    image_focus_y: Math.max(0, Math.min(100, Number(payload.image_focus_y) || 50)),
  };
  const write = async (body) => {
    if (id) return sb.from('events').update(body).eq('id', id).select().single();
    return sb.from('events').insert(body).select().single();
  };
  let { data, error } = await write(row);
  if (error && /image_path|maps_url|multi_day|image_focus|column/i.test(error.message || '')) {
    const slim = { ...row };
    delete slim.image_path;
    delete slim.maps_url;
    delete slim.multi_day;
    delete slim.image_focus_x;
    delete slim.image_focus_y;
    ({ data, error } = await write(slim));
  }
  if (error) throw error;
  return data;
}

async function fetchEventsAdmin () {
  const sb = requireSb();
  const { data, error } = await sb.from('events')
    .select('*')
    .order('starts_at', { ascending: false });
  if (error) throw error;
  return data || [];
}

async function fetchEventConfirmations (eventId) {
  const sb = requireSb();
  const { data, error } = await sb.from('event_confirmations')
    .select('id, event_id, student_id, student_code, student_name, confirmed_at')
    .eq('event_id', eventId)
    .order('confirmed_at', { ascending: false });
  if (error && /event_confirmations/i.test(error.message || '')) return [];
  if (error) throw error;
  return data || [];
}

async function deleteEventConfirmation (id) {
  const sb = requireSb();
  const { error } = await sb.from('event_confirmations').delete().eq('id', id);
  if (error) throw error;
}

async function deleteEvent (id) {
  const sb = requireSb();
  const { error } = await sb.from('events').delete().eq('id', id);
  if (error) throw error;
}

async function fetchAllProducts () {
  const sb = requireSb();
  const { data, error } = await sb.from('products').select('*').order('name');
  if (error) throw error;
  return data || [];
}

/** Historial de ventas que descontaron inventario (por talla). */
async function fetchInventoryMovements ({ productId = null, limit = 150 } = {}) {
  const sb = requireSb();
  let q = sb.from('inventory_movements')
    .select(`
      id, product_id, product_name, product_sku, size,
      qty_delta, stock_before, stock_after, movement_type,
      order_id, order_number, student_code, student_name,
      notes, created_at
    `)
    .order('created_at', { ascending: false })
    .limit(Math.min(Math.max(limit, 1), 300));

  if (productId) q = q.eq('product_id', productId);

  const { data, error } = await q;
  if (error) {
    if (/inventory_movements|schema cache|PGRST205/i.test(error.message || '')) {
      return fetchInventoryMovementsFromOrders({ productId, limit });
    }
    throw error;
  }
  return data || [];
}

/** Vacía el historial de movimientos (compras/descuentos). No modifica stock ni productos. */
/** Reinicio total: borra datos operativos de todos los módulos (requiere confirmación BORRAR TODO). */
/** Borra pagos, órdenes e historial de inventario (dashboard en ceros). Conserva alumnos y catálogo. */
async function adminClearBillingStoreHistory (confirmation) {
  const sb = requireSb();
  const { data, error } = await sb.rpc('admin_clear_billing_store_history', {
    p_confirmation: String(confirmation || '').trim(),
  });
  if (error) {
    if (/admin_clear_billing_store_history|schema cache|PGRST202/i.test(error.message || '')) {
      throw new Error(
        'Función no disponible. Ejecuta la migración 070_admin_clear_billing_store_history_finance.sql en Supabase.'
      );
    }
    throw error;
  }
  return data || { ok: true };
}

async function adminFactoryReset (confirmation) {
  const sb = requireSb();
  const { data, error } = await sb.rpc('admin_factory_reset', {
    p_confirmation: String(confirmation || '').trim(),
  });
  if (error) {
    if (/admin_factory_reset|schema cache|PGRST202/i.test(error.message || '')) {
      throw new Error(
        'Función no disponible. Ejecuta la migración 068_admin_factory_reset.sql en Supabase (SQL Editor).'
      );
    }
    throw error;
  }
  return data || { ok: true };
}

async function resetInventoryMovementsHistory () {
  const sb = requireSb();
  const { data, error } = await sb.rpc('admin_reset_inventory_movements');
  if (error) {
    if (/admin_reset_inventory_movements|schema cache|PGRST202/i.test(error.message || '')) {
      throw new Error(
        'Función no disponible. Ejecuta la migración 067_admin_reset_inventory_movements.sql en Supabase (SQL Editor).'
      );
    }
    throw error;
  }
  const deleted = data?.deleted ?? data?.Deleted ?? 0;
  return { deleted: Number(deleted) || 0 };
}

/** Respaldo si la migración 051 aún no está aplicada. */
async function fetchInventoryMovementsFromOrders ({ productId = null, limit = 150 } = {}) {
  const sb = requireSb();
  const { data: orders, error } = await sb.from('orders')
    .select(`
      id, order_number, status, guest_student_code, guest_student_name,
      reviewed_at, updated_at, created_at,
      order_items (
        id, product_id, product_name, size, qty, unit_price, line_total
      )
    `)
    .in('status', ['confirmado', 'entregado'])
    .order('created_at', { ascending: false })
    .limit(80);
  if (error) throw error;

  const rows = [];
  (orders || []).forEach((o) => {
    const at = o.reviewed_at || o.updated_at || o.created_at;
    (o.order_items || []).forEach((it) => {
      if (productId && it.product_id !== productId) return;
      if (!it.product_id) return;
      rows.push({
        id: `${o.id}-${it.id}`,
        product_id: it.product_id,
        product_name: it.product_name,
        product_sku: null,
        size: it.size || '—',
        qty_delta: -(Number(it.qty) || 0),
        stock_before: null,
        stock_after: null,
        movement_type: 'venta',
        order_id: o.id,
        order_number: o.order_number,
        student_code: o.guest_student_code,
        student_name: o.guest_student_name,
        notes: null,
        created_at: at,
      });
    });
  });
  return rows.slice(0, limit);
}

function buildProductStockRow (payload) {
  const stockBySize = payload.stock_by_size && typeof payload.stock_by_size === 'object'
    ? payload.stock_by_size
    : {};
  const sizeKeys = ['6', '8', '10', '12', '14', '16', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL'];
  const sizes = sizeKeys.filter(s => (Number(stockBySize[s]) || 0) > 0);
  const finalSizes = sizes.length ? sizes : ['Único'];
  const stock = Object.values(stockBySize).reduce((a, b) => a + (Number(b) || 0), 0);
  return {
    sku: payload.sku?.trim() || null,
    name: payload.name?.trim(),
    category: payload.category?.trim() || 'General',
    price: Number(payload.price) || 0,
    sizes: finalSizes,
    stock_by_size: stockBySize,
    stock,
  };
}

async function writeProductRow (sb, row, id) {
  const write = async (body) => {
    if (id) return sb.from('products').update(body).eq('id', id).select().single();
    return sb.from('products').insert(body).select().single();
  };
  let { data, error } = await write(row);
  if (error && /stock_by_size|column/i.test(error.message || '')) {
    const { stock_by_size, ...legacyRow } = row;
    ({ data, error } = await write(legacyRow));
  }
  if (error && /show_on_landing|cover_focus|gallery_paths|column/i.test(error.message || '')) {
    const slim = { ...row };
    delete slim.show_on_landing;
    delete slim.cover_focus_x;
    delete slim.cover_focus_y;
    delete slim.gallery_paths;
    ({ data, error } = await write(slim));
  }
  if (error) throw error;
  return data;
}

/** Inventario: datos de producto y stock (aparece en módulo Tienda online para configurar vitrina). */
async function upsertProductInventory (payload, id) {
  const sb = requireSb();
  const row = buildProductStockRow(payload);
  if (!id) {
    row.active = false;
    row.show_on_landing = false;
  }
  return writeProductRow(sb, row, id || null);
}

/** Tienda online (admin): fotos, encuadre y visibilidad en landing / tienda completa. */
async function updateProductStore (payload, id) {
  const sb = requireSb();
  if (!id) throw new Error('Selecciona un producto del inventario.');
  const galleryPaths = Array.isArray(payload.gallery_paths)
    ? payload.gallery_paths.filter(Boolean)
    : [];
  const row = {
    image_path: payload.image_path?.trim() || null,
    gallery_paths: galleryPaths,
    cover_focus_x: Math.max(0, Math.min(100, Number(payload.cover_focus_x) || 50)),
    cover_focus_y: Math.max(0, Math.min(100, Number(payload.cover_focus_y) || 50)),
    active: payload.active !== false && payload.active !== 'false',
    show_on_landing: payload.show_on_landing === true,
  };
  return writeProductRow(sb, row, id);
}

async function upsertProduct (payload, id) {
  const sb = requireSb();
  const row = {
    ...buildProductStockRow(payload),
    image_path: payload.image_path?.trim() || null,
    active: payload.active !== false && payload.active !== 'false',
    show_on_landing: payload.show_on_landing === true,
    cover_focus_x: Math.max(0, Math.min(100, Number(payload.cover_focus_x) || 50)),
    cover_focus_y: Math.max(0, Math.min(100, Number(payload.cover_focus_y) || 50)),
    gallery_paths: Array.isArray(payload.gallery_paths) ? payload.gallery_paths.filter(Boolean) : [],
  };
  if (!id) {
    row.active = payload.active !== false && payload.active !== 'false';
    row.show_on_landing = payload.show_on_landing === true;
  }
  return writeProductRow(sb, row, id || null);
}

async function deleteProduct (id) {
  const sb = requireSb();
  const { error } = await sb.from('products').delete().eq('id', id);
  if (error) throw error;
}

async function updateOrderStatus (id, status) {
  const sb = requireSb();
  const { data, error } = await sb.from('orders').update({ status }).eq('id', id).select().single();
  if (error) throw error;
  return data;
}

async function deleteOrder (id) {
  const sb = requireSb();
  const { error } = await sb.from('orders').delete().eq('id', id);
  if (error) throw error;
}

async function upsertCoach (payload, id) {
  const sb = requireSb();
  const base = {
    name: payload.name?.trim(),
    specialty: payload.specialty?.trim() || null,
    experience_years: payload.experience_years?.trim() || null,
    card_summary: payload.card_summary?.trim() || null,
    trajectory: payload.trajectory?.trim() || null,
    bio: payload.trajectory?.trim() || payload.bio?.trim() || null,
    certifications: payload.certifications?.trim() || null,
    assigned_groups: payload.assigned_groups?.trim() || null,
    icon: payload.icon?.trim() || 'whistle',
    photo_path: payload.photo_path?.trim() || null,
    sort_order: Number(payload.sort_order) || 0,
    active: payload.active !== false,
  };
  const focus = {
    photo_focus_x: Math.max(0, Math.min(100, Number(payload.photo_focus_x) || 50)),
    photo_focus_y: Math.max(0, Math.min(100, Number(payload.photo_focus_y) || 50)),
  };
  const row = { ...base, ...focus };

  const write = async (r) => {
    if (id) {
      return sb.from('coaches').update(r).eq('id', id).select().single();
    }
    return sb.from('coaches').insert(r).select().single();
  };

  let { data, error } = await write(row);
  if (error && /photo_focus/i.test(error.message || '')) {
    ({ data, error } = await write(base));
    if (!error) {
      console.warn('[Tecos] Ejecuta la migración 021_coaches_photo_focus.sql para guardar el encuadre de fotos.');
    }
  }
  if (error) throw error;
  return data;
}

async function deleteCoach (id) {
  const sb = requireSb();
  const { error } = await sb.from('coaches').delete().eq('id', id);
  if (error) throw error;
}

async function upsertGalleryItem (payload, id) {
  const sb = requireSb();
  const row = {
    title: payload.title?.trim() || null,
    storage_path: payload.storage_path?.trim(),
    thumb_path: payload.thumb_path?.trim() || null,
    category: payload.category?.trim() || 'general',
    album_id: payload.album_id || null,
    is_cover: !!payload.is_cover,
    sort_order: Number(payload.sort_order) || 0,
  };
  if (id) {
    const { data, error } = await sb.from('gallery_items').update(row).eq('id', id).select().single();
    if (error) throw error;
    return data;
  }
  const { data, error } = await sb.from('gallery_items').insert(row).select().single();
  if (error) throw error;
  return data;
}

async function deleteGalleryItem (id) {
  const sb = requireSb();
  const { error } = await sb.from('gallery_items').delete().eq('id', id);
  if (error) throw error;
}

function slugifyGalleryCategory (name) {
  return String(name || '')
    .toLowerCase()
    .normalize('NFD')
    .replace(/[\u0300-\u036f]/g, '')
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/^-|-$/g, '') || 'general';
}

async function fetchGalleryCategories () {
  const sb = requireSb();
  const { data, error } = await sb.from('gallery_categories').select('*').order('sort_order');
  if (error) throw error;
  return data || [];
}

async function createGalleryCategory (name) {
  const sb = requireSb();
  const slug = slugifyGalleryCategory(name);
  const { data, error } = await sb.from('gallery_categories').insert({
    name: name.trim(),
    slug,
    sort_order: 99,
  }).select().single();
  if (error) throw error;
  return data;
}

async function fetchGalleryAlbumsAdmin () {
  const sb = requireSb();
  const { data: albums, error } = await sb.from('gallery_albums')
    .select('*, gallery_categories(id, name, slug), events(id, title, starts_at)')
    .order('sort_order')
    .order('created_at', { ascending: false });
  if (error) throw error;
  const { data: photos, error: pErr } = await sb.from('gallery_items')
    .select('id, album_id, storage_path, is_cover, sort_order')
    .not('album_id', 'is', null);
  if (pErr) throw pErr;
  const byAlbum = {};
  (photos || []).forEach(p => {
    if (!byAlbum[p.album_id]) byAlbum[p.album_id] = [];
    byAlbum[p.album_id].push(p);
  });
  return (albums || []).map(a => ({
    ...a,
    photos: (byAlbum[a.id] || []).sort((x, y) => x.sort_order - y.sort_order),
    photoCount: (byAlbum[a.id] || []).length,
  }));
}

async function fetchGalleryAlbumById (albumId) {
  const sb = requireSb();
  const { data: album, error } = await sb.from('gallery_albums')
    .select('*, gallery_categories(id, name, slug), events(id, title, starts_at)')
    .eq('id', albumId)
    .single();
  if (error) throw error;
  const { data: photos, error: pErr } = await sb.from('gallery_items')
    .select('id, storage_path, is_cover, sort_order, title')
    .eq('album_id', albumId)
    .order('sort_order');
  if (pErr) throw pErr;
  return { ...album, photos: photos || [] };
}

async function saveGalleryAlbum (payload, id) {
  const sb = requireSb();
  const row = {
    title: payload.title?.trim(),
    category_id: payload.category_id,
    event_id: payload.event_id || null,
    album_date: payload.album_date || null,
    description: payload.description?.trim() || null,
    cover_storage_path: payload.cover_storage_path || null,
    sort_order: Number(payload.sort_order) || 0,
    published: payload.published !== false,
    cover_focus_x: Math.max(0, Math.min(100, Number(payload.cover_focus_x) || 50)),
    cover_focus_y: Math.max(0, Math.min(100, Number(payload.cover_focus_y) || 50)),
  };
  const write = async (body) => {
    if (id) return sb.from('gallery_albums').update(body).eq('id', id).select().single();
    return sb.from('gallery_albums').insert(body).select().single();
  };
  let { data, error } = await write(row);
  if (error && /cover_focus/i.test(error.message || '')) {
    const { cover_focus_x, cover_focus_y, ...legacy } = row;
    ({ data, error } = await write(legacy));
  }
  if (error) throw error;
  return data;
}

async function deleteGalleryAlbum (id) {
  const sb = requireSb();
  const { error } = await sb.from('gallery_albums').delete().eq('id', id);
  if (error) throw error;
}

async function setGalleryAlbumCover (albumId, storagePath, itemId) {
  const sb = requireSb();
  await sb.from('gallery_items').update({ is_cover: false }).eq('album_id', albumId);
  if (itemId) {
    await sb.from('gallery_items').update({ is_cover: true }).eq('id', itemId);
  }
  const { error } = await sb.from('gallery_albums').update({ cover_storage_path: storagePath }).eq('id', albumId);
  if (error) throw error;
}

async function deleteInterestedLead (id) {
  const sb = requireSb();
  const { error } = await sb.from('interested_leads').delete().eq('id', id);
  if (error) throw error;
}

async function updateInterestedLead (id, payload) {
  const sb = requireSb();
  const row = {};
  if (payload.status != null) row.status = payload.status;
  if (payload.admin_notes !== undefined) row.admin_notes = payload.admin_notes?.trim() || null;
  if (payload.status === 'contactado' && !payload.skip_contacted_at) {
    row.contacted_at = new Date().toISOString();
  }
  const { data, error } = await sb.from('interested_leads').update(row).eq('id', id).select().single();
  if (error) throw error;
  return data;
}

async function fetchBankAccountsAdmin () {
  const sb = requireSb();
  const { data, error } = await sb.from('bank_transfer_accounts').select('*').order('sort_order');
  if (error) throw error;
  return data || [];
}

async function saveAcademySettings (payload) {
  const sb = requireSb();
  const row = { updated_at: new Date().toISOString() };
  const num = (v, fallback = 0) => (v === undefined || v === '' ? undefined : Number(v) ?? fallback);
  const str = (v) => (v === undefined ? undefined : (v == null ? null : String(v).trim() || null));
  const patchNum = (key, fallback) => { const v = num(payload[key], fallback); if (v !== undefined) row[key] = v; };
  const patchStr = (key) => { if (payload[key] !== undefined) row[key] = str(payload[key]); };

  patchNum('monthly_fee', 0);
  patchNum('billing_period_start_day', 1);
  patchNum('billing_period_end_day', 5);
  patchNum('overdue_from_day', 6);
  patchNum('late_until_day', 15);
  patchNum('late_fee_percent', 0);
  patchNum('severe_late_from_day', 16);
  patchNum('severe_late_until_day', 28);
  patchNum('severe_late_days', 5);
  patchNum('severe_late_fee_percent', 0);
  patchStr('venue_name');
  patchStr('location_kicker');
  patchStr('location_title');
  patchStr('location_subtitle');
  patchStr('address_street');
  patchStr('address_city');
  patchStr('address_state');
  patchStr('address_postal');
  patchStr('address_country');
  if (payload.latitude !== undefined && payload.latitude !== '') row.latitude = Number(payload.latitude);
  if (payload.longitude !== undefined && payload.longitude !== '') row.longitude = Number(payload.longitude);
  patchStr('google_maps_url');
  patchStr('schedule_weekdays');
  patchStr('schedule_saturday');
  if (payload.contact_phone !== undefined) row.contact_phone = sanitizePhoneForStorage(payload.contact_phone);
  patchStr('contact_email');
  patchStr('footer_address_line1');
  patchStr('footer_address_line2');
  patchStr('facebook_url');
  if (payload.whatsapp_phone !== undefined) row.whatsapp_phone = sanitizePhoneForStorage(payload.whatsapp_phone);
  patchStr('whatsapp_url');
  patchStr('wa_bridge_url');
  patchStr('footer_tagline');
  patchStr('footer_copyright_text');
  patchStr('footer_copyright_url');
  patchStr('footer_credit_text');
  patchStr('footer_credit_url');
  patchStr('hero_badge');
  patchStr('hero_title_line1');
  patchStr('hero_title_line2');
  patchStr('hero_title_line3');
  patchStr('hero_subtitle');
  patchStr('hero_stat_alumni_value');
  patchStr('hero_stat_alumni_label');
  patchStr('hero_stat_coaches_value');
  patchStr('hero_stat_coaches_label');
  patchStr('hero_stat_categories_value');
  patchStr('hero_stat_categories_label');
  patchStr('hero_stat_trophies_value');
  patchStr('hero_stat_trophies_label');
  patchStr('tienda_hero_badge');
  patchStr('tienda_hero_title_line1');
  patchStr('tienda_hero_title_line2');
  patchStr('tienda_hero_subtitle_line1');
  patchStr('tienda_hero_subtitle_line2');
  patchStr('tienda_stat_products_value');
  patchStr('tienda_stat_products_suffix');
  patchStr('tienda_stat_products_label');
  patchStr('tienda_stat_categories_value');
  patchStr('tienda_stat_categories_suffix');
  patchStr('tienda_stat_categories_label');
  patchStr('tienda_stat_delivery_value');
  patchStr('tienda_stat_delivery_suffix');
  patchStr('tienda_stat_delivery_label');
  patchStr('tienda_stat_official_value');
  patchStr('tienda_stat_official_suffix');
  patchStr('tienda_stat_official_label');
  patchStr('tienda_guarantee_1_title');
  patchStr('tienda_guarantee_1_desc');
  patchStr('tienda_guarantee_2_title');
  patchStr('tienda_guarantee_2_desc');
  patchStr('tienda_guarantee_3_title');
  patchStr('tienda_guarantee_3_desc');
  patchStr('tienda_guarantee_4_title');
  patchStr('tienda_guarantee_4_desc');
  patchStr('map_marker_image_path');
  if (payload.map_zoom !== undefined && payload.map_zoom !== '') row.map_zoom = Math.min(21, Math.max(10, Number(payload.map_zoom) || 16));

  const { data, error } = await sb.from('academy_settings')
    .update(row)
    .eq('singleton_key', 'default')
    .select()
    .single();
  if (error) {
    const msg = error.message || '';
    if (/footer_|hero_|tienda_|facebook_url|whatsapp_|late_until_day|severe_late_from_day|severe_late_until_day|schema cache|PGRST204/i.test(msg)) {
      const mig = /tienda_/i.test(msg)
        ? 'supabase/migrations/036_tienda_page_settings.sql'
        : /hero_title|hero_badge/i.test(msg)
          ? 'supabase/migrations/035_hero_title_badge.sql'
          : /hero_/i.test(msg)
            ? 'supabase/migrations/034_hero_landing_settings.sql'
            : /footer_|facebook|whatsapp/i.test(msg)
              ? 'supabase/migrations/033_site_footer_settings.sql'
              : 'supabase/migrations/031_billing_tier_days.sql';
      throw new Error(
        'Faltan columnas en Supabase. Abre el proyecto → SQL Editor, ejecuta '
        + mig + ' y vuelve a guardar. (Detalle: ' + msg + ')'
      );
    }
    throw error;
  }
  return data;
}

async function upsertBankAccount (payload, id) {
  const sb = requireSb();
  const row = {
    purpose: payload.purpose,
    label: payload.label?.trim(),
    beneficiary: payload.beneficiary?.trim(),
    bank_name: payload.bank_name?.trim(),
    account_number: payload.account_number?.trim(),
    clabe: payload.clabe?.trim(),
    is_active: payload.is_active !== false,
    sort_order: Number(payload.sort_order) || 0,
  };
  if (id) {
    const { data, error } = await sb.from('bank_transfer_accounts').update(row).eq('id', id).select().single();
    if (error) throw error;
    return data;
  }
  const { data, error } = await sb.from('bank_transfer_accounts').insert(row).select().single();
  if (error) throw error;
  return data;
}

Object.assign(window, {
  fetchMyProfile,
  updateAdminProfileName,
  updateAdminAuthEmail,
  updateAdminAuthPassword,
  lookupStudentCode,
  fetchStudentPortalByCode,
  createStudent,
  updateStudent,
  migrateAllStudentPhonesOnce,
  sanitizeStudentPhonePayload,
  deleteStudent,
  fetchStudentDocuments,
  fetchStudentsDocumentCounts,
  fetchStudentDocumentRequirements,
  saveStudentDocumentRequirements,
  uploadStudentDocument,
  removeStudentDocument,
  generateStudentBillingCyclePayments,
  pruneStudentPreJoinPayments,
  pruneStudentOutOfCyclePendingPayments,
  ensureStudentCurrentMonthPayment,
  createPayment,
  updatePayment,
  deletePayment,
  markPaymentPaid,
  ensurePaymentForBillingMonth,
  registerStudentMonthPayment,
  fetchPaymentDetailAdmin,
  upsertAnnouncement,
  deleteAnnouncement,
  fetchAnnouncementRegistrations,
  deleteAnnouncementRegistration,
  fetchInterestedLeadsForContext,
  fetchStudentBriefForReceipt,
  upsertEvent,
  fetchEventsAdmin,
  fetchEventConfirmations,
  deleteEventConfirmation,
  deleteEvent,
  fetchAllProducts,
  fetchInventoryMovements,
  resetInventoryMovementsHistory,
  adminFactoryReset,
  adminClearBillingStoreHistory,
  upsertProduct,
  upsertProductInventory,
  updateProductStore,
  deleteProduct,
  updateOrderStatus,
  deleteOrder,
  upsertCoach,
  deleteCoach,
  upsertGalleryItem,
  deleteGalleryItem,
  fetchGalleryCategories,
  createGalleryCategory,
  fetchGalleryAlbumsAdmin,
  fetchGalleryAlbumById,
  saveGalleryAlbum,
  deleteGalleryAlbum,
  setGalleryAlbumCover,
  slugifyGalleryCategory,
  deleteInterestedLead,
  updateInterestedLead,
  fetchBankAccountsAdmin,
  upsertBankAccount,
  saveAcademySettings,
});