<?php
/**
 * Aiforya_Schema_Markup_Db — service database.
 * Plugin : AIFORYA Schema Markup (aiforya-schema-markup).
 * Généré par PIVOT scaffolder déterministe (doctrine 2 mai 2026, anti-patch).
 * Le scaffold est figé. Le LLM N1 remplit UNIQUEMENT les bodies entre
 * <<<AIFORYA_SLOT_BEGIN ... >>> et <<<AIFORYA_SLOT_END>>>.
 */

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

if ( class_exists( 'Aiforya_Schema_Markup_Db', false ) ) {
    return;
}
class Aiforya_Schema_Markup_Db {

    /** @var self|null Singleton instance (pattern AIFORYA canonical). */
    private static ?self $instance = null;

    /**
     * Singleton accessor — convention AIFORYA canonical.
     * Tous les autres services et tabs accèdent à cette classe via X::get_instance().
     */
    public static function get_instance(): self {
        if ( self::$instance === null ) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    public function __construct() {
        // PIVOT scaffold — constructor public pour DI/test, mais convention = get_instance().
    }
    /**
     * Defaults canoniques de toutes les settings du plugin (émis déterministe par PIVOT scaffolder).
     * Source de vérité : plan.options[] du plan.
     * @return array Defaults complets toutes settings keys.
     */
    public static function get_default_settings(): array {
        return array(
            'settings' => ['auto_inject_enabled' => true, 'cache_ttl_minutes' => 1440],
            'type_mappings' => ['post' => 'Article', 'page' => 'WebPage', 'product' => 'Product'],
        );
    }

    /**
     * Récupère toutes les settings (merge stored + defaults via wp_parse_args).
     * @return array Tableau associatif COMPLET (toutes les keys garanties).
     */
    public function get_settings(): array {
        $stored = get_option( 'aiforya_sm_settings', array() );
        if ( ! is_array( $stored ) ) {
            $stored = array();
        }
        $defaults = self::get_default_settings();
        $merged = array();
        foreach ( $defaults as $k => $v ) {
            if ( is_array( $v ) && isset( $stored[ $k ] ) && is_array( $stored[ $k ] ) ) {
                $merged[ $k ] = wp_parse_args( $stored[ $k ], $v );
            } else {
                $merged[ $k ] = isset( $stored[ $k ] ) ? $stored[ $k ] : $v;
            }
        }
        return $merged;
    }

    /**
     * Sauvegarde les settings (merge avec defaults pour garantir aucune clé manquante).
     * @param array $settings Settings à sauvegarder.
     * @return bool True si la sauvegarde a réussi.
     */
    public function save_settings( array $settings = array() ): bool {
        $defaults = self::get_default_settings();
        $merged = array();
        foreach ( $defaults as $k => $v ) {
            $merged[ $k ] = isset( $settings[ $k ] ) ? $settings[ $k ] : $v;
        }
        return update_option( 'aiforya_sm_settings', $merged, true );
    }

    /**
     * Récupère UNE seule setting par clé (avec fallback default).
     * @param string $key Clé de la setting.
     * @param mixed $default Default si key manquante.
     * @return mixed Valeur de la setting.
     */
    public function get_setting( string $key = '', $default = null ) {
        $settings = $this->get_settings();
        if ( isset( $settings[ $key ] ) ) {
            return $settings[ $key ];
        }
        return $default;
    }

    /**
     * Met à jour UNE seule setting par clé (préserve les autres).
     * @param string $key Clé.
     * @param mixed $value Nouvelle valeur.
     * @return bool True si la mise à jour a réussi.
     */
    public function update_setting( string $key = '', $value = null ): bool {
        $settings = $this->get_settings();
        $settings[ $key ] = $value;
        return $this->save_settings( $settings );
    }

    /**
     * Retourne le nom complet de la table avec le préfixe WordPress.
     * @param string $name Nom court de la table.
     * @return string Nom complet de la table.
     */
    public function get_table_name( string $name = '' ): string {
        global $wpdb;
        return $wpdb->prefix . 'aiforya_sm_' . $name;
    }

    /**
     * Crée ou met à jour un schéma pour un post.
     * @param array $data Données du schéma (post_id, schema_type, schema_json, etc.).
     * @return mixed ID du schéma ou erreur.
     */
    public function save_or_update_schema( array $data = [] ) {
        global $wpdb;
        $table = $this->get_table_name('schemas');

        // Validation des données requises
        if (empty($data['post_id'])) {
            return new WP_Error('missing_post_id', __('Post ID required.', 'aiforya-schema-markup'));
        }
        if (empty($data['schema_type'])) {
            return new WP_Error('missing_schema_type', __('Schema type required.', 'aiforya-schema-markup'));
        }
        if (empty($data['schema_json'])) {
            return new WP_Error('missing_schema_json', __('JSON-LD data required.', 'aiforya-schema-markup'));
        }

        $post_id = absint($data['post_id']);
        $post = get_post($post_id);
        if (!$post) {
            return new WP_Error('invalid_post', __('Post not found.', 'aiforya-schema-markup'));
        }
        $schema_type = sanitize_text_field($data['schema_type']);
        $schema_json = $data['schema_json'];
        // Cohérent avec SQL CREATE TABLE Activator : status + source + post_type + richness_score (pas is_active/validation_status).
        $status     = isset($data['status']) ? sanitize_text_field($data['status']) : ( ( isset($data['is_active']) && empty($data['is_active']) ) ? 'inactive' : 'active' );
        $source     = isset($data['source']) ? sanitize_text_field($data['source']) : 'auto_detected';
        $post_type  = isset($data['post_type']) ? sanitize_key($data['post_type']) : (string) $post->post_type;
        $richness   = isset($data['richness_score']) ? max(0, min(100, (int) $data['richness_score'])) : 0;

        // Vérifier si un schéma existe déjà pour ce post
        $existing = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM %i WHERE post_id = %d", $table,
            $post_id
        ));

        $current_time = current_time('mysql');

        if ($existing) {
            // Mise à jour
            $result = $wpdb->update(
                $table,
                array(
                    'schema_type'    => $schema_type,
                    'schema_json'    => $schema_json,
                    'status'         => $status,
                    'source'         => $source,
                    'richness_score' => $richness,
                    'updated_at'     => $current_time,
                ),
                array('id' => $existing),
                array('%s', '%s', '%s', '%s', '%d', '%s'),
                array('%d')
            );

            if ($result === false) {
                return new WP_Error('db_update_error', sprintf(/* translators: %s : valeur insérée dynamiquement. */ __('Schema update error: %s', 'aiforya-schema-markup'), $wpdb->last_error));
            }

            return (int) $existing;
        } else {
            // Insertion
            $result = $wpdb->insert(
                $table,
                array(
                    'post_id'        => $post_id,
                    'post_type'      => $post_type,
                    'schema_type'    => $schema_type,
                    'schema_json'    => $schema_json,
                    'status'         => $status,
                    'source'         => $source,
                    'richness_score' => $richness,
                    'created_at'     => $current_time,
                    'updated_at'     => $current_time,
                ),
                array('%d', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s')
            );

            if ($result === false) {
                return new WP_Error('db_insert_error', sprintf(/* translators: %s : valeur insérée dynamiquement. */ __('Schema creation error: %s', 'aiforya-schema-markup'), $wpdb->last_error));
            }

            return (int) $wpdb->insert_id;
        }
    }

    /**
     * Récupère le schéma actif pour un post ID.
     * @param int $post_id ID du post.
     * @return ?object Objet du schéma ou null.
     */
    public function get_schema_by_post_id( int $post_id = 0 ): ?object {
        global $wpdb;
        $table = $this->get_table_name('schemas');

        $schema = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM %i WHERE post_id = %d AND status = 'active' LIMIT 1", $table,
            $post_id
        ));

        return $schema ?: null;
    }

    /**
     * Récupère une liste paginée de schémas pour l'affichage.
     * @param array $args Arguments de pagination et de filtrage.
     * @return array Tableau d'objets schémas.
     */
    public function get_schemas_for_listing( array $args = [] ): array {
        global $wpdb;
        $table = $this->get_table_name('schemas');

        // Paramètres par défaut
        $defaults = array(
            'per_page' => 20,
            'page' => 1,
            'orderby' => 'updated_at',
            'order' => 'DESC',
            'schema_type' => '',
            'validation_status' => '',
            'search' => '',
        );

        $args = wp_parse_args($args, $defaults);

        $per_page = absint($args['per_page']);
        $page = absint($args['page']);
        $offset = ($page - 1) * $per_page;
        $orderby = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']);

        // Construction de la requête WHERE
        $where = array('1=1');
        $params = array();

        if (!empty($args['schema_type'])) {
            $where[] = 'schema_type = %s';
            $params[] = sanitize_text_field($args['schema_type']);
        }

        // validation_status est dans validation_log (pas schemas) — filter via subquery JOIN.
        if (!empty($args['validation_status'])) {
            // %i = marqueur d'IDENTIFIANT de $wpdb->prepare (WordPress 6.2+, et l'en-tête
            // exige 6.2). Le nom de table est donc échappé par WordPress lui-même, au lieu
            // d'être interpolé puis couvert par un phpcs:ignore qui ne protégeait rien.
            $where[]  = 'id IN (SELECT schema_id FROM %i WHERE validation_status = %s)';
            $params[] = $wpdb->prefix . 'aiforya_sm_validation_log';
            $params[] = sanitize_text_field($args['validation_status']);
        }

        if (!empty($args['search'])) {
            $where[] = 'post_id IN (SELECT ID FROM %i WHERE post_title LIKE %s)';
            $params[] = $wpdb->posts;
            $params[] = '%' . $wpdb->esc_like($args['search']) . '%';
        }

        $where_clause = implode(' AND ', $where);

        // Requête avec limit et offset. La table de tête est le PREMIER marqueur, donc son
        // paramètre passe en tête de $params.
        $query = "SELECT * FROM %i WHERE {$where_clause}";
        array_unshift($params, $table);
        if (!empty($orderby)) {
            $query .= " ORDER BY {$orderby}";
        }
        $query .= " LIMIT %d OFFSET %d";
        $params[] = $per_page;
        $params[] = $offset;

        // $orderby goes through sanitize_sql_orderby() (a WordPress function) behind an
        // !empty guard; everything else — table names included — is handed to prepare().
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- ORDER BY sanitized with sanitize_sql_orderby(); every other dynamic part is a placeholder passed to $wpdb->prepare() on this very line.
        $results = $wpdb->get_results($wpdb->prepare($query, $params));

        return $results ?: array();
    }

    /**
     * Compte le nombre total de schémas selon des filtres.
     * @param array $args Arguments de filtrage.
     * @return int Nombre de schémas.
     */
    public function count_schemas( array $args = [] ): int {
        global $wpdb;
        $table = $this->get_table_name('schemas');

        // Construction de la requête WHERE
        $where = array('1=1');
        $params = array();

        if (!empty($args['schema_type'])) {
            $where[] = 'schema_type = %s';
            $params[] = sanitize_text_field($args['schema_type']);
        }

        // validation_status est dans validation_log (pas schemas) — filter via subquery JOIN.
        if (!empty($args['validation_status'])) {
            $where[]  = 'id IN (SELECT schema_id FROM %i WHERE validation_status = %s)';
            $params[] = $wpdb->prefix . 'aiforya_sm_validation_log';
            $params[] = sanitize_text_field($args['validation_status']);
        }

        if (!empty($args['search'])) {
            $where[] = 'post_id IN (SELECT ID FROM %i WHERE post_title LIKE %s)';
            $params[] = $wpdb->posts;
            $params[] = '%' . $wpdb->esc_like($args['search']) . '%';
        }

        $where_clause = implode(' AND ', $where);

        // $where_clause is built above from literal fragments only: every dynamic part is a
        // placeholder (%i for identifiers, %s/%d for values) and its argument is pushed to
        // $params in the same order. The query is therefore fully prepared below; the sniff
        // simply cannot follow a query string assembled in a variable.
        $query = "SELECT COUNT(*) FROM %i WHERE {$where_clause}";
        array_unshift($params, $table);

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $query is assembled from literal fragments and placeholders only, then passed to $wpdb->prepare() on this very line.
        $count = $wpdb->get_var($wpdb->prepare($query, $params));

        return (int) $count;
    }

    /**
     * Supprime le schéma associé à un post.
     * @param int $post_id ID du post.
     * @return bool Succès ou échec.
     */
    public function delete_schema_for_post( int $post_id = 0 ): bool {
        global $wpdb;
        $table = $this->get_table_name('schemas');

        $result = $wpdb->delete(
            $table,
            array('post_id' => $post_id),
            array('%d')
        );

        return $result !== false;
    }

    /**
     * Enregistre un événement de détection par l'IA.
     * @param array $data Données de détection (post_id, detected_type, etc.).
     * @return mixed ID du log ou erreur.
     */
    public function log_detection( array $data = [] ) {
        global $wpdb;
        $table = $this->get_table_name('detection_logs');

        // Validation des données requises
        if (empty($data['post_id'])) {
            return new WP_Error('missing_post_id', __('Post ID required.', 'aiforya-schema-markup'));
        }
        if (empty($data['detected_type'])) {
            return new WP_Error('missing_detected_type', __('Detected type required.', 'aiforya-schema-markup'));
        }

        $post_id = absint($data['post_id']);
        $detected_type = sanitize_text_field($data['detected_type']);
        $confidence = isset($data['confidence']) ? floatval($data['confidence']) : null;
        $ai_provider = isset($data['ai_provider']) ? sanitize_text_field($data['ai_provider']) : '';
        $prompt_used = isset($data['prompt_used']) ? $data['prompt_used'] : '';
        $raw_response = isset($data['raw_response']) ? $data['raw_response'] : '';

        $result = $wpdb->insert(
            $table,
            array(
                'post_id' => $post_id,
                'detected_type' => $detected_type,
                'confidence' => $confidence,
                'ai_provider' => $ai_provider,
                'prompt_used' => $prompt_used,
                'raw_response' => $raw_response,
                'created_at' => current_time('mysql'),
            ),
            array('%d', '%s', '%f', '%s', '%s', '%s', '%s')
        );

        if ($result === false) {
            return new WP_Error('db_insert_error', __('Error while saving the detection log.', 'aiforya-schema-markup'));
        }

        return (int) $wpdb->insert_id;
    }

    /**
     * Enregistre un résultat de validation Google.
     * @param array $data Données de validation (schema_id, status, response, etc.).
     * @return mixed ID du log ou erreur.
     */
    public function log_validation( array $data = [] ) {
        global $wpdb;
        $table = $this->get_table_name('validation_log');

        // Validation des données requises
        if (empty($data['schema_id'])) {
            return new WP_Error('missing_schema_id', __('Schema ID required.', 'aiforya-schema-markup'));
        }
        if (empty($data['status'])) {
            return new WP_Error('missing_status', __('Validation status required.', 'aiforya-schema-markup'));
        }

        $schema_id = absint($data['schema_id']);
        $status = sanitize_text_field($data['status']);
        $errors_count = isset($data['errors_count']) ? absint($data['errors_count']) : 0;
        $warnings_count = isset($data['warnings_count']) ? absint($data['warnings_count']) : 0;
        $response = isset($data['response']) ? $data['response'] : '';

        $result = $wpdb->insert(
            $table,
            array(
                'schema_id'           => $schema_id,
                'post_id'             => isset($data['post_id']) ? absint($data['post_id']) : 0,
                'validation_status'   => $status,
                'validation_response' => $response,
                'checked_at'          => current_time('mysql'),
            ),
            array('%d', '%d', '%s', '%s', '%s')
        );
        unset($errors_count, $warnings_count);

        if ($result === false) {
            return new WP_Error('db_insert_error', __('Error while saving the validation log.', 'aiforya-schema-markup'));
        }

        return (int) $wpdb->insert_id;
    }

    /**
     * Récupère le dernier log de validation pour un post.
     * @param int $post_id ID du post.
     * @return ?object Objet du log ou null.
     */
    public function get_last_validation_for_post( int $post_id = 0 ): ?object {
        global $wpdb;
        $schemas_table = $this->get_table_name('schemas');
        $validations_table = $this->get_table_name('validation_log');

        $validation = $wpdb->get_row($wpdb->prepare(
            "SELECT v.* FROM %i v
            INNER JOIN %i s ON v.schema_id = s.id
            WHERE s.post_id = %d
            ORDER BY v.checked_at DESC
            LIMIT 1", $validations_table, $schemas_table,
            $post_id
        ));

        return $validation ?: null;
    }

    /**
     * Récupère toutes les statistiques agrégées pour le tableau de bord.
     * @return array Tableau de statistiques.
     */
    public function get_dashboard_stats(  ): array {
        global $wpdb;
        $schemas_table = $this->get_table_name('schemas');
        $validations_table = $this->get_table_name('validation_log');

        // Total des schémas
        $total_schemas = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i", $schemas_table ) );

        // Schémas actifs (colonne `status` selon SQL Activator)
        $active_schemas = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status = 'active'", $schemas_table ) );

        // Schémas validés / en erreur : lus depuis validation_log (colonne validation_status n'est PAS sur schemas).
        $validation_log_table = $wpdb->prefix . 'aiforya_sm_validation_log';
        $validated_schemas = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(DISTINCT schema_id) FROM %i WHERE validation_status = 'valid'",
            $validation_log_table
        ) );
        $schemas_with_errors = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(DISTINCT schema_id) FROM %i WHERE validation_status = 'error'",
            $validation_log_table
        ) );

        // Répartition par type de schéma
        $types_distribution = $wpdb->get_results( $wpdb->prepare(
            "SELECT schema_type, COUNT(*) as count FROM %i GROUP BY schema_type ORDER BY count DESC",
            $schemas_table
        ), ARRAY_A );

        // Validations récentes (7 derniers jours)
        $recent_validations = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE checked_at >= %s", $validations_table,
            gmdate('Y-m-d H:i:s', strtotime('-7 days'))
        ));

        // Taux de succès des validations
        $total_validations = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i", $validations_table ) );
        $successful_validations = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE validation_status = 'valid'", $validations_table ) );
        $validation_success_rate = $total_validations > 0 ? round(($successful_validations / $total_validations) * 100, 2) : 0;

        // Posts sans schéma
        $total_posts = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE post_status = 'publish' AND post_type IN ('post', 'page', 'product')", $wpdb->posts ) );
        $posts_without_schema = $total_posts - $total_schemas;

        return array(
            'total_schemas' => $total_schemas,
            'active_schemas' => $active_schemas,
            'validated_schemas' => $validated_schemas,
            'schemas_with_errors' => $schemas_with_errors,
            'types_distribution' => $types_distribution ?: array(),
            'recent_validations' => $recent_validations,
            'validation_success_rate' => $validation_success_rate,
            'total_posts' => $total_posts,
            'posts_without_schema' => max(0, $posts_without_schema),
        );
    }

    /**
     * Récupère une liste de posts n'ayant pas encore de schéma.
     * @param int $limit Nombre maximum de posts à retourner.
     */
    public function get_posts_without_schema( int $limit = 0 ): array {
        global $wpdb;
        $schemas_table = $this->get_table_name('schemas');

        $query = $wpdb->prepare(
            "SELECT p.ID, p.post_title, p.post_type, p.post_date
            FROM %i p
            LEFT JOIN %i s ON p.ID = s.post_id
            WHERE p.post_status = 'publish'
            AND p.post_type IN ('post', 'page', 'product')
            AND s.id IS NULL
            ORDER BY p.post_date DESC
            LIMIT %d", $wpdb->posts, $schemas_table,
            $limit
        );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- requête préparée juste au-dessus, tables {$wpdb->posts} + constante.
        $posts = $wpdb->get_results($query);

        return $posts ?: array();
    }

    /**
     * Récupère les schémas nécessitant une revalidation.
     * @param int $limit Nombre maximum de schémas à retourner.
     */
    public function get_schemas_to_revalidate( int $limit = 0 ): array {
        global $wpdb;
        $schemas_table = $this->get_table_name('schemas');
        $validations_table = $this->get_table_name('validation_log');

        // Schémas qui nécessitent une revalidation :
        // - Pas de validation depuis 7 jours
        // - Ou dernière validation en erreur
        // - Ou jamais validés

        $query = $wpdb->prepare(
            "SELECT s.*,
            (SELECT MAX(v.checked_at) FROM %i v WHERE v.schema_id = s.id) as last_validation_date,
            (SELECT v.validation_status FROM %i v WHERE v.schema_id = s.id ORDER BY v.checked_at DESC LIMIT 1) as last_validation_status
            FROM %i s
            WHERE s.status = 'active'
            AND (
                (SELECT MAX(v.checked_at) FROM %i v WHERE v.schema_id = s.id) IS NULL
                OR (SELECT MAX(v.checked_at) FROM %i v WHERE v.schema_id = s.id) < %s
                OR (SELECT v.validation_status FROM %i v WHERE v.schema_id = s.id ORDER BY v.checked_at DESC LIMIT 1) = 'error'
            )
            ORDER BY s.updated_at ASC
            LIMIT %d", $validations_table, $validations_table, $schemas_table, $validations_table, $validations_table, $validations_table,
            gmdate('Y-m-d H:i:s', strtotime('-7 days')),
            $limit
        );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- requête préparée juste au-dessus, tables {$wpdb->posts} + constante.
        $schemas = $wpdb->get_results($query);

        return $schemas ?: array();
    }
}