```md
# Roadmap: Geo-Intelligent MR ↔ Doctor Tracking, Territory Compliance, and Decision Intelligence (PHP + Free Libraries)

## Product goal
A system that ingests Excel field activity, stores it in a database, maps activity to doctors/territories, performs spatial + behavioral analysis (assigned vs visited areas, overlap/intersection, drift), and produces explainable actions (“why they avoided D/E/F and went to A/B/C”, and what to do next).

## 0) Technology baseline (free only)

### Backend (PHP)
- **Framework:** Laravel (queues, jobs, policies, validation, migrations)
- **DB (recommended):** PostgreSQL + PostGIS (best for spatial queries, indexing, polygons, intersections)
- **Alternative:** MySQL 8 spatial (possible, but PostGIS is stronger for polygon operations and performance)
- **Excel import:** PhpSpreadsheet
- **Search / matching:** PHP Levenshtein + custom scoring + optional full-text (Postgres)
- **Queues:** Laravel queue with database driver (free)
- **Auth / RBAC:** Laravel policies + spatie/laravel-permission (free, widely used)

### Frontend
- **Map:** Leaflet
- **Spatial on client:** Turf.js (overlap/intersection, buffers, unions, distance)
- **Charts:** Chart.js or ApexCharts
- **UI:** Laravel Blade or Vue (either is fine)

### Map data
- **Tiles:** OpenStreetMap (respect usage policy; optionally self-host later)
- **Borders:** Import area polygons (GeoJSON/Shapefiles) → PostGIS

## 1) Phase 1 — Requirements, KPIs, and “truth model” (foundation)

### 1.1 Define entities and truth rules (non-negotiable)
**Key rule:** Every visit record must be classified with confidence and geography.

**Deliverables:**
- Entity glossary: MR, Doctor, Area/Territory, Visit, Assignment, Target, Exception
- Area rules:
  - Primary assigned areas (must cover)
  - Secondary/allowed overlap areas (optional)
  - Outside territory (deviation)
- Confidence model (0–100):
  - Source confidence (Excel completeness, columns present)
  - Mapping confidence (doctor match score)
  - Geo confidence (GPS present vs inferred vs manual)
- KPI definitions (minimum):
  - Territory compliance score
  - Coverage score (visited target doctors / expected doctors)
  - Doctor neglect risk (days since last visit weighted by priority)
  - Travel inefficiency score
  - Drift score (directional or repeated deviation)

**Acceptance criteria:**
For any visit row, system can state:
- “Mapped doctor = X (confidence Y)”
- “Area classification = in-primary / in-secondary / outside”
- “Included/excluded from KPIs based on confidence thresholds”

## 2) Phase 2 — Database + spatial model (make geography first-class)

### 2.1 Core tables (minimum viable schema)
- **doctors:** id, name, specialty, clinic, priority, geom_point, area_id (optional)
- **mrs:** id, name, designation, home_point, active
- **areas:** id, name, region, geom_polygon (MULTIPOLYGON), metadata
- **mr_area_assignments:** mr_id, area_id, type(primary/secondary), active_from/to
- **visits_raw_staging:** imported rows (as-is), import_batch_id
- **visits:** normalized, mr_id, visit_date, geom_point, mapped_doctor_id, area_id_resolved, confidence_score, flags
- **mapping_decisions:** visit_id, chosen_doctor_id, chosen_area_id (if manual), decision_by, reason_code, timestamp
- **import_batches:** file_name, uploaded_by, status, stats, timestamps
- **audit_logs:** action, entity, old/new, user_id, timestamp

### 2.2 Spatial indexes & geometry constraints
**PostGIS:**
- GIST index on areas.geom_polygon
- GIST index on doctors.geom_point
- GIST index on visits.geom_point
- Standardized SRID (e.g., 4326) and validation jobs (fix invalid polygons)

**Acceptance criteria:**
- Point-in-polygon query returns area for a visit in acceptable time at realistic volume
- Area intersection queries return overlap sets reliably

## 3) Phase 3 — Excel import pipeline (staging → validate → match → commit)

### 3.1 Import workflow (must-have)
1. Upload Excel
2. Validate structure & required columns
3. Load into staging (no direct commit)
4. Run auto-matching + geo resolution
5. Preview results + show issues
6. Manual mapping queue (only for low confidence / conflicts)
7. Final commit into normalized tables
8. Save metrics + audit trail

### 3.2 Validation and data hygiene (must-have)
- Date parsing normalization
- Deduplication rules (same MR, same timestamp window, same doctor candidate)
- Missing GPS handling:
  - If doctor mapped with high confidence → infer point from doctor
  - Else flag as geo-unknown (excluded from geo KPIs)

**Acceptance criteria:**
- Import never corrupts production tables
- Every import batch is reproducible (re-run possible) and auditable

## 4) Phase 4 — Matching engine + manual mapping UI (your key differentiator)

### 4.1 Auto-matching engine (explainable scoring)
For each staging row, generate candidate doctors with a composite score:
- Name similarity: Levenshtein / token match
- Area compatibility: candidate doctor inside MR assigned areas (bonus)
- Distance: between visit point and doctor point (bonus if close)
- History: MR previously visited that doctor (bonus)
- Clinic keywords (optional): if present in sheet

**Output:**
- top N candidates + explanation strings
- mapping confidence score

### 4.2 Manual mapping panel (must-have)
**UI:**
- Left: unmapped/low-confidence rows with filters (MR, date, area, confidence)
- Right: suggested doctors (distance, area match, similarity, last visit)

**Actions:**
- Approve one
- Create new doctor (if truly missing)
- Mark as exception (meeting/training/leave/outside territory allowed)
- Bulk actions with rules (e.g., “map all ‘Dr Saba’ to doctor_id=123”)

**Learning loop:**
- Save mapping decisions → future auto-match weights increased (per MR/region)

**Acceptance criteria:**
- Manual mapping time drops over repeated imports due to learned decisions
- Every mapping decision is auditable (who/when/why)

## 5) Phase 5 — Territory compliance & “Why A/B/C not D/E/F” engine
This is the behavior intelligence layer.

### 5.1 Classification engine (automatic)
For every visit:
- Determine area_id_resolved by point-in-polygon
- Determine assignment_status:
  - in_primary / in_secondary / outside / border_ambiguous

Add border ambiguity:
- Buffer polygon edges (small tolerance)
- Mark visits near border as “border ambiguous” (avoid unfair penalties)

### 5.2 Deviation scoring (simple + explainable)
Per MR per period (daily/weekly/rolling windows):
- Base score 100
- Penalties:
  - Outside-area visit count
  - Distance from assigned territory centroid / nearest assigned polygon
  - Repeat drift pattern (same outside area recurring)
- Credits:
  - Returning to primary areas
  - Visiting high-priority neglected doctors inside assigned areas

**Output:**
- Compliance band: OK / Warning / Action required
- Evidence summary

### 5.3 Reason engine (rule-based probabilities)
Generate top reasons for deviation, each with a score:
- Doctor density bias  
  Outside areas have much higher doctor density or high-priority doctors
- Distance friction  
  Assigned areas far from MR home/base or typical start point
- Low-value assignment  
  Assigned areas have low priority / low targets / poor ROI
- Habitual drift  
  Same outside polygons repeatedly visited across weeks
- Territory design flaw  
  Many MRs drift into same outside area → assignment borders unrealistic
- Data quality  
  Low geo confidence / missing points → exclude from behavioral conclusions

**Acceptance criteria:**
For any deviation alert, system shows:
- “Deviation detected” + “top reasons” + “supporting evidence”
- Avoids blaming when confidence is low or borders ambiguous

## 6) Phase 6 — Geo analytics: overlap, intersections, coverage gaps (beyond My Maps)

### 6.1 Spatial analytics (server + client)
**Server (PostGIS):**
- Area overlaps: polygon intersection sets
- Doctor outside assigned area detection
- Coverage gaps: doctors in area not visited within threshold

**Client (Leaflet + Turf):**
- Heatmaps (visit density)
- Drift arrows and movement paths
- Visual overlays for overlap/intersection

### 6.2 Coverage intelligence
- “Uncovered clusters”: doctor clusters not visited in last X days
- “Over-coverage”: too many visits in same micro-zone
- “Territory saturation”: visits per km² vs expected

**Acceptance criteria:**
Managers can identify overlap problems and re-assign territories using evidence

## 7) Phase 7 — Dashboards by role (operational + executive)

### 7.1 Role-based dashboards
**MR view**
- Today’s plan (next best actions)
- Assigned areas + route suggestions
- Missed high-priority doctors

**Manager view**
- Compliance leaderboard
- Deviation reasons (ranked)
- Coverage gaps and neglected doctors
- Territory design warnings (multi-MR drift)

**Executive view**
- Coverage health by region
- Risk areas (doctor neglect + territory non-compliance)
- Recommended interventions (rebalance territories, add MR, revise targets)

**Acceptance criteria:**
- Each role sees only actionable KPIs (not raw BI noise)
- Every KPI is drill-downable to evidence rows and map view

## 8) Phase 8 — Next Best Action engine (turn analytics into daily operations)
Without heavy ML:
- For each MR, produce top N doctor visits:  
  priority × neglect risk × distance cost × territory compliance needs
- Provide “return-to-territory” suggestions when drift is detected
- Generate route ordering (simple heuristic: nearest neighbor + priority boosts)

**Acceptance criteria:**
System can generate a daily recommended list per MR with explanations

## 9) Phase 9 — Governance, security, reliability (enterprise-grade)

### Must-have controls
- Full audit logs (imports, mappings, edits)
- Data freshness indicators and stale warnings
- Anti-manipulation checks:
  - Too many visits at same coordinates/time
  - Impossible travel speeds between points
- Backups and restore plan
- Permission model:
  - Who can edit areas, doctors, assignments, mapping rules
- Testing:
  - Unit tests: scoring, matching, classification
  - Integration tests: import batch end-to-end
  - Spatial regression tests: known points/polygons expected results

## 10) Release plan (incremental milestones)

### MVP (minimum competitive)
- Spatial DB + areas/doctors/MRs
- Excel import to staging + validation
- Auto-match + manual mapping panel
- Point-in-polygon classification
- Basic compliance score + deviation alerts
- Leaflet map: assigned vs visited overlay

### V1 (beats common BI for your case)
- Reason engine with evidence
- Overlap/intersection analytics
- Role dashboards (manager/executive)
- Confidence model gating KPIs
- Audit logs + exception handling

### V2 (operational advantage)
- Next best action lists
- Drift trend analysis (rolling windows)
- Manipulation detection
- Territory design flaw detection (multi-MR patterns)
- Scenario simulation (what-if reassignment)
```
