// =====================================================================
// Emporium da Arte — Cliente Supabase
// =====================================================================
// Expõe `window.sb` (cliente Supabase) e helpers de banco para o resto do app.
//
// CHAVES:
//   - SUPABASE_PUBLISHABLE  →  pública/segura no front (designed pra isso)
//   - sb_secret_*           →  NUNCA aqui, NUNCA em git, só servidor
//
// Diagnóstico rápido (cole no console do navegador):
//   await dbPing()          →  testa conexão e lista usuários
//   await dbVerifyPin(id, pin)
// =====================================================================

const SUPABASE_URL         = "https://nodlurvavsyovvhsmpon.supabase.co";
const SUPABASE_PUBLISHABLE = "sb_publishable_DjLTre1WqgmlRdsiGjBRqQ_7PFRP2q8";

const sb = window.supabase.createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE, {
  auth: { persistSession: false }, // login do PDV é por PIN, não Supabase Auth
});

// ─── Helpers de banco ────────────────────────────────────────────────

async function dbListLoginUsers() {
  const { data, error } = await sb.rpc("list_login_users");
  if (error) throw error;
  return data; // [{id, name, initials, role}]
}

async function dbVerifyPin(userId, pin) {
  const { data, error } = await sb.rpc("verify_user_pin", {
    p_user_id: userId,
    p_pin: pin,
  });
  if (error) throw error;
  return data === true;
}

// ─── Admin de usuários (apenas dono) ─────────────────────────────────
async function dbListUsersAdmin() {
  const { data, error } = await sb.rpc("list_users_admin");
  if (error) throw error;
  return data || [];
}

// upsert: id null = cria; id presente = atualiza. PIN só é trocado se for
// não-vazio (passe "" pra manter PIN atual).
async function dbUpsertUserAdmin({ id, name, role, isActive, permissions, pin }) {
  const { data, error } = await sb.rpc("upsert_user_admin", {
    p_id:          id ?? null,
    p_name:        name,
    p_role:        role,
    p_is_active:   isActive,
    p_permissions: permissions ?? null,
    p_pin:         pin || null,
  });
  if (error) throw error;
  return data; // uuid
}

async function dbDeactivateUserAdmin(id) {
  const { error } = await sb.rpc("deactivate_user_admin", { p_id: id });
  if (error) throw error;
}

// ─── Caixa (cash sessions) ──────────────────────────────────────────

async function dbGetActiveCashSession() {
  const { data, error } = await sb.rpc("get_active_cash_session");
  if (error) throw error;
  return data && data.length > 0 ? data[0] : null;
}

async function dbOpenCashSession(userId, openingBalance) {
  const { data, error } = await sb.rpc("open_cash_session", {
    p_user_id: userId,
    p_opening_balance: openingBalance ?? 0,
  });
  if (error) throw error;
  return data && data.length > 0 ? data[0] : null;
}

// ─── Catálogo (produtos, categorias, fornecedores) ─────────────────

async function dbListProducts(status = "active") {
  // Paginação cliente-side em lotes de 1000. O PostgREST do Supabase tem
  // `db-max-rows = 1000` no servidor, que é teto duro: `.range()` sozinho
  // não passa disso. Para uma loja com >1000 produtos, busca-se em janelas
  // até voltar um lote incompleto (sinal de que acabou).
  const PAGE = 1000;
  const rows = [];
  let offset = 0;
  while (true) {
    const { data, error } = await sb.rpc("list_products", { p_status: status })
      .range(offset, offset + PAGE - 1);
    if (error) throw error;
    const batch = data || [];
    rows.push(...batch);
    if (batch.length < PAGE) break;  // último lote → fim
    offset += PAGE;
  }
  // Normaliza pro shape usado pelas telas (cat/supplier como atalhos do mock).
  return rows.map(r => ({
    id:            r.id,
    code:          r.code,
    name:          r.name,
    cat:           r.category_id,
    cat_name:      r.category_name,
    supplier:      r.supplier_id,
    supplier_name: r.supplier_name,
    cost:          Number(r.cost  || 0),
    price:         Number(r.price || 0),
    stock:         Number(r.current_stock || 0),
    min:           Number(r.min_stock || 0),
    glyph:         r.glyph   || "",
    c1:            r.color_a || "#FFE1EC",
    c2:            r.color_b || "#FF93BC",
    sku:           r.code,            // legado: telas antigas leem p.sku
    is_active:     r.is_active,
    // E-commerce
    is_online:     r.is_online === true,
    description:   r.description || "",
    weight_g:      r.weight_g  != null ? Number(r.weight_g)  : null,
    length_cm:     r.length_cm != null ? Number(r.length_cm) : null,
    width_cm:      r.width_cm  != null ? Number(r.width_cm)  : null,
    height_cm:     r.height_cm != null ? Number(r.height_cm) : null,
    // Preço promocional (válido dentro da janela sale_starts_at..sale_ends_at)
    sale_price:     r.sale_price     != null ? Number(r.sale_price) : null,
    sale_starts_at: r.sale_starts_at || null,
    sale_ends_at:   r.sale_ends_at   || null,
    // Auditoria (fase 5)
    created_at:      r.created_at      || null,
    updated_at:      r.updated_at      || null,
    created_by:      r.created_by      || null,
    created_by_name: r.created_by_name || null,
    updated_by:      r.updated_by      || null,
    updated_by_name: r.updated_by_name || null,
  }));
}

async function dbUpdateProductEcommerce({
  id, isOnline, description, weightG, lengthCm, widthCm, heightCm,
  salePrice, saleStartsAt, saleEndsAt,
}) {
  const { error } = await sb.rpc("update_product_ecommerce", {
    p_id:             id,
    p_is_online:      !!isOnline,
    p_description:    description || null,
    p_weight_g:       weightG  != null && weightG  !== ""  ? Number(weightG)  : null,
    p_length_cm:      lengthCm != null && lengthCm !== "" ? Number(lengthCm) : null,
    p_width_cm:       widthCm  != null && widthCm  !== ""  ? Number(widthCm)  : null,
    p_height_cm:      heightCm != null && heightCm !== "" ? Number(heightCm) : null,
    p_sale_price:     salePrice != null && salePrice !== "" ? Number(salePrice) : null,
    p_sale_starts_at: saleStartsAt || null,
    p_sale_ends_at:   saleEndsAt   || null,
  });
  if (error) throw error;
}

async function dbListCategories() {
  const { data, error } = await sb.rpc("list_categories");
  if (error) throw error;
  return (data || []).map(r => ({
    id: r.id, name: r.name, icon: r.icon || "tag",
    color: r.color || "#F94C99", display_order: r.display_order,
    is_active: r.is_active !== false,
    image_url: r.image_url || null,
    product_count: Number(r.product_count || 0),
  }));
}

// Lista TODAS as categorias (ativas + inativas) com contagem completa
async function dbListCategoriesAdmin() {
  const { data, error } = await sb.rpc("list_categories_admin");
  if (error) throw error;
  return (data || []).map(r => ({
    id: r.id, name: r.name, icon: r.icon || "tag",
    color: r.color || "#F94C99", display_order: r.display_order,
    is_active: r.is_active,
    image_url: r.image_url || null,
    created_at: r.created_at,
    product_count:        Number(r.product_count || 0),
    active_product_count: Number(r.active_product_count || 0),
  }));
}

async function dbDeleteCategory(id) {
  const { error } = await sb.rpc("delete_category", { p_id: id });
  if (error) throw error;
}

async function dbSetCategoryActive(id, active) {
  const { error } = await sb.rpc("set_category_active", { p_id: id, p_active: active });
  if (error) throw error;
}

async function dbListProductsByCategory(categoryId) {
  const { data, error } = await sb.rpc("list_products_by_category", { p_category_id: categoryId });
  if (error) throw error;
  return (data || []).map(r => ({
    id: r.id, code: r.code, name: r.name,
    price: Number(r.price || 0), stock: Number(r.current_stock || 0),
    is_active: r.is_active, is_online: r.is_online === true,
  }));
}

async function dbListSuppliers() {
  const { data, error } = await sb.rpc("list_suppliers");
  if (error) throw error;
  return (data || []).map(r => ({
    id: r.id, name: r.name, taxId: r.tax_id || "", taxIdType: r.tax_id_type,
    contact: r.contact_name || "", phone: r.phone || "",
    email: r.email || "", city: r.city || "", status: r.status,
    // Auditoria
    created_at: r.created_at || null, updated_at: r.updated_at || null,
    created_by: r.created_by || null, created_by_name: r.created_by_name || null,
    updated_by: r.updated_by || null, updated_by_name: r.updated_by_name || null,
  }));
}

async function dbUpsertProduct(p) {
  const { data, error } = await sb.rpc("upsert_product", {
    p_id:            p.id || null,
    p_code:          p.code,
    p_name:          p.name,
    p_category_id:   p.category_id,
    p_supplier_id:   p.supplier_id,
    p_cost:          p.cost ?? 0,
    p_price:         p.price ?? 0,
    p_current_stock: p.current_stock ?? 0,
    p_min_stock:     p.min_stock ?? 0,
    p_user_id:       p.user_id || null,   // quem está fazendo a alteração
  });
  if (error) throw error;
  return data; // uuid do produto salvo
}

async function dbUpsertCategory(c) {
  const { data, error } = await sb.rpc("upsert_category", {
    p_id:        c.id || null,
    p_name:      c.name,
    p_icon:      c.icon  || "tag",
    p_color:     c.color || "#F94C99",
    p_image_url: c.image_url ?? null,   // null = remove a foto; string = grava a URL
  });
  if (error) throw error;
  return data; // uuid da categoria
}

// Upload de foto da categoria. Usa o mesmo bucket 'product-images' (já
// existe e tem policy anon liberada), só com subpasta 'categories/'.
// Retorna a URL pública pra você passar pro dbUpsertCategory({ image_url }).
async function dbUploadCategoryImage(categoryId, file) {
  const ext = (file.name || "").split(".").pop() || "jpg";
  // categoryId pode vir null/undefined quando estamos criando uma categoria
  // nova (ainda sem id). Nesse caso usamos um prefixo temp/.
  const folder = categoryId || `temp-${Date.now()}`;
  const path = `categories/${folder}/${crypto.randomUUID()}.${ext}`;
  const { error: upErr } = await sb.storage
    .from("product-images")
    .upload(path, file, { cacheControl: "3600", upsert: false });
  if (upErr) throw upErr;
  const { data: { publicUrl } } = sb.storage.from("product-images").getPublicUrl(path);
  return publicUrl;
}

// Remove a foto da categoria do Storage. NÃO mexe na tabela (o caller
// faz dbUpsertCategory com image_url=null pra desvincular).
async function dbDeleteCategoryImage(imageUrl) {
  if (!imageUrl) return;
  // URL pública vem como .../object/public/product-images/<path>
  const m = imageUrl.match(/\/product-images\/(.+)$/);
  if (!m) return;
  await sb.storage.from("product-images").remove([m[1]]);
}

async function dbDeleteProduct(id) {
  const { error } = await sb.rpc("delete_product", { p_id: id });
  if (error) throw error;
}

async function dbSetProductActive(id, active) {
  const { error } = await sb.rpc("set_product_active", { p_id: id, p_active: !!active });
  if (error) throw error;
}

async function dbAddStockEntry({ productId, quantity, unitCost, reason, type, userId }) {
  const { data, error } = await sb.rpc("add_stock_entry", {
    p_product_id: productId,
    p_quantity:   quantity,
    p_unit_cost:  unitCost ?? null,
    p_reason:     reason   || null,
    p_type:       type     || "compra",
    p_user_id:    userId   || null,
  });
  if (error) throw error;
  return data; // uuid do movimento criado
}

async function dbAddStockExit({ productId, quantity, reason, type, userId }) {
  const { data, error } = await sb.rpc("add_stock_exit", {
    p_product_id: productId,
    p_quantity:   quantity,
    p_reason:     reason || null,
    p_type:       type   || "perda",
    p_user_id:    userId || null,
  });
  if (error) throw error;
  return data;
}

async function dbListStockMovements({ limit = 200, productId = null } = {}) {
  const { data, error } = await sb.rpc("list_stock_movements", {
    p_limit:      limit,
    p_product_id: productId,
  });
  if (error) throw error;
  return data || [];
}

// ─── Vendas ─────────────────────────────────────────────────────────

async function dbListSales({ limit = 200, origin = "venda" } = {}) {
  // origin: 'venda' (default), 'curso', ou 'all'
  const { data, error } = await sb.rpc("list_sales", { p_limit: limit, p_origin: origin });
  if (error) throw error;
  return data || [];
}

async function dbGetSaleDetails(id) {
  const { data, error } = await sb.rpc("get_sale_details", { p_id: id });
  if (error) throw error;
  return data; // { sale, items, payments }
}

async function dbCancelSale({ id, reason, userId }) {
  const { error } = await sb.rpc("cancel_sale", {
    p_id:      id,
    p_reason:  reason || null,
    p_user_id: userId || null,
  });
  if (error) throw error;
}

async function dbUpdateSaleMetadata({ id, discount, notes }) {
  const { error } = await sb.rpc("update_sale_metadata", {
    p_id:       id,
    p_discount: discount ?? null,
    p_notes:    notes    ?? null,
  });
  if (error) throw error;
}

async function dbDeleteSaleItem({ itemId, userId }) {
  const { error } = await sb.rpc("delete_sale_item", {
    p_item_id: itemId,
    p_user_id: userId || null,
  });
  if (error) throw error;
}

async function dbAddSaleItem({ saleId, productId, quantity, unitPrice, userId }) {
  const { data, error } = await sb.rpc("add_sale_item", {
    p_sale_id:    saleId,
    p_product_id: productId,
    p_quantity:   quantity,
    p_unit_price: unitPrice,
    p_user_id:    userId || null,
  });
  if (error) throw error;
  return data; // uuid do novo sale_item
}

async function dbReplaceSalePayments({ saleId, payments }) {
  // payments: [{ method, amount, installments?, change_given? }]
  const { error } = await sb.rpc("replace_sale_payments", {
    p_sale_id:  saleId,
    p_payments: payments || [],
  });
  if (error) throw error;
}

// Finaliza uma venda do PDV em uma transação atômica.
//   items:    [{ product_id, quantity, unit_price }]
//   payments: [{ method, amount, installments?, change_given? }]
// Retorna { id, sale_number, total } da venda criada.
async function dbRegisterSale({ sessionId, cashierId, items, payments, discount, notes, customerId, deliveryFee }) {
  const { data, error } = await sb.rpc("register_sale", {
    p_session_id:   sessionId,
    p_cashier_id:   cashierId,
    p_items:        items    || [],
    p_payments:     payments || [],
    p_discount:     discount ?? 0,
    p_notes:        notes    ?? null,
    p_customer_id:  customerId  ?? null,
    p_delivery_fee: deliveryFee ?? 0,
  });
  if (error) throw error;
  return data && data.length > 0 ? data[0] : null;
}

// ─── Configurações (taxas de maquininha, parcelamento) ─────────────

async function dbGetAppSetting(key) {
  const { data, error } = await sb.rpc("get_app_setting", { p_key: key });
  if (error) throw error;
  return data; // jsonb (objeto/array/null)
}

async function dbSetAppSetting(key, value) {
  const { error } = await sb.rpc("set_app_setting", {
    p_key: key, p_value: value,
  });
  if (error) throw error;
}

// Backup completo do banco — retorna jsonb com todas as tabelas
// (exceto pin_hash dos users). Usado pela tela Configurações.
async function dbBackupDump() {
  const { data, error } = await sb.rpc("backup_dump");
  if (error) throw error;
  return data;
}

// Restaura um backup JSON gerado por dbBackupDump. AÇÃO DESTRUTIVA:
// apaga todos os dados atuais e reinsere os do arquivo. Retorna um
// objeto com contagem de linhas por tabela.
async function dbBackupRestore(payload) {
  const { data, error } = await sb.rpc("restore_backup", { p_data: payload });
  if (error) throw error;
  return data;
}

// ─── Cupons de desconto (gerenciados no PDV, aplicados no site) ─────

// Lista todos os cupons cadastrados. p_status: 'active'|'inactive'|'all'.
// Retorna usage_count e flags is_expired/is_exhausted pré-calculadas.
async function dbListCouponsAdmin(status = "all") {
  const { data, error } = await sb.rpc("list_coupons_admin", { p_status: status });
  if (error) throw error;
  return data || [];
}

// Cria ou atualiza um cupom. id null = insert. Campos opcionais podem
// vir null. discountType: 'percent' | 'fixed'.
async function dbUpsertCoupon({
  id                 = null,
  code,
  description        = null,
  discountType       = "percent",
  discountValue      = 0,
  validFrom          = null,
  validUntil         = null,
  categoryIds        = [],
  minSubtotal        = null,
  maxUses            = null,
  maxUsesPerUser     = null,
  firstPurchaseOnly  = false,
  isActive           = true,
  notes              = null,
  userId             = null,
}) {
  const { data, error } = await sb.rpc("upsert_coupon", {
    p_id:                  id,
    p_code:                code,
    p_description:         description,
    p_discount_type:       discountType,
    p_discount_value:      discountValue,
    p_valid_from:          validFrom,
    p_valid_until:         validUntil,
    p_category_ids:        categoryIds,
    p_min_subtotal:        minSubtotal,
    p_max_uses:            maxUses,
    p_max_uses_per_user:   maxUsesPerUser,
    p_first_purchase_only: firstPurchaseOnly,
    p_is_active:           isActive,
    p_notes:               notes,
    p_user_id:             userId,
  });
  if (error) throw error;
  return data; // uuid
}

async function dbSetCouponActive(id, active) {
  const { error } = await sb.rpc("set_coupon_active", { p_id: id, p_active: active });
  if (error) throw error;
}

async function dbDeleteCoupon(id) {
  const { error } = await sb.rpc("delete_coupon", { p_id: id });
  if (error) throw error;
}

// ─── E-commerce (Vendas Online) ──────────────────────────────────────

// Listagem PÚBLICA — só produtos is_online=true. Usada pelo site Next.js
// e também pelo painel admin pra visualizar o que está exposto.
async function dbListShopProducts(categoryId = null) {
  const { data, error } = await sb.rpc("list_shop_products", { p_category_id: categoryId });
  if (error) throw error;
  return data || [];
}

async function dbGetShopProduct(id) {
  const { data, error } = await sb.rpc("get_shop_product", { p_id: id });
  if (error) throw error;
  return data;
}

// Registra venda online (status inicial = aguardando_pagamento).
// Já decrementa estoque via trigger fn_sale_item_stock.
async function dbRegisterOnlineSale({
  customerId, items, shippingAddress, shippingMethod,
  deliveryFee = 0, paymentGatewayRef = null,
}) {
  const { data, error } = await sb.rpc("register_online_sale", {
    p_customer_id:         customerId,
    p_items:               items,
    p_shipping_address:    shippingAddress,
    p_shipping_method:     shippingMethod,
    p_delivery_fee:        deliveryFee,
    p_payment_gateway_ref: paymentGatewayRef,
  });
  if (error) throw error;
  return data?.[0] || null;
}

// Confirma pagamento (chamado pelo webhook do MP via Edge Function).
async function dbConfirmOnlinePayment({ saleId, paymentMethod, amount, gatewayRef }) {
  const { error } = await sb.rpc("confirm_online_payment", {
    p_sale_id:        saleId,
    p_payment_method: paymentMethod,
    p_amount:         amount,
    p_gateway_ref:    gatewayRef,
  });
  if (error) throw error;
}

// Avança ciclo (em_separacao → enviado → entregue) ou cancela.
async function dbUpdateOnlineStatus({ saleId, newStatus, trackingCode = null, userId = null }) {
  const { error } = await sb.rpc("update_online_status", {
    p_sale_id:       saleId,
    p_new_status:    newStatus,
    p_tracking_code: trackingCode,
    p_user_id:       userId,
  });
  if (error) throw error;
}

// Lista vendas online pro painel admin (filtra opcionalmente por status).
// Omite p_status quando null pra ajudar o PostgREST a resolver a função
// sem precisar inferir o tipo do enum `online_status` a partir de NULL.
async function dbListOnlineSales({ status = null, limit = 200 } = {}) {
  const args = { p_limit: limit };
  if (status) args.p_status = status;
  const { data, error } = await sb.rpc("list_online_sales", args);
  if (error) throw error;
  return data || [];
}

// ─── Storage: imagens de produto ──────────────────────────────────────
// Bucket 'product-images' (público pra leitura, autenticado pra escrita).

// Faz upload de uma imagem e retorna a URL pública.
// `file` é um File ou Blob (vindo de <input type=file>).
async function dbUploadProductImage(productId, file) {
  const ext = (file.name || "").split(".").pop() || "jpg";
  const path = `${productId}/${crypto.randomUUID()}.${ext}`;
  const { error: upErr } = await sb.storage
    .from("product-images")
    .upload(path, file, { cacheControl: "3600", upsert: false });
  if (upErr) throw upErr;
  const { data: { publicUrl } } = sb.storage.from("product-images").getPublicUrl(path);

  // Persiste o registro em product_images com position no fim da lista
  const { data: existing } = await sb
    .from("product_images")
    .select("position")
    .eq("product_id", productId)
    .order("position", { ascending: false })
    .limit(1);
  const nextPos = (existing?.[0]?.position ?? -1) + 1;

  const { data, error } = await sb.from("product_images")
    .insert({ product_id: productId, url: publicUrl, position: nextPos })
    .select()
    .single();
  if (error) throw error;
  return data;
}

async function dbListProductImages(productId) {
  const { data, error } = await sb
    .from("product_images")
    .select("*")
    .eq("product_id", productId)
    .order("position", { ascending: true });
  if (error) throw error;
  return data || [];
}

async function dbDeleteProductImage(imageId) {
  // Pega a URL pra remover do storage também
  const { data: img } = await sb.from("product_images").select("url").eq("id", imageId).single();
  if (img?.url) {
    // URL do supabase storage: .../object/public/product-images/<path>
    const m = img.url.match(/\/product-images\/(.+)$/);
    if (m) {
      await sb.storage.from("product-images").remove([m[1]]);
    }
  }
  const { error } = await sb.from("product_images").delete().eq("id", imageId);
  if (error) throw error;
}

// ─── Dashboard ──────────────────────────────────────────────────────

// Devolve em uma chamada só:
//   period_total/count/avg_ticket  → KPIs do período (statsFrom/statsTo)
//   total_stock_units, revenue_series, top_products, low_stock, payment_mix
//
// Parâmetros (todos opcionais):
//   revenueDays  → tamanho da série do gráfico (default 14)
//   statsFrom    → ISO date 'YYYY-MM-DD' (default = hoje)
//   statsTo      → ISO date 'YYYY-MM-DD' (default = hoje)
//   paymixDays   → janela do mix de pagamentos em dias (default 30)
async function dbDashboardSummary({
  revenueDays = 14,
  statsFrom   = null,
  statsTo     = null,
  paymixDays  = 30,
} = {}) {
  const { data, error } = await sb.rpc("dashboard_summary", {
    p_revenue_days: revenueDays,
    p_stats_from:   statsFrom,
    p_stats_to:     statsTo,
    p_paymix_days:  paymixDays,
  });
  if (error) throw error;
  return data;
}

// ─── Relatórios ─────────────────────────────────────────────────────
// Todas as RPCs aceitam from/to em ISO (YYYY-MM-DD).

async function dbReportSummary(from, to) {
  const { data, error } = await sb.rpc("report_sales_summary", { p_from: from, p_to: to });
  if (error) throw error;
  return data?.[0] || null;
}

async function dbReportRevenueByDay(from, to) {
  const { data, error } = await sb.rpc("report_revenue_by_day", { p_from: from, p_to: to });
  if (error) throw error;
  return data || [];
}

async function dbReportPeakHours(from, to) {
  const { data, error } = await sb.rpc("report_peak_hours", { p_from: from, p_to: to });
  if (error) throw error;
  return data || [];
}

async function dbReportHourlySummary(from, to) {
  const { data, error } = await sb.rpc("report_hourly_summary", { p_from: from, p_to: to });
  if (error) throw error;
  return data || [];
}

async function dbReportTopProducts(from, to, limit = 50) {
  const { data, error } = await sb.rpc("report_top_products",
    { p_from: from, p_to: to, p_limit: limit });
  if (error) throw error;
  return data || [];
}

async function dbReportSalesBySupplier(from, to, supplierId = null) {
  const { data, error } = await sb.rpc("report_sales_by_supplier",
    { p_from: from, p_to: to, p_supplier_id: supplierId });
  if (error) throw error;
  return data || [];
}

async function dbReportSuppliersSummary(from, to) {
  const { data, error } = await sb.rpc("report_suppliers_summary", { p_from: from, p_to: to });
  if (error) throw error;
  return data || [];
}

async function dbReportStock({ supplierId = null, from = null, to = null } = {}) {
  const { data, error } = await sb.rpc("report_stock",
    { p_supplier_id: supplierId, p_from: from, p_to: to });
  if (error) throw error;
  return data || [];
}

async function dbReportPaymentsByMethod(from, to) {
  const { data, error } = await sb.rpc("report_payments_by_method", { p_from: from, p_to: to });
  if (error) throw error;
  return data || [];
}

// ─── Caixa ──────────────────────────────────────────────────────────

async function dbListCashSessions({ limit = 100 } = {}) {
  const { data, error } = await sb.rpc("list_cash_sessions", { p_limit: limit });
  if (error) throw error;
  return data || [];
}

async function dbCloseCashSession({ id, countedAmount, userId, notes }) {
  const { error } = await sb.rpc("close_cash_session", {
    p_id:             id,
    p_counted_amount: countedAmount ?? 0,
    p_user_id:        userId || null,
    p_notes:          notes || null,
  });
  if (error) throw error;
}

async function dbReopenCashSession({ id, userId, reason }) {
  const { error } = await sb.rpc("reopen_cash_session", {
    p_id:      id,
    p_user_id: userId || null,
    p_reason:  reason || null,
  });
  if (error) throw error;
}

async function dbListCashSessionAudit(sessionId) {
  const { data, error } = await sb.rpc("list_cash_session_audit", { p_session_id: sessionId });
  if (error) throw error;
  return data || [];
}

async function dbAddCashMovement({ sessionId, type, amount, reason, userId }) {
  const { data, error } = await sb.rpc("add_cash_movement", {
    p_session_id: sessionId,
    p_type:       type || "sangria",
    p_amount:     amount,
    p_reason:     reason || null,
    p_user_id:    userId || null,
  });
  if (error) throw error;
  return data;
}

async function dbListCashMovements({ sessionId }) {
  const { data, error } = await sb.rpc("list_cash_movements", {
    p_session_id: sessionId,
  });
  if (error) throw error;
  return data || [];
}

async function dbListSessionSales({ sessionId }) {
  const { data, error } = await sb.rpc("list_session_sales", {
    p_session_id: sessionId,
  });
  if (error) throw error;
  return data || [];
}

async function dbUpsertSupplier(s) {
  const { data, error } = await sb.rpc("upsert_supplier", {
    p_id:           s.id || null,
    p_name:         s.name,
    p_tax_id:       s.taxId || null,
    p_tax_id_type:  s.taxIdType || "pj",
    p_contact_name: s.contact || null,
    p_phone:        s.phone   || null,
    p_email:        s.email   || null,
    p_city:         s.city    || null,
    p_user_id:      s.user_id || null,   // auditoria
  });
  if (error) throw error;
  return data; // uuid do fornecedor
}

async function dbDeleteSupplier(id) {
  const { error } = await sb.rpc("delete_supplier", { p_id: id });
  if (error) throw error;
}

async function dbSetSupplierStatus(id, status) {
  const { error } = await sb.rpc("set_supplier_status", {
    p_id: id, p_status: status,
  });
  if (error) throw error;
}

// ─── Clientes ───────────────────────────────────────────────────────

async function dbListCustomers() {
  const { data, error } = await sb.rpc("list_customers");
  if (error) throw error;
  return (data || []).map(r => ({
    id: r.id, name: r.name, taxId: r.tax_id || "", taxIdType: r.tax_id_type,
    phone: r.phone || "", email: r.email || "",
    zip:           r.zip           || "",
    street:        r.street        || "",
    streetNumber:  r.street_number || "",
    complement:    r.complement    || "",
    district:      r.district      || "",
    city:          r.city          || "",
    state:         r.state         || "",
    status: r.status,
    // Auditoria
    created_at: r.created_at || null, updated_at: r.updated_at || null,
    created_by: r.created_by || null, created_by_name: r.created_by_name || null,
    updated_by: r.updated_by || null, updated_by_name: r.updated_by_name || null,
  }));
}

async function dbUpsertCustomer(c) {
  const { data, error } = await sb.rpc("upsert_customer", {
    p_id:            c.id || null,
    p_name:          c.name,
    p_tax_id:        c.taxId || null,
    p_tax_id_type:   c.taxIdType || "pf",
    p_phone:         c.phone || null,
    p_email:         c.email || null,
    p_zip:           c.zip           || null,
    p_street:        c.street        || null,
    p_street_number: c.streetNumber  || null,
    p_complement:    c.complement    || null,
    p_district:      c.district      || null,
    p_city:          c.city          || null,
    p_state:         c.state         || null,
    p_user_id:       c.user_id       || null,   // auditoria
  });
  if (error) throw error;
  return data;
}

async function dbDeleteCustomer(id) {
  const { error } = await sb.rpc("delete_customer", { p_id: id });
  if (error) throw error;
}

async function dbSetCustomerStatus(id, status) {
  const { error } = await sb.rpc("set_customer_status", {
    p_id: id, p_status: status,
  });
  if (error) throw error;
}

// Histórico de compras de um cliente (PDV + Online, concluídas + canceladas).
// Mesma estrutura de dbListSales pra reuso de SaleDetailModal.
async function dbListCustomerSales(customerId) {
  const { data, error } = await sb.rpc("list_customer_sales", {
    p_customer_id: customerId,
  });
  if (error) throw error;
  return (data || []).map(r => ({
    id:               r.id,
    sale_number:      r.sale_number,
    sold_at:          r.sold_at,
    status:           r.status,
    channel:          r.channel || "pdv",
    online_status:    r.online_status || null,
    subtotal:         Number(r.subtotal)     || 0,
    discount:         Number(r.discount)     || 0,
    delivery_fee:     Number(r.delivery_fee) || 0,
    total:            Number(r.total)        || 0,
    items_count:      Number(r.items_count)  || 0,
    units_total:      Number(r.units_total)  || 0,
    products:         r.products       || [],
    payment_methods:  r.payment_methods || [],
  }));
}

// ─── Auditoria universal ─────────────────────────────────────────────
// Retorna timeline de alterações de qualquer entidade (products, suppliers,
// customers, sales) usando audit_log.
// p_entity: nome da tabela ('products' | 'suppliers' | 'customers' | 'sales')
async function dbGetEntityAudit(entity, entityId) {
  const { data, error } = await sb.rpc("get_entity_audit", {
    p_entity:    entity,
    p_entity_id: entityId,
  });
  if (error) throw error;
  return (data || []).map(r => ({
    id:            r.id,
    created_at:    r.created_at,
    actor_id:      r.actor_id,
    actor_name:    r.actor_name || "Sistema",
    action:        r.action,
    before_data:   r.before_data,
    after_data:    r.after_data,
    justification: r.justification,
    changes:       r.changes || {},   // { field: { old, new }, ... }
  }));
}

// ─── Diagnóstico ─────────────────────────────────────────────────────

async function dbPing() {
  console.log("[Supabase] testando conexão com", SUPABASE_URL);
  try {
    const users = await dbListLoginUsers();
    console.log("[Supabase] OK — " + users.length + " usuários ativos:", users);
    return { ok: true, users };
  } catch (e) {
    console.error("[Supabase] FALHA:", e.message || e);
    if (e.message && e.message.includes("Could not find")) {
      console.warn("[Supabase] dica: a função list_login_users() ainda não foi criada. Rode estrutura_completa.sql no SQL Editor.");
    }
    return { ok: false, error: e };
  }
}

/* ─── Módulo Cursos ────────────────────────────────────────────────
   CRUD de cursos + matrículas + pagamento de mensalidade + relatórios.
   Pagamento de mensalidade gera 1 sale com origin='curso' (sem items)
   pra entrar no caixa mas ficar separada nos relatórios.
*/
async function dbListCourses(status = "ativo") {
  const { data, error } = await sb.rpc("list_courses", { p_status: status });
  if (error) throw error;
  return data || [];
}

async function dbCreateCourse(c) {
  const { data, error } = await sb.rpc("create_course", {
    p_name:             c.name,
    p_monthly_price:    c.monthly_price ?? 0,
    p_weekdays:         c.weekdays ?? [],
    p_start_time:       c.start_time ?? null,
    p_end_time:         c.end_time ?? null,
    p_teacher_id:       c.teacher_id ?? null,
    p_commission_mode:  c.commission_mode ?? "pct",
    p_commission_value: c.commission_value ?? 0,
    p_max_students:     c.max_students ?? null,
    p_description:      c.description ?? null,
    p_notes:            c.notes ?? null,
  });
  if (error) throw error;
  return data;  // uuid
}

async function dbUpdateCourse(c) {
  const { error } = await sb.rpc("update_course", {
    p_id:               c.id,
    p_name:             c.name,
    p_monthly_price:    c.monthly_price ?? 0,
    p_weekdays:         c.weekdays ?? [],
    p_start_time:       c.start_time ?? null,
    p_end_time:         c.end_time ?? null,
    p_teacher_id:       c.teacher_id ?? null,
    p_commission_mode:  c.commission_mode ?? "pct",
    p_commission_value: c.commission_value ?? 0,
    p_max_students:     c.max_students ?? null,
    p_status:           c.status ?? "ativo",
    p_description:      c.description ?? null,
    p_notes:            c.notes ?? null,
  });
  if (error) throw error;
}

async function dbListEnrollments({ courseId = null, status = "ativo" } = {}) {
  const { data, error } = await sb.rpc("list_enrollments", {
    p_course_id: courseId,
    p_status:    status,
  });
  if (error) throw error;
  return data || [];
}

async function dbCreateEnrollment({ courseId, customerId, monthlyValue = null, notes = null }) {
  const { data, error } = await sb.rpc("create_enrollment", {
    p_course_id:     courseId,
    p_customer_id:   customerId,
    p_monthly_value: monthlyValue,
    p_enrolled_at:   null,
    p_notes:         notes,
  });
  if (error) throw error;
  return data;
}

async function dbUpdateEnrollment({ id, monthlyValue = null, status = null, notes = null }) {
  const { error } = await sb.rpc("update_enrollment", {
    p_id:            id,
    p_monthly_value: monthlyValue,
    p_status:        status,
    p_notes:         notes,
  });
  if (error) throw error;
}

async function dbListMonthlyStatus(yearMonth) {
  // yearMonth: 'YYYY-MM-01' (ou qualquer date dentro do mês — função normaliza)
  const { data, error } = await sb.rpc("list_monthly_status", { p_year_month: yearMonth });
  if (error) throw error;
  return data || [];
}

async function dbRegisterCoursePayment({ sessionId, cashierId, enrollmentId, referenceMonth, payments }) {
  const { data, error } = await sb.rpc("register_course_payment", {
    p_session_id:      sessionId,
    p_cashier_id:      cashierId,
    p_enrollment_id:   enrollmentId,
    p_reference_month: referenceMonth,
    p_payments:        payments || [],
  });
  if (error) throw error;
  return Array.isArray(data) ? data[0] : data;  // { id, sale_number, total }
}

async function dbReportCoursesSummary(from, to) {
  const { data, error } = await sb.rpc("report_courses_summary", { p_from: from, p_to: to });
  if (error) throw error;
  return (Array.isArray(data) ? data[0] : data) || {};
}

async function dbReportCoursesTop(from, to, metric = "revenue") {
  const { data, error } = await sb.rpc("report_courses_top", {
    p_from: from, p_to: to, p_metric: metric,
  });
  if (error) throw error;
  return data || [];
}

async function dbReportTeacherCommissions(from, to) {
  const { data, error } = await sb.rpc("report_teacher_commissions", { p_from: from, p_to: to });
  if (error) throw error;
  return data || [];
}

Object.assign(window, {
  sb,
  dbListLoginUsers, dbVerifyPin,
  dbListUsersAdmin, dbUpsertUserAdmin, dbDeactivateUserAdmin,
  dbGetActiveCashSession, dbOpenCashSession,
  dbListProducts, dbListCategories, dbListSuppliers, dbListCustomers,
  dbListCategoriesAdmin, dbDeleteCategory, dbSetCategoryActive, dbListProductsByCategory,
  dbUploadCategoryImage, dbDeleteCategoryImage,
  dbUpsertProduct, dbUpsertCategory, dbUpsertSupplier, dbUpsertCustomer,
  dbDeleteProduct, dbSetProductActive,
  dbDeleteSupplier, dbSetSupplierStatus,
  dbDeleteCustomer, dbSetCustomerStatus, dbListCustomerSales,
  dbGetEntityAudit,
  dbAddStockEntry, dbAddStockExit, dbListStockMovements,
  dbListSales, dbGetSaleDetails, dbCancelSale, dbUpdateSaleMetadata,
  dbDeleteSaleItem, dbAddSaleItem, dbReplaceSalePayments, dbRegisterSale,
  dbListCashSessions, dbCloseCashSession, dbReopenCashSession,
  dbListCashSessionAudit,
  dbAddCashMovement, dbListCashMovements,
  dbListSessionSales,
  dbGetAppSetting, dbSetAppSetting, dbBackupDump, dbBackupRestore,
  dbListCouponsAdmin, dbUpsertCoupon, dbSetCouponActive, dbDeleteCoupon,
  dbListShopProducts, dbGetShopProduct, dbRegisterOnlineSale,
  dbConfirmOnlinePayment, dbUpdateOnlineStatus, dbListOnlineSales,
  dbUploadProductImage, dbListProductImages, dbDeleteProductImage,
  dbUpdateProductEcommerce,
  dbDashboardSummary,
  dbReportSummary, dbReportRevenueByDay, dbReportPeakHours, dbReportHourlySummary, dbReportTopProducts,
  dbReportSalesBySupplier, dbReportSuppliersSummary, dbReportStock, dbReportPaymentsByMethod,
  dbListCourses, dbCreateCourse, dbUpdateCourse,
  dbListEnrollments, dbCreateEnrollment, dbUpdateEnrollment,
  dbListMonthlyStatus, dbRegisterCoursePayment,
  dbReportCoursesSummary, dbReportCoursesTop, dbReportTeacherCommissions,
  dbPing,
});
console.log("[Supabase] cliente inicializado · projeto:", SUPABASE_URL.split("//")[1].split(".")[0]);