stl-storage/database.js

const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs').promises;

class STLDatabase {
    constructor(dbPath = './stl_storage.db', uploadDir = './uploads') {
        // Use data directory if available (for containers)
        if (process.env.NODE_ENV === 'production' && require('fs').existsSync('./data')) {
            this.dbPath = './data/stl_storage.db';
        } else {
            this.dbPath = dbPath;
        }
        this.uploadDir = uploadDir;
        this.db = null;
    }

    async initialize() {
        await this.ensureDirectories();
        await this.connectDatabase();
        await this.initializeSchema();
    }

    async ensureDirectories() {
        try {
            await fs.access(this.uploadDir);
        } catch {
            await fs.mkdir(this.uploadDir, { recursive: true });
        }
    }

    async connectDatabase() {
        return new Promise((resolve, reject) => {
            this.db = new sqlite3.Database(this.dbPath, (err) => {
                if (err) {
                    reject(err);
                } else {
                    resolve();
                }
            });
        });
    }

    async initializeSchema() {
        const schema = await fs.readFile('./schema.sql', 'utf8');
        return new Promise((resolve, reject) => {
            this.db.exec(schema, (err) => {
                if (err) {
                    reject(err);
                } else {
                    resolve();
                }
            });
        });
    }

    async addSTLFile(fileData) {
        const {
            filename,
            originalName,
            filePath,
            fileSize,
            description = null,
            tags = null,
            printSettings = null,
            dimensions = null
        } = fileData;

        return new Promise((resolve, reject) => {
            const stmt = this.db.prepare(`
                INSERT INTO stl_files 
                (filename, original_name, file_path, file_size, description, tags, print_settings, dimensions)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            `);

            stmt.run([
                filename,
                originalName,
                filePath,
                fileSize,
                description,
                tags,
                printSettings ? JSON.stringify(printSettings) : null,
                dimensions ? JSON.stringify(dimensions) : null
            ], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.lastID);
                }
            });

            stmt.finalize();
        });
    }

    async getSTLFile(id) {
        return new Promise((resolve, reject) => {
            this.db.get(
                'SELECT * FROM stl_files WHERE id = ?',
                [id],
                (err, row) => {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(row ? this.parseRow(row) : null);
                    }
                }
            );
        });
    }

    async getAllSTLFiles(limit = 50, offset = 0) {
        return new Promise((resolve, reject) => {
            this.db.all(
                'SELECT * FROM stl_files ORDER BY upload_date DESC LIMIT ? OFFSET ?',
                [limit, offset],
                (err, rows) => {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(rows.map(row => this.parseRow(row)));
                    }
                }
            );
        });
    }

    async searchSTLFiles(query) {
        return new Promise((resolve, reject) => {
            this.db.all(
                `SELECT * FROM stl_files 
                 WHERE filename LIKE ? OR original_name LIKE ? OR description LIKE ? OR tags LIKE ?
                 ORDER BY upload_date DESC`,
                [`%${query}%`, `%${query}%`, `%${query}%`, `%${query}%`],
                (err, rows) => {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(rows.map(row => this.parseRow(row)));
                    }
                }
            );
        });
    }

    async updateSTLFile(id, updates) {
        const allowedFields = ['description', 'tags', 'print_settings', 'dimensions'];
        const updateFields = [];
        const values = [];

        for (const [key, value] of Object.entries(updates)) {
            if (allowedFields.includes(key)) {
                updateFields.push(`${key} = ?`);
                if (key === 'print_settings' || key === 'dimensions') {
                    values.push(value ? JSON.stringify(value) : null);
                } else {
                    values.push(value);
                }
            }
        }

        if (updateFields.length === 0) {
            throw new Error('No valid fields to update');
        }

        values.push(id);

        return new Promise((resolve, reject) => {
            this.db.run(
                `UPDATE stl_files SET ${updateFields.join(', ')} WHERE id = ?`,
                values,
                function(err) {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(this.changes > 0);
                    }
                }
            );
        });
    }

    async deleteSTLFile(id) {
        const file = await this.getSTLFile(id);
        if (!file) {
            return false;
        }

        try {
            await fs.unlink(file.file_path);
        } catch (err) {
            console.warn(`Could not delete file ${file.file_path}:`, err.message);
        }

        return new Promise((resolve, reject) => {
            this.db.run(
                'DELETE FROM stl_files WHERE id = ?',
                [id],
                function(err) {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(this.changes > 0);
                    }
                }
            );
        });
    }

    parseRow(row) {
        const parsed = { ...row };
        
        if (parsed.print_settings) {
            try {
                parsed.print_settings = JSON.parse(parsed.print_settings);
            } catch {
                parsed.print_settings = null;
            }
        }
        
        if (parsed.dimensions) {
            try {
                parsed.dimensions = JSON.parse(parsed.dimensions);
            } catch {
                parsed.dimensions = null;
            }
        }
        
        return parsed;
    }

    async close() {
        return new Promise((resolve) => {
            this.db.close((err) => {
                if (err) {
                    console.error('Error closing database:', err);
                }
                resolve();
            });
        });
    }
}

module.exports = STLDatabase;