Seasonal Crew Availability Map (Landscaping)

Goal

Provide a consolidated, season-long view of every landscaping crew’s capacity and availability. Highlight overtime risk, jobs that need backup coverage, and schedule changes requiring approval, then export structured tables plus a concise summary.

Inputs to gather

• Season parameters: start & end dates, operating days (e.g., Mon–Sat), primary time zone, week start day (why: frames the planning horizon and calendar logic).
• Labor policy: standard daily hours per worker, maximum weekly hours, overtime thresholds/rules, holidays, weather-contingency days, travel-time buffer policy (why: drives capacity, OT calculations, and buffer insertion).
• Workforce data sources: links/IDs for route or assignment spreadsheets; file paths or sheet IDs for time logs/timesheets; calendar account names for staff & shared resources; email label/folder or search keywords for manager notes (why: these are the raw inputs to ingest).
• Skill/credential matrix for workers and job requirements, plus any equipment/resource dependencies and geographic clustering rules (why: needed to respect qualification and tool constraints when suggesting backups).
• Output destinations: preferred filenames, Sheet IDs, or folder paths for the Crew Availability Map, Backup Coverage Queue, Approval Queue, and Markdown summary (why: tells the agent where to write results).
Before doing any work, ask the user for these inputs in ONE message. Skip anything they already provided. If they tell you to decide, choose sensible defaults and say what you chose.

Workflow

1. Confirm scope and policies using the provided season parameters, labor policy, skill matrix, equipment rules, and output destinations. Log any missing items for later follow-up.
2. Inventory and load sources:
   a. Import route/assignment spreadsheets to capture jobs, planned dates or recurrences, estimated durations, locations, assigned crews, and priorities.
   b. Pull staff and shared-resource calendars to collect PTO, shifts, training, equipment downtime, and partial-day events.
   c. Load time logs/timesheets to obtain hours already worked per worker/day and any overtime already incurred.
   d. Search the specified email label/folder or keywords, then extract structured manager notes (date, sender, crew/worker, job, requested change, effective dates, approval status, constraints).
   e. Record any missing or corrupt sources; create placeholders and flag them for the user.
3. Normalize and reconcile entities: unify worker, crew, job, and date identifiers; map workers to crews and roles with effective dates; de-duplicate conflicts, favoring the most recent authoritative record, and flag unresolved issues.
4. Build the baseline demand plan: expand recurring jobs across the season, estimate labor hours (inferring from historical logs when missing), apply travel/setup buffers, and aggregate planned hours per crew/day/week.
5. Calculate available capacity: subtract calendar blocks from standard daily hours, cap weekly hours per policy, roll up to crew level, and adjust for equipment/resource outages.
6. Integrate time logs to compute current overtime status and forecast overtime risk against future planned hours, classifying risk as none, medium, or high based on policy thresholds.
7. Parse manager notes to identify requested schedule changes, compare them to the baseline plan, and classify each as approved, pending, or needing clarification with an impact summary.
8. Reconcile demand vs. supply: compute daily/weekly slack per crew, flag overtime risk periods, list jobs lacking coverage, and generate ranked backup crew suggestions considering skills, geography, slack, and OT impact.
9. Produce outputs:
   • Crew Availability Map table.
   • Backup Coverage Queue table.
   • Approval Queue table.
   • Concise Markdown summary of key findings, assumptions, data gaps, and pending approvals.
   Write these to the specified destinations (CSV/Sheet tabs and Markdown file).
10. Validate results (no negative availability, totals correct, holidays and time zones honored, duplicate jobs resolved). Log assumptions, inferred values, and data gaps.
11. Present the summary and key flags to the user, request confirmation on pending approvals or policy choices, then, on confirmation, finalize and time-stamp the outputs. Check in with the user after steps 2, 6, and 11 to surface issues early and avoid information overload.

Output

1. Crew Availability Map (CSV/Sheet tab) listing, per crew and week (optionally day): headcount, planned hours, available hours, slack, overtime risk level, blockers/notes, and top backup crews.
2. Backup Coverage Queue (CSV/Sheet tab) detailing each uncovered job/visit with date window, required skills, gap hours, current assignment, and three ranked backup options.
3. Approval Queue (CSV/Sheet tab) summarizing requested schedule changes with status, rationale, and impact.
4. Markdown summary (~1-2 pages) highlighting availability windows, OT hotspots, uncovered jobs, pending approvals, assumptions, data gaps, and validation notes.