-- ICYLAB Billing - production readiness preflight v5
-- Date: 2026-07-13
-- READ ONLY: this file contains SELECT statements only.
-- Run only after scripts/billing_production_preflight.php confirms the schema.

SELECT '00_context' AS section,
       DATABASE() AS database_name,
       VERSION() AS mysql_version,
       UTC_TIMESTAMP() AS generated_at_utc;

SELECT '01_required_tables' AS section,
       required.table_name,
       CASE WHEN actual.TABLE_NAME IS NULL THEN 'MISSING' ELSE 'OK' END AS status
FROM (
    SELECT 'billing_document_types' AS table_name UNION ALL
    SELECT 'billing_issue_logs' UNION ALL
    SELECT 'billing_collections' UNION ALL
    SELECT 'billing_payments' UNION ALL
    SELECT 'billing_payment_allocations' UNION ALL
    SELECT 'billing_manual_operations' UNION ALL
    SELECT 'billing_export_operations' UNION ALL
    SELECT 'billing_tax_operations' UNION ALL
    SELECT 'billing_complementary_operations' UNION ALL
    SELECT 'billing_dispatch_guides' UNION ALL
    SELECT 'billing_reconciliation_issues' UNION ALL
    SELECT 'facto_tokens' UNION ALL
    SELECT 'facto_documents' UNION ALL
    SELECT 'facto_inbox_documents' UNION ALL
    SELECT 'crm_customers' UNION ALL
    SELECT 'finance_centers' UNION ALL
    SELECT 'permissions' UNION ALL
    SELECT 'role_permissions' UNION ALL
    SELECT 'users' UNION ALL
    SELECT 'facto_sync_logs'
) required
LEFT JOIN information_schema.TABLES actual
  ON actual.TABLE_SCHEMA = DATABASE()
 AND actual.TABLE_NAME = required.table_name
ORDER BY required.table_name;

SELECT '02_required_indexes' AS section,
       expected.table_name,
       expected.index_name,
       CASE WHEN actual.INDEX_NAME IS NULL THEN 'MISSING' ELSE 'OK' END AS status
FROM (
    SELECT 'facto_tokens' AS table_name, 'uq_facto_tokens_environment' AS index_name UNION ALL
    SELECT 'facto_inbox_documents', 'uq_inbox_document_env' UNION ALL
    SELECT 'billing_collections', 'uq_collection_document_direction' UNION ALL
    SELECT 'billing_issue_logs', 'uq_billing_issue_idempotency' UNION ALL
    SELECT 'crm_customers', 'unique_customer_tax_id' UNION ALL
    SELECT 'finance_centers', 'uq_finance_center_code'
) expected
LEFT JOIN (
    SELECT DISTINCT TABLE_NAME, INDEX_NAME
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
) actual
  ON actual.TABLE_NAME = expected.table_name
 AND actual.INDEX_NAME = expected.index_name
ORDER BY expected.table_name, expected.index_name;

SELECT '03_document_type_gate' AS section,
       direction,
       document_key,
       document_label,
       enabled_sandbox,
       enabled_production,
       is_active
FROM billing_document_types
ORDER BY direction, document_key;

SELECT '04_document_type_gate_summary' AS section,
       SUM(direction = 'issued' AND is_active = 1 AND enabled_production = 1) AS issued_types_enabled_production,
       SUM(direction = 'received' AND is_active = 1 AND enabled_production = 1) AS received_types_enabled_production,
       SUM(direction = 'issued' AND is_active = 1 AND enabled_sandbox = 1) AS issued_types_enabled_sandbox
FROM billing_document_types;

SELECT '05_facto_token_metadata' AS section,
       environment,
       COUNT(*) AS token_rows,
       SUM(expires_at > NOW()) AS currently_valid_rows,
       MIN(expires_at) AS earliest_expiry,
       MAX(expires_at) AS latest_expiry
FROM facto_tokens
GROUP BY environment
ORDER BY environment;

SELECT '06_production_data_inventory' AS section,
       (SELECT COUNT(*) FROM facto_documents WHERE environment = 'production') AS issued_documents,
       (SELECT COUNT(*) FROM facto_inbox_documents WHERE environment = 'production') AS inbox_documents,
       (SELECT COUNT(*) FROM billing_collections WHERE environment = 'production' AND direction = 'receivable') AS receivables,
       (SELECT COUNT(*) FROM billing_collections WHERE environment = 'production' AND direction = 'payable') AS payables,
       (SELECT COUNT(*) FROM billing_payments WHERE environment = 'production' AND direction = 'received') AS collections_received,
       (SELECT COUNT(*) FROM billing_payments WHERE environment = 'production' AND direction = 'paid') AS supplier_payments;

SELECT '07_pending_remote_operations' AS section,
       'billing_issue_logs' AS source_table,
       COUNT(*) AS pending_count
FROM billing_issue_logs
WHERE environment = 'production' AND status IN ('sent', 'pending_reconcile')
UNION ALL
SELECT '07_pending_remote_operations', 'billing_manual_operations', COUNT(*)
FROM billing_manual_operations
WHERE environment = 'production' AND status IN ('issuing', 'pending_local_commit', 'remote_uncertain')
UNION ALL
SELECT '07_pending_remote_operations', 'billing_export_operations', COUNT(*)
FROM billing_export_operations
WHERE environment = 'production' AND status IN ('issuing', 'pending_local_commit', 'remote_uncertain')
UNION ALL
SELECT '07_pending_remote_operations', 'billing_tax_operations', COUNT(*)
FROM billing_tax_operations
WHERE environment = 'production' AND status IN ('issuing', 'pending_local_commit', 'remote_uncertain')
UNION ALL
SELECT '07_pending_remote_operations', 'billing_complementary_operations', COUNT(*)
FROM billing_complementary_operations
WHERE environment = 'production' AND status IN ('sent', 'pending_reconcile')
UNION ALL
SELECT '07_pending_remote_operations', 'billing_dispatch_guides', COUNT(*)
FROM billing_dispatch_guides
WHERE environment = 'production' AND status IN ('issuing', 'pending_local_commit', 'remote_uncertain');

SELECT '08_collection_reconciliation' AS section,
       direction,
       COUNT(*) AS collection_count,
       ROUND(SUM(total_amount), 2) AS total_amount,
       ROUND(SUM(paid_amount), 2) AS paid_amount,
       ROUND(SUM(balance_amount), 2) AS balance_amount,
       ROUND(SUM(total_amount - paid_amount - balance_amount), 2) AS arithmetic_difference,
       SUM(ABS(balance_amount - (total_amount - paid_amount)) > 0.01) AS inconsistent_rows,
       SUM(total_amount < 0 OR paid_amount < 0 OR balance_amount < -0.01) AS negative_rows
FROM billing_collections
WHERE environment = 'production'
GROUP BY direction
ORDER BY direction;

SELECT '09_collection_status_distribution' AS section,
       direction,
       collection_status,
       COUNT(*) AS rows_count,
       ROUND(SUM(total_amount), 2) AS total_amount,
       ROUND(SUM(paid_amount), 2) AS paid_amount,
       ROUND(SUM(balance_amount), 2) AS balance_amount
FROM billing_collections
WHERE environment = 'production'
GROUP BY direction, collection_status
ORDER BY direction, collection_status;

SELECT '10_duplicate_payable_groups' AS section,
       COUNT(*) AS duplicate_groups
FROM (
    SELECT inbox_document_id
    FROM billing_collections
    WHERE environment = 'production'
      AND direction = 'payable'
      AND inbox_document_id IS NOT NULL
      AND inbox_document_id <> ''
    GROUP BY inbox_document_id
    HAVING COUNT(*) > 1
) duplicates;

SELECT '11_duplicate_receivable_groups' AS section,
       COUNT(*) AS duplicate_groups
FROM (
    SELECT facto_document_id
    FROM billing_collections
    WHERE environment = 'production'
      AND direction = 'receivable'
      AND facto_document_id IS NOT NULL
      AND facto_document_id <> ''
    GROUP BY facto_document_id
    HAVING COUNT(*) > 1
) duplicates;

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

SELECT '13_orphan_references' AS section,
       (SELECT COUNT(*)
          FROM billing_payments p
          LEFT JOIN billing_collections c ON c.id = p.collection_id
         WHERE p.environment = 'production'
           AND p.collection_id IS NOT NULL
           AND c.id IS NULL) AS orphan_payments,
       (SELECT COUNT(*)
          FROM billing_collections c
          LEFT JOIN crm_customers cp ON cp.id = c.customer_id
         WHERE c.environment = 'production'
           AND c.customer_id IS NOT NULL
           AND cp.id IS NULL) AS orphan_counterparties,
       (SELECT COUNT(*)
          FROM billing_collections c
          LEFT JOIN finance_centers fc ON fc.id = c.finance_center_id
         WHERE c.environment = 'production'
           AND c.finance_center_id IS NOT NULL
           AND fc.id IS NULL) AS orphan_finance_centers;

SELECT '14_open_balance_coverage' AS section,
       direction,
       COUNT(*) AS open_rows,
       SUM(customer_id IS NULL) AS without_counterparty,
       SUM(finance_center_id IS NULL) AS without_finance_center,
       ROUND(SUM(balance_amount), 2) AS open_balance
FROM billing_collections
WHERE environment = 'production'
  AND balance_amount > 0.01
  AND collection_status NOT IN ('pagado', 'anulado', 'rechazado')
GROUP BY direction
ORDER BY direction;

SELECT '15_supplier_resolution' AS section,
       supplier_resolution_status,
       COUNT(*) AS rows_count,
       ROUND(SUM(total_amount), 2) AS total_amount
FROM facto_inbox_documents
WHERE environment = 'production'
GROUP BY supplier_resolution_status
ORDER BY supplier_resolution_status;

SELECT '16_inbox_financial_coverage' AS section,
       COUNT(*) AS production_inbox_rows,
       SUM(supplier_customer_id IS NULL) AS without_supplier,
       SUM(finance_center_id IS NULL) AS without_cost_center,
       SUM(payment_due_date IS NULL AND payment_status NOT IN ('pagado', 'rechazado', 'anulado')) AS without_due_date
FROM facto_inbox_documents
WHERE environment = 'production';

SELECT '17_issued_financial_coverage' AS section,
       COUNT(*) AS production_issued_rows,
       SUM(crm_customer_id IS NULL) AS without_customer,
       SUM(finance_center_id IS NULL) AS without_income_center,
       SUM(payment_due_date IS NULL AND payment_status NOT IN ('pagado', 'anulado', 'no_facturado')) AS without_due_date
FROM facto_documents
WHERE environment = 'production'
  AND received_issued_flag = 1
  AND document_type_taxbureau IN (33, 34, 39, 46, 56, 61, 110, 111, 112);

SELECT '18_normalized_tax_id_duplicates' AS section,
       COUNT(*) AS duplicate_groups
FROM (
    SELECT UPPER(REPLACE(REPLACE(REPLACE(TRIM(customer_tax_id), '.', ''), '-', ''), ' ', '')) AS normalized_tax_id
    FROM crm_customers
    WHERE deleted_at IS NULL
      AND customer_tax_id IS NOT NULL
      AND TRIM(customer_tax_id) <> ''
    GROUP BY normalized_tax_id
    HAVING COUNT(*) > 1
) duplicates;

SELECT '19_finance_centers' AS section,
       center_type,
       is_active,
       is_default,
       COUNT(*) AS centers_count
FROM finance_centers
GROUP BY center_type, is_active, is_default
ORDER BY center_type, is_active DESC, is_default DESC;

SELECT '20_open_reconciliation_issues' AS section,
       severity,
       direction,
       issue_type,
       COUNT(*) AS issue_count,
       ROUND(SUM(COALESCE(actual_amount, 0) - COALESCE(expected_amount, 0)), 2) AS aggregate_difference
FROM billing_reconciliation_issues
WHERE status = 'open'
GROUP BY severity, direction, issue_type
ORDER BY FIELD(severity, 'critical', 'warning', 'info'), direction, issue_type;

SELECT '21_production_permissions' AS section,
       rp.role,
       rp.permission_key
FROM role_permissions rp
WHERE rp.permission_key IN (
    'billing.issue.production',
    'billing.issue.invoice',
    'billing.admin'
)
ORDER BY rp.permission_key, rp.role;

SELECT '22_active_users_by_role' AS section,
       role,
       COUNT(*) AS active_users
FROM users
WHERE status = 'active'
GROUP BY role
ORDER BY role;

SELECT '23_sync_log_health' AS section,
       status,
       COUNT(*) AS rows_count,
       MAX(synced_at) AS latest_sync
FROM facto_sync_logs
GROUP BY status
ORDER BY status;

SELECT '24_readiness_gate' AS section,
       CASE
           WHEN (SELECT COUNT(*) FROM billing_document_types WHERE direction = 'issued' AND is_active = 1 AND enabled_production = 1) > 0
               THEN 'BLOCKED: production document types already enabled'
           WHEN (SELECT COUNT(*) FROM billing_issue_logs WHERE environment = 'production' AND status IN ('sent', 'pending_reconcile')) > 0
               THEN 'BLOCKED: pending issue logs'
           WHEN (SELECT COUNT(*) FROM billing_collections WHERE environment = 'production' AND ABS(balance_amount - (total_amount - paid_amount)) > 0.01) > 0
               THEN 'BLOCKED: collection arithmetic mismatch'
           WHEN (SELECT COUNT(*) FROM billing_reconciliation_issues WHERE status = 'open' AND severity = 'critical') > 0
               THEN 'BLOCKED: critical reconciliation issues'
           WHEN (SELECT COUNT(*) FROM billing_collections WHERE environment = 'production' AND balance_amount > 0.01 AND collection_status NOT IN ('pagado', 'anulado', 'rechazado') AND customer_id IS NULL) > 0
               THEN 'BLOCKED: open balances without counterparty'
           ELSE 'READY FOR CONTROLLED READ-ONLY E2E; NOT AUTHORIZED TO ISSUE'
       END AS readiness_status;
