use student025;

SET foreign_key_checks = 0;
DROP TABLE DRZAVA;
DROP TABLE POSTA;
DROP TABLE NASLOV;
DROP TABLE GOSTI;
DROP TABLE GOSTI_PRENOCISCE;
DROP TABLE PONUDBA_PRENOCISCE;
DROP TABLE PONUDNIKI;
DROP TABLE TIPI_PONUDBE;
DROP TABLE POPUSTI;
DROP TABLE RACUN;
DROP TABLE POSTAVKA;
DROP TABLE STORITEV;
DROP TABLE CENIK;
DROP TABLE SOBE;
DROP TABLE TIPI_SOB;
DROP TABLE TIP_PONUDNIKA;
SET foreign_key_checks = 1;

CREATE TABLE DRZAVA (
    ID INT not null,
    ime VARCHAR(45),
    kratica VARCHAR(3)
);
ALTER TABLE DRZAVA ADD CONSTRAINT PK_DRZAVA PRIMARY KEY(ID);

CREATE TABLE POSTA (
    ID INT not null,
    kraj VARCHAR(20),
    postna_st INT(4),
    DRZAVA_ID INT not null
);
ALTER TABLE POSTA ADD CONSTRAINT PK_POSTA PRIMARY KEY(ID);
ALTER TABLE POSTA ADD CONSTRAINT FK_POSTA_DRZAVA FOREIGN KEY(DRZAVA_ID) REFERENCES DRZAVA(ID);

CREATE TABLE NASLOV (
    ID INT not null,
    ulica_cesta VARCHAR(45),
    hisna_st INT,
    POSTA_ID INT not null
);
ALTER TABLE NASLOV ADD CONSTRAINT PK_NASLOV PRIMARY KEY(ID);
ALTER TABLE NASLOV ADD CONSTRAINT FK_NASLOV_POSTA FOREIGN KEY(POSTA_ID) REFERENCES POSTA(ID);

CREATE TABLE GOSTI (
    ID INT not null,
    ime VARCHAR(45) not null,
    priimek VARCHAR(45) not null,
    spol VARCHAR(45) not null,
    email VARCHAR(45) not null,
    tel INT ZEROFILL not null,
    davcna_st INT ZEROFILL not null,
    davcni_zavezanec BOOL not null,
    rojstni_datum DATE not null,
    NASLOV_ID INT not null
);
ALTER TABLE GOSTI ADD CONSTRAINT PK_GOSTI PRIMARY KEY(ID);
ALTER TABLE GOSTI ADD CONSTRAINT FK_GOSTI_NASLOV FOREIGN KEY(NASLOV_ID) REFERENCES NASLOV(ID);

CREATE TABLE TIPI_PONUDBE (
    ID INT not null,
    naziv VARCHAR(25)
);
ALTER TABLE TIPI_PONUDBE ADD CONSTRAINT PK_TIPIPONUDBE PRIMARY KEY(ID);

CREATE TABLE TIP_PONUDNIKA (
    ID INT not null,
    naziv VARCHAR(10)
);
ALTER TABLE TIP_PONUDNIKA ADD CONSTRAINT PK_TIPPONUDNIKA PRIMARY KEY(ID);

CREATE TABLE PONUDNIKI (
    ID INT not null,
    naziv VARCHAR(20),
    TIP_PONUDNIKA_ID INT not null
);
ALTER TABLE PONUDNIKI ADD CONSTRAINT PK_PONUDNIKI PRIMARY KEY(ID);
ALTER TABLE PONUDNIKI ADD CONSTRAINT FK_PONUDNIKI_TIPPONUDNIKA FOREIGN KEY(TIP_PONUDNIKA_ID) REFERENCES TIP_PONUDNIKA(ID);

CREATE TABLE PONUDBA_PRENOCISCE (
    ID INT not null,
    naziv VARCHAR(45),
    st_zvezdic INT,
    TIPI_PONUDBE_ID INT not null,
    PONUDNIKI_ID INT not null,
    NASLOV_ID INT not null
);
ALTER TABLE PONUDBA_PRENOCISCE ADD CONSTRAINT PK_PONUDBAPRENOCISCE PRIMARY KEY(ID);
ALTER TABLE PONUDBA_PRENOCISCE ADD CONSTRAINT FK_PONUDBAPRENOCISCE_TIPIPONUDBE FOREIGN KEY(TIPI_PONUDBE_ID) REFERENCES TIPI_PONUDBE(ID);
ALTER TABLE PONUDBA_PRENOCISCE ADD CONSTRAINT FK_PONUDBAPRENOCISCE_PONUDNIKI FOREIGN KEY(PONUDNIKI_ID) REFERENCES PONUDNIKI(ID);
ALTER TABLE PONUDBA_PRENOCISCE ADD CONSTRAINT FK_PONUDBAPRENOCISCE_NASLOV FOREIGN KEY(NASLOV_ID) REFERENCES NASLOV(ID);

CREATE TABLE SOBE (
    ID INT not null,
    zasedenost BOOL not null,
    TIPI_SOB_ID INT not null,
    PONUDBA_PRENOCISCE_ID INT not null
);
ALTER TABLE SOBE ADD CONSTRAINT PK_SOBE PRIMARY KEY(ID);
ALTER TABLE SOBE ADD CONSTRAINT FK_SOBE_PONUDBAPRENOCISCE FOREIGN KEY(PONUDBA_PRENOCISCE_ID) REFERENCES PONUDBA_PRENOCISCE(ID);

CREATE TABLE GOSTI_PRENOCISCE (
    ID INT not null,
    datum DATE,
    GOSTI_ID INT not null,
    PONUDBA_PRENOCISCE_ID INT not null,
    SOBE_ID INT not null
);
ALTER TABLE GOSTI_PRENOCISCE ADD CONSTRAINT PK_GOSTIPRENOCISCE PRIMARY KEY(ID);
ALTER TABLE GOSTI_PRENOCISCE ADD CONSTRAINT FK_GOSTIPRENOCISCE_GOSTI FOREIGN KEY(GOSTI_ID) REFERENCES GOSTI(ID);
ALTER TABLE GOSTI_PRENOCISCE ADD CONSTRAINT FK_GOSTIPRENOCISCE_PONUDBAPRENOCISCE FOREIGN KEY(PONUDBA_PRENOCISCE_ID) REFERENCES PONUDBA_PRENOCISCE(ID);
ALTER TABLE GOSTI_PRENOCISCE ADD CONSTRAINT FK_GOSTIPRENOCISCE_SOBE FOREIGN KEY(SOBE_ID) REFERENCES SOBE(ID);

CREATE TABLE POPUSTI (
    ID INT not null,
    kolicina_procenti INT,
    datum_od DATE,
    datum_do DATE,
    PONUDBA_PRENOCISCE_ID INT not null
);
ALTER TABLE POPUSTI ADD CONSTRAINT PK_POPUSTI PRIMARY KEY(ID);
ALTER TABLE POPUSTI ADD CONSTRAINT FK_POPUSTI_PONUDBAPRENOCISCE FOREIGN KEY(PONUDBA_PRENOCISCE_ID) REFERENCES PONUDBA_PRENOCISCE(ID);

CREATE TABLE RACUN (
    ID INT not null,
    datum_cas DATETIME,
    koncni_znesek INT,
    GOSTI_ID INT not null
);
ALTER TABLE RACUN ADD CONSTRAINT PK_RACUN PRIMARY KEY(ID);
ALTER TABLE RACUN ADD CONSTRAINT FK_RACUN_GOSTI FOREIGN KEY(GOSTI_ID) REFERENCES GOSTI(ID);

CREATE TABLE STORITEV (
    ID INT not null,
    naziv VARCHAR(30),
    PONUDBA_PRENOCISCE_ID INT not null
);
ALTER TABLE STORITEV ADD CONSTRAINT PK_STORITEV PRIMARY KEY(ID);
ALTER TABLE STORITEV ADD CONSTRAINT FK_STORITEV_PONUDBAPRENOCISCE FOREIGN KEY(PONUDBA_PRENOCISCE_ID) REFERENCES PONUDBA_PRENOCISCE(ID);

CREATE TABLE TIPI_SOB (
    ID INT not null,
    naziv VARCHAR(30)
);
ALTER TABLE TIPI_SOB ADD CONSTRAINT PK_TIPISOB PRIMARY KEY(ID);

CREATE TABLE POSTAVKA (
    ID INT not null,
    cena INT,
    DDV INT,
    RACUN_ID INT not null,
    SOBE_ID INT not null,
    STORITEV_ID INT not null
);
ALTER TABLE POSTAVKA ADD CONSTRAINT PK_POSTAVKA PRIMARY KEY(ID);
ALTER TABLE POSTAVKA ADD CONSTRAINT FK_POSTAVKA_RACUN FOREIGN KEY(RACUN_ID) REFERENCES RACUN(ID);
ALTER TABLE POSTAVKA ADD CONSTRAINT FK_POSTAVKA_SOBE FOREIGN KEY(SOBE_ID) REFERENCES SOBE(ID);
ALTER TABLE POSTAVKA ADD CONSTRAINT FK_POSTAVKA_STORITEV FOREIGN KEY(STORITEV_ID) REFERENCES STORITEV(ID) ON DELETE CASCADE;

CREATE TABLE CENIK (
    ID INT not null,
    cena INT,
    datum_od DATE,
    datum_do DATE,
    STORITEV_ID INT not null,
    SOBE_ID INT not null
);
ALTER TABLE CENIK ADD CONSTRAINT PK_CENIK PRIMARY KEY(ID);
ALTER TABLE CENIK ADD CONSTRAINT FK_CENIK_STORITEV FOREIGN KEY(STORITEV_ID) REFERENCES STORITEV(ID) ON DELETE CASCADE;
ALTER TABLE CENIK ADD CONSTRAINT FK_CENIK_SOBE FOREIGN KEY(SOBE_ID) REFERENCES SOBE(ID);

INSERT INTO DRZAVA VALUES (1,'Slovenija','SLO');
INSERT INTO DRZAVA VALUES (2,'Hrvaska','CRO');
INSERT INTO DRZAVA VALUES (3,'Nemcija','NEM');
INSERT INTO DRZAVA VALUES (4,'Italija','ITA');
INSERT INTO DRZAVA VALUES (5,'Poljska','POL');

INSERT INTO POSTA VALUES (1,'Celje','3000',1);
INSERT INTO POSTA VALUES (2,'Maribor','1253',2);
INSERT INTO POSTA VALUES (3,'Ljubljana','2000',3);
INSERT INTO POSTA VALUES (4,'Ptuj','3251',4);
INSERT INTO POSTA VALUES (5,'Velenje','2351',5);
INSERT INTO POSTA VALUES (6,'Krsko','5312',4);
INSERT INTO POSTA VALUES (7,'Lasko','1235',2);
INSERT INTO POSTA VALUES (8,'Kranj','3451',1);
INSERT INTO POSTA VALUES (9,'Koper','4312',4);
INSERT INTO POSTA VALUES (10,'Rogaska Slatina','3250',5);

INSERT INTO NASLOV VALUES (1,'Presernova','2',1);
INSERT INTO NASLOV VALUES (2,'Vegova','14',2);
INSERT INTO NASLOV VALUES (3,'Ljubljanska','3',3);
INSERT INTO NASLOV VALUES (4,'Smetanova','12',4);
INSERT INTO NASLOV VALUES (5,'Zidovska','42',5);
INSERT INTO NASLOV VALUES (6,'Kidriceva','33',6);
INSERT INTO NASLOV VALUES (7,'Mariborska','1',7);
INSERT INTO NASLOV VALUES (8,'Gobova','21',8);
INSERT INTO NASLOV VALUES (9,'Celjska','34',9);
INSERT INTO NASLOV VALUES (10,'Steklarska','15',10);

INSERT INTO GOSTI VALUES (1,'Janez','Kovac','Moski','janez.kovac@gmail.com',051432541,67242308,true,'1995-11-25',1);
INSERT INTO GOSTI VALUES (2,'Andraz','Novak','Moski','andraz.novak@gmail.com',070321231,67232508,false,'1975-03-01',2);
INSERT INTO GOSTI VALUES (3,'Stefka','Peterle','Zenska','stefka.peterle@gmail.com',061324231,32232308,true,'1964-02-27',3);
INSERT INTO GOSTI VALUES (4,'Matija','Stare','Moski','matija.stare@gmail.com',051454541,67235368,true,'1998-04-25',4);
INSERT INTO GOSTI VALUES (5,'Jana','Rudar','Zenska','jana.rudar@gmail.com',051432531,67243308,true,'1991-09-08',5);
INSERT INTO GOSTI VALUES (6,'Jozica','Rog','Zenska','jozica.rog@gmail.com',051432221,67232788,false,'1992-06-22',6);
INSERT INTO GOSTI VALUES (7,'Mateja','Nemec','Zenska','mateja.nemec@gmail.com',054332541,67832308,true,'1999-12-09',7);
INSERT INTO GOSTI VALUES (8,'Niko','Poljak','Moski','niko.poljak@gmail.com',051423541,67275308,true,'1986-02-16',8);
INSERT INTO GOSTI VALUES (9,'Marko','Horvat','Moski','marko.horvat@gmail.com',056432541,64232308,false,'1985-05-12',9);
INSERT INTO GOSTI VALUES (10,'Anja','Rus','Zenska','anja.rus@gmail.com',051443541,67432338,true,'1989-03-22',10);

INSERT INTO TIPI_PONUDBE VALUES (1,'Hotel');
INSERT INTO TIPI_PONUDBE VALUES (2,'Motel');
INSERT INTO TIPI_PONUDBE VALUES (3,'Pocitniska hisa');
INSERT INTO TIPI_PONUDBE VALUES (4,'Hotel');
INSERT INTO TIPI_PONUDBE VALUES (5,'Motel');

INSERT INTO TIP_PONUDNIKA VALUES (1,'Posameznik');
INSERT INTO TIP_PONUDNIKA VALUES (2,'Podjetje');
INSERT INTO TIP_PONUDNIKA VALUES (3,'Podjetje');
INSERT INTO TIP_PONUDNIKA VALUES (4,'Posameznik');
INSERT INTO TIP_PONUDNIKA VALUES (5,'Podjetje');

INSERT INTO PONUDNIKI VALUES (1,'Joze s.p.',1);
INSERT INTO PONUDNIKI VALUES (2,'Parketi d.o.o.',2);
INSERT INTO PONUDNIKI VALUES (3,'Tilen d.o.o.',3);
INSERT INTO PONUDNIKI VALUES (4,'Novak s.p.',4);
INSERT INTO PONUDNIKI VALUES (5,'Kovac d.o.o.',5);
INSERT INTO PONUDNIKI VALUES (6,'Streha d.o.o.',3);
INSERT INTO PONUDNIKI VALUES (7,'Matevz s.p.',4);
INSERT INTO PONUDNIKI VALUES (8,'Elektrarna s.p.',1);
INSERT INTO PONUDNIKI VALUES (9,'Karel d.o.o.',5);
INSERT INTO PONUDNIKI VALUES (10,'Gozd d.o.o.',2);

INSERT INTO PONUDBA_PRENOCISCE VALUES (1,'Sonce','4',1,1,1);
INSERT INTO PONUDBA_PRENOCISCE VALUES (2,'Uran','3',2,7,2);
INSERT INTO PONUDBA_PRENOCISCE VALUES (3,'Titan','3',3,3,4);
INSERT INTO PONUDBA_PRENOCISCE VALUES (4,'Rogaska','4',4,7,4);
INSERT INTO PONUDBA_PRENOCISCE VALUES (5,'Grah','4',5,7,5);
INSERT INTO PONUDBA_PRENOCISCE VALUES (6,'Aleksander','5',4,6,6);
INSERT INTO PONUDBA_PRENOCISCE VALUES (7,'Celjan','3',3,7,7);
INSERT INTO PONUDBA_PRENOCISCE VALUES (8,'Titanik','3',1,8,8);
INSERT INTO PONUDBA_PRENOCISCE VALUES (9,'Ptic','5',2,7,9);
INSERT INTO PONUDBA_PRENOCISCE VALUES (10,'Nebo','4',5,10,10);

INSERT INTO SOBE VALUES (1,true,1,1);
INSERT INTO SOBE VALUES (2,false,2,7);
INSERT INTO SOBE VALUES (3,true,3,3);
INSERT INTO SOBE VALUES (4,true,4,4);
INSERT INTO SOBE VALUES (5,true,5,2);
INSERT INTO SOBE VALUES (6,true,6,7);
INSERT INTO SOBE VALUES (7,true,7,7);
INSERT INTO SOBE VALUES (8,true,8,8);
INSERT INTO SOBE VALUES (9,true,9,7);
INSERT INTO SOBE VALUES (10,true,10,10);

INSERT INTO GOSTI_PRENOCISCE VALUES (1,'2017-06-25',1,1,1);
INSERT INTO GOSTI_PRENOCISCE VALUES (2,'2017-10-12',2,2,2);
INSERT INTO GOSTI_PRENOCISCE VALUES (3,'2016-12-16',3,3,3);
INSERT INTO GOSTI_PRENOCISCE VALUES (4,'2017-02-24',4,4,4);
INSERT INTO GOSTI_PRENOCISCE VALUES (5,'2016-11-05',5,5,5);
INSERT INTO GOSTI_PRENOCISCE VALUES (6,'2017-01-25',6,6,6);
INSERT INTO GOSTI_PRENOCISCE VALUES (7,'2016-09-09',7,7,7);
INSERT INTO GOSTI_PRENOCISCE VALUES (8,'2016-07-13',8,8,8);
INSERT INTO GOSTI_PRENOCISCE VALUES (9,'2017-04-11',9,9,9);
INSERT INTO GOSTI_PRENOCISCE VALUES (10,'2016-07-08',10,10,10);

INSERT INTO POPUSTI VALUES (1,85,'2017-01-25','2017-02-08',1);
INSERT INTO POPUSTI VALUES (2,20,'2016-03-03','2016-03-25',2);
INSERT INTO POPUSTI VALUES (3,15,'2016-07-22','2016-07-25',3);
INSERT INTO POPUSTI VALUES (4,2,'2017-09-21','2017-10-21',4);
INSERT INTO POPUSTI VALUES (5,12,'2017-04-15','2017-04-20',5);
INSERT INTO POPUSTI VALUES (6,50,'2016-11-04','2016-11-08',6);
INSERT INTO POPUSTI VALUES (7,40,'2017-06-12','2017-06-20',7);
INSERT INTO POPUSTI VALUES (8,30,'2016-12-16','2016-01-01',8);
INSERT INTO POPUSTI VALUES (9,10,'2017-05-29','2017-06-11',9);
INSERT INTO POPUSTI VALUES (10,5,'2017-09-08','2017-09-17',10);

INSERT INTO RACUN VALUES (1,'2017-12-31 12:22:59',500,1);
INSERT INTO RACUN VALUES (2,'2017-12-31 11:43:15',1250,2);
INSERT INTO RACUN VALUES (3,'2017-12-31 05:17:23',330,3);
INSERT INTO RACUN VALUES (4,'2016-12-31 19:13:53',2670,4);
INSERT INTO RACUN VALUES (5,'2017-12-31 09:45:15',5400,5);
INSERT INTO RACUN VALUES (6,'2017-12-31 22:55:43',760,6);
INSERT INTO RACUN VALUES (7,'2017-12-31 17:44:33',1050,7);
INSERT INTO RACUN VALUES (8,'2016-12-31 10:54:31',670,8);
INSERT INTO RACUN VALUES (9,'2017-12-31 23:17:12',1200,9);
INSERT INTO RACUN VALUES (10,'2016-12-31 14:15:10',400,10);

INSERT INTO STORITEV VALUES (1,'Jahanje',1);
INSERT INTO STORITEV VALUES (2,'Savna',2);
INSERT INTO STORITEV VALUES (3,'Bazen',3);
INSERT INTO STORITEV VALUES (4,'Sprehajalne poti',4);
INSERT INTO STORITEV VALUES (5,'Masaza',5);
INSERT INTO STORITEV VALUES (6,'Jahanje',6);
INSERT INTO STORITEV VALUES (7,'Savna',7);
INSERT INTO STORITEV VALUES (8,'Bazen',8);
INSERT INTO STORITEV VALUES (9,'Sprehajalne poti',9);
INSERT INTO STORITEV VALUES (10,'Masaza',10);

INSERT INTO TIPI_SOB VALUES (1,'Enoposteljna');
INSERT INTO TIPI_SOB VALUES (2,'Dvoposteljna');
INSERT INTO TIPI_SOB VALUES (3,'Dvoposteljna');
INSERT INTO TIPI_SOB VALUES (4,'Enoposteljna');
INSERT INTO TIPI_SOB VALUES (5,'Troposteljna');

INSERT INTO POSTAVKA VALUES (1,250,9,1,1,1);
INSERT INTO POSTAVKA VALUES (2,300,22,2,2,2);
INSERT INTO POSTAVKA VALUES (3,150,22,3,3,4);
INSERT INTO POSTAVKA VALUES (4,100,22,4,4,4);
INSERT INTO POSTAVKA VALUES (5,450,9,5,5,5);
INSERT INTO POSTAVKA VALUES (6,1520,22,6,6,4);
INSERT INTO POSTAVKA VALUES (7,670,9,7,7,1);
INSERT INTO POSTAVKA VALUES (8,1334,22,8,8,4);
INSERT INTO POSTAVKA VALUES (9,125,22,9,9,3);
INSERT INTO POSTAVKA VALUES (10,450,9,10,10,5);

INSERT INTO CENIK VALUES (1,500,'2017-09-14','2017-09-20',1,1);
INSERT INTO CENIK VALUES (2,1205,'2016-04-08','2017-04-12',2,2);
INSERT INTO CENIK VALUES (3,2500,'2017-03-16','2017-03-17',3,3);
INSERT INTO CENIK VALUES (4,1450,'2016-11-02','2017-11-17',4,4);
INSERT INTO CENIK VALUES (5,650,'2017-12-13','2017-12-14',5,5);
INSERT INTO CENIK VALUES (6,800,'2017-01-14','2017-01-22',4,6);
INSERT INTO CENIK VALUES (7,430,'2017-02-08','2017-02-11',1,7);
INSERT INTO CENIK VALUES (8,1600,'2016-05-22','2017-05-27',3,8);
INSERT INTO CENIK VALUES (9,312,'2016-06-18','2017-06-20',2,9);
INSERT INTO CENIK VALUES (10,642,'2017-08-28','2017-09-05',5,10);

SET SQL_SAFE_UPDATES = 0;

-- 1. Koliko dvoposteljnih sob imamo "na zalogi"?
SELECT COUNT(*)
FROM sobe INNER JOIN tipi_sob
ON tipi_sob.ID = sobe.TIPI_SOB_ID
WHERE naziv="Dvoposteljna"
AND zasedenost = false;

-- 2. Kateri posameznik oddaja največ sob?
SELECT * FROM (
SELECT COUNT(*) st_sob, p.ID PK_ponudniki, p.naziv naziv_ponudnika, pp.naziv naziv_prenocisca, s.ID PK_sobe
FROM tip_ponudnika tp, ponudniki p, ponudba_prenocisce pp, sobe s
WHERE tp.ID = p.TIP_PONUDNIKA_ID
AND p.ID = pp.PONUDNIKI_ID
AND pp.ID = s.PONUDBA_PRENOCISCE_ID
AND tp.naziv ="posameznik"
GROUP BY naziv_ponudnika) najvec_sob
WHERE st_sob = 6;
-- WHERE stevilo_placil = (SELECT MAX(stevilo_placil) FROM najveckrat_placana);

-- 3. Katera prenočišča imajo savno?
SELECT ponudba_prenocisce.naziv
FROM ponudba_prenocisce INNER JOIN storitev
ON storitev.ID = ponudba_prenocisce.ID
WHERE storitev.naziv ="Savna";

-- 4. Katero storitev je plačalo največ gostov?
SELECT najveckrat_placana.naziv FROM (
SELECT storitev.ID, storitev.naziv, COUNT(postavka.STORITEV_ID) stevilo_placil
FROM storitev INNER JOIN postavka
ON postavka.STORITEV_ID = storitev.ID
GROUP BY storitev.naziv) najveckrat_placana
WHERE stevilo_placil = 4;
-- WHERE stevilo_placil = (SELECT MAX(stevilo_placil) FROM najveckrat_placana);

-- 5. Spremeni naziv ponudbe 'jahanje' v 'turistično jahanje na prostem'.
SELECT * FROM storitev;
UPDATE storitev
SET naziv ="Turisticno jahanje na prostem"
WHERE naziv ="Jahanje";

-- 6. Izbriši ponudbo 'savna'.
DELETE FROM storitev
WHERE naziv="Savna";

SET SQL_SAFE_UPDATES = 1;
show tables;