# Handoff: EHS Module Plan Fixes (7 Findings)

**Date:** 2026-05-19
**From:** Auditor session
**To:** Executor (next Claude session — update the plan file)
**Plan file:** `~/.claude/plans/zazzy-sparking-moonbeam.md`
**Audit report:** `/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/AUDIT_PLAN_EHS_MODULE.md`
**Priority:** Fix before implementation begins

---

## Instructions

Read the plan at `~/.claude/plans/zazzy-sparking-moonbeam.md`, then apply these 7 corrections IN ORDER. Each fix references the exact section and line to change.

---

## Fix 1 (CRITICAL): Correct Worker List template cell mapping — Step 7

**Current plan text (Step 7):**
```
- Fills detailed cards (rows 13+): NAME, IMSS, CURP, RFC per worker
```

**Replace with:**
```
- Fills detailed cards starting at row 13. Each worker block = 4 rows:
  - Row N:   A=worker_number, B=full_name
  - Row N+1: B=imss_number
  - Row N+2: B=curp
  - Row N+3: (blank gap row)
  - Next worker starts at N+4
  - First worker at row 13, second at row 17, third at row 21, etc.
- NOTE: Template has NO RFC field — do not attempt to fill RFC
- NOTE: Column E = PHOTO. Phase 1: leave blank or insert placeholder image.
  If HTS rejects without photos, escalate to Phase 3 photo embedding immediately.
- NOTE: Summary table (rows 7-10) columns: A=No, B=Name, C=DOB (dd/mm/yyyy), D=Address, E=Remarks
```

**Also add to Step 7:**
```
- Use openpyxl load_workbook(template_path) — preserve formatting, merged cells, column widths
- Dynamic row discovery: scan column A for integer values to find worker block start positions
  (template may have pre-formatted blocks — count them, expand if more workers than blocks)
```

---

## Fix 2 (MAJOR): Change EmployeeEHSProfile to standalone BorbollaWorker — Step 2

**Current plan text (Step 2, EmployeeEHSProfile row):**
```
| `EmployeeEHSProfile` | EHS compliance data for Borbolla employees | user OneToOne, date_of_birth, address, imss_number, curp, rfc, photo_minio_key |
```

**Replace with:**
```
| `BorbollaWorker` | Borbolla employee data for EHS | first_name, last_name, date_of_birth, address, imss_number, curp, rfc, phone, photo_minio_key, is_active |
```

**Reason:** Field workers (Felipe, Samuel, Carlos) are NOT Django auth Users. They exist only in the HR Telegram bot system (hr_database.db). Creating phantom Django User accounts just to satisfy a OneToOne FK is wrong. `BorbollaWorker` is a standalone model — same structure as `SubcontractorWorker`, just for our own people.

**Also update the ProjectWorker constraint:**
```
| `ProjectWorker` | Links worker to project | project FK, borbolla_worker FK (nullable), subcontractor_worker FK (nullable), role, crew_number, dc3_completed, sua_verified |
```
(Changed `employee FK` to `borbolla_worker FK`)

**Also update Step 6 seed data:**
```
- Create `BorbollaWorker` entries: Felipe Rodriguez (supervisor), Samuel Cenicero (rigger), Carlos Medellin (rigger)
- NOTE: IMSS/CURP data must come from HR system (hr_database.db) or be entered manually via admin.
  Alejandro's crew (utilities) is still TBD — create entries when confirmed.
```

---

## Fix 3 (MAJOR): Bundle Excel templates in app, not external path — Step 3

**Current plan text (Step 3, settings):**
```python
EHS_TEMPLATES_DIR = '/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/formats'
```

**Replace with:**
```python
EHS_TEMPLATES_DIR = os.environ.get(
    'EHS_TEMPLATES_DIR',
    str(Path(__file__).resolve().parent.parent / 'apps' / 'operations' / 'excel_templates')
)
```

**Also add to Step 1 (app skeleton):**
```
apps/operations/
    excel_templates/
        List of workers - example.xlsx    # copied from project/formats/
        Weekly work plan Sample.xlsx      # copied from project/formats/
```

**Reason:** The plan's path (`/home/borbolla/clawd/...`) only exists on the dev machine. Production server at `/opt/django-ui/current` has no access to it. Bundling templates inside the app means they deploy with the code.

---

## Fix 4 (MAJOR): Add openpyxl correctly — Files to Modify

**Current plan text:**
```
| `/home/borbolla/django-ui-local/requirements.txt` | Pin `openpyxl==3.1.5` |
```

This is correct but incomplete. **Add a verification step after Step 4 (migrate):**
```
### Step 4b: Verify openpyxl template compatibility
- Run: python -c "import openpyxl; wb = openpyxl.load_workbook('apps/operations/excel_templates/List of workers - example.xlsx'); wb.save('/tmp/test_workers.xlsx'); print('OK')"
- Open /tmp/test_workers.xlsx and verify formatting, merged cells, and column widths survived the load/save cycle
- If formatting breaks, add keep_vba=False, data_only=False flags and re-test
```

---

## Fix 5 (MODERATE): Weekly Plan is 7 days not 6 — Step 8

**Current plan text (Step 8):**
```
- Fills daily rows (Mon-Sat): Date, Working Time, Manager, People Required, Activities, hazard flags
```

**Replace with:**
```
- Fills 7 daily rows (Mon-Sun, rows 10-16):
  - Column B: date (datetime object, formatted dd/mm/yyyy)
  - Column C: Working Time (e.g., "08:00-17:00" or blank for Sunday)
  - Column D: Manager name
  - Column E: People Required (integer)
  - Column F: Activities description
  - Columns G/H/I: Boolean hazard flags (Height Works / Cut and Welding / Heavy Equipment)
  - Column J: Etcetera (additional hazards)
  - Column K: Remarks
- Sunday (row 16): fill date but set People Required=0, Activities="No work scheduled"
- NOTE: Date column is B, not A. Column A appears unused in the template.
```

---

## Fix 6 (MODERATE): Use Google API client instead of Gmail MCP SSE — Step 9

**Current plan text (Step 9):**
```
- Connects to Gmail MCP SSE at http://100.116.127.87:8080/sse
```

**Replace with:**
```
- Uses Google API client library (google-api-python-client) with OAuth2 token
- Token location: /home/borbolla/gmail/token.json (dev) or /opt/django-ui/shared/secrets/gmail_token.json (prod)
- Pattern reference: /home/borbolla/clawd/projects/HTS/scripts/send_chang_as_thread_reply.py
- Add to requirements.txt:
  google-api-python-client==2.147.0
  google-auth==2.35.0
  google-auth-oauthlib==1.2.1
  google-auth-httplib2==0.2.0
- Email construction: use email.mime with base64 attachment encoding
- This is MUCH simpler than SSE+JSON-RPC and is already proven in production
```

**Also update Files to Modify:**
```
| `/home/borbolla/django-ui-local/requirements.txt` | Pin openpyxl==3.1.5, google-api-python-client==2.147.0, google-auth==2.35.0, google-auth-oauthlib==1.2.1, google-auth-httplib2==0.2.0 |
```

---

## Fix 7 (LOW): Mark seed data as placeholder — Step 6

**Add note to Step 6:**
```
NOTE: Seed data is partial. Full worker roster depends on:
- Alejandro confirming utilities crew (TBD since May 14 — 5 days overdue)
- Alex Aguilar (Belean) providing subcontractor worker list with IMSS/CURP
- Carlos Medellin needs verification — not referenced in masterplan crew plan
First EHS submission may use partial roster. Update as workers are confirmed.
```

---

## Verification After Fixes

After applying all 7 fixes, the plan should pass these checks:

1. No reference to Django User model in EHS worker context
2. Excel template paths resolve to `apps/operations/excel_templates/`
3. Worker list generator uses 4-row blocks starting at row 13
4. Weekly plan generator fills 7 rows (B10-B16) including Sunday
5. Email service uses `google-api-python-client`, not MCP SSE
6. No RFC field reference in worker list generator
7. requirements.txt additions: openpyxl + google API packages

---

## Source Files for Reference

| File | Purpose |
|------|---------|
| `/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/formats/List of workers - example.xlsx` | Worker list template (copy to app) |
| `/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/formats/Weekly work plan Sample.xlsx` | Weekly plan template (copy to app) |
| `/home/borbolla/django-ui-local/apps/tenants/models.py` | TenantModel base (line 48) |
| `/home/borbolla/django-ui-local/apps/core/mixins.py` | OperationsRequiredMixin (line 76) |
| `/home/borbolla/django-ui-local/apps/services/views.py` | View pattern reference |
| `/home/borbolla/django-ui-local/templates/base.html` | Sidebar nav (line 128+) |
| `/home/borbolla/django-ui-local/config/settings.py` | INSTALLED_APPS (line 43) |
| `/home/borbolla/django-ui-local/requirements.txt` | Current deps (no openpyxl) |
| `/home/borbolla/clawd/projects/HTS/scripts/send_chang_as_thread_reply.py` | Gmail API pattern |