PROJECT CONTROL FILE
======================
Project Codename: PartsLink
Client: KG-Fire (kg-fire.com)
Generated By: Jake 🥷🧑‍💻

------------------------------------------------------------
🧭 PURPOSE
------------------------------------------------------------
A web-based internal system to streamline parts procurement and distribution between field technicians, warehouse staff, and the purchasing department.

System is modular, secure, and distributable to other companies in various trades.

------------------------------------------------------------
📦 DEPLOYMENT STRATEGY
------------------------------------------------------------
- Each company gets a SEPARATE installation (1 DB + 1 codebase)
- Can be hosted as:
   • Subfolder → kg-fire.com/clientA
   • Subdomain → clientA.kg-fire.com
   • Self-hosted by client (optional)

- Local dev with Flask + XAMPP
- Live deployment on Apache server

------------------------------------------------------------
🧱 SYSTEM ARCHITECTURE
------------------------------------------------------------

✅ FRONTEND
- HTML / CSS / JS (Vanilla)
- Bootstrap for styling
- AJAX for backend interaction
- Responsive:
   • Field (Mobile)
   • Warehouse (Tablet)
   • Purchaser (Desktop)
- Optional: Wrap as mobile app later using Capacitor

✅ BACKEND
- Python + Flask
- REST-style API + Jinja rendering
- Flask Blueprints for modularity
- Secure Auth: Email/Password + Google OAuth (future)
- Config managed via .env and DB-stored settings
- LocalStorage/IndexedDB for offline support

✅ DATABASE
- MySQL (via XAMPP locally, MariaDB/MySQL remotely)
- One DB per company (full isolation)
- Tables:
   • users (roles: Field, Warehouse, Purchaser)
   • parts
   • requests
   • orders
   • suppliers
   • warehouses
   • trucks
   • attachments
   • config (name/logo/colors)

✅ FILE STORAGE
- Uploads (photos, receipts, item images)
- Organized per deployment
- File uploads disabled in offline mode

✅ OFFLINE MODE (Field Users)
- IndexedDB / localStorage
- Part list cached
- Requests can be created offline, queued for sync

✅ NOTIFICATIONS
- In-app or toast alerts for pending requests
- Push notifications (future support)

✅ REPORTING
- Cost tracking (supplier-level)
- Inventory reports
- Export/import via Excel/CSV

✅ BRANDING CONFIG
- Editable via Admin UI:
   • Company name
   • Logo
   • Colors

------------------------------------------------------------
🛠️ DEVELOPMENT ENVIRONMENT
------------------------------------------------------------
- OS: Windows 10
- Server: XAMPP (Apache + MySQL)
- IDEs: VS Code, PyCharm
- Editor: Notepad++
- Version Control: Git
- Dependency Manager: Composer (for PHP support, optional)

------------------------------------------------------------
🛡️ SECURITY
------------------------------------------------------------
- Role-based access control (Field, Warehouse, Purchaser)
- Flask-Login / Flask-JWT for sessions
- Passwords hashed with bcrypt
- No open registration – Purchaser pre-registers all users
- Data completely isolated per deployment
- Supplier access: NONE

------------------------------------------------------------
🌍 DEPLOYMENT PATHS
------------------------------------------------------------
• Dev: http://localhost/parts/
• Internal Prod: https://kg-fire.com/parts/
• Client Prod (option 1): https://clientA.kg-fire.com
• Client Prod (option 2): Self-hosted on client's server/domain

------------------------------------------------------------
📌 NEXT STEPS (build paused)
------------------------------------------------------------
[ ] Scaffold Flask project folder + blueprints
[ ] Create MySQL schema
[ ] Build Field Request module (offline-ready)
[ ] Design Admin Config UI
[ ] Build core routes and templates

------------------------------------------------------------
🧑‍💻 CONTROLLED BY: JAKE (Code)

==============================
📦 PartsLink Full Schema Map
==============================

TABLE: users
------------
id                  INT          Primary key
name                VARCHAR(100) Full name of user
email               VARCHAR(100) Unique email for login
password            VARCHAR(255) Hashed login password
role                ENUM         Role: Field, Warehouse, Purchaser
created_at          TIMESTAMP    Timestamp of account creation

TABLE: parts
------------
id                          INT            Primary key
name                        VARCHAR(100)   Part name
sku                         VARCHAR(50)    Unique part code
description                 TEXT           Optional description
image_path                  VARCHAR(255)   File path to item image
reorder_point_warehouse     INT            Low-stock trigger for warehouse
reorder_point_truck         INT            Low-stock trigger for trucks
created_at                  TIMESTAMP      Timestamp part was added

TABLE: suppliers
----------------
id             INT            Primary key
name           VARCHAR(100)   Supplier name
contact_email  VARCHAR(100)   Email for communication
phone          VARCHAR(50)    Contact number
notes          TEXT           Optional notes
created_at     TIMESTAMP      Supplier record creation time

TABLE: part_supplier_costs
--------------------------
id           INT           Primary key
part_id      INT           Foreign key to parts.id
supplier_id  INT           Foreign key to suppliers.id
cost         DECIMAL(10,2) Cost of part from specific supplier

TABLE: warehouses
-----------------
id        INT            Primary key
name      VARCHAR(100)   Warehouse label or name
location  VARCHAR(255)   Physical location or tag

TABLE: trucks
-------------
id           INT            Primary key
name         VARCHAR(100)   Truck label or ID
assigned_to  INT            Foreign key to users.id (driver/user)

TABLE: warehouse_stock
----------------------
id           INT  Primary key
part_id      INT  Foreign key to parts.id
warehouse_id INT  Foreign key to warehouses.id
quantity     INT  Number of parts in stock

TABLE: truck_stock
------------------
id        INT  Primary key
part_id   INT  Foreign key to parts.id
truck_id  INT  Foreign key to trucks.id
quantity  INT  Quantity in that truck

TABLE: requests
---------------
id           INT      Primary key
requested_by INT      FK to users.id (nullable)
part_id      INT      FK to parts.id
quantity     INT      Quantity requested
status       ENUM     'Pending', 'Approved', 'Rejected', 'Fulfilled'
request_note TEXT     Optional note
created_at   TIMESTAMP When request created
updated_at   TIMESTAMP Last update time

TABLE: orders
-------------
id           INT      Primary key
requested_by INT      FK to users.id (nullable)
supplier_id  INT      FK to suppliers.id
part_id      INT      FK to parts.id
quantity     INT      Quantity ordered
status       ENUM     'Requested', 'Ordered', 'Shipped', 'Delivered'
cost         DECIMAL(10,2) Cost per unit
order_note   TEXT     Internal comments
created_at   TIMESTAMP When order created
updated_at   TIMESTAMP Last update time

TABLE: attachments
------------------
id           INT            Primary key
filename     VARCHAR(255)   Original file name
filetype     VARCHAR(50)    Type (e.g. jpg, pdf)
filepath     VARCHAR(255)   Server path to file
related_type ENUM           'request' or 'order'
related_id   INT            ID of request/order
uploaded_by  INT            FK to users.id (nullable)
uploaded_at  TIMESTAMP      Upload time

TABLE: config
-------------
id              INT            Primary key
company_name    VARCHAR(100)   Company display name
logo_path       VARCHAR(255)   Path to logo file
primary_color   VARCHAR(20)    HEX or color name
secondary_color VARCHAR(20)    HEX or color name
created_at      TIMESTAMP      Record creation time

### ✅ Session Log — 2025-03-22

**Phase: Project Planning + Environment Setup + Database Build**

---

**🧠 Strategic Planning Completed:**
- Defined core goal: streamline parts procurement & distribution
- Confirmed 3 user roles: Field, Warehouse, Purchaser
- Scoped out all features including offline mode, role-based access, and notifications
- Selected stack:
  • Backend: Python + Flask
  • Database: MySQL
  • Frontend: HTML/CSS/JS + Bootstrap
  • Environment: XAMPP on Windows 10
- Clarified that the system is intended to be distributed to other companies (standalone, not SaaS)
- Deployment strategy includes subdomain and subfolder support
- Every company will run its own isolated instance with a unique DB

---

**💻 Development Environment Setup:**
- Verified Python 3.10.4 installed
- Installed Flask, flask-mysqldb, python-dotenv via pip
- Created project folder at: `C:\xampp\htdocs\PartsLink`
- Manually scaffolded project structure:
  - `run.py` – Entry point
  - `app/__init__.py` – App factory
  - `app/routes.py` – Main route
  - `.env` – Environment variables
- Flask app successfully launched at: `http://127.0.0.1:5000`
- Verified output: *PartsLink is alive!*

---

**🗃️ MySQL Database Creation:**
- Created new database: `partslink_kgfire`
- Used phpMyAdmin via XAMPP

---

**📊 Database Schema Build:**

✅ Created tables:

1. `users`  
   - Stores all login credentials + user roles

2. `parts`  
   - Master list of parts with SKUs and reorder points

3. `suppliers`  
   - Vendor contact details and notes

4. `part_supplier_costs`  
   - Junction table mapping parts to multiple suppliers with individual costs

5. `warehouses`  
   - Physical stock locations

6. `trucks`  
   - Field vehicles with optional user assignment

7. `warehouse_stock`  
   - Tracks part quantities in warehouses

8. `truck_stock`  
   - Tracks part quantities in trucks

9. `requests`  
   - Field user part requests sent to warehouse  
   - Includes status tracking and notes

10. `orders`  
    - Warehouse-to-purchaser part orders with supplier & cost info

11. `attachments`  
    - Photos, receipts, and file uploads tied to requests/orders

12. `config`  
    - Stores per-company branding settings (logo, name, theme colors)

🛠️ Foreign key errors resolved during build by allowing `NULL` on `requested_by` fields.

---

**📋 Output Requested:**
- Full schema column-purpose breakdown (copied separately)
- Full session log (this entry)

🧾 Session Work Log – 2025-03-22

🧠 Architecture + Fixes:
- Restructured project for modular routing:
  - `app/__init__.py`: Flask app factory with blueprint loading
  - `app/routes/__init__.py`: Added `register_blueprints()` to import and register `config_routes` and `category_routes`
- Fixed Flask import errors caused by out-of-sync `__init__.py` files

🗂️ Category Manager:
- Moved category manager from `config.html` into a full standalone page `category_manager.html`
- Linked from `config.html` and added back button
- Verified full routing and blueprint registration

🌲 UI Enhancements:
- Replaced dropdown parent selector with breadcrumb-style category path
  - Dropdown now shows child categories only
  - Added `⬆ Top Level` reset button
- Backend now sends each category with full breadcrumb `path` (e.g. `Fittings > Elbows > 90°`)
- Frontend dropdown and visual tree are both alphabetically sorted

✅ Files Modified or Created:
- app/__init__.py
- app/routes/__init__.py
- app/routes/category_routes.py
- templates/config.html
- templates/category_manager.html
- static/js/category.js

🧠 Parts Manager Update Log — 2025-03-22

✅ Added Route: GET /parts
  - Returns all parts under a selected category_id.
  - Used by frontend to populate the parts table dynamically.

✅ Added Route: GET /parts/columns
  - Dynamically queries the `parts` table to get column names.
  - Skips system fields like 'id', 'image_path', 'category_id'.
  - Powers the frontend table headers without hardcoding.

✅ Fixed: Missing get_db_connection() in parts_routes.py
  - Ensured MySQL credentials are dynamically loaded from .env

✅ Verified Blueprint Name: parts_bp
  - Registered properly in app/routes/__init__.py

✅ HTML Route: /parts-manager
  - Renders `parts_manager.html` for GUI-based part editing.

⚠️ Note: `parts.js` and HTML currently support dynamic loading,
         but the PUT route for saving edits is not yet implemented.