<?php

declare(strict_types=1);

namespace AetosSeo\Storage;

if (!defined('ABSPATH')) {
    exit;
}

use wpdb;

class Schema
{
    /** @var wpdb */
    private $wpdb;

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

    /**
     * @return string[]
     */
    public function get_table_names(): array
    {
        $p = $this->wpdb->prefix;
        return [
            $p . 'aetos_seo_runs',
            $p . 'aetos_seo_findings',
            $p . 'aetos_seo_crawl',
            $p . 'aetos_seo_crawl_state',
        ];
    }

    /**
     * @return string[] SQL statements for dbDelta.
     *
     * v3.1.0 — Pin ENGINE=InnoDB on every table. Without an explicit engine,
     * tables on hosts where MyISAM is still the MySQL server default end up
     * MyISAM, losing transactional support and FK enforcement. dbDelta does
     * NOT inject the engine for you — it accepts whatever the CREATE TABLE
     * statement provides.
     */
    public function get_create_table_sql(): array
    {
        $charset = $this->wpdb->get_charset_collate();
        $p = $this->wpdb->prefix;

        $runs = $p . 'aetos_seo_runs';
        $findings = $p . 'aetos_seo_findings';
        $crawl = $p . 'aetos_seo_crawl';
        $crawl_state = $p . 'aetos_seo_crawl_state';

        return [
            "CREATE TABLE {$runs} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                run_type VARCHAR(20) NOT NULL DEFAULT 'audit',
                status VARCHAR(20) NOT NULL DEFAULT 'completed',
                created_at DATETIME NOT NULL,
                ended_at DATETIME NULL,
                overall_score INT NULL,
                category_scores LONGTEXT NULL,
                counts_json LONGTEXT NULL,
                context_json LONGTEXT NULL,
                duration_ms INT NULL,
                notes TEXT NULL,
                PRIMARY KEY  (id),
                KEY run_type (run_type),
                KEY created_at (created_at)
            ) ENGINE=InnoDB {$charset};",

            "CREATE TABLE {$findings} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                run_id BIGINT UNSIGNED NOT NULL,
                finding_id VARCHAR(191) NOT NULL,
                check_id VARCHAR(191) NULL,
                title TEXT NOT NULL,
                category VARCHAR(50) NOT NULL,
                severity VARCHAR(10) NOT NULL,
                score_impact INT NOT NULL DEFAULT 0,
                status VARCHAR(10) NOT NULL DEFAULT 'open',
                location TEXT NULL,
                evidence LONGTEXT NULL,
                why_it_matters LONGTEXT NULL,
                recommendation_steps LONGTEXT NULL,
                retest LONGTEXT NULL,
                created_at DATETIME NOT NULL,
                updated_at DATETIME NOT NULL,
                PRIMARY KEY  (id),
                KEY run_id (run_id),
                KEY finding_id (finding_id),
                KEY category (category),
                KEY severity (severity),
                KEY status (status)
            ) ENGINE=InnoDB {$charset};",

            "CREATE TABLE {$crawl} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                run_id BIGINT UNSIGNED NOT NULL,
                url TEXT NOT NULL,
                normalized_url TEXT NULL,
                normalized_url_hash CHAR(32) NULL,
                status_code INT NOT NULL DEFAULT 0,
                redirect_chain LONGTEXT NULL,
                canonical TEXT NULL,
                robots_noindex TINYINT(1) NOT NULL DEFAULT 0,
                title TEXT NULL,
                meta_desc TEXT NULL,
                h1_count INT NOT NULL DEFAULT 0,
                h2_count INT NOT NULL DEFAULT 0,
                h3_count INT NOT NULL DEFAULT 0,
                word_count INT NOT NULL DEFAULT 0,
                images_missing_alt INT NOT NULL DEFAULT 0,
                images_total INT NOT NULL DEFAULT 0,
                jsonld_types LONGTEXT NULL,
                og_title TEXT NULL,
                og_description TEXT NULL,
                og_image TEXT NULL,
                og_type VARCHAR(50) NULL,
                twitter_card VARCHAR(50) NULL,
                lang VARCHAR(20) NULL,
                hreflang_count INT NOT NULL DEFAULT 0,
                outbound_links_count INT NOT NULL DEFAULT 0,
                analysis_json LONGTEXT NULL,
                author_name TEXT NULL,
                article_modified_time VARCHAR(50) NULL,
                discovered_from TEXT NULL,
                created_at DATETIME NOT NULL,
                PRIMARY KEY (id),
                KEY run_id (run_id),
                KEY status_code (status_code),
                KEY normalized_url_hash (normalized_url_hash)
            ) ENGINE=InnoDB {$charset};",

            "CREATE TABLE {$crawl_state} (
                run_id BIGINT UNSIGNED NOT NULL,
                state_blob LONGTEXT NULL,
                updated_at DATETIME NOT NULL,
                lock_token CHAR(36) NULL,
                lock_until DATETIME NULL,
                PRIMARY KEY (run_id),
                KEY updated_at (updated_at),
                KEY lock_until (lock_until)
            ) ENGINE=InnoDB {$charset};",

        ];
    }
}