-- =====================================================
-- MIGRACIONES FALTANTES: Performance Evaluations Module
-- Ejecutar en phpMyAdmin o MySQL CLI
-- VERSIÓN CORREGIDA: Con campos de texto (question_text, etc.)
-- =====================================================

-- 1. ELIMINAR tablas si ya existen (para recrearlas correctamente)
DROP TABLE IF EXISTS `evaluation_hseq_responses`;
DROP TABLE IF EXISTS `evaluation_responsibility_responses`;
DROP TABLE IF EXISTS `evaluation_competency_responses`;

-- 2. Tabla: evaluation_hseq_responses
CREATE TABLE `evaluation_hseq_responses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `evaluation_id` int(11) NOT NULL,
  `question_key` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `question_text` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `rating` int(11) NOT NULL,
  `comments` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `evaluation_hseq_responses_evaluation_id_index` (`evaluation_id`),
  CONSTRAINT `hseq_eval_fk` FOREIGN KEY (`evaluation_id`) REFERENCES `performance_evaluations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Tabla: evaluation_responsibility_responses
CREATE TABLE `evaluation_responsibility_responses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `evaluation_id` int(11) NOT NULL,
  `responsibility_key` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `responsibility_text` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `rating` int(11) NOT NULL,
  `comments` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `evaluation_responsibility_responses_evaluation_id_index` (`evaluation_id`),
  CONSTRAINT `resp_eval_fk` FOREIGN KEY (`evaluation_id`) REFERENCES `performance_evaluations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Tabla: evaluation_competency_responses
CREATE TABLE `evaluation_competency_responses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `evaluation_id` int(11) NOT NULL,
  `competency_key` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `competency_text` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `rating` int(11) NOT NULL,
  `comments` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `evaluation_competency_responses_evaluation_id_index` (`evaluation_id`),
  CONSTRAINT `comp_eval_fk` FOREIGN KEY (`evaluation_id`) REFERENCES `performance_evaluations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Verificación: Verificar que las tablas se crearon
-- =====================================================
SHOW TABLES LIKE 'evaluation_%';

-- =====================================================
-- Verificación: Ver estructura de las tablas
-- =====================================================
DESCRIBE evaluation_hseq_responses;
DESCRIBE evaluation_responsibility_responses;
DESCRIBE evaluation_competency_responses;