<?php

declare(strict_types=1);

return static function (string $tableSuffix): callable {
    return static function (PDO $pdo) use ($tableSuffix): void {
        $pdo->exec('CREATE TABLE IF NOT EXISTS site_page_roles (
            role_key VARCHAR(32) PRIMARY KEY,
            page_id VARCHAR(32) NOT NULL,
            assigned_at VARCHAR(40) NOT NULL,
            assigned_by_user_id VARCHAR(80) NOT NULL DEFAULT \'\'
        )' . $tableSuffix);

        try {
            $pdo->exec('CREATE INDEX idx_site_page_roles_page ON site_page_roles(page_id)');
        } catch (PDOException $exception) {
            $message = strtolower($exception->getMessage());
            if (!str_contains($message, 'already exists') && !str_contains($message, 'duplicate key name')) {
                throw $exception;
            }
        }

        $now = gmdate('Y-m-d\\TH:i:s\\Z');
        $statement = $pdo->prepare(
            "INSERT INTO site_page_roles (role_key, page_id, assigned_at, assigned_by_user_id)
             SELECT 'home', pages.id, :assigned_at, ''
             FROM pages
             WHERE pages.path = '/'
               AND pages.publication_state = 'published'
               AND pages.current_published_version_id IS NOT NULL
               AND TRIM(pages.current_published_version_id) <> ''
               AND NOT EXISTS (SELECT 1 FROM site_page_roles WHERE role_key = 'home')
             ORDER BY pages.updated_at DESC, pages.id ASC
             LIMIT 1"
        );
        $statement->execute([':assigned_at' => $now]);
    };
};
