const sqlite3 = require('sqlite3').verbose();
const path = require('path');

const dbPath = path.resolve(__dirname, 'inventory.db');
const db = new sqlite3.Database(dbPath);

db.serialize(() => {
    console.log('Running migration: Adding return_type to returns table...');

    db.run(`ALTER TABLE returns ADD COLUMN return_type TEXT DEFAULT 'Normal'`, (err) => {
        if (err) {
            if (err.message.includes('duplicate column name')) {
                console.log('Column return_type already exists.');
            } else {
                console.error('Error adding column:', err.message);
            }
        } else {
            console.log('Successfully added return_type column.');
        }
    });

    db.run(`UPDATE returns SET return_type = 'Normal' WHERE return_type IS NULL`, (err) => {
        if (err) console.error('Error updating existing records:', err);
        else console.log('Updated existing records to Normal.');
    });
    
    // Check if we can add the check constraint (SQLite usually doesn't support adding constraints to existing tables easily without recreating, 
    // so we'll skip the strict CHECK constraint enforcement in DB for now and handle it in code, 
    // or we'd need a more complex migration). 
    // For this environment, code-level validation is sufficient.
});

db.close(() => {
    console.log('Migration complete.');
});
