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

// Datenbank-Pfad: Umgebungsvariable oder Standard-Pfad
const dbPath = process.env.DB_PATH || path.join(__dirname, 'stundenerfassung.db');
const db = new sqlite3.Database(dbPath);

// Datenbank initialisieren
function initDatabase() {
  db.serialize(() => {
    // Benutzer-Tabelle
    db.run(`CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE NOT NULL,
      password TEXT NOT NULL,
      firstname TEXT NOT NULL,
      lastname TEXT NOT NULL,
      role TEXT NOT NULL DEFAULT 'mitarbeiter',
      last_week_start TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`);
    
    // Migration: last_week_start Spalte hinzufügen falls sie nicht existiert
    db.run(`ALTER TABLE users ADD COLUMN last_week_start TEXT`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
    });

    // Stundenerfassung-Tabelle
    db.run(`CREATE TABLE IF NOT EXISTS timesheet_entries (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      date TEXT NOT NULL,
      start_time TEXT,
      end_time TEXT,
      break_minutes INTEGER DEFAULT 0,
      total_hours REAL,
      notes TEXT,
      activity1_desc TEXT,
      activity1_hours REAL,
      activity2_desc TEXT,
      activity2_hours REAL,
      activity3_desc TEXT,
      activity3_hours REAL,
      activity4_desc TEXT,
      activity4_hours REAL,
      activity5_desc TEXT,
      activity5_hours REAL,
      status TEXT DEFAULT 'offen',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id)
    )`);
    
    // Migration: Tätigkeitsfelder hinzufügen falls sie nicht existieren
    const activityColumns = [
      'activity1_desc', 'activity1_hours',
      'activity2_desc', 'activity2_hours',
      'activity3_desc', 'activity3_hours',
      'activity4_desc', 'activity4_hours',
      'activity5_desc', 'activity5_hours'
    ];
    
    activityColumns.forEach(col => {
      const colType = col.includes('_hours') ? 'REAL' : 'TEXT';
      db.run(`ALTER TABLE timesheet_entries ADD COLUMN ${col} ${colType}`, (err) => {
        // Fehler ignorieren wenn Spalte bereits existiert
      });
    });

    // Wöchentliche Stundenzettel-Tabelle
    db.run(`CREATE TABLE IF NOT EXISTS weekly_timesheets (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      week_start TEXT NOT NULL,
      week_end TEXT NOT NULL,
      version INTEGER DEFAULT 1,
      status TEXT DEFAULT 'eingereicht',
      submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      reviewed_by INTEGER,
      reviewed_at DATETIME,
      pdf_downloaded_at DATETIME,
      pdf_downloaded_by INTEGER,
      FOREIGN KEY (user_id) REFERENCES users(id),
      FOREIGN KEY (reviewed_by) REFERENCES users(id),
      FOREIGN KEY (pdf_downloaded_by) REFERENCES users(id)
    )`);
    
    // Migration: version Spalte hinzufügen falls sie nicht existiert
    db.run(`ALTER TABLE weekly_timesheets ADD COLUMN version INTEGER DEFAULT 1`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
      // Wenn Spalte neu erstellt wurde, bestehende Einträge haben automatisch version = 1
    });
    
    // Migration: pdf_downloaded_at Spalte hinzufügen falls sie nicht existiert
    db.run(`ALTER TABLE weekly_timesheets ADD COLUMN pdf_downloaded_at DATETIME`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
    });
    
    // Migration: pdf_downloaded_by Spalte hinzufügen falls sie nicht existiert
    db.run(`ALTER TABLE weekly_timesheets ADD COLUMN pdf_downloaded_by INTEGER`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
    });

    // Migration: version_reason Spalte hinzufügen
    db.run(`ALTER TABLE weekly_timesheets ADD COLUMN version_reason TEXT`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
      if (err && !err.message.includes('duplicate column')) {
        console.warn('Warnung beim Hinzufügen der Spalte version_reason:', err.message);
      }
    });

    // Migration: admin_comment Spalte hinzufügen
    db.run(`ALTER TABLE weekly_timesheets ADD COLUMN admin_comment TEXT`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
      if (err && !err.message.includes('duplicate column')) {
        console.warn('Warnung beim Hinzufügen der Spalte admin_comment:', err.message);
      }
    });

    // Migration: Projektnummern für Tätigkeiten hinzufügen
    const projectNumberColumns = [
      'activity1_project_number', 'activity2_project_number',
      'activity3_project_number', 'activity4_project_number',
      'activity5_project_number'
    ];
    
    projectNumberColumns.forEach(col => {
      db.run(`ALTER TABLE timesheet_entries ADD COLUMN ${col} TEXT`, (err) => {
        // Fehler ignorieren wenn Spalte bereits existiert
        if (err && !err.message.includes('duplicate column')) {
          console.warn(`Warnung beim Hinzufügen der Spalte ${col}:`, err.message);
        }
      });
    });

    // Migration: Überstunden und Urlaub hinzufügen
    db.run(`ALTER TABLE timesheet_entries ADD COLUMN overtime_taken_hours REAL`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
      if (err && !err.message.includes('duplicate column')) {
        console.warn('Warnung beim Hinzufügen der Spalte overtime_taken_hours:', err.message);
      }
    });
    
    db.run(`ALTER TABLE timesheet_entries ADD COLUMN vacation_type TEXT`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
      if (err && !err.message.includes('duplicate column')) {
        console.warn('Warnung beim Hinzufügen der Spalte vacation_type:', err.message);
      }
    });

    // Migration: Krank-Status hinzufügen
    db.run(`ALTER TABLE timesheet_entries ADD COLUMN sick_status INTEGER DEFAULT 0`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
      if (err && !err.message.includes('duplicate column')) {
        console.warn('Warnung beim Hinzufügen der Spalte sick_status:', err.message);
      }
    });

    // Migration: Pausen-Zeiten für API-Zeiterfassung hinzufügen
    db.run(`ALTER TABLE timesheet_entries ADD COLUMN pause_start_time TEXT`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
      if (err && !err.message.includes('duplicate column')) {
        console.warn('Warnung beim Hinzufügen der Spalte pause_start_time:', err.message);
      }
    });
    
    db.run(`ALTER TABLE timesheet_entries ADD COLUMN pause_end_time TEXT`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
      if (err && !err.message.includes('duplicate column')) {
        console.warn('Warnung beim Hinzufügen der Spalte pause_end_time:', err.message);
      }
    });

    // Migration: User-Felder hinzufügen (Personalnummer, Wochenstunden, Urlaubstage)
    db.run(`ALTER TABLE users ADD COLUMN personalnummer TEXT`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
    });
    
    db.run(`ALTER TABLE users ADD COLUMN wochenstunden REAL`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
    });
    
    db.run(`ALTER TABLE users ADD COLUMN urlaubstage REAL`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
    });
    
    // Migration: Überstunden-Offset (manuelle Korrektur durch Verwaltung)
    db.run(`ALTER TABLE users ADD COLUMN overtime_offset_hours REAL DEFAULT 0`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
      if (err && !err.message.includes('duplicate column')) {
        console.warn('Warnung beim Hinzufügen der Spalte overtime_offset_hours:', err.message);
      }
    });

    // Migration: ping_ip Spalte hinzufügen
    db.run(`ALTER TABLE users ADD COLUMN ping_ip TEXT`, (err) => {
      // Fehler ignorieren wenn Spalte bereits existiert
    });

    // Ping-Status-Tabelle für IP-basierte Zeiterfassung
    db.run(`CREATE TABLE IF NOT EXISTS ping_status (
      user_id INTEGER NOT NULL,
      date TEXT NOT NULL,
      last_successful_ping DATETIME,
      failed_ping_count INTEGER DEFAULT 0,
      start_time_set INTEGER DEFAULT 0,
      first_failed_ping_time DATETIME,
      PRIMARY KEY (user_id, date),
      FOREIGN KEY (user_id) REFERENCES users(id)
    )`, (err) => {
      if (err && !err.message.includes('duplicate column')) {
        console.warn('Warnung beim Erstellen der ping_status Tabelle:', err.message);
      }
    });

    // LDAP-Konfiguration-Tabelle
    db.run(`CREATE TABLE IF NOT EXISTS ldap_config (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      enabled INTEGER DEFAULT 0,
      url TEXT,
      bind_dn TEXT,
      bind_password TEXT,
      base_dn TEXT,
      user_search_filter TEXT,
      username_attribute TEXT DEFAULT 'cn',
      firstname_attribute TEXT DEFAULT 'givenName',
      lastname_attribute TEXT DEFAULT 'sn',
      sync_interval INTEGER DEFAULT 0,
      last_sync DATETIME,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`);

    // LDAP-Sync-Log-Tabelle
    db.run(`CREATE TABLE IF NOT EXISTS ldap_sync_log (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      sync_type TEXT NOT NULL,
      status TEXT NOT NULL,
      users_synced INTEGER DEFAULT 0,
      error_message TEXT,
      sync_started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      sync_completed_at DATETIME
    )`);

    // Migration: Bestehende Rollen zu JSON-Arrays konvertieren
    // Prüfe ob Rollen noch als einfache Strings gespeichert sind (nicht als JSON-Array)
    db.all('SELECT id, role FROM users', (err, users) => {
      if (!err && users) {
        users.forEach(user => {
          let roleValue = user.role;
          // Prüfe ob es bereits ein JSON-Array ist
          try {
            const parsed = JSON.parse(roleValue);
            // Wenn erfolgreich geparst und es ist ein Array, nichts tun
            if (Array.isArray(parsed)) {
              return; // Bereits JSON-Array
            }
          } catch (e) {
            // Nicht JSON, konvertiere zu JSON-Array
          }
          
          // Konvertiere zu JSON-Array
          const roleArray = JSON.stringify([roleValue]);
          db.run('UPDATE users SET role = ? WHERE id = ?', [roleArray, user.id], (err) => {
            if (err) {
              console.warn(`Warnung beim Konvertieren der Rolle für User ${user.id}:`, err.message);
            }
          });
        });
      }
    });

    // Standard Admin-Benutzer erstellen
    const adminPassword = bcrypt.hashSync('admin123', 10);
    db.run(`INSERT OR IGNORE INTO users (id, username, password, firstname, lastname, role) 
            VALUES (1, 'admin', ?, 'System', 'Administrator', ?)`, 
            [adminPassword, JSON.stringify(['admin'])]);

    // Standard Verwaltungs-Benutzer erstellen
    const verwaltungPassword = bcrypt.hashSync('verwaltung123', 10);
    db.run(`INSERT OR IGNORE INTO users (id, username, password, firstname, lastname, role) 
            VALUES (2, 'verwaltung', ?, 'Verwaltung', 'User', ?)`, 
            [verwaltungPassword, JSON.stringify(['verwaltung'])]);
  });
}

module.exports = { db, initDatabase };