backend/models/Review.js - master - varsl/HTML-CSS

backend/models/Review.js
196 строк7 KB
varsl
Создание моделей данных для приложения
21 дек 2025, 23:53
21 дек 2025, 23:53a1e5649
const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const { dbPath } = require('../config');

const db = new sqlite3.Database(path.resolve(dbPath));

class Review {
    // Создание отзыва
    static async create(reviewData) {
        const { userId, animalId, rating, comment } = reviewData;

        return new Promise((resolve, reject) => {
            const sql = `INSERT INTO reviews (user_id, animal_id, rating, comment) VALUES (?, ?, ?, ?)`;
            db.run(sql, [userId, animalId, rating, comment || null], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.lastID);
                }
            });
        });
    }

    // Получение всех отзывов для животного
    static async findByAnimalId(animalId, limit = 50, offset = 0) {
        return new Promise((resolve, reject) => {
            const sql = `
        SELECT r.*, u.name as user_name, u.avatar as user_avatar 
        FROM reviews r 
        JOIN users u ON r.user_id = u.id 
        WHERE r.animal_id = ? 
        ORDER BY r.created_at DESC 
        LIMIT ? OFFSET ?
      `;
            db.all(sql, [animalId, limit, offset], (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    // Получение отзыва пользователя для животного
    static async findByUserAndAnimal(userId, animalId) {
        return new Promise((resolve, reject) => {
            const sql = `SELECT * FROM reviews WHERE user_id = ? AND animal_id = ?`;
            db.get(sql, [userId, animalId], (err, row) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(row);
                }
            });
        });
    }

    // Получение отзыва по ID
    static async findById(id) {
        return new Promise((resolve, reject) => {
            const sql = `SELECT * FROM reviews WHERE id = ?`;
            db.get(sql, [id], (err, row) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(row);
                }
            });
        });
    }

    // Обновление отзыва
    static async update(id, userId, updates) {
        const fields = [];
        const values = [];
        const validFields = ['rating', 'comment'];

        for (const [key, value] of Object.entries(updates)) {
            if (validFields.includes(key) && value !== undefined) {
                fields.push(`${key} = ?`);
                values.push(value);
            }
        }

        if (fields.length === 0) {
            return Promise.resolve(false);
        }

        values.push(id, userId);

        return new Promise((resolve, reject) => {
            const sql = `UPDATE reviews SET ${fields.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ? AND user_id = ?`;
            db.run(sql, values, function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.changes > 0);
                }
            });
        });
    }

    // Удаление отзыва
    static async delete(id, userId) {
        return new Promise((resolve, reject) => {
            const sql = `DELETE FROM reviews WHERE id = ? AND user_id = ?`;
            db.run(sql, [id, userId], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.changes > 0);
                }
            });
        });
    }

    // Получение статистики отзывов для животного
    static async getStatsForAnimal(animalId) {
        return new Promise((resolve, reject) => {
            const sql = `
        SELECT 
          COUNT(*) as total_reviews,
          AVG(rating) as average_rating,
          COUNT(CASE WHEN rating = 5 THEN 1 END) as five_star,
          COUNT(CASE WHEN rating = 4 THEN 1 END) as four_star,
          COUNT(CASE WHEN rating = 3 THEN 1 END) as three_star,
          COUNT(CASE WHEN rating = 2 THEN 1 END) as two_star,
          COUNT(CASE WHEN rating = 1 THEN 1 END) as one_star
        FROM reviews 
        WHERE animal_id = ?
      `;
            db.get(sql, [animalId], (err, row) => {
                if (err) {
                    reject(err);
                } else {
                    resolve({
                        totalReviews: row.total_reviews || 0,
                        averageRating: row.average_rating ? parseFloat(row.average_rating.toFixed(1)) : 0,
                        ratingDistribution: {
                            5: row.five_star || 0,
                            4: row.four_star || 0,
                            3: row.three_star || 0,
                            2: row.two_star || 0,
                            1: row.one_star || 0
                        }
                    });
                }
            });
        });
    }

    // Получение всех отзывов пользователя
    static async findByUserId(userId, limit = 100, offset = 0) {
        return new Promise((resolve, reject) => {
            const sql = `
        SELECT r.*, a.name as animal_name, a.image_url as animal_image 
        FROM reviews r 
        JOIN animals a ON r.animal_id = a.animal_id 
        WHERE r.user_id = ? 
        ORDER BY r.created_at DESC 
        LIMIT ? OFFSET ?
      `;
            db.all(sql, [userId, limit, offset], (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    // Получение последних отзывов
    static async getRecent(limit = 10) {
        return new Promise((resolve, reject) => {
            const sql = `
        SELECT r.*, u.name as user_name, u.avatar as user_avatar, a.name as animal_name 
        FROM reviews r 
        JOIN users u ON r.user_id = u.id 
        JOIN animals a ON r.animal_id = a.animal_id 
        ORDER BY r.created_at DESC 
        LIMIT ?
      `;
            db.all(sql, [limit], (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }
}

module.exports = Review;