db.js

const { createClient } = require('@libsql/client');
require('dotenv').config();

const db = createClient({
    url: process.env.TURSO_DATABASE_URL,
    authToken: process.env.TURSO_AUTH_TOKEN,
});

async function initDB() {
    console.log("Initializing Database...");
    
    const queries = [
        // 1. Users Table
        `CREATE TABLE IF NOT EXISTS users (
            user_id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            display_name TEXT,
            email TEXT UNIQUE NOT NULL,
            lv INTEGER DEFAULT 1,
            xp INTEGER DEFAULT 0,
            hp INTEGER DEFAULT 100,
            power INTEGER DEFAULT 0,
            coins INTEGER DEFAULT 0,
            gems INTEGER DEFAULT 0,
            pass_active BOOLEAN DEFAULT 0,
            pass_level INTEGER DEFAULT 0,
            pass_xp INTEGER DEFAULT 0,
            pass_season INTEGER DEFAULT 1,
            pass_purchase_date DATETIME,
            pass_expire_date DATETIME,
            profile TEXT,
            banner TEXT,
            design TEXT,
            verified BOOLEAN DEFAULT 0,
            two_auth BOOLEAN DEFAULT 0,
            vital_verification BOOLEAN DEFAULT 0,
            two_auth_code TEXT,
            marketing_mail BOOLEAN DEFAULT 0,
            login_count INTEGER DEFAULT 0,
            playtime INTEGER DEFAULT 0,
            safety_score INTEGER DEFAULT 100,
            role TEXT DEFAULT 'User',
            account_status TEXT DEFAULT 'Active',
            ban_reason TEXT,
            ban_expire DATETIME,
            last_login_ip TEXT,
            login_failed_count INTEGER DEFAULT 0,
            last_failed_login DATETIME,
            language TEXT DEFAULT 'ar',
            timezone TEXT,
            tutorial_completed BOOLEAN DEFAULT 0,
            daily_reward DATETIME,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )`,
        // Set starting ID for users to 10000
        `INSERT OR IGNORE INTO sqlite_sequence (name, seq) VALUES ('users', 9999)`,

        // 2. Sessions Table
        `CREATE TABLE IF NOT EXISTS sessions (
            session_id TEXT PRIMARY KEY,
            user_id INTEGER NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            expires_at DATETIME NOT NULL,
            ip TEXT,
            user_agent TEXT,
            device_name TEXT,
            country TEXT,
            last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
            is_active BOOLEAN DEFAULT 1,
            FOREIGN KEY(user_id) REFERENCES users(user_id)
        )`,

        // 3. Mailbox Table
        `CREATE TABLE IF NOT EXISTS mailbox (
            mailbox_id TEXT PRIMARY KEY,
            user_id INTEGER,
            type TEXT DEFAULT 'private',
            title TEXT NOT NULL,
            description TEXT,
            is_read BOOLEAN DEFAULT 0,
            views INTEGER DEFAULT 0,
            reward_received BOOLEAN DEFAULT 0,
            sending_time DATETIME DEFAULT CURRENT_TIMESTAMP,
            end_time DATETIME,
            FOREIGN KEY(user_id) REFERENCES users(user_id)
        )`,

        // 4. Device Count Table
        `CREATE TABLE IF NOT EXISTS device_count (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            device_name TEXT,
            ip TEXT,
            country TEXT,
            user_agent TEXT,
            last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(user_id) REFERENCES users(user_id)
        )`,

        // 5. Clans Table
        `CREATE TABLE IF NOT EXISTS clans (
            clan_id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            tag TEXT NOT NULL,
            description TEXT,
            logo TEXT,
            banner TEXT,
            members_count INTEGER DEFAULT 1,
            max_members INTEGER DEFAULT 50,
            level INTEGER DEFAULT 1,
            xp INTEGER DEFAULT 0,
            power INTEGER DEFAULT 0,
            score INTEGER DEFAULT 0,
            verified BOOLEAN DEFAULT 0,
            privacy TEXT DEFAULT 'open',
            state TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
            announcement TEXT,
            join_requirement TEXT,
            status TEXT DEFAULT 'active',
            treasury INTEGER DEFAULT 0
        )`,

        // 6. Clan Members Table
        `CREATE TABLE IF NOT EXISTS clan_members (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            clan_id TEXT NOT NULL,
            user_id INTEGER NOT NULL,
            role TEXT DEFAULT 'Member',
            join_date DATETIME DEFAULT CURRENT_TIMESTAMP,
            contribution INTEGER DEFAULT 0,
            last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(clan_id) REFERENCES clans(clan_id),
            FOREIGN KEY(user_id) REFERENCES users(user_id)
        )`
    ];

    for (let q of queries) {
        try {
            await db.execute(q);
        } catch (err) {
            console.error("DB Init Error:", err.message);
        }
    }
    console.log("Database Initialized Successfully.");
}

module.exports = { db, initDB };