101 lines
3.2 KiB
SQL
101 lines
3.2 KiB
SQL
-- 1. Eliminar tablas antiguas (El respaldo ya está hecho)
|
|
DROP TABLE IF EXISTS surveys_answers;
|
|
DROP TABLE IF EXISTS surveys_questions;
|
|
DROP TABLE IF EXISTS surveys;
|
|
|
|
-- 2. Crear tabla de configuracion de encuestas
|
|
CREATE TABLE surveys_questions (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
question VARCHAR(255) DEFAULT NULL,
|
|
levels INT DEFAULT 5,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
|
|
|
|
-- 3. Crear tabla de respuestas Historicas
|
|
CREATE TABLE surveys_answers (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
context_id INT DEFAULT NULL,
|
|
id_user INT DEFAULT NULL,
|
|
rh_name VARCHAR(255) DEFAULT NULL,
|
|
snapshot_json JSON DEFAULT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
|
|
|
|
-- 4. Reemplazar Stored Procedure: sp_upsert_survey
|
|
DELIMITER $$
|
|
DROP PROCEDURE IF EXISTS sp_upsert_survey$$
|
|
CREATE PROCEDURE sp_upsert_survey(
|
|
IN p_id INT,
|
|
IN p_surveyType VARCHAR(255),
|
|
IN p_title VARCHAR(255),
|
|
IN p_checkboxes TEXT,
|
|
IN p_openQuestions TEXT,
|
|
IN p_ranges JSON
|
|
)
|
|
BEGIN
|
|
-- Limpiamos las preguntas actuales de forma definitiva
|
|
TRUNCATE TABLE surveys_questions;
|
|
|
|
-- Insertamos las nuevas preguntas iterando sobre el JSON p_ranges
|
|
-- El JSON tiene la forma: [{"question": "Pregunta 1", "levels": 5}, ...]
|
|
IF p_ranges IS NOT NULL THEN
|
|
INSERT INTO surveys_questions (question, levels)
|
|
SELECT
|
|
JSON_UNQUOTE(JSON_EXTRACT(value, '$.question')),
|
|
JSON_UNQUOTE(JSON_EXTRACT(value, '$.levels'))
|
|
FROM JSON_TABLE(p_ranges, '$[*]' COLUMNS (value JSON PATH '$')) AS jt;
|
|
END IF;
|
|
|
|
SELECT 1 AS success, 'Encuesta actualizada correctamente' AS message;
|
|
END$$
|
|
DELIMITER ;
|
|
|
|
-- 5. Reemplazar Stored Procedure: sp_get_all_surveys
|
|
DELIMITER $$
|
|
DROP PROCEDURE IF EXISTS sp_get_all_surveys$$
|
|
CREATE PROCEDURE sp_get_all_surveys()
|
|
BEGIN
|
|
-- Forzamos la estructura que Vue espera en el frontend:
|
|
-- [{ id: 1, docType: 'SERVICE_ORDER', name: 'Encuesta', questions: '[...]' }]
|
|
-- En este caso los IDs no importan, agrupamos todo en un único objeto "Virtual".
|
|
|
|
SELECT
|
|
1 AS id,
|
|
'Encuesta de Satisfacción' AS title,
|
|
'SERVICE_ORDER' AS surveyType,
|
|
IFNULL(
|
|
(
|
|
SELECT JSON_ARRAYAGG(
|
|
JSON_OBJECT(
|
|
'id', id,
|
|
'typeQuestion', 'ranges',
|
|
'question', question,
|
|
'levels', levels
|
|
)
|
|
)
|
|
FROM surveys_questions
|
|
),
|
|
'[]'
|
|
) AS questions;
|
|
END$$
|
|
DELIMITER ;
|
|
|
|
-- 6. Reemplazar Stored Procedure: sp_save_survey_answers
|
|
DELIMITER $$
|
|
DROP PROCEDURE IF EXISTS sp_save_survey_answers$$
|
|
CREATE PROCEDURE sp_save_survey_answers(
|
|
IN p_surveyId INT,
|
|
IN p_rhName VARCHAR(255),
|
|
IN p_answers_json JSON
|
|
)
|
|
BEGIN
|
|
-- P_answers_json debe contener la "fotografia" cruda enviada desde frontend
|
|
INSERT INTO surveys_answers (context_id, rh_name, snapshot_json)
|
|
VALUES (p_surveyId, p_rhName, p_answers_json);
|
|
|
|
SELECT 1 AS success;
|
|
END$$
|
|
DELIMITER ;
|