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

const dbPath = path.join(__dirname, 'inventory.db');
const backupPath = path.join(__dirname, 'inventory.db.backup_amount_paid');

// 1. Create Backup
console.log('Creating backup...');
fs.copyFileSync(dbPath, backupPath);
console.log(`Backup created at ${backupPath}`);

const db = new sqlite3.Database(dbPath);

db.serialize(() => {
    // 2. Add Column
    console.log('Adding amount_paid column...');
    db.run('ALTER TABLE transactions ADD COLUMN amount_paid REAL DEFAULT 0;', (err) => {
        if (err) {
            if (err.message.includes('duplicate column name')) {
                console.log('Column amount_paid already exists.');
            } else {
                console.error('Error adding column:', err);
                process.exit(1);
            }
        } else {
            console.log('Column amount_paid added successfully.');
        }

        // 3. Verify
        db.all('PRAGMA table_info(transactions)', [], (err, rows) => {
            if (err) {
                console.error('Error verifying table info:', err);
                process.exit(1);
            }

            const hasColumn = rows.some(r => r.name === 'amount_paid');
            console.log('Verification - Has amount_paid:', hasColumn);

            if (hasColumn) {
                console.log('Migration completed successfully.');
            } else {
                console.error('Migration failed: Column not found after alter.');
                process.exit(1);
            }
        });
    });
});

db.close();
