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

DROP TABLE IF EXISTS `aktivnosti`;
CREATE TABLE IF NOT EXISTS `aktivnosti` (
  `idA` int NOT NULL AUTO_INCREMENT,
  `naziv` varchar(45) COLLATE utf16_bin NOT NULL,
  `datum` date NOT NULL,
  `potrebno` int NOT NULL,
  `prijavljeno` int NOT NULL,
  PRIMARY KEY (`idA`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf16 COLLATE=utf16_bin;

INSERT INTO `aktivnosti` (`idA`, `naziv`, `datum`, `potrebno`, `prijavljeno`) VALUES
(1, 'Ciscenje snega', '2024-11-23', 20, 19),
(2, 'Ciscenje Zvezdarske sume', '2024-11-23', 5, 5),
(3, 'Pomaganje u Svratistu', '2024-11-24', 15, 10),
(4, 'Ciscenje snega', '2024-11-30', 20, 10),
(5, 'Ciscenje parka Tasmajdan', '2024-11-23', 20, 5),
(6, 'Cista Sava', '2024-11-07', 20, 15);

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
('aca', 'aca123', 'Aca', 'Aca', 'organizator@gmail.com', 'organizator'),
('ana', 'ana123', 'Ana', 'Anic', 'ana@gmail.com', 'volonter'),
('jova', 'jova123', 'Jovan', 'Jovanovic', 'jova@gmail.com', 'volonter'),
('mare', 'mare123', 'Marko', 'Markovic', 'mare@gmail.com', 'volonter');

DROP TABLE IF EXISTS `prijave`;
CREATE TABLE IF NOT EXISTS `prijave` (
  `idP` int NOT NULL AUTO_INCREMENT,
  `volonter` varchar(45) COLLATE utf16_bin NOT NULL,
  `aktivnost` int NOT NULL,
  `status` varchar(45) COLLATE utf16_bin NOT NULL,
  PRIMARY KEY (`idP`),
  KEY `volonter` (`volonter`),
  KEY `aktivnost` (`aktivnost`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf16 COLLATE=utf16_bin;

INSERT INTO `prijave` (`idP`, `volonter`, `aktivnost`, `status`) VALUES
(1, 'ana', 1, 'odobren'),
(2, 'ana', 5, 'neodobren'),
(3, 'jova', 2, 'odobren'),
(4, 'mare', 1, 'neodobren');


ALTER TABLE `prijave`
  ADD CONSTRAINT `aktivnost` FOREIGN KEY (`aktivnost`) REFERENCES `aktivnosti` (`idA`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `volonter` FOREIGN KEY (`volonter`) REFERENCES `korisnici` (`kor_ime`) 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 */;