-- ===================================================================
-- GameStore - Enhanced Professional Database (single SQL file)
-- ===================================================================
-- Use: MySQL 8.x recommended
-- ===================================================================
-- 0) Drop DB if you want to recreate (comment out if not desired)
DROP DATABASE IF EXISTS GameStore;
CREATE DATABASE GameStore;
USE GameStore;
-- ===================================================================
-- 1) Core Tables
-- ===================================================================
-- 1.1 Users (Admins, Managers, Customers)
CREATE TABLE Users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE,
phone VARCHAR(20),
password_hash VARCHAR(255) NOT NULL,
role ENUM('Admin','Manager','Customer') NOT NULL DEFAULT 'Customer',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
CHECK (email LIKE '%@%.%'),
CHECK (phone REGEXP '^[0-9]{0,15}$')
);
-- 1.2 Games / Products
CREATE TABLE Games (
game_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(150) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
genre VARCHAR(50),
platform VARCHAR(50),
image_path VARCHAR(255) DEFAULT 'images/[Link]',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- 1.3 Inventory (one row per game)
CREATE TABLE Inventory (
game_id INT PRIMARY KEY,
stock_quantity INT NOT NULL CHECK (stock_quantity >= 0),
last_updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (game_id) REFERENCES Games(game_id) ON DELETE CASCADE
);
-- 1.4 Cart (user's cart items)
CREATE TABLE Cart (
cart_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
game_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE (user_id, game_id),
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
FOREIGN KEY (game_id) REFERENCES Games(game_id) ON DELETE CASCADE
);
-- 1.5 Addresses (shipping)
CREATE TABLE Addresses (
address_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
label VARCHAR(50), -- e.g., "Home", "Office"
line1 VARCHAR(255) NOT NULL,
city VARCHAR(100) NOT NULL,
state VARCHAR(100),
postal_code VARCHAR(20),
country VARCHAR(100) NOT NULL,
phone VARCHAR(20),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);
-- 1.6 Coupons
CREATE TABLE Coupons (
coupon_id INT AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(30) NOT NULL UNIQUE,
description VARCHAR(255),
discount_percent INT NOT NULL CHECK (discount_percent BETWEEN 1 AND 80),
max_discount_amount DECIMAL(10,2) DEFAULT NULL,
min_order_amount DECIMAL(10,2) DEFAULT 0,
expiry_date DATE NOT NULL,
active BOOLEAN DEFAULT TRUE
);
-- 1.7 Orders
CREATE TABLE Orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
address_id INT NOT NULL,
coupon_id INT DEFAULT NULL,
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount >= 0),
status
ENUM('Pending','Processing','Shipped','Delivered','Cancelled','Refunded') DEFAULT
'Pending',
notes VARCHAR(255),
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (address_id) REFERENCES Addresses(address_id),
FOREIGN KEY (coupon_id) REFERENCES Coupons(coupon_id)
);
-- 1.8 OrderDetails / OrderItems
CREATE TABLE OrderDetails (
order_id INT NOT NULL,
game_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, game_id),
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE,
FOREIGN KEY (game_id) REFERENCES Games(game_id)
);
-- 1.9 Payments (one payment record per order; may be updated)
CREATE TABLE Payments (
payment_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL UNIQUE,
payment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
amount DECIMAL(10,2) NOT NULL CHECK (amount >= 0),
payment_status ENUM('Pending','Success','Failed','Refunded') DEFAULT 'Pending',
payment_method ENUM('Credit Card','Debit Card','PayPal','Bank Transfer','Cash')
DEFAULT 'Credit Card',
transaction_ref VARCHAR(100),
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE
);
-- 1.10 Invoices
CREATE TABLE Invoices (
invoice_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL UNIQUE,
invoice_date DATETIME DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(10,2) NOT NULL,
invoice_path VARCHAR(255) DEFAULT NULL, -- path to generated PDF
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE
);
-- 1.11 Ledger (financial entries)
CREATE TABLE Ledger (
ledger_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
user_id INT,
entry_date DATETIME DEFAULT CURRENT_TIMESTAMP,
amount DECIMAL(10,2) NOT NULL,
entry_type ENUM('Credit','Debit') NOT NULL,
description VARCHAR(255),
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE SET NULL,
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE SET NULL
);
-- 1.12 Reviews
CREATE TABLE Reviews (
review_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
game_id INT NOT NULL,
rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
comment VARCHAR(1000),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
FOREIGN KEY (game_id) REFERENCES Games(game_id) ON DELETE CASCADE
);
-- 1.13 Wishlists
CREATE TABLE Wishlists (
user_id INT NOT NULL,
game_id INT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, game_id),
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
FOREIGN KEY (game_id) REFERENCES Games(game_id) ON DELETE CASCADE
);
-- 1.14 Refunds / Returns
CREATE TABLE Refunds (
refund_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
requested_at DATETIME DEFAULT CURRENT_TIMESTAMP,
amount DECIMAL(10,2) NOT NULL CHECK (amount >= 0),
reason VARCHAR(500),
status ENUM('Pending','Approved','Rejected','Processed') DEFAULT 'Pending',
processed_at DATETIME DEFAULT NULL,
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE
);
-- 1.15 InventoryAlerts
CREATE TABLE InventoryAlerts (
alert_id INT AUTO_INCREMENT PRIMARY KEY,
game_id INT NOT NULL,
stock_quantity INT NOT NULL,
alert_message VARCHAR(255),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (game_id) REFERENCES Games(game_id) ON DELETE CASCADE
);
-- 1.16 AuditLog (general history / change capture)
CREATE TABLE AuditLog (
audit_id INT AUTO_INCREMENT PRIMARY KEY,
entity_name VARCHAR(100) NOT NULL,
entity_pk VARCHAR(255),
action ENUM('INSERT','UPDATE','DELETE') NOT NULL,
changed_by INT,
old_value JSON,
new_value JSON,
changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (changed_by) REFERENCES Users(user_id) ON DELETE SET NULL
);
-- 1.17 ActivityLog (user actions)
CREATE TABLE ActivityLog (
activity_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
action VARCHAR(100) NOT NULL,
table_name VARCHAR(100),
record_id VARCHAR(100),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE SET NULL
);
-- 1.18 EmailLogs
CREATE TABLE EmailLogs (
email_log_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
subject VARCHAR(150),
body TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE SET NULL
);
-- ===================================================================
-- 2) Indexes (performance)
-- ===================================================================
CREATE INDEX idx_users_email ON Users(email);
CREATE INDEX idx_games_title ON Games(title);
CREATE INDEX idx_inventory_stock ON Inventory(stock_quantity);
CREATE INDEX idx_orders_user ON Orders(user_id);
CREATE INDEX idx_orderdetails_game ON OrderDetails(game_id);
CREATE INDEX idx_payments_status ON Payments(payment_status);
-- ===================================================================
-- 3) Views (reports)
-- ===================================================================
CREATE OR REPLACE VIEW vw_OrderSummary AS
SELECT o.order_id, u.user_id, [Link], o.order_date, o.total_amount, [Link],
p.payment_status, p.payment_method
FROM Orders o
LEFT JOIN Users u ON o.user_id = u.user_id
LEFT JOIN Payments p ON o.order_id = p.order_id;
CREATE OR REPLACE VIEW vw_InventoryStatus AS
SELECT g.game_id, [Link], i.stock_quantity, [Link]
FROM Games g
LEFT JOIN Inventory i ON g.game_id = i.game_id;
CREATE OR REPLACE VIEW vw_SalesByGame AS
SELECT g.game_id, [Link], COALESCE(SUM([Link]),0) AS units_sold,
COALESCE(SUM([Link] * od.unit_price),0) AS revenue
FROM Games g
LEFT JOIN OrderDetails od ON g.game_id = od.game_id
GROUP BY g.game_id, [Link];
-- ===================================================================
-- 4) Stored Procedures (utilities & analytics)
-- ===================================================================
DELIMITER $$
-- 4.1 Add sample product
CREATE PROCEDURE AddGame(
IN p_title VARCHAR(150),
IN p_description TEXT,
IN p_price DECIMAL(10,2),
IN p_genre VARCHAR(50),
IN p_platform VARCHAR(50),
IN p_image VARCHAR(255),
IN p_stock INT
)
BEGIN
INSERT INTO Games(title, description, price, genre, platform, image_path)
VALUES (p_title, p_description, p_price, p_genre, p_platform, p_image);
SET @gid = LAST_INSERT_ID();
INSERT INTO Inventory(game_id, stock_quantity) VALUES (@gid, p_stock);
END$$
-- 4.2 Top selling games
CREATE PROCEDURE TopSellingGames(IN limit_rows INT)
BEGIN
SELECT g.game_id, [Link], SUM([Link]) AS units_sold,
SUM([Link]*od.unit_price) AS revenue
FROM OrderDetails od
JOIN Games g ON od.game_id = g.game_id
GROUP BY g.game_id, [Link]
ORDER BY units_sold DESC
LIMIT limit_rows;
END$$
-- 4.3 Revenue in date range
CREATE PROCEDURE RevenueByDateRange(IN start_date DATE, IN end_date DATE)
BEGIN
SELECT DATE(o.order_date) AS day, SUM(o.total_amount) AS revenue
FROM Orders o
WHERE DATE(o.order_date) BETWEEN start_date AND end_date
AND [Link] IN ('Processing','Shipped','Delivered')
GROUP BY DATE(o.order_date)
ORDER BY DATE(o.order_date);
END$$
-- 4.4 Most active customers
CREATE PROCEDURE MostActiveCustomers(IN limit_rows INT)
BEGIN
SELECT u.user_id, [Link], COUNT(o.order_id) AS orders_count,
SUM(o.total_amount) AS total_spent
FROM Orders o
JOIN Users u ON o.user_id = u.user_id
GROUP BY u.user_id, [Link]
ORDER BY total_spent DESC
LIMIT limit_rows;
END$$
DELIMITER ;
-- ===================================================================
-- 5) Triggers (audit, inventory alerts, invoice & ledger automation)
-- ===================================================================
DELIMITER $$
-- 5.1 Generic Audit triggers functionized via per-table triggers
-- Orders: INSERT
CREATE TRIGGER trg_orders_insert
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(entity_name, entity_pk, action, changed_by, new_value)
VALUES('Orders', CONCAT('order_id=', NEW.order_id), 'INSERT', NEW.user_id,
JSON_OBJECT('order_id', NEW.order_id, 'user_id', NEW.user_id,
'total_amount', NEW.total_amount, 'status', [Link]));
INSERT INTO ActivityLog(user_id, action, table_name, record_id)
VALUES (NEW.user_id, 'Created order', 'Orders', NEW.order_id);
END$$
-- Orders: UPDATE
CREATE TRIGGER trg_orders_update
AFTER UPDATE ON Orders
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(entity_name, entity_pk, action, changed_by, old_value,
new_value)
VALUES('Orders', CONCAT('order_id=', OLD.order_id), 'UPDATE', NEW.user_id,
JSON_OBJECT('status', [Link], 'total_amount', OLD.total_amount),
JSON_OBJECT('status', [Link], 'total_amount', NEW.total_amount));
INSERT INTO ActivityLog(user_id, action, table_name, record_id)
VALUES (NEW.user_id, CONCAT('Updated order: ', [Link]), 'Orders',
NEW.order_id);
END$$
-- Orders: DELETE
CREATE TRIGGER trg_orders_delete
BEFORE DELETE ON Orders
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(entity_name, entity_pk, action, changed_by, old_value)
VALUES('Orders', CONCAT('order_id=', OLD.order_id), 'DELETE', OLD.user_id,
JSON_OBJECT('order_id', OLD.order_id, 'total', OLD.total_amount, 'status',
[Link]));
END$$
-- OrderDetails: INSERT/UPDATE/DELETE (audit)
CREATE TRIGGER trg_orderdetails_insert
AFTER INSERT ON OrderDetails
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(entity_name, entity_pk, action, changed_by, new_value)
VALUES('OrderDetails', CONCAT('order=', NEW.order_id, ',game=',
NEW.game_id),'INSERT', NULL,
JSON_OBJECT('order_id', NEW.order_id, 'game_id', NEW.game_id, 'qty',
[Link],'unit_price', NEW.unit_price));
END$$
CREATE TRIGGER trg_orderdetails_update
AFTER UPDATE ON OrderDetails
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(entity_name, entity_pk, action, changed_by, old_value,
new_value)
VALUES('OrderDetails', CONCAT('order=', OLD.order_id, ',game=',
OLD.game_id),'UPDATE', NULL,
JSON_OBJECT('quantity', [Link], 'unit_price', OLD.unit_price),
JSON_OBJECT('quantity', [Link], 'unit_price', NEW.unit_price));
END$$
CREATE TRIGGER trg_orderdetails_delete
BEFORE DELETE ON OrderDetails
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(entity_name, entity_pk, action, changed_by, old_value)
VALUES('OrderDetails', CONCAT('order=', OLD.order_id, ',game=',
OLD.game_id),'DELETE', NULL,
JSON_OBJECT('order_id', OLD.order_id, 'game_id', OLD.game_id, 'qty',
[Link]));
END$$
-- Games: INSERT/UPDATE/DELETE (audit)
CREATE TRIGGER trg_games_insert
AFTER INSERT ON Games
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(entity_name, entity_pk, action, new_value)
VALUES('Games', CONCAT('game_id=', NEW.game_id), 'INSERT',
JSON_OBJECT('title', [Link], 'price', [Link]));
INSERT INTO ActivityLog(user_id, action, table_name, record_id)
VALUES (NULL, CONCAT('Added game: ', [Link]), 'Games', NEW.game_id);
END$$
CREATE TRIGGER trg_games_update
AFTER UPDATE ON Games
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(entity_name, entity_pk, action, old_value, new_value)
VALUES('Games', CONCAT('game_id=', OLD.game_id), 'UPDATE',
JSON_OBJECT('title', [Link], 'price', [Link]),
JSON_OBJECT('title', [Link], 'price', [Link]));
END$$
CREATE TRIGGER trg_games_delete
BEFORE DELETE ON Games
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(entity_name, entity_pk, action, old_value)
VALUES('Games', CONCAT('game_id=', OLD.game_id), 'DELETE',
JSON_OBJECT('title', [Link], 'price', [Link]));
END$$
-- Inventory: PREVENT negative & ALERT low stock & AUDIT
CREATE TRIGGER trg_inventory_prevent_negative
BEFORE UPDATE ON Inventory
FOR EACH ROW
BEGIN
IF NEW.stock_quantity < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Inventory stock cannot be
negative';
END IF;
END$$
CREATE TRIGGER trg_inventory_update_audit
AFTER UPDATE ON Inventory
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(entity_name, entity_pk, action, old_value, new_value)
VALUES('Inventory', CONCAT('game_id=', OLD.game_id), 'UPDATE',
JSON_OBJECT('stock', OLD.stock_quantity),
JSON_OBJECT('stock', NEW.stock_quantity));
-- low stock alert threshold (example: 5)
IF NEW.stock_quantity <= 5 THEN
INSERT INTO InventoryAlerts(game_id, stock_quantity, alert_message)
VALUES (NEW.game_id, NEW.stock_quantity, CONCAT('Low stock for game_id=',
NEW.game_id, '. Only ', NEW.stock_quantity, ' left.'));
END IF;
END$$
-- Payments: when payment becomes success → create Invoice (if not exist) & ledger
entry & update order status to Processing or Shipped
CREATE TRIGGER trg_payments_after_update
AFTER UPDATE ON Payments
FOR EACH ROW
BEGIN
IF OLD.payment_status <> NEW.payment_status THEN
INSERT INTO AuditLog(entity_name, entity_pk, action, old_value, new_value)
VALUES('Payments', CONCAT('payment_id=', NEW.payment_id), 'UPDATE',
JSON_OBJECT('status', OLD.payment_status), JSON_OBJECT('status',
NEW.payment_status));
-- If payment success -> create invoice and ledger and mark order processing
IF NEW.payment_status = 'Success' THEN
-- Invoice (only if not exists)
INSERT IGNORE INTO Invoices(order_id, invoice_date, total_amount)
SELECT o.order_id, NOW(), [Link] FROM Orders o WHERE o.order_id =
NEW.order_id;
-- Ledger entry
INSERT INTO Ledger(order_id, user_id, amount, entry_type, description)
SELECT NEW.order_id, o.user_id, [Link], 'Credit', CONCAT('Payment
received for order ', NEW.order_id)
FROM Orders o WHERE o.order_id = NEW.order_id;
-- Update order status if pending
UPDATE Orders SET status = 'Processing' WHERE order_id = NEW.order_id AND
status = 'Pending';
-- Email log
INSERT INTO EmailLogs(user_id, subject, body)
SELECT user_id, CONCAT('Payment Received - Order ', NEW.order_id),
CONCAT('Your payment of ', [Link], ' was successful for order ',
NEW.order_id)
FROM Orders WHERE order_id = NEW.order_id;
-- Activity log
INSERT INTO ActivityLog(user_id, action, table_name, record_id)
SELECT user_id, CONCAT('Payment received: ', NEW.payment_id), 'Payments',
NEW.payment_id FROM Orders WHERE order_id = NEW.order_id;
END IF;
-- If refunded -> create ledger debit and set order to Refunded
IF NEW.payment_status = 'Refunded' THEN
INSERT INTO Ledger(order_id, user_id, amount, entry_type, description)
SELECT NEW.order_id, o.user_id, [Link], 'Debit', CONCAT('Refund for order
', NEW.order_id)
FROM Orders o WHERE o.order_id = NEW.order_id;
UPDATE Orders SET status = 'Refunded' WHERE order_id = NEW.order_id;
END IF;
END IF;
END$$
-- OrderDetails: after insert -> reduce inventory (with check)
CREATE TRIGGER trg_orderdetails_after_insert
AFTER INSERT ON OrderDetails
FOR EACH ROW
BEGIN
-- Check stock and update (Inventory trigger prevents negative)
UPDATE Inventory
SET stock_quantity = stock_quantity - [Link]
WHERE game_id = NEW.game_id;
-- Activity log
INSERT INTO ActivityLog(user_id, action, table_name, record_id)
VALUES (NULL, CONCAT('OrderDetail added for order=', NEW.order_id, ', game=',
NEW.game_id), 'OrderDetails', CONCAT(NEW.order_id, '-', NEW.game_id));
END$$
-- Refunds: audit on creation
CREATE TRIGGER trg_refunds_insert
AFTER INSERT ON Refunds
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(entity_name, entity_pk, action, new_value)
VALUES('Refunds', CONCAT('refund_id=', NEW.refund_id), 'INSERT',
JSON_OBJECT('order_id', NEW.order_id, 'amount', [Link], 'reason', [Link]));
INSERT INTO ActivityLog(user_id, action, table_name, record_id)
VALUES (NULL, CONCAT('Refund requested for order=', NEW.order_id), 'Refunds',
NEW.refund_id);
END$$
DELIMITER ;
-- ===================================================================
-- 6) Transactional Checkout Procedure (full workflow)
-- - Creates Order from Cart
-- - Applies coupon (if valid)
-- - Validates inventory, inserts OrderDetails
-- - Creates Payment placeholder (Pending)
-- - Creates Invoice & Ledger entries when payment updates to Success (handled
by trigger)
-- - Clears user's cart
-- - All inside a transaction (atomic)
-- ===================================================================
DELIMITER $$
DROP PROCEDURE IF EXISTS CheckoutUserCart$$
CREATE PROCEDURE CheckoutUserCart(
IN p_user_id INT,
IN p_address_id INT,
IN p_coupon_code VARCHAR(30) -- pass NULL if no coupon
)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE cur_game_id INT;
DECLARE cur_qty INT;
DECLARE cur_price DECIMAL(10,2);
DECLARE v_total DECIMAL(10,2) DEFAULT 0.00;
DECLARE v_coupon_id INT DEFAULT NULL;
DECLARE v_discount DECIMAL(10,2) DEFAULT 0.00;
DECLARE v_final_total DECIMAL(10,2) DEFAULT 0.00;
DECLARE v_order_id INT;
-- Start transaction
START TRANSACTION;
-- Calculate cart total and ensure cart not empty
SELECT SUM([Link] * [Link]) INTO v_total
FROM Cart c JOIN Games g ON c.game_id = g.game_id
WHERE c.user_id = p_user_id;
IF v_total IS NULL OR v_total <= 0 THEN
ROLLBACK;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cart is empty or total <= 0';
END IF;
-- Validate address ownership
IF NOT EXISTS (SELECT 1 FROM Addresses WHERE address_id = p_address_id AND
user_id = p_user_id) THEN
ROLLBACK;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid address selected';
END IF;
-- Validate coupon if provided
IF p_coupon_code IS NOT NULL THEN
SELECT coupon_id, discount_percent, max_discount_amount, min_order_amount,
expiry_date, active
INTO v_coupon_id, @dpercent, @dmax, @minamt, @expiry, @active_flag
FROM Coupons
WHERE code = p_coupon_code
LIMIT 1;
IF v_coupon_id IS NULL OR @active_flag = 0 OR @expiry < CURDATE() OR v_total <
@minamt THEN
SET v_coupon_id = NULL;
SET v_discount = 0;
ELSE
SET v_discount = (v_total * (@dpercent / 100));
IF @dmax IS NOT NULL AND v_discount > @dmax THEN
SET v_discount = @dmax;
END IF;
END IF;
END IF;
SET v_final_total = v_total - v_discount;
IF v_final_total < 0 THEN SET v_final_total = 0; END IF;
-- Create order
INSERT INTO Orders(user_id, address_id, coupon_id, total_amount, status)
VALUES (p_user_id, p_address_id, v_coupon_id, v_final_total, 'Pending');
SET v_order_id = LAST_INSERT_ID();
-- Insert order details from cart; also check inventory for each item BEFORE
insert
-- Use cursor-style iteration via temporary table for simplicity
-- Create temp table of cart snapshot
CREATE TEMPORARY TABLE tmp_cart AS
SELECT c.game_id, [Link], [Link]
FROM Cart c JOIN Games g ON c.game_id = g.game_id
WHERE c.user_id = p_user_id
FOR UPDATE;
-- Check inventory for each item
DECLARE cur CURSOR FOR SELECT game_id, quantity, price FROM tmp_cart;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO cur_game_id, cur_qty, cur_price;
IF done = 1 THEN
LEAVE read_loop;
END IF;
-- Check stock
IF (SELECT stock_quantity FROM Inventory WHERE game_id = cur_game_id) < cur_qty
THEN
CLOSE cur;
DROP TEMPORARY TABLE IF EXISTS tmp_cart;
ROLLBACK;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT('Not enough stock for
game_id=', cur_game_id);
END IF;
-- Insert order detail
INSERT INTO OrderDetails(order_id, game_id, quantity, unit_price)
VALUES (v_order_id, cur_game_id, cur_qty, cur_price);
END LOOP;
CLOSE cur;
DROP TEMPORARY TABLE IF EXISTS tmp_cart;
-- Create Payment placeholder (Pending)
INSERT INTO Payments(order_id, amount, payment_status, payment_method)
VALUES (v_order_id, v_final_total, 'Pending', 'Credit Card');
-- Create Invoice placeholder (will be finalized after payment success)
INSERT INTO Invoices(order_id, total_amount)
VALUES (v_order_id, v_final_total);
-- Create ledger entry (debit to stock/sales?) For simplicity, add credit for
order (will be confirmed on payment)
INSERT INTO Ledger(order_id, user_id, amount, entry_type, description)
VALUES (v_order_id, p_user_id, v_final_total, 'Credit', CONCAT('Order created: ',
v_order_id));
-- Clear user's cart
DELETE FROM Cart WHERE user_id = p_user_id;
-- Activity log
INSERT INTO ActivityLog(user_id, action, table_name, record_id)
VALUES (p_user_id, CONCAT('Checkout created order ', v_order_id), 'Orders',
v_order_id);
COMMIT;
END$$
DELIMITER ;
-- ===================================================================
-- 7) Example helper stored procedures for admin tasks
-- ===================================================================
DELIMITER $$
-- Restock a game (admin)
CREATE PROCEDURE RestockGame(IN p_game_id INT, IN p_quantity INT, IN p_admin_id
INT)
BEGIN
IF p_quantity <= 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Restock quantity must be > 0';
END IF;
INSERT INTO ActivityLog(user_id, action, table_name, record_id)
VALUES (p_admin_id, CONCAT('Restocked game ', p_game_id, ' by ', p_quantity),
'Inventory', p_game_id);
UPDATE Inventory SET stock_quantity = stock_quantity + p_quantity WHERE game_id =
p_game_id;
END$$
DELIMITER ;
-- ===================================================================
-- 8) Seed some sample data (optional but helpful)
-- ===================================================================
-- Add sample admin & users
INSERT INTO Users(username, full_name, email, phone, password_hash, role)
VALUES
('admin', 'Admin User', 'admin@[Link]', '0000000000', 'HASHED_PASSWORD',
'Admin'),
('manager', 'Manager User', 'manager@[Link]', '0000000001',
'HASHED_PASSWORD', 'Manager'),
('alice', 'Alice Customer', 'alice@[Link]', '03001234567', 'HASHED_PASSWORD',
'Customer'),
('bob', 'Bob Customer', 'bob@[Link]', '03007654321', 'HASHED_PASSWORD',
'Customer');
-- Add sample games
INSERT INTO Games(title, description, price, genre, platform, image_path)
VALUES
('Alan Wake 2', 'Psychological horror adventure', 59.99, 'Horror', 'PC',
'images/[Link]'),
('Minecraft', 'Sandbox building game', 19.99, 'Sandbox', 'Multiplatform',
'images/[Link]'),
('Ratchet & Clank', 'Platform shooter', 34.89, 'Platformer', 'Console',
'images/[Link]');
-- Inventory initial quantities
INSERT INTO Inventory(game_id, stock_quantity) SELECT game_id, FLOOR(RAND()*50)+5
FROM Games;
-- Add sample addresses
INSERT INTO Addresses(user_id, label, line1, city, country, phone) VALUES
(3, 'Home', '123 Main St', 'Lahore', 'Pakistan', '03001234567'),
(4, 'Home', '555 Market St', 'Karachi', 'Pakistan', '03007654321');
-- Add a coupon
INSERT INTO Coupons(code, description, discount_percent, max_discount_amount,
min_order_amount, expiry_date, active)
VALUES ('WELCOME10','10% off for new users',10,50,0,DATE_ADD(CURDATE(), INTERVAL 30
DAY), TRUE);
-- ===================================================================
-- 9) Useful sample queries (as views or SPs already provided)
-- ===================================================================
-- Use views: SELECT * FROM vw_OrderSummary;
-- Use SPs: CALL TopSellingGames(5);
-- ===================================================================
-- 10) Notes to run safely
-- ===================================================================
-- - Make sure you run on MySQL 8.0+ (JSON, ENUM, CHECK improvements).
-- - Use prepared statements with application layer when dealing with user inputs.
-- - Passwords shown as 'HASHED_PASSWORD' must be real salted hashes in your app
(not plain text).
-- - Frontend validation must still be implemented to avoid unnecessary DB errors
(see checklist in your docs).
-- ===================================================================
-- 11) End of file
-- ===================================================================