<?php
require_once __DIR__ . '/../core/config.php';
require_once __DIR__ . '/../core/database.php';
require_once __DIR__ . '/../services/billing/BillingRuntimeGate.php';
require_once __DIR__ . '/../services/billing/BillingPayablesService.php';

date_default_timezone_set('UTC');
$pdo = db();
$dbName = (string)$pdo->query('SELECT DATABASE()')->fetchColumn();
$environment = 'production';
$failures = array();
$created = 0;
$existing = 0;

$pdo->exec("CREATE TABLE IF NOT EXISTS billing_cxp_catchup_log_v6_19_1 (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    environment VARCHAR(20) NOT NULL,
    collection_id BIGINT UNSIGNED NOT NULL,
    local_inbox_id BIGINT UNSIGNED NOT NULL,
    inbox_document_id VARCHAR(100) NOT NULL,
    document_id VARCHAR(100) DEFAULT NULL,
    document_number VARCHAR(100) DEFAULT NULL,
    total_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_cxp_catchup_collection_v6191 (collection_id),
    UNIQUE KEY uq_cxp_catchup_inbox_v6191 (environment,inbox_document_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

function v6191_approved_condition_sql($alias = 'd') {
    return "(
      LOWER(TRIM(CAST(COALESCE(" . $alias . ".icylab_reception_status,'') AS CHAR))) IN ('approved','approved_with_observations')
      OR LOWER(TRIM(CAST(COALESCE(" . $alias . ".document_approval_status,'') AS CHAR))) IN ('1','accepted','approved','approved_with_observations')
      OR TRIM(CAST(COALESCE(" . $alias . ".inbox_document_status,'') AS CHAR)) IN ('2','3')
    )";
}

$gate = billing_runtime_gate_state($environment);
echo "ICYLAB BILLING APPROVED INVOICE -> CxP CATCH-UP v6.19.1\n";
echo 'Generated at UTC: ' . gmdate('Y-m-d H:i:s') . "\n";
echo 'DB name: ' . $dbName . "\n";
echo 'production read: ' . ($gate['read_enabled'] ? 'ALLOWED' : 'BLOCKED') . "\n";
echo 'production finance: ' . ($gate['finance_enabled'] ? 'ALLOWED' : 'BLOCKED') . "\n";
echo 'production issue: ' . ($gate['issue_enabled'] ? 'ALLOWED' : 'BLOCKED') . "\n\n";

if ($dbName !== 'icylab_app') {
    echo "RESULT: FAIL\n- Base de datos incorrecta.\n";
    exit(1);
}
if (!$gate['read_enabled'] || !$gate['finance_enabled']) {
    echo "RESULT: FAIL\n- READ y FINANCE deben estar activos durante este catch-up.\n";
    exit(1);
}
if ($gate['issue_enabled']) {
    echo "RESULT: FAIL\n- ISSUE debe permanecer bloqueado. No se ejecutó el catch-up.\n";
    exit(1);
}

$approvedSql = v6191_approved_condition_sql('d');
$sql = "SELECT d.id,d.inbox_document_id,d.document_id,d.document_number,d.issuer_name,d.total_amount
 FROM facto_inbox_documents d
 LEFT JOIN billing_collections c
   ON BINARY c.environment=BINARY d.environment
  AND BINARY c.direction=BINARY 'payable'
  AND BINARY c.inbox_document_id=BINARY d.inbox_document_id
 WHERE BINARY d.environment=BINARY ?
   AND CAST(COALESCE(d.document_type_id,0) AS UNSIGNED) IN (9,33)
   AND d.total_amount > 0
   AND " . $approvedSql . "
   AND c.id IS NULL
 ORDER BY d.document_date,d.id
 LIMIT 2000";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($environment));
$candidates = $stmt->fetchAll(PDO::FETCH_ASSOC);
$before = count($candidates);
$service = new BillingPayablesService($pdo);

foreach ($candidates as $candidate) {
    try {
        $collection = $service->ensurePayableCollection((string)$candidate['inbox_document_id'], $environment);
        if ($collection && !empty($collection['id'])) {
            $created++;
            $log = $pdo->prepare("INSERT IGNORE INTO billing_cxp_catchup_log_v6_19_1
                (environment,collection_id,local_inbox_id,inbox_document_id,document_id,document_number,total_amount)
                VALUES (?,?,?,?,?,?,?)");
            $log->execute(array(
                $environment,
                (int)$collection['id'],
                (int)$candidate['id'],
                (string)$candidate['inbox_document_id'],
                $candidate['document_id'] !== null ? (string)$candidate['document_id'] : null,
                $candidate['document_number'] !== null ? (string)$candidate['document_number'] : null,
                (float)$candidate['total_amount']
            ));
        } else {
            $failures[] = 'Inbox ' . $candidate['inbox_document_id'] . ' folio ' . $candidate['document_number'] . ': no devolvió CxP.';
        }
    } catch (Throwable $e) {
        $failures[] = 'Inbox ' . $candidate['inbox_document_id'] . ' folio ' . $candidate['document_number'] . ': ' . $e->getMessage();
    }
}

$remainingStmt = $pdo->prepare("SELECT COUNT(*) FROM facto_inbox_documents d
 LEFT JOIN billing_collections c
   ON BINARY c.environment=BINARY d.environment
  AND BINARY c.direction=BINARY 'payable'
  AND BINARY c.inbox_document_id=BINARY d.inbox_document_id
 WHERE BINARY d.environment=BINARY ?
   AND CAST(COALESCE(d.document_type_id,0) AS UNSIGNED) IN (9,33)
   AND d.total_amount > 0
   AND " . $approvedSql . "
   AND c.id IS NULL");
$remainingStmt->execute(array($environment));
$remaining = (int)$remainingStmt->fetchColumn();

$selectable = (int)$pdo->query("SELECT COUNT(*) FROM billing_collections c
 WHERE BINARY c.environment=BINARY 'production' AND BINARY c.direction=BINARY 'payable'
   AND c.balance_amount > 0.5 AND c.collection_status NOT IN ('pagado','rechazado','anulado')")->fetchColumn();
$duplicates = (int)$pdo->query("SELECT COUNT(*) FROM (
 SELECT environment,inbox_document_id,COUNT(*) c
 FROM billing_collections
 WHERE BINARY environment=BINARY 'production' AND BINARY direction=BINARY 'payable'
 GROUP BY environment,inbox_document_id HAVING COUNT(*)>1
) x")->fetchColumn();

$unapprovedCxP = (int)$pdo->query("SELECT COUNT(*) FROM billing_collections c
 INNER JOIN facto_inbox_documents d
   ON BINARY d.environment=BINARY c.environment
  AND BINARY d.inbox_document_id=BINARY c.inbox_document_id
 WHERE BINARY c.environment=BINARY 'production'
   AND BINARY c.direction=BINARY 'payable'
   AND NOT " . $approvedSql)->fetchColumn();

echo 'candidates before: ' . $before . "\n";
echo 'created: ' . $created . "\n";
echo 'remaining approved invoices without CxP: ' . $remaining . "\n";
echo 'selectable open CxP: ' . $selectable . "\n";
echo 'duplicate CxP groups: ' . $duplicates . "\n";
echo 'unapproved invoices with CxP: ' . $unapprovedCxP . "\n";
if ($failures) {
    echo "\nERRORS (max 50)\n";
    foreach (array_slice($failures, 0, 50) as $failure) echo '- ' . $failure . "\n";
}

$ok = $remaining === 0 && $duplicates === 0 && count($failures) === 0 && $selectable > 0;
echo "\nRESULT: " . ($ok ? 'PASS' : 'FAIL') . "\n";
if ($ok) {
    echo "PASS: APPROVED RECEIVED INVOICES HAVE CxP; INDIVIDUAL AND BULK SELECTION ARE ENABLED FOR OPEN BALANCES\n";
}
exit($ok ? 0 : 1);
