# 🗄️ Database Schema, Archiving Engine & Indexes

## 1. Consolidated Migrations Overview

The application features 11 clean, foundational migrations:

```
0001_01_01_000000_create_system_core_and_users_tables
0001_01_01_000001_create_cache_table
0001_01_01_000002_create_jobs_table
2026_09_16_000001_create_permission_tables
2026_09_16_000002_create_geography_and_facilities_tables
2026_09_16_000003_create_medical_and_pharmacy_directories_tables
2026_09_16_000004_create_product_catalog_tables
2026_09_16_000005_create_visits_and_inventory_tables
2026_09_16_000006_create_activity_logs_table
2026_09_18_000001_create_representative_targets_table
2026_09_19_000001_create_archive_tables
```

---

## 2. Core Tables Specification

### 2.1 `users`
- `id` (`char(26)` ULID, Primary Key)
- `name` (`varchar(255)`)
- `email` (`varchar(255)`, Unique)
- `phone` (`varchar(50)`, Nullable)
- `is_active` (`boolean`, Default: `true`)
- `password`, `two_factor_*`, `remember_token`, `timestamps`, `softDeletes`

### 2.2 `governorates` & `areas`
- `governorates`: `id` (ULID), `name_ar`, `name_en`, `timestamps`, `softDeletes`
- `areas`: `id` (ULID), `governorate_id` (FK), `name_ar`, `name_en`, `is_active`, `timestamps`, `softDeletes`

### 2.3 `doctors` & `doctor_workplaces`
- `doctors`:
  - `id` (ULID PK)
  - `specialty_id` (FK → `specialties.id`)
  - `name`, `classification` (`enum: A+, A, B, C`), `phone`, `email`, `default_cooperation`, `notes`, `is_active`
  - Indexes: `['specialty_id', 'is_active']`, `phone`, `name`
- `doctor_workplaces`:
  - `id` (ULID PK)
  - `doctor_id` (FK), `workplace_type`, `clinic_name`, `area_id` (FK), `hospital_id` (FK), `phone`, `is_primary`
  - Indexes: `['doctor_id', 'is_primary']`, `area_id`

### 2.4 `pharmacies`
- `id` (ULID PK)
- `area_id` (FK → `areas.id`)
- `name`, `pharmacist_name`, `address`, `phone`, `is_hospital_pharmacy`, `hospital_id` (FK), `notes`, `is_active`
- **High-Performance Indexes**:
  - `['area_id', 'is_active']`
  - `area_id` (`pharmacies_area_id_index`)
  - `hospital_id` (`pharmacies_hospital_id_index`)
  - `phone`, `name`

### 2.5 `products`, `product_categories` & `dosage_forms`
- `product_categories`: `id`, `name_ar`, `name_en`
- `dosage_forms`: `id`, `name_ar`, `name_en`, `is_active`
- `products`: `id`, `category_id`, `name_ar`, `name_en`, `dosage_form`, `image_path`, `leaflet_path`, `is_active`

### 2.6 `visits`
Core transactional table for all detailing visits:
- `id` (ULID PK)
- `user_id` (FK → `users.id`)
- `visit_type` (`enum: doctor, doctor_clinic, hospital_doctor, pharmacy, hospital_pharmacy`)
- `visited_at` (`dateTime`)
- `governorate_id`, `area_id`, `hospital_id`, `doctor_id`, `doctor_workplace_id`, `pharmacy_id`
- `doctor_classification_snapshot`, `doctor_cooperation`, `doctor_seen`, `pitch_focus_points`, `pharmacy_feedback`, `pharmacy_order`, `general_notes`, `latitude`, `longitude`
- **Composite Performance Indexes**:
  - `doctor_id` (`visits_doctor_id_index`)
  - `pharmacy_id` (`visits_pharmacy_id_index`)
  - `['user_id', 'visited_at']`
  - `['governorate_id', 'visited_at']`
  - `['area_id', 'visited_at']`
  - `['visited_at', 'visit_type']` (`visits_time_type_idx`)

### 2.7 `visit_doctor_products` & `visit_pharmacy_inventories`
- `visit_doctor_products`:
  - `id`, `visit_id` (FK), `product_id` (FK), `action_type` (`presented`, `sample_given`, `next_target`)
  - **Index**: `product_id` (`vdp_product_id_index`)
- `visit_pharmacy_inventories`:
  - `id`, `visit_id` (FK), `product_id` (FK), `stock_status` (`available`, `out_of_stock`, `near_expiry`)
  - **Indexes**:
    - `product_id` (`vpi_product_id_index`)
    - `['product_id', 'stock_status']` (`vpi_prod_stock_idx`)

### 2.8 `activity_logs`
System operational audit trail:
- `id`, `user_id` (FK), `causer_name`, `action_type`, `module_name`, `description`, `ip_address`, `user_agent`, `details` (JSON), `timestamps`, `softDeletes`
- **High-Scale Pruning & Query Indexes**:
  - `created_at` (`act_logs_created_idx`)
  - `['module_name', 'created_at']` (`act_logs_mod_created_idx`)
  - `['user_id', 'created_at']` (`act_logs_user_created_idx`)

---

## 3. Data Archiving Tables (`2026_09_19_000001_create_archive_tables.php`)

### 3.1 `archive_batches`
Master ledger documenting each archival cycle:
- `id` (`char(26)` ULID PK)
- `period_name` (`varchar(100)`)
- `start_date` (`dateTime`, Index)
- `end_date` (`dateTime`, Index)
- `visits_count`, `doctor_products_count`, `pharmacy_inventories_count` (`unsignedInteger`)
- `snapshot_path` (`varchar(255)`, Sealed local storage path)
- `snapshot_hash` (`varchar(64)`, SHA-256 integrity hash)
- `created_by` (FK → `users.id`)

### 3.2 `archived_visits`
Mirror of `visits` table containing historical, completed years:
- Complete field parity with `visits`.
- Foreign key: `archive_batch_id` (FK → `archive_batches.id`, cascadeOnDelete)
- Indexes:
  - `['archive_batch_id', 'visited_at']`
  - `['user_id', 'visited_at']`
  - `['governorate_id', 'visited_at']`
  - `['doctor_id', 'visited_at']`
  - `['pharmacy_id', 'visited_at']`

### 3.3 `archived_visit_doctor_products` & `archived_visit_pharmacy_inventories`
- `archived_visit_doctor_products`: `id`, `archived_visit_id` (FK), `product_id` (FK), `action_type`.
- `archived_visit_pharmacy_inventories`: `id`, `archived_visit_id` (FK), `product_id` (FK), `stock_status`.

### 3.4 `archived_activity_logs`
Target table populated during automated Laravel 13 monthly log pruning:
- `id`, `user_id`, `causer_name`, `action_type`, `module_name`, `description`, `ip_address`, `user_agent`, `details`, `archived_at`.