# Database Quick Reference

## File Location Guide

📍 **In your CURA workspace** (`C:\Users\RichmondBonah\Documents\K&A\Laravel\CURA\cura-app\`):

| Document | Purpose | When to Use |
|----------|---------|-------------|
| `DATABASE_SCHEMA.md` | Complete schema reference | Understanding table structure |
| `DATABASE_MANAGEMENT.md` | Operational guide | Running migrations, troubleshooting |
| `SCHEMA_CONSOLIDATION_SUMMARY.md` | Summary of changes | Understanding what was done |
| This file | Quick reference | Fast lookups |

## Key Database Concepts

### The Two "Jobs" Tables

```
jobs table:
├── Created by: Laravel (framework)
├── Purpose: Queue system for background tasks
├── Columns: id, queue, payload, attempts, reserved_at, available_at, created_at
├── Use case: Email, notifications, scheduled tasks
└── ⚠️ DO NOT MODIFY

job_postings table:
├── Created by: CURA application
├── Purpose: Job listings posted by employers
├── Columns: id, employer_id, title, description, salary_min, salary_max, ...
├── Use case: Nurses searching and applying for jobs
└── ✅ USE THIS FOR JOB LISTINGS
```

## Quick Command Reference

```bash
# Migrations
php artisan migrate                                    # Run all migrations
php artisan migrate:fresh                              # Start fresh
php artisan migrate:rollback                           # Undo last batch
php artisan migrate:status                             # View migration status
php artisan make:migration create_table_name           # Create new migration

# Validation
php artisan db:seed --class=ValidateDatabaseSchemaSeeder   # Validate schema

# Testing
php artisan tinker                                     # Interactive shell
php artisan db:seed                                    # Run all seeders

# Refresh
php artisan migrate:refresh                            # Rollback and migrate
php artisan migrate:fresh --seed                       # Fresh + seed
```

## Table Quick Reference

### Users & Identity
```
users
├── id (PK)
├── email (UNIQUE)
├── password
├── role (nurse | employer | admin)
├── is_active (boolean)
└── timestamps

nurse_profiles (1:1 with users)
├── id (PK)
├── user_id (FK → users)
├── license_number
├── specialties
├── years_of_experience
└── timestamps

employers (1:1 with users)
├── id (PK)
├── user_id (FK → users)
├── company_name
├── industry
├── verification_status
└── timestamps
```

### Job Management
```
job_postings (MAIN JOBS TABLE)
├── id (PK)
├── employer_id (FK → employers)
├── title
├── description
├── status (draft | published | closed)
├── salary_min, salary_max
├── location_country, location_state, location_city
├── employment_type (full-time | part-time | contract)
├── published_at
└── timestamps

job_applications
├── id (PK)
├── nurse_id (FK → users)
├── job_posting_id (FK → job_postings)
├── status (applied | reviewed | rejected | accepted)
├── applied_at
└── timestamps

saved_jobs (Bookmarks)
├── id (PK)
├── user_id (FK → users)
├── job_posting_id (FK → job_postings)
└── timestamps
```

### Communication
```
conversations
├── id (PK)
├── initiator_id (FK → users)
├── status (active | archived)
└── timestamps

messages
├── id (PK)
├── conversation_id (FK → conversations)
├── sender_id (FK → users)
├── content
├── is_read
└── timestamps
```

### Forum/Posts
```
nurse_posts
├── id (PK)
├── nurse_id (FK → users)
├── category_id (FK → forum_categories)
├── title
├── content
├── status (draft | published)
└── timestamps

nurse_post_comments
├── id (PK)
├── post_id (FK → nurse_posts)
├── nurse_id (FK → users)
├── content
└── timestamps
```

## Common Queries

### Find Jobs Posted by Employer
```php
$employer = Employer::find($id);
$jobs = $employer->jobPostings;  // via relationship

// Or direct query
$jobs = JobPosting::where('employer_id', $id)->get();
```

### Find Applications for a Job
```php
$job = JobPosting::find($id);
$applications = $job->applications;  // via relationship

// Or direct query
$apps = JobApplication::where('job_posting_id', $id)->get();
```

### Find Saved Jobs for a User
```php
$user = User::find($id);
$saved = $user->savedJobs;  // via relationship

// Or direct query
$saved = SavedJob::where('user_id', $id)->get();
```

### Find User Messages
```php
$user = User::find($id);
$messages = Message::where('sender_id', $id)->get();

// Or via conversation
$conversation = Conversation::find($id);
$messages = $conversation->messages;
```

## Common Issues & Solutions

### Issue: Foreign Key Constraint Error
```
"Cannot add or update a child row: a foreign key constraint fails"
```

**Solution**:
1. Check referenced table exists: `Schema::hasTable('table_name')`
2. Verify data types match (use bigint unsigned)
3. Check for orphaned records
4. Run validation: `php artisan db:seed --class=ValidateDatabaseSchemaSeeder`

### Issue: Duplicate Email Error
```
"UNIQUE constraint failed: users.email"
```

**Solution**:
- Email must be unique; check for existing records
- Use `orFail()` or check before creating

### Issue: Missing Column
```
"SQLSTATE[42S22]: Column not found"
```

**Solution**:
1. Run pending migrations: `php artisan migrate`
2. Check migration file exists and is valid
3. Verify column name matches exactly

### Issue: Connection Refused
```
"SQLSTATE[HY000]: General error: 2006 MySQL server has gone away"
```

**Solution**:
- Ensure MySQL/database is running
- Check `.env` database credentials
- Verify database exists

## Index Reference

### What's Indexed
```
job_postings:
├── employer_id (FK)
├── status
├── published_at
├── location_country
└── Composite: (status, published_at)

users:
├── email (UNIQUE)
├── role
└── Composite: (role, is_active)

job_applications:
├── nurse_id (FK)
├── job_posting_id (FK)
└── Composite: (status, applied_at)

saved_jobs:
├── user_id (FK)
├── job_posting_id (FK)
└── Composite: (user_id, job_posting_id)
```

## Foreign Key Cascade Rules

```
CASCADE = Delete child when parent deleted
SET NULL = Set NULL when parent deleted

job_postings.employer_id → employers
├── Rule: CASCADE
└── Effect: Delete all jobs when employer deleted

job_applications.job_posting_id → job_postings
├── Rule: CASCADE
└── Effect: Delete all applications when job deleted

nurse_posts.category_id → forum_categories
├── Rule: SET NULL
└── Effect: Keep post, remove category link if category deleted
```

## Model Relationships

```
// User
User::with('nurseProfile')           // Has one
User::with('employer')               // Has one
User::with('jobApplications')        // Has many
User::with('savedJobs')              // Has many through
User::with('sentMessages')           // Has many
User::with('conversations')          // Many to many

// JobPosting
JobPosting::with('employer')         // Belongs to
JobPosting::with('applications')     // Has many

// Employer
Employer::with('jobPostings')        // Has many

// NurseProfile
NurseProfile::with('user')           // Belongs to
```

## Performance Tips

✅ **Do**:
- Use eager loading: `User::with('jobApplications')->get()`
- Use indexes on filter columns
- Limit results: `->limit(10)->get()`
- Use pagination: `->paginate(25)`
- Cache frequently accessed data

❌ **Don't**:
- Use N+1 queries (load relationships in loops)
- Query without indexes
- Fetch all data without limit
- Delete cascade without checking dependencies
- Run heavy queries in loops

## Backup & Recovery

```bash
# Backup database
mysqldump -u username -p database_name > backup.sql

# Restore from backup
mysql -u username -p database_name < backup.sql

# Backup via Laravel
php artisan db:backup  # if package installed
```

## Monitoring

```bash
# Check table sizes
SELECT table_name, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'cura_db'
ORDER BY size_mb DESC;

# Check slow queries
SHOW FULL PROCESSLIST;

# Check indexes
SHOW INDEX FROM job_postings;
```

## File Structure

```
cura-app/
├── app/
│   └── Models/              ← Eloquent models
│       ├── User.php
│       ├── JobPosting.php
│       ├── JobApplication.php
│       ├── Employer.php
│       ├── NurseProfile.php
│       └── ...
├── database/
│   ├── migrations/          ← All migration files
│   │   ├── 0001_01_01_000000_create_users_table.php
│   │   ├── 2025_11_30_025828_create_jobs_table.php (DEPRECATED)
│   │   ├── 2025_12_05_000001_consolidate_jobs_table.php ✅ NEW
│   │   ├── 2025_12_05_000002_fix_foreign_keys_and_constraints.php ✅ NEW
│   │   ├── 2025_12_05_000003_ensure_schema_integrity.php ✅ NEW
│   │   └── 2025_12_05_000004_final_schema_optimization.php ✅ NEW
│   └── seeders/
│       └── ValidateDatabaseSchemaSeeder.php ✅ NEW
├── DATABASE_SCHEMA.md ✅ NEW (Complete reference)
├── DATABASE_MANAGEMENT.md ✅ NEW (Operational guide)
└── SCHEMA_CONSOLIDATION_SUMMARY.md ✅ NEW (Summary)
```

## Learning Path

1. **Start Here**: Read this quick reference
2. **Understand Structure**: Read `DATABASE_SCHEMA.md`
3. **Learn Operations**: Read `DATABASE_MANAGEMENT.md`
4. **Validate Setup**: Run `ValidateDatabaseSchemaSeeder`
5. **Review Code**: Check migration files
6. **Explore Models**: Browse `app/Models/`

## Still Need Help?

| Question | File to Check |
|----------|---------------|
| "What columns does this table have?" | `DATABASE_SCHEMA.md` |
| "How do I run migrations?" | `DATABASE_MANAGEMENT.md` → Quick Start |
| "Why are there two jobs tables?" | This file → Key Database Concepts |
| "How do I find a bug?" | `DATABASE_MANAGEMENT.md` → Troubleshooting |
| "What changed?" | `SCHEMA_CONSOLIDATION_SUMMARY.md` |

---

**Last Updated**: 2025-12-05  
**Status**: Ready for production