const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// Load env vars manually since we don't have dotenv
const envPath = path.resolve(__dirname, '../.env');
const envContent = fs.readFileSync(envPath, 'utf8');
const envVars = {};
envContent.split('\n').forEach(line => {
    const [key, value] = line.split('=');
    if (key && value) {
        envVars[key.trim()] = value.trim().replace(/"/g, ''); // Remove quotes
    }
});

const dbConfig = {
    host: envVars.DB_HOST || 'localhost',
    user: envVars.DB_USER || 'nordic_app_user',
    password: envVars.DB_PASSWORD,
    database: envVars.DB_NAME || 'nordic_storium',
    port: parseInt(envVars.DB_PORT || '3306')
};

const categories = [
    { name: 'Kontorsstolar', image: '/assets/Images/categories/office-chair.jpg', description: 'Ergonomiska stolar för arbete' },
    { name: 'Skrivbord', image: '/assets/Images/categories/desk.jpg', description: 'Höj- och sänkbara skrivbord' },
    { name: 'Konferensmöbler', image: '/assets/Images/categories/conference.jpg', description: 'För effektiva möten' },
    { name: 'Förvaring', image: '/assets/Images/categories/storage.jpg', description: 'Skåp och hyllor' },
    { name: 'Lounge & Soffor', image: '/assets/Images/categories/lounge.jpg', description: 'Bekväm inredning för pausutrymmen' }
];

const products = [
    {
        name: 'Ergonomisk Kontorsstol Herman Miller',
        price: 4500,
        originalPrice: 12000,
        image: '/assets/Images/products/herman-miller.jpg',
        category: 'Kontorsstolar',
        isPopular: true,
        isNew: false,
        created_at: '2025-11-15 10:00:00'
    },
    {
        name: 'Höj- och sänkbart Skrivbord 160x80',
        price: 2900,
        originalPrice: 6500,
        image: '/assets/Images/products/desk-white.jpg',
        category: 'Skrivbord',
        isPopular: true,
        isNew: false,
        created_at: '2025-12-01 10:00:00'
    },
    {
        name: 'Konferensstol Läder Svart',
        price: 1200,
        originalPrice: 3500,
        image: '/assets/Images/products/conf-chair.jpg',
        category: 'Konferensmöbler',
        isPopular: false,
        isNew: true,
        created_at: '2026-01-20 10:00:00'
    },
    {
        name: 'Designsoffa 3-sits Grå',
        price: 5500,
        originalPrice: 15000,
        image: '/assets/Images/products/sofa-grey.jpg',
        category: 'Lounge & Soffor',
        isPopular: true,
        isNew: true,
        created_at: '2026-01-25 10:00:00'
    },
    {
        name: 'Arkivskåp Plåt Vit',
        price: 1500,
        originalPrice: 3000,
        image: '/assets/Images/products/cabinet.jpg',
        category: 'Förvaring',
        isPopular: false,
        isNew: true,
        created_at: '2026-01-28 10:00:00'
    },
    {
        name: 'Kinnarps 6000 Plus',
        price: 1900,
        originalPrice: 6000,
        image: '/assets/Images/products/kinnarps.jpg',
        category: 'Kontorsstolar',
        isPopular: true,
        isNew: false,
        created_at: '2025-10-10 10:00:00'
    }
];

async function main() {
    let connection;
    try {
        console.log('Connecting to database...');
        connection = await mysql.createConnection(dbConfig);
        console.log('Connected!');

        // 1. Alter Categories Table
        console.log('Updating categories table schema...');
        try {
            await connection.query('ALTER TABLE categories ADD COLUMN image_url VARCHAR(255)');
            console.log('Added image_url column to categories');
        } catch (e) {
            if (e.code !== 'ER_DUP_FIELDNAME') throw e;
            console.log('image_url column already exists in categories');
        }

        // 2. Alter Products Table
        console.log('Updating products table schema...');
        const productColumns = [
            'ALTER TABLE products ADD COLUMN is_popular BOOLEAN DEFAULT FALSE',
            'ALTER TABLE products ADD COLUMN is_new BOOLEAN DEFAULT FALSE',
            'ALTER TABLE products ADD COLUMN is_trendy BOOLEAN DEFAULT FALSE',
            'ALTER TABLE products ADD COLUMN original_price DECIMAL(10, 2)'
        ];

        for (const sql of productColumns) {
            try {
                await connection.query(sql);
                console.log(`Executed: ${sql}`);
            } catch (e) {
                if (e.code !== 'ER_DUP_FIELDNAME') throw e;
                console.log(`Column already exists: ${sql}`);
            }
        }

        // 3. Clear Data
        console.log('Clearing existing data...');
        await connection.query('SET FOREIGN_KEY_CHECKS = 0');
        await connection.query('TRUNCATE TABLE products');
        await connection.query('TRUNCATE TABLE categories');
        await connection.query('SET FOREIGN_KEY_CHECKS = 1');

        // 4. Insert Categories
        console.log('Inserting categories...');
        for (const cat of categories) {
            await connection.execute(
                'INSERT INTO categories (name, image_url, description) VALUES (?, ?, ?)',
                [cat.name, cat.image, cat.description]
            );
        }

        // 5. Insert Products
        console.log('Inserting products...');
        const [catRows] = await connection.query('SELECT id, name FROM categories');

        for (const prod of products) {
            const cat = catRows.find(c => c.name === prod.category);
            if (!cat) {
                console.warn(`Category not found for product: ${prod.name}`);
                continue;
            }

            await connection.execute(
                `INSERT INTO products 
                (category_id, name, price, original_price, image_url, is_popular, is_new, created_at) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
                [
                    cat.id,
                    prod.name,
                    prod.price,
                    prod.originalPrice,
                    prod.image,
                    prod.isPopular,
                    prod.isNew,
                    prod.created_at
                ]
            );
        }

        console.log('✅ Migration and Seeding Completed Successfully!');

    } catch (error) {
        console.error('❌ Error:', error);
    } finally {
        if (connection) await connection.end();
    }
}

main();