-- uklid, kdyby objekt daneho jmena uz existoval (pozor destruktivni)
DROP VIEW IF EXISTS zakaznik CASCADE;

-- zakladni tvorba tabulky
-- omezeni NOT NULL zakazuje NULL hodnoty v daném sloupci
CREATE TABLE zakaznik (
    cislo INT NOT NULL,
    jmeno VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    adresa VARCHAR(200) NOT NULL
);
-- Urcujici vlastnost: Zakaznik s cislem cislo se jmenuje jmeno, bydli v adresa a má email email.


-- uklid, kdyby objekt daneho jmena uz existoval (pozor destruktivni)
DROP VIEW IF EXISTS produkt CASCADE;


-- tabulka s primarnim a alternativnim klicem
CREATE TABLE produkt (
    ID INT PRIMARY KEY,
    nazev VARCHAR(100) NOT NULL,
    cena DECIMAL(10,2) NOT NULL,
    popis TEXT NOT NULL,
    UNIQUE (nazev) -- alternativní klíč
);
-- Urcujici vlastnost Produkt ID ID s nazvem nazev a popisem popis stoji cena korun.

-- uklid, kdyby objekt daneho jmena uz existoval (pozor destruktivni)
DROP VIEW IF EXISTS nakup CASCADE;


-- tabulka s cizim klicem
CREATE TABLE nakup (
    ID INT PRIMARY KEY,
    ID_Zakaznika INT NOT NULL,
    datum DATE NOT NULL,
    FOREIGN KEY (ID_Zakaznika) REFERENCES Zakaznik(cislo)
);
-- Urcujici vlastnost: Nakup ID ID provedl zakaznik ID_zakaznika dne datum.

-- u zakaznika jsme zapomneli na klice, postgres nas tedy nenecha referencovat
-- (ukazka upravy tabulky)
ALTER TABLE zakaznik
ADD CONSTRAINT pk_zakaznik PRIMARY KEY (cislo),
ADD CONSTRAINT uq_zakaznik_email UNIQUE (email);




-- uklid, kdyby objekt daneho jmena uz existoval (pozor destruktivni)
DROP VIEW IF EXISTS polozka_nakupu CASCADE;


-- pro rozsireni obzoru:
-- existuji i jine typy constraintu 
-- muzeme mit slozene klice
CREATE TABLE polozka_nakupu (
    ID_nakup INT NOT NULL,
    ID_produkt INT NOT NULL,
    mnozstvi INT DEFAULT 1 CHECK (mnozstvi > 0),
    PRIMARY KEY (ID_nakup, ID_produkt),
    FOREIGN KEY (ID_nakup) REFERENCES Nakup(ID),
    FOREIGN KEY (ID_produkt) REFERENCES Produkt(ID)
);

-- Urcujici vlastnost: polozka ID_produkt byla zakoupena v nakupu ID_nakup v mnozstvi kusech.


-- vypis zatim prazdnych tabulek
TABLE zakaznik;
TABLE produkt;
TABLE nakup;
TABLE polozka_nakupu;


-- tabulku lze i zrusit (destruktivni, vcetne dat)
-- takto to postgres nedovoli, protoze na tabulce neco zavisi
DROP TABLE nakup;

-- muzme tedy pridat slovo CASCADE a tabulka s sebou vezme vse zavisejici
-- velmi destruktivni
-- CASCADE a RESTRICT se da specifikovat i primo u cizich klicu (ON DELETE CASCADE/RESTRICT) vizte dokumetnaci
DROP TABLE nakup CASCADE;


--vratime zpet smazane
-- tabulka
CREATE TABLE nakup (
    ID INT PRIMARY KEY,
    ID_Zakaznika INT NOT NULL,
    datum DATE NOT NULL,
    FOREIGN KEY (ID_Zakaznika) REFERENCES Zakaznik(cislo)
);

-- cizi klic
ALTER TABLE polozka_nakupu
ADD CONSTRAINT fk_nakup FOREIGN KEY (ID_nakup) REFERENCES nakup(id);




--naplnime daty

INSERT INTO Zakaznik (cislo, jmeno, email, adresa) VALUES
(1, 'Jan Novák', 'jan.novak@email.cz', 'Praha 4, Česká 12'),
(2, 'Petra Dvořáková', 'petra.dvorakova@email.cz', 'Brno, Husova 33'),
(3, 'Karel Svoboda', 'karel.svoboda@email.cz', 'Ostrava, Nádražní 5'),
(4, 'Lucie Horáková', 'lucie.horakova@email.cz', 'Plzeň, Klatovská 101'),
(5, 'Marek Král', 'marek.kral@email.cz', 'Hradec Králové, Třebešská 8'),
(6, 'Anna Benešová', 'anna.benesova@email.cz', 'Zlín, Dlouhá 55'),
(7, 'Michal Pokorný', 'michal.pokorny@email.cz', 'Olomouc, tř. Svobody 21'),
(8, 'Eva Malá', 'eva.mala@email.cz', 'Liberec, Sokolská 2'),
(9, 'Tomáš Černý', 'tomas.cerny@email.cz', 'Pardubice, Hlavní 19'),
(10, 'Jitka Růžičková', 'jitka.ruzickova@email.cz', 'České Budějovice, Pražská 10');
INSERT INTO Produkt (ID, nazev, cena, popis) VALUES
(101, 'Televize Samsung 55" QLED', 19990.00, 'Ultra HD Smart TV s rozlišením 4K'),
(102, 'Notebook Lenovo IdeaPad 5', 15990.00, '15.6" Full HD, AMD Ryzen 5, 16 GB RAM'),
(103, 'Sluchátka Sony WH-1000XM5', 7490.00, 'Bezdrátová sluchátka s aktivním potlačením hluku'),
(104, 'Mobilní telefon iPhone 14', 24990.00, '128 GB, 6.1", iOS 17'),
(105, 'Tablet Samsung Galaxy Tab S9', 18990.00, '11" AMOLED, 256 GB, Android 13'),
(106, 'Chytré hodinky Garmin Venu 3', 9990.00, 'GPS, měření tepu, sportovní režimy'),
(107, 'Bluetooth reproduktor JBL Charge 5', 3990.00, 'Voděodolný přenosný reproduktor s dlouhou výdrží'),
(108, 'Myš Logitech MX Master 3S', 2490.00, 'Ergonomická bezdrátová myš pro profesionály'),
(109, 'Klávesnice Keychron K6', 2890.00, 'Mechanická bezdrátová klávesnice s RGB podsvícením'),
(110, 'Externí disk WD My Passport 2TB', 2490.00, 'Přenosný USB 3.0 disk s kapacitou 2 TB'),
(111, 'Drahý nemsmysl', 24900000.00, 'Nablýskané nic');
INSERT INTO Nakup (ID, ID_Zakaznika, datum) VALUES
(1001, 1, '2025-10-01'),
(1002, 2, '2025-10-02'),
(1003, 3, '2025-10-03'),
(1004, 1, '2025-10-04'),
(1005, 4, '2025-10-05'),
(1006, 5, '2025-10-06'),
(1007, 6, '2025-10-07'),
(1008, 7, '2025-10-08'),
(1009, 8, '2025-10-09'),
(1010, 9, '2025-10-10');
INSERT INTO Polozka_nakupu (ID_nakup, ID_produkt, mnozstvi) VALUES
(1001, 104, 1), -- iPhone 14
(1001, 108, 1), -- Myš Logitech
(1002, 102, 1), -- Notebook Lenovo
(1003, 103, 1), -- Sluchátka Sony
(1003, 107, 1), -- Reproduktor JBL
(1004, 109, 1), -- Klávesnice Keychron
(1004, 110, 1), -- Externí disk
(1005, 101, 1), -- Televize Samsung
(1006, 106, 2), -- Hodinky Garmin
(1007, 105, 1), -- Tablet Samsung
(1008, 107, 1), -- Reproduktor JBL
(1008, 108, 1), -- Myš Logitech
(1009, 103, 1), -- Sluchátka Sony
(1009, 109, 1), -- Klávesnice Keychron
(1010, 110, 1); -- Externí disk


SELECT * FROM nakup;

-- Pro prunik, sjednoceni a rozdil potrebujeme tabulky nad stejnym schematem
DROP VIEW IF EXISTS dobry_produkt;
CREATE TABLE dobry_produkt (
    ID INT PRIMARY KEY,
    nazev VARCHAR(100)
);

-- Urcujici vlastnost: produkt Id ID s nazvem nazev povazuji za dobry

DROP VIEW IF EXISTS spatny_produkt;
CREATE TABLE spatny_produkt (
    ID INT PRIMARY KEY,
    nazev VARCHAR(100)
);

-- Urcujici vlastnost: produkt Id ID s nazvem nazev povazuji za spatny

DELETE FROM dobry_produkt;
DELETE FROM spatny_produkt;

--data
INSERT INTO dobry_produkt VALUES
(1, 'Televize Samsung 55" QLED'),
(2, 'Notebook Lenovo IdeaPad 5'),
(3, 'Sluchátka Sony WH-1000XM5'),
(4, 'Mobilní telefon iPhone 14'),
(5, 'Chytré hodinky Garmin Venu 3');

INSERT INTO spatny_produkt VALUES
(3, 'Sluchátka Sony WH-1000XM5'),   -- stejné jako v dobrých
(4, 'Mobilní telefon iPhone 14'),   -- stejné jako v dobrých
(6, 'Tablet Samsung Galaxy Tab S9'),-- jen zde
(7, 'Reproduktor JBL Charge 5');    -- jen zde

TABLE dobry_produkt;
TABLE spatny_produkt;


-- vyraz SELECT
SELECT * FROM dobry_produkt;

SELECT * FROM spatny_produkt;

SELECT * from produkt;


-- sjednoceni tabulek
TABLE dobry_produkt
UNION
TABLE spatny_produkt;

-- Urcujici vlastnost: produkt s id id a nazvem nazev povazuji za dobry nebo spatny

--prunik tabulek
TABLE dobry_produkt
INTERSECT
TABLE spatny_produkt;

-- Urcujici vlastnost: produkt s id id a nazvem nazev povazuji za dobry a take za spatny


--rozdil tabulek
TABLE dobry_produkt
EXCEPT
TABLE spatny_produkt;

-- Urcujici vlastnost: produkt s id id a nazvem nazev povazuji za dobry a nepovazuji za spatny

-- prvky, ktere jsou v prave jedne tabulce
(TABLE dobry_produkt
EXCEPT
TABLE spatny_produkt)
UNION
(TABLE spatny_produkt
EXCEPT
TABLE dobry_produkt)
-- Urcujici vlastnost: produkt s id id a nazvem nazev povazuji za dobry a zaroven ne spatny nebo za spatny a zaroven ne dobry



-- totez, ale jinak
(TABLE dobry_produkt
UNION
TABLE spatny_produkt)
EXCEPT
(TABLE spatny_produkt
INTERSECT
TABLE dobry_produkt)
-- Urcujici vlastnost: produkt s id id a nazvem nazev povazuji za dobry nebo spatny a zaroven jej nepovazuji za spatny a zaroven dobry

-- mazani zaznamu, smazani (vsech) radku s id = 3
TABLE spatny_produkt;

DELETE FROM spatny_produkt 
WHERE
    id = 3;

TABLE spatny_produkt;


-- uprava zaznamu, zmena id u (vsech) radku s id = 4
TABLE spatny_produkt;

UPDATE spatny_produkt 
SET     id = 3
WHERE id = 4;

TABLE spatny_produkt;


TABLE produkt;

-- restrikce, produkty s cenou nad 10 000
SELECT *
FROM produkt
WHERE cena > 10000;

-- Urcujici vlastnsot: produkt id s nazvem nazev cenou cena a popiem popis, ktery stoji vice nez 10 000 korun

-- projekce, nazvy a ceny produktu
SELECT
	nazev
	, cena
FROM
	produkt;

-- Urcujici vlastnost: produkt s nazvem nazev stoji cena korun


-- prejmenovani, totez s prejmenovanym sloupcem nazev na produkt
SELECT
	nazev AS produkt,
	cena
FROM
	produkt;

-- Urcujici vlastnost: Produkt produkt stoji cena korun

TABLE zakaznik;

TABLE nakup;

SELECT
*
FROM
zakaznik
NATURAL JOIN
nakup;

-- prirozene spojeni
SELECT
*
FROM
	zakaznik
	NATURAL JOIN
	(SELECT
		id,
		id_zakaznika AS cislo,
		datum
	FROM
		nakup);
-- musime prejmenovat sloupec id_zakaznika na cislo, jinak nebude na cem spojovat (prazdny prunik schemat)

--Urcujici vlastnost: Zakaznik s cislem cislo, jmenem jmeno bydlici na adrese adresa s emailem email u nas nakoupil data datum nakup s id id

-- totez "rucne" - misto prejmenovani sloupcu specifikujeme pozadovanou rovnost
SELECT zakaznik.cislo,
    zakaznik.jmeno,
    zakaznik.adresa,
    zakaznik.email,
    nakup.id,
    nakup.datum
FROM
    zakaznik,
    nakup
WHERE
    nakup.id_zakaznika = zakaznik.cislo;


-- prunik pomoci prirozeneho spojeni  (stejna schemata)


-- nejprve znovu naplnte tabulky (staci spustit vhodne prikazy vyse)
TABLE dobry_produkt
INTERSECT
TABLE spatny_produkt;

SELECT
*
FROM
dobry_produkt
NATURAL JOIN
spatny_produkt

-- kartezsky soucin pomoci prirozeneho spojeni (disjunktni schemata)

SELECT
	*
FROM
	zakaznik
	NATURAL JOIN
	spatny_produkt;


-- kartezsky soucin obecne
SELECT
	*
FROM
	zakaznik,
	spatny_produkt;

SELECT
	*
FROM
	zakaznik
	CROSS JOIN
	spatny_produkt;



-- par ukazek zajimavejsich dotazu

-- kdo si od nas koupil jaky produkt
SELECT zakaznik.jmeno,
       produkt.nazev
FROM
	zakaznik,
	produkt,
	nakup,
	polozka_nakupu
WHERE
	zakaznik.cislo = nakup.id_zakaznika
	AND
	nakup.id = polozka_nakupu.id_nakup
	AND
	produkt.id = polozka_nakupu.id_produkt



-- kdy a kam jsme poslali klavesnici Keychron K6
SELECT zakaznik.adresa,
       nakup.datum
FROM
	zakaznik,
	produkt,
	nakup,
	polozka_nakupu
WHERE
	zakaznik.cislo = nakup.id_zakaznika
	AND
	nakup.id = polozka_nakupu.id_nakup
	AND
	produkt.id = polozka_nakupu.id_produkt
	AND
	produkt.nazev = 'Klávesnice Keychron K6'



-- jake dobre produkty si od nas koupil Jan Novák?
(SELECT
	produkt.nazev AS produkt
FROM
	zakaznik,
	produkt,
	nakup,
	polozka_nakupu
WHERE
	zakaznik.cislo = nakup.id_zakaznika
	AND
	nakup.id = polozka_nakupu.id_nakup
	AND
	produkt.id = polozka_nakupu.id_produkt
	AND
	zakaznik.jmeno = 'Jan Novák')
INTERSECT
(SELECT
	nazev AS produkt
FROM
	dobry_produkt)

	


-- kdo si od nas koupil nejaky dobry produkt a jaky

SELECT zakaznik.jmeno,
       produkt.nazev,
       produkt.popis
FROM
	zakaznik,
	produkt,
	nakup,
	polozka_nakupu
WHERE
	zakaznik.cislo = nakup.id_zakaznika
	AND
	nakup.id = polozka_nakupu.id_nakup
	AND
	produkt.id = polozka_nakupu.id_produkt
	AND
	produkt.nazev IN (SELECT nazev FROM dobry_produkt)  --konstrukce s IN, podivejte se do dokumentace


-- jmena zakaznuiku, jejichz adresa obsahuje olomouc (nezavisle na velikosti pismen)

SELECT
	jmeno
FROM
	zakaznik	
WHERE
	adresa ILIKE '%olomouc%';  -- podivejte se na ILIKE do dokumentace


-- produkty, ktere nikdo nikdy nekoupil
SELECT
	p.nazev
FROM
	produkt AS p      -- lze pojmenovavat tabulky (ktere mohou byt i ziskany vyrazem, AS lze vynechat)
WHERE
	p.id NOT IN (
    	     SELECT DISTINCT
	     	    id_produkt
    	     FROM
		polozka_nakupu);


-- lze i ruzne agreogvat a filtrovat podle vysledku, uvidime priste



-- pohledy, ukazky z prednasky
-- dotaz jsme jiz videli
CREATE VIEW nakupujici_zakaznik AS
SELECT
*
FROM
	zakaznik
	NATURAL JOIN
	(SELECT
		id,
		id_zakaznika AS cislo,
		datum
	FROM
		nakup);

-- lze delat pohled nad pohledem
CREATE VIEW jmeno_nakupujiciho AS
SELECT jmeno
FROM nakupujici_zakaznik;

--vse se ale vzdy vyhodnocuje, zkuste zmenit nejaka data v zakaznik nebo nakup
TABLE jmeno_nakupujiciho;
