<?php
defined( 'ABSPATH' ) || exit;

class SITECL_DB_Tables {

    public static function create(): void {
        global $wpdb;
        $charset = $wpdb->get_charset_collate();

        $tables = [
            // Health score history (4-week rolling window).
            "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}sitecl_health_scores (
                id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                score       TINYINT NOT NULL COMMENT 'A=5 B=4 C=3 D=2 F=1',
                grade       CHAR(1) NOT NULL,
                issues_json LONGTEXT NOT NULL,
                scanned_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY scanned_at (scanned_at)
            ) {$charset};",

            // Broken links cache.
            "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}sitecl_broken_links (
                id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                source_url   VARCHAR(2048) NOT NULL,
                target_url   VARCHAR(2048) NOT NULL,
                http_status  SMALLINT NOT NULL,
                redirect_hops TINYINT NOT NULL DEFAULT 0,
                last_checked DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY last_checked (last_checked)
            ) {$charset};",

            // Scan results — stores the last full-scan output.
            "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}sitecl_scan_results (
                id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                scan_type  VARCHAR(64) NOT NULL,
                result     LONGTEXT NOT NULL,
                scanned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY scan_type (scan_type),
                KEY scanned_at (scanned_at)
            ) {$charset};",

            // Error log — persistent tracking of PHP errors, with fix status.
            "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}sitecl_error_log (
                id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                error_hash       CHAR(32)        NOT NULL,
                type             VARCHAR(64)     NOT NULL DEFAULT '',
                message          TEXT            NOT NULL,
                file             VARCHAR(1024)   NOT NULL DEFAULT '',
                line             INT UNSIGNED    NOT NULL DEFAULT 0,
                plugin_name      VARCHAR(255)    NOT NULL DEFAULT '',
                first_seen       DATETIME        NOT NULL,
                last_seen        DATETIME        NOT NULL,
                occurrence_count INT UNSIGNED    NOT NULL DEFAULT 1,
                status           VARCHAR(16)     NOT NULL DEFAULT 'open',
                fix_type         VARCHAR(64)     NULL DEFAULT NULL,
                fix_key          VARCHAR(128)    NULL DEFAULT NULL,
                fixed_at         DATETIME        NULL DEFAULT NULL,
                server_job_id    VARCHAR(255)    NULL DEFAULT NULL,
                PRIMARY KEY (id),
                UNIQUE KEY error_hash (error_hash),
                KEY status (status),
                KEY last_seen (last_seen)
            ) {$charset};",
        ];

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        foreach ( $tables as $sql ) {
            dbDelta( $sql );
        }
    }
}
