BEGIN;

CREATE EXTENSION vector;
CREATE EXTENSION moddatetime;
CREATE EXTENSION insert_username;

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_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;
SET default_tablespace = '';
SET default_table_access_method = heap;

CREATE TABLE public.files (
    files_id integer NOT NULL,
    files_uuid uuid DEFAULT gen_random_uuid() NOT NULL,
    files_datecreated timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    files_datemodified timestamp with time zone,
    files_usercreated text DEFAULT CURRENT_USER NOT NULL,
    files_usermodified text DEFAULT CURRENT_USER NOT NULL,
    files_name text NOT NULL,
    files_description text,
    files_plain text,
    files_summary text
);

COMMENT ON TABLE public.files IS 'Files';
COMMENT ON COLUMN public.files.files_id IS 'ID';
COMMENT ON COLUMN public.files.files_uuid IS 'UUID';
COMMENT ON COLUMN public.files.files_datecreated IS 'Date created';
COMMENT ON COLUMN public.files.files_datemodified IS 'Date modified';
COMMENT ON COLUMN public.files.files_usercreated IS 'User created';
COMMENT ON COLUMN public.files.files_usermodified IS 'User modified';
COMMENT ON COLUMN public.files.files_name IS 'File name';
COMMENT ON COLUMN public.files.files_description IS 'Description';
COMMENT ON COLUMN public.files.files_plain IS 'Plain text';
COMMENT ON COLUMN public.files.files_summary IS 'Summary';

CREATE SEQUENCE public.files_files_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.files_files_id_seq OWNED BY public.files.files_id;

ALTER TABLE ONLY public.files ALTER COLUMN files_id SET DEFAULT nextval('public.files_files_id_seq'::regclass);

ALTER TABLE ONLY public.files
    ADD CONSTRAINT files_files_uuid_key UNIQUE (files_uuid);

ALTER TABLE ONLY public.files
    ADD CONSTRAINT files_name_unique UNIQUE (files_name);

ALTER TABLE ONLY public.files
    ADD CONSTRAINT files_pkey PRIMARY KEY (files_id);

CREATE TRIGGER files_moddatetime BEFORE UPDATE ON public.files FOR EACH ROW EXECUTE FUNCTION public.moddatetime('files_datemodified');
CREATE TRIGGER insert_username_files BEFORE INSERT OR UPDATE ON public.files FOR EACH ROW EXECUTE FUNCTION public.insert_username('files_usermodified');

CREATE TABLE public.embeddingtypes (
    embeddingtypes_id integer NOT NULL,
    embeddingtypes_uuid uuid DEFAULT gen_random_uuid() NOT NULL,
    embeddingtypes_datecreated timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    embeddingtypes_datemodified timestamp with time zone,
    embeddingtypes_usercreated text DEFAULT CURRENT_USER NOT NULL,
    embeddingtypes_usermodified text DEFAULT CURRENT_USER NOT NULL,
    embeddingtypes_name text NOT NULL,
    embeddingtypes_description text,
    embeddingtypes_table text DEFAULT 'PLACEHOLDER TABLE'::text NOT NULL
);

COMMENT ON TABLE public.embeddingtypes IS 'Embeddings types';
COMMENT ON COLUMN public.embeddingtypes.embeddingtypes_id IS 'ID';
COMMENT ON COLUMN public.embeddingtypes.embeddingtypes_uuid IS 'UUID';
COMMENT ON COLUMN public.embeddingtypes.embeddingtypes_datecreated IS 'Date created';
COMMENT ON COLUMN public.embeddingtypes.embeddingtypes_datemodified IS 'Date modified';
COMMENT ON COLUMN public.embeddingtypes.embeddingtypes_usercreated IS 'User created';
COMMENT ON COLUMN public.embeddingtypes.embeddingtypes_usermodified IS 'User modified';
COMMENT ON COLUMN public.embeddingtypes.embeddingtypes_name IS 'Embeddings type';
COMMENT ON COLUMN public.embeddingtypes.embeddingtypes_description IS 'Description';
COMMENT ON COLUMN public.embeddingtypes.embeddingtypes_table IS 'Table';

CREATE SEQUENCE public.embeddingtypes_embeddingtypes_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.embeddingtypes_embeddingtypes_id_seq OWNED BY public.embeddingtypes.embeddingtypes_id;

ALTER TABLE ONLY public.embeddingtypes ALTER COLUMN embeddingtypes_id SET DEFAULT nextval('public.embeddingtypes_embeddingtypes_id_seq'::regclass);

ALTER TABLE ONLY public.embeddingtypes
    ADD CONSTRAINT embeddingtypes_embeddingtypes_uuid_key UNIQUE (embeddingtypes_uuid);

ALTER TABLE ONLY public.embeddingtypes
    ADD CONSTRAINT embeddingtypes_pkey PRIMARY KEY (embeddingtypes_id);

CREATE TRIGGER embeddingtypes_moddatetime BEFORE UPDATE ON public.embeddingtypes FOR EACH ROW EXECUTE FUNCTION public.moddatetime('embeddingtypes_datemodified');

CREATE TRIGGER insert_username_embeddingtypes BEFORE INSERT OR UPDATE ON public.embeddingtypes FOR EACH ROW EXECUTE FUNCTION public.insert_username('embeddingtypes_usermodified');

CREATE TABLE public.embeddings (
    embeddings_id integer NOT NULL,
    embeddings_uuid uuid DEFAULT gen_random_uuid() NOT NULL,
    embeddings_datecreated timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    embeddings_datemodified timestamp with time zone,
    embeddings_usercreated text DEFAULT CURRENT_USER NOT NULL,
    embeddings_usermodified text DEFAULT CURRENT_USER NOT NULL,
    embeddings_name text GENERATED ALWAYS AS ('embedding_' || embeddings_id) STORED,
    embeddings_description text,
    embeddings_embeddings public.vector(768) NOT NULL,
    embeddings_chunkid integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
    embeddings_embeddingtypes integer NOT NULL,
    embeddings_referencedid integer,
    embeddings_referenceduuid uuid,
    embeddings_text text NOT NULL,
    embeddings_files integer,
    embeddings_pagenr integer
);

COMMENT ON TABLE public.embeddings IS 'Embeddings';
COMMENT ON COLUMN public.embeddings.embeddings_id IS 'ID';
COMMENT ON COLUMN public.embeddings.embeddings_uuid IS 'UUID';
COMMENT ON COLUMN public.embeddings.embeddings_datecreated IS 'Date created';
COMMENT ON COLUMN public.embeddings.embeddings_datemodified IS 'Date modified';
COMMENT ON COLUMN public.embeddings.embeddings_usercreated IS 'User created';
COMMENT ON COLUMN public.embeddings.embeddings_usermodified IS 'User modified';
COMMENT ON COLUMN public.embeddings.embeddings_name IS 'Name';
COMMENT ON COLUMN public.embeddings.embeddings_description IS 'Description';
COMMENT ON COLUMN public.embeddings.embeddings_embeddings IS 'Embeddings';
COMMENT ON COLUMN public.embeddings.embeddings_chunkid IS 'Chunk ID';
COMMENT ON COLUMN public.embeddings.embeddings_embeddingtypes IS 'Type';
COMMENT ON COLUMN public.embeddings.embeddings_referencedid IS 'Referenced ID';
COMMENT ON COLUMN public.embeddings.embeddings_referenceduuid IS 'Referenced UUID';
COMMENT ON COLUMN public.embeddings.embeddings_text IS 'Text';
COMMENT ON COLUMN public.embeddings.embeddings_files IS 'File';
COMMENT ON COLUMN public.embeddings.embeddings_pagenr IS 'Page number';

CREATE SEQUENCE public.embeddings_embeddings_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.embeddings_embeddings_id_seq OWNED BY public.embeddings.embeddings_id;
ALTER TABLE ONLY public.embeddings ALTER COLUMN embeddings_id SET DEFAULT nextval('public.embeddings_embeddings_id_seq'::regclass);

ALTER TABLE ONLY public.embeddings
    ADD CONSTRAINT embeddings_embeddings_uuid_key UNIQUE (embeddings_uuid);

ALTER TABLE ONLY public.embeddings
    ADD CONSTRAINT embeddings_pkey PRIMARY KEY (embeddings_id);

CREATE INDEX embeddings_embeddings_embeddings_idx ON public.embeddings USING ivfflat (embeddings_embeddings public.vector_cosine_ops) WITH (lists='100');

CREATE INDEX idx_embeddings_embeddingtypes ON public.embeddings USING btree (embeddings_embeddingtypes);

CREATE INDEX idx_embeddings_file_type ON public.embeddings USING btree (embeddings_files, embeddings_embeddingtypes);

CREATE INDEX idx_embeddings_files ON public.embeddings USING btree (embeddings_files);

CREATE INDEX idx_embeddings_name ON public.embeddings USING btree (embeddings_name);

CREATE INDEX idx_embeddings_referencedid ON public.embeddings USING btree (embeddings_referencedid);

CREATE INDEX idx_embeddings_referenceduuid ON public.embeddings USING btree (embeddings_referenceduuid);

CREATE TRIGGER embeddings_moddatetime BEFORE UPDATE ON public.embeddings FOR EACH ROW EXECUTE FUNCTION public.moddatetime('embeddings_datemodified');

CREATE TRIGGER insert_username_embeddings BEFORE INSERT OR UPDATE ON public.embeddings FOR EACH ROW EXECUTE FUNCTION public.insert_username('embeddings_usermodified');

ALTER TABLE ONLY public.embeddings
    ADD CONSTRAINT embeddings_embeddings_files_fkey FOREIGN KEY (embeddings_files) REFERENCES public.files(files_id);

ALTER TABLE ONLY public.embeddings
    ADD CONSTRAINT embeddings_embeddingtypes_fkey FOREIGN KEY (embeddings_embeddingtypes) REFERENCES public.embeddingtypes(embeddingtypes_id);

INSERT INTO public.embeddingtypes (
    embeddingtypes_name,
    embeddingtypes_description,
    embeddingtypes_table
) VALUES
    ('Elementary objects', NULL, 'hyobjects'),
    ('People', NULL, 'people'),
    ('Files', NULL, 'files'),
    ('LLM Responses', NULL, 'llm'),
    ('Speech', NULL, 'speech'),
    ('Org Mode Headings', NULL, 'org'),
    ('Emacs Lisp', NULL, 'elisp');

COMMIT;
