SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

CREATE DATABASE IF NOT EXISTS `konferencije2023` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
USE `konferencije2023`;

DROP TABLE IF EXISTS `hale`;
CREATE TABLE IF NOT EXISTS `hale` (
  `naziv` varchar(45) COLLATE utf8_bin NOT NULL,
  `kapacitet` int(11) NOT NULL,
  PRIMARY KEY (`naziv`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `hale` (`naziv`, `kapacitet`) VALUES
('Hala 1', 100),
('Hala 2', 250),
('Hala 3', 150),
('Hala 4', 1000),
('Hala 5', 600);

DROP TABLE IF EXISTS `korisnici`;
CREATE TABLE IF NOT EXISTS `korisnici` (
  `ime` varchar(45) COLLATE utf8_bin NOT NULL,
  `prezime` varchar(45) COLLATE utf8_bin NOT NULL,
  `kor_ime` varchar(45) COLLATE utf8_bin NOT NULL,
  `lozinka` varchar(45) COLLATE utf8_bin NOT NULL,
  `mejl` varchar(45) COLLATE utf8_bin NOT NULL,
  `tip` varchar(45) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`kor_ime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `korisnici` (`ime`, `prezime`, `kor_ime`, `lozinka`, `mejl`, `tip`) VALUES
('Jova', 'Jovic', 'jova', 'jova123', 'organizator@gmail.com', 'organizator'),
('Mika', 'Mikic', 'mika', 'mika123', 'mika@gmail.com', 'klijent'),
('Petar', 'Petrovic', 'pera', 'pera123', 'pera@gmail.com', 'klijent'),
('Zika', 'Zikic', 'zika', 'zika123', 'zika@gmail.com', 'klijent');

DROP TABLE IF EXISTS `zahtevi`;
CREATE TABLE IF NOT EXISTS `zahtevi` (
  `idZ` int(11) NOT NULL AUTO_INCREMENT,
  `naziv` varchar(45) COLLATE utf8_bin NOT NULL,
  `datum_od` date NOT NULL,
  `datum_do` date NOT NULL,
  `broj_ljudi` int(11) DEFAULT NULL,
  `hala` varchar(45) COLLATE utf8_bin NOT NULL,
  `status` varchar(45) COLLATE utf8_bin NOT NULL,
  `klijent` varchar(45) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`idZ`),
  KEY `hala` (`hala`),
  KEY `klijent` (`klijent`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `zahtevi` (`idZ`, `naziv`, `datum_od`, `datum_do`, `broj_ljudi`, `hala`, `status`, `klijent`) VALUES
(1, 'Buducnost IT sektora', '2023-07-10', '2023-07-12', NULL, 'Hala 1', 'U obradi', 'pera'),
(2, 'JS radna okruzenja', '2023-07-13', '2023-07-15', 130, 'Hala 3', 'Prihvaceno', 'pera'),
(3, 'Scrum metodologija', '2023-07-23', '2023-07-29', 200, 'Hala 2', 'U obradi', 'mika'),
(4, 'Umetnost danasnjice', '2023-07-25', '2023-07-29', 170, 'Hala 2', 'U obradi', 'zika');


ALTER TABLE `zahtevi`
  ADD CONSTRAINT `hala` FOREIGN KEY (`hala`) REFERENCES `hale` (`naziv`),
  ADD CONSTRAINT `klijent` FOREIGN KEY (`klijent`) REFERENCES `korisnici` (`kor_ime`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;