-- ICYLAB OS v9.1 - CRM + Facturación integrity preflight
-- READ ONLY: contiene exclusivamente consultas SELECT.

SELECT '01_customer_identity' AS section,
       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;

SELECT '02_duplicate_rut_groups' AS section,
       COUNT(*) AS duplicate_groups
FROM (
    SELECT REPLACE(REPLACE(REPLACE(UPPER(customer_tax_id), '.', ''), '-', ''), ' ', '') AS normalized_rut
    FROM crm_customers
    WHERE deleted_at IS NULL
      AND customer_tax_id IS NOT NULL
      AND TRIM(customer_tax_id) <> ''
    GROUP BY normalized_rut
    HAVING COUNT(*) > 1
) duplicated;

SELECT '03_opportunity_customer_links' AS section,
       COUNT(*) AS active_opportunities,
       SUM(c.id IS NULL) AS orphan_customer_links,
       SUM(c.id IS NOT NULL AND COALESCE(o.customer_name, '') <>
           COALESCE(NULLIF(TRIM(c.commercial_name), ''), NULLIF(TRIM(c.customer_name), ''), c.legal_name)) AS display_snapshot_drift,
       SUM(c.id IS NOT NULL AND COALESCE(o.customer_tax_id, '') <> COALESCE(c.customer_tax_id, '')) AS tax_id_snapshot_drift
FROM crm_opportunities o
LEFT JOIN crm_customers c ON c.id = o.customer_id AND c.deleted_at IS NULL
WHERE o.deleted_at IS NULL;

SELECT '04_quote_links' AS section,
       COUNT(*) AS active_quotes,
       SUM(c.id IS NULL) AS orphan_customer_links,
       SUM(q.opportunity_id IS NOT NULL AND o.id IS NULL) AS orphan_opportunity_links,
       SUM(o.id IS NOT NULL AND o.customer_id <> q.customer_id) AS opportunity_customer_mismatch
FROM crm_quotes q
LEFT JOIN crm_customers c ON c.id = q.customer_id AND c.deleted_at IS NULL
LEFT JOIN crm_opportunities o ON o.id = q.opportunity_id AND o.deleted_at IS NULL
WHERE q.deleted_at IS NULL;

SELECT '05_invoiced_opportunities' AS section,
       COUNT(*) AS invoiced_opportunities,
       SUM(o.facto_document_id IS NULL OR TRIM(o.facto_document_id) = '') AS without_facto_document_id,
       SUM(o.facto_document_id IS NOT NULL AND TRIM(o.facto_document_id) <> '' AND fd.id IS NULL) AS without_production_document
FROM crm_opportunities o
LEFT JOIN facto_documents fd
  ON fd.environment = 'production'
 AND BINARY fd.document_id = BINARY o.facto_document_id
WHERE o.deleted_at IS NULL
  AND o.stage = 'facturada';

SELECT '06_billing_quote_links' AS section,
       COUNT(*) AS billing_quotes,
       SUM(q.facto_document_id IS NULL OR TRIM(q.facto_document_id) = '') AS without_facto_document_id,
       SUM(q.facto_document_id IS NOT NULL AND TRIM(q.facto_document_id) <> '' AND fd.id IS NULL) AS without_matching_document
FROM crm_quotes q
LEFT JOIN facto_documents fd
  ON fd.environment = COALESCE(NULLIF(q.billing_environment, ''), 'production')
 AND BINARY fd.document_id = BINARY q.facto_document_id
WHERE q.deleted_at IS NULL
  AND q.status IN ('Facturada', 'Esperando pago', 'Pagada');

SELECT '07_crm_document_links' AS section,
       COUNT(*) AS crm_documents,
       SUM(d.opportunity_id IS NOT NULL AND o.id IS NULL) AS orphan_opportunity_links,
       SUM(d.customer_id IS NOT NULL AND c.id IS NULL) AS orphan_customer_links,
       SUM(d.facto_document_id IS NOT NULL AND TRIM(d.facto_document_id) <> '' AND fd.id IS NULL) AS missing_facto_document,
       SUM(d.file_path IS NOT NULL AND TRIM(d.file_path) <> '') AS stored_file_references
FROM crm_documents d
LEFT JOIN crm_opportunities o ON o.id = d.opportunity_id
LEFT JOIN crm_customers c ON c.id = d.customer_id
LEFT JOIN facto_documents fd
  ON fd.environment = 'production'
 AND BINARY fd.document_id = BINARY d.facto_document_id;

SELECT '08_collection_arithmetic' AS section,
       direction,
       COUNT(*) AS collection_rows,
       SUM(ABS(balance_amount - (total_amount - paid_amount)) > 0.01) AS inconsistent_rows,
       ROUND(SUM(total_amount - paid_amount - balance_amount), 2) AS net_difference
FROM billing_collections
WHERE environment = 'production'
GROUP BY direction;

SELECT '09_active_payment_reconciliation' AS section,
       COUNT(*) AS collection_rows,
       SUM(ABS(payment_sum - paid_amount) > 0.01) AS mismatch_rows,
       ROUND(SUM(payment_sum - paid_amount), 2) AS net_difference
FROM (
    SELECT c.id,
           c.paid_amount,
           COALESCE(SUM(CASE WHEN COALESCE(p.is_voided, 0) = 0 THEN p.amount ELSE 0 END), 0) AS payment_sum
    FROM billing_collections c
    LEFT JOIN billing_payments p
      ON p.collection_id = c.id
     AND p.environment = c.environment
    WHERE c.environment = 'production'
    GROUP BY c.id, c.paid_amount
) reconciliation;

SELECT '10_pending_remote_operations' AS section,
       'issue_logs' AS source,
       COUNT(*) AS pending_rows
FROM billing_issue_logs
WHERE environment = 'production' AND status IN ('sent', 'pending_reconcile')
UNION ALL
SELECT '10_pending_remote_operations', 'manual_operations', COUNT(*)
FROM billing_manual_operations
WHERE environment = 'production' AND status IN ('issuing', 'pending_local_commit', 'remote_uncertain')
UNION ALL
SELECT '10_pending_remote_operations', 'export_operations', COUNT(*)
FROM billing_export_operations
WHERE environment = 'production' AND status IN ('issuing', 'pending_local_commit', 'remote_uncertain')
UNION ALL
SELECT '10_pending_remote_operations', 'tax_operations', COUNT(*)
FROM billing_tax_operations
WHERE environment = 'production' AND status IN ('issuing', 'pending_local_commit', 'remote_uncertain');

