/* Carga de datos desde Supabase — crece conforme agregas registros */

const EmptyState = ({ theme, title = 'Sin registros', message, icon = 'inbox', action }) => (
  <div style={{
    padding: 48, textAlign: 'center', fontFamily: 'Inter, sans-serif',
    color: theme.textDim,
  }}>
    <div style={{
      width: 64, height: 64, borderRadius: 16, margin: '0 auto 16px',
      background: theme.bgInput, border: `1px solid ${theme.border}`,
      display: 'grid', placeItems: 'center', color: theme.textMute,
    }}>
      <Icon name={icon} size={28} />
    </div>
    <div style={{ fontWeight: 700, color: theme.text, fontSize: 16, marginBottom: 8 }}>{title}</div>
    <p style={{ fontSize: 13, margin: '0 auto', maxWidth: 400, lineHeight: 1.5 }}>{message}</p>
    {action}
  </div>
);

const LoadingBlock = ({ theme, label = 'Cargando…' }) => (
  <div style={{ padding: 48, textAlign: 'center', color: theme.textMute, fontFamily: 'Inter, sans-serif' }}>{label}</div>
);

const AuthRequired = ({ theme, onRetry }) => (
  <EmptyState
    theme={theme}
    title="Inicia sesión como administrador"
    message="Para ver y gestionar datos reales, entra con tu correo y contraseña en Iniciar sesión → Administrador."
    icon="user"
    action={onRetry ? <Btn theme={theme} kind="soft" size="sm" onClick={onRetry} style={{ marginTop: 16 }}>Reintentar</Btn> : null}
  />
);

function useLiveData (loader, deps = []) {
  const [data, setData] = React.useState(null);
  const [loading, setLoading] = React.useState(true);
  const [error, setError] = React.useState('');
  const [needsAuth, setNeedsAuth] = React.useState(false);

  const reload = React.useCallback(async () => {
    setLoading(true);
    setError('');
    setNeedsAuth(false);
    if (!isSupabaseReady()) {
      setData([]);
      setLoading(false);
      return;
    }
    try {
      const session = await getAuthSession();
      const result = await loader({ session });
      setData(result ?? []);
    } catch (e) {
      const msg = e?.message || '';
      if (msg.includes('JWT') || e?.code === 'PGRST301' || msg.includes('permission')) {
        setNeedsAuth(true);
      } else {
        setError(msg || 'No se pudo cargar');
      }
      setData([]);
    }
    setLoading(false);
  }, deps);

  React.useEffect(() => { reload(); }, [reload]);

  return { data: data ?? [], loading, error, needsAuth, reload };
}

const AcademySettingsContext = React.createContext(null);

function AcademySettingsProvider ({ children }) {
  const { data, loading, error, reload } = useLiveData(() => fetchAcademySettings(), []);
  const settings = React.useMemo(() => normalizeAcademySettings(data || {}), [data]);
  const value = React.useMemo(
    () => ({ settings, loading, error, reload }),
    [settings, loading, error, reload]
  );
  return React.createElement(AcademySettingsContext.Provider, { value }, children);
}

function useAcademySettings () {
  const ctx = React.useContext(AcademySettingsContext);
  if (ctx) return ctx;
  const { data, loading, error, reload } = useLiveData(() => fetchAcademySettings(), []);
  return {
    settings: normalizeAcademySettings(data || {}),
    loading,
    error,
    reload,
  };
}

function ageFromBirthDate (birthDate) {
  if (!birthDate) return '—';
  const b = new Date(birthDate);
  if (Number.isNaN(b.getTime())) return '—';
  return Math.floor((Date.now() - b.getTime()) / (365.25 * 86400000));
}

function mapStudentRow (s, adeudoMap = {}) {
  return {
    id: s.code,
    _uuid: s.id,
    name: s.full_name,
    age: ageFromBirthDate(s.birth_date),
    cat: s.category || '—',
    tutor: s.tutor_name || '—',
    phone: s.tutor_phone || '—',
    student_phone: s.student_phone || '—',
    email: s.tutor_email || '—',
    status: s.status,
    joined: s.joined_at
      ? new Date(s.joined_at).toLocaleDateString('es-MX', { day: 'numeric', month: 'short', year: 'numeric' })
      : '—',
    antiguedad: computeSeniorityLabel(s.joined_at),
    adeudo: adeudoMap[s.id] || 0,
    birth_date: s.birth_date,
    joined_at: s.joined_at,
    notes: s.notes,
    photo_path: s.photo_path,
    photoUrl: s.photo_path ? getStoragePublicUrl('gallery', s.photo_path) : null,
    portal_must_change_password: !!s.portal_must_change_password,
    document_requirements: s.document_requirements ?? null,
  };
}

const DEMO_STUDENT_CODES = ['TEC067', 'TEC-0067'];

function isDemoStudentCode (code) {
  const raw = String(code || '').trim().toUpperCase();
  const n = normalizeTecStudentCode(code);
  return DEMO_STUDENT_CODES.includes(raw) || (n && DEMO_STUDENT_CODES.includes(n));
}

const DEFAULT_ACADEMY_SETTINGS = {
  monthly_fee: 1200,
  billing_period_start_day: 1,
  billing_period_end_day: 5,
  overdue_from_day: 6,
  late_until_day: 15,
  late_fee_percent: 0,
  severe_late_from_day: 16,
  severe_late_until_day: 28,
  severe_late_days: 5,
  severe_late_fee_percent: 0,
  venue_name: 'TECOS ELITE CLUB',
  location_kicker: 'Ubicación',
  location_title: 'ENCUÉNTRANOS',
  location_subtitle: 'Visítanos en nuestras instalaciones. Cancha cubierta, área de calentamiento y vestidores.',
  address_street: 'Av. Universidad #1234, Col. Centro',
  address_city: 'Guadalajara',
  address_state: 'Jalisco',
  address_postal: '44100',
  address_country: 'México',
  latitude: 20.659698,
  longitude: -103.349609,
  google_maps_url: '',
  schedule_weekdays: 'Lun a Vie · 16:00 — 21:00',
  schedule_saturday: 'Sáb · 09:00 — 14:00',
  contact_phone: '+52 33 1234 5678',
  contact_email: 'contacto@tecoselite.mx',
  footer_address_line1: 'Av. Universidad #1234',
  footer_address_line2: 'Guadalajara, Jal.',
  facebook_url: '',
  whatsapp_phone: '+52 33 1234 5678',
  whatsapp_url: '',
  wa_bridge_url: '',
  footer_tagline: 'Academia profesional de VOLLEYBALL formando atletas y campeones desde 2014. Disciplina, técnica y pasión.',
  footer_copyright_text: '© 2026 Tecos Elite VOLLEYBALL — Todos los derechos reservados',
  footer_copyright_url: '',
  footer_credit_text: 'Luna Creativa Studio',
  footer_credit_url: 'diseñador.html',
  hero_badge: 'Temporada 2026 abierta',
  hero_title_line1: 'DONDE NACEN',
  hero_title_line2: 'LOS CAMPEONES',
  hero_title_line3: 'DEL VOLLEYBALL',
  hero_subtitle: 'Academia profesional de VOLLEYBALL para todas las edades. Entrenamiento de élite, torneos, valores deportivos y formación integral.',
  hero_stat_alumni_value: '320+',
  hero_stat_alumni_label: 'Alumnos activos',
  hero_stat_coaches_value: '14',
  hero_stat_coaches_label: 'Entrenadores',
  hero_stat_categories_value: '8',
  hero_stat_categories_label: 'Categorías',
  hero_stat_trophies_value: '47',
  hero_stat_trophies_label: 'Trofeos ganados',
  tienda_hero_badge: 'Equipo oficial · Temporada 2026',
  tienda_hero_title_line1: 'TIENDA',
  tienda_hero_title_line2: 'OFICIAL',
  tienda_hero_subtitle_line1: 'Uniformes, balones y accesorios con el sello Tecos Elite.',
  tienda_hero_subtitle_line2: 'Compra fácil: elige, transfiere y recoge en el club.',
  tienda_stat_products_value: '8',
  tienda_stat_products_suffix: '',
  tienda_stat_products_label: 'Productos',
  tienda_stat_categories_value: '4',
  tienda_stat_categories_suffix: '',
  tienda_stat_categories_label: 'Categorías',
  tienda_stat_delivery_value: '72',
  tienda_stat_delivery_suffix: 'h',
  tienda_stat_delivery_label: 'Entrega',
  tienda_stat_official_value: '100',
  tienda_stat_official_suffix: '%',
  tienda_stat_official_label: 'Oficial',
  tienda_guarantee_1_title: 'Producto 100% oficial',
  tienda_guarantee_1_desc: 'Todos los artículos llevan el logotipo bordado o impreso de Tecos Elite VOLLEYBALL.',
  tienda_guarantee_2_title: 'Garantía de calidad',
  tienda_guarantee_2_desc: 'Si tu producto llega defectuoso, lo cambiamos sin costo en el plazo de 7 días.',
  tienda_guarantee_3_title: 'Soporte por WhatsApp',
  tienda_guarantee_3_desc: 'Seguimiento de tu orden en tiempo real. Respuesta en menos de 2 horas hábiles.',
  tienda_guarantee_4_title: 'Recogida en el club',
  tienda_guarantee_4_desc: 'Entrega segura directamente en las instalaciones de la academia en Guadalajara.',
  map_marker_image_path: '',
  map_zoom: 16,
};

function normalizeAcademySettings (row) {
  if (!row) return { ...DEFAULT_ACADEMY_SETTINGS };
  const d = DEFAULT_ACADEMY_SETTINGS;
  return {
    id: row.id,
    monthly_fee: Number(row.monthly_fee) ?? d.monthly_fee,
    billing_period_start_day: Number(row.billing_period_start_day) || 1,
    billing_period_end_day: Number(row.billing_period_end_day) || 5,
    overdue_from_day: Number(row.overdue_from_day) || 6,
    late_until_day: Number(row.late_until_day) || 15,
    late_fee_percent: Number(row.late_fee_percent) || 0,
    severe_late_from_day: Number(row.severe_late_from_day) || 16,
    severe_late_until_day: Number(row.severe_late_until_day) || 28,
    severe_late_days: Number(row.severe_late_days) || 5,
    severe_late_fee_percent: Number(row.severe_late_fee_percent) || 0,
    venue_name: row.venue_name ?? d.venue_name,
    location_kicker: row.location_kicker ?? d.location_kicker,
    location_title: row.location_title ?? d.location_title,
    location_subtitle: row.location_subtitle ?? d.location_subtitle,
    address_street: row.address_street ?? d.address_street,
    address_city: row.address_city ?? d.address_city,
    address_state: row.address_state ?? d.address_state,
    address_postal: row.address_postal ?? d.address_postal,
    address_country: row.address_country ?? d.address_country,
    latitude: row.latitude != null ? Number(row.latitude) : d.latitude,
    longitude: row.longitude != null ? Number(row.longitude) : d.longitude,
    google_maps_url: row.google_maps_url ?? d.google_maps_url,
    schedule_weekdays: row.schedule_weekdays ?? d.schedule_weekdays,
    schedule_saturday: row.schedule_saturday ?? d.schedule_saturday,
    contact_phone: row.contact_phone ?? d.contact_phone,
    contact_email: row.contact_email ?? d.contact_email,
    footer_address_line1: row.footer_address_line1 ?? d.footer_address_line1,
    footer_address_line2: row.footer_address_line2 ?? d.footer_address_line2,
    facebook_url: row.facebook_url ?? d.facebook_url,
    whatsapp_phone: row.whatsapp_phone ?? d.whatsapp_phone,
    whatsapp_url: row.whatsapp_url ?? d.whatsapp_url,
    wa_bridge_url: row.wa_bridge_url ?? d.wa_bridge_url,
    footer_tagline: row.footer_tagline ?? d.footer_tagline,
    footer_copyright_text: row.footer_copyright_text ?? d.footer_copyright_text,
    footer_copyright_url: row.footer_copyright_url ?? d.footer_copyright_url,
    footer_credit_text: row.footer_credit_text ?? d.footer_credit_text,
    footer_credit_url: row.footer_credit_url ?? d.footer_credit_url,
    hero_badge: row.hero_badge ?? d.hero_badge,
    hero_title_line1: row.hero_title_line1 ?? d.hero_title_line1,
    hero_title_line2: row.hero_title_line2 ?? d.hero_title_line2,
    hero_title_line3: row.hero_title_line3 ?? d.hero_title_line3,
    hero_subtitle: row.hero_subtitle ?? d.hero_subtitle,
    hero_stat_alumni_value: row.hero_stat_alumni_value ?? d.hero_stat_alumni_value,
    hero_stat_alumni_label: row.hero_stat_alumni_label ?? d.hero_stat_alumni_label,
    hero_stat_coaches_value: row.hero_stat_coaches_value ?? d.hero_stat_coaches_value,
    hero_stat_coaches_label: row.hero_stat_coaches_label ?? d.hero_stat_coaches_label,
    hero_stat_categories_value: row.hero_stat_categories_value ?? d.hero_stat_categories_value,
    hero_stat_categories_label: row.hero_stat_categories_label ?? d.hero_stat_categories_label,
    hero_stat_trophies_value: row.hero_stat_trophies_value ?? d.hero_stat_trophies_value,
    hero_stat_trophies_label: row.hero_stat_trophies_label ?? d.hero_stat_trophies_label,
    tienda_hero_badge: row.tienda_hero_badge ?? d.tienda_hero_badge,
    tienda_hero_title_line1: row.tienda_hero_title_line1 ?? d.tienda_hero_title_line1,
    tienda_hero_title_line2: row.tienda_hero_title_line2 ?? d.tienda_hero_title_line2,
    tienda_hero_subtitle_line1: row.tienda_hero_subtitle_line1 ?? d.tienda_hero_subtitle_line1,
    tienda_hero_subtitle_line2: row.tienda_hero_subtitle_line2 ?? d.tienda_hero_subtitle_line2,
    tienda_stat_products_value: row.tienda_stat_products_value ?? d.tienda_stat_products_value,
    tienda_stat_products_suffix: row.tienda_stat_products_suffix ?? d.tienda_stat_products_suffix,
    tienda_stat_products_label: row.tienda_stat_products_label ?? d.tienda_stat_products_label,
    tienda_stat_categories_value: row.tienda_stat_categories_value ?? d.tienda_stat_categories_value,
    tienda_stat_categories_suffix: row.tienda_stat_categories_suffix ?? d.tienda_stat_categories_suffix,
    tienda_stat_categories_label: row.tienda_stat_categories_label ?? d.tienda_stat_categories_label,
    tienda_stat_delivery_value: row.tienda_stat_delivery_value ?? d.tienda_stat_delivery_value,
    tienda_stat_delivery_suffix: row.tienda_stat_delivery_suffix ?? d.tienda_stat_delivery_suffix,
    tienda_stat_delivery_label: row.tienda_stat_delivery_label ?? d.tienda_stat_delivery_label,
    tienda_stat_official_value: row.tienda_stat_official_value ?? d.tienda_stat_official_value,
    tienda_stat_official_suffix: row.tienda_stat_official_suffix ?? d.tienda_stat_official_suffix,
    tienda_stat_official_label: row.tienda_stat_official_label ?? d.tienda_stat_official_label,
    tienda_guarantee_1_title: row.tienda_guarantee_1_title ?? d.tienda_guarantee_1_title,
    tienda_guarantee_1_desc: row.tienda_guarantee_1_desc ?? d.tienda_guarantee_1_desc,
    tienda_guarantee_2_title: row.tienda_guarantee_2_title ?? d.tienda_guarantee_2_title,
    tienda_guarantee_2_desc: row.tienda_guarantee_2_desc ?? d.tienda_guarantee_2_desc,
    tienda_guarantee_3_title: row.tienda_guarantee_3_title ?? d.tienda_guarantee_3_title,
    tienda_guarantee_3_desc: row.tienda_guarantee_3_desc ?? d.tienda_guarantee_3_desc,
    tienda_guarantee_4_title: row.tienda_guarantee_4_title ?? d.tienda_guarantee_4_title,
    tienda_guarantee_4_desc: row.tienda_guarantee_4_desc ?? d.tienda_guarantee_4_desc,
    map_marker_image_path: row.map_marker_image_path ?? d.map_marker_image_path,
    map_zoom: Number(row.map_zoom) || d.map_zoom,
  };
}

function clampDay (day, refDate = new Date()) {
  const d = Math.min(31, Math.max(1, Number(day) || 1));
  const last = new Date(refDate.getFullYear(), refDate.getMonth() + 1, 0).getDate();
  return Math.min(d, last);
}

/** Fecha límite de pago: último día del periodo normal (mensualidad sin recargo) */
function defaultPaymentDueDate (settings, refDate = new Date()) {
  const s = normalizeAcademySettings(settings);
  const y = refDate.getFullYear();
  const m = refDate.getMonth();
  const dueDay = clampDay(s.billing_period_end_day, refDate);
  return `${y}-${String(m + 1).padStart(2, '0')}-${String(dueDay).padStart(2, '0')}`;
}

function dayOfMonthForBilling (refDate = new Date()) {
  return clampDay(refDate.getDate(), refDate);
}

/** ¿El día del mes (1–31) cae en un rango? Si inicio > fin, el rango cruza fin de mes (ej. 24 al 5). */
function dayInMonthRange (dayOfMonth, rangeStart, rangeEnd) {
  const d = Number(dayOfMonth) || 1;
  const a = Number(rangeStart) || 1;
  const b = Number(rangeEnd) || 31;
  if (a <= b) return d >= a && d <= b;
  return d >= a || d <= b;
}

function formatDayRangeLabel (start, end) {
  const a = Number(start) || 1;
  const b = Number(end) || 31;
  if (a <= b) return `días ${a}–${b}`;
  return `días ${a}–31 y 1–${b}`;
}

/** Tramo de cobro según el día del mes en que se cobra o registra el pago. */
function getBillingTierForDate (settings, refDate = new Date()) {
  const s = normalizeAcademySettings(settings);
  const dom = dayOfMonthForBilling(refDate);

  if (dayInMonthRange(dom, s.billing_period_start_day, s.billing_period_end_day)) {
    return {
      tier: 'normal',
      label: 'Mensualidad normal',
      percents: [],
      rangeLabel: formatDayRangeLabel(s.billing_period_start_day, s.billing_period_end_day),
    };
  }

  const inLate1 = dayInMonthRange(dom, s.overdue_from_day, s.late_until_day);
  const inLate2 = dayInMonthRange(dom, s.severe_late_from_day, s.severe_late_until_day);

  if (inLate2) {
    const percents = [Number(s.severe_late_fee_percent) || 0];
    if (inLate1) percents.unshift(Number(s.late_fee_percent) || 0);
    const unique = [...new Set(percents.filter((p) => p > 0))];
    return {
      tier: unique.length >= 2 ? 'severe' : 'severe',
      label: unique.length >= 2 ? 'Primer y segundo recargo' : 'Segundo recargo',
      percents: unique.length ? unique : [s.severe_late_fee_percent],
      rangeLabel: inLate1
        ? `${formatDayRangeLabel(s.overdue_from_day, s.late_until_day)} + ${formatDayRangeLabel(s.severe_late_from_day, s.severe_late_until_day)}`
        : formatDayRangeLabel(s.severe_late_from_day, s.severe_late_until_day),
    };
  }

  if (inLate1) {
    return {
      tier: 'late',
      label: 'Primer recargo',
      percents: [Number(s.late_fee_percent) || 0].filter((p) => p > 0),
      rangeLabel: formatDayRangeLabel(s.overdue_from_day, s.late_until_day),
    };
  }

  return {
    tier: 'normal',
    label: 'Mensualidad normal',
    percents: [],
    rangeLabel: 'Fuera de tramos de recargo',
  };
}

function daysLateFromDue (dueDate) {
  if (!dueDate) return 0;
  const due = new Date(dueDate);
  if (Number.isNaN(due.getTime())) return 0;
  due.setHours(0, 0, 0, 0);
  const today = new Date();
  today.setHours(0, 0, 0, 0);
  return Math.max(0, Math.floor((today - due) / 86400000));
}

function formatMoneyMX (n) {
  return (Number(n) || 0).toLocaleString('es-MX', { minimumFractionDigits: 0, maximumFractionDigits: 2 });
}

function dateWithDayOfMonth (day, refDate = new Date()) {
  const y = refDate.getFullYear();
  const m = refDate.getMonth();
  const d = clampDay(day, refDate);
  return new Date(y, m, d, 12, 0, 0, 0);
}

function buildSurchargeLines (base, percents, settings) {
  const s = normalizeAcademySettings(settings);
  const lines = [];
  (percents || []).forEach((p, i) => {
    const pct = Number(p) || 0;
    if (pct <= 0) return;
    const amount = Math.round(base * (pct / 100) * 100) / 100;
    let label = `Recargo (${pct}%)`;
    if ((percents || []).length >= 2) {
      label = i === 0 ? `Recargo 1 (${pct}%)` : `Recargo 2 adicional (${pct}%)`;
    } else if (pct === Number(s.severe_late_fee_percent) && pct !== Number(s.late_fee_percent)) {
      label = `Recargo 2 adicional (${pct}%)`;
    } else if (pct === Number(s.late_fee_percent)) {
      label = `Recargo 1 (${pct}%)`;
    }
    lines.push({ label, percent: pct, amount });
  });
  return lines;
}

function computeLateFees (baseAmount, dueDate, settings, refDate) {
  const base = Number(baseAmount) || 0;
  const ref = refDate || new Date();
  const s = normalizeAcademySettings(settings);
  const tierInfo = getBillingTierForDate(settings, ref);
  const dom = dayOfMonthForBilling(ref);
  const daysLate = daysLateFromDue(dueDate);

  if (tierInfo.tier === 'normal') {
    return {
      base,
      surcharge: 0,
      surchargeLines: [],
      total: base,
      daysLate,
      dayOfMonth: dom,
      tier: tierInfo.tier,
      tierLabel: tierInfo.label,
      percentSum: 0,
      formula: `$${formatMoneyMX(base)} (mensualidad sin recargo)`,
    };
  }

  const surchargeLines = buildSurchargeLines(base, tierInfo.percents, s);
  const percentSum = surchargeLines.reduce((a, l) => a + l.percent, 0);
  const surcharge = Math.round(surchargeLines.reduce((a, l) => a + l.amount, 0) * 100) / 100;
  const total = Math.round((base + surcharge) * 100) / 100;
  const parts = surchargeLines.map(l => `$${formatMoneyMX(base)} × ${l.percent}% = $${formatMoneyMX(l.amount)}`);
  const formula = `$${formatMoneyMX(base)} + ${parts.join(' + ')} = $${formatMoneyMX(total)}`;

  return {
    base,
    surcharge,
    surchargeLines,
    total,
    daysLate,
    dayOfMonth: dom,
    tier: tierInfo.tier,
    tierLabel: tierInfo.label,
    percentSum,
    formula,
  };
}

/** Filas de ejemplo por día del mes — misma lógica que pagos de cada alumno. */
function getBillingPreviewRows (settings, baseAmount, refDate = new Date()) {
  const s = normalizeAcademySettings(settings);
  const base = Number(baseAmount) ?? s.monthly_fee;
  const todayDom = dayOfMonthForBilling(refDate);
  return Array.from({ length: 31 }, (_, i) => i + 1).map((day) => {
    const ref = dateWithDayOfMonth(day, refDate);
    const due = defaultPaymentDueDate(settings, ref);
    const fees = computeLateFees(base, due, settings, ref);
    const tier = getBillingTierForDate(settings, ref);
    const tramoLabel = tier.tier === 'normal'
      ? `Sin recargo (${formatDayRangeLabel(s.billing_period_start_day, s.billing_period_end_day)})`
      : tier.tier === 'severe'
        ? `Recargos (${tier.rangeLabel || ''})`
        : `Primer recargo (${formatDayRangeLabel(s.overdue_from_day, s.late_until_day)})`;
    return {
      day,
      isToday: day === todayDom,
      tramo: tramoLabel,
      tierLabel: fees.tierLabel,
      ...fees,
    };
  });
}

function billingConfigOverlapWarning (form) {
  const s = normalizeAcademySettings(form);
  const overlaps = [];
  for (let d = 1; d <= 31; d += 1) {
    const ref = dateWithDayOfMonth(d);
    const inNormal = dayInMonthRange(d, s.billing_period_start_day, s.billing_period_end_day);
    const inLate1 = dayInMonthRange(d, s.overdue_from_day, s.late_until_day);
    const inLate2 = dayInMonthRange(d, s.severe_late_from_day, s.severe_late_until_day);
    const count = [inNormal, inLate1, inLate2].filter(Boolean).length;
    if (count > 1) overlaps.push(d);
  }
  if (overlaps.length) {
    return `Los días ${overlaps.slice(0, 8).join(', ')}${overlaps.length > 8 ? '…' : ''} están en más de un tramo. Si un día coincide, se aplica primero sin recargo, luego segundo recargo, luego primer recargo. Ajusta los rangos para evitar solapamientos.`;
  }
  return '';
}

function billingPeriodLabel (settings) {
  const s = normalizeAcademySettings(settings);
  return `Pago normal (${formatDayRangeLabel(s.billing_period_start_day, s.billing_period_end_day)})`;
}

function billingLateTierLabel (settings) {
  const s = normalizeAcademySettings(settings);
  return `Recargo 1: ${formatDayRangeLabel(s.overdue_from_day, s.late_until_day)} (+${s.late_fee_percent}%) · Recargo 2: ${formatDayRangeLabel(s.severe_late_from_day, s.severe_late_until_day)} (+${s.severe_late_fee_percent}% adicional)`;
}

/** Texto corto para adelantos en portal (misma tarifa base que admin). */
function billingAdvancePayNote (settings) {
  const s = normalizeAcademySettings(settings);
  return `tarifa base sin recargo (${formatDayRangeLabel(s.billing_period_start_day, s.billing_period_end_day)})`;
}

/** Párrafo del calendario del alumno alineado con Configuración → Tarifas. */
function portalBillingScheduleHint (settings) {
  const s = normalizeAcademySettings(settings);
  const normal = formatDayRangeLabel(s.billing_period_start_day, s.billing_period_end_day);
  const late1 = formatDayRangeLabel(s.overdue_from_day, s.late_until_day);
  const late2 = formatDayRangeLabel(s.severe_late_from_day, s.severe_late_until_day);
  return {
    activeMonths: `Mes en curso y atrasados: el monto depende del día del mes (sin recargo ${normal}, primer recargo ${late1}, segundo recargo ${late2}).`,
    advanceMonths: `Meses futuros del año: adelanto con ${billingAdvancePayNote(s)}.`,
    calendar: `Toca un mes con monto para transferir y subir comprobante. Adelantos: ${billingAdvancePayNote(s)}; el pago se confirma cuando el administrador lo apruebe.`,
  };
}

function validateBillingSettings (form) {
  const s = normalizeAcademySettings(form);
  const checkRange = (name, start, end) => {
    const a = Number(start);
    const b = Number(end);
    if (!Number.isFinite(a) || !Number.isFinite(b) || a < 1 || a > 31 || b < 1 || b > 31) {
      return `${name}: cada día debe estar entre 1 y 31.`;
    }
    return '';
  };
  return (
    checkRange('Sin recargo', s.billing_period_start_day, s.billing_period_end_day)
    || checkRange('Primer recargo', s.overdue_from_day, s.late_until_day)
    || checkRange('Segundo recargo', s.severe_late_from_day, s.severe_late_until_day)
    || ''
  );
}

async function fetchAcademySettings () {
  const sb = getSupabase();
  if (!sb) return { ...DEFAULT_ACADEMY_SETTINGS };
  const { data, error } = await sb.from('academy_settings')
    .select('*')
    .eq('singleton_key', 'default')
    .maybeSingle();
  if (error) throw error;
  return normalizeAcademySettings(data);
}

function notifySettingsChanged () {
  notifyAcademyDataSync('settings');
  window.dispatchEvent(new CustomEvent('tecos:settings-changed'));
}

function buildWhatsAppLink (settings, text) {
  const s = normalizeAcademySettings(settings);
  const custom = String(s.whatsapp_url || '').trim();
  const norm = typeof normalizeWaPhoneDigits === 'function' ? normalizeWaPhoneDigits : (p) => String(p || '').replace(/\D/g, '');
  const phone = s.whatsapp_phone || s.contact_phone || '';
  if (typeof buildWhatsAppUrl === 'function') {
    const fromPhone = buildWhatsAppUrl(phone, text);
    if (fromPhone) return fromPhone;
  }
  if (custom && !text) return custom;
  const digits = norm(phone);
  if (!digits) return custom || '';
  const base = `https://wa.me/${digits}`;
  if (!text) return base;
  const msg = typeof stripWaMessageForUrl === 'function'
    ? stripWaMessageForUrl(text)
    : String(text).replace(/\*/g, '');
  return `${base}?text=${encodeURIComponent(msg)}`;
}

function getHeroStats (settings) {
  const s = normalizeAcademySettings(settings);
  return [
    { v: s.hero_stat_alumni_value, l: s.hero_stat_alumni_label },
    { v: s.hero_stat_coaches_value, l: s.hero_stat_coaches_label },
    { v: s.hero_stat_categories_value, l: s.hero_stat_categories_label },
    { v: s.hero_stat_trophies_value, l: s.hero_stat_trophies_label },
  ];
}

function parseTiendaStatNumber (raw) {
  const n = Number(String(raw || '').replace(/[^\d.]/g, ''));
  return Number.isFinite(n) ? n : 0;
}

function getTiendaHeroStats (settings) {
  const s = normalizeAcademySettings(settings);
  const row = (vKey, sKey, lKey) => ({
    v: parseTiendaStatNumber(s[vKey]),
    s: s[sKey] || '',
    l: s[lKey],
  });
  return [
    row('tienda_stat_products_value', 'tienda_stat_products_suffix', 'tienda_stat_products_label'),
    row('tienda_stat_categories_value', 'tienda_stat_categories_suffix', 'tienda_stat_categories_label'),
    row('tienda_stat_delivery_value', 'tienda_stat_delivery_suffix', 'tienda_stat_delivery_label'),
    row('tienda_stat_official_value', 'tienda_stat_official_suffix', 'tienda_stat_official_label'),
  ];
}

const TIENDA_GUARANTEE_ICONS = ['check', 'star', 'wa', 'pin'];

function getTiendaGuarantees (settings) {
  const s = normalizeAcademySettings(settings);
  return [1, 2, 3, 4].map((i, idx) => ({
    icon: TIENDA_GUARANTEE_ICONS[idx],
    title: s[`tienda_guarantee_${i}_title`],
    desc: s[`tienda_guarantee_${i}_desc`],
  }));
}

function formatBirthDateDisplay (birthDate) {
  if (!birthDate) return '—';
  const d = new Date(birthDate);
  if (Number.isNaN(d.getTime())) return '—';
  return d.toLocaleDateString('es-MX', { day: 'numeric', month: 'short', year: 'numeric' });
}

async function fetchStudentPaymentsByUuid (studentUuid) {
  const sb = getSupabase();
  const [settings, paymentsRes] = await Promise.all([
    fetchAcademySettings(),
    sb.from('payments')
      .select('id, concept, amount, due_date, paid_at, status')
      .eq('student_id', studentUuid)
      .order('due_date', { ascending: false }),
  ]);
  if (paymentsRes.error) throw paymentsRes.error;
  return mapPortalPaymentsExpediente(paymentsRes.data, settings);
}

function mapOrderRowsToPortal (rows) {
  return mapPortalOrders((rows || []).map((o) => {
    const items = o.order_items || [];
    const summary = o.product_summary
      || (items.length
        ? items.map((i) => `${i.product_name}${i.size ? ` (${i.size})` : ''} ×${i.qty}`).join(', ')
        : 'Pedido tienda');
    return { ...o, product_summary: summary };
  }));
}

async function fetchStudentOrdersByUuid (studentUuid, studentCode) {
  const sb = getSupabase();
  if (!sb) return [];
  const code = typeof normalizeTecStudentCode === 'function'
    ? normalizeTecStudentCode(studentCode)
    : String(studentCode || '').trim().toUpperCase();
  if (!code && !studentUuid) return [];

  if (code) {
    const { data: rpcRows, error: rpcErr } = await sb.rpc('get_student_orders_by_code', {
      p_student_code: code,
    });
    if (!rpcErr && rpcRows) return mapPortalOrders(rpcRows);
    if (rpcErr && !/get_student_orders_by_code|schema cache|PGRST202/i.test(rpcErr.message || '')) {
      console.warn('[Tecos] orders by code RPC', rpcErr);
    }
  }

  const filters = [];
  if (studentUuid) filters.push(`student_id.eq.${studentUuid}`);
  if (code) filters.push(`guest_student_code.eq.${code}`);
  if (!filters.length) return [];

  const { data, error } = await sb.from('orders')
    .select(`
      id, order_number, total, status, created_at, student_id,
      guest_student_code, guest_phone, admin_notes, receipt_path, transfer_reference,
      order_items ( product_name, size, qty )
    `)
    .or(filters.join(','))
    .order('created_at', { ascending: false });
  if (error) throw error;
  return mapOrderRowsToPortal(data);
}

function notifyOrdersChanged () {
  notifyAcademyDataSync('orders');
  window.dispatchEvent(new CustomEvent('tecos:orders-changed'));
}

async function fetchStudentNotificationsByUuid (studentUuid) {
  const sb = getSupabase();
  const { data, error } = await sb.from('notifications')
    .select('id, title, body, icon, category, created_at, read_at')
    .eq('student_id', studentUuid)
    .order('created_at', { ascending: false })
    .limit(50);
  if (error) throw error;
  return (data || []).map(n => ({
    t: n.title,
    d: new Date(n.created_at).toLocaleString('es-MX', { day: 'numeric', month: 'short', hour: '2-digit', minute: '2-digit' }),
    s: n.read_at ? 'leido' : 'enviado',
    i: n.icon || 'bell',
  }));
}

function downloadCsv (filename, rows) {
  const esc = (v) => {
    const s = String(v ?? '');
    return s.includes(',') || s.includes('"') || s.includes('\n') ? `"${s.replace(/"/g, '""')}"` : s;
  };
  const csv = rows.map(r => r.map(esc).join(',')).join('\n');
  const blob = new Blob(['\ufeff' + csv], { type: 'text/csv;charset=utf-8' });
  const a = document.createElement('a');
  a.href = URL.createObjectURL(blob);
  a.download = filename;
  a.click();
  URL.revokeObjectURL(a.href);
}

function exportStudentsCsv (alumnos) {
  downloadCsv(`alumnos-tecos-${new Date().toISOString().slice(0, 10)}.csv`, [
    ['ID', 'Nombre', 'Edad', 'Categoría', 'Tutor', 'Teléfono', 'Correo', 'Estado', 'Adeudo', 'Ingreso'],
    ...alumnos.map(a => [a.id, a.name, a.age, a.cat, a.tutor, a.phone, a.email, a.status, a.adeudo, a.joined]),
  ]);
}

function exportStudentExpedienteCsv (alumno, pagos, ordenes) {
  const rows = [
    ['Campo', 'Valor'],
    ['ID', alumno.id],
    ['Nombre', alumno.name],
    ['Categoría', alumno.cat],
    ['Tutor', alumno.tutor],
    ['Teléfono', alumno.phone],
    ['Correo', alumno.email],
    ['Estado', alumno.status],
    ['Adeudo', alumno.adeudo],
    ['Ingreso', alumno.joined],
    [],
    ['Pagos — Concepto', 'Monto', 'Vencimiento', 'Estado'],
    ...pagos.map(p => [p.c, p.a, p.d, p.s]),
    [],
    ['Órdenes — Producto', 'Total', 'Fecha', 'Estado'],
    ...ordenes.map(o => [o.p, o.a, o.d, o.s]),
  ];
  downloadCsv(`expediente-${alumno.id}-${new Date().toISOString().slice(0, 10)}.csv`, rows);
}

function studentPhotoUrl (row) {
  const path = row?.photo_path;
  if (!path || typeof getStoragePublicUrl !== 'function') return null;
  return getStoragePublicUrl('gallery', path);
}

function mapPortalStudent (s, extra = {}) {
  if (!s) return null;
  const photoUrl = studentPhotoUrl(s);
  return {
    code: s.code,
    name: s.full_name,
    shortName: (s.full_name || '').split(' ').slice(0, 2).join(' '),
    age: ageFromBirthDate(s.birth_date),
    cat: s.category || '—',
    tutor: s.tutor_name || '—',
    phone: s.tutor_phone || '—',
    email: s.tutor_email || '—',
    status: s.status,
    joined: s.joined_at
      ? new Date(s.joined_at).toLocaleDateString('es-MX', { day: 'numeric', month: 'short', year: 'numeric' })
      : '—',
    joined_at: s.joined_at,
    _uuid: s.id,
    photoUrl,
    birth_date: s.birth_date,
    adeudo: Number(extra.adeudo) || 0,
    document_requirements: s.document_requirements ?? null,
  };
}

const PORTAL_OPEN_PAYMENT_STATUSES = new Set([
  'pendiente', 'vencido', 'urgente', 'revision', 'en_revision', 'rechazado',
]);

function isPortalMonthExigible (m) {
  return !!(m && !m.hideAmount && m.status !== 'sin_registro');
}

function firstExigiblePendingMonth (months) {
  return (months || []).find((m) => (
    isPortalMonthExigible(m) && PORTAL_OPEN_PAYMENT_STATUSES.has(m.status)
  ));
}

function countExigiblePendingMonths (months) {
  return (months || []).filter((m) => (
    isPortalMonthExigible(m) && PORTAL_OPEN_PAYMENT_STATUSES.has(m.status)
  )).length;
}

function formatAlbumDateLabel (albumDate, eventStartsAt) {
  if (eventStartsAt) {
    try {
      return new Date(eventStartsAt).toLocaleDateString('es-MX', { day: 'numeric', month: 'short', year: 'numeric' });
    } catch { /* ignore */ }
  }
  if (albumDate) {
    try {
      return new Date(albumDate + 'T12:00:00').toLocaleDateString('es-MX', { day: 'numeric', month: 'short', year: 'numeric' });
    } catch { /* ignore */ }
  }
  return '';
}

function mapGalleryAlbum (album, photos) {
  const cat = album.gallery_categories || {};
  const ev = album.events || {};
  const sorted = [...(photos || [])].sort((a, b) => a.sort_order - b.sort_order);
  const coverPath = album.cover_storage_path || sorted.find(p => p.is_cover)?.storage_path || sorted[0]?.storage_path;
  return {
    id: album.id,
    title: album.title,
    description: album.description || '',
    category: cat.name || 'General',
    categorySlug: cat.slug || 'general',
    categoryId: cat.id,
    eventId: ev.id || null,
    eventTitle: ev.title || null,
    dateLabel: formatAlbumDateLabel(album.album_date, ev.starts_at),
    coverUrl: coverPath ? getStoragePublicUrl('gallery', coverPath) : null,
    coverFocusX: Number(album.cover_focus_x) || 50,
    coverFocusY: Number(album.cover_focus_y) || 50,
    photoCount: sorted.length,
    photos: sorted.map(p => ({
      id: p.id,
      url: getStoragePublicUrl('gallery', p.storage_path),
      isCover: p.is_cover,
    })),
  };
}

async function fetchPublicGalleryAlbums () {
  const sb = getSupabase();
  if (!sb) return [];
  let { data: albums, error } = await sb.from('gallery_albums')
    .select('id, title, album_date, description, cover_storage_path, cover_focus_x, cover_focus_y, sort_order, created_at, gallery_categories(id, name, slug), events(id, title, starts_at)')
    .eq('published', true)
    .order('sort_order')
    .order('created_at', { ascending: false });
  if (error && /cover_focus/i.test(error.message || '')) {
    const legacy = await sb.from('gallery_albums')
      .select('id, title, album_date, description, cover_storage_path, sort_order, created_at, gallery_categories(id, name, slug), events(id, title, starts_at)')
      .eq('published', true)
      .order('sort_order')
      .order('created_at', { ascending: false });
    if (legacy.error) throw legacy.error;
    albums = (legacy.data || []).map(a => ({ ...a, cover_focus_x: 50, cover_focus_y: 50 }));
    error = null;
  } else if (error) {
    throw error;
  }
  if (!albums?.length) {
    const { data: legacy, error: legErr } = await sb.from('gallery_items')
      .select('id, title, storage_path, thumb_path, category, created_at')
      .is('album_id', null)
      .order('created_at', { ascending: false });
    if (legErr) throw legErr;
    return (legacy || []).map(g => ({
      id: g.id,
      title: g.title || 'Foto',
      description: '',
      category: g.category || 'general',
      categorySlug: g.category || 'general',
      eventTitle: null,
      dateLabel: g.created_at ? new Date(g.created_at).toLocaleDateString('es-MX', { day: 'numeric', month: 'short', year: 'numeric' }) : '',
      coverUrl: getStoragePublicUrl('gallery', g.storage_path),
      photoCount: 1,
      photos: [{ id: g.id, url: getStoragePublicUrl('gallery', g.storage_path), isCover: true }],
      _legacy: true,
    }));
  }
  const ids = albums.map(a => a.id);
  const { data: photos, error: pErr } = await sb.from('gallery_items')
    .select('id, album_id, storage_path, is_cover, sort_order')
    .in('album_id', ids);
  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 => mapGalleryAlbum(a, byAlbum[a.id] || []));
}

async function fetchPublicGalleryCategories () {
  const sb = getSupabase();
  if (!sb) return [];
  const { data, error } = await sb.from('gallery_categories').select('id, name, slug').order('sort_order');
  if (error) throw error;
  return data || [];
}

/** @deprecated use fetchPublicGalleryAlbums */
async function fetchPublicGallery () {
  const albums = await fetchPublicGalleryAlbums();
  return albums.flatMap(a => a.photos.map((p, i) => ({
    id: p.id,
    title: a.title,
    category: a.categorySlug,
    url: p.url,
    created_at: null,
  })));
}

function notifyGalleryChanged () {
  window.dispatchEvent(new CustomEvent('tecos:gallery-changed'));
}

const MONTH_KEYS = ['ene', 'feb', 'mar', 'abr', 'may', 'jun', 'jul', 'ago', 'sep', 'oct', 'nov', 'dic'];
const MONTH_LABELS = ['Ene', 'Feb', 'Mar', 'Abr', 'May', 'Jun', 'Jul', 'Ago', 'Sep', 'Oct', 'Nov', 'Dic'];

function parseJoinedMonthStart (joinedAt) {
  if (!joinedAt) return null;
  const s = String(joinedAt).trim();
  const m = /^(\d{4})-(\d{2})-(\d{2})/.exec(s);
  if (m) {
    const y = Number(m[1]);
    const mo = Number(m[2]) - 1;
    if (mo >= 0 && mo <= 11) return new Date(y, mo, 1);
  }
  const d = new Date(joinedAt);
  if (Number.isNaN(d.getTime())) return null;
  return new Date(d.getFullYear(), d.getMonth(), 1);
}

/** Mes cobrable si es el mes de ingreso o cualquier mes posterior (mensualidad continua). */
function isMonthInBillingCycle (year, monthIndex, joinedAt) {
  const start = parseJoinedMonthStart(joinedAt);
  if (!start) return true;
  const monthStart = new Date(year, monthIndex, 1);
  return monthStart >= start;
}

function isEnrollmentMonth (year, monthIndex, joinedAt) {
  const start = parseJoinedMonthStart(joinedAt);
  if (!start) return false;
  return start.getFullYear() === year && start.getMonth() === monthIndex;
}

function daysInCalendarMonth (year, monthIndex) {
  return new Date(year, monthIndex + 1, 0).getDate();
}

function parseJoinDateLocal (joinedAt) {
  if (!joinedAt) return null;
  const raw = String(joinedAt).trim();
  const d = new Date(raw.includes('T') ? raw : `${raw}T12:00:00`);
  return Number.isNaN(d.getTime()) ? null : d;
}

/**
 * Primer mes de ingreso: divide la tarifa base por días o semanas restantes del mes calendario.
 * unit: 'day' | 'week'. Si ingresa el día 1, devuelve mes completo.
 */
function computeProratedFirstMonthAmount (settings, joinedAt, unit = 'day') {
  const s = normalizeAcademySettings(settings);
  const base = Number(s.monthly_fee) || 0;
  const join = parseJoinDateLocal(joinedAt);
  if (!base || !join) {
    return { amount: base, fullMonth: true, base, unit, formula: 'Tarifa mensual completa' };
  }
  const y = join.getFullYear();
  const m = join.getMonth();
  const dim = daysInCalendarMonth(y, m);
  const joinDay = join.getDate();
  if (joinDay <= 1) {
    return {
      amount: base,
      fullMonth: true,
      base,
      unit,
      daysRemaining: dim,
      daysInMonth: dim,
      formula: `Mes completo · $${formatMoneyMX(base)}`,
    };
  }
  const daysRemaining = dim - joinDay + 1;
  const daysInMonth = dim;
  let amount;
  let formula;
  let weeksRemaining;
  let weeksInMonth;
  if (unit === 'week') {
    weeksRemaining = Math.ceil(daysRemaining / 7);
    weeksInMonth = Math.ceil(daysInMonth / 7);
    amount = Math.round(base * (weeksRemaining / weeksInMonth) * 100) / 100;
    formula = `${weeksRemaining} semana(s) de ${weeksInMonth} × $${formatMoneyMX(base)} = $${formatMoneyMX(amount)}`;
  } else {
    amount = Math.round(base * (daysRemaining / daysInMonth) * 100) / 100;
    formula = `${daysRemaining} día(s) de ${daysInMonth} × $${formatMoneyMX(base)} = $${formatMoneyMX(amount)}`;
  }
  return {
    amount,
    fullMonth: false,
    base,
    unit,
    daysRemaining,
    daysInMonth,
    weeksRemaining: unit === 'week' ? weeksRemaining : null,
    weeksInMonth: unit === 'week' ? weeksInMonth : null,
    formula,
  };
}

/** Extrae "(proporcional 7 días)" o "(proporcional 2 sem.)" del concepto guardado en payments. */
function parseProrationFromConcept (concept) {
  const c = String(concept || '');
  const m = c.match(/\(proporcional\s+(\d+)\s*(d[ií]as|sem\.?)\)/i);
  if (!m) return null;
  const count = Number(m[1]) || 0;
  const unit = /sem/i.test(m[2]) ? 'week' : 'day';
  const label = unit === 'week'
    ? (count === 1 ? '1 semana' : `${count} semanas`)
    : (count === 1 ? '1 día' : `${count} días`);
  return { count, unit, label };
}

function getPaymentProrationMeta (payment, joinedAt, settings, year, monthIndex) {
  const baseMonthly = Number(settings?.monthly_fee) || 0;
  const storedAmount = Number(payment?.amount) || 0;
  const parsed = parseProrationFromConcept(payment?.concept);
  if (parsed) {
    return {
      prorated: true,
      prorationLabel: parsed.label,
      prorationUnit: parsed.unit,
      prorationCount: parsed.count,
      baseMonthly,
      storedAmount,
      prorationNote: `Cobro proporcional del mes de ingreso: ${parsed.label}`,
    };
  }
  if (!payment || !joinedAt || !isEnrollmentMonth(year, monthIndex, joinedAt)) {
    return { prorated: false, baseMonthly, storedAmount };
  }
  if (baseMonthly > 0 && storedAmount > 0 && storedAmount < baseMonthly * 0.995) {
    const pr = computeProratedFirstMonthAmount(settings, joinedAt, 'day');
    if (!pr.fullMonth) {
      const label = pr.unit === 'week'
        ? (pr.weeksRemaining === 1 ? '1 semana' : `${pr.weeksRemaining} semanas`)
        : (pr.daysRemaining === 1 ? '1 día' : `${pr.daysRemaining} días`);
      return {
        prorated: true,
        prorationLabel: label,
        prorationUnit: pr.unit,
        prorationCount: pr.unit === 'week' ? pr.weeksRemaining : pr.daysRemaining,
        baseMonthly,
        storedAmount,
        prorationNote: `Cobro proporcional del mes de ingreso: ${label}`,
      };
    }
  }
  return { prorated: false, baseMonthly, storedAmount };
}

function attachProrationToSlot (slot, meta) {
  if (!meta?.prorated) return slot;
  return {
    ...slot,
    prorated: true,
    prorationLabel: meta.prorationLabel,
    prorationUnit: meta.prorationUnit,
    prorationCount: meta.prorationCount,
    baseMonthlyFee: meta.baseMonthly,
    prorationNote: meta.prorationNote,
  };
}

function studentProrateSeedOptions (studentOrPayload) {
  const s = studentOrPayload || {};
  return {
    prorateFirstMonth: !!(s.prorate_enrollment_month ?? s.prorate_first_month),
    prorateUnit: (s.prorate_enrollment_unit || s.prorate_unit) === 'week' ? 'week' : 'day',
  };
}

function paymentAmountAndConceptForSeedMonth (year, monthIndex, joinedAt, settings, options = {}) {
  const base = Number(settings.monthly_fee) || 0;
  let concept = mensualidadConceptForMonth(year, monthIndex);
  if (
    options.prorateFirstMonth
    && joinedAt
    && isEnrollmentMonth(year, monthIndex, joinedAt)
  ) {
    const pr = computeProratedFirstMonthAmount(settings, joinedAt, options.prorateUnit || 'day');
    if (!pr.fullMonth) {
      const bit = pr.unit === 'week'
        ? `${pr.weeksRemaining} sem.`
        : `${pr.daysRemaining} días`;
      concept += ` (proporcional ${bit})`;
    }
    return { amount: pr.amount, concept };
  }
  return { amount: base, concept };
}

function mensualidadConceptForMonth (year, monthIndex) {
  const m = String(monthIndex + 1).padStart(2, '0');
  return `Mensualidad ${m}/${year}`;
}

function dueDateForBillingMonth (settings, year, monthIndex) {
  return defaultPaymentDueDate(settings, new Date(year, monthIndex, 15));
}

/** Primer mes del año en que aplica mensualidad (desde fecha de ingreso). 12 = ninguno en ese año. */
function firstBillableMonthInYear (year, joinedAt) {
  const join = parseJoinedMonthStart(joinedAt);
  if (!join) return 0;
  if (join.getFullYear() > year) return 12;
  if (join.getFullYear() === year) return join.getMonth();
  return 0;
}

function isMonthBeforeJoinInYear (year, monthIndex, joinedAt) {
  return monthIndex < firstBillableMonthInYear(year, joinedAt);
}

/** due_date en mes/año anterior al ingreso o fuera del periodo activo del alumno. */
function isPaymentBillableForJoin (payment, joinedAt) {
  if (!joinedAt) return true;
  const d = new Date(payment?.due_date);
  if (Number.isNaN(d.getTime())) return false;
  const y = d.getFullYear();
  const m = d.getMonth();
  if (isMonthBeforeJoinInYear(y, m, joinedAt)) return false;
  return isMonthInBillingCycle(y, m, joinedAt);
}

function filterPaymentsForJoin (payments, joinedAt) {
  if (!joinedAt) return payments || [];
  return (payments || []).filter((p) => isPaymentBillableForJoin(p, joinedAt));
}

function buildStudentJoinedAtMap (students) {
  const map = {};
  (students || []).forEach((s) => {
    if (s?.id) map[s.id] = s.joined_at;
  });
  return map;
}

function filterDashboardPayments (payments, joinedByStudentId) {
  return (payments || []).filter((p) => {
    const joinedAt = joinedByStudentId?.[p.student_id];
    if (!joinedAt) return true;
    return isPaymentBillableForJoin(p, joinedAt);
  });
}

/** Día del calendario usado para aplicar tramos de tarifa del mes que se cobra. */
function monthTierRefDate (year, monthIndex, asOf = new Date()) {
  const cy = asOf.getFullYear();
  const cm = asOf.getMonth();
  if (year < cy || (year === cy && monthIndex < cm)) {
    return new Date(year, monthIndex + 1, 0, 12, 0, 0);
  }
  if (year === cy && monthIndex === cm) {
    return asOf;
  }
  return new Date(year, monthIndex, 1, 12, 0, 0);
}

function isFutureBillingMonth (year, monthIndex, asOf = new Date()) {
  const cy = asOf.getFullYear();
  const cm = asOf.getMonth();
  return year > cy || (year === cy && monthIndex > cm);
}

/** Tarifa base del periodo sin recargo (configuración), sin cargos adicionales. */
function baseBillingMonthCharge (settings) {
  const s = normalizeAcademySettings(settings);
  const base = Number(s.monthly_fee) || 0;
  const range = formatDayRangeLabel(s.billing_period_start_day, s.billing_period_end_day);
  return {
    amt: base,
    formula: `$${formatMoneyMX(base)} (tarifa base, ${range})`,
  };
}

function attachBillingTierMeta (slot, settings, year, monthIndex, asOf = new Date()) {
  const s = normalizeAcademySettings(settings);
  const tierRef = monthTierRefDate(year, monthIndex, asOf);
  const tier = getBillingTierForDate(s, tierRef);
  const dom = dayOfMonthForBilling(tierRef);

  if (slot.advancePay && isFutureBillingMonth(year, monthIndex, asOf)) {
    const base = baseBillingMonthCharge(s);
    return {
      billingTier: 'normal',
      billingTierLabel: 'Sin recargo',
      billingRangeLabel: formatDayRangeLabel(s.billing_period_start_day, s.billing_period_end_day),
      billingDayUsed: dom,
      feeFormula: slot.feeFormula || base.formula,
    };
  }

  if (slot.hideAmount || slot.status === 'sin_registro') {
    return {};
  }

  const baseForFees = Number(slot.baseMonthlyFee) > 0
    ? Number(slot.baseMonthlyFee)
    : (Number(s.monthly_fee) || 0);
  let feeFormula = slot.feeFormula;
  if (!feeFormula && slot.status !== 'pagado') {
    const fees = computeLateFees(
      baseForFees,
      dueDateForBillingMonth(s, year, monthIndex),
      s,
      tierRef,
    );
    feeFormula = fees.formula;
  }

  return {
    billingTier: tier.tier,
    billingTierLabel: tier.label,
    billingRangeLabel: tier.rangeLabel,
    billingDayUsed: dom,
    feeFormula: feeFormula || slot.feeFormula || null,
  };
}

function projectedMonthCharge (settings, year, monthIndex, asOf = new Date(), options = {}) {
  const s = normalizeAcademySettings(settings);
  const dueDate = dueDateForBillingMonth(s, year, monthIndex);
  const concept = mensualidadConceptForMonth(year, monthIndex);

  if (isFutureBillingMonth(year, monthIndex, asOf)) {
    if (options.allowAdvancePay || options.adminAdvancePay) {
      const base = baseBillingMonthCharge(s);
      return {
        dueDate,
        concept,
        amt: base.amt,
        status: 'pendiente',
        hideAmount: false,
        advancePay: true,
        feeFormula: base.formula,
      };
    }
    return { dueDate, concept, amt: 0, status: 'pendiente', hideAmount: true };
  }

  const tierRef = monthTierRefDate(year, monthIndex, asOf);
  const virtual = { amount: s.monthly_fee, due_date: dueDate, status: 'pendiente' };
  const fees = computeLateFees(s.monthly_fee, dueDate, s, tierRef);
  const tier = getBillingTierForDate(s, tierRef);
  return {
    dueDate,
    concept,
    amt: fees.total,
    status: paymentDisplayEstado(virtual, s),
    hideAmount: false,
    feeFormula: fees.formula,
    billingTier: tier.tier,
    billingTierLabel: tier.label,
    billingRangeLabel: tier.rangeLabel,
    billingDayUsed: fees.dayOfMonth,
  };
}

function monthChargeFromPayment (p, settings, year, monthIndex, asOf = new Date()) {
  const s = normalizeAcademySettings(settings);
  const base = Number(p?.amount) > 0 ? Number(p.amount) : Number(s.monthly_fee) || 0;

  if (isFutureBillingMonth(year, monthIndex, asOf)) {
    if (p?.status === 'pagado') {
      return { amt: base, status: 'pagado', hideAmount: false };
    }
    if (p?.status === 'en_revision') {
      return { amt: base, status: 'revision', hideAmount: false, advancePay: true };
    }
    if (p) {
      return {
        amt: base,
        status: paymentDisplayEstado(p, s),
        hideAmount: false,
        advancePay: true,
      };
    }
    return { amt: 0, status: 'pendiente', hideAmount: true };
  }

  if (p.status === 'pagado') {
    return { amt: base, status: 'pagado', hideAmount: false };
  }
  const tierRef = monthTierRefDate(year, monthIndex, asOf);
  const fees = computeLateFees(base, p.due_date, s, tierRef);
  const tier = getBillingTierForDate(s, tierRef);
  const virtual = { amount: base, due_date: p.due_date, status: p.status };
  return {
    amt: fees.total,
    status: paymentDisplayEstado(virtual, s),
    hideAmount: false,
    feeFormula: fees.formula,
    billingTier: tier.tier,
    billingTierLabel: tier.label,
    billingRangeLabel: tier.rangeLabel,
    billingDayUsed: fees.dayOfMonth,
  };
}

/** Copia fila payments al slot del calendario (PDF / comprobante). */
function applySlotPaymentFromRow (slot, p, prorationCtx) {
  if (!p) return slot;
  const y = prorationCtx?.year ?? slot.calendarYear ?? new Date().getFullYear();
  const mi = prorationCtx?.monthIndex ?? slot.monthIndex;
  const meta = getPaymentProrationMeta(
    p,
    prorationCtx?.joinedAt,
    prorationCtx?.settings,
    y,
    mi,
  );
  const next = {
    ...slot,
    paymentId: p.id,
    concept: p.concept ?? slot.concept,
    adminNotes: p.admin_notes || slot.adminNotes || null,
    paidAt: p.paid_at || null,
    receiptPath: p.receipt_path || null,
    transferRef: p.transfer_reference || null,
    dueDate: p.due_date || slot.dueDate || null,
    rawPayment: {
      id: p.id,
      concept: p.concept,
      amount: p.amount,
      due_date: p.due_date,
      paid_at: p.paid_at,
      status: p.status,
      receipt_path: p.receipt_path,
      transfer_reference: p.transfer_reference,
      admin_notes: p.admin_notes,
      created_at: p.created_at,
    },
  };
  return attachProrationToSlot(next, meta);
}

function mapPortalPaymentsToMonths (payments, year, settings, joinedAt, options = {}) {
  const y = year || new Date().getFullYear();
  const billablePayments = filterPaymentsForJoin(payments, joinedAt);
  const recordsOnly = options.mode === 'records_only';
  const billingSchedule = options.mode === 'billing_schedule';
  const allowAdvancePay = !!(options.allowAdvancePay || options.adminAdvancePay);
  const calendarYear = !recordsOnly && !billingSchedule && options.mode === 'calendar_year';
  const now = new Date();
  const s = normalizeAcademySettings(settings);
  const currentMonth = now.getMonth();
  const isCurrentYear = y === now.getFullYear();
  const slots = MONTH_KEYS.map((key, i) => {
    const inCycle = recordsOnly
      ? false
      : (calendarYear || isMonthInBillingCycle(y, i, joinedAt));
    return {
      key,
      monthIndex: i,
      name: MONTH_LABELS[i],
      status: 'sin_registro',
      amt: s.monthly_fee,
      paymentId: null,
      concept: null,
      inCycle,
      isCurrentMonth: y === now.getFullYear() && i === now.getMonth(),
      calendarYear: y,
    };
  });

  const paymentByMonth = {};
  billablePayments.forEach((p) => {
    const d = new Date(p.due_date);
    if (Number.isNaN(d.getTime()) || d.getFullYear() !== y) return;
    const m = d.getMonth();
    const prev = paymentByMonth[m];
    if (!prev || p.status === 'pagado') paymentByMonth[m] = p;
    else if (prev.status !== 'pagado') paymentByMonth[m] = p;
  });

  if (billingSchedule) {
    const first = firstBillableMonthInYear(y, joinedAt);
    const prorationCtx = { joinedAt, settings: s, year: y };
    slots.forEach((slot, i) => {
      const outOfScope = i < first
        || (joinedAt && !isMonthInBillingCycle(y, i, joinedAt));
      slot.inCycle = !outOfScope;
      const finalizeSlot = () => {
        Object.assign(slot, attachBillingTierMeta(slot, s, y, i, now));
      };
      if (outOfScope) {
        slot.status = 'sin_registro';
        slot.amt = 0;
        slot.hideAmount = true;
        return;
      }

      const p = paymentByMonth[i];
      const monthPaid = isMonthPaidInDb(billablePayments, y, i, joinedAt);
      const isFuture = isFutureBillingMonth(y, i, now);
      if (isFuture) {
        if (monthPaid || p?.status === 'pagado') {
          const paidP = billablePayments.find((row) => {
            if (row.status !== 'pagado') return false;
            const d = new Date(row.due_date);
            return d.getFullYear() === y && d.getMonth() === i;
          }) || p;
          const paid = monthChargeFromPayment(paidP, s, y, i, now);
          Object.assign(slot, applySlotPaymentFromRow({
            ...slot,
            status: 'pagado',
            amt: paid.amt,
            hideAmount: false,
            concept: paidP?.concept || mensualidadConceptForMonth(y, i),
          }, paidP, { ...prorationCtx, monthIndex: i }));
          finalizeSlot();
        } else if (allowAdvancePay) {
          const base = baseBillingMonthCharge(s);
          if (p && p.status !== 'pagado') {
            slot.status = p.status === 'en_revision' ? 'revision' : (p.status === 'pendiente' ? 'pendiente' : paymentDisplayEstado(p, s));
            slot.amt = Number(p.amount) || base.amt;
            slot.hideAmount = false;
            slot.advancePay = true;
            slot.paymentId = p.id;
            slot.concept = p.concept;
            Object.assign(slot, attachProrationToSlot(slot, getPaymentProrationMeta(p, joinedAt, s, y, i)));
          } else {
            slot.status = 'pendiente';
            slot.amt = base.amt;
            slot.hideAmount = false;
            slot.advancePay = true;
            slot.paymentId = p?.id || null;
            slot.concept = mensualidadConceptForMonth(y, i);
            slot.feeFormula = base.formula;
          }
          finalizeSlot();
        } else {
          slot.status = 'pendiente';
          slot.amt = 0;
          slot.hideAmount = true;
          slot.paymentId = p?.id || null;
          slot.concept = mensualidadConceptForMonth(y, i);
        }
        return;
      }

      if (monthPaid) {
        const paidP = billablePayments.find((row) => {
          if (row.status !== 'pagado') return false;
          const d = new Date(row.due_date);
          return d.getFullYear() === y && d.getMonth() === i;
        }) || p;
        const paid = monthChargeFromPayment(paidP, s, y, i, now);
        Object.assign(slot, applySlotPaymentFromRow({
          ...slot,
          status: 'pagado',
          amt: paid.amt,
          hideAmount: false,
          concept: paidP?.concept || mensualidadConceptForMonth(y, i),
        }, paidP, { ...prorationCtx, monthIndex: i }));
        finalizeSlot();
        return;
      }

      if (p) {
        let charge = monthChargeFromPayment(p, s, y, i, now);
        if (allowAdvancePay && isFutureBillingMonth(y, i, now) && p.status !== 'pagado') {
          const base = baseBillingMonthCharge(s);
          charge = {
            amt: Number(p.amount) || base.amt,
            status: p.status === 'en_revision' ? 'revision' : paymentDisplayEstado(p, s),
            hideAmount: false,
            advancePay: true,
          };
        }
        slot.status = charge.status === 'en_revision' ? 'revision' : charge.status;
        if (p.status === 'en_revision') slot.status = 'revision';
        slot.amt = charge.amt;
        slot.hideAmount = charge.hideAmount;
        slot.advancePay = !!charge.advancePay;
        slot.feeFormula = charge.feeFormula || slot.feeFormula;
        Object.assign(slot, applySlotPaymentFromRow(slot, p, { ...prorationCtx, monthIndex: i }));
        finalizeSlot();
        return;
      }

      const proj = projectedMonthCharge(s, y, i, now, { allowAdvancePay });
      slot.status = proj.status;
      slot.amt = proj.amt;
      slot.hideAmount = proj.hideAmount;
      slot.advancePay = !!proj.advancePay;
      slot.concept = proj.concept;
      slot.feeFormula = proj.feeFormula || null;
      finalizeSlot();
    });
  } else {
    const prorationCtx = { joinedAt, settings: s, year: y };
    const applyPaymentToSlot = (idx, p) => {
      const base = Number(p.amount) || 0;
      const tierRef = monthTierRefDate(y, idx, now);
      const fees = p.status === 'pagado' ? { total: base } : computeLateFees(base, p.due_date, s, tierRef);
      let status = p.status;
      if (status === 'en_revision') status = 'revision';
      else if (status === 'pendiente' || status === 'vencido') status = paymentDisplayEstado(p, s);
      slots[idx] = applySlotPaymentFromRow({
        ...slots[idx],
        status,
        amt: fees.total,
        hideAmount: false,
        concept: p.concept,
      }, p, { ...prorationCtx, monthIndex: idx });
    };
    Object.keys(paymentByMonth).forEach((k) => {
      const idx = Number(k);
      if (isMonthBeforeJoinInYear(y, idx, joinedAt)) return;
      if (joinedAt && !isMonthInBillingCycle(y, idx, joinedAt)) return;
      applyPaymentToSlot(idx, paymentByMonth[k]);
    });
  }

  if (!billingSchedule && !recordsOnly && calendarYear) {
    slots.forEach((slot, i) => {
      if (!slot.inCycle || slot.paymentId) return;
      slot.status = 'pendiente';
      slot.concept = mensualidadConceptForMonth(y, i);
    });
  }

  return slots;
}

/** Mes con al menos un pago marcado pagado en la base (solo meses cobrables si hay ingreso). */
function isMonthPaidInDb (payments, year, monthIndex, joinedAt) {
  if (joinedAt) {
    if (isMonthBeforeJoinInYear(year, monthIndex, joinedAt)) return false;
    if (!isMonthInBillingCycle(year, monthIndex, joinedAt)) return false;
  }
  return (payments || []).some((p) => {
    const st = String(p.status || '').toLowerCase().trim();
    if (st !== 'pagado') return false;
    const d = new Date(p.due_date);
    return !Number.isNaN(d.getTime()) && d.getFullYear() === year && d.getMonth() === monthIndex;
  });
}

/**
 * Adeudo en lista: solo el mes en curso (cobro por periodo).
 * Si el mes actual está pagado → 0. Meses futuros → 0 hasta que lleguen.
 */
function computeStudentAdeudoAmount (payments, settings, joinedAt) {
  const y = new Date().getFullYear();
  const cm = new Date().getMonth();
  const first = firstBillableMonthInYear(y, joinedAt);
  if (cm < first) return 0;
  const billable = filterPaymentsForJoin(payments, joinedAt);
  if (isMonthPaidInDb(billable, y, cm, joinedAt)) return 0;
  if (isFutureBillingMonth(y, cm)) return 0;

  const slots = mapPortalPaymentsToMonths(billable, y, settings, joinedAt, { mode: 'billing_schedule' });
  const slot = slots[cm];
  if (!slot || slot.hideAmount || slot.status === 'pagado' || slot.status === 'sin_registro') return 0;

  const openStatuses = new Set(['pendiente', 'vencido', 'urgente', 'revision', 'en_revision', 'rechazado']);
  if (!openStatuses.has(slot.status)) return 0;

  return Math.round((Number(slot.amt) || 0) * 100) / 100;
}

function waBillingKindLabel (kind) {
  const labels = {
    al_corriente: 'Al corriente',
    pendiente: 'Recordatorio de pago',
    vencido: 'Adeudo con atraso',
    urgente: 'Aviso urgente',
    revision: 'Comprobante en revisión',
    rechazado: 'Pago rechazado',
  };
  return labels[kind] || 'Mensaje';
}

/** Estado de cobro del mes en curso para mensajes WhatsApp (alineado con adeudo en lista). */
function resolveCurrentMonthBillingWa (payments, settings, joinedAt) {
  const y = new Date().getFullYear();
  const cm = new Date().getMonth();
  const s = normalizeAcademySettings(settings);
  const dueDate = dueDateForBillingMonth(s, y, cm);
  const due = new Date(dueDate);
  const mes = due.toLocaleDateString('es-MX', { month: 'short', year: 'numeric' });
  const lim = due.toLocaleDateString('es-MX', { day: 'numeric', month: 'short', year: 'numeric' });
  const daysLate = daysLateFromDue(dueDate);
  const concept = mensualidadConceptForMonth(y, cm);
  const adeudo = computeStudentAdeudoAmount(payments, settings, joinedAt);

  const billable = filterPaymentsForJoin(payments, joinedAt);
  const slots = mapPortalPaymentsToMonths(billable, y, settings, joinedAt, { mode: 'billing_schedule' });
  const slot = slots[cm];
  const slotStatus = slot?.status;

  let kind = 'al_corriente';
  if (adeudo > 0) {
    if (slotStatus === 'revision') kind = 'revision';
    else if (slotStatus === 'rechazado') kind = 'rechazado';
    else if (slotStatus === 'urgente') kind = 'urgente';
    else if (slotStatus === 'vencido') kind = 'vencido';
    else if (daysLate > (s.severe_late_days || 0)) kind = 'urgente';
    else if (daysLate > 0) kind = 'vencido';
    else kind = 'pendiente';
  } else if (slotStatus === 'revision') {
    kind = 'revision';
  }

  const monto = adeudo > 0 ? adeudo : (Number(slot?.amt) || 0);
  return { kind, mes, lim, daysLate, concept, monto, adeudo };
}

function buildWaRecipientFromStudentSync (student, settings, payments) {
  const joinedAt = student.joined_at || student.joinedAt || null;
  const billing = resolveCurrentMonthBillingWa(payments || [], settings, joinedAt);
  const urgent = billing.kind === 'urgente' || billing.kind === 'vencido';
  return {
    name: student.name,
    alumno: student.name,
    id: student.id,
    tutor: student.tutor,
    tel: student.phone,
    phone: student.phone,
    mes: billing.mes,
    concepto: billing.concept,
    monto: billing.kind === 'al_corriente' ? null : billing.monto,
    lim: billing.lim,
    dias: billing.daysLate,
    daysLate: billing.daysLate,
    billingKind: billing.kind,
    billingStatus: billing.kind,
    tipo: waBillingKindLabel(billing.kind),
    urgent,
    _uuid: student._uuid,
  };
}

async function buildWaRecipientFromStudent (student, settingsOpt) {
  const settings = settingsOpt || await fetchAcademySettings();
  const sb = getSupabase();
  let payments = [];
  if (sb && student?._uuid) {
    const res = await sb.from('payments')
      .select('id, concept, amount, due_date, paid_at, status, receipt_path, transfer_reference, admin_notes, created_at')
      .eq('student_id', student._uuid);
    if (res.error) throw res.error;
    payments = res.data || [];
  }
  return buildWaRecipientFromStudentSync(student, settings, payments);
}

async function fetchStudentPaymentCalendar (studentUuid, year, joinedAt, options = {}) {
  const sb = getSupabase();
  if (!sb || !studentUuid) return [];
  const y = year || new Date().getFullYear();
  const [settings, paymentsRes] = await Promise.all([
    fetchAcademySettings(),
    sb.from('payments')
      .select('id, concept, amount, due_date, paid_at, status, admin_notes, receipt_path, transfer_reference, created_at')
      .eq('student_id', studentUuid),
  ]);
  if (paymentsRes.error) throw paymentsRes.error;
  const billable = filterPaymentsForJoin(paymentsRes.data, joinedAt);
  return mapPortalPaymentsToMonths(billable, y, settings, joinedAt, {
    mode: 'billing_schedule',
    allowAdvancePay: !!(options.allowAdvancePay || options.adminAdvancePay),
  });
}

function mapPortalOrders (orders) {
  return (orders || []).map((o, i) => ({
    id: o.id || `o${i}`,
    orderId: o.id,
    p: o.product_summary || 'Pedido tienda',
    d: new Date(o.created_at).toLocaleDateString('es-MX', { day: 'numeric', month: 'short', year: 'numeric' }),
    n: o.order_number?.startsWith('#') ? o.order_number : `#${o.order_number || ''}`,
    a: Number(o.total) || 0,
    s: o.status,
    i: 'cart',
    phone: o.guest_phone || '',
    studentCode: o.guest_student_code || '',
    transferRef: o.transfer_reference || '',
    adminNotes: o.admin_notes || null,
    receipt_path: o.receipt_path || null,
    created_at: o.created_at,
    order_number: o.order_number,
  }));
}

function mapPortalPaymentsExpediente (payments, settings, joinedAt) {
  const s = normalizeAcademySettings(settings);
  const now = new Date();
  const rows = filterPaymentsForJoin(payments, joinedAt);
  return rows.slice().sort((a, b) => new Date(b.due_date) - new Date(a.due_date)).map((p) => {
    const due = new Date(p.due_date);
    const year = due.getFullYear();
    const monthIndex = due.getMonth();
    const mesLabel = due.toLocaleDateString('es-MX', { month: 'long', year: 'numeric' });
    const future = !Number.isNaN(due.getTime()) && isFutureBillingMonth(year, monthIndex, now);

    const rawPayment = {
      id: p.id,
      concept: p.concept,
      amount: p.amount,
      due_date: p.due_date,
      paid_at: p.paid_at,
      status: p.status,
      receipt_path: p.receipt_path || null,
      transfer_reference: p.transfer_reference || null,
      admin_notes: p.admin_notes || null,
      created_at: p.created_at,
    };

    const prMeta = getPaymentProrationMeta(p, joinedAt, s, year, monthIndex);

    if (p.status === 'pagado') {
      const base = Number(p.amount) > 0 ? Number(p.amount) : Number(s.monthly_fee) || 0;
      return {
        m: mesLabel,
        c: p.concept,
        a: base,
        aBase: base,
        recargo: 0,
        d: p.paid_at ? new Date(p.paid_at).toLocaleDateString('es-MX', { day: 'numeric', month: 'short' }) : '—',
        s: 'pagado',
        id: p.id,
        hideAmount: false,
        raw: rawPayment,
        ...prMeta,
      };
    }

    if (p.status === 'en_revision') {
      const base = Number(p.amount) || s.monthly_fee;
      return {
        m: mesLabel,
        c: p.concept,
        a: base,
        aBase: base,
        recargo: 0,
        d: 'En verificación',
        s: 'revision',
        id: p.id,
        hideAmount: false,
        raw: rawPayment,
        ...prMeta,
      };
    }

    const charge = monthChargeFromPayment(p, s, year, monthIndex, now);
    const dueLabel = future
      ? 'Tarifa al iniciar mes'
      : (due.toLocaleDateString('es-MX', { day: 'numeric', month: 'short', year: 'numeric' }));
    return {
      m: mesLabel,
      c: p.concept,
      a: charge.hideAmount ? 0 : charge.amt,
      aBase: Number(p.amount) || s.monthly_fee,
      recargo: charge.hideAmount ? 0 : Math.max(0, charge.amt - (Number(p.amount) || s.monthly_fee)),
      d: future ? dueLabel : (p.paid_at
        ? new Date(p.paid_at).toLocaleDateString('es-MX', { day: 'numeric', month: 'short' })
        : `Vence ${dueLabel}`),
      s: charge.status,
      id: p.id,
      hideAmount: charge.hideAmount,
      raw: rawPayment,
      ...prMeta,
    };
  });
}

function paymentDisplayEstado (p, settings) {
  if (p.status === 'pagado') return 'pagado';
  if (p.status === 'en_revision') return 'revision';
  if (p.status === 'rechazado') return 'rechazado';
  if (p.status === 'vencido') return 'vencido';
  const s = normalizeAcademySettings(settings);
  const daysLate = daysLateFromDue(p.due_date);
  if (daysLate > s.severe_late_days) return 'urgente';
  if (daysLate > 0) return 'vencido';
  return 'pendiente';
}

function mapPaymentRow (p, settings) {
  const st = p.students;
  const due = p.due_date ? new Date(p.due_date) : null;
  const mes = due
    ? due.toLocaleDateString('es-MX', { month: 'short', year: 'numeric' })
    : (p.concept || '—');
  const code = st?.code || '—';
  const montoBase = Number(p.amount) || 0;
  const fees = p.status === 'pagado'
    ? { base: montoBase, surcharge: 0, total: montoBase, daysLate: 0, formula: `$${formatMoneyMX(montoBase)} (pagado)`, surchargeLines: [], tierLabel: 'Pagado' }
    : computeLateFees(montoBase, p.due_date, settings);
  return {
    id: code,
    _paymentId: p.id,
    _studentUuid: p.student_id || null,
    name: st?.full_name?.split(' ').slice(0, 2).join(' ') || '—',
    fullName: st?.full_name || '—',
    tutor: st?.tutor_name || '—',
    phone: st?.tutor_phone || '—',
    mes,
    concepto: p.concept || 'Mensualidad',
    monto: fees.total,
    montoBase: fees.base,
    recargo: fees.surcharge,
    daysLate: fees.daysLate,
    feeFormula: fees.formula,
    tierLabel: fees.tierLabel,
    surchargeLines: fees.surchargeLines || [],
    lim: due ? due.toLocaleDateString('es-MX', { day: 'numeric', month: 'short', year: 'numeric' }) : '—',
    due_date: p.due_date,
    pago: p.paid_at ? new Date(p.paid_at).toLocaleDateString('es-MX', { day: 'numeric', month: 'short', year: 'numeric' }) : '—',
    estado: paymentDisplayEstado(p, settings),
    dbStatus: p.status,
    metodo: p.receipt_path ? 'Transferencia' : '—',
    receipt_path: p.receipt_path,
    transfer_reference: p.transfer_reference,
    admin_notes: p.admin_notes,
    isDemo: isDemoStudentCode(code),
  };
}

function exportPaymentsCsv (pagos, filterLabel) {
  const tag = filterLabel ? String(filterLabel).replace(/\s+/g, '-').toLowerCase() : 'todos';
  downloadCsv(`pagos-tecos-${tag}-${new Date().toISOString().slice(0, 10)}.csv`, [
    ['ID alumno', 'Alumno', 'Mes', 'Concepto', 'Base', 'Recargo', 'Total', 'Fecha límite', 'Fecha pago', 'Estado', 'Método'],
    ...pagos.map(p => [p.id, p.fullName, p.mes, p.concepto, p.montoBase, p.recargo || 0, p.monto, p.lim, p.pago, p.estado, p.metodo]),
  ]);
}

/**
 * Evento central: dashboard, alumnos, pagos y nómina recargan con la misma fuente (fetchAcademySettings + tablas).
 * source: 'payments' | 'finance' | 'settings' | 'students' | 'orders' | 'comprobantes'
 */
function notifyAcademyDataSync (source, detail) {
  window.dispatchEvent(new CustomEvent('tecos:academy-data-sync', {
    detail: { source: source || 'unknown', at: Date.now(), ...(detail || {}) },
  }));
}

function notifyPaymentsChanged () {
  notifyAcademyDataSync('payments');
  window.dispatchEvent(new CustomEvent('tecos:comprobantes-changed'));
  window.dispatchEvent(new CustomEvent('tecos:payments-changed'));
}

function notifyStudentsChanged (detail) {
  notifyAcademyDataSync('students', detail);
  window.dispatchEvent(new CustomEvent('tecos:students-changed', { detail: detail || {} }));
}

function notifyStudentDocumentsChanged (detail) {
  notifyAcademyDataSync('students', detail);
  window.dispatchEvent(new CustomEvent('tecos:student-documents-changed', { detail: detail || {} }));
  window.dispatchEvent(new CustomEvent('tecos:students-changed', { detail: detail || {} }));
}

function notifyComprobantesChanged () {
  notifyAcademyDataSync('comprobantes');
  window.dispatchEvent(new CustomEvent('tecos:comprobantes-changed'));
}

function notifyAnnouncementsChanged () {
  window.dispatchEvent(new CustomEvent('tecos:announcements-changed'));
}

/** datetime-local del formulario → ISO (hora local del navegador, sin corrimiento UTC). */
function datetimeLocalToIso (localValue) {
  if (!localValue || typeof localValue !== 'string') return null;
  const trimmed = localValue.trim();
  if (!trimmed) return null;
  const d = new Date(trimmed);
  if (Number.isNaN(d.getTime())) return null;
  return d.toISOString();
}

function isAnnouncementPublished (row) {
  if (!row?.published_at) return false;
  const t = new Date(row.published_at).getTime();
  return !Number.isNaN(t) && t <= Date.now() + 120000;
}

function announcementImageUrl (path) {
  if (!path) return null;
  return typeof getStoragePublicUrl === 'function' ? getStoragePublicUrl('gallery', path) : null;
}

function formatAnnouncementVigencia (a) {
  const ref = a?.starts_at || a?.published_at;
  if (!ref) return '—';
  const start = new Date(ref);
  if (Number.isNaN(start.getTime())) return '—';
  if (a.multi_day && a.ends_at) {
    const end = new Date(a.ends_at);
    if (!Number.isNaN(end.getTime()) && formatEventDayKey(start) !== formatEventDayKey(end)) {
      return `${start.toLocaleDateString('es-MX', { day: 'numeric', month: 'short' })} – ${end.toLocaleDateString('es-MX', { day: 'numeric', month: 'short', year: 'numeric' })}`;
    }
  }
  return start.toLocaleDateString('es-MX', { day: 'numeric', month: 'long', year: 'numeric' });
}

/** Aviso con fecha (starts_at o published_at) → ítem del calendario público */
function mapAnnouncementCalendar (a, theme) {
  const refIso = a?.starts_at || a?.published_at;
  if (!refIso) return null;
  const card = mapAnnouncementCard(a, theme);
  const start = new Date(refIso);
  if (Number.isNaN(start.getTime())) return null;
  const end = a.ends_at ? new Date(a.ends_at) : null;
  const dayKeys = buildEventDayKeys({ ...a, starts_at: refIso });
  const monthAbbr = start.toLocaleDateString('es-MX', { month: 'short' }).replace('.', '').toUpperCase();
  return {
    id: a.id,
    itemKind: 'anuncio',
    day: start.getDate(),
    month: start.getMonth(),
    year: start.getFullYear(),
    monthAbbr,
    dayKeys,
    viewYear: start.getFullYear(),
    viewMonth: start.getMonth(),
    name: a.title,
    description: a.body || '',
    location: a.location || '',
    mapsUrl: a.maps_url || '',
    imageUrl: card.imageUrl,
    imageFocusX: card.imageFocusX,
    imageFocusY: card.imageFocusY,
    multiDay: a.multi_day === true,
    type: 'aviso',
    tag: card.tag,
    time: card.time,
    dateLabel: card.dateLabel,
    color: card.color,
    _raw: a,
  };
}

function mapAnnouncementCard (a, theme) {
  const tag = (a.priority || 'normal').toLowerCase();
  const colors = { urgente: theme.urgent, alta: theme.warning, normal: theme.info };
  const color = colors[tag] || theme.primary;
  return {
    id: a.id,
    tag,
    icon: tag === 'urgente' ? 'warning' : 'megaphone',
    title: a.title,
    body: a.body,
    location: a.location || '',
    mapsUrl: a.maps_url || '',
    imageUrl: announcementImageUrl(a.image_path),
    imageFocusX: Number(a.image_focus_x) || 50,
    imageFocusY: Number(a.image_focus_y) || 50,
    date: a.published_at
      ? new Date(a.published_at).toLocaleDateString('es-MX', { day: 'numeric', month: 'short' })
      : 'Borrador',
    dateLabel: formatAnnouncementVigencia(a),
    time: (a.starts_at || a.published_at)
      ? new Date(a.starts_at || a.published_at).toLocaleTimeString('es-MX', { hour: '2-digit', minute: '2-digit' })
      : '',
    dur: formatAnnouncementVigencia(a),
    color,
    _raw: a,
  };
}

function formatEventDayKey (d) {
  const dt = d instanceof Date ? d : new Date(d);
  if (Number.isNaN(dt.getTime())) return '';
  return `${dt.getFullYear()}-${String(dt.getMonth() + 1).padStart(2, '0')}-${String(dt.getDate()).padStart(2, '0')}`;
}

function buildEventDayKeys (e) {
  const start = new Date(e.starts_at);
  if (Number.isNaN(start.getTime())) return [];
  if (!e.multi_day) return [formatEventDayKey(start)];
  const end = e.ends_at ? new Date(e.ends_at) : start;
  const keys = [];
  const cur = new Date(start);
  cur.setHours(12, 0, 0, 0);
  const last = new Date(end);
  last.setHours(12, 0, 0, 0);
  if (last < cur) return [formatEventDayKey(start)];
  while (cur <= last) {
    keys.push(formatEventDayKey(cur));
    cur.setDate(cur.getDate() + 1);
  }
  return keys;
}

function eventImageUrl (path) {
  if (!path) return null;
  return typeof getStoragePublicUrl === 'function' ? getStoragePublicUrl('gallery', path) : null;
}

function mapBirthdayCalendar (row, theme) {
  const d = row.calendar_date ? new Date(row.calendar_date) : null;
  if (!d || Number.isNaN(d.getTime())) return null;
  const dayKey = formatEventDayKey(d);
  const monthAbbr = d.toLocaleDateString('es-MX', { month: 'short' }).replace('.', '').toUpperCase();
  const age = row.birth_date && typeof ageFromBirthDate === 'function'
    ? ageFromBirthDate(row.birth_date)
    : null;
  return {
    id: `bday-${row.student_id}-${dayKey}`,
    itemKind: 'birthday',
    day: d.getDate(),
    month: d.getMonth(),
    year: d.getFullYear(),
    monthAbbr,
    dayKeys: [dayKey],
    viewYear: d.getFullYear(),
    viewMonth: d.getMonth(),
    name: `🎂 ${row.full_name || 'Alumno'}`,
    description: age && age !== '—' ? `Cumpleaños · ${age} años` : 'Cumpleaños del alumno',
    location: '',
    type: 'cumpleaños',
    time: '',
    dateLabel: d.toLocaleDateString('es-MX', { weekday: 'long', day: 'numeric', month: 'long' }),
    color: theme.urgent,
    imageUrl: row.photo_path ? eventImageUrl(row.photo_path) : null,
    imageFocusX: 50,
    imageFocusY: 50,
    _raw: row,
  };
}

function mapEventCalendar (e, theme) {
  const start = new Date(e.starts_at);
  const end = e.ends_at ? new Date(e.ends_at) : null;
  const cat = (e.category || 'general').toLowerCase();
  const colors = {
    torneo: theme.warning,
    entrenamiento: theme.info,
    reunión: theme.primary,
    reunion: theme.primary,
    pago: theme.danger,
    cumpleaños: theme.urgent,
    cumpleanos: theme.urgent,
    general: theme.primary,
  };
  const monthAbbr = start.toLocaleDateString('es-MX', { month: 'short' }).replace('.', '').toUpperCase();
  const dayKeys = buildEventDayKeys(e);
  let dateLabel = start.toLocaleDateString('es-MX', { day: 'numeric', month: 'long', year: 'numeric' });
  if (e.multi_day && end && formatEventDayKey(start) !== formatEventDayKey(end)) {
    dateLabel = `${start.toLocaleDateString('es-MX', { day: 'numeric', month: 'short' })} – ${end.toLocaleDateString('es-MX', { day: 'numeric', month: 'short', year: 'numeric' })}`;
  }
  return {
    id: e.id,
    itemKind: 'event',
    day: start.getDate(),
    month: start.getMonth(),
    year: start.getFullYear(),
    monthAbbr,
    dayKeys,
    viewYear: start.getFullYear(),
    viewMonth: start.getMonth(),
    name: e.title,
    description: e.description || '',
    location: e.location || '',
    mapsUrl: e.maps_url || '',
    imageUrl: eventImageUrl(e.image_path),
    imageFocusX: Number(e.image_focus_x) || 50,
    imageFocusY: Number(e.image_focus_y) || 50,
    multiDay: e.multi_day === true,
    type: cat,
    time: start.toLocaleTimeString('es-MX', { hour: '2-digit', minute: '2-digit' }),
    dateLabel,
    color: colors[cat] || theme.primary,
    _raw: e,
  };
}

function parseCoachLines (text) {
  if (!text) return [];
  return String(text).split('\n').map(s => s.trim()).filter(Boolean);
}

function coachPhotoUrl (c) {
  if (!c?.photo_path) return null;
  const bucket = c.photo_path.startsWith('coaches/') || c.photo_path.startsWith('students/')
    ? 'gallery'
    : 'public-assets';
  return typeof getStoragePublicUrl === 'function'
    ? getStoragePublicUrl(bucket, c.photo_path)
    : null;
}

function coachPhotoFocus (c) {
  const x = Number(c?.photo_focus_x);
  const y = Number(c?.photo_focus_y);
  return {
    x: Number.isFinite(x) ? x : 50,
    y: Number.isFinite(y) ? y : 50,
  };
}

function coachPhotoObjectPosition (c) {
  const { x, y } = coachPhotoFocus(c);
  return `${x}% ${y}%`;
}

function mapCoachCard (c) {
  const exp = (c.experience_years || '').trim();
  const focus = coachPhotoFocus(c);
  return {
    id: c.id,
    name: c.name,
    role: c.specialty || 'Entrenador',
    spec: c.card_summary || c.bio || '',
    exp: exp || null,
    icon: c.icon || 'whistle',
    photoUrl: coachPhotoUrl(c),
    photoFocusX: focus.x,
    photoFocusY: focus.y,
    trajectory: c.trajectory || c.bio || '',
    certifications: parseCoachLines(c.certifications),
    assignedGroups: parseCoachLines(c.assigned_groups),
    _raw: c,
  };
}

function notifyCoachesChanged () {
  window.dispatchEvent(new CustomEvent('tecos:coaches-changed'));
}

async function fetchStudentsAdmin () {
  const sb = getSupabase();
  const [studentsRes, paymentsRes, settings] = await Promise.all([
    sb.from('students')
      .select('id, code, full_name, birth_date, category, status, joined_at, tutor_name, tutor_phone, tutor_email, student_phone, notes, photo_path, portal_must_change_password, document_requirements')
      .order('full_name'),
    sb.from('payments')
      .select('student_id, amount, due_date, status'),
    fetchAcademySettings(),
  ]);
  if (studentsRes.error) throw studentsRes.error;
  if (paymentsRes.error) throw paymentsRes.error;
  const byStudent = {};
  (paymentsRes.data || []).forEach((p) => {
    if (!byStudent[p.student_id]) byStudent[p.student_id] = [];
    byStudent[p.student_id].push(p);
  });
  const adeudoMap = {};
  return (studentsRes.data || []).map((s) => {
    const sp = filterPaymentsForJoin(byStudent[s.id] || [], s.joined_at);
    const adeudo = computeStudentAdeudoAmount(sp, settings, s.joined_at);
    return mapStudentRow(s, { [s.id]: adeudo });
  });
}

async function fetchPaymentsAdmin () {
  const sb = getSupabase();
  const [settings, paymentsRes] = await Promise.all([
    fetchAcademySettings(),
    sb.from('payments')
      .select(`
        id, student_id, concept, amount, due_date, paid_at, status, receipt_path,
        transfer_reference, admin_notes,
        students ( code, full_name, tutor_name, tutor_phone, joined_at )
      `)
      .order('due_date', { ascending: false }),
  ]);
  if (paymentsRes.error) throw paymentsRes.error;
  return (paymentsRes.data || [])
    .filter((p) => !p.students?.joined_at || isPaymentBillableForJoin(p, p.students.joined_at))
    .map(p => mapPaymentRow(p, settings));
}

function adminSearchPattern (query) {
  const safe = String(query || '').trim().replace(/[%_,]/g, ' ').slice(0, 80);
  if (safe.length < 2) return '';
  return `%${safe}%`;
}

function dedupeRowsById (rows) {
  const seen = new Set();
  return (rows || []).filter((r) => {
    if (!r?.id || seen.has(r.id)) return false;
    seen.add(r.id);
    return true;
  });
}

async function searchAdminGlobal (query, limit = 6) {
  const pat = adminSearchPattern(query);
  if (!pat) return { students: [], payments: [], orders: [] };

  const sb = getSupabase();
  if (!sb) return { students: [], payments: [], orders: [] };

  const studentSel = 'id, code, full_name, category, status';
  const paymentSel = 'id, concept, amount, status, due_date, students ( code, full_name )';
  const orderSel = 'id, order_number, total, status, product_summary, guest_student_name, guest_student_code';

  const [
    stName, stCode, stTutor,
    payConcept, payRef,
    ordNum, ordProd, ordGuest, ordCode,
  ] = await Promise.all([
    sb.from('students').select(studentSel).ilike('full_name', pat).limit(limit),
    sb.from('students').select(studentSel).ilike('code', pat).limit(limit),
    sb.from('students').select(studentSel).ilike('tutor_name', pat).limit(limit),
    sb.from('payments').select(paymentSel).ilike('concept', pat).order('due_date', { ascending: false }).limit(limit),
    sb.from('payments').select(paymentSel).ilike('transfer_reference', pat).order('due_date', { ascending: false }).limit(limit),
    sb.from('orders').select(orderSel).ilike('order_number', pat).order('created_at', { ascending: false }).limit(limit),
    sb.from('orders').select(orderSel).ilike('product_summary', pat).order('created_at', { ascending: false }).limit(limit),
    sb.from('orders').select(orderSel).ilike('guest_student_name', pat).order('created_at', { ascending: false }).limit(limit),
    sb.from('orders').select(orderSel).ilike('guest_student_code', pat).order('created_at', { ascending: false }).limit(limit),
  ]);

  const studentRows = dedupeRowsById([
    ...(stName.data || []), ...(stCode.data || []), ...(stTutor.data || []),
  ]).slice(0, limit);
  const paymentRows = dedupeRowsById([
    ...(payConcept.data || []), ...(payRef.data || []),
  ]).slice(0, limit);
  const orderRows = dedupeRowsById([
    ...(ordNum.data || []), ...(ordProd.data || []), ...(ordGuest.data || []), ...(ordCode.data || []),
  ]).slice(0, limit);

  const students = studentRows.map((s) => ({
    kind: 'student',
    id: s.id,
    title: s.full_name || 'Alumno',
    subtitle: `${s.code || '—'} · ${s.category || ''}`.trim(),
    nav: { view: 'alumnos', entityId: s.id, entityType: 'student', searchQuery: s.code || '' },
  }));

  const payments = paymentRows.map((p) => {
    const st = p.students;
    const name = st?.full_name || 'Alumno';
    return {
      kind: 'payment',
      id: p.id,
      title: p.concept || 'Pago',
      subtitle: `${name} · $${Number(p.amount || 0).toLocaleString('es-MX')} · ${p.status}`,
      nav: { view: 'pagos', entityId: p.id, entityType: 'payment' },
    };
  });

  const orders = orderRows.map((o) => ({
    kind: 'order',
    id: o.id,
    title: o.product_summary || `Orden ${o.order_number || ''}`.trim(),
    subtitle: `${o.guest_student_name || o.guest_student_code || '—'} · $${Number(o.total || 0).toLocaleString('es-MX')}`,
    nav: { view: 'comprobantes', entityId: o.id, entityType: 'order', comprobantesTab: 'tienda' },
  }));

  return { students, payments, orders };
}

const DASHBOARD_MONTH_LABELS = ['Ene', 'Feb', 'Mar', 'Abr', 'May', 'Jun', 'Jul', 'Ago', 'Sep', 'Oct', 'Nov', 'Dic'];

function normalizePaymentStatus (status) {
  return String(status || '').toLowerCase().trim();
}

/** Fecha de cobro confirmado (caja): aprobación admin o registro de pago, no el vencimiento del periodo. */
function paymentCashDate (p) {
  if (normalizePaymentStatus(p.status) !== 'pagado') return null;
  const raw = p.reviewed_at || p.paid_at;
  if (raw) {
    const d = new Date(raw);
    return Number.isNaN(d.getTime()) ? null : d;
  }
  if (!p.due_date) return null;
  const d = new Date(p.due_date);
  return Number.isNaN(d.getTime()) ? null : d;
}

/** Mes de la mensualidad cobrada (periodo de facturación), no el día del depósito. */
function paymentPeriodMonthDate (p) {
  if (normalizePaymentStatus(p.status) !== 'pagado') return null;
  if (p.due_date) {
    const d = new Date(p.due_date);
    if (!Number.isNaN(d.getTime())) return d;
  }
  return paymentCashDate(p);
}

function paymentIncomeDate (p) {
  return paymentPeriodMonthDate(p);
}

function isSameCalendarMonth (d, ref) {
  if (!d || !ref) return false;
  return d.getFullYear() === ref.getFullYear() && d.getMonth() === ref.getMonth();
}

function forEachUniquePayment (payments, fn) {
  const seen = new Set();
  (payments || []).forEach((p) => {
    if (!p?.id || seen.has(p.id)) return;
    seen.add(p.id);
    fn(p);
  });
}

function mapRpcPaymentToDashboardRow (row) {
  return {
    id: row.id,
    student_id: row.student_id,
    concept: row.concept,
    amount: row.amount,
    due_date: row.due_date,
    paid_at: row.paid_at,
    reviewed_at: row.reviewed_at,
    status: row.status,
    receipt_path: row.receipt_path,
    students: (row.student_code || row.student_name)
      ? { code: row.student_code, full_name: row.student_name }
      : null,
  };
}

/** Pagos para dashboard (RPC admin o fallback Supabase). */
async function fetchPaymentsForDashboard (sb) {
  if (!sb) return [];

  const { data: rpcRows, error: rpcErr } = await sb.rpc('get_admin_dashboard_payments');
  if (!rpcErr && rpcRows?.length) {
    return dedupeRowsById((rpcRows || []).map(mapRpcPaymentToDashboardRow));
  }
  if (rpcErr && typeof isRpcMissing === 'function' && !isRpcMissing(rpcErr, 'get_admin_dashboard_payments')) {
    console.warn('[Tecos] dashboard payments rpc', rpcErr);
  }

  let res = await sb.from('payments')
    .select(`
      id, student_id, concept, amount, due_date, paid_at, reviewed_at, status, receipt_path,
      students ( code, full_name )
    `)
    .order('updated_at', { ascending: false });

  if (res.error) {
    console.warn('[Tecos] dashboard payments join', res.error);
    res = await sb.from('payments')
      .select('id, student_id, concept, amount, due_date, paid_at, reviewed_at, status, receipt_path')
      .order('due_date', { ascending: false });
  }
  if (res.error) {
    console.warn('[Tecos] dashboard payments', res.error);
    return [];
  }
  return dedupeRowsById(res.data || []);
}

async function sbDashboardSelect (query, label) {
  const { data, error } = await query;
  if (error) {
    console.warn(`[Tecos] dashboard ${label}`, error);
    return { data: [], error };
  }
  return { data: data || [], error: null };
}

function normalizeOrderStatus (status) {
  return String(status || '').toLowerCase().trim();
}

/** Ingreso de tienda: misma regla que inventario / comprobantes (orden confirmada por admin). */
function orderCountsAsIncome (o) {
  return ['confirmado', 'entregado'].includes(normalizeOrderStatus(o?.status));
}

function orderIncomeDate (o) {
  if (!orderCountsAsIncome(o)) return null;
  const raw = o.reviewed_at || o.created_at;
  if (!raw) return null;
  const d = new Date(raw);
  return Number.isNaN(d.getTime()) ? null : d;
}

function summarizeOrderProducts (o) {
  const items = o?.order_items || [];
  if (!items.length) return 'Pedido tienda';
  return items.map((i) => `${i.product_name}${i.size ? ` (${i.size})` : ''} ×${i.qty}`).join(', ');
}

function mapRecentPaymentForDashboard (p) {
  const st = p.students;
  const paid = p.paid_at ? new Date(p.paid_at) : null;
  const when = paid && !Number.isNaN(paid.getTime())
    ? paid.toLocaleString('es-MX', { day: 'numeric', month: 'short', year: 'numeric', hour: '2-digit', minute: '2-digit' })
    : '—';
  const amt = Number(p.amount) || 0;
  return {
    id: p.id,
    name: st?.full_name?.split(' ').slice(0, 2).join(' ') || 'Alumno',
    fullName: st?.full_name || '—',
    code: st?.code || '—',
    monto: amt,
    a: `$${amt.toLocaleString('es-MX')}`,
    mes: when,
    d: when,
    estado: 'pagado',
    concept: p.concept || 'Mensualidad',
    kind: 'mensualidad',
  };
}

function sumIncomeInMonth (payments, orders, monthStart) {
  let mensualidades = 0;
  let tienda = 0;
  forEachUniquePayment(payments, (p) => {
    const d = paymentIncomeDate(p);
    if (d && isSameCalendarMonth(d, monthStart)) mensualidades += Number(p.amount) || 0;
  });
  (orders || []).forEach((o) => {
    const d = orderIncomeDate(o);
    if (d && isSameCalendarMonth(d, monthStart)) tienda += Number(o.total) || 0;
  });
  return { mensualidades, tienda, total: mensualidades + tienda };
}

function buildDashboardMonthlySeries (payments, orders, year) {
  const months = Array.from({ length: 12 }, (_, i) => ({
    month: i + 1,
    label: DASHBOARD_MONTH_LABELS[i],
    year,
    total: 0,
    pagos: 0,
    tienda: 0,
  }));

  forEachUniquePayment(payments, (p) => {
    const d = paymentIncomeDate(p);
    if (!d || d.getFullYear() !== year) return;
    const idx = d.getMonth();
    const amt = Number(p.amount) || 0;
    months[idx].pagos += amt;
    months[idx].total += amt;
  });

  (orders || []).forEach((o) => {
    const d = orderIncomeDate(o);
    if (!d || d.getFullYear() !== year) return;
    const idx = d.getMonth();
    const amt = Number(o.total) || 0;
    months[idx].tienda += amt;
    months[idx].total += amt;
  });

  return months;
}

function filterDashboardMonths (months, filter) {
  const list = months || [];
  if (!filter) return list;
  if (filter.mode === 'meses') {
    if (!filter.selectedMonths?.length) return [];
    const set = new Set(filter.selectedMonths.map(Number));
    return list.filter(m => set.has(m.month));
  }
  const from = Math.min(filter.rangeFrom || 1, filter.rangeTo || 12);
  const to = Math.max(filter.rangeFrom || 1, filter.rangeTo || 12);
  return list.filter(m => m.month >= from && m.month <= to);
}

function sumDashboardMonths (months) {
  return (months || []).reduce((s, m) => s + (Number(m.total) || 0), 0);
}

/** Resumen de cobranza alineado con calendario por periodo y adeudo del mes en curso. */
function analyzeBillingForDashboard (students, payments, settings) {
  const now = new Date();
  const y = now.getFullYear();
  const cm = now.getMonth();
  const byStudent = {};
  (payments || []).forEach((p) => {
    if (!p.student_id) return;
    if (!byStudent[p.student_id]) byStudent[p.student_id] = [];
    byStudent[p.student_id].push(p);
  });

  let alumnosConAdeudo = 0;
  let totalAdeudoMes = 0;
  let mesesAtrasados = 0;
  let mesesUrgentes = 0;

  (students || []).forEach((s) => {
    if (s.status !== 'activo') return;
    const sp = filterPaymentsForJoin(byStudent[s.id] || [], s.joined_at);
    const adeudo = computeStudentAdeudoAmount(sp, settings, s.joined_at);
    if (adeudo > 0) {
      alumnosConAdeudo += 1;
      totalAdeudoMes += adeudo;
    }

    const slots = mapPortalPaymentsToMonths(sp, y, settings, s.joined_at, { mode: 'billing_schedule' });
    slots.forEach((slot, i) => {
      if (slot.status === 'sin_registro' || slot.hideAmount) return;
      if (slot.status === 'pagado') return;
      if (slot.status === 'revision' || slot.status === 'en_revision') return;
      if (i >= cm) return;
      if (slot.status === 'urgente') mesesUrgentes += 1;
      else if (slot.status === 'vencido' || slot.status === 'pendiente') mesesAtrasados += 1;
    });
  });

  return {
    alumnosConAdeudo,
    totalAdeudoMes: Math.round(totalAdeudoMes * 100) / 100,
    mesesAtrasados,
    mesesUrgentes,
    cobrosAbiertos: mesesAtrasados + mesesUrgentes + alumnosConAdeudo,
  };
}

function financePct (part, whole) {
  const p = Number(part) || 0;
  const w = Number(whole) || 0;
  if (w <= 0) return p > 0 ? 100 : 0;
  return Math.round((p / w) * 1000) / 10;
}

/** Desglose de alumnos activos: pagados, adeudo por tramo de recargo (misma lógica que portal). */
function analyzeFinanceDashboard (students, payments, settings) {
  const now = new Date();
  const y = now.getFullYear();
  const cm = now.getMonth();
  const tierToday = typeof getBillingTierForDate === 'function'
    ? getBillingTierForDate(settings, now)
    : { tier: 'normal', percents: [] };

  const byStudent = {};
  (payments || []).forEach((p) => {
    if (!p.student_id) return;
    if (!byStudent[p.student_id]) byStudent[p.student_id] = [];
    byStudent[p.student_id].push(p);
  });

  let activos = 0;
  let alumnosPagaronMes = 0;
  let ingresoMensualidadesActivosPagaron = 0;
  let alumnosAdeudoRecargo1 = 0;
  let pendienteRecargo1 = 0;
  let alumnosAdeudoRecargo2 = 0;
  let pendienteRecargo2 = 0;
  let alumnosAdeudoAmbosRecargos = 0;
  let pendienteAmbosRecargos = 0;
  let alumnosAdeudoCualquierRecargo = 0;
  let pendienteCualquierRecargo = 0;

  (students || []).forEach((s) => {
    if (s.status !== 'activo') return;
    activos += 1;
    const sp = filterPaymentsForJoin(byStudent[s.id] || [], s.joined_at);
    const paidMonth = isMonthPaidInDb(sp, y, cm, s.joined_at);
    if (paidMonth) {
      alumnosPagaronMes += 1;
      forEachUniquePayment(sp, (p) => {
        if (normalizePaymentStatus(p.status) !== 'pagado') return;
        const d = paymentIncomeDate(p);
        if (d && d.getFullYear() === y && d.getMonth() === cm) {
          ingresoMensualidadesActivosPagaron += Number(p.amount) || 0;
        }
      });
    }

    const slots = mapPortalPaymentsToMonths(sp, y, settings, s.joined_at, { mode: 'billing_schedule' });
    const slot = slots[cm];
    const adeudo = computeStudentAdeudoAmount(sp, settings, s.joined_at);
    if (adeudo <= 0) return;

    const amt = Math.round(adeudo * 100) / 100;
    const tierRef = typeof monthTierRefDate === 'function' ? monthTierRefDate(y, cm, now) : now;
    const tier = slot?.billingTier
      ? { tier: slot.billingTier, label: slot.billingTierLabel || tierToday.label, percents: tierToday.percents }
      : (typeof getBillingTierForDate === 'function' ? getBillingTierForDate(settings, tierRef) : tierToday);

    if (tier.tier === 'late') {
      alumnosAdeudoRecargo1 += 1;
      pendienteRecargo1 += amt;
    }
    if (tier.tier === 'severe') {
      alumnosAdeudoRecargo2 += 1;
      pendienteRecargo2 += amt;
      const both = (typeof getBillingTierForDate === 'function'
        ? getBillingTierForDate(settings, tierRef).percents
        : tier.percents || []).length >= 2;
      if (both) {
        alumnosAdeudoAmbosRecargos += 1;
        pendienteAmbosRecargos += amt;
      }
    }
    if (tier.tier !== 'normal') {
      alumnosAdeudoCualquierRecargo += 1;
      pendienteCualquierRecargo += amt;
    }
  });

  const round = (n) => Math.round(n * 100) / 100;
  return {
    activos,
    alumnosPagaronMes,
    ingresoMensualidadesActivosPagaron: round(ingresoMensualidadesActivosPagaron),
    alumnosAdeudoRecargo1,
    pendienteRecargo1: round(pendienteRecargo1),
    alumnosAdeudoRecargo2,
    pendienteRecargo2: round(pendienteRecargo2),
    alumnosAdeudoAmbosRecargos,
    pendienteAmbosRecargos: round(pendienteAmbosRecargos),
    alumnosAdeudoCualquierRecargo,
    pendienteCualquierRecargo: round(pendienteCualquierRecargo),
    tramoHoy: tierToday.label,
  };
}

function sumAllTimeIncome (payments, orders) {
  let mensualidades = 0;
  let tienda = 0;
  forEachUniquePayment(payments, (p) => {
    if (paymentIncomeDate(p)) mensualidades += Number(p.amount) || 0;
  });
  (orders || []).forEach((o) => {
    if (orderIncomeDate(o)) tienda += Number(o.total) || 0;
  });
  return {
    mensualidades: Math.round(mensualidades * 100) / 100,
    tienda: Math.round(tienda * 100) / 100,
    total: Math.round((mensualidades + tienda) * 100) / 100,
  };
}

/** Egresos históricos: todo lo registrado en Nómina y gastos (sin filtro de mes). */
function sumAllTimeEgresos (expenses, payrollPayments, withdrawals) {
  let gastos = 0;
  let nomina = 0;
  let retiros = 0;
  (expenses || []).forEach((e) => { gastos += Number(e.amount) || 0; });
  (payrollPayments || []).forEach((p) => { nomina += Number(p.amount) || 0; });
  (withdrawals || []).forEach((w) => { retiros += Number(w.amount) || 0; });
  const total = gastos + nomina + retiros;
  return {
    gastos: Math.round(gastos * 100) / 100,
    nomina: Math.round(nomina * 100) / 100,
    retiros: Math.round(retiros * 100) / 100,
    total: Math.round(total * 100) / 100,
  };
}

function sumEgresosInRange (expenses, payrollPayments, withdrawals, rangeStart, rangeEnd) {
  const t0 = rangeStart ? rangeStart.getTime() : 0;
  const t1 = rangeEnd ? rangeEnd.getTime() : Infinity;
  const inRange = (iso) => {
    const t = new Date(iso).getTime();
    return !Number.isNaN(t) && t >= t0 && t <= t1;
  };
  let gastos = 0;
  let nomina = 0;
  let retiros = 0;
  let gastosCount = 0;
  let nominaCount = 0;
  let retirosCount = 0;
  (expenses || []).forEach((e) => {
    if (!inRange(e.expense_at)) return;
    gastos += Number(e.amount) || 0;
    gastosCount += 1;
  });
  (payrollPayments || []).forEach((p) => {
    if (!inRange(p.paid_at)) return;
    nomina += Number(p.amount) || 0;
    nominaCount += 1;
  });
  (withdrawals || []).forEach((w) => {
    if (!inRange(w.withdrawn_at)) return;
    retiros += Number(w.amount) || 0;
    retirosCount += 1;
  });
  const total = gastos + nomina + retiros;
  return {
    gastos: Math.round(gastos * 100) / 100,
    nomina: Math.round(nomina * 100) / 100,
    retiros: Math.round(retiros * 100) / 100,
    total: Math.round(total * 100) / 100,
    gastosCount,
    nominaCount,
    retirosCount,
    movimientosCount: gastosCount + nominaCount + retirosCount,
  };
}

function buildDashboardMonthlySeriesFinance (payments, orders, expenses, payrollPayments, withdrawals, year) {
  const months = buildDashboardMonthlySeries(payments, orders, year);
  return months.map((m, i) => {
    const start = new Date(year, i, 1);
    const end = new Date(year, i + 1, 0, 23, 59, 59, 999);
    const eg = sumEgresosInRange(expenses, payrollPayments, withdrawals, start, end);
    return {
      ...m,
      egresos: eg.total,
      egresosGastos: eg.gastos,
      egresosNomina: eg.nomina,
      egresosRetiros: eg.retiros,
      neto: Math.round((m.total - eg.total) * 100) / 100,
    };
  });
}

async function fetchFinanceTablesForDashboard (sb) {
  const empty = { expenses: [], payrollPayments: [], withdrawals: [], payrollProfiles: [] };
  if (!sb) return empty;
  const safe = async (fn) => {
    try {
      return await fn();
    } catch (e) {
      if (/payroll_|expense|finance_withdraw|schema cache|PGRST202/i.test(e.message || '')) return [];
      throw e;
    }
  };
  const [expenses, payrollPayments, withdrawals, payrollProfiles] = await Promise.all([
    safe(async () => {
      const { data, error } = await sb.from('expenses').select('id, amount, expense_at').order('expense_at', { ascending: false }).limit(5000);
      if (error) throw error;
      return data || [];
    }),
    safe(async () => {
      const { data, error } = await sb.from('payroll_payments').select('id, amount, paid_at').order('paid_at', { ascending: false }).limit(5000);
      if (error) throw error;
      return data || [];
    }),
    safe(async () => {
      const { data, error } = await sb.from('finance_withdrawals').select('id, amount, withdrawn_at').order('withdrawn_at', { ascending: false }).limit(2000);
      if (error) throw error;
      return data || [];
    }),
    safe(async () => {
      const { data, error } = await sb.from('payroll_profiles').select('id, display_name, next_pay_date, active');
      if (error) throw error;
      return data || [];
    }),
  ]);
  return { expenses, payrollPayments, withdrawals, payrollProfiles };
}

async function fetchAdminDashboardStats (year = new Date().getFullYear()) {
  const sb = getSupabase();
  if (!sb) {
    return {
      year,
      totalStudents: 0,
      activos: 0,
      pendientes: 0,
      vencidos: 0,
      alumnosConAdeudo: 0,
      totalAdeudoMes: 0,
      mesesAtrasados: 0,
      mesesUrgentes: 0,
      pagadoMes: 0,
      ordenesPend: 0,
      eventosProximos: 0,
      recentPayments: [],
      monthlySeries: buildDashboardMonthlySeries([], [], year),
      hero: {
        ingresosHoy: 0,
        ingresosMensualidadesHoy: 0,
        ingresosTiendaHoy: 0,
        nuevosMes: 0,
        tasaActivos: 0,
        comprobantesPend: 0,
      },
      alerts: [],
      recentOrders: [],
      upcomingEvents: [],
      ingresosMensualidadesMes: 0,
      ingresosTiendaMes: 0,
      recaudadoAnio: 0,
      recaudadoMensualidadesAnio: 0,
      recaudadoTiendaAnio: 0,
      ingresoAcumuladoHistorico: 0,
      finance: null,
    };
  }

  const now = new Date();
  const todayKey = now.toISOString().slice(0, 10);
  const monthStart = new Date(now.getFullYear(), now.getMonth(), 1);
  const eventsHorizon = new Date(now.getFullYear(), now.getMonth() + 4, 1);

  const settings = await fetchAcademySettings();

  const [studentsRes, payments, ordersRes, productsRes, ordersRecentRes, eventsListRes] = await Promise.all([
    sbDashboardSelect(
      sb.from('students').select('id, status, full_name, birth_date, joined_at'),
      'students'
    ),
    fetchPaymentsForDashboard(sb),
    sbDashboardSelect(
      sb.from('orders').select('id, status, total, created_at, reviewed_at, guest_student_name, order_number, guest_student_code'),
      'orders'
    ),
    sbDashboardSelect(
      sb.from('products').select('id, name, stock, stock_by_size, active'),
      'products'
    ),
    sbDashboardSelect(
      sb.from('orders')
        .select(`
          id, order_number, total, status, guest_student_name, guest_student_code, created_at,
          order_items ( product_name, size, qty )
        `)
        .order('created_at', { ascending: false })
        .limit(8),
      'orders-recent'
    ),
    sbDashboardSelect(
      sb.from('events')
        .select('id, title, starts_at, location, category')
        .gte('starts_at', now.toISOString())
        .lte('starts_at', eventsHorizon.toISOString())
        .order('starts_at', { ascending: true })
        .limit(12),
      'events'
    ),
  ]);

  let orders = ordersRes.data || [];
  if (ordersRes.error) {
    const legacy = await sbDashboardSelect(
      sb.from('orders').select('id, status, total, created_at, reviewed_at, guest_student_name, order_number, guest_student_code'),
      'orders-legacy'
    );
    orders = legacy.data || [];
  }

  const students = studentsRes.data || [];
  const joinedByStudent = buildStudentJoinedAtMap(students);
  const billablePayments = filterDashboardPayments(payments, joinedByStudent);
  const products = (productsRes.data || []).filter(p => p.active !== false);

  const activos = students.filter(s => s.status === 'activo').length;
  const billing = analyzeBillingForDashboard(students, billablePayments, settings);
  const financeStudents = analyzeFinanceDashboard(students, billablePayments, settings);
  const financeTables = await fetchFinanceTablesForDashboard(sb);
  const monthEnd = new Date(now.getFullYear(), now.getMonth() + 1, 0, 23, 59, 59, 999);
  const egresosMes = sumEgresosInRange(
    financeTables.expenses,
    financeTables.payrollPayments,
    financeTables.withdrawals,
    monthStart,
    monthEnd,
  );
  const ingresoHistorico = sumAllTimeIncome(billablePayments, orders);
  const egresoHistorico = sumAllTimeEgresos(
    financeTables.expenses,
    financeTables.payrollPayments,
    financeTables.withdrawals,
  );
  const pendientes = billing.alumnosConAdeudo;
  const vencidos = billing.mesesAtrasados + billing.mesesUrgentes;
  const ordenesPend = orders.filter(o => ['pendiente', 'en_revision'].includes(normalizeOrderStatus(o.status))).length;
  const ordenesConfirmadas = orders.filter(orderCountsAsIncome).length;

  const incomeMes = sumIncomeInMonth(billablePayments, orders, monthStart);
  const pagadoMes = incomeMes.total;
  const ingresosMensualidadesMes = incomeMes.mensualidades;
  const ingresosTiendaMes = incomeMes.tienda;
  const monthlySeries = buildDashboardMonthlySeriesFinance(
    billablePayments,
    orders,
    financeTables.expenses,
    financeTables.payrollPayments,
    financeTables.withdrawals,
    year,
  );
  const recaudadoAnio = sumDashboardMonths(monthlySeries);
  const recaudadoMensualidadesAnio = monthlySeries.reduce((n, m) => n + (Number(m.pagos) || 0), 0);
  const recaudadoTiendaAnio = monthlySeries.reduce((n, m) => n + (Number(m.tienda) || 0), 0);

  let ingresosMensualidadesHoy = 0;
  let ingresosTiendaHoy = 0;
  forEachUniquePayment(billablePayments, (p) => {
    const d = paymentCashDate(p);
    if (d && d.toISOString().slice(0, 10) === todayKey) ingresosMensualidadesHoy += Number(p.amount) || 0;
  });
  orders.forEach((o) => {
    const d = orderIncomeDate(o);
    if (d && d.toISOString().slice(0, 10) === todayKey) ingresosTiendaHoy += Number(o.total) || 0;
  });
  const ingresosHoy = ingresosMensualidadesHoy + ingresosTiendaHoy;

  const nuevosMes = students.filter((s) => {
    if (!s.joined_at) return false;
    const j = new Date(s.joined_at);
    return !Number.isNaN(j.getTime()) && j >= monthStart;
  }).length;

  const tasaActivos = students.length
    ? Math.round((activos / students.length) * 100)
    : 0;

  const alerts = [];
  if (billing.alumnosConAdeudo > 0) {
    alerts.push({
      id: 'adeudo-mes',
      color: 'danger',
      icon: 'money',
      text: `${billing.alumnosConAdeudo} alumno(s) con adeudo del mes ($${billing.totalAdeudoMes.toLocaleString('es-MX')})`,
      nav: 'alumnos',
      alumnosFilter: 'adeudo',
    });
  }
  if (billing.mesesAtrasados > 0) {
    alerts.push({
      id: 'atrasados',
      color: 'warning',
      icon: 'clock',
      text: `${billing.mesesAtrasados} mensualidad(es) atrasada(s) sin pagar`,
      nav: 'pagos',
    });
  }
  if (billing.mesesUrgentes > 0) {
    alerts.push({
      id: 'urgentes',
      color: 'urgent',
      icon: 'flame',
      text: `${billing.mesesUrgentes} mensualidad(es) en estado urgente`,
      nav: 'pagos',
    });
  }
  const lowStock = products.filter((p) => {
    const stockMap = parseStockBySize(p);
    const total = sumStockBySize(stockMap);
    return total > 0 && total <= 5;
  }).length;
  if (lowStock > 0) {
    alerts.push({ id: 'stock', color: 'warning', icon: 'box', text: `${lowStock} producto(s) con stock bajo`, nav: 'inventario' });
  }
  if (ordenesPend > 0) {
    alerts.push({ id: 'ordenes', color: 'info', icon: 'cart', text: `${ordenesPend} orden(es) de tienda pendientes`, nav: 'comprobantes', comprobantesTab: 'tienda' });
  }
  const pagadosCount = billablePayments.filter((p) => normalizePaymentStatus(p.status) === 'pagado').length;
  const enRevision = billablePayments.filter((p) => normalizePaymentStatus(p.status) === 'en_revision').length;
  if (enRevision > 0) {
    alerts.push({
      id: 'revision',
      color: 'urgent',
      icon: 'doc',
      text: `${enRevision} comprobante(s) en revisión — aprueba en Verificar comprobantes para sumar ingresos`,
      nav: 'comprobantes',
    });
  }
  const nominaManana = typeof payrollProfilesDueTomorrow === 'function'
    ? payrollProfilesDueTomorrow(financeTables.payrollProfiles)
    : [];
  if (nominaManana.length > 0) {
    alerts.push({
      id: 'nomina-manana',
      color: 'warning',
      icon: 'money',
      text: `${nominaManana.length} pago(s) de nómina programados para mañana`,
      nav: 'nomina-gastos',
      highlightNomina: true,
    });
  }
  if (pagadosCount === 0 && payments.length > 0) {
    alerts.push({
      id: 'sin-confirmados',
      color: 'warning',
      icon: 'money',
      text: `${payments.length} pago(s) en sistema; ninguno confirmado como pagado aún`,
      nav: 'comprobantes',
    });
  }

  const upcomingEvents = (eventsListRes.data || []).map((ev) => ({
    id: ev.id,
    title: ev.title || 'Evento',
    when: ev.starts_at
      ? new Date(ev.starts_at).toLocaleString('es-MX', { day: 'numeric', month: 'short', hour: '2-digit', minute: '2-digit' })
      : '—',
    location: ev.location || '',
  }));

  const recentOrders = (ordersRecentRes.data || [])
    .filter((o) => orderCountsAsIncome(o))
    .map((o) => {
    const created = o.created_at ? new Date(o.created_at) : null;
    const when = created && !Number.isNaN(created.getTime())
      ? created.toLocaleString('es-MX', { day: 'numeric', month: 'short', hour: '2-digit', minute: '2-digit' })
      : '—';
    return {
      id: o.id,
      name: summarizeOrderProducts(o),
      amount: Number(o.total) || 0,
      status: o.status,
      student: o.guest_student_name || o.guest_student_code || '—',
      number: o.order_number,
      when,
    };
  });

  return {
    year,
    totalStudents: students.length,
    activos,
    pendientes,
    vencidos,
    alumnosConAdeudo: billing.alumnosConAdeudo,
    totalAdeudoMes: billing.totalAdeudoMes,
    mesesAtrasados: billing.mesesAtrasados,
    mesesUrgentes: billing.mesesUrgentes,
    pagadoMes,
    ordenesPend,
    eventosProximos: upcomingEvents.length,
    recentPayments: billablePayments
      .filter((p) => normalizePaymentStatus(p.status) === 'pagado')
      .sort((a, b) => {
        const da = new Date(a.reviewed_at || a.paid_at || a.due_date || 0).getTime();
        const db = new Date(b.reviewed_at || b.paid_at || b.due_date || 0).getTime();
        return db - da;
      })
      .slice(0, 8)
      .map((p) => mapRecentPaymentForDashboard(p)),
    monthlySeries,
    ingresosMensualidadesMes,
    ingresosTiendaMes,
    recaudadoAnio,
    recaudadoMensualidadesAnio,
    recaudadoTiendaAnio,
    _meta: {
      paymentsTotal: payments.length,
      paymentsPagado: pagadosCount,
      paymentsEnRevision: enRevision,
      studentsLoaded: students.length,
      ordersLoaded: orders.length,
      ordersConfirmadas: ordenesConfirmadas,
      ordersPendientes: ordenesPend,
      financeEgresosRegistros: {
        gastos: (financeTables.expenses || []).length,
        nomina: (financeTables.payrollPayments || []).length,
        retiros: (financeTables.withdrawals || []).length,
      },
      loadWarnings: [
        studentsRes.error ? 'alumnos' : null,
        ordersRes.error && !orders.length ? 'órdenes' : null,
      ].filter(Boolean),
    },
    hero: {
      ingresosHoy,
      ingresosMensualidadesHoy,
      ingresosTiendaHoy,
      nuevosMes,
      tasaActivos,
      comprobantesPend: ordenesPend + enRevision,
    },
    alerts,
    recentOrders,
    upcomingEvents,
    ingresoAcumuladoHistorico: ingresoHistorico.total,
    ingresoAcumuladoMensualidades: ingresoHistorico.mensualidades,
    ingresoAcumuladoTienda: ingresoHistorico.tienda,
    egresoAcumuladoHistorico: egresoHistorico.total,
    egresoAcumuladoNomina: egresoHistorico.nomina,
    egresoAcumuladoGastos: egresoHistorico.gastos,
    egresoAcumuladoRetiros: egresoHistorico.retiros,
    egresosMes: egresosMes.total,
    egresosMesGastos: egresosMes.gastos,
    egresosMesNomina: egresosMes.nomina,
    egresosMesRetiros: egresosMes.retiros,
    balanceMes: Math.round((pagadoMes - egresosMes.total) * 100) / 100,
    billingSettings: settings,
    billingHint: typeof portalBillingScheduleHint === 'function'
      ? portalBillingScheduleHint(settings)
      : null,
    loadedAt: new Date().toISOString(),
    finance: {
      ...financeStudents,
      billingHint: typeof portalBillingScheduleHint === 'function'
        ? portalBillingScheduleHint(settings)
        : null,
      alumnosConAdeudo: billing.alumnosConAdeudo,
      totalAdeudoMes: billing.totalAdeudoMes,
      mesesAtrasados: billing.mesesAtrasados,
      mesesUrgentes: billing.mesesUrgentes,
      cobrosAbiertos: billing.cobrosAbiertos,
      ingresoMensualidadesMes: ingresosMensualidadesMes,
      ingresoTiendaMes: ingresosTiendaMes,
      ingresoTotalPagadoMes: pagadoMes,
      ingresoAcumuladoHistorico: ingresoHistorico.total,
      ingresoAcumuladoMensualidades: ingresoHistorico.mensualidades,
      ingresoAcumuladoTienda: ingresoHistorico.tienda,
      egresoAcumuladoHistorico: egresoHistorico.total,
      egresoAcumuladoNomina: egresoHistorico.nomina,
      egresoAcumuladoGastos: egresoHistorico.gastos,
      egresoAcumuladoRetiros: egresoHistorico.retiros,
      ingresoMensualidadesActivosPagaron: financeStudents.ingresoMensualidadesActivosPagaron,
      egresosMes: egresosMes.total,
      egresosMesGastos: egresosMes.gastos,
      egresosMesNomina: egresosMes.nomina,
      egresosMesRetiros: egresosMes.retiros,
      egresosMesGastosCount: egresosMes.gastosCount,
      egresosMesNominaCount: egresosMes.nominaCount,
      egresosMesRetirosCount: egresosMes.retirosCount,
      egresosMesMovimientosCount: egresosMes.movimientosCount,
      balanceMes: Math.round((pagadoMes - egresosMes.total) * 100) / 100,
      pct: {
        alumnosPagaronMes: financePct(financeStudents.alumnosPagaronMes, financeStudents.activos),
        ingresoMensualidadesActivos: financePct(
          financeStudents.ingresoMensualidadesActivosPagaron,
          ingresoHistorico.mensualidades || pagadoMes,
        ),
        alumnosRecargo1: financePct(financeStudents.alumnosAdeudoRecargo1, financeStudents.activos),
        pendienteRecargo1: financePct(financeStudents.pendienteRecargo1, financeStudents.pendienteCualquierRecargo || 1),
        alumnosRecargo2: financePct(financeStudents.alumnosAdeudoRecargo2, financeStudents.activos),
        pendienteRecargo2: financePct(financeStudents.pendienteRecargo2, financeStudents.pendienteCualquierRecargo || 1),
        alumnosAmbosRecargos: financePct(financeStudents.alumnosAdeudoAmbosRecargos, financeStudents.activos),
        pendienteAmbos: financePct(financeStudents.pendienteAmbosRecargos, financeStudents.pendienteCualquierRecargo || 1),
        ingresoTiendaMes: financePct(ingresosTiendaMes, pagadoMes || ingresoHistorico.total),
        ingresoMensualidadesMes: financePct(ingresosMensualidadesMes, pagadoMes || ingresoHistorico.total),
        ingresoTotalMes: financePct(pagadoMes, ingresoHistorico.total || pagadoMes),
      },
    },
    nominaPagoManana: nominaManana.map((p) => p.display_name),
  };
}

const PRODUCT_SIZE_OPTIONS = ['6', '8', '10', '12', '14', '16', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL'];

function parseProductSizesArray (row) {
  if (Array.isArray(row?.sizes)) return row.sizes.map(String);
  if (typeof row?.sizes === 'string') {
    try {
      const p = JSON.parse(row.sizes);
      if (Array.isArray(p)) return p.map(String);
    } catch {
      return row.sizes.split(',').map(s => s.trim()).filter(Boolean);
    }
  }
  return ['Único'];
}

function parseStockBySize (row) {
  if (row?.stock_by_size && typeof row.stock_by_size === 'object' && !Array.isArray(row.stock_by_size)) {
    return { ...row.stock_by_size };
  }
  const sizes = parseProductSizesArray(row);
  const fallback = {};
  sizes.forEach(s => { fallback[s] = Number(row?.stock) || 0; });
  return fallback;
}

function sumStockBySize (stockBySize) {
  return Object.values(stockBySize || {}).reduce((a, b) => a + (Number(b) || 0), 0);
}

function normalizeTecStudentCode (raw) {
  const v = String(raw || '').trim().toUpperCase().replace(/\s/g, '');
  const m = v.match(/^TEC-?(\d{1,6})$/i);
  if (!m) return '';
  const n = parseInt(m[1], 10);
  if (n < 1 || n > 999) return '';
  return `TEC${String(n).padStart(3, '0')}`;
}

/** Solo dígitos (máx. 3) mientras el usuario escribe — sin rellenar con ceros. */
function formatTecCodeDigitsInput (raw) {
  return String(raw || '').replace(/\D/g, '').slice(0, 3);
}

/** Normaliza a 3 dígitos con ceros a la izquierda (solo al guardar / enviar, no al escribir). */
function formatTecCodeDigits (raw) {
  const v = formatTecCodeDigitsInput(raw);
  if (!v) return '';
  return v.padStart(3, '0');
}

function buildTecStudentCode (digits) {
  const d = formatTecCodeDigitsInput(digits);
  if (!d) return '';
  return `TEC${d.padStart(3, '0')}`;
}

/** Siguiente TEC### sin alumno en `students` (RPC o escaneo local). */
async function fetchNextStudentCodeDigits () {
  const sb = getSupabase();
  if (!sb) return '001';

  const digitsFromCode = (code) => {
    const m = String(code || '').trim().toUpperCase().match(/^TEC(\d{3})$/i);
    return m ? m[1] : '';
  };

  const scanStudentsTable = async () => {
    const { data: rows, error: selErr } = await sb.from('students').select('code');
    if (selErr) throw selErr;
    const used = new Set();
    for (const r of rows || []) {
      const c = normalizeTecStudentCode(r.code);
      if (c) used.add(c);
    }
    for (let i = 1; i <= 999; i += 1) {
      const c = `TEC${String(i).padStart(3, '0')}`;
      if (!used.has(c)) return String(i).padStart(3, '0');
    }
    return '';
  };

  const { data, error } = await sb.rpc('get_next_available_student_code');
  if (!error && data) {
    const d = digitsFromCode(data);
    if (d) return d;
  } else if (error) {
    console.warn('[Tecos] get_next_available_student_code', error);
  }

  try {
    const scanned = await scanStudentsTable();
    if (scanned) return scanned;
  } catch (scanErr) {
    console.warn('[Tecos] escaneo local de IDs alumno', scanErr);
  }

  return '001';
}

const DEFAULT_PHONE_DIAL = '+52';

const PHONE_DIAL_OPTIONS = [
  { code: '+52', label: 'México (+52)', country: 'MX' },
  { code: '+1', label: 'EE.UU. / Canadá (+1)', country: 'US' },
  { code: '+57', label: 'Colombia (+57)', country: 'CO' },
  { code: '+54', label: 'Argentina (+54)', country: 'AR' },
  { code: '+56', label: 'Chile (+56)', country: 'CL' },
  { code: '+51', label: 'Perú (+51)', country: 'PE' },
  { code: '+593', label: 'Ecuador (+593)', country: 'EC' },
  { code: '+502', label: 'Guatemala (+502)', country: 'GT' },
  { code: '+503', label: 'El Salvador (+503)', country: 'SV' },
  { code: '+504', label: 'Honduras (+504)', country: 'HN' },
  { code: '+505', label: 'Nicaragua (+505)', country: 'NI' },
  { code: '+506', label: 'Costa Rica (+506)', country: 'CR' },
  { code: '+507', label: 'Panamá (+507)', country: 'PA' },
  { code: '+58', label: 'Venezuela (+58)', country: 'VE' },
  { code: '+591', label: 'Bolivia (+591)', country: 'BO' },
  { code: '+595', label: 'Paraguay (+595)', country: 'PY' },
  { code: '+598', label: 'Uruguay (+598)', country: 'UY' },
  { code: '+53', label: 'Cuba (+53)', country: 'CU' },
  { code: '+509', label: 'Haití (+509)', country: 'HT' },
  { code: '+1876', label: 'Jamaica (+1876)', country: 'JM' },
  { code: '+1809', label: 'Rep. Dominicana (+1809)', country: 'DO' },
  { code: '+1787', label: 'Puerto Rico (+1787)', country: 'PR' },
  { code: '+34', label: 'España (+34)', country: 'ES' },
  { code: '+55', label: 'Brasil (+55)', country: 'BR' },
  { code: '+39', label: 'Italia (+39)', country: 'IT' },
  { code: '+351', label: 'Portugal (+351)', country: 'PT' },
  { code: '+49', label: 'Alemania (+49)', country: 'DE' },
  { code: '+33', label: 'Francia (+33)', country: 'FR' },
  { code: '+44', label: 'Reino Unido (+44)', country: 'GB' },
  { code: '+31', label: 'Países Bajos (+31)', country: 'NL' },
  { code: '+32', label: 'Bélgica (+32)', country: 'BE' },
  { code: '+41', label: 'Suiza (+41)', country: 'CH' },
  { code: '+43', label: 'Austria (+43)', country: 'AT' },
  { code: '+46', label: 'Suecia (+46)', country: 'SE' },
  { code: '+47', label: 'Noruega (+47)', country: 'NO' },
  { code: '+45', label: 'Dinamarca (+45)', country: 'DK' },
  { code: '+48', label: 'Polonia (+48)', country: 'PL' },
  { code: '+420', label: 'Rep. Checa (+420)', country: 'CZ' },
  { code: '+36', label: 'Hungría (+36)', country: 'HU' },
  { code: '+40', label: 'Rumania (+40)', country: 'RO' },
  { code: '+90', label: 'Turquía (+90)', country: 'TR' },
  { code: '+972', label: 'Israel (+972)', country: 'IL' },
  { code: '+971', label: 'Emiratos Árabes (+971)', country: 'AE' },
  { code: '+966', label: 'Arabia Saudita (+966)', country: 'SA' },
  { code: '+20', label: 'Egipto (+20)', country: 'EG' },
  { code: '+212', label: 'Marruecos (+212)', country: 'MA' },
  { code: '+27', label: 'Sudáfrica (+27)', country: 'ZA' },
  { code: '+234', label: 'Nigeria (+234)', country: 'NG' },
  { code: '+81', label: 'Japón (+81)', country: 'JP' },
  { code: '+82', label: 'Corea del Sur (+82)', country: 'KR' },
  { code: '+86', label: 'China (+86)', country: 'CN' },
  { code: '+91', label: 'India (+91)', country: 'IN' },
  { code: '+63', label: 'Filipinas (+63)', country: 'PH' },
  { code: '+62', label: 'Indonesia (+62)', country: 'ID' },
  { code: '+60', label: 'Malasia (+60)', country: 'MY' },
  { code: '+65', label: 'Singapur (+65)', country: 'SG' },
  { code: '+66', label: 'Tailandia (+66)', country: 'TH' },
  { code: '+84', label: 'Vietnam (+84)', country: 'VN' },
  { code: '+61', label: 'Australia (+61)', country: 'AU' },
  { code: '+64', label: 'Nueva Zelanda (+64)', country: 'NZ' },
];

function parsePhoneWithDial (full) {
  const s = String(full || '').trim();
  if (!s || s === '—') return { dial: DEFAULT_PHONE_DIAL, national: '' };
  const sorted = [...PHONE_DIAL_OPTIONS].sort((a, b) => b.code.length - a.code.length);
  for (const { code } of sorted) {
    if (s.startsWith(code)) {
      let national = s.slice(code.length).replace(/[\s\-().]/g, '');
      if (typeof stripDupNationalForDial === 'function') {
        national = stripDupNationalForDial(code, national);
      } else if (code === '+52' && national.startsWith('52') && national.length >= 12) {
        national = national.slice(2);
        if (national.length === 11 && national[0] === '1') national = national.slice(1);
      }
      return { dial: code, national };
    }
  }
  const plus = s.match(/^(\+\d{1,4})(.*)$/);
  if (plus) {
    let national = plus[2].replace(/\D/g, '');
    if (typeof stripDupNationalForDial === 'function') {
      national = stripDupNationalForDial(plus[1], national);
    }
    return { dial: plus[1], national };
  }
  let national = s.replace(/\D/g, '');
  if (typeof stripDupNationalForDial === 'function') {
    national = stripDupNationalForDial(DEFAULT_PHONE_DIAL, national);
  }
  return { dial: DEFAULT_PHONE_DIAL, national };
}

function combinePhoneWithDial (dial, national) {
  const n = String(national || '').replace(/\D/g, '');
  if (!n) return '';
  const d = dial || DEFAULT_PHONE_DIAL;
  const norm = typeof normalizeWaPhoneDigits === 'function' ? normalizeWaPhoneDigits : (p) => String(p || '').replace(/\D/g, '');
  const digits = norm(`${d}${n}`);
  if (!digits) return `${d} ${n}`;
  return typeof formatWaPhoneDisplay === 'function' ? formatWaPhoneDisplay(digits) : `+${digits}`;
}

function computeSeniorityLabel (joinedAt) {
  if (!joinedAt) return '—';
  const start = new Date(joinedAt);
  if (Number.isNaN(start.getTime())) return '—';
  const now = new Date();
  let months = (now.getFullYear() - start.getFullYear()) * 12 + (now.getMonth() - start.getMonth());
  if (now.getDate() < start.getDate()) months -= 1;
  if (months < 0) months = 0;
  if (months < 12) return `${months} mes${months === 1 ? '' : 'es'}`;
  const years = Math.floor(months / 12);
  const rem = months % 12;
  if (rem === 0) return `${years} año${years === 1 ? '' : 's'}`;
  return `${years} año${years === 1 ? '' : 's'} ${rem} mes${rem === 1 ? '' : 'es'}`;
}

const STUDENT_DOCUMENT_TYPES = [
  { id: 'inscripcion', label: 'Inscripción' },
  { id: 'papeleria', label: 'Papelería' },
  { id: 'seguro', label: 'Seguro' },
  { id: 'acta_nacimiento', label: 'Acta de nacimiento' },
  { id: 'curp', label: 'CURP' },
  { id: 'identificacion', label: 'Identificación oficial' },
  { id: 'comprobante_domicilio', label: 'Comprobante de domicilio' },
  { id: 'carta_responsiva', label: 'Carta responsiva' },
  { id: 'certificado_medico', label: 'Certificado médico' },
  { id: 'otro', label: 'Otro' },
];

function defaultDocumentRequirements () {
  const o = {};
  STUDENT_DOCUMENT_TYPES.forEach((t) => { o[t.id] = true; });
  return o;
}

/** Combina requisitos guardados con el catálogo completo (por defecto todo aplica). */
function mergeDocumentRequirements (stored) {
  const base = defaultDocumentRequirements();
  if (!stored || typeof stored !== 'object') return base;
  STUDENT_DOCUMENT_TYPES.forEach((t) => {
    if (stored[t.id] === false) base[t.id] = false;
    else if (stored[t.id] === true) base[t.id] = true;
  });
  return base;
}

function getApplicableDocumentTypes (requirements) {
  const req = requirements ? mergeDocumentRequirements(requirements) : defaultDocumentRequirements();
  return STUDENT_DOCUMENT_TYPES.filter((t) => req[t.id] !== false);
}

function getProductSizeStock (product, size) {
  return Number(product?.stockBySize?.[size]) || 0;
}

async function lookupStudentCodePublic (code) {
  const sb = getSupabase();
  if (!sb) return null;
  const normalized = normalizeTecStudentCode(code);
  if (!normalized) return null;
  const { data, error } = await sb.rpc('get_student_by_code', { p_student_code: normalized });
  if (error) throw error;
  return data;
}

function productImageUrl (path) {
  if (!path) return null;
  return typeof getStoragePublicUrl === 'function' ? getStoragePublicUrl('products', path) : null;
}

function parseProductGalleryPaths (row) {
  const g = row?.gallery_paths;
  if (Array.isArray(g)) return g.filter(p => typeof p === 'string' && p.trim());
  return [];
}

function mapProductTienda (row) {
  const stockBySize = parseStockBySize(row);
  const configured = parseProductSizesArray(row);
  const tallas = PRODUCT_SIZE_OPTIONS.filter(s => (stockBySize[s] ?? 0) > 0);
  const finalTallas = tallas.length ? tallas : configured.filter(s => (stockBySize[s] ?? 0) > 0);
  const stock = sumStockBySize(stockBySize) || Number(row.stock) || 0;
  const icons = { Uniformes: 'medal', Playeras: 'star', Balones: 'target', Accesorios: 'flame' };
  const mainUrl = productImageUrl(row.image_path);
  const extraUrls = parseProductGalleryPaths(row).map(p => productImageUrl(p)).filter(Boolean);
  const imageUrls = [...(mainUrl ? [mainUrl] : []), ...extraUrls].filter((u, i, a) => a.indexOf(u) === i);
  return {
    id: row.sku || row.id,
    dbId: row.id,
    name: row.name,
    cat: row.category || 'General',
    price: Number(row.price) || 0,
    tallas: finalTallas.length ? finalTallas : ['Único'],
    stockBySize,
    stock,
    inStock: stock > 0,
    imageUrl: imageUrls[0] || null,
    imageUrls,
    coverFocusX: Number(row.cover_focus_x) || 50,
    coverFocusY: Number(row.cover_focus_y) || 50,
    icon: icons[row.category] || 'box',
  };
}

function notifyProductsChanged () {
  window.dispatchEvent(new CustomEvent('tecos:products-changed'));
}

async function fetchProductsForTienda ({ landingOnly = false } = {}) {
  const safe = typeof fetchActiveProductsSafe === 'function'
    ? fetchActiveProductsSafe
    : async () => ({ rows: await fetchActiveProducts(), error: null });
  const { rows, error } = await safe({ landingOnly });
  if (error) {
    return { products: [], error };
  }
  return { products: (rows || []).map(mapProductTienda), error: null };
}

const ANNOUNCEMENTS_PUBLIC_SELECT = 'id, title, body, priority, published_at, starts_at, ends_at, multi_day, location, maps_url, image_path, image_focus_x, image_focus_y';
const ANNOUNCEMENTS_PUBLIC_SELECT_LEGACY = 'id, title, body, priority, published_at';

async function fetchPublishedAnnouncements () {
  const sb = getSupabase();
  if (!sb) return [];
  let { data, error } = await sb.from('announcements')
    .select(ANNOUNCEMENTS_PUBLIC_SELECT)
    .not('published_at', 'is', null)
    .order('published_at', { ascending: false });
  if (error && /starts_at|ends_at|multi_day|maps_url|image_path|image_focus|location|column/i.test(error.message || '')) {
    ({ data, error } = await sb.from('announcements')
      .select(ANNOUNCEMENTS_PUBLIC_SELECT_LEGACY)
      .not('published_at', 'is', null)
      .order('published_at', { ascending: false }));
  }
  if (error) throw error;
  return (data || []).filter(isAnnouncementPublished);
}

const EVENTS_PUBLIC_SELECT = 'id, title, description, starts_at, ends_at, location, category, maps_url, multi_day, image_path, image_focus_x, image_focus_y';
const EVENTS_PUBLIC_SELECT_LEGACY = 'id, title, description, starts_at, ends_at, location, category';

async function fetchPublicEvents () {
  const sb = getSupabase();
  let { data, error } = await sb.from('events')
    .select(EVENTS_PUBLIC_SELECT)
    .order('starts_at', { ascending: true });
  if (error && /image_path|maps_url|multi_day|image_focus|column/i.test(error.message || '')) {
    ({ data, error } = await sb.from('events')
      .select(EVENTS_PUBLIC_SELECT_LEGACY)
      .order('starts_at', { ascending: true }));
  }
  if (error) throw error;
  return data || [];
}

async function fetchEventsForGalleryLink () {
  const sb = getSupabase();
  if (!sb) return [];
  const { data, error } = await sb.from('events')
    .select('id, title, starts_at')
    .order('starts_at', { ascending: false });
  if (error) throw error;
  return data || [];
}

const COACHES_PUBLIC_SELECT = 'id, name, bio, specialty, photo_path, photo_focus_x, photo_focus_y, sort_order, experience_years, card_summary, trajectory, certifications, assigned_groups, icon';
const COACHES_PUBLIC_SELECT_LEGACY = 'id, name, bio, specialty, photo_path, sort_order, experience_years, card_summary, trajectory, certifications, assigned_groups, icon';

async function fetchActiveCoaches () {
  const sb = getSupabase();
  let { data, error } = await sb.from('coaches')
    .select(COACHES_PUBLIC_SELECT)
    .eq('active', true)
    .order('sort_order');
  if (error && /photo_focus/i.test(error.message || '')) {
    const legacy = await sb.from('coaches')
      .select(COACHES_PUBLIC_SELECT_LEGACY)
      .eq('active', true)
      .order('sort_order');
    if (legacy.error) throw legacy.error;
    data = (legacy.data || []).map(c => ({ ...c, photo_focus_x: 50, photo_focus_y: 50 }));
  } else if (error) {
    throw error;
  }
  return data || [];
}

async function fetchProductsAdmin () {
  return fetchActiveProducts();
}

/** Órdenes visibles en Ventas y pagos / verificar (no confirmadas ni entregadas). */
const ORDER_STATUSES_ADMIN_QUEUE = ['pendiente', 'en_revision', 'rechazado'];

function isOrderInAdminSalesQueue (status) {
  const s = String(status || '').toLowerCase();
  return ORDER_STATUSES_ADMIN_QUEUE.includes(s);
}

async function fetchOrdersAdminLandingQueue () {
  const sb = getSupabase();
  const { data, error } = await sb.from('orders')
    .select(`
      id, order_number, total, currency, status, created_at, updated_at,
      guest_student_name, guest_student_code, guest_tutor_name, guest_phone, guest_email,
      receipt_path, receipt_uploaded_at, transfer_reference, admin_notes, reviewed_at,
      student_id,
      students ( id, code, full_name, category, status, tutor_name, tutor_phone, tutor_email, birth_date ),
      order_items ( id, product_id, product_name, size, qty, unit_price, line_total )
    `)
    .in('status', ORDER_STATUSES_ADMIN_QUEUE)
    .order('created_at', { ascending: false })
    .limit(80);
  if (error) throw error;
  return data || [];
}

/** Todas las órdenes (admin interno). Preferir fetchOrdersAdminLandingQueue en listados operativos. */
async function fetchOrdersAdmin () {
  const sb = getSupabase();
  const { data, error } = await sb.from('orders')
    .select(`
      id, order_number, total, currency, status, created_at, updated_at,
      guest_student_name, guest_student_code, guest_tutor_name, guest_phone, guest_email,
      receipt_path, receipt_uploaded_at, transfer_reference, admin_notes, reviewed_at,
      student_id,
      students ( id, code, full_name, category, status, tutor_name, tutor_phone, tutor_email, birth_date ),
      order_items ( id, product_id, product_name, size, qty, unit_price, line_total )
    `)
    .order('created_at', { ascending: false })
    .limit(80);
  if (error) throw error;
  return data || [];
}

async function fetchStudentOrdersByUuidActive (studentUuid, studentCode) {
  const all = await fetchStudentOrdersByUuid(studentUuid, studentCode);
  return (all || []).filter((o) => isOrderInAdminSalesQueue(o.s));
}

function parseLeadMessageField (message, prefix) {
  if (!message) return '';
  const re = new RegExp(`${prefix}:\\s*(.+?)(?:\\n|$)`, 'i');
  const m = String(message).match(re);
  return m ? m[1].trim() : '';
}

function mapInterestedLead (row) {
  const studentName = row.student_name || parseLeadMessageField(row.message, 'Alumno');
  const studentAge = row.student_age || parseLeadMessageField(row.message, 'Edad');
  const ann = row.announcements;
  const evt = row.events;
  const announcementTitle = (ann && (ann.title || ann)) || row.announcement_title || '';
  const eventTitle = (evt && (evt.title || evt)) || row.event_title || '';
  let contextLabel = 'Contacto web';
  let contextKind = 'landing';
  if (row.announcement_id) {
    contextKind = 'anuncio';
    contextLabel = announcementTitle ? `Aviso: ${announcementTitle}` : 'Aviso';
  } else if (row.event_id) {
    contextKind = 'evento';
    contextLabel = eventTitle ? `Evento: ${eventTitle}` : 'Evento';
  }
  return {
    id: row.id,
    name: row.name,
    phone: row.phone || '—',
    email: row.email || '—',
    message: row.message || '',
    source: row.source || 'landing',
    studentName: studentName || '—',
    studentAge: studentAge || '—',
    status: row.status || 'nuevo',
    admin_notes: row.admin_notes || '',
    contacted_at: row.contacted_at,
    created_at: row.created_at,
    announcementId: row.announcement_id || null,
    eventId: row.event_id || null,
    announcementTitle,
    eventTitle,
    contextLabel,
    contextKind,
    createdLabel: row.created_at
      ? new Date(row.created_at).toLocaleString('es-MX', { dateStyle: 'medium', timeStyle: 'short' })
      : '—',
  };
}

function groupInterestedLeads (rows) {
  const map = new Map();
  const ensure = (key, meta) => {
    if (!map.has(key)) map.set(key, { ...meta, leads: [] });
    return map.get(key);
  };
  (rows || []).forEach((lead) => {
    if (lead.announcementId) {
      ensure(`ann:${lead.announcementId}`, {
        key: `ann:${lead.announcementId}`,
        kind: 'anuncio',
        title: lead.announcementTitle || 'Aviso',
        icon: 'megaphone',
      }).leads.push(lead);
    } else if (lead.eventId) {
      ensure(`evt:${lead.eventId}`, {
        key: `evt:${lead.eventId}`,
        kind: 'evento',
        title: lead.eventTitle || 'Evento',
        icon: 'cal',
      }).leads.push(lead);
    } else {
      ensure('landing', {
        key: 'landing',
        kind: 'landing',
        title: 'Formulario de contacto (landing)',
        icon: 'form',
      }).leads.push(lead);
    }
  });
  const order = { anuncio: 0, evento: 1, landing: 2 };
  return [...map.values()].sort((a, b) => (order[a.kind] ?? 9) - (order[b.kind] ?? 9));
}

async function fetchInterestedLeads () {
  const sb = getSupabase();
  if (!sb) return [];
  let { data, error } = await sb.from('interested_leads')
    .select(`
      id, name, phone, email, message, source, student_name, student_age,
      status, admin_notes, contacted_at, created_at,
      announcement_id, event_id,
      announcements:announcement_id ( title ),
      events:event_id ( title )
    `)
    .order('created_at', { ascending: false });
  if (error && /announcement_id|event_id|announcements|events/i.test(error.message || '')) {
    ({ data, error } = await sb.from('interested_leads')
      .select('id, name, phone, email, message, source, student_name, student_age, status, admin_notes, contacted_at, created_at')
      .order('created_at', { ascending: false }));
  }
  if (error) throw error;
  return (data || []).map(mapInterestedLead);
}

function exportInterestedLeadsCsv (leads) {
  downloadCsv(`interesados-tecos-${new Date().toISOString().slice(0, 10)}.csv`, [
    ['Fecha', 'Estado', 'Aviso/Evento', 'Nombre', 'Teléfono', 'Correo', 'Alumno', 'Edad', 'Mensaje', 'Origen'],
    ...leads.map(l => [l.createdLabel, l.status, l.contextLabel, l.name, l.phone, l.email, l.studentName, l.studentAge, l.message, l.source]),
  ]);
}

function notifyLeadsChanged () {
  window.dispatchEvent(new CustomEvent('tecos:leads-changed'));
}

/** Landing → interested_leads (sin .select(): anon no tiene SELECT en esa tabla) */
async function submitInterestedLead (payload) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado. Revisa config.js');
  const phoneRaw = payload.phone?.trim() || null;
  const phoneNorm = phoneRaw && typeof formatPhoneForStorage === 'function'
    ? formatPhoneForStorage(phoneRaw)
    : phoneRaw;
  const rpcArgs = {
    p_name: payload.name?.trim(),
    p_phone: phoneNorm,
    p_email: payload.email?.trim() || null,
    p_message: payload.message?.trim() || null,
    p_student_name: payload.student_name?.trim() || null,
    p_student_age: payload.student_age?.trim() || null,
    p_source: payload.source || 'landing',
    p_announcement_id: payload.announcement_id || null,
    p_event_id: payload.event_id || null,
  };
  const { data: leadId, error: rpcError } = await sb.rpc('submit_interested_lead', rpcArgs);
  if (!rpcError && leadId) return { id: leadId };

  const rpcMissing = rpcError && (
    rpcError.code === '42883'
    || rpcError.code === 'PGRST202'
    || String(rpcError.message || '').includes('submit_interested_lead')
  );
  if (!rpcMissing) throw rpcError;

  const base = {
    name: rpcArgs.p_name,
    phone: rpcArgs.p_phone,
    email: rpcArgs.p_email,
    message: rpcArgs.p_message,
    source: rpcArgs.p_source,
  };
  const full = {
    ...base,
    student_name: rpcArgs.p_student_name,
    student_age: rpcArgs.p_student_age,
    announcement_id: rpcArgs.p_announcement_id,
    event_id: rpcArgs.p_event_id,
  };
  let { error } = await sb.from('interested_leads').insert(full);
  if (error && (error.code === '42703' || String(error.message).includes('column'))) {
    const legacyMessage = [
      payload.message?.trim(),
      payload.student_name?.trim() && `Alumno: ${payload.student_name.trim()}`,
      payload.student_age?.trim() && `Edad: ${payload.student_age.trim()}`,
    ].filter(Boolean).join('\n');
    ({ error } = await sb.from('interested_leads').insert({
      ...base,
      message: legacyMessage || null,
    }));
  }
  if (error) throw error;
  return { id: null };
}

async function fetchAnnouncementsAdmin () {
  const sb = getSupabase();
  let { data, error } = await sb.from('announcements')
    .select(`${ANNOUNCEMENTS_PUBLIC_SELECT}, created_at`)
    .order('created_at', { ascending: false });
  if (error && /starts_at|ends_at|multi_day|maps_url|image_path|image_focus|location|column/i.test(error.message || '')) {
    ({ data, error } = await sb.from('announcements')
      .select('id, title, body, priority, published_at, created_at')
      .order('created_at', { ascending: false }));
  }
  if (error) throw error;
  return data || [];
}

Object.assign(window, {
  EmptyState,
  LoadingBlock,
  AuthRequired,
  useLiveData,
  AcademySettingsProvider,
  useAcademySettings,
  mapStudentRow,
  studentPhotoUrl,
  mapPaymentRow,
  announcementImageUrl,
  formatAnnouncementVigencia,
  mapAnnouncementCard,
  mapAnnouncementCalendar,
  formatEventDayKey,
  buildEventDayKeys,
  eventImageUrl,
  mapEventCalendar,
  mapBirthdayCalendar,
  mapCoachCard,
  fetchStudentsAdmin,
  fetchStudentPaymentsByUuid,
  fetchStudentOrdersByUuid,
  fetchStudentNotificationsByUuid,
  exportStudentsCsv,
  exportStudentExpedienteCsv,
  formatBirthDateDisplay,
  isDemoStudentCode,
  DEMO_STUDENT_CODES,
  fetchPaymentsAdmin,
  exportPaymentsCsv,
  paymentDisplayEstado,
  notifyAcademyDataSync,
  notifyPaymentsChanged,
  notifyStudentsChanged,
  notifyStudentDocumentsChanged,
  notifyComprobantesChanged,
  notifyOrdersChanged,
  notifyAnnouncementsChanged,
  datetimeLocalToIso,
  isAnnouncementPublished,
  DEFAULT_ACADEMY_SETTINGS,
  normalizeAcademySettings,
  fetchAcademySettings,
  defaultPaymentDueDate,
  computeLateFees,
  daysLateFromDue,
  billingPeriodLabel,
  billingLateTierLabel,
  billingAdvancePayNote,
  portalBillingScheduleHint,
  attachBillingTierMeta,
  validateBillingSettings,
  getBillingTierForDate,
  dayInMonthRange,
  formatDayRangeLabel,
  dayOfMonthForBilling,
  getBillingPreviewRows,
  formatMoneyMX,
  billingConfigOverlapWarning,
  notifySettingsChanged,
  buildWhatsAppLink,
  getHeroStats,
  getTiendaHeroStats,
  getTiendaGuarantees,
  parseTiendaStatNumber,
  searchAdminGlobal,
  fetchAdminDashboardStats,
  analyzeFinanceDashboard,
  financePct,
  sumAllTimeIncome,
  sumAllTimeEgresos,
  sumEgresosInRange,
  buildDashboardMonthlySeriesFinance,
  fetchFinanceTablesForDashboard,
  fetchPaymentsForDashboard,
  mapRecentPaymentForDashboard,
  sumIncomeInMonth,
  paymentCashDate,
  paymentPeriodMonthDate,
  paymentIncomeDate,
  normalizePaymentStatus,
  DASHBOARD_MONTH_LABELS,
  filterDashboardMonths,
  sumDashboardMonths,
  buildDashboardMonthlySeries,
  fetchPublishedAnnouncements,
  fetchPublicEvents,
  fetchActiveCoaches,
  notifyCoachesChanged,
  parseCoachLines,
  coachPhotoUrl,
  coachPhotoFocus,
  coachPhotoObjectPosition,
  fetchProductsAdmin,
  fetchOrdersAdmin,
  fetchOrdersAdminLandingQueue,
  isOrderInAdminSalesQueue,
  fetchStudentOrdersByUuidActive,
  fetchInterestedLeads,
  mapInterestedLead,
  groupInterestedLeads,
  exportInterestedLeadsCsv,
  notifyLeadsChanged,
  submitInterestedLead,
  fetchAnnouncementsAdmin,
  mapProductTienda,
  PRODUCT_SIZE_OPTIONS,
  parseStockBySize,
  normalizeTecStudentCode,
  formatTecCodeDigits,
  formatTecCodeDigitsInput,
  buildTecStudentCode,
  fetchNextStudentCodeDigits,
  DEFAULT_PHONE_DIAL,
  PHONE_DIAL_OPTIONS,
  normalizeWaPhoneDigits,
  formatWaPhoneDisplay,
  formatPhoneForStorage,
  parsePhoneWithDial,
  combinePhoneWithDial,
  computeSeniorityLabel,
  STUDENT_DOCUMENT_TYPES,
  defaultDocumentRequirements,
  mergeDocumentRequirements,
  getApplicableDocumentTypes,
  lookupStudentCodePublic,
  getProductSizeStock,
  sumStockBySize,
  productImageUrl,
  notifyProductsChanged,
  fetchProductsForTienda,
  mapPortalStudent,
  isPortalMonthExigible,
  firstExigiblePendingMonth,
  countExigiblePendingMonths,
  mapPortalPaymentsToMonths,
  computeStudentAdeudoAmount,
  waBillingKindLabel,
  resolveCurrentMonthBillingWa,
  buildWaRecipientFromStudentSync,
  buildWaRecipientFromStudent,
  fetchStudentPaymentCalendar,
  MONTH_KEYS,
  MONTH_LABELS,
  parseJoinedMonthStart,
  firstBillableMonthInYear,
  isEnrollmentMonth,
  computeProratedFirstMonthAmount,
  parseProrationFromConcept,
  getPaymentProrationMeta,
  attachProrationToSlot,
  studentProrateSeedOptions,
  paymentAmountAndConceptForSeedMonth,
  isMonthBeforeJoinInYear,
  isPaymentBillableForJoin,
  filterPaymentsForJoin,
  isMonthInBillingCycle,
  mensualidadConceptForMonth,
  dueDateForBillingMonth,
  isFutureBillingMonth,
  baseBillingMonthCharge,
  mapPortalOrders,
  mapPortalPaymentsExpediente,
  exportPaymentsCsv,
  fetchPublicGallery,
  fetchPublicGalleryAlbums,
  fetchPublicGalleryCategories,
  fetchEventsForGalleryLink,
  notifyGalleryChanged,
});