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

backend/src/services/lab10/shop.service.js
271 строка8 KB
Coderdev
28 апр 2026, 12:34
28 апр 2026, 12:34df65d61
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 safePage = Math.max(1, parseInt(page) || 1);
    const safeLimit = Math.max(1, parseInt(limit) || 6);
    const offset = (safePage - 1) * safeLimit;

    const values = [];
    const conditions = [];

    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 where = conditions.length ? 'WHERE ' + conditions.join(' AND ') : '';

    const dataResult = 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}
      ORDER BY p.id
      LIMIT $${values.length + 1} OFFSET $${values.length + 2}
    `, [...values, safeLimit, offset]);

    const countResult = await db.query(
      `SELECT COUNT(*)::int AS count FROM lab10.products p ${where}`,
      values
    );

    return {
      data: dataResult.rows,
      total: countResult.rows[0].count,
      page: safePage,
      limit: safeLimit,
      pages: Math.ceil(countResult.rows[0].count / safeLimit)
    };
  }

  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];
  }

  async buyProduct(db, productId) {
    return db.transact(async (client) => {
      const checkRes = await client.query(
        'SELECT id, title, price, amount FROM lab10.products WHERE id = $1 FOR UPDATE',
        [productId]
      );

      if (checkRes.rows.length === 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 (product_id, quantity, price_at_purchase) VALUES ($1, $2, $3) RETURNING *',
        [productId, 1, product.price]
      );

      return {
        orderId: orderRes.rows[0].id,
        productId: product.id,
        title: product.title,
        quantity: 1,
        price: product.price
      };
    });
  }

  async getOrders(db) {
    const { rows } = await db.query(`
      SELECT id, created_at, (quantity * price_at_purchase)::bigint AS total_sum, 1 AS positions
      FROM lab10.orders
      ORDER BY id DESC
    `);

    return rows;
  }

  async getOrderById(db, id) {
    const orderRes = await db.query(
      'SELECT id, created_at FROM lab10.orders WHERE id = $1',
      [id]
    );

    if (orderRes.rows.length === 0) return null;

    const itemsRes = await db.query(`
      SELECT o.product_id, p.title, o.quantity, o.price_at_purchase,
             (o.quantity * o.price_at_purchase)::bigint AS line_total
      FROM lab10.orders o
      LEFT JOIN lab10.products p ON p.id = o.product_id
      WHERE o.id = $1
    `, [id]);

    const total = itemsRes.rows.reduce((sum, item) => sum + Number(item.line_total), 0);

    return {
      id: orderRes.rows[0].id,
      created_at: orderRes.rows[0].created_at,
      items: itemsRes.rows,
      total_sum: total
    };
  }

  async getScheduleDictionaries(db) {
    const [groups, weeks, days, rooms, subjects, lessonTypes, teachers] = await Promise.all([
      db.query('SELECT * FROM lab10.study_groups ORDER BY name'),
      db.query('SELECT * FROM lab10.weeks ORDER BY id'),
      db.query('SELECT * FROM lab10.days ORDER BY id'),
      db.query('SELECT * FROM lab10.rooms ORDER BY name'),
      db.query('SELECT * FROM lab10.subjects ORDER BY name'),
      db.query('SELECT * FROM lab10.lesson_types ORDER BY name'),
      db.query('SELECT * FROM lab10.teachers ORDER BY fullname')
    ]);

    return {
      groups: groups.rows,
      weeks: weeks.rows,
      days: days.rows,
      rooms: rooms.rows,
      subjects: subjects.rows,
      lessonTypes: lessonTypes.rows,
      teachers: teachers.rows
    };
  }

  async getSchedule(db, filters = {}) {
    const values = [];
    const conditions = [];

    const map = [
      ['s.week_id', filters.week],
      ['s.group_id', filters.group],
      ['s.day_id', filters.day],
      ['s.room_id', filters.room],
      ['s.subject_id', filters.subject],
      ['s.teacher_id', filters.teacher],
      ['s.lesson_type_id', filters.lessonType]
    ];

    for (const [field, value] of map) {
      if (value) {
        values.push(value);
        conditions.push(`${field} = $${values.length}`);
      }
    }

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

    const { rows } = await db.query(`
      SELECT s.id, w.name AS week, g.name AS group_name, d.name AS day_name,
             s.lesson_number, sub.name AS subject, lt.name AS lesson_type,
             t.fullname AS teacher, r.name AS room
      FROM lab10.schedule s
      LEFT JOIN lab10.weeks w ON w.id = s.week_id
      LEFT JOIN lab10.study_groups g ON g.id = s.group_id
      LEFT JOIN lab10.days d ON d.id = s.day_id
      LEFT JOIN lab10.subjects sub ON sub.id = s.subject_id
      LEFT JOIN lab10.lesson_types lt ON lt.id = s.lesson_type_id
      LEFT JOIN lab10.teachers t ON t.id = s.teacher_id
      LEFT JOIN lab10.rooms r ON r.id = s.room_id
      ${where}
      ORDER BY g.name, w.id, d.id, s.lesson_number
    `, values);

    return rows;
  }

  async createScheduleRecord(db, data) {
    const { week_id, group_id, day_id, lesson_number, subject_id, lesson_type_id, teacher_id, room_id } = data;

    const { rows } = await db.query(`
      INSERT INTO lab10.schedule
      (week_id, group_id, day_id, lesson_number, subject_id, lesson_type_id, teacher_id, room_id)
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8)
      RETURNING *
    `, [week_id, group_id, day_id, lesson_number, subject_id, lesson_type_id, teacher_id, room_id]);

    return rows[0];
  }

  async updateScheduleRecord(db, id, data) {
    const { week_id, group_id, day_id, lesson_number, subject_id, lesson_type_id, teacher_id, room_id } = data;

    const { rows } = await db.query(`
      UPDATE lab10.schedule
      SET week_id=$1, group_id=$2, day_id=$3, lesson_number=$4,
          subject_id=$5, lesson_type_id=$6, teacher_id=$7, room_id=$8
      WHERE id=$9
      RETURNING *
    `, [week_id, group_id, day_id, lesson_number, subject_id, lesson_type_id, teacher_id, room_id, id]);

    return rows[0];
  }

  async deleteScheduleRecord(db, id) {
    const { rows } = await db.query(
      'DELETE FROM lab10.schedule WHERE id = $1 RETURNING id',
      [id]
    );

    return rows[0];
  }

  async createSubject(db, data) {
    const { name } = data;

    const { rows } = await db.query(
      'INSERT INTO lab10.subjects (name) VALUES ($1) RETURNING *',
      [name]
    );

    return rows[0];
  }

  async deleteSubject(db, id) {
    const { rows } = await db.query(
      'DELETE FROM lab10.subjects WHERE id = $1 RETURNING id',
      [id]
    );

    return rows[0];
  }

  async createTeacher(db, data) {
    const { fullname } = data;

    const { rows } = await db.query(
      'INSERT INTO lab10.teachers (fullname) VALUES ($1) RETURNING *',
      [fullname]
    );

    return rows[0];
  }

  async deleteTeacher(db, id) {
    const { rows } = await db.query(
      'DELETE FROM lab10.teachers WHERE id = $1 RETURNING id',
      [id]
    );

    return rows[0];
  }
}

export default new ShopService();