Spaces:
Sleeping
Sleeping
File size: 3,900 Bytes
90a13c0 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 | 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
}
}
}
|