musadaq-saas/scripts/migrate_queue_hardening.php

<?php
/**
 * Migration: Queue Hardening (2026-07-30)
 *
 * Brings the live schema in line with the fixed queue/processing code:
 *   - invoice_processing_queue.claimed_at   -> detects workers that died mid-item
 *   - invoice_processing_queue.max_attempts -> retry ceiling (was referenced, never existed on old installs)
 *   - invoice_processing_queue.image_order  -> per-image ordering
 *   - invoice_processing_queue.company_id   -> written by batches/upload_image.php
 *   - invoice_batches.failed_images         -> failure accounting for completion
 *   - invoice_batches.updated_at            -> touched on every upload
 *   - invoice_batches.status                -> adds 'failed' to the enum
 *   - invoices.batch_id                     -> links an invoice back to its batch
 *   - ai_usage_log.tenant_id                -> per-office AI cost attribution
 *
 * Idempotent: safe to run repeatedly.
 *
 * Usage:  php scripts/migrate_queue_hardening.php
 */

declare(strict_types=1);

if (PHP_SAPI !== 'cli') {
    http_response_code(404);
    exit('Not Found');
}

require_once __DIR__ . '/../app/bootstrap/init.php';

use App\Core\Database;

$db = Database::getInstance();

function columnExists(\PDO $db, string $table, string $column): bool
{
    $stmt = $db->prepare("
        SELECT COUNT(*) FROM information_schema.columns
        WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?
    ");
    $stmt->execute([$table, $column]);
    return (int)$stmt->fetchColumn() > 0;
}

function tableExists(\PDO $db, string $table): bool
{
    $stmt = $db->prepare("
        SELECT COUNT(*) FROM information_schema.tables
        WHERE table_schema = DATABASE() AND table_name = ?
    ");
    $stmt->execute([$table]);
    return (int)$stmt->fetchColumn() > 0;
}

function indexExists(\PDO $db, string $table, string $index): bool
{
    $stmt = $db->prepare("
        SELECT COUNT(*) FROM information_schema.statistics
        WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ?
    ");
    $stmt->execute([$table, $index]);
    return (int)$stmt->fetchColumn() > 0;
}

function addColumn(\PDO $db, string $table, string $column, string $definition): void
{
    if (!tableExists($db, $table)) {
        echo "  - skip {$table}.{$column} (table missing)\n";
        return;
    }
    if (columnExists($db, $table, $column)) {
        echo "  = {$table}.{$column} already present\n";
        return;
    }
    $db->exec("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
    echo "  + {$table}.{$column} added\n";
}

function addIndex(\PDO $db, string $table, string $index, string $columns): void
{
    if (!tableExists($db, $table)) return;
    if (indexExists($db, $table, $index)) {
        echo "  = index {$table}.{$index} already present\n";
        return;
    }
    $db->exec("CREATE INDEX `{$index}` ON `{$table}` ({$columns})");
    echo "  + index {$table}.{$index} added\n";
}

echo "=== Queue hardening migration ===\n";

try {
    echo "\n[1] invoice_processing_queue\n";
    addColumn($db, 'invoice_processing_queue', 'company_id',   "CHAR(36) NULL AFTER tenant_id");
    addColumn($db, 'invoice_processing_queue', 'image_order',  "INT NOT NULL DEFAULT 0 AFTER image_path");
    addColumn($db, 'invoice_processing_queue', 'attempts',     "INT NOT NULL DEFAULT 0");
    addColumn($db, 'invoice_processing_queue', 'max_attempts', "INT NOT NULL DEFAULT 3 AFTER attempts");
    addColumn($db, 'invoice_processing_queue', 'claimed_at',   "DATETIME NULL AFTER created_at");
    addIndex($db, 'invoice_processing_queue', 'idx_batch', 'batch_id');
    addIndex($db, 'invoice_processing_queue', 'idx_claim', 'status, attempts');

    echo "\n[2] invoice_batches\n";
    addColumn($db, 'invoice_batches', 'failed_images', "INT NOT NULL DEFAULT 0 AFTER processed_images");
    addColumn($db, 'invoice_batches', 'updated_at', "DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
    if (tableExists($db, 'invoice_batches')) {
        // Widen the status enum so a fully-failed batch has a terminal state.
        $db->exec("
            ALTER TABLE invoice_batches
            MODIFY COLUMN status ENUM('uploading','processing','done','partial_fail','failed')
            NOT NULL DEFAULT 'uploading'
        ");
        echo "  ~ invoice_batches.status enum widened (adds 'failed')\n";
    }

    echo "\n[3] invoices\n";
    addColumn($db, 'invoices', 'batch_id', "CHAR(36) NULL AFTER company_id");
    addIndex($db, 'invoices', 'idx_batch_id', 'batch_id');

    echo "\n[4] ai_usage_log\n";
    addColumn($db, 'ai_usage_log', 'tenant_id', "CHAR(36) NULL AFTER id");
    addIndex($db, 'ai_usage_log', 'idx_tenant', 'tenant_id');

    echo "\n[5] user_devices (Live Activity + per-device refresh tokens)\n";
    addColumn($db, 'user_devices', 'live_activity_token', "TEXT NULL AFTER push_token");
    addColumn($db, 'user_devices', 'refresh_token_hash', "CHAR(64) NULL AFTER device_secret");
    addColumn($db, 'user_devices', 'refresh_expires_at', "DATETIME NULL AFTER refresh_token_hash");
    addIndex($db, 'user_devices', 'idx_refresh_token', 'refresh_token_hash');

    echo "\n[6] backfill invoices.batch_id from the queue\n";
    if (tableExists($db, 'invoices') && tableExists($db, 'invoice_processing_queue')) {
        $backfilled = $db->exec("
            UPDATE invoices i
            JOIN invoice_processing_queue q ON q.invoice_id = i.id
            SET i.batch_id = q.batch_id
            WHERE i.batch_id IS NULL AND q.batch_id IS NOT NULL
        ");
        echo "  ~ backfilled {$backfilled} invoice(s)\n";
    }

    echo "\n[7] release rows stuck in 'processing' from the pre-fix code\n";
    if (tableExists($db, 'invoice_processing_queue')) {
        $released = $db->exec("
            UPDATE invoice_processing_queue
            SET status = 'pending', claimed_at = NULL, attempts = 0,
                error_message = 'Released by queue-hardening migration'
            WHERE status = 'processing'
        ");
        echo "  ~ released {$released} row(s)\n";
    }

    echo "\n[8] recount failed_images from the queue (was never populated)\n";
    if (tableExists($db, 'invoice_batches')) {
        $db->exec("
            UPDATE invoice_batches b
            SET b.failed_images = (
                SELECT COUNT(*) FROM invoice_processing_queue q
                WHERE q.batch_id = b.id AND q.status = 'failed'
            )
        ");
        echo "  ~ failed_images recounted\n";

        $db->exec("
            UPDATE invoice_batches b
            SET b.processed_images = (
                SELECT COUNT(*) FROM invoice_processing_queue q
                WHERE q.batch_id = b.id AND q.status = 'done'
            )
        ");
        echo "  ~ processed_images recounted\n";
    }

    echo "\n=== Migration completed successfully ===\n";

} catch (\Throwable $e) {
    echo "\n!!! MIGRATION FAILED: " . $e->getMessage() . "\n";
    exit(1);
}