<?php

namespace WPSN\Modules\Audit;

defined('ABSPATH') || exit;

/**
 * Stage 8: Creates/updates custom DB tables for the Audit module.
 */
final class AuditInstaller
{
    // 1.1.0: add scan history tables (scans + per-scan issues)
    // 1.2.0: async scan progress columns + title-key table for duplicate detection
    // 1.4.0: generic async duplicate-key tables for posts + terms
    private const DB_VERSION = '1.4.0';

    public static function maybeInstall(): void
    {
        $installed = (string) get_option('wpsn_audit_db_version', '');
        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();
        $table   = $wpdb->prefix . 'wpsn_audit_issues';
        $scans   = $wpdb->prefix . 'wpsn_audit_scans';
        $scanIssues = $wpdb->prefix . 'wpsn_audit_scan_issues';
        $scanTitles = $wpdb->prefix . 'wpsn_audit_scan_titles';
        $scanDescriptions = $wpdb->prefix . 'wpsn_audit_scan_descriptions';
        $scanTitleKeys = $wpdb->prefix . 'wpsn_audit_scan_title_keys';
        $scanDescriptionKeys = $wpdb->prefix . 'wpsn_audit_scan_description_keys';

        // Latest issues table (keeps current scan results for fast UI)
        $sql = "CREATE TABLE {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            object_type varchar(20) NOT NULL,
            object_id bigint(20) unsigned NOT NULL,
            object_subtype varchar(64) NOT NULL DEFAULT '',
            object_title varchar(255) NOT NULL DEFAULT '',
            object_url varchar(255) NOT NULL DEFAULT '',
            issue_code varchar(64) NOT NULL,
            severity tinyint(1) unsigned NOT NULL DEFAULT 2,
            message varchar(255) NOT NULL,
            suggestion varchar(255) NOT NULL DEFAULT '',
            details longtext DEFAULT NULL,
            detected_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY object_issue (object_type, object_id, issue_code),
            KEY issue_code (issue_code),
            KEY severity (severity),
            KEY object_type (object_type),
            KEY updated_at (updated_at),
            KEY object_id (object_id)
        ) {$charset};";

        // Scan history tables.
        $sqlScans = "CREATE TABLE {$scans} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            status varchar(20) NOT NULL DEFAULT 'running',
            phase varchar(20) NOT NULL DEFAULT 'queued',
            started_at datetime NOT NULL,
            finished_at datetime DEFAULT NULL,
            duration_sec int(11) unsigned NOT NULL DEFAULT 0,
            include_terms tinyint(1) unsigned NOT NULL DEFAULT 1,
            check_social tinyint(1) unsigned NOT NULL DEFAULT 1,
            total_posts int(11) unsigned NOT NULL DEFAULT 0,
            total_terms int(11) unsigned NOT NULL DEFAULT 0,
            scanned_posts int(11) unsigned NOT NULL DEFAULT 0,
            scanned_terms int(11) unsigned NOT NULL DEFAULT 0,
            news_eligible int(11) unsigned NOT NULL DEFAULT 0,
            video_candidates int(11) unsigned NOT NULL DEFAULT 0,
            video_eligible int(11) unsigned NOT NULL DEFAULT 0,
            issues_total int(11) unsigned NOT NULL DEFAULT 0,
            issues_by_code longtext DEFAULT NULL,
            config_json longtext DEFAULT NULL,
            error_message varchar(255) NOT NULL DEFAULT '',
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY status (status),
            KEY phase (phase),
            KEY started_at (started_at),
            KEY finished_at (finished_at)
        ) {$charset};";

        $sqlScanIssues = "CREATE TABLE {$scanIssues} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            scan_id bigint(20) unsigned NOT NULL,
            object_type varchar(20) NOT NULL,
            object_id bigint(20) unsigned NOT NULL,
            object_subtype varchar(64) NOT NULL DEFAULT '',
            object_title varchar(255) NOT NULL DEFAULT '',
            object_url varchar(255) NOT NULL DEFAULT '',
            issue_code varchar(64) NOT NULL,
            severity tinyint(1) unsigned NOT NULL DEFAULT 2,
            message varchar(255) NOT NULL,
            suggestion varchar(255) NOT NULL DEFAULT '',
            details longtext DEFAULT NULL,
            detected_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY scan_object_issue (scan_id, object_type, object_id, issue_code),
            KEY scan_id (scan_id),
            KEY issue_code (issue_code),
            KEY severity (severity),
            KEY object_type (object_type),
            KEY updated_at (updated_at),
            KEY object_id (object_id)
        ) {$charset};";

        // Legacy post-only title keys used by SQL scan duplicate detection.
        $sqlScanTitles = "CREATE TABLE {$scanTitles} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            scan_id bigint(20) unsigned NOT NULL,
            post_id bigint(20) unsigned NOT NULL,
            post_type varchar(64) NOT NULL DEFAULT '',
            title_key varchar(191) NOT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY scan_post (scan_id, post_id),
            KEY scan_key (scan_id, title_key),
            KEY post_id (post_id)
        ) {$charset};";

        // Legacy post-only description keys used by SQL scan duplicate detection.
        $sqlScanDescriptions = "CREATE TABLE {$scanDescriptions} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            scan_id bigint(20) unsigned NOT NULL,
            post_id bigint(20) unsigned NOT NULL,
            post_type varchar(64) NOT NULL DEFAULT '',
            description_key varchar(191) NOT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY scan_post (scan_id, post_id),
            KEY scan_key (scan_id, description_key),
            KEY post_id (post_id)
        ) {$charset};";

        // Generic title keys for accurate async duplicate detection across posts and terms.
        $sqlScanTitleKeys = "CREATE TABLE {$scanTitleKeys} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            scan_id bigint(20) unsigned NOT NULL,
            object_type varchar(20) NOT NULL,
            object_id bigint(20) unsigned NOT NULL,
            object_subtype varchar(64) NOT NULL DEFAULT '',
            title_key varchar(191) NOT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY scan_object (scan_id, object_type, object_id),
            KEY scan_key (scan_id, object_type, title_key),
            KEY object_ref (object_type, object_id)
        ) {$charset};";

        // Generic description keys for accurate async duplicate detection across posts and terms.
        $sqlScanDescriptionKeys = "CREATE TABLE {$scanDescriptionKeys} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            scan_id bigint(20) unsigned NOT NULL,
            object_type varchar(20) NOT NULL,
            object_id bigint(20) unsigned NOT NULL,
            object_subtype varchar(64) NOT NULL DEFAULT '',
            description_key varchar(191) NOT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY scan_object (scan_id, object_type, object_id),
            KEY scan_key (scan_id, object_type, description_key),
            KEY object_ref (object_type, object_id)
        ) {$charset};";

        dbDelta($sql);
        dbDelta($sqlScans);
        dbDelta($sqlScanIssues);
        dbDelta($sqlScanTitles);
        dbDelta($sqlScanDescriptions);
        dbDelta($sqlScanTitleKeys);
        dbDelta($sqlScanDescriptionKeys);

        update_option('wpsn_audit_db_version', self::DB_VERSION);
    }
}
