SmartCart — Intelligent E-Commerce Solution 🛒

---

📖 Table of Contents

• Project Overview
• CEP Report (Academic Compliance)
• Database Architecture (ERD & Schema)
• Key Features
• Technology Stack
• Installation & Setup
• API Documentation

---

🚀 Project Overview

SmartCart is a robust, full-stack e-commerce application designed to demonstrate advanced database concepts in a real-world scenario. Unlike typical CRUD apps, SmartCart leverages the full power of the DBMS (MySQL) to ensure data integrity, automate business logic, and provide deep analytics.

It features a GenAI-powered Chatbot (Google Gemini) for customer support, ACID-compliant transactions for order processing, and a normalized schema refined to 3NF.

---

🎓 Academic Compliance

This project strictly adheres to the Complex Engineering Problem (CEP) guidelines for CS-220.

Category	Requirement	Implementation Details
Problem Statement	Real-world DB Application	E-Commerce Platform with Inventory Management & User Roles.
System Analysis	5-8 Functional Reqs	User Auth, Cart Management, Order Processing, Review System, Analytics Dashboard, AI Chatbot.
Conceptual Design	ERD with 6-10 Entities	7 Core Entities: Users, Products, Categories, Orders, OrderItems, Reviews, Logins.
Logical Design	3NF Normalization	Schema is fully normalized. Addresses broken out. Join tables for M:N relationships (order_items).
Physical Implementation	Triggers (2-3)	trg_check_stock (Pre-Order Validation), trg_reduce_stock (Post-Order Inventory Update).
Physical Implementation	Stored Procedures (2-3)	sp_get_recommendations (Content-based filtering), sp_get_user_summary (User stats).
Physical Implementation	Transactions	Full COMMIT/ROLLBACK support during Order Placement to prevent partial updates.
Complex Querying	Joins, Aggregation, Subqueries	Used in Analytics Dashboard (best_sellers view) and Order History retrieval.
Reports	Analytical Reports	1. Best Sellers (Revenue analysis) 2. User Spending Summary

---

🏗 Database Architecture

Entity Relationship Diagram (ERD)

erDiagram
    USERS ||--o{ ORDERS : places
    USERS ||--o{ REVIEWS : writes
    USERS ||--o{ LOGINS : tracks
    CATEGORIES ||--o{ PRODUCTS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : included_in
    PRODUCTS ||--o{ REVIEWS : receives
    ORDERS ||--|{ ORDER_ITEMS : contains

    USERS {
        int id PK
        string role "admin/buyer"
        string email
        string password_hash
    }
    PRODUCTS {
        int id PK
        string name
        decimal price
        int stock
        int category_id FK
    }
    ORDERS {
        int id PK
        int buyer_id FK
        enum status
        decimal total
    }
    ORDER_ITEMS {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
    }

Loading

Advanced SQL Features

1. Triggers

Automated business logic ensures that users cannot order more items than available and that inventory is automatically decremented.

-- Trigger: Check Stock Before Order
CREATE TRIGGER trg_check_stock
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
    DECLARE available_stock INT;
    SELECT stock INTO available_stock FROM products WHERE id = NEW.product_id;
    IF available_stock < NEW.quantity THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock';
    END IF;
END;

2. Stored Procedures

Encapsulated logic for complex data retrieval.

• sp_get_recommendations(user_id): Analyzes purchase history to suggest products from preferred categories.
• sp_get_user_summary(user_id): Aggregates total spend and order count for a specific user.

3. Views

• best_sellers: aggregated view joining 3 tables to report revenue and units sold per product.

---

✨ Key Features

🤖 AI-Powered Support

Integrated Google Gemini API to answer user queries about products, shipping, and store policies in natural language.

🛡️ Secure & Robust

• JWT Authentication (HttpOnly Cookies)
• Bcrypt Password Hashing
• Row-Level Locking (FOR UPDATE) during checkout to preventing race conditions on stock.

📊 Analytics Dashboard

Admins have access to real-time sales data, powered by SQL views and aggregations.

---

💻 Technology Stack

• Backend: Node.js (v18+), Express.js
• Database: MySQL 8.0 (InnoDB Engine)
• Frontend: HTML5, Vanilla JavaScript, TailwindCSS (via CDN)
• Authentication: JSON Web Tokens (JWT)
• AI: Google Generative AI SDK

---

🛠 Installation & Setup

Prerequisites

• Node.js & npm installed.
• MySQL Server running locally.

1. Clone & Install

git clone https://github.com/your-repo/smartcart.git
cd smartcart
npm install

2. Configure Environment

Create a .env file in the root directory:

cp .env.example .env

Update the .env with your credentials:

DB_HOST=localhost
DB_USER=root
DB_PASS=your_password
DB_NAME=smartcart
JWT_SECRET=super_secret_key
GEMINI_API_KEY=your_gemini_api_key

3. Setup Database

Run the schema script to create tables, triggers, and procedures.

# Log into MySQL
mysql -u root -p < sql/schema.sql

Alternatively, copy the contents of sql/schema.sql and run them in MySQL Workbench.

4. Seed Data (Optional)

npm run seed

5. Run Application

npm run dev

Visit http://localhost:3000.

---