108 lines
4.0 KiB
JavaScript
108 lines
4.0 KiB
JavaScript
// 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 };
|