'use strict';

const fs = require('fs');
const path = require('path');
const { DatabaseSync } = require('node:sqlite');

const dbPath = path.join(__dirname, '..', 'data', 'zup-changes.db');

const FIELDS = [
  { key: 'fio', label: 'ФИО' },
  { key: 'position', label: 'Должность' },
  { key: 'phone', label: 'Телефон' },
  { key: 'email', label: 'Почта' }
];

let db = null;

function getDb() {
  if (db) return db;
  fs.mkdirSync(path.dirname(dbPath), { recursive: true });
  db = new DatabaseSync(dbPath);
  db.exec(`
    PRAGMA journal_mode = WAL;
    CREATE TABLE IF NOT EXISTS user_snapshot (
      login TEXT PRIMARY KEY,
      fio TEXT NOT NULL DEFAULT '',
      position TEXT NOT NULL DEFAULT '',
      phone TEXT NOT NULL DEFAULT '',
      email TEXT NOT NULL DEFAULT '',
      updated_at INTEGER NOT NULL
    );
    CREATE TABLE IF NOT EXISTS data_changes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      ts INTEGER NOT NULL,
      login TEXT NOT NULL,
      field TEXT NOT NULL,
      old_value TEXT NOT NULL DEFAULT '',
      new_value TEXT NOT NULL DEFAULT ''
    );
    CREATE INDEX IF NOT EXISTS idx_changes_login ON data_changes(login);
    CREATE INDEX IF NOT EXISTS idx_changes_ts ON data_changes(ts);
  `);
  return db;
}

function normalize(value) {
  return String(value == null ? '' : value).trim();
}

// Сравнивает текущие данные пользователей LDAP с прошлым снимком.
// При отличиях пишет записи в data_changes и обновляет снимок.
// Возвращает список зафиксированных изменений.
function detectChanges(users) {
  const database = getDb();
  const now = Date.now();
  const changes = [];

  const stmtGet = database.prepare('SELECT fio, position, phone, email FROM user_snapshot WHERE login = ?');
  const stmtInsert = database.prepare(
    'INSERT INTO user_snapshot (login, fio, position, phone, email, updated_at) VALUES (?, ?, ?, ?, ?, ?)'
  );
  const stmtUpdate = database.prepare(
    'UPDATE user_snapshot SET fio = ?, position = ?, phone = ?, email = ?, updated_at = ? WHERE login = ?'
  );
  const stmtLog = database.prepare(
    'INSERT INTO data_changes (ts, login, field, old_value, new_value) VALUES (?, ?, ?, ?, ?)'
  );

  for (const user of users || []) {
    const login = normalize(user.login);
    if (!login) continue;
    const current = {
      fio: normalize(user.fio),
      position: normalize(user.position),
      phone: normalize(user.phone),
      email: normalize(user.email)
    };
    const row = stmtGet.get(login);
    if (!row) {
      stmtInsert.run(login, current.fio, current.position, current.phone, current.email, now);
      continue;
    }
    let touched = false;
    for (const f of FIELDS) {
      const oldValue = normalize(row[f.key]);
      const newValue = current[f.key];
      if (oldValue !== newValue) {
        stmtLog.run(now, login, f.key, oldValue, newValue);
        changes.push({ ts: now, login, field: f.key, label: f.label, old_value: oldValue, new_value: newValue });
        touched = true;
      }
    }
    if (touched) stmtUpdate.run(current.fio, current.position, current.phone, current.email, now, login);
  }

  return changes;
}

// История изменений данных по пользователю (по убыванию даты)
function changesFor(login) {
  return getDb()
    .prepare('SELECT ts, login, field, old_value, new_value FROM data_changes WHERE login = ? ORDER BY ts DESC, id DESC')
    .all(String(login || ''))
    .map((r) => ({
      ts: r.ts,
      login: r.login,
      field: r.field,
      label: (FIELDS.find((f) => f.key === r.field) || {}).label || r.field,
      old_value: r.old_value,
      new_value: r.new_value
    }));
}

module.exports = { detectChanges, changesFor, dbPath };