/* ============================================================
   LF CONCEITO — Supabase Data Layer
   Substitui o localStorage por dados reais
   ============================================================ */

const sb = () => window._sb;

/* ---------- PRODUTOS ---------- */
async function fetchProducts() {
  const { data, error } = await sb()
    .from("products")
    .select("*")
    .eq("active", true)
    .order("featured", { ascending: false });
  if (error) { console.error("fetchProducts:", error); return []; }
  return data.map(dbToProduct);
}

async function fetchAllProducts() {
  const { data, error } = await sb()
    .from("products")
    .select("*")
    .order("featured", { ascending: false });
  if (error) { console.error("fetchAllProducts:", error); return []; }
  return data.map(dbToProduct);
}

async function fetchProductById(id) {
  const { data, error } = await sb()
    .from("products")
    .select("*")
    .eq("id", id)
    .single();
  if (error) return null;
  const product = dbToProduct(data);
  product.variants = await fetchVariants(id);
  return product;
}

/* ---------- VARIANTES (estoque por cor + tamanho) ---------- */
async function fetchVariants(productId) {
  const { data, error } = await sb()
    .from("product_variants")
    .select("*")
    .eq("product_id", productId);
  if (error) { console.error("fetchVariants:", error); return []; }
  return data.map(v => ({ color: v.color, size: v.size, stock: v.stock }));
}

// Retorna o total de estoque (soma das variantes) por produto, para a lista de produtos do admin
async function fetchVariantTotals() {
  const { data, error } = await sb().from("product_variants").select("product_id, stock");
  if (error) { console.error("fetchVariantTotals:", error); return {}; }
  const totals = {};
  data.forEach(v => { totals[v.product_id] = (totals[v.product_id]||0) + v.stock; });
  return totals;
}

// Salva a grade completa de variantes de um produto (substitui tudo que existia antes)
async function saveVariants(productId, variants) {
  // Remove variantes antigas e insere as novas, mantendo simples e consistente
  const { error: delErr } = await sb().from("product_variants").delete().eq("product_id", productId);
  if (delErr) throw delErr;
  if (variants.length === 0) return;
  const rows = variants.map(v => ({ product_id: productId, color: v.color, size: v.size, stock: parseInt(v.stock)||0 }));
  const { error } = await sb().from("product_variants").insert(rows);
  if (error) throw error;
}

// Abate 1 unidade da combinacao cor+tamanho especifica (chamado ao confirmar pedido)
async function decrementVariantStock(productId, color, size, qty) {
  const { data } = await sb()
    .from("product_variants")
    .select("id, stock")
    .eq("product_id", productId).eq("color", color).eq("size", size)
    .maybeSingle();
  if (!data) return; // produto sem variantes cadastradas, nada a debitar
  const newStock = Math.max(0, data.stock - qty);
  await sb().from("product_variants").update({ stock: newStock }).eq("id", data.id);
}

async function saveProduct(p) {
  const row = productToDb(p);
  const { data, error } = await sb()
    .from("products")
    .upsert(row, { onConflict: "id" })
    .select()
    .single();
  if (error) throw error;
  return dbToProduct(data);
}

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

async function updateStock(id, stock) {
  const { error } = await sb()
    .from("products")
    .update({ stock, updated_at: new Date().toISOString() })
    .eq("id", id);
  if (error) throw error;
}

// Normaliza o campo images: aceita formato antigo (array de strings) e novo ({url,color}); garante array nao vazio com fallback no image_url
function normalizeImages(images, fallbackUrl) {
  if (images && images.length > 0) {
    return images.map(it => typeof it === "string" ? { url: it, color: "" } : it);
  }
  return fallbackUrl ? [{ url: fallbackUrl, color: "" }] : [];
}

function dbToProduct(r) {
  return {
    id: r.id, name: r.name, cat: r.cat, sub: r.sub,
    price: parseFloat(r.price),
    compareAt: parseFloat(r.compare_at) || 0,
    sizes: r.sizes || [], colors: r.colors || [],
    stock: r.stock || 0, sku: r.sku || "",
    tone: r.tone || "#888", kind: r.kind || "product",
    tag: r.tag || "", featured: r.featured || false,
    active: r.active !== false, brandline: r.brandline || "",
    image_url: r.image_url || "",
    images: normalizeImages(r.images, r.image_url),
    description: r.description || "",
    composition: r.composition || "",
    rating: r.rating != null ? parseFloat(r.rating) : null,
    reviewsCount: r.reviews_count != null ? parseInt(r.reviews_count) : null,
  };
}

function productToDb(p) {
  return {
    id: p.id || ("prod-" + Date.now()),
    name: p.name, cat: p.cat, sub: p.sub || "",
    price: p.price, compare_at: p.compareAt || 0,
    sizes: p.sizes || [], colors: p.colors || [],
    stock: p.stock || 0, sku: p.sku || "",
    tone: p.tone || "#888", kind: p.kind || "product",
    tag: p.tag || "", featured: p.featured || false,
    active: p.active !== false, brandline: p.brandline || "",
    images: (p.images || []).map(it => typeof it === "string" ? { url: it, color: "" } : it),
    image_url: (p.images && p.images[0] && (p.images[0].url || p.images[0])) || p.image_url || "",
    description: p.description || "",
    composition: p.composition || "",
    rating: (p.rating !== "" && p.rating != null) ? parseFloat(p.rating) : null,
    reviews_count: (p.reviewsCount !== "" && p.reviewsCount != null) ? parseInt(p.reviewsCount) : null,
    updated_at: new Date().toISOString(),
  };
}

/* ---------- CATEGORIAS ---------- */
async function fetchCategories() {
  const { data, error } = await sb()
    .from("categories")
    .select("*")
    .order("sort_order");
  if (error) return [];
  return data.map(r => ({ slug: r.slug, name: r.name, subs: r.subs || [] }));
}

/* ---------- PEDIDOS ---------- */
async function fetchOrderStatus(code) {
  const { data } = await sb()
    .from("orders")
    .select("status")
    .eq("code", code)
    .single();
  return data?.status || null;
}

async function fetchOrders(opts = {}) {
  const { limit = 100, status = null } = opts;
  let q = sb()
    .from("orders")
    .select("*, order_items(*)")
    .order("created_at", { ascending: false })
    .limit(limit);
  if (status) q = q.eq("status", status);
  const { data, error } = await q;
  if (error) { console.error("fetchOrders:", error); return []; }
  await hydrateOrderImages(data);
  return data.map(dbToOrder);
}

async function saveOrder(order) {
  // 1. Upsert customer
  let customerId = null;
  if (order.customer_email) {
    const { data: cust } = await sb()
      .from("customers")
      .upsert({ name: order.customer, email: order.customer_email,
                 phone: order.customer_phone, city: order.city },
               { onConflict: "email" })
      .select("id").single();
    if (cust) customerId = cust.id;
  }

  // 2. Inserir pedido
  const code = "LF" + Date.now().toString().slice(-6);
  const { data: ord, error } = await sb()
    .from("orders")
    .insert({
      code, customer_id: customerId,
      customer_name: order.customer, customer_phone: order.customer_phone || "", city: order.city,
      status: "Pendente", payment: order.payment,
      subtotal: order.subtotal, freight: order.freight,
      discount: order.discount || 0, total: order.total,
    })
    .select().single();
  if (error) throw error;

  // 3. Inserir itens
  if (order.items && order.items.length > 0) {
    const items = order.items.map(i => ({
      order_id: ord.id, product_id: i.id, name: i.name,
      price: i.price, qty: i.qty, size: i.size,
      color: i.color || "", tone: i.tone, kind: i.kind,
    }));
    await sb().from("order_items").insert(items);

    // 4. Decrementar estoque geral do produto (sistema antigo, mantido por compatibilidade)
    for (const i of order.items) {
      await sb().rpc("decrement_stock", { p_id: i.id, p_qty: i.qty })
        .catch(() => {}); // ignora se RPC não existir
    }

    // 5. Decrementar estoque da variante especifica cor+tamanho (se o produto usar esse controle)
    for (const i of order.items) {
      if (i.color && i.size) {
        await decrementVariantStock(i.id, i.color, i.size, i.qty).catch(() => {});
      }
    }
  }

  return dbToOrder(ord);
}

async function updateOrderStatus(id, status) {
  const { error } = await sb()
    .from("orders")
    .update({ status, updated_at: new Date().toISOString() })
    .eq("id", id);
  if (error) throw error;
}

async function cancelOrder(orderIdOrCode) {
  // Aceita tanto UUID quanto código (ex: "LF553504")
  let orderId = orderIdOrCode;
  if (typeof orderIdOrCode === "string" && orderIdOrCode.startsWith("LF")) {
    const { data: ord } = await sb().from("orders").select("id").eq("code", orderIdOrCode).single();
    if (!ord) return;
    orderId = ord.id;
  }

  // 1. Buscar itens do pedido
  const { data: items } = await sb()
    .from("order_items")
    .select("product_id, qty, color, size")
    .eq("order_id", orderId);

  // 2. Devolver estoque de cada item
  for (const item of (items || [])) {
    const sizeKey = (!item.size || item.size === "Único") ? "_" : item.size;

    // Devolver estoque da variante
    if (item.color) {
      const { data: v } = await sb().from("product_variants")
        .select("id, stock")
        .eq("product_id", item.product_id)
        .eq("color", item.color)
        .eq("size", sizeKey)
        .maybeSingle();
      if (v) {
        await sb().from("product_variants")
          .update({ stock: v.stock + item.qty })
          .eq("id", v.id);
      }
    }

    // Sincroniza products.stock = soma das variantes (após devolver variante)
    const { data: variants } = await sb().from("product_variants")
      .select("stock").eq("product_id", item.product_id);
    if (variants && variants.length > 0) {
      const totalStock = variants.reduce((sum, v) => sum + (v.stock || 0), 0);
      await sb().from("products").update({ stock: totalStock }).eq("id", item.product_id);
    } else {
      const { data: prod } = await sb().from("products").select("stock").eq("id", item.product_id).single();
      if (prod) await sb().from("products").update({ stock: prod.stock + item.qty }).eq("id", item.product_id);
    }
  }

  // 3. Atualizar status para Cancelado usando UUID
  await updateOrderStatus(orderId, "Cancelado");

  // 4. Recarregar produtos no front
  if (window.refreshProducts) window.refreshProducts();
}

async function deleteOrder(id) {
  // 1. Buscar status e itens do pedido
  const { data: order } = await sb().from("orders").select("status").eq("id", id).single();
  const { data: items } = await sb()
    .from("order_items")
    .select("product_id, qty, color, size")
    .eq("order_id", id);

  // 2. Devolver estoque SOMENTE se o pedido NÃO estava cancelado
  // (se já estava cancelado, o estoque já foi devolvido no cancelamento)
  const shouldRestoreStock = order && !["Cancelado"].includes(order.status);

  if (shouldRestoreStock) {
    for (const item of (items || [])) {
      const sizeKey = (!item.size || item.size === "Único") ? "_" : item.size;
      if (item.color) {
        const { data: v } = await sb().from("product_variants")
          .select("id, stock").eq("product_id", item.product_id)
          .eq("color", item.color).eq("size", sizeKey).maybeSingle();
        if (v) await sb().from("product_variants").update({ stock: v.stock + item.qty }).eq("id", v.id);
      }
      const { data: variants } = await sb().from("product_variants")
        .select("stock").eq("product_id", item.product_id);
      if (variants && variants.length > 0) {
        const totalStock = variants.reduce((sum, v) => sum + (v.stock || 0), 0);
        await sb().from("products").update({ stock: totalStock }).eq("id", item.product_id);
      } else {
        const { data: prod } = await sb().from("products").select("stock").eq("id", item.product_id).single();
        if (prod) await sb().from("products").update({ stock: prod.stock + item.qty }).eq("id", item.product_id);
      }
    }
  }

  // 3. Excluir itens e pedido
  await sb().from("order_items").delete().eq("order_id", id);
  const { error } = await sb().from("orders").delete().eq("id", id);
  if (error) throw error;

  if (window.refreshProducts) window.refreshProducts();
}

async function deleteCustomer(id) {
  const { error } = await sb().from("customers").delete().eq("id", id);
  if (error) throw error;
}

// Preenche image_url dos itens de uma lista de pedidos, buscando do catalogo atual de produtos
async function hydrateOrderImages(orders) {
  const ids = [...new Set(orders.flatMap(o => (o.order_items||[]).map(i => i.product_id)).filter(Boolean))];
  if (ids.length === 0) return orders;
  const { data: prods } = await sb().from("products").select("id, image_url").in("id", ids);
  const imgMap = Object.fromEntries((prods||[]).map(p => [p.id, p.image_url]));
  orders.forEach(o => {
    (o.order_items||[]).forEach(i => { i.image_url = imgMap[i.product_id] || null; });
  });
  return orders;
}

function dbToOrder(r) {
  return {
    id: r.id, code: r.code,
    customer: r.customer_name, phone: r.customer_phone || "", city: r.city,
    address: r.address || "",
    date: r.created_at, status: r.status, payment: r.payment,
    subtotal: parseFloat(r.subtotal),
    freight: parseFloat(r.freight),
    discount: parseFloat(r.discount || 0),
    total: parseFloat(r.total),
    items: (r.order_items || []).map(i => ({
      id: i.product_id, name: i.name,
      price: parseFloat(i.price), qty: i.qty,
      size: i.size, color: i.color, tone: i.tone, kind: i.kind,
      image_url: i.image_url,
    })),
  };
}

/* ---------- CLIENTES ---------- */
async function fetchCustomers() {
  const { data, error } = await sb()
    .from("customers")
    .select("*")
    .order("total_spent", { ascending: false });
  if (error) return [];
  return data.map(r => ({
    id: r.id, name: r.name, email: r.email,
    city: r.city, orders: r.total_orders,
    spent: parseFloat(r.total_spent), last: r.last_order,
  }));
}

/* ---------- CUPONS ---------- */
async function fetchCoupons() {
  const { data, error } = await sb().from("coupons").select("*").order("created_at", { ascending: false });
  if (error) return [];
  return data;
}

async function validateCoupon(code, orderTotal) {
  const { data, error } = await sb()
    .from("coupons")
    .select("*")
    .eq("code", code.toUpperCase())
    .eq("active", true)
    .single();
  if (error || !data) return { valid: false, message: "Cupom inválido" };
  if (data.expires_at && new Date(data.expires_at) < new Date())
    return { valid: false, message: "Cupom expirado" };
  if (data.used >= data.max_uses)
    return { valid: false, message: "Cupom esgotado" };
  if (orderTotal < data.min_order)
    return { valid: false, message: `Pedido mínimo R$${data.min_order.toFixed(2).replace(".",",")}` };
  const discount = data.type === "percent"
    ? orderTotal * data.value / 100
    : data.value;
  return { valid: true, discount, coupon: data };
}

async function saveCoupon(c) {
  const { data, error } = await sb()
    .from("coupons")
    .upsert(c, { onConflict: "id" })
    .select().single();
  if (error) throw error;
  return data;
}

async function deleteCoupon(id) {
  const { data, error } = await sb().from("coupons").delete().eq("id", id).select();
  if (error) throw error;
  if (!data || data.length === 0) {
    throw new Error("Cupom nao foi excluido — verifique as permissoes (RLS) da tabela coupons no Supabase.");
  }
}

/* ---------- CONFIG ---------- */
/* ---------- PERFIL ---------- */
async function fetchProfile(userId) {
  const { data } = await sb()
    .from("profiles")
    .select("*")
    .eq("id", userId)
    .single();
  return data;
}

async function fetchMyOrders(userId) {
  const { data, error } = await sb()
    .from("orders")
    .select("*, order_items(*)")
    .eq("customer_id", userId)
    .order("created_at", { ascending: false })
    .limit(20);
  if(error) return [];
  await hydrateOrderImages(data);
  return data.map(dbToOrder);
}

/* ---------- CONFIG ---------- */
async function fetchConfig() {
  const { data } = await sb().from("store_config").select("*");
  if (!data) return {};
  return Object.fromEntries(data.map(r => [r.key, r.value]));
}

async function saveConfig(key, value) {
  await sb().from("store_config").upsert({ key, value }, { onConflict: "key" });
}

/* ---------- FINANCEIRO ---------- */
// Formata uma Date para "YYYY-MM-DD" usando o horário LOCAL (não UTC, evita bug de fuso)
function localISODate(d) {
  const y = d.getFullYear();
  const m = String(d.getMonth()+1).padStart(2,"0");
  const day = String(d.getDate()).padStart(2,"0");
  return `${y}-${m}-${day}`;
}

async function fetchFinancials(days=7) {
  const { data } = await sb()
    .from("orders")
    .select("total, payment, status, created_at")
    .neq("status", "Cancelado");
  if (!data) return { revenue: 0, byPayment: {}, byDay: [] };

  const revenue = data.reduce((s, o) => s + parseFloat(o.total), 0);
  const byPayment = {};
  data.forEach(o => {
    byPayment[o.payment] = (byPayment[o.payment] || 0) + parseFloat(o.total);
  });

  // Últimos N dias, usando data LOCAL (corrige bug de pedido aparecer no dia seguinte por causa do UTC)
  const dayMap = {};
  const now = new Date();
  for (let i = days-1; i >= 0; i--) {
    const d = new Date(now.getFullYear(), now.getMonth(), now.getDate() - i);
    dayMap[localISODate(d)] = 0;
  }
  data.forEach(o => {
    const d = localISODate(new Date(o.created_at));
    if (dayMap[d] !== undefined) dayMap[d] += parseFloat(o.total);
  });

  return {
    revenue,
    byPayment,
    orderCount: data.length,
    byDay: Object.entries(dayMap).map(([date, val]) => ({ date, val })),
  };
}

// Faturamento agrupado por dia, dentro de um intervalo de datas especifico (para filtro de mes/ano)
async function fetchFinancialsByRange(startDate, endDate) {
  const { data } = await sb()
    .from("orders")
    .select("total, payment, status, created_at")
    .neq("status", "Cancelado")
    .gte("created_at", startDate.toISOString())
    .lte("created_at", endDate.toISOString());
  if (!data) return { revenue: 0, byPayment: {}, byDay: [] };

  const revenue = data.reduce((s, o) => s + parseFloat(o.total), 0);
  const byPayment = {};
  data.forEach(o => {
    byPayment[o.payment] = (byPayment[o.payment] || 0) + parseFloat(o.total);
  });

  const dayMap = {};
  const cursor = new Date(startDate.getFullYear(), startDate.getMonth(), startDate.getDate());
  const end = new Date(endDate.getFullYear(), endDate.getMonth(), endDate.getDate());
  while (cursor <= end) {
    dayMap[localISODate(cursor)] = 0;
    cursor.setDate(cursor.getDate()+1);
  }
  data.forEach(o => {
    const d = localISODate(new Date(o.created_at));
    if (dayMap[d] !== undefined) dayMap[d] += parseFloat(o.total);
  });

  return {
    revenue,
    byPayment,
    orderCount: data.length,
    byDay: Object.entries(dayMap).map(([date, val]) => ({ date, val })),
  };
}

/* ---------- FAVORITOS ---------- */
async function fetchFavorites() {
  const { data: { user } } = await sb().auth.getUser();
  if (!user) return [];
  const { data, error } = await sb()
    .from("favorites")
    .select("product_id")
    .eq("user_id", user.id);
  if (error) throw error;
  return (data || []).map(f => f.product_id);
}

async function addFavorite(productId) {
  const { data: { user } } = await sb().auth.getUser();
  if (!user) throw new Error("É preciso estar logado para favoritar.");
  const { error } = await sb()
    .from("favorites")
    .insert({ user_id: user.id, product_id: productId });
  if (error && error.code !== "23505") throw error; // ignora duplicado
}

async function removeFavorite(productId) {
  const { data: { user } } = await sb().auth.getUser();
  if (!user) return;
  const { error } = await sb()
    .from("favorites")
    .delete()
    .eq("user_id", user.id)
    .eq("product_id", productId);
  if (error) throw error;
}

/* ---------- EXPORTAR ---------- */
Object.assign(window, {
  DB: {
    fetchProfile, fetchMyOrders,
    fetchProducts, fetchAllProducts, fetchProductById, saveProduct, deleteProduct, updateStock,
    fetchCategories,
    fetchOrders, saveOrder, updateOrderStatus, cancelOrder, deleteOrder, fetchOrderStatus,
    fetchCustomers, deleteCustomer,
    fetchCoupons, validateCoupon, saveCoupon, deleteCoupon,
    fetchConfig, saveConfig,
    fetchFinancials, fetchFinancialsByRange,
    fetchFavorites, addFavorite, removeFavorite,
    fetchVariants, saveVariants, decrementVariantStock, fetchVariantTotals,
  }
});