ecommerce_system.sql

CREATE DATABASE ecommerce_system;
USE ecommerce_system; 
#---------------------------------------------------------------
CREATE USER 'ecommerce_user'@'localhost' IDENTIFIED BY 'Password1234';
GRANT SELECT, INSERT, UPDATE, Delete ON ecommerce_system.* TO 'ecommerce_user'@'localhost';
FLUSH PRIVILEGES;

---------------------------------------------------------------
GRANT CREATE, DROP,DELETE ON ecommerce_system.* TO 'ecommerce_user'@'localhost';
FLUSH PRIVILEGES;

SHOW GRANTS FOR 'ecommerce_user'@'localhost';

DROP TABLE IF EXISTS orders, inventory, pricing_rules, tax_rates, customers, notification_log;
#---------------------------------------------------------------

-- Table: customers (for Customer Service)
CREATE TABLE customers ( 
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL, 
email VARCHAR(100) UNIQUE NOT NULL, 
phone VARCHAR(20), 
loyalty_points INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
#---------------------------------------------------------------
-- Table: inventory (for Inventory Service)
CREATE TABLE inventory ( 
product_id INT PRIMARY KEY AUTO_INCREMENT, 
product_name VARCHAR(100) NOT NULL, 
quantity_available INT NOT NULL, 
unit_price DECIMAL(10,2) NOT NULL, 
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
#---------------------------------------------------------------
-- Table: pricing_rules (for Pricing Service)
CREATE TABLE pricing_rules ( 
rule_id INT PRIMARY KEY AUTO_INCREMENT, 
product_id INT, 
min_quantity INT,
discount_percentage DECIMAL(5,2));
#---------------------------------------------------------------
-- Table: tax_rates (for Pricing Service)
CREATE TABLE tax_rates ( 
region VARCHAR(50) PRIMARY KEY,
tax_rate DECIMAL(5,2));
#---------------------------------------------------------------
-- Table: notification_log (for Notification Service)
CREATE TABLE notification_log ( 
notification_id INT PRIMARY KEY AUTO_INCREMENT, 
order_id INT NOT NULL, 
customer_id INT NOT NULL, 
notification_type VARCHAR(50), 
message TEXT, 
sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

#---------------------------------------------------------------
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    products JSON NOT NULL,
    order_amount DECIMAL(10,2) NOT NULL,
    order_status VARCHAR(50) DEFAULT 'confirmed',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

#---------------------------------------------------------------
-- Sample products
INSERT INTO inventory (product_name, quantity_available, unit_price) VALUES
('Laptop', 50, 999.99),
('Mouse', 200, 29.99),
('Keyboard', 150, 79.99),
('Monitor', 75, 299.99),
('Smartphone', 120, 499.99),
('Tablet', 60, 249.99),
('USB Cable', 500, 9.99),
('External Hard Drive', 90, 89.99),
('Webcam', 130, 59.99),
('Gaming Chair', 30, 199.99),
('Printer', 25, 159.99),
('Headphones', 100, 149.99);

-- Sample customers
INSERT INTO customers (name, email, phone, loyalty_points) VALUES
('Ahmed Hassan', 'ahmed@example.com', '01012345678', 100),
('Sara Mohamed', 'sara@example.com', '01098765432', 250),
('Mona Saeed', 'mona.saeed@example.com', '01123456789', 300),
('Khaled Youssef', 'khaled.youssef@example.com', '01299887766', 120),
('Huda Ibrahim', 'huda.ibrahim@example.com', '01077553322', 40),
('Youssef Adel', 'youssef.adel@example.com', '01566778899', 500),
('Nour El-Din', 'nour.eldin@example.com', '01011223344', 180),
('Omar Ali', 'omar@example.com', '01055686385', 50);

-- Sample pricing rules
INSERT INTO pricing_rules (product_id, min_quantity, discount_percentage) VALUES
(1, 5, 10.00),
(2, 10, 15.00),
(3, 10, 12.00),
(5, 3, 5.00),
(6, 5, 7.50),
(7, 20, 20.00),
(8, 4, 10.00);

-- Sample tax rate (optional but recommended for Pricing Service)
INSERT INTO tax_rates (region, tax_rate) VALUES
('Egypt', 14.00);


-- sample notification
INSERT INTO notification_log (order_id, customer_id, notification_type, message) VALUES
(4, 4, 'Email', 'Your order #4 has been delivered successfully.'),
(5, 5, 'SMS', 'Your order #5 has been cancelled.'),
(6, 6, 'Email', 'Order #6 is pending and awaiting payment.'),
(7, 7, 'Email', 'Your order #7 has been delivered.'),
(8, 8, 'SMS', 'Order #8 is being processed.'),
(9, 3, 'Email', 'Order #9 has been shipped today.'),
(10, 1, 'SMS', 'Order #10 is ready for pickup.'),
(11, 2, 'Email', 'Order #11 is currently being packaged.');