<?php

namespace AIProductContentImageGenerator\Core;

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

class Database {
    
    const DB_VERSION = '1.0.0';
    
    private $tables = [
        'generations' => 'apcig_generations',
        'batch_jobs' => 'apcig_batch_jobs',
        'api_usage' => 'apcig_api_usage'
    ];
    
    public function init() {
        add_action('init', [$this, 'check_database_version']);
        register_activation_hook(APCIG_PLUGIN_FILE, [$this, 'create_tables']);
        register_deactivation_hook(APCIG_PLUGIN_FILE, [$this, 'cleanup_scheduled_events']);
    }
    
    public function check_database_version() {
        $installed_version = get_option('apcig_db_version', '0.0.0');
        
        if (version_compare($installed_version, self::DB_VERSION, '<')) {
            $this->create_tables();
            update_option('apcig_db_version', self::DB_VERSION);
        }
    }
    
    public function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        $generations_table = $wpdb->prefix . $this->tables['generations'];
        $generations_sql = "CREATE TABLE $generations_table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned NOT NULL,
            content_type varchar(50) NOT NULL,
            provider varchar(50) NOT NULL,
            prompt_used text,
            generated_content longtext,
            applied tinyint(1) DEFAULT 0,
            tokens_used int(11) DEFAULT 0,
            cost decimal(10,6) DEFAULT 0.000000,
            user_id bigint(20) unsigned NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY product_id (product_id),
            KEY content_type (content_type),
            KEY provider (provider),
            KEY user_id (user_id),
            KEY created_at (created_at)
        ) $charset_collate;";
        
        $batch_jobs_table = $wpdb->prefix . $this->tables['batch_jobs'];
        $batch_jobs_sql = "CREATE TABLE $batch_jobs_table (
            id varchar(50) NOT NULL,
            action_type varchar(50) NOT NULL,
            status varchar(20) DEFAULT 'pending',
            total_items int(11) NOT NULL DEFAULT 0,
            processed_items int(11) NOT NULL DEFAULT 0,
            failed_items int(11) NOT NULL DEFAULT 0,
            settings longtext,
            product_ids longtext,
            results longtext,
            error_log longtext,
            user_id bigint(20) unsigned NOT NULL,
            started_at datetime DEFAULT NULL,
            completed_at datetime DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status (status),
            KEY action_type (action_type),
            KEY user_id (user_id),
            KEY created_at (created_at)
        ) $charset_collate;";
        
        $api_usage_table = $wpdb->prefix . $this->tables['api_usage'];
        $api_usage_sql = "CREATE TABLE $api_usage_table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            provider varchar(50) NOT NULL,
            model varchar(100),
            tokens_used int(11) NOT NULL DEFAULT 0,
            cost decimal(10,6) DEFAULT 0.000000,
            request_type varchar(50),
            user_id bigint(20) unsigned NOT NULL,
            date_used date NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY provider (provider),
            KEY user_id (user_id),
            KEY date_used (date_used),
            KEY request_type (request_type)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        
        dbDelta($generations_sql);
        dbDelta($batch_jobs_sql);
        dbDelta($api_usage_sql);
    }
    
    /**
     * Get table name
     * 
     * @param string $table Table key
     * @return string Full table name
     */
    public function get_table_name($table) {
        global $wpdb;
        
        if (!isset($this->tables[$table])) {
            return false;
        }
        
        return $wpdb->prefix . $this->tables[$table];
    }
    
    /**
     * Save generation record
     * 
     * @param array $data Generation data
     * @return int|false Generation ID or false on failure
     */
    public function save_generation($data) {
        global $wpdb;
        
        $table = $this->get_table_name('generations');
        
        $defaults = [
            'product_id' => 0,
            'content_type' => '',
            'provider' => '',
            'prompt_used' => '',
            'generated_content' => '',
            'applied' => 0,
            'tokens_used' => 0,
            'cost' => 0.0,
            'user_id' => get_current_user_id()
        ];
        
        $data = wp_parse_args($data, $defaults);
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $wpdb->insert(
            $table,
            $data,
            [
                '%d', // product_id
                '%s', // content_type
                '%s', // provider
                '%s', // prompt_used
                '%s', // generated_content
                '%d', // applied
                '%d', // tokens_used
                '%f', // cost
                '%d'  // user_id
            ]
        );
        
        return $result ? $wpdb->insert_id : false;
    }
    
    /**
     * Get generations for a product
     * 
     * @param int $product_id Product ID
     * @param string $content_type Content type filter
     * @param int $limit Number of records to retrieve
     * @return array Generation records
     */
    public function get_product_generations($product_id, $content_type = '', $limit = 10) {
        global $wpdb;
        
        $table = $this->get_table_name('generations');
        
        if (!empty($content_type)) {
            $sql = $wpdb->prepare(
                "SELECT * FROM {$wpdb->prefix}apcig_generations WHERE product_id = %d AND content_type = %s ORDER BY created_at DESC LIMIT %d",
                $product_id,
                $content_type,
                $limit
            );
        } else {
            $sql = $wpdb->prepare(
                "SELECT * FROM {$wpdb->prefix}apcig_generations WHERE product_id = %d ORDER BY created_at DESC LIMIT %d",
                $product_id,
                $limit
            );
        }
        
        return $wpdb->get_results($sql); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    }
    
    /**
     * Save batch job
     * 
     * @param array $data Batch job data
     * @return bool Success status
     */
    public function save_batch_job($data) {
        global $wpdb;
        
        $table = $this->get_table_name('batch_jobs');
        
        $defaults = [
            'id' => uniqid('batch_'),
            'action_type' => '',
            'status' => 'pending',
            'total_items' => 0,
            'processed_items' => 0,
            'failed_items' => 0,
            'settings' => '',
            'product_ids' => '',
            'results' => '',
            'error_log' => '',
            'user_id' => get_current_user_id()
        ];
        
        $data = wp_parse_args($data, $defaults);
        
        // Serialize arrays
        if (is_array($data['settings'])) {
            $data['settings'] = maybe_serialize($data['settings']);
        }
        if (is_array($data['product_ids'])) {
            $data['product_ids'] = maybe_serialize($data['product_ids']);
        }
        if (is_array($data['results'])) {
            $data['results'] = maybe_serialize($data['results']);
        }
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->replace(
            $table,
            $data,
            [
                '%s', // id
                '%s', // action_type
                '%s', // status
                '%d', // total_items
                '%d', // processed_items
                '%d', // failed_items
                '%s', // settings
                '%s', // product_ids
                '%s', // results
                '%s', // error_log
                '%d'  // user_id
            ]
        );
        
        return $result !== false;
    }
    
    /**
     * Get batch job
     * 
     * @param string $batch_id Batch ID
     * @return object|null Batch job data
     */
    public function get_batch_job($batch_id) {
        global $wpdb;
        
        $table = $this->get_table_name('batch_jobs');
        
        $job = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->prepare(
                "SELECT * FROM {$wpdb->prefix}apcig_batch_jobs WHERE id = %s",
                $batch_id
            )
        );
        
        if ($job) {
            // Unserialize arrays
            $job->settings = maybe_unserialize($job->settings);
            $job->product_ids = maybe_unserialize($job->product_ids);
            $job->results = maybe_unserialize($job->results);
        }
        
        return $job;
    }
    
    /**
     * Update batch job progress
     * 
     * @param string $batch_id Batch ID
     * @param array $updates Update data
     * @return bool Success status
     */
    public function update_batch_job($batch_id, $updates) {
        global $wpdb;
        
        $table = $this->get_table_name('batch_jobs');
        
        // Serialize arrays if needed
        foreach (['settings', 'product_ids', 'results'] as $field) {
            if (isset($updates[$field]) && is_array($updates[$field])) {
                $updates[$field] = maybe_serialize($updates[$field]);
            }
        }
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->update(
            $table,
            $updates,
            ['id' => $batch_id],
            null,
            ['%s']
        );
        
        return $result !== false;
    }
    
    /**
     * Log API usage
     * 
     * @param array $data Usage data
     * @return bool Success status
     */
    public function log_api_usage($data) {
        global $wpdb;
        
        $table = $this->get_table_name('api_usage');
        
        $defaults = [
            'provider' => '',
            'model' => '',
            'tokens_used' => 0,
            'cost' => 0.0,
            'request_type' => '',
            'user_id' => get_current_user_id(),
            'date_used' => current_time('Y-m-d')
        ];
        
        $data = wp_parse_args($data, $defaults);
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $wpdb->insert(
            $table,
            $data,
            [
                '%s', // provider
                '%s', // model
                '%d', // tokens_used
                '%f', // cost
                '%s', // request_type
                '%d', // user_id
                '%s'  // date_used
            ]
        );
        
        return $result !== false;
    }
    
    /**
     * Get API usage statistics
     * 
     * @param string $period Period (today, week, month, all)
     * @param int $user_id User ID (0 for all users)
     * @return array Usage statistics
     */
    public function get_api_usage_stats($period = 'month', $user_id = 0) {
        global $wpdb;
        
        $table = $this->get_table_name('api_usage');
        
        $where_conditions = [];
        $where_values = [];
        
        // Date filter
        switch ($period) {
            case 'today':
                $where_conditions[] = 'date_used = %s';
                $where_values[] = current_time('Y-m-d');
                break;
            case 'week':
                $where_conditions[] = 'date_used >= %s';
                $where_values[] = gmdate('Y-m-d', strtotime('-7 days'));
                break;
            case 'month':
                $where_conditions[] = 'date_used >= %s';
                $where_values[] = gmdate('Y-m-d', strtotime('-30 days'));
                break;
        }
        
        if ($user_id > 0) {
            $where_conditions[] = 'user_id = %d';
            $where_values[] = absint($user_id);
        }
        
        $where_clause = !empty($where_conditions) ? 'WHERE ' . implode(' AND ', $where_conditions) : '';
        
        $sql = "SELECT 
                    provider,
                    COUNT(*) as requests,
                    SUM(tokens_used) as total_tokens,
                    SUM(cost) as total_cost
                FROM {$wpdb->prefix}apcig_api_usage 
                $where_clause
                GROUP BY provider
                ORDER BY total_cost DESC";
        
        if (!empty($where_values)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is prepared with $where_values
            $sql = $wpdb->prepare($sql, $where_values);
        }
        
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is safely prepared above when $where_values is not empty
        return $wpdb->get_results($sql);
    }
    
    /**
     * Clean up old records
     * 
     * @param int $days Days to keep records
     */
    public function cleanup_old_records($days = 90) {
        global $wpdb;
        
        $cutoff_date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
        
        // Clean up generations
        $generations_table = $this->get_table_name('generations');
        $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->prepare(
                "DELETE FROM {$wpdb->prefix}apcig_generations WHERE created_at < %s",
                $cutoff_date
            )
        );
        
        // Clean up completed batch jobs
        $batch_jobs_table = $this->get_table_name('batch_jobs');
        $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->prepare(
                "DELETE FROM {$wpdb->prefix}apcig_batch_jobs WHERE completed_at < %s AND status IN ('completed', 'failed')",
                $cutoff_date
            )
        );
        
        // Clean up API usage (keep longer)
        $api_usage_table = $this->get_table_name('api_usage');
        $api_cutoff_date = gmdate('Y-m-d H:i:s', strtotime("-365 days"));
        $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->prepare(
                "DELETE FROM {$wpdb->prefix}apcig_api_usage WHERE created_at < %s",
                $api_cutoff_date
            )
        );
    }
    
    /**
     * Cleanup scheduled events on deactivation
     */
    public function cleanup_scheduled_events() {
        wp_clear_scheduled_hook('apcig_cleanup_old_records');
        wp_clear_scheduled_hook('apcig_process_batch');
    }
    
    /**
     * Get generation statistics
     * 
     * @return array Statistics
     */
    public function get_generation_stats() {
        global $wpdb;
        
        $table = $this->get_table_name('generations');
        
        $stats = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            "SELECT 
                COUNT(*) as total_generations,
                COUNT(CASE WHEN applied = 1 THEN 1 END) as applied_generations,
                SUM(tokens_used) as total_tokens,
                SUM(cost) as total_cost,
                COUNT(DISTINCT product_id) as products_with_content
            FROM {$wpdb->prefix}apcig_generations"
        );
        
        return [
            'total_generations' => (int) $stats->total_generations,
            'applied_generations' => (int) $stats->applied_generations,
            'total_tokens' => (int) $stats->total_tokens,
            'total_cost' => (float) $stats->total_cost,
            'products_with_content' => (int) $stats->products_with_content
        ];
    }
}