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

const DB_PATH = path.join(__dirname, '../../database/ashtransfer.db');

// Ensure database directory exists
const dbDir = path.dirname(DB_PATH);
if (!fs.existsSync(dbDir)) {
    fs.mkdirSync(dbDir, { recursive: true });
}

class Database {
    constructor() {
        this.db = null;
    }

    async initialize() {
        return new Promise((resolve, reject) => {
            this.db = new sqlite3.Database(DB_PATH, async (err) => {
                if (err) {
                    console.error('Error opening database:', err);
                    reject(err);
                } else {
                    console.log('✓ Connected to SQLite database');
                    await this.createTables();
                    await this.seedDefaultData();
                    resolve();
                }
            });
        });
    }

    async createTables() {
        const schemaPath = path.join(__dirname, '../../database/schema.sql');
        const schema = fs.readFileSync(schemaPath, 'utf8');
        
        // Split by semicolon and execute each statement
        const statements = schema.split(';').filter(stmt => stmt.trim());
        
        for (const statement of statements) {
            await this.run(statement);
        }
        
        console.log('✓ Database tables created');
    }

    async seedDefaultData() {
        // Hash default admin password
        const hashedPassword = await bcrypt.hash('Global2026@', 10);
        
        // Update admin user with hashed password
        await this.run(
            `UPDATE users SET password = ? WHERE email = 'admin@ashtransfer.com'`,
            [hashedPassword]
        );
        
        console.log('✓ Default data seeded');
        console.log('  Admin credentials:');
        console.log('  Email: admin@ashtransfer.com');
        console.log('  Password: Global2026@');
    }

    run(sql, params = []) {
        return new Promise((resolve, reject) => {
            this.db.run(sql, params, function(err) {
                if (err) {
                    console.error('Error running SQL:', err.message);
                    reject(err);
                } else {
                    resolve({ id: this.lastID, changes: this.changes });
                }
            });
        });
    }

    get(sql, params = []) {
        return new Promise((resolve, reject) => {
            this.db.get(sql, params, (err, row) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(row);
                }
            });
        });
    }

    all(sql, params = []) {
        return new Promise((resolve, reject) => {
            this.db.all(sql, params, (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    close() {
        return new Promise((resolve, reject) => {
            this.db.close((err) => {
                if (err) {
                    reject(err);
                } else {
                    console.log('✓ Database connection closed');
                    resolve();
                }
            });
        });
    }
}

// Singleton instance
const database = new Database();

module.exports = database;
