# MVNexus - Database Schema Documentation

## Entity Relationship Diagram

### Core Entities Overview

```mermaid
erDiagram
    companies ||--o{ departments : contains
    companies ||--o{ users : employs
    companies ||--o{ company_settings : has
    
    departments ||--o{ department_user : has_members
    departments ||--o{ department_settings : has
    departments ||--o{ tickets : contains
    departments ||--o{ categories : has
    departments ||--o{ sla_policies : has
    departments ||--o{ ticket_statuses : has
    departments ||--o{ ticket_priorities : has
    
    users ||--o{ department_user : belongs_to
    users ||--o{ auth_sessions : has
    users ||--o{ login_otps : requests
    users ||--o{ user_invites : invited_by
    users }o--o{ roles : has
    
    roles }o--o{ permissions : has
    roles ||--o{ role_scopes : has_scope
    
    categories ||--o{ subcategories : contains
    categories ||--o{ routing_rules : has
    
    tickets ||--|| ticket_sla_tracking : tracks
    tickets ||--o{ ticket_messages : has
    tickets ||--o{ ticket_assignments : has
    tickets ||--o{ ticket_status_history : has
    tickets ||--o{ attachments : has
    
    ticket_sla_tracking ||--o{ sla_breach_events : generates
    
    ticket_messages ||--o{ ticket_mentions : has
    ticket_messages ||--o{ attachments : has
    
    sla_policies ||--o{ ticket_sla_tracking : monitors
    sla_policies ||--o{ subcategories : applies_to
    
    users ||--o{ tickets : creates
    users ||--o{ tickets : assigned_to
    users ||--o{ ticket_messages : writes
    users ||--o{ audit_logs : performs
```

---

## Detailed Table Schemas

### Identity & Access Management

#### companies
```sql
CREATE TABLE companies (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    email_domain VARCHAR(255),
    logo_url VARCHAR(500),
    is_active BOOLEAN DEFAULT TRUE,
    settings JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    INDEX idx_companies_code (code),
    INDEX idx_companies_is_active (is_active),
    INDEX idx_companies_deleted_at (deleted_at)
);
```

**Columns:**
- `id`: UUID primary key
- `name`: Company name (e.g., "Acme Corporation")
- `code`: Unique short code for ticket numbering (e.g., "ACME")
- `email_domain`: Email domain for auto-assignment (e.g., "acme.com")
- `logo_url`: Company logo URL
- `is_active`: Soft activation flag
- `settings`: JSON blob for company-level settings
- `deleted_at`: Soft delete timestamp

---

#### departments
```sql
CREATE TABLE departments (
    id CHAR(36) PRIMARY KEY,
    company_id CHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    settings JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    
    UNIQUE KEY uk_departments_company_code (company_id, code),
    INDEX idx_departments_company_id (company_id),
    INDEX idx_departments_is_active (is_active),
    INDEX idx_departments_deleted_at (deleted_at)
);
```

**Columns:**
- `id`: UUID primary key
- `company_id`: Foreign key to companies
- `name`: Department name (e.g., "IT Infrastructure")
- `code`: Short code for ticket numbering (e.g., "INFRA")
- `description`: Department description
- `is_active`: Soft activation flag
- `settings`: JSON blob for department-level settings overrides
- `deleted_at`: Soft delete timestamp

---

#### users
```sql
CREATE TABLE users (
    id CHAR(36) PRIMARY KEY,
    company_id CHAR(36) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(500),
    is_super_admin BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    locale VARCHAR(10) DEFAULT 'en',
    timezone VARCHAR(50) DEFAULT 'UTC',
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    
    INDEX idx_users_company_id (company_id),
    INDEX idx_users_email (email),
    INDEX idx_users_is_super_admin (is_super_admin),
    INDEX idx_users_is_active (is_active),
    INDEX idx_users_deleted_at (deleted_at)
);
```

**Columns:**
- `id`: UUID primary key
- `company_id`: Foreign key to companies
- `email`: User email address (unique across system)
- `name`: User full name
- `avatar_url`: User profile picture URL
- `is_super_admin`: Global super admin flag
- `is_active`: Account active status
- `locale`: User language preference (en/ar)
- `timezone`: User timezone
- `last_login_at`: Last successful login timestamp
- `deleted_at`: Soft delete timestamp

---

#### login_otps
```sql
CREATE TABLE login_otps (
    id CHAR(36) PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    otp_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    used_at TIMESTAMP NULL,
    attempts TINYINT UNSIGNED DEFAULT 0,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_login_otps_email (email),
    INDEX idx_login_otps_expires_at (expires_at),
    INDEX idx_login_otps_used_at (used_at)
);
```

**Purpose:** Short-lived hashed OTP codes for email login. Plaintext OTP is never stored; rows are invalidated after use or expiry.

---

#### auth_magic_links
```sql
CREATE TABLE auth_magic_links (
    id CHAR(36) PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    used_at TIMESTAMP NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_auth_magic_links_email (email),
    INDEX idx_auth_magic_links_expires_at (expires_at),
    INDEX idx_auth_magic_links_used_at (used_at)
);
```

**Purpose:** One-time magic-link tokens (hashed) emailed to users for passwordless sign-in.

---

#### auth_sessions
```sql
CREATE TABLE auth_sessions (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    token_id BIGINT UNSIGNED NULL,
    session_id VARCHAR(255) NULL,
    device_name VARCHAR(255),
    device_fingerprint_hash VARCHAR(255),
    ip_address VARCHAR(45),
    user_agent TEXT,
    last_used_at TIMESTAMP NULL,
    trusted_until TIMESTAMP NULL,
    revoked_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    
    INDEX idx_auth_sessions_user_revoked (user_id, revoked_at),
    INDEX idx_auth_sessions_token_id (token_id),
    INDEX idx_auth_sessions_session_id (session_id),
    INDEX idx_auth_sessions_device_fingerprint (device_fingerprint_hash)
);
```

**Purpose:** Tracks active sign-in devices/sessions for Sanctum SPA auth, trusted-device expiry, and user-initiated revocation.

---

#### user_invites
```sql
CREATE TABLE user_invites (
    id CHAR(36) PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    invited_by_id CHAR(36) NOT NULL,
    company_id CHAR(36) NOT NULL,
    department_id CHAR(36) NULL,
    role_name VARCHAR(255),
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    accepted_at TIMESTAMP NULL,
    revoked_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (invited_by_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
    
    INDEX idx_user_invites_email (email),
    INDEX idx_user_invites_expires_at (expires_at)
);
```

**Purpose:** Admin-issued onboarding invites; acceptance creates/links the user account before first OTP or magic-link login.

---

#### sso_identities — LEGACY (not implemented)

> **Status:** Table exists from migration `2026_01_01_000004_create_sso_identities_table.php` only. **No OAuth/Microsoft Entra runtime code.** Do not configure Entra unless implementing future SSO. Do not drop without a planned migration.

```sql
CREATE TABLE sso_identities (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    provider VARCHAR(50) NOT NULL,
    provider_user_id VARCHAR(255) NOT NULL,
    provider_data JSON,
    access_token TEXT,
    refresh_token TEXT,
    token_expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    
    UNIQUE KEY uk_sso_provider_user (provider, provider_user_id),
    INDEX idx_sso_user_id (user_id),
    INDEX idx_sso_provider (provider)
);
```

**Columns (reserved for future SSO):**
- `provider` / `provider_user_id`: External IdP identity
- `provider_data`, `access_token`, `refresh_token`: Would hold OAuth tokens if SSO were implemented

---

#### department_user
```sql
CREATE TABLE department_user (
    id CHAR(36) PRIMARY KEY,
    department_id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    role VARCHAR(50) NOT NULL DEFAULT 'agent',
    is_manager BOOLEAN DEFAULT FALSE,
    visibility_settings JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    
    UNIQUE KEY uk_department_user (department_id, user_id),
    INDEX idx_department_user_department_id (department_id),
    INDEX idx_department_user_user_id (user_id),
    INDEX idx_department_user_is_manager (is_manager)
);
```

**Columns:**
- `id`: UUID primary key
- `department_id`: Foreign key to departments
- `user_id`: Foreign key to users
- `role`: User role in department (agent, manager, viewer)
- `is_manager`: Quick flag for manager status
- `visibility_settings`: JSON configuration for ticket visibility rules

**Visibility Settings JSON Example:**
```json
{
  "see_all_tickets": false,
  "see_unassigned_tickets": true,
  "see_assigned_tickets": true,
  "see_team_tickets": false
}
```

---

#### roles (Spatie Permission)
```sql
CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    guard_name VARCHAR(255) NOT NULL,
    scope VARCHAR(50) NOT NULL DEFAULT 'global',
    scope_id CHAR(36) NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_roles_name_guard_scope (name, guard_name, scope, scope_id),
    INDEX idx_roles_scope (scope),
    INDEX idx_roles_scope_id (scope_id)
);
```

**Columns:**
- `id`: Auto-increment primary key (Spatie standard)
- `name`: Role name (e.g., "super_admin", "company_admin", "department_manager")
- `guard_name`: Authentication guard (default: "web")
- `scope`: Role scope (global, company, department)
- `scope_id`: UUID of company or department (NULL for global)
- `description`: Human-readable description

**Example Roles:**
```sql
-- Global roles
(1, 'super_admin', 'web', 'global', NULL, 'System Super Administrator')
(2, 'platform_manager', 'web', 'global', NULL, 'Platform Manager')

-- Company roles (scope_id = company UUID)
(3, 'company_admin', 'web', 'company', '...uuid...', 'Company Administrator')
(4, 'company_viewer', 'web', 'company', '...uuid...', 'Company Viewer')

-- Department roles (scope_id = department UUID)
(5, 'department_manager', 'web', 'department', '...uuid...', 'Department Manager')
(6, 'agent', 'web', 'department', '...uuid...', 'Support Agent')
```

---

#### permissions (Spatie Permission)
```sql
CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    guard_name VARCHAR(255) NOT NULL,
    scope VARCHAR(50) NOT NULL DEFAULT 'global',
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_permissions_name_guard_scope (name, guard_name, scope),
    INDEX idx_permissions_scope (scope)
);
```

**Example Permissions:**
```sql
-- Global permissions
'companies.manage'
'companies.view'
'global.audit.view'
'global.settings.manage'

-- Company permissions
'company.departments.manage'
'company.users.manage'
'company.reports.view'
'company.settings.manage'

-- Department permissions
'department.tickets.view'
'department.tickets.create'
'department.tickets.assign'
'department.tickets.close'
'department.categories.manage'
'department.sla.manage'
'department.settings.manage'
```

---

#### model_has_roles (Spatie Permission)
```sql
CREATE TABLE model_has_roles (
    role_id BIGINT UNSIGNED NOT NULL,
    model_type VARCHAR(255) NOT NULL,
    model_id CHAR(36) NOT NULL,
    
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    
    PRIMARY KEY (role_id, model_id, model_type),
    INDEX idx_model_has_roles_model (model_type, model_id)
);
```

---

#### role_has_permissions (Spatie Permission)
```sql
CREATE TABLE role_has_permissions (
    permission_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    
    PRIMARY KEY (permission_id, role_id)
);
```

---

### Settings Management

#### global_settings
```sql
CREATE TABLE global_settings (
    id CHAR(36) PRIMARY KEY,
    category VARCHAR(100) NOT NULL,
    key VARCHAR(255) NOT NULL,
    value JSON NOT NULL,
    description TEXT,
    is_locked BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_global_settings_category_key (category, key),
    INDEX idx_global_settings_category (category)
);
```

**Categories:**
- `ticket_statuses`
- `ticket_priorities`
- `sla_policies`
- `assignment_rules`
- `notification_templates`

---

#### company_settings
```sql
CREATE TABLE company_settings (
    id CHAR(36) PRIMARY KEY,
    company_id CHAR(36) NOT NULL,
    category VARCHAR(100) NOT NULL,
    key VARCHAR(255) NOT NULL,
    value JSON NOT NULL,
    overrides_global BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    
    UNIQUE KEY uk_company_settings_company_category_key (company_id, category, key),
    INDEX idx_company_settings_company_id (company_id),
    INDEX idx_company_settings_category (category)
);
```

---

#### department_settings
```sql
CREATE TABLE department_settings (
    id CHAR(36) PRIMARY KEY,
    department_id CHAR(36) NOT NULL,
    category VARCHAR(100) NOT NULL,
    key VARCHAR(255) NOT NULL,
    value JSON NOT NULL,
    overrides_company BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    
    UNIQUE KEY uk_department_settings_department_category_key (department_id, category, key),
    INDEX idx_department_settings_department_id (department_id),
    INDEX idx_department_settings_category (category)
);
```

---

### Ticketing System

#### ticket_statuses
```sql
CREATE TABLE ticket_statuses (
    id CHAR(36) PRIMARY KEY,
    department_id CHAR(36) NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100) NOT NULL,
    color VARCHAR(20) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    is_closed BOOLEAN DEFAULT FALSE,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    
    INDEX idx_ticket_statuses_department_id (department_id),
    INDEX idx_ticket_statuses_sort_order (sort_order),
    INDEX idx_ticket_statuses_deleted_at (deleted_at)
);
```

---

#### ticket_priorities
```sql
CREATE TABLE ticket_priorities (
    id CHAR(36) PRIMARY KEY,
    department_id CHAR(36) NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100) NOT NULL,
    color VARCHAR(20) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    
    INDEX idx_ticket_priorities_department_id (department_id),
    INDEX idx_ticket_priorities_sort_order (sort_order),
    INDEX idx_ticket_priorities_deleted_at (deleted_at)
);
```

---

#### categories
```sql
CREATE TABLE categories (
    id CHAR(36) PRIMARY KEY,
    department_id CHAR(36) NOT NULL,
    name_en VARCHAR(255) NOT NULL,
    name_ar VARCHAR(255) NOT NULL,
    description_en TEXT,
    description_ar TEXT,
    icon VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    
    INDEX idx_categories_department_id (department_id),
    INDEX idx_categories_is_active (is_active),
    INDEX idx_categories_sort_order (sort_order),
    INDEX idx_categories_deleted_at (deleted_at)
);
```

---

#### subcategories
```sql
CREATE TABLE subcategories (
    id CHAR(36) PRIMARY KEY,
    category_id CHAR(36) NOT NULL,
    sla_policy_id CHAR(36) NULL,
    name_en VARCHAR(255) NOT NULL,
    name_ar VARCHAR(255) NOT NULL,
    description_en TEXT,
    description_ar TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    FOREIGN KEY (sla_policy_id) REFERENCES sla_policies(id) ON DELETE SET NULL,
    
    INDEX idx_subcategories_category_id (category_id),
    INDEX idx_subcategories_sla_policy_id (sla_policy_id),
    INDEX idx_subcategories_is_active (is_active),
    INDEX idx_subcategories_sort_order (sort_order),
    INDEX idx_subcategories_deleted_at (deleted_at)
);
```

---

#### routing_rules
```sql
CREATE TABLE routing_rules (
    id CHAR(36) PRIMARY KEY,
    category_id CHAR(36) NOT NULL,
    subcategory_id CHAR(36) NULL,
    default_assignee_id CHAR(36) NULL,
    fallback_manager_id CHAR(36) NULL,
    conditions JSON,
    priority INT NOT NULL DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    FOREIGN KEY (subcategory_id) REFERENCES subcategories(id) ON DELETE CASCADE,
    FOREIGN KEY (default_assignee_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (fallback_manager_id) REFERENCES users(id) ON DELETE SET NULL,
    
    INDEX idx_routing_rules_category_id (category_id),
    INDEX idx_routing_rules_subcategory_id (subcategory_id),
    INDEX idx_routing_rules_priority (priority),
    INDEX idx_routing_rules_is_active (is_active)
);
```

---

#### tickets
```sql
CREATE TABLE tickets (
    id CHAR(36) PRIMARY KEY,
    ticket_number VARCHAR(100) NOT NULL UNIQUE,
    department_id CHAR(36) NOT NULL,
    requester_id CHAR(36) NOT NULL,
    category_id CHAR(36) NULL,
    subcategory_id CHAR(36) NULL,
    ticket_status_id CHAR(36) NOT NULL,
    ticket_priority_id CHAR(36) NOT NULL,
    current_assignee_id CHAR(36) NULL,
    subject VARCHAR(500) NOT NULL,
    description TEXT NOT NULL,
    first_response_at TIMESTAMP NULL,
    resolved_at TIMESTAMP NULL,
    closed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    FOREIGN KEY (requester_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    FOREIGN KEY (subcategory_id) REFERENCES subcategories(id) ON DELETE SET NULL,
    FOREIGN KEY (ticket_status_id) REFERENCES ticket_statuses(id),
    FOREIGN KEY (ticket_priority_id) REFERENCES ticket_priorities(id),
    FOREIGN KEY (current_assignee_id) REFERENCES users(id) ON DELETE SET NULL,
    
    INDEX idx_tickets_ticket_number (ticket_number),
    INDEX idx_tickets_department_id (department_id),
    INDEX idx_tickets_requester_id (requester_id),
    INDEX idx_tickets_category_id (category_id),
    INDEX idx_tickets_subcategory_id (subcategory_id),
    INDEX idx_tickets_ticket_status_id (ticket_status_id),
    INDEX idx_tickets_ticket_priority_id (ticket_priority_id),
    INDEX idx_tickets_current_assignee_id (current_assignee_id),
    INDEX idx_tickets_created_at (created_at),
    INDEX idx_tickets_deleted_at (deleted_at),
    
    FULLTEXT INDEX ft_tickets_subject_description (subject, description)
);
```

**Ticket Number Format:** `MHD-{COMPANY_CODE}-{DEPT_CODE}-{YEAR}-{SEQUENCE}`
**Example:** `MHD-ACME-INFRA-2026-000042`

---

#### ticket_assignments
```sql
CREATE TABLE ticket_assignments (
    id CHAR(36) PRIMARY KEY,
    ticket_id CHAR(36) NOT NULL,
    assigned_by_id CHAR(36) NOT NULL,
    assigned_to_id CHAR(36) NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_to_id) REFERENCES users(id) ON DELETE CASCADE,
    
    INDEX idx_ticket_assignments_ticket_id (ticket_id),
    INDEX idx_ticket_assignments_assigned_to_id (assigned_to_id),
    INDEX idx_ticket_assignments_created_at (created_at)
);
```

---

#### ticket_status_history
```sql
CREATE TABLE ticket_status_history (
    id CHAR(36) PRIMARY KEY,
    ticket_id CHAR(36) NOT NULL,
    changed_by_id CHAR(36) NOT NULL,
    from_status_id CHAR(36) NULL,
    to_status_id CHAR(36) NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (from_status_id) REFERENCES ticket_statuses(id) ON DELETE SET NULL,
    FOREIGN KEY (to_status_id) REFERENCES ticket_statuses(id) ON DELETE SET NULL,
    
    INDEX idx_ticket_status_history_ticket_id (ticket_id),
    INDEX idx_ticket_status_history_created_at (created_at)
);
```

---

### SLA Management

#### sla_policies
```sql
CREATE TABLE sla_policies (
    id CHAR(36) PRIMARY KEY,
    department_id CHAR(36) NOT NULL,
    name_en VARCHAR(255) NOT NULL,
    name_ar VARCHAR(255) NOT NULL,
    description_en TEXT,
    description_ar TEXT,
    first_response_hours INT NOT NULL,
    resolution_hours INT NOT NULL,
    business_hours_only BOOLEAN DEFAULT TRUE,
    escalation_rules JSON,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    
    INDEX idx_sla_policies_department_id (department_id),
    INDEX idx_sla_policies_is_active (is_active),
    INDEX idx_sla_policies_deleted_at (deleted_at)
);
```

**Escalation Rules JSON Example:**
```json
{
  "response": [
    {"at_percentage": 50, "notify": ["manager"]},
    {"at_percentage": 80, "notify": ["manager", "department_head"]}
  ],
  "resolution": [
    {"at_percentage": 75, "notify": ["manager"]},
    {"at_percentage": 100, "notify": ["manager", "department_head", "company_admin"]}
  ]
}
```

---

#### ticket_sla_tracking
```sql
CREATE TABLE ticket_sla_tracking (
    id CHAR(36) PRIMARY KEY,
    ticket_id CHAR(36) NOT NULL UNIQUE,
    sla_policy_id CHAR(36) NOT NULL,
    response_due_at TIMESTAMP NOT NULL,
    resolution_due_at TIMESTAMP NOT NULL,
    first_response_at TIMESTAMP NULL,
    resolved_at TIMESTAMP NULL,
    response_breached BOOLEAN DEFAULT FALSE,
    resolution_breached BOOLEAN DEFAULT FALSE,
    response_time_minutes INT NULL,
    resolution_time_minutes INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (sla_policy_id) REFERENCES sla_policies(id) ON DELETE CASCADE,
    
    INDEX idx_ticket_sla_tracking_sla_policy_id (sla_policy_id),
    INDEX idx_ticket_sla_tracking_response_due_at (response_due_at),
    INDEX idx_ticket_sla_tracking_resolution_due_at (resolution_due_at),
    INDEX idx_ticket_sla_tracking_response_breached (response_breached),
    INDEX idx_ticket_sla_tracking_resolution_breached (resolution_breached)
);
```

---

#### sla_breach_events
```sql
CREATE TABLE sla_breach_events (
    id CHAR(36) PRIMARY KEY,
    ticket_sla_tracking_id CHAR(36) NOT NULL,
    ticket_id CHAR(36) NOT NULL,
    sla_policy_id CHAR(36) NOT NULL,
    breach_type ENUM('response', 'resolution') NOT NULL,
    breached_at TIMESTAMP NOT NULL,
    breach_duration_minutes INT NOT NULL,
    escalation_data JSON,
    escalation_sent BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ticket_sla_tracking_id) REFERENCES ticket_sla_tracking(id) ON DELETE CASCADE,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (sla_policy_id) REFERENCES sla_policies(id) ON DELETE CASCADE,
    
    INDEX idx_sla_breach_events_tracking_id (ticket_sla_tracking_id),
    INDEX idx_sla_breach_events_ticket_id (ticket_id),
    INDEX idx_sla_breach_events_sla_policy_id (sla_policy_id),
    INDEX idx_sla_breach_events_breach_type (breach_type),
    INDEX idx_sla_breach_events_breached_at (breached_at),
    INDEX idx_sla_breach_events_escalation_sent (escalation_sent)
);
```

**Full SLA Traceability:**
- `ticket_sla_tracking_id`: Links back to the tracking record
- `ticket_id`: Direct link to ticket (for quick queries)
- `sla_policy_id`: Direct link to policy (for reporting)
- `breach_duration_minutes`: How long after due time the breach occurred
- `escalation_data`: JSON with notification details and recipients

---

### Communication

#### ticket_messages
```sql
CREATE TABLE ticket_messages (
    id CHAR(36) PRIMARY KEY,
    ticket_id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    type ENUM('public_reply', 'private_note', 'system_event') NOT NULL DEFAULT 'public_reply',
    content TEXT NOT NULL,
    is_internal BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    
    INDEX idx_ticket_messages_ticket_id (ticket_id),
    INDEX idx_ticket_messages_user_id (user_id),
    INDEX idx_ticket_messages_type (type),
    INDEX idx_ticket_messages_created_at (created_at),
    INDEX idx_ticket_messages_deleted_at (deleted_at)
);
```

---

#### ticket_mentions
```sql
CREATE TABLE ticket_mentions (
    id CHAR(36) PRIMARY KEY,
    message_id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (message_id) REFERENCES ticket_messages(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    
    UNIQUE KEY uk_ticket_mentions_message_user (message_id, user_id),
    INDEX idx_ticket_mentions_user_id (user_id),
    INDEX idx_ticket_mentions_is_read (is_read)
);
```

---

#### attachments
```sql
CREATE TABLE attachments (
    id CHAR(36) PRIMARY KEY,
    attachable_type VARCHAR(255) NOT NULL,
    attachable_id CHAR(36) NOT NULL,
    uploaded_by_id CHAR(36) NOT NULL,
    filename VARCHAR(500) NOT NULL,
    original_filename VARCHAR(500) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    size INT UNSIGNED NOT NULL,
    path VARCHAR(1000) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (uploaded_by_id) REFERENCES users(id) ON DELETE CASCADE,
    
    INDEX idx_attachments_attachable (attachable_type, attachable_id),
    INDEX idx_attachments_uploaded_by_id (uploaded_by_id),
    INDEX idx_attachments_created_at (created_at),
    INDEX idx_attachments_deleted_at (deleted_at)
);
```

**Attachable Types:**
- `App\Modules\Ticketing\Models\Ticket`
- `App\Modules\Replies\Models\TicketMessage`

---

### Notifications

#### notification_templates
```sql
CREATE TABLE notification_templates (
    id CHAR(36) PRIMARY KEY,
    department_id CHAR(36) NULL,
    event_type VARCHAR(100) NOT NULL,
    channel VARCHAR(50) NOT NULL DEFAULT 'email',
    subject_en VARCHAR(500) NOT NULL,
    subject_ar VARCHAR(500) NOT NULL,
    body_en TEXT NOT NULL,
    body_ar TEXT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    
    INDEX idx_notification_templates_department_id (department_id),
    INDEX idx_notification_templates_event_type (event_type),
    INDEX idx_notification_templates_is_active (is_active)
);
```

**Event Types:**
- `ticket.created`
- `ticket.assigned`
- `ticket.status_changed`
- `ticket.reply_added`
- `user.mentioned`
- `sla.breached`
- `sla.escalation`

---

#### notification_logs
```sql
CREATE TABLE notification_logs (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    channel VARCHAR(50) NOT NULL,
    data JSON NOT NULL,
    status ENUM('pending', 'sent', 'failed') NOT NULL DEFAULT 'pending',
    error_message TEXT NULL,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    
    INDEX idx_notification_logs_user_id (user_id),
    INDEX idx_notification_logs_event_type (event_type),
    INDEX idx_notification_logs_status (status),
    INDEX idx_notification_logs_created_at (created_at)
);
```

---

### Audit & Search

#### audit_logs
```sql
CREATE TABLE audit_logs (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NULL,
    company_id CHAR(36) NULL,
    department_id CHAR(36) NULL,
    auditable_type VARCHAR(255) NOT NULL,
    auditable_id CHAR(36) NOT NULL,
    event VARCHAR(50) NOT NULL,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
    
    INDEX idx_audit_logs_user_id (user_id),
    INDEX idx_audit_logs_company_id (company_id),
    INDEX idx_audit_logs_department_id (department_id),
    INDEX idx_audit_logs_auditable (auditable_type, auditable_id),
    INDEX idx_audit_logs_event (event),
    INDEX idx_audit_logs_created_at (created_at)
);
```

**Events:**
- `created`, `updated`, `deleted`
- `assigned`, `status_changed`, `priority_changed`
- `closed`, `reopened`

---

#### saved_views
```sql
CREATE TABLE saved_views (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    department_id CHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    filters JSON NOT NULL,
    columns JSON,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    
    INDEX idx_saved_views_user_id (user_id),
    INDEX idx_saved_views_department_id (department_id),
    INDEX idx_saved_views_is_default (is_default)
);
```

---

#### bulk_action_logs
```sql
CREATE TABLE bulk_action_logs (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    action_type VARCHAR(100) NOT NULL,
    ticket_ids JSON NOT NULL,
    parameters JSON NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
    results JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    
    INDEX idx_bulk_action_logs_user_id (user_id),
    INDEX idx_bulk_action_logs_action_type (action_type),
    INDEX idx_bulk_action_logs_status (status),
    INDEX idx_bulk_action_logs_created_at (created_at)
);
```

---

## Database Indexes Strategy

### Primary Indexes
- All primary keys are indexed automatically
- All foreign keys have dedicated indexes

### Composite Indexes
- `(department_id, created_at)` on tickets for department-scoped queries
- `(company_id, email_domain)` on companies for domain matching
- `(category_id, subcategory_id)` on tickets for reporting

### Fulltext Indexes
- `(subject, description)` on tickets for search

### Performance Considerations
- UUID indexes use CHAR(36) for consistency
- Soft delete queries always include `deleted_at IS NULL` check
- Date range queries use indexed timestamp columns
- JSON columns are not indexed directly but use MySQL JSON functions

---

## Data Integrity Rules

### Cascading Deletes
- Company deleted → Departments deleted
- Department deleted → Tickets, Categories, Users soft deleted
- Ticket deleted → Messages, Attachments, History deleted
- User deleted → Set to NULL on ticket assignments (preserve history)

### Soft Deletes
- Companies, Departments, Users, Tickets, Categories
- Audit logs and history tables are never soft deleted

### Immutable Records
- Audit logs (no updates/deletes)
- SLA breach events (no updates/deletes)
- Ticket status history (no updates/deletes)
- Ticket assignments history (no updates/deletes)

---

This schema supports all functional requirements while maintaining data integrity, performance, and scalability.