DROP VIEW prepravaNad30kg;
DROP VIEW produktNad100Eur;
DROP VIEW pouzivateliaGmailuKtoryVNomNemajuMeno;
DROP VIEW zakazniciNakupiliVDanomObchode;
DROP VIEW obchodZlavaNadpolovicna;
DROP VIEW pocetTypovZliavPreSortiment;
DROP VIEW zoznamProduktovSPoctomJedna;
DROP VIEW produktyDanehoObchoduAIchCeny;
DROP VIEW statistikyDodaniaTovaru;
DROP VIEW zoznamZakaznikovKtorySiKupiliTovar;
DROP TABLE pracuje_pre;
DROP TABLE produkt_obchod;
DROP TABLE Obchod;
DROP TABLE Produkt;
DROP TABLE Prepravna_spolocnost;
DROP TABLE Zakaznik;
DROP TABLE Zlavomat;
DROP SEQUENCE seq_zlavomat_pk;

CREATE SEQUENCE seq_zlavomat_pk START 1;

CREATE TABLE Zlavomat
(
  vyska_zlavy INT NOT NULL,
  typ_zlavy VARCHAR(150),
  kontakt VARCHAR(150) NOT NULL,
  nazov VARCHAR(105) NOT NULL,
  id INTEGER PRIMARY KEY DEFAULT nextval('seq_zlavomat_pk')
);

CREATE TABLE Zakaznik
(
  meno VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL,
  adresa VARCHAR(150) NOT NULL,
  mesto VARCHAR(150) NOT NULL,
  id SERIAL PRIMARY KEY ,
  priezvisko VARCHAR(150) NOT NULL,
  telefonne_cislo INTEGER NOT NULL,
  cislo_uctu INTEGER NOT NULL,
  id_zlavomatu INTEGER NOT NULL,
  FOREIGN KEY (id_zlavomatu) REFERENCES Zlavomat(id)
);

CREATE TABLE Prepravna_spolocnost
(
  typ_prepravy VARCHAR(150) NOT NULL,
  cas_dodania DATE NOT NULL,
  nazov VARCHAR(150) NOT NULL,
  id SERIAL PRIMARY KEY,
  id_zlavomatu INTEGER NOT NULL,
  FOREIGN KEY (id_zlavomatu) REFERENCES Zlavomat(id)
);

CREATE TABLE Produkt
(
  cena INTEGER NOT NULL,
  nazov VARCHAR(150) NOT NULL,
  pocet_kusov INTEGER NOT NULL,
  sposob_dopravy VARCHAR(150) DEFAULT 'do 50 kg',
  id SERIAL PRIMARY KEY,
  id_zlavomatu INTEGER NOT NULL,
  id_zakaznika INTEGER NOT NULL,
  FOREIGN KEY (id_zlavomatu) REFERENCES Zlavomat(id),
  FOREIGN KEY (id_zakaznika) REFERENCES Zakaznik(id)
);

CREATE TABLE Obchod
(
  nazov VARCHAR(150) NOT NULL,
  sortiment VARCHAR(150) NOT NULL,
  bankove_spojenie INTEGER NOT NULL,
  id SERIAL PRIMARY KEY
);

CREATE TABLE pracuje_pre
(
  id_obchodu INTEGER NOT NULL,
  id_prepravnej_spolocnosti INTEGER NOT NULL,
  PRIMARY KEY (id_obchodu, id_prepravnej_spolocnosti),
  FOREIGN KEY (id_prepravnej_spolocnosti) REFERENCES Prepravna_spolocnost(id),
  FOREIGN KEY (id_obchodu) REFERENCES Obchod(id)
);

CREATE TABLE produkt_obchod
(
  id_obchodu INTEGER NOT NULL,
  id_produktu INTEGER NOT NULL,
  PRIMARY KEY (id_obchodu,id_produktu),
  FOREIGN KEY (id_obchodu) REFERENCES Obchod(id),
  FOREIGN KEY (id_produktu) REFERENCES Produkt(id)
);

INSERT INTO Zlavomat (vyska_zlavy, typ_zlavy, kontakt, nazov) VALUES (50,'jarna','helpdesk@heureka.sk','heureka');
INSERT INTO Zlavomat (vyska_zlavy, typ_zlavy, kontakt, nazov) VALUES (10,'vernostna','ozlave@ozlave.sk','ozlave');
INSERT INTO Zlavomat (vyska_zlavy, typ_zlavy, kontakt, nazov) VALUES (70,'black friday','zlavomat@zlavomat.sk','zlavomat');
INSERT INTO Zlavomat (vyska_zlavy, typ_zlavy, kontakt, nazov) VALUES (50,'vypredaj','ezlva@ezlava.sk','ezlava');
INSERT INTO Zlavomat (vyska_zlavy, typ_zlavy, kontakt, nazov) VALUES (50,'kuponova','kupon@kuponovnik.sk','kuponovnik');
INSERT INTO Zlavomat (vyska_zlavy, typ_zlavy, kontakt, nazov) VALUES (90,'likvidacna','info@zlavadna.sk','zlavadna');

INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('marian','thew7u11@gmail.com','javorova 8','gelnica','ferenc',0908111111,7557686,1);
INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('erik','erik@gmail.com','drienov 20','drienov','jakuba',0915222222,8754567,1);
INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('vlado','vlado@post.sk','hlavna 4','kezmarok','kovalsky',0944333333,8754567,2);
INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('juraj','juraj271@gmail.com','zeleznicna 3','margecany','klein',0904444444,2544567,2);
INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('radovan','ofmann@azet.sk','kvp 10','kosice','kocik',0944555555,985467,3);
INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('kristian','rovastus@gmail.com','jarna 2','budimir','golias',0911666666,985467,4);
INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('matus','koporec@gmail.com','mnisek 8','mnisek nad hnilcom','koporec',0911777777,985467,4);
INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('martinka','filipova@gmail.com','snp 3','spisska nova ves','filipova',0904888888,45547,5);
INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('jana','stastna@gmail.com','ceskoslovenskej armady 31','trebisov','stastna',0910999999,87547,6);

INSERT INTO Prepravna_spolocnost (typ_prepravy, cas_dodania, nazov, id_zlavomatu) VALUES ('do 30kg',current_date+1,'UPC',1);
INSERT INTO Prepravna_spolocnost (typ_prepravy, cas_dodania, nazov, id_zlavomatu) VALUES ('do 100kg',current_date+1,'TOPTRANS',2);
INSERT INTO Prepravna_spolocnost (typ_prepravy, cas_dodania, nazov, id_zlavomatu) VALUES ('do 30kg',current_date+1,'posta',3);
INSERT INTO Prepravna_spolocnost (typ_prepravy, cas_dodania, nazov, id_zlavomatu) VALUES ('do 70kg',current_date+2,'geis',4);
INSERT INTO Prepravna_spolocnost (typ_prepravy, cas_dodania, nazov, id_zlavomatu) VALUES ('do 50kg',current_date+5,'dpd',5);

INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (760,'telefon',1,2,1);
INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (200,'ssd',1,1,2);
INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (800,'notebook',2,3,3);
INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (300,'tablet',1,4,4);

INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (50,'sluchadla',3,3,5);
INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (43,'hra',2,1,6);
INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (195,'graficka karta',1,2,7);
INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (15,'puzdro na telefon',1,3,8);

INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('alza.sk','elektronika',578754);
INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('hej.sk','elektronika',87787);
INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('mall.sk','elektronika',448487);
INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('muziker.sk','hudobone nastroje',324451);
INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('hrac.sk','hry a softver',977878);
INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('tichepc.sk','pc komponenty',811454);
INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('vsetkonamobil.sk','prislusenstvo pre telefony',115447);
INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('nahodsa.sk','oblecenie',54844);

INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (1,1);
INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (1,2);
INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (2,3);
INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (3,4);
INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (4,5);

INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (5,6);
INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (6,7);
INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (7,8);

INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (1,1);
INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (2,3);
INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (3,4);
INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (4,5);
INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (5,5);
INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (6,3);
INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (7,1);

/*Ziskanie prepravnej spolocnosti, ktora prepravuje nad 30kg*/
CREATE VIEW prepravaNad30kg AS
  SELECT nazov, typ_prepravy
  FROM Prepravna_spolocnost
  WHERE NOT typ_prepravy='do 30kg'
  ORDER BY cas_dodania
;

/*Ziskanie produktu s cenou nad 100 Eur*/
CREATE VIEW produktNad100Eur AS
  SELECT nazov, cena
  FROM Produkt
  WHERE cena > 100
  ORDER BY cena
;

/* Ziskanie mena a priezviska zakaznikov, ktory nakupili v danom obchode*/
CREATE VIEW zakazniciNakupiliVDanomObchode AS
SELECT meno,priezvisko
FROM Zakaznik
  JOIN Produkt ON Zakaznik.id = Produkt.id
  JOIN produkt_obchod ON Produkt.id = produkt_obchod.id_produktu
WHERE id_obchodu=1;

/*Ziskanie obchodu, ktory ponuka vysku zlavy vacsiu ako 50%*/
CREATE VIEW obchodZlavaNadpolovicna AS
SELECT Obchod.nazov,sortiment
FROM Obchod
JOIN produkt_obchod ON Obchod.id = produkt_obchod.id_obchodu
JOIN produkt ON produkt_obchod.id_produktu = Produkt.id
JOIN zakaznik ON Produkt.id_zakaznika = Zakaznik.id
JOIN Zlavomat ON Produkt.id_zlavomatu = Zlavomat.id
WHERE vyska_zlavy > 50;

/*Ziskanie poctu typov zliav spojenych s danym sortimentom*/
CREATE VIEW pocetTypovZliavPreSortiment AS
SELECT count(DISTINCT typ_zlavy) AS pocet
FROM Zlavomat
JOIN Zakaznik ON Zlavomat.id = Zakaznik.id_zlavomatu
JOIN Produkt ON Zakaznik.id = Produkt.id_zakaznika
JOIN produkt_obchod ON Produkt.id = produkt_obchod.id_produktu
JOIN Obchod ON produkt_obchod.id_obchodu = Obchod.id
WHERE sortiment='elektronika';

/* Ziskanie obchodu, sortimentu a produktu, kde pocet kusov produktu je 1*/
CREATE VIEW zoznamProduktovSPoctomJedna AS
SELECT obchod.nazov AS obchod, obchod.sortiment,produkt.nazov AS produkt
FROM obchod
JOIN produkt_obchod ON Obchod.id = produkt_obchod.id_obchodu
JOIN produkt ON produkt_obchod.id_produktu = Produkt.id
WHERE pocet_kusov=1
ORDER BY obchod.nazov;

/*Ziskanie produktov daneho obchodu alza.sk a ich ceny v roznych menach*/
CREATE VIEW produktyDanehoObchoduAIchCeny AS
SELECT produkt.nazov AS produkt,
  ROUND(cena,2)||'€' AS EUR,
  ROUND((cena*0.76),2)||'$' AS USD,
  ROUND((cena * 19.20),2)||'CK' AS CZK
FROM Produkt
JOIN produkt_obchod ON Produkt.id = produkt_obchod.id_produktu
JOIN Obchod ON produkt_obchod.id_obchodu = Obchod.id
WHERE Obchod.nazov='alza.sk'
ORDER BY ROUND(cena,2) DESC;
;

/*Ziskanie pouzivatelov gmailu, ktorym sa nenachadza ich meno ani ich priezvisko v ich emailovej adrese*/
CREATE VIEW pouzivateliaGmailuKtoryVNomNemajuMeno AS
  SELECT meno,priezvisko,email
  FROM Zakaznik
  WHERE email LIKE '%gmail.com%'
  AND Zakaznik.email NOT LIKE CONCAT(Zakaznik.meno,'%gmail.com%')
  AND Zakaznik.email NOT LIKE CONCAT(Zakaznik.priezvisko,'%gmail.com%')
  ORDER BY priezvisko,meno;

/*Ziskanie max, min a priemernej dlzky dodania tovaru dopravnou spolocnostou pre dany produkt*/
CREATE VIEW statistikyDodaniaTovaru AS
SELECT
  produkt.nazov AS produkt,
  max((EXTRACT(DAY FROM cas_dodania)*24)-(extract(DAY FROM current_date))*24) AS longest,
  min((EXTRACT(DAY FROM cas_dodania)-extract(DAY FROM current_date))*24) AS shortest,
  avg((extract(DAY FROM cas_dodania)-extract(DAY FROM current_date))*24) AS average
FROM prepravna_spolocnost
JOIN pracuje_pre ON Prepravna_spolocnost.id = pracuje_pre.id_prepravnej_spolocnosti
JOIN Obchod ON pracuje_pre.id_obchodu = Obchod.id
JOIN produkt_obchod ON Obchod.id = produkt_obchod.id_obchodu
JOIN Produkt ON produkt_obchod.id_produktu = Produkt.id
GROUP BY produkt.nazov
ORDER BY max((EXTRACT(DAY FROM cas_dodania)*24)-(extract(DAY FROM current_date))*24) DESC;
;

/*Zoznam zakaznikov, ktory si kupil dany tovar z daneho obchodu*/
CREATE VIEW zoznamZakaznikovKtorySiKupiliTovar AS
SELECT DISTINCT meno,priezvisko, produkt.nazov AS produkt,produkt.pocet_kusov, obchod.nazov AS obchod FROM Zakaznik
LEFT OUTER JOIN Produkt ON Zakaznik.id = Produkt.id_zakaznika
  LEFT OUTER JOIN produkt_obchod ON Produkt.id = produkt_obchod.id_produktu
  LEFT OUTER JOIN obchod ON produkt_obchod.id_obchodu = Obchod.id
ORDER BY pocet_kusov DESC
;