sikozonpc · charucjoshi · Dec 16, 2024
diff --git a/cmd/migrate/migrations/20240518192420_add-productquantity-table.down.sql b/cmd/migrate/migrations/20240518192420_add-productquantity-table.down.sql
@@ -0,0 +1 @@
+DROP TABLE IF EXISTS productquantity;
diff --git a/cmd/migrate/migrations/20240518192420_add-productquantity-table.up.sql b/cmd/migrate/migrations/20240518192420_add-productquantity-table.up.sql
@@ -0,0 +1,6 @@
+CREATE TABLE IF NOT EXISTS productquantity (
+  `id` INT UNSIGNED NOT NULL,
+  `quantity` INT UNSIGNED NOT NULL,
+  PRIMARY KEY (`id`),
+  FOREIGN KEY (`id`) REFERENCES products(`id`)
+);
diff --git a/services/cart/routes_test.go b/services/cart/routes_test.go
@@ -203,6 +203,10 @@ func (m *mockProductStore) UpdateProduct(product types.Product) error {
 	return nil
 }
 
+func (m *mockProductStore) UpdateProductQuantity(product types.Product) error {
+	return nil
+}
+
 type mockOrderStore struct{}
 
 func (m *mockOrderStore) CreateOrder(order types.Order) (int, error) {

diff --git a/services/cart/service.go b/services/cart/service.go
@@ -68,7 +68,7 @@ func (h *Handler) createOrder(products []types.Product, cartItems []types.CartCh
 	for _, item := range cartItems {
 		product := productsMap[item.ProductID]
 		product.Quantity -= item.Quantity
-		h.store.UpdateProduct(product)
+		h.store.UpdateProductQuantity(product)
 	}
 
 	// create order record

diff --git a/services/product/routes_test.go b/services/product/routes_test.go
@@ -142,6 +142,10 @@ func (m *mockProductStore) GetProductsByID(ids []int) ([]types.Product, error) {
 	return []types.Product{}, nil
 }
 
+func (m *mockProductStore) UpdateProductQuantity(product types.Product) error {
+	return nil
+}
+
 type mockUserStore struct{}
 
 func (m *mockUserStore) GetUserByID(userID int) (*types.User, error) {

diff --git a/services/product/store.go b/services/product/store.go
@@ -17,7 +17,7 @@ func NewStore(db *sql.DB) *Store {
 }
 
 func (s *Store) GetProductByID(productID int) (*types.Product, error) {
-	rows, err := s.db.Query("SELECT * FROM products WHERE id = ?", productID)
+	rows, err := s.db.Query("SELECT p.id, p.name, p.description, p.image, p.price, pq.quantity, p.createdat FROM products p join productquantity pq on p.id = pq.id WHERE p.id = ?", productID)
 	if err != nil {
 		return nil, err
 	}
@@ -35,7 +35,7 @@ func (s *Store) GetProductByID(productID int) (*types.Product, error) {
 
 func (s *Store) GetProductsByID(productIDs []int) ([]types.Product, error) {
 	placeholders := strings.Repeat(",?", len(productIDs)-1)
-	query := fmt.Sprintf("SELECT * FROM products WHERE id IN (?%s)", placeholders)
+	query := fmt.Sprintf("SELECT p.id, p.name, p.description, p.image, p.price, pq.quantity, p.createdat FROM products p join productquantity pq on p.id = pq.id WHERE p.id IN (?%s)", placeholders)
 
 	// Convert productIDs to []interface{}
 	args := make([]interface{}, len(productIDs))
@@ -63,7 +63,7 @@ func (s *Store) GetProductsByID(productIDs []int) ([]types.Product, error) {
 }
 
 func (s *Store) GetProducts() ([]*types.Product, error) {
-	rows, err := s.db.Query("SELECT * FROM products")
+	rows, err := s.db.Query("SELECT p.id, p.name, p.description, p.image, p.price, pq.quantity, p.createdat FROM products p join productquantity pq on p.id = pq.id")
 	if err != nil {
 		return nil, err
 	}
@@ -82,20 +82,81 @@ func (s *Store) GetProducts() ([]*types.Product, error) {
 }
 
 func (s *Store) CreateProduct(product types.CreateProductPayload) error {
-	_, err := s.db.Exec("INSERT INTO products (name, price, image, description, quantity) VALUES (?, ?, ?, ?, ?)", product.Name, product.Price, product.Image, product.Description, product.Quantity)
+
+	// transaction to add product and quantity together
+	tx, err := s.db.Begin()
 	if err != nil {
 		return err
 	}
 
+	res, err := tx.Exec("INSERT INTO products (name, price, image, description) VALUES (?, ?, ?, ?)", product.Name, product.Price, product.Image, product.Description)
+	if err != nil {
+		tx.Rollback()
+		return err
+	}
+
+	productID, err := res.LastInsertId()
+	if err != nil {
+		tx.Rollback()
+		return err
+	}
+
+	_, err = tx.Exec("INSERT INTO productquantity (id, quantity) VALUES (?, ?)", productID, product.Quantity)
+
+	if err != nil {
+		tx.Rollback()
+		return err
+	}
+
+	if err = tx.Commit(); err != nil {
+		return err
+	}
+
 	return nil
 }
 
 func (s *Store) UpdateProduct(product types.Product) error {
-	_, err := s.db.Exec("UPDATE products SET name = ?, price = ?, image = ?, description = ?, quantity = ? WHERE id = ?", product.Name, product.Price, product.Image, product.Description, product.Quantity, product.ID)
+
+	tx, err := s.db.Begin()
+	if err != nil {
+		return err
+	}
+
+
+	_, err = tx.Exec("UPDATE products SET name = ?, price = ?, image = ?, description = ? WHERE id = ?", product.Name, product.Price, product.Image, product.Description, product.ID)
+	if err != nil {
+		tx.Rollback()
+		return err
+	}
+
+	_, err = tx.Exec("UPDATE productquantity SET quantity = ? WHERE id = ?", product.Quantity, product.ID)
 	if err != nil {
+		tx.Rollback()
 		return err
 	}
 
+	if err = tx.Commit(); err != nil {
+		return err
+	}
+
+	return nil
+}
+
+func (s *Store) UpdateProductQuantity(product types.Product) error {
+	tx, err := s.db.Begin()
+	if err != nil {
+		return err
+	}
+
+	_, err = tx.Exec("UPDATE productquantity SET quantity = ? WHERE id = ?", product.Quantity, product.ID)
+	if err != nil {
+		tx.Rollback()
+		return err
+	}
+
+	if err := tx.Commit(); err != nil {
+		return err
+	}
 	return nil
 }
 

diff --git a/types/types.go b/types/types.go
@@ -60,6 +60,7 @@ type ProductStore interface {
 	GetProducts() ([]*Product, error)
 	CreateProduct(CreateProductPayload) error
 	UpdateProduct(Product) error
+	UpdateProductQuantity(Product) error
 }
 
 type OrderStore interface {