-- ICYLAB OS v9.1
-- Unifica nombre comercial, razón social y snapshots CRM.
-- Idempotente: puede ejecutarse nuevamente sin alterar datos ya normalizados.
-- No elimina clientes, oportunidades, documentos ni registros históricos.

START TRANSACTION;

-- Conserva el dato histórico disponible cuando falta una de las dos identidades.
UPDATE crm_customers
SET legal_name = COALESCE(NULLIF(TRIM(legal_name), ''), NULLIF(TRIM(customer_name), ''), NULLIF(TRIM(commercial_name), ''))
WHERE legal_name IS NULL OR TRIM(legal_name) = '';

UPDATE crm_customers
SET commercial_name = COALESCE(NULLIF(TRIM(commercial_name), ''), NULLIF(TRIM(customer_name), ''), NULLIF(TRIM(legal_name), ''))
WHERE commercial_name IS NULL OR TRIM(commercial_name) = '';

-- customer_name queda como alias comercial para compatibilidad con código heredado.
UPDATE crm_customers
SET customer_name = COALESCE(NULLIF(TRIM(commercial_name), ''), NULLIF(TRIM(legal_name), ''), NULLIF(TRIM(customer_name), '')),
    updated_at = updated_at
WHERE COALESCE(customer_name, '') <>
      COALESCE(NULLIF(TRIM(commercial_name), ''), NULLIF(TRIM(legal_name), ''), NULLIF(TRIM(customer_name), ''));

-- Corrige snapshots utilizados por el embudo y el timeline.
UPDATE crm_opportunities o
INNER JOIN crm_customers c ON c.id = o.customer_id
SET o.customer_name = COALESCE(NULLIF(TRIM(c.commercial_name), ''), NULLIF(TRIM(c.customer_name), ''), c.legal_name),
    o.customer_tax_id = c.customer_tax_id,
    o.updated_at = o.updated_at
WHERE o.deleted_at IS NULL;

UPDATE crm_activities a
INNER JOIN crm_customers c ON c.id = a.customer_id
SET a.customer_name = COALESCE(NULLIF(TRIM(c.commercial_name), ''), NULLIF(TRIM(c.customer_name), ''), c.legal_name)
WHERE a.customer_id IS NOT NULL;

COMMIT;

SELECT
    'customer_identity_v9_1' AS migration,
    COUNT(*) AS active_customers,
    SUM(legal_name IS NULL OR TRIM(legal_name) = '') AS missing_legal_name,
    SUM(commercial_name IS NULL OR TRIM(commercial_name) = '') AS missing_commercial_name,
    SUM(COALESCE(customer_name, '') <>
        COALESCE(NULLIF(TRIM(commercial_name), ''), NULLIF(TRIM(legal_name), ''), NULLIF(TRIM(customer_name), ''))) AS alias_drift
FROM crm_customers
WHERE deleted_at IS NULL;

