205 lines
7.1 KiB
SQL
205 lines
7.1 KiB
SQL
-- =============================================================================
|
|
-- AgenciaPsi — Tables — SaaS Admin, FAQ, Docs, UI
|
|
-- =============================================================================
|
|
-- saas_docs, saas_doc_votos, saas_faq, saas_faq_itens,
|
|
-- feriados, global_notices, login_carousel_slides, notice_dismissals,
|
|
-- support_sessions
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE public.saas_doc_votos (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
doc_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
util boolean NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public.saas_doc_votos OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: saas_docs; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE TABLE public.saas_docs (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
titulo text NOT NULL,
|
|
conteudo text DEFAULT ''::text NOT NULL,
|
|
medias jsonb DEFAULT '[]'::jsonb NOT NULL,
|
|
tipo_acesso text DEFAULT 'usuario'::text NOT NULL,
|
|
pagina_path text NOT NULL,
|
|
docs_relacionados uuid[] DEFAULT '{}'::uuid[] NOT NULL,
|
|
ativo boolean DEFAULT true NOT NULL,
|
|
ordem integer DEFAULT 0 NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
categoria text,
|
|
exibir_no_faq boolean DEFAULT false NOT NULL,
|
|
votos_util integer DEFAULT 0 NOT NULL,
|
|
votos_nao_util integer DEFAULT 0 NOT NULL,
|
|
CONSTRAINT saas_docs_tipo_acesso_check CHECK ((tipo_acesso = ANY (ARRAY['admin'::text, 'usuario'::text])))
|
|
);
|
|
|
|
|
|
ALTER TABLE public.saas_docs OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: COLUMN saas_docs.categoria; Type: COMMENT; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
COMMENT ON COLUMN public.saas_docs.categoria IS 'Agrupa docs no portal FAQ (ex: Conta, Agenda, Pagamentos)';
|
|
|
|
|
|
--
|
|
-- Name: COLUMN saas_docs.exibir_no_faq; Type: COMMENT; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
COMMENT ON COLUMN public.saas_docs.exibir_no_faq IS 'Se true, a doc e seus itens FAQ aparecem no portal de FAQ';
|
|
|
|
|
|
--
|
|
-- Name: saas_faq; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE TABLE public.saas_faq (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
pergunta text NOT NULL,
|
|
categoria text,
|
|
publico boolean DEFAULT false NOT NULL,
|
|
votos integer DEFAULT 0 NOT NULL,
|
|
titulo text,
|
|
conteudo text,
|
|
tipo_acesso text DEFAULT 'usuario'::text NOT NULL,
|
|
pagina_path text NOT NULL,
|
|
pagina_label text,
|
|
medias jsonb DEFAULT '[]'::jsonb NOT NULL,
|
|
faqs_relacionados uuid[] DEFAULT '{}'::uuid[] NOT NULL,
|
|
ativo boolean DEFAULT true NOT NULL,
|
|
ordem integer DEFAULT 0 NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public.saas_faq OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: saas_faq_itens; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE TABLE public.saas_faq_itens (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
doc_id uuid NOT NULL,
|
|
pergunta text NOT NULL,
|
|
resposta text,
|
|
ordem integer DEFAULT 0 NOT NULL,
|
|
ativo boolean DEFAULT true NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public.saas_faq_itens OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: TABLE saas_faq_itens; Type: COMMENT; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
COMMENT ON TABLE public.saas_faq_itens IS 'Pares pergunta/resposta vinculados a um documento de ajuda';
|
|
|
|
|
|
--
|
|
-- Name: services; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
|
|
CREATE TABLE public.feriados (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
tenant_id uuid,
|
|
owner_id uuid,
|
|
tipo text DEFAULT 'municipal'::text NOT NULL,
|
|
nome text NOT NULL,
|
|
data date NOT NULL,
|
|
cidade text,
|
|
estado text,
|
|
observacao text,
|
|
bloqueia_sessoes boolean DEFAULT false NOT NULL,
|
|
criado_em timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT feriados_tipo_check CHECK ((tipo = ANY (ARRAY['municipal'::text, 'personalizado'::text])))
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.global_notices (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
title text,
|
|
message text DEFAULT ''::text NOT NULL,
|
|
variant text DEFAULT 'info'::text NOT NULL,
|
|
roles text[] DEFAULT '{}'::text[] NOT NULL,
|
|
contexts text[] DEFAULT '{}'::text[] NOT NULL,
|
|
starts_at timestamp with time zone,
|
|
ends_at timestamp with time zone,
|
|
is_active boolean DEFAULT true NOT NULL,
|
|
priority integer DEFAULT 0 NOT NULL,
|
|
dismissible boolean DEFAULT true NOT NULL,
|
|
persist_dismiss boolean DEFAULT true NOT NULL,
|
|
dismiss_scope text DEFAULT 'device'::text NOT NULL,
|
|
show_once boolean DEFAULT false NOT NULL,
|
|
max_views integer,
|
|
cooldown_minutes integer,
|
|
version integer DEFAULT 1 NOT NULL,
|
|
action_type text DEFAULT 'none'::text NOT NULL,
|
|
action_label text,
|
|
action_url text,
|
|
action_route text,
|
|
views_count integer DEFAULT 0 NOT NULL,
|
|
clicks_count integer DEFAULT 0 NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
created_by uuid,
|
|
content_align text DEFAULT 'left'::text NOT NULL,
|
|
link_target text DEFAULT '_blank'::text NOT NULL,
|
|
CONSTRAINT global_notices_action_type_check CHECK ((action_type = ANY (ARRAY['none'::text, 'internal'::text, 'external'::text]))),
|
|
CONSTRAINT global_notices_content_align_check CHECK ((content_align = ANY (ARRAY['left'::text, 'center'::text, 'right'::text, 'justify'::text]))),
|
|
CONSTRAINT global_notices_dismiss_scope_check CHECK ((dismiss_scope = ANY (ARRAY['session'::text, 'device'::text, 'user'::text]))),
|
|
CONSTRAINT global_notices_link_target_check CHECK ((link_target = ANY (ARRAY['_blank'::text, '_self'::text, '_parent'::text, '_top'::text]))),
|
|
CONSTRAINT global_notices_variant_check CHECK ((variant = ANY (ARRAY['info'::text, 'success'::text, 'warning'::text, 'error'::text])))
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.login_carousel_slides (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
title text NOT NULL,
|
|
body text NOT NULL,
|
|
icon text DEFAULT 'pi-star'::text NOT NULL,
|
|
ordem integer DEFAULT 0 NOT NULL,
|
|
ativo boolean DEFAULT true NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.notice_dismissals (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
notice_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
version integer DEFAULT 1 NOT NULL,
|
|
dismissed_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.support_sessions (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
tenant_id uuid NOT NULL,
|
|
admin_id uuid NOT NULL,
|
|
token text DEFAULT encode(extensions.gen_random_bytes(32), 'hex'::text) NOT NULL,
|
|
expires_at timestamp with time zone DEFAULT (now() + '01:00:00'::interval) NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|