/**
 * Seed script: Migrate products from old B2B (Python) project to B2B-js
 * 
 * Reads products directly from the old SQLite database and copies images.
 * Run: node seed_from_old_db.js
 */

const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

// Paths
const OLD_DB_PATH = path.join('/Users/yasindanish13/PycharmProjects/B2B/instance/database.db');
const NEW_DB_PATH = path.join(__dirname, 'instance', 'database.db');
const OLD_IMAGES_DIR = path.join('/Users/yasindanish13/PycharmProjects/B2B/static/uploads/products');
const NEW_IMAGES_DIR = path.join(__dirname, 'public', 'uploads', 'products');

// Ensure target directories exist
if (!fs.existsSync(path.join(__dirname, 'instance'))) {
    fs.mkdirSync(path.join(__dirname, 'instance'), { recursive: true });
}
if (!fs.existsSync(NEW_IMAGES_DIR)) {
    fs.mkdirSync(NEW_IMAGES_DIR, { recursive: true });
}

// Open both databases
const oldDb = new Database(OLD_DB_PATH, { readonly: true });
const newDb = new Database(NEW_DB_PATH);
newDb.pragma('foreign_keys = ON');

// Ensure products table exists in new DB
newDb.exec(`
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        product_name TEXT NOT NULL,
        category TEXT NOT NULL,
        product_options TEXT,
        product_rate TEXT,
        stock_status TEXT NOT NULL,
        image_filename TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
`);

// Clear existing products in new DB
const existingCount = newDb.prepare('SELECT COUNT(*) as count FROM products').get().count;
console.log(`\n🗄️  Existing products in B2B-js database: ${existingCount}`);

if (existingCount > 0) {
    newDb.exec('DELETE FROM products');
    console.log(`🗑️  Cleared ${existingCount} existing products`);
}

// Read all products from old DB
const oldProducts = oldDb.prepare('SELECT * FROM products ORDER BY id').all();
console.log(`\n📦 Found ${oldProducts.length} products in old B2B database`);

// Insert products into new DB
const insertStmt = newDb.prepare(`
    INSERT INTO products (id, product_name, category, product_options, product_rate, stock_status, image_filename, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
`);

const insertMany = newDb.transaction((products) => {
    let inserted = 0;
    for (const p of products) {
        insertStmt.run(
            p.id,
            p.product_name,
            p.category,
            p.product_options || '',
            p.product_rate || '',
            p.stock_status || 'in_stock',
            p.image_filename || '',
            p.created_at || new Date().toISOString(),
            p.updated_at || new Date().toISOString()
        );
        inserted++;
    }
    return inserted;
});

const insertedCount = insertMany(oldProducts);
console.log(`✅ Inserted ${insertedCount} products into B2B-js database`);

// Copy product images
console.log(`\n🖼️  Copying product images...`);
let imagesCopied = 0;
let imagesSkipped = 0;
let imagesFailed = 0;

if (fs.existsSync(OLD_IMAGES_DIR)) {
    const imageFiles = fs.readdirSync(OLD_IMAGES_DIR).filter(f => !f.startsWith('.'));

    for (const file of imageFiles) {
        const srcPath = path.join(OLD_IMAGES_DIR, file);
        const destPath = path.join(NEW_IMAGES_DIR, file);

        try {
            if (fs.statSync(srcPath).isFile()) {
                if (fs.existsSync(destPath)) {
                    imagesSkipped++;
                } else {
                    fs.copyFileSync(srcPath, destPath);
                    imagesCopied++;
                }
            }
        } catch (err) {
            console.error(`   ❌ Failed to copy ${file}: ${err.message}`);
            imagesFailed++;
        }
    }
}

console.log(`   ✅ Copied: ${imagesCopied}`);
console.log(`   ⏭️  Skipped (already existed): ${imagesSkipped}`);
if (imagesFailed > 0) console.log(`   ❌ Failed: ${imagesFailed}`);

// Verify
const finalCount = newDb.prepare('SELECT COUNT(*) as count FROM products').get().count;
const categories = newDb.prepare('SELECT DISTINCT category FROM products ORDER BY category').all();
console.log(`\n📊 Final Summary:`);
console.log(`   Total products in B2B-js database: ${finalCount}`);
console.log(`   Categories: ${categories.map(c => c.category).join(', ')}`);

// Close databases
oldDb.close();
newDb.close();

console.log(`\n🎉 Migration complete!\n`);
