# Database Migration Guide for Namecheap Deployment
## Updating Tables & Clearing Learner Data

### Overview
This guide explains how to deploy the database changes for learner promotion workflow to your Namecheap hosted database and clear all student data for a fresh start.

### What This Does
1. **Adds promotion workflow columns** to `learner_promotions` table
   - status (pending/approved/rejected)
   - balance tracking
   - approval audit fields

2. **Clears all learner data** (clean slate for admissions)
   - Removes all learners
   - Removes all payments/invoices
   - Removes attendance records
   - Removes marks, notes, applications
   - Resets auto-increment counters

3. **Prepares for fresh admissions** starting at MAV26001

---

## How to Deploy on Namecheap

### Method 1: Using phpMyAdmin (Easiest)

**Step 1: Access phpMyAdmin**
- Login to your Namecheap cPanel
- Go to Databases → phpMyAdmin
- Select your `mavela_db` database

**Step 2: Run the SQL Script**
1. Click the "SQL" tab at the top
2. Copy all SQL from `migration_deploy_namecheap.sql`
3. Paste it in the text area
4. Click "Go" button to execute

**Expected Output:**
- No errors (just status messages)
- You'll see "0 rows affected" messages for each DELETE
- ALTER TABLE statements execute silently

**Step 3: Verify Success**
Run these verification queries one by one:

```sql
-- Check learners table is empty
SELECT COUNT(*) as learner_count FROM learners;
-- Should return: 0

-- Check promotion table structure
SHOW COLUMNS FROM learner_promotions;
-- Should show new columns: status, balance_before, balance_carried, etc.

-- Check auto-increment reset
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = 'learners' AND TABLE_SCHEMA = DATABASE();
-- Should return: 1
```

---

### Method 2: Using Command Line (If Available)

**Step 1: Connect via SSH/Terminal**
```bash
mysql -h your-host -u your-username -p your-database-name < migration_deploy_namecheap.sql
```

Replace:
- `your-host`: Your database host (from cPanel)
- `your-username`: Your database username
- `your-database-name`: Your database name (e.g., mavela_db)

When prompted, enter your database password.

**Step 2: Check for Errors**
- No error messages = success
- Review output for any warnings

---

### Method 3: Upload & Execute via File

**Step 1: Create Scheduled Job (cPanel)**
- Go to cPanel → Cron Jobs
- Create new cron to run: `mysql -u user -p pass dbname < /path/to/migration_deploy_namecheap.sql`
- Schedule for off-peak hours

---

## What Gets Deleted (Detailed Breakdown)

### Tables Cleared:
| Table | Records Removed | Impact |
|-------|-----------------|--------|
| learners | All student records | Can add new learners starting from ID 1 |
| learner_marks | All assessment scores | New assessments will start fresh |
| learner_notes | All teacher notes | Can add fresh notes |
| learner_promotions | All promotion history | Ready for new promotions |
| daily_attendance_register | All attendance records | Attendance tracking starts fresh |
| fee_payments | All payment records | Clean financial slate |
| fee_invoices | All invoices | Invoice numbering resets |
| parent_learner_links | All parent-student links | Relink parents after admission |
| learner_applications | All applications | Clear application backlog |
| admissions_pending | All pending admissions | Clear admission queue |
| elearning_posts | All assignments/exercises | Teachers can repost |
| elearning_submissions | All student submissions | Clean submission history |
| library_access_logs | All library access logs | Access tracking resets |

### Tables NOT Cleared:
- `users` - Admin, teachers, parents remain
- `classes` - Class setup maintained (1North, 2North, etc.)
- `subjects` - Curriculum maintained
- `class_teacher_assignments` - Teacher assignments kept
- `teacher_hop_assignments` - HOP assignments kept
- `app_settings` - School settings preserved

---

## After Deployment: Next Steps

### 1. Verify in Application
1. Access admin.php
2. Check Learner Promotions view (should be empty)
3. Start Student Admission process
4. First learner will be assigned: MAV26001

### 2. Re-link Parents (if keeping parent accounts)
- If you kept parent users, re-link them to new learners during admission
- Parents can access updated learner records

### 3. Re-assign Classes/Teachers
- If class/teacher assignments were cleared, re-assign as needed
- (Note: Our script keeps these intact)

---

## Troubleshooting

### "Access denied for user"
- Check database username and password in Namecheap cPanel
- Verify you're using correct credentials

### "Table doesn't exist"
- Database name might be different
- Check exact table names in phpMyAdmin

### "Foreign key constraint fails"
- Script disables foreign keys (SET FOREIGN_KEY_CHECKS = 0;)
- If this fails, contact Namecheap support
- They may need to run as database admin

### "Storage engine error"
- Ensure all tables use InnoDB
- Should be default on modern Namecheap systems

### Partial execution (only some tables cleared)
- May indicate file was cut off
- Re-run the complete SQL script
- Verify all lines copied correctly

---

## Backup Before Running

**CRITICAL: Create database backup first!**

Steps:
1. In phpMyAdmin, select `mavela_db` database
2. Click "Export" tab
3. Choose "Quick" export type
4. Download the SQL file
5. Keep this backup safe
6. Only then run migration script

---

## Rollback Procedure

If something goes wrong:

1. **From Backup (Easiest)**
   - In phpMyAdmin, click "Import" tab
   - Select your backup SQL file
   - Click "Go"
   - Your database is restored

2. **Restore via cPanel**
   - Go to cPanel → Backups
   - Select backup point before migration
   - Request restore

---

## Performance Notes

- Script execution time: 2-5 seconds for typical databases
- No locking/downtime needed
- Safe to run during school hours (minimal impact)

---

## Questions or Issues?

1. Check PHP error logs on Namecheap cPanel
2. Verify database credentials in Config.php match Namecheap panel
3. Ensure Config.php is set to 'production' environment:
   ```php
   $appEnv = 'production';
   ```
4. Test connection after migration by accessing admin.php

---

## Script Contents Summary

The `migration_deploy_namecheap.sql` file contains:

**PHASE 1**: Add learner promotion workflow columns (7 columns)
- Queries to ALTER TABLE learner_promotions
- Add 2 performance indexes

**PHASE 2**: Clear all learner-related data (13 DELETE statements)
- Respects foreign key relationships
- Uses IF NOT EXISTS for safety

**PHASE 3**: Reset auto-increment counters (11 ALTER TABLE statements)
- Prepares for fresh admissions from ID 1
- Ensures reg numbers start at MAV26001

**PHASE 4**: Verification queries (commented out for reference)
- You can run these manually to confirm success

---

## File Location

**File**: `DB/migration_deploy_namecheap.sql`
**Size**: ~3 KB
**Type**: SQL script
**Compatibility**: MySQL 5.7+ / MariaDB 10.2+

---

## Deployment Checklist

- [ ] Backup database in phpMyAdmin
- [ ] Copy migration script content
- [ ] Access phpMyAdmin on Namecheap
- [ ] Paste SQL in SQL tab
- [ ] Click Go to execute
- [ ] Check for errors
- [ ] Run verification queries
- [ ] Update local Config.php to production
- [ ] Test login in admin.php
- [ ] Begin fresh admissions at MAV26001
- [ ] Archive backup file securely

---

## Success Indicators

After running the script, you should see:

✓ learners table empty (COUNT = 0)
✓ New columns in learner_promotions visible
✓ Auto-increment reset to 1
✓ No error messages in phpMyAdmin
✓ First new learner gets MAV26001 registration
✓ Promotion view shows no pending/history records
✓ Financial records empty (clean slate)

---

*Last Updated: 2026-07-22*
*For: MAVELA Government High School Management System*
