db.js

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

const db = new Database(path.join(__dirname, 'giftbot.db'));

// Initialize tables
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tg_id INTEGER UNIQUE NOT NULL,
    username TEXT,
    first_name TEXT,
    balance INTEGER DEFAULT 0,
    total_won INTEGER DEFAULT 0,
    total_lost INTEGER DEFAULT 0,
    total_bets INTEGER DEFAULT 0,
    pending_prize_name TEXT,
    pending_prize_value INTEGER,
    pending_gift_id TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tg_id INTEGER NOT NULL,
    type TEXT NOT NULL,
    amount INTEGER NOT NULL,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS sent_gifts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tg_id INTEGER NOT NULL,
    gift_id TEXT NOT NULL,
    gift_name TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );
`);

// Migration for existing table
try { db.exec("ALTER TABLE users ADD COLUMN pending_gift_id TEXT;"); } catch(e){}

function getUser(tg_id) {
  return db.prepare('SELECT * FROM users WHERE tg_id = ?').get(tg_id);
}

function upsertUser(tg_id, username, first_name) {
  db.prepare(`
    INSERT INTO users (tg_id, username, first_name)
    VALUES (?, ?, ?)
    ON CONFLICT(tg_id) DO UPDATE SET
      username = excluded.username,
      first_name = excluded.first_name
  `).run(tg_id, username || '', first_name || '');
  return getUser(tg_id);
}

function addBalance(tg_id, amount, description = '') {
  db.prepare('UPDATE users SET balance = balance + ? WHERE tg_id = ?').run(amount, tg_id);
  db.prepare('INSERT INTO transactions (tg_id, type, amount, description) VALUES (?, ?, ?, ?)')
    .run(tg_id, 'credit', amount, description);
}

function deductBalance(tg_id, amount, description = '') {
  const user = getUser(tg_id);
  if (!user || user.balance < amount) return false;
  db.prepare('UPDATE users SET balance = balance - ? WHERE tg_id = ?').run(amount, tg_id);
  db.prepare('INSERT INTO transactions (tg_id, type, amount, description) VALUES (?, ?, ?, ?)')
    .run(tg_id, 'debit', amount, description);
  return true;
}

function recordBetWin(tg_id, won, bet) {
  if (won > 0) {
    db.prepare('UPDATE users SET total_won = total_won + ?, total_bets = total_bets + 1 WHERE tg_id = ?').run(won, tg_id);
  } else {
    db.prepare('UPDATE users SET total_lost = total_lost + ?, total_bets = total_bets + 1 WHERE tg_id = ?').run(bet, tg_id);
  }
}

function logSentGift(tg_id, gift_id, gift_name) {
  db.prepare('INSERT INTO sent_gifts (tg_id, gift_id, gift_name) VALUES (?, ?, ?)')
    .run(tg_id, gift_id, gift_name);
}

function setPendingPrize(tg_id, name, value, gift_id = null) {
  db.prepare('UPDATE users SET pending_prize_name = ?, pending_prize_value = ?, pending_gift_id = ? WHERE tg_id = ?')
    .run(name, value, gift_id, tg_id);
}

function claimPrize(tg_id) {
  const user = getUser(tg_id);
  if (!user || (!user.pending_prize_value && !user.pending_gift_id)) return null;
  const val = user.pending_prize_value;
  const name = user.pending_prize_name;
  const gift_id = user.pending_gift_id;

  db.prepare('UPDATE users SET balance = balance + ?, pending_prize_name = NULL, pending_prize_value = NULL, pending_gift_id = NULL WHERE tg_id = ?')
    .run(val || 0, tg_id);
  
  if (val > 0) {
    db.prepare('INSERT INTO transactions (tg_id, type, amount, description) VALUES (?, ?, ?, ?)')
      .run(tg_id, 'credit', val, `Claimed ${name}`);
  }

  return { name, val, gift_id };
}

function clearPendingPrize(tg_id) {
  db.prepare('UPDATE users SET pending_prize_name = NULL, pending_prize_value = NULL, pending_gift_id = NULL WHERE tg_id = ?')
    .run(tg_id);
}

function getHistory(tg_id, limit = 20) {
  return db.prepare('SELECT * FROM transactions WHERE tg_id = ? ORDER BY created_at DESC LIMIT ?').all(tg_id, limit);
}

function getLeaderboard(limit = 10) {
  return db.prepare('SELECT tg_id, username, first_name, balance, total_won FROM users ORDER BY balance DESC LIMIT ?').all(limit);
}

function getAllUsers() {
  return db.prepare('SELECT * FROM users').all();
}

function getStats() {
  const totalUsers = db.prepare('SELECT COUNT(*) as count FROM users').get().count;
  const totalTransactions = db.prepare('SELECT COUNT(*) as count FROM transactions').get().count;
  const totalGifts = db.prepare('SELECT COUNT(*) as count FROM gifts').get().count;
  const totalStarsSold = db.prepare("SELECT SUM(amount) as total FROM transactions WHERE type='credit' AND description LIKE '%Stars%'").get().total || 0;
  return { totalUsers, totalTransactions, totalGifts, totalStarsSold };
}

module.exports = {
  getUser, upsertUser, addBalance, deductBalance, recordBetWin,
  logSentGift, getHistory, getLeaderboard, getAllUsers, getStats,
  setPendingPrize, claimPrize, clearPendingPrize
};