<?php
if ( ! defined( 'ABSPATH' ) ) exit;

/**
 * VMO Consultation Database Manager
 * Handles all consultation data storage, retrieval, and management
 */

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

class VMO_Consultation_Database {
    
    private $table_name;
    private $media_table_name;
    
    public function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'vmo_consultations';
        $this->media_table_name = $wpdb->prefix . 'vmo_consultation_media';
        
        // ✅ AUTOMATIC SELF-HEALING: Check and fix database schema on every init
        add_action('init', array($this, 'ensure_database_schema'), 5);
    }
    
    /**
     * ✅ AUTOMATIC SELF-HEALING: Ensures database schema is correct
     * This runs on every page load and auto-fixes missing columns
     * NO USER INTERACTION REQUIRED!
     */
    public function ensure_database_schema() {
        global $wpdb;
        
        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$this->table_name}'") === $this->table_name;
        
        if (!$table_exists) {
            // Table doesn't exist, create it
            $this->create_consultation_tables();
            return;
        }
        
        // ✅ SELF-HEALING: Check and add missing columns automatically
        $required_columns = array(
            // Phase tracking
            'phase' => "ALTER TABLE {$this->table_name} ADD COLUMN phase TINYINT UNSIGNED DEFAULT 1",
            'phase_state' => "ALTER TABLE {$this->table_name} ADD COLUMN phase_state LONGTEXT DEFAULT NULL",

            // Core persisted artifacts (JSON strings)
            'business_summary' => "ALTER TABLE {$this->table_name} ADD COLUMN business_summary LONGTEXT DEFAULT NULL",
            'research_data' => "ALTER TABLE {$this->table_name} ADD COLUMN research_data LONGTEXT DEFAULT NULL",
            'approved_checklist' => "ALTER TABLE {$this->table_name} ADD COLUMN approved_checklist LONGTEXT DEFAULT NULL",
            'stripe_account_id' => "ALTER TABLE {$this->table_name} ADD COLUMN stripe_account_id VARCHAR(255) DEFAULT NULL",
            'products' => "ALTER TABLE {$this->table_name} ADD COLUMN products LONGTEXT DEFAULT NULL",
            'generated_content' => "ALTER TABLE {$this->table_name} ADD COLUMN generated_content LONGTEXT DEFAULT NULL",
            'client_data' => "ALTER TABLE {$this->table_name} ADD COLUMN client_data LONGTEXT DEFAULT NULL",
            'start_time' => "ALTER TABLE {$this->table_name} ADD COLUMN start_time DATETIME DEFAULT CURRENT_TIMESTAMP",
            'end_time' => "ALTER TABLE {$this->table_name} ADD COLUMN end_time DATETIME DEFAULT NULL",

            // Legacy/back-compat fields
            'business_name' => "ALTER TABLE {$this->table_name} ADD COLUMN business_name VARCHAR(255) DEFAULT NULL",
            'business_type' => "ALTER TABLE {$this->table_name} ADD COLUMN business_type VARCHAR(100) DEFAULT NULL",
            'consultation_data' => "ALTER TABLE {$this->table_name} ADD COLUMN consultation_data LONGTEXT DEFAULT NULL",
            'conversation_log' => "ALTER TABLE {$this->table_name} ADD COLUMN conversation_log LONGTEXT DEFAULT NULL",
            'business_analysis' => "ALTER TABLE {$this->table_name} ADD COLUMN business_analysis LONGTEXT DEFAULT NULL",
            'recommendations' => "ALTER TABLE {$this->table_name} ADD COLUMN recommendations LONGTEXT DEFAULT NULL",
            'website_data' => "ALTER TABLE {$this->table_name} ADD COLUMN website_data LONGTEXT DEFAULT NULL"
        );
        
        // Get existing columns
        $existing_columns = $wpdb->get_col("DESCRIBE {$this->table_name}", 0);
        
        // Add any missing columns silently
        foreach ($required_columns as $column_name => $alter_sql) {
            if (!in_array($column_name, $existing_columns)) {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->query($alter_sql);
                // Log for debugging (won't show to user)
                error_log("VMO Plugin: Auto-added missing column '{$column_name}' to {$this->table_name}");
            }
        }
        
        // Ensure media table exists
        $media_table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$this->media_table_name}'") === $this->media_table_name;
        if (!$media_table_exists) {
            $this->create_media_table();
        }
    }
    
    /**
     * Create database tables for consultation data
     */
    public function create_tables() {
        $this->create_consultation_tables();
    }
    
    /**
     * Create consultation database tables (alias for compatibility)
     */
    public function create_consultation_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        // Main consultations table
        // Keep aligned with vmo_create_plugin_tables() in vmo-ai-website-builder.php.
        $sql = "CREATE TABLE IF NOT EXISTS {$this->table_name} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            session_id VARCHAR(255) NOT NULL,
            user_id BIGINT UNSIGNED DEFAULT NULL,

            phase TINYINT UNSIGNED DEFAULT 1,
            phase_state LONGTEXT DEFAULT NULL,

            conversation_log LONGTEXT DEFAULT NULL,
            business_summary LONGTEXT DEFAULT NULL,
            research_data LONGTEXT DEFAULT NULL,
            approved_checklist LONGTEXT DEFAULT NULL,
            stripe_account_id VARCHAR(255) DEFAULT NULL,
            products LONGTEXT DEFAULT NULL,
            generated_content LONGTEXT DEFAULT NULL,

            start_time DATETIME DEFAULT CURRENT_TIMESTAMP,
            end_time DATETIME DEFAULT NULL,
            status VARCHAR(50) DEFAULT 'in_progress',
            client_data LONGTEXT DEFAULT NULL,

            business_name VARCHAR(255) DEFAULT NULL,
            business_type VARCHAR(100) DEFAULT NULL,
            consultation_data LONGTEXT DEFAULT NULL,
            business_analysis LONGTEXT DEFAULT NULL,
            recommendations LONGTEXT DEFAULT NULL,
            website_data LONGTEXT DEFAULT NULL,

            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY session_id (session_id),
            KEY user_id (user_id),
            KEY status (status),
            KEY phase (phase)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Create media table
        $this->create_media_table();
    }
    
    /**
     * Create media attachments table
     */
    private function create_media_table() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();
        
        $media_sql = "CREATE TABLE IF NOT EXISTS {$this->media_table_name} (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            consultation_id mediumint(9) NOT NULL,
            media_url varchar(500) NOT NULL,
            media_type varchar(50),
            media_title varchar(255),
            upload_context text,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY consultation_id (consultation_id)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($media_sql);
    }
    
    /**
     * Save or update consultation data
     */
    public function save_consultation($user_id, $session_id, $data) {
        global $wpdb;
        
        $existing = $this->get_consultation_by_session($session_id);
        
        if ($existing) {
            // Update existing consultation
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $result = $wpdb->update(
                $this->table_name,
                array(
                    'phase' => isset($data['phase']) ? (int) $data['phase'] : (int) ($existing->phase ?? 1),
                    'phase_state' => isset($data['phase_state'])
                        ? (is_string($data['phase_state']) ? $data['phase_state'] : json_encode($data['phase_state']))
                        : ($existing->phase_state ?? null),
                    'business_name' => $data['business_name'] ?? $existing->business_name,
                    'business_type' => $data['business_type'] ?? $existing->business_type,
                    'consultation_data' => isset($data['consultation_data'])
                        ? json_encode($data['consultation_data'])
                        : ($existing->consultation_data ?? json_encode([])),
                    'conversation_log' => isset($data['conversation_log'])
                        ? json_encode($data['conversation_log'])
                        : ($existing->conversation_log ?? json_encode([])),
                    'business_summary' => isset($data['business_summary'])
                        ? (is_string($data['business_summary']) ? $data['business_summary'] : json_encode($data['business_summary']))
                        : ($existing->business_summary ?? null),
                    'research_data' => isset($data['research_data'])
                        ? (is_string($data['research_data']) ? $data['research_data'] : json_encode($data['research_data']))
                        : ($existing->research_data ?? null),
                    'approved_checklist' => isset($data['approved_checklist'])
                        ? (is_string($data['approved_checklist']) ? $data['approved_checklist'] : json_encode($data['approved_checklist']))
                        : ($existing->approved_checklist ?? null),
                    'stripe_account_id' => $data['stripe_account_id'] ?? ($existing->stripe_account_id ?? null),
                    'products' => isset($data['products'])
                        ? (is_string($data['products']) ? $data['products'] : json_encode($data['products']))
                        : ($existing->products ?? null),
                    'generated_content' => isset($data['generated_content'])
                        ? (is_string($data['generated_content']) ? $data['generated_content'] : json_encode($data['generated_content']))
                        : ($existing->generated_content ?? null),
                    'client_data' => isset($data['client_data'])
                        ? (is_string($data['client_data']) ? $data['client_data'] : json_encode($data['client_data']))
                        : ($existing->client_data ?? null),
                    'business_analysis' => $data['business_analysis'] ?? $existing->business_analysis,
                    'recommendations' => $data['recommendations'] ?? $existing->recommendations,
                    'website_data' => isset($data['website_data'])
                        ? (is_string($data['website_data']) ? $data['website_data'] : json_encode($data['website_data']))
                        : ($existing->website_data ?? null),
                    'status' => $data['status'] ?? $existing->status,
                    'updated_at' => current_time('mysql')
                ),
                array('session_id' => $session_id),
                array(
                    '%d', '%s',
                    '%s', '%s', '%s', '%s',
                    '%s', '%s', '%s', '%s', '%s', '%s', '%s',
                    '%s', '%s', '%s',
                    '%s', '%s', '%s',
                    '%s', '%s'
                ),
                array('%s')
            );
            
            return $existing->id;
        } else {
            // Insert new consultation
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $result = $wpdb->insert(
                $this->table_name,
                array(
                    'user_id' => $user_id ? (int) $user_id : null,
                    'session_id' => $session_id,
                    'phase' => isset($data['phase']) ? (int) $data['phase'] : 1,
                    'phase_state' => isset($data['phase_state'])
                        ? (is_string($data['phase_state']) ? $data['phase_state'] : json_encode($data['phase_state']))
                        : null,
                    'business_name' => $data['business_name'] ?? '',
                    'business_type' => $data['business_type'] ?? '',
                    'consultation_data' => json_encode($data['consultation_data'] ?? []),
                    'conversation_log' => json_encode($data['conversation_log'] ?? []),
                    'business_summary' => isset($data['business_summary'])
                        ? (is_string($data['business_summary']) ? $data['business_summary'] : json_encode($data['business_summary']))
                        : null,
                    'research_data' => isset($data['research_data'])
                        ? (is_string($data['research_data']) ? $data['research_data'] : json_encode($data['research_data']))
                        : null,
                    'approved_checklist' => isset($data['approved_checklist'])
                        ? (is_string($data['approved_checklist']) ? $data['approved_checklist'] : json_encode($data['approved_checklist']))
                        : null,
                    'stripe_account_id' => $data['stripe_account_id'] ?? null,
                    'products' => isset($data['products'])
                        ? (is_string($data['products']) ? $data['products'] : json_encode($data['products']))
                        : null,
                    'generated_content' => isset($data['generated_content'])
                        ? (is_string($data['generated_content']) ? $data['generated_content'] : json_encode($data['generated_content']))
                        : null,
                    'client_data' => isset($data['client_data'])
                        ? (is_string($data['client_data']) ? $data['client_data'] : json_encode($data['client_data']))
                        : null,
                    'business_analysis' => $data['business_analysis'] ?? '',
                    'recommendations' => $data['recommendations'] ?? '',
                    'website_data' => isset($data['website_data'])
                        ? (is_string($data['website_data']) ? $data['website_data'] : json_encode($data['website_data']))
                        : '',
                    'status' => $data['status'] ?? 'in_progress',
                    'created_at' => current_time('mysql')
                ),
                array(
                    '%d', '%s',
                    '%d', '%s',
                    '%s', '%s', '%s', '%s',
                    '%s', '%s', '%s', '%s', '%s', '%s', '%s',
                    '%s', '%s', '%s',
                    '%s', '%s', '%s',
                    '%s'
                )
            );
            
            return $wpdb->insert_id;
        }
    }
    
    /**
     * Get consultation by session ID
     */
    public function get_consultation_by_session($session_id) {
        global $wpdb;
        
        return $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM {$this->table_name} WHERE session_id = %s", $session_id)
        );
    }
    
    /**
     * Get all consultations for a user
     */
    public function get_user_consultations($user_id) {
        global $wpdb;
        
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$this->table_name} WHERE user_id = %d ORDER BY updated_at DESC", 
                $user_id
            )
        );
    }

    /**
     * Get the latest consultation row for a user
     */
    public function get_latest_consultation_by_user($user_id) {
        global $wpdb;
        return $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$this->table_name} WHERE user_id = %d ORDER BY updated_at DESC LIMIT 1",
                $user_id
            )
        );
    }

    /**
     * Delete a consultation by session_id.
     * Also removes associated media records.
     */
    public function delete_consultation($session_id) {
        global $wpdb;
        if (empty($session_id)) return false;

        // Get the consultation row first (for its PK, needed for media table)
        $row = $this->get_consultation_by_session($session_id);
        if ($row) {
            // Delete media records linked to this consultation
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->delete(
                $wpdb->prefix . 'vmo_consultation_media',
                array('consultation_id' => $row->id),
                array('%d')
            );
        }

        // Delete the consultation row itself
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->delete(
            $this->table_name,
            array('session_id' => $session_id),
            array('%s')
        );

        return $result !== false;
    }
    
    /**
     * Save media attachment for consultation
     */
    public function save_consultation_media($consultation_id, $media_data) {
        global $wpdb;
        // Normalize context to JSON string
        $context = $media_data['context'] ?? '';
        if (is_array($context)) {
            $context = wp_json_encode($context);
        }
        $title = isset($media_data['title']) ? $media_data['title'] : '';
        $type  = isset($media_data['type']) ? $media_data['type'] : '';
        $url   = isset($media_data['url']) ? $media_data['url'] : '';
        if (!$url) { return false; }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->insert(
            $this->media_table_name,
            array(
                'consultation_id' => $consultation_id,
                'media_url' => $url,
                'media_type' => $type,
                'media_title' => $title,
                'upload_context' => $context,
                'created_at' => current_time('mysql')
            ),
            array('%d', '%s', '%s', '%s', '%s', '%s')
        );
    }
    
    /**
     * Get media for consultation
     */
    public function get_consultation_media($consultation_id) {
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$this->media_table_name} WHERE consultation_id = %d ORDER BY created_at ASC", 
                $consultation_id
            )
        );
        // Normalize rows to arrays with decoded context
        $out = array();
        foreach ($rows as $row) {
            $ctx = $row->upload_context;
            $decoded = null;
            if (is_string($ctx) && $ctx !== '') {
                $decoded = json_decode($ctx, true);
            }
            $out[] = array(
                'id' => intval($row->id),
                'consultation_id' => intval($row->consultation_id),
                'url' => $row->media_url,
                'type' => $row->media_type,
                'title' => $row->media_title,
                'context' => is_array($decoded) ? $decoded : $ctx,
            );
        }
        return $out;
    }

    /**
     * Helper: Get media by WordPress attachment id, if stored in context
     */
    public function find_media_by_attachment($consultation_id, $attachment_id) {
        $media = $this->get_consultation_media($consultation_id);
        foreach ($media as $m) {
            if (is_array($m['context']) && isset($m['context']['attachment_id']) && intval($m['context']['attachment_id']) === intval($attachment_id)) {
                return $m;
            }
        }
        return null;
    }
    
    /**
     * Generate monthly business report
     */
    public function generate_monthly_report($consultation_id) {
        $consultation = $this->get_consultation($consultation_id);
        if (!$consultation) return false;
        
        $consultation_data = json_decode($consultation->consultation_data, true);
        $business_analysis = $consultation->business_analysis;
        
        // This would integrate with various analytics and generate comprehensive report
        $report = array(
            'business_name' => $consultation->business_name,
            'report_date' => current_time('Y-m-d'),
            'progress_summary' => $this->generate_progress_summary($consultation_data),
            'seo_performance' => $this->get_seo_performance($consultation_id),
            'lead_generation' => $this->get_lead_metrics($consultation_id),
            'competitive_analysis' => $this->update_competitive_analysis($consultation_data),
            'next_actions' => $this->generate_next_actions($consultation_data),
            'roadmap_progress' => $this->calculate_roadmap_progress($consultation_data)
        );
        
        return $report;
    }
    
    /**
     * Send monthly report email
     */
    public function send_monthly_report($user_id, $consultation_id) {
        $user = get_userdata($user_id);
        $report = $this->generate_monthly_report($consultation_id);
        
        if (!$user || !$report) return false;
        
        $subject = "Your Monthly Business Progress Report - " . $report['business_name'];
        
        $message = $this->format_monthly_report_email($report);
        
        $headers = array('Content-Type: text/html; charset=UTF-8');
        
        return wp_mail($user->user_email, $subject, $message, $headers);
    }
    
    /**
     * Format monthly report for email
     */
    private function format_monthly_report_email($report) {
        ob_start();
        ?>
        <!DOCTYPE html>
        <html>
        <head>
            <meta charset="UTF-8">
            <title>Monthly Business Report</title>
        </head>
        <body style="font-family: Arial, sans-serif; line-height: 1.6; color: #333;">
            <div style="max-width: 600px; margin: 0 auto; background: #f9f9f9; padding: 20px;">
                <header style="background: #1a1a2e; color: #00ffff; padding: 20px; text-align: center; border-radius: 8px 8px 0 0;">
                    <h1 style="margin: 0;">🏢 VMO Business Report</h1>
                    <h2 style="margin: 10px 0 0 0; color: #88ffff;"><?php echo esc_html($report['business_name']); ?></h2>
                    <p style="margin: 5px 0 0 0; color: #ccffff;">Monthly Progress Report - <?php echo esc_html($report['report_date']); ?></p>
                </header>
                
                <main style="background: white; padding: 30px; border-radius: 0 0 8px 8px;">
                    <section style="margin-bottom: 30px;">
                        <h3 style="color: #1a1a2e; border-bottom: 2px solid #00ffff; padding-bottom: 5px;">📊 Progress Summary</h3>
                        <p><?php echo nl2br(esc_html($report['progress_summary'])); ?></p>
                    </section>
                    
                    <section style="margin-bottom: 30px;">
                        <h3 style="color: #1a1a2e; border-bottom: 2px solid #00ffff; padding-bottom: 5px;">🎯 SEO Performance</h3>
                        <p><?php echo nl2br(esc_html($report['seo_performance'])); ?></p>
                    </section>
                    
                    <section style="margin-bottom: 30px;">
                        <h3 style="color: #1a1a2e; border-bottom: 2px solid #00ffff; padding-bottom: 5px;">📈 Lead Generation</h3>
                        <p><?php echo nl2br(esc_html($report['lead_generation'])); ?></p>
                    </section>
                    
                    <section style="margin-bottom: 30px;">
                        <h3 style="color: #1a1a2e; border-bottom: 2px solid #00ffff; padding-bottom: 5px;">🏆 Competitive Analysis</h3>
                        <p><?php echo nl2br(esc_html($report['competitive_analysis'])); ?></p>
                    </section>
                    
                    <section style="margin-bottom: 30px;">
                        <h3 style="color: #1a1a2e; border-bottom: 2px solid #00ffff; padding-bottom: 5px;">🗺️ Roadmap Progress</h3>
                        <p><?php echo nl2br(esc_html($report['roadmap_progress'])); ?></p>
                    </section>
                    
                    <section style="margin-bottom: 30px;">
                        <h3 style="color: #1a1a2e; border-bottom: 2px solid #00ffff; padding-bottom: 5px;">🚀 Recommended Actions</h3>
                        <p><?php echo nl2br(esc_html($report['next_actions'])); ?></p>
                    </section>
                    
                    <footer style="background: #f0f8ff; padding: 20px; border-radius: 8px; text-align: center; margin-top: 30px;">
                        <p style="margin: 0; color: #1a1a2e;"><strong>Visit your VMO AI Builder dashboard to manage your business activities and track progress.</strong></p>
                        <p style="margin: 10px 0 0 0; color: #666; font-size: 0.9em;">This report is generated by VMO AI Business Intelligence system based on your consultation data and ongoing business activities.</p>
                    </footer>
                </main>
            </div>
        </body>
        </html>
        <?php
        return ob_get_clean();
    }
    
    /**
     * Helper methods for report generation
     */
    private function generate_progress_summary($consultation_data) {
        return "Based on your business consultation and ongoing activities, your business is progressing according to the established roadmap.";
    }
    
    private function get_seo_performance($consultation_id) {
        return "SEO monitoring and optimization activities are being tracked and executed according to your business strategy.";
    }
    
    private function get_lead_metrics($consultation_id) {
        return "Lead generation activities are being monitored and optimized based on your target market analysis.";
    }
    
    private function update_competitive_analysis($consultation_data) {
        return "Competitive landscape analysis has been updated with current market conditions and competitor activities.";
    }
    
    private function generate_next_actions($consultation_data) {
        return "Continue implementing the recommended business strategies and monitor website performance metrics.";
    }
    
    private function calculate_roadmap_progress($consultation_data) {
        return "Your business roadmap implementation is on track with established milestones.";
    }
    
    /**
     * Get consultation by ID
     */
    public function get_consultation($consultation_id) {
        global $wpdb;
        
        return $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM {$this->table_name} WHERE id = %d", $consultation_id)
        );
    }
}

// Initialize the consultation database
add_action('plugins_loaded', function() {
    new VMO_Consultation_Database();
}, 20);
