<?php
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * DB helpers for newssync item -> post mapping (indexed table implementation).
 *
 * Estratégia:
 * 1. Criar tabela otimizada com índices corretos
 * 2. Cache agressivo em TODAS as queries
 * 3. Fallback automático para postmeta se tabela falhar
 * 4. Logging de erros para debug
 */

if ( ! function_exists( 'newssync_get_item_map_table_name' ) ) {
    function newssync_get_item_map_table_name() {
        global $wpdb;
        return $wpdb->prefix . 'newssync_item_map';
    }
}

if ( ! function_exists( 'newssync_table_exists' ) ) {
    /**
     * Verifica se a tabela existe (com cache).
     * Cache de 5 minutos para evitar queries repetidas.
     */
    function newssync_table_exists() {
        $cache_key = 'newssync_table_exists_check';
        $cached = wp_cache_get( $cache_key, 'newssync' );

        if ( false !== $cached ) {
            return (bool) $cached;
        }

        global $wpdb;
        $table = newssync_get_item_map_table_name();

        // Query preparada e segura
        $sql = $wpdb->prepare( "SHOW TABLES LIKE %s", $wpdb->esc_like( $table ) );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery -- Query is prepared above, SHOW TABLES required to check custom table existence, result is cached
        $res = $wpdb->get_var( $sql );
        $exists = ( (string) $res === (string) $table );

        // Cache por 5 minutos
        wp_cache_set( $cache_key, $exists, 'newssync', 5 * MINUTE_IN_SECONDS );

        return $exists;
    }
}

if ( ! function_exists( 'newssync_create_item_map_table' ) ) {
    /**
     * Cria a tabela otimizada com tratamento de erros completo.
     *
     * @return array Resultado estruturado: ['success' => bool, 'error' => string, 'created' => bool]
     */
    function newssync_create_item_map_table() {
        global $wpdb;

        $result = array(
            'success' => false,
            'error'   => '',
            'created' => false,
        );

        // Limpar cache antes de tentar criar
        wp_cache_delete( 'newssync_table_exists_check', 'newssync' );

        // Verificar se já existe
        if ( newssync_table_exists() ) {
            $result['success'] = true;
            $result['created'] = true;
            return $result;
        }

        $table_name = newssync_get_item_map_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        // SQL otimizado com índices corretos
        $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            guid VARCHAR(191) NOT NULL,
            post_id BIGINT UNSIGNED NOT NULL,
            feed_slug VARCHAR(128) DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY guid_unique (guid),
            KEY post_id_idx (post_id),
            KEY feed_slug_idx (feed_slug)
        ) {$charset_collate};";

        // Incluir upgrade.php
        if ( ! function_exists( 'dbDelta' ) ) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }

        // Limpar last_error antes
        $wpdb->flush();
        $wpdb->last_error = '';

        try {
            // Executar dbDelta
            $delta_result = dbDelta( $sql );

            // Verificar se realmente foi criada
            wp_cache_delete( 'newssync_table_exists_check', 'newssync' );

            if ( newssync_table_exists() ) {
                $result['success'] = true;
                $result['created'] = true;

                // Limpar erro anterior se houver
                delete_option( 'newssync_last_table_error' );

                // Log de sucesso
                if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
                    // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging only when WP_DEBUG is enabled
                    error_log( '[NewsSync] Tabela otimizada criada com sucesso: ' . $table_name );
                }

                return $result;
            }

            // Se não foi criada, capturar erro
            $db_error = $wpdb->last_error;

            if ( empty( $db_error ) ) {
                // Verificar se é problema de permissões
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Testing database permissions during table creation
                $test_query = $wpdb->query( "SHOW TABLES" );
                if ( false === $test_query ) {
                    $db_error = 'Database connection error or insufficient privileges';
                } else {
                    // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_print_r -- Debug output for table creation diagnostics
                    $db_error = 'Table creation failed for unknown reason. dbDelta result: ' . print_r( $delta_result, true );
                }
            }

            $result['error'] = $db_error;
            update_option( 'newssync_last_table_error', substr( $db_error, 0, 2000 ) );

            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging only when WP_DEBUG is enabled
                error_log( '[NewsSync] Erro ao criar tabela: ' . $db_error );
            }

        } catch ( Exception $e ) {
            $result['error'] = 'Exception: ' . $e->getMessage();
            update_option( 'newssync_last_table_error', substr( $result['error'], 0, 2000 ) );

            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging only when WP_DEBUG is enabled
                error_log( '[NewsSync] Exception ao criar tabela: ' . $e->getMessage() );
            }
        }

        return $result;
    }
}

if ( ! function_exists( 'newssync_find_post_id_by_guid' ) ) {
    /**
     * Find post_id by GUID com cache agressivo.
     *
     * Estratégia:
     * 1. Verificar cache primeiro (6h)
     * 2. Usar tabela indexada se existir
     * 3. Fallback para postmeta com query preparada
     *
     * @param string $guid
     * @return int|null Post ID ou null se não encontrado
     */
    function newssync_find_post_id_by_guid( $guid ) {
        global $wpdb;

        $guid = sanitize_text_field( (string) $guid );
        if ( '' === $guid ) {
            return null;
        }

        // Cache layer 1: WordPress object cache
        $cache_key = 'newssync_guid_' . md5( $guid );
        $cached = wp_cache_get( $cache_key, 'newssync' );

        if ( false !== $cached ) {
            return $cached > 0 ? (int) $cached : null;
        }

        // Cache layer 2: Transient (para object-cache desabilitado)
        $transient_key = 'newssync_guid_map_' . md5( $guid );
        $cached_transient = get_transient( $transient_key );

        if ( false !== $cached_transient ) {
            $post_id = $cached_transient > 0 ? (int) $cached_transient : null;
            wp_cache_set( $cache_key, $post_id ? $post_id : 0, 'newssync', 6 * HOUR_IN_SECONDS );
            return $post_id;
        }

        $post_id = null;

        // Preferir tabela indexada
        if ( newssync_table_exists() ) {
            $table = newssync_get_item_map_table_name();

            // Query preparada e segura
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safely generated by newssync_get_item_map_table_name()
            $sql = $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- {$table} safely generated by newssync_get_item_map_table_name()
                "SELECT post_id FROM {$table} WHERE guid = %s LIMIT 1",
                $guid
            );

            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is prepared above, custom table lookup for optimized GUID mapping, result is cached
            $res = $wpdb->get_var( $sql );
            $post_id = $res ? (int) $res : null;

        } else {
            // Fallback: postmeta (query preparada)
            $sql = $wpdb->prepare(
                "SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = %s AND meta_value = %s LIMIT 1",
                '_newssync_guid',
                $guid
            );

            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is prepared above, fallback postmeta lookup when custom table unavailable, result is cached
            $res = $wpdb->get_var( $sql );
            $post_id = $res ? (int) $res : null;
        }

        // Cachear resultado (incluindo misses como 0)
        $cache_value = $post_id ? $post_id : 0;
        wp_cache_set( $cache_key, $cache_value, 'newssync', 6 * HOUR_IN_SECONDS );
        set_transient( $transient_key, $cache_value, 6 * HOUR_IN_SECONDS );

        return $post_id;
    }
}

if ( ! function_exists( 'newssync_find_post_id_by_original_url' ) ) {
    /**
     * Find post_id by original URL com cache.
     *
     * @param string $url
     * @return int|null Post ID ou null
     */
    function newssync_find_post_id_by_original_url( $url ) {
        global $wpdb;

        $url = (string) $url;
        if ( '' === $url ) {
            return null;
        }

        // Cache
        $cache_key = 'newssync_url_' . md5( $url );
        $cached = wp_cache_get( $cache_key, 'newssync' );

        if ( false !== $cached ) {
            return $cached > 0 ? (int) $cached : null;
        }

        // Query preparada
        $sql = $wpdb->prepare(
            "SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = %s AND meta_value = %s LIMIT 1",
            '_newssync_original_url',
            $url
        );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is prepared above, postmeta lookup for duplicate detection, result is cached
        $res = $wpdb->get_var( $sql );
        $post_id = $res ? (int) $res : null;

        // Cachear
        $cache_value = $post_id ? $post_id : 0;
        wp_cache_set( $cache_key, $cache_value, 'newssync', 6 * HOUR_IN_SECONDS );

        return $post_id;
    }
}

if ( ! function_exists( 'newssync_map_guid_to_post' ) ) {
    /**
     * Mapear guid -> post_id com fallback automático.
     *
     * @param string $guid
     * @param int $post_id
     * @param string|null $feed
     * @return bool Sucesso
     */
    function newssync_map_guid_to_post( $guid, $post_id, $feed = null ) {
        global $wpdb;

        $guid = (string) $guid;
        $post_id = (int) $post_id;
        $feed = $feed === null ? null : sanitize_text_field( (string) $feed );

        if ( empty( $guid ) || $post_id <= 0 ) {
            return false;
        }

        // Invalidar caches
        $cache_key = 'newssync_guid_' . md5( $guid );
        wp_cache_delete( $cache_key, 'newssync' );
        delete_transient( 'newssync_guid_map_' . md5( $guid ) );

        // Tentar tabela indexada
        if ( newssync_table_exists() ) {
            $table = newssync_get_item_map_table_name();

            $data = array(
                'guid'      => $guid,
                'post_id'   => $post_id,
                'feed_slug' => $feed,
            );

            $formats = array( '%s', '%d', '%s' );

            // REPLACE é seguro aqui (upsert)
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- REPLACE into custom table for optimized GUID mapping
            $res = $wpdb->replace( $table, $data, $formats );

            if ( false !== $res ) {
                return true;
            }

            // Se falhou, log e continua para fallback
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging only when WP_DEBUG is enabled
                error_log( '[NewsSync] REPLACE falhou na tabela: ' . $wpdb->last_error );
            }
        }

        // Fallback: postmeta (sempre funciona)
        update_post_meta( $post_id, '_newssync_guid', sanitize_text_field( $guid ) );
        return true;
    }
}

if ( ! function_exists( 'newssync_clear_guid_cache' ) ) {
    /**
     * Helper para limpar cache de um GUID específico.
     * Útil ao apagar posts.
     */
    function newssync_clear_guid_cache( $guid ) {
        if ( empty( $guid ) ) {
            return;
        }

        $cache_key = 'newssync_guid_' . md5( (string) $guid );
        wp_cache_delete( $cache_key, 'newssync' );
        delete_transient( 'newssync_guid_map_' . md5( (string) $guid ) );
    }
}
