# CURA Application Database Schema Documentation

## Overview
This document outlines the complete database schema for the CURA application, clarifying table relationships and resolving naming conventions.

## Core Tables

### Users (Core Identity)
- **Table**: `users`
- **Purpose**: Base user table for both nurses and employers
- **Key Columns**:
  - `id` (PK)
  - `email` (unique)
  - `role` (enum: 'nurse', 'employer', 'admin')
  - `is_active` (boolean)
  - `created_at`, `updated_at`

### Nurse Profiles
- **Table**: `nurse_profiles` (1:1 with users)
- **Purpose**: Extended nurse-specific information
- **Relationships**:
  - `user_id` → `users.id` (UNIQUE, CASCADE)
- **Key Columns**:
  - `license_number`
  - `specialties`
  - `years_of_experience`
  - `certifications`

### Employers
- **Table**: `employers` (1:1 with users)
- **Purpose**: Employer organization information
- **Relationships**:
  - `user_id` → `users.id` (CASCADE)
- **Key Columns**:
  - `company_name`
  - `industry`
  - `size`
  - `logo_url`
  - `verification_status`
  - `reputation_risk` (float)

## Job Management Tables

### Job Postings (PRIMARY)
- **Table**: `job_postings` (not `jobs` - that's Laravel's queue system)
- **Purpose**: All job listings posted by employers
- **Relationships**:
  - `employer_id` → `employers.id` (CASCADE)
- **Key Columns**:
  - `title`, `description`
  - `employment_type`, `work_mode`
  - `location_country`, `location_state`, `location_city`
  - `salary_min`, `salary_max`, `salary_currency`
  - `status` (draft, published, closed)
  - `published_at`, `application_deadline`
  - `benefits` (visa_sponsorship, relocation_package, housing_provided, etc.)
  - `requirements` (license, experience, specialty, exam, language)

### Job Applications
- **Table**: `job_applications`
- **Purpose**: Nurse applications to job postings
- **Relationships**:
  - `nurse_id` → `users.id` (CASCADE)
  - `job_posting_id` → `job_postings.id` (CASCADE)
- **Key Columns**:
  - `status` (applied, reviewed, shortlisted, rejected, accepted)
  - `applied_at`
  - `cover_letter`
  - `notes` (via ApplicationNote)

### Saved Jobs (Bookmarks)
- **Table**: `saved_jobs`
- **Purpose**: Nurses bookmarking job postings
- **Relationships**:
  - `user_id` → `users.id` (CASCADE)
  - `job_posting_id` → `job_postings.id` (CASCADE)
- **Constraints**: UNIQUE(user_id, job_posting_id)

### Job Alerts
- **Table**: `job_alerts`
- **Purpose**: User job search alerts
- **Relationships**:
  - `user_id` → `users.id` (CASCADE)
- **Key Columns**:
  - `title`, `description`
  - `filters` (JSON - specialty, location, employment_type, etc.)
  - `frequency` (daily, weekly, monthly)
  - `is_active`

### Application Notes
- **Table**: `application_notes`
- **Purpose**: Internal notes on job applications
- **Relationships**:
  - `job_application_id` → `job_applications.id` (CASCADE)

## Communication Tables

### Conversations
- **Table**: `conversations`
- **Purpose**: Chat conversations between users
- **Relationships**:
  - `initiator_id` → `users.id` (CASCADE)
- **Key Columns**:
  - `status` (active, archived, blocked)
  - `last_message_at`

### Conversation Participants
- **Table**: `conversation_participants`
- **Purpose**: Track users in each conversation
- **Relationships**:
  - `conversation_id` → `conversations.id` (CASCADE)
  - `participant_id` → `users.id` (CASCADE)

### Messages
- **Table**: `messages`
- **Purpose**: Individual messages in conversations
- **Relationships**:
  - `conversation_id` → `conversations.id` (CASCADE)
  - `sender_id` → `users.id` (CASCADE)
- **Key Columns**:
  - `content`
  - `is_read`
  - `read_at`
  - `created_at`

### Notifications
- **Table**: `notifications`
- **Purpose**: In-app notifications for users
- **Key Columns**:
  - `user_id` → `users.id` (CASCADE)
  - `type` (job_application, message, connection_request, etc.)
  - `notifiable_type`, `notifiable_id` (polymorphic)
  - `is_read`

## Nurse Network (Connections)

### Nurse Connections
- **Table**: `nurse_connections`
- **Purpose**: Network/connections between nurses
- **Relationships**:
  - `nurse_id` → `users.id` (CASCADE)
  - `connected_nurse_id` → `users.id` (CASCADE)
- **Key Columns**:
  - `status` (pending, accepted, blocked)
  - `created_at`

### Profile Likes
- **Table**: `profile_likes`
- **Purpose**: Nurses liking other nurse profiles
- **Relationships**:
  - `nurse_id` → `users.id` (CASCADE)
  - `liked_nurse_id` → `users.id` (CASCADE)

## Forum/Discussion Tables

### Forum Categories
- **Table**: `forum_categories`
- **Purpose**: Organization of forum discussions
- **Key Columns**:
  - `name`, `description`
  - `slug`
  - `is_active`

### Nurse Posts
- **Table**: `nurse_posts`
- **Purpose**: Forum posts from nurses
- **Relationships**:
  - `nurse_id` → `users.id` (CASCADE)
  - `category_id` → `forum_categories.id` (SET NULL)
  - `quoted_post_id` → `nurse_posts.id` (SET NULL - for replies/quotes)
- **Key Columns**:
  - `title`, `content`
  - `is_pinned`, `is_locked`
  - `views_count`, `engagement_score`
  - `status` (draft, published, archived)

### Nurse Post Comments
- **Table**: `nurse_post_comments`
- **Purpose**: Comments on nurse posts
- **Relationships**:
  - `post_id` → `nurse_posts.id` (CASCADE)
  - `nurse_id` → `users.id` (CASCADE)
- **Key Columns**:
  - `content`
  - `is_edited`

### Nurse Post Interactions
- **Table**: `nurse_post_interactions`
- **Purpose**: Like/dislike tracking for posts
- **Relationships**:
  - `post_id` → `nurse_posts.id` (CASCADE)
  - `nurse_id` → `users.id` (CASCADE)
  - `target_id` (optional, for replies)
- **Key Columns**:
  - `interaction_type` (like, dislike, comment)

### Nurse Comment Reactions
- **Table**: `nurse_comment_reactions`
- **Purpose**: Reactions to comments
- **Relationships**:
  - `comment_id` → `nurse_post_comments.id` (CASCADE)
  - `nurse_id` → `users.id` (CASCADE)

## Content Management Tables

### Blog Categories
- **Table**: `blog_categories`
- **Purpose**: Blog post organization
- **Key Columns**:
  - `name`, `slug`
  - `is_active`

### Blog Posts
- **Table**: `blog_posts`
- **Purpose**: Blog/resource content
- **Relationships**:
  - `category_id` → `blog_categories.id` (SET NULL)
- **Key Columns**:
  - `title`, `slug`
  - `content`, `excerpt`
  - `published_at`

### Pages
- **Table**: `pages`
- **Purpose**: Static pages (privacy, terms, etc.)
- **Key Columns**:
  - `title`, `slug`
  - `content`
  - `is_active`

### FAQs
- **Table**: `faqs`
- **Purpose**: Frequently asked questions
- **Key Columns**:
  - `question`, `answer`
  - `category`
  - `order` (display order)

## Reference Data Tables

### Tags
- **Table**: `tags`
- **Purpose**: Flexible tagging system
- **Key Columns**:
  - `name`, `slug`
  - `type` (skill, specialty, interest, etc.)

### Specialties
- **Table**: `specialties`
- **Purpose**: Nursing specialties
- **Key Columns**:
  - `name`, `description`
  - `is_active`

### Countries
- **Table**: `countries`
- **Purpose**: Country reference data
- **Key Columns**:
  - `name`, `code`

## Reporting & Admin Tables

### Employer Ratings
- **Table**: `employer_ratings`
- **Purpose**: User ratings/reviews of employers
- **Relationships**:
  - `user_id` → `users.id` (CASCADE)
  - `employer_id` → `employers.id` (CASCADE)
- **Key Columns**:
  - `rating` (1-5)
  - `review`, `comment`

### Employer Reports
- **Table**: `employer_reports`
- **Purpose**: Flagged/reported employers
- **Relationships**:
  - `reported_by` → `users.id` (CASCADE)
  - `employer_id` → `employers.id` (CASCADE)
- **Key Columns**:
  - `reason`, `description`
  - `status` (pending, reviewed, resolved)

### Content Reports
- **Table**: `content_reports`
- **Purpose**: Reported posts/comments
- **Relationships**:
  - `reported_by` → `users.id` (CASCADE)
- **Key Columns**:
  - `reportable_type`, `reportable_id` (polymorphic)
  - `reason`
  - `status`

### Admins
- **Table**: `admins`
- **Purpose**: Admin user accounts
- **Relationships**:
  - `user_id` → `users.id` (CASCADE)
- **Key Columns**:
  - `role` (super_admin, moderator, support)
  - `permissions` (JSON)

### Admin Activity Logs
- **Table**: `admin_activity_logs`
- **Purpose**: Track admin actions
- **Relationships**:
  - `admin_id` → `admins.id` (CASCADE)
- **Key Columns**:
  - `action`, `description`
  - `loggable_type`, `loggable_id` (polymorphic)

## System Tables

### Settings
- **Table**: `settings`
- **Purpose**: Application configuration
- **Key Columns**:
  - `key`, `value`
  - `group` (mail, queue, storage, etc.)

### Data Requests
- **Table**: `data_requests`
- **Purpose**: GDPR data export requests
- **Relationships**:
  - `user_id` → `users.id` (CASCADE)
- **Key Columns**:
  - `status` (pending, processing, completed, failed)
  - `export_fields` (JSON)
  - `file_path`
  - `requested_at`, `completed_at`

### Nurse Documents
- **Table**: `nurse_documents`
- **Purpose**: Uploaded documents (licenses, certificates)
- **Relationships**:
  - `nurse_id` → `users.id` (CASCADE)
- **Key Columns**:
  - `document_type`
  - `file_path`
  - `expiration_date`
  - `is_verified`

### App Errors
- **Table**: `app_errors`
- **Purpose**: Error logging
- **Key Columns**:
  - `error_type`, `message`
  - `stack_trace`
  - `context` (JSON)

### Queue Health
- **Table**: `queue_health`
- **Purpose**: Queue system monitoring
- **Key Columns**:
  - `queue_name`
  - `pending_jobs`, `failed_jobs`
  - `last_checked_at`

### Jobs (Laravel Queue System)
- **Table**: `jobs`
- **Purpose**: Laravel queue - DO NOT modify
- **Note**: This is NOT for job postings; use `job_postings` instead

## Important Notes

1. **Jobs Table Naming**: 
   - `jobs` = Laravel queue system (reserved)
   - `job_postings` = Application job listings (use this)

2. **Foreign Key Constraints**:
   - All foreign keys use CASCADE delete for data integrity
   - Some use SET NULL for optional relationships

3. **Indexes**:
   - Comprehensive indexes added on frequently queried columns
   - Full-text indexes on searchable content
   - Composite indexes for common query patterns

4. **Soft Deletes**:
   - Many tables support soft deletes for audit trail
   - Use `withTrashed()` in Eloquent when needed

5. **Timestamps**:
   - Most tables have `created_at` and `updated_at`
   - Some have additional date fields (published_at, read_at, etc.)

6. **Polymorphic Relations**:
   - Comments, ratings, reports use polymorphic relationships
   - Allows flexible association with multiple model types

## Data Integrity

All migrations include:
- Foreign key constraints with proper cascade rules
- Unique constraints where applicable
- Check constraints for enums and valid values
- Indexes for query optimization
- Soft deletes for audit trails

## Migration Order

Migrations are timestamped to ensure proper execution order:
1. Core tables (users, roles)
2. Reference data (countries, specialties, tags)
3. Main feature tables (job_postings, nurse_profiles, etc.)
4. Relationship tables (job_applications, saved_jobs, etc.)
5. Optional features (blog, forum, etc.)
6. Admin/reporting tables (admin_activity_logs, reports, etc.)