# 🍽️ Restaurant COGS Calculator

A comprehensive web-based application for managing restaurant operations, calculating Cost of Goods Sold (COGS), and analyzing profit margins. Built with modern UI/UX design principles, this tool helps restaurant owners and managers make data-driven pricing decisions.

## 📋 Table of Contents

- [Overview](#overview)
- [Key Features](#key-features)
- [Calculation Methods](#calculation-methods)
- [Requirements](#requirements)
- [Quick Start](#quick-start)
- [Detailed Setup](#detailed-setup)
- [Usage Guide](#usage-guide)
- [Architecture](#architecture)
- [API Endpoints](#api-endpoints)
- [Database Schema](#database-schema)
- [Troubleshooting](#troubleshooting)
- [Recent Updates](#recent-updates)

## 🎯 Overview

The Restaurant COGS Calculator is a full-featured management system designed specifically for restaurants to:

- **Track Costs**: Monitor ingredient costs and calculate dish-level COGS accurately
- **Manage Customers**: Handle multiple customers with customer-specific pricing and COGS
- **Analyze Margins**: Real-time margin calculations with two different calculation methods
- **Optimize Pricing**: Compare margins at different price points to make informed decisions
- **Generate Reports**: Export comprehensive reports in Excel format for analysis

### What is COGS?

Cost of Goods Sold (COGS) represents the direct costs attributable to the production of a dish, including:
- Ingredient costs (based on BOM/recipe)
- Packaging costs
- Manufacturing overhead (labor, machinery) as a percentage

## ✨ Key Features

### 🎨 Modern User Interface
- **Dark/Light Mode**: Toggle between themes for comfortable viewing
- **Responsive Design**: Works seamlessly on desktop and tablet devices
- **Modern Button Design**: Beautiful gradients, smooth animations, and hover effects
- **Intuitive Navigation**: Sidebar navigation with active state indicators
- **Real-time Updates**: Instant calculations and live data updates

### 👥 Customer Management
- Create and manage multiple customer accounts
- Store business details (GST/TAN/PAN numbers)
- Customer-specific dish pricing
- Custom COGS per customer
- Customer-specific BOM (recipes) with custom ingredient costs

### 🍲 Dish Management
- Add, edit, and delete dishes
- Organize dishes by categories
- Set selling prices per customer
- Track margin percentages
- View dish COGS history over time

### 🥘 Ingredient Management
- Track ingredient costs per unit
- Support for various Units of Measurement (UOM): kg, g, L, mL, pieces, etc.
- Item code support for inventory tracking
- Purchase history tracking (optional)
- Automatic cost updates

### 📝 BOM (Bill of Materials) / Recipe Management
- Create recipes by mapping ingredients to dishes
- Set ingredient quantities per dish
- Base recipes and customer-specific recipes
- Automatic COGS calculation based on current ingredient costs
- Visual recipe breakdown

### 💰 COGS Calculation
- **Automatic Calculation**: COGS computed from ingredient costs and quantities
- **Packaging Costs**: Add packaging costs per dish
- **Manufacturing Overhead**: Configurable percentage for labor and machinery
- **Real-time Updates**: COGS recalculates when ingredient costs change
- **Historical Tracking**: Save and view COGS history over time

### 📊 Margin Analysis
- **Two Calculation Methods**:
  - **Method 1**: Calculate margin from Selling Price (SP)
  - **Method 2**: Calculate Selling Price from COGS + Margin %
- Real-time margin percentage display
- Margin comparison tool
- Visual indicators for profit/loss

### 📈 Dashboard & Reports
- **Dashboard**: Overview metrics, quick stats, and recent activity
- **Detailed Reports**: Cost breakdown per dish
- **Excel Exports**: Multiple export formats:
  - Dishes Report
  - Ingredients Report
  - BOM Report
  - COGS Analysis
  - Margin Analysis
  - Complete Report (all data)

### 🔍 Search & Filter
- Global search across dishes, ingredients, and customers
- Filter by category
- Quick access to frequently used items

## 🧮 Calculation Methods

The application supports two calculation methods for flexibility:

### Method 1: Margin from Selling Price
- **Input**: Selling Price (SP)
- **Calculation**: 
  - Margin = SP - (COGS + Packaging + Manufacturing Overhead)
  - Margin % = (Margin / SP) × 100
- **Use Case**: When you know your selling price and want to see the margin

### Method 2: Selling Price from Margin %
- **Input**: Desired Margin Percentage
- **Calculation**:
  - SP = (COGS + Packaging) / (1 - Manufacturing Overhead % - Margin %)
- **Use Case**: When you want to achieve a specific margin percentage

**Note**: Method 2 requires both Manufacturing Overhead % and Margin % to be set.

## 📦 Requirements

- **Server**: XAMPP (or any PHP/MySQL server)
- **PHP**: Version 7.0 or higher
- **MySQL**: Version 5.7 or higher
- **Browser**: Modern browser (Chrome, Firefox, Safari, Edge)
- **Storage**: Minimal disk space required

## 🚀 Quick Start

1. **Start XAMPP**
   - Open XAMPP Control Panel
   - Start Apache and MySQL services

2. **Setup Database**
   - Navigate to: `http://localhost/Cogs Calculator/create_tables.php`
   - This will automatically create all required tables

3. **Configure Database**
   - Copy `config.example.php` to `config.php`
   - Edit `config.php` with your database credentials

4. **Access Application**
   - Open: `http://localhost/Cogs Calculator/restaurant-cogs-ui.html`
   - Start managing your restaurant data!

## 📖 Detailed Setup

### Step 1: Database Setup

#### Option A: Automatic Setup (Recommended)
1. Ensure XAMPP Apache and MySQL are running
2. Open in browser: `http://localhost/Cogs Calculator/create_tables.php`
3. The script will:
   - Check existing tables
   - Create missing tables
   - Display a detailed report
   - Verify all tables are present

#### Option B: Manual Setup
1. Open phpMyAdmin: `http://localhost/phpmyadmin`
2. Create a new database (or use existing)
3. Go to SQL tab
4. Copy and paste contents from `database_schema.sql`
5. Click "Go" to execute

### Step 2: Database Configuration

1. **Copy configuration file**:
   ```bash
   cp config.example.php config.php
   ```

2. **Edit `config.php`** with your database details:
   ```php
   $host = "localhost";           // MySQL host
   $user = "root";                 // MySQL username
   $pass = "";                     // MySQL password (empty for XAMPP default)
   $db = "restaurant_cogs";        // Database name
   ```

3. **Test connection** (optional):
   - Open: `http://localhost/Cogs Calculator/test_db_connection.php`
   - Should display connection status

### Step 3: File Structure

Ensure all files are in the XAMPP htdocs directory:
```
/Applications/XAMPP/xamppfiles/htdocs/Cogs Calculator/
├── restaurant-cogs-ui.html      # Main application
├── config.php                   # Database config (create from example)
├── config.example.php           # Config template
├── database_schema.sql           # Database schema
├── create_tables.php            # Auto table creation
├── test_db_connection.php       # Connection tester
├── get_*.php                    # API endpoints
├── save_*.php                   # API endpoints
├── delete_*.php                 # API endpoints
└── update_*.php                 # API endpoints
```

### Step 4: Access Application

Open in your browser:
   ```
   http://localhost/Cogs Calculator/restaurant-cogs-ui.html
   ```

## 📚 Usage Guide

### 1. Setting Up Your First Customer

1. Navigate to **Customers** section
2. Click **"+ Add Customer"** button
3. Fill in customer details:
   - Name (required)
   - Address
   - Business ID
   - GST/TAN/PAN numbers
4. Click **Save**

### 2. Adding Ingredients

1. Go to **Ingredients** section
2. Click **"+ Add Ingredient"**
3. Enter details:
   - Name (e.g., "Tomato")
   - Item Code (optional, for inventory)
   - Unit (e.g., "kg", "g", "L", "pieces")
   - Cost per Unit (e.g., 50.00 for ₹50/kg)
4. Click **Save**

### 3. Creating Dishes

1. Navigate to **Dishes** section
2. Select a customer (required for new dishes)
3. Click **"+ Add Dish"**
4. Enter dish details:
   - Name (e.g., "Margherita Pizza")
   - Category (optional, e.g., "Pizza")
   - Selling Price (for Method 1) OR Margin % (for Method 2)
   - Manufacturing Overhead % (optional)
5. Click **Save**

### 4. Creating Recipes (BOM)

1. Go to **BOM** section or click **"BOM"** button next to a dish
2. Select a dish from dropdown
3. Add ingredients:
   - Select ingredient
   - Enter quantity (e.g., 0.5 for 0.5 kg)
   - Click **Add**
4. COGS will calculate automatically
5. Add packaging cost if needed
6. View total COGS and margin

### 5. Viewing Reports

1. Go to **Reports** section
2. Select a dish from the list
3. View detailed breakdown:
   - Ingredient costs
   - Packaging cost
   - Manufacturing overhead
   - Total COGS
   - Selling Price
   - Margin and Margin %

### 6. Exporting Data

1. Go to **Exports** section
2. Choose export type:
   - Dishes Report
   - Ingredients Report
   - BOM Report
   - COGS Analysis
   - Margin Analysis
   - Complete Report
3. Click **Export** to download Excel file

### 7. Using Calculation Methods

**Switch between methods** using the toggle buttons in the header:
- **Method 1**: Enter Selling Price, see calculated margin
- **Method 2**: Enter Margin %, see calculated Selling Price

**Note**: When using Method 2, ensure both Manufacturing Overhead % and Margin % are set.

## 🏗️ Architecture

### Frontend
- **Single Page Application (SPA)**: `restaurant-cogs-ui.html`
- **Vanilla JavaScript**: No framework dependencies
- **Modern CSS**: CSS Variables, Flexbox, Grid
- **Responsive Design**: Mobile-friendly layout
- **Dark/Light Theme**: User preference support

### Backend
- **PHP REST API**: RESTful endpoints for all operations
- **MySQL Database**: Relational database for data storage
- **Prepared Statements**: SQL injection protection
- **Error Handling**: Comprehensive error responses

### Data Flow
```
User Action (UI) 
  → JavaScript Fetch API 
    → PHP Endpoint 
      → MySQL Database 
        → Response (JSON) 
          → UI Update
```

## 🔌 API Endpoints

### Customer Management
- `get_customers.php` - GET: Fetch all customers
- `save_customer.php` - POST: Create/update customer
- `delete_customer.php` - POST: Delete customer
- `get_customer_dishes.php` - GET: Get dishes for a customer

### Dish Management
- `get_dishes.php` - GET: Fetch all dishes with BOM
- `save_dish.php` - POST: Create/update dish
- `delete_dish.php` - POST: Delete dish

### Ingredient Management
- `get_ingredients.php` - GET: Fetch all ingredients
- `save_ingredient.php` - POST: Create/update ingredient
- `delete_ingredient.php` - POST: Delete ingredient

### BOM Management
- `update_bom.php` - POST: Update dish BOM (recipe)
- `delete_bom.php` - POST: Delete BOM entry

### COGS & History
- `save_cogs.php` - POST: Save COGS calculation
- `save_cogs_history.php` - POST: Save COGS history
- `get_dish_cogs_history.php` - GET: Get dish COGS history
- `get_customer_cogs_history.php` - GET: Get customer COGS history

### Customer-Specific
- `save_customer_dish.php` - POST: Save customer-specific dish pricing

## 🗄️ Database Schema

### Core Tables

#### `customers`
Stores customer account information.
- `id` (Primary Key)
- `name`, `address`, `business_id`
- `gst_number`, `tan_number`, `pan_number`
- `created_at`, `updated_at`

#### `dishes`
Stores dish information.
- `id` (Primary Key)
- `customer_id` (Foreign Key)
- `name`, `category`
- `selling_price`
- `other_manufacturing_cost_percent`
- `margin_percent`
- `created_at`, `updated_at`

#### `ingredients`
Stores ingredient information.
- `id` (Primary Key)
- `name`, `item_code`
- `unit` (UOM)
- `cost_per_unit`
- `last_updated`

#### `dish_bom`
Maps ingredients to dishes (base recipes).
- `id` (Primary Key)
- `dish_id` (Foreign Key)
- `ingredient_id` (Foreign Key)
- `quantity`

### Customer-Specific Tables

#### `customer_dishes`
Customer-specific dish pricing and custom COGS.
- `id` (Primary Key)
- `customer_id`, `dish_id` (Foreign Keys)
- `selling_price`
- `custom_cogs`
- `margin_percent`
- `notes`

#### `customer_bom`
Customer-specific BOM with custom ingredient costs.
- `id` (Primary Key)
- `customer_id`, `dish_id`, `ingredient_id` (Foreign Keys)
- `quantity`
- `custom_cost_per_unit`

### History Tables

#### `dish_cogs_history`
Historical COGS data for dishes.
- `id` (Primary Key)
- `dish_id` (Foreign Key)
- `total_cogs`, `selling_price`, `margin_percent`
- `calculated_at`

#### `customer_cogs_history`
Customer-specific COGS history.
- `id` (Primary Key)
- `customer_id`, `dish_id` (Foreign Keys)
- `total_cogs`, `selling_price`, `margin_percent`
- `calculated_at`

### Optional Tables

#### `purchase`
Ingredient purchase history (optional).
- `id` (Primary Key)
- `ingredient_id` (Foreign Key)
- `cost_per_unit`
- `purchase_date`

## 🔧 Troubleshooting

### Database Connection Issues

**Error**: "Database connection failed"
- ✅ Check MySQL is running in XAMPP
- ✅ Verify credentials in `config.php`
- ✅ Test connection: `http://localhost/Cogs Calculator/test_db_connection.php`
- ✅ Check MySQL port (default: 3306)

**Error**: "Table doesn't exist"
- ✅ Run: `http://localhost/Cogs Calculator/create_tables.php`
- ✅ Or manually import `database_schema.sql` in phpMyAdmin

### Application Not Loading

**Error**: Blank page or 404
- ✅ Check file path: `http://localhost/Cogs Calculator/restaurant-cogs-ui.html`
- ✅ Ensure Apache is running
- ✅ Check browser console for JavaScript errors
- ✅ Verify all files are in correct directory

### Data Not Saving

**Error**: "Failed to save" or 500 error
- ✅ Check PHP error logs in XAMPP
- ✅ Verify database connection
- ✅ Check browser console for API errors
- ✅ Ensure all required fields are filled

### CORS Errors

**Error**: "CORS policy" in browser console
- ✅ Ensure all files are in same directory
- ✅ Access via `http://localhost` (not `file://`)
- ✅ Check `config.php` headers are set correctly

### Calculation Issues

**Error**: Incorrect COGS or margin calculations
- ✅ Verify all ingredient costs are entered
- ✅ Check BOM quantities are correct
- ✅ Ensure calculation method is set correctly
- ✅ For Method 2, verify both overhead % and margin % are set

### Performance Issues

**Slow loading or timeouts**
- ✅ Check MySQL connection limits
- ✅ Optimize database queries
- ✅ Clear browser cache
- ✅ Check server resources

## 📝 Recent Updates

### UI/UX Improvements
- ✅ Modern button design with gradients and animations
- ✅ Indigo/purple color scheme
- ✅ Enhanced hover effects and transitions
- ✅ Improved navigation with active state indicators
- ✅ Better form styling and focus states

### Feature Additions
- ✅ Customer-based COGS system
- ✅ Unit of Measurement (UOM) support
- ✅ Item code support for ingredients
- ✅ Margin percentage field in dishes
- ✅ Two calculation methods (SP-based and Margin-based)
- ✅ Excel export functionality
- ✅ Comprehensive error handling
- ✅ Database migration tools

### Bug Fixes
- ✅ Fixed margin_percent not being saved in dish updates
- ✅ Fixed redundant margin percentage checks
- ✅ Improved database connection error handling
- ✅ Enhanced backward compatibility for missing columns

## 🔒 Security Notes

- ✅ All SQL queries use **prepared statements** to prevent SQL injection
- ✅ Database credentials stored in `config.php` (excluded from git)
- ✅ Input validation on both client and server side
- ✅ CORS headers configured for API endpoints
- ⚠️ **For production**: Move `config.php` outside web root or use environment variables
- ⚠️ **For production**: Implement authentication and authorization
- ⚠️ **For production**: Use HTTPS for secure data transmission

## 📞 Support & Resources

### Getting Help

1. **Check Error Logs**:
   - PHP errors: XAMPP logs directory
   - Browser console: F12 → Console tab
   - MySQL errors: XAMPP MySQL logs

2. **Database Tools**:
   - `test_db_connection.php` - Test database connection
   - `create_tables.php` - Auto-create tables
   - `fix_database.php` - Fix database issues

3. **Common Solutions**:
   - Restart XAMPP services
   - Clear browser cache
   - Check file permissions
   - Verify database credentials

## 📄 License

This project is provided as-is for restaurant management purposes.

## 🤝 Contributing

To contribute improvements:
1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Test thoroughly
5. Submit a pull request

---

**Made with ❤️ for restaurant owners and managers**

For questions or issues, please check the troubleshooting section or review the code comments.