agenciapsilmno/database-novo/schema/04_tables/core.sql

-- =============================================================================
-- AgenciaPsi — Tables — Core
-- =============================================================================
-- profiles, tenants, tenant_members, tenant_invites, tenant_features,
-- tenant_feature_exceptions_log, saas_admins, owner_users, user_settings,
-- company_profiles, dev_user_credentials
-- =============================================================================

CREATE TABLE public.profiles (
    id uuid NOT NULL,
    role text DEFAULT 'tenant_member'::text NOT NULL,
    full_name text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    avatar_url text,
    phone text,
    bio text,
    language text DEFAULT 'pt-BR'::text NOT NULL,
    timezone text DEFAULT 'America/Sao_Paulo'::text NOT NULL,
    notify_system_email boolean DEFAULT true NOT NULL,
    notify_reminders boolean DEFAULT true NOT NULL,
    notify_news boolean DEFAULT false NOT NULL,
    account_type text DEFAULT 'free'::text NOT NULL,
    platform_roles text[] DEFAULT '{}'::text[] NOT NULL,
    nickname text,
    work_description text,
    work_description_other text,
    site_url text,
    social_instagram text,
    social_youtube text,
    social_facebook text,
    social_x text,
    social_custom jsonb DEFAULT '[]'::jsonb NOT NULL,
    CONSTRAINT profiles_account_type_check CHECK ((account_type = ANY (ARRAY['free'::text, 'patient'::text, 'therapist'::text, 'clinic'::text]))),
    CONSTRAINT profiles_role_check CHECK ((role = ANY (ARRAY['saas_admin'::text, 'tenant_member'::text, 'portal_user'::text, 'patient'::text])))
);



CREATE TABLE public.tenants (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    kind text DEFAULT 'saas'::text NOT NULL,
    CONSTRAINT tenants_kind_check CHECK ((kind = ANY (ARRAY['therapist'::text, 'clinic_coworking'::text, 'clinic_reception'::text, 'clinic_full'::text, 'clinic'::text, 'saas'::text, 'supervisor'::text])))
);



CREATE TABLE public.tenant_members (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    tenant_id uuid NOT NULL,
    user_id uuid NOT NULL,
    role text NOT NULL,
    status text DEFAULT 'active'::text NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);



CREATE TABLE public.tenant_invites (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    tenant_id uuid NOT NULL,
    email text NOT NULL,
    role text NOT NULL,
    token uuid DEFAULT gen_random_uuid() NOT NULL,
    invited_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    expires_at timestamp with time zone DEFAULT (now() + '7 days'::interval) NOT NULL,
    accepted_at timestamp with time zone,
    accepted_by uuid,
    revoked_at timestamp with time zone,
    revoked_by uuid,
    CONSTRAINT tenant_invites_role_check CHECK ((role = ANY (ARRAY['therapist'::text, 'secretary'::text])))
);



CREATE TABLE public.tenant_features (
    tenant_id uuid NOT NULL,
    feature_key text NOT NULL,
    enabled boolean DEFAULT false NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);



CREATE TABLE public.tenant_feature_exceptions_log (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    tenant_id uuid NOT NULL,
    feature_key text NOT NULL,
    enabled boolean NOT NULL,
    reason text,
    created_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);



CREATE TABLE public.saas_admins (
    user_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);



CREATE TABLE public.owner_users (
    owner_id uuid NOT NULL,
    user_id uuid NOT NULL,
    role text DEFAULT 'admin'::text NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);



CREATE TABLE public.user_settings (
    user_id uuid NOT NULL,
    theme_mode text DEFAULT 'dark'::text NOT NULL,
    preset text DEFAULT 'Aura'::text NOT NULL,
    primary_color text DEFAULT 'noir'::text NOT NULL,
    surface_color text DEFAULT 'slate'::text NOT NULL,
    menu_mode text DEFAULT 'static'::text NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    layout_variant text DEFAULT 'classic'::text NOT NULL,
    CONSTRAINT user_settings_layout_variant_check CHECK ((layout_variant = ANY (ARRAY['classic'::text, 'rail'::text]))),
    CONSTRAINT user_settings_menu_mode_check CHECK ((menu_mode = ANY (ARRAY['static'::text, 'overlay'::text]))),
    CONSTRAINT user_settings_theme_mode_check CHECK ((theme_mode = ANY (ARRAY['light'::text, 'dark'::text])))
);



CREATE TABLE public.company_profiles (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    tenant_id uuid NOT NULL,
    nome_fantasia text,
    razao_social text,
    tipo_empresa text,
    cnpj text,
    ie text,
    im text,
    cep text,
    logradouro text,
    numero text,
    complemento text,
    bairro text,
    cidade text,
    estado text,
    email text,
    telefone text,
    site text,
    logo_url text,
    redes_sociais jsonb DEFAULT '[]'::jsonb NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);



CREATE TABLE public.dev_user_credentials (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid,
    email text NOT NULL,
    password_dev text NOT NULL,
    kind text DEFAULT 'custom'::text NOT NULL,
    note text,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);