const db = require('./analytics-db');
const crypto = require('crypto');

/**
 * Users Database Access Layer
 * Handles user management and authentication
 */
class UsersDB {
    constructor() {
        // Migrate database: Add status column if it doesn't exist
        this.migrateDatabase();

        this.statements = {
            getByUsername: db.prepare('SELECT * FROM users WHERE username = ?'),
            getById: db.prepare('SELECT * FROM users WHERE id = ?'),
            insert: db.prepare(`
                INSERT INTO users (username, password_hash, role, status, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?)
            `),
            updatePassword: db.prepare(`
                UPDATE users 
                SET password_hash = ?, updated_at = ?
                WHERE username = ?
            `),
            count: db.prepare('SELECT COUNT(*) as count FROM users'),
            getAll: db.prepare('SELECT id, username, role, status, created_at, updated_at FROM users ORDER BY created_at DESC'),
            getByStatus: db.prepare('SELECT id, username, role, status, created_at, updated_at FROM users WHERE status = ? ORDER BY created_at DESC'),
            delete: db.prepare('DELETE FROM users WHERE id = ?'),
            updateRole: db.prepare('UPDATE users SET role = ?, updated_at = ? WHERE id = ?'),
            updateStatus: db.prepare('UPDATE users SET status = ?, updated_at = ? WHERE id = ?'),
            updateUser: db.prepare(`
                UPDATE users 
                SET username = COALESCE(?, username),
                    role = COALESCE(?, role),
                    password_hash = COALESCE(?, password_hash),
                    updated_at = ?
                WHERE id = ?
            `)
        };
    }

    /**
     * Migrate database: Add status column if it doesn't exist
     */
    migrateDatabase() {
        try {
            // Check if status column exists
            const tableInfo = db.prepare('PRAGMA table_info(users)').all();
            const hasStatusColumn = tableInfo.some(col => col.name === 'status');

            if (!hasStatusColumn) {
                console.log('📦 Migrating users table: Adding status column...');
                
                // Add status column with default value 'active'
                db.exec(`
                    ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';
                `);
                
                // Update all existing users to have 'active' status
                db.exec(`
                    UPDATE users SET status = 'active' WHERE status IS NULL;
                `);
                
                console.log('✅ Migration complete: Users table now has status column');
            }
        } catch (error) {
            console.error('❌ Error during database migration:', error);
            throw error;
        }
    }

    /**
     * Hash password using PBKDF2
     */
    hashPassword(password) {
        const salt = crypto.randomBytes(16).toString('hex');
        const hash = crypto.pbkdf2Sync(password, salt, 1000, 64, 'sha512').toString('hex');
        return `${salt}:${hash}`;
    }

    /**
     * Verify password
     */
    verifyPassword(password, storedHash) {
        const [salt, hash] = storedHash.split(':');
        const verifyHash = crypto.pbkdf2Sync(password, salt, 1000, 64, 'sha512').toString('hex');
        return hash === verifyHash;
    }

    /**
     * Get user by username
     */
    getUser(username) {
        return this.statements.getByUsername.get(username);
    }

    /**
     * Get user by ID
     */
    getById(id) {
        return this.statements.getById.get(id);
    }

    /**
     * Create new user
     */
    createUser(username, password, role = 'admin', status = 'active') {
        const now = new Date().toISOString();
        const passwordHash = this.hashPassword(password);
        
        try {
            return this.statements.insert.run(
                username,
                passwordHash,
                role,
                status,
                now,
                now
            );
        } catch (error) {
            if (error.code === 'SQLITE_CONSTRAINT_UNIQUE') {
                throw new Error('Username already exists');
            }
            throw error;
        }
    }

    /**
     * Update user password (with current password verification)
     */
    updatePassword(userId, currentPassword, newPassword) {
        try {
            // Get user by ID
            const user = this.getById(userId);
            if (!user) {
                return { success: false, error: 'User not found' };
            }

            // Verify current password
            if (!this.verifyPassword(currentPassword, user.password_hash)) {
                return { success: false, error: 'Current password is incorrect' };
            }

            // Update to new password
            const now = new Date().toISOString();
            const passwordHash = this.hashPassword(newPassword);
            this.statements.updatePassword.run(passwordHash, now, user.username);
            
            return { success: true };
        } catch (error) {
            console.error('Error updating password:', error);
            return { success: false, error: 'Failed to update password' };
        }
    }

    /**
     * Update user password by username (without verification - for admin reset)
     */
    updatePasswordByUsername(username, newPassword) {
        const now = new Date().toISOString();
        const passwordHash = this.hashPassword(newPassword);
        return this.statements.updatePassword.run(passwordHash, now, username);
    }

    /**
     * Get all users (excluding passwords)
     * @param {string} statusFilter - Optional status filter ('all', 'active', 'inactive', 'archived')
     */
    getAllUsers(statusFilter = 'all') {
        if (statusFilter && statusFilter !== 'all') {
            return this.statements.getByStatus.all(statusFilter);
        }
        return this.statements.getAll.all();
    }

    /**
     * Get users by status
     */
    getUsersByStatus(status) {
        return this.statements.getByStatus.all(status);
    }

    /**
     * Update user status
     */
    updateUserStatus(id, status) {
        const now = new Date().toISOString();
        return this.statements.updateStatus.run(status, now, id);
    }

    /**
     * Delete user by ID
     */
    deleteUser(id) {
        return this.statements.delete.run(id);
    }

    /**
     * Update user role
     */
    updateUserRole(id, role) {
        const now = new Date().toISOString();
        return this.statements.updateRole.run(role, now, id);
    }

    /**
     * Update user details
     */
    updateUser(id, { username, role, password }) {
        const now = new Date().toISOString();
        let passwordHash = null;
        
        if (password) {
            passwordHash = this.hashPassword(password);
        }

        try {
            return this.statements.updateUser.run(
                username || null,
                role || null,
                passwordHash,
                now,
                id
            );
        } catch (error) {
            if (error.code === 'SQLITE_CONSTRAINT_UNIQUE') {
                throw new Error('Username already exists');
            }
            throw error;
        }
    }

    /**
     * Initialize default superuser if no users exist
     */

    init() {
        // Check if any superuser exists
        const users = this.statements.getAll.all();
        const superuserExists = users.some(u => u.role === 'superuser');

        if (!superuserExists) {
            console.log('⚠️ No superuser found. Ensuring system access...');
            
            // Check if 'superuser' user exists
            const superuserUser = this.getUser('superuser');
            
            if (superuserUser) {
                // Promote existing superuser to superuser
                console.log('🔄 Promoting existing "superuser" user to superuser role.');
                const now = new Date().toISOString();
                this.statements.updateRole.run('superuser', now, superuserUser.id);
            } else {
                // Create new superuser
                console.log('✨ Creating default superuser (username: superuser)');
                const defaultPassword = process.env.SUPERUSER_INITIAL_PASSWORD || 'superuser123';
                this.createUser('superuser', defaultPassword, 'superuser');
            }
        }
    }
}

module.exports = new UsersDB();
