# Database Cleanup Guide for Production

This guide provides instructions for cleaning your database before going live, removing all test data while keeping essential configuration.

## ⚠️ WARNING

**BACKUP YOUR DATABASE BEFORE RUNNING ANY CLEANUP SCRIPT!**

These scripts will permanently delete data. Make sure you have a backup.

## Method 1: Laravel Artisan Command (Recommended)

### Basic Usage

```bash
php artisan db:clean-for-production
```

This will:
- ✅ Delete all orders and order items
- ✅ Delete all products and variants
- ✅ Delete all carts and cart items
- ✅ Delete all wishlists
- ✅ Delete all product reviews
- ✅ Delete all stock movements
- ✅ Delete all stock reservations
- ✅ Delete old OTP codes (older than 1 hour)
- ✅ Clear application cache
- ✅ Keep admin and staff users
- ✅ Keep categories, settings, banners, etc.

### Options

```bash
# Keep all users (including customers)
php artisan db:clean-for-production --keep-users

# Keep customer addresses
php artisan db:clean-for-production --keep-addresses

# Keep coupons and redemptions
php artisan db:clean-for-production --keep-coupons

# Keep product images and media files
php artisan db:clean-for-production --keep-images

# Combine options
php artisan db:clean-for-production --keep-users --keep-addresses --keep-coupons --keep-images

# Skip confirmation prompt
php artisan db:clean-for-production --force
```

### What Gets Deleted

- ❌ All orders and order items
- ❌ All products and product variants
- ❌ **All product images and media files from storage** (unless `--keep-images` is used)
- ❌ All product media database records
- ❌ All product reviews
- ❌ All carts and cart items
- ❌ All wishlists
- ❌ All stock movements
- ❌ All stock reservations
- ❌ Old OTP codes (older than 1 hour)
- ❌ Customer users (unless `--keep-users` is used)
- ❌ Customer addresses (unless `--keep-addresses` is used)
- ❌ Coupon redemptions (unless `--keep-coupons` is used)

### What Gets Kept

- ✅ Categories (structure and hierarchy)
- ✅ Settings (site configuration)
- ✅ Admin and staff users
- ✅ Shipping methods
- ✅ Tax settings
- ✅ Banners
- ✅ Announcements
- ✅ Brands
- ✅ Attributes and attribute values
- ✅ Coupons (if `--keep-coupons` is used)
- ✅ Units
- ✅ Permissions and roles
- ✅ Customer addresses (if `--keep-addresses` is used)
- ✅ Customer users (if `--keep-users` is used)

## Method 2: SQL Script

### Usage

1. **Backup your database first!**
   ```bash
   mysqldump -u username -p database_name > backup.sql
   ```

2. **Run the SQL script:**
   ```bash
   mysql -u username -p database_name < database/scripts/clean_database_for_production.sql
   ```

   Or import via phpMyAdmin/MySQL Workbench.

### Customization

Edit `database/scripts/clean_database_for_production.sql` and uncomment sections you want to include:

- **Delete customer users:** Uncomment the DELETE statement in section 10
- **Delete customer addresses:** Uncomment the TRUNCATE statement in section 11
- **Delete coupon redemptions:** Uncomment the TRUNCATE statement in section 9
- **Reset auto-increment IDs:** Uncomment the ALTER TABLE statements in section 12

## Verification

After running the cleanup, verify the results:

### Using Laravel Tinker

```bash
php artisan tinker
```

```php
// Check counts
DB::table('orders')->count();
DB::table('products')->count();
DB::table('carts')->count();
DB::table('wishlists')->count();
DB::table('product_reviews')->count();

// Verify essential data is kept
DB::table('categories')->count();
DB::table('settings')->count();
DB::table('users')->whereIn('user_type', ['admin', 'staff'])->count();
```

### Using SQL

```sql
-- Check counts
SELECT COUNT(*) as order_count FROM orders;
SELECT COUNT(*) as product_count FROM products;
SELECT COUNT(*) as cart_count FROM carts;
SELECT COUNT(*) as wishlist_count FROM wishlists;
SELECT COUNT(*) as review_count FROM product_reviews;

-- Verify essential data
SELECT COUNT(*) as category_count FROM categories;
SELECT COUNT(*) as setting_count FROM settings;
SELECT COUNT(*) as admin_staff_count FROM users WHERE user_type IN ('admin', 'staff');
```

## Post-Cleanup Steps

1. **Clear all caches:**
   ```bash
   php artisan cache:clear
   php artisan config:clear
   php artisan view:clear
   php artisan route:clear
   ```

2. **Optimize database:**
   ```bash
   php artisan optimize
   ```

3. **Verify application:**
   - Check homepage loads correctly
   - Verify admin panel access
   - Test category navigation
   - Check settings are intact

## Common Scenarios

### Scenario 1: Complete Clean Slate
```bash
php artisan db:clean-for-production --force
```
Deletes everything except essential configuration.

### Scenario 2: Keep Customer Data
```bash
php artisan db:clean-for-production --keep-users --keep-addresses --force
```
Keeps customer accounts and addresses, removes only transactional data.

### Scenario 3: Keep Coupons
```bash
php artisan db:clean-for-production --keep-coupons --force
```
Keeps coupons and their redemption history.

### Scenario 4: Keep Product Images
```bash
php artisan db:clean-for-production --keep-images --force
```
Keeps product images and media files in storage (only deletes database records).

## Troubleshooting

### Foreign Key Constraint Errors

If you encounter foreign key errors, the SQL script automatically disables foreign key checks. The Laravel command also handles this.

### Missing Tables

If a table doesn't exist, the script will skip it. Check your migrations to ensure all tables are created.

### Cache Issues

After cleanup, always clear cache:
```bash
php artisan cache:clear
php artisan config:clear
php artisan view:clear
```

## Safety Features

1. **Confirmation Prompt:** The Laravel command asks for confirmation (unless `--force` is used)
2. **Transaction Safety:** Uses proper foreign key handling
3. **Selective Deletion:** Only deletes transactional data, keeps configuration
4. **Verification Queries:** Included in SQL script for verification

## Files

- **Laravel Command:** `app/Console/Commands/CleanDatabaseForProduction.php`
- **SQL Script:** `database/scripts/clean_database_for_production.sql`
- **This Guide:** `DATABASE_CLEANUP_GUIDE.md`

## Support

If you encounter any issues:
1. Check the error message
2. Verify database backup is available
3. Review the script output
4. Check Laravel logs: `storage/logs/laravel.log`