const fs = require('fs');
const path = require('path');
const db = require('../src/db');

const OUTPUT_FILE = path.join(__dirname, '../blog_posts_import.sql');

async function generateSql() {
    try {
        console.log('Fetching blog posts...');
        // Select all posts, or just drafts? User asked for "file to import", usually implies the work we just did.
        // Let's export ALL blog posts to be safe, or maybe just the drafts?
        // Let's export ALL.
        const { rows } = await db.query("SELECT * FROM blog_posts");
        
        console.log(`Found ${rows.length} posts.`);
        
        let sqlContent = "INSERT IGNORE INTO `blog_posts` (`title`, `slug`, `content`, `excerpt`, `featured_image`, `featured_image_alt`, `author_id`, `status`, `created_at`, `updated_at`, `category`, `seo_title`, `seo_description`, `focus_keyword`) VALUES\n";
        
        const values = rows.map(row => {
            return `(` +
                escape(row.title) + ", " +
                escape(row.slug) + ", " +
                escape(row.content) + ", " +
                escape(row.excerpt) + ", " +
                escape(row.featured_image) + ", " +
                escape(row.featured_image_alt) + ", " +
                (row.author_id || 'NULL') + ", " +
                escape(row.status) + ", " +
                escapeDate(row.created_at) + ", " +
                escapeDate(row.updated_at) + ", " +
                escape(row.category) + ", " +
                escape(row.seo_title) + ", " +
                escape(row.seo_description) + ", " +
                escape(row.focus_keyword) +
            `)`;
        }).join(",\n");
        
        sqlContent += values + ";\n";
        
        fs.writeFileSync(OUTPUT_FILE, sqlContent);
        console.log(`SQL file generated at: ${OUTPUT_FILE}`);
        process.exit(0);
        
    } catch (error) {
        console.error('Error:', error);
        process.exit(1);
    }
}

function escape(str) {
    if (str === null || str === undefined) return 'NULL';
    // Basic SQL escaping
    return "'" + String(str).replace(/'/g, "''").replace(/\\/g, "\\\\") + "'";
}

function escapeDate(date) {
    if (!date) return 'NULL';
    return "'" + new Date(date).toISOString().slice(0, 19).replace('T', ' ') + "'";
}

generateSql();
