# Database Overview

The LinkProx database is a relational MySQL database. The schema is in `database.sql` in the project root.

## Core Domains

### 1. Lookup / Reference Tables
- `roles`, `permissions`, `role_permissions`
- `emirates`, `areas`, `countries`, `languages`, `currencies`
- `business_types`, `pricing_models`, `measurement_units`
- `catalog_categories` — **global** (not company-scoped). Has a `type` field: `'product'` or `'service'`.

### 2. Users & Auth
- `users` — core account (email, full_name, `password_hash` for bcrypt via PHP `password_hash` / `password_verify`, token_version for global logout)
- `user_sessions` — JWT token log
- `otp_codes`, `password_reset_tokens`

### 3. Customer
- `customers` — linked to `users.id`
- `customer_addresses`
- `authorized_agents` — customers who can act on behalf of others

### 4. Company
- `companies` — the business entity (name, logo, address, status)
- `company_legal_information` — trade license, legal rep name
- `company_business_hours`
- `company_documents` — uploaded verification docs
- `company_staff` — employees linked to a company (full_name, official_title)
- `company_deactivation_requests` — pending deactivation requests reviewed by admin

### 5. Catalog
- `catalog_categories` — global; `type = 'product'|'service'`
- `products` — company-scoped products
- `product_media`, `product_specifications`
- `services` — company-scoped services
- `service_media` — stored as JSON or linked rows

### 6. Requests
- `requests` — the inquiry. Fields: `request_no`, `status`, `type` (direct/broadcast), `company_id`, `customer_id`, `customer_address_id`
- `request_target_companies` — for broadcast requests
- `request_logs` — audit trail

### 7. Quotations
- `quotations` — links `request_id` → `company_id`. Fields: `subtotal`, `vat_amount`, `grand_total`, `status`
- `quotation_items` — line items (title, qty, rate, line_total)
- `quotation_milestones` — payment milestones (title, percentage, calculated_amount)
- `quotation_attachments` — uploaded PDF files

### 8. Contracts
- `contracts` — the main entity. Key fields:
  - `contract_no` — e.g. `CT-2026-XXXX`
  - `status` — see Contract Statuses below
  - `current_version_no` — the accepted version
  - `signing_representative_id` → `company_staff.id`
  - `site_engineer_id` → `company_staff.id`
  - `project_name`, `total_amount`
  - `signed_at`, `activated_at`, `completed_at`, `terminated_at`
- `contract_versions` — each edit creates a new version row. Fields:
  - `version_no` — incrementing integer
  - `review_status` — `draft | sent_for_review | accepted | rejected` (company **Send** in `contract_review.php` only when latest row is `draft`; sending sets `sent_for_review`)
  - `revision_reason` — text from client when requesting changes
  - `scope_of_work`, `technical_specifications`, `excluded_scope`
  - `subtotal`, `vat_percent`, `vat_amount`, `grand_total`
  - `execution_duration_value`, `execution_duration_unit`
  - `delay_penalty_enabled`, `delay_penalty_type`, `delay_penalty_value`
  - `sent_for_review_at`
- `contract_version_items` — line items for a version
- `contract_version_item_subitems` — sub-items (for measurement-based items)
- `contract_version_milestones` — payment milestones for a version
- `contract_termination_requests` — termination workflow: `contract_id`, `system_scenario`, suggested refund/outstanding amounts, optional `adjustment_amount` / `adjustment_reason`, `status` (`pending` \| `approved` \| `rejected` \| `finalized`), timestamps (`approved_at`, `finalized_at`)

### 9. Payments
- `payments` — milestone payment records (proof files, status)

### 9b. Execution (contract delivery stages)
- `execution_stages` — per-contract ordered stages (`sequence_no`, `title`, `status`, optional `milestone_id`)
- `execution_stage_updates`, `execution_stage_reviews` — progress and customer review
- `execution_delays` — delay log; approved extension requests append rows with `delay_type` like `extension_request:client_instruction_change`
- `execution_stage_extension_requests` — deadline extension workflow (reason `material_delay` / `client_instruction_change` / `force_majeure`, proposed dates, `pending_customer` until customer **POST** review). Attachments use `attachments.entity_type = execution_stage_extension`.

### 10. Chat / Messaging
- `chat_messages` — messages linked to `request_id`. Fields: `sender_id`, `sender_role`, `message`, `created_at`

---

## Contract Status Values
| Status | Meaning |
|---|---|
| `draft` | Generated but not yet sent |
| `under_review` | Sent to client, awaiting approval |
| `revision_requested` | Client asked for changes |
| `pending_signature` | Client approved, waiting for digital signatures |
| `active` | Signed and in execution |
| `completed` | All milestones done |
| `cancelled` | Cancelled |
| `termination_pending` | Termination approved; settlement reconciliation not yet finalized |
| `terminated` | Closed after termination (see `terminated_at`) |
| `archived` | Archived record |

## Request Status Values
`new → viewed → quoted → contracted → completed | cancelled`

## Database Guidelines
- All foreign keys enforce referential integrity (`ON DELETE CASCADE` or `ON DELETE SET NULL`).
- `created_at` and `updated_at` are standard MySQL timestamps.
- Soft deletes use `deleted_at DATETIME NULL` columns.
- Never hard-delete contracts or quotations — archive them instead.