<?php

declare(strict_types=1);

namespace ShieldX\Database;

defined('ABSPATH') || exit;

// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema cleanup drops only SHIELD-X-owned tables; identifiers use %i when supported or strict whitelisted backticks.

use ShieldX\Support\WordPressPaths;

final class SchemaInstaller
{
    public const SCHEMA_VERSION = '9';

    /** @var object|null */
    private $wpdb;

    public function __construct($wpdb = null)
    {
        $this->wpdb = $wpdb;
    }

    public static function fromWordPress(): self
    {
        global $wpdb;

        return new self($wpdb);
    }

    public function install(): void
    {
        if (!$this->wpdb) {
            return;
        }

        if (!function_exists('dbDelta')) {
            $upgradeFile = WordPressPaths::adminIncludesPath('upgrade.php');
            if ($upgradeFile !== '' && is_readable($upgradeFile)) {
                require_once $upgradeFile;
            }
        }

        if (function_exists('dbDelta')) {
            foreach ($this->schemaSql() as $sql) {
                dbDelta($sql);
            }
        }

        update_option('shieldx_schema_version', self::SCHEMA_VERSION, false);
        if (!function_exists('get_option') || (string) get_option('shieldx_activated_at', '') === '') {
            update_option('shieldx_activated_at', gmdate('c'), false);
        }
    }

    public function uninstall(): void
    {
        if (!$this->wpdb) {
            return;
        }

        $this->dropTables($this->tableNames());
    }

    public function installedVersion(): string
    {
        if (!function_exists('get_option')) {
            return '';
        }

        $version = get_option('shieldx_schema_version', '');

        return is_string($version) ? $version : '';
    }

    /**
     * @return array<int,string>
     */
    public function tableNames(?string $prefix = null): array
    {
        $prefix = $prefix ?: ($this->wpdb && isset($this->wpdb->prefix) ? (string) $this->wpdb->prefix : 'wp_');

        return [
            $prefix . 'shieldx_event_summaries',
            $prefix . 'shieldx_threats',
            $prefix . 'shieldx_patches',
            $prefix . 'shieldx_files',
            $prefix . 'shieldx_scans',
            $prefix . 'shieldx_user_events',
        ];
    }

    /**
     * @return array<int,string>
     */
    public function schemaSql(?string $prefix = null, ?string $charsetCollate = null): array
    {
        $prefix = $prefix ?: ($this->wpdb && isset($this->wpdb->prefix) ? (string) $this->wpdb->prefix : 'wp_');
        $charsetCollate = $charsetCollate ?: ($this->wpdb && method_exists($this->wpdb, 'get_charset_collate') ? $this->wpdb->get_charset_collate() : '');

        return [
            "CREATE TABLE {$prefix}shieldx_event_summaries (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                summary_date date NOT NULL,
                event_type varchar(50) NOT NULL,
                severity varchar(20) NOT NULL,
                count int unsigned DEFAULT 0,
                first_seen datetime NOT NULL,
                last_seen datetime NOT NULL,
                sample_event_id varchar(32) DEFAULT NULL,
                PRIMARY KEY  (id),
                UNIQUE KEY uk_date_type_sev (summary_date,event_type,severity),
                KEY idx_date (summary_date),
                KEY idx_last_seen (last_seen)
            ) {$charsetCollate};",
            "CREATE TABLE {$prefix}shieldx_threats (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                threat_id varchar(64) NOT NULL,
                rule_id varchar(80) DEFAULT NULL,
                type varchar(50) NOT NULL,
                severity varchar(20) NOT NULL,
                status varchar(30) DEFAULT 'active',
                source varchar(100) DEFAULT NULL,
                description text DEFAULT NULL,
                affected_path varchar(500) DEFAULT NULL,
                first_detected datetime NOT NULL,
                last_detected datetime NOT NULL,
                resolved_at datetime DEFAULT NULL,
                resolved_by bigint(20) unsigned DEFAULT NULL,
                PRIMARY KEY  (id),
                UNIQUE KEY uk_threat_id (threat_id),
                KEY idx_rule_id (rule_id),
                KEY idx_status_severity (status,severity),
                KEY idx_type (type),
                KEY idx_source_status (source,status),
                KEY idx_source_status_last_detected (source,status,last_detected),
                KEY idx_affected_path (affected_path(191))
            ) {$charsetCollate};",
            "CREATE TABLE {$prefix}shieldx_patches (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                patch_id varchar(32) NOT NULL,
                cve varchar(50) DEFAULT NULL,
                affected_plugin varchar(100) DEFAULT NULL,
                affected_versions varchar(255) DEFAULT NULL,
                method varchar(50) NOT NULL,
                rules text DEFAULT NULL,
                applied_at datetime DEFAULT NULL,
                reverted_at datetime DEFAULT NULL,
                status varchar(30) DEFAULT 'pending',
                PRIMARY KEY  (id),
                UNIQUE KEY uk_patch_id (patch_id),
                KEY idx_cve (cve),
                KEY idx_status (status)
            ) {$charsetCollate};",
            "CREATE TABLE {$prefix}shieldx_files (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                file_path varchar(500) NOT NULL,
                file_hash varchar(64) NOT NULL,
                file_size int unsigned DEFAULT NULL,
                last_modified datetime DEFAULT NULL,
                metadata_changed datetime DEFAULT NULL,
                plugin_slug varchar(100) DEFAULT NULL,
                is_core tinyint(1) DEFAULT 0,
                is_modified tinyint(1) DEFAULT 0,
                scan_time datetime DEFAULT NULL,
                PRIMARY KEY  (id),
                UNIQUE KEY uk_path (file_path(255)),
                KEY idx_plugin (plugin_slug),
                KEY idx_modified (is_modified),
                KEY idx_scan_time (scan_time)
            ) {$charsetCollate};",
            "CREATE TABLE {$prefix}shieldx_scans (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                scan_id varchar(32) NOT NULL,
                type varchar(30) NOT NULL,
                status varchar(30) DEFAULT 'pending',
                started_at datetime NOT NULL,
                completed_at datetime DEFAULT NULL,
                files_total int unsigned DEFAULT 0,
                files_scanned int unsigned DEFAULT 0,
                threats_found int unsigned DEFAULT 0,
                threats_cleaned int unsigned DEFAULT 0,
                results mediumtext DEFAULT NULL,
                PRIMARY KEY  (id),
                UNIQUE KEY uk_scan_id (scan_id),
                KEY idx_status (status),
                KEY idx_started (started_at)
            ) {$charsetCollate};",
            "CREATE TABLE {$prefix}shieldx_user_events (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                visitor_id varchar(64) NOT NULL,
                ip varchar(45) NOT NULL,
                country_code varchar(2) DEFAULT NULL,
                browser varchar(80) DEFAULT NULL,
                user_agent varchar(500) DEFAULT NULL,
                method varchar(10) NOT NULL,
                uri text NOT NULL,
                path varchar(500) NOT NULL,
                query_string text DEFAULT NULL,
                referer varchar(500) DEFAULT NULL,
                is_admin tinyint(1) DEFAULT 0,
                user_id bigint(20) unsigned DEFAULT 0,
                visited_at datetime NOT NULL,
                PRIMARY KEY  (id),
                KEY idx_visitor_seen (visitor_id,visited_at),
                KEY idx_ip_seen (ip,visited_at),
                KEY idx_country_seen (country_code,visited_at),
                KEY idx_path (path(191)),
                KEY idx_seen (visited_at)
            ) {$charsetCollate};",
        ];
    }

    /**
     * @param array<int,string> $tables
     */
    private function dropTables(array $tables): void
    {
        if (!$this->wpdb) {
            return;
        }

        foreach (array_values(array_unique($tables)) as $table) {
            $prepared = $this->dropTableSql($table);
            if ($prepared === '') {
                continue;
            }

            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared with %i when supported, otherwise a strict backticked plugin table identifier.
            $this->wpdb->query($prepared);
        }
    }

    private function dropTableSql(string $table): string
    {
        if (method_exists($this->wpdb, 'has_cap') && $this->wpdb->has_cap('identifier_placeholders')) {
            return (string) $this->wpdb->prepare('DROP TABLE IF EXISTS %i', $table);
        }

        if (preg_match('/^[A-Za-z0-9_]+$/', $table) !== 1) {
            return '';
        }

        return 'DROP TABLE IF EXISTS `' . str_replace('`', '``', $table) . '`';
    }
}
