backup.sql

--
-- PostgreSQL database dump
--

\restrict ULzYLWNDAQEgP2fRVtuSfwcwEcR1olnZkImDGBoRS1euXDD4XkZADrfPov7cnO4

-- Dumped from database version 16.11
-- Dumped by pg_dump version 16.11

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: clean_archived_bookings(); Type: PROCEDURE; Schema: public; Owner: docker
--

CREATE PROCEDURE public.clean_archived_bookings()
    LANGUAGE plpgsql
    AS $$
BEGIN
    DELETE FROM bookings
    WHERE (date::text || ' ' || end_time::text)::timestamp < CURRENT_TIMESTAMP;
END;
$$;


ALTER PROCEDURE public.clean_archived_bookings() OWNER TO docker;

--
-- Name: log_booking_deletion(); Type: FUNCTION; Schema: public; Owner: docker
--

CREATE FUNCTION public.log_booking_deletion() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO bookings_audit_log (booking_id, user_id, room_id, reason)
    VALUES (OLD.id, OLD.user_id, OLD.room_id, 'Booking deleted/cancelled');
    RETURN OLD;
END;
$$;


ALTER FUNCTION public.log_booking_deletion() OWNER TO docker;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: bookings; Type: TABLE; Schema: public; Owner: docker
--

CREATE TABLE public.bookings (
    id integer NOT NULL,
    user_id integer NOT NULL,
    room_id character varying(50) NOT NULL,
    date date NOT NULL,
    start_time time without time zone NOT NULL,
    end_time time without time zone NOT NULL,
    status character varying(20) DEFAULT 'Confirmed'::character varying,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE public.bookings OWNER TO docker;

--
-- Name: bookings_audit_log; Type: TABLE; Schema: public; Owner: docker
--

CREATE TABLE public.bookings_audit_log (
    log_id integer NOT NULL,
    booking_id integer,
    user_id integer,
    room_id character varying(50),
    deleted_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    reason text
);


ALTER TABLE public.bookings_audit_log OWNER TO docker;

--
-- Name: bookings_audit_log_log_id_seq; Type: SEQUENCE; Schema: public; Owner: docker
--

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


ALTER SEQUENCE public.bookings_audit_log_log_id_seq OWNER TO docker;

--
-- Name: bookings_audit_log_log_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: docker
--

ALTER SEQUENCE public.bookings_audit_log_log_id_seq OWNED BY public.bookings_audit_log.log_id;


--
-- Name: bookings_id_seq; Type: SEQUENCE; Schema: public; Owner: docker
--

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


ALTER SEQUENCE public.bookings_id_seq OWNER TO docker;

--
-- Name: bookings_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: docker
--

ALTER SEQUENCE public.bookings_id_seq OWNED BY public.bookings.id;


--
-- Name: roles; Type: TABLE; Schema: public; Owner: docker
--

CREATE TABLE public.roles (
    id integer NOT NULL,
    name character varying(50) NOT NULL
);


ALTER TABLE public.roles OWNER TO docker;

--
-- Name: roles_id_seq; Type: SEQUENCE; Schema: public; Owner: docker
--

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


ALTER SEQUENCE public.roles_id_seq OWNER TO docker;

--
-- Name: roles_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: docker
--

ALTER SEQUENCE public.roles_id_seq OWNED BY public.roles.id;


--
-- Name: rooms; Type: TABLE; Schema: public; Owner: docker
--

CREATE TABLE public.rooms (
    id character varying(50) NOT NULL,
    name character varying(100) NOT NULL,
    workspaces integer NOT NULL,
    type character varying(50),
    description text
);


ALTER TABLE public.rooms OWNER TO docker;

--
-- Name: user_creation_logs; Type: TABLE; Schema: public; Owner: docker
--

CREATE TABLE public.user_creation_logs (
    user_id integer NOT NULL,
    created_at_log timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE public.user_creation_logs OWNER TO docker;

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

CREATE TABLE public.users (
    id integer NOT NULL,
    name character varying(100) NOT NULL,
    surname character varying(100) NOT NULL,
    email character varying(255) NOT NULL,
    password character varying(255) NOT NULL,
    id_role integer NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE public.users OWNER TO docker;

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

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


ALTER SEQUENCE public.users_id_seq OWNER TO docker;

--
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: docker
--

ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;


--
-- Name: vw_booking_details; Type: VIEW; Schema: public; Owner: docker
--

CREATE VIEW public.vw_booking_details AS
 SELECT b.id AS booking_id,
    b.date,
    b.start_time,
    b.end_time,
    b.status,
    u.email AS user_email,
    (((u.name)::text || ' '::text) || (u.surname)::text) AS user_fullname,
    r.name AS room_name,
    r.type AS room_type
   FROM ((public.bookings b
     JOIN public.users u ON ((b.user_id = u.id)))
     JOIN public.rooms r ON (((b.room_id)::text = (r.id)::text)));


ALTER VIEW public.vw_booking_details OWNER TO docker;

--
-- Name: vw_room_stats; Type: VIEW; Schema: public; Owner: docker
--

CREATE VIEW public.vw_room_stats AS
 SELECT r.name AS room_name,
    count(b.id) AS total_bookings,
    max(b.date) AS last_booking_date
   FROM (public.rooms r
     LEFT JOIN public.bookings b ON (((r.id)::text = (b.room_id)::text)))
  GROUP BY r.name;


ALTER VIEW public.vw_room_stats OWNER TO docker;

--
-- Name: bookings id; Type: DEFAULT; Schema: public; Owner: docker
--

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


--
-- Name: bookings_audit_log log_id; Type: DEFAULT; Schema: public; Owner: docker
--

ALTER TABLE ONLY public.bookings_audit_log ALTER COLUMN log_id SET DEFAULT nextval('public.bookings_audit_log_log_id_seq'::regclass);


--
-- Name: roles id; Type: DEFAULT; Schema: public; Owner: docker
--

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


--
-- Name: users id; Type: DEFAULT; Schema: public; Owner: docker
--

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


--
-- Data for Name: bookings; Type: TABLE DATA; Schema: public; Owner: docker
--

COPY public.bookings (id, user_id, room_id, date, start_time, end_time, status, created_at) FROM stdin;
1	3	AULA2	2025-12-27	03:03:00	06:13:00	Confirmed	2025-12-12 22:02:55.558969
2	3	STUDYROOM1	2026-01-01	03:02:00	04:09:00	Confirmed	2025-12-12 22:03:07.635497
\.


--
-- Data for Name: bookings_audit_log; Type: TABLE DATA; Schema: public; Owner: docker
--

COPY public.bookings_audit_log (log_id, booking_id, user_id, room_id, deleted_at, reason) FROM stdin;
\.


--
-- Data for Name: roles; Type: TABLE DATA; Schema: public; Owner: docker
--

COPY public.roles (id, name) FROM stdin;
1	student
2	teacher
3	admin
\.


--
-- Data for Name: rooms; Type: TABLE DATA; Schema: public; Owner: docker
--

COPY public.rooms (id, name, workspaces, type, description) FROM stdin;
ROOM1	Sala Konferencyjna	20	Conference	Du┼╝a sala z rzutnikiem.
ROOM2	Mała Salka	6	Focus	Idealna do pracy w ciszy.
ROOM3	Laboratorium	12	Lab	Wyposa┼╝ona w komputery.
ROOM4	Sala Spotka┼ä	8	Meeting	St├│┼é okr─ůg┼éy.
ROOM5	Open Space	15	Common	Przestrzeń wspólna.
AULA1	Aula Główna	100	Lecture Hall	Dla dużych wykładów.
AULA2	Aula Boczna	50	Lecture Hall	Mniejsza aula.
STUDYROOM1	Pok├│j Cichej Nauki	4	Study	Tylko do nauki indywidualnej.
\.


--
-- Data for Name: user_creation_logs; Type: TABLE DATA; Schema: public; Owner: docker
--

COPY public.user_creation_logs (user_id, created_at_log) FROM stdin;
3	2025-12-12 21:59:15.982163
4	2025-12-12 21:59:27.970506
\.


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

COPY public.users (id, name, surname, email, password, id_role, created_at) FROM stdin;
4	Admin	System	admin@example.com	$2y$10$yocGZGO53SMvKRwOzkJ3v.jB0mL1O/oxZvkn7fOveAgy5i//9M/gq	3	2025-12-12 21:59:27.970506
3	Bartek	Kowalski	bartek@example.com	$2y$10$wNeQuNsxIBQp.e2hBWY.nuAfEY3OIlr254jMTSmfBUAb3IoyevPxq	2	2025-12-12 21:59:15.982163
\.


--
-- Name: bookings_audit_log_log_id_seq; Type: SEQUENCE SET; Schema: public; Owner: docker
--

SELECT pg_catalog.setval('public.bookings_audit_log_log_id_seq', 1, false);


--
-- Name: bookings_id_seq; Type: SEQUENCE SET; Schema: public; Owner: docker
--

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


--
-- Name: roles_id_seq; Type: SEQUENCE SET; Schema: public; Owner: docker
--

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


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

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


--
-- Name: bookings_audit_log bookings_audit_log_pkey; Type: CONSTRAINT; Schema: public; Owner: docker
--

ALTER TABLE ONLY public.bookings_audit_log
    ADD CONSTRAINT bookings_audit_log_pkey PRIMARY KEY (log_id);


--
-- Name: bookings bookings_pkey; Type: CONSTRAINT; Schema: public; Owner: docker
--

ALTER TABLE ONLY public.bookings
    ADD CONSTRAINT bookings_pkey PRIMARY KEY (id);


--
-- Name: roles roles_name_key; Type: CONSTRAINT; Schema: public; Owner: docker
--

ALTER TABLE ONLY public.roles
    ADD CONSTRAINT roles_name_key UNIQUE (name);


--
-- Name: roles roles_pkey; Type: CONSTRAINT; Schema: public; Owner: docker
--

ALTER TABLE ONLY public.roles
    ADD CONSTRAINT roles_pkey PRIMARY KEY (id);


--
-- Name: rooms rooms_pkey; Type: CONSTRAINT; Schema: public; Owner: docker
--

ALTER TABLE ONLY public.rooms
    ADD CONSTRAINT rooms_pkey PRIMARY KEY (id);


--
-- Name: user_creation_logs user_creation_logs_pkey; Type: CONSTRAINT; Schema: public; Owner: docker
--

ALTER TABLE ONLY public.user_creation_logs
    ADD CONSTRAINT user_creation_logs_pkey PRIMARY KEY (user_id);


--
-- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: docker
--

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


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

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


--
-- Name: bookings trg_log_booking_delete; Type: TRIGGER; Schema: public; Owner: docker
--

CREATE TRIGGER trg_log_booking_delete AFTER DELETE ON public.bookings FOR EACH ROW EXECUTE FUNCTION public.log_booking_deletion();


--
-- Name: bookings bookings_room_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: docker
--

ALTER TABLE ONLY public.bookings
    ADD CONSTRAINT bookings_room_id_fkey FOREIGN KEY (room_id) REFERENCES public.rooms(id) ON DELETE CASCADE;


--
-- Name: bookings bookings_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: docker
--

ALTER TABLE ONLY public.bookings
    ADD CONSTRAINT bookings_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;


--
-- Name: user_creation_logs user_creation_logs_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: docker
--

ALTER TABLE ONLY public.user_creation_logs
    ADD CONSTRAINT user_creation_logs_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;


--
-- Name: users users_id_role_fkey; Type: FK CONSTRAINT; Schema: public; Owner: docker
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_id_role_fkey FOREIGN KEY (id_role) REFERENCES public.roles(id);


--
-- PostgreSQL database dump complete
--

\unrestrict ULzYLWNDAQEgP2fRVtuSfwcwEcR1olnZkImDGBoRS1euXDD4XkZADrfPov7cnO4