<?php

namespace VulnTitan\Services;

class VulnerabilityRiskService
{
    protected const SCHEMA_VERSION = 1;
    protected const OPTION_SCHEMA_VERSION = 'vulntitan_vuln_risk_schema_version';
    protected static bool $tablesChecked = false;
    protected static bool $tablesReady = false;

    public static function ensureTables(): bool
    {
        if (self::$tablesChecked) {
            return self::$tablesReady;
        }

        self::$tablesChecked = true;
        $storedVersion = (int) get_option(self::OPTION_SCHEMA_VERSION, 0);
        $riskReady = self::tableExists(self::getRiskTableName()) && self::hasRiskColumns();
        $auditReady = self::tableExists(self::getAuditTableName()) && self::hasAuditColumns();
        $needsSchemaSync = !$riskReady || !$auditReady || $storedVersion < self::SCHEMA_VERSION;

        if ($needsSchemaSync) {
            self::$tablesReady = self::createTables();
        } else {
            self::$tablesReady = true;
        }

        if (self::$tablesReady && $storedVersion !== self::SCHEMA_VERSION) {
            update_option(self::OPTION_SCHEMA_VERSION, self::SCHEMA_VERSION, false);
        }

        return self::$tablesReady;
    }

    public static function createTables(): bool
    {
        global $wpdb;

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

        $riskTable = self::getRiskTableName();
        $auditTable = self::getAuditTableName();
        $charsetCollate = $wpdb->get_charset_collate();

        $riskSql = "CREATE TABLE {$riskTable} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            vuln_uid CHAR(40) NOT NULL,
            item_type VARCHAR(24) NOT NULL DEFAULT '',
            item_slug VARCHAR(191) NOT NULL DEFAULT '',
            item_version VARCHAR(64) NOT NULL DEFAULT '',
            vuln_id VARCHAR(191) NOT NULL DEFAULT '',
            cve VARCHAR(64) NOT NULL DEFAULT '',
            title VARCHAR(191) NOT NULL DEFAULT '',
            decision VARCHAR(16) NOT NULL DEFAULT '',
            expires_at DATETIME NOT NULL,
            note VARCHAR(255) NOT NULL DEFAULT '',
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            created_by BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            updated_by BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            PRIMARY KEY  (id),
            UNIQUE KEY vuln_uid (vuln_uid),
            KEY item_type_slug (item_type, item_slug),
            KEY decision (decision),
            KEY expires_at (expires_at)
        ) {$charsetCollate};";

        $auditSql = "CREATE TABLE {$auditTable} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            vuln_uid CHAR(40) NOT NULL,
            item_type VARCHAR(24) NOT NULL DEFAULT '',
            item_slug VARCHAR(191) NOT NULL DEFAULT '',
            item_version VARCHAR(64) NOT NULL DEFAULT '',
            vuln_id VARCHAR(191) NOT NULL DEFAULT '',
            cve VARCHAR(64) NOT NULL DEFAULT '',
            title VARCHAR(191) NOT NULL DEFAULT '',
            action VARCHAR(24) NOT NULL DEFAULT '',
            note VARCHAR(255) NOT NULL DEFAULT '',
            expires_at DATETIME NULL,
            user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL,
            PRIMARY KEY  (id),
            KEY vuln_uid (vuln_uid),
            KEY action (action),
            KEY created_at (created_at)
        ) {$charsetCollate};";

        dbDelta($riskSql);
        dbDelta($auditSql);

        self::$tablesReady = self::tableExists($riskTable)
            && self::tableExists($auditTable)
            && self::hasRiskColumns()
            && self::hasAuditColumns();

        self::$tablesChecked = true;

        if (self::$tablesReady) {
            update_option(self::OPTION_SCHEMA_VERSION, self::SCHEMA_VERSION, false);
        }

        return self::$tablesReady;
    }

    public static function getRiskTableName(): string
    {
        global $wpdb;

        return $wpdb->prefix . 'vulntitan_vuln_risks';
    }

    public static function getAuditTableName(): string
    {
        global $wpdb;

        return $wpdb->prefix . 'vulntitan_vuln_audit';
    }

    public static function attachDecisions(array $item, array $vulnerabilities): array
    {
        if (!self::ensureTables() || !$vulnerabilities) {
            return $vulnerabilities;
        }

        $uids = [];
        foreach ($vulnerabilities as $vuln) {
            $uids[] = self::buildVulnUid($item, is_array($vuln) ? $vuln : []);
        }

        $decisions = self::getDecisions($uids);

        return array_map(function ($vuln, $uid) use ($decisions) {
            if (!is_array($vuln)) {
                return $vuln;
            }

            $decision = $decisions[$uid] ?? null;
            $vuln['risk_uid'] = $uid;
            $vuln['risk_decision'] = $decision;

            return $vuln;
        }, $vulnerabilities, $uids);
    }

    public static function setDecision(array $item, array $vuln, string $decision, int $expiresInDays, string $note = ''): array
    {
        if (!self::ensureTables()) {
            return [];
        }

        $decision = $decision === 'ignore' ? 'ignore' : 'accept';
        $expiresInDays = max(1, min(3650, $expiresInDays));
        $userId = function_exists('get_current_user_id') ? (int) get_current_user_id() : 0;

        $uid = self::buildVulnUid($item, $vuln);
        $meta = self::normalizePayload($item, $vuln);
        $now = current_time('mysql');
        $expiresAt = date('Y-m-d H:i:s', (int) current_time('timestamp') + ($expiresInDays * DAY_IN_SECONDS));

        global $wpdb;
        $riskTable = self::getRiskTableName();

        $wpdb->query(
            $wpdb->prepare(
                "INSERT INTO {$riskTable}
                (vuln_uid, item_type, item_slug, item_version, vuln_id, cve, title, decision, expires_at, note, created_at, updated_at, created_by, updated_by)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %d)
                ON DUPLICATE KEY UPDATE
                decision = VALUES(decision),
                expires_at = VALUES(expires_at),
                note = VALUES(note),
                updated_at = VALUES(updated_at),
                updated_by = VALUES(updated_by)",
                $uid,
                $meta['item_type'],
                $meta['item_slug'],
                $meta['item_version'],
                $meta['vuln_id'],
                $meta['cve'],
                $meta['title'],
                $decision,
                $expiresAt,
                $note,
                $now,
                $now,
                $userId,
                $userId
            )
        );

        self::logAudit($uid, $meta, $decision, $note, $expiresAt, $userId);

        return [
            'status' => $decision,
            'expires_at' => $expiresAt,
            'note' => $note,
            'updated_by' => $userId,
        ];
    }

    public static function clearDecision(array $item, array $vuln, string $reason = ''): bool
    {
        if (!self::ensureTables()) {
            return false;
        }

        $uid = self::buildVulnUid($item, $vuln);
        $meta = self::normalizePayload($item, $vuln);
        $userId = function_exists('get_current_user_id') ? (int) get_current_user_id() : 0;

        global $wpdb;
        $riskTable = self::getRiskTableName();

        $wpdb->delete($riskTable, ['vuln_uid' => $uid], ['%s']);
        self::logAudit($uid, $meta, 'clear', $reason, null, $userId);

        return true;
    }

    public static function getAudit(string $uid, int $limit = 20): array
    {
        if (!self::ensureTables()) {
            return [];
        }

        global $wpdb;
        $auditTable = self::getAuditTableName();

        $limit = max(1, min(100, $limit));
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT action, note, expires_at, user_id, created_at
                 FROM {$auditTable}
                 WHERE vuln_uid = %s
                 ORDER BY created_at DESC
                 LIMIT %d",
                $uid,
                $limit
            ),
            ARRAY_A
        );

        if (!is_array($rows)) {
            return [];
        }

        foreach ($rows as &$row) {
            $userId = (int) ($row['user_id'] ?? 0);
            if ($userId > 0) {
                $user = get_user_by('id', $userId);
                $row['user_label'] = $user && isset($user->display_name)
                    ? (string) $user->display_name
                    : ('User #' . $userId);
            } else {
                $row['user_label'] = esc_html__('System', 'vulntitan');
            }
        }
        unset($row);

        return $rows;
    }

    protected static function getDecisions(array $uids): array
    {
        if (!$uids) {
            return [];
        }

        global $wpdb;
        $riskTable = self::getRiskTableName();
        $placeholders = implode(',', array_fill(0, count($uids), '%s'));

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT vuln_uid, decision, expires_at, note, updated_by
                 FROM {$riskTable}
                 WHERE vuln_uid IN ({$placeholders})",
                $uids
            ),
            ARRAY_A
        );

        if (!is_array($rows)) {
            return [];
        }

        $now = current_time('mysql');
        $decisions = [];
        $expired = [];

        foreach ($rows as $row) {
            if (!empty($row['expires_at']) && $row['expires_at'] < $now) {
                $expired[] = $row['vuln_uid'];
                continue;
            }

            $decisions[$row['vuln_uid']] = [
                'status' => $row['decision'],
                'expires_at' => $row['expires_at'],
                'note' => $row['note'],
                'updated_by' => (int) $row['updated_by'],
            ];
        }

        if ($expired) {
            $thisPlaceholders = implode(',', array_fill(0, count($expired), '%s'));
            $wpdb->query(
                $wpdb->prepare(
                    "DELETE FROM {$riskTable} WHERE vuln_uid IN ({$thisPlaceholders})",
                    $expired
                )
            );
        }

        return $decisions;
    }

    protected static function logAudit(string $uid, array $meta, string $action, string $note, ?string $expiresAt, int $userId): void
    {
        if (!self::ensureTables()) {
            return;
        }

        global $wpdb;
        $auditTable = self::getAuditTableName();

        $wpdb->insert(
            $auditTable,
            [
                'vuln_uid' => $uid,
                'item_type' => $meta['item_type'],
                'item_slug' => $meta['item_slug'],
                'item_version' => $meta['item_version'],
                'vuln_id' => $meta['vuln_id'],
                'cve' => $meta['cve'],
                'title' => $meta['title'],
                'action' => $action,
                'note' => $note,
                'expires_at' => $expiresAt,
                'user_id' => $userId,
                'created_at' => current_time('mysql'),
            ],
            [
                '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s',
            ]
        );
    }

    protected static function normalizePayload(array $item, array $vuln): array
    {
        $itemType = sanitize_key($item['type'] ?? '');
        $itemSlug = sanitize_text_field($item['slug'] ?? '');
        $itemVersion = sanitize_text_field($item['version'] ?? '');
        $vulnId = sanitize_text_field($vuln['id'] ?? '');
        $cve = sanitize_text_field($vuln['cve'] ?? '');
        $title = sanitize_text_field($vuln['title'] ?? '');

        return [
            'item_type' => $itemType,
            'item_slug' => $itemSlug,
            'item_version' => $itemVersion,
            'vuln_id' => $vulnId,
            'cve' => $cve,
            'title' => $title,
        ];
    }

    protected static function buildVulnUid(array $item, array $vuln): string
    {
        $payload = self::normalizePayload($item, $vuln);
        $source = implode('|', [
            $payload['item_type'],
            $payload['item_slug'],
            $payload['item_version'],
            $payload['vuln_id'],
            $payload['cve'],
            $payload['title'],
        ]);

        return substr(sha1($source), 0, 40);
    }

    protected static function tableExists(string $tableName): bool
    {
        global $wpdb;

        $tableName = esc_sql($tableName);
        $result = $wpdb->get_var("SHOW TABLES LIKE '{$tableName}'");

        return $result === $tableName;
    }

    protected static function hasRiskColumns(): bool
    {
        return self::hasColumns(self::getRiskTableName(), [
            'vuln_uid',
            'decision',
            'expires_at',
            'item_type',
        ]);
    }

    protected static function hasAuditColumns(): bool
    {
        return self::hasColumns(self::getAuditTableName(), [
            'vuln_uid',
            'action',
            'created_at',
        ]);
    }

    protected static function hasColumns(string $tableName, array $columns): bool
    {
        global $wpdb;

        $tableName = esc_sql($tableName);
        $existing = $wpdb->get_col("DESC {$tableName}", 0);
        if (!is_array($existing)) {
            return false;
        }

        foreach ($columns as $column) {
            if (!in_array($column, $existing, true)) {
                return false;
            }
        }

        return true;
    }
}
