dump.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.4 (Debian 15.4-1.pgdg120+1)
-- Dumped by pg_dump version 15.4 (Debian 15.4-1.pgdg120+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: bot; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA bot;


ALTER SCHEMA bot OWNER TO postgres;

--
-- Name: facebook; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA facebook;


ALTER SCHEMA facebook OWNER TO postgres;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: pg_database_owner
--

COMMENT ON SCHEMA public IS '';


--
-- Name: vector; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA public;


--
-- Name: EXTENSION vector; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION vector IS 'vector data type and ivfflat access method';


--
-- Name: chat_sender; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE public.chat_sender AS ENUM (
    'bot',
    'agent',
    'user'
);


ALTER TYPE public.chat_sender OWNER TO postgres;

--
-- Name: supported_platforms; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE public.supported_platforms AS ENUM (
    'instagram',
    'facebook'
);


ALTER TYPE public.supported_platforms OWNER TO postgres;

--
-- Name: check_dependencies_on_delete(); Type: FUNCTION; Schema: bot; Owner: postgres
--

CREATE FUNCTION bot.check_dependencies_on_delete() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  -- Check if any elements in the dependencies array match ID being deleted.
  IF EXISTS (SELECT 1 FROM bot.templates WHERE OLD.id = ANY(dependencies)) THEN
    RAISE EXCEPTION 'Cannot delete this row because it is referenced by other rows.';
  END IF;

  -- If there are no conflicts, allow the delete operation.
  RETURN OLD;
END;
$$;


ALTER FUNCTION bot.check_dependencies_on_delete() OWNER TO postgres;

--
-- Name: check_trigger_updated(); Type: FUNCTION; Schema: bot; Owner: postgres
--

CREATE FUNCTION bot.check_trigger_updated() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  -- Check if the new trigger_text is equal to the old trigger_text
  IF NEW.text != OLD.text THEN
    -- Set the update_vec value to true
    NEW.embedding := NULL;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION bot.check_trigger_updated() OWNER TO postgres;

--
-- Name: upsert_bot_action(uuid, uuid, jsonb, text); Type: FUNCTION; Schema: bot; Owner: postgres
--

CREATE FUNCTION bot.upsert_bot_action(p_intent uuid, p_assistant uuid, p_data jsonb, p_user text, OUT action_id uuid, OUT operation text) RETURNS SETOF record
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- Check if the user owns it
    IF EXISTS (
        SELECT 1
        FROM bot.intents i
        JOIN assistants a ON a.id = p_assistant
        WHERE i.id = p_intent 
        AND (i.assistant = p_assistant OR i.assistant IS NULL) 
        AND a.owner = p_user
    ) THEN
        -- Check if the action record exists
        IF EXISTS (
            SELECT ba.id
            FROM bot.actions ba
            WHERE ba.assistant = p_assistant AND ba.intent = p_intent
        ) THEN
            -- Update the existing action record
            UPDATE bot.actions
            SET data = p_data
            WHERE intent = p_intent AND assistant = p_assistant
            RETURNING id INTO action_id;
            operation := 'update';  -- Set the operation to 'update'
        ELSE
            -- Insert a new action record
            INSERT INTO bot.actions (intent, assistant, data)
            VALUES (p_intent, p_assistant, p_data)
            RETURNING id INTO action_id;
            operation := 'insert';  -- Set the operation to 'insert'
        END IF;
    ELSE
        RAISE EXCEPTION 'Not found';
    END IF;
    
    -- Return the action_id and operation
    RETURN NEXT;
END;
$$;


ALTER FUNCTION bot.upsert_bot_action(p_intent uuid, p_assistant uuid, p_data jsonb, p_user text, OUT action_id uuid, OUT operation text) OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: actions; Type: TABLE; Schema: bot; Owner: postgres
--

CREATE TABLE bot.actions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    intent uuid,
    data jsonb,
    assistant uuid
);


ALTER TABLE bot.actions OWNER TO postgres;

--
-- Name: intents; Type: TABLE; Schema: bot; Owner: postgres
--

CREATE TABLE bot.intents (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    text text NOT NULL,
    assistant uuid,
    data jsonb,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    template character varying(128),
    archived boolean DEFAULT false
);


ALTER TABLE bot.intents OWNER TO postgres;

--
-- Name: templates; Type: TABLE; Schema: bot; Owner: postgres
--

CREATE TABLE bot.templates (
    name text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    dependencies character varying(128)[] DEFAULT '{}'::character varying(128)[],
    "primary" boolean DEFAULT false,
    id character varying(128) NOT NULL
);


ALTER TABLE bot.templates OWNER TO postgres;

--
-- Name: triggers; Type: TABLE; Schema: bot; Owner: postgres
--

CREATE TABLE bot.triggers (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    intent uuid,
    text text NOT NULL,
    embedding public.vector(1536)
);


ALTER TABLE bot.triggers OWNER TO postgres;

--
-- Name: accounts; Type: TABLE; Schema: facebook; Owner: postgres
--

CREATE TABLE facebook.accounts (
    id bigint NOT NULL,
    "user" text NOT NULL,
    name text,
    picture text,
    businesses jsonb[] DEFAULT '{}'::jsonb[] NOT NULL,
    permissions text[] DEFAULT '{}'::text[] NOT NULL,
    user_token text NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now()
);


ALTER TABLE facebook.accounts OWNER TO postgres;

--
-- Name: pages; Type: TABLE; Schema: facebook; Owner: postgres
--

CREATE TABLE facebook.pages (
    id bigint NOT NULL,
    insta_id bigint,
    "user" text NOT NULL,
    assistant uuid,
    name text,
    picture text,
    about text,
    business text NOT NULL,
    category text,
    insta_data jsonb,
    page_token text NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    account bigint NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    subscribed boolean DEFAULT false NOT NULL
);


ALTER TABLE facebook.pages OWNER TO postgres;

--
-- Name: assistants; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.assistants (
    owner text NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    name text NOT NULL,
    config jsonb,
    basic_info jsonb,
    category text NOT NULL,
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    delete_at timestamp with time zone,
    chat_templates character varying(128)[] DEFAULT '{default}'::character varying[] NOT NULL,
    active boolean DEFAULT true,
    archived boolean DEFAULT false
);


ALTER TABLE public.assistants OWNER TO postgres;

--
-- Name: chats; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.chats (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    sender public.chat_sender,
    data jsonb,
    sent_at timestamp with time zone DEFAULT now(),
    room uuid NOT NULL
);


ALTER TABLE public.chats OWNER TO postgres;

--
-- Name: conversation_rooms; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.conversation_rooms (
    data jsonb DEFAULT '{}'::jsonb,
    platform public.supported_platforms NOT NULL,
    receiver text NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    name text,
    email text,
    username text,
    cooldown bigint DEFAULT 0,
    blacklisted boolean DEFAULT false NOT NULL,
    assistant uuid,
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    sender text NOT NULL,
    picture text,
    updated_at timestamp with time zone DEFAULT now()
);


ALTER TABLE public.conversation_rooms OWNER TO postgres;

--
-- Name: invoices; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.invoices (
    currency text NOT NULL,
    subscription text NOT NULL,
    "user" text NOT NULL,
    status text NOT NULL,
    total numeric DEFAULT 0 NOT NULL,
    billing_reason text NOT NULL,
    hosted_invoice_url text,
    invoice_number text,
    paid boolean DEFAULT false,
    created bigint,
    id text NOT NULL,
    plan text NOT NULL
);


ALTER TABLE public.invoices OWNER TO postgres;

--
-- Name: plans; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.plans (
    name text NOT NULL,
    tokens numeric(10,2) DEFAULT 0 NOT NULL,
    id text NOT NULL
);


ALTER TABLE public.plans OWNER TO postgres;

--
-- Name: subscriptions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.subscriptions (
    status text NOT NULL,
    billing_cycle_anchor bigint NOT NULL,
    current_period_start bigint NOT NULL,
    current_period_end bigint NOT NULL,
    next_invoice_at bigint,
    upcoming_invoice jsonb,
    cancel_at bigint,
    latest_invoice text,
    created bigint NOT NULL,
    id text NOT NULL,
    "user" text NOT NULL,
    plan text NOT NULL
);


ALTER TABLE public.subscriptions OWNER TO postgres;

--
-- Name: usage; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.usage (
    token_used smallint DEFAULT 0,
    data jsonb,
    created_at timestamp with time zone DEFAULT now(),
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    "user" text NOT NULL,
    assistant uuid NOT NULL
);


ALTER TABLE public.usage OWNER TO postgres;

--
-- Name: users; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.users (
    id text NOT NULL,
    email text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    stripe_customer text,
    next_billing_cycle bigint,
    subscription text,
    credits numeric(10,2) DEFAULT 0
);


ALTER TABLE public.users OWNER TO postgres;

--
-- Name: actions actions_pkey; Type: CONSTRAINT; Schema: bot; Owner: postgres
--

ALTER TABLE ONLY bot.actions
    ADD CONSTRAINT actions_pkey PRIMARY KEY (id);


--
-- Name: intents intents_pkey; Type: CONSTRAINT; Schema: bot; Owner: postgres
--

ALTER TABLE ONLY bot.intents
    ADD CONSTRAINT intents_pkey PRIMARY KEY (id);


--
-- Name: templates templates_pkey; Type: CONSTRAINT; Schema: bot; Owner: postgres
--

ALTER TABLE ONLY bot.templates
    ADD CONSTRAINT templates_pkey PRIMARY KEY (id);


--
-- Name: triggers triggers_pkey; Type: CONSTRAINT; Schema: bot; Owner: postgres
--

ALTER TABLE ONLY bot.triggers
    ADD CONSTRAINT triggers_pkey PRIMARY KEY (id);


--
-- Name: actions unique_intent_assistant; Type: CONSTRAINT; Schema: bot; Owner: postgres
--

ALTER TABLE ONLY bot.actions
    ADD CONSTRAINT unique_intent_assistant UNIQUE (intent, assistant);


--
-- Name: accounts accounts_pkey; Type: CONSTRAINT; Schema: facebook; Owner: postgres
--

ALTER TABLE ONLY facebook.accounts
    ADD CONSTRAINT accounts_pkey PRIMARY KEY (id);


--
-- Name: accounts accounts_un; Type: CONSTRAINT; Schema: facebook; Owner: postgres
--

ALTER TABLE ONLY facebook.accounts
    ADD CONSTRAINT accounts_un UNIQUE ("user");


--
-- Name: pages pages_pkey; Type: CONSTRAINT; Schema: facebook; Owner: postgres
--

ALTER TABLE ONLY facebook.pages
    ADD CONSTRAINT pages_pkey PRIMARY KEY (id);


--
-- Name: pages pages_un; Type: CONSTRAINT; Schema: facebook; Owner: postgres
--

ALTER TABLE ONLY facebook.pages
    ADD CONSTRAINT pages_un UNIQUE (assistant);


--
-- Name: assistants assistants_pk; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.assistants
    ADD CONSTRAINT assistants_pk PRIMARY KEY (id);


--
-- Name: chats chats_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.chats
    ADD CONSTRAINT chats_pkey PRIMARY KEY (id);


--
-- Name: conversation_rooms conversation_rooms_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.conversation_rooms
    ADD CONSTRAINT conversation_rooms_pkey PRIMARY KEY (id);


--
-- Name: invoices invoices_pk; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.invoices
    ADD CONSTRAINT invoices_pk PRIMARY KEY (id);


--
-- Name: plans plans_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.plans
    ADD CONSTRAINT plans_pkey PRIMARY KEY (id);


--
-- Name: subscriptions subscriptions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.subscriptions
    ADD CONSTRAINT subscriptions_pkey PRIMARY KEY (id);


--
-- Name: usage usage_history_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.usage
    ADD CONSTRAINT usage_history_pkey PRIMARY KEY (id);


--
-- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_email_key UNIQUE (email);


--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- Name: users users_stripe_customer_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_stripe_customer_key UNIQUE (stripe_customer);


--
-- Name: triggers_embedding_idx; Type: INDEX; Schema: bot; Owner: postgres
--

CREATE INDEX triggers_embedding_idx ON bot.triggers USING hnsw (embedding public.vector_cosine_ops);


--
-- Name: templates check_dependencies_trigger; Type: TRIGGER; Schema: bot; Owner: postgres
--

CREATE TRIGGER check_dependencies_trigger BEFORE DELETE ON bot.templates FOR EACH ROW EXECUTE FUNCTION bot.check_dependencies_on_delete();


--
-- Name: triggers check_trigger_updated; Type: TRIGGER; Schema: bot; Owner: postgres
--

CREATE TRIGGER check_trigger_updated AFTER UPDATE ON bot.triggers FOR EACH ROW EXECUTE FUNCTION bot.check_trigger_updated();


--
-- Name: actions actions_assistant_fkey; Type: FK CONSTRAINT; Schema: bot; Owner: postgres
--

ALTER TABLE ONLY bot.actions
    ADD CONSTRAINT actions_assistant_fkey FOREIGN KEY (assistant) REFERENCES public.assistants(id) ON DELETE CASCADE;


--
-- Name: actions actions_intent_fkey; Type: FK CONSTRAINT; Schema: bot; Owner: postgres
--

ALTER TABLE ONLY bot.actions
    ADD CONSTRAINT actions_intent_fkey FOREIGN KEY (intent) REFERENCES bot.intents(id) ON DELETE CASCADE;


--
-- Name: intents intents_assistant_fkey; Type: FK CONSTRAINT; Schema: bot; Owner: postgres
--

ALTER TABLE ONLY bot.intents
    ADD CONSTRAINT intents_assistant_fkey FOREIGN KEY (assistant) REFERENCES public.assistants(id) ON DELETE CASCADE;


--
-- Name: intents intents_template_fkey; Type: FK CONSTRAINT; Schema: bot; Owner: postgres
--

ALTER TABLE ONLY bot.intents
    ADD CONSTRAINT intents_template_fkey FOREIGN KEY (template) REFERENCES bot.templates(id);


--
-- Name: triggers triggers_intent_fkey; Type: FK CONSTRAINT; Schema: bot; Owner: postgres
--

ALTER TABLE ONLY bot.triggers
    ADD CONSTRAINT triggers_intent_fkey FOREIGN KEY (intent) REFERENCES bot.intents(id) ON DELETE CASCADE;


--
-- Name: accounts accounts_user_fkey; Type: FK CONSTRAINT; Schema: facebook; Owner: postgres
--

ALTER TABLE ONLY facebook.accounts
    ADD CONSTRAINT accounts_user_fkey FOREIGN KEY ("user") REFERENCES public.users(id) ON DELETE CASCADE;


--
-- Name: pages pages_account_fkey; Type: FK CONSTRAINT; Schema: facebook; Owner: postgres
--

ALTER TABLE ONLY facebook.pages
    ADD CONSTRAINT pages_account_fkey FOREIGN KEY (account) REFERENCES facebook.accounts(id) ON DELETE CASCADE;


--
-- Name: pages pages_assistant_fkey; Type: FK CONSTRAINT; Schema: facebook; Owner: postgres
--

ALTER TABLE ONLY facebook.pages
    ADD CONSTRAINT pages_assistant_fkey FOREIGN KEY (assistant) REFERENCES public.assistants(id) ON DELETE SET NULL;


--
-- Name: pages pages_user_fkey; Type: FK CONSTRAINT; Schema: facebook; Owner: postgres
--

ALTER TABLE ONLY facebook.pages
    ADD CONSTRAINT pages_user_fkey FOREIGN KEY ("user") REFERENCES public.users(id) ON DELETE CASCADE;


--
-- Name: assistants assistants_owner_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.assistants
    ADD CONSTRAINT assistants_owner_fkey FOREIGN KEY (owner) REFERENCES public.users(id) ON DELETE CASCADE;


--
-- Name: chats chats_room_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.chats
    ADD CONSTRAINT chats_room_fkey FOREIGN KEY (room) REFERENCES public.conversation_rooms(id) ON DELETE CASCADE;


--
-- Name: conversation_rooms conversation_rooms_assistant_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.conversation_rooms
    ADD CONSTRAINT conversation_rooms_assistant_fkey FOREIGN KEY (assistant) REFERENCES public.assistants(id) ON DELETE CASCADE;


--
-- Name: invoices invoices_plan_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.invoices
    ADD CONSTRAINT invoices_plan_fkey FOREIGN KEY (plan) REFERENCES public.plans(id);


--
-- Name: invoices invoices_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.invoices
    ADD CONSTRAINT invoices_user_fkey FOREIGN KEY ("user") REFERENCES public.users(id);


--
-- Name: subscriptions subscriptions_plan_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.subscriptions
    ADD CONSTRAINT subscriptions_plan_fkey FOREIGN KEY (plan) REFERENCES public.plans(id);


--
-- Name: subscriptions subscriptions_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.subscriptions
    ADD CONSTRAINT subscriptions_user_fkey FOREIGN KEY ("user") REFERENCES public.users(id);


--
-- Name: usage usage_assistant_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.usage
    ADD CONSTRAINT usage_assistant_fkey FOREIGN KEY (assistant) REFERENCES public.assistants(id) ON DELETE CASCADE;


--
-- Name: usage usage_history_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.usage
    ADD CONSTRAINT usage_history_user_fkey FOREIGN KEY ("user") REFERENCES public.users(id);


--
-- Name: SCHEMA public; Type: ACL; Schema: -; Owner: pg_database_owner
--

REVOKE USAGE ON SCHEMA public FROM PUBLIC;


--
-- PostgreSQL database dump complete
--