# Database Reset Instructions

## Overview
The `clean_reset_db.sql` script will safely delete all operational and transactional data while preserving:
- **user** table (all user accounts and settings)
- **province** table (all province data)
- **role** table (user role definitions)

## What Gets Deleted
- All invoice records (paid, unpaid, and backup)
- All products and stock records (main, retail, wholesale, province-level)
- All customer data and loan records
- All requisitions and expenses
- All cashier shifts and balances
- All bank accounts and deposits
- All orders and suppliers
- All financial transactions and logs

## Important Safeguards
✓ Foreign Key checks are **disabled** during deletion  
✓ Deletion order respects FK relationships  
✓ Auto-increment counters are reset to 1  
✓ Foreign Key checks are **re-enabled** after deletion  
✓ No data corruption or orphaned records

## Pre-Import Checklist
- [ ] You have a backup of your current database
- [ ] You have verified the backup is valid
- [ ] You are ready to start testing with fresh data

## Execution Methods

### Method 1: Command Line (Recommended)
```bash
cd /opt/lampp/htdocs/masunzu_bar_hotel/database
mysql -u root -p masunzu_bar_hotel < clean_reset_db.sql
```
When prompted, enter your MySQL password.

### Method 2: phpMyAdmin
1. Open phpMyAdmin
2. Select the `masunzu_bar_hotel` database
3. Go to **Import** tab
4. Click **Choose File** and select `clean_reset_db.sql`
5. Click **Import**

### Method 3: Direct MySQL Command
```bash
mysql -u root masunzu_bar_hotel -e "SET FOREIGN_KEY_CHECKS = 0;" && \
mysql -u root masunzu_bar_hotel < clean_reset_db.sql && \
mysql -u root masunzu_bar_hotel -e "SET FOREIGN_KEY_CHECKS = 1;"
```

## After Import - Verification

### Verify Users Still Exist
```sql
SELECT COUNT(*) as user_count FROM user;
SELECT id, username, full_name, role_id FROM user LIMIT 5;
```

### Verify Provinces Still Exist
```sql
SELECT COUNT(*) as province_count FROM province;
SELECT id, name FROM province;
```

### Verify Data Was Deleted
```sql
SELECT COUNT(*) FROM paid_invoices;        -- Should be 0
SELECT COUNT(*) FROM unpaid_invoices;      -- Should be 0
SELECT COUNT(*) FROM product;              -- Should be 0
SELECT COUNT(*) FROM customer;             -- Should be 0
SELECT COUNT(*) FROM requisition;          -- Should be 0
SELECT COUNT(*) FROM expense;              -- Should be 0
```

### Check for FK Errors
```sql
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_SCHEMA = 'masunzu_bar_hotel' 
AND REFERENCED_TABLE_NAME IS NOT NULL 
LIMIT 10;
```

## Testing Workflow

After successful reset, follow this flow to test your system:

1. **Add Products**
   - Create a few test products (e.g., Beer, Soda, Snacks)
   - Stock them in main inventory

2. **Create Customers**
   - Add test customers from different provinces
   - Assign them to different sales agents

3. **Test Sales Flow**
   - Create invoices for customers
   - Test approval workflow
   - Mark as paid
   - Verify cash flow tracking

4. **Test Requisitions**
   - Create requisitions from provinces
   - Approve them
   - Mark as paid
   - Track expenses

5. **Test Bank Operations**
   - Create bank deposits
   - Track cashier balances
   - Verify accountant balance calculations

6. **Test Reports**
   - Verify reports.php shows correct data
   - Test filters and exports
   - Check dashboard metrics

## Troubleshooting

### If You Get FK Errors
The script disables FK checks, so you shouldn't see errors. If you do:
1. Check that MySQL FK support is enabled: `SELECT @@foreign_key_checks;`
2. Run the script again from scratch
3. Contact support with the error message

### If Import Fails
1. Check available disk space
2. Verify database user has proper permissions
3. Try importing smaller sections first
4. Check MySQL error log: `tail -f /var/log/mysql/error.log`

### If Some Data Wasn't Deleted
1. Verify the script completed fully
2. Check for errors in the import log
3. Re-run the import

## Rolling Back

If something goes wrong:
1. Stop any application processes
2. Restore from your backup:
   ```bash
   mysql -u root -p masunzu_bar_hotel < /path/to/backup.sql
   ```

## Safety Note

This script is designed to be safe and tested, but always:
- Keep a valid backup
- Test in a development environment first if possible
- Document any custom data before cleanup
- Don't interrupt the import process

---
**Generated**: November 8, 2025
**Script**: clean_reset_db.sql
**Tables Preserved**: user, province, role
**Database**: masunzu_bar_hotel
