path: root/db/dump.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 17.2
-- Dumped by pg_dump version 17.2

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;

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

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;


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

COMMENT ON EXTENSION pgcrypto IS 'cryptographic functions';


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

CREATE TYPE public.assignment_state AS ENUM (
    'AWAITING_GARDENER_NOTIFICATION',
    'AWAITING_FINISH',
    'AWAITING_WATERMARKING',
    'AWAITING_OWNER_NOTIFICATION',
    'DONE'
);


ALTER TYPE public.assignment_state OWNER TO postgres;

--
-- Name: TYPE assignment_state; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TYPE public.assignment_state IS 'The states for an assignment.';


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

CREATE TYPE public.role AS ENUM (
    'gardener',
    'owner'
);


ALTER TYPE public.role OWNER TO postgres;

--
-- Name: TYPE role; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TYPE public.role IS 'The role of a user within the system.';


SET default_tablespace = '';

SET default_table_access_method = heap;

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

CREATE TABLE public.assignments (
    id integer NOT NULL,
    gardener_id integer NOT NULL,
    cemetary_plot_id integer NOT NULL,
    date date,
    state public.assignment_state DEFAULT 'AWAITING_GARDENER_NOTIFICATION'::public.assignment_state NOT NULL,
    note text
);


ALTER TABLE public.assignments OWNER TO postgres;

--
-- Name: COLUMN assignments.note; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.assignments.note IS 'When an assignment is finished, the gardener can supply a note.';


--
-- Name: assignments_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.assignments ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.assignments_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


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

CREATE TABLE public.cemetary_plots (
    id integer NOT NULL,
    address text NOT NULL,
    owner_id integer NOT NULL,
    assignment_interval interval day DEFAULT '14 days'::interval NOT NULL
);


ALTER TABLE public.cemetary_plots OWNER TO postgres;

--
-- Name: COLUMN cemetary_plots.assignment_interval; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.cemetary_plots.assignment_interval IS 'The interval between when cleaning assignments. If there was a job at time N, the next one would be at N + assignment_interval.';


--
-- Name: cementary_plots_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.cemetary_plots ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.cementary_plots_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


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

CREATE TABLE public.images (
    id integer NOT NULL,
    s3_path text NOT NULL,
    original_filename text NOT NULL,
    assignment_id integer
);


ALTER TABLE public.images OWNER TO postgres;

--
-- Name: TABLE images; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.images IS 'This table stores the images uploaded when finishing an assignment.';


--
-- Name: COLUMN images.assignment_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.images.assignment_id IS 'Reference to the owning assignment';


--
-- Name: images_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.images ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.images_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


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

CREATE TABLE public.sessions (
    token text NOT NULL,
    user_id integer NOT NULL,
    expires_at timestamp with time zone NOT NULL
);


ALTER TABLE public.sessions OWNER TO postgres;

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

CREATE TABLE public.users (
    id integer NOT NULL,
    role public.role NOT NULL,
    first_name text NOT NULL,
    last_name text NOT NULL,
    password_hash text NOT NULL,
    email text
);


ALTER TABLE public.users OWNER TO postgres;

--
-- Name: COLUMN users.password_hash; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.users.password_hash IS 'Hash of password + salt.';


--
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.users ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Data for Name: assignments; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.assignments (id, gardener_id, cemetary_plot_id, date, state, note) FROM stdin;
2	1	2	2025-03-05	AWAITING_GARDENER_NOTIFICATION	\N
1	1	1	2025-02-28	AWAITING_WATERMARKING	\N
\.


--
-- Data for Name: cemetary_plots; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.cemetary_plots (id, address, owner_id, assignment_interval) FROM stdin;
1	24B, Kirkevej 64, Egå 8328	4	21 days
2	26C, Kirkevej 64, Egå 8328	5	14 days
\.


--
-- Data for Name: images; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.images (id, s3_path, original_filename, assignment_id) FROM stdin;
3	fe5e7c27-99c4-4d4b-ba77-fcad5c6607e3	eva.jpeg	1
\.


--
-- Data for Name: sessions; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.sessions (token, user_id, expires_at) FROM stdin;
je2bic5i5jou7woob4ehreoqojuir4co	1	2025-03-19 18:04:35.904+01
27ghlb4vzh543kxpnkniip2oai6q4dvx	1	2025-03-19 18:04:47.722+01
bjt7wgbfrlzrg7fhphsxezpc7fornezv	1	2025-03-19 20:42:57.838+01
bgrgdtxmiq44ig6dh32pop4wkterqj6r	1	2025-03-20 18:03:54.199+01
q6ifvlkjdoncw5usnb37da3sf4y3fxyq	1	2025-03-20 20:01:53.318+01
\.


--
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.users (id, role, first_name, last_name, password_hash, email) FROM stdin;
1	gardener	Emil	Blomst	$2a$06$vw1iVTLDQzeUwmhyW17ZzeJL.gyC4xWWzAwvJO8dsZi7Jdg2w5zbu	emil@eksempel.dk
2	gardener	Sarah	Hækkesen	$2a$06$RyzzMad/jmKZwg9hRDymCO/KvxiSUFcdYQZdxB9trHHOU3tKhtFIK	sarah.hækkesen@kirkefætrene.dk
3	gardener	Sten	Graversen	$2a$06$/InJ2HFv1/z0YVXMwoHWZuV7aTlWl3GH9csKEvN5gb4nEPSECEdFq	wad@gaidefar.com
4	owner	Lotte	Ejersen	$2a$06$C1JTdp2y9lIO57S7kGsBruE2oLj87AYpNNrZqx/5Zgap5lqcDj.my	l.ejersen@eksempel.dk
5	owner	Mads Hylgaard	Jensen	$2a$06$FuFHsoFo1cPJ9ms9TceVG..2BAHAyJpE2NyBUTH9JnG6v8GWuOh4u	mads@wraaath.xyz
6	owner	Lukas	Ibasen	$2a$06$p2nOFXtLC0YE/7TTxqzUOeEfpPKVjVZr2sge1KBFT/7lrx2rpsvO2	contact@ibsenware.xyz
\.


--
-- Name: assignments_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.assignments_id_seq', 2, true);


--
-- Name: cementary_plots_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.cementary_plots_id_seq', 2, true);


--
-- Name: images_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.images_id_seq', 3, true);


--
-- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.users_id_seq', 6, true);


--
-- Name: assignments assignments_id_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.assignments
    ADD CONSTRAINT assignments_id_pkey PRIMARY KEY (id);


--
-- Name: cemetary_plots cemetary_plots_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.cemetary_plots
    ADD CONSTRAINT cemetary_plots_pkey PRIMARY KEY (id);


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

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


--
-- Name: CONSTRAINT email_uniqueness ON users; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON CONSTRAINT email_uniqueness ON public.users IS 'Multiple users cannot have the same email.';


--
-- Name: images images_s3_path_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.images
    ADD CONSTRAINT images_s3_path_key UNIQUE (s3_path);


--
-- Name: sessions sessions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.sessions
    ADD CONSTRAINT sessions_pkey PRIMARY KEY (token);


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

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


--
-- Name: assignments assignments_cemetary_plot_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.assignments
    ADD CONSTRAINT assignments_cemetary_plot_id_fkey FOREIGN KEY (cemetary_plot_id) REFERENCES public.cemetary_plots(id);


--
-- Name: assignments assignments_gardener_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.assignments
    ADD CONSTRAINT assignments_gardener_id_fkey FOREIGN KEY (gardener_id) REFERENCES public.users(id);


--
-- Name: cemetary_plots cemetary_plots_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.cemetary_plots
    ADD CONSTRAINT cemetary_plots_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES public.users(id);


--
-- Name: images images_assignment_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.images
    ADD CONSTRAINT images_assignment_id_fkey FOREIGN KEY (assignment_id) REFERENCES public.assignments(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: sessions sessions_userid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.sessions
    ADD CONSTRAINT sessions_userid_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);


--
-- PostgreSQL database dump complete
--