# 📊 Product History Tracking Feature

## Overview
The Product History feature provides a complete audit trail of all product changes, specifically tracking:
- **Product Creation** - When a product was created and initial pricing
- **Price Changes** - All price-per-crate modifications
- **Who Made Changes** - User information for each change
- **When Changes Occurred** - Exact timestamp with date, time, minutes, and seconds
- **Change History** - Complete timeline showing all past changes

---

## 🎯 Purpose
This feature enables administrators and managers to:
- Track all product price modifications over time
- Audit who changed prices and when
- View the complete history of price adjustments (even multiple changes)
- Maintain transparency and accountability in pricing decisions

---

## 👥 Who Can Access Product History?

The following roles have access to view product history:

1. **DG** (Director General)
2. **DGA** (Deputy Director General)
3. **Admin** (Administrator)
4. **Operation Manager** (Can see from create/view/edit product pages)
5. **Stock Manager** (All variations - Senior, Junior, etc.)
6. **Accountants** (Senior Accountant, Junior Accountant)

---

## 🚀 How to Access Product History

### Method 1: From Product List
1. Navigate to **Products → Create/Manage Products**
2. Find the product in the table
3. Click the **History** button (purple button) in the Actions column
4. View the complete price change timeline

### Method 2: From View Product Page
1. Go to **Create/Manage Products**
2. Click **View** on any product
3. Click the **📊 View History** button
4. See the full audit trail

### Method 3: From Edit Product Page
1. Go to **Create/Manage Products**
2. Click **Edit** on any product
3. After clicking "Back to List", click the **History** button
4. View all previous changes

---

## 📋 What Information is Displayed?

### Product Information Section
- Product Name
- Volume (cl)
- Crate Quantity
- Creation Date & Time
- Current Price / Crate

### Price Change Timeline
Each entry shows:

#### For Product Creation:
- **Event**: ✨ Product Created
- **User**: Who created it (with colored badge)
- **Date & Time**: Exact timestamp (format: DD Mon YYYY · HH:MM:SS)
- **Price / Crate**: Initial price (derived bottle price is shown for context)

#### For Price Updates:
- **Event**: ✏️ Price Updated
- **User**: Who changed it (with colored badge)
- **Date & Time**: Exact timestamp with minutes and seconds
- **Previous Price / Crate**: Shown with strikethrough (if changed from previous)
- **New Price / Crate**: Highlighted in green
- **Direction Arrow**: Visual indicator → showing change

---

## 💾 Database Structure

The history is stored in the `product_price_history` table with the following fields:

```sql
CREATE TABLE product_price_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,                    -- Which product
  changed_by INT NOT NULL,                    -- User ID who made the change
  old_price_per_crate BIGINT,                 -- Previous crate price (NULL if creation)
  new_price_per_crate BIGINT NOT NULL,        -- New crate price
  change_reason VARCHAR(255),                 -- Why the change was made
  changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,  -- When the change occurred
  FOREIGN KEY (product_id) REFERENCES product(id),
  FOREIGN KEY (changed_by) REFERENCES user(id),
  INDEX (product_id),
  INDEX (changed_by),
  INDEX (changed_at)
);
```

---

## 🔄 How History is Recorded

### On Product Creation
When a new product is created:
1. Product details are saved to the `product` table
2. An initial entry is created in `product_price_history`
3. **old_price_per_crate** is set to `NULL` (indicating first creation)
4. **changed_by** is set to the current user's ID
5. **change_reason** includes the user's full name

### On Product Edit - Price Change
When a product is edited and prices change:
1. Product details are updated in the `product` table
2. The system automatically detects if prices were modified
3. If prices changed:
   - A new entry is created in `product_price_history`
   - **old_price_per_crate** stores the previous value
   - **new_price_per_crate** stores the new value
   - **changed_by** is set to the current user's ID
4. If prices didn't change:
   - No new history entry is created (only other fields like name or volume are tracked in the main table)

---

## 📊 Example Timeline

Here's an example of a product that was created and then had its prices changed 3 times:

```
Product: Primus Beer 75cl

📍 Entry 1 (Creation):
   Date: 15 Nov 2025 · 10:30:45
   User: John Doe (Operation Manager)
   Price / Crate: 3000 FBU
   Note: Product Created by John Doe

📍 Entry 2 (First Price Increase):
   Date: 18 Nov 2025 · 14:15:22
   User: Jane Smith (Operation Manager)
   Price / Crate: 3000 → 3200 FBU
   Note: Price updated by Jane Smith

📍 Entry 3 (Second Price Adjustment):
   Date: 22 Nov 2025 · 09:45:10
   User: John Doe (Operation Manager)
   Price / Crate: 3200 → 3100 FBU
   Note: Price updated by John Doe

📍 Entry 4 (Third Price Update):
   Date: 25 Nov 2025 · 16:20:33
   User: Jane Smith (Operation Manager)
   Price / Crate: 3100 → 3250 FBU
   Note: Price updated by Jane Smith
```

---

## 🎨 Visual Design Features

### Color Coding
- **Primary Color** (#4B2F1F): Headers, labels, text
- **Accent Color** (#F4A261): Price highlights, timeline markers
- **Success Green**: New prices
- **Error Red**: Old prices (with strikethrough)
- **Purple**: History buttons

### Timeline Visualization
- Connected timeline with dots for each entry
- Different marker colors (gold for updates, brown for creation)
- Clear visual hierarchy with cards
- Responsive design for mobile devices

### Icons
- ✨ Product Created
- ✏️ Price Updated
- 📊 Price History (in history page title)
- ⏱️ Timestamp indicator
- 🏪 Wholesale price label

---

## 🔒 Security & Access Control

### Authorization
- The history page checks if the user's role is in the allowed list
- Unauthorized users are redirected to an "Unauthorized" page
- All database queries use prepared statements (SQL injection prevention)

### Data Integrity
- Foreign keys ensure product and user records must exist
- Cascade delete on product removal automatically cleans history
- Restrict delete prevents accidental user removal if they have history entries

---

## 📱 Responsive Design

The history page is fully responsive:

### Desktop (≥768px)
- Two-column layouts for related information
- Full timeline display with side-by-side details
- Optimized spacing and margins

### Mobile (≤768px)
- Single-column layout
- Stacked timeline entries
- Touch-friendly button sizes
- Full-width content

---

## 🛠️ Technical Implementation

### Files Involved

1. **`/modules/operation/product_history.php`**
   - Main history viewing page
   - Handles authorization, fetching, and display
   - Responsive styling

2. **`/modules/operation/create_product.php`**
   - Creates initial history entry on product creation
   - Added History button to product table

3. **`/modules/operation/edit_product.php`**
   - Detects price changes
   - Logs changes to history table
   - Creates table if it doesn't exist

4. **`/modules/operation/view_product.php`**
   - Added link to history page

5. **Database Migration**
   - `/database/migrations/20251108_create_product_price_history.sql`
   - Creates the history table structure

---

## ⚙️ Installation & Setup

### Step 1: Run the Migration
Execute the migration SQL file to create the history table:

```bash
mysql -u root -p masunzu_bar_hotel < database/migrations/20251108_create_product_price_history.sql
```

Or if using a GUI like phpMyAdmin:
1. Go to the database
2. Click "SQL" tab
3. Paste the SQL content
4. Click "Execute"

### Step 2: Verify Installation
After running the migration, verify the table exists:

```sql
DESCRIBE product_price_history;
```

You should see the table structure with all columns.

### Step 3: Test the Feature
1. Go to the product management page
2. Create a new product
3. Click "History" button
4. You should see the creation entry
5. Edit the product and change prices
6. Refresh history - new entry should appear

---

## 🐛 Troubleshooting

### Issue: History button not showing
**Solution**: Ensure all three files are updated:
- `create_product.php` - Contains history button
- `view_product.php` - Contains history link
- `product_history.php` - The history page

### Issue: "Unauthorized access" error
**Solution**: Check if your role is in the allowed list:
- DG, DGA, Admin, Operation Manager, Stock Manager, or Accountant

### Issue: No history entries showing
**Solution**: 
1. Ensure the migration was executed
2. Check if the table exists: `SELECT * FROM product_price_history;`
3. Create a new product or edit existing product to generate entries

### Issue: Historical prices not showing
**Solution**: 
1. The table may not exist yet - run the migration
2. Old products created before this feature won't have history
3. Only changes made after this feature is installed will be recorded

---

## 📈 Future Enhancements

Possible improvements for future versions:

1. **Export to CSV/PDF** - Download history reports
2. **Filter by Date Range** - View changes within specific dates
3. **Filter by User** - See all changes made by specific users
4. **Price Comparison** - Compare prices across dates
5. **Change Reasons** - Allow users to provide reason for price change
6. **Notifications** - Alert admins when prices change significantly
7. **Mass Update Tracking** - If multiple products are updated at once
8. **Bulk Operations Audit** - Track batch import changes

---

## 📞 Support

For issues or questions about the Product History feature:

1. Check the troubleshooting section above
2. Review the database structure
3. Verify all files are properly installed
4. Check application logs for errors
5. Contact your administrator

---

## 📝 Changelog

### Version 1.0 (November 8, 2025)
- ✅ Initial release
- ✅ Product creation tracking
- ✅ Price change logging
- ✅ Complete timeline display
- ✅ User attribution
- ✅ Role-based access control
- ✅ Responsive design
- ✅ DateTime with minutes and seconds
