SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET 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 */;

CREATE DATABASE IF NOT EXISTS `festivali` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `festivali`;

DROP TABLE IF EXISTS `festival`;
CREATE TABLE IF NOT EXISTS `festival` (
  `idFes` bigint(20) NOT NULL,
  `naziv` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `datumOd` date NOT NULL,
  `datumDo` date NOT NULL,
  `mesto` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `detalji` text COLLATE utf8_unicode_ci NOT NULL,
  `kapacitetPoDanu` int(11) NOT NULL,
  `maxKarataPoKorisniku` int(11) NOT NULL,
  `cenaPaket` int(11) NOT NULL,
  `cenaDan` int(11) NOT NULL,
  `pregledi` bigint(20) NOT NULL DEFAULT '0',
  `facebook` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `twitter` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `instagram` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `youtube` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `otkazano` tinyint(1) NOT NULL DEFAULT '0',
  `brojKupljenih` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `galerija`;
CREATE TABLE IF NOT EXISTS `galerija` (
  `idGal` bigint(20) NOT NULL,
  `idFes` bigint(20) NOT NULL,
  `putanja` varchar(511) COLLATE utf8_unicode_ci NOT NULL,
  `tip` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `korisnik`;
CREATE TABLE IF NOT EXISTS `korisnik` (
  `idKor` bigint(20) NOT NULL,
  `ime` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `prezime` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `username` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `telefon` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `admin` tinyint(1) NOT NULL DEFAULT '0',
  `odobren` tinyint(1) NOT NULL DEFAULT '0',
  `poslednjaPrijava` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `brojUpozorenja` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `korisnik` (`idKor`, `ime`, `prezime`, `username`, `password`, `telefon`, `email`, `admin`, `odobren`, `poslednjaPrijava`, `brojUpozorenja`) VALUES
(1, 'Ana', 'Pesko', 'peska', 'Sifra123', '0601234567', 'ana.pesko@yahoo.com', 1, 1, '2017-02-13 16:17:37', 0),
(2, 'Petar', 'Djukic', 'petar', 'Sifra123', '302390293', 'petar@petar.com', 0, 0, '2017-02-13 19:01:02', 0),
(3, 'Branislava', 'Ivkovic', 'brana', 'Sifra123', '089298129', 'brana@yahoo.com', 0, 0, '2017-02-13 19:05:36', 0),
(4, 'Drazen', 'Draskovic', 'drasko', 'Sifra123', '4983204', 'drazen@etf.rs', 0, 0, '2017-02-13 19:08:55', 0),
(5, 'Nikola', 'Djukic', 'nikola', 'Sifra123', '9302', 'nikola@guru.com', 0, 0, '2017-02-13 19:13:37', 0),
(6, 'Sanja', 'Delcev', 'sanja', 'Sifra123', '3483', 'sanjad@etf.rs', 0, 0, '2017-02-13 19:18:50', 0),
(7, 'Sanja', 'Delcev', 'sanja5', 'Sifra123', '392083', 'sanjad@etf.rs', 0, 0, '2017-02-13 19:36:26', 0),
(8, 'Levi', 'Account', 'potpuno', 'Sifra123', '493284091', 'levi@account.potpuno', 0, 0, '2017-02-13 19:39:11', 0),
(9, 'Korisnik', 'Korisnik', 'korisnik', 'Sifra123', '49304812', 'nebitno@gmail.com', 0, 0, '2017-02-13 19:43:14', 0);

DROP TABLE IF EXISTS `nastup`;
CREATE TABLE IF NOT EXISTS `nastup` (
  `idNas` bigint(20) NOT NULL,
  `izvodjac` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `datumOd` date NOT NULL,
  `datumDo` date NOT NULL,
  `vremeOd` time NOT NULL,
  `vremeDo` time NOT NULL,
  `idFes` bigint(20) NOT NULL,
  `otkazano` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `notifikacija`;
CREATE TABLE IF NOT EXISTS `notifikacija` (
  `idNot` bigint(20) NOT NULL,
  `idKor` bigint(20) NOT NULL,
  `tekst` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `crniSinGrehMePrati` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `ocena`;
CREATE TABLE IF NOT EXISTS `ocena` (
  `idOce` bigint(20) NOT NULL,
  `idKor` bigint(20) NOT NULL,
  `idFes` bigint(20) NOT NULL,
  `ocena` int(11) NOT NULL,
  `komentar` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `imeFestivala` varchar(45) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `ulaznica`;
CREATE TABLE IF NOT EXISTS `ulaznica` (
  `idUla` bigint(20) NOT NULL,
  `idFes` bigint(20) NOT NULL,
  `datumNastupa` date DEFAULT NULL,
  `idKor` bigint(20) NOT NULL,
  `kolicina` int(11) NOT NULL,
  `vremeKreiranja` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `prodato` tinyint(1) NOT NULL DEFAULT '0',
  `isteklo` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE `festival`
  ADD PRIMARY KEY (`idFes`);

ALTER TABLE `galerija`
  ADD PRIMARY KEY (`idGal`),
  ADD KEY `idFes` (`idFes`);

ALTER TABLE `korisnik`
  ADD PRIMARY KEY (`idKor`),
  ADD UNIQUE KEY `username` (`username`);

ALTER TABLE `nastup`
  ADD PRIMARY KEY (`idNas`),
  ADD KEY `idFes` (`idFes`);

ALTER TABLE `notifikacija`
  ADD PRIMARY KEY (`idNot`),
  ADD KEY `idKor` (`idKor`);

ALTER TABLE `ocena`
  ADD PRIMARY KEY (`idOce`),
  ADD KEY `idKor` (`idKor`),
  ADD KEY `idFes` (`idFes`);

ALTER TABLE `ulaznica`
  ADD PRIMARY KEY (`idUla`),
  ADD KEY `idFes` (`idFes`),
  ADD KEY `idKor` (`idKor`);


ALTER TABLE `festival`
  MODIFY `idFes` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `galerija`
  MODIFY `idGal` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `korisnik`
  MODIFY `idKor` bigint(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=10;
ALTER TABLE `nastup`
  MODIFY `idNas` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `notifikacija`
  MODIFY `idNot` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `ocena`
  MODIFY `idOce` bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE `ulaznica`
  MODIFY `idUla` bigint(20) NOT NULL AUTO_INCREMENT;

ALTER TABLE `galerija`
  ADD CONSTRAINT `galerija_ibfk_1` FOREIGN KEY (`idFes`) REFERENCES `festival` (`idFes`);

ALTER TABLE `nastup`
  ADD CONSTRAINT `nastup_ibfk_1` FOREIGN KEY (`idFes`) REFERENCES `festival` (`idFes`);

ALTER TABLE `notifikacija`
  ADD CONSTRAINT `notifikacija_ibfk_1` FOREIGN KEY (`idKor`) REFERENCES `korisnik` (`idKor`);

ALTER TABLE `ocena`
  ADD CONSTRAINT `ocena_ibfk_1` FOREIGN KEY (`idKor`) REFERENCES `korisnik` (`idKor`),
  ADD CONSTRAINT `ocena_ibfk_2` FOREIGN KEY (`idFes`) REFERENCES `festival` (`idFes`);

ALTER TABLE `ulaznica`
  ADD CONSTRAINT `ulaznica_ibfk_1` FOREIGN KEY (`idFes`) REFERENCES `festival` (`idFes`),
  ADD CONSTRAINT `ulaznica_ibfk_2` FOREIGN KEY (`idKor`) REFERENCES `korisnik` (`idKor`);

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