# Final Database Schema Documentation
# Based on comprehensive analysis of ALL migrations in the project

## TABLE: users
Primary user accounts table with roles and office assignments.

Columns:
- id (PK)
- name (string)
- username (string, unique, nullable)
- first_name (string, nullable)
- last_name (string, nullable)
- email (string, unique)
- email_verified_at (timestamp, nullable)
- password (string)
- avatar (text)
- remember_token (string, nullable)
- role (string, 50 chars, nullable)
- is_active (boolean, default: true)
- office_name (string, nullable) - references offices.name
- created_at, updated_at (timestamps)

## TABLE: offices
Organizational units and offices in the system.

Columns:
- id (PK)
- name (string, unique)
- name_en (string, unique, nullable)
- name_kh (string, nullable)
- code (string, 50 chars, unique)
- description (string, 500 chars, nullable)
- is_active (boolean, default: true)
- is_faculty (boolean, default: false)
- created_at, updated_at (timestamps)

## TABLE: faculties
Dedicated faculty records separated from offices.

Columns:
- id (PK)
- office_id (FK to offices.id, unique, nullable)
- name (string, unique)
- name_en (string, nullable)
- name_kh (string, nullable)
- code (string, 50 chars, unique)
- description (string, 500 chars, nullable)
- is_active (boolean, default: true)
- head_reviewer_user_id (FK to users.id, nullable)
- created_at, updated_at (timestamps)

## TABLE: document_types
Document types that can be requested in the workflow.

Columns:
- id (PK)
- name (string)
- name_en (string, nullable)
- name_kh (string, nullable)
- code (string, 100 chars, unique)
- sla_days (unsigned integer, default: 7)
- workflow_template_id (FK to workflow_templates, nullable)
- is_active (boolean, default: true)
- template_pdf_path (string, nullable)
- form_schema (json, nullable)
- form_data (json, nullable)
- required_files (json, nullable)
- allowed_submitter_roles (json, nullable)
- created_at, updated_at (timestamps)

## TABLE: workflow_templates
Defines the workflow steps and stages for document processing.

Columns:
- id (PK)
- name (string)
- description (text, nullable)
- document_type (string, nullable, with comment)
- office_name (string, nullable) - references offices.name
- steps (json, nullable, with comment - "Ordered array of workflow steps")
- is_active (boolean, default: true)
- created_at, updated_at (timestamps)

## TABLE: document_requests
Main table tracking document requests through the workflow.

Columns:
- id (PK)
- user_id (FK to users, cascade on delete)
- document_name (string)
- document_type (string, nullable)
- description (longText, nullable)
- main_file_path (string, nullable)
- supporting_files_paths (longText, nullable)
- form_data (json, nullable)
- required_files (json, nullable)
- workflow_template_id (FK to workflow_templates, nullable)
- office_name (string, nullable)
- status (enum: 'pending', 'approved', 'rejected', default: 'pending')
- workflow_state (string, default: 'submitted')
- current_holder_user_id (FK to users, nullable)
- current_holder_role (string, nullable)
- current_workflow_step_order (unsigned integer, nullable)
- workflow_steps_override (json, nullable)
- last_action_note (longText, nullable)
- created_at, updated_at (timestamps)

## TABLE: document_request_histories
Audit trail and history of actions on each request.

Columns:
- id (PK)
- requester_request_id (FK to document_requests, cascade on delete)
- parent_id (unsigned bigInteger, nullable, self-FK)
- action (string)
- from_state (string, nullable)
- to_state (string)
- actor_user_id (FK to users, nullable)
- actor_role (string, nullable)
- note (longText, nullable)
- meta (json, nullable)
- is_read (boolean, default: false)
- created_at, updated_at (timestamps)

## TABLE: role_permissions
Permissions and admin capabilities per role.

Columns:
- id (PK)
- role (string, 100 chars, unique)
- admin_permissions (json, nullable)
- created_at, updated_at (timestamps)

## Removed Framework Tables

The following default Laravel tables are restored and available again for the
framework/auth/queue features:

- password_resets
- personal_access_tokens
- failed_jobs