<?php
/**
 * Handles all database interactions for the plugin.
 *
 * @since      1.0.0
 * @package    Advanced_Redirect_Manager
 * @subpackage Advanced_Redirect_Manager/includes
 */
class NCB_ARM_DB {

    private $table_name;

    public function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'ncb_arm_redirects';

        // Register AJAX handlers
        add_action('wp_ajax_ncb_arm_get_redirects', array($this, 'ajax_get_redirects'));
        add_action('wp_ajax_ncb_arm_save_redirect', array($this, 'ajax_save_redirect'));
        add_action('wp_ajax_ncb_arm_delete_redirect', array($this, 'ajax_delete_redirect'));
        add_action('wp_ajax_ncb_arm_smart_search', array($this, 'ajax_smart_search'));

        $this->maybe_add_qs_column();
        $this->maybe_expand_url_columns();
        $this->maybe_migrate_original_mode();
        $this->maybe_migrate_removed_original();
    }

    /**
     * Convert removed 'original' mode to 'ignore' (non-destructive).
     */
    private function maybe_migrate_removed_original() {
        global $wpdb;

        if ( get_option( 'ncb_arm_migrated_removed_original', false ) ) {
            return;
        }

        $table_exists = $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $this->table_name ) ) === $this->table_name;
        if ( ! $table_exists ) {
            return;
        }

        $wpdb->query( "UPDATE {$this->table_name} SET query_string_mode = 'ignore' WHERE query_string_mode = 'original'" );

        if ( ! $wpdb->last_error ) {
            update_option( 'ncb_arm_migrated_removed_original', 1, false );
        } else {
            error_log( 'ARM DB migration failed (convert original mode): ' . $wpdb->last_error );
        }
    }

    /**
     * Convert legacy 'original' mode to 'ignore' (non-destructive).
     */
    private function maybe_migrate_original_mode() {
        global $wpdb;

        if ( get_option( 'ncb_arm_migrated_original_mode', false ) ) {
            return;
        }

        $table_exists = $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $this->table_name ) ) === $this->table_name;
        if ( ! $table_exists ) {
            return;
        }

        $wpdb->query( "UPDATE {$this->table_name} SET query_string_mode = 'ignore' WHERE query_string_mode = 'original'" );

        if ( ! $wpdb->last_error ) {
            update_option( 'ncb_arm_migrated_original_mode', 1, false );
        } else {
            error_log( 'ARM DB migration failed (convert original mode): ' . $wpdb->last_error );
        }
    }

    /**
     * Ensure url_from and url_to are TEXT for long URLs (non-destructive).
     */
    private function maybe_expand_url_columns() {
        global $wpdb;

        if ( get_option( 'ncb_arm_redirects_url_text', false ) ) {
            return;
        }

        $table_exists = $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $this->table_name ) ) === $this->table_name;
        if ( ! $table_exists ) {
            return;
        }

        $wpdb->query( "ALTER TABLE {$this->table_name} MODIFY url_from TEXT NOT NULL" );
        if ( $wpdb->last_error ) {
            error_log( 'ARM DB migration failed (expand url_from): ' . $wpdb->last_error );
            return;
        }

        $wpdb->query( "ALTER TABLE {$this->table_name} MODIFY url_to TEXT NOT NULL" );
        if ( $wpdb->last_error ) {
            error_log( 'ARM DB migration failed (expand url_to): ' . $wpdb->last_error );
            return;
        }

        // Verify concretely that both columns are TEXT.
        $cols = $wpdb->get_results( "SHOW COLUMNS FROM {$this->table_name}" );
        $from_ok = false;
        $to_ok   = false;

        foreach ( $cols as $c ) {
            if ( 'url_from' === $c->Field && stripos( $c->Type, 'text' ) === 0 ) {
                $from_ok = true;
            }
            if ( 'url_to' === $c->Field && stripos( $c->Type, 'text' ) === 0 ) {
                $to_ok = true;
            }
        }

        if ( ! $from_ok || ! $to_ok ) {
            error_log( 'ARM DB migration failed: url_from/url_to not TEXT after ALTER.' );
            return;
        }

        update_option( 'ncb_arm_redirects_url_text', 1, false );
    }

    /**
     * Ensure the query_string_mode column exists for older installs.
     */
    private function maybe_add_qs_column() {
        global $wpdb;

        if ( get_option( 'ncb_arm_redirects_has_qs_mode', false ) ) {
            return;
        }

        $table_exists = $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $this->table_name ) ) === $this->table_name;
        if ( ! $table_exists ) {
            return;
        }

        $col = $wpdb->get_results( "SHOW COLUMNS FROM {$this->table_name} LIKE 'query_string_mode'" );
        if ( empty( $col ) ) {
            $wpdb->query( "ALTER TABLE {$this->table_name} ADD COLUMN query_string_mode VARCHAR(20) NOT NULL DEFAULT 'ignore'" );

            if ( $wpdb->last_error ) {
                error_log( 'ARM DB migration failed (add query_string_mode): ' . $wpdb->last_error );
                return;
            }
        }

        // Verify concretely that the column exists.
        $col = $wpdb->get_results( "SHOW COLUMNS FROM {$this->table_name} LIKE 'query_string_mode'" );
        if ( empty( $col ) ) {
            error_log( 'ARM DB migration failed: query_string_mode column not present after migration.' );
            return;
        }

        update_option( 'ncb_arm_redirects_has_qs_mode', 1, false );
    }

    /**
     * AJAX: Fetch all redirect rules.
     */
    public function ajax_get_redirects() {
        check_ajax_referer('ncb_arm_admin_ajax_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => 'Permission denied.']);
        }
        $redirects = $this->get_all_redirects();
        wp_send_json_success($redirects);
    }

    /**
     * AJAX: Save or update a redirect rule.
     */
    public function ajax_save_redirect() {
        // 1. Controlla il nonce per la sicurezza
        check_ajax_referer('ncb_arm_admin_ajax_nonce', 'nonce');

        // 2. Controlla i permessi dell'utente
        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => 'Permission denied.']);
        }

        // 3. Valida che l'input esista e sia un array grezzo
        if (!isset($_POST['rule']) || !is_array($_POST['rule'])) {
            wp_send_json_error(['message' => 'Invalid data provided.']);
        }
        
        // 4. Prepara un array di dati puliti leggendo e sanitizzando
        //    DIRETTAMENTE da $_POST per ogni singolo valore.
        $rule_data = array(
            'id'       => isset( $_POST['rule']['id'] ) ? sanitize_key( wp_unslash( $_POST['rule']['id'] ) ) : 'new', // <-- CORREZIONE QUI
            'url_from' => isset( $_POST['rule']['url_from'] ) ? sanitize_text_field( wp_unslash( $_POST['rule']['url_from'] ) ) : '',
            'url_to'   => isset( $_POST['rule']['url_to'] ) ? sanitize_text_field( wp_unslash( $_POST['rule']['url_to'] ) ) : '',
            'status'   => isset( $_POST['rule']['status'] ) ? sanitize_key( wp_unslash( $_POST['rule']['status'] ) ) : '301',
            'query_string_mode' => isset( $_POST['rule']['query_string_mode'] ) ? sanitize_key( wp_unslash( $_POST['rule']['query_string_mode'] ) ) : 'ignore',
        );

        // 5. Passa l'array GIA' SANITIZZATO a 'save_redirect'.
        $result = $this->save_redirect( $rule_data );

        // 6. Gestisce la risposta
        if (is_wp_error($result)) {
            // Se save_redirect restituisce un errore, lo invio al client
            wp_send_json_error(['message' => $result->get_error_message()]);
        } else {
            // Altrimenti, invio una risposta di successo con l'ID della regola
            wp_send_json_success(['id' => $result]);
        }
    }

    /**
     * AJAX: Delete a redirect rule.
     */
    public function ajax_delete_redirect() {
        check_ajax_referer('ncb_arm_admin_ajax_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => 'Permission denied.']);
        }

        if (!isset($_POST['id']) || !absint($_POST['id'])) {
            wp_send_json_error(['message' => 'Invalid redirect ID.']);
        }

        $id = absint($_POST['id']);
        $deleted = $this->delete_redirect($id);

        if ($deleted) {
            wp_send_json_success();
        } else {
            wp_send_json_error(['message' => 'Could not delete the redirect rule.']);
        }
    }

    /**
     * AJAX: Perform a smart search for posts, pages, and terms.
     */
    public function ajax_smart_search() {
        check_ajax_referer('ncb_arm_admin_ajax_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => 'Permission denied.']);
        }
        
        $search_term = isset( $_POST['search'] ) ? sanitize_text_field( wp_unslash( $_POST['search'] ) ) : '';
        if (strlen($search_term) < 2) {
            wp_send_json_success([]);
            return;
        }

        $results = [];
        $post_types = get_post_types(['public' => true]);
        
        // Search posts and pages
        $post_args = [
            's' => $search_term,
            'post_type' => $post_types,
            'posts_per_page' => 10,
        ];
        $post_query = new WP_Query($post_args);
        if ($post_query->have_posts()) {
            while ($post_query->have_posts()) {
                $post_query->the_post();
                $post_type_obj = get_post_type_object(get_post_type());
                $results[] = [
                    'url' => get_permalink(),
                    'title' => get_the_title(),
                    'type' => $post_type_obj->labels->singular_name,
                ];
            }
        }
        wp_reset_postdata();

        // Search terms
        $term_args = [
            'search' => $search_term,
            'number' => 10,
        ];
        $terms = get_terms($term_args);
        if (!is_wp_error($terms) && !empty($terms)) {
            foreach ($terms as $term) {
                $tax = get_taxonomy($term->taxonomy);
                $results[] = [
                    'url' => get_term_link($term),
                    'title' => $term->name,
                    'type' => $tax->labels->singular_name,
                ];
            }
        }

        wp_send_json_success($results);
    }
    
    /**
     * Retrieves all redirect rules, ordered by type for processing.
     */
    public function get_all_redirects() {
        global $wpdb;
        // Ordina per tipo (wildcard prima) e poi per ID
        $results = $wpdb->get_results("SELECT * FROM {$this->table_name} ORDER BY type DESC, id DESC");

        foreach ($results as $key => $rule) {
            $results[$key]->status_label = $this->get_status_label($rule->status);
            // Crea un campo 'url_to_rendered' per la visualizzazione, es. il permalink di un ID
            $results[$key]->url_to_rendered = $this->get_rendered_url($rule->url_to);
        }
        return $results;
    }

    /**
     * Inserts or updates a redirect rule.
     */
    public function save_redirect($data) {
        global $wpdb;

        $id = isset($data['id']) && $data['id'] !== 'new' ? absint($data['id']) : 0;
        $url_from = sanitize_text_field($data['url_from']);
        $url_to = sanitize_text_field($data['url_to']);
        $status = in_array($data['status'], ['301', '302', '307', '308', '403', '410', '451', 'off']) ? $data['status'] : '301';
        $type = strpos($url_from, '*') !== false ? 'wildcard' : 'url';
        $query_string_mode = isset($data['query_string_mode']) ? sanitize_key($data['query_string_mode']) : 'ignore';
        if (!in_array($query_string_mode, ['ignore', 'passthrough', 'exact', 'exact_passthrough', 'original_passthrough'], true)) {
            $query_string_mode = 'ignore';
        }

        // Validation
        if (empty($url_from) || empty($url_to)) {
            return new WP_Error('empty_fields', 'Both "Redirect From" and "Redirect To" fields are required.');
        }

        // Normalizza solo i percorsi relativi: gli URL assoluti vengono preservati.
        if (!preg_match('~^https?://~i', $url_from)) {
            $url_from = '/' . ltrim(trim($url_from), '/');
        }

        // Check for duplicates
        $existing = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$this->table_name} WHERE url_from = %s AND id != %d", $url_from, $id));
        if ($existing) {
            return new WP_Error('duplicate_url_from', 'A redirect for this "From" URL already exists.');
        }

        $db_data = compact('url_from', 'url_to', 'status', 'type', 'query_string_mode');
        $db_formats = ['%s', '%s', '%s', '%s', '%s'];

        if ($id > 0) {
            $wpdb->update($this->table_name, $db_data, ['id' => $id], $db_formats, ['%d']);
            NCB_ARM_Redirect_Handler::invalidate_cache();
            return $id;
        } else {
            $db_data['created_at'] = current_time('mysql');
            $db_formats[] = '%s';
            $wpdb->insert($this->table_name, $db_data, $db_formats);
            NCB_ARM_Redirect_Handler::invalidate_cache();
            return $wpdb->insert_id;
        }
    }

    public function delete_redirect($id) {
        global $wpdb;
        $result = $wpdb->delete($this->table_name, ['id' => absint($id)], ['%d']);
        
        if ($result) {
            NCB_ARM_Redirect_Handler::invalidate_cache();
        }
        
        return $result;
    }

    private function get_status_label($status) {
    $labels = [
        '301' => '301', 
        '302' => '302', 
        '307' => '307', 
        '308' => '308', 
        '410' => '410', 
        '451' => '451', 
        'off' => 'Off'
    ];
        return $labels[$status] ?? $status;
    }
    
    private function get_rendered_url($url_to) {
        if (filter_var($url_to, FILTER_VALIDATE_URL)) {
            return $url_to;
        }
        if (is_numeric($url_to)) {
            return get_permalink( (int) $url_to ) ?: $url_to;
        }
        return home_url( '/' . ltrim($url_to, '/') );
    }
}