# Fortnight Stock Management - Database Migration Guide

## Problem
If you're seeing errors like:
- `Unknown column 'start_date' in 'where clause'`
- `Unknown column 'from_date' in 'where clause'`
- Tables missing or columns missing

## Solution
Run the migration to create/fix all required tables and columns.

## Method 1: Using PHP Migration Script (Recommended)

1. **Access the migration script:**
   - URL: `http://your-domain/database/migrations/run_fortnight_migration.php`
   - **Note:** You must be logged in as Admin

2. **The script will:**
   - Check which columns exist
   - Create missing tables
   - Add missing columns
   - Rename `from_date`/`to_date` to `start_date`/`end_date` if needed
   - Remove old `item_id` column if it exists

3. **After migration:**
   - You'll see a success message
   - Click the link to go to Fortnight Management

## Method 2: Using Direct SQL (phpMyAdmin)

1. **Open phpMyAdmin**
2. **Select your database**
3. **Go to SQL tab**
4. **Copy and paste the contents of:** `database/migrations/fortnight_migration_direct.sql`
5. **Click "Go"**

## Tables Created/Fixed

### 1. `fortnight_periods`
- Stores period information (dates, month, year)
- Columns: `period_id`, `period_name`, `start_date`, `end_date`, `month`, `year`, `site_id`, `is_locked`, `created_by`, `created_at`, `updated_at`

### 2. `fortnight_inventory`
- Stores inventory data for each period
- Columns: `inventory_id`, `period_id`, `sr_no`, `material_hsn_code`, `material_description`, `category`, `net_rate`, `opening_stock_qty`, `opening_stock_value`, `purchase_qty`, `purchase_value`, `consumption_qty`, `consumption_value`, `closing_stock_qty`, `closing_stock_value`, `supplier_name`, `bill_challan_no`, `site_id`, `created_by`, `updated_by`, `created_at`, `updated_at`

### 3. `item_master`
- Stores material/item master data for dropdowns
- Columns: `item_id`, `material_hsn_code`, `material_name`, `category`, `unit`, `rate`, `site_id`, `is_active`, `display_order`, `created_at`, `updated_at`

### 4. `supplier_master`
- Stores supplier master data for dropdowns
- Columns: `supplier_id`, `supplier_code`, `supplier_name`, `contact`, `address`, `site_id`, `is_active`, `created_at`, `updated_at`

## After Migration

1. **Add Items to item_master:**
   ```sql
   INSERT INTO `item_master` (`material_hsn_code`, `material_name`, `category`, `unit`, `rate`, `site_id`) 
   VALUES ('HSN001', 'Rice', 'Grocery', 'Kg', 50.00, 0);
   ```

2. **Add Suppliers to supplier_master:**
   ```sql
   INSERT INTO `supplier_master` (`supplier_name`, `contact`, `site_id`) 
   VALUES ('ABC Suppliers', '1234567890', 0);
   ```

3. **Test the system:**
   - Go to Fortnight Management
   - Create a new period
   - Add inventory data
   - Save and view reports

## Troubleshooting

### Error: "Table already exists"
- This is normal if tables already exist
- The migration will only add missing columns

### Error: "Column already exists"
- This is normal if columns already exist
- The migration skips existing columns

### Error: "Foreign key constraint fails"
- Make sure `fortnight_periods` table exists first
- The migration creates tables in the correct order

## Support

If you encounter any issues:
1. Check the error message
2. Verify database user has CREATE/ALTER permissions
3. Check if tables exist: `SHOW TABLES LIKE 'fortnight%';`
4. Check table structure: `DESCRIBE fortnight_periods;`
