<?php
/**
 * Wally Monitor - Database Operations
 *
 * Centraliza todas as operações de banco de dados.
 * Elimina código duplicado de CREATE TABLE e inserts.
 *
 * @package    WallyMonitor
 * @subpackage Includes
 * @since      1.1.0
 */

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

class Wally_Database {

    /** @var Wally_Database|null Singleton instance */
    private static $instance = null;

    /**
     * Obtém instância singleton
     *
     * @return Wally_Database
     */
    public static function get_instance() {
        if ( null === self::$instance ) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Retorna nome da tabela com prefixo
     *
     * @return string
     */
    public function get_table_name() {
        global $wpdb;
        return $wpdb->prefix . 'wally_history';
    }

    /**
     * Hook de ativação do plugin
     * Chamado via register_activation_hook()
     */
    public static function activate() {
        $db = self::get_instance();
        $db->create_tables();
    }

    /**
     * Cria tabelas no banco de dados (fonte única de verdade)
     */
    public function create_tables() {
        global $wpdb;

        $table_name      = $this->get_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safe (wpdb->prefix + hardcoded string)
        $sql = "CREATE TABLE $table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            url varchar(255) NOT NULL,
            scan_date datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
            report_data longtext NOT NULL,
            score int(3) DEFAULT 0,
            total_violations int(5) DEFAULT 0,
            PRIMARY KEY  (id),
            KEY url (url),
            KEY scan_date (scan_date)
        ) $charset_collate;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta( $sql );

        update_option( 'wally_monitor_db_version', WALLY_MONITOR_VERSION );
    }

    /**
     * Garante que a tabela existe (safety net para upgrades).
     * Resultado é cacheado em memória para evitar SHOW TABLES repetitivo.
     */
    public function ensure_table_exists() {
        static $verified = false;
        if ( $verified ) {
            return;
        }

        global $wpdb;
        $table_name = $this->get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        if ( $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $table_name ) ) !== $table_name ) {
            $this->create_tables();
        }

        $verified = true;
    }

    /**
     * Insere relatório de análise
     *
     * @param string $url              URL analisada.
     * @param array  $report_data      Dados completos do relatório.
     * @param int    $score            Score 0-100.
     * @param int    $total_violations Total de violações.
     * @return int|false Insert ID ou false em caso de erro.
     */
    public function insert_report( $url, $report_data, $score, $total_violations ) {
        global $wpdb;

        $this->ensure_table_exists();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $wpdb->insert(
            $this->get_table_name(),
            array(
                'url'              => esc_url_raw( $url ),
                'scan_date'        => current_time( 'mysql' ),
                'report_data'      => wp_json_encode( $report_data ),
                'score'            => max( 0, min( 100, intval( $score ) ) ),
                'total_violations' => max( 0, intval( $total_violations ) ),
            ),
            array( '%s', '%s', '%s', '%d', '%d' )
        );

        return $result ? $wpdb->insert_id : false;
    }

    /**
     * Remove relatórios antigos de uma URL
     *
     * @param string $url URL a limpar.
     */
    public function delete_reports_by_url( $url ) {
        global $wpdb;

        $this->ensure_table_exists();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->delete(
            $this->get_table_name(),
            array( 'url' => $url ),
            array( '%s' )
        );
    }

    /**
     * Obtém relatório mais recente por URL
     *
     * @param string $url URL a buscar.
     * @return array|null Dados do relatório ou null.
     */
    public function get_latest_report( $url ) {
        global $wpdb;
        $table_name = $this->get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        if ( $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $table_name ) ) !== $table_name ) {
            return null;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $report_data = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT report_data FROM " . esc_sql( $table_name ) . " WHERE url = %s ORDER BY scan_date DESC LIMIT 1",
                $url
            )
        );

        if ( ! $report_data ) {
            return null;
        }

        $decoded = json_decode( $report_data, true );
        if ( json_last_error() !== JSON_ERROR_NONE ) {
            return null;
        }

        return $decoded;
    }

    /**
     * Obtém o relatório mais recente de qualquer URL.
     *
     * @return array|null Dados do relatório ou null.
     */
    public function get_latest_any_report() {
        global $wpdb;
        $table = $this->get_table_name();

        $this->ensure_table_exists();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $row = $wpdb->get_row(
            "SELECT report_data FROM " . esc_sql( $table ) . " ORDER BY scan_date DESC LIMIT 1"
        );

        if ( ! $row ) {
            return null;
        }

        $decoded = json_decode( $row->report_data, true );
        if ( json_last_error() !== JSON_ERROR_NONE ) {
            return null;
        }

        return $decoded;
    }

    /**
     * Atualiza histórico resumido (últimas 10 análises em wp_options)
     *
     * @param array  $report_data Dados do relatório.
     * @param string $url         URL analisada.
     */
    public function update_history_summary( $report_data, $url ) {
        $history = get_option( 'wally_monitor_history', array() );

        array_unshift( $history, array(
            'timestamp'      => sanitize_text_field( $report_data['timestamp'] ),
            'user_id'        => absint( $report_data['user_id'] ),
            'total_problems' => absint( $report_data['summary']['totalProblems'] ),
            'score'          => absint( $report_data['summary']['score'] ),
            'url'            => esc_url_raw( $url ),
            'analysis_mode'  => isset( $report_data['summary']['analysisMode'] ) ? sanitize_key( $report_data['summary']['analysisMode'] ) : 'wcag',
        ) );

        $history = array_slice( $history, 0, 10 );
        update_option( 'wally_monitor_history', $history, false );
    }
}
