[MSA] e-commerce API; 구현2.2 - product

[MSA] e-commerce API; 프로젝트 흐름

[MSA] e-commerce API; 구현1 - 설정(config-server, eureka-server, gateway)

[MSA] e-commerce API; 구현2 - customer, product, payment, order, notification

(앞선 게시글에 작성한 내용은 생략할 예정입니다)

 

작성한 ERD를 참조하여 구현합니다.

Product

제품(Product)은 다양한 범주(Category)가 있습니다.

Product를 연관관계의 주인으로 보고 다대일 관계로 설정합니다.

일관성이 중요한 데이터이므로 RDBMS에 저장합니다.

(RDBMS는 postgreSQL을 사용했습니다)

 

아래는 product 프로젝트의 의존성입니다.

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-actuator'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-validation'
    implementation 'org.flywaydb:flyway-database-postgresql'
    implementation 'io.micrometer:micrometer-tracing-bridge-brave'
    implementation 'io.zipkin.reporter2:zipkin-reporter-brave'
    implementation 'org.springframework.cloud:spring-cloud-starter-config'
    implementation 'org.springframework.cloud:spring-cloud-starter-netflix-eureka-client'
    compileOnly 'org.projectlombok:lombok'
    runtimeOnly 'org.postgresql:postgresql'
    annotationProcessor 'org.projectlombok:lombok'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    testRuntimeOnly 'org.junit.platform:junit-platform-launcher'
}

주요 의존성으로는 ORM으로 JPA를 사용하는 것,

postgresql관련 의존성,

flyway를 사용하여 db 스키마를 관리할 것이므로 flyway 의존성이 되겠습니다.

 

product의 설정파일인 application.yml은 아래와 같습니다.

spring:
  application:
    name: product-service
  config:
    import: optional:configserver:http://localhost:8888

이제 config-server에 product-service.yml을 작성합니다.

server:
  port: 8050
spring:
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5432/product
    username: ride
    password: password
  jpa:
    hibernate:
      ddl-auto: validate
    database: postgresql
    database-platform: org.hibernate.dialect.PostgreSQLDialect
  flyway:
    baseline-on-migrate: true
    enabled: true
    baseline-description: "init"
    baseline-version: 0
    user: ${spring.datasource.username}
    password: ${spring.datasource.password}

jpa.hibernate.ddl-auto를 validate로 설정하여 스키마 간 불일치가 발생해도 이를 수정하지 않습니다.

update로 설정하면, 스키마와 엔티티 불일치 시 DB가 변경되며 데이터에 손실이 발생할 수 있지만,

validate로 설정 시 스키마와 엔티티가 불일치하면, 애플리케이션이 실행되지 않을 수 있습니다.

이때 flyway와 같은 마이그레이션 도구를 활용하여 DB 스키마에 대한 버전 관리를 하는 것으로,

불일치를 대처합니다.

(개발 편의상 update를 적용할 수도 있습니다)

product DB에 flyway 관련 데이터를 담고 있는 테이블이 생성됩니다.

 

아래는 product도메인이 사용하는 postgreSQL의 product database입니다.

1.0 디렉토리 구조

product의 디렉토리 구조는 아래와 같습니다.

exception 디렉토리에 Product에 대한 Exception 클래스(ProductPurchaseException)를 생성할 것이며,

handler 디렉토리에 Exception 전역처리 설정을 위한 GlobalExceptionHandler를 구성합니다.

product 디렉토리에 product 도메인과 연관된 Category, Purchase 등에 대한 코드, 파일을 구성합니다.

 

flyway를 사용하면 아래와 같은 디렉토리를 생성합니다.

파일명은 <버전>__<설명>.sql 형식으로(언더버 2개입니다),

설명의 공백도 언더바를 사용합니다.

1.1 product

1.1.1 Product, Category class

Product, Category Entity를 정의합니다.

package com.ride.ecommerce.product;

import jakarta.persistence.*;
import lombok.*;

import java.math.BigDecimal;

@AllArgsConstructor
@NoArgsConstructor
@Builder
@Getter
@Setter
@Entity
public class Product {

    @Id
    @GeneratedValue
    private Integer id;
    private String name;
    private String description;
    private double availableQuantity;
    private BigDecimal price;
    @ManyToOne
    @JoinColumn(name = "category_id")
    private Category category;
}

@Entity 어노테이션을 사용합니다.

@Id 을 통해 PK를 설정하고, @GeneratedValue를 통해 시퀀스 설정을 합니다.

@ManyToOne, @JoinColumn으로 Category와 다대일 관계를 설정합니다.

package com.ride.ecommerce.product;

import jakarta.persistence.*;
import lombok.*;

import java.util.List;

@AllArgsConstructor
@NoArgsConstructor
@Builder
@Getter
@Setter
@Entity
public class Category {
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
    private String description;
    @OneToMany(mappedBy = "category", cascade = CascadeType.REMOVE)
    private List<Product> products;
}

@OneToMany를 통해 Product와 Category의 일대다 관계를 설정합니다.

그러나 다대일 관계만으로도 충분할 수 있습니다.

리소스 상황을 포함한 요구사항에 따릅니다.

1.1.2 ProductController

package com.ride.ecommerce.product;

import jakarta.validation.Valid;
import lombok.RequiredArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * REST controller for managing products.
 */
@RestController
@RequestMapping("/api/v1/products")
@RequiredArgsConstructor
public class ProductController {

    private final ProductService service;

    /**
     * POST /api/v1/products : Create a new product.
     *
     * @param request the product request with product details
     * @return the ResponseEntity with status 200 (OK) and the ID of the created product
     */
    @PostMapping
    public ResponseEntity<Integer> createProduct(
            @RequestBody @Valid ProductRequest request
    ) {
        return ResponseEntity.ok(service.createProduct(request));
    }

    /**
     * POST /api/v1/products/purchase : Purchase products.
     *
     * @param request the list of product purchase requests
     * @return the ResponseEntity with status 200 (OK) and the list of product purchase responses
     */
    @PostMapping("/purchase")
    public ResponseEntity<List<ProductPurchaseResponse>> purchaseProducts(
            @RequestBody List<ProductPurchaseRequest> request
    ) {
        return ResponseEntity.ok(service.purchaseProducts(request));
    }

    /**
     * GET /api/v1/products/{product-id} : Get a product by ID.
     *
     * @param productId the ID of the product to retrieve
     * @return the ResponseEntity with status 200 (OK) and the product details
     */
    @GetMapping("/{product-id}")
    public ResponseEntity<ProductResponse> findById(
            @PathVariable("product-id") Integer productId
    ) {
        return ResponseEntity.ok(service.findById(productId));
    }

    /**
     * GET /api/v1/products : Get all products.
     *
     * @return the ResponseEntity with status 200 (OK) and the list of products
     */
    @GetMapping
    public ResponseEntity<List<ProductResponse>> findAll() {
        return ResponseEntity.ok(service.findAll());
    }
}

아래는 CustomerController와 중복되지 않은 메서드로 order 프로젝트에서 호출할 메서드입니다.

/**
 * POST /api/v1/products/purchase : Purchase products.
 *
 * @param request the list of product purchase requests
 * @return the ResponseEntity with status 200 (OK) and the list of product purchase responses
 */
@PostMapping("/purchase")
public ResponseEntity<List<ProductPurchaseResponse>> purchaseProducts(
        @RequestBody List<ProductPurchaseRequest> request
) {
    return ResponseEntity.ok(service.purchaseProducts(request));
}

1.1.3 ProductRequest, ProductResponse record

package com.ride.ecommerce.product;

import jakarta.validation.constraints.NotNull;
import jakarta.validation.constraints.Positive;

import java.math.BigDecimal;

public record ProductRequest(
        Integer id,
        @NotNull(message = "Product name is required")
        String name,
        @NotNull(message = "Product description is required")
        String description,
        @Positive(message = "Available quantity should be positive")
        double availableQuantity,
        @Positive(message = "Price should be positive")
        BigDecimal price,
        @NotNull(message = "Product category is required")
        Integer categoryId
) {
}

@Positive는 숫자형 필드 값이 양수(positive)인지 검사합니다.

package com.ride.ecommerce.product;

import java.math.BigDecimal;

public record ProductResponse(
        Integer id,
        String name,
        String description,
        double availableQuantity,
        BigDecimal price,
        Integer categoryId,
        String categoryName,
        String categoryDescription
) {
}

1.1.4 ProductPurchaseRequest, ProductPurchaseResponse record

package com.ride.ecommerce.product;

import jakarta.validation.constraints.NotNull;

public record ProductPurchaseRequest(
        @NotNull(message = "Product is mandatory")
        Integer productId,
        @NotNull(message = "Quantity is mandatory")
        double quantity
) {
}

package com.ride.ecommerce.product;

import java.math.BigDecimal;

public record ProductPurchaseResponse(
        Integer productId,
        String name,
        String description,
        BigDecimal price,
        double quantity
) {
}

1.1.5 ProductService, ProductMapper class

package com.ride.ecommerce.product;

import com.ride.ecommerce.exception.ProductPurchaseException;
import jakarta.persistence.EntityNotFoundException;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;

import static java.util.stream.Collectors.toList;


/**
 * Service class for managing products.
 */
@Service
@RequiredArgsConstructor
public class ProductService {

    private final ProductRepository repository;
    private final ProductMapper mapper;

    /**
     * Creates a new product.
     *
     * @param request the product request containing product details
     * @return the ID of the created product
     */
    public Integer createProduct(ProductRequest request) {
        var product = mapper.toProduct(request);
        return repository.save(product).getId();
    }

    /**
     * Processes the purchase of multiple products.
     *
     * @param request the list of product purchase requests
     * @return the list of product purchase responses
     * @throws ProductPurchaseException if one or more products do not exist or if there is insufficient stock
     */
    public List<ProductPurchaseResponse> purchaseProducts(List<ProductPurchaseRequest> request) {
        var productIds = request
                .stream()
                .map(ProductPurchaseRequest::productId)
                .toList();
        var storedProducts = repository.findAllByIdInOrderById(productIds);
        if (productIds.size() != storedProducts.size()) {
            throw new ProductPurchaseException("One or more products does not exits");
        }
        var storedRequest = request
                .stream()
                .sorted(Comparator.comparing(ProductPurchaseRequest::productId))
                .toList();
        var purchasedProducts = new ArrayList<ProductPurchaseResponse>();
        for (int i = 0; i < storedProducts.size(); i++) {
            var product = storedProducts.get(i);
            var productRequest = storedRequest.get(i);
            if (product.getAvailableQuantity() < productRequest.quantity()) {
                throw new ProductPurchaseException("Insufficient stock quantity for product with ID:: " + productRequest.productId());
            }
            var newAvailableQuantity = product.getAvailableQuantity() - productRequest.quantity();
            product.setAvailableQuantity(newAvailableQuantity);
            repository.save(product);
            purchasedProducts.add(mapper.toProductPurchaseResponse(product, productRequest.quantity()));
        }

        return purchasedProducts;
    }

    /**
     * Finds a product by its ID.
     *
     * @param productId the ID of the product to retrieve
     * @return the product response containing product details
     * @throws EntityNotFoundException if no product is found with the given ID
     */
    public ProductResponse findById(Integer productId) {
        return repository.findById(productId)
                .map(mapper::toProductResponse)
                .orElseThrow(() -> new EntityNotFoundException("Product not found with the ID:: " + productId));
    }

    /**
     * Finds all products.
     *
     * @return the list of product responses containing details of all products
     */
    public List<ProductResponse> findAll() {
        return repository.findAll()
                .stream()
                .map(mapper::toProductResponse)
                .collect(toList());
    }
}

아래는 purchase 관련 로직입니다.

/**
 * Processes the purchase of multiple products.
 *
 * @param request the list of product purchase requests
 * @return the list of product purchase responses
 * @throws ProductPurchaseException if one or more products do not exist or if there is insufficient stock
 */
public List<ProductPurchaseResponse> purchaseProducts(List<ProductPurchaseRequest> request) {
    var productIds = request
            .stream()
            .map(ProductPurchaseRequest::productId)
            .toList();
    var storedProducts = repository.findAllByIdInOrderById(productIds);
    if (productIds.size() != storedProducts.size()) {
        throw new ProductPurchaseException("One or more products does not exits");
    }
    var storedRequest = request
            .stream()
            .sorted(Comparator.comparing(ProductPurchaseRequest::productId))
            .toList();
    var purchasedProducts = new ArrayList<ProductPurchaseResponse>();
    for (int i = 0; i < storedProducts.size(); i++) {
        var product = storedProducts.get(i);
        var productRequest = storedRequest.get(i);
        if (product.getAvailableQuantity() < productRequest.quantity()) {
            throw new ProductPurchaseException("Insufficient stock quantity for product with ID:: " + productRequest.productId());
        }
        var newAvailableQuantity = product.getAvailableQuantity() - productRequest.quantity();
        product.setAvailableQuantity(newAvailableQuantity);
        repository.save(product);
        purchasedProducts.add(mapper.toProductPurchaseResponse(product, productRequest.quantity()));
    }

    return purchasedProducts;
}

public List<ProductPurchaseResponse> purchaseProducts(List<ProductPurchaseRequest> request) {
    var productIds = request
            .stream()
            .map(ProductPurchaseRequest::productId)
            .toList();
    var storedProducts = repository.findAllByIdInOrderById(productIds);
    if (productIds.size() != storedProducts.size()) {
        throw new ProductPurchaseException("One or more products does not exits");
    }

클라이언트가 List<ProductPurchaseReqeust> 객체를 보내면, 먼저 해당 id의 product가 존재하는지 확인합니다.

var storedRequest = request
        .stream()
        .sorted(comparing(ProductPurchaseRequest::productId))
        .toList();
var purchasedProducts = new ArrayList<ProductPurchaseResponse>();
for (int i = 0; i < storedProducts.size(); i++) {
    var product = storedProducts.get(i);
    var productRequest = storedRequest.get(i);
    if (product.getAvailableQuantity() < productRequest.quantity()) {
        throw new ProductPurchaseException("Insufficient stock quantity for product with ID:: " + productRequest.productId());
    }

클라이언트가 요청한 product의 개수와 DB product의 가용한 개수를 비교하고,

가용한 개수를 초과한 요청이라면 ProductPurchaseException 예외를 발생시킵니다.

    var newAvailableQuantity = product.getAvailableQuantity() - productRequest.quantity();
    product.setAvailableQuantity(newAvailableQuantity);
    repository.save(product);
    purchasedProducts.add(mapper.toProductPurchaseResponse(product, productRequest.quantity()));
}

return purchasedProducts;

 

예외가 발생하지 않는다면, 클라이언트의 요청을 DB에 적용하고,

List<ProductPurchaseResponse>객체로 변환한 뒤에 클라이언트에게 반환합니다.

package com.ride.ecommerce.product;

import org.springframework.stereotype.Service;

/**
 * Mapper class for converting between Product entities and various DTOs.
 */
@Service
public class ProductMapper {

    /**
     * Converts a ProductRequest DTO to a Product entity.
     *
     * @param request the product request containing product details
     * @return the Product entity
     */
    public Product toProduct(ProductRequest request) {
        return Product.builder()
                .id(request.id())
                .name(request.name())
                .description(request.description())
                .price(request.price())
                .availableQuantity(request.availableQuantity())
                .category(
                        Category.builder()
                                .id(request.categoryId())
                                .build()
                )
                .build();
    }

    /**
     * Converts a Product entity to a ProductResponse DTO.
     *
     * @param product the Product entity
     * @return the ProductResponse DTO
     */
    public ProductResponse toProductResponse(Product product) {
        return new ProductResponse(
                product.getId(),
                product.getName(),
                product.getDescription(),
                product.getAvailableQuantity(),
                product.getPrice(),
                product.getCategory().getId(),
                product.getCategory().getName(),
                product.getCategory().getDescription()
        );
    }

    /**
     * Converts a Product entity to a ProductPurchaseResponse DTO.
     *
     * @param product the Product entity
     * @param quantity the quantity of the product purchased
     * @return the ProductPurchaseResponse DTO
     */
    public ProductPurchaseResponse toProductPurchaseResponse(Product product, double quantity) {
        return new ProductPurchaseResponse(
                product.getId(),
                product.getName(),
                product.getDescription(),
                product.getPrice(),
                quantity
        );
    }
}

1.1.6 ProductRepository interface

package com.ride.ecommerce.product;

import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface ProductRepository extends JpaRepository<Product, Integer> {
    List<Product> findAllByIdInOrderById(List<Integer> productIds);
}

JpaRepository를 extends합니다.

order에서 요청한 데이터의 productId리스트로 Product를 조회하도록 합니다.

현재는 Spring Data JPA interface 에 의존하여 코드를 작성했기에,

상대적으로 구현 난도가 낮으나 최적화되어 있지 않습니다.

(최적화 게시글에서 다루도록 하겠습니다)

1.2 exception

1.2.1 ProductPurchaseException class

제품 구매 중 오류가 발생했을 때 던질 예외를 정의합니다.

package com.ride.ecommerce.exception;

/**
 * Exception thrown when an error occurs during the purchase of a product.
 * This exception is a runtime exception which means it is unchecked and does not need to be declared in a method's or constructor's 'throws' clause.
 */
public class ProductPurchaseException extends RuntimeException {

    /**
     * Constructs a new ProductPurchaseException with the specified detail message.
     * The detail message is saved for later retrieval by the getMessage() method.
     *
     * @param message the detail message
     */
    public ProductPurchaseException(String message) {
        super(message);
    }
}

1.3 handler

1.3.1 GlobalExeptionHandler class

package com.ride.ecommerce.handler;

import com.ride.ecommerce.exception.ProductPurchaseException;
import jakarta.persistence.EntityNotFoundException;
import org.springframework.http.ResponseEntity;
import org.springframework.validation.FieldError;
import org.springframework.web.bind.MethodArgumentNotValidException;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;

import java.util.HashMap;

import static org.springframework.http.HttpStatus.*;

/**
 * Global exception handler for handling various exceptions across the application.
 */
@RestControllerAdvice
public class GlobalExceptionHandler {

    /**
     * Handles ProductPurchaseException and returns a 400 BAD REQUEST response.
     *
     * @param exp the ProductPurchaseException instance
     * @return a ResponseEntity with a 400 status and the exception message
     */
    @ExceptionHandler(ProductPurchaseException.class)
    public ResponseEntity<String> handle(ProductPurchaseException exp) {
        return ResponseEntity
                .status(BAD_REQUEST)
                .body(exp.getMessage());
    }

    /**
     * Handles EntityNotFoundException and returns a 400 BAD REQUEST response.
     *
     * @param exp the EntityNotFoundException instance
     * @return a ResponseEntity with a 400 status and the exception message
     */
    @ExceptionHandler(EntityNotFoundException.class)
    public ResponseEntity<String> handle(EntityNotFoundException exp) {
        return ResponseEntity
                .status(BAD_REQUEST)
                .body(exp.getMessage());
    }

    /**
     * Handles MethodArgumentNotValidException and returns a 400 BAD REQUEST response with validation errors.
     *
     * @param exp the MethodArgumentNotValidException instance
     * @return a ResponseEntity with a 400 status and the validation errors
     */
    @ExceptionHandler(MethodArgumentNotValidException.class)
    public ResponseEntity<ErrorResponse> handle(MethodArgumentNotValidException exp) {
        var errors = new HashMap<String, String>();
        exp.getBindingResult().getAllErrors()
                .forEach(error -> {
                    var fieldName = ((FieldError)error).getField();
                    var errorMessage = error.getDefaultMessage();
                    errors.put(fieldName, errorMessage);
                });

        return ResponseEntity
                .status(BAD_REQUEST)
                .body(new ErrorResponse(errors));
    }
}

1.3.2 ErrorResponse record

package com.ride.ecommerce.handler;

import java.util.Map;

public record ErrorResponse(
        Map<String, String> errors
) {
}

1.4 resources.db.migration

flyway를 활용하여 스키마의 버전관리를 합니다.

1.4.1 V1__init_database.sql

create table if not exists category
(
    id integer not null primary key,
    description varchar(255),
    name varchar(255)
);

create table if not exists product
(
    id integer not null primary key,
    description varchar(255),
    name varchar(255),
    available_quantity double precision not null,
    price numeric(38, 2),
    category_id integer
            constraint fk_product_category references category
);

create sequence if not exists category_seq increment by 50;
create sequence if not exists product_seq increment by 50;

1.4.2 V2__insert_data.sql

INSERT INTO category (id, description, name) VALUES (nextval('category_seq'), 'Computer Keyboards', 'Keyboards');
INSERT INTO category (id, description, name) VALUES (nextval('category_seq'), 'Computer Monitors', 'Monitors');
INSERT INTO category (id, description, name) VALUES (nextval('category_seq'), 'Display Screens', 'Screens');
INSERT INTO category (id, description, name) VALUES (nextval('category_seq'), 'Computer Mice', 'Mice');
INSERT INTO category (id, description, name) VALUES (nextval('category_seq'), 'Computer Accessories', 'Accessories');

-- Assuming you already have a sequence named 'product_seq'

-- Insert products for the 'Keyboards' category
INSERT INTO public.product (id, available_quantity, description, name, price, category_id)
VALUES
    (nextval('product_seq'), 10, 'Mechanical keyboard with RGB lighting', 'Mechanical Keyboard 1', 99.99, (SELECT id FROM category WHERE name = 'Keyboards')),
    (nextval('product_seq'), 15, 'Wireless compact keyboard', 'Wireless Compact Keyboard 1', 79.99, (SELECT id FROM category WHERE name = 'Keyboards')),
    (nextval('product_seq'), 20, 'Backlit gaming keyboard with customizable keys', 'Gaming Keyboard 1', 129.99, (SELECT id FROM category WHERE name = 'Keyboards')),
    (nextval('product_seq'), 25, 'Mechanical keyboard with wrist rest', 'Ergonomic Keyboard 1', 109.99, (SELECT id FROM category WHERE name = 'Keyboards')),
    (nextval('product_seq'), 18, 'Wireless keyboard and mouse combo', 'Wireless Combo 1', 69.99, (SELECT id FROM category WHERE name = 'Keyboards'));

-- Insert products for the 'Monitors' category
INSERT INTO public.product (id, available_quantity, description, name, price, category_id)
VALUES
    (nextval('product_seq'), 30, '27-inch IPS monitor with 4K resolution', '4K Monitor 1', 399.99, (SELECT id FROM category WHERE name = 'Monitors')),
    (nextval('product_seq'), 25, 'Ultra-wide gaming monitor with HDR support', 'Ultra-wide Gaming Monitor 1', 499.99, (SELECT id FROM category WHERE name = 'Monitors')),
    (nextval('product_seq'), 22, '24-inch LED monitor for office use', 'Office Monitor 1', 179.99, (SELECT id FROM category WHERE name = 'Monitors')),
    (nextval('product_seq'), 28, '32-inch curved monitor with AMD FreeSync', 'Curved Monitor 1', 329.99, (SELECT id FROM category WHERE name = 'Monitors')),
    (nextval('product_seq'), 35, 'Portable USB-C monitor for laptops', 'Portable Monitor 1', 249.99, (SELECT id FROM category WHERE name = 'Monitors'));

-- Insert products for the 'Screens' category
INSERT INTO public.product (id, available_quantity, description, name, price, category_id)
VALUES
    (nextval('product_seq'), 15, 'Curved OLED gaming screen with 240Hz refresh rate', 'Curved OLED Gaming Screen 1', 799.99, (SELECT id FROM category WHERE name = 'Screens')),
    (nextval('product_seq'), 18, 'Flat QLED monitor with 1440p resolution', 'QLED Monitor 1', 599.99, (SELECT id FROM category WHERE name = 'Screens')),
    (nextval('product_seq'), 22, '27-inch touch screen display for creative work', 'Touch Screen Display 1', 699.99, (SELECT id FROM category WHERE name = 'Screens')),
    (nextval('product_seq'), 20, 'Ultra-slim 4K HDR display for multimedia', 'Ultra-slim 4K HDR Display 1', 449.99, (SELECT id FROM category WHERE name = 'Screens')),
    (nextval('product_seq'), 25, 'Gaming projector with low input lag', 'Gaming Projector 1', 899.99, (SELECT id FROM category WHERE name = 'Screens'));

-- Insert products for the 'Mice' category
INSERT INTO public.product (id, available_quantity, description, name, price, category_id)
VALUES
    (nextval('product_seq'), 30, 'Wireless gaming mouse with customizable RGB lighting', 'RGB Gaming Mouse 1', 59.99, (SELECT id FROM category WHERE name = 'Mice')),
    (nextval('product_seq'), 28, 'Ergonomic wired mouse for productivity', 'Ergonomic Wired Mouse 1', 29.99, (SELECT id FROM category WHERE name = 'Mice')),
    (nextval('product_seq'), 32, 'Ambidextrous gaming mouse with high DPI', 'Ambidextrous Gaming Mouse 1', 69.99, (SELECT id FROM category WHERE name = 'Mice')),
    (nextval('product_seq'), 26, 'Travel-sized compact mouse for laptops', 'Travel Mouse 1', 19.99, (SELECT id FROM category WHERE name = 'Mice')),
    (nextval('product_seq'), 35, 'Vertical ergonomic mouse for reduced strain', 'Vertical Ergonomic Mouse 1', 39.99, (SELECT id FROM category WHERE name = 'Mice'));

-- Insert products for the 'Accessories' category
INSERT INTO public.product (id, available_quantity, description, name, price, category_id)
VALUES
    (nextval('product_seq'), 25, 'Adjustable laptop stand with cooling fan', 'Adjustable Laptop Stand 1', 34.99, (SELECT id FROM category WHERE name = 'Accessories')),
    (nextval('product_seq'), 20, 'Wireless charging pad for smartphones', 'Wireless Charging Pad 1', 24.99, (SELECT id FROM category WHERE name = 'Accessories')),
    (nextval('product_seq'), 28, 'Gaming headset stand with RGB lighting', 'RGB Headset Stand 1', 49.99, (SELECT id FROM category WHERE name = 'Accessories')),
    (nextval('product_seq'), 22, 'Bluetooth mechanical keypad for tablets', 'Bluetooth Keypad 1', 39.99, (SELECT id FROM category WHERE name = 'Accessories')),
    (nextval('product_seq'), 30, 'External hard drive enclosure with USB-C', 'External Hard Drive Enclosure 1', 29.99, (SELECT id FROM category WHERE name = 'Accessories'));