// scripts/init-db.js
const { Pool } = require('pg');
const bcrypt = require('bcrypt');
require('dotenv').config();

const pool = new Pool({
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    ssl: process.env.DB_SSL === 'true' ? { rejectUnauthorized: false } : false
});

async function initDatabase() {
    try {
        console.log('🚀 Initialisiere Datenbank...');
        
        // Verbindung testen
        await pool.query('SELECT NOW()');
        console.log('✅ Datenbankverbindung erfolgreich');
        
        // Adminusers Tabelle erstellen
        await pool.query(`
            CREATE TABLE IF NOT EXISTS adminusers (
                id SERIAL PRIMARY KEY,
                username VARCHAR(50) UNIQUE NOT NULL,
                password_hash VARCHAR(255) NOT NULL,
                is_active BOOLEAN DEFAULT true,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                last_login TIMESTAMP
            )
        `);
        console.log('✅ adminusers Tabelle erstellt/überprüft');
        
        // API Tokens Tabelle erstellen
        await pool.query(`
            CREATE TABLE IF NOT EXISTS api_tokens (
                id SERIAL PRIMARY KEY,
                token VARCHAR(255) UNIQUE NOT NULL,
                description TEXT,
                standorte TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                expires_at TIMESTAMP,
                is_active BOOLEAN DEFAULT true
            )
        `);
        console.log('✅ api_tokens Tabelle erstellt/überprüft');
        
        // Locations Tabelle erstellen
        await pool.query(`
            CREATE TABLE IF NOT EXISTS locations (
                id SERIAL PRIMARY KEY,
                name VARCHAR(255) UNIQUE NOT NULL,
                latitude DECIMAL(10, 8) NOT NULL,
                longitude DECIMAL(11, 8) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        console.log('✅ locations Tabelle erstellt/überprüft');
        
        // Standardbenutzer erstellen (falls nicht vorhanden)
        const existingUser = await pool.query(
            'SELECT id FROM adminusers WHERE username = $1',
            ['admin']
        );
        
        if (existingUser.rows.length === 0) {
            const passwordHash = await bcrypt.hash('admin123', 10);
            await pool.query(
                'INSERT INTO adminusers (username, password_hash) VALUES ($1, $2)',
                ['admin', passwordHash]
            );
            console.log('✅ Standardbenutzer "admin" mit Passwort "admin123" erstellt');
        } else {
            console.log('ℹ️ Standardbenutzer "admin" existiert bereits');
        }
        
        // Index für bessere Performance
        await pool.query(`
            CREATE INDEX IF NOT EXISTS idx_adminusers_username ON adminusers(username);
            CREATE INDEX IF NOT EXISTS idx_adminusers_active ON adminusers(is_active);
            CREATE INDEX IF NOT EXISTS idx_api_tokens_token ON api_tokens(token);
            CREATE INDEX IF NOT EXISTS idx_api_tokens_active ON api_tokens(is_active);
            CREATE INDEX IF NOT EXISTS idx_locations_name ON locations(name);
            CREATE INDEX IF NOT EXISTS idx_locations_coords ON locations(latitude, longitude);
        `);
        console.log('✅ Indizes erstellt/überprüft');
        
        console.log('🎉 Datenbank erfolgreich initialisiert!');
        console.log('📝 Standardanmeldung: admin / admin123');
        console.log('⚠️ Ändern Sie das Standardpasswort in der Produktion!');
        
    } catch (error) {
        console.error('❌ Fehler bei der Datenbankinitialisierung:', error);
        process.exit(1);
    } finally {
        await pool.end();
    }
}

// Skript ausführen wenn direkt aufgerufen
if (require.main === module) {
    initDatabase();
}

module.exports = { initDatabase };