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

const dbPath = path.join(__dirname, '../data/app.db');
const db = new sqlite3.Database(dbPath, (err) => {
  if (err) {
    console.error('Error connecting to SQLite database:', err.message);
  } else {
    console.log('✅ Connected to SQLite database.');
  }
});

function initDB() {
  db.serialize(async () => {
    // 1. Create Users Table
    db.run(`CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE NOT NULL,
      password TEXT NOT NULL,
      role TEXT DEFAULT 'user',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`);

    // 2. Create History Table
    db.run(`CREATE TABLE IF NOT EXISTS scraping_history (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER,
      keyword TEXT,
      total_tweets INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(user_id) REFERENCES users(id)
    )`);

    // 3. Create default Admin account if not exists
    db.get("SELECT * FROM users WHERE username = 'admin'", async (err, row) => {
      if (err) return console.error(err);
      if (!row) {
        const hashedPw = await bcrypt.hash('admin123', 10);
        db.run("INSERT INTO users (username, password, role) VALUES (?, ?, ?)", ['admin', hashedPw, 'admin'], (err) => {
          if (err) console.error(err);
          else console.log('✅ Default Admin account created (admin / admin123).');
        });
      }
    });
  });
}

initDB();

module.exports = db;
