<?php

declare(strict_types=1);

return static function (string $tableSuffix): callable {
    return static function (PDO $pdo): void {
        $tableExists = static function (string $table) use ($pdo): bool {
            try {
                $pdo->query('SELECT 1 FROM ' . $table . ' LIMIT 1');
                return true;
            } catch (Throwable) {
                return false;
            }
        };
        $hasColumn = static function (string $table, string $column) use ($pdo): bool {
            $driver = (string) $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
            if ($driver === 'sqlite') {
                $statement = $pdo->query('PRAGMA table_info(' . $table . ')');
                foreach (($statement ? $statement->fetchAll(PDO::FETCH_ASSOC) : []) as $row) {
                    if (strtolower((string) ($row['name'] ?? '')) === strtolower($column)) {
                        return true;
                    }
                }
                return false;
            }

            $statement = $pdo->query('SHOW COLUMNS FROM ' . $table);
            foreach (($statement ? $statement->fetchAll(PDO::FETCH_ASSOC) : []) as $row) {
                if (strtolower((string) ($row['Field'] ?? '')) === strtolower($column)) {
                    return true;
                }
            }
            return false;
        };
        $addColumn = static function (string $table, string $column, string $definition) use ($pdo, $tableExists, $hasColumn): void {
            if (!$tableExists($table) || $hasColumn($table, $column)) {
                return;
            }
            $pdo->exec(sprintf('ALTER TABLE %s ADD COLUMN %s %s', $table, $column, $definition));
        };

        $addColumn('page_versions', 'canonical_override', 'TEXT DEFAULT NULL');
        $addColumn('page_versions', 'robots_override', 'TEXT DEFAULT NULL');
        $addColumn('page_versions', 'og_title', 'TEXT DEFAULT NULL');
        $addColumn('page_versions', 'og_description', 'TEXT DEFAULT NULL');
        $addColumn('page_versions', 'og_image', 'TEXT DEFAULT NULL');
        $addColumn('page_versions', 'twitter_title', 'TEXT DEFAULT NULL');
        $addColumn('page_versions', 'twitter_description', 'TEXT DEFAULT NULL');
        $addColumn('page_versions', 'twitter_image', 'TEXT DEFAULT NULL');
        $addColumn('page_versions', 'jsonld_mode', "VARCHAR(16) NOT NULL DEFAULT 'auto'");
        $addColumn('page_versions', 'jsonld_type', "VARCHAR(32) NOT NULL DEFAULT 'Article'");
        $addColumn('page_versions', 'jsonld_custom', 'TEXT DEFAULT NULL');

        $addColumn('site_settings', 'seo_title_suffix', 'VARCHAR(255) NOT NULL DEFAULT \'\'' );
        $addColumn('site_settings', 'seo_default_description', 'TEXT DEFAULT NULL');
        $addColumn('site_settings', 'seo_default_og_image', 'TEXT DEFAULT NULL');
        $addColumn('site_settings', 'seo_twitter_card_type', "VARCHAR(32) NOT NULL DEFAULT 'summary_large_image'");
        $addColumn('site_settings', 'seo_org_name', 'VARCHAR(255) NOT NULL DEFAULT \'\'' );
        $addColumn('site_settings', 'seo_org_url', 'TEXT DEFAULT NULL');
        $addColumn('site_settings', 'seo_org_logo', 'TEXT DEFAULT NULL');
        $addColumn('site_settings', 'seo_social_profiles_json', 'TEXT DEFAULT NULL');
        $addColumn('site_settings', 'seo_local_business_enabled', 'INTEGER NOT NULL DEFAULT 0');
        $addColumn('site_settings', 'seo_local_business_name', 'VARCHAR(255) NOT NULL DEFAULT \'\'' );
        $addColumn('site_settings', 'seo_local_business_address', 'TEXT DEFAULT NULL');
        $addColumn('site_settings', 'seo_local_business_telephone', 'VARCHAR(80) NOT NULL DEFAULT \'\'' );
        $addColumn('site_settings', 'seo_local_business_hours', 'TEXT DEFAULT NULL');
        $addColumn('site_settings', 'seo_local_business_image', 'TEXT DEFAULT NULL');
        $addColumn('site_settings', 'seo_enable_canonical_output', 'INTEGER NOT NULL DEFAULT 1');
        $addColumn('site_settings', 'seo_enable_json_ld_output', 'INTEGER NOT NULL DEFAULT 1');
        $addColumn('site_settings', 'seo_enable_social_output', 'INTEGER NOT NULL DEFAULT 1');
        $addColumn('site_settings', 'seo_enable_robots_output', 'INTEGER NOT NULL DEFAULT 1');

        $addColumn('users', 'job_title', 'VARCHAR(255) NOT NULL DEFAULT \'\'' );
        $addColumn('users', 'same_as_urls_json', 'TEXT DEFAULT NULL');
    };
};
