# DrJobs Employer Module - Schema Design

## Design Principles

- **Company-Centric**: Company is the primary tenant; users belong to one company
- **Simple 1:N Relationship**: One user belongs to one company, one company has many users
- **Single Source of Truth**: No data duplication across collections
- **DocumentDB Optimized**: Designed for Amazon DocumentDB (MongoDB-compatible)
- **Scalability**: Designed for millions of records with proper indexing
- **Clean Architecture**: Clear separation of concerns

---

## Naming Conventions

| Element | Convention | Example |
|---------|------------|---------|
| Collections | `snake_case`, plural | `users`, `companies`, `jobs` |
| Fields | `snake_case` | `created_at`, `company_id`, `is_active` |
| Boolean fields | `is_` or `has_` prefix | `is_active`, `is_verified`, `has_2fa_enabled` |
| Foreign keys | `{entity}_id` | `company_id`, `user_id`, `role_id` |
| Timestamps | `{action}_at` (ISODate) | `created_at`, `verified_at`, `joined_at` |
| Status fields | Enum strings | `status: "active"` |

---

## Data Types

| Type | MongoDB/DocumentDB | Example |
|------|-------------------|---------|
| Primary Key | `ObjectId` | `_id: ObjectId("...")` |
| Integer ID | `Integer` (Int32) | `id: 12345` |
| String | `String` | `name: "Company Name"` |
| Boolean | `Boolean` | `is_active: true` |
| Decimal | `Decimal128` | `price: Decimal128("99.99")` |
| Date/Time | `ISODate` | `created_at: ISODate("2026-01-19T10:30:00Z")` |
| Object/JSON | `Object` | `features: { ... }` |

> **Important**: All datetime fields (`created_at`, `updated_at`, `joined_at`, `last_login_at`, `expires_at`, etc.) must be stored as **ISODate** format in UTC timezone.

---

## ID Strategy

**Decision**: Use **integer auto-increment IDs** as the primary identifier for all entities.

**Rationale**:
- Consistent across all collections
- Easier to reference in APIs and logs
- Better for human readability
- MongoDB `_id` (ObjectId) retained for internal MongoDB operations

```javascript
// Every collection follows this pattern
{
  "_id": ObjectId("..."),     // MongoDB internal ID
  "id": 12345,                // Application-level primary key
  // ... other fields
}
```

---

## Entity Relationship Diagram

> **Note**: In the ERD below, `_id` fields are shown as `string` due to Mermaid syntax limitations. In the actual MongoDB/DocumentDB schema, these are `ObjectId` types.

```mermaid
erDiagram
    companies ||--o{ users : has_users
    companies ||--|| subscriptions : has_subscription
    companies ||--o| billing_details : has_billing
    companies ||--o{ jobs : posts_jobs
    
    users }o--|| roles : has_role
    users ||--o{ user_permissions : has_permissions
    users ||--o{ auth_tokens : has_tokens
    users ||--o{ password_resets : has_resets
    
    roles ||--o{ role_permissions : has_permissions
    permissions ||--o{ role_permissions : in_roles
    permissions ||--o{ user_permissions : granted_to
    
    subscriptions }o--|| subscription_plans : based_on
    subscriptions ||--o{ payments : has_payments

    companies {
        string _id PK
        int id UK
        string name
        string name_ar
        string slug UK
        string logo_url
        string cover_image_url
        string website
        string industry
        int employee_count
        int founded_year
        string description
        string description_ar
        int country_id
        int city_id
        string referral_source
        string referred_by
        string status
        boolean is_verified
        datetime verified_at
        datetime created_at
        datetime updated_at
    }

    users {
        string _id PK
        int id UK
        int company_id FK
        int role_id FK
        int invited_by_id FK
        string email UK
        string password_hash
        string phone
        string phone_country_code
        string first_name
        string last_name
        string avatar_url
        string designation
        string employee_code
        boolean is_owner
        boolean is_deleted
        string status
        boolean is_email_verified
        boolean has_2fa_enabled
        string two_fa_secret
        datetime joined_at
        datetime last_login_at
        datetime created_at
        datetime updated_at
    }

    roles {
        string _id PK
        int id UK
        string name
        string slug UK
        string description
        boolean is_system
        boolean is_active
        datetime created_at
        datetime updated_at
    }

    permissions {
        string _id PK
        int id UK
        string name
        string slug UK
        string module
        string action
        string description
        boolean is_active
        datetime created_at
        datetime updated_at
    }

    role_permissions {
        string _id PK
        int role_id FK
        int permission_id FK
        datetime created_at
    }

    user_permissions {
        string _id PK
        int user_id FK
        int permission_id FK
        int granted_by_id FK
        boolean is_granted
        datetime created_at
    }

    subscriptions {
        string _id PK
        int id UK
        int company_id FK
        int plan_id FK
        json feature_usage
        datetime starts_at
        datetime ends_at
        string status
        datetime cancelled_at
        string cancellation_reason
        datetime created_at
        datetime updated_at
    }

    subscription_plans {
        string _id PK
        int id UK
        string name
        string slug UK
        decimal price
        string currency
        int duration_days
        json features
        string description
        boolean is_active
        boolean is_featured
        int sort_order
        datetime created_at
        datetime updated_at
    }

    billing_details {
        string _id PK
        int id UK
        int company_id FK
        string contact_name
        string business_name
        string email
        string phone
        string address_line_1
        string address_line_2
        string city
        string state
        string country
        string postal_code
        string tax_id
        datetime created_at
        datetime updated_at
    }

    payments {
        string _id PK
        int id UK
        int company_id FK
        int user_id FK
        int subscription_id FK
        int plan_id FK
        string provider
        string provider_payment_id
        string provider_subscription_id
        decimal amount
        string currency
        string status
        json provider_data
        string invoice_number
        string invoice_url
        datetime paid_at
        datetime created_at
        datetime updated_at
    }

    jobs {
        string _id PK
        int id UK
        int company_id FK
        int created_by_id FK
        string title
        string description
        json requirements
        string status
        datetime posted_at
        datetime expires_at
        datetime created_at
        datetime updated_at
    }

    auth_tokens {
        string _id PK
        int user_id FK
        string token_hash
        string type
        string ip_address
        string user_agent
        datetime expires_at
        datetime created_at
    }

    password_resets {
        string _id PK
        int user_id FK
        string token_hash
        datetime expires_at
        datetime used_at
        datetime created_at
    }
```

---

## Collection Schemas

### 1. `companies` - Primary Tenant Entity

```javascript
{
  "_id": ObjectId,
  "id": Integer,                    // Auto-increment PK
  
  // Profile
  "name": String,
  "name_ar": String,
  "slug": String,                   // Unique URL identifier
  "logo_url": String,
  "cover_image_url": String,
  "website": String,
  "industry": String,
  "employee_count": Integer,
  "founded_year": Integer,
  "description": String,
  "description_ar": String,
  
  // Location
  "country_id": Integer,
  "city_id": Integer,
  
  // Acquisition
  "referral_source": String,
  "referred_by": String,
  
  // Status
  "status": String,                 // "active" | "inactive" | "suspended"
  "is_verified": Boolean,
  "verified_at": ISODate,
  
  // Audit
  "created_at": ISODate,
  "updated_at": ISODate
}

// Indexes
{ "id": 1 }                         // unique
{ "slug": 1 }                       // unique
{ "status": 1 }
```

---

### 2. `users` - All Users (Owners + Members)

```javascript
{
  "_id": ObjectId,
  "id": Integer,
  
  // Company Relationship
  "company_id": Integer,            // FK → companies.id
  "role_id": Integer,               // FK → roles.id
  "is_owner": Boolean,              // true = company owner
  
  // Authentication
  "email": String,                  // Unique
  "password_hash": String,
  "phone": String,
  "phone_country_code": String,
  
  // Profile
  "first_name": String,
  "last_name": String,
  "avatar_url": String,
  "designation": String,            // Job title within company
  "employee_code": String,          // Internal employee ID
  
  // Status
  "status": String,                 // "active" | "invited" | "suspended"
  "is_email_verified": Boolean,
  "is_deleted": Boolean,
  
  // 2FA
  "has_2fa_enabled": Boolean,
  "two_fa_secret": String,
  
  // Audit
  "invited_by_id": Integer,         // FK → users.id (who invited this user)
  "joined_at": ISODate,
  "last_login_at": ISODate,
  "created_at": ISODate,
  "updated_at": ISODate
}

// Indexes
{ "id": 1 }                         // unique
{ "email": 1 }                      // unique
{ "company_id": 1, "status": 1 }    // list company users
{ "company_id": 1, "is_owner": 1 }  // find company owner
{ "company_id": 1, "is_deleted": 1 } // filter deleted users
{ "role_id": 1 }                    // role-based queries
```

---

### 3. `roles` - Role Definitions

```javascript
{
  "_id": ObjectId,
  "id": Integer,
  "name": String,
  "slug": String,
  "description": String,
  "is_system": Boolean,
  "is_active": Boolean,
  "created_at": ISODate,
  "updated_at": ISODate
}

// Seed Data
[
  { "id": 1, "slug": "owner", "name": "Owner", "is_system": true, "is_active": true },
  { "id": 2, "slug": "admin", "name": "Admin", "is_system": true, "is_active": true },
  { "id": 3, "slug": "recruiter", "name": "Recruiter", "is_system": true, "is_active": true },
  { "id": 4, "slug": "hiring-manager", "name": "Hiring Manager", "is_system": true, "is_active": true },
  { "id": 5, "slug": "member", "name": "Member", "is_system": true, "is_active": true }
]

// Indexes
{ "id": 1 }                         // unique
{ "slug": 1 }                       // unique
{ "is_active": 1 }
```

---

### 4. `permissions` - Permission Definitions

```javascript
{
  "_id": ObjectId,
  "id": Integer,
  "name": String,
  "slug": String,                   // "jobs.create", "candidates.view"
  "module": String,                 // "jobs", "candidates", "interviews"
  "action": String,                 // "create", "read", "update", "delete"
  "description": String,
  "is_active": Boolean,
  "created_at": ISODate,
  "updated_at": ISODate
}

// Indexes
{ "id": 1 }                         // unique
{ "slug": 1 }                       // unique
{ "module": 1 }
{ "is_active": 1 }
```

---

### 5. `role_permissions` - Default Role Permissions

```javascript
{
  "_id": ObjectId,
  "role_id": Integer,
  "permission_id": Integer,
  "created_at": ISODate
}

// Indexes
{ "role_id": 1, "permission_id": 1 }  // unique
{ "role_id": 1 }
```

---

### 6. `user_permissions` - User-Specific Permission Overrides

```javascript
{
  "_id": ObjectId,
  "user_id": Integer,               // FK → users.id
  "permission_id": Integer,         // FK → permissions.id
  "granted_by_id": Integer,         // FK → users.id
  "is_granted": Boolean,            // true = grant, false = revoke
  "created_at": ISODate
}

// Indexes
{ "user_id": 1, "permission_id": 1 }  // unique
{ "user_id": 1 }
```

---

### 7. `subscriptions` - Company Subscriptions

```javascript
{
  "_id": ObjectId,
  "id": Integer,
  "company_id": Integer,
  "plan_id": Integer,
  
  // Feature Usage (embedded)
  "feature_usage": {
    "active_jobs": { "limit": 10, "used": 3 },
    "ai_searches": { "limit": 100, "used": 45 },
    "video_interviews": { "limit": 20, "used": 5 },
    "resume_parsing": { "limit": 50, "used": 10 }
  },
  
  // Period
  "starts_at": ISODate,
  "ends_at": ISODate,
  
  // Status
  "status": String,                 // "active" | "cancelled" | "expired"
  "cancelled_at": ISODate,
  "cancellation_reason": String,
  
  // Audit
  "created_at": ISODate,
  "updated_at": ISODate
}

// Indexes
{ "id": 1 }                         // unique
{ "company_id": 1, "status": 1 }
{ "ends_at": 1 }
```

---

### 8. `subscription_plans` - Plan Templates

```javascript
{
  "_id": ObjectId,
  "id": Integer,
  "name": String,
  "slug": String,
  "price": Decimal128,
  "currency": String,
  "duration_days": Integer,
  
  "features": {
    "active_jobs": 10,
    "ai_searches": 100,
    "video_interviews": 20,
    "resume_parsing": 50,
    "ai_screening": true,
    "analytics": true
  },
  
  "description": String,
  "is_active": Boolean,
  "is_featured": Boolean,
  "sort_order": Integer,
  "created_at": ISODate,
  "updated_at": ISODate
}

// Indexes
{ "id": 1 }                         // unique
{ "slug": 1 }                       // unique
{ "is_active": 1, "sort_order": 1 }
```

---

### 9. `billing_details` - Company Billing Info

```javascript
{
  "_id": ObjectId,
  "id": Integer,
  "company_id": Integer,
  
  "contact_name": String,
  "business_name": String,
  "email": String,
  "phone": String,
  
  "address_line_1": String,
  "address_line_2": String,
  "city": String,
  "state": String,
  "country": String,
  "postal_code": String,
  "tax_id": String,
  
  "created_at": ISODate,
  "updated_at": ISODate
}

// Indexes
{ "id": 1 }                         // unique
{ "company_id": 1 }                 // unique
```

---

### 10. `payments` - Payment Transactions

```javascript
{
  "_id": ObjectId,
  "id": Integer,
  "company_id": Integer,
  "user_id": Integer,               // Who made the payment
  "subscription_id": Integer,       // FK → subscriptions.id
  "plan_id": Integer,
  
  "provider": String,               // "paypal" | "telr"
  "provider_payment_id": String,
  "provider_subscription_id": String,
  
  "amount": Decimal128,
  "currency": String,
  "status": String,                 // "pending" | "completed" | "failed" | "refunded"
  
  "provider_data": Object,
  "invoice_number": String,
  "invoice_url": String,
  
  "paid_at": ISODate,
  "created_at": ISODate,
  "updated_at": ISODate
}

// Indexes
{ "id": 1 }                         // unique
{ "company_id": 1 }
{ "subscription_id": 1 }
{ "provider_payment_id": 1 }
{ "status": 1 }
```

---

### 11. `jobs` - Job Postings

```javascript
{
  "_id": ObjectId,
  "id": Integer,
  "company_id": Integer,
  "created_by_id": Integer,         // FK → users.id
  
  "title": String,
  "description": String,
  "requirements": Object,
  
  "status": String,                 // "draft" | "active" | "closed" | "expired"
  "posted_at": ISODate,
  "expires_at": ISODate,
  
  "created_at": ISODate,
  "updated_at": ISODate
}

// Indexes
{ "id": 1 }                         // unique
{ "company_id": 1, "status": 1 }
{ "status": 1, "expires_at": 1 }
```

---

### 12. `auth_tokens` - JWT/Session Tokens

```javascript
{
  "_id": ObjectId,
  "user_id": Integer,
  "token_hash": String,
  "type": String,                   // "access" | "refresh"
  "ip_address": String,
  "user_agent": String,
  "expires_at": ISODate,
  "created_at": ISODate
}

// Indexes
{ "token_hash": 1 }
{ "user_id": 1 }
{ "expires_at": 1 }                 // TTL index
```

---

### 13. `password_resets` - Password Reset Tokens

```javascript
{
  "_id": ObjectId,
  "user_id": Integer,
  "token_hash": String,
  "expires_at": ISODate,
  "used_at": ISODate,
  "created_at": ISODate
}

// Indexes
{ "token_hash": 1 }
{ "expires_at": 1 }                 // TTL index
```

---

## Key Relationships

| Relationship | Type | Foreign Key | Description |
|--------------|------|-------------|-------------|
| Company → Users | 1:N | `users.company_id` | Company has many users |
| User → Company | N:1 | `users.company_id` | User belongs to one company |
| User → Role | N:1 | `users.role_id` | User has one role |
| Company → Billing | 1:1 | `billing_details.company_id` | Company billing info |
| Company → Subscription | 1:1 | `subscriptions.company_id` | One active subscription |
| Company → Jobs | 1:N | `jobs.company_id` | Company posts many jobs |
| Subscription → Payments | 1:N | `payments.subscription_id` | Subscription has many payments |
| Role → Permissions | N:M | `role_permissions` | Default permissions |
| User → Permissions | 1:N | `user_permissions` | Custom overrides |
| User → Auth Tokens | 1:N | `auth_tokens.user_id` | Login sessions |
| User → Password Resets | 1:N | `password_resets.user_id` | Reset tokens |
| Subscription → Plan | N:1 | `subscriptions.plan_id` | Based on plan template |

---

## Status Enums

```php
class CompanyStatus {
    const ACTIVE = 'active';
    const INACTIVE = 'inactive';
    const SUSPENDED = 'suspended';
}

class UserStatus {
    const ACTIVE = 'active';
    const INVITED = 'invited';
    const SUSPENDED = 'suspended';
}

class SubscriptionStatus {
    const ACTIVE = 'active';
    const CANCELLED = 'cancelled';
    const EXPIRED = 'expired';
}

class PaymentStatus {
    const PENDING = 'pending';
    const COMPLETED = 'completed';
    const FAILED = 'failed';
    const REFUNDED = 'refunded';
}

class JobStatus {
    const DRAFT = 'draft';
    const ACTIVE = 'active';
    const CLOSED = 'closed';
    const EXPIRED = 'expired';
}

class AuthTokenType {
    const ACCESS = 'access';
    const REFRESH = 'refresh';
}
```

---

## Migration Mapping

| Current Collection | New Collection | Notes |
|-------------------|----------------|-------|
| `master_employers` | `users` | Merged with members |
| `members` | `users` | Single user table |
| `companies` | `companies` | Primary entity |
| `user_member_mappings` | Removed | Replaced by `users.company_id` |
| `emp_roles` | `roles` | Renamed |
| `emp_permissions` | `permissions` | Renamed |
| `member_permissions` | `user_permissions` | Simplified |
| `company_subscriptions` | `subscriptions` | Renamed |
| `company_billing_details` | `billing_details` | Renamed |
| `verification_tokens` | `password_resets` + `auth_tokens` | Split by purpose |

---

## Collection Summary

| # | Collection | Purpose | Key Indexes |
|---|------------|---------|-------------|
| 1 | `companies` | Primary tenant entity | `id`, `slug`, `status` |
| 2 | `users` | All users (owners + members) | `id`, `email`, `company_id`, `role_id` |
| 3 | `roles` | Role definitions | `id`, `slug`, `is_active` |
| 4 | `permissions` | Permission definitions | `id`, `slug`, `module`, `is_active` |
| 5 | `role_permissions` | Default role permissions | `role_id + permission_id` |
| 6 | `user_permissions` | User permission overrides | `user_id + permission_id` |
| 7 | `subscriptions` | Company subscriptions | `id`, `company_id + status` |
| 8 | `subscription_plans` | Plan templates | `id`, `slug` |
| 9 | `billing_details` | Company billing info | `id`, `company_id` |
| 10 | `payments` | Payment transactions | `id`, `subscription_id`, `status` |
| 11 | `jobs` | Job postings | `id`, `company_id + status` |
| 12 | `auth_tokens` | JWT/Session tokens | `token_hash`, `user_id` |
| 13 | `password_resets` | Password reset tokens | `token_hash` |

---

## Common Queries

```javascript
// Get company with owner
db.companies.aggregate([
  { $match: { id: companyId } },
  { $lookup: {
      from: "users",
      let: { cid: "$id" },
      pipeline: [
        { $match: { $expr: { $and: [
          { $eq: ["$company_id", "$$cid"] },
          { $eq: ["$is_owner", true] },
          { $eq: ["$is_deleted", false] }
        ]}}}
      ],
      as: "owner"
  }},
  { $unwind: "$owner" }
])

// Get all active users in a company
db.users.find({ 
  company_id: companyId, 
  status: "active",
  is_deleted: false 
})

// Get user with company and role
db.users.aggregate([
  { $match: { id: userId, is_deleted: false } },
  { $lookup: { from: "companies", localField: "company_id", foreignField: "id", as: "company" }},
  { $lookup: { from: "roles", localField: "role_id", foreignField: "id", as: "role" }},
  { $unwind: "$company" },
  { $unwind: "$role" }
])

// Check user permission (with role fallback)
function hasPermission(userId, permissionSlug) {
  // 1. Check user-specific permission override
  var userPerm = db.user_permissions.findOne({ 
    user_id: userId, 
    permission_id: db.permissions.findOne({ slug: permissionSlug }).id 
  });
  if (userPerm) return userPerm.is_granted;
  
  // 2. Fall back to role permission
  var user = db.users.findOne({ id: userId });
  return db.role_permissions.findOne({ 
    role_id: user.role_id, 
    permission_id: db.permissions.findOne({ slug: permissionSlug }).id 
  }) !== null;
}

// Get company subscription with plan details
db.subscriptions.aggregate([
  { $match: { company_id: companyId, status: "active" } },
  { $lookup: { from: "subscription_plans", localField: "plan_id", foreignField: "id", as: "plan" }},
  { $unwind: "$plan" }
])
```

---