SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
SET @@global.time_zone='+00:00';
SET @@session.time_zone='+00:00';

/*!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 */;

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

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

INSERT INTO `korisnici` (`korisnicko_ime`, `lozinka`, `ime`, `prezime`, `mejl`, `tip`) VALUES
('ana', 'ana111', 'Ana', 'Anic', 'ana@gmail.com', 1),
('jova', 'jova111', 'Jovan', 'Jovanovic', 'jova@gmail.com', 1),
('mika', 'mika111', 'Mika', 'Mikic', 'mika@gmail.com', 0),
('pera', 'pera111', 'Petar', 'Petrovic', 'pera@gmail.com', 0),
('zika', 'zika111', 'Zika', 'Zikic', 'zika@gmail.com', 0);

DROP TABLE IF EXISTS `prijave`;
CREATE TABLE IF NOT EXISTS `prijave` (
  `idP` int(11) NOT NULL AUTO_INCREMENT,
  `radionica` int(11) NOT NULL,
  `ucesnik` varchar(45) COLLATE utf8_bin NOT NULL,
  `posluzenje` int(11) NOT NULL,
  `napomena` varchar(200) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`idP`),
  KEY `radionica` (`radionica`),
  KEY `ucesnik` (`ucesnik`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `prijave` (`idP`, `radionica`, `ucesnik`, `posluzenje`, `napomena`) VALUES
(1, 1, 'mika', 1, ''),
(2, 4, 'mika', 0, ''),
(3, 4, 'pera', 1, 'Da li moze meze da bude posno?');

DROP TABLE IF EXISTS `radionice`;
CREATE TABLE IF NOT EXISTS `radionice` (
  `idR` int(11) NOT NULL AUTO_INCREMENT,
  `naziv` varchar(45) COLLATE utf8_bin NOT NULL,
  `datum` date NOT NULL,
  `mesto` varchar(45) COLLATE utf8_bin NOT NULL,
  `tehnika` varchar(45) COLLATE utf8_bin NOT NULL,
  `slobodna_mesta` int(11) NOT NULL,
  PRIMARY KEY (`idR`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `radionice` (`idR`, `naziv`, `datum`, `mesto`, `tehnika`, `slobodna_mesta`) VALUES
(1, 'Fridica', '2023-04-28', 'Novi Sad', 'akril', 15),
(2, 'Novogodisnja vrata', '2023-04-18', 'Beograd', 'ulje na platnu', 1),
(3, 'Moneovi lokvanji', '2023-05-21', 'Sremski Karlovci', 'akril', 0),
(4, 'Izgubljeni u Japanu', '2023-04-25', 'Novi Sad', 'ulje na platnu', 1);


ALTER TABLE `prijave`
  ADD CONSTRAINT `radionica` FOREIGN KEY (`radionica`) REFERENCES `radionice` (`idR`),
  ADD CONSTRAINT `ucesnik` FOREIGN KEY (`ucesnik`) REFERENCES `korisnici` (`korisnicko_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 */;