Inventory/scripts/fix-prod-recreate-and-migrate.php

<?php

declare(strict_types=1);

/**
 * Full prod fix:
 * 1. Re-create missing CustomField definitions on ModelTypes (from orphaned CFs that still have values)
 * 2. Migrate orphaned CFValues to the newly created CFs
 * 3. Restore deleted values from audit logs
 * 4. Clean up orphaned CFs with no remaining values
 *
 * Usage: php scripts/fix-prod-recreate-and-migrate.php [--dry-run]
 */

require_once __DIR__.'/../vendor/autoload.php';

use Doctrine\DBAL\DriverManager;

$dryRun = in_array('--dry-run', $argv, true);

$conn = DriverManager::getConnection([
    'driver' => 'pdo_pgsql',
    'host' => 'localhost',
    'port' => 5432,
    'dbname' => 'inventory',
    'user' => 'ferme_user',
    'password' => 'fermerecette',
]);

echo $dryRun ? "=== DRY RUN MODE ===\n\n" : "=== LIVE MODE ===\n\n";

$createdCfCount = 0;
$migratedCount = 0;
$restoredCount = 0;
$deletedOrphanedCfv = 0;
$deletedOrphanedCf = 0;

$entityTypes = [
    ['label' => 'piece', 'entityTable' => 'pieces', 'cfvFk' => 'pieceid', 'modelTypeFk' => 'typepieceid', 'cfFk' => 'typepieceid'],
    ['label' => 'composant', 'entityTable' => 'composants', 'cfvFk' => 'composantid', 'modelTypeFk' => 'typecomposantid', 'cfFk' => 'typecomposantid'],
    ['label' => 'product', 'entityTable' => 'products', 'cfvFk' => 'productid', 'modelTypeFk' => 'typeproductid', 'cfFk' => 'typeproductid'],
];

// ============================================================
// PART 1: Re-create missing CF definitions on ModelTypes
// ============================================================
echo "--- PART 1: Re-create missing CF definitions ---\n\n";

foreach ($entityTypes as $et) {
    // Find distinct (ModelType, field name, type) from orphaned CFs that have values
    $missingDefs = $conn->fetchAllAssociative("
        SELECT e.{$et['modelTypeFk']} as model_type_id,
               mt.name as model_type_name,
               cf_orphan.name as field_name,
               MIN(cf_orphan.type) as field_type,
               BOOL_OR(COALESCE(cf_orphan.required, false)) as field_required,
               MIN(cf_orphan.options::text) as field_options,
               MIN(cf_orphan.defaultvalue) as field_default
        FROM custom_field_values cfv
        JOIN custom_fields cf_orphan ON cf_orphan.id = cfv.customfieldid
        JOIN {$et['entityTable']} e ON e.id = cfv.{$et['cfvFk']}
        JOIN model_types mt ON mt.id = e.{$et['modelTypeFk']}
        WHERE cfv.{$et['cfvFk']} IS NOT NULL
          AND cf_orphan.typecomposantid IS NULL
          AND cf_orphan.typepieceid IS NULL
          AND cf_orphan.typeproductid IS NULL
        GROUP BY e.{$et['modelTypeFk']}, mt.name, cf_orphan.name
        ORDER BY mt.name, cf_orphan.name
    ");

    foreach ($missingDefs as $def) {
        // Check if this CF already exists on the ModelType
        $exists = $conn->fetchOne(
            "SELECT COUNT(*) FROM custom_fields WHERE {$et['cfFk']} = ? AND name = ?",
            [$def['model_type_id'], $def['field_name']]
        );

        if ($exists > 0) {
            continue;
        }

        // Get next orderIndex
        $maxOrder = $conn->fetchOne(
            "SELECT COALESCE(MAX(orderindex), -1) FROM custom_fields WHERE {$et['cfFk']} = ?",
            [$def['model_type_id']]
        );
        $nextOrder = ((int) $maxOrder) + 1;

        // Generate CUID-like ID
        $newId = 'cl' . bin2hex(random_bytes(12));

        echo sprintf("  CREATE CF: ModelType '%s' (%s) + field '%s' (type=%s)\n",
            $def['model_type_name'], $et['label'], $def['field_name'], $def['field_type']);

        if (!$dryRun) {
            $options = $def['field_options'];
            if (null !== $options && 'null' === $options) {
                $options = null;
            }
            $required = !empty($def['field_required']) && 'f' !== $def['field_required'];
            $conn->executeStatement(
                "INSERT INTO custom_fields (id, name, type, required, options, defaultvalue, orderindex, {$et['cfFk']})
                 VALUES (?, ?, ?, ?::boolean, ?::json, ?, ?, ?)",
                [
                    $newId,
                    $def['field_name'],
                    $def['field_type'],
                    $required ? 'true' : 'false',
                    $options,
                    $def['field_default'],
                    $nextOrder,
                    $def['model_type_id'],
                ]
            );
        }
        ++$createdCfCount;
    }
}

echo sprintf("\n  Created %d CF definitions\n\n", $createdCfCount);

// ============================================================
// PART 2: Migrate orphaned CFValues to current CFs
// ============================================================
echo "--- PART 2: Migrate orphaned CFValues ---\n\n";

foreach ($entityTypes as $et) {
    $orphanedValues = $conn->fetchAllAssociative("
        SELECT cfv.id as cfv_id, cfv.value, cfv.{$et['cfvFk']} as entity_id,
               cf_old.name as field_name,
               e.name as entity_name, e.{$et['modelTypeFk']} as model_type_id
        FROM custom_field_values cfv
        JOIN custom_fields cf_old ON cf_old.id = cfv.customfieldid
        JOIN {$et['entityTable']} e ON e.id = cfv.{$et['cfvFk']}
        WHERE cfv.{$et['cfvFk']} IS NOT NULL
          AND cf_old.typecomposantid IS NULL
          AND cf_old.typepieceid IS NULL
          AND cf_old.typeproductid IS NULL
        ORDER BY e.name, cf_old.name
    ");

    $migrated = 0;
    $cleaned = 0;

    foreach ($orphanedValues as $ov) {
        if (!$ov['model_type_id']) {
            continue;
        }

        $currentCf = $conn->fetchAssociative(
            "SELECT id FROM custom_fields WHERE {$et['cfFk']} = ? AND name = ? LIMIT 1",
            [$ov['model_type_id'], $ov['field_name']]
        );

        if (!$currentCf) {
            continue;
        }

        $existingValue = $conn->fetchAssociative(
            "SELECT id, value FROM custom_field_values WHERE {$et['cfvFk']} = ? AND customfieldid = ?",
            [$ov['entity_id'], $currentCf['id']]
        );

        if ($existingValue) {
            if (('' === $existingValue['value'] || null === $existingValue['value']) && '' !== $ov['value'] && null !== $ov['value']) {
                if (!$dryRun) {
                    $conn->executeStatement('UPDATE custom_field_values SET value = ? WHERE id = ?', [$ov['value'], $existingValue['id']]);
                }
                ++$migrated;
            }
            if (!$dryRun) {
                $conn->executeStatement('DELETE FROM custom_field_values WHERE id = ?', [$ov['cfv_id']]);
            }
            ++$cleaned;
        } else {
            if (!$dryRun) {
                $conn->executeStatement('UPDATE custom_field_values SET customfieldid = ? WHERE id = ?', [$currentCf['id'], $ov['cfv_id']]);
            }
            ++$migrated;
        }
    }

    echo sprintf("  %ss: %d migrated, %d cleaned\n", $et['label'], $migrated, $cleaned);
    $migratedCount += $migrated;
    $deletedOrphanedCfv += $cleaned;
}

// ============================================================
// PART 3: Restore values from audit logs
// ============================================================
echo "\n--- PART 3: Restore values from audit logs ---\n\n";

$deletionLogs = $conn->fetchAllAssociative("
    SELECT al.entityid, al.entitytype, al.diff::text as diff
    FROM audit_logs al
    WHERE al.diff::text LIKE '%customField%'
      AND al.diff::text LIKE '%\"to\":null%'
    ORDER BY al.createdat DESC
");

foreach ($deletionLogs as $log) {
    $diff = json_decode($log['diff'], true);
    $cfvFk = match ($log['entitytype']) {
        'piece' => 'pieceid',
        'composant' => 'composantid',
        'product' => 'productid',
        default => null,
    };
    if (!$cfvFk) {
        continue;
    }

    foreach ($diff as $key => $change) {
        if (!str_starts_with($key, 'customField:')) {
            continue;
        }
        if (null !== $change['to'] || null === $change['from'] || '' === $change['from']) {
            continue;
        }

        $fieldName = substr($key, strlen('customField:'));
        $cfv = $conn->fetchAssociative(
            "SELECT cfv.id, cfv.value FROM custom_field_values cfv JOIN custom_fields cf ON cf.id = cfv.customfieldid WHERE cfv.{$cfvFk} = ? AND cf.name = ?",
            [$log['entityid'], $fieldName]
        );

        if (!$cfv || ('' !== $cfv['value'] && null !== $cfv['value'])) {
            continue;
        }

        echo sprintf("  RESTORE: %s %s field '%s' = '%s'\n", $log['entitytype'], $log['entityid'], $fieldName, $change['from']);
        if (!$dryRun) {
            $conn->executeStatement('UPDATE custom_field_values SET value = ? WHERE id = ?', [$change['from'], $cfv['id']]);
        }
        ++$restoredCount;
    }
}

echo sprintf("  Restored: %d\n", $restoredCount);

// ============================================================
// PART 4: Clean orphaned CFs with no values
// ============================================================
echo "\n--- PART 4: Clean orphaned CF definitions ---\n\n";

$orphanedCfs = $conn->fetchAllAssociative('
    SELECT id FROM custom_fields
    WHERE typecomposantid IS NULL AND typepieceid IS NULL AND typeproductid IS NULL
      AND NOT EXISTS (SELECT 1 FROM custom_field_values cfv WHERE cfv.customfieldid = id)
');

echo sprintf("  %d orphaned CFs to delete\n", count($orphanedCfs));
foreach ($orphanedCfs as $cf) {
    if (!$dryRun) {
        $conn->executeStatement('DELETE FROM custom_fields WHERE id = ?', [$cf['id']]);
    }
    ++$deletedOrphanedCf;
}

echo sprintf("\n=== SUMMARY ===\n");
echo sprintf("CF definitions re-created: %d\n", $createdCfCount);
echo sprintf("Values migrated: %d\n", $migratedCount);
echo sprintf("Values restored from audit: %d\n", $restoredCount);
echo sprintf("Orphaned CFValues cleaned: %d\n", $deletedOrphanedCfv);
echo sprintf("Orphaned CFs deleted: %d\n", $deletedOrphanedCf);
echo "=== DONE ===\n";