<?php
/**
 * Extract FAQs from faq/index.php and create SQL insert statements
 */
require_once dirname(__DIR__) . '/config.php';

$faq_content = file_get_contents(dirname(__DIR__) . '/faq/index.php');
$faqs = [];

// Extract all FAQ items using regex
preg_match_all('/<div class="elementor-toggle-item">.*?<a class="elementor-toggle-title"[^>]*>(.*?)<\/a>.*?<div[^>]*class="elementor-tab-content"[^>]*>(.*?)<\/div>/s', $faq_content, $matches, PREG_SET_ORDER);

foreach ($matches as $index => $match) {
    $question = strip_tags(trim($match[1]));
    $answer = trim($match[2]);
    // Clean up HTML entities and extra whitespace
    $answer = preg_replace('/\s+/', ' ', $answer);
    $answer = html_entity_decode($answer, ENT_QUOTES | ENT_HTML5, 'UTF-8');
    
    if (!empty($question) && !empty($answer)) {
        $faqs[] = [
            'question' => $question,
            'answer' => $answer,
            'order' => $index + 1
        ];
    }
}

// Create SQL
$sql = "CREATE TABLE IF NOT EXISTS `faqs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question` text NOT NULL,
  `answer` text NOT NULL,
  `display_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `display_order` (`display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n";

$sql .= "INSERT INTO `faqs` (`question`, `answer`, `display_order`) VALUES\n";

$values = [];
foreach ($faqs as $faq) {
    $conn = get_db_connection();
    $question = $conn->real_escape_string($faq['question']);
    $answer = $conn->real_escape_string($faq['answer']);
    $values[] = "('$question', '$answer', {$faq['order']})";
}

$sql .= implode(",\n", $values) . ";\n";

file_put_contents(__DIR__ . '/faqs.sql', $sql);

echo "Found " . count($faqs) . " FAQs\n";
echo "SQL file created: admin/faqs.sql\n";
echo "\nFirst few FAQs:\n";
foreach (array_slice($faqs, 0, 3) as $faq) {
    echo "- Q: " . substr($faq['question'], 0, 50) . "...\n";
}
