SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
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 `forum2026` DEFAULT CHARACTER SET utf16 COLLATE utf16_bin;
USE `forum2026`;

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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_bin;

INSERT INTO `korisnici` (`kor_ime`, `lozinka`, `ime`, `prezime`, `mejl`, `tip`) VALUES
('ana', 'ana123', 'Ana', 'Aničić', 'ana@mejl.com', 'administrator'),
('jova', 'jova123', 'Jova', 'Jović', 'jova@mejl.com', 'komentator'),
('maja', 'maja123', 'Maja', 'Majić', 'maja@mejl.com', 'komentator'),
('pera', 'pera123', 'Pera', 'Perić', 'pera@mejl.com', 'komentator');

DROP TABLE IF EXISTS `teme`;
CREATE TABLE IF NOT EXISTS `teme` (
  `idT` int NOT NULL AUTO_INCREMENT,
  `naslov` varchar(45) COLLATE utf16_bin NOT NULL,
  `opis` varchar(500) COLLATE utf16_bin NOT NULL,
  `broj_komentara` int NOT NULL,
  PRIMARY KEY (`idT`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf16 COLLATE=utf16_bin;


INSERT INTO `teme` (`idT`, `naslov`, `opis`, `broj_komentara`) VALUES
(1, 'Testiranje aplikacije', 'Svrha ove teme je testiranje aplikacije. Ostavite komentar kako sistem funkcioniše.', 3),
(2, 'Ideje', 'Ostavite ideju šta nedostaje ovoj aplikaciji.', 2);

DROP TABLE IF EXISTS `komentari`;
CREATE TABLE IF NOT EXISTS `komentari` (
  `idK` int NOT NULL AUTO_INCREMENT,
  `korisnik` varchar(45) COLLATE utf16_bin NOT NULL,
  `tekst` varchar(45) COLLATE utf16_bin NOT NULL,
  `datum` datetime NOT NULL,
  `ocena` int NOT NULL,
  `tema` int NOT NULL,
  PRIMARY KEY (`idK`),
  KEY `korisnik` (`korisnik`),
  KEY `tema` (`tema`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_bin;

INSERT INTO `komentari` (`idK`, `korisnik`, `tekst`, `datum`, `ocena`, `tema`) VALUES
(1, 'pera', 'Prvi komentar', '2026-02-23 10:30:45', 0, 1),
(2, 'maja', 'Dodajte izmene komentara', '2026-02-23 10:35:17', 1, 2),
(3, 'maja', 'Sve deluje u redu', '2026-02-23 11:38:52', 3, 1),
(4, 'jova', 'Ništa ne radi', '2026-02-23 10:42:28', -7, 1),
(5, 'pera', 'Nedostaje kreiranje tema', '2026-02-23 12:06:45', 0, 2);

ALTER TABLE `komentari`
  ADD CONSTRAINT `komentari_ibfk_1` FOREIGN KEY (`korisnik`) REFERENCES `korisnici` (`kor_ime`) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE `komentari`
  ADD CONSTRAINT `komentari_ibfk_2` FOREIGN KEY (`tema`) REFERENCES `teme` (`idT`) ON DELETE RESTRICT ON UPDATE RESTRICT;
COMMIT;

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