// Retry mechanism for locked database
async function retryDatabaseOperation(operation, maxRetries = 3) {
    for (let attempt = 1; attempt <= maxRetries; attempt++) {
        try {
            return await operation();
        } catch (error) {
            if (error.code === 'SQLITE_BUSY' && attempt < maxRetries) {
                // Wait with exponential backoff
                await new Promise(resolve => setTimeout(resolve, 100 * Math.pow(2, attempt - 1)));
                continue;
            }
            throw error;
        }
    }
}

// Usage
const result = await retryDatabaseOperation(async () => {
    return db.all('SELECT * FROM users WHERE status = ?', ['active']);
});

// Proper connection management
class DatabaseManager {
    constructor(dbPath) {
        this.db = new MatsushibaDB(dbPath);
        this.db.run('PRAGMA journal_mode = WAL'); // Enable WAL mode
        this.db.run('PRAGMA synchronous = NORMAL'); // Reduce locking
    }
    
    async close() {
        if (this.db) {
            await this.db.close();
            this.db = null;
        }
    }
}

// Handle constraint violations gracefully
function insertUserWithConflictResolution(userData) {
    try {
        // Try regular insert first
        const result = db.run(`
            INSERT INTO users (username, email, password_hash)
            VALUES (?, ?, ?)
        `, [userData.username, userData.email, userData.passwordHash]);
        
        return { success: true, userId: result.lastInsertRowid };
    } catch (error) {
        if (error.message.includes('UNIQUE constraint failed')) {
            if (error.message.includes('username')) {
                return { success: false, error: 'Username already exists' };
            } else if (error.message.includes('email')) {
                return { success: false, error: 'Email already exists' };
            }
        }
        
        if (error.message.includes('NOT NULL constraint failed')) {
            return { success: false, error: 'Required fields are missing' };
        }
        
        throw error;
    }
}

// Use INSERT OR REPLACE for upsert operations
function upsertUser(userData) {
    const result = db.run(`
        INSERT OR REPLACE INTO users (id, username, email, password_hash, updated_at)
        VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)
    `, [userData.id, userData.username, userData.email, userData.passwordHash]);
    
    return { success: true, userId: result.lastInsertRowid };
}

// Validate foreign keys before insert
function validateForeignKeys(tableName, data) {
    const foreignKeyChecks = {
        'posts': {
            'author_id': 'users.id'
        },
        'comments': {
            'post_id': 'posts.id',
            'user_id': 'users.id'
        }
    };
    
    const checks = foreignKeyChecks[tableName];
    if (!checks) return { valid: true };
    
    for (const [column, reference] of Object.entries(checks)) {
        if (data[column]) {
            const [refTable, refColumn] = reference.split('.');
            const exists = db.get(`SELECT 1 FROM ${refTable} WHERE ${refColumn} = ?`, [data[column]]);
            
            if (!exists) {
                return { 
                    valid: false, 
                    error: `Invalid ${column}: ${data[column]} does not exist in ${refTable}` 
                };
            }
        }
    }
    
    return { valid: true };
}

// Query performance analyzer
class QueryPerformanceAnalyzer {
    constructor(db) {
        this.db = db;
    }
    
    analyzeQuery(sql, params = []) {
        const startTime = Date.now();
        
        try {
            // Get query plan
            const plan = this.db.all(`EXPLAIN QUERY PLAN ${sql}`, params);
            
            // Execute query
            const result = this.db.all(sql, params);
            const executionTime = Date.now() - startTime;
            
            // Analyze plan
            const hasTableScan = plan.some(step => 
                step.detail.includes('SCAN TABLE') && 
                !step.detail.includes('USING INDEX')
            );
            
            const usesIndex = plan.some(step => 
                step.detail.includes('USING INDEX')
            );
            
            return {
                sql: sql,
                executionTime: executionTime,
                resultCount: result.length,
                hasTableScan: hasTableScan,
                usesIndex: usesIndex,
                plan: plan,
                recommendations: this.generateRecommendations(hasTableScan, usesIndex, executionTime)
            };
        } catch (error) {
            throw error;
        }
    }
    
    generateRecommendations(hasTableScan, usesIndex, executionTime) {
        const recommendations = [];
        
        if (hasTableScan) {
            recommendations.push('Add an index to avoid full table scan');
        }
        
        if (executionTime > 1000) {
            recommendations.push('Query is slow - consider optimization');
        }
        
        if (!usesIndex && executionTime > 100) {
            recommendations.push('Consider adding an index for better performance');
        }
        
        return recommendations;
    }
}

// Usage
const analyzer = new QueryPerformanceAnalyzer(db);
const analysis = analyzer.analyzeQuery('SELECT * FROM users WHERE status = ?', ['active']);
console.log('Query Analysis:', analysis);

// Memory-efficient data processing
class MemoryEfficientProcessor {
    constructor(db) {
        this.db = db;
        this.batchSize = 1000;
    }
    
    async processLargeDataset(sql, params, processor) {
        let offset = 0;
        let totalProcessed = 0;
        
        while (true) {
            const batch = this.db.all(`${sql} LIMIT ? OFFSET ?`, [...params, this.batchSize, offset]);
            
            if (batch.length === 0) break;
            
            await processor(batch);
            totalProcessed += batch.length;
            offset += this.batchSize;
            
            // Force garbage collection if available
            if (global.gc) {
                global.gc();
            }
        }
        
        return { totalProcessed };
    }
    
    // Stream processing for very large datasets
    streamProcess(sql, params, processor) {
        const stmt = this.db.prepare(sql);
        
        return new Promise((resolve, reject) => {
            stmt.each(
                params,
                (row) => {
                    try {
                        processor(row);
                    } catch (error) {
                        reject(error);
                    }
                },
                (err, count) => {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(count);
                    }
                    stmt.finalize();
                }
            );
        });
    }
}

// Usage
const processor = new MemoryEfficientProcessor(db);

await processor.processLargeDataset(
    'SELECT * FROM large_table WHERE status = ?',
    ['active'],
    async (batch) => {
        console.log(`Processing ${batch.length} records`);
        // Process batch
    }
);

// Optimal connection pool configuration
const poolConfig = {
    database: 'app.db',
    min: 5,                    // Minimum connections
    max: 20,                   // Maximum connections
    acquireTimeoutMillis: 30000,
    createTimeoutMillis: 30000,
    destroyTimeoutMillis: 5000,
    idleTimeoutMillis: 30000,
    reapIntervalMillis: 1000,
    createRetryIntervalMillis: 200,
    propagateCreateError: false
};

// Enable debug logging
const MatsushibaDB = require('matsushibadb');

// Set debug mode
process.env.MATSHIBA_DEBUG = 'true';

const db = new MatsushibaDB('app.db', {
    debug: true,
    logLevel: 'debug'
});

// Custom logging
db.on('query', (sql, params) => {
    console.log('Query:', sql, params);
});

db.on('error', (error) => {
    console.error('Database Error:', error);
});

// Database health check
async function healthCheck() {
    try {
        const startTime = Date.now();
        
        // Test basic connectivity
        await db.get('SELECT 1');
        
        // Check database integrity
        const integrityResult = db.get('PRAGMA integrity_check');
        
        // Check database size
        const sizeResult = db.get(`
            SELECT page_count * page_size as size
            FROM pragma_page_count(), pragma_page_size()
        `);
        
        const responseTime = Date.now() - startTime;
        
        return {
            status: 'healthy',
            responseTime: responseTime,
            integrity: integrityResult,
            size: sizeResult.size,
            timestamp: new Date().toISOString()
        };
    } catch (error) {
        return {
            status: 'unhealthy',
            error: error.message,
            timestamp: new Date().toISOString()
        };
    }
}