# 🚗 Apex Mechanical Workshop Spare Parts Management System

A complete, modern, responsive, and functional full-stack web application designed for automobile workshops and garages to manage **spare parts inventory, stock tracking, suppliers, inward procurement, customer sales billing, printable tax invoices, and financial profit reporting**.

---

## 🌟 Key Highlights & Features

### 1. Modern Mechanical Workshop Dashboard
- Real-time inventory and financial metric cards:
  - **Total Spare Parts**: Active part lines in catalog.
  - **Total Stock Quantity**: Physical units present in storage racks.
  - **Total Stock Value**: Live valuation of inventory at purchase cost.
  - **Low Stock Items**: Instant count of parts needing replenishment.
  - **Today's Sales**: Total counter sales revenue generated today.
  - **Total Suppliers**: Registered automobile parts vendors.
  - **Total Purchases**: Procurement order counts and expenditures.
- **Interactive Quick-Glance Sections**:
  - Recent Sales ledger.
  - Low Stock Warning alerts with reorder levels.
  - Real-time stock audit transaction history.
  - Top 5 selling spare parts by volume and revenue.
  - Last 6-month monthly sales vs purchases performance summary.

### 2. Spare Parts Catalog Management
- Fields tracked: `Part ID`, `Part Code` (unique, e.g., `ENG-PST-01`), `Part Name`, `Category`, `Description`, `Brand/Manufacturer`, `Vehicle Model Fitment Compatibility`, `Current Quantity`, `Reorder Level`, `Purchase Price`, `Selling Price`, `Supplier`, `Date Added`.
- Full CRUD (Create, Read, Update, Delete) with reference integrity.
- Real-time search across part name, OEM code, brand, and vehicle model compatibility.
- Category filter and one-click "Low Stock Only" filter.
- Multi-criteria sorting (A-Z, stock low-to-high, high-to-low, price).
- Stock status badges: `In Stock`, `Low Stock`, `Out of Stock`.
- Comprehensive **Part Dossier Modal** showcasing full specs and recent movement log.

### 3. Categories Management
- Pre-seeded with authentic automotive categories:
  - Engine Parts, Brake Parts, Electrical Parts, Suspension Parts, Transmission Parts, Clutch Parts, Filters, Lubricants, Tyres & Accessories.
- Add, Edit, and Delete categories with referential safeguards (prevents deletion if parts are assigned).

### 4. Supplier & Vendor Management
- Fields: `Supplier ID`, `Supplier Name`, `Contact Person`, `Mobile Number`, `Email`, `Office/Warehouse Address`, `GST Number`, `Status`.
- Fast search by company name, contact, mobile, or GSTIN.
- **Purchase History Tracker**: View all past purchase orders placed with any vendor.

### 5. Inward Purchase Management (Stock Replenishment)
- Fields: `Purchase Invoice No`, `Supplier`, `Purchase Date`, `Line Items (Parts, Qty, Unit Price)`, `GST %`, `Total Amount`, `Payment Status` (Paid/Pending/Partial), `Payment Method` (UPI, Cash, Cheque, Card), `Notes`.
- **Automatic Stock Increase**: As soon as a purchase order is saved, inventory quantities automatically increment in MySQL.
- **ACID SQL Transactions**: Uses row locks and rollback protection to ensure data consistency.
- Next purchase invoice number auto-generation.

### 6. Counter Sales & Tax Invoicing (Billing Module)
- Fields: `Invoice No`, `Customer Name`, `Customer Mobile`, `Sale Date`, `Line Items`, `Subtotal`, `Discount`, `GST % (18%)`, `Grand Total`, `Payment Mode`, `Status`.
- **Real-Time Stock Check**: Prohibits sales if requested quantity exceeds current available stock.
- **Automatic Stock Deduction**: Subtracts sold quantities and logs outward movements.
- **Canceling an invoice** automatically restores parts back to warehouse stock.

### 7. Professional Printable Workshop Invoice
- Fully formatted tax invoice with:
  - Workshop branding: **Apex Motors Workshop**, contact info, and GSTIN (`27AABCM9876Q1ZB`).
  - Customer contact details & job card reference.
  - Itemized table with quantities, rates, and line totals.
  - Calculation breakdown: Subtotal, Discount, CGST/SGST, and Grand Total.
  - Standard warranty and terms & conditions.
  - **Print Invoice** (`Ctrl+P` or one-click button) with dedicated `@media print` clean paper styling.
  - **Download PDF** option.

### 8. Stock Movement & Inventory Audit Trail
- Supported transaction types:
  - `Stock In` (+ Inward parts)
  - `Stock Out` (- Workshop floor dispatch)
  - `Stock Adjustment` (Count reconciliation)
  - `Return Stock` (+ Customer / vendor returns)
  - `Damage Stock` (- Defective or scrapped units)
- Complete historical audit log showing: Date & Time, Part Code, Movement Type, Quantity Delta, Previous Stock, Resulting Stock, Reference Doc No, and Reason Notes.
- **Strict Negative Stock Prevention**: The system will never allow stock to drop below 0.

### 9. Management Reports & Financial Profit Analysis
- **Sales Reports**: Daily, Monthly, and Yearly revenue breakdowns.
- **Gross Profit & Margin Analysis**: Evaluates `(Selling Price - Purchase Cost) * Quantity Sold` for every spare part to report true gross profit and margin percentages.
- **Stock Valuation Report**: Total cost value in warehouse vs potential retail value.
- **Purchases Expenditure Report**: Spending breakdown across vendors.
- **Supplier Performance Report**: Order volumes and value per vendor.
- **One-Click CSV Export**: Download live report datasets as `.csv` spreadsheets.

### 10. Security & Robust Backend Architecture
- **JWT Authentication** (`jsonwebtoken`) with 24-hour expiration.
- **Password Hashing** via `bcryptjs` (salt rounds: 10).
- **Parameterized SQL Queries**: 100% immune to SQL Injection attacks.
- Protected REST API endpoints guarded by authorization middleware.
- Self-healing database initializer: checks MySQL connection and auto-seeds default admin if not present.

---

## 🛠️ Technology Stack

| Layer | Technologies Used |
| :--- | :--- |
| **Frontend** | Vanilla HTML5, Modern CSS3 (Navy & White palette), ES6+ JavaScript |
| **Icons & Fonts** | Font Awesome 6.5.1 CDN |
| **Backend** | Node.js & Express.js (REST API architecture) |
| **Database** | MySQL 5.7+ / MySQL 8.0+ / MariaDB |
| **Database Driver** | `mysql2/promise` (Connection pool with transaction support) |
| **Security** | `bcryptjs` for salted hashing, `jsonwebtoken` (JWT) for stateless bearer tokens |

---

## 📁 Project Directory Structure

```text
mechanical-workshop-management/
├── config/
│   └── database.js          # MySQL connection pool & auto-seed check
├── database/
│   └── database.sql         # Normalized schema + complete sample seed dataset
├── middleware/
│   └── auth.js              # JWT authentication & role-based middleware
├── public/
│   ├── css/
│   │   └── style.css        # Navy blue & white dashboard UI, print stylesheet
│   ├── js/
│   │   └── app.js           # Single-page application engine, API handlers, DOM UI
│   ├── index.html           # Main dashboard & management tabs
│   └── login.html           # Admin authentication page
├── routes/
│   ├── auth.js              # Login, token session check, logout
│   ├── categories.js        # Category CRUD endpoints
│   ├── dashboard.js         # Real-time dashboard KPI cards & summaries
│   ├── parts.js             # Spare parts inventory CRUD & search
│   ├── purchases.js         # Purchase orders & stock increment transactions
│   ├── reports.js           # Sales, profit, stock valuation & CSV export
│   ├── sales.js             # Customer sales billing & stock deduction
│   ├── stock.js             # Manual stock adjustments & audit logs
│   └── suppliers.js         # Supplier registry & purchase history
├── .env                     # Local environment settings
├── .env.example             # Example environment template
├── package.json             # NPM dependencies & scripts
├── README.md                # Documentation & installation guide
└── server.js                # Express server entry point & static hosting
```

---

## 🚀 Quick Setup & Installation Guide

### Prerequisites
1. **Node.js** (v16.x or newer): [Download Node.js](https://nodejs.org/)
2. **MySQL Server** (via [XAMPP](https://www.apachefriends.org/), WampServer, Docker, or MySQL Workbench).

---

### Step 1: Clone or Navigate to the Project Folder
```bash
cd "C:\Users\Radha Krishna\.gemini\antigravity\scratch\mechanical-workshop-management"
```

---

### Step 2: Install Node.js Dependencies
```bash
npm install
```

---

### Step 3: Setup the MySQL Database

#### Option A: Using phpMyAdmin (XAMPP / WAMP)
1. Start **Apache** and **MySQL** in your XAMPP Control Panel.
2. Open your browser and navigate to `http://localhost/phpmyadmin`.
3. Click on the **Import** tab at the top.
4. Click **Choose File** and select `database/database.sql` from this project folder.
5. Click **Go** (bottom right).
   - This automatically creates the `workshop_db` database and seeds all categories, suppliers, spare parts, and sample transactions.

#### Option B: Using MySQL Command Line
```bash
mysql -u root -p < database/database.sql
```
*(If your root user has no password, simply press Enter when prompted)*.

---

### Step 4: Configure Environment Variables
Verify your `.env` file matches your local MySQL settings:
```ini
PORT=3000
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=
DB_NAME=workshop_db
DB_PORT=3306
JWT_SECRET=supersecretworkshopkey2026_jwt_token_auth
JWT_EXPIRES_IN=24h
```
*(If your MySQL `root` has a password, enter it in `DB_PASSWORD`)*.

---

### Step 5: Start the Application Server

#### Production Mode:
```bash
npm start
```
#### Development Mode (Auto-reload on file edits):
```bash
npm run dev
```

You should see output similar to:
```text
====================================================
  MECHANICAL WORKSHOP SPARE PARTS MANAGEMENT SYSTEM  
====================================================
✅ Connected to MySQL database: workshop_db
🚀 Server running on: http://localhost:3000
🔑 Login Page:       http://localhost:3000/login.html
📊 Dashboard UI:     http://localhost:3000/index.html
🩺 Health check:     http://localhost:3000/api/health
====================================================
```

---

## 🔑 Default Admin Demo Credentials

| Attribute | Value |
| :--- | :--- |
| **Login URL** | `http://localhost:3000/login.html` |
| **Email Address** | `admin@workshop.local` |
| **Password** | `Admin@123` |
| **Role** | `admin` |

*(Tip: On the login page, you can click the **"Auto Fill"** button in the credentials card for one-click testing).*

---

## 📊 Database Schema Details (`workshop_db`)

The database is normalized to 3rd Normal Form (3NF) with foreign keys and cascading rules:

1. **`users`**: Administrator and workshop technician accounts with bcrypt hashes.
2. **`categories`**: Spare parts categories (Engine, Brakes, Suspension, etc.).
3. **`suppliers`**: Auto parts manufacturers and wholesale vendors with GST numbers.
4. **`parts`**: Spare parts catalog with prices, stock levels, and OEM compatibility.
5. **`purchases`**: Inward purchase invoice headers with supplier references.
6. **`purchase_items`**: Line items for inward purchases.
7. **`sales`**: Customer counter sales invoice headers with payment records.
8. **`sale_items`**: Line items for sales invoices.
9. **`stock_transactions`**: Complete audit trail of all physical quantity changes.

---

## 🌐 REST API Endpoints Reference

### Authentication (`/api/auth`)
- `POST /api/auth/login` - Authenticate admin & receive JWT.
- `GET  /api/auth/me` - Validate current token session.
- `POST /api/auth/logout` - Sign out.

### Dashboard Statistics (`/api/dashboard`)
- `GET /api/dashboard/stats` - Total parts, stock count, stock value, today's sales, etc.
- `GET /api/dashboard/recent-sales` - 6 latest counter invoices.
- `GET /api/dashboard/recent-transactions` - 8 latest stock audit entries.
- `GET /api/dashboard/low-stock-alerts` - Items requiring immediate reorder.
- `GET /api/dashboard/top-selling` - Best-performing spare parts by volume and revenue.
- `GET /api/dashboard/monthly-summary` - 6-month monthly revenue & purchase overview.

### Spare Parts (`/api/parts`)
- `GET    /api/parts` - List parts (supports `?search=`, `?category_id=`, `?low_stock=true`, `?sort_by=`).
- `GET    /api/parts/:id` - Full part dossier with recent stock transactions.
- `POST   /api/parts` - Add new spare part.
- `PUT    /api/parts/:id` - Update existing part.
- `DELETE /api/parts/:id` - Remove spare part (safe reference validation).

### Categories (`/api/categories`)
- `GET    /api/categories` - List categories with associated part counts.
- `POST   /api/categories` - Create new category.
- `PUT    /api/categories/:id` - Update category.
- `DELETE /api/categories/:id` - Delete category (protected if parts exist).

### Suppliers (`/api/suppliers`)
- `GET    /api/suppliers` - List vendors with purchase counts.
- `GET    /api/suppliers/:id/purchases` - View complete purchase history for vendor.
- `POST   /api/suppliers` - Register new supplier.
- `PUT    /api/suppliers/:id` - Edit supplier.
- `DELETE /api/suppliers/:id` - Remove supplier.

### Purchases (`/api/purchases`)
- `GET    /api/purchases` - List inward purchase invoices.
- `GET    /api/purchases/:id` - Detailed purchase invoice with item lines.
- `GET    /api/purchases/next-invoice-no` - Generates next invoice number.
- `POST   /api/purchases` - Records purchase & automatically **increments stock**.
- `DELETE /api/purchases/:id` - Cancels purchase & reverses stock safely.

### Sales & Billing (`/api/sales`)
- `GET    /api/sales` - List customer sales invoices.
- `GET    /api/sales/:id` - Retrieve full invoice for printable bill rendering.
- `GET    /api/sales/next-invoice-no` - Generates next sales invoice number.
- `POST   /api/sales` - Records sale, **validates sufficient stock**, & **deducts inventory**.
- `DELETE /api/sales/:id` - Cancels sale & returns parts back to stock.

### Stock Movement (`/api/stock`)
- `GET  /api/stock/overview` - Inventory valuation by category.
- `GET  /api/stock/transactions` - Filterable audit log of inventory movements.
- `POST /api/stock/adjust` - Manual Stock In, Stock Out, Adjustment, Return, Damage.

### Reports (`/api/reports`)
- `GET /api/reports/sales` - Daily, monthly, yearly sales report.
- `GET /api/reports/profit` - Gross profit and margin analysis per spare part.
- `GET /api/reports/stock` - Current inventory cost and retail valuation.
- `GET /api/reports/purchases` - Inward procurement report.
- `GET /api/reports/suppliers` - Vendor performance report.
- `GET /api/reports/export/csv` - Export reports to downloadable CSV.

---

## 💾 Database Backup & Recovery

### Creating a Backup
To export a full database backup with schema and data:
```bash
mysqldump -u root -p workshop_db > workshop_db_backup.sql
```

### Restoring from Backup
To restore the backup file:
```bash
mysql -u root -p workshop_db < workshop_db_backup.sql
```

---

## 🎓 College Project Demonstration Guide

When presenting this project for an engineering evaluation or viva:
1. **Login Flow**: Open `http://localhost:3000/login.html`, click "Auto Fill" to populate `admin@workshop.local` / `Admin@123`, and explain how **bcrypt** verifies the salted hash and returns an authenticated **JWT token**.
2. **Dashboard Overview**: Show real-time KPI cards computed from live MySQL queries. Highlight the Low Stock alert badge.
3. **Spare Parts Module**: Show search by keyword ("Brake"), filtering by Category, and clicking the view icon to examine the part's full movement history.
4. **Stock Inward (Purchases)**: Record a new inward purchase for a part (e.g. 10 units of Brake Pads). Point out that the inventory immediately increases by 10 in the parts table and logs a `Purchase` record in `stock_transactions`.
5. **Counter Sales Billing**: Create a customer sales invoice. Attempt to sell more units than are in stock to show the **automatic stock-out prevention rule**. Then sell 2 units and watch the stock decrement.
6. **Printable Tax Invoice**: Open the generated invoice in the modal, click **Print Invoice**, and demonstrate the clean print stylesheet with workshop header, GSTIN, and signatures.
7. **Profit & Analytics Report**: Switch to the Reports tab, select "Gross Profit & Margin Analysis", and show how the system calculates true gross profit (`Selling Price - Purchase Cost`) for each part sold.
8. **Export Data**: Click **Export to CSV** to show instant download of spreadsheet data.
