/* Cliente Supabase — TECOS ELITE VOLLEYBALL */

let _supabase = null;

function getSupabase () {
  if (_supabase) return _supabase;
  if (!window.supabase?.createClient) {
    console.warn('[Tecos] Supabase JS no cargado. Revisa index.html');
    return null;
  }
  const cfg = window.SUPABASE_CONFIG;
  if (!cfg?.url || !cfg?.anonKey || cfg.url.includes('TU_PROYECTO')) {
    console.warn('[Tecos] Crea config.js desde config.example.js con tus credenciales.');
    return null;
  }
  _supabase = window.supabase.createClient(cfg.url, cfg.anonKey);
  return _supabase;
}

function getSupabaseConfigStatus () {
  if (!window.supabase?.createClient) {
    return { ok: false, code: 'cdn', message: 'No cargó la librería de Supabase. Revisa tu conexión o recarga la página.' };
  }
  const cfg = window.SUPABASE_CONFIG;
  if (!cfg?.url || !cfg?.anonKey) {
    return {
      ok: false,
      code: 'config',
      message: 'Falta configuración de Supabase. Crea local.settings.js o config.js (copia config.example.js) con url y anon key.',
    };
  }
  if (String(cfg.url).includes('TU_PROYECTO') || String(cfg.anonKey).includes('TU_ANON')) {
    return {
      ok: false,
      code: 'placeholder',
      message: 'Reemplaza los valores de ejemplo en local.settings.js o config.js con tu URL y anon key de Supabase.',
    };
  }
  return { ok: true };
}

function isSupabaseReady () {
  return getSupabaseConfigStatus().ok && !!getSupabase();
}

/** Formulario landing — la versión completa está en data-service.jsx (sobrescribe en window) */
async function submitInterestedLead (payload) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado. Revisa config.js');
  const { error } = await sb.from('interested_leads').insert({
    name: payload.name?.trim(),
    phone: payload.phone?.trim() || null,
    email: payload.email?.trim() || null,
    message: payload.message?.trim() || null,
    student_name: payload.student_name?.trim() || null,
    student_age: payload.student_age?.trim() || null,
    source: payload.source || 'landing',
  });
  if (error) throw error;
  return { id: null };
}

/** Cuenta bancaria para modales de transferencia (tienda | mensualidad) */
async function fetchBankTransfer (purpose = 'tienda') {
  const sb = getSupabase();
  if (!sb) return null;
  const { data, error } = await sb.rpc('get_bank_transfer', { p_purpose: purpose });
  if (error) {
    console.error('[Tecos] get_bank_transfer', error);
    return null;
  }
  return data;
}

/** Pedido de tienda sin login */
async function createGuestOrder ({
  productId, size, qty,
  studentName, studentCode, tutorName, phone, email,
}) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const phoneNorm = phone && typeof formatPhoneForStorage === 'function'
    ? formatPhoneForStorage(phone)
    : phone;
  const { data, error } = await sb.rpc('create_guest_order', {
    p_product_id: productId,
    p_size: size,
    p_qty: qty,
    p_student_name: studentName,
    p_student_code: studentCode,
    p_tutor_name: tutorName,
    p_phone: phoneNorm,
    p_email: email || null,
  });
  if (error) throw error;
  return data;
}

/** Sube comprobante al bucket receipts y lo vincula a la orden */
async function uploadOrderReceipt ({ orderId, phone, file, studentCode }) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const ext = (file.name.split('.').pop() || 'jpg').toLowerCase();
  const path = `guest/${orderId}/${Date.now()}.${ext}`;
  const { error: upErr } = await sb.storage.from('receipts').upload(path, file, {
    cacheControl: '3600',
    upsert: false,
  });
  if (upErr) throw upErr;
  const { error: rpcErr } = await sb.rpc('attach_order_receipt', {
    p_order_id: orderId,
    p_phone: phone || '',
    p_storage_path: path,
    p_student_code: studentCode || null,
  });
  if (rpcErr) throw rpcErr;
  return path;
}

/** Mensualidad: usuario autenticado sube comprobante */
async function uploadPaymentReceipt ({ paymentId, file }) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { data: { user } } = await sb.auth.getUser();
  if (!user) throw new Error('Debes iniciar sesión');
  const ext = (file.name.split('.').pop() || 'jpg').toLowerCase();
  const path = `${user.id}/payments/${paymentId}/${Date.now()}.${ext}`;
  const { error: upErr } = await sb.storage.from('receipts').upload(path, file, {
    cacheControl: '3600',
    upsert: false,
  });
  if (upErr) throw upErr;
  const { error: rpcErr } = await sb.rpc('attach_payment_receipt', {
    p_payment_id: paymentId,
    p_storage_path: path,
  });
  if (rpcErr) throw rpcErr;
  return path;
}

/** Mensualidad por código de alumno (portal sin sesión Auth de admin) */
async function uploadPaymentReceiptByCode ({ studentCode, concept, file, year, monthIndex, amount }) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const code = typeof normalizeTecStudentCode === 'function'
    ? normalizeTecStudentCode(studentCode)
    : String(studentCode || '').trim().toUpperCase();
  const ext = (file.name.split('.').pop() || 'jpg').toLowerCase();
  const path = `guest/payments/${code}/${Date.now()}.${ext}`;
  const { error: upErr } = await sb.storage.from('receipts').upload(path, file, {
    cacheControl: '3600',
    upsert: false,
  });
  if (upErr) throw upErr;
  const { data: paymentId, error: rpcErr } = await sb.rpc('attach_payment_receipt_by_code', {
    p_student_code: code,
    p_concept: concept,
    p_storage_path: path,
    p_year: year ?? null,
    p_month_index: monthIndex ?? null,
    p_amount: amount ?? null,
  });
  if (rpcErr) {
    const msg = rpcErr.message || '';
    if (/attach_payment_receipt_by_code|schema cache|PGRST202|row-level security/i.test(msg)) {
      throw new Error(
        'No se pudo registrar el comprobante. Ejecuta la migración 045_student_payment_receipt_portal.sql en Supabase (SQL Editor) e intenta de nuevo.'
      );
    }
    throw rpcErr;
  }
  return { path, paymentId };
}

async function getAuthSession () {
  const sb = getSupabase();
  if (!sb) return null;
  const { data: { session } } = await sb.auth.getSession();
  return session;
}

async function signInWithEmail (email, password) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { data, error } = await sb.auth.signInWithPassword({ email: email.trim(), password });
  if (error) throw error;
  return data;
}

/** Revalida la contraseña del admin con sesión activa (acciones destructivas en panel). */
async function verifyAdminSessionPassword (password) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { data: { user }, error: userErr } = await sb.auth.getUser();
  if (userErr || !user?.email) {
    throw new Error('Inicia sesión como administrador con correo y contraseña.');
  }
  const { error } = await sb.auth.signInWithPassword({
    email: user.email,
    password: String(password || ''),
  });
  if (error) {
    const msg = error.message || '';
    if (/invalid|credentials|password|email/i.test(msg)) {
      throw new Error('Contraseña incorrecta.');
    }
    throw error;
  }
  return true;
}

async function signOutSupabase () {
  const sb = getSupabase();
  if (!sb) return;
  await sb.auth.signOut();
}

async function fetchMyProfileRole () {
  const sb = getSupabase();
  if (!sb) return null;
  const { data: { user } } = await sb.auth.getUser();
  if (!user) return null;
  const { data, error } = await sb
    .from('profiles')
    .select('role')
    .eq('id', user.id)
    .maybeSingle();
  if (error) {
    console.error('[Tecos] profile role', error);
    return null;
  }
  return data?.role || null;
}

/** Pagos con comprobante pendientes de verificación (admin) */
async function fetchPaymentsPendingReview () {
  const sb = getSupabase();
  if (!sb) return [];
  const { data, error } = await sb
    .from('payments')
    .select(`
      id, concept, amount, currency, due_date, status,
      receipt_path, receipt_uploaded_at, transfer_reference, admin_notes,
      students ( code, full_name )
    `)
    .eq('status', 'en_revision')
    .not('receipt_path', 'is', null)
    .order('receipt_uploaded_at', { ascending: true });
  if (error) {
    console.error('[Tecos] payments pending', error);
    throw error;
  }
  return data || [];
}

async function getReceiptSignedUrl (storagePath, expiresIn = 3600) {
  const sb = getSupabase();
  if (!sb || !storagePath) return null;
  const { data, error } = await sb.storage.from('receipts').createSignedUrl(storagePath, expiresIn);
  if (error) {
    console.error('[Tecos] signed url', error);
    return null;
  }
  return data?.signedUrl || null;
}

async function approvePaymentReceipt (paymentId) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');

  const { data: pay, error: fetchErr } = await sb.from('payments')
    .select('id, amount, due_date, status')
    .eq('id', paymentId)
    .maybeSingle();
  if (fetchErr) throw fetchErr;

  if (pay && Number(pay.amount) <= 0 && typeof fetchAcademySettings === 'function') {
    const settings = await fetchAcademySettings();
    const due = pay.due_date ? new Date(pay.due_date) : null;
    const base = Number(settings.monthly_fee) || 0;
    const isAdvance = due && !Number.isNaN(due.getTime())
      && typeof isFutureBillingMonth === 'function'
      && isFutureBillingMonth(due.getFullYear(), due.getMonth());
    if (base > 0 && (isAdvance || pay.status === 'en_revision')) {
      const { error: amtErr } = await sb.from('payments')
        .update({ amount: base })
        .eq('id', paymentId);
      if (amtErr) throw amtErr;
    }
  }

  const { error } = await sb.rpc('approve_payment_receipt', { p_payment_id: paymentId });
  if (error) throw error;
}

async function rejectPaymentReceipt ({ paymentId, adminNotes }) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { data: oldPath, error: rpcErr } = await sb.rpc('reject_payment_receipt', {
    p_payment_id: paymentId,
    p_notes: adminNotes || null,
  });
  if (rpcErr) throw rpcErr;
  if (oldPath) {
    await sb.storage.from('receipts').remove([oldPath]);
  }
}

/** Pagos del alumno autenticado (por student_id o código demo) */
/** Órdenes de tienda pendientes de verificación (admin) */
async function fetchOrdersPendingReview () {
  const sb = getSupabase();
  if (!sb) return [];
  const { data, error } = await sb
    .from('orders')
    .select(`
      id, order_number, total, currency, status,
      receipt_path, receipt_uploaded_at, transfer_reference, admin_notes,
      guest_student_name, guest_student_code, guest_tutor_name, guest_phone, guest_email,
      created_at,
      order_items ( product_name, size, qty, unit_price, line_total )
    `)
    .eq('status', 'en_revision')
    .not('receipt_path', 'is', null)
    .order('receipt_uploaded_at', { ascending: true });
  if (error) throw error;
  return data || [];
}

/** Mensualidades confirmadas (admin historial) */
async function fetchPaymentsConfirmedHistory () {
  const sb = getSupabase();
  if (!sb) return [];
  const { data, error } = await sb
    .from('payments')
    .select(`
      id, concept, amount, currency, due_date, status, paid_at, reviewed_at,
      receipt_path, receipt_uploaded_at, transfer_reference, admin_notes,
      students (
        code, full_name, joined_at, category, status,
        tutor_name, tutor_phone, student_phone, photo_path
      )
    `)
    .eq('status', 'pagado')
    .order('paid_at', { ascending: false, nullsFirst: false })
    .limit(200);
  if (error) {
    console.error('[Tecos] payments history', error);
    throw error;
  }
  const rows = data || [];
  if (typeof isPaymentBillableForJoin !== 'function') return rows;
  return rows.filter((p) => isPaymentBillableForJoin(p, p.students?.joined_at));
}

/** Órdenes de tienda confirmadas o entregadas (admin historial) */
function isOrderBillableForJoin (order, joinedAt) {
  if (!joinedAt) return true;
  const join = new Date(joinedAt);
  if (Number.isNaN(join.getTime())) return true;
  const joinStart = new Date(join.getFullYear(), join.getMonth(), 1).getTime();
  const atIso = order?.reviewed_at || order?.created_at;
  if (!atIso) return false;
  const at = new Date(atIso).getTime();
  return !Number.isNaN(at) && at >= joinStart;
}

async function fetchStoreOrdersHistory () {
  const sb = getSupabase();
  if (!sb) return [];
  const { data, error } = await sb
    .from('orders')
    .select(`
      id, order_number, total, currency, status,
      receipt_path, receipt_uploaded_at, transfer_reference, admin_notes,
      reviewed_at, created_at,
      guest_student_name, guest_student_code, guest_tutor_name, guest_phone, guest_email,
      student_id,
      students ( code, full_name, joined_at ),
      order_items ( product_name, size, qty, unit_price, line_total )
    `)
    .in('status', ['confirmado', 'entregado'])
    .order('reviewed_at', { ascending: false, nullsFirst: false })
    .limit(200);
  if (error) {
    console.error('[Tecos] orders history', error);
    throw error;
  }
  const rows = data || [];
  return rows.filter((o) => isOrderBillableForJoin(o, o.students?.joined_at));
}

/** Órdenes de tienda pendientes de comprobante o en revisión (admin) */
async function fetchStoreOrdersAdminQueue () {
  const sb = getSupabase();
  if (!sb) return [];
  const { data, error } = await sb
    .from('orders')
    .select(`
      id, order_number, total, currency, status,
      receipt_path, receipt_uploaded_at, transfer_reference, admin_notes,
      guest_student_name, guest_student_code, guest_tutor_name, guest_phone, guest_email,
      created_at,
      order_items ( product_name, size, qty, unit_price, line_total )
    `)
    .in('status', ['pendiente', 'en_revision'])
    .order('created_at', { ascending: false })
    .limit(80);
  if (error) throw error;
  return data || [];
}

async function approveOrderReceipt (orderId) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { error } = await sb.rpc('approve_order_receipt', { p_order_id: orderId });
  if (error) throw error;
  if (typeof notifyOrdersChanged === 'function') notifyOrdersChanged();
  if (typeof notifyComprobantesChanged === 'function') notifyComprobantesChanged();
}

async function completeOrder (orderId) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { error } = await sb.rpc('complete_order', { p_order_id: orderId });
  if (error) throw error;
}

async function rejectOrderReceipt ({ orderId, adminNotes }) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { data: oldPath, error: rpcErr } = await sb.rpc('reject_order_receipt', {
    p_order_id: orderId,
    p_notes: adminNotes || null,
  });
  if (rpcErr) throw rpcErr;
  if (oldPath) {
    await sb.storage.from('receipts').remove([oldPath]);
  }
}

async function adminRepairOrderReceiptStatus (orderId) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { error } = await sb.rpc('admin_repair_order_receipt_status', { p_order_id: orderId });
  if (error) throw error;
}

async function adminDeleteStoreOrders (orderIds, adminNotes = null) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const ids = (orderIds || []).filter(Boolean);
  if (!ids.length) return 0;
  const { data, error } = await sb.rpc('admin_delete_store_orders', {
    p_order_ids: ids,
    p_notes: adminNotes || null,
  });
  if (error) throw error;
  return Number(data) || 0;
}

async function fetchStudentPayments (studentId) {
  const sb = getSupabase();
  if (!sb || !studentId) return [];
  const { data, error } = await sb
    .from('payments')
    .select('id, concept, amount, due_date, paid_at, status, receipt_path, admin_notes, transfer_reference')
    .eq('student_id', studentId)
    .order('due_date', { ascending: true });
  if (error) {
    console.error('[Tecos] student payments', error);
    return [];
  }
  return data || [];
}

async function fetchAdminNotifications (limit = 80) {
  const sb = getSupabase();
  if (!sb) return [];
  const { data, error } = await sb
    .from('notifications')
    .select('id, title, body, icon, category, read_at, created_at, entity_type, entity_id')
    .eq('recipient_role', 'admin')
    .order('created_at', { ascending: false })
    .limit(limit);
  if (error) {
    console.error('[Tecos] admin notifications', error);
    return [];
  }
  return data || [];
}

async function syncStudentPortalNotifications (studentCode) {
  const sb = getSupabase();
  if (!sb || !studentCode) return 0;
  const { data, error } = await sb.rpc('sync_student_portal_notifications', {
    p_student_code: studentCode,
  });
  if (error) {
    if (isRpcMissing(error, 'sync_student_portal_notifications')) return 0;
    console.warn('[Tecos] sync student notifications', error);
    return 0;
  }
  return typeof data === 'number' ? data : 0;
}

async function fetchPortalUpcomingBirthdays (studentCode, limit = 8) {
  const sb = getSupabase();
  if (!sb || !studentCode) return [];
  const code = typeof normalizeTecStudentCode === 'function'
    ? normalizeTecStudentCode(studentCode)
    : String(studentCode || '').trim().toUpperCase();
  const { data, error } = await sb.rpc('get_portal_upcoming_birthdays', {
    p_student_code: code,
    p_limit: limit,
  });
  if (error) {
    if (isRpcMissing(error, 'get_portal_upcoming_birthdays')) return [];
    console.warn('[Tecos] portal birthdays', error);
    return [];
  }
  return (data || []).map((r) => ({
    id: r.student_id,
    name: r.full_name,
    isSelf: !!r.is_self,
    daysUntil: r.days_until,
    label: r.label,
  }));
}

/** Cumpleaños de alumnos activos para un mes del calendario (landing / portal). */
async function fetchCalendarBirthdays (year, monthIndex) {
  const sb = getSupabase();
  if (!sb) return [];
  const y = Number(year) || new Date().getFullYear();
  const m = monthIndex != null ? Number(monthIndex) : null;
  const { data, error } = await sb.rpc('get_calendar_birthdays', {
    p_year: y,
    p_month: m,
  });
  if (error) {
    if (isRpcMissing(error, 'get_calendar_birthdays')) return [];
    console.warn('[Tecos] calendar birthdays', error);
    return [];
  }
  return data || [];
}

async function fetchStudentNotificationsByCode (studentCode, limit = 40) {
  const sb = getSupabase();
  if (!sb || !studentCode) return [];
  const { data, error } = await sb.rpc('get_notifications_for_student_code', {
    p_student_code: studentCode,
    p_limit: limit,
  });
  if (error) {
    console.error('[Tecos] student notifications', error);
    return [];
  }
  return data || [];
}

function subscribeAdminNotifications (onChange) {
  const sb = getSupabase();
  if (!sb) return () => {};
  const channel = sb
    .channel('tecos-admin-notifications')
    .on(
      'postgres_changes',
      { event: '*', schema: 'public', table: 'notifications', filter: 'recipient_role=eq.admin' },
      () => onChange()
    )
    .subscribe();
  return () => { sb.removeChannel(channel); };
}

function isRpcMissing (error, fnName) {
  return error && (
    error.code === '42883'
    || error.code === 'PGRST202'
    || String(error.message || '').includes(fnName)
  );
}

async function markNotificationRead (notificationId) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { error } = await sb.rpc('mark_notification_read', { p_notification_id: notificationId });
  if (!error) return;
  if (isRpcMissing(error, 'mark_notification_read')) {
    const { error: err2 } = await sb.from('notifications')
      .update({ read_at: new Date().toISOString() })
      .eq('id', notificationId)
      .eq('recipient_role', 'admin')
      .is('read_at', null);
    if (err2) throw err2;
    return;
  }
  throw error;
}

async function markNotificationReadByCode (notificationId, studentCode) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { error } = await sb.rpc('mark_notification_read_by_code', {
    p_notification_id: notificationId,
    p_student_code: studentCode,
  });
  if (error) throw error;
}

async function markAllNotificationsRead (role, studentCode = null) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { error } = await sb.rpc('mark_all_notifications_read', {
    p_role: role,
    p_student_code: studentCode,
  });
  if (!error) return;
  if (isRpcMissing(error, 'mark_all_notifications_read') && role === 'admin') {
    const { error: err2 } = await sb.from('notifications')
      .update({ read_at: new Date().toISOString() })
      .eq('recipient_role', 'admin')
      .is('read_at', null);
    if (err2) throw err2;
    return;
  }
  throw error;
}

async function deleteAdminNotifications () {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const session = typeof getAuthSession === 'function' ? await getAuthSession() : null;
  if (!session) throw new Error('Inicia sesión como administrador para borrar notificaciones.');
  const { data, error } = await sb.rpc('delete_admin_notifications');
  if (error) throw error;
  return typeof data === 'number' ? data : 0;
}

async function deleteStudentNotificationsByCode (studentCode) {
  const sb = getSupabase();
  if (!sb || !studentCode) throw new Error('Código de alumno requerido');
  const { data, error } = await sb.rpc('delete_notifications_for_student_code', {
    p_student_code: studentCode,
  });
  if (error) throw error;
  return typeof data === 'number' ? data : 0;
}

function getStoragePublicUrl (bucket, path) {
  const sb = getSupabase();
  if (!sb || !path) return null;
  const { data } = sb.storage.from(bucket).getPublicUrl(path);
  return data?.publicUrl || null;
}

async function uploadProductImage (file, productId) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const ext = (file.name.split('.').pop() || 'jpg').toLowerCase();
  const safeId = String(productId || 'new').replace(/[^a-zA-Z0-9-]/g, '');
  const path = `catalog/${safeId}/image-${Date.now()}.${ext}`;
  const { error } = await sb.storage.from('products').upload(path, file, {
    cacheControl: '3600',
    upsert: true,
  });
  if (error) throw error;
  return path;
}

async function uploadAnnouncementImage (file, announcementId) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const ext = (file.name.split('.').pop() || 'jpg').toLowerCase();
  const safeId = String(announcementId || 'new').replace(/[^a-zA-Z0-9-]/g, '');
  const path = `announcements/${safeId}/cover-${Date.now()}.${ext}`;
  const { error } = await sb.storage.from('gallery').upload(path, file, {
    cacheControl: '3600',
    upsert: true,
  });
  if (error) throw error;
  return path;
}

async function uploadEventImage (file, eventId) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const ext = (file.name.split('.').pop() || 'jpg').toLowerCase();
  const safeId = String(eventId || 'new').replace(/[^a-zA-Z0-9-]/g, '');
  const path = `events/${safeId}/cover-${Date.now()}.${ext}`;
  const { error } = await sb.storage.from('gallery').upload(path, file, {
    cacheControl: '3600',
    upsert: true,
  });
  if (error) throw error;
  return path;
}

async function confirmEventAttendance (eventId, studentCode) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { data, error } = await sb.rpc('confirm_event_attendance', {
    p_event_id: eventId,
    p_student_code: studentCode,
  });
  if (error) throw error;
  return data;
}

async function confirmAnnouncementRegistration (announcementId, studentCode) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { data, error } = await sb.rpc('confirm_announcement_registration', {
    p_announcement_id: announcementId,
    p_student_code: studentCode,
  });
  if (error) throw error;
  return data;
}

async function checkAnnouncementRegistration (announcementId, studentCode) {
  const sb = getSupabase();
  if (!sb || !announcementId) return false;
  const code = typeof normalizeTecStudentCode === 'function'
    ? normalizeTecStudentCode(studentCode)
    : String(studentCode || '').trim().toUpperCase();
  if (!code) return false;
  const { data, error } = await sb.from('announcement_registrations')
    .select('id')
    .eq('announcement_id', announcementId)
    .eq('student_code', code)
    .maybeSingle();
  if (error) return false;
  return !!data;
}

async function checkEventAttendance (eventId, studentCode) {
  const sb = getSupabase();
  if (!sb || !eventId) return false;
  const code = typeof normalizeTecStudentCode === 'function'
    ? normalizeTecStudentCode(studentCode)
    : String(studentCode || '').trim().toUpperCase();
  if (!code) return false;
  const { data, error } = await sb.from('event_confirmations')
    .select('id')
    .eq('event_id', eventId)
    .eq('student_code', code)
    .maybeSingle();
  if (error) return false;
  return !!data;
}

async function uploadCoachPhoto (file, coachId) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const ext = (file.name.split('.').pop() || 'jpg').toLowerCase();
  const folder = coachId ? `coaches/${coachId}` : 'coaches/new';
  const path = `${folder}/photo-${Date.now()}.${ext}`;
  const { error } = await sb.storage.from('gallery').upload(path, file, {
    cacheControl: '3600',
    upsert: true,
  });
  if (error) throw error;
  return path;
}

async function uploadLocationMarkerImage (file, previousPath = null) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const allowed = ['image/jpeg', 'image/png', 'image/webp'];
  const mime = (file.type || '').toLowerCase();
  if (!allowed.includes(mime)) {
    throw new Error('Formato no válido. Usa JPG, PNG o WebP.');
  }
  if (file.size > 5 * 1024 * 1024) {
    throw new Error('La imagen no puede superar 5 MB.');
  }
  const ext = mime === 'image/png' ? 'png' : mime === 'image/webp' ? 'webp' : 'jpg';
  const path = `location/map-marker.${ext}`;
  const bucket = 'public-assets';

  const doUpload = async () => {
    const { error } = await sb.storage.from(bucket).upload(path, file, {
      cacheControl: '60',
      upsert: true,
      contentType: mime,
    });
    if (error) throw error;
  };

  const timeoutMs = 60000;
  let timer;
  try {
    await Promise.race([
      doUpload(),
      new Promise((_, reject) => {
        timer = setTimeout(() => reject(new Error('La subida tardó demasiado. Revisa tu conexión e intenta de nuevo.')), timeoutMs);
      }),
    ]);
  } catch (err) {
    const msg = err?.message || '';
    if (/duplicate|already exists|409/i.test(msg)) {
      await sb.storage.from(bucket).remove([path]).catch(() => {});
      const { error: err2 } = await sb.storage.from(bucket).upload(path, file, {
        cacheControl: '60',
        upsert: true,
        contentType: mime,
      });
      if (err2) throw new Error(err2.message || 'No se pudo reemplazar la imagen del pin');
    } else if (/policy|permission|JWT|row-level|not authorized/i.test(msg)) {
      throw new Error('Sin permiso para subir. Inicia sesión como administrador.');
    } else {
      throw err;
    }
  } finally {
    clearTimeout(timer);
  }

  if (previousPath && previousPath !== path) {
    await sb.storage.from(bucket).remove([previousPath]).catch(() => {});
  }
  return path;
}

async function uploadGalleryFile (file, folder = 'uploads') {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const ext = (file.name.split('.').pop() || 'jpg').toLowerCase();
  const safeFolder = folder.replace(/[^a-zA-Z0-9/_-]/g, '');
  const path = `${safeFolder}/${Date.now()}.${ext}`;
  const { error } = await sb.storage.from('gallery').upload(path, file, {
    cacheControl: '3600',
    upsert: false,
  });
  if (error) throw error;
  return path;
}

async function uploadGalleryAlbumPhoto (file, albumId, sortOrder = 0) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const ext = (file.name.split('.').pop() || 'jpg').toLowerCase();
  const safeId = String(albumId).replace(/[^a-zA-Z0-9-]/g, '');
  const path = `albums/${safeId}/${Date.now()}-${sortOrder}.${ext}`;
  const { error } = await sb.storage.from('gallery').upload(path, file, {
    cacheControl: '3600',
    upsert: false,
  });
  if (error) throw error;
  return path;
}

async function fetchStudentDocumentsByCode (studentCode) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const code = typeof normalizeTecStudentCode === 'function'
    ? normalizeTecStudentCode(studentCode)
    : String(studentCode || '').trim().toUpperCase();
  const { data, error } = await sb.rpc('list_student_documents_by_code', { p_student_code: code });
  if (error) {
    if (/list_student_documents_by_code|schema cache|PGRST202/i.test(error.message || '')) {
      throw new Error('Ejecuta la migración 064_student_portal_documents.sql en Supabase.');
    }
    throw error;
  }
  return data || [];
}

function resolveStudentDocumentMime (file) {
  const raw = (file?.type || '').toLowerCase().split(';')[0].trim();
  if (raw) return raw;
  const ext = (file?.name?.split('.').pop() || '').toLowerCase();
  if (ext === 'pdf') return 'application/pdf';
  if (ext === 'png') return 'image/png';
  if (ext === 'webp') return 'image/webp';
  if (ext === 'jpg' || ext === 'jpeg') return 'image/jpeg';
  return '';
}

async function uploadStudentDocumentByCode ({ studentCode, docType, file, label }) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const code = typeof normalizeTecStudentCode === 'function'
    ? normalizeTecStudentCode(studentCode)
    : String(studentCode || '').trim().toUpperCase();
  if (!code) throw new Error('Código de alumno inválido');
  const allowed = ['image/jpeg', 'image/png', 'image/webp', 'application/pdf'];
  const mime = resolveStudentDocumentMime(file);
  if (!allowed.includes(mime)) {
    throw new Error('Formato no válido. Usa JPG, PNG, WebP o PDF.');
  }
  if (file.size > 5 * 1024 * 1024) {
    throw new Error('El archivo no puede superar 5 MB.');
  }
  const ext = mime === 'application/pdf' ? 'pdf'
    : mime === 'image/png' ? 'png'
      : mime === 'image/webp' ? 'webp' : 'jpg';
  const path = `student-docs/${code}/${docType}/file-${Date.now()}.${ext}`;
  const { error: upErr } = await sb.storage.from('gallery').upload(path, file, {
    cacheControl: '3600',
    upsert: false,
    contentType: mime,
  });
  if (upErr) {
    const msg = upErr.message || '';
    if (/mime type|not supported/i.test(msg)) {
      throw new Error('El servidor no acepta PDF aún. Ejecuta la migración 066_gallery_bucket_allow_pdf.sql en Supabase.');
    }
    if (/duplicate|already exists|409/i.test(msg)) {
      await sb.storage.from('gallery').remove([path]).catch(() => {});
      const { error: up2 } = await sb.storage.from('gallery').upload(path, file, {
        cacheControl: '3600', upsert: true, contentType: mime,
      });
      if (up2) throw up2;
    } else {
      throw upErr;
    }
  }
  const { data, error } = await sb.rpc('upsert_student_document_portal', {
    p_student_code: code,
    p_doc_type: docType,
    p_storage_path: path,
    p_file_name: file.name,
    p_mime_type: mime,
    p_label: label || null,
  });
  if (error) {
    if (/upsert_student_document_portal|schema cache|PGRST202/i.test(error.message || '')) {
      throw new Error('Ejecuta la migración 064_student_portal_documents.sql en Supabase.');
    }
    throw error;
  }
  if (typeof notifyStudentDocumentsChanged === 'function') {
    notifyStudentDocumentsChanged({ studentCode: code, docType });
  } else {
    window.dispatchEvent(new CustomEvent('tecos:student-documents-changed', { detail: { studentCode: code, docType } }));
  }
  return { id: data, path };
}

async function uploadStudentPhotoByCode (studentCode, file) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const code = String(studentCode || '').trim().toUpperCase();
  const ext = (file.name.split('.').pop() || 'jpg').toLowerCase();
  const path = `students/${code}/avatar.${ext}`;
  const { error: upErr } = await sb.storage.from('gallery').upload(path, file, {
    cacheControl: '3600',
    upsert: true,
  });
  if (upErr) throw upErr;
  const { error: rpcErr } = await sb.rpc('set_student_photo_by_code', {
    p_student_code: code,
    p_storage_path: path,
  });
  if (rpcErr) throw rpcErr;
  return path;
}

const PRODUCTS_PUBLIC_SELECT = 'id, sku, name, category, price, sizes, stock, stock_by_size, image_path, active, show_on_landing, cover_focus_x, cover_focus_y, gallery_paths';
const PRODUCTS_PUBLIC_SELECT_LEGACY = 'id, sku, name, category, price, sizes, stock, image_path, active';

function legacyStockBySizeFromRow (row) {
  let sizes = row?.sizes;
  if (typeof sizes === 'string') {
    try { sizes = JSON.parse(sizes); } catch { sizes = ['Único']; }
  }
  if (!Array.isArray(sizes) || !sizes.length) sizes = ['Único'];
  const out = {};
  sizes.forEach((s, i) => {
    out[s] = i === 0 ? (Number(row?.stock) || 0) : 0;
  });
  return out;
}

/** Carga productos activos para tienda pública (con detalle de error). */
async function fetchActiveProductsSafe ({ landingOnly = false } = {}) {
  const sb = getSupabase();
  if (!sb) {
    return { rows: [], error: 'Supabase no configurado. Crea config.js con tu URL y anon key.' };
  }
  const runQuery = (selectCols) => {
    let q = sb.from('products').select(selectCols).eq('active', true).order('name');
    if (landingOnly) q = q.eq('show_on_landing', true);
    return q;
  };
  let { data, error } = await runQuery(PRODUCTS_PUBLIC_SELECT);
  if (error && /stock_by_size|show_on_landing|cover_focus|gallery_paths|column/i.test(error.message || '')) {
    const legacy = await runQuery(PRODUCTS_PUBLIC_SELECT_LEGACY);
    if (legacy.error) {
      return { rows: [], error: legacy.error.message || String(legacy.error) };
    }
    data = (legacy.data || []).map(r => ({
      ...r,
      stock_by_size: legacyStockBySizeFromRow(r),
      show_on_landing: landingOnly ? true : (r.show_on_landing !== false),
      cover_focus_x: 50,
      cover_focus_y: 50,
      gallery_paths: [],
    }));
    if (landingOnly) data = data.filter(r => r.show_on_landing !== false);
    error = null;
  } else if (error) {
    return { rows: [], error: error.message || String(error) };
  }
  return { rows: data || [], error: null };
}

async function fetchActiveProducts () {
  const { rows, error } = await fetchActiveProductsSafe();
  if (error) console.error('[Tecos] products', error);
  return rows;
}

async function verifyStudentPortalLogin (studentCode, password) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { data, error } = await sb.rpc('verify_student_portal_login', {
    p_student_code: studentCode,
    p_password: password,
  });
  if (error) throw error;
  return data;
}

async function changeStudentPortalPassword ({ studentCode, currentPassword, newPassword }) {
  const sb = getSupabase();
  if (!sb) throw new Error('Supabase no configurado');
  const { data, error } = await sb.rpc('change_student_portal_password', {
    p_student_code: studentCode,
    p_current_password: currentPassword,
    p_new_password: newPassword,
  });
  if (error) throw error;
  if (data && data.ok === false) throw new Error(data.error || 'No se pudo cambiar la contraseña');
  return data;
}

Object.assign(window, {
  getSupabase,
  isSupabaseReady,
  getSupabaseConfigStatus,
  verifyStudentPortalLogin,
  changeStudentPortalPassword,
  submitInterestedLead,
  fetchBankTransfer,
  createGuestOrder,
  uploadOrderReceipt,
  uploadPaymentReceipt,
  uploadPaymentReceiptByCode,
  getAuthSession,
  signInWithEmail,
  verifyAdminSessionPassword,
  signOutSupabase,
  fetchMyProfileRole,
  fetchPaymentsPendingReview,
  fetchPaymentsConfirmedHistory,
  isOrderBillableForJoin,
  fetchOrdersPendingReview,
  fetchStoreOrdersAdminQueue,
  fetchStoreOrdersHistory,
  getReceiptSignedUrl,
  approvePaymentReceipt,
  rejectPaymentReceipt,
  approveOrderReceipt,
  rejectOrderReceipt,
  adminRepairOrderReceiptStatus,
  adminDeleteStoreOrders,
  completeOrder,
  fetchStudentPayments,
  getStoragePublicUrl,
  uploadGalleryFile,
  uploadGalleryAlbumPhoto,
  uploadProductImage,
  uploadAnnouncementImage,
  uploadEventImage,
  confirmEventAttendance,
  confirmAnnouncementRegistration,
  checkAnnouncementRegistration,
  checkEventAttendance,
  uploadCoachPhoto,
  uploadLocationMarkerImage,
  uploadStudentPhotoByCode,
  fetchStudentDocumentsByCode,
  uploadStudentDocumentByCode,
  fetchActiveProducts,
  fetchActiveProductsSafe,
  fetchAdminNotifications,
  fetchStudentNotificationsByCode,
  syncStudentPortalNotifications,
  fetchPortalUpcomingBirthdays,
  fetchCalendarBirthdays,
  subscribeAdminNotifications,
  markNotificationRead,
  markNotificationReadByCode,
  markAllNotificationsRead,
  deleteAdminNotifications,
  deleteStudentNotificationsByCode,
});