<?php
/**
 * ============================================================================
 * clean-database-content.php - Clean HTML from Database Content
 * ============================================================================
 * Run this once to clean existing database content
 * Usage: php clean-database-content.php
 */

// Adjust path to your config
require_once __DIR__ . '/../config/config.php';
require_once __DIR__ . '/../classes/ResponseFormatter.php';

use App\Database;
use App\ResponseFormatter;

echo "==============================================\n";
echo "Database Content Cleaning Script\n";
echo "==============================================\n\n";

$db = Database::getInstance();

// Clean agricultural_tips
echo "1. Cleaning agricultural_tips table...\n";
try {
    $tips = $db->fetchAll("SELECT id, title, content FROM agricultural_tips");
    $cleaned = 0;
    
    foreach ($tips as $tip) {
        $originalContent = $tip['content'];
        $cleanedContent = ResponseFormatter::cleanHtmlResponse($originalContent);
        
        // Only update if content changed
        if ($originalContent !== $cleanedContent) {
            $db->execute("UPDATE agricultural_tips SET content = ? WHERE id = ?", 
                        [$cleanedContent, $tip['id']]);
            $cleaned++;
            
            echo "   ✓ Cleaned: " . substr($tip['title'], 0, 50) . "...\n";
        }
    }
    
    echo "   Total cleaned: $cleaned / " . count($tips) . "\n\n";
    
} catch (Exception $e) {
    echo "   ✗ Error: " . $e->getMessage() . "\n\n";
}

// Clean crop_diseases
echo "2. Cleaning crop_diseases table...\n";
try {
    $diseases = $db->fetchAll("SELECT id, disease_name, symptoms, treatment, prevention FROM crop_diseases");
    $cleaned = 0;
    
    foreach ($diseases as $disease) {
        $originalSymptoms = $disease['symptoms'] ?? '';
        $originalTreatment = $disease['treatment'] ?? '';
        $originalPrevention = $disease['prevention'] ?? '';
        
        $cleanedSymptoms = ResponseFormatter::cleanHtmlResponse($originalSymptoms);
        $cleanedTreatment = ResponseFormatter::cleanHtmlResponse($originalTreatment);
        $cleanedPrevention = ResponseFormatter::cleanHtmlResponse($originalPrevention);
        
        // Check if any field changed
        if ($originalSymptoms !== $cleanedSymptoms || 
            $originalTreatment !== $cleanedTreatment || 
            $originalPrevention !== $cleanedPrevention) {
            
            $db->execute("UPDATE crop_diseases SET symptoms = ?, treatment = ?, prevention = ? WHERE id = ?",
                        [$cleanedSymptoms, $cleanedTreatment, $cleanedPrevention, $disease['id']]);
            $cleaned++;
            
            echo "   ✓ Cleaned: " . $disease['disease_name'] . "\n";
        }
    }
    
    echo "   Total cleaned: $cleaned / " . count($diseases) . "\n\n";
    
} catch (Exception $e) {
    echo "   ✗ Error: " . $e->getMessage() . "\n\n";
}

// Clean farming_schedules
echo "3. Cleaning farming_schedules table...\n";
try {
    $schedules = $db->fetchAll("SELECT id, title, description FROM farming_schedules");
    $cleaned = 0;
    
    foreach ($schedules as $schedule) {
        $originalDescription = $schedule['description'] ?? '';
        $cleanedDescription = ResponseFormatter::cleanHtmlResponse($originalDescription);
        
        if ($originalDescription !== $cleanedDescription) {
            $db->execute("UPDATE farming_schedules SET description = ? WHERE id = ?",
                        [$cleanedDescription, $schedule['id']]);
            $cleaned++;
            
            echo "   ✓ Cleaned: " . substr($schedule['title'], 0, 50) . "...\n";
        }
    }
    
    echo "   Total cleaned: $cleaned / " . count($schedules) . "\n\n";
    
} catch (Exception $e) {
    echo "   ✗ Error: " . $e->getMessage() . "\n\n";
}

// Clean ai_learned_patterns (if exists)
echo "4. Cleaning ai_learned_patterns table...\n";
try {
    $patterns = $db->fetchAll("SELECT id, response_template FROM ai_learned_patterns");
    $cleaned = 0;
    
    foreach ($patterns as $pattern) {
        $originalResponse = $pattern['response_template'] ?? '';
        $cleanedResponse = ResponseFormatter::cleanHtmlResponse($originalResponse);
        
        if ($originalResponse !== $cleanedResponse) {
            $db->execute("UPDATE ai_learned_patterns SET response_template = ? WHERE id = ?",
                        [$cleanedResponse, $pattern['id']]);
            $cleaned++;
        }
    }
    
    echo "   Total cleaned: $cleaned / " . count($patterns) . "\n\n";
    
} catch (Exception $e) {
    echo "   ✗ Error: " . $e->getMessage() . " (table may not exist)\n\n";
}

echo "==============================================\n";
echo "Cleaning Complete!\n";
echo "==============================================\n\n";

// Show example of cleaned content
echo "Example of cleaned content:\n";
echo "----------------------------\n";

try {
    $example = $db->fetchOne("SELECT content FROM agricultural_tips LIMIT 1");
    if ($example) {
        echo substr($example['content'], 0, 500);
        if (strlen($example['content']) > 500) {
            echo "...\n";
        }
    }
} catch (Exception $e) {
    echo "Could not fetch example\n";
}

echo "\n\nDone! Your database content is now clean and formatted.\n";
echo "You can now test the AI assistant with improved responses.\n\n";