-- Migración 036: Crear tabla task_attachments para soportar múltiples archivos adjuntos
-- Migrar datos existentes desde tasks.attachment_path / attachment_original_name
-- Luego eliminar columnas antiguas de tasks

CREATE TABLE IF NOT EXISTS task_attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_id INT NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_task_attachments_task_id ON task_attachments(task_id);

-- Migrar adjuntos existentes
INSERT INTO task_attachments (task_id, file_path, original_name, created_at)
SELECT id, attachment_path, attachment_original_name, updated_at
FROM tasks
WHERE attachment_path IS NOT NULL AND attachment_path != '';

-- Eliminar columnas antiguas
ALTER TABLE tasks
    DROP COLUMN attachment_path,
    DROP COLUMN attachment_original_name;
