-- IDS - project 3rd part
-- Roman Nečas - xnecasr00
-- Kristián Pribila - xpribik00
-- 9.4.2025

-- Drop existing tables (if they exist)
DROP TABLE Recenzia CASCADE CONSTRAINTS;
DROP TABLE Vypozicka CASCADE CONSTRAINTS;
DROP TABLE Rezervacia CASCADE CONSTRAINTS;
DROP TABLE Exemplar CASCADE CONSTRAINTS;
DROP TABLE Kniha CASCADE CONSTRAINTS;
DROP TABLE Casopis CASCADE CONSTRAINTS;
DROP TABLE Titul CASCADE CONSTRAINTS;
DROP TABLE Citatel CASCADE CONSTRAINTS;
DROP TABLE Zamestnanec CASCADE CONSTRAINTS;
DROP TABLE Pouzivatel CASCADE CONSTRAINTS;

-- Drop sequences (if they exist)
DROP SEQUENCE pouzivatel_seq;
DROP SEQUENCE titul_seq;
DROP SEQUENCE exemplar_seq;
DROP SEQUENCE rezervacia_seq;
DROP SEQUENCE vypozicka_seq;
DROP SEQUENCE recenzia_seq;

-- Create sequences for primary keys
CREATE SEQUENCE pouzivatel_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE titul_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE exemplar_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE rezervacia_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE vypozicka_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE recenzia_seq START WITH 1 INCREMENT BY 1;


-- Create tables
-- Implementation of generalization/specialization:
-- We are using the approach where parent tables (Pouzivatel and Titul) contain common attributes,
-- and child tables (Citatel, Zamestnanec, Kniha, Casopis) contain specific attributes.
-- Child tables have primary keys that are also foreign keys referencing parent tables.
-- This allows us to maintain the integrity of the inheritance relationship while
-- having specific attributes for each specialization.

-- Parent table for Users
CREATE TABLE Pouzivatel (
    ID_pouzivatela NUMBER PRIMARY KEY,
    Meno VARCHAR2(50) NOT NULL,
    Priezvisko VARCHAR2(50) NOT NULL,
    Email VARCHAR2(100) NOT NULL UNIQUE,
    Telefon VARCHAR2(20),
    Adresa VARCHAR2(200),
    Datum_registracie DATE DEFAULT SYSDATE NOT NULL
);

-- Child table for Readers
CREATE TABLE Citatel (
    ID_pouzivatela NUMBER PRIMARY KEY,
    Cislo_preukazu VARCHAR2(20) NOT NULL UNIQUE,
    Platnost_do DATE NOT NULL,
    Status VARCHAR2(20) DEFAULT 'Aktívny' CHECK (Status IN ('Aktívny', 'Neaktívny', 'Pozastavený')),
    Pocet_vypoziciek NUMBER DEFAULT 0,
    CONSTRAINT fk_citatel_pouzivatel FOREIGN KEY (ID_pouzivatela) REFERENCES Pouzivatel(ID_pouzivatela)
    ON DELETE CASCADE
);

-- Child table for Employees
CREATE TABLE Zamestnanec (
    ID_pouzivatela NUMBER PRIMARY KEY,
    Osobne_cislo VARCHAR2(20) NOT NULL UNIQUE,
    Datum_nastupu DATE NOT NULL,
    Oddelenie VARCHAR2(50) NOT NULL,
    Titul VARCHAR2(20),
    CONSTRAINT fk_zamestnanec_pouzivatel FOREIGN KEY (ID_pouzivatela) REFERENCES Pouzivatel(ID_pouzivatela)
    ON DELETE CASCADE
);

-- Parent table for Titles
CREATE TABLE Titul (
    ID_titulu NUMBER PRIMARY KEY,
    Nazov VARCHAR2(200) NOT NULL,
    Vydavatelstvo VARCHAR2(100),
    Rok_vydania NUMBER(4),
    Popis VARCHAR2(4000),
    Kategoria VARCHAR2(50),
    Pocet_exemplarov NUMBER DEFAULT 0
);

-- Child table for Books with ISBN validation
CREATE TABLE Kniha (
    ID_titulu NUMBER PRIMARY KEY,
    ISBN VARCHAR2(13) NOT NULL UNIQUE,
    Autor VARCHAR2(200) NOT NULL,
    Pocet_stran NUMBER,
    Zaner VARCHAR2(50),
    CONSTRAINT fk_kniha_titul FOREIGN KEY (ID_titulu) REFERENCES Titul(ID_titulu)
    ON DELETE CASCADE,
    -- ISBN must be exactly 13 digits
    CONSTRAINT chk_isbn CHECK (REGEXP_LIKE(ISBN, '^[0-9]{13}$'))
);

-- Child table for Magazines with ISSN validation
CREATE TABLE Casopis (
    ID_titulu NUMBER PRIMARY KEY,
    ISSN VARCHAR2(9) NOT NULL UNIQUE,
    Rocnik NUMBER,
    Cislo NUMBER,
    Periodicita VARCHAR2(50),
    CONSTRAINT fk_casopis_titul FOREIGN KEY (ID_titulu) REFERENCES Titul(ID_titulu)
    ON DELETE CASCADE,
    -- ISSN must be in format XXXX-XXXX where X is a digit
    CONSTRAINT chk_issn CHECK (REGEXP_LIKE(ISSN, '^[0-9]{4}-[0-9]{4}$'))
);

-- Table for Copies
CREATE TABLE Exemplar (
    ID_exemplaru NUMBER PRIMARY KEY,
    ID_titulu NUMBER NOT NULL,
    Datum_nadobudnutia DATE,
    Umiestnenie VARCHAR2(100),
    Signatura VARCHAR2(50),
    Status VARCHAR2(20) DEFAULT 'Dostupný' CHECK (Status IN ('Dostupný', 'Vypožičaný', 'Rezervovaný', 'Vyradený', 'Poškodený')),
    CONSTRAINT fk_exemplar_titul FOREIGN KEY (ID_titulu) REFERENCES Titul(ID_titulu)
    ON DELETE CASCADE
);

-- Table for Reservations
CREATE TABLE Rezervacia (
    ID_rezervacie NUMBER PRIMARY KEY,
    ID_pouzivatela NUMBER NOT NULL,
    ID_titulu NUMBER NOT NULL,
    Datum_rezervacie DATE DEFAULT SYSDATE NOT NULL,
    Datum_expiracie DATE,
    Stav VARCHAR2(20) DEFAULT 'Aktívna' CHECK (Stav IN ('Aktívna', 'Vybavená', 'Zrušená')),
    CONSTRAINT fk_rezervacia_pouzivatel FOREIGN KEY (ID_pouzivatela) REFERENCES Pouzivatel(ID_pouzivatela),
    CONSTRAINT fk_rezervacia_titul FOREIGN KEY (ID_titulu) REFERENCES Titul(ID_titulu)
);

-- Table for Loans
CREATE TABLE Vypozicka (
    ID_vypozicky NUMBER PRIMARY KEY,
    ID_pouzivatela NUMBER NOT NULL,
    ID_exemplaru NUMBER NOT NULL,
    Datum_vypozicania DATE DEFAULT SYSDATE NOT NULL,
    Planovany_datum_vratenia DATE NOT NULL,
    Skutocny_datum_vratenia DATE,
    Stav VARCHAR2(20) DEFAULT 'Aktívna' CHECK (Stav IN ('Aktívna', 'Vrátená', 'Oneskorená')),
    CONSTRAINT fk_vypozicka_pouzivatel FOREIGN KEY (ID_pouzivatela) REFERENCES Pouzivatel(ID_pouzivatela),
    CONSTRAINT fk_vypozicka_exemplar FOREIGN KEY (ID_exemplaru) REFERENCES Exemplar(ID_exemplaru)
);

-- Table for Reviews
CREATE TABLE Recenzia (
    ID_recenzie NUMBER PRIMARY KEY,
    ID_pouzivatela NUMBER NOT NULL,
    ID_titulu NUMBER NOT NULL,
    Hodnotenie NUMBER(1) CHECK (Hodnotenie BETWEEN 1 AND 5),
    Sprava VARCHAR2(2000),
    Datum DATE DEFAULT SYSDATE NOT NULL,
    CONSTRAINT fk_recenzia_pouzivatel FOREIGN KEY (ID_pouzivatela) REFERENCES Pouzivatel(ID_pouzivatela),
    CONSTRAINT fk_recenzia_titul FOREIGN KEY (ID_titulu) REFERENCES Titul(ID_titulu)
);

-- Create triggers for auto-incrementing primary keys from sequences
CREATE OR REPLACE TRIGGER pouzivatel_bir
BEFORE INSERT ON Pouzivatel
FOR EACH ROW
BEGIN
    IF :NEW.ID_pouzivatela IS NULL THEN
        SELECT pouzivatel_seq.NEXTVAL
        INTO :NEW.ID_pouzivatela
        FROM dual;
    END IF;
END;
/

CREATE OR REPLACE TRIGGER titul_bir
BEFORE INSERT ON Titul
FOR EACH ROW
BEGIN
    IF :NEW.ID_titulu IS NULL THEN
        SELECT titul_seq.NEXTVAL
        INTO :NEW.ID_titulu
        FROM dual;
    END IF;
END;
/

CREATE OR REPLACE TRIGGER exemplar_bir
BEFORE INSERT ON Exemplar
FOR EACH ROW
BEGIN
    IF :NEW.ID_exemplaru IS NULL THEN
        SELECT exemplar_seq.NEXTVAL
        INTO :NEW.ID_exemplaru
        FROM dual;
    END IF;
END;
/

CREATE OR REPLACE TRIGGER rezervacia_bir
BEFORE INSERT ON Rezervacia
FOR EACH ROW
BEGIN
    IF :NEW.ID_rezervacie IS NULL THEN
        SELECT rezervacia_seq.NEXTVAL
        INTO :NEW.ID_rezervacie
        FROM dual;
    END IF;
END;
/

CREATE OR REPLACE TRIGGER vypozicka_bir
BEFORE INSERT ON Vypozicka
FOR EACH ROW
BEGIN
    IF :NEW.ID_vypozicky IS NULL THEN
        SELECT vypozicka_seq.NEXTVAL
        INTO :NEW.ID_vypozicky
        FROM dual;
    END IF;
END;
/

CREATE OR REPLACE TRIGGER recenzia_bir
BEFORE INSERT ON Recenzia
FOR EACH ROW
BEGIN
    IF :NEW.ID_recenzie IS NULL THEN
        SELECT recenzia_seq.NEXTVAL
        INTO :NEW.ID_recenzie
        FROM dual;
    END IF;
END;
/

-- Insert sample data
-- Insert Users
INSERT INTO Pouzivatel (Meno, Priezvisko, Email, Telefon, Adresa, Datum_registracie)
VALUES ('Ján', 'Novák', 'jan.novak@example.com', '+421901234567', 'Bratislavská 123, Bratislava', TO_DATE('2022-01-15', 'YYYY-MM-DD'));

INSERT INTO Pouzivatel (Meno, Priezvisko, Email, Telefon, Adresa, Datum_registracie)
VALUES ('Anna', 'Králová', 'anna.kralova@example.com', '+421902345678', 'Košická 456, Košice', TO_DATE('2022-02-20', 'YYYY-MM-DD'));

INSERT INTO Pouzivatel (Meno, Priezvisko, Email, Telefon, Adresa, Datum_registracie)
VALUES ('Peter', 'Malý', 'peter.maly@example.com', '+421903456789', 'Hlavná 789, Žilina', TO_DATE('2022-03-10', 'YYYY-MM-DD'));

INSERT INTO Pouzivatel (Meno, Priezvisko, Email, Telefon, Adresa, Datum_registracie)
VALUES ('Eva', 'Veľká', 'eva.velka@example.com', '+421904567890', 'Námestie SNP 10, Banská Bystrica', TO_DATE('2022-04-05', 'YYYY-MM-DD'));

INSERT INTO Pouzivatel (Meno, Priezvisko, Email, Telefon, Adresa, Datum_registracie)
VALUES ('Milan', 'Kovář', 'milan.kovar@example.com', '+421905678901', 'Moyzesova 321, Nitra', TO_DATE('2022-05-12', 'YYYY-MM-DD'));

-- Insert Readers
INSERT INTO Citatel (ID_pouzivatela, Cislo_preukazu, Platnost_do, Status, Pocet_vypoziciek)
VALUES (1, 'CIT-001', TO_DATE('2025-01-15', 'YYYY-MM-DD'), 'Aktívny', 0);

INSERT INTO Citatel (ID_pouzivatela, Cislo_preukazu, Platnost_do, Status, Pocet_vypoziciek)
VALUES (2, 'CIT-002', TO_DATE('2025-02-20', 'YYYY-MM-DD'), 'Aktívny', 0);

INSERT INTO Citatel (ID_pouzivatela, Cislo_preukazu, Platnost_do, Status, Pocet_vypoziciek)
VALUES (3, 'CIT-003', TO_DATE('2025-03-10', 'YYYY-MM-DD'), 'Pozastavený', 0);

-- Insert Employees
INSERT INTO Zamestnanec (ID_pouzivatela, Osobne_cislo, Datum_nastupu, Oddelenie, Titul)
VALUES (4, 'ZAM-001', TO_DATE('2020-04-05', 'YYYY-MM-DD'), 'Katalogizácia', 'Mgr.');

INSERT INTO Zamestnanec (ID_pouzivatela, Osobne_cislo, Datum_nastupu, Oddelenie, Titul)
VALUES (5, 'ZAM-002', TO_DATE('2021-05-12', 'YYYY-MM-DD'), 'Výpožičky', 'Ing.');

-- Insert Titles
INSERT INTO Titul (Nazov, Vydavatelstvo, Rok_vydania, Popis, Kategoria, Pocet_exemplarov)
VALUES ('Pán Prsteňov: Spoločenstvo prsteňa', 'Slovart', 2001, 'Prvá časť fantasy trilógie od J.R.R. Tolkiena', 'Fantasy', 200);

INSERT INTO Titul (Nazov, Vydavatelstvo, Rok_vydania, Popis, Kategoria, Pocet_exemplarov)
VALUES ('Harry Potter a Kameň mudrcov', 'Ikar', 2000, 'Prvá kniha zo série o mladom čarodejníkovi', 'Fantasy', 80);

INSERT INTO Titul (Nazov, Vydavatelstvo, Rok_vydania, Popis, Kategoria, Pocet_exemplarov)
VALUES ('National Geographic', 'National Geographic Society', 2023, 'Populárno-náučný časopis', 'Veda', 25);

INSERT INTO Titul (Nazov, Vydavatelstvo, Rok_vydania, Popis, Kategoria, Pocet_exemplarov)
VALUES ('Forbes', 'Forbes Media', 2023, 'Ekonomický časopis', 'Biznis', 100);

INSERT INTO Titul (Nazov, Vydavatelstvo, Rok_vydania, Popis, Kategoria, Pocet_exemplarov)
VALUES ('1984', 'Slovart', 2015, 'Dystopický román od George Orwella', 'Sci-fi', 50);

-- Insert Books
INSERT INTO Kniha (ID_titulu, ISBN, Autor, Pocet_stran, Zaner)
VALUES (1, '9788055603261', 'J.R.R. Tolkien', 432, 'Fantasy');

INSERT INTO Kniha (ID_titulu, ISBN, Autor, Pocet_stran, Zaner)
VALUES (2, '9788055159423', 'J.K. Rowling', 320, 'Fantasy');

INSERT INTO Kniha (ID_titulu, ISBN, Autor, Pocet_stran, Zaner)
VALUES (5, '9788055138887', 'George Orwell', 288, 'Sci-fi');

-- Insert Magazines
INSERT INTO Casopis (ID_titulu, ISSN, Rocnik, Cislo, Periodicita)
VALUES (3, '0027-9358', 2023, 3, 'Mesačník');

INSERT INTO Casopis (ID_titulu, ISSN, Rocnik, Cislo, Periodicita)
VALUES (4, '0015-6914', 2023, 4, 'Mesačník');

-- Insert Copies
INSERT INTO Exemplar (ID_titulu, Datum_nadobudnutia, Umiestnenie, Signatura, Status)
VALUES (1, TO_DATE('2022-01-10', 'YYYY-MM-DD'), 'Regál A, Polica 1', 'F-TOL-001', 'Dostupný');

INSERT INTO Exemplar (ID_titulu, Datum_nadobudnutia, Umiestnenie, Signatura, Status)
VALUES (1, TO_DATE('2022-01-10', 'YYYY-MM-DD'), 'Regál A, Polica 1', 'F-TOL-002', 'Dostupný');

INSERT INTO Exemplar (ID_titulu, Datum_nadobudnutia, Umiestnenie, Signatura, Status)
VALUES (2, TO_DATE('2022-02-15', 'YYYY-MM-DD'), 'Regál A, Polica 2', 'F-ROW-001', 'Vypožičaný');

INSERT INTO Exemplar (ID_titulu, Datum_nadobudnutia, Umiestnenie, Signatura, Status)
VALUES (3, TO_DATE('2023-03-01', 'YYYY-MM-DD'), 'Regál B, Polica 1', 'C-NAT-001', 'Dostupný');

INSERT INTO Exemplar (ID_titulu, Datum_nadobudnutia, Umiestnenie, Signatura, Status)
VALUES (4, TO_DATE('2023-04-05', 'YYYY-MM-DD'), 'Regál B, Polica 2', 'C-FOR-001', 'Dostupný');

INSERT INTO Exemplar (ID_titulu, Datum_nadobudnutia, Umiestnenie, Signatura, Status)
VALUES (5, TO_DATE('2022-06-10', 'YYYY-MM-DD'), 'Regál C, Polica 1', 'S-ORW-001', 'Vypožičaný');

-- Insert Reservations
INSERT INTO Rezervacia (ID_pouzivatela, ID_titulu, Datum_rezervacie, Datum_expiracie, Stav)
VALUES (1, 2, TO_DATE('2023-04-10', 'YYYY-MM-DD'), TO_DATE('2023-04-17', 'YYYY-MM-DD'), 'Aktívna');

INSERT INTO Rezervacia (ID_pouzivatela, ID_titulu, Datum_rezervacie, Datum_expiracie, Stav)
VALUES (2, 5, TO_DATE('2023-04-12', 'YYYY-MM-DD'), TO_DATE('2023-04-19', 'YYYY-MM-DD'), 'Vybavená');

-- Insert Loans
INSERT INTO Vypozicka (ID_pouzivatela, ID_exemplaru, Datum_vypozicania, Planovany_datum_vratenia, Skutocny_datum_vratenia, Stav)
VALUES (1, 3, TO_DATE('2023-04-10', 'YYYY-MM-DD'), TO_DATE('2023-05-10', 'YYYY-MM-DD'), NULL, 'Aktívna');

INSERT INTO Vypozicka (ID_pouzivatela, ID_exemplaru, Datum_vypozicania, Planovany_datum_vratenia, Skutocny_datum_vratenia, Stav)
VALUES (2, 6, TO_DATE('2023-04-12', 'YYYY-MM-DD'), TO_DATE('2023-05-12', 'YYYY-MM-DD'), TO_DATE('2023-04-30', 'YYYY-MM-DD'), 'Vrátená');

-- Insert Reviews
INSERT INTO Recenzia (ID_pouzivatela, ID_titulu, Hodnotenie, Sprava, Datum)
VALUES (1, 1, 5, 'Vynikajúca kniha, jedno z najlepších fantasy diel všetkých čias.', TO_DATE('2023-03-15', 'YYYY-MM-DD'));

INSERT INTO Recenzia (ID_pouzivatela, ID_titulu, Hodnotenie, Sprava, Datum)
VALUES (2, 2, 4, 'Skvelá kniha pre mladých čitateľov, fascinujúci magický svet.', TO_DATE('2023-03-20', 'YYYY-MM-DD'));

INSERT INTO Recenzia (ID_pouzivatela, ID_titulu, Hodnotenie, Sprava, Datum)
VALUES (3, 5, 5, 'Nadčasový dystopický román, stále aktuálny aj dnes.', TO_DATE('2023-03-25', 'YYYY-MM-DD'));



-- 3rd part of the project
-- Extra Data for better filtering
INSERT INTO Pouzivatel (Meno, Priezvisko, Email, Telefon, Adresa, Datum_registracie)
VALUES ('Lucia', 'Horváthová', 'lucia.horvathova@example.com', '+421906789012', 'Dlhá 5, Trnava', TO_DATE('2022-06-01', 'YYYY-MM-DD'));

INSERT INTO Pouzivatel (Meno, Priezvisko, Email, Telefon, Adresa, Datum_registracie)
VALUES ('Martin', 'Šimek', 'martin.simek@example.com', '+421907890123', 'Jarná 22, Prešov', TO_DATE('2022-07-10', 'YYYY-MM-DD'));

INSERT INTO Pouzivatel (Meno, Priezvisko, Email, Telefon, Adresa, Datum_registracie)
VALUES ('Barbora', 'Petrová', 'barbora.petrova@example.com', '+421908901234', 'Letná 7, Poprad', TO_DATE('2022-08-18', 'YYYY-MM-DD'));

INSERT INTO Citatel (ID_pouzivatela, Cislo_preukazu, Platnost_do, Status, Pocet_vypoziciek)
VALUES (6, 'CIT-004', TO_DATE('2025-06-01', 'YYYY-MM-DD'), 'Aktívny', 1);

INSERT INTO Citatel (ID_pouzivatela, Cislo_preukazu, Platnost_do, Status, Pocet_vypoziciek)
VALUES (7, 'CIT-005', TO_DATE('2025-07-10', 'YYYY-MM-DD'), 'Pozastavený', 0);

INSERT INTO Zamestnanec (ID_pouzivatela, Osobne_cislo, Datum_nastupu, Oddelenie, Titul)
VALUES (8, 'ZAM-003', TO_DATE('2021-08-18', 'YYYY-MM-DD'), 'Oddelenie vývoja databáz', 'PhDr.');

INSERT INTO Titul (Nazov, Vydavatelstvo, Rok_vydania, Popis, Kategoria, Pocet_exemplarov)
VALUES ('Bratstvo čiernej dýky', 'Ikar', 2012, 'Moderný mestský fantasy román s upírskou tematikou', 'Fantasy', 30);

INSERT INTO Titul (Nazov, Vydavatelstvo, Rok_vydania, Popis, Kategoria, Pocet_exemplarov)
VALUES ('Sapiens: Stručná história ľudstva', 'Tatran', 2018, 'Fascinujúci pohľad na vývoj ľudskej civilizácie', 'História',120);

INSERT INTO Kniha (ID_titulu, ISBN, Autor, Pocet_stran, Zaner)
VALUES (6, '9788055163223', 'J.R. Ward', 400, 'Fantasy');

INSERT INTO Kniha (ID_titulu, ISBN, Autor, Pocet_stran, Zaner)
VALUES (7, '9788022209223', 'Yuval Noah Harari', 448, 'História');

INSERT INTO Exemplar (ID_titulu, Datum_nadobudnutia, Umiestnenie, Signatura, Status)
VALUES (6, TO_DATE('2022-09-01', 'YYYY-MM-DD'), 'Regál D, Polica 1', 'F-WAR-001', 'Dostupný');

INSERT INTO Exemplar (ID_titulu, Datum_nadobudnutia, Umiestnenie, Signatura, Status)
VALUES (7, TO_DATE('2022-10-01', 'YYYY-MM-DD'), 'Regál E, Polica 3', 'H-HAR-001', 'Vypožičaný');

INSERT INTO Rezervacia (ID_pouzivatela, ID_titulu, Datum_rezervacie, Datum_expiracie, Stav)
VALUES (6, 6, TO_DATE('2023-06-05', 'YYYY-MM-DD'), TO_DATE('2023-06-12', 'YYYY-MM-DD'), 'Aktívna');


INSERT INTO Vypozicka (ID_pouzivatela, ID_exemplaru, Datum_vypozicania, Planovany_datum_vratenia, Skutocny_datum_vratenia, Stav)
VALUES (6, 7, TO_DATE('2023-06-06', 'YYYY-MM-DD'), TO_DATE('2023-07-06', 'YYYY-MM-DD'), NULL, 'Aktívna');

INSERT INTO Recenzia (ID_pouzivatela, ID_titulu, Hodnotenie, Sprava, Datum)
VALUES (6, 7, 5, 'Skvelo napísané a veľmi poučné. Harari píše pútavo.', TO_DATE('2023-06-10', 'YYYY-MM-DD'));


--Selects
-- Show information about employees
SELECT p.meno || ' ' || p.priezvisko AS zamestnanec, p.ID_pouzivatela ,p.Telefon, p.Email, p.Adresa
FROM Zamestnanec z
JOIN Pouzivatel p ON z.ID_pouzivatela = p.ID_pouzivatela;

-- Show information about reservations
SELECT p.meno || ' ' || p.priezvisko AS citatel, r.ID_titulu, r.Datum_expiracie, r.Datum_rezervacie, r.Stav
FROM Rezervacia r
JOIN Pouzivatel p ON p.ID_pouzivatela = r.ID_pouzivatela;

-- Show reviews
SELECT p.meno || ' ' || p.priezvisko AS recenzent, t.Nazov, r.Hodnotenie, r.Sprava
FROM Recenzia r
JOIN Titul t ON r.ID_titulu = t.ID_titulu
JOIN Pouzivatel p ON r.ID_pouzivatela = p.ID_pouzivatela ;

-- Show available titles and their count
SELECT
    t.Nazov,
    COUNT(e.ID_exemplaru) AS Pocet_dostupnych
FROM Titul t
JOIN Exemplar e ON t.ID_titulu = e.ID_titulu
WHERE e.Status = 'Dostupný'
GROUP BY t.Nazov;

-- Show active borrowings
SELECT
    p.Meno || ' ' || p.Priezvisko AS Citatel,
    t.Nazov AS Kniha,
    v.Datum_vypozicania,
    v.Planovany_datum_vratenia
FROM Vypozicka v
JOIN Pouzivatel p ON v.ID_pouzivatela = p.ID_pouzivatela
JOIN Exemplar e ON v.ID_exemplaru = e.ID_exemplaru
JOIN Titul t ON e.ID_titulu = t.ID_titulu
WHERE v.Stav = 'Aktívna';

-- Show all names with their role
SELECT
    p.ID_pouzivatela,
    p.Meno,
    p.Priezvisko,
    CASE
        WHEN EXISTS (SELECT 1 FROM Citatel c WHERE c.ID_pouzivatela = p.ID_pouzivatela) THEN 'Citatel'
        WHEN EXISTS (SELECT 1 FROM Zamestnanec z WHERE z.ID_pouzivatela = p.ID_pouzivatela) THEN 'Zamestnanec'
        ELSE 'Neznámy'
    END AS Typ_pouzivatela
FROM Pouzivatel p;

-- Show which books wasnt returned
SELECT p.Meno || ' ' || p.Priezvisko AS citatel
FROM Pouzivatel p
WHERE EXISTS (
    SELECT 1
    FROM Vypozicka v
    WHERE v.ID_pouzivatela = p.ID_pouzivatela
    AND v.Skutocny_datum_vratenia IS NULL
);

-- Show titles based on genre
SELECT
    Nazov,
    Kategoria,
    Pocet_exemplarov
FROM Titul
WHERE Kategoria = 'Fantasy';

-- Show how many borrows and reservations reader made.
SELECT p.Meno || ' ' || p.Priezvisko AS Citatel, c.ID_pouzivatela, v.Stav, COUNT(DISTINCT v.ID_vypozicky) AS pocet_vypoziciek, COUNT(DISTINCT r.ID_rezervacie) AS pocet_rezervaci
FROM Citatel c
JOIN Vypozicka v ON c.ID_pouzivatela = v.ID_pouzivatela
JOIN Pouzivatel p ON c.ID_pouzivatela = p.ID_pouzivatela
JOIN Rezervacia r ON p.ID_pouzivatela = r.ID_pouzivatela
GROUP BY p.Meno, p.Priezvisko, c.ID_pouzivatela, v.Stav;

-- Show only reviewed titles
SELECT nazov
FROM Titul
WHERE id_titulu IN (
    SELECT id_titulu
    FROM Recenzia
);

COMMIT;