-- ICYLAB OS v8.5.3
-- Corrección de matching para facturas de exportación y ficha de cliente.
-- Idempotente. Ejecutar una sola vez en producción después de backup.

SET @environment := 'production';

CREATE TABLE IF NOT EXISTS facto_documents_export_link_backup_v853 LIKE facto_documents;

INSERT IGNORE INTO facto_documents_export_link_backup_v853
SELECT d.*
FROM facto_documents d
WHERE d.environment = @environment
  AND d.received_issued_flag = 1
  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112);

-- 1. Relación explícita desde cotización.
UPDATE facto_documents d
INNER JOIN crm_quotes q
  ON BINARY q.facto_document_id = BINARY d.document_id
SET d.crm_quote_id = COALESCE(d.crm_quote_id, q.id),
    d.crm_opportunity_id = COALESCE(d.crm_opportunity_id, q.opportunity_id),
    d.crm_customer_id = COALESCE(d.crm_customer_id, q.customer_id),
    d.finance_center_id = COALESCE(d.finance_center_id, q.finance_center_id),
    d.link_status = CASE WHEN d.link_status = 'unlinked' THEN 'auto_linked' ELSE d.link_status END,
    d.link_confidence = COALESCE(d.link_confidence, 100),
    d.linked_at = COALESCE(d.linked_at, NOW()),
    d.updated_at = CURRENT_TIMESTAMP
WHERE d.environment = @environment
  AND d.received_issued_flag = 1
  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112);

-- 2. Llave estable de FACTO. Solo se usa si identifica un cliente único.
UPDATE facto_documents d
INNER JOIN (
    SELECT facto_customer_supplier_id, MIN(id) AS customer_id
    FROM crm_customers
    WHERE deleted_at IS NULL
      AND facto_customer_supplier_id IS NOT NULL
      AND facto_customer_supplier_id <> ''
    GROUP BY facto_customer_supplier_id
    HAVING COUNT(*) = 1
) m ON BINARY m.facto_customer_supplier_id = BINARY d.customer_supplier_id
SET d.crm_customer_id = m.customer_id,
    d.link_status = 'auto_linked',
    d.link_confidence = 100,
    d.linked_at = COALESCE(d.linked_at, NOW()),
    d.updated_at = CURRENT_TIMESTAMP
WHERE d.environment = @environment
  AND d.received_issued_flag = 1
  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)
  AND d.crm_customer_id IS NULL;

-- 3. Identificador extranjero canónico.
UPDATE facto_documents d
INNER JOIN (
    SELECT
        REPLACE(REPLACE(REPLACE(UPPER(export_tax_id_code),'.',''),'-',''),' ','') AS normalized_tax,
        MIN(id) AS customer_id
    FROM crm_customers
    WHERE deleted_at IS NULL
      AND export_tax_id_code IS NOT NULL
      AND export_tax_id_code <> ''
    GROUP BY REPLACE(REPLACE(REPLACE(UPPER(export_tax_id_code),'.',''),'-',''),' ','')
    HAVING COUNT(*) = 1
) m
  ON m.normalized_tax = REPLACE(REPLACE(REPLACE(UPPER(d.receiver_tax_id_code),'.',''),'-',''),' ','')
SET d.crm_customer_id = m.customer_id,
    d.link_status = 'auto_linked',
    d.link_confidence = 98,
    d.linked_at = COALESCE(d.linked_at, NOW()),
    d.updated_at = CURRENT_TIMESTAMP
WHERE d.environment = @environment
  AND d.received_issued_flag = 1
  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)
  AND d.crm_customer_id IS NULL
  AND d.receiver_tax_id_code IS NOT NULL
  AND d.receiver_tax_id_code <> '';

-- 4. Compatibilidad histórica: identificador extranjero almacenado en customer_tax_id.
UPDATE facto_documents d
INNER JOIN (
    SELECT
        REPLACE(REPLACE(REPLACE(UPPER(customer_tax_id),'.',''),'-',''),' ','') AS normalized_tax,
        MIN(id) AS customer_id
    FROM crm_customers
    WHERE deleted_at IS NULL
      AND customer_tax_id IS NOT NULL
      AND customer_tax_id <> ''
    GROUP BY REPLACE(REPLACE(REPLACE(UPPER(customer_tax_id),'.',''),'-',''),' ','')
    HAVING COUNT(*) = 1
) m
  ON m.normalized_tax = REPLACE(REPLACE(REPLACE(UPPER(d.receiver_tax_id_code),'.',''),'-',''),' ','')
SET d.crm_customer_id = m.customer_id,
    d.link_status = 'auto_linked',
    d.link_confidence = 95,
    d.linked_at = COALESCE(d.linked_at, NOW()),
    d.updated_at = CURRENT_TIMESTAMP
WHERE d.environment = @environment
  AND d.received_issued_flag = 1
  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)
  AND d.crm_customer_id IS NULL
  AND d.receiver_tax_id_code IS NOT NULL
  AND d.receiver_tax_id_code <> '';

-- 5. Normaliza metadatos del documento y del cliente de exportación.
UPDATE facto_documents d
LEFT JOIN billing_currencies cur
  ON cur.facto_currency_id = CAST(d.currency_id AS UNSIGNED)
SET d.currency_code = COALESCE(NULLIF(d.currency_code, ''), cur.currency_code),
    d.source_mode = COALESCE(NULLIF(d.source_mode, ''), 'facto_sync'),
    d.updated_at = CURRENT_TIMESTAMP
WHERE d.environment = @environment
  AND d.received_issued_flag = 1
  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112);

UPDATE facto_documents d
INNER JOIN crm_customers c ON c.id = d.crm_customer_id
SET d.receiver_country_id = COALESCE(d.receiver_country_id, c.export_country_id, c.country_id),
    d.receiver_tax_id_type = COALESCE(NULLIF(d.receiver_tax_id_type, ''), NULLIF(c.export_tax_id_type, ''), NULLIF(c.tax_id_type, ''), 'IDEXTRANJERO'),
    d.finance_center_id = COALESCE(d.finance_center_id, c.default_income_center_id),
    d.updated_at = CURRENT_TIMESTAMP
WHERE d.environment = @environment
  AND d.received_issued_flag = 1
  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112);

UPDATE crm_customers c
INNER JOIN facto_documents d
  ON d.crm_customer_id = c.id
 AND d.environment = @environment
 AND d.received_issued_flag = 1
 AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)
SET c.export_tax_id_code = COALESCE(NULLIF(c.export_tax_id_code, ''), NULLIF(d.receiver_tax_id_code, '')),
    c.export_tax_id_type = COALESCE(NULLIF(c.export_tax_id_type, ''), NULLIF(d.receiver_tax_id_type, ''), 'IDEXTRANJERO'),
    c.export_country_id = COALESCE(c.export_country_id, d.receiver_country_id, CASE WHEN c.country_id <> 253 THEN c.country_id ELSE NULL END),
    c.updated_at = CURRENT_TIMESTAMP
WHERE c.deleted_at IS NULL;

-- 6. Propaga el vínculo a cuentas por cobrar históricas.
UPDATE billing_collections bc
INNER JOIN facto_documents d
  ON d.environment = bc.environment
 AND BINARY d.document_id = BINARY bc.facto_document_id
SET bc.customer_id = COALESCE(bc.customer_id, d.crm_customer_id),
    bc.finance_center_id = COALESCE(bc.finance_center_id, d.finance_center_id),
    bc.currency_code = COALESCE(NULLIF(bc.currency_code, ''), NULLIF(d.currency_code, '')),
    bc.currency_id = COALESCE(bc.currency_id, d.currency_id),
    bc.exchange_rate_value = COALESCE(bc.exchange_rate_value, d.exchange_rate_value),
    bc.updated_at = CURRENT_TIMESTAMP
WHERE d.environment = @environment
  AND d.received_issued_flag = 1
  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)
  AND d.crm_customer_id IS NOT NULL;

-- Controles de aceptación.
SELECT COUNT(*) AS export_documents_total
FROM facto_documents d
WHERE d.environment = @environment
  AND d.received_issued_flag = 1
  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112);

SELECT COUNT(*) AS export_documents_unlinked
FROM facto_documents d
WHERE d.environment = @environment
  AND d.received_issued_flag = 1
  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)
  AND d.crm_customer_id IS NULL;

SELECT
    d.id AS local_document_id,
    d.document_id,
    d.document_number,
    d.issue_date,
    d.receiver_legal_name,
    d.receiver_tax_id_code,
    d.customer_supplier_id,
    d.crm_customer_id,
    c.customer_name,
    d.link_status,
    d.link_confidence,
    d.currency_code,
    d.exchange_rate_value
FROM facto_documents d
LEFT JOIN crm_customers c ON c.id = d.crm_customer_id
WHERE d.environment = @environment
  AND d.received_issued_flag = 1
  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)
ORDER BY d.issue_date DESC, d.id DESC;
