<?php
/**
 * AYSA SEO Generation History Manager
 * 
 * Manages WordPress database table for tracking SEO generation history
 * with versioning support for all SEO generation activities.
 * 
 * @package AYSA
 * @since 1.0.0
 */

if (!defined('ABSPATH')) {
    exit; // Exit if accessed directly
}

class Aysa_Seo_History {
    
    /**
     * Table name constant
     */
    const TABLE_NAME = 'aysa_seo_history';
    
    /**
     * Database table name with prefix
     * @var string
     */
    private $table_name;
    
    /**
     * WordPress database instance
     * @var wpdb
     */
    private $wpdb;
    
    /**
     * Constructor
     */
    public function __construct() {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->table_name = $wpdb->prefix . self::TABLE_NAME;
        
        // Create table on plugin activation
        add_action('init', array($this, 'maybe_create_table'));
    }
    
    /**
     * Create the SEO history table if it doesn't exist
     */
    public function create_table() {
        $charset_collate = $this->get_safe_charset_collate();
        
        $sql = "CREATE TABLE IF NOT EXISTS {$this->table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            entity_id bigint(20) unsigned NOT NULL,
            entity_type varchar(50) NOT NULL,
            entity_name varchar(255) DEFAULT '',
            keyword varchar(255) NOT NULL,
            language varchar(10) DEFAULT 'en',
            website_name varchar(255) DEFAULT '',
            
            -- Generated SEO data
            meta_title text DEFAULT NULL,
            meta_description text DEFAULT NULL,
            meta_keywords text DEFAULT NULL,
            secondary_keywords text DEFAULT NULL,
            content_title text DEFAULT NULL,
            content_description longtext DEFAULT NULL,
            short_description text DEFAULT NULL,
            permalink varchar(500) DEFAULT NULL,
            
            -- Previous values (for comparison)
            previous_meta_title text DEFAULT NULL,
            previous_meta_description text DEFAULT NULL,
            previous_meta_keywords text DEFAULT NULL,
            previous_content_title text DEFAULT NULL,
            previous_content_description longtext DEFAULT NULL,
            previous_short_description text DEFAULT NULL,
            previous_permalink varchar(500) DEFAULT NULL,
            
            -- Generation metadata
            generator_type varchar(50) DEFAULT 'original',
            generation_method varchar(100) DEFAULT 'manual',
            api_response_time int(11) DEFAULT NULL,
            partial_failure tinyint(1) DEFAULT 0,
            failed_fields text DEFAULT NULL,
            
            -- Versioning and tracking
            version int(11) NOT NULL DEFAULT 1,
            parent_version_id bigint(20) unsigned DEFAULT NULL,
            is_current_version tinyint(1) DEFAULT 1,
            
            -- User and session info
            wp_user_id bigint(20) unsigned NOT NULL,
            user_ip varchar(45) DEFAULT NULL,
            user_agent text DEFAULT NULL,
            session_id varchar(100) DEFAULT NULL,
            
            -- Timestamps
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            applied_at datetime DEFAULT NULL,
            reverted_at datetime DEFAULT NULL,
            
            -- Status and flags
            status varchar(20) DEFAULT 'generated',
            is_applied tinyint(1) DEFAULT 0,
            is_reverted tinyint(1) DEFAULT 0,
            confidence_score decimal(5,2) DEFAULT NULL,
            
            -- Additional metadata
            generation_context longtext DEFAULT NULL,
            notes text DEFAULT NULL,
            
            PRIMARY KEY (id),
            KEY entity_lookup (entity_id, entity_type),
            KEY keyword_lookup (keyword),
            KEY version_lookup (entity_id, entity_type, version),
            KEY current_version (entity_id, entity_type, is_current_version),
            KEY user_history (wp_user_id, created_at),
            KEY status_lookup (status),
            KEY created_at (created_at),
            KEY parent_version (parent_version_id)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        
        aysa_debug_log('[AYSA SEO History] Creating table with SQL: ' . $sql);
        $result = dbDelta($sql);
        
        if ($result) {
            aysa_debug_log('[AYSA SEO History] Table created successfully');
            update_option('aysa_seo_history_table_version', '1.0.0');
        } else {
            aysa_debug_log('[AYSA SEO History] Failed to create table');
        }
        
        return $result;
    }
    
    /**
     * Check if table needs to be created
     */
    public function maybe_create_table() {
        $table_version = get_option('aysa_seo_history_table_version', '0.0.0');
        
        if (version_compare($table_version, '1.0.0', '<')) {
            $this->create_table();
        }
    }
    
    /**
     * Get safe charset collate for table creation
     */
    private function get_safe_charset_collate() {
        $charset_collate = '';
        
        if (!empty($this->wpdb->charset)) {
            $charset_collate = "DEFAULT CHARACTER SET {$this->wpdb->charset}";
        }
        
        if (!empty($this->wpdb->collate)) {
            $charset_collate .= " COLLATE {$this->wpdb->collate}";
        }
        
        return $charset_collate;
    }
    
    /**
     * Log a new SEO generation entry
     * 
     * @param array $data SEO generation data
     * @return int|false Entry ID on success, false on failure
     */
    public function log_seo_generation($data) {
        // Validate required fields
        $required_fields = ['entity_id', 'entity_type', 'keyword'];
        foreach ($required_fields as $field) {
            if (empty($data[$field])) {
                aysa_debug_log("[AYSA SEO History] Missing required field: {$field}");
                return false;
            }
        }
        
        // Get current user info
        $current_user_id = get_current_user_id();
        if (!$current_user_id) {
            aysa_debug_log('[AYSA SEO History] No current user found');
            return false;
        }
        
        // Mark previous versions as not current
        $this->mark_previous_versions_as_old($data['entity_id'], $data['entity_type']);
        
        // Get next version number
        $version = $this->get_next_version_number($data['entity_id'], $data['entity_type']);
        
        // Prepare data for insertion
        $insert_data = array(
            'entity_id' => intval($data['entity_id']),
            'entity_type' => sanitize_text_field($data['entity_type']),
            'entity_name' => sanitize_text_field($data['entity_name'] ?? ''),
            'keyword' => sanitize_text_field($data['keyword']),
            'language' => sanitize_text_field($data['language'] ?? 'en'),
            'website_name' => sanitize_text_field($data['website_name'] ?? ''),
            
            // Generated SEO data
            'meta_title' => $data['meta_title'] ?? null,
            'meta_description' => $data['meta_description'] ?? null,
            'meta_keywords' => $data['meta_keywords'] ?? null,
            'secondary_keywords' => is_array($data['secondary_keywords'] ?? null) ? 
                implode(', ', $data['secondary_keywords']) : ($data['secondary_keywords'] ?? null),
            'content_title' => $data['content_title'] ?? null,
            'content_description' => $data['content_description'] ?? null,
            'short_description' => $data['short_description'] ?? null,
            'permalink' => $data['permalink'] ?? null,
            
            // Previous values
            'previous_meta_title' => $data['previous_meta_title'] ?? null,
            'previous_meta_description' => $data['previous_meta_description'] ?? null,
            'previous_meta_keywords' => $data['previous_meta_keywords'] ?? null,
            'previous_content_title' => $data['previous_content_title'] ?? null,
            'previous_content_description' => $data['previous_content_description'] ?? null,
            'previous_short_description' => $data['previous_short_description'] ?? null,
            'previous_permalink' => $data['previous_permalink'] ?? null,
            
            // Generation metadata
            'generator_type' => sanitize_text_field($data['generator_type'] ?? 'original'),
            'generation_method' => sanitize_text_field($data['generation_method'] ?? 'manual'),
            'api_response_time' => isset($data['api_response_time']) ? intval($data['api_response_time']) : null,
            'partial_failure' => isset($data['partial_failure']) ? intval($data['partial_failure']) : 0,
            'failed_fields' => is_array($data['failed_fields'] ?? null) ? 
                implode(', ', $data['failed_fields']) : ($data['failed_fields'] ?? null),
            
            // Versioning
            'version' => $version,
            'parent_version_id' => isset($data['parent_version_id']) ? intval($data['parent_version_id']) : null,
            'is_current_version' => 1,
            
            // User info
            'wp_user_id' => $current_user_id,
            'user_ip' => $this->get_user_ip(),
            'user_agent' => $this->get_user_agent(),
            'session_id' => $this->get_session_id(),
            
            // Status
            'status' => sanitize_text_field($data['status'] ?? 'generated'),
            'is_applied' => isset($data['is_applied']) ? intval($data['is_applied']) : 0,
            'confidence_score' => isset($data['confidence_score']) ? floatval($data['confidence_score']) : null,
            
            // Additional data
            'generation_context' => isset($data['generation_context']) ? 
                wp_json_encode($data['generation_context']) : null,
            'notes' => sanitize_textarea_field($data['notes'] ?? '')
        );
        
        // Insert into database
        $result = $this->wpdb->insert(
            $this->table_name,
            $insert_data,
            array(
                '%d', '%s', '%s', '%s', '%s', '%s', // entity info
                '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', // generated data
                '%s', '%s', '%s', '%s', '%s', '%s', '%s', // previous data
                '%s', '%s', '%d', '%d', '%s', // generation metadata
                '%d', '%d', '%d', // versioning
                '%d', '%s', '%s', '%s', // user info
                '%s', '%d', '%f', // status
                '%s', '%s' // additional
            )
        );
        
        if ($result === false) {
            aysa_debug_log('[AYSA SEO History] Failed to insert SEO generation: ' . $this->wpdb->last_error);
            return false;
        }
        
        $entry_id = $this->wpdb->insert_id;
        aysa_debug_log("[AYSA SEO History] Successfully logged SEO generation with ID: {$entry_id}");
        
        return $entry_id;
    }
    
    /**
     * Mark an entry as applied
     */
    public function mark_as_applied($entry_id) {
        return $this->wpdb->update(
            $this->table_name,
            array(
                'is_applied' => 1,
                'applied_at' => current_time('mysql')
            ),
            array('id' => $entry_id),
            array('%d', '%s'),
            array('%d')
        );
    }
    
    /**
     * Mark an entry as reverted
     */
    public function mark_as_reverted($entry_id) {
        return $this->wpdb->update(
            $this->table_name,
            array(
                'is_reverted' => 1,
                'reverted_at' => current_time('mysql')
            ),
            array('id' => $entry_id),
            array('%d', '%s'),
            array('%d')
        );
    }
    
    /**
     * Get SEO generation history for an entity
     */
    public function get_entity_history($entity_id, $entity_type, $limit = 50) {
        $sql = $this->wpdb->prepare(
            "SELECT * FROM {$this->table_name} 
             WHERE entity_id = %d AND entity_type = %s 
             ORDER BY created_at DESC 
             LIMIT %d",
            $entity_id,
            $entity_type,
            $limit
        );
        
        return $this->wpdb->get_results($sql);
    }
    
    /**
     * Get current version for an entity
     */
    public function get_current_version($entity_id, $entity_type) {
        $sql = $this->wpdb->prepare(
            "SELECT * FROM {$this->table_name} 
             WHERE entity_id = %d AND entity_type = %s AND is_current_version = 1 
             ORDER BY version DESC 
             LIMIT 1",
            $entity_id,
            $entity_type
        );
        
        return $this->wpdb->get_row($sql);
    }
    
    /**
     * Get history by user
     */
    public function get_user_history($user_id, $limit = 100) {
        $sql = $this->wpdb->prepare(
            "SELECT * FROM {$this->table_name} 
             WHERE wp_user_id = %d 
             ORDER BY created_at DESC 
             LIMIT %d",
            $user_id,
            $limit
        );
        
        return $this->wpdb->get_results($sql);
    }
    
    /**
     * Get recent history across all entities
     */
    public function get_recent_history($limit = 50) {
        $sql = $this->wpdb->prepare(
            "SELECT h.*, u.display_name as user_name 
             FROM {$this->table_name} h 
             LEFT JOIN {$this->wpdb->users} u ON h.wp_user_id = u.ID 
             ORDER BY h.created_at DESC 
             LIMIT %d",
            $limit
        );
        
        return $this->wpdb->get_results($sql);
    }
    
    /**
     * Get statistics
     */
    public function get_statistics() {
        $stats = array();
        
        // Total generations
        $stats['total_generations'] = $this->wpdb->get_var(
            "SELECT COUNT(*) FROM {$this->table_name}"
        );
        
        // Generations by entity type
        $stats['by_entity_type'] = $this->wpdb->get_results(
            "SELECT entity_type, COUNT(*) as count 
             FROM {$this->table_name} 
             GROUP BY entity_type"
        );
        
        // Generations by generator type
        $stats['by_generator_type'] = $this->wpdb->get_results(
            "SELECT generator_type, COUNT(*) as count 
             FROM {$this->table_name} 
             GROUP BY generator_type"
        );
        
        // Recent activity (last 30 days)
        $stats['recent_activity'] = $this->wpdb->get_var(
            "SELECT COUNT(*) FROM {$this->table_name} 
             WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)"
        );
        
        // Applied vs generated
        $stats['applied_count'] = $this->wpdb->get_var(
            "SELECT COUNT(*) FROM {$this->table_name} WHERE is_applied = 1"
        );
        
        return $stats;
    }
    
    /**
     * Search history
     */
    public function search_history($search_term, $limit = 50) {
        $search_term = '%' . $this->wpdb->esc_like($search_term) . '%';
        
        $sql = $this->wpdb->prepare(
            "SELECT h.*, u.display_name as user_name 
             FROM {$this->table_name} h 
             LEFT JOIN {$this->wpdb->users} u ON h.wp_user_id = u.ID 
             WHERE h.keyword LIKE %s 
                OR h.entity_name LIKE %s 
                OR h.meta_title LIKE %s 
                OR h.meta_description LIKE %s 
             ORDER BY h.created_at DESC 
             LIMIT %d",
            $search_term,
            $search_term,
            $search_term,
            $search_term,
            $limit
        );
        
        return $this->wpdb->get_results($sql);
    }
    
    /**
     * Delete old history entries (cleanup)
     */
    public function cleanup_old_entries($days = 90) {
        $deleted = $this->wpdb->query(
            $this->wpdb->prepare(
                "DELETE FROM {$this->table_name} 
                 WHERE created_at < DATE_SUB(NOW(), INTERVAL %d DAY) 
                 AND is_current_version = 0",
                $days
            )
        );
        
        aysa_debug_log("[AYSA SEO History] Cleaned up {$deleted} old entries");
        return $deleted;
    }
    
    /**
     * Helper methods
     */
    
    private function mark_previous_versions_as_old($entity_id, $entity_type) {
        return $this->wpdb->update(
            $this->table_name,
            array('is_current_version' => 0),
            array(
                'entity_id' => $entity_id,
                'entity_type' => $entity_type
            ),
            array('%d'),
            array('%d', '%s')
        );
    }
    
    private function get_next_version_number($entity_id, $entity_type) {
        $max_version = $this->wpdb->get_var(
            $this->wpdb->prepare(
                "SELECT MAX(version) FROM {$this->table_name} 
                 WHERE entity_id = %d AND entity_type = %s",
                $entity_id,
                $entity_type
            )
        );
        
        return ($max_version ? intval($max_version) : 0) + 1;
    }
    
    private function get_user_ip() {
        $ip_keys = array('HTTP_CLIENT_IP', 'HTTP_X_FORWARDED_FOR', 'REMOTE_ADDR');
        
        foreach ($ip_keys as $key) {
            if (array_key_exists($key, $_SERVER) === true) {
                foreach (explode(',', $_SERVER[$key]) as $ip) {
                    $ip = trim($ip);
                    if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE) !== false) {
                        return $ip;
                    }
                }
            }
        }
        
        return $_SERVER['REMOTE_ADDR'] ?? 'unknown';
    }
    
    private function get_user_agent() {
        return $_SERVER['HTTP_USER_AGENT'] ?? 'unknown';
    }
    
    private function get_session_id() {
        if (!session_id()) {
            return 'no-session';
        }
        return session_id();
    }
    
    /**
     * Get table name (for external access)
     */
    public function get_table_name() {
        return $this->table_name;
    }
}

// Initialize the SEO history manager
if (!function_exists('aysa_get_seo_history')) {
    function aysa_get_seo_history() {
        static $instance = null;
        if ($instance === null) {
            $instance = new Aysa_Seo_History();
        }
        return $instance;
    }
}