-- ============================================
-- MIGRACIÓN 005: loss_reasons, lead_status_history
-- ============================================

CREATE TABLE IF NOT EXISTS loss_reasons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_status_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NOT NULL,
    previous_status VARCHAR(30) NULL,
    new_status VARCHAR(30) NOT NULL,
    loss_reason_id INT NULL,
    notes VARCHAR(500) NULL,
    changed_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (loss_reason_id) REFERENCES loss_reasons(id) ON DELETE SET NULL,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed: motivos de pérdida por defecto
INSERT INTO loss_reasons (name, description, status) VALUES
('Precio', 'El lead consideró que el precio era muy alto', 'active'),
('Competencia', 'El lead eligió un competidor', 'active'),
('Sin interés', 'El lead perdió interés en el producto/servicio', 'active'),
('No contactable', 'No fue posible contactar al lead', 'active'),
('Presupuesto', 'El lead no cuenta con presupuesto', 'active'),
('Otro', 'Otro motivo no especificado', 'active');