<?php
/**
 * Aiforya_Faq_Generator_Db — service database (version gratuite, sans IA).
 *
 * Une seule table : {prefix}aiforya_fg_faqs (une FAQ par article/page).
 * Chaque FAQ est stockée en JSON schema.org FAQPage (mainEntity[]).
 * Aucune table de coûts/tokens IA, aucun réglage de clé API.
 *
 * @package aiforya-faq-generator
 */

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

if ( class_exists( 'Aiforya_Faq_Generator_Db', false ) ) {
    return;
}

class Aiforya_Faq_Generator_Db {

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

    const TABLE_FAQS = 'aiforya_fg_faqs';

    public static function get_instance(): self {
        if ( self::$instance === null ) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    public function __construct() {}

    // =========================================================================
    // Schéma — création de la table (appelé par l'Activator).
    // =========================================================================

    public function create_tables(): void {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();
        $table           = $this->get_table_name( 'faqs' );

        $sql = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            post_id BIGINT UNSIGNED NOT NULL,
            content_json LONGTEXT NOT NULL,
            source_content_hash VARCHAR(64) NOT NULL DEFAULT '',
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY post_id_unique (post_id)
        ) {$charset_collate};";

        dbDelta( $sql );
    }

    public function drop_tables(): void {
        global $wpdb;
        $table = $this->get_table_name( 'faqs' );
        $wpdb->query( "DROP TABLE IF EXISTS {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery
    }

    /**
     * Nom complet de la table avec le préfixe WordPress.
     *
     * @param string $name Nom court ('faqs').
     */
    public function get_table_name( string $name = 'faqs' ): string {
        global $wpdb;
        $map = [
            'faqs' => $wpdb->prefix . self::TABLE_FAQS,
        ];
        return $map[ $name ] ?? '';
    }

    // =========================================================================
    // Réglages d'affichage (option API standard WP).
    // =========================================================================

    public static function get_default_settings(): array {
        return [
            'position'       => 'manual',   // manual | after | before
            'layout'         => 'accordion',
            'default_title'  => '',
            'color_primary'  => '#2271b1',
            'color_bg'       => '#f8f9fa',
            'color_text'     => '#4a5568',
            'color_question' => '#1a202c',
        ];
    }

    public function get_settings(): array {
        $stored = get_option( 'aiforya_fg_settings', [] );
        if ( ! is_array( $stored ) ) {
            $stored = [];
        }
        return wp_parse_args( $stored, self::get_default_settings() );
    }

    public function save_settings( array $settings ): bool {
        $merged = wp_parse_args( $settings, $this->get_settings() );
        return update_option( 'aiforya_fg_settings', $merged, true );
    }

    public function get_setting( string $key, $default = null ) {
        $settings = $this->get_settings();
        return $settings[ $key ] ?? $default;
    }

    public function update_setting( string $key, $value ): bool {
        $settings = $this->get_settings();
        $settings[ $key ] = $value;
        return $this->save_settings( $settings );
    }

    // =========================================================================
    // FAQ CRUD (une FAQ par post_id).
    // =========================================================================

    /**
     * Enregistre ou met à jour la FAQ d'un article.
     *
     * @param int    $post_id      ID de l'article.
     * @param string $content_json Contenu au format JSON schema.org FAQPage.
     * @param string $source_hash  Hash du contenu source (optionnel).
     * @return int|WP_Error ID de la ligne ou erreur.
     */
    public function save_faq( int $post_id = 0, string $content_json = '', string $source_hash = '' ) {
        global $wpdb;
        $table = $this->get_table_name( 'faqs' );
        if ( $post_id <= 0 || '' === $table ) {
            return new WP_Error( 'invalid_args', __( 'Invalid post.', 'aiforya-faq-generator' ) );
        }

        $existing = $this->get_faq_by_post_id( $post_id );
        $now      = current_time( 'mysql' );

        if ( $existing ) {
            $result = $wpdb->update(
                $table,
                [
                    'content_json'        => $content_json,
                    'source_content_hash' => $source_hash,
                    'updated_at'          => $now,
                ],
                [ 'post_id' => $post_id ],
                [ '%s', '%s', '%s' ],
                [ '%d' ]
            );
            if ( false === $result ) {
                return new WP_Error( 'db_error', __( 'Update error.', 'aiforya-faq-generator' ) );
            }
            return (int) $existing->id;
        }

        $result = $wpdb->insert(
            $table,
            [
                'post_id'             => $post_id,
                'content_json'        => $content_json,
                'source_content_hash' => $source_hash,
                'created_at'          => $now,
                'updated_at'          => $now,
            ],
            [ '%d', '%s', '%s', '%s', '%s' ]
        );
        if ( false === $result ) {
            return new WP_Error( 'db_error', __( 'Insertion error.', 'aiforya-faq-generator' ) );
        }
        return (int) $wpdb->insert_id;
    }

    public function get_faq_by_post_id( int $post_id = 0 ): ?object {
        global $wpdb;
        $table = $this->get_table_name( 'faqs' );
        if ( $post_id <= 0 || '' === $table ) {
            return null;
        }
        $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE post_id = %d LIMIT 1", $table, $post_id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $row ?: null;
    }

    public function delete_faq_by_post_id( int $post_id = 0 ): bool {
        global $wpdb;
        $table = $this->get_table_name( 'faqs' );
        if ( $post_id <= 0 || '' === $table ) {
            return false;
        }
        $result = $wpdb->delete( $table, [ 'post_id' => $post_id ], [ '%d' ] );
        return false !== $result && $result > 0;
    }

    /**
     * Liste paginée des FAQ enregistrées (pour l'éditeur manuel).
     *
     * @return array{items: object[], total: int}
     */
    public function get_faqs_paginated( int $page = 1, int $per_page = 20 ): array {
        global $wpdb;
        $table = $this->get_table_name( 'faqs' );
        if ( '' === $table ) {
            return [ 'items' => [], 'total' => 0 ];
        }
        $page     = max( 1, $page );
        $per_page = max( 1, min( 100, $per_page ) );
        $offset   = ( $page - 1 ) * $per_page;

        $items = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM %i ORDER BY updated_at DESC LIMIT %d OFFSET %d", $table,
            $per_page,
            $offset
        ) );
        $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        return [ 'items' => $items ?: [], 'total' => $total ];
    }

    // =========================================================================
    // Compteurs (tableau de bord).
    // =========================================================================

    public function count_faqs(): int {
        global $wpdb;
        $table = $this->get_table_name( 'faqs' );
        if ( '' === $table ) {
            return 0;
        }
        return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    public function count_posts_with_faqs(): int {
        global $wpdb;
        $table = $this->get_table_name( 'faqs' );
        if ( '' === $table ) {
            return 0;
        }
        return (int) $wpdb->get_var( "SELECT COUNT(DISTINCT post_id) FROM {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    /**
     * Nombre total de questions cumulées sur toutes les FAQ.
     */
    public function count_total_questions(): int {
        global $wpdb;
        $table = $this->get_table_name( 'faqs' );
        if ( '' === $table ) {
            return 0;
        }
        $rows  = $wpdb->get_col( "SELECT content_json FROM {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $total = 0;
        foreach ( $rows ?: [] as $json ) {
            $data = json_decode( (string) $json, true );
            if ( ! empty( $data['mainEntity'] ) && is_array( $data['mainEntity'] ) ) {
                $total += count( $data['mainEntity'] );
            }
        }
        return $total;
    }

    /**
     * N dernières FAQ enregistrées (tableau de bord — activité récente).
     */
    public function get_recent_faqs( int $limit = 5 ): array {
        global $wpdb;
        $table = $this->get_table_name( 'faqs' );
        if ( '' === $table ) {
            return [];
        }
        $limit = max( 1, min( 50, $limit ) );
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM %i ORDER BY updated_at DESC LIMIT %d", $table,
            $limit
        ) ) ?: [];
    }
}