const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./cve_database.db');

console.log('🔄 Starting database migration for multi-vendor support...\n');

db.serialize(() => {
    // Backup existing data
    console.log('📦 Creating backup tables...');
    db.run(`CREATE TABLE IF NOT EXISTS cves_backup AS SELECT * FROM cves`, (err) => {
        if (err) console.error('Backup error:', err);
        else console.log('✓ CVEs backed up');
    });

    db.run(`CREATE TABLE IF NOT EXISTS documents_backup AS SELECT * FROM documents`, (err) => {
        if (err) console.error('Backup error:', err);
        else console.log('✓ Documents backed up');
    });

    // Drop old table
    console.log('\n🗑️  Dropping old cves table...');
    db.run(`DROP TABLE IF EXISTS cves`, (err) => {
        if (err) {
            console.error('Drop error:', err);
            return;
        }
        console.log('✓ Old table dropped');

        // Create new table with UNIQUE(cve_id, vendor) instead of UNIQUE(cve_id)
        console.log('\n🏗️  Creating new cves table with multi-vendor support...');
        db.run(`
            CREATE TABLE cves (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                cve_id VARCHAR(20) NOT NULL,
                vendor VARCHAR(100) NOT NULL,
                severity VARCHAR(20) NOT NULL,
                description TEXT,
                published_date DATE,
                status VARCHAR(50) DEFAULT 'Open',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(cve_id, vendor)
            )
        `, (err) => {
            if (err) {
                console.error('Create error:', err);
                return;
            }
            console.log('✓ New table created with UNIQUE(cve_id, vendor)');

            // Restore data
            console.log('\n📥 Restoring data...');
            db.run(`INSERT INTO cves SELECT * FROM cves_backup`, (err) => {
                if (err) {
                    console.error('Restore error:', err);
                    return;
                }
                console.log('✓ Data restored');

                // Recreate indexes
                console.log('\n🔍 Creating indexes...');
                db.run(`CREATE INDEX idx_cve_id ON cves(cve_id)`, () => {
                    console.log('✓ Index: idx_cve_id');
                });
                db.run(`CREATE INDEX idx_vendor ON cves(vendor)`, () => {
                    console.log('✓ Index: idx_vendor');
                });
                db.run(`CREATE INDEX idx_severity ON cves(severity)`, () => {
                    console.log('✓ Index: idx_severity');
                });
                db.run(`CREATE INDEX idx_status ON cves(status)`, () => {
                    console.log('✓ Index: idx_status');
                });

                // Update view
                console.log('\n👁️  Updating cve_document_status view...');
                db.run(`DROP VIEW IF EXISTS cve_document_status`, (err) => {
                    if (err) console.error('Drop view error:', err);

                    db.run(`
                        CREATE VIEW cve_document_status AS
                        SELECT 
                            c.id as record_id,
                            c.cve_id,
                            c.vendor,
                            c.severity,
                            c.status,
                            COUNT(DISTINCT d.id) as total_documents,
                            COUNT(DISTINCT CASE WHEN d.type = 'advisory' THEN d.id END) as advisory_count,
                            COUNT(DISTINCT CASE WHEN d.type = 'email' THEN d.id END) as email_count,
                            COUNT(DISTINCT CASE WHEN d.type = 'screenshot' THEN d.id END) as screenshot_count,
                            CASE 
                                WHEN COUNT(DISTINCT CASE WHEN d.type = 'advisory' THEN d.id END) > 0 
                                THEN 'Complete'
                                ELSE 'Missing Required Docs'
                            END as compliance_status
                        FROM cves c
                        LEFT JOIN documents d ON c.cve_id = d.cve_id AND c.vendor = d.vendor
                        GROUP BY c.id, c.cve_id, c.vendor, c.severity, c.status
                    `, (err) => {
                        if (err) {
                            console.error('Create view error:', err);
                        } else {
                            console.log('✓ View recreated');
                        }

                        console.log('\n✅ Migration complete!');
                        console.log('\n📊 Summary:');
                        
                        db.get('SELECT COUNT(*) as count FROM cves', (err, row) => {
                            if (!err) console.log(`   Total CVE entries: ${row.count}`);
                            
                            db.get('SELECT COUNT(DISTINCT cve_id) as count FROM cves', (err, row) => {
                                if (!err) console.log(`   Unique CVE IDs: ${row.count}`);
                                
                                console.log('\n💡 Next steps:');
                                console.log('   1. Restart backend: pkill -f "node server.js" && node server.js &');
                                console.log('   2. Replace frontend/src/App.js with multi-vendor version');
                                console.log('   3. Test by adding same CVE with multiple vendors\n');
                                
                                db.close();
                            });
                        });
                    });
                });
            });
        });
    });
});