Configuración / Setup

Este portal aún no está conectado. Sigue estos pasos (solo una vez):
En supabase.com crea un proyecto (gratis).
Menú SQL Editor → New query: pega el código de abajo → Run.
Project Settings → API: copia Project URL y la llave anon public (NUNCA la service_role).
Abre este mismo archivo en un editor de texto, busca PEGA AQUI TUS DATOS y reemplaza SUPABASE_URL y SUPABASE_ANON_KEY.
Supabase → Authentication → URL Configuration: en Site URL pon la dirección final del portal.
Sube este archivo a Vercel (renómbralo index.html).
Crea tu cuenta en el portal y corre en SQL Editor: update public.profiles set role='admin', status='approved' where email='TU-CORREO'; — luego cierra sesión y entra otra vez.
SQL (paso 2)
-- =====================================================================
-- VALUE 141 PARTNER PORTAL — Paso 1: cuentas, roles, empresas, noticias
-- Pega TODO esto en Supabase > SQL Editor > New query > Run
-- =====================================================================

create extension if not exists pgcrypto;

-- ---------- TABLAS ----------
create table if not exists public.companies (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_at timestamptz not null default now()
);

create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  email text,
  full_name text,
  company_name_requested text,
  role text not null default 'company' check (role in ('admin','company')),
  status text not null default 'pending' check (status in ('pending','approved','blocked')),
  company_id uuid references public.companies(id) on delete set null,
  created_at timestamptz not null default now()
);

create table if not exists public.news (
  id uuid primary key default gen_random_uuid(),
  tag text not null default 'news' check (tag in ('update','news','event','tip')),
  title text not null,
  body text not null default '',
  title_es text,
  body_es text,
  created_by uuid references public.profiles(id) on delete set null,
  created_at timestamptz not null default now()
);

create index if not exists profiles_status_idx on public.profiles(status);
create index if not exists profiles_company_idx on public.profiles(company_id);
create index if not exists news_created_idx on public.news(created_at desc);

-- ---------- FUNCIONES DE AYUDA (security definer evita recursion en RLS) ----------
create or replace function public.is_admin()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.profiles
    where id = auth.uid() and role = 'admin' and status = 'approved'
  );
$$;

create or replace function public.is_approved()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.profiles
    where id = auth.uid() and status = 'approved'
  );
$$;

create or replace function public.my_company_id()
returns uuid
language sql
stable
security definer
set search_path = public
as $$
  select company_id from public.profiles
  where id = auth.uid() and status = 'approved';
$$;

-- ---------- CREAR PERFIL AUTOMATICO AL REGISTRARSE ----------
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id, email, full_name, company_name_requested)
  values (
    new.id,
    new.email,
    left(coalesce(new.raw_user_meta_data->>'full_name', ''), 120),
    left(coalesce(new.raw_user_meta_data->>'company_name', ''), 160)
  )
  on conflict (id) do nothing;
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

-- ---------- SEGURIDAD (RLS) ----------
alter table public.profiles  enable row level security;
alter table public.companies enable row level security;
alter table public.news      enable row level security;

-- profiles: cada quien ve el suyo; admin ve todos; SOLO admin modifica.
drop policy if exists profiles_select on public.profiles;
create policy profiles_select on public.profiles
  for select using (id = auth.uid() or public.is_admin());

drop policy if exists profiles_update_admin on public.profiles;
create policy profiles_update_admin on public.profiles
  for update using (public.is_admin()) with check (public.is_admin());

-- companies: admin todo; miembro aprobado ve solo la suya.
drop policy if exists companies_select on public.companies;
create policy companies_select on public.companies
  for select using (public.is_admin() or id = public.my_company_id());

drop policy if exists companies_insert on public.companies;
create policy companies_insert on public.companies
  for insert with check (public.is_admin());

drop policy if exists companies_update on public.companies;
create policy companies_update on public.companies
  for update using (public.is_admin()) with check (public.is_admin());

drop policy if exists companies_delete on public.companies;
create policy companies_delete on public.companies
  for delete using (public.is_admin());

-- news: miembros aprobados leen; solo admin escribe.
drop policy if exists news_select on public.news;
create policy news_select on public.news
  for select using (public.is_approved());

drop policy if exists news_insert on public.news;
create policy news_insert on public.news
  for insert with check (public.is_admin());

drop policy if exists news_update on public.news;
create policy news_update on public.news
  for update using (public.is_admin()) with check (public.is_admin());

drop policy if exists news_delete on public.news;
create policy news_delete on public.news
  for delete using (public.is_admin());

-- =====================================================================
-- PASO 2: LEADS, CLIENTES, NOTAS Y SEGUIMIENTOS (cada empresa ve SOLO lo suyo)
-- =====================================================================
create table if not exists public.leads (
  id uuid primary key default gen_random_uuid(),
  company_id uuid not null references public.companies(id) on delete cascade,
  name text not null,
  phone text,
  email text,
  source text not null default 'other' check (source in ('facebook','instagram','website','referral','call','other')),
  status text not null default 'new' check (status in ('new','contacted','appointment','quoted','won','lost')),
  project_type text,
  value numeric(12,2),
  next_followup date,
  appt_at timestamptz,
  created_by uuid references public.profiles(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create table if not exists public.lead_notes (
  id uuid primary key default gen_random_uuid(),
  lead_id uuid not null references public.leads(id) on delete cascade,
  company_id uuid not null references public.companies(id) on delete cascade,
  body text not null,
  author_name text,
  created_by uuid references public.profiles(id) on delete set null,
  created_at timestamptz not null default now()
);
create index if not exists leads_company_idx on public.leads(company_id, status);
create index if not exists leads_followup_idx on public.leads(company_id, next_followup);
create index if not exists lead_notes_lead_idx on public.lead_notes(lead_id, created_at desc);

alter table public.leads      enable row level security;
alter table public.lead_notes enable row level security;

drop policy if exists leads_all on public.leads;
create policy leads_all on public.leads for all
  using (public.is_admin() or company_id = public.my_company_id())
  with check (public.is_admin() or company_id = public.my_company_id());

drop policy if exists lead_notes_all on public.lead_notes;
create policy lead_notes_all on public.lead_notes for all
  using (public.is_admin() or company_id = public.my_company_id())
  with check (public.is_admin() or company_id = public.my_company_id());

-- =====================================================================
-- DESPUES de crear TU cuenta en el portal, corre esta linea UNA vez
-- (cambia el correo por el tuyo) para volverte administrador:
--
--   update public.profiles set role='admin', status='approved'
--   where email='tu-correo@ejemplo.com';
-- =====================================================================
Configuración / Setup

Value 141
