---

START TRANSACTION;

DELETE FROM WORK WHERE projectId = 102;

DELETE FROM PROJECT WHERE shifr = 102;

ROLLBACK;



START TRANSACTION;

DELETE FROM WORK WHERE projectId = 102;

DELETE FROM PROJECT WHERE shifr = 102;

COMMIT;



ALTER TABLE TEAM ADD COLUMN kol INT DEFAULT 0;



START TRANSACTION;

SET @A = (SELECT COUNT(*) FROM STUDENTS WHERE team = 1);

UPDATE TEAM SET kol = @A WHERE id = 1;

COMMIT;



DELIMITER //

CREATE PROCEDURE checkOK(

    IN Sh VARCHAR(20),

    IN Tm VARCHAR(100),

    IN Nz VARCHAR(50),

    IN dNla DATE,

    IN dOk DATE

)

BEGIN

    DECLARE rez VARCHAR(3) DEFAULT 'OK';

    DECLARE pr INT;

    START TRANSACTION;

    INSERT INTO PROJECT (shifr, topic, project, dateStart, dateFinish) VALUES (Sh, Tm, Nz, dNla, dOk);

    SELECT DATEDIFF(dateFinish, dateStart) INTO pr FROM PROJECT WHERE shifr = Sh;

    IF pr > 0 THEN

        COMMIT;

    ELSE

        ROLLBACK;

        SET rez = 'No';

    END IF;

    SELECT rez AS Result;

END //

DELIMITER ;



SET @sh = 123, @tm = 'Тестовый проект', @nz = 'Проект_А', @d1 = '2026-01-01', @d2 = '2026-12-31';

CALL checkOK(@sh, @tm, @nz, @d1, @d2);



SET @sh = 122, @tm = 'Ошибка дат', @nz = 'Проект_Б', @d1 = '2025-12-31', @d2 = '2025-01-01';

CALL checkOK(@sh, @tm, @nz, @d1, @d2);



SET GLOBAL event_scheduler = ON;



DELIMITER //

CREATE EVENT delete_old_work

ON SCHEDULE EVERY 1 DAY

DO

BEGIN

    DELETE w FROM WORK w INNER JOIN PROJECT p ON w.project = p.shifr WHERE p.dateFinish < DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR);

END //

DELIMITER ;



ALTER EVENT delete_old_work ON SCHEDULE EVERY 1 MINUTE;

SHOW EVENTS LIKE 'delete_old_work';



DELIMITER //

CREATE PROCEDURE reduce_cost_yearly()

BEGIN

    UPDATE PROJECT SET cost = cost * 0.99 WHERE dateStart <= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR);

END //



CREATE EVENT yearly_cost_update ON SCHEDULE EVERY 1 YEAR DO CALL reduce_cost_yearly() //

ALTER EVENT yearly_cost_update ON SCHEDULE EVERY 1 MINUTE //

ALTER EVENT yearly_cost_update ON SCHEDULE EVERY 1 YEAR //

DELIMITER ;



SHOW EVENTS LIKE 'yearly_cost_update';

SET SQL_SAFE_UPDATES = 0; -- Отключаем защиту

-- ALTER TABLE PROJECT ADD COLUMN atWork TINYINT(1) DEFAULT 1;
-- UPDATE PROJECT SET atWork = 1 WHERE shifr IS NOT NULL;

DELIMITER //


DROP PROCEDURE IF EXISTS delete_project_safe //
CREATE PROCEDURE delete_project_safe(IN p_shifr VARCHAR(20))
BEGIN
    DECLARE is_active TINYINT;
    START TRANSACTION;
    
    SELECT atWork INTO is_active FROM PROJECT WHERE shifr = p_shifr;
    
    IF is_active = 1 THEN
        ROLLBACK;
        SELECT 'ОШИБКА: Проект является текущим (atWork=1). Удаление отменено.' AS Message;
    ELSE
        DELETE FROM WORK WHERE project = p_shifr;
        DELETE FROM PROJECT WHERE shifr = p_shifr;
        COMMIT;
        SELECT 'Проект успешно удалён.' AS Message;
    END IF;
END //

CREATE TABLE IF NOT EXISTS USERS (
    login VARCHAR(50) PRIMARY KEY,
    pass VARCHAR(255),
    phone_home VARCHAR(20),
    phone_cell VARCHAR(20)
) //

DROP PROCEDURE IF EXISTS insert_user_safe //
CREATE PROCEDURE insert_user_safe(IN p_login VARCHAR(50), IN p_home VARCHAR(20), IN p_cell VARCHAR(20))
BEGIN
    IF p_home IS NULL AND p_cell IS NULL THEN
        SELECT 'ОШИБКА: Необходимо указать домашний или сотовый телефон.' AS Message;
    ELSE
        INSERT INTO USERS (login, phone_home, phone_cell) VALUES (p_login, p_home, p_cell);
        SELECT 'Пользователь успешно добавлен.' AS Message;
    END IF;
END //

DROP PROCEDURE IF EXISTS delete_student_safe //
CREATE PROCEDURE delete_student_safe(IN p_st_id INT)
BEGIN
    DECLARE leader_count INT;
    START TRANSACTION;
    
    SELECT COUNT(*) INTO leader_count FROM TEAM WHERE lider = p_st_id;
    
    IF leader_count > 0 THEN
        ROLLBACK;
        SELECT 'ОШИБКА: Студент является лидером команды. Удаление отменено.' AS Message;
    ELSE
        DELETE FROM STUDENT WHERE id = p_st_id;
        COMMIT;
        SELECT 'Студент успешно удалён.' AS Message;
    END IF;
END //

DELIMITER ;

SET SQL_SAFE_UPDATES = 1;