<?php

namespace WPSN\Modules\SchemaValidator;

defined('ABSPATH') || exit;

final class SchemaValidatorInstaller
{
    private const DB_VERSION = 1;
    private const OPTION_NAME = 'wpsn_schema_validator_db_version';

    public static function maybeInstall(): void
    {
        $installed = (int) get_option(self::OPTION_NAME, 0);
        if ($installed >= self::DB_VERSION) {
            return;
        }

        self::install();
    }

    public static function install(): void
    {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset = $wpdb->get_charset_collate();

        $scans = $wpdb->prefix . 'wpsn_schema_scans';
        $issues = $wpdb->prefix . 'wpsn_schema_issues';

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $sqlScans = "CREATE TABLE {$scans} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            status varchar(16) NOT NULL DEFAULT 'running',
            initiated_by bigint(20) unsigned NULL,
            params longtext NULL,
            stats longtext NULL,
            started_at datetime NOT NULL,
            finished_at datetime NULL,
            PRIMARY KEY  (id),
            KEY status_started (status, started_at),
            KEY started_at (started_at)
        ) {$charset};";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $sqlIssues = "CREATE TABLE {$issues} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            scan_id bigint(20) unsigned NOT NULL,
            object_type varchar(16) NOT NULL,
            object_id bigint(20) unsigned NOT NULL DEFAULT 0,
            object_subtype varchar(64) NULL,
            object_title text NULL,
            object_url text NULL,
            issue_code varchar(64) NOT NULL,
            severity tinyint(3) unsigned NOT NULL DEFAULT 1,
            message text NULL,
            suggestion text NULL,
            details longtext NULL,
            detected_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY scan_obj (scan_id, object_type, object_id),
            KEY scan_issue (scan_id, issue_code),
            KEY scan_severity (scan_id, severity),
            KEY detected_at (detected_at)
        ) {$charset};";

        dbDelta($sqlScans);
        dbDelta($sqlIssues);

        update_option(self::OPTION_NAME, self::DB_VERSION);
    }
}
