SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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 `pijaca2023`;
CREATE DATABASE IF NOT EXISTS `pijaca2023` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
USE `pijaca2023`;

DROP TABLE IF EXISTS `detaljinarudzbine`;
CREATE TABLE IF NOT EXISTS `detaljinarudzbine` (
  `idN` int(11) NOT NULL,
  `proizvod` varchar(45) COLLATE utf8_bin NOT NULL,
  `kolicina` decimal(10,2) NOT NULL,
  PRIMARY KEY (`idN`,`proizvod`),
  KEY `proizvod` (`proizvod`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `detaljinarudzbine` (`idN`, `proizvod`, `kolicina`) VALUES
(1, 'krompir', 2),
(1, 'kupus', 22),
(2, 'jabuka', 3),
(3, 'ananas', 1),
(3, 'krompir', 3);

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', 'Nedeljkovic', 'jova', 'jova111', 'jova@gmail.com', 'kupac'),
('Maja', 'Manic', 'maja', 'maja111', 'maja@gmail.com', 'radnik'),
('Mirko', 'Mirkovic', 'mirko', 'mirko111', 'mirko@gmail.com', 'kupac');

DROP TABLE IF EXISTS `narudzbine`;
CREATE TABLE IF NOT EXISTS `narudzbine` (
  `idN` int(11) NOT NULL,
  `kupac` varchar(45) COLLATE utf8_bin NOT NULL,
  `racun` decimal(10,2) NOT NULL,
  PRIMARY KEY (`idN`),
  KEY `kupac` (`kupac`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `narudzbine` (`idN`, `kupac`, `racun`) VALUES
(1, 'mirko', 2196),
(2, 'mirko', 300),
(3, 'jova', 480);

DROP TABLE IF EXISTS `proizvodi`;
CREATE TABLE IF NOT EXISTS `proizvodi` (
  `naziv` varchar(45) COLLATE utf8_bin NOT NULL,
  `kategorija` varchar(45) COLLATE utf8_bin NOT NULL,
  `cena` int(11) NOT NULL,
  `stanje` decimal(10,2) NOT NULL,
  PRIMARY KEY (`naziv`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `proizvodi` (`naziv`, `kategorija`, `cena`, `stanje`) VALUES
('ananas', 'voce', 210, 3),
('banana', 'voce', 150, 30),
('jabuka', 'voce', 100, 12),
('krompir', 'povrce', 90, 30),
('kupus', 'povrce', 90, 5),
('paprika', 'povrce', 220, 10);


ALTER TABLE `detaljinarudzbine`
  ADD CONSTRAINT `proizvod` FOREIGN KEY (`proizvod`) REFERENCES `proizvodi` (`naziv`);

ALTER TABLE `narudzbine`
  ADD CONSTRAINT `kupac` FOREIGN KEY (`kupac`) 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 */;