Banco de dados

-- ============================================================
-- StarChat :: Schema multi-tenant para Supabase Self-Hosted
-- Executar no SQL Editor da instancia (VPS).
-- ============================================================

create extension if not exists "pgcrypto";

-- ---------- Enums ----------
do $$ begin
  create type app_role as enum ('admin', 'supervisor', 'agent');
exception when duplicate_object then null; end $$;

do $$ begin
  create type wa_provider as enum ('meta_cloud', 'evolution');
exception when duplicate_object then null; end $$;

do $$ begin
  create type instance_status as enum ('connected', 'connecting', 'disconnected');
exception when duplicate_object then null; end $$;

do $$ begin
  create type conversation_status as enum ('open', 'pending', 'closed');
exception when duplicate_object then null; end $$;

do $$ begin
  create type message_direction as enum ('inbound', 'outbound');
exception when duplicate_object then null; end $$;

do $$ begin
  create type message_kind as enum ('text', 'image', 'audio', 'video', 'document', 'note', 'template');
exception when duplicate_object then null; end $$;

-- ---------- organizations (tenant raiz) ----------
create table if not exists public.organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text unique not null,
  plan text not null default 'starter',
  timezone text not null default 'America/Sao_Paulo',
  business_hours jsonb not null default '{"mon_fri":["08:00","18:00"],"sat":["08:00","12:00"]}'::jsonb,
  created_at timestamptz not null default now()
);

-- ---------- profiles (usuarios do tenant) ----------
create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  organization_id uuid not null references public.organizations(id) on delete cascade,
  full_name text not null,
  email text not null,
  avatar_url text,
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);
create index if not exists profiles_org_idx on public.profiles(organization_id);

-- ---------- user_roles (papeis SEMPRE em tabela separada) ----------
create table if not exists public.user_roles (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  organization_id uuid not null references public.organizations(id) on delete cascade,
  role app_role not null,
  unique (user_id, organization_id, role)
);

-- ---------- departments / filas ----------
create table if not exists public.departments (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  name text not null,
  sla_minutes int not null default 5,
  created_at timestamptz not null default now()
);

-- ---------- whatsapp_instances (Meta Cloud + Evolution) ----------
create table if not exists public.whatsapp_instances (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  department_id uuid references public.departments(id) on delete set null,
  name text not null,
  provider wa_provider not null,
  status instance_status not null default 'disconnected',
  phone_number text,
  -- Meta Cloud API
  waba_id text,
  phone_number_id text,
  access_token text,
  -- Evolution API
  server_url text,
  global_api_key text,
  instance_name text,
  webhook_secret text default encode(gen_random_bytes(24), 'hex'),
  created_at timestamptz not null default now()
);
create index if not exists wa_instances_org_idx on public.whatsapp_instances(organization_id);

-- ---------- contacts ----------
create table if not exists public.contacts (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  name text not null,
  phone text not null,
  email text,
  document text,            -- CPF / CNPJ
  medical_record text,      -- prontuario (StarLaudo)
  balance_due numeric(12,2) not null default 0,
  tags text[] not null default '{}',
  custom_fields jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  unique (organization_id, phone)
);
create index if not exists contacts_org_idx on public.contacts(organization_id);

-- ---------- conversations (tickets / cards do CRM) ----------
create table if not exists public.conversations (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  contact_id uuid not null references public.contacts(id) on delete cascade,
  instance_id uuid references public.whatsapp_instances(id) on delete set null,
  department_id uuid references public.departments(id) on delete set null,
  assignee_id uuid references public.profiles(id) on delete set null,
  status conversation_status not null default 'open',
  stage text not null default 'Novo Lead',
  deal_value numeric(12,2) not null default 0,
  tags text[] not null default '{}',
  unread_count int not null default 0,
  last_message_at timestamptz not null default now(),
  created_at timestamptz not null default now()
);
create index if not exists conversations_org_status_idx on public.conversations(organization_id, status);
create index if not exists conversations_assignee_idx on public.conversations(assignee_id);

-- ---------- messages ----------
create table if not exists public.messages (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  conversation_id uuid not null references public.conversations(id) on delete cascade,
  sender_id uuid references public.profiles(id) on delete set null,
  direction message_direction not null,
  kind message_kind not null default 'text',
  body text,
  media_url text,
  is_private boolean not null default false,   -- nota interna
  provider_message_id text,
  delivered_at timestamptz,
  read_at timestamptz,
  created_at timestamptz not null default now()
);
create index if not exists messages_conversation_idx on public.messages(conversation_id, created_at desc);

-- ---------- automations / chatbot ----------
create table if not exists public.automations (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  name text not null,
  trigger_type text not null,
  is_active boolean not null default true,
  flow jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default now()
);

-- ---------- api_keys (StarGestor / StarLaudo) ----------
create table if not exists public.api_keys (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  name text not null,
  key_hash text not null,
  scopes text[] not null default '{}',
  last_used_at timestamptz,
  revoked_at timestamptz,
  created_at timestamptz not null default now()
);

-- ---------- webhooks ----------
create table if not exists public.webhooks (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  target_url text not null,
  events text[] not null default '{}',
  secret text not null default encode(gen_random_bytes(24), 'hex'),
  is_active boolean not null default true,
  last_status int,
  created_at timestamptz not null default now()
);

-- ============================================================
-- GRANTS (obrigatorio: PostgREST nao concede por padrao)
-- ============================================================
grant select on public.organizations to authenticated;
grant all on public.organizations to service_role;

grant select, insert, update on public.profiles to authenticated;
grant all on public.profiles to service_role;

grant select on public.user_roles to authenticated;
grant all on public.user_roles to service_role;

grant select, insert, update, delete on public.departments to authenticated;
grant all on public.departments to service_role;

grant select, insert, update, delete on public.whatsapp_instances to authenticated;
grant all on public.whatsapp_instances to service_role;

grant select, insert, update, delete on public.contacts to authenticated;
grant all on public.contacts to service_role;

grant select, insert, update, delete on public.conversations to authenticated;
grant all on public.conversations to service_role;

grant select, insert, update, delete on public.messages to authenticated;
grant all on public.messages to service_role;

grant select, insert, update, delete on public.automations to authenticated;
grant all on public.automations to service_role;

grant select, insert, update, delete on public.api_keys to authenticated;
grant all on public.api_keys to service_role;

grant select, insert, update, delete on public.webhooks to authenticated;
grant all on public.webhooks to service_role;

-- ============================================================
-- Funcoes de seguranca (security definer, evitam recursao em RLS)
-- ============================================================
create or replace function public.has_role(_user_id uuid, _role app_role)
returns boolean language sql stable security definer set search_path = public as $$
  select exists (select 1 from public.user_roles where user_id = _user_id and role = _role);
$$;

create or replace function public.current_org()
returns uuid language sql stable security definer set search_path = public as $$
  select organization_id from public.profiles where id = auth.uid();
$$;

-- ============================================================
-- RLS
-- ============================================================
alter table public.organizations      enable row level security;
alter table public.profiles           enable row level security;
alter table public.user_roles         enable row level security;
alter table public.departments        enable row level security;
alter table public.whatsapp_instances enable row level security;
alter table public.contacts           enable row level security;
alter table public.conversations      enable row level security;
alter table public.messages           enable row level security;
alter table public.automations        enable row level security;
alter table public.api_keys           enable row level security;
alter table public.webhooks           enable row level security;

create policy "org members read org" on public.organizations
  for select to authenticated using (id = public.current_org());

create policy "read profiles of my org" on public.profiles
  for select to authenticated using (organization_id = public.current_org());
create policy "update own profile" on public.profiles
  for update to authenticated using (id = auth.uid());

create policy "read my roles" on public.user_roles
  for select to authenticated using (user_id = auth.uid() or organization_id = public.current_org());

-- Tabelas operacionais: isolamento por tenant
do $$
declare t text;
begin
  foreach t in array array['departments','whatsapp_instances','contacts','conversations','messages','automations','webhooks']
  loop
    execute format($f$
      create policy "tenant read %1$s" on public.%1$s
        for select to authenticated using (organization_id = public.current_org());
      create policy "tenant write %1$s" on public.%1$s
        for insert to authenticated with check (organization_id = public.current_org());
      create policy "tenant update %1$s" on public.%1$s
        for update to authenticated using (organization_id = public.current_org());
      create policy "tenant delete %1$s" on public.%1$s
        for delete to authenticated using (organization_id = public.current_org()
          and (public.has_role(auth.uid(),'admin') or public.has_role(auth.uid(),'supervisor')));
    $f$, t);
  end loop;
end $$;

-- Credenciais sensiveis: somente admin do tenant
create policy "admin manage api keys" on public.api_keys
  for all to authenticated
  using (organization_id = public.current_org() and public.has_role(auth.uid(),'admin'))
  with check (organization_id = public.current_org() and public.has_role(auth.uid(),'admin'));

-- ============================================================
-- Realtime (Live Chat)
-- ============================================================
alter publication supabase_realtime add table public.messages;
alter publication supabase_realtime add table public.conversations;