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

const dataDir = path.join(__dirname, 'data');
if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir);

const db = new Database(path.join(dataDir, 'database.sqlite'));

db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

db.exec(`
  -- Таблица администраторов (логин/пароль)
  CREATE TABLE IF NOT EXISTS admins (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    login TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL
  );

  -- Карточки клиентов
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    phone TEXT UNIQUE NOT NULL,
    name TEXT DEFAULT '',
    status TEXT DEFAULT 'Bronze',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  -- Заявки на заселение
  CREATE TABLE IF NOT EXISTS bookings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    external_id INTEGER UNIQUE,
    user_id INTEGER,
    name TEXT,
    phone_raw TEXT,
    adults INTEGER DEFAULT 1,
    children INTEGER DEFAULT 0,
    checkin_date TEXT,
    checkout_date TEXT,
    status TEXT DEFAULT 'Новая',
    comments TEXT DEFAULT '',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
  );

  -- Журнал действий
  CREATE TABLE IF NOT EXISTS audit_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    action TEXT NOT NULL,
    details TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );
`);

function normalizePhone(phone) {
  let digits = phone.replace(/\D/g, '');
  if (/^[78]\d{10}$/.test(digits)) {
    digits = '7' + digits.slice(1);
  }
  return digits;
}

function logAction(action, details = {}) {
  const stmt = db.prepare('INSERT INTO audit_log (action, details) VALUES (?, ?)');
  stmt.run(action, JSON.stringify(details));
}

function ensureSchema() {
  try {
    const info = db.prepare("PRAGMA table_info(users)").all();
    const hasStatus = info.some(col => col.name.toLowerCase() === 'status');
    if (!hasStatus) {
      db.exec("ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'Bronze'");
      db.prepare("UPDATE users SET status = 'Bronze' WHERE status IS NULL OR status = ''").run();
      console.log('[MIGRATE] Added status column to users (Bronze default)');
    }
  } catch (e) {
    console.error('[MIGRATE] Failed to ensure users.status column', e);
  }
}

module.exports = { db, normalizePhone, logAction, ensureSchema };