last_cart_script

DROP TABLE IF EXISTS Cart;
CREATE TABLE Cart
(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    UserId INT,
    CreatedAt DATETIME,
    StateCart VARCHAR(2),
    TotalAmount DECIMAL(10,2),
    UpdatedAt DATETIME,
    Active BIT
);

DROP TABLE IF EXISTS DetailsCart;
CREATE TABLE DetailsCart
(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    CartId INT,
    ProductId INT,
    Price DECIMAL(10,2),
    Quantity INT,
    Amount DECIMAL(10,2),
    UpdatedAt DATETIME,
    Active BIT
);

DROP PROCEDURE IF EXISTS pa_cart_list;
DELIMITER //
CREATE PROCEDURE pa_cart_list
(
    IN type VARCHAR(10),
    IN userId INT
)
BEGIN
    IF type = 'GET_ALL' THEN
        SELECT * FROM DetailsCart AS DC INNER JOIN Cart AS C ON DC.CartId = C.Id WHERE C.UserId = userId;
    END IF;
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS pa_cart_register;
DELIMITER //
CREATE PROCEDURE pa_cart_register
(
    IN userId INT,
    IN createdAt DATETIME,
    IN stateCart VARCHAR(2),
    IN totalAmount DECIMAL(10,2),
    OUT cartId INT
)
BEGIN
    INSERT INTO Cart (UserId, CreatedAt, StateCart, TotalAmount, UpdatedAt, Active) VALUES (userId, createdAt, stateCart, totalAmount, NOW(), 1);

    SET cartId = LAST_INSERT_ID();
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS pa_cartDetail_register;
DELIMITER //
CREATE PROCEDURE pa_cartDetail_register
(
    IN cartId INT,
    IN productId INT,
    IN price DECIMAL(10,2),
    IN quantity INT,
    IN amount DECIMAL(10, 2)
)
BEGIN
    INSERT INTO DetailsCart (CartId, ProductId, Price, Quantity, UpdatedAt, Active) VALUES (cartId, productId, price, quantity, amount, NOW(), 1);
END//
DELIMITER ;