CREATE DATABASE IF NOT EXISTS `banka2025` DEFAULT CHARACTER SET utf16 COLLATE utf16_bin;
USE `banka2025`;



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', 'Anicic', 'ana@gmail.com', 'bankar'),
('marko', 'marko123', 'Marko', 'Markovic', 'marko@gmail.com', 'administrator'),
('pera', 'pera123', 'Petar', 'Petrovic', 'pera@gmail.com', 'bankar');



DROP TABLE IF EXISTS `klijenti`;
CREATE TABLE IF NOT EXISTS `klijenti` (
  `broj_racuna` int NOT NULL,
  `ime_i_prezime` varchar(45) COLLATE utf16_bin NOT NULL,
  `stanje` float NOT NULL,
  PRIMARY KEY (`broj_racuna`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_bin;

INSERT INTO `klijenti` (`broj_racuna`, `ime_i_prezime`, `stanje`) VALUES
(100000000, 'Zika Zikic', 1024.5),
(100000001, 'Jova Jovic', -5600),
(100000002, 'Mika Mikic', 150.37);



DROP TABLE IF EXISTS `transakcije`;
CREATE TABLE IF NOT EXISTS `transakcije` (
  `idT` int NOT NULL AUTO_INCREMENT,
  `broj_racuna` int NOT NULL,
  `bankar` varchar(45) COLLATE utf16_bin NOT NULL,
  `iznos` float NOT NULL,
  `datum` datetime NOT NULL,
  PRIMARY KEY (`idT`),
  KEY `broj_racuna` (`broj_racuna`),
  KEY `bankar` (`bankar`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf16 COLLATE=utf16_bin;

INSERT INTO `transakcije` (`idT`, `broj_racuna`, `bankar`, `iznos`, `datum`) VALUES
(1, 100000000, 'pera', 1024.5, '2025-09-30 20:30:45'),
(2, 100000001, 'ana', -2000, '2025-09-30 20:31:12'),
(3, 100000001, 'pera', -3600, '2025-09-30 20:35:37'),
(4, 100000002, 'ana', 200, '2025-09-30 20:32:40'),
(5, 100000002, 'ana', -49.63, '2025-09-30 20:37:21');


ALTER TABLE `transakcije`
  ADD CONSTRAINT `transakcije_ibfk_1` FOREIGN KEY (`bankar`) REFERENCES `korisnici` (`kor_ime`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `transakcije_ibfk_2` FOREIGN KEY (`broj_racuna`) REFERENCES `klijenti` (`broj_racuna`) ON DELETE RESTRICT ON UPDATE RESTRICT;
COMMIT;