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

const DB_PATH = path.join(__dirname, '../../data/app.db');

// Ensure directory exists
const dbDir = path.dirname(DB_PATH);
if (!fs.existsSync(dbDir)) {
    fs.mkdirSync(dbDir, { recursive: true });
}

// Initialize database
const db = new Database(DB_PATH);

// Enable WAL mode for better concurrent performance
db.pragma('journal_mode = WAL');

// Create schema
db.exec(`
    -- Visitors table
    CREATE TABLE IF NOT EXISTS visitors (
        user_id TEXT PRIMARY KEY,
        first_seen DATETIME NOT NULL,
        last_seen DATETIME NOT NULL
    );

    -- Sessions table
    CREATE TABLE IF NOT EXISTS sessions (
        session_id TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        game_name TEXT NOT NULL,
        start_time DATETIME NOT NULL,
        end_time DATETIME,
        duration INTEGER DEFAULT 0,
        FOREIGN KEY (user_id) REFERENCES visitors(user_id)
    );

    -- Page views table
    CREATE TABLE IF NOT EXISTS page_views (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id TEXT NOT NULL,
        game_name TEXT NOT NULL,
        timestamp DATETIME NOT NULL,
        date TEXT NOT NULL,
        hour INTEGER NOT NULL,
        FOREIGN KEY (user_id) REFERENCES visitors(user_id)
    );

    -- Indexes for performance
    CREATE INDEX IF NOT EXISTS idx_visitors_last_seen ON visitors(last_seen);
    CREATE INDEX IF NOT EXISTS idx_sessions_game ON sessions(game_name);
    CREATE INDEX IF NOT EXISTS idx_sessions_start ON sessions(start_time);
    CREATE INDEX IF NOT EXISTS idx_page_views_date ON page_views(date);
    CREATE INDEX IF NOT EXISTS idx_page_views_timestamp ON page_views(timestamp);
    CREATE INDEX IF NOT EXISTS idx_page_views_game ON page_views(game_name);

    -- Games configuration table
    CREATE TABLE IF NOT EXISTS games (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        description TEXT NOT NULL,
        icon TEXT NOT NULL,
        players TEXT NOT NULL,
        estimated_time TEXT NOT NULL,
        enabled INTEGER DEFAULT 1,
        featured INTEGER DEFAULT 0,
        category TEXT DEFAULT 'strategy',
        path TEXT NOT NULL,
        ai_powered INTEGER DEFAULT 0,
        is_coming_soon INTEGER DEFAULT 0,
        created_at TEXT,
        updated_at TEXT
    );

    CREATE TABLE IF NOT EXISTS testimonials (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        player_name TEXT NOT NULL,
        player_rank TEXT,
        player_avatar TEXT,
        message TEXT NOT NULL,
        rating INTEGER DEFAULT 5,
        created_at TEXT
    );

    -- Index for enabled games query
    CREATE INDEX IF NOT EXISTS idx_games_enabled ON games(enabled);
    CREATE INDEX IF NOT EXISTS idx_games_featured ON games(featured);

    -- Users table
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        role TEXT DEFAULT 'admin',
        created_at TEXT,
        updated_at TEXT
    );
`);

// Run migrations for device tracking
const { 
    migrateDeviceColumns, 
    migrateGameMaintenanceColumns, 
    migrateSupportsRoomsColumn, 
    migrateComingSoonColumn, 
    migratePlayerModeColumn,
    migrateGameStatsColumns,
    migrateMatchAnalyticsColumns,
    migrateToMetricsColumn,
    dropDifficultyColumn,
    dropResultColumn,
    dropOpponentIdColumn,
    dropPlayerRoleColumn,
    migrateMatchIdToInteger
} = require('./migration');

migrateDeviceColumns(db);
migrateGameMaintenanceColumns(db);
migrateSupportsRoomsColumn(db);
migrateComingSoonColumn(db);
migratePlayerModeColumn(db);
migrateGameStatsColumns(db);
migrateMatchAnalyticsColumns(db);
migrateToMetricsColumn(db);
dropDifficultyColumn(db);
dropResultColumn(db);
dropOpponentIdColumn(db);
dropPlayerRoleColumn(db);
migrateMatchIdToInteger(db);

module.exports = db;
