<?php
// Dedupe lookup_items for specific groups by code/value, keeping earliest row.

require __DIR__ . '/../../vendor/autoload.php';
require __DIR__ . '/../../app/bootstrap.php';

$db = \App\Database\Connection::getInstance();
$pdo = $db->getPdo();

$groupCodes = ['payment_methods', 'payment_destination_banks', 'field_rules', 'allowed_services'];

$pdo->beginTransaction();
try {
    $groupStmt = $pdo->prepare("SELECT id, code FROM lookup_groups WHERE code IN (" . implode(',', array_fill(0, count($groupCodes), '?')) . ")");
    $groupStmt->execute($groupCodes);
    $groups = $groupStmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

    $totalRemoved = 0;
    foreach ($groups as $group) {
        $groupId = (int)$group['id'];
        $items = $pdo->prepare("SELECT id, code, value, meta_json FROM lookup_items WHERE group_id = ? ORDER BY id ASC");
        $items->execute([$groupId]);
        $rows = $items->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        $seen = [];
        foreach ($rows as $row) {
            $code = $row['code'] ?? '';
            $value = $row['value'] ?? '';
            $key = ($code !== '' ? 'code:' . $code : 'value:' . $value);
            if ($key === 'value:' && $value === '') {
                continue;
            }
            if (!isset($seen[$key])) {
                $seen[$key] = $row;
                continue;
            }

            $keepId = (int)$seen[$key]['id'];
            $dropId = (int)$row['id'];

            // Merge meta_json if keep has none and drop has value
            $keepMeta = $seen[$key]['meta_json'] ?? '';
            $dropMeta = $row['meta_json'] ?? '';
            if (($keepMeta === null || $keepMeta === '') && $dropMeta) {
                $update = $pdo->prepare("UPDATE lookup_items SET meta_json = ? WHERE id = ?");
                $update->execute([$dropMeta, $keepId]);
                $seen[$key]['meta_json'] = $dropMeta;
            }

            $delete = $pdo->prepare("DELETE FROM lookup_items WHERE id = ?");
            $delete->execute([$dropId]);
            $totalRemoved++;
        }
    }

    $pdo->commit();
    echo "Dedupe complete. Removed rows: {$totalRemoved}\n";
} catch (\Throwable $e) {
    $pdo->rollBack();
    echo "Dedupe failed: " . $e->getMessage() . "\n";
    exit(1);
}
