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

const dataDir = path.join(__dirname, '../data');
if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir, { recursive: true });

const db = new DatabaseSync(path.join(dataDir, 'vianexu.db'));

db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    name                TEXT    NOT NULL,
    email               TEXT    UNIQUE NOT NULL,
    password            TEXT    NOT NULL,
    ig_user_id          TEXT,
    ig_token            TEXT,
    ig_username         TEXT,
    bot_msg_enabled     INTEGER DEFAULT 0,
    bot_msg_persona     TEXT,
    bot_cmt_enabled     INTEGER DEFAULT 0,
    bot_cmt_persona     TEXT,
    created_at          DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// Migrations — adiciona colunas se não existirem
const existingCols = db.prepare(`PRAGMA table_info(users)`).all().map(c => c.name);
for (const [col, def] of [
  ['bot_msg_enabled',   'INTEGER DEFAULT 0'],
  ['bot_msg_persona',   'TEXT'],
  ['bot_cmt_enabled',   'INTEGER DEFAULT 0'],
  ['bot_cmt_persona',   'TEXT'],
  ['ig_state',          'TEXT'],
  ['google_token',      'TEXT'],
  ['google_refresh',    'TEXT'],
  ['google_account_id', 'TEXT'],
  ['google_location_id','TEXT'],
  ['google_biz_name',   'TEXT'],
  ['google_state',      'TEXT'],
]) {
  if (!existingCols.includes(col)) db.exec(`ALTER TABLE users ADD COLUMN ${col} ${def}`);
}

module.exports = db;
