const fs = require('fs');
const path = require('path');
const Database = require('better-sqlite3');
const bcrypt = require('bcryptjs');

const dataDir = process.env.DATA_DIR || (fs.existsSync('/data') ? '/data' : __dirname);
if (!fs.existsSync(dataDir)) {
  fs.mkdirSync(dataDir, { recursive: true });
}

const dbPath = path.join(dataDir, 'data.db');
const db = new Database(dbPath);

db.pragma('journal_mode = WAL');

// Tabellen anlegen
db.exec(`
  CREATE TABLE IF NOT EXISTS config (
    key TEXT PRIMARY KEY,
    value TEXT
  );

  CREATE TABLE IF NOT EXISTS product_groups (
    id TEXT PRIMARY KEY,
    name TEXT,
    sort_order INTEGER DEFAULT 0
  );

  CREATE TABLE IF NOT EXISTS products (
    id TEXT PRIMARY KEY,
    text1 TEXT NOT NULL,
    text2 TEXT,
    langtext1 TEXT,
    langtext2 TEXT,
    langtext3 TEXT,
    langtext4 TEXT,
    group_id TEXT,
    preis1 REAL DEFAULT 0,
    unit TEXT,
    active INTEGER DEFAULT 1,
    sort_order INTEGER DEFAULT 0,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS sync_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    status TEXT,
    message TEXT,
    items_count INTEGER DEFAULT 0
  );
`);

// Migration für bestehende DBs falls Spalten fehlen
const tableInfo = db.prepare("PRAGMA table_info(products)").all();
const cols = tableInfo.map(c => c.name);
if (!cols.includes('langtext1')) db.exec("ALTER TABLE products ADD COLUMN langtext1 TEXT;");
if (!cols.includes('langtext2')) db.exec("ALTER TABLE products ADD COLUMN langtext2 TEXT;");
if (!cols.includes('langtext3')) db.exec("ALTER TABLE products ADD COLUMN langtext3 TEXT;");
if (!cols.includes('langtext4')) db.exec("ALTER TABLE products ADD COLUMN langtext4 TEXT;");

// Standard Admin Initialisieren
const adminUser = db.prepare('SELECT value FROM config WHERE key = ?').get('admin_user');
if (!adminUser) {
  const hash = bcrypt.hashSync('Kathi2022!', 10);
  db.prepare('INSERT INTO config (key, value) VALUES (?, ?)').run('admin_user', 'admin');
  db.prepare('INSERT INTO config (key, value) VALUES (?, ?)').run('admin_password_hash', hash);
  db.prepare('INSERT INTO config (key, value) VALUES (?, ?)').run('jwt_secret', 'secret_' + Math.random().toString(36).substring(2));
}

// Standard Theme & Texte initialisieren
const initConfig = (k, v) => {
  const existing = db.prepare('SELECT value FROM config WHERE key = ?').get(k);
  if (!existing) {
    db.prepare('INSERT INTO config (key, value) VALUES (?, ?)').run(k, v);
  }
};

initConfig('site_title', 'GETRÄNKEKARTE');
initConfig('site_subtitle', 'Frisch zubereitete Drinks, Spezialitäten & Erfrischungen');
initConfig('site_badge', '✦ EXKLUSIVE AUSWAHL ✦');
initConfig('site_footer', '© Bar & Lounge • Live POS Sync');

initConfig('theme_bg', '#090b10');
initConfig('theme_card_bg', 'rgba(22, 27, 34, 0.75)');
initConfig('theme_primary', '#f59e0b');
initConfig('theme_accent', '#ec4899');
initConfig('theme_text', '#f8fafc');
initConfig('theme_text_muted', '#94a3b8');

module.exports = db;