-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema student2020
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema student2020
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `student2020` DEFAULT CHARACTER SET utf8mb4 ;
USE `student2020` ;

-- -----------------------------------------------------
-- Table `student2020`.`korisnici`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `student2020`.`korisnici` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `ime` VARCHAR(45) NOT NULL DEFAULT '',
  `prezime` VARCHAR(45) NOT NULL DEFAULT '',
  `iznos` DOUBLE(10,2) NULL DEFAULT NULL,
  `email` VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8
COMMENT = 'Test tabela za ugradjenu proceduru';


-- -----------------------------------------------------
-- Table `student2020`.`transakcije`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `student2020`.`transakcije` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ovome_oduzmi` INT(11) NULL DEFAULT NULL,
  `ovome_dodaj` INT(11) NULL DEFAULT NULL,
  `iznos` DOUBLE(10,2) NULL DEFAULT NULL,
  `opis_transakcije` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `FK_transakcije_oduzmi` (`ovome_oduzmi` ASC),
  INDEX `FK_transakcije_dodaj` (`ovome_dodaj` ASC),
  CONSTRAINT `FK_transakcije_dodaj`
    FOREIGN KEY (`ovome_dodaj`)
    REFERENCES `student2020`.`korisnici` (`id`)
    ON DELETE SET NULL,
  CONSTRAINT `FK_transakcije_oduzmi`
    FOREIGN KEY (`ovome_oduzmi`)
    REFERENCES `student2020`.`korisnici` (`id`)
    ON DELETE SET NULL)
ENGINE = InnoDB
AUTO_INCREMENT = 22
DEFAULT CHARACTER SET = utf8
COMMENT = 'Lookup tabela sa transakcijama';

USE `student2020` ;

-- -----------------------------------------------------
-- procedure obavi_transakciju
-- -----------------------------------------------------

DELIMITER $$
USE `student2020`$$
CREATE DEFINER=`admin`@`%` PROCEDURE `obavi_transakciju`(
         IN p_oduzmi_id INT,
         IN p_dodaj_id INT,
         IN p_iznos DOUBLE,
         IN p_opis VARCHAR(255),
         OUT p_poruka VARCHAR(255))
BEGIN
  DECLARE v_trenutno_stanje DOUBLE(10, 2) DEFAULT 0;

  BEGIN
	DECLARE exit HANDLER FOR NOT FOUND
	    SET p_poruka = 'Korisnik nije pronadjen!';
	 
	 DECLARE exit HANDLER FOR SQLEXCEPTION
	        BEGIN
          	ROLLBACK;	
          	SET p_poruka = 'Greska';
        	       END;
  -- oznaci pocetak transakcije
	  START TRANSACTION;
  -- proveri da li korisnik, kom se skida sa racuna, postoji i da li
  -- ima dovoljno novca da mu se zadani iznos moze skinuti:
    	  SELECT iznos
	    INTO v_trenutno_stanje
	    FROM korisnici
	   WHERE id = p_oduzmi_id;
-- ako ima, onda obavi transakciju:
IF v_trenutno_stanje > p_iznos THEN
	     UPDATE korisnici
	     SET iznos = iznos - p_iznos
	      WHERE id = p_oduzmi_id;

	     UPDATE korisnici
	      SET iznos = iznos + p_iznos
	      WHERE id = p_dodaj_id;

-- sacuvaj informacije o obavljenoj transakciji:
	INSERT INTO transakcije (id, ovome_oduzmi, ovome_dodaj, iznos, 		    opis_transakcije)
	VALUES (NULL, p_oduzmi_id, p_dodaj_id, p_iznos, p_opis);
      ELSE
         SET p_poruka = 'Korisnik nema dovoljno novca';
      END IF;
  END;

  -- potvrdi unos i oznaci kraj transakcije
  -- ili ponisti unos
  IF p_poruka != '' THEN
     ROLLBACK;
  ELSE
     COMMIT;
     SET p_poruka = 'SUCCESS! Transakcija je uspesno obavljena';
  END IF;

  SELECT p_poruka;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure pop_tabele
-- -----------------------------------------------------

DELIMITER $$
USE `student2020`$$
CREATE DEFINER=`admin`@`%` PROCEDURE `pop_tabele`(IN p_broj_korisnika INT, 
OUT p_poruka VARCHAR(255))
    COMMENT 'Ova procedura sluzi za popunjavanje tabele ''korisnici''  '
BEGIN
  -- deklaracija potrebnih varijabli
  DECLARE v_counter INT DEFAULT 0;
START TRANSACTION;

  -- petlja za unos podataka
  WHILE v_counter < p_broj_korisnika  DO

   -- povecaj brojac za 1
   SET v_counter = v_counter + 1;

   INSERT INTO korisnici (id, ime, prezime, iznos, email)
   VALUES ( NULL,
            CONCAT('ime_', v_counter),
            CONCAT('prezime_', v_counter),
            RAND() * (10/RAND()) * 1000,
            CONCAT('email', MOD(v_counter, 5), '@domena', MOD(v_counter, 7), '.com')
          );
  END WHILE;
    
   -- potvrdimo unos i oznacimo kao kraj transakcije
  COMMIT;


  -- sastavimo OUT poruku
  SET p_poruka = CONCAT('Broj unesenih korisnika: ', v_counter);

  -- prikazimo sadrzaj poruke:
  SELECT p_poruka;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure pop_transakcije
-- -----------------------------------------------------

DELIMITER $$
USE `student2020`$$
CREATE DEFINER=`admin`@`%` PROCEDURE `pop_transakcije`(OUT p_poruka VARCHAR(255))
    MODIFIES SQL DATA
    COMMENT 'Ova procedura sluzi za popunjavanje tabela ''transakcije'' '
BEGIN
  -- deklaracija potrebnih varijabli
  DECLARE v_broj_transakcija INT DEFAULT 0;
  DECLARE v_oduzmi_id INT;
  DECLARE v_dodaj_id INT;
  DECLARE v_iznos DOUBLE(10,2);
  DECLARE nema_vise_redova BOOLEAN DEFAULT false;
 -- deklaracija cursor-a
  DECLARE cur_korisnici CURSOR FOR
      SELECT k1.id AS oduzmi, k2.id AS dodaj
        FROM korisnici k1, korisnici k2
       WHERE k1.id <> k2.id;

  DECLARE continue HANDLER FOR SQLSTATE '02000' SET nema_vise_redova = true;
  DECLARE exit HANDLER FOR SQLEXCEPTION
    BEGIN
      ROLLBACK;
      SET p_poruka = 'Unos prekinut zbog SQLExceptiona';
    END;
  -- oznacimo pocetak transakcije
  START TRANSACTION;
 -- otvori cursor sa korisnicima
  OPEN cur_korisnici;
  -- otvori petlju sa korisnicima
  petlja_sa_korisnicima: LOOP
     -- pokupi vrednosti iz cursora
     FETCH cur_korisnici INTO v_oduzmi_id, v_dodaj_id;
     -- ako cursor ne sadrzi vise redova, zavrsi sa petljom
     IF nema_vise_redova THEN
        LEAVE petlja_sa_korisnicima;
     END IF;
     -- povecaj brojac transakcija za 1
     SET v_broj_transakcija = v_broj_transakcija + 1;
     SET v_iznos = RAND() * (MOD(v_broj_transakcija, 33) + 10);

     -- necemo vrsiti proveru koliko je trenutni korisnikov iznos,
     -- nego cemo samo uneti podatke radi demonstracije funkcionisanja:
      UPDATE korisnici
         SET iznos = iznos - v_iznos
       WHERE id = v_oduzmi_id;
      UPDATE korisnici
         SET iznos = iznos + v_iznos
       WHERE id = v_dodaj_id;

      -- unesi transakciju u tabelu
      INSERT INTO transakcije(id, ovome_oduzmi, ovome_dodaj, iznos, opis_transakcije)
      VALUES (NULL, v_oduzmi_id, v_dodaj_id, v_iznos, 
      CONCAT('Prebaci ', v_iznos, 'EUR- > sa racuna korisnika ', v_oduzmi_id, '  na racun korisnika ',v_dodaj_id));

  -- zatvori petlju sa korisnicima
  END LOOP petlja_sa_korisnicima;

  CLOSE cur_korisnici;

  -- potvrdimo unos i oznacimo kao kraj transakcije
  COMMIT;
  -- sastavimo OUT poruku
  SET p_poruka = CONCAT( 'Broj obavljenih transakcija: ', v_broj_transakcija);
  -- prikazimo sadrzaj poruke:
  SELECT p_poruka;
END$$

DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;