sql.sql

-- Create Tables
CREATE TABLE customers ( customer_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR(150) NOT NULL, email VARCHAR(150), phone VARCHAR(50), address VARCHAR(250) );
CREATE TABLE products ( product_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(150) NOT NULL, description TEXT, price NUMERIC(10, 2), weight NUMERIC(10, 2) );
CREATE TABLE shipments ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, departure DATE NOT NULL, status_value VARCHAR(50), customer_id INTEGER REFERENCES customers(customer_id) );
CREATE TABLE shipment_items ( item_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, shipment_id INTEGER REFERENCES shipments(id), product_id INTEGER REFERENCES products(product_id), quantity INTEGER NOT NULL, weight NUMERIC(10, 2) );

-- Add Data
INSERT INTO customers (customer_name, email, phone, address) VALUES ('Alice Johnson', 'alice@example.com', '555-1234', '123 Maple St, New York, NY'), ('Bob Smith', 'bob@example.com', '555-5678', '456 Oak Ave, Los Angeles, CA'), ('Cathy Lee', 'cathy@example.com', '555-8765', '789 Pine Rd, Chicago, IL');
INSERT INTO products (product_name, description, price, weight) VALUES ('Laptop', '15-inch screen, 8GB RAM, 256GB SSD', 1200.00, 2.5), ('Smartphone', '128GB storage, 6GB RAM', 800.00, 0.4), ('Headphones', 'Noise-cancelling, wireless', 150.00, 0.3);
INSERT INTO shipments (departure, status_value, customer_id) VALUES ('2024-11-10', 'In Transit', 1), ('2024-11-11', 'Delivered', 2), ('2024-11-12', 'In Transit', 3);
INSERT INTO shipment_items (shipment_id, product_id, quantity, weight) VALUES (1, 1, 2, 5.0), (1, 3, 1, 0.3), (2, 2, 1, 0.4), (3, 1, 1, 2.5), (3, 3, 2, 0.6);

-- Create Store Procedures add_customer and send_shipment
DELIMITER //
CREATE OR REPLACE PROCEDURE add_customer(
    IN p_name VARCHAR(150),
    IN p_email VARCHAR(150),
    IN p_phone VARCHAR(50),
    IN p_address VARCHAR(250)
)
LANGUAGE SQL
BEGIN
INSERT INTO customers (customer_name, email, phone, address)
VALUES (p_name, p_email, p_phone, p_address);
END;
//

DELIMITER //
CREATE OR REPLACE PROCEDURE send_shipment(
    IN customer_id INTEGER,
    IN origin_id INTEGER,
    IN destination_id INTEGER,
    IN departure DATE,
    IN items JSON,
    IN status_value VARCHAR(50),
    IN location_id INTEGER
)
LANGUAGE SQL
BEGIN
	 DECLARE shipment_id INTEGER;
	 DECLARE item JSON;

INSERT INTO shipments (customer_id, origin_id, destination_id, departure, status_value)
VALUES (customer_id, origin_id, destination_id, departure, status_value);
SELECT LAST_INSERT_ID() into shipment_id;
END;
//