-- P0.2 - Soft delete de clientes + cambio visible Pipeline -> Embudo
-- Ejecutar una sola vez sobre la base icylab_app ANTES de subir los archivos PHP actualizados.
-- Idempotente: se puede ejecutar nuevamente sin duplicar columnas o indices.

SET @db_name := DATABASE();

-- 1) Columnas de soft delete en crm_customers
SET @sql := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE crm_customers ADD COLUMN deleted_at DATETIME NULL AFTER updated_at',
        'SELECT "crm_customers.deleted_at ya existe" AS info'
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'crm_customers'
      AND COLUMN_NAME = 'deleted_at'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE crm_customers ADD COLUMN deleted_by VARCHAR(255) NULL AFTER deleted_at',
        'SELECT "crm_customers.deleted_by ya existe" AS info'
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'crm_customers'
      AND COLUMN_NAME = 'deleted_by'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE crm_customers ADD COLUMN deleted_reason VARCHAR(255) NULL AFTER deleted_by',
        'SELECT "crm_customers.deleted_reason ya existe" AS info'
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'crm_customers'
      AND COLUMN_NAME = 'deleted_reason'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2) Indices operativos para filtrar clientes activos y validar RUT duplicado
SET @sql := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE crm_customers ADD INDEX idx_crm_customers_deleted_at (deleted_at)',
        'SELECT "idx_crm_customers_deleted_at ya existe" AS info'
    )
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'crm_customers'
      AND INDEX_NAME = 'idx_crm_customers_deleted_at'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE crm_customers ADD INDEX idx_crm_customers_deleted_tax (deleted_at, customer_tax_id)',
        'SELECT "idx_crm_customers_deleted_tax ya existe" AS info'
    )
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'crm_customers'
      AND INDEX_NAME = 'idx_crm_customers_deleted_tax'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3) Limpieza semantica de historial visible: pipeline -> embudo
UPDATE crm_activities
SET description = REPLACE(description, 'pipeline', 'embudo')
WHERE description LIKE '%pipeline%';

UPDATE crm_activities
SET description = REPLACE(description, 'Pipeline', 'Embudo')
WHERE description LIKE '%Pipeline%';

UPDATE crm_activities
SET title = REPLACE(title, 'pipeline', 'embudo')
WHERE title LIKE '%pipeline%';

UPDATE crm_activities
SET title = REPLACE(title, 'Pipeline', 'Embudo')
WHERE title LIKE '%Pipeline%';

-- 4) Verificacion rapida
SELECT
    COUNT(*) AS deleted_customers
FROM crm_customers
WHERE deleted_at IS NOT NULL;
