<?php
/**
 * Database schema for FAQ Generator
 */

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Create tables for FAQ system.
 *
 * Dates are written by the plugin (current_time()), so the columns need no
 * CURRENT_TIMESTAMP default (not supported before MySQL 5.6.5).
 *
 * @param string $error Set to the last database error of dbDelta when a table is missing.
 * @return bool Whether every table exists afterwards.
 */
function dotes_faq_generator_create_tables(&$error = '') {
    global $wpdb;
    
    $charset_collate = $wpdb->get_charset_collate();
    
    // Categories table
    $table_categories = $wpdb->prefix . 'dotes_faq_categories';
    $sql_categories = "CREATE TABLE $table_categories (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        slug varchar(255) NOT NULL,
        description text,
        icon varchar(100),
        color varchar(7) DEFAULT '#075e54',
        sort_order int(11) DEFAULT 0,
        is_active tinyint(1) DEFAULT 1,
        created_at datetime DEFAULT NULL,
        updated_at datetime DEFAULT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY slug (slug),
        KEY sort_order (sort_order),
        KEY is_active (is_active)
    ) $charset_collate;";
    
    // FAQ groups table (for grouping multiple questions)
    $table_groups = $wpdb->prefix . 'dotes_faq_groups';
    $sql_groups = "CREATE TABLE $table_groups (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        category_id mediumint(9) NOT NULL,
        title varchar(255) NOT NULL,
        slug varchar(255) NOT NULL,
        description text,
        featured tinyint(1) DEFAULT 0,
        sort_order int(11) DEFAULT 0,
        is_active tinyint(1) DEFAULT 1,
        view_count int(11) DEFAULT 0,
        meta_data longtext,
        created_at datetime DEFAULT NULL,
        updated_at datetime DEFAULT NULL,
        PRIMARY KEY (id),
        KEY category_id (category_id),
        KEY slug (slug),
        KEY featured (featured),
        KEY sort_order (sort_order),
        KEY is_active (is_active),
        KEY view_count (view_count)
    ) $charset_collate;";

    // FAQ items table (individual questions within groups)
    $table_items = $wpdb->prefix . 'dotes_faq_items';
    $sql_items = "CREATE TABLE $table_items (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        category_id mediumint(9) NOT NULL,
        faq_group_id mediumint(9) DEFAULT NULL,
        question text NOT NULL,
        answer longtext NOT NULL,
        slug varchar(255) NOT NULL,
        featured tinyint(1) DEFAULT 0,
        sort_order int(11) DEFAULT 0,
        is_active tinyint(1) DEFAULT 1,
        view_count int(11) DEFAULT 0,
        helpful_count int(11) DEFAULT 0,
        not_helpful_count int(11) DEFAULT 0,
        meta_data longtext,
        created_at datetime DEFAULT NULL,
        updated_at datetime DEFAULT NULL,
        PRIMARY KEY (id),
        KEY category_id (category_id),
        KEY faq_group_id (faq_group_id),
        KEY slug (slug),
        KEY featured (featured),
        KEY sort_order (sort_order),
        KEY is_active (is_active),
        KEY view_count (view_count)
    ) $charset_collate;";
    
    // Helpful votes table
    $table_helpful = $wpdb->prefix . 'dotes_faq_helpful';
    $sql_helpful = "CREATE TABLE $table_helpful (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        faq_id mediumint(9) NOT NULL,
        item_id mediumint(9) DEFAULT NULL,
        user_id mediumint(9) DEFAULT NULL,
        ip_address varchar(45) NOT NULL,
        vote_type enum('helpful','not_helpful') NOT NULL,
        created_at datetime DEFAULT NULL,
        PRIMARY KEY (id),
        KEY faq_id (faq_id),
        KEY item_id (item_id),
        KEY user_id (user_id),
        KEY ip_address (ip_address),
        KEY vote_type (vote_type),
        UNIQUE KEY unique_user_vote (faq_id, item_id, user_id),
        UNIQUE KEY unique_ip_vote (faq_id, item_id, ip_address)
    ) $charset_collate;";
    
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    
    // The error is read right after each dbDelta (the table check below resets it).
    $errors = array();
    foreach (array($sql_categories, $sql_groups, $sql_items, $sql_helpful) as $sql) {
        dbDelta($sql);
        if ('' !== $wpdb->last_error) {
            $errors[] = $wpdb->last_error;
        }
    }
    // Up to 1.1 this plugin also created dotes_faq_analytics and dotes_faq_targeting;
    // they belong to the Pro add-on now and are left untouched here.
    
    foreach (array($table_categories, $table_groups, $table_items, $table_helpful) as $table) {
        if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($table))) !== $table) {
            $error = implode(' | ', array_unique($errors));
            return false;
        }
    }
    
    // Insert default categories
    $default_categories = array(
        array(
            'name' => 'Geral',
            'slug' => 'geral',
            'description' => 'Perguntas gerais',
            'icon' => 'fas fa-question-circle',
            'color' => '#075e54',
            'sort_order' => 1
        ),
        array(
            'name' => 'Suporte',
            'slug' => 'suporte',
            'description' => 'Perguntas de suporte técnico',
            'icon' => 'fas fa-life-ring',
            'color' => '#25d366',
            'sort_order' => 2
        ),
        array(
            'name' => 'Vendas',
            'slug' => 'vendas',
            'description' => 'Perguntas sobre vendas e preços',
            'icon' => 'fas fa-shopping-cart',
            'color' => '#0073aa',
            'sort_order' => 3
        )
    );
    
    foreach ($default_categories as $category) {
        $exists = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM %i WHERE slug = %s",
            $table_categories,
            $category['slug']
        ));
        
        if (!$exists) {
            $wpdb->insert($table_categories, $category + array('created_at' => current_time('mysql'), 'updated_at' => current_time('mysql')));
        }
    }
    
    // Add performance optimization indexes
    dotes_faq_generator_add_performance_indexes();
    
    return true;
}

/**
 * Add critical performance indexes based on common query patterns
 * PERFORMANCE OPTIMIZATION: Critical indexes for the most common queries
 */
function dotes_faq_generator_add_performance_indexes() {
    global $wpdb;
    
    $table_items = $wpdb->prefix . 'dotes_faq_items';
    $table_groups = $wpdb->prefix . 'dotes_faq_groups';
    
    // Get existing indexes to avoid duplicates
    $existing_indexes = array();
    $indexes_query = $wpdb->get_results($wpdb->prepare("SHOW INDEX FROM %i WHERE Key_name != 'PRIMARY'", $table_items));
    foreach ($indexes_query as $index) {
        $existing_indexes[$table_items][] = $index->Key_name;
    }
    
    $indexes_query = $wpdb->get_results($wpdb->prepare("SHOW INDEX FROM %i WHERE Key_name != 'PRIMARY'", $table_groups));
    foreach ($indexes_query as $index) {
        $existing_indexes[$table_groups][] = $index->Key_name;
    }
    
    // Critical indexes for FAQ items table
    $performance_indexes = array(
        // For render_faq_shortcode() queries - most critical
        "$table_items" => array(
            'idx_active_sort' => "ADD INDEX idx_active_sort (is_active, sort_order)",
            'idx_active_featured' => "ADD INDEX idx_active_featured (is_active, featured, sort_order)",
            'idx_group_active' => "ADD INDEX idx_group_active (faq_group_id, is_active)",
            'idx_category_active' => "ADD INDEX idx_category_active (category_id, is_active, sort_order)",
        ),
        
        // For admin DataTables and stats
        "$table_groups" => array(
            'idx_category_active_sort' => "ADD INDEX idx_category_active_sort (category_id, is_active, sort_order)",
            'idx_featured_active' => "ADD INDEX idx_featured_active (featured, is_active, sort_order)",
            'idx_view_count_desc' => "ADD INDEX idx_view_count_desc (view_count DESC)", // For popular FAQs
        )
    );
    
    // Add indexes that don't already exist
    foreach ($performance_indexes as $table => $indexes) {
        foreach ($indexes as $index_name => $index_sql) {
            // Check if index already exists
            if (!isset($existing_indexes[$table]) || !in_array($index_name, $existing_indexes[$table], true)) {
                // $index_sql is a fixed string defined above; only the table name is dynamic.
                $wpdb->query($wpdb->prepare("ALTER TABLE %i " . $index_sql, $table)); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            }
        }
    }
}

/**
 * Run the installer/upgrade routine only when the stored DB version differs.
 *
 * Cheap on every request (one autoloaded option compare); the heavy work runs
 * on activation or after the plugin files are updated.
 */
function dotes_faq_generator_check_database() {
    // After a failed install, retry once an hour (or on reactivation) instead of on every request.
    if (get_option('dotes_faq_generator_db_version', '0') === DOTES_FAQ_GENERATOR_DB_VERSION || get_transient('dotes_faq_generator_db_retry')) {
        return;
    }
    dotes_faq_generator_upgrade();
}

/**
 * Admin notice shown while the tables could not be created.
 */
function dotes_faq_generator_database_notice() {
    $error = get_option('dotes_faq_generator_db_error');
    if (false === $error || !current_user_can('activate_plugins')) {
        return;
    }
    echo '<div class="notice notice-error"><p>';
    echo esc_html__('Dotes FAQ Generator could not create its database tables, so FAQs cannot be saved. Please check that the database user can create tables, then deactivate and activate the plugin again.', 'dotes-faq-generator');
    if ('' !== (string) $error) {
        echo '<br><code>' . esc_html((string) $error) . '</code>';
    }
    echo '</p></div>';
}
add_action('admin_notices', 'dotes_faq_generator_database_notice');

/**
 * Install or upgrade tables and stored data.
 *
 * The stored DB version only changes when every table exists; otherwise the
 * error is kept for the admin notice.
 */
function dotes_faq_generator_upgrade() {
    global $wpdb;

    $installed = get_option('dotes_faq_generator_db_version', '0');

    // 1.6.0: items.slug was UNIQUE in old installs; the schema declares a plain KEY
    // and dbDelta failed with "Duplicate key name 'slug'" on every run.
    $items = $wpdb->prefix . 'dotes_faq_items';
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($items))) === $items) {
        $unique_slug = $wpdb->get_var($wpdb->prepare("SHOW INDEX FROM %i WHERE Key_name = 'slug' AND Non_unique = 0", $items));
        if ($unique_slug) {
            $wpdb->query($wpdb->prepare('ALTER TABLE %i DROP INDEX slug', $items));
        }
    }

    $error = '';
    if (!dotes_faq_generator_create_tables($error)) {
        update_option('dotes_faq_generator_db_error', $error, false);
        set_transient('dotes_faq_generator_db_retry', 1, HOUR_IN_SECONDS);
        return;
    }
    delete_option('dotes_faq_generator_db_error');
    delete_transient('dotes_faq_generator_db_retry');

    if (version_compare($installed, '1.6.0', '<')) {
        dotes_faq_generator_upgrade_1_6_0();
    }
    
    if (version_compare($installed, '1.7.0', '<')) {
        dotes_faq_generator_upgrade_1_7_0('0' !== $installed);
    }
    
    if (version_compare($installed, '1.9.0', '<')) {
        dotes_faq_generator_upgrade_1_9_0();
    }
    
    if (version_compare($installed, '1.10.0', '<')) {
        dotes_faq_generator_upgrade_1_10_0();
    }

    update_option('dotes_faq_generator_db_version', DOTES_FAQ_GENERATOR_DB_VERSION);
}

/**
 * 1.6.0: remove licence data, orphan test/debug options, public export files;
 * anonymise stored IP addresses.
 */
function dotes_faq_generator_upgrade_1_6_0() {
    global $wpdb;

    $settings = get_option('dotes_faq_generator_settings', array());
    if (is_array($settings)) {
        foreach (array('dotes_base_license_key', 'dotes_base_email_license', 'license_status', 'license_expira_em', 'license_tipo') as $key) {
            unset($settings[$key]);
        }
        if (!isset($settings['ip_collection'])) {
            $settings['ip_collection'] = 'anonymize';
        }
        update_option('dotes_faq_generator_settings', $settings);
    }

    foreach (array('dotes_faq_debug_logs', 'dotes_faq_license_status', 'dotes_faq_generator_indexes_added', 'dotes_faq_generator_version') as $option) {
        delete_option($option);
    }
    // Orphan options left by removed test suites / old storage (not read by any code).
    $orphans = $wpdb->get_col($wpdb->prepare(
        "SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE %s OR option_name LIKE %s",
        $wpdb->esc_like('dotes_faq_meta_') . '%',
        $wpdb->esc_like('dotes_faq_taxonomy_') . '%'
    ));
    foreach ((array) $orphans as $option) {
        delete_option($option);
    }
    delete_transient('dotes_faq_generator_ultima_validacao_licenca');

    // Exports used to be written to the public uploads folder.
    $uploads = wp_upload_dir(null, false);
    if (empty($uploads['error'])) {
        $patterns = array('dotes-faq-backup-*.json', 'faq-analytics-*.csv', 'faq-analytics-*.json');
        foreach ($patterns as $pattern) {
            foreach (array_merge((array) glob($uploads['basedir'] . '/' . $pattern), (array) glob($uploads['basedir'] . '/*/*/' . $pattern)) as $file) {
                if ($file && is_file($file)) {
                    wp_delete_file($file);
                }
            }
        }
    }

    if (class_exists('Dotes_FAQ_Privacy')) {
        Dotes_FAQ_Privacy::anonymize_stored_ips();
    }
}

// Note: Hook for activation is registered in main plugin file

/**
 * 1.7.0: Google Fonts became opt-in (external service). Existing installs keep
 * loading them; settings that no longer exist are dropped.
 *
 * @param bool $existing_install Whether the plugin was installed before.
 */
function dotes_faq_generator_upgrade_1_7_0($existing_install) {
    $settings = get_option('dotes_faq_generator_settings', array());
    if (!is_array($settings) || empty($settings)) {
        return;
    }
    // "google_fonts" is read by the Pro add-on (per-FAQ fonts) since 1.2.
    if ($existing_install && !isset($settings['google_fonts'])) {
        $settings['google_fonts'] = true;
    }
    foreach (array('enable_cache', 'cache_duration', 'enable_ajax', 'default_category', 'debug_mode') as $key) {
        unset($settings[$key]);
    }
    update_option('dotes_faq_generator_settings', $settings);
}

/**
 * 1.9.0: drop the prefix index on items.meta_data (no query could use it: the
 * font lookup is a LIKE '%...%') and the option that only recorded the indexes.
 */
function dotes_faq_generator_upgrade_1_9_0() {
    global $wpdb;

    $items = $wpdb->prefix . 'dotes_faq_items';
    if ($wpdb->get_var($wpdb->prepare("SHOW INDEX FROM %i WHERE Key_name = 'idx_meta_fonts'", $items))) {
        $wpdb->query($wpdb->prepare('ALTER TABLE %i DROP INDEX idx_meta_fonts', $items));
    }
    delete_option('dotes_faq_performance_indexes_applied');
}

/**
 * 1.10.0: the "custom CSS" setting was removed (WordPress.org guidelines); its
 * content moves to the Additional CSS of the active theme.
 */
function dotes_faq_generator_upgrade_1_10_0() {
    $settings = get_option('dotes_faq_generator_settings', array());
    if (!is_array($settings) || !array_key_exists('custom_css', $settings)) {
        return;
    }
    $css = trim(wp_strip_all_tags((string) $settings['custom_css']));
    if ('' !== $css && function_exists('wp_update_custom_css_post')) {
        $current = (string) wp_get_custom_css();
        if (false === strpos($current, $css)) {
            wp_update_custom_css_post(trim($current . "\n\n/* Dotes FAQ Generator */\n" . $css));
        }
    }
    unset($settings['custom_css']);
    update_option('dotes_faq_generator_settings', $settings);
}