--
-- PostgreSQL database cluster dump
--

\restrict 8RggYatQz7SxDKtUaNiBUo25WDN8daaGmpv0w8U2nqciaUBGxLDd3Qidl3YDKGZ

SET default_transaction_read_only = off;


--
-- Roles
--

CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
CREATE ROLE softwarebestellung_user;
ALTER ROLE softwarebestellung_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:0apc6wKTlHOwJaqr0XCA4A==$oUiAw8yFXGmG3gLyp/PRrVCkutqXTxSPVkUdzKQqss4=:79KrtPyQs0OOCFNt1Yuzi1lvobka1TAKHFmwpJiGYgE=';

--
-- User Configurations
--








\unrestrict 8RggYatQz7SxDKtUaNiBUo25WDN8daaGmpv0w8U2nqciaUBGxLDd3Qidl3YDKGZ

--
-- Databases
--

--
-- Database "template1" dump
--

\connect template1

--
--

\restrict Uw7AufBnQb3sE2CK1FfLYbbAu31kReX67caXgXidHQxEYc2LAK1mfCa23aPQCNf


SET transaction_timeout = 0;
SELECT pg_catalog.set_config('search_path', '', false);

--
--

\unrestrict Uw7AufBnQb3sE2CK1FfLYbbAu31kReX67caXgXidHQxEYc2LAK1mfCa23aPQCNf

--
-- Database "postgres" dump
--

\connect postgres

--
--

\restrict vdPaXalQM36zdh8Zbd08XsmAcSusZELvVKJsabj9zJrEa07D5GrgXDNhivXJ3fY


SET transaction_timeout = 0;
SELECT pg_catalog.set_config('search_path', '', false);

--
--

\unrestrict vdPaXalQM36zdh8Zbd08XsmAcSusZELvVKJsabj9zJrEa07D5GrgXDNhivXJ3fY

--
-- Database "softwarebestellung" dump
--

--
--

\restrict OZjLh2pDYAtFrVWm63wWNMUlSVD7O7FIVhfVxjeOH3TwwI0AFirz2rDpCtaGYWE


SET transaction_timeout = 0;
SELECT pg_catalog.set_config('search_path', '', false);

--
-- Name: softwarebestellung; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE softwarebestellung WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'de_DE.UTF-8';


ALTER DATABASE softwarebestellung OWNER TO postgres;

\unrestrict OZjLh2pDYAtFrVWm63wWNMUlSVD7O7FIVhfVxjeOH3TwwI0AFirz2rDpCtaGYWE
\connect softwarebestellung
\restrict OZjLh2pDYAtFrVWm63wWNMUlSVD7O7FIVhfVxjeOH3TwwI0AFirz2rDpCtaGYWE

SET transaction_timeout = 0;
SELECT pg_catalog.set_config('search_path', '', false);



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

CREATE TABLE public.bestellungen (
    id integer NOT NULL,
    schul_name character varying(255) NOT NULL,
    status character varying(50) DEFAULT 'warte_auf_genehmigung'::character varying,
    software_name character varying(255) NOT NULL,
    version character varying(100) NOT NULL,
    hersteller character varying(255) NOT NULL,
    hersteller_website character varying(500) NOT NULL,
    isbn character varying(50),
    software_quelle character varying(50) NOT NULL,
    download_pfad text,
    lizenz_typ character varying(50) NOT NULL,
    lizenzschluessel text,
    lizenzserver_anmerkungen text,
    erstellt_am timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    bearbeitet_am timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    bearbeitet_von character varying(100),
    notizen text,
    CONSTRAINT bestellungen_lizenz_typ_check CHECK (((lizenz_typ)::text = ANY ((ARRAY['noLicense'::character varying, 'licenseKey'::character varying, 'licenseHardware'::character varying, 'licenseServer'::character varying])::text[]))),
    CONSTRAINT bestellungen_software_quelle_check CHECK (((software_quelle)::text = ANY ((ARRAY['downloadFree'::character varying, 'downloadCustomer'::character varying, 'physicalMedia'::character varying])::text[]))),
    CONSTRAINT bestellungen_status_check CHECK (((status)::text = ANY ((ARRAY['warte_auf_genehmigung'::character varying, 'genehmigt'::character varying, 'gesendet'::character varying, 'erledigt'::character varying, 'abgelehnt'::character varying, 'neu'::character varying, 'in_pruefung'::character varying, 'korrektur_notwendig'::character varying, 'versendet'::character varying])::text[])))
);


ALTER TABLE public.bestellungen OWNER TO postgres;

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

CREATE TABLE public.bestellungen_archiv (
    id integer NOT NULL,
    schul_name character varying(255) NOT NULL,
    software_name character varying(255) NOT NULL,
    version character varying(100) NOT NULL,
    hersteller character varying(255) NOT NULL,
    hersteller_website character varying(500) NOT NULL,
    isbn character varying(50),
    software_quelle character varying(50) NOT NULL,
    download_pfad text,
    lizenz_typ character varying(50) NOT NULL,
    lizenzschluessel text,
    lizenzserver_anmerkungen text,
    erstellt_am timestamp without time zone,
    bearbeitet_am timestamp without time zone,
    bearbeitet_von character varying(100),
    notizen text,
    archiviert_am timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    archiviert_von character varying(100)
);


ALTER TABLE public.bestellungen_archiv OWNER TO postgres;

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

CREATE TABLE public.bestellungen_historie (
    id integer NOT NULL,
    bestellung_id integer NOT NULL,
    aenderung_von character varying(100),
    aenderung_am timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    feld character varying(100),
    alter_wert text,
    neuer_wert text
);


ALTER TABLE public.bestellungen_historie OWNER TO postgres;

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

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


ALTER SEQUENCE public.bestellungen_historie_id_seq OWNER TO postgres;

--
-- Name: bestellungen_historie_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.bestellungen_historie_id_seq OWNED BY public.bestellungen_historie.id;


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

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


ALTER SEQUENCE public.bestellungen_id_seq OWNER TO postgres;

--
-- Name: bestellungen_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.bestellungen_id_seq OWNED BY public.bestellungen.id;


--
-- Name: bestellungen id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.bestellungen ALTER COLUMN id SET DEFAULT nextval('public.bestellungen_id_seq'::regclass);


--
-- Name: bestellungen_historie id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.bestellungen_historie ALTER COLUMN id SET DEFAULT nextval('public.bestellungen_historie_id_seq'::regclass);


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

COPY public.bestellungen (id, schul_name, status, software_name, version, hersteller, hersteller_website, isbn, software_quelle, download_pfad, lizenz_typ, lizenzschluessel, lizenzserver_anmerkungen, erstellt_am, bearbeitet_am, bearbeitet_von, notizen) FROM stdin;
1	Grundschule Musterstadt	warte_auf_genehmigung	GeoGebra Classic	6.0.750	GeoGebra GmbH	https://www.geogebra.org	\N	downloadFree	https://www.geogebra.org/download	noLicense	\N	\N	2026-02-05 09:42:16.599943	2026-02-05 09:42:16.599943	\N	\N
7	Testschule SMTP-Test	warte_auf_genehmigung	Visual Studio Code	1.85.0	Microsoft	https://code.visualstudio.com	\N	downloadFree	https://code.visualstudio.com/download	noLicense	\N	\N	2026-02-05 10:18:14.715912	2026-02-05 10:18:14.715912	\N	\N
2	Gymnasium am Park	warte_auf_genehmigung	Microsoft Office 365	2023	Microsoft Corporation	https://www.microsoft.com	\N	downloadCustomer	/software/office365/setup.exe	licenseKey	XXXXX-XXXXX-XXXXX-XXXXX-XXXXX	\N	2026-02-05 09:42:16.599943	2026-02-05 09:42:16.599943	\N	Lizenz wird noch geprüft
4	Berufsschule Technik	warte_auf_genehmigung	AutoCAD LT	2024	Autodesk	https://www.autodesk.de	\N	physicalMedia	\N	licenseServer	\N	Lizenzserver auf srv-cad.schule.local, Port 27000-27009	2026-02-05 09:42:16.599943	2026-02-05 09:42:16.599943	\N	Lizenzserver-Daten unvollständig
5	Gesamtschule Süd	abgelehnt	Blender	4.0	Blender Foundation	https://www.blender.org	\N	downloadFree	https://www.blender.org/download/	noLicense	\N	\N	2026-02-05 09:42:16.599943	2026-02-06 20:56:28.78963	admin	Software bereits als iServ-Paket verfügbar
3	Realschule Nord	gesendet	GIMP	2.10.34	The GIMP Team	https://www.gimp.org	\N	downloadFree	https://www.gimp.org/downloads/	noLicense	\N	\N	2026-02-05 09:42:16.599943	2026-02-06 21:30:29.92916	admin	\N
10	Gymnamsium Irgendwo	gesendet	Bestellsystem	1.0	EBLOK	http://tools.eblok.de	\N	downloadCustomer	/home/user/share	licenseKey	aabbccddeeff	\N	2026-02-06 21:44:25.853424	2026-02-06 21:55:54.637794	admin	Hinweis Noitz
\.


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

COPY public.bestellungen_archiv (id, schul_name, software_name, version, hersteller, hersteller_website, isbn, software_quelle, download_pfad, lizenz_typ, lizenzschluessel, lizenzserver_anmerkungen, erstellt_am, bearbeitet_am, bearbeitet_von, notizen, archiviert_am, archiviert_von) FROM stdin;
6	Hauptschule West	Klett Mathe-Trainer	3.5	Ernst Klett Verlag	https://www.klett.de	978-3-12-345678-9	downloadCustomer	/software/klett/mathe_trainer.msi	licenseHardware	\N	\N	2026-02-05 09:42:16.599943	2026-02-05 09:42:16.599943	\N	Hardware-Dongle wird per Post geliefert	2026-02-06 19:44:34.235448	test-admin
8	eee	eee	eee	eee	http://eblok.de	\N	downloadFree	dd	noLicense	\N	\N	2026-02-06 19:06:14.885341	2026-02-06 19:17:28.582999	admin	\N	2026-02-06 19:47:43.769794	admin
9	ss	ss	ss	s	http://eblok.de	\N	downloadFree	sss	noLicense	\N	\N	2026-02-06 19:50:18.662861	2026-02-06 20:55:34.620648	admin	\N	2026-02-06 20:55:48.798035	admin
\.


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

COPY public.bestellungen_historie (id, bestellung_id, aenderung_von, aenderung_am, feld, alter_wert, neuer_wert) FROM stdin;
1	2	admin	2026-02-05 09:42:16.604082	status	neu	in_pruefung
2	3	admin	2026-02-05 09:42:16.604082	status	neu	genehmigt
3	4	admin	2026-02-05 09:42:16.604082	status	neu	in_pruefung
4	4	admin	2026-02-05 09:42:16.604082	status	in_pruefung	korrektur_notwendig
5	5	admin	2026-02-05 09:42:16.604082	status	neu	abgelehnt
12	3	admin	2026-02-06 21:30:29.905508	status	genehmigt	gesendet
13	10	admin	2026-02-06 21:54:42.449425	notizen	\N	Hinweis Noitz
14	10	admin	2026-02-06 21:55:54.136342	status	warte_auf_genehmigung	genehmigt
15	10	admin	2026-02-06 21:55:54.630589	status	genehmigt	gesendet
\.


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

SELECT pg_catalog.setval('public.bestellungen_historie_id_seq', 15, true);


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

SELECT pg_catalog.setval('public.bestellungen_id_seq', 10, true);


--
-- Name: bestellungen_archiv bestellungen_archiv_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.bestellungen_archiv
    ADD CONSTRAINT bestellungen_archiv_pkey PRIMARY KEY (id);


--
-- Name: bestellungen_historie bestellungen_historie_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.bestellungen_historie
    ADD CONSTRAINT bestellungen_historie_pkey PRIMARY KEY (id);


--
-- Name: bestellungen bestellungen_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.bestellungen
    ADD CONSTRAINT bestellungen_pkey PRIMARY KEY (id);


--
-- Name: idx_aenderung_am; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_aenderung_am ON public.bestellungen_historie USING btree (aenderung_am);


--
-- Name: idx_bestellung; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_bestellung ON public.bestellungen_historie USING btree (bestellung_id);


--
-- Name: idx_erstellt; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_erstellt ON public.bestellungen USING btree (erstellt_am);


--
-- Name: idx_schule; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_schule ON public.bestellungen USING btree (schul_name);


--
-- Name: idx_software; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_software ON public.bestellungen USING btree (software_name);


--
-- Name: idx_status; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_status ON public.bestellungen USING btree (status);


--
-- Name: bestellungen_historie bestellungen_historie_bestellung_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.bestellungen_historie
    ADD CONSTRAINT bestellungen_historie_bestellung_id_fkey FOREIGN KEY (bestellung_id) REFERENCES public.bestellungen(id) ON DELETE CASCADE;


--
-- Name: DATABASE softwarebestellung; Type: ACL; Schema: -; Owner: postgres
--

GRANT ALL ON DATABASE softwarebestellung TO softwarebestellung_user;


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

GRANT ALL ON SCHEMA public TO softwarebestellung_user;


--
-- Name: TABLE bestellungen; Type: ACL; Schema: public; Owner: postgres
--

GRANT ALL ON TABLE public.bestellungen TO softwarebestellung_user;


--
-- Name: TABLE bestellungen_archiv; Type: ACL; Schema: public; Owner: postgres
--

GRANT ALL ON TABLE public.bestellungen_archiv TO softwarebestellung_user;


--
-- Name: TABLE bestellungen_historie; Type: ACL; Schema: public; Owner: postgres
--

GRANT ALL ON TABLE public.bestellungen_historie TO softwarebestellung_user;


--
-- Name: SEQUENCE bestellungen_historie_id_seq; Type: ACL; Schema: public; Owner: postgres
--

GRANT ALL ON SEQUENCE public.bestellungen_historie_id_seq TO softwarebestellung_user;


--
-- Name: SEQUENCE bestellungen_id_seq; Type: ACL; Schema: public; Owner: postgres
--

GRANT ALL ON SEQUENCE public.bestellungen_id_seq TO softwarebestellung_user;


--
--

\unrestrict OZjLh2pDYAtFrVWm63wWNMUlSVD7O7FIVhfVxjeOH3TwwI0AFirz2rDpCtaGYWE

--
-- PostgreSQL database cluster dump complete
--

