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

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

// 初始化表结构
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS tickets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    type TEXT NOT NULL,
    game TEXT NOT NULL,
    issue TEXT,
    numbers TEXT,
    bet_count INTEGER DEFAULT 1,
    multiple INTEGER DEFAULT 1,
    is_additional INTEGER DEFAULT 0,
    cost REAL NOT NULL,
    prize REAL DEFAULT 0,
    status TEXT DEFAULT 'pending',
    image_path TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS lottery_results (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    game TEXT NOT NULL,
    issue TEXT NOT NULL,
    numbers TEXT NOT NULL,
    draw_date DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(game, issue)
  );
`);

module.exports = db;