// === Supabase client (single-tenant · Casa do José) ===
// O anon key é público por design — a segurança real é feita pelas
// Row-Level Security policies definidas em supabase/schema.sql.

const SUPABASE_URL = 'https://hknvcwhewutnnmdxrivq.supabase.co';
const SUPABASE_ANON_KEY = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6ImhrbnZjd2hld3V0bm5tZHhyaXZxIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NzkwNDQ3NTMsImV4cCI6MjA5NDYyMDc1M30.vlI6C0bx0oMKLy7-FzSbpWzJorHgyUuFNOv0Ty7H4Bo';

const sb = window.supabase.createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
  auth: {
    persistSession: true,
    autoRefreshToken: true,
    detectSessionInUrl: true,
  },
});

// Cliente auxiliar usado APENAS para criar novos usuários (signUp)
// sem afetar a sessão do admin que está logado.
const sbAdminInvite = window.supabase.createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
  auth: {
    persistSession: false,
    autoRefreshToken: false,
    detectSessionInUrl: false,
    storageKey: 'sb-invite-only',
  },
});

// Convenience helpers used across screens.
const scpAuth = {
  async signIn(email, password) {
    return sb.auth.signInWithPassword({ email, password });
  },
  async signOut() {
    return sb.auth.signOut();
  },
  async resetPassword(email) {
    return sb.auth.resetPasswordForEmail(email, {
      redirectTo: window.location.origin,
    });
  },
  async getSession() {
    const { data } = await sb.auth.getSession();
    return data.session;
  },
  async getProfile() {
    const session = await this.getSession();
    if (!session?.user) return null;
    const { data, error } = await sb
      .from('profiles')
      .select('*')
      .eq('id', session.user.id)
      .single();
    if (error) {
      console.warn('Falha ao carregar profile:', error.message);
      return null;
    }
    return data;
  },
  onChange(cb) {
    return sb.auth.onAuthStateChange((_event, session) => cb(session));
  },
};

// === Data services ===
// Cada tela importa via window.scpDb.<entidade>.
const scpDb = {
  products: {
    async list() {
      const { data, error } = await sb
        .from('products')
        .select('*, preferred_supplier:suppliers(id, name, city, score, avg_delivery, whatsapp)')
        .eq('active', true)
        .order('name');
      if (error) throw error;
      return data || [];
    },
    async create(payload) {
      const { data, error } = await sb
        .from('products')
        .insert(payload)
        .select('*, preferred_supplier:suppliers(id, name, city, score, avg_delivery, whatsapp)')
        .single();
      if (error) throw error;
      return data;
    },
    async update(id, payload) {
      const { data, error } = await sb
        .from('products')
        .update(payload)
        .eq('id', id)
        .select('*, preferred_supplier:suppliers(id, name, city, score, avg_delivery, whatsapp)')
        .single();
      if (error) throw error;
      return data;
    },
    async remove(id) {
      // soft delete — mantém histórico
      const { error } = await sb.from('products').update({ active: false }).eq('id', id);
      if (error) throw error;
    },
    async uploadPhoto(file, productCode) {
      const ext = file.name.split('.').pop()?.toLowerCase() || 'jpg';
      const slug = (productCode || crypto.randomUUID()).replace(/[^a-z0-9-]/gi, '');
      const path = `${slug}-${Date.now()}.${ext}`;
      const { error: upErr } = await sb.storage
        .from('product-photos')
        .upload(path, file, { upsert: true, contentType: file.type });
      if (upErr) throw upErr;
      const { data: pub } = sb.storage.from('product-photos').getPublicUrl(path);
      return pub.publicUrl;
    },
  },

  stats: {
    // Contadores usados pelos badges da sidebar + card "Assistente IA"
    async sidebar() {
      const [q, o, p] = await Promise.all([
        sb.from('quotations').select('id', { count: 'exact', head: true }).in('status', ['aberta','analise']),
        sb.from('orders').select('id', { count: 'exact', head: true }).in('status', ['confirmado','em_transito']),
        sb.from('products').select('id, stock, min_stock').eq('active', true),
      ]);
      const lowStock = (p.data || []).filter(r => Number(r.stock) < Number(r.min_stock)).length;

      // Economia acumulada este ano
      const yearStart = new Date(new Date().getFullYear(), 0, 1).toISOString();
      const { data: saved } = await sb
        .from('quotations')
        .select('saving_value')
        .eq('status', 'fechada')
        .gte('closed_at', yearStart);
      const totalSavings = (saved || []).reduce((s, r) => s + Number(r.saving_value || 0), 0);

      return {
        cotacoes: q.count || 0,
        pedidos: o.count || 0,
        estoque: lowStock,
        savingsThisYear: totalSavings,
      };
    },
  },

  ai: {
    // Pega a análise mais recente da cotação (cache).
    async getLatestAnalysis(quotationId) {
      const { data, error } = await sb
        .from('quotation_analyses')
        .select('*')
        .eq('quotation_id', quotationId)
        .order('created_at', { ascending: false })
        .limit(1)
        .maybeSingle();
      if (error) return null;
      return data;
    },
    // Chama a Edge Function para gerar nova análise via OpenAI.
    async analyzeQuotation(quotationId) {
      const { data, error } = await sb.functions.invoke('analyze-quotation', {
        body: { quotation_id: quotationId },
      });
      if (error) throw error;
      if (data?.error) throw new Error(data.detail || data.error);
      return data;
    },
  },

  profiles: {
    async list() {
      // Junta com auth.users via view? Como não temos acesso direto a auth schema do cliente,
      // pegamos só de profiles. Para obter o email, usamos uma view ou fazemos via RPC.
      // Por simplicidade, lemos só de profiles e pegamos o email do auth.users via select com join.
      const { data, error } = await sb
        .from('profiles')
        .select('*')
        .order('created_at', { ascending: true });
      if (error) throw error;
      // Para emails, fazemos uma chamada complementar (admin pode ver via RPC)
      const ids = (data || []).map(p => p.id);
      let emailMap = {};
      if (ids.length) {
        // Usa rpc para buscar emails — vamos criar essa função no schema
        const { data: emails } = await sb.rpc('get_user_emails', { user_ids: ids });
        if (emails) {
          emails.forEach(e => { emailMap[e.id] = e.email; });
        }
      }
      return (data || []).map(p => ({ ...p, email: emailMap[p.id] || '—' }));
    },
    async update(id, patch) {
      const { data, error } = await sb
        .from('profiles')
        .update(patch)
        .eq('id', id)
        .select('*')
        .single();
      if (error) throw error;
      return data;
    },
    async deactivate(id) {
      // soft delete: marca inativo. Para excluir completamente precisa admin API.
      const { error } = await sb.from('profiles').update({ active: false }).eq('id', id);
      if (error) throw error;
    },
    async reactivate(id) {
      const { error } = await sb.from('profiles').update({ active: true }).eq('id', id);
      if (error) throw error;
    },
    // Convida um novo usuário criando uma conta auth + atualizando o profile.
    // Usa um cliente auxiliar pra não bagunçar a sessão do admin.
    async invite({ name, email, password, role }) {
      const { data, error } = await sbAdminInvite.auth.signUp({
        email,
        password,
        options: {
          data: { name },
          emailRedirectTo: window.location.origin,
        },
      });
      if (error) throw error;
      if (!data.user) throw new Error('Falha ao criar usuário');

      // O trigger handle_new_user cria o profile automaticamente.
      // Esperamos um pouco e atualizamos o profile com nome e role corretos.
      await new Promise(r => setTimeout(r, 600));
      const { error: updErr } = await sb
        .from('profiles')
        .update({ name, role, active: true })
        .eq('id', data.user.id);
      if (updErr) console.warn('Profile atualizado falhou:', updErr.message);

      return {
        user: data.user,
        needsConfirmation: !data.session, // se session é null, email precisa ser confirmado
      };
    },
  },

  notifications: {
    async list(limit = 20) {
      const { data, error } = await sb
        .from('notifications')
        .select('*')
        .order('created_at', { ascending: false })
        .limit(limit);
      if (error) throw error;
      return data || [];
    },
    async unreadCount() {
      const { count, error } = await sb
        .from('notifications')
        .select('id', { count: 'exact', head: true })
        .eq('unread', true);
      if (error) return 0;
      return count || 0;
    },
    async markRead(id) {
      const { error } = await sb.from('notifications').update({ unread: false }).eq('id', id);
      if (error) throw error;
    },
    async markAllRead() {
      const session = await scpAuth.getSession();
      if (!session?.user) return;
      const { error } = await sb
        .from('notifications')
        .update({ unread: false })
        .eq('user_id', session.user.id)
        .eq('unread', true);
      if (error) throw error;
    },
    async create({ user_id, icon, title, sub, tone }) {
      const { data, error } = await sb
        .from('notifications')
        .insert({ user_id, icon, title, sub, tone, unread: true })
        .select('*')
        .single();
      if (error) throw error;
      return data;
    },
    // Subscribe to realtime changes for the current user. cb is called with the new/updated row.
    subscribe(userId, cb) {
      const channel = sb
        .channel('notif-' + userId)
        .on('postgres_changes',
          { event: '*', schema: 'public', table: 'notifications', filter: `user_id=eq.${userId}` },
          (payload) => cb(payload))
        .subscribe();
      return () => { sb.removeChannel(channel); };
    },
  },

  dashboard: {
    // Carrega todos os dados necessários do dashboard em paralelo
    async load() {
      const [q, o, p, r, cats] = await Promise.all([
        sb.from('quotations').select(`
          id, code, title, status, total_value, saving_value, deadline, closed_at, created_at,
          items:quotation_items(id),
          invited:quotation_suppliers(supplier_id, status)
        `).order('created_at', { ascending: false }),
        sb.from('orders').select(`
          id, code, total, status, emitted_at, eta, delivered_at,
          supplier:suppliers(id, name),
          items:order_items(id, product_id, qty, unit_price, product:products(cat))
        `).order('emitted_at', { ascending: false }),
        sb.from('products').select('id, name, stock, min_stock, last_price, cat').eq('active', true),
        sb.from('receivings').select('id, code, status, divergence_count, received_at, order:orders(supplier:suppliers(name))').order('received_at', { ascending: false }).limit(20),
        sb.from('categories').select('*').order('sort_order'),
      ]);
      if (q.error) throw q.error;
      if (o.error) throw o.error;
      if (p.error) throw p.error;
      if (r.error) throw r.error;
      return {
        quotations: q.data || [],
        orders: o.data || [],
        products: p.data || [],
        receivings: r.data || [],
        categories: cats.data || [],
      };
    },
  },

  receivings: {
    async list() {
      const { data, error } = await sb
        .from('receivings')
        .select(`
          id, code, status, divergence_count, received_at, approved_at, notes,
          order:orders(id, code, total, supplier:suppliers(id, name))
        `)
        .order('received_at', { ascending: false });
      if (error) throw error;
      return data || [];
    },
    async getById(id) {
      const { data, error } = await sb
        .from('receivings')
        .select(`
          *,
          order:orders(
            id, code, total,
            supplier:suppliers(id, name, cnpj, whatsapp),
            items:order_items(id, product_id, name_snapshot, unit_snapshot, qty, unit_price)
          ),
          items:receiving_items(*)
        `)
        .eq('id', id)
        .single();
      if (error) throw error;
      return data;
    },
    async create({ order_id, notes }) {
      const code = await this._nextCode();
      const { data: rec, error } = await sb
        .from('receivings')
        .insert({ code, order_id, status: 'pendente', notes: notes || null })
        .select('*')
        .single();
      if (error) throw error;
      // Cria 1 item de conferência por order_item (qty_received começa em 0)
      const { data: orderItems } = await sb
        .from('order_items')
        .select('id, product_id, qty')
        .eq('order_id', order_id);
      if (orderItems?.length) {
        const rows = orderItems.map(it => ({
          receiving_id: rec.id,
          product_id: it.product_id,
          qty_ordered: it.qty,
          qty_received: 0,
          ok: false,
        }));
        await sb.from('receiving_items').insert(rows);
      }
      return rec;
    },
    // Aprova recebimento + cria movimentações de entrada + atualiza estoque
    async approve(receivingId, opts = {}) {
      const session = await scpAuth.getSession();
      const { data: full, error: gErr } = await sb
        .from('receivings')
        .select('id, status, items:receiving_items(*), order:orders(code)')
        .eq('id', receivingId)
        .single();
      if (gErr) throw gErr;
      if (full.status === 'aprovado') return full;

      // 1) movimentações de entrada (1 por item recebido com qty > 0)
      const movs = (full.items || [])
        .filter(it => Number(it.qty_received) > 0 || (opts.fallbackToOrdered && Number(it.qty_ordered) > 0))
        .map(it => ({
          product_id: it.product_id,
          kind: 'entrada',
          qty: Number(it.qty_received) > 0 ? Number(it.qty_received) : Number(it.qty_ordered),
          reason: `Recebimento ${full.order?.code || ''}`.trim(),
          ref_table: 'receivings',
          ref_id: receivingId,
          created_by: session?.user?.id || null,
        }));
      if (movs.length) {
        const { error: mErr } = await sb.from('stock_movements').insert(movs);
        if (mErr) throw mErr;
      }

      // 2) Incrementa estoque dos produtos (via RPC ou updates individuais)
      for (const m of movs) {
        const { data: prod } = await sb.from('products').select('stock').eq('id', m.product_id).single();
        if (prod) {
          await sb.from('products').update({ stock: Number(prod.stock) + m.qty }).eq('id', m.product_id);
        }
      }

      // 3) Marca recebimento como aprovado
      const { data: upd, error: uErr } = await sb
        .from('receivings')
        .update({ status: 'aprovado', approved_at: new Date().toISOString(), approved_by: session?.user?.id || null })
        .eq('id', receivingId)
        .select('*')
        .single();
      if (uErr) throw uErr;
      return upd;
    },
    async reject(receivingId, reason) {
      const session = await scpAuth.getSession();
      const { data, error } = await sb
        .from('receivings')
        .update({ status: 'recusado', approved_at: new Date().toISOString(), approved_by: session?.user?.id || null, notes: reason || null })
        .eq('id', receivingId)
        .select('*')
        .single();
      if (error) throw error;
      return data;
    },
    async updateItem(itemId, patch) {
      const { error } = await sb.from('receiving_items').update(patch).eq('id', itemId);
      if (error) throw error;
    },
    async _nextCode() {
      const { data } = await sb
        .from('receivings')
        .select('code')
        .like('code', 'REC-%')
        .order('code', { ascending: false })
        .limit(1);
      const last = data?.[0]?.code;
      const lastNum = last ? parseInt(last.split('-').pop()) : 0;
      return `REC-${String(lastNum + 1).padStart(4, '0')}`;
    },
  },

  stockMovements: {
    async listByProduct(productId, limit = 20) {
      const { data, error } = await sb
        .from('stock_movements')
        .select('*')
        .eq('product_id', productId)
        .order('created_at', { ascending: false })
        .limit(limit);
      if (error) throw error;
      return data || [];
    },
  },

  orders: {
    async list() {
      const { data, error } = await sb
        .from('orders')
        .select(`
          id, code, total, status, emitted_at, confirmed_at, eta, delivered_at, created_at,
          supplier:suppliers(id, name, cnpj, city, whatsapp, email, score),
          quotation:quotations(id, code, title),
          items:order_items(id, name_snapshot, qty, unit_price)
        `)
        .order('emitted_at', { ascending: false });
      if (error) throw error;
      return (data || []).map(o => ({
        ...o,
        itemsCount: o.items?.length || 0,
      }));
    },
    async getById(id) {
      const { data, error } = await sb
        .from('orders')
        .select(`
          *,
          supplier:suppliers(*),
          quotation:quotations(*),
          items:order_items(*, product:products(id, code, name, brand, unit, photo_url))
        `)
        .eq('id', id)
        .single();
      if (error) throw error;
      return data;
    },
    async updateStatus(id, status) {
      const patch = { status };
      if (status === 'confirmado') patch.confirmed_at = new Date().toISOString();
      if (status === 'entregue')   patch.delivered_at = new Date().toISOString();
      const { data, error } = await sb.from('orders').update(patch).eq('id', id).select('*').single();
      if (error) throw error;
      return data;
    },
    async _nextCode() {
      const year = new Date().getFullYear();
      const { data } = await sb
        .from('orders')
        .select('code')
        .like('code', `PC-${year}-%`)
        .order('code', { ascending: false })
        .limit(1);
      const last = data?.[0]?.code;
      const lastNum = last ? parseInt(last.split('-').pop()) : 0;
      return `PC-${year}-${String(lastNum + 1).padStart(4, '0')}`;
    },
    // Cria múltiplos pedidos a partir de uma cotação aprovada.
    // groupedBids: { [supplier_id]: [{ item, best }] } — mesma estrutura usada no detalhe.
    async createFromQuotation(quotation, groupedBids) {
      const created = [];
      const eta = (() => { const d = new Date(); d.setDate(d.getDate() + 3); return d.toISOString().slice(0, 10); })();

      for (const [supplierId, items] of Object.entries(groupedBids)) {
        const code = await this._nextCode();
        const total = items.reduce((s, it) => s + Number(it.best.bid.price) * Number(it.item.qty), 0);
        const { data: order, error } = await sb
          .from('orders')
          .insert({
            code,
            supplier_id: supplierId,
            quotation_id: quotation.id,
            total,
            status: 'emitido',
            eta,
          })
          .select('*')
          .single();
        if (error) throw error;

        const itemRows = items.map(it => ({
          order_id: order.id,
          product_id: it.item.product_id,
          name_snapshot: it.item.name,
          unit_snapshot: it.item.unit,
          qty: Number(it.item.qty),
          unit_price: Number(it.best.bid.price),
        }));
        if (itemRows.length) {
          const { error: itErr } = await sb.from('order_items').insert(itemRows);
          if (itErr) throw itErr;
        }
        created.push(order);
      }
      return created;
    },
  },

  quotations: {
    async list() {
      const { data, error } = await sb
        .from('quotations')
        .select(`
          id, code, title, status, obs, deadline, closed_at, total_value, saving_value, created_at,
          items:quotation_items(id),
          invited:quotation_suppliers(supplier_id, status)
        `)
        .order('created_at', { ascending: false });
      if (error) throw error;
      return (data || []).map(q => ({
        ...q,
        itemsCount: q.items?.length || 0,
        suppliersCount: q.invited?.length || 0,
        respondedCount: (q.invited || []).filter(i => i.status === 'respondida').length,
      }));
    },
    async getByIdOrCode(idOrCode) {
      const col = idOrCode && idOrCode.startsWith('COT-') ? 'code' : 'id';
      const { data, error } = await sb
        .from('quotations')
        .select(`
          *,
          items:quotation_items(*, product:products(id, name, code, unit, brand, photo_url)),
          invited:quotation_suppliers(supplier_id, status, responded_at, access_token, supplier:suppliers(id, name, cnpj, city, whatsapp, email, score, avg_delivery, cats)),
          bids:bids(*)
        `)
        .eq(col, idOrCode)
        .single();
      if (error) throw error;
      return data;
    },
    async create({ title, deadline, obs, items, supplier_ids }) {
      const session = await scpAuth.getSession();
      const code = await this._nextCode();
      const { data: q, error } = await sb
        .from('quotations')
        .insert({ code, title, status: 'aberta', obs, deadline, created_by: session?.user?.id })
        .select('*')
        .single();
      if (error) throw error;

      if (items?.length) {
        const itemRows = items.map((it, i) => ({
          quotation_id: q.id,
          product_id: it.product_id,
          name_snapshot: it.name,
          unit_snapshot: it.unit,
          qty: it.qty,
          last_price: it.last_price,
          position: i,
        }));
        const { error: itErr } = await sb.from('quotation_items').insert(itemRows);
        if (itErr) throw itErr;
      }
      if (supplier_ids?.length) {
        const supRows = supplier_ids.map(sid => ({ quotation_id: q.id, supplier_id: sid, status: 'pendente' }));
        const { error: sErr } = await sb.from('quotation_suppliers').insert(supRows);
        if (sErr) throw sErr;
      }
      return q;
    },
    async updateStatus(id, status) {
      const patch = { status };
      if (status === 'fechada') patch.closed_at = new Date().toISOString();
      const { data, error } = await sb.from('quotations').update(patch).eq('id', id).select('*').single();
      if (error) throw error;
      return data;
    },
    async remove(id) {
      // Cascade já limpa items, suppliers convidados, bids e analyses.
      // orders ficam preservados (FK set null).
      const { error } = await sb.from('quotations').delete().eq('id', id);
      if (error) throw error;
    },
    async _nextCode() {
      const year = new Date().getFullYear();
      const { data } = await sb
        .from('quotations')
        .select('code')
        .like('code', `COT-${year}-%`)
        .order('code', { ascending: false })
        .limit(1);
      const last = data?.[0]?.code;
      const lastNum = last ? parseInt(last.split('-').pop()) : 0;
      return `COT-${year}-${String(lastNum + 1).padStart(4, '0')}`;
    },
  },

  suppliers: {
    async list({ includeInactive = false } = {}) {
      let q = sb.from('suppliers').select('*').order('created_at', { ascending: false });
      if (!includeInactive) q = q.neq('status', 'inativo');
      const { data, error } = await q;
      if (error) throw error;
      return data || [];
    },
    async create(payload) {
      const { data, error } = await sb
        .from('suppliers')
        .insert(payload)
        .select('*')
        .single();
      if (error) throw error;
      return data;
    },
    async update(id, payload) {
      const { data, error } = await sb
        .from('suppliers')
        .update(payload)
        .eq('id', id)
        .select('*')
        .single();
      if (error) throw error;
      return data;
    },
    async remove(id) {
      // Soft delete: marca como 'inativo' para preservar histórico (cotações, pedidos, bids).
      // Hard delete só é possível se o fornecedor nunca foi convidado/usado em nada.
      const { error } = await sb.from('suppliers').update({ status: 'inativo' }).eq('id', id);
      if (error) throw error;
    },
    async ordersBy(supplierId) {
      const { data, error } = await sb
        .from('orders')
        .select('*')
        .eq('supplier_id', supplierId)
        .order('emitted_at', { ascending: false })
        .limit(4);
      if (error) return [];
      return data || [];
    },
    async productsBy(supplierId) {
      const { data, error } = await sb
        .from('products')
        .select('*')
        .eq('preferred_supplier_id', supplierId)
        .order('name')
        .limit(6);
      if (error) return [];
      return data || [];
    },
  },
};

window.sb = sb;
window.scpAuth = scpAuth;
window.scpDb = scpDb;