<?php

/**
 * Database management for AI Magic plugin
 *
 * @link       https://insya.com/ai-magic
 * @since      1.0.7
 *
 * @package    Ai_Magic
 * @subpackage Ai_Magic/includes
 */

/**
 * Database schema and upgrade management
 *
 * @package    Ai_Magic
 * @subpackage Ai_Magic/includes
 * @author     AI Magic <info@insya.com>
 */
class Ai_Magic_Database
{
    /**
     * Create all required database tables
     *
     * @since    1.0.7
     */
    public static function create_tables()
    {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

        // Table for content generation history
        $table_history = $wpdb->prefix . 'ai_magic_history';
        $sql_history = "CREATE TABLE $table_history (
			id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
			user_id BIGINT UNSIGNED NOT NULL,
			post_id BIGINT UNSIGNED NULL,
			title VARCHAR(255) NOT NULL,
			prompt TEXT NOT NULL,
			content LONGTEXT NOT NULL,
			model VARCHAR(50) NOT NULL,
			tokens_used INT NOT NULL,
			generation_time FLOAT NOT NULL,
            cost DECIMAL(10,6) DEFAULT 0,
			is_favorite TINYINT(1) DEFAULT 0,
			created_at DATETIME NOT NULL,
			INDEX idx_user_id (user_id),
			INDEX idx_post_id (post_id),
			INDEX idx_created_at (created_at)
		) $charset_collate;";

        // Table for content templates
        $table_templates = $wpdb->prefix . 'ai_magic_templates';
        $sql_templates = "CREATE TABLE $table_templates (
			id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
			name VARCHAR(100) NOT NULL,
			description TEXT,
			category VARCHAR(50) NOT NULL,
			prompt_template TEXT NOT NULL,
			variables TEXT,
			is_default TINYINT(1) DEFAULT 0,
			user_id BIGINT UNSIGNED NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			INDEX idx_category (category),
			INDEX idx_user_id (user_id)
		) $charset_collate;";

        // Table for analytics
        $table_analytics = $wpdb->prefix . 'ai_magic_analytics';
        $sql_analytics = "CREATE TABLE $table_analytics (
			id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
			user_id BIGINT UNSIGNED NOT NULL,
			action_type VARCHAR(50) NOT NULL,
			model VARCHAR(50) NOT NULL,
			tokens_used INT NOT NULL,
			cost DECIMAL(10,6) NOT NULL,
			success TINYINT(1) NOT NULL,
			error_message TEXT NULL,
			created_at DATETIME NOT NULL,
			INDEX idx_user_id (user_id),
			INDEX idx_created_at (created_at),
			INDEX idx_action_type (action_type)
		) $charset_collate;";

        // Table for custom prompts
        $table_prompts = $wpdb->prefix . 'ai_magic_prompts';
        $sql_prompts = "CREATE TABLE $table_prompts (
			id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
			user_id BIGINT UNSIGNED NOT NULL,
			name VARCHAR(100) NOT NULL,
			description TEXT,
			prompt TEXT NOT NULL,
			variables TEXT,
			category VARCHAR(50),
			usage_count INT DEFAULT 0,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			INDEX idx_user_id (user_id),
			INDEX idx_category (category)
		) $charset_collate;";

        // Execute table creation
        dbDelta($sql_history);
        dbDelta($sql_templates);
        dbDelta($sql_analytics);
        dbDelta($sql_prompts);

        // Update database version
        update_option('ai_magic_db_version', AI_MAGIC_DB_VERSION);

        // Insert default templates
        self::insert_default_templates();
    }

    /**
     * Insert default content templates
     *
     * @since    1.0.7
     */
    private static function insert_default_templates()
    {
        global $wpdb;
        $table = $wpdb->prefix . 'ai_magic_templates';

        // Check if templates already exist
        $count = $wpdb->get_var("SELECT COUNT(*) FROM $table WHERE is_default = 1");
        if ($count > 0) {
            return; // Templates already inserted
        }

        $templates = array(
            array(
                'name' => 'Blog Post',
                'description' => 'General blog article with introduction, body, and conclusion',
                'category' => 'blog',
                'prompt_template' => 'Write a comprehensive blog post about "{{title}}". Include an engaging introduction, detailed body with multiple sections, and a strong conclusion. Target word count: {{word_count}} words. Writing style: {{style}}. Tone: {{tone}}. Language: {{language}}.',
                'variables' => '["title","word_count","style","tone","language"]'
            ),
            array(
                'name' => 'How-To Guide',
                'description' => 'Step-by-step tutorial format',
                'category' => 'tutorial',
                'prompt_template' => 'Create a detailed how-to guide on "{{title}}". Structure it with: 1) Introduction explaining what readers will learn, 2) Required materials/prerequisites, 3) Step-by-step instructions with clear numbering, 4) Tips and best practices, 5) Conclusion. Make it practical and easy to follow. Target word count: {{word_count}} words. Language: {{language}}.',
                'variables' => '["title","word_count","language"]'
            ),
            array(
                'name' => 'Listicle',
                'description' => 'Numbered list article (e.g., "10 Ways to...")',
                'category' => 'blog',
                'prompt_template' => 'Write an engaging listicle titled "{{title}}". Create {{list_count}} well-researched points, each with a descriptive heading and 2-3 paragraphs of explanation. Include an introduction that hooks the reader and a conclusion that summarizes the key takeaways. Writing style: {{style}}. Tone: {{tone}}. Language: {{language}}.',
                'variables' => '["title","list_count","style","tone","language"]'
            ),
            array(
                'name' => 'Product Review',
                'description' => 'Detailed product analysis and review',
                'category' => 'review',
                'prompt_template' => 'Write a comprehensive product review for "{{title}}". Include: 1) Product overview and specifications, 2) Key features and benefits, 3) Pros and cons, 4) Performance analysis, 5) Comparison with alternatives, 6) Final verdict and rating. Be honest and balanced. Target word count: {{word_count}} words. Tone: {{tone}}. Language: {{language}}.',
                'variables' => '["title","word_count","tone","language"]'
            ),
            array(
                'name' => 'Tutorial',
                'description' => 'Educational content with examples',
                'category' => 'tutorial',
                'prompt_template' => 'Create an educational tutorial on "{{title}}". Structure: 1) Introduction to the concept, 2) Why it\'s important, 3) Core concepts explained simply, 4) Practical examples, 5) Common mistakes to avoid, 6) Next steps for learning. Include code examples if relevant. Target word count: {{word_count}} words. Language: {{language}}.',
                'variables' => '["title","word_count","language"]'
            ),
            array(
                'name' => 'Product Description',
                'description' => 'E-commerce product copy',
                'category' => 'ecommerce',
                'prompt_template' => 'Write compelling product description for "{{title}}". Include: 1) Attention-grabbing headline, 2) Key features and benefits (focus on benefits over features), 3) Technical specifications, 4) Use cases and who it\'s for, 5) Call-to-action. Make it persuasive and SEO-friendly. Target word count: {{word_count}} words. Tone: {{tone}}. Language: {{language}}.',
                'variables' => '["title","word_count","tone","language"]'
            )
        );

        $current_time = current_time('mysql');

        foreach ($templates as $template) {
            $wpdb->insert(
                $table,
                array(
                    'name' => $template['name'],
                    'description' => $template['description'],
                    'category' => $template['category'],
                    'prompt_template' => $template['prompt_template'],
                    'variables' => $template['variables'],
                    'is_default' => 1,
                    'user_id' => null,
                    'created_at' => $current_time,
                    'updated_at' => $current_time
                ),
                array('%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s')
            );
        }
    }

    /**
     * Upgrade database if needed
     *
     * @since    1.0.7
     */
    public static function upgrade()
    {
        $installed_version = get_option('ai_magic_db_version', '0');

        if (version_compare($installed_version, AI_MAGIC_DB_VERSION, '<')) {
            self::create_tables();
        }
    }

    /**
     * Drop all plugin tables (used on uninstall)
     *
     * @since    1.0.7
     */
    public static function drop_tables()
    {
        global $wpdb;

        $tables = array(
            $wpdb->prefix . 'ai_magic_history',
            $wpdb->prefix . 'ai_magic_templates',
            $wpdb->prefix . 'ai_magic_analytics',
            $wpdb->prefix . 'ai_magic_prompts'
        );

        foreach ($tables as $table) {
            $wpdb->query("DROP TABLE IF EXISTS $table");
        }

        delete_option('ai_magic_db_version');
    }
}
