#
# Tranzactii
#

##Create table(s)

CREATE TABLE IF NOT EXISTS departament
(id int unique auto_increment primary key,
nume char(20),
manager_id int) ENGINE = 'innodb';

CREATE TABLE IF NOT EXISTS angajat
(id int unique auto_increment primary key,
nume char(20),
prenume char(20),
departament_id int,
manager_id int ,
salariu int,
bonus int,
angajare date,
vechime date,
INDEX (departament_id),
FOREIGN KEY (departament_id) REFERENCES departament(id),
FOREIGN KEY(manager_id) REFERENCES angajat(id)) ENGINE = 'innodb';


INSERT INTO departament (nume, manager_id) VALUES
('R&D', 1), ('QA', 2), ('IT', 3), ('Backend', 4), ('HR', 5);


INSERT INTO angajat
(nume, prenume, departament_id, manager_id, salariu, bonus,angajare)
VALUES
('Popa', 	'Ion', 	  1, NULL, 8000, 0,'2000-1-12'),
('Popescu', 	'Maria',  1, 1,    3000, 0, '2003-5-6'),
('Marinescu', 	'Vasile', 1, 1,    5000, 0, '2004-6-3'),
('Ionescu', 	'Andrei', 1, NULL, 3000, 0, '2002-1-1'),
('Vasilescu', 	'Ana', 	  2, NULL, 2000, 0, '2006-3-3'),
('Dragan', 	'Dinu',   2, 5,    2000, 0, '2004-11-12'),
('Mihailescu', 	'Adrian', 5, NULL, 2500, 0, '2006-10-12'),
('Teodorescu', 	'Matei',  3, NULL, 2000, 0, '2005-1-12'),
('Popescu', 	'Vasile', 3, 8,    3000, 0, '2005-9-9'),
('Mateescu', 	'Dumitru',3, 8,    3000, 0, '2007-2-5'),
('Calinescu', 	'Alin',   4, NULL, 3200, 0, '2005-8-2'),
('Popescu', 	'Mihaela',4, 12,   1500, 0, '2005-4-8'),
('Enachescu', 	'Ionel',  5, NULL, 4500, 0, '2001-1-12'),
('Dediu', 	'Carmen', 4, 12,   2700, 0, '2005-6-5'),
('Antonescu', 	'Paul',   2, 5,    2700, 0, '2007-3-22');



# Mariti salariul tuturor angajatilor cu o zecime din media salariului
START TRANSACTION;
SELECT @AV_SAL:= 0.1 * AVG(salariu) FROM angajat;
UPDATE angajat SET salariu = salariu  + @AV_SAL;
SELECT nume, salariu FROM angajat;
COMMIT;


# Mariti salariul tuturor angajatilor cu un procent din media salariului,
# daca aceasta e mai mica decat pragul maxim in lei

DROP PROCEDURE IF EXISTS MARESTE_SALARIU_CU_PROCENT_DIN_MEDIA_SALARIILOR;
DELIMITER //

CREATE PROCEDURE
MARESTE_SALARIU_CU_PROCENT_DIN_MEDIA_SALARIILOR(procent FLOAT, prag_maxim FLOAT)
  BEGIN
    START TRANSACTION;
    SELECT @AV_SAL:= procent * AVG(salariu) FROM angajat;

    IF (@AV_SAL < prag_maxim) THEN
        BEGIN
    	   UPDATE angajat SET salariu = salariu  + @AV_SAL;
    	   COMMIT;
        END;
    ELSE
	ROLLBACK;
    END IF;
  END //

DELIMITER ;

SELECT nume, prenume, salariu FROM angajat;
CALL MARESTE_SALARIU_CU_PROCENT_DIN_MEDIA_SALARIILOR(0.1,500);
SELECT nume, prenume, salariu FROM angajat;



# Mariti salariul tuturor angajatilor cu o suma fixa, cu conditia ca maximul rezultat
# sa fie mai mic decat o suma fixa
DROP PROCEDURE IF EXISTS MARESTE_SALARIU_CU_SUMA_FIXA;

DELIMITER //

CREATE PROCEDURE MARESTE_SALARIU_CU_SUMA_FIXA(marire FLOAT, prag_maxim_rezultat FLOAT)
  BEGIN

    START TRANSACTION;
    UPDATE angajat SET salariu = salariu  + marire;
    SELECT @MAX_SAL:= MAX(salariu) FROM angajat;

    IF (@MAX_SAL < prag_maxim_rezultat) THEN COMMIT;
    ELSE ROLLBACK;
    END IF;

  END //
DELIMITER ;

SELECT nume, prenume, salariu FROM angajat;
CALL MARESTE_SALARIU_CU_SUMA_FIXA(400,9000);
SELECT nume, prenume, salariu FROM angajat;




# Micsoram toate salariile care trec de o suma fixa cu un procent dat

DROP PROCEDURE IF EXISTS  REDUCE_SALARIU_PESTE_MAX_CU_PROCENT_DAT;

DELIMITER //

CREATE PROCEDURE REDUCE_SALARIU_PESTE_MAX_CU_PROCENT_DAT(prag_maxim FLOAT, procent FLOAT)
  BEGIN

    START TRANSACTION;

    UPDATE angajat SET salariu = (salariu  -  procent * salariu) WHERE salariu > prag_maxim;
	COMMIT;
  END //
DELIMITER ;



# Micsoram toate salariile care trec de o suma fixa cu un procent dat
# Pragul maxim : prag_maxim
# Procent: procent
#Conditii aditionale:
# -  reducerea totala sa fie peste parametrul reducere_totala
#  - media salariilor rezultate sa fie peste paramentrul medie_minima

DROP PROCEDURE IF EXISTS  REDUCE_SALARIU_PESTE_MAX_CU_PROCENT_DAT_2;

DELIMITER //

CREATE PROCEDURE REDUCE_SALARIU_PESTE_MAX_CU_PROCENT_DAT_2(prag_maxim FLOAT, procent FLOAT, reducere_totala FLOAT, medie_minima FLOAT)
  BEGIN
    select @SUM_SAL_INI:= SUM(salariu) from angajat;
    START TRANSACTION;

    	   UPDATE angajat SET salariu = (salariu  -  procent * salariu) WHERE salariu > prag_maxim;

    	    select @SUM_SAL_END:= SUM(salariu) from angajat;

    	    SELECT @medie_end:=AVG(salariu) from angajat;

    	    IF ((@SUM_SAL_INI - @SUM_SAL_END > reducere_totala) AND (@medie_end > medie_minima)) THEN COMMIT;

    	    ELSE ROLLBACK;
    	    END IF;
  END //
DELIMITER ;


 # Micsorati salariul managerilor cu o suma fixa, daca nu au bonus si au salariu peste medie;
 # Aceasta regula insa se va aplica numai daca micsorarea este sub 20% din salariul mediu
 DROP PROCEDURE IF EXISTS MICSOREAZA_SALARIU_MANAGER;

 DELIMITER //

 CREATE PROCEDURE MICSOREAZA_SALARIU_MANAGER(micsorare FLOAT)
   BEGIN

     START TRANSACTION;

     	SELECT @AVG_SAL:= AVG(salariu) FROM angajat;
     	IF micsorare < 0.2 * @AVG_SAL THEN
     	   BEGIN
 		UPDATE angajat SET salariu = salariu - micsorare
     		WHERE salariu > @AVG_SAL AND manager_id IS NULL AND bonus =  0;
     		COMMIT;
  	   END;
  	ELSE ROLLBACK;
  	END IF;
   END //
 DELIMITER ;

 SELECT nume, prenume, ISNULL(manager_id) AS Manager, salariu,bonus FROM angajat;
 CALL MICSOREAZA_SALARIU_MANAGER(400);
 SELECT nume, prenume, ISNULL(manager_id) AS Manager, salariu,bonus FROM angajat;


# Calcul minim a doua valori

 DROP FUNCTION  IF EXISTS MINVAL;

 DELIMITER //

 CREATE FUNCTION MINVAL(X FLOAT, Y FLOAT) RETURNS FLOAT
   BEGIN
   	IF X <= Y THEN RETURN X;
   	ELSE RETURN Y;
   	END IF;
   END //
 DELIMITER ;

 # Acordati bonus toturor non-managerilor cu vechime de peste 2 ani, cu conditia
 # sa nu cresteti cheltuielile totale cu mai mult de 5000 lei;
 # bonusul va reprezenta minimul dintre (bonus_performanta) lei si 30% din salariu

 DROP PROCEDURE IF EXISTS BONUS_PERFORMANTA_ANGAJAT;

 DELIMITER //

 CREATE PROCEDURE BONUS_PERFORMANTA_ANGAJAT(bonus_performanta FLOAT)
   BEGIN

     START TRANSACTION;

     	SELECT @CHELTUIELI_TOTALE:= SUM(salariu)+SUM(bonus) FROM angajat;

     	UPDATE angajat SET bonus = MINVAL(bonus_performanta, 0.3* salariu)
     	WHERE manager_id IS NOT NULL AND
     	(YEAR(CURRENT_DATE()) - YEAR(angajare)) > 2;

	SELECT @NOI_CHELTUIELI_TOTALE:= SUM(salariu)+SUM(bonus) FROM angajat;


      	IF (@NOI_CHELTUIELI_TOTALE - @CHELTUIELI_TOTALE) >= 5000 THEN
      		ROLLBACK;
      	ELSE
      		COMMIT;

    	END IF;
   END //
 DELIMITER ;

 SELECT nume, prenume, ISNULL(manager_id) AS Manager, salariu,bonus FROM angajat;
 CALL BONUS_PERFORMANTA_ANGAJAT(800);
 SELECT nume, prenume, ISNULL(manager_id) AS Manager, salariu,bonus FROM angajat;



 # Acordati bonus (parametru) acelor manageri care au cel putin
 # 2 subalterni cu vechime peste 1 an;
 # Aceasta regula insa se va aplica numai daca rezultatul va mari
 # cheltuielile totale cu mai putin de (parametrul 2) lei
 DROP PROCEDURE IF EXISTS BONUS_PERFORMANTA_MANAGER;

 DELIMITER //

 CREATE PROCEDURE
 BONUS_PERFORMANTA_MANAGER(bonus_performanta FLOAT, total_crestere_cheltuieli FLOAT)
   BEGIN
     START TRANSACTION;

     	SELECT @CHELTUIELI_TOTALE:= SUM(salariu)+SUM(bonus) FROM angajat;

     	CREATE TEMPORARY TABLE t1
	  SELECT A1.id
	  FROM angajat AS A1 JOIN angajat as A2 on A1.id = A2.manager_id
	  WHERE (YEAR(CURRENT_DATE()) - YEAR(A2.angajare)) > 1
	  GROUP by A2.manager_id
	  HAVING count(*) >= 2;

#############
	select * from t1;
#############

     	UPDATE angajat SET bonus = bonus + bonus_performanta
     	WHERE id in (SELECT * from t1);

     	DROP TABLE t1;

	SELECT @NOI_CHELTUIELI_TOTALE:= SUM(salariu)+SUM(bonus) FROM angajat;

      	IF (@NOI_CHELTUIELI_TOTALE - @CHELTUIELI_TOTALE) >= total_crestere_cheltuieli THEN
      		ROLLBACK;
      	ELSE
      		COMMIT;
    	END IF;
   END //
 DELIMITER ;

 SELECT nume, prenume, ISNULL(manager_id) AS Manager, salariu,bonus FROM angajat;
 CALL BONUS_PERFORMANTA_MANAGER(500, 1000);
 SELECT nume, prenume, ISNULL(manager_id) AS Manager, salariu,bonus FROM angajat;


# Popa -> Popescu (500 lei)
START TRANSACTION;
UPDATE cont JOIN client ON cont.client_id = client.id
SET valoare = valoare - 500
WHERE client.nume = 'Popa';
##-----------------------
SELECT @Val:= valoare
FROM cont JOIN client ON cont.client_id = client.id
WHERE client.nume = 'Popescu';
##--
UPDATE cont JOIN client ON cont.client_id = client.id
SET valoare = @Val + 500 WHERE client.nume = 'Popescu';
##-----------------------
COMMIT;


# Marinescu -> Popa (500 lei)
START TRANSACTION;
UPDATE cont JOIN client ON cont.client_id = client.id
SET valoare = valoare - 500
WHERE client.nume = 'Marinescu';
##-----------------------
SELECT @Val:= valoare
FROM cont JOIN client ON cont.client_id = client.id
WHERE client.nume = 'Popa';
##--
UPDATE cont JOIN client ON cont.client_id = client.id
SET valoare = @Val + 500 WHERE client.nume = 'Popa';
##-----------------------
COMMIT;


# Popa -> Popescu (500 lei)
START TRANSACTION;
UPDATE cont JOIN client ON cont.client_id = client.id
SET valoare = valoare - 500
WHERE client.nume = 'Popa';
UPDATE cont JOIN client ON cont.client_id = client.id
SET valoare = valoare + 500
WHERE client.nume = 'Popescu';
COMMIT;

# Marinescu -> Popescu (500 lei)
START TRANSACTION;
UPDATE cont JOIN client ON cont.client_id = client.id
SET valoare = valoare - 500
WHERE client.nume = 'Marinescu';
UPDATE cont JOIN client ON cont.client_id = client.id
SET valoare = valoare + 500
WHERE client.nume = 'Popescu';
COMMIT;


SELECT nume, prenume, valoare
FROM cont JOIN client
ON cont.client_id = client.id;


INSERT INTO client
(nume, prenume, adresa)
VALUES
('Popa', 	'Ion', 	  'aaaa'),
('Popescu', 	'Maria',  'bbbbb'),
('Marinescu', 	'Vasile', 'ccccc'),
('Ionescu', 	'Andrei', 'ddddd');

INSERT INTO cont
(client_id,  valoare)
VALUES
(1,1000),(2,1500),(3,2000),(4,2000);