DROP DATABASE IF EXISTS `restoran2026`;
CREATE DATABASE IF NOT EXISTS `restoran2026` DEFAULT CHARACTER SET utf16 COLLATE utf16_bin;
USE `restoran2026`;


DROP TABLE IF EXISTS `korisnici`;
CREATE TABLE IF NOT EXISTS `korisnici` (
  `kor_ime` varchar(45) COLLATE utf16_bin NOT NULL,
  `lozinka` varchar(45) COLLATE utf16_bin NOT NULL,
  `ime` varchar(45) COLLATE utf16_bin NOT NULL,
  `prezime` varchar(45) COLLATE utf16_bin NOT NULL,
  `mejl` varchar(45) COLLATE utf16_bin NOT NULL,
  `tip` varchar(45) COLLATE utf16_bin NOT NULL,
  PRIMARY KEY (`kor_ime`)
);

INSERT INTO `korisnici` (`kor_ime`, `lozinka`, `ime`, `prezime`, `mejl`, `tip`) VALUES
('ana', 'ana123', 'Ana', 'Anicic', 'ana@gmail.com', 'posetilac'),
('marko', 'marko123', 'Marko', 'Markovic', 'marko@gmail.com', 'radnik'),
('pera', 'pera123', 'Petar', 'Petrovic', 'pera@gmail.com', 'posetilac');



DROP TABLE IF EXISTS `restorani`;
CREATE TABLE IF NOT EXISTS `restorani` (
  `ime` varchar(45) COLLATE utf16_bin NOT NULL,
  `lokacija` varchar(45) COLLATE utf16_bin NOT NULL,
  `slobodnih_mesta` int NOT NULL,
  PRIMARY KEY (`ime`)
);

INSERT INTO `restorani` (`ime`, `lokacija`, `slobodnih_mesta`) VALUES
('Velika smokva', 'Molerova 33', 13),
('Mali Dunav', 'Obalska 7', 27),
('Orahov hlad', 'Bulevar 122', 0);



DROP TABLE IF EXISTS `rezervacije`;
CREATE TABLE IF NOT EXISTS `rezervacije` (
  `idR` int NOT NULL AUTO_INCREMENT,
  `korisnik` varchar(45) COLLATE utf16_bin NOT NULL,
  `restoran` varchar(45) COLLATE utf16_bin NOT NULL,
  `broj_mesta` int NOT NULL,
  `status` varchar(45) COLLATE utf16_bin NOT NULL,
  PRIMARY KEY (`idR`),
  KEY `korisnik` (`korisnik`),
  KEY `restoran` (`restoran`)
);

INSERT INTO `rezervacije` (`korisnik`, `restoran`, `broj_mesta`, `status`) VALUES
('pera', 'Orahov hlad', 30, 'prihvacena'),
('ana', 'Orahov hlad', 10, 'odbijena'),
('pera', 'Mali Dunav', 5, 'na cekanju'),
('ana', 'Velika smokva', 7, 'zavrsena'),
('ana', 'Mali Dunav', 8, 'na cekanju'),
('ana', 'Mali Dunav', 20, 'na cekanju'),
('ana', 'Orahov hlad', 10, 'na cekanju');


ALTER TABLE `rezervacije`
  ADD CONSTRAINT `rezervacije_ibfk_1` FOREIGN KEY (`korisnik`) REFERENCES `korisnici` (`kor_ime`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `rezervacije_ibfk_2` FOREIGN KEY (`restoran`) REFERENCES `restorani` (`ime`) ON DELETE RESTRICT ON UPDATE RESTRICT;
COMMIT;