DELIMITER $$

-- Deshabilitar restricciones de claves foráneas temporalmente

-- Eliminar todas las vistas
DROP PROCEDURE IF EXISTS drop_all_views$$
CREATE PROCEDURE drop_all_views()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE view_name VARCHAR(255);
    DECLARE cur CURSOR FOR
        SELECT table_name
        FROM information_schema.views
        WHERE table_schema = 'cdt_timecontroling';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    view_loop: LOOP
        FETCH cur INTO view_name;
        IF done THEN
            LEAVE view_loop;
        END IF;
        SET @sql = CONCAT('DROP VIEW IF EXISTS `', view_name, '`;');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END$$
CALL drop_all_views$$

-- Eliminar todas las tablas
DROP PROCEDURE IF EXISTS drop_all_tables$$
CREATE PROCEDURE drop_all_tables()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE table_name VARCHAR(255);
    DECLARE cur CURSOR FOR
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'cdt_timecontroling';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    table_loop: LOOP
        FETCH cur INTO table_name;
        IF done THEN
            LEAVE table_loop;
        END IF;
        SET @sql = CONCAT('DROP TABLE IF EXISTS `', table_name, '`;');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END$$
CALL drop_all_tables$$

-- Eliminar todos los procedimientos almacenados
DROP PROCEDURE IF EXISTS drop_all_procedures$$
CREATE PROCEDURE drop_all_procedures()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE proc_name VARCHAR(255);
    DECLARE cur CURSOR FOR
        SELECT routine_name
        FROM information_schema.routines
        WHERE routine_schema = 'cdt_timecontroling' AND routine_type = 'PROCEDURE';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    proc_loop: LOOP
        FETCH cur INTO proc_name;
        IF done THEN
            LEAVE proc_loop;
        END IF;
        SET @sql = CONCAT('DROP PROCEDURE IF EXISTS `', proc_name, '`;');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END$$
CALL drop_all_procedures$$

-- Eliminar todas las funciones almacenadas
DROP PROCEDURE IF EXISTS drop_all_functions$$
CREATE PROCEDURE drop_all_functions()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE func_name VARCHAR(255);
    DECLARE cur CURSOR FOR
        SELECT routine_name
        FROM information_schema.routines
        WHERE routine_schema = 'cdt_timecontroling' AND routine_type = 'FUNCTION';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    func_loop: LOOP
        FETCH cur INTO func_name;
        IF done THEN
            LEAVE func_loop;
        END IF;
        SET @sql = CONCAT('DROP FUNCTION IF EXISTS `', func_name, '`;');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END$$
CALL drop_all_functions$$

-- Restaurar las restricciones de claves foráneas

DELIMITER ;



-- Generar y ejecutar los comandos para eliminar las tablas
SELECT CONCAT('DROP TABLE IF EXISTS `', areas, '`;') AS drop_statement
FROM information_schema.tables
WHERE table_schema = 'cdt_timecontroling';

-- Restaurar las restricciones de claves foráneas
