const duckdb = require('duckdb');
const path = require('path');

const dbPath = path.join(__dirname, 'data', 'chat.duckdb');
const db = new duckdb.Database(dbPath);

const con = db.connect();

// Initialize Schema
function initDb() {
    return new Promise((resolve, reject) => {
        try {
            con.exec(`
                CREATE TABLE IF NOT EXISTS users (
                    wallet_address VARCHAR PRIMARY KEY,
                    username VARCHAR UNIQUE,
                    bio VARCHAR DEFAULT '',
                    banner_color VARCHAR DEFAULT '#6366f1',
                    balance INTEGER DEFAULT 100,
                    last_seen TIMESTAMP
                );

                CREATE TABLE IF NOT EXISTS messages (
                    id INTEGER PRIMARY KEY,
                    channel_id VARCHAR,
                    wallet_address VARCHAR,
                    content VARCHAR,
                    timestamp TIMESTAMP,
                    tx_id VARCHAR,
                    FOREIGN KEY (wallet_address) REFERENCES users(wallet_address)
                );

                CREATE TABLE IF NOT EXISTS reactions (
                    message_id INTEGER,
                    wallet_address VARCHAR,
                    emoji VARCHAR,
                    PRIMARY KEY (message_id, wallet_address, emoji),
                    FOREIGN KEY (wallet_address) REFERENCES users(wallet_address)
                );

                CREATE TABLE IF NOT EXISTS posts (
                    id INTEGER PRIMARY KEY,
                    wallet_address VARCHAR,
                    content VARCHAR,
                    timestamp TIMESTAMP,
                    FOREIGN KEY (wallet_address) REFERENCES users(wallet_address)
                );

                CREATE TABLE IF NOT EXISTS comments (
                    id INTEGER PRIMARY KEY,
                    post_id INTEGER,
                    wallet_address VARCHAR,
                    content VARCHAR,
                    timestamp TIMESTAMP,
                    FOREIGN KEY (post_id) REFERENCES posts(id),
                    FOREIGN KEY (wallet_address) REFERENCES users(wallet_address)
                );

                CREATE TABLE IF NOT EXISTS reposts (
                    id INTEGER PRIMARY KEY,
                    post_id INTEGER,
                    wallet_address VARCHAR,
                    timestamp TIMESTAMP,
                    FOREIGN KEY (post_id) REFERENCES posts(id),
                    FOREIGN KEY (wallet_address) REFERENCES users(wallet_address)
                );
                
                CREATE SEQUENCE IF NOT EXISTS seq_msg_id START 1;
                CREATE SEQUENCE IF NOT EXISTS seq_post_id START 1;
                CREATE SEQUENCE IF NOT EXISTS seq_comment_id START 1;
                CREATE SEQUENCE IF NOT EXISTS seq_repost_id START 1;
                
                CREATE UNIQUE INDEX IF NOT EXISTS idx_reposts_unique ON reposts(post_id, wallet_address);
            `, (err) => {
                if (err) {
                    console.error('Schema initialization error:', err);
                    return resolve(); // Resolve anyway so server starts
                }
                console.log('Database schema created/verified');
                resolve();
            });
        } catch (e) {
            console.error('Fatal database initialization error:', e);
            resolve();
        }
    });
}

initDb().then(() => {
    console.log('Database initialized successfully');
}).catch(err => {
    console.error('Failed to initialize database (continuing anyway):', err);
});

module.exports = { db, con };