class Glsr_Fix_Assigned_Posts
{
    protected int $current;
    protected int $fixed;
    protected int $limit;

    public function __construct()
    {
        $this->current = 0;
        $this->fixed = 0;
        $this->limit = 100;
    }

    public function run()
    {
        if (!function_exists('glsr')) {
            return;
        }
        wp_raise_memory_limit('admin');
        glsr('Database')->beginTransaction('assigned_posts');
        while (true) {
            $results = $this->unassigned();
            if (empty($results)) {
                break;
            }
            $values = $this->prepareValues($results);
            if (!empty($values)) {
                glsr('Database')->insertBulk('assigned_posts', $values, [
                    'rating_id',
                    'post_id',
                    'is_published',
                ]);
                $this->fixed += count($values);
            }
        }
        glsr('Database')->finishTransaction('assigned_posts');
        glsr_log()->info("{$this->fixed} review assignments fixed!");
    }

    public function prepareValues(array $results): array
    {
        $values = [];
        foreach ($results as &$result) {
            $submitted = maybe_unserialize($result['submitted']);
            $postIds = \GeminiLabs\SiteReviews\Helpers\Arr::getAs('array', $submitted, 'assigned_posts');
            $postIds = glsr('Modules\Sanitizer')->sanitizePostIds($postIds);
            foreach ($postIds as $postId) {
                $isPublished = 'publish' === get_post_status($postId);
                $values[] = [
                    'rating_id' => (int) $result['rating_id'],
                    'post_id' => (int) $postId,
                    'is_published' => (int) $isPublished,
                ];
            }
        }
        $last = end($results);
        $this->current = $last['rating_id'] ?? 0;
        return $values;
    }

    public function unassigned(): array
    {
        $sql = "
            SELECT r.ID AS rating_id, pm.meta_value AS submitted
            FROM table|ratings AS r
            INNER JOIN table|postmeta AS pm ON (pm.post_id = r.review_id)
            WHERE 1=1
            AND r.ID > %s
            AND r.ID NOT IN (
                SELECT apt.rating_id
                FROM table|assigned_posts apt
            )
            AND pm.meta_key = '_submitted'
            ORDER BY r.ID
            LIMIT 0, %d
        ";
        return glsr('Database')->dbGetResults(
            glsr('Database\Query')->sql($sql, $this->current, $this->limit),
            ARRAY_A
        );
    }
}

add_action('admin_init', function () {
    $command = new \Glsr_Fix_Assigned_Posts();
    $command->run();
});