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

const DATA_DIR = path.join(__dirname, 'data');
const DB_PATH = path.join(DATA_DIR, 'sessions.db');

// make sure data dir exists
if (!fs.existsSync(DATA_DIR)) {
    fs.mkdirSync(DATA_DIR);
}

const db = new Database(DB_PATH);

// setup tables
db.exec(`
    CREATE TABLE IF NOT EXISTS sessions (
        id TEXT PRIMARY KEY,
        display_name TEXT DEFAULT 'Anonymous',
        username TEXT DEFAULT '@anonymous',
        text TEXT DEFAULT 'No text provided',
        avatar_url TEXT,
        image_url TEXT,
        timestamp INTEGER,
        verified INTEGER DEFAULT 0,
        engagement_likes INTEGER,
        engagement_retweets INTEGER,
        engagement_replies INTEGER,
        engagement_views INTEGER,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
    );

    CREATE INDEX IF NOT EXISTS idx_sessions_updated_at ON sessions(updated_at);
`);

// snapshots table
db.exec(`
    CREATE TABLE IF NOT EXISTS snapshots (
        token TEXT PRIMARY KEY,
        session_id TEXT NOT NULL,
        config_json TEXT NOT NULL,
        created_at INTEGER NOT NULL,
        accessed_at INTEGER NOT NULL,
        FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE
    );

    CREATE INDEX IF NOT EXISTS idx_snapshots_accessed_at ON snapshots(accessed_at);
    CREATE INDEX IF NOT EXISTS idx_snapshots_session_id ON snapshots(session_id);
`);

// migration: add verified column if it doesn't exist
try {
    db.exec(`ALTER TABLE sessions ADD COLUMN verified INTEGER DEFAULT 0`);
} catch (err) {
    // column already exists, ignore
}


function generateId() {
    return crypto.randomUUID();
}

function nowEpoch() {
    return Math.floor(Date.now() / 1000);
}

function generateSnapshotToken() {
    const buffer = crypto.randomBytes(16);
    return buffer.toString('base64')
        .replace(/\+/g, '-')
        .replace(/\//g, '_')
        .replace(/=/g, '~');
}

// convert db row to api resposne format
function rowToSession(row) {
    if (!row) return null;

    let engagement = null;
    if (row.engagement_likes !== null &&
        row.engagement_retweets !== null &&
        row.engagement_replies !== null &&
        row.engagement_views !== null) {
        engagement = {
            likes: row.engagement_likes,
            retweets: row.engagement_retweets,
            replies: row.engagement_replies,
            views: row.engagement_views
        };
    }

    return {
        id: row.id,
        displayName: row.display_name,
        username: row.username,
        text: row.text,
        avatarUrl: row.avatar_url,
        imageUrl: row.image_url,
        timestamp: row.timestamp,
        verified: Boolean(row.verified),
        engagement: engagement,
        createdAt: row.created_at,
        updatedAt: row.updated_at
    };
}

function createSession(data = {}) {
    const id = generateId();
    const now = nowEpoch();

    const stmt = db.prepare(`
        INSERT INTO sessions (
            id, display_name, username, text, avatar_url, image_url, timestamp, verified,
            engagement_likes, engagement_retweets, engagement_replies, engagement_views,
            created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
        id,
        data.displayName || 'Anonymous',
        data.username || '@anonymous',
        data.text || 'No text provided',
        data.avatarUrl || null,
        data.imageUrl || null,
        data.timestamp || null,
        data.verified ? 1 : 0,
        data.engagement?.likes ?? null,
        data.engagement?.retweets ?? null,
        data.engagement?.replies ?? null,
        data.engagement?.views ?? null,
        now,
        now
    );

    return getSession(id);
}

function getSession(id) {
    const stmt = db.prepare('SELECT * FROM sessions WHERE id = ?');
    const row = stmt.get(id);
    return rowToSession(row);
}

function updateSession(id, data) {
    const session = getSession(id);
    if (!session) return null;

    const updates = [];
    const values = [];

    if (data.displayName !== undefined) {
        updates.push('display_name = ?');
        values.push(data.displayName || 'Anonymous');
    }
    if (data.username !== undefined) {
        updates.push('username = ?');
        values.push(data.username || '@anonymous');
    }
    if (data.text !== undefined) {
        updates.push('text = ?');
        values.push(data.text || 'No text provided');
    }
    if (data.avatarUrl !== undefined) {
        updates.push('avatar_url = ?');
        values.push(data.avatarUrl);
    }
    if (data.imageUrl !== undefined) {
        updates.push('image_url = ?');
        values.push(data.imageUrl);
    }
    if (data.timestamp !== undefined) {
        updates.push('timestamp = ?');
        values.push(data.timestamp);
    }

    // engagement updates (atomic clear or partial updates)
    if (data.engagement !== undefined) {
        if (data.engagement === null) {
            // explicitly clear all engagement fields
            updates.push('engagement_likes = ?');
            updates.push('engagement_retweets = ?');
            updates.push('engagement_replies = ?');
            updates.push('engagement_views = ?');
            values.push(null, null, null, null);
        } else {
            // partial engagement field updates
            if (data.engagement.likes !== undefined) {
                updates.push('engagement_likes = ?');
                values.push(data.engagement.likes);
            }
            if (data.engagement.retweets !== undefined) {
                updates.push('engagement_retweets = ?');
                values.push(data.engagement.retweets);
            }
            if (data.engagement.replies !== undefined) {
                updates.push('engagement_replies = ?');
                values.push(data.engagement.replies);
            }
            if (data.engagement.views !== undefined) {
                updates.push('engagement_views = ?');
                values.push(data.engagement.views);
            }
        }
    }

    if (updates.length === 0) {
        return session;
    }

    updates.push('updated_at = ?');
    values.push(nowEpoch());
    values.push(id);

    const stmt = db.prepare(`UPDATE sessions SET ${updates.join(', ')} WHERE id = ?`);
    stmt.run(...values);

    return getSession(id);
}

function deleteSession(id) {
    const stmt = db.prepare('DELETE FROM sessions WHERE id = ?');
    const result = stmt.run(id);
    return result.changes > 0;
}

function createSnapshot(sessionId, config, maxRetries = 3) {
    const configJson = JSON.stringify(config);
    const now = nowEpoch();

    for (let attempt = 0; attempt < maxRetries; attempt++) {
        const token = generateSnapshotToken();

        try {
            const stmt = db.prepare(`
                INSERT INTO snapshots (token, session_id, config_json, created_at, accessed_at)
                VALUES (?, ?, ?, ?, ?)
            `);

            stmt.run(token, sessionId, configJson, now, now);

            return {
                token: token,
                sessionId: sessionId,
                configJson: configJson,
                createdAt: now,
                accessedAt: now
            };
        } catch (err) {
            if (err.code === 'SQLITE_CONSTRAINT' && attempt < maxRetries - 1) {
                continue;
            }
            throw err;
        }
    }

    throw new Error('Failed to generate unique snapshot token after retries');
}

function getSnapshot(token) {
    const stmt = db.prepare('SELECT * FROM snapshots WHERE token = ?');
    const row = stmt.get(token);

    if (!row) return null;

    return {
        token: row.token,
        sessionId: row.session_id,
        configJson: row.config_json,
        createdAt: row.created_at,
        accessedAt: row.accessed_at
    };
}

function touchSnapshot(token) {
    const now = nowEpoch();
    const stmt = db.prepare('UPDATE snapshots SET accessed_at = ? WHERE token = ?');
    const result = stmt.run(now, token);
    return result.changes > 0;
}

function deleteSnapshot(token) {
    const stmt = db.prepare('DELETE FROM snapshots WHERE token = ?');
    const result = stmt.run(token);
    return result.changes > 0;
}

function getSnapshotsForSession(sessionId) {
    const stmt = db.prepare('SELECT * FROM snapshots WHERE session_id = ? ORDER BY created_at DESC');
    const rows = stmt.all(sessionId);

    return rows.map(row => ({
        token: row.token,
        sessionId: row.session_id,
        configJson: row.config_json,
        createdAt: row.created_at,
        accessedAt: row.accessed_at
    }));
}

function cleanupExpiredSnapshots() {
    const FORTY_EIGHT_HOURS = 48 * 60 * 60;
    const cutoff = nowEpoch() - FORTY_EIGHT_HOURS;

    const stmt = db.prepare('DELETE FROM snapshots WHERE accessed_at < ?');
    const result = stmt.run(cutoff);

    if (result.changes > 0) {
        console.log(`Cleaned up ${result.changes} expired snapshot(s)`);
    }
}

function countSnapshotsForSession(sessionId) {
    const stmt = db.prepare('SELECT COUNT(*) as count FROM snapshots WHERE session_id = ?');
    const row = stmt.get(sessionId);
    return row.count;
}


function cleanupExpiredSessions() {
    const ONE_DAY = 24 * 60 * 60;
    const cutoff = nowEpoch() - ONE_DAY;

    const stmt = db.prepare('DELETE FROM sessions WHERE updated_at < ?');
    const result = stmt.run(cutoff);

    if (result.changes > 0) {
        console.log(`Cleaned up ${result.changes} expired session(s)`);
    }
}

module.exports = {
    createSession,
    getSession,
    updateSession,
    deleteSession,
    cleanupExpiredSessions,
    createSnapshot,
    getSnapshot,
    touchSnapshot,
    deleteSnapshot,
    getSnapshotsForSession,
    cleanupExpiredSnapshots,
    countSnapshotsForSession
};