From f7a6244cc1a8f39ad44186a4da6b743ab6821d51 Mon Sep 17 00:00:00 2001
From: Linnnus <linnnus@users.noreply.github.com>
Date: Wed, 19 Feb 2025 18:37:33 +0100
Subject: Add assignment submission

---
 db/dump.sql | 250 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 249 insertions(+), 1 deletion(-)

(limited to 'db/dump.sql')

diff --git a/db/dump.sql b/db/dump.sql
index 5cb3a51..9486eb6 100644
--- a/db/dump.sql
+++ b/db/dump.sql
@@ -31,6 +31,28 @@ CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;
 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
 --
@@ -54,6 +76,120 @@ 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
 --
@@ -104,6 +240,35 @@ ALTER TABLE public.users ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
 );
 
 
+--
+-- 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
 --
@@ -111,6 +276,9 @@ ALTER TABLE public.users ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
 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
 \.
 
 
@@ -122,14 +290,54 @@ COPY public.users (id, role, first_name, last_name, password_hash, email) FROM s
 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', 3, true);
+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);
 
 
 --
@@ -147,6 +355,14 @@ ALTER TABLE ONLY public.users
 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
 --
@@ -163,6 +379,38 @@ 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
 --
-- 
cgit v1.2.3

