backend/src/services/lab10/shop.service.js - master - Coderdev/web-nodejs-labs

backend/src/services/lab10/shop.service.js
233 строки8 KB
Coderdev
12 май 2026, 12:34
12 май 2026, 12:34f5e899f
// backend/src/services/lab10/shop.service.js

class ShopService {
  // ─── Категории ───────────────────────────────────────────────────────

  async getCategories(db) {
    const { rows } = await db.query('SELECT * FROM lab10.categories ORDER BY name');
    return rows;
  }

  // ─── Товары с фильтрацией и пагинацией ──────────────────────────────

  async getProducts(db, { page = 1, limit = 6, categoryId, search }) {
    const offset = (page - 1) * limit;
    const conditions = [];
    const values = [];

    if (categoryId) {
      values.push(categoryId);
      conditions.push(`p.category_id = $${values.length}`);
    }
    if (search) {
      values.push(`%${search}%`);
      conditions.push(`p.title ILIKE $${values.length}`);
    }

    const whereClause = conditions.length ? 'WHERE ' + conditions.join(' AND ') : '';

    const sql = `
      SELECT p.*, c.name AS category_title
      FROM lab10.products p
      LEFT JOIN lab10.categories c ON p.category_id = c.id
      ${whereClause}
      ORDER BY p.id
      LIMIT $${values.length + 1} OFFSET $${values.length + 2}
    `;

    const dataResult = await db.query(sql, [...values, limit, offset]);

    const countSql = `SELECT COUNT(*) FROM lab10.products p ${whereClause}`;
    const countResult = await db.query(countSql, values);

    return {
      data: dataResult.rows,
      total: parseInt(countResult.rows[0].count),
      page: parseInt(page),
      limit: parseInt(limit)
    };
  }

  // ─── Задание 1: карточка товара ──────────────────────────────────────

  async getProductById(db, id) {
    const { rows } = await db.query(`
      SELECT p.*, c.name AS category_title
      FROM lab10.products p
      LEFT JOIN lab10.categories c ON p.category_id = c.id
      WHERE p.id = $1
    `, [id]);
    return rows[0] || null;
  }

  // ─── Задание 1: все заказы ────────────────────────────────────────────

  async getAllOrders(db) {
    const { rows } = await db.query(`
      SELECT
        o.id,
        o.created_at,
        COUNT(oi.id) AS items_count,
        SUM(oi.price * oi.qty) AS total_sum
      FROM lab10.orders o
      LEFT JOIN lab10.order_items oi ON oi.order_id = o.id
      GROUP BY o.id
      ORDER BY o.created_at DESC
    `);
    return rows;
  }

  // ─── Задание 1: один заказ с деталями ─────────────────────────────────

  async getOrderById(db, id) {
    const { rows: orderRows } = await db.query(
      'SELECT * FROM lab10.orders WHERE id = $1',
      [id]
    );
    if (!orderRows[0]) return null;

    const { rows: items } = await db.query(`
      SELECT
        oi.id,
        oi.qty,
        oi.price AS unit_price,
        oi.qty * oi.price AS subtotal,
        p.title,
        p.image
      FROM lab10.order_items oi
      JOIN lab10.products p ON oi.product_id = p.id
      WHERE oi.order_id = $1
    `, [id]);

    const total = items.reduce((sum, i) => sum + Number(i.subtotal), 0);

    return {
      id: orderRows[0].id,
      created_at: orderRows[0].created_at,
      items,
      total_sum: total
    };
  }

  // ─── Покупка одного товара (транзакция) ──────────────────────────────

  async buyProduct(db, productId) {
    return db.transact(async (client) => {
      const checkRes = await client.query(
        'SELECT * FROM lab10.products WHERE id = $1 FOR UPDATE',
        [productId]
      );
      if (!checkRes.rows[0]) throw new Error('Товар не найден');
      const product = checkRes.rows[0];
      if (product.amount <= 0) throw new Error('Товар закончился');

      await client.query(
        'UPDATE lab10.products SET amount = amount - 1 WHERE id = $1',
        [productId]
      );

      const orderRes = await client.query(
        'INSERT INTO lab10.orders (created_at) VALUES (NOW()) RETURNING id'
      );
      const orderId = orderRes.rows[0].id;

      await client.query(
        'INSERT INTO lab10.order_items (order_id, product_id, qty, price) VALUES ($1, $2, $3, $4)',
        [orderId, productId, 1, product.price]
      );

      return { orderId };
    });
  }

  // ─── Покупка корзины (транзакция, несколько товаров) ─────────────────

  async buyCart(db, items) {
    return db.transact(async (client) => {
      // Блокируем и проверяем все товары
      for (const item of items) {
        const { rows } = await client.query(
          'SELECT id, title, amount, price FROM lab10.products WHERE id = $1 FOR UPDATE',
          [item.id]
        );
        if (!rows[0]) throw new Error(`Товар #${item.id} не найден`);
        if (rows[0].amount < item.qty) throw new Error(`Товар "${rows[0].title}" — не хватает на складе`);
      }

      // Создаём заказ
      const orderRes = await client.query(
        'INSERT INTO lab10.orders (created_at) VALUES (NOW()) RETURNING id'
      );
      const orderId = orderRes.rows[0].id;

      // Списываем и записываем позиции
      for (const item of items) {
        const { rows } = await client.query(
          'SELECT price FROM lab10.products WHERE id = $1',
          [item.id]
        );
        await client.query(
          'UPDATE lab10.products SET amount = amount - $1 WHERE id = $2',
          [item.qty, item.id]
        );
        await client.query(
          'INSERT INTO lab10.order_items (order_id, product_id, qty, price) VALUES ($1, $2, $3, $4)',
          [orderId, item.id, item.qty, rows[0].price]
        );
      }

      return { ok: true, orderId };
    });
  }

  // ─── Расписание с динамическими фильтрами ─────────────────────────────

  async getSchedule(db, filters) {
    const { week, group_id, day, room_id, discipline_id, teacher_id, lesson_type } = filters;
    const conditions = [];
    const values = [];

    const add = (cond, val) => {
      values.push(val);
      conditions.push(`${cond} = $${values.length}`);
    };

    if (week)          add('s.week', week);
    if (group_id)      add('s.group_id', group_id);
    if (day)           add('s.day_of_week', day);
    if (room_id)       add('s.room_id', room_id);
    if (discipline_id) add('s.discipline_id', discipline_id);
    if (teacher_id)    add('s.teacher_id', teacher_id);
    if (lesson_type)   add('s.lesson_type', lesson_type);

    const where = conditions.length ? 'WHERE ' + conditions.join(' AND ') : '';

    const { rows } = await db.query(`
      SELECT
        s.id,
        s.week,
        s.day_of_week,
        s.lesson_num,
        s.lesson_type,
        g.name AS group_name,
        d.name AS discipline_name,
        r.number AS room_number,
        b.name AS building_name,
        p.last_name || ' ' || LEFT(p.first_name,1) || '.' ||
          CASE WHEN p.middle_name IS NOT NULL THEN LEFT(p.middle_name,1) || '.' ELSE '' END AS teacher_name
      FROM lab10_schedule.schedule s
      JOIN lab10_schedule.groups g ON s.group_id = g.id
      JOIN lab10_schedule.disciplines d ON s.discipline_id = d.id
      JOIN lab10_schedule.rooms r ON s.room_id = r.id
      JOIN lab10_schedule.buildings b ON r.building_id = b.id
      JOIN lab10_schedule.teachers t ON s.teacher_id = t.id
      JOIN lab10_schedule.persons p ON t.person_id = p.id
      ${where}
      ORDER BY s.week, s.day_of_week, s.lesson_num, g.name
    `, values);

    return rows;
  }
}

export default new ShopService();