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 `igre2024` DEFAULT CHARACTER SET utf16 COLLATE utf16_bin;
USE `igre2024`;

DROP TABLE IF EXISTS `igre`;
CREATE TABLE IF NOT EXISTS `igre` (
  `naziv` varchar(45) COLLATE utf16_bin NOT NULL,
  `trenutno_pridruzeni` varchar(45) COLLATE utf16_bin NOT NULL,
  `min` int NOT NULL,
  `max` int NOT NULL,
  `status` varchar(45) COLLATE utf16_bin NOT NULL,
  PRIMARY KEY (`naziv`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_bin;

INSERT INTO `igre` (`naziv`, `trenutno_pridruzeni`, `min`, `max`, `status`) VALUES
('Arboretum', 'mika,ana', 2, 4, 'otvorena'),
('Serif od Notingema', 'mika', 2, 6, 'otvorena'),
('Uno', 'ana,maja,pera', 2, 10, 'zapoceta');

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
('admin', 'admin123', 'Admin', 'Admin', 'admin@gmail.com', 'admin'),
('ana', 'ana123', 'Ana', 'Anic', 'ana@gmail.com', 'igrac'),
('maja', 'maja123', 'Maja', 'Majic', 'maja@gmail.com', 'igrac'),
('mika', 'mika123', 'Mika', 'Mikic', 'mika@gmail.com', 'igrac'),
('pera', 'pera123', 'Petar', 'Petrovic', 'pera@gmail.com', 'igrac');

DROP TABLE IF EXISTS `poruke`;
CREATE TABLE IF NOT EXISTS `poruke` (
  `idP` int NOT NULL AUTO_INCREMENT,
  `igra` varchar(45) COLLATE utf16_bin NOT NULL,
  `igrac1` varchar(45) COLLATE utf16_bin NOT NULL,
  `igrac2` varchar(45) COLLATE utf16_bin NOT NULL,
  `tekst` varchar(45) COLLATE utf16_bin NOT NULL,
  PRIMARY KEY (`idP`),
  KEY `igra` (`igra`),
  KEY `igrac1` (`igrac1`),
  KEY `igrac2` (`igrac2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf16 COLLATE=utf16_bin;

INSERT INTO `poruke` (`idP`, `igra`, `igrac1`, `igrac2`, `tekst`) VALUES
(1, 'Uno', 'ana', 'maja', 'super sto igramo zajedno'),
(2, 'Uno', 'maja', 'ana', 'daa, bas se radujem'),
(3, 'Arboretum', 'mika', 'ana', 'zdravoo');


ALTER TABLE `poruke`
  ADD CONSTRAINT `igra` FOREIGN KEY (`igra`) REFERENCES `igre` (`naziv`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `igrac1` FOREIGN KEY (`igrac1`) REFERENCES `korisnici` (`kor_ime`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `igrac2` FOREIGN KEY (`igrac2`) REFERENCES `korisnici` (`kor_ime`) ON DELETE RESTRICT ON UPDATE RESTRICT;

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