src/models/AdventureModel.ts

import sql from '../db';
import { Adventure } from '../types/types';

/**
 * AdventureModel - Handles database operations for user-saved adventures
 * These are user-recorded activities that can be created, read, and deleted
 */
export class AdventureModel {
    /**
     * Find all adventures, optionally filtered by user ID
     */
    static async findAll(userId?: string): Promise<Adventure[]> {
        if (userId) {
            const adventures = await sql<Adventure[]>`
                SELECT 
                    id, 
                    user_id, 
                    name, 
                    description, 
                    path, 
                    properties, 
                    recorded_at,
                    created_at
                FROM adventures
                WHERE user_id = ${userId}
                ORDER BY created_at DESC
            `;
            return adventures;
        } else {
            const adventures = await sql<Adventure[]>`
                SELECT 
                    id, 
                    user_id, 
                    name, 
                    description, 
                    path, 
                    properties, 
                    recorded_at,
                    created_at
                FROM adventures
                ORDER BY created_at DESC
            `;
            return adventures;
        }
    }

    /**
     * Find a single adventure by ID
     */
    static async findById(id: number): Promise<Adventure | null> {
        const adventures = await sql<Adventure[]>`
            SELECT 
                id, 
                user_id, 
                name, 
                description, 
                path, 
                properties, 
                recorded_at,
                created_at
            FROM adventures
            WHERE id = ${id}
        `;
        return adventures.length > 0 ? adventures[0] : null;
    }

    /**
     * Create a new adventure
     */
    static async create(adventure: Partial<Adventure>): Promise<number> {
        const {
            user_id,
            name,
            description,
            path,
            properties,
            recorded_at
        } = adventure;

        const result = await sql<{ id: number }[]>`
            INSERT INTO adventures (
                user_id, 
                name, 
                description, 
                path, 
                properties, 
                recorded_at
            ) 
            VALUES (
                ${user_id || null}, 
                ${name || 'Untitled Adventure'}, 
                ${description || null}, 
                ${JSON.stringify(path)}, 
                ${JSON.stringify(properties || {})}, 
                ${recorded_at || new Date()}
            ) 
            RETURNING id;
        `;
        return result[0].id;
    }

    /**
     * Delete an adventure by ID
     */
    static async delete(id: number): Promise<boolean> {
        const result = await sql`
            DELETE FROM adventures
            WHERE id = ${id}
        `;
        return result.count > 0;
    }

    /**
     * Ensure the adventures table exists with the correct schema
     * This is called during initialization
     */
    static async ensureTable(): Promise<void> {
        await sql`
            CREATE TABLE IF NOT EXISTS adventures (
                id SERIAL PRIMARY KEY,
                user_id VARCHAR(255),
                name VARCHAR(255),
                description TEXT,
                path JSONB,
                properties JSONB,
                recorded_at TIMESTAMP,
                created_at TIMESTAMP DEFAULT NOW()
            );
        `;

        // Ensure index exists
        try {
            await sql`CREATE INDEX IF NOT EXISTS idx_adventures_user_id ON adventures(user_id);`;
        } catch (e) {
            // Ignore errors if index already exists
        }
    }
}