src/DatabaseManager.js - master - digitaldepartmentsmipt/store_api

src/DatabaseManager.js
682 строки23 KB
digitaldepartmentsmipt
Create: AA.png, AB.png, AC.png, AD.png, AE.png, AF.png, AG.png, AH.png, AI.png, AJ.png, AK.png, AL.png, AM.png, DatabaseManager.js, router.js, .gitignore, app.js, config.js, package.json, package-lock.json, README.md, swagger.yaml
04 авг 2026, 08:39
Верифицирован
04 авг 2026, 08:3917f8036
import sqlite3 from "sqlite3";
import { DATABASE_NAME } from "../config.js";

const STORE_CATEGORIES = [
    {
        name: "Electronics",
        slug: "electronics",
        description: "Smart devices and everyday tech for work and leisure.",
    },
    {
        name: "Home",
        slug: "home",
        description: "Practical products for comfort, cooking, and daily routines.",
    },
    {
        name: "Books",
        slug: "books",
        description: "Non-fiction and learning materials for personal growth.",
    },
    {
        name: "Sports",
        slug: "sports",
        description: "Equipment and apparel for training and active lifestyles.",
    },
    {
        name: "Accessories",
        slug: "accessories",
        description: "Useful add-ons for commuting, travel, and work setups.",
    },
];

const STORE_PRODUCTS = [
    {
        name: "Altair 14 Laptop",
        slug: "altair-14-laptop",
        brand: "Altair",
        categorySlug: "electronics",
        price: 99990,
        stock: 12,
        imageUrl: "/images/goods/AA.png",
        shortDescription: "Ультратонкий ноутбук с мощными характеристиками и длительным временем автономной работы.",
        details: "14-дюймовый дисплей, процессор Intel Core i7, 16 ГБ оперативной памяти, 512 ГБ SSD и до 12 часов работы от батареи.",
    },
    {
        name: "Nova Wireless Earbuds",
        slug: "nova-wireless-earbuds",
        brand: "Nova",
        categorySlug: "electronics",
        price: 12990,
        stock: 34,
        imageUrl: "/images/goods/AB.png",
        shortDescription: "Компактные беспроводные наушники с активным шумоподавлением и длительным временем работы от батареи.",
        details: "До 8 часов воспроизведения на одном заряде, поддержка быстрой зарядки, сенсорное управление и водонепроницаемость IPX5 для использования в любых условиях.",
    },
    {
        name: "Pulse Smartwatch S5",
        slug: "pulse-smartwatch-s5",
        brand: "Pulse",
        categorySlug: "electronics",
        price: 18990,
        stock: 18,
        imageUrl: "/images/goods/AC.png",
        shortDescription: "Фитнес-трекер с круглым дисплеем и множеством спортивных режимов.",
        details: "1.3-дюймовый AMOLED-дисплей, мониторинг сердечного ритма, GPS и водонепроницаемость до 50 метров.",
    },
    {
        name: "Orbit Mechanical Keyboard",
        slug: "orbit-mechanical-keyboard",
        brand: "Orbit",
        categorySlug: "accessories",
        price: 7990,
        stock: 27,
        imageUrl: "/images/goods/AD.png",
        shortDescription: "Настольная механическая клавиатура с RGB-подсветкой и поддержкой горячей замены переключателей.",
        details: "Горячая замена переключателей, 16.8 млн цветов RGB-подсветки, поддержка макросов и совместимость с Windows и MacOS.",
    },
    {
        name: "Atlas City Backpack",
        slug: "atlas-city-backpack",
        brand: "Atlas",
        categorySlug: "accessories",
        price: 6490,
        stock: 25,
        imageUrl: "/images/goods/AE.png",
        shortDescription: "Водонепроницаемый городской рюкзак для ноутбука и повседневных вещей.",
        details: "20 литров объема, отделение для ноутбука до 15.6 дюймов, USB-порт для зарядки и скрытые карманы для безопасности.",
    },
    {
        name: "Lumi Desk Lamp",
        slug: "lumi-desk-lamp",
        brand: "Lumi",
        categorySlug: "home",
        price: 4590,
        stock: 31,
        imageUrl: "/images/goods/AF.png",
        shortDescription: "Минималистичная настольная лампа с регулируемой яркостью и цветовой температурой.",
        details: "Три режима освещения, сенсорное управление, USB-порт для зарядки устройств и современный дизайн для любого рабочего пространства.",
    },
    {
        name: "BrewSense Coffee Maker",
        slug: "brewsense-coffee-maker",
        brand: "BrewSense",
        categorySlug: "home",
        price: 15490,
        stock: 9,
        imageUrl: "/images/goods/AG.png",
        shortDescription: "Автоматическая кофеварка для приготовления эспрессо и капучино.",
        details: "19-барный насос, встроенная кофемолка, сенсорная панель управления и режимы для разных видов кофе.",
    },
    {
        name: "Cloud Cotton Bedding Set",
        slug: "cloud-cotton-bedding-set",
        brand: "Cloud",
        categorySlug: "home",
        price: 6990,
        stock: 14,
        imageUrl: "/images/goods/AH.png",
        shortDescription: "Мягкие и дышащие простыни из 100% хлопка для комфортного сна.",
        details: "Двуспальный комплект из простыни, наволочки и пододеяльника с классической отделкой и прочными швами.",
    },
    {
        name: "Trail Running Shoes",
        slug: "trail-running-shoes",
        brand: "TrailFlex",
        categorySlug: "sports",
        price: 11490,
        stock: 20,
        imageUrl: "/images/goods/AI.png",
        shortDescription: "Легкие кроссовки для бега по пересеченной местности с отличным сцеплением.",
        details: "Водонепроницаемая мембрана, амортизационная подошва и дышащий верх для комфорта на длинных дистанциях.",
    },
    {
        name: "Core Yoga Mat",
        slug: "core-yoga-mat",
        brand: "Core",
        categorySlug: "sports",
        price: 2990,
        stock: 40,
        imageUrl: "/images/goods/AJ.png",
        shortDescription: "Мат для йоги с нескользящей поверхностью и амортизацией.",
        details: "6 мм толщина, экологичный материал, текстурированная поверхность для лучшего сцепления и удобная сумка для переноски.",
    },
    {
        name: "Deep Work Handbook",
        slug: "deep-work-handbook",
        brand: "North Press",
        categorySlug: "books",
        price: 1490,
        stock: 55,
        imageUrl: "/images/goods/AK.png",
        shortDescription: "Руководство по развитию навыков глубокого фокусирования и продуктивности.",
        details: "Включает в себя стратегии для борьбы с отвлекающими факторами и создания условий для эффективной работы в современном мире.",
    },
    {
        name: "Product Strategy Notes",
        slug: "product-strategy-notes",
        brand: "Signal Books",
        categorySlug: "books",
        price: 1790,
        stock: 23,
        imageUrl: "/images/goods/AL.png",
        shortDescription: "Практическое руководство по разработке и реализации продуктовой стратегии.",
        details: "Включает в себя фреймворки, шаблоны и реальные примеры для создания успешных продуктов на рынке.",
    },
    {
        name: "Homelander's Phone",
        slug: "homelander-phone",
        brand: "Vought",
        categorySlug: "electronics",
        price: 999999,
        stock: 1,
        imageUrl: "/images/goods/AM.png",
        shortDescription: "По нему слушал Патриот.",
        details: "Тот самый телефон, который передала Мудрец Патриоту, чтобы он в итоге послушал.",
    },
];

function openDatabase() {
    return new sqlite3.Database(DATABASE_NAME);
}

function run(database, sql, params = []) {
    return new Promise((resolve, reject) => {
        database.run(sql, params, function onRun(err) {
            if (err) {
                reject(err);
                return;
            }

            resolve({
                lastID: this.lastID,
                changes: this.changes,
            });
        });
    });
}

function get(database, sql, params = []) {
    return new Promise((resolve, reject) => {
        database.get(sql, params, (err, row) => {
            if (err) {
                reject(err);
                return;
            }

            resolve(row ?? null);
        });
    });
}

function all(database, sql, params = []) {
    return new Promise((resolve, reject) => {
        database.all(sql, params, (err, rows) => {
            if (err) {
                reject(err);
                return;
            }

            resolve(rows);
        });
    });
}

function closeDatabase(database) {
    return new Promise((resolve, reject) => {
        database.close((err) => {
            if (err) {
                reject(err);
                return;
            }

            resolve();
        });
    });
}

async function withDatabase(callback) {
    const database = openDatabase();

    try {
        await run(database, "PRAGMA foreign_keys = ON");
        return await callback(database);
    } finally {
        await closeDatabase(database);
    }
}

async function createStoreTables(database) {
    await run(
        database,
        `CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE,
            role TEXT NOT NULL DEFAULT 'user'
        )`,
    );

    await run(
        database,
        `CREATE TABLE IF NOT EXISTS refresh_tokens (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            token TEXT UNIQUE NOT NULL,
            expires_at DATETIME NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )`,
    );

    await run(
        database,
        `CREATE TABLE IF NOT EXISTS categories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            slug TEXT NOT NULL UNIQUE,
            description TEXT NOT NULL
        )`,
    );

    await run(
        database,
        `CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            category_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            slug TEXT NOT NULL UNIQUE,
            brand TEXT NOT NULL,
            price REAL NOT NULL,
            stock INTEGER NOT NULL DEFAULT 0,
            image_url TEXT NOT NULL,
            short_description TEXT NOT NULL,
            details TEXT NOT NULL,
            FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
        )`,
    );

    await run(
        database,
        `CREATE TABLE IF NOT EXISTS basket_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            product_id INTEGER NOT NULL,
            quantity INTEGER NOT NULL DEFAULT 1 CHECK(quantity > 0),
            UNIQUE (user_id, product_id),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
        )`,
    );
}

async function seedStore(database) {
    for (const category of STORE_CATEGORIES) {
        await run(
            database,
            `INSERT OR IGNORE INTO categories (name, slug, description)
             VALUES (?, ?, ?)`,
            [category.name, category.slug, category.description],
        );
    }

    const categories = await all(database, "SELECT id, slug FROM categories");
    const categoryIds = new Map(categories.map((category) => [category.slug, category.id]));

    for (const product of STORE_PRODUCTS) {
        await run(
            database,
            `INSERT OR IGNORE INTO products (
                category_id,
                name,
                slug,
                brand,
                price,
                stock,
                image_url,
                short_description,
                details
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [
                categoryIds.get(product.categorySlug),
                product.name,
                product.slug,
                product.brand,
                product.price,
                product.stock,
                product.imageUrl,
                product.shortDescription,
                product.details,
            ],
        );
    }
}

async function initializeStore() {
    await withDatabase(async (database) => {
        await createStoreTables(database);

        const productsTotal = await get(database, "SELECT COUNT(*) AS total FROM products");
        if ((productsTotal?.total ?? 0) === 0) {
            await seedStore(database);
        }
    });
}

await initializeStore();

export class Products {
    static async getFeed(page, size) {
        const offset = (page - 1) * size;

        return withDatabase(async (database) => {
            const totalRow = await get(database, "SELECT COUNT(*) AS total FROM products");
            const items = await all(
                database,
                `SELECT
                    p.id,
                    p.name,
                    p.slug,
                    p.brand,
                    p.price,
                    p.stock,
                    p.image_url,
                    p.short_description,
                    c.name AS category_name,
                    c.slug AS category_slug
                FROM products AS p
                INNER JOIN categories AS c ON c.id = p.category_id
                ORDER BY p.id
                LIMIT ? OFFSET ?`,
                [size, offset],
            );

            return {
                page,
                size,
                total: totalRow?.total ?? 0,
                items,
            };
        });
    }

    static async getFeedByCategory(categorySlug, page, size) {
        const offset = (page - 1) * size;

        return withDatabase(async (database) => {
            const totalRow = await get(
                database,
                `SELECT COUNT(*) AS total
                 FROM products AS p
                 INNER JOIN categories AS c ON c.id = p.category_id
                 WHERE c.slug = ?`,
                [categorySlug],
            );

            const items = await all(
                database,
                `SELECT
                    p.id,
                    p.name,
                    p.slug,
                    p.brand,
                    p.price,
                    p.stock,
                    p.image_url,
                    p.short_description,
                    c.name AS category_name,
                    c.slug AS category_slug
                FROM products AS p
                INNER JOIN categories AS c ON c.id = p.category_id
                WHERE c.slug = ?
                ORDER BY p.id
                LIMIT ? OFFSET ?`,
                [categorySlug, size, offset],
            );

            return {
                page,
                size,
                total: totalRow?.total ?? 0,
                items,
            };
        });
    }

    static async getProductById(id) {
        return withDatabase(async (database) => {
            const product = await get(
                database,
                `SELECT
                    p.id,
                    p.name,
                    p.slug,
                    p.brand,
                    p.price,
                    p.stock,
                    p.image_url,
                    p.short_description,
                    p.details,
                    c.id AS category_id,
                    c.name AS category_name,
                    c.slug AS category_slug,
                    c.description AS category_description
                FROM products AS p
                INNER JOIN categories AS c ON c.id = p.category_id
                WHERE p.id = ?`,
                [id],
            );

            if (!product) {
                return null;
            }

            return {
                id: product.id,
                name: product.name,
                slug: product.slug,
                brand: product.brand,
                price: product.price,
                stock: product.stock,
                image_url: product.image_url,
                short_description: product.short_description,
                details: product.details,
                category: {
                    id: product.category_id,
                    name: product.category_name,
                    slug: product.category_slug,
                    description: product.category_description,
                },
            };
        });
    }
}

export class Categories {
    static async getAllCategories() {
        return withDatabase((database) =>
            all(
                database,
                `SELECT
                    c.id,
                    c.name,
                    c.slug,
                    c.description,
                    COUNT(p.id) AS products_count
                FROM categories AS c
                LEFT JOIN products AS p ON p.category_id = c.id
                GROUP BY c.id, c.name, c.slug, c.description
                ORDER BY c.name`,
            ),
        );
    }

    static async getCategoryBySlug(slug) {
        return withDatabase((database) =>
            get(
                database,
                `SELECT id, name, slug, description
                 FROM categories
                 WHERE slug = ?`,
                [slug],
            ),
        );
    }
}

export class Basket {
    static async getBasketByUserId(userId) {
        return withDatabase(async (database) => {
            const items = await all(
                database,
                `SELECT
                    p.id,
                    p.name,
                    p.slug,
                    p.brand,
                    p.price,
                    p.image_url,
                    p.short_description,
                    c.name AS category_name,
                    c.slug AS category_slug,
                    b.quantity,
                    ROUND(p.price * b.quantity, 2) AS line_total
                FROM basket_items AS b
                INNER JOIN products AS p ON p.id = b.product_id
                INNER JOIN categories AS c ON c.id = p.category_id
                WHERE b.user_id = ?
                ORDER BY b.id`,
                [userId],
            );

            const totals = await get(
                database,
                `SELECT
                    COALESCE(SUM(b.quantity), 0) AS total_items,
                    COALESCE(ROUND(SUM(b.quantity * p.price), 2), 0) AS total_amount
                FROM basket_items AS b
                INNER JOIN products AS p ON p.id = b.product_id
                WHERE b.user_id = ?`,
                [userId],
            );

            return {
                total_items: totals?.total_items ?? 0,
                total_amount: totals?.total_amount ?? 0,
                items,
            };
        });
    }

    static async addProduct(userId, productId, quantity) {
        return withDatabase(async (database) => {
            const existingItem = await get(
                database,
                `SELECT id, quantity
                 FROM basket_items
                 WHERE user_id = ? AND product_id = ?`,
                [userId, productId],
            );

            if (existingItem) {
                await run(
                    database,
                    `UPDATE basket_items
                     SET quantity = quantity + ?
                     WHERE id = ?`,
                    [quantity, existingItem.id],
                );

                return {
                    created: false,
                    quantity: existingItem.quantity + quantity,
                };
            }

            await run(
                database,
                `INSERT INTO basket_items (user_id, product_id, quantity)
                 VALUES (?, ?, ?)`,
                [userId, productId, quantity],
            );

            return {
                created: true,
                quantity,
            };
        });
    }

    static async deleteProduct(userId, productId) {
        return withDatabase(async (database) => {
            const result = await run(
                database,
                `DELETE FROM basket_items
                 WHERE user_id = ? AND product_id = ?`,
                [userId, productId],
            );

            return result.changes;
        });
    }
}

export class Token {
    static async deleteTokenByUserId(userId) {
        return withDatabase((database) =>
            run(database, "DELETE FROM refresh_tokens WHERE user_id = ?", [userId]),
        );
    }

    static async deleteToken(token) {
        return withDatabase((database) =>
            run(database, "DELETE FROM refresh_tokens WHERE token = ?", [token]),
        );
    }

    static async saveToken(userId, refreshToken, expiresAt) {
        return withDatabase((database) =>
            run(
                database,
                "INSERT INTO refresh_tokens (user_id, token, expires_at) VALUES (?, ?, ?)",
                [userId, refreshToken, expiresAt.toISOString()],
            ),
        );
    }

    static async getToken(token) {
        return withDatabase((database) =>
            get(
                database,
                `SELECT user_id, expires_at
                 FROM refresh_tokens
                 WHERE token = ?`,
                [token],
            ),
        );
    }
}

export class User {
    static async getUserByEmail(email) {
        return withDatabase((database) =>
            get(database, "SELECT * FROM users WHERE email = ?", [email]),
        );
    }

    static async getUserById(id) {
        return withDatabase((database) =>
            get(database, "SELECT * FROM users WHERE id = ?", [id]),
        );
    }

    static async create(email, hashedPassword, username) {
        return withDatabase((database) =>
            run(
                database,
                "INSERT INTO users (email, password, username) VALUES (?, ?, ?)",
                [email, hashedPassword, username],
            ),
        );
    }
}