<?php
/**
 * Database operations for LLMS.txt Generator
 */
if (!defined('ABSPATH')) {
    exit;
}
class CCSPLLMS_Database {
    
    public function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        // Settings table
        $settings_table = $wpdb->prefix . 'ccspllms_settings';
        $settings_sql = "CREATE TABLE $settings_table (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            setting_name varchar(100) NOT NULL,
            setting_value longtext,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY setting_name (setting_name)
        ) $charset_collate;";
        
        // Analytics table
        $analytics_table = $wpdb->prefix . 'ccspllms_analytics';
        $analytics_sql = "CREATE TABLE $analytics_table (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            bot_name varchar(100),
            ip_address varchar(45),
            user_agent text,
            request_time datetime DEFAULT CURRENT_TIMESTAMP,
            file_size int(11),
            response_code int(3) DEFAULT 200,
            PRIMARY KEY (id),
            KEY bot_name (bot_name),
            KEY ip_address (ip_address),
            KEY request_time (request_time)
        ) $charset_collate;";
        
        // Generation log table
        $generation_table = $wpdb->prefix . 'ccspllms_generation_log';
        $generation_sql = "CREATE TABLE $generation_table (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            generated_at datetime DEFAULT CURRENT_TIMESTAMP,
            posts_count int(11),
            file_size bigint(20),
            trigger_type varchar(50),
            success tinyint(1) DEFAULT 1,
            error_message text,
            PRIMARY KEY (id),
            KEY generated_at (generated_at)
        ) $charset_collate;";
        
        // Bot management table
        $bots_table = $wpdb->prefix . 'ccspllms_bots';
        $bots_sql = "CREATE TABLE $bots_table (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            bot_name varchar(100) NOT NULL,
            user_agent_pattern text,
            ip_address varchar(45),
            status enum('allowed','blocked') DEFAULT 'allowed',
            first_seen datetime DEFAULT CURRENT_TIMESTAMP,
            last_seen datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            visit_count int(11) DEFAULT 1,
            PRIMARY KEY (id),
            UNIQUE KEY bot_name (bot_name)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        
        dbDelta($settings_sql);
        dbDelta($analytics_sql);
        dbDelta($generation_sql);
        dbDelta($bots_sql);
        
        // Insert default bot patterns
        $this->insert_default_bots();
    }
    
    private function insert_default_bots() {
        global $wpdb;
        
        $bots_table = $wpdb->prefix . 'ccspllms_bots';
        
        $default_bots = array(
            array('GPTBot', 'GPTBot', null, 'allowed'),
            array('Claude-Web', 'Claude-Web', null, 'allowed'),
            array('ChatGPT-User', 'ChatGPT-User', null, 'allowed'),
            array('Bard', 'Bard', null, 'allowed'),
            array('PerplexityBot', 'PerplexityBot', null, 'allowed'),
            array('YouBot', 'YouBot', null, 'allowed'),
            array('Anthropic', 'anthropic', null, 'allowed'),
            array('OpenAI', 'openai', null, 'allowed'),

            // Major search engines
            array('Googlebot', 'Googlebot', null, 'allowed'),
            array('Bingbot', 'Bingbot', null, 'allowed'),
            array('DuckDuckBot', 'DuckDuckBot', null, 'allowed'),
            array('YandexBot', 'YandexBot', null, 'allowed'),
            array('BaiduSpider', 'BaiduSpider', null, 'allowed'),
            array('Slurp', 'Slurp', null, 'allowed'), // Yahoo Slurp
            array('Sogou', 'Sogou', null, 'allowed'),
            array('Exabot', 'Exabot', null, 'allowed'),
            array('SeznamBot', 'SeznamBot', null, 'allowed'),

            // SEO crawlers
            array('AhrefsBot', 'AhrefsBot', null, 'allowed'),
            array('SemrushBot', 'SemrushBot', null, 'allowed'),
            array('MJ12bot', 'MJ12bot', null, 'allowed'),
            array('DotBot', 'DotBot', null, 'allowed'),

            // Social bots
            array('Facebook', 'facebookexternalhit', null, 'allowed'),
            array('Twitter', 'Twitterbot', null, 'allowed'),
            array('LinkedIn', 'LinkedInBot', null, 'allowed'),
            array('Applebot', 'Applebot', null, 'allowed')
        );

        
        foreach ($default_bots as $bot) {
            $wpdb->replace(
                $bots_table,
                array(
                    'bot_name' => $bot[0],
                    'user_agent_pattern' => $bot[1],
                    'ip_address' => $bot[2],
                    'status' => $bot[3]
                ),
                array('%s', '%s', '%s', '%s')
            );
        }
    }
    


    public function get_analytics_data($days = 30) {
        global $wpdb;
        $analytics_table = $wpdb->prefix . 'ccspllms_analytics';
        $results = $wpdb->get_results($wpdb->prepare("
            SELECT DATE(request_time) as date, COUNT(*) as visit_count
            FROM $analytics_table
            WHERE request_time >= DATE_SUB(CURDATE(), INTERVAL %d DAY)
            GROUP BY DATE(request_time)
            ORDER BY date ASC
        ", $days));
        return $results;
    }
    
/**
 * Get bot analytics statistics.
 *
 * @return array List of bot statistics objects.
 */
    public function get_bot_stats() {
        global $wpdb;

        $analytics_table = $wpdb->prefix . 'ccspllms_analytics';
        $bots_table      = $wpdb->prefix . 'ccspllms_bots';

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $query = "
            SELECT 
                a.bot_name,
                COUNT(*) AS visit_count,
                MAX(a.request_time) AS last_visit,
                COUNT(DISTINCT a.ip_address) AS unique_ips,
                b.status
            FROM {$analytics_table} a
            LEFT JOIN {$bots_table} b 
                ON a.bot_name = b.bot_name
            WHERE a.bot_name IS NOT NULL
            GROUP BY a.bot_name, b.status
            ORDER BY last_visit DESC
        ";

        $results = $wpdb->get_results( $query );

        if ( null === $results ) {
            error_log(
                sprintf(
                    '[CCSP LLms.txt Generator]: Failed to fetch bot stats. DB error: %s',
                    $wpdb->last_error
                )
            );
            return array();
        }

        return $results;
    }


    
    public function log_bot_visit($bot_name, $ip_address, $user_agent) {
        global $wpdb;
        
        $analytics_table = $wpdb->prefix . 'ccspllms_analytics';
        $bots_table = $wpdb->prefix . 'ccspllms_bots';
        
        // Log the visit
        $wpdb->insert(
            $analytics_table,
            array(
                'bot_name' => $bot_name,
                'ip_address' => $ip_address,
                'user_agent' => $user_agent,
                'file_size' => $this->get_current_file_size()
            ),
            array('%s', '%s', '%s', '%d')
        );
        
        // Update bot record
        $wpdb->query($wpdb->prepare("
            INSERT INTO $bots_table (bot_name, user_agent_pattern, ip_address, visit_count)
            VALUES (%s, %s, %s, 1)
            ON DUPLICATE KEY UPDATE
            visit_count = visit_count + 1,
            last_seen = CURRENT_TIMESTAMP,
            ip_address = VALUES(ip_address)
        ", $bot_name, $user_agent, $ip_address));
    }
    
    private function get_current_file_size() {
        $file_path = ABSPATH . 'llms.txt';
        return file_exists($file_path) ? filesize($file_path) : 0;
    }
    
    public function cleanup_old_data($days = 90) {
        global $wpdb;
        
        $analytics_table = $wpdb->prefix . 'ccspllms_analytics';
        $generation_table = $wpdb->prefix . 'ccspllms_generation_log';
        
        // Clean old analytics data
        $wpdb->query($wpdb->prepare("
            DELETE FROM $analytics_table 
            WHERE request_time < DATE_SUB(NOW(), INTERVAL %d DAY)
        ", $days));
        
        // Clean old generation logs
        $wpdb->query($wpdb->prepare("
            DELETE FROM $generation_table 
            WHERE generated_at < DATE_SUB(NOW(), INTERVAL %d DAY)
        ", $days));
    }
}