LeadFlow CRM — Backend Completo

-- ================================================
-- LeadFlow CRM · Migración Inicial · Supabase
-- ================================================

-- Tipo enum para estado de leads
create type lead_status as enum ('new', 'contacted', 'qualified', 'won', 'lost');

-- Tabla: workspaces (multitenancy)
create table public.workspaces (
  id          uuid default gen_random_uuid() primary key,
  name        text not null,
  slug        text unique not null,
  owner_id    uuid references public.profiles(id) on delete cascade,
  plan        text default 'starter',
  created_at  timestamptz default now()
);

-- Tabla: profiles (auto-creado en signup via trigger)
create table public.profiles (
  id          uuid references auth.users on delete cascade primary key,
  full_name   text,
  avatar_url  text,
  role        text default 'user',
  created_at  timestamptz default now()
);

-- Tabla: leads (core de la app)
create table public.leads (
  id            uuid default gen_random_uuid() primary key,
  workspace_id  uuid references public.workspaces(id) on delete cascade not null,
  name          text not null,
  email         text,
  company       text,
  status        lead_status default 'new',
  value_eur     numeric(10,2) default 0,
  assigned_to   uuid references public.profiles(id),
  notes         text,
  created_at    timestamptz default now(),
  updated_at    timestamptz default now()
);

-- Auto-update updated_at
create or replace function update_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end; $$ language plpgsql;

create trigger leads_updated_at
  before update on public.leads
  for each row execute procedure update_updated_at();

-- Trigger: crear profile al registrarse
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, full_name)
  values (new.id, new.raw_user_meta_data->>>'full_name');
  return new;
end; $$ language plpgsql security definer;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

-- Habilitar RLS en todas las tablas
alter table public.workspaces        enable row level security;
alter table public.profiles          enable row level security;
alter table public.workspace_members  enable row level security;
alter table public.leads             enable row level security;
alter table public.lead_files        enable row level security;

// Subir propuesta PDF a un lead
export async function uploadLeadFile(
  leadId: string, workspaceId: string, file: File
) {
  const sb = createClient();
  const path = `${workspaceId}/${leadId}/${Date.now()}_${file.name}`;

  const { error } = await sb.storage
    .from('lead-files')
    .upload(path, file, { upsert: false, contentType: file.type });

  if (error) throw error;

  // Registrar en BD
  await sb.from('lead_files').insert({
    lead_id: leadId,
    storage_path: path,
    filename: file.name,
    size_bytes: file.size
  });

  // URL firmada (expira en 1h)
  return sb.storage.from('lead-files')
    .createSignedUrl(path, 3600);
}

import { serve } from 'https://deno.land/std@0.168.0/http/server.ts';
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';

serve(async (req) => {
  const { record, old_record } = await req.json();

  // Solo notificar si cambió assigned_to
  if (record.assigned_to === old_record.assigned_to)
    return new Response('no change');

  const sb = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
  );

  // Obtener email del nuevo asignado
  const { data: user } = await sb.auth.admin
    .getUserById(record.assigned_to);

  await fetch('https://api.resend.com/emails', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${Deno.env.get('RESEND_API_KEY')}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      from: 'noreply@leadflow.app',
      to: user?.email,
      subject: `[LeadFlow] Nuevo lead asignado: ${record.name}`,
      html: `<h2>Tienes un nuevo lead: <strong>${record.name}</strong></h2>
             <p>Empresa: ${record.company} | Valor: €${record.value_eur}</p>
             <a href="https://app.leadflow.app/leads/${record.id}">
               Ver lead →
             </a>`
    })
  });

  return new Response(JSON.stringify({ sent: true }),
    { headers: { 'Content-Type': 'application/json' } });
});