# Database Schema Management Guide

This guide explains how the CURA application database is structured and how to manage migrations and schema integrity.

## Quick Start

### Running Migrations

To apply all migrations to your database:

```bash
php artisan migrate
```

To create a fresh database:

```bash
php artisan migrate:fresh
```

To seed validation data:

```bash
php artisan db:seed --class=ValidateDatabaseSchemaSeeder
```

### Validating Schema

To check if your database schema is correctly set up:

```bash
php artisan db:seed --class=ValidateDatabaseSchemaSeeder
```

This will:
- Verify all critical tables exist
- Check foreign key constraints
- Validate unique constraints
- Confirm indexes are in place
- Report any issues or warnings

## Key Migration Files

### Core Migrations

1. **`0001_01_01_000000_create_users_table.php`**
   - Creates the base `users` table
   - Adds `role` and `is_active` columns

2. **`0001_01_01_000001_create_cache_table.php`**
   - Laravel cache table

3. **`0001_01_01_000002_create_jobs_table.php`**
   - ⚠️ Laravel queue system (NOT for job postings)
   - Keep intact

### Feature Migrations

4. **`2025_11_30_025815_create_nurse_profiles_table.php`**
   - Nurse-specific profile information

5. **`2025_11_30_025824_create_employers_table.php`**
   - Employer organization information

6. **`2025_11_30_025828_create_jobs_table.php`**
   - ❌ **DEPRECATED** - This may conflict with Laravel's queue system
   - **Actual job listings use `job_postings` table**

7. **`2025_11_30_025832_create_job_applications_table.php`**
   - Job applications from nurses

8. **`2025_12_04_051306_create_saved_jobs_table.php`**
   - Bookmarked job listings

### New Schema Integrity Migrations

9. **`2025_12_05_000001_consolidate_jobs_table.php`**
   - Resolves the dual `jobs` table issue
   - Ensures `job_postings` is the primary jobs table
   - Fixes relationships

10. **`2025_12_05_000002_fix_foreign_keys_and_constraints.php`**
    - Adds/ensures all foreign key constraints
    - Prevents orphaned records
    - Enables proper cascade behavior

11. **`2025_12_05_000003_ensure_schema_integrity.php`**
    - Comprehensive integrity checks
    - Fixes missing relationships
    - Validates entire schema

## Table Naming Clarification

### ⚠️ Important: Jobs vs Job Postings

The application has two distinct uses of the word "jobs":

1. **`jobs` table (Laravel Queue System)**
   ```
   Columns: id, queue, payload, attempts, reserved_at, available_at, created_at
   Purpose: Background job processing (email, notifications, etc.)
   Managed by: Laravel framework
   DON'T MODIFY: This is reserved by Laravel
   ```

2. **`job_postings` table (Application Job Listings)**
   ```
   Purpose: Job listings posted by employers
   Used by: Nurses to search and apply
   Should reference: This is the main jobs table for your domain logic
   ```

### Other Job-Related Tables

- **`job_applications`** - Applications to specific job postings
- **`saved_jobs`** - Bookmarked/saved job postings
- **`job_alerts`** - User job search alerts
- **`application_notes`** - Notes on applications

## Model and Table Mapping

```
Eloquent Model          Database Table              Purpose
User                    users                       Base user account
NurseProfile            nurse_profiles              Nurse profile details
Employer                employers                   Employer organization
JobPosting              job_postings               Job listings ⭐
JobApplication          job_applications           Applications to jobs
SavedJob                saved_jobs                 Bookmarked jobs
JobAlert                job_alerts                 Job search alerts
ApplicationNote         application_notes          Application notes

Conversation            conversations              User messages
Message                 messages                   Individual messages
NurseConnection         nurse_connections         Nurse network

NursePost               nurse_posts                Forum posts
NursePostComment        nurse_post_comments        Forum comments
NursePostInteraction    nurse_post_interactions   Post reactions

BlogPost                blog_posts                 Blog articles
BlogCategory            blog_categories            Blog categories

Admin                   admins                     Admin accounts
AdminActivityLog        admin_activity_logs        Admin actions
```

## Common Scenarios

### Adding a New Job Feature

1. Create a migration file:
   ```bash
   php artisan make:migration add_feature_to_job_postings_table
   ```

2. Reference `job_postings` table (not `jobs`):
   ```php
   Schema::table('job_postings', function (Blueprint $table) {
       $table->newColumn(...);
   });
   ```

3. Run migration:
   ```bash
   php artisan migrate
   ```

### Creating a New Relationship

Always use proper foreign keys:

```php
Schema::create('my_table', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')
        ->constrained('users')
        ->onDelete('cascade');
    $table->foreignId('job_posting_id')
        ->constrained('job_postings')
        ->onDelete('cascade');
});
```

### Fixing Foreign Key Errors

If you get a foreign key constraint error:

1. Check the referenced table exists
2. Verify the column name matches
3. Ensure the data type matches (both should be bigint unsigned)
4. Run the schema validation seeder to identify issues

```bash
php artisan db:seed --class=ValidateDatabaseSchemaSeeder
```

### Viewing Current Schema

To see the current state of your database:

```bash
# In Laravel tinker
php artisan tinker
>>> Schema::getTables()
>>> Schema::getColumns('job_postings')
>>> Schema::getIndexes('job_postings')
```

## Foreign Key Relationships

### Important Cascade Rules

- **onDelete('cascade')**: When parent is deleted, all child records are deleted
- **onDelete('set null')**: When parent is deleted, foreign key becomes NULL
- **onUpdate('cascade')**: When parent ID changes, child records update

Examples in CURA:

```php
// When employer deletes account, all their job postings are deleted
$table->foreignId('employer_id')
    ->constrained('employers')
    ->onDelete('cascade');

// When post is deleted, comments are deleted
$table->foreignId('post_id')
    ->constrained('nurse_posts')
    ->onDelete('cascade');

// Category can be deleted, posts remain with NULL category
$table->foreignId('category_id')
    ->constrained('forum_categories')
    ->onDelete('set null');
```

## Indexes for Performance

The schema includes indexes on commonly queried columns:

```php
// Single column indexes
$table->index('user_id');
$table->index('status');
$table->index('created_at');

// Unique indexes
$table->unique(['user_id', 'job_posting_id']);

// Full-text indexes for search
$table->fullText(['title', 'description']);

// Composite indexes for complex queries
$table->index(['employer_id', 'status', 'published_at']);
```

## Monitoring and Maintenance

### Database Size

```sql
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;
```

### Missing Indexes

```sql
SELECT * FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'cura_db'
AND REFERENCED_TABLE_NAME IS NOT NULL;
```

### Constraint Check

```sql
SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'cura_db'
AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME;
```

## Troubleshooting

### Migration Fails with Foreign Key Error

**Problem**: "Cannot add or update a child row: a foreign key constraint fails"

**Solution**:
1. Check if referenced table exists
2. Verify data types match (use bigint unsigned)
3. Check for orphaned records in child table
4. Temporarily disable foreign key checks if needed:
   ```bash
   php artisan tinker
   >>> DB::statement('SET FOREIGN_KEY_CHECKS=0');
   >>> DB::statement('SET FOREIGN_KEY_CHECKS=1');
   ```

### Duplicate Table Issue

**Problem**: Both `jobs` and `job_postings` tables exist

**Solution**: This is NORMAL
- `jobs` = Laravel queue system (keep it)
- `job_postings` = Application job listings (use this)

Run the consolidation migration to ensure clarity:
```bash
php artisan migrate
```

### Schema Out of Sync

**Problem**: Schema doesn't match models

**Solution**:
1. Run validation seeder:
   ```bash
   php artisan db:seed --class=ValidateDatabaseSchemaSeeder
   ```
2. Create migration to fix issues:
   ```bash
   php artisan make:migration fix_schema_issues
   ```
3. Run migration:
   ```bash
   php artisan migrate
   ```

## Best Practices

1. **Always use foreign keys**: Ensures referential integrity
2. **Use cascade deletes carefully**: Only delete related data when appropriate
3. **Index frequently queried columns**: Improves performance
4. **Add unique constraints**: Prevents duplicate data
5. **Document custom column types**: JSON, enums, etc.
6. **Test migrations**: Use `migrate:fresh` before deploying
7. **Keep audit trails**: Use soft deletes when needed
8. **Version your schema**: Use migration timestamps

## Related Documentation

- [DATABASE_SCHEMA.md](./DATABASE_SCHEMA.md) - Complete schema reference
- Laravel Documentation: https://laravel.com/docs/migrations
- Database Design Guide: (internal documentation)

## Support

For schema-related issues:
1. Check the [DATABASE_SCHEMA.md](./DATABASE_SCHEMA.md) file
2. Run the validation seeder
3. Review migration files in `database/migrations/`
4. Check Eloquent model relationships in `app/Models/`