unindexed.sql

--
-- PostgreSQL database dump
--

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

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: unindexed; Type: SCHEMA; Schema: -; Owner: testUser
--

CREATE SCHEMA unindexed;


ALTER SCHEMA unindexed OWNER TO "testUser";

--
-- Name: SCHEMA unindexed; Type: COMMENT; Schema: -; Owner: testUser
--

COMMENT ON SCHEMA unindexed IS 'standard unindexed schema';


--
-- Name: log_severity_enum; Type: TYPE; Schema: unindexed; Owner: testUser
--

CREATE TYPE unindexed.log_severity_enum AS ENUM (
    'INFO',
    'DEBUG',
    'VERBOSE',
    'WARNING',
    'ERROR',
    'CRITICAL'
);


ALTER TYPE unindexed.log_severity_enum OWNER TO "testUser";

--
-- Name: message_author_enum; Type: TYPE; Schema: unindexed; Owner: testUser
--

CREATE TYPE unindexed.message_author_enum AS ENUM (
    'user',
    'system',
    'bot'
);


ALTER TYPE unindexed.message_author_enum OWNER TO "testUser";

--
-- Name: task_editor_role_enum; Type: TYPE; Schema: unindexed; Owner: testUser
--

CREATE TYPE unindexed.task_editor_role_enum AS ENUM (
    'none',
    'editor',
    'owner'
);


ALTER TYPE unindexed.task_editor_role_enum OWNER TO "testUser";

--
-- Name: task_visibility_enum; Type: TYPE; Schema: unindexed; Owner: testUser
--

CREATE TYPE unindexed.task_visibility_enum AS ENUM (
    'unlisted',
    'editors_patient_only',
    'unindexed'
);


ALTER TYPE unindexed.task_visibility_enum OWNER TO "testUser";

--
-- Name: word_retrieval_hint_type_enum; Type: TYPE; Schema: unindexed; Owner: testUser
--

CREATE TYPE unindexed.word_retrieval_hint_type_enum AS ENUM (
    'message',
    'option_select'
);


ALTER TYPE unindexed.word_retrieval_hint_type_enum OWNER TO "testUser";

--
-- Name: word_retrieval_input_restriction; Type: TYPE; Schema: unindexed; Owner: testUser
--

CREATE TYPE unindexed.word_retrieval_input_restriction AS ENUM (
    'NONE',
    'TEXT_ONLY',
    'VOICE_ONLY'
);


ALTER TYPE unindexed.word_retrieval_input_restriction OWNER TO "testUser";

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: admin; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.admin (
                                 id uuid DEFAULT gen_random_uuid() NOT NULL,
                                 username text NOT NULL,
                                 hashed_password text NOT NULL
);


ALTER TABLE unindexed.admin OWNER TO "testUser";

--
-- Name: admin_session_token; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.admin_session_token (
                                               token character varying(256) NOT NULL,
                                               admin_id uuid NOT NULL,
                                               encrypted_client_info text NOT NULL,
                                               last_used timestamp(0) without time zone DEFAULT now() NOT NULL,
                                               created_at timestamp(0) without time zone DEFAULT now() NOT NULL,
                                               expiry_date timestamp(0) without time zone
);


ALTER TABLE unindexed.admin_session_token OWNER TO "testUser";

--
-- Name: enrollment_code; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.enrollment_code (
                                           code character varying(256) NOT NULL,
                                           staff_id uuid NOT NULL,
                                           patient_username text NOT NULL,
                                           created_at date DEFAULT now() NOT NULL
);


ALTER TABLE unindexed.enrollment_code OWNER TO "testUser";

--
-- Name: log; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.log (
                               id integer NOT NULL,
                               message text NOT NULL,
                               severity unindexed.log_severity_enum DEFAULT 'INFO'::unindexed.log_severity_enum NOT NULL,
                               at timestamp(0) without time zone DEFAULT now() NOT NULL
);


ALTER TABLE unindexed.log OWNER TO "testUser";

--
-- Name: log_id_seq; Type: SEQUENCE; Schema: unindexed; Owner: testUser
--

CREATE SEQUENCE unindexed.log_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE unindexed.log_id_seq OWNER TO "testUser";

--
-- Name: log_id_seq; Type: SEQUENCE OWNED BY; Schema: unindexed; Owner: testUser
--

ALTER SEQUENCE unindexed.log_id_seq OWNED BY unindexed.log.id;


--
-- Name: patient; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.patient (
                                   id uuid DEFAULT gen_random_uuid() NOT NULL,
                                   username text NOT NULL,
                                   hashed_password text NOT NULL,
                                   enrolled_at timestamp(0) without time zone DEFAULT now() NOT NULL
);


ALTER TABLE unindexed.patient OWNER TO "testUser";

--
-- Name: patient_session_token; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.patient_session_token (
                                                 token character varying(256) NOT NULL,
                                                 patient_id uuid NOT NULL,
                                                 encrypted_client_info text NOT NULL,
                                                 last_used timestamp(0) without time zone DEFAULT now() NOT NULL,
                                                 created_at timestamp(0) without time zone DEFAULT now() NOT NULL,
                                                 expiry_date timestamp(0) without time zone
);


ALTER TABLE unindexed.patient_session_token OWNER TO "testUser";

--
-- Name: patient_staff; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.patient_staff (
                                         patient_id uuid NOT NULL,
                                         staff_id uuid NOT NULL,
                                         assigned_at timestamp(0) without time zone DEFAULT now() NOT NULL
);


ALTER TABLE unindexed.patient_staff OWNER TO "testUser";

--
-- Name: staff; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.staff (
                                 id uuid DEFAULT gen_random_uuid() NOT NULL,
                                 username text NOT NULL,
                                 hashed_password text NOT NULL
);


ALTER TABLE unindexed.staff OWNER TO "testUser";

--
-- Name: staff_session_token; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.staff_session_token (
                                               token character varying(256) NOT NULL,
                                               staff_id uuid NOT NULL,
                                               encrypted_client_info text NOT NULL,
                                               last_used timestamp(0) without time zone DEFAULT now() NOT NULL,
                                               created_at timestamp(0) without time zone DEFAULT now() NOT NULL,
                                               expiry_date timestamp(0) without time zone
);


ALTER TABLE unindexed.staff_session_token OWNER TO "testUser";

--
-- Name: task; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.task (
                                id uuid DEFAULT gen_random_uuid() NOT NULL,
                                name text NOT NULL,
                                description text,
                                task_visibility unindexed.task_visibility_enum DEFAULT 'unlisted'::unindexed.task_visibility_enum NOT NULL,
                                created_at timestamp(0) without time zone DEFAULT now() NOT NULL
);


ALTER TABLE unindexed.task OWNER TO "testUser";

--
-- Name: task_editor; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.task_editor (
                                       task_id uuid NOT NULL,
                                       staff_id uuid NOT NULL,
                                       role unindexed.task_editor_role_enum DEFAULT 'owner'::unindexed.task_editor_role_enum NOT NULL
);


ALTER TABLE unindexed.task_editor OWNER TO "testUser";

--
-- Name: word_retrieval_session; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.word_retrieval_session (
                                                  id uuid DEFAULT gen_random_uuid() NOT NULL,
                                                  patient_id uuid NOT NULL,
                                                  task_id uuid NOT NULL,
                                                  hints_used_count smallint DEFAULT 0 NOT NULL,
                                                  started_at timestamp(0) without time zone DEFAULT now() NOT NULL,
                                                  completed_at timestamp(0) without time zone,
                                                  is_successful boolean
);


ALTER TABLE unindexed.word_retrieval_session OWNER TO "testUser";

--
-- Name: word_retrieval_session_message; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.word_retrieval_session_message (
                                                          id uuid DEFAULT gen_random_uuid() NOT NULL,
                                                          session_id uuid NOT NULL,
                                                          author unindexed.message_author_enum NOT NULL,
                                                          content text NOT NULL,
                                                          sent_at timestamp(0) without time zone DEFAULT now() NOT NULL
);


ALTER TABLE unindexed.word_retrieval_session_message OWNER TO "testUser";

--
-- Name: word_retrieval_task; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.word_retrieval_task (
                                               task_id uuid NOT NULL,
                                               image_path text NOT NULL,
                                               answer text NOT NULL,
                                               input_restriction unindexed.word_retrieval_input_restriction DEFAULT 'NONE'::unindexed.word_retrieval_input_restriction NOT NULL
);


ALTER TABLE unindexed.word_retrieval_task OWNER TO "testUser";

--
-- Name: word_retrieval_task_hint; Type: TABLE; Schema: unindexed; Owner: testUser
--

CREATE TABLE unindexed.word_retrieval_task_hint (
                                                    hint_number smallint NOT NULL,
                                                    task_id uuid NOT NULL,
                                                    content text NOT NULL,
                                                    type unindexed.word_retrieval_hint_type_enum NOT NULL,
                                                    hierarchy_num integer NOT NULL
);


ALTER TABLE unindexed.word_retrieval_task_hint OWNER TO "testUser";

--
-- Name: log id; Type: DEFAULT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.log ALTER COLUMN id SET DEFAULT nextval('unindexed.log_id_seq'::regclass);


--
-- Name: admin admin_pkey; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.admin
    ADD CONSTRAINT admin_pkey PRIMARY KEY (id);


--
-- Name: admin_session_token admin_session_token_pkey; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.admin_session_token
    ADD CONSTRAINT admin_session_token_pkey PRIMARY KEY (token);


--
-- Name: admin admin_username_unique; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.admin
    ADD CONSTRAINT admin_username_unique UNIQUE (username);


--
-- Name: enrollment_code enrollment_code_patient_username_unique; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.enrollment_code
    ADD CONSTRAINT enrollment_code_patient_username_unique UNIQUE (patient_username);


--
-- Name: enrollment_code enrollment_code_pkey; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.enrollment_code
    ADD CONSTRAINT enrollment_code_pkey PRIMARY KEY (code);


--
-- Name: log log_pkey; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.log
    ADD CONSTRAINT log_pkey PRIMARY KEY (id);


--
-- Name: patient patient_pkey; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.patient
    ADD CONSTRAINT patient_pkey PRIMARY KEY (id);


--
-- Name: patient_session_token patient_session_token_pkey; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.patient_session_token
    ADD CONSTRAINT patient_session_token_pkey PRIMARY KEY (token);


--
-- Name: patient_staff patient_staff_patient_id_staff_id_pk; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.patient_staff
    ADD CONSTRAINT patient_staff_patient_id_staff_id_pk PRIMARY KEY (patient_id, staff_id);


--
-- Name: patient patient_username_unique; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.patient
    ADD CONSTRAINT patient_username_unique UNIQUE (username);


--
-- Name: staff staff_pkey; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.staff
    ADD CONSTRAINT staff_pkey PRIMARY KEY (id);


--
-- Name: staff_session_token staff_session_token_pkey; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.staff_session_token
    ADD CONSTRAINT staff_session_token_pkey PRIMARY KEY (token);


--
-- Name: staff staff_username_unique; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.staff
    ADD CONSTRAINT staff_username_unique UNIQUE (username);


--
-- Name: task_editor task_editor_task_id_staff_id_pk; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.task_editor
    ADD CONSTRAINT task_editor_task_id_staff_id_pk PRIMARY KEY (task_id, staff_id);


--
-- Name: task task_name_unique; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.task
    ADD CONSTRAINT task_name_unique UNIQUE (name);


--
-- Name: task task_pkey; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.task
    ADD CONSTRAINT task_pkey PRIMARY KEY (id);


--
-- Name: word_retrieval_session_message word_retrieval_session_message_pkey; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.word_retrieval_session_message
    ADD CONSTRAINT word_retrieval_session_message_pkey PRIMARY KEY (id);


--
-- Name: word_retrieval_session word_retrieval_session_pkey; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.word_retrieval_session
    ADD CONSTRAINT word_retrieval_session_pkey PRIMARY KEY (id);


--
-- Name: word_retrieval_task_hint word_retrieval_task_hint_hint_number_task_id_pk; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.word_retrieval_task_hint
    ADD CONSTRAINT word_retrieval_task_hint_hint_number_task_id_pk PRIMARY KEY (hint_number, task_id);


--
-- Name: word_retrieval_task word_retrieval_task_pkey; Type: CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.word_retrieval_task
    ADD CONSTRAINT word_retrieval_task_pkey PRIMARY KEY (task_id);

--
-- Name: admin_session_token admin_session_token_admin_id_admin_id_fk; Type: FK CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.admin_session_token
    ADD CONSTRAINT admin_session_token_admin_id_admin_id_fk FOREIGN KEY (admin_id) REFERENCES unindexed.admin(id) ON DELETE CASCADE;


--
-- Name: enrollment_code enrollment_code_staff_id_staff_id_fk; Type: FK CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.enrollment_code
    ADD CONSTRAINT enrollment_code_staff_id_staff_id_fk FOREIGN KEY (staff_id) REFERENCES unindexed.staff(id) ON DELETE CASCADE;


--
-- Name: patient_session_token patient_session_token_patient_id_patient_id_fk; Type: FK CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.patient_session_token
    ADD CONSTRAINT patient_session_token_patient_id_patient_id_fk FOREIGN KEY (patient_id) REFERENCES unindexed.patient(id) ON DELETE CASCADE;


--
-- Name: patient_staff patient_staff_patient_id_patient_id_fk; Type: FK CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.patient_staff
    ADD CONSTRAINT patient_staff_patient_id_patient_id_fk FOREIGN KEY (patient_id) REFERENCES unindexed.patient(id) ON DELETE CASCADE;


--
-- Name: patient_staff patient_staff_staff_id_staff_id_fk; Type: FK CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.patient_staff
    ADD CONSTRAINT patient_staff_staff_id_staff_id_fk FOREIGN KEY (staff_id) REFERENCES unindexed.staff(id) ON DELETE CASCADE;


--
-- Name: staff_session_token staff_session_token_staff_id_staff_id_fk; Type: FK CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.staff_session_token
    ADD CONSTRAINT staff_session_token_staff_id_staff_id_fk FOREIGN KEY (staff_id) REFERENCES unindexed.staff(id) ON DELETE CASCADE;


--
-- Name: task_editor task_editor_staff_id_staff_id_fk; Type: FK CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.task_editor
    ADD CONSTRAINT task_editor_staff_id_staff_id_fk FOREIGN KEY (staff_id) REFERENCES unindexed.staff(id) ON DELETE CASCADE;


--
-- Name: task_editor task_editor_task_id_task_id_fk; Type: FK CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.task_editor
    ADD CONSTRAINT task_editor_task_id_task_id_fk FOREIGN KEY (task_id) REFERENCES unindexed.task(id) ON DELETE CASCADE;


--
-- Name: word_retrieval_session_message word_retrieval_session_message_session_id_word_retrieval_sessio; Type: FK CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.word_retrieval_session_message
    ADD CONSTRAINT word_retrieval_session_message_session_id_word_retrieval_sessio FOREIGN KEY (session_id) REFERENCES unindexed.word_retrieval_session(id) ON DELETE CASCADE;


--
-- Name: word_retrieval_session word_retrieval_session_patient_id_patient_id_fk; Type: FK CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.word_retrieval_session
    ADD CONSTRAINT word_retrieval_session_patient_id_patient_id_fk FOREIGN KEY (patient_id) REFERENCES unindexed.patient(id) ON DELETE CASCADE;


--
-- Name: word_retrieval_session word_retrieval_session_task_id_word_retrieval_task_task_id_fk; Type: FK CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.word_retrieval_session
    ADD CONSTRAINT word_retrieval_session_task_id_word_retrieval_task_task_id_fk FOREIGN KEY (task_id) REFERENCES unindexed.word_retrieval_task(task_id) ON DELETE CASCADE;


--
-- Name: word_retrieval_task_hint word_retrieval_task_hint_task_id_task_id_fk; Type: FK CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.word_retrieval_task_hint
    ADD CONSTRAINT word_retrieval_task_hint_task_id_task_id_fk FOREIGN KEY (task_id) REFERENCES unindexed.task(id) ON DELETE CASCADE;


--
-- Name: word_retrieval_task word_retrieval_task_task_id_task_id_fk; Type: FK CONSTRAINT; Schema: unindexed; Owner: testUser
--

ALTER TABLE ONLY unindexed.word_retrieval_task
    ADD CONSTRAINT word_retrieval_task_task_id_task_id_fk FOREIGN KEY (task_id) REFERENCES unindexed.task(id) ON DELETE CASCADE;


--
-- Name: SCHEMA unindexed; Type: ACL; Schema: -; Owner: testUser
--

REVOKE USAGE ON SCHEMA unindexed FROM public;
GRANT ALL ON SCHEMA unindexed TO public;


--
-- PostgreSQL database dump complete
--