-- 2026_02_milestone_templates.sql
-- Plantillas de hitos editables (por tipo EE/IA) + mejoras compatibles en project_milestones

-- 1) Tabla de plantillas
CREATE TABLE IF NOT EXISTS milestone_templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_type ENUM('EE','IA') NOT NULL,
  name VARCHAR(255) NOT NULL,
  position INT NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_mt_type_pos (project_type, position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2) Asegurar columna position en project_milestones (MySQL 5.7 compatible)
SET @has_pos := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'project_milestones'
    AND COLUMN_NAME = 'position'
);
SET @sql := IF(@has_pos = 0,
  'ALTER TABLE project_milestones ADD COLUMN position INT NOT NULL DEFAULT 1 AFTER name',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3) Permitir due_date NULL (si tu hosting usa MySQL antiguo y falla, ejecútalo manualmente)
-- Nota: en MySQL 5.7 esto funciona.
ALTER TABLE project_milestones MODIFY COLUMN due_date DATE NULL;

-- 4) Seed por defecto (solo si está vacío por tipo)
INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'EE', 'Elección de tema', 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM milestone_templates WHERE project_type='EE');

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'EE', 'Pregunta de investigación (RQ)', 2, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='EE') = 1;

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'EE', 'Propuesta aprobada', 3, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='EE') = 2;

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'EE', 'Búsqueda de fuentes', 4, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='EE') = 3;

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'EE', 'Borrador 1', 5, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='EE') = 4;

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'EE', 'Retroalimentación aplicada', 6, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='EE') = 5;

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'EE', 'Borrador final', 7, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='EE') = 6;

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'EE', 'Viva voce (entrevista final)', 8, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='EE') = 7;

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'EE', 'Versión final subida', 9, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='EE') = 8;


INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'IA', 'Tema y enfoque', 1, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM milestone_templates WHERE project_type='IA');

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'IA', 'Pregunta / objetivo', 2, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='IA') = 1;

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'IA', 'Planificación / diseño', 3, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='IA') = 2;

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'IA', 'Recolección de datos / evidencia', 4, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='IA') = 3;

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'IA', 'Procesamiento / análisis', 5, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='IA') = 4;

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'IA', 'Discusión / evaluación', 6, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='IA') = 5;

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'IA', 'Borrador', 7, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='IA') = 6;

INSERT INTO milestone_templates (project_type, name, position, is_active)
SELECT 'IA', 'Versión final', 8, 1 FROM DUAL
WHERE (SELECT COUNT(*) FROM milestone_templates WHERE project_type='IA') = 7;