agenciapsilmno/database-novo/migrations/20260521000004_specialties.sql

-- ============================================================================
-- Compliance CFP — Especialidades do profissional (ROADMAP item #9)
-- ----------------------------------------------------------------------------
-- Catálogo de especialidades/abordagens + join many-to-many com profiles.
-- Profissional pode ter múltiplas especialidades (clínica + jurídica, etc).
-- ============================================================================

BEGIN;

CREATE TABLE IF NOT EXISTS public.specialties (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    key text UNIQUE NOT NULL,
    name text NOT NULL,
    category text,
    is_system boolean DEFAULT false NOT NULL,
    active boolean DEFAULT true NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);

COMMENT ON TABLE public.specialties IS
    'Catálogo global de especialidades/abordagens psicológicas (ROADMAP item #9). is_system=true pra entries seedadas.';

CREATE INDEX IF NOT EXISTS idx_specialties_active ON public.specialties (active, category, name);

CREATE TABLE IF NOT EXISTS public.profile_specialties (
    profile_id uuid NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
    specialty_id uuid NOT NULL REFERENCES public.specialties(id) ON DELETE RESTRICT,
    other_label text,
    created_at timestamptz DEFAULT now() NOT NULL,
    PRIMARY KEY (profile_id, specialty_id)
);

COMMENT ON TABLE public.profile_specialties IS
    'M:N entre profile e specialty. other_label preenchido só quando specialty.key=outra (custom user-defined).';

CREATE INDEX IF NOT EXISTS idx_profile_specialties_profile ON public.profile_specialties (profile_id);

-- ──────────────────────────────────────────────────────────────────────────
-- RLS
-- ──────────────────────────────────────────────────────────────────────────

ALTER TABLE public.specialties ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.profile_specialties ENABLE ROW LEVEL SECURITY;

-- specialties: read-only pra todos authenticated (catálogo público); só saas_admin escreve
CREATE POLICY specialties_authenticated_read
    ON public.specialties FOR SELECT TO authenticated
    USING (active = true);

CREATE POLICY specialties_saas_admin_write
    ON public.specialties TO authenticated
    USING (public.is_saas_admin())
    WITH CHECK (public.is_saas_admin());

-- profile_specialties: cada user gerencia o próprio
CREATE POLICY profile_specialties_owner_select
    ON public.profile_specialties FOR SELECT TO authenticated
    USING (profile_id = auth.uid());

CREATE POLICY profile_specialties_owner_insert
    ON public.profile_specialties FOR INSERT TO authenticated
    WITH CHECK (profile_id = auth.uid());

CREATE POLICY profile_specialties_owner_delete
    ON public.profile_specialties FOR DELETE TO authenticated
    USING (profile_id = auth.uid());

-- Tenant_admin pode VER specialties dos membros (pra cards públicos / perfil clínica)
CREATE POLICY profile_specialties_tenant_admin_read
    ON public.profile_specialties FOR SELECT TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM public.tenant_members tm
            WHERE tm.user_id = profile_specialties.profile_id
              AND public.is_tenant_admin(tm.tenant_id)
        )
    );

COMMIT;