# Monitoring & Evaluation Cell (MEC)

# Public API — Technical Specification & Implementation Guide

| Item | Specification |
|------|----------------|
| Document type | Technical API specification (requirements + build guide) |
| Audience | Implementing developers (Laravel / SIMS PMS) |
| API access | Public, read-only |
| Authentication | **None** for public endpoints |
| Primary format | JSON (UTF-8) |
| Transport | HTTPS in production |
| Version | `v1` |
| Consumers | MEC website, public dashboards, GIS/map applications, third-party public interfaces |
| Source of truth | Live SIMS / PMS Laravel codebase (not the older N-of-M MoM training docs) |
| Related internal docs | `docs/training/16-MEC-Module-Developer-Reference.md` |

---

## Table of contents

1. [Purpose](#1-purpose)
2. [Public API principles](#2-public-api-principles)
3. [What this API must **not** expose](#3-what-this-api-must-not-expose)
4. [System architecture (existing)](#4-system-architecture-existing)
5. [Database connections](#5-database-connections)
6. [Canonical data model](#6-canonical-data-model)
7. [Business definitions (authoritative counting rules)](#7-business-definitions-authoritative-counting-rules)
8. [Year, region and status filters](#8-year-region-and-status-filters)
9. [Coordinate resolution (WGS84)](#9-coordinate-resolution-wgs84)
10. [Proposed base URL and routing](#10-proposed-base-url-and-routing)
11. [Common response contract](#11-common-response-contract)
12. [HTTP status codes](#12-http-status-codes)
13. [Query parameters](#13-query-parameters)
14. [API-01 — Overall MEC statistics](#14-api-01--overall-mec-statistics)
15. [API-02 — Region-wise MEC statistics](#15-api-02--region-wise-mec-statistics)
16. [API-03 — Planned scheme coordinates](#16-api-03--planned-scheme-coordinates)
17. [API-04 — Visited scheme coordinates and status](#17-api-04--visited-scheme-coordinates-and-status)
18. [Optional individual statistic endpoints](#18-optional-individual-statistic-endpoints)
19. [Recommended Laravel implementation](#19-recommended-laravel-implementation)
20. [SQL / query blueprints](#20-sql--query-blueprints)
21. [Caching, rate limiting, CORS, logging](#21-caching-rate-limiting-cors-logging)
22. [OpenAPI / Swagger](#22-openapi--swagger)
23. [Tests](#23-tests)
24. [Acceptance criteria](#24-acceptance-criteria)
25. [Implementation checklist](#25-implementation-checklist)
26. [Appendix A — Column reference](#26-appendix-a--column-reference)
27. [Appendix B — Existing code to reuse (do not copy user-scope)](#27-appendix-b--existing-code-to-reuse-do-not-copy-user-scope)
28. [Appendix C — Sample payloads](#28-appendix-c--sample-payloads)

---

## 1. Purpose

Build a **public, versioned, read-only REST API** that exposes selected Monitoring & Evaluation Cell (MEC) data for the MEC website and GIS maps.

The four required endpoints cover **ten public outputs**:

| # | Public output | Served by |
|---|---------------|-----------|
| 1 | Total ADP schemes | `GET /statistics/summary` |
| 2 | Total published reports | `GET /statistics/summary` |
| 3 | Total satisfactory reports | `GET /statistics/summary` |
| 4 | Total unsatisfactory reports | `GET /statistics/summary` |
| 5 | ADP schemes by region | `GET /statistics/regions` |
| 6 | Published reports by region | `GET /statistics/regions` |
| 7 | Satisfactory reports by region | `GET /statistics/regions` |
| 8 | Unsatisfactory reports by region | `GET /statistics/regions` |
| 9 | Planned scheme GIS coordinates | `GET /schemes/planned/coordinates` |
| 10 | Visited scheme GIS coordinates + satisfactory/unsatisfactory | `GET /schemes/visited/coordinates` |

**Do not create new tables.** This API is a public read projection over existing SIMS data.

**Do not reuse authenticated dashboard services as-is.** Internal MEC dashboard code applies `SchemeAccessHelper` user/department scope. The public API must ignore the logged-in user (there is none) and must never leak unpublished, declined, or in-process workflow data.

---

## 2. Public API principles

- GET only. Do not register POST / PUT / PATCH / DELETE on this prefix.
- No login, API key, Sanctum token, or registration.
- JSON UTF-8. Stable versioned URLs under `/api/v1/public/mec/`.
- Only data approved for public disclosure.
- GIS coordinates in decimal latitude/longitude, WGS84 / EPSG:4326.
- Consistent envelope, HTTP status codes, and `meta.generated_at`.
- HTTPS in production.
- Server-side validation of every query parameter.
- Rate limiting by IP is allowed; it must **not** become an authentication requirement.

---

## 3. What this API must **not** expose

Never return any of the following fields or nested objects:

| Category | Examples (tables / columns) |
|----------|-----------------------------|
| People | `users.name`, emails, phones, CNICs, `mec_reports.user_id`, `workflow_assignee_user_id`, monitoring officer IDs |
| Workflow | `mec_reports.workflow_status`, `workflow_log`, `payload` JSON, ratings (`mec_report_workflow_ratings`), drafts (`mec_report_drafts`) |
| Internal remarks | `plan_schemes.remarks`, RD rejection remarks, compliance replies, SIMS department enquiry |
| Unpublished reports | `status IN ('processing','approved','declined')` |
| Unpublished plans | `plans.status != 'approved'`, withdrawn schemes (`plan_schemes.rd_rejected_at IS NOT NULL`) |
| Admin / auth | passwords, tokens, `control_access`, role memberships, `user_mec_regions` |
| Region contact | `mec_regions.address`, `mec_regions.phone` (used on internal letters) |
| Documents | publish-letter HTML/PDF, field-visit letters, attachments |
| Financial internals | budget allocations, expenditure, PC-I amounts (not requested) |
| Estimated GPS | do not invent or geocode a district centroid when scheme coords are missing |

Errors must not include SQL, file paths, stack traces, or connection names.

---

## 4. System architecture (existing)

This application is the **Sindh Development Portfolio Management System (PMS / SIMS)**. MEC lives inside it.

```
Public client (website / GIS)
        |
        |  HTTPS GET /api/v1/public/mec/...
        v
Laravel API routes (routes/api.php, prefix api)
        |
        +-- Public Mec controllers (NEW, no auth)
        |
        +-- Public Mec services (NEW)
        |         |
        |         +-- default DB (mysql / pc)  → plans, plan_schemes, mec_reports, mec_regions
        |         +-- core DB                  → projects, project_locations, announce_budgets, districts, locations
        |
        v
JSON envelope
```

Existing **authenticated** surfaces that already compute similar numbers (for logic reuse only):

| Internal surface | Path | Use for public API |
|------------------|------|--------------------|
| MEC dashboard Reports tab | `app/Services/Dashboard/Mec/MecDashboardReportsAggregateService.php` | Published / SAT / UnSAT counts |
| MEC dashboard Visits tab | `app/Services/Dashboard/Mec/MecDashboardVisitsAggregateService.php` | Approved current-version plan schemes |
| MEC dashboard portfolio | `app/Services/Dashboard/Mec/MecDashboardPortfolioService.php` | ADP scheme count by announce-budget year |
| ADP schemes map | `app/Services/Mec/AdpSchemesMapService.php` | Lat/lng resolution + published assessment |
| Scheme listing tones | `app/Support/SchemeNameListingTones.php` | Latest published SAT/UnSAT per scheme |
| Scheme list region filter | `app/Services/PC/SchemeListService.php` | `mec_region_scope` → locations/divisions |

Existing authenticated mobile API (`/api/v1/eaed/*`) is **not** public. Do not hang public MEC routes under `eaed`.

---

## 5. Database connections

Defined in `config/database.php`.

| Laravel connection | Typical env | Holds |
|--------------------|-------------|--------|
| `mysql` (default) | `DB_DATABASE` (often `pms_db`) | MEC operational data: `plans`, `plan_versions`, `plan_schemes`, `mec_reports`, `mec_regions` |
| `pc` | often same host/database as default | `mec_region_scope` (created on `pc` connection) |
| `core` | `CORE_DB_DATABASE` (often `bpms_db`) | ADP schemes: `projects`, `project_locations`, `project_budget_allocation`, `announce_budgets`, `locations`, `districts` |

`App\Models\Core\Project` and related core models are **read-only** (`CoreReadOnly`). Public API must only SELECT from core.

If `mec_region_scope` is not visible on the default connection in a given environment, query it with `DB::connection('pc')` (same pattern as `SchemeListService` and `MecRegionService`).

Cross-database joins: `plan_schemes.scheme_id` = `projects.project_id`. There is **no FK** across databases. Join in application SQL only when both DBs are on the same MySQL server and the DBA allows `pms_db.plan_schemes` ↔ `bpms_db.projects`; otherwise run two queries and join in PHP by `project_id`.

---

## 6. Canonical data model

### 6.1 Entity relationship (public-relevant subset)

```
mec_regions (1) ----< plans.mec_region_id
                         |
                         | current_version
                         v
                    plan_versions
                         |
                         v
                    plan_schemes.scheme_id  =  core.projects.project_id
                         |
                         +----< mec_reports.plan_scheme_id
                         +----< plan_scheme_dates.plan_scheme_id

mec_regions (1) ----< mec_region_scope (location_id or district_id)
                         |
                         v
                    core.locations / core.districts
                         |
                         v
                    core.project_locations ---- core.projects
```

### 6.2 MEC regions (master)

Table: `mec_regions`  
Model: `App\Models\PC\MecRegion`

Seeded public names (`database/seeders/data/mec_regions_sectors_subsectors_snapshot.json`):

| `id` | `name` |
|------|--------|
| 1 | Karachi |
| 2 | Hyderabad |
| 3 | Sukkur |
| 4 | Larkano |
| 5 | Mirpur Khas |

Use live rows where `status = 1`. Do not hard-code IDs in queries; hard-coded names are only examples.

| Column | Type | Public? | Notes |
|--------|------|---------|--------|
| `id` | bigint PK | **Yes** as `region_id` | |
| `name` | string unique | **Yes** as `region_name` | |
| `short_code` | string nullable unique | Optional | Not required by the public spec |
| `status` | tinyint | No | Filter `status = 1` |
| `address`, `phone` | string | **No** | Internal letter footer |
| `description`, `created_by`, `updated_by`, timestamps | | **No** | |

### 6.3 Region geographic scope

Table: `mec_region_scope` (connection `pc`)

| Column | Type | Meaning |
|--------|------|---------|
| `mec_region_id` | bigint | FK to `mec_regions.id` |
| `scope_type` | string | `'location'` or `'division'` |
| `scope_id` | bigint | If `location` → `locations.location_id`. If `division` → `districts.district_id` |

In this product, core table `districts` is used as the **division** dimension for MEC region scope (see `SchemeListService` region filter).

**Use this table** to attach an ADP **scheme** to a region (scheme geography).

**Do not use this table** to attach a **monitoring report** to a region. Reports inherit region from the visit plan: `plans.mec_region_id`.

### 6.4 ADP schemes (core)

Table: `projects` (`core`)  
Model: `App\Models\Core\Project`  
PK: `project_id`

Public-safe columns:

| Column | Public field | Notes |
|--------|--------------|--------|
| `project_id` | `scheme_id` (integer, canonical) | Stable internal ID |
| `name` | `scheme_name` | HTML-entity decode if stored encoded |
| `code` | `scheme_uid` (optional extra) | Generated UID, e.g. department prefixes |
| `adp_number` | `adp_scheme_code` | ADP number as published in ADP |
| `latitude`, `longitude` | GIS | Prefer these; treat `0` as missing |
| `status` | filter only | Must be `1` (active) |
| `published` | filter only | Must be `1` |
| `completion_status` | filter only | Exclude `'Drop'` |
| `start_year` | year helper only | Not a public response field unless needed |

Do **not** expose: `mec_status`, `mec_major_reason`, `chronic_issue_reason`, amounts, `created_by`, `is_disabled`, `disabled_message`.

Related core tables:

| Table | Role |
|-------|------|
| `project_locations` | Extra pins: `project_id`, `location_id`, `latitude`, `longitude` |
| `locations` | Place name + `district_id`, `talka_id` |
| `districts` | Division-level geography for region scope |
| `announce_budgets` | ADP year header (`announce_budget_id`, `year` e.g. `'2026-2027'`) |
| `project_budget_allocation` | Links `project_id` to `announce_budget_id` |

### 6.5 Visit plans

Table: `plans`  
Model: `App\Models\PC\Plan`

| Column | Type | Public use |
|--------|------|------------|
| `id` | PK | Internal join only |
| `status` | enum | Public only when `'approved'` |
| `is_active` | bool | Must be `true` |
| `current_version` | int | Must equal `plan_versions.version_no` |
| `mec_region_id` | bigint nullable | Report/visit region |
| `year` | int | Calendar year of the plan (e.g. `2026`) |
| `month` | int | Optional finer filter; not in public spec |
| `plan_type` | enum/string | RVP, CVP, SVP, DRP, ERP, PMP — internal only |

Table: `plan_versions`

| Column | Use |
|--------|-----|
| `id` | PK; `plan_schemes.plan_version_id` |
| `plan_id` | FK `plans.id` |
| `version_no` | Must match `plans.current_version` for “current approved plan” |

Table: `plan_schemes`  
Model: `App\Models\PC\PlanScheme`

| Column | Type | Public use |
|--------|------|------------|
| `id` | PK | Join to `mec_reports.plan_scheme_id` |
| `plan_version_id` | FK | Current version only |
| `scheme_id` | bigint | = `projects.project_id` |
| `visit_date` | date | Fallback visit date |
| `rd_rejected_at` | timestamp nullable | **Exclude** if not null (scheme withdrawn from that plan) |
| `monitoring_officer_id`, `remarks`, progress fields | | **Never public** |

Table: `plan_scheme_dates`

| Column | Use |
|--------|-----|
| `plan_scheme_id` | FK |
| `visit_date` | Preferred visit date when multiple dates exist; use `MAX(visit_date)` |

**Current approved planned scheme** (same predicate as dashboard visits):

```
plans.status = 'approved'
AND plans.is_active = 1
AND plan_versions.version_no = plans.current_version
AND plan_schemes.rd_rejected_at IS NULL
```

### 6.6 Monitoring reports

Table: `mec_reports`  
Model: `App\Models\MecReport`

| Constant | Value | Public? |
|----------|-------|---------|
| `STATUS_PROCESSING` | `processing` | No |
| `STATUS_APPROVED` | `approved` | No (signed, not yet published) |
| `STATUS_PUBLISHED` | `published` | **Yes** |
| `STATUS_DECLINED` | `declined` | No |
| `ASSESSMENT_SATISFACTORY` | `satisfactory` | **Yes** (only with published) |
| `ASSESSMENT_UNSATISFACTORY` | `unsatisfactory` | **Yes** (only with published) |

| Column | Public use |
|--------|------------|
| `id` | Internal only |
| `plan_scheme_id` | Join to scheme + plan region |
| `status` | Must equal `published` |
| `assessment_status` | `satisfactory` / `unsatisfactory` / null |
| `approved_at`, `submitted_at`, `updated_at` | Visit-date / “latest report” ordering |
| `published_report_uid` | Optional public report id; **not** requested — omit unless product asks later |
| `payload` | **Never return.** May be read server-side only to recover GPS or legacy assessment if `assessment_status` is empty |
| `user_id`, `workflow_*`, letter numbers | **Never public** |

A published row with empty `assessment_status` is still a published report for **count of published reports**, but it is **not** SAT or UnSAT. Do not invent a status. For the visited GIS endpoint, skip schemes whose latest published report has no resolvable SAT/UnSAT tone.

---

## 7. Business definitions (authoritative counting rules)

These rules must match MEC’s live reporting, not the sample numbers in the original request document.

### 7.1 ADP scheme

An **ADP scheme** counted by this API is a core `projects` row that is:

1. Active: `projects.status = 1`
2. Published: `projects.published = 1`
3. Not dropped: `projects.completion_status != 'Drop'`  
   (`SchemeAccessHelper::COMPLETION_STATUS_DROP`)
4. Linked to the selected ADP year via:

```text
EXISTS (
  SELECT 1 FROM project_budget_allocation pba
  WHERE pba.project_id = projects.project_id
    AND pba.announce_budget_id = :budgetId
)
```

This is the same rule as `MecDashboardPortfolioService::totalSchemesForAnnounceBudgetYear()`.

Default ADP year when `year` is omitted: the latest `announce_budgets.year` (order by `year` descending), typically `'2026-2027'` in current dashboards.

When public query `year=2026` is supplied (calendar year):

1. Resolve `announce_budgets` where `year` equals `'2026-2027'` **or** `year LIKE '2026-%'` **or** `year` starts with the four-digit calendar year.
2. If none match, return zeros for ADP scheme counts (do not silently fall back to another year).
3. Document the resolved ADP label in `meta.adp_year` (e.g. `"2026-2027"`).

Completed schemes (`completion_status = 'Completed'`) **are included** in the ADP total if they are still in that year’s allocation and not Dropped. Do not apply the portal-listing filter `completion_status = 'Not Completed'` to public ADP totals — that filter is for the internal schemes list / map default population, not for “how many ADP schemes exist this year”.

### 7.2 Published report

Count rows in `mec_reports` where `status = 'published'`.

Do **not** require `submitted_at IS NOT NULL` (dashboard type mix uses that for some charts; the published KPI uses status only — follow `MecDashboardReportsAggregateService::globalStats()`).

Do **not** apply `SchemeAccessHelper::allowedProjectIds()`.

### 7.3 Satisfactory report

```
mec_reports.status = 'published'
AND mec_reports.assessment_status = 'satisfactory'
```

### 7.4 Unsatisfactory report

```
mec_reports.status = 'published'
AND mec_reports.assessment_status = 'unsatisfactory'
```

Note: `published` ≥ `satisfactory + unsatisfactory` because some published reports may lack assessment. That is expected. Do not force equality.

### 7.5 Planned scheme (GIS)

A scheme currently on an **approved, active, current-version** visit plan, not RD-withdrawn, and approved for public GIS:

- Predicate in §6.5.
- Public GIS: include the row even if coordinates are missing; set `latitude`/`longitude` to `null` rather than guessing.
- `status` in the JSON is always the public literal `"planned"`.
- One output row per distinct `scheme_id` (if the scheme appears on multiple approved plans, keep one row; prefer the latest `plans.year`/`month` or latest `visit_date`).

Optional product choice (document in code comments, default **include**): a scheme that is both planned and visited still appears on the planned layer. GIS clients overlay the two layers.

### 7.6 Visited scheme (GIS)

A scheme whose **latest published** monitoring report has a public assessment of `satisfactory` or `unsatisfactory`.

Latest published report per `plan_schemes.scheme_id`:

```
ORDER BY COALESCE(mec_reports.approved_at, mec_reports.updated_at) DESC
```

Same idea as `SchemeNameListingTones::latestPublishedReportRowByProjectId()`.

`status` in JSON is only `"satisfactory"` or `"unsatisfactory"` (lowercase, as in the request spec).

`visit_date`:

```
COALESCE(
  (SELECT MAX(psd.visit_date) FROM plan_scheme_dates psd WHERE psd.plan_scheme_id = ps.id),
  ps.visit_date,
  DATE(mr.approved_at),
  DATE(mr.submitted_at)
)
```

If still null, return `visit_date: null` (do not invent a date).

### 7.7 Region assignment — two different rules

| Metric | Region source | Why |
|--------|---------------|-----|
| ADP scheme counts by region | Scheme geography via `mec_region_scope` + `project_locations` / `projects.location_id` | Scheme may never have been planned by MEC |
| Published / SAT / UnSAT report counts by region | `plans.mec_region_id` of the report’s plan | Report is owned by the MEC regional office that ran the visit |
| Planned GIS points | `plans.mec_region_id` of the approved plan | Planning is regional |
| Visited GIS points | `plans.mec_region_id` of the latest published report’s plan | Outcome belongs to that visit |

**Reconciliation**

- Sum of region **report** counts should equal overall report counts, except reports whose plan has `mec_region_id` null/0. Put those in no public region row (omit), and expose `meta.unassigned_reports` so auditors can reconcile.
- Sum of region **ADP scheme** counts may **exceed** overall ADP schemes if one scheme has locations in more than one MEC region. Count **distinct `project_id` per region**. Document this in OpenAPI `meta` (`schemes_may_appear_in_multiple_regions: true`).

If a scheme has no matching `mec_region_scope`, it contributes to overall ADP total but not to any region row.

---

## 8. Year, region and status filters

### 8.1 `year` (integer, optional)

| Dataset | How `year=2026` is applied |
|---------|----------------------------|
| ADP schemes | `announce_budgets` for FY starting 2026 (`2026-2027` / `LIKE '2026-%'`) |
| Reports | `YEAR(COALESCE(mec_reports.approved_at, mec_reports.submitted_at, mec_reports.created_at)) = 2026` **or** join plan and use `plans.year = 2026`. **Recommended:** filter reports by the visit plan year: `plans.year = :year` after joining `plan_schemes` → `plan_versions` → `plans`. That keeps report year aligned with MEC’s planning year. |
| Planned GIS | `plans.year = :year` |
| Visited GIS | `plans.year = :year` of the latest published report’s plan |

Validation: integer, `2000–2100`. Invalid → HTTP 400.

When omitted:

| Dataset | Default |
|---------|---------|
| ADP schemes | Latest announce-budget year |
| Reports | All-time published (matches dashboard Reports tab) |
| GIS planned | All current approved plans (all years) |
| GIS visited | All years |

Always echo the effective filter in `meta`.

### 8.2 `region_id` (integer, optional)

Must exist in `mec_regions` with `status = 1`. Unknown id → HTTP 400 (bad request), **not** 404 (404 is for unknown URL paths).

On `/statistics/regions`, `region_id` returns a one-element array (still an array for a stable client contract).

### 8.3 `status` (string, visited GIS only)

Allowed: `satisfactory`, `unsatisfactory` (case-insensitive input; normalize to lowercase). Anything else → 400.

### 8.4 Pagination (GIS list endpoints)

| Param | Default | Max | Notes |
|-------|---------|-----|--------|
| `page` | 1 | — | Integer ≥ 1 |
| `per_page` | 100 | 500 | Integer |

Statistics endpoints are not paginated.

---

## 9. Coordinate resolution (WGS84)

Reuse the precedence in `AdpSchemesMapService` (LAT_EXPR / LNG_EXPR), simplified for **one pin per scheme**:

1. `projects.latitude` / `projects.longitude` if both are non-null and not `0`.
2. Else first `project_locations` row for that `project_id` with non-null, non-zero lat and lng (`ORDER BY project_locations.id ASC`).
3. Else **null** (do not geocode).

Optional extra (only if product confirms field GPS is public): parse GPS pairs from the latest **published** report `payload` via existing helper `report_scheme_unit_gps_pairs_from_payload()`. Default for v1: **skip payload GPS** to avoid leaking report body and to keep SQL simple. If enabled later, still never return the payload.

Validation of a pair:

- Numeric, comma-as-decimal allowed when parsing strings.
- Reject `(0, 0)`.
- Reject `|lat| > 90` or `|lng| > 180`.
- JSON types must be **numbers**, not strings. Use ~6 decimal places.

CRS: WGS84 / EPSG:4326. Do not convert from other systems in v1; core coords are already stored as decimal lat/lng.

---

## 10. Proposed base URL and routing

Public production example from the business request:

```text
https://mecsindh.gov.pk/api/v1/statistics/summary
```

**Implementation inside this Laravel app** (keep EAED and public MEC apart):

```text
https://{host}/api/v1/public/mec/statistics/summary
https://{host}/api/v1/public/mec/statistics/regions
https://{host}/api/v1/public/mec/schemes/planned/coordinates
https://{host}/api/v1/public/mec/schemes/visited/coordinates
```

If reverse-proxy / the MEC website must match the shorter paths, map at nginx:

```text
/api/v1/statistics/*            →  /api/v1/public/mec/statistics/*
/api/v1/schemes/*/coordinates   →  /api/v1/public/mec/schemes/*/coordinates
```

Register in `routes/api.php` (already prefixed with `api` by `RouteServiceProvider`):

```php
Route::prefix('v1/public/mec')
    ->middleware(['throttle:mec-public'])
    ->name('api.v1.public.mec.')
    ->group(function () {
        Route::get('statistics/summary', [PublicMecStatisticsController::class, 'summary'])
            ->name('statistics.summary');
        Route::get('statistics/regions', [PublicMecStatisticsController::class, 'regions'])
            ->name('statistics.regions');
        Route::get('schemes/planned/coordinates', [PublicMecSchemeCoordinatesController::class, 'planned'])
            ->name('schemes.planned.coordinates');
        Route::get('schemes/visited/coordinates', [PublicMecSchemeCoordinatesController::class, 'visited'])
            ->name('schemes.visited.coordinates');
    });
```

- No `auth:sanctum`.
- No `web` middleware (no session/CSRF).
- Do not put these routes in `routes/web.php`.

`RouteServiceProvider` already loads `routes/api.php` with middleware group `api` (includes `ThrottleRequests:api` at 60/min). Add a dedicated limiter `mec-public` (see §21).

---

## 11. Common response contract

Success:

```json
{
  "success": true,
  "data": {},
  "message": "Request processed successfully",
  "meta": {
    "generated_at": "2026-09-07T14:00:00+05:00",
    "timezone": "Asia/Karachi"
  }
}
```

- `data` is an **object** for summary, an **array** for list endpoints.
- `generated_at` is ISO-8601 with offset `+05:00` (Pakistan). Use `Carbon::now('Asia/Karachi')->toIso8601String()`.
- List endpoints add pagination inside `meta` (not a second top-level key):

```json
"meta": {
  "generated_at": "2026-09-07T14:00:00+05:00",
  "timezone": "Asia/Karachi",
  "page": 1,
  "per_page": 100,
  "total": 1250,
  "last_page": 13,
  "adp_year": "2026-2027",
  "filters": { "region_id": null, "year": 2026, "status": null }
}
```

Error:

```json
{
  "success": false,
  "data": null,
  "message": "Invalid query parameter: year",
  "errors": {
    "year": ["The year must be an integer between 2000 and 2100."]
  },
  "meta": {
    "generated_at": "2026-09-07T14:00:00+05:00"
  }
}
```

Do not include `code` that leaks internals. Optional stable codes: `invalid_query`, `not_found`, `rate_limited`, `server_error`.

HTTP `429` body:

```json
{
  "success": false,
  "data": null,
  "message": "Too many requests. Please retry later."
}
```

---

## 12. HTTP status codes

| Code | When |
|------|------|
| 200 | Success (including empty `data: []`) |
| 400 | Invalid query (`year`, `region_id`, `status`, `page`, `per_page`) |
| 404 | Unknown path only |
| 405 | POST/PUT/PATCH/DELETE on these URLs (Laravel automatic if routes are GET-only) |
| 429 | Rate limit |
| 500 | Unexpected exception (generic message) |
| 503 | Maintenance (`PreventRequestsDuringMaintenance`) |

Never return 401/403 on these endpoints (that would imply auth).

---

## 13. Query parameters

| Parameter | Endpoints | Type | Rules |
|-----------|-----------|------|--------|
| `region_id` | regions, planned, visited | integer | Must be active `mec_regions.id` |
| `year` | all four | integer | 2000–2100 |
| `status` | visited only | string | `satisfactory` \| `unsatisfactory` |
| `page` | planned, visited | integer | ≥ 1 |
| `per_page` | planned, visited | integer | 1–500, default 100 |

Unknown query keys: **ignore** (do not 400), so old clients stay compatible.

Form Request class (recommended): `App\Http\Requests\Api\V1\PublicMec\PublicMecIndexRequest`.

---

## 14. API-01 — Overall MEC statistics

**Endpoint:** `GET /api/v1/public/mec/statistics/summary`  
**Auth:** none  
**Cache:** 10 minutes (see §21)

### 14.1 Response fields

| Field | Type | Logic |
|-------|------|--------|
| `total_adp_schemes` | integer | §7.1 |
| `total_published_reports` | integer | §7.2 (+ year filter §8.1) |
| `total_satisfactory_reports` | integer | §7.3 |
| `total_unsatisfactory_reports` | integer | §7.4 |
| `generated_at` | datetime | Also duplicated in `meta` for the original sample shape |

Keep `generated_at` **inside `data`** as in the business sample **and** in `meta`.

### 14.2 Sample

```json
{
  "success": true,
  "message": "Request processed successfully",
  "data": {
    "total_adp_schemes": 1250,
    "total_published_reports": 4380,
    "total_satisfactory_reports": 3520,
    "total_unsatisfactory_reports": 860,
    "generated_at": "2026-09-07T14:00:00+05:00"
  },
  "meta": {
    "generated_at": "2026-09-07T14:00:00+05:00",
    "timezone": "Asia/Karachi",
    "adp_year": "2026-2027",
    "filters": { "year": null }
  }
}
```

### 14.3 Implementation notes

- Three cheap COUNT queries (or one SQL with conditional SUMs for reports) plus one ADP count on core.
- Do not load report payloads.
- Indexes: `mec_reports(status, assessment_status)`, `mec_reports(status, approved_at)`.

---

## 15. API-02 — Region-wise MEC statistics

**Endpoint:** `GET /api/v1/public/mec/statistics/regions`

### 15.1 Response fields (array item)

| Field | Type | Logic |
|-------|------|--------|
| `region_id` | integer | `mec_regions.id` |
| `region_name` | string | `mec_regions.name` |
| `total_adp_schemes` | integer | Distinct ADP schemes in region scope (§7.7) |
| `total_published_reports` | integer | Published reports on plans with this `mec_region_id` |
| `total_satisfactory_reports` | integer | Same + `assessment_status = satisfactory` |
| `total_unsatisfactory_reports` | integer | Same + `unsatisfactory` |

Return **all active regions**, including zeros, ordered by `mec_regions.name` ASC (stable for GIS legends). Filtering `?region_id=3` returns that region only.

### 15.2 Sample

```json
{
  "success": true,
  "message": "Request processed successfully",
  "data": [
    {
      "region_id": 1,
      "region_name": "Karachi",
      "total_adp_schemes": 320,
      "total_published_reports": 1250,
      "total_satisfactory_reports": 1010,
      "total_unsatisfactory_reports": 240
    },
    {
      "region_id": 2,
      "region_name": "Hyderabad",
      "total_adp_schemes": 285,
      "total_published_reports": 980,
      "total_satisfactory_reports": 790,
      "total_unsatisfactory_reports": 190
    }
  ],
  "meta": {
    "generated_at": "2026-09-07T14:00:00+05:00",
    "adp_year": "2026-2027",
    "unassigned_reports": 0,
    "schemes_may_appear_in_multiple_regions": true
  }
}
```

---

## 16. API-03 — Planned scheme coordinates

**Endpoint:** `GET /api/v1/public/mec/schemes/planned/coordinates`

### 16.1 Response fields (array item)

| Field | Type | Source |
|-------|------|--------|
| `scheme_id` | integer | `projects.project_id` |
| `scheme_name` | string | `projects.name` |
| `adp_scheme_code` | string \| null | `projects.adp_number` (null if empty) |
| `region_id` | integer \| null | `plans.mec_region_id` |
| `region_name` | string \| null | `mec_regions.name` |
| `latitude` | number \| null | §9 |
| `longitude` | number \| null | §9 |
| `status` | string | always `"planned"` |

If the original website contract requires a string `scheme_id` like `"ADP-2026-0001"`, add `scheme_uid` (`projects.code`) and keep integer `scheme_id`. Do not invent formatted IDs.

### 16.2 Pagination

Use Laravel `LengthAwarePaginator` (or equivalent) on the **distinct scheme** query, then hydrate names/coords for the page only.

### 16.3 Sample

```json
{
  "success": true,
  "message": "Request processed successfully",
  "data": [
    {
      "scheme_id": 1001,
      "scheme_name": "Example Road Improvement Scheme",
      "adp_scheme_code": "ADP-26-001",
      "region_id": 1,
      "region_name": "Karachi",
      "latitude": 24.8607,
      "longitude": 67.0011,
      "status": "planned"
    }
  ],
  "meta": {
    "generated_at": "2026-09-07T14:00:00+05:00",
    "page": 1,
    "per_page": 100,
    "total": 1,
    "last_page": 1
  }
}
```

---

## 17. API-04 — Visited scheme coordinates and status

**Endpoint:** `GET /api/v1/public/mec/schemes/visited/coordinates`

### 17.1 Response fields (array item)

| Field | Type | Source |
|-------|------|--------|
| `scheme_id` | integer | `projects.project_id` |
| `scheme_name` | string | `projects.name` |
| `adp_scheme_code` | string \| null | `projects.adp_number` |
| `region_id` | integer \| null | plan of **latest** published report |
| `region_name` | string \| null | |
| `latitude` | number \| null | §9 |
| `longitude` | number \| null | §9 |
| `visit_date` | date `YYYY-MM-DD` \| null | §7.6 |
| `status` | string | `"satisfactory"` or `"unsatisfactory"` |

One row per scheme (latest published assessed report), not one row per historical visit.

`?status=satisfactory` filters after resolving the latest tone.

### 17.2 Sample

```json
{
  "success": true,
  "data": [
    {
      "scheme_id": 1001,
      "scheme_name": "Example Road Improvement Scheme",
      "adp_scheme_code": "ADP-26-001",
      "region_id": 1,
      "region_name": "Karachi",
      "latitude": 24.8607,
      "longitude": 67.0011,
      "visit_date": "2026-08-21",
      "status": "satisfactory"
    },
    {
      "scheme_id": 1002,
      "scheme_name": "Example Water Supply Scheme",
      "adp_scheme_code": "ADP-26-002",
      "region_id": 1,
      "region_name": "Karachi",
      "latitude": 24.875,
      "longitude": 67.03,
      "visit_date": "2026-08-24",
      "status": "unsatisfactory"
    }
  ]
}
```

---

## 18. Optional individual statistic endpoints

Only implement if product insists on one URL per number. Prefer the four endpoints above.

| Req | Path | `data` |
|-----|------|--------|
| 1 | `GET .../statistics/total-adp-schemes` | `{ "total_adp_schemes": 1250 }` |
| 2 | `GET .../statistics/published-reports` | `{ "total_published_reports": 4380 }` |
| 3 | `GET .../statistics/satisfactory-reports` | `{ "total_satisfactory_reports": 3520 }` |
| 4 | `GET .../statistics/unsatisfactory-reports` | `{ "total_unsatisfactory_reports": 860 }` |
| 5 | `GET .../statistics/adp-schemes/regions` | `[{ "region_id", "region_name", "total_adp_schemes" }]` |
| 6 | `GET .../statistics/published-reports/regions` | region + `total_published_reports` |
| 7 | `GET .../statistics/satisfactory-reports/regions` | region + `total_satisfactory_reports` |
| 8 | `GET .../statistics/unsatisfactory-reports/regions` | region + `total_unsatisfactory_reports` |

If added, they must call the **same service methods** as API-01/02 so numbers cannot drift.

---

## 19. Recommended Laravel implementation

### 19.1 New files (do not invent extra entry points)

```
app/Http/Controllers/Api/V1/PublicMec/PublicMecStatisticsController.php
app/Http/Controllers/Api/V1/PublicMec/PublicMecSchemeCoordinatesController.php
app/Http/Controllers/Api/V1/PublicMec/Concerns/RespondsWithPublicMecJson.php
app/Http/Requests/Api/V1/PublicMec/PublicMecIndexRequest.php
app/Http/Resources/Api/V1/PublicMec/RegionStatisticsResource.php
app/Http/Resources/Api/V1/PublicMec/PlannedSchemeCoordinateResource.php
app/Http/Resources/Api/V1/PublicMec/VisitedSchemeCoordinateResource.php
app/Services/Api/V1/PublicMec/PublicMecStatisticsService.php
app/Services/Api/V1/PublicMec/PublicMecSchemeCoordinatesService.php
app/Services/Api/V1/PublicMec/PublicMecAdpYearResolver.php
app/Services/Api/V1/PublicMec/PublicMecRegionScopeService.php
app/Services/Api/V1/PublicMec/PublicMecCoordinateResolver.php
tests/Feature/Api/V1/PublicMec/PublicMecStatisticsTest.php
tests/Feature/Api/V1/PublicMec/PublicMecSchemeCoordinatesTest.php
docs/openapi/mec-public-v1.yaml
```

### 19.2 Controller rules

- Thin controllers: validate → service → resource.
- Trait similar to `RespondsWithEaedJson`, but always include `meta`.
- `abort(405)` is automatic if only GET is registered.
- Catch nothing that would dump exceptions; rely on `App\Exceptions\Handler` to convert to the public envelope **without** debug when `APP_DEBUG=false`. Add a dedicated render for this route prefix if the global handler currently returns HTML.

### 19.3 Service rules

- `PublicMecStatisticsService` and `PublicMecSchemeCoordinatesService` must **not** call `auth()`, `SchemeAccessHelper::allowedProjectIds()`, or `applyMapSchemeVisibilityScope()`.
- They **may** copy SQL fragments from dashboard/map services.
- Core reads: `Project::query()->withoutGlobalScope(OrderByScope::class)` and do not rely on `PublishScope` if it would hide unpublished (you already filter `published = 1` explicitly).
- Decode HTML entities in `projects.name` if `DecodesHtmlEntityAttributes` would do so on the Eloquent model; Query Builder will not.

### 19.4 JSON resources

Cast:

- integers with `(int)`
- floats with `round((float) $lat, 6)` — JSON number
- dates with `Y-m-d`
- empty `adp_number` → `null`, not `""`

---

## 20. SQL / query blueprints

Aliases: `p` = `projects`, `r` = `mec_reports`, `ps` = `plan_schemes`, `pv` = `plan_versions`, `pl` = `plans`, `mr` = `mec_regions`.

### 20.1 ADP year → budget id (core)

```sql
SELECT announce_budget_id, year
FROM announce_budgets
WHERE year = :fyLabel          -- e.g. '2026-2027'
   OR year LIKE CONCAT(:calendarYear, '-%')
ORDER BY year DESC
LIMIT 1;
```

### 20.2 Total ADP schemes (core)

```sql
SELECT COUNT(p.project_id) AS total_adp_schemes
FROM projects p
WHERE p.status = 1
  AND p.published = 1
  AND p.completion_status <> 'Drop'
  AND EXISTS (
        SELECT 1
        FROM project_budget_allocation pba
        WHERE pba.project_id = p.project_id
          AND pba.announce_budget_id = :budgetId
      );
```

### 20.3 Published / SAT / UnSAT (default DB)

All-time:

```sql
SELECT
  SUM(r.status = 'published') AS total_published_reports,
  SUM(r.status = 'published' AND r.assessment_status = 'satisfactory') AS total_satisfactory_reports,
  SUM(r.status = 'published' AND r.assessment_status = 'unsatisfactory') AS total_unsatisfactory_reports
FROM mec_reports r;
```

With plan year:

```sql
SELECT
  SUM(r.status = 'published') AS total_published_reports,
  SUM(r.status = 'published' AND r.assessment_status = 'satisfactory') AS total_satisfactory_reports,
  SUM(r.status = 'published' AND r.assessment_status = 'unsatisfactory') AS total_unsatisfactory_reports
FROM mec_reports r
INNER JOIN plan_schemes ps ON ps.id = r.plan_scheme_id
INNER JOIN plan_versions pv ON pv.id = ps.plan_version_id
INNER JOIN plans pl ON pl.id = pv.plan_id
WHERE pl.year = :year;
```

(When filtering by year, still count only `status = 'published'`.)

### 20.4 Reports by region

```sql
SELECT
  mr.id AS region_id,
  mr.name AS region_name,
  SUM(r.status = 'published') AS total_published_reports,
  SUM(r.status = 'published' AND r.assessment_status = 'satisfactory') AS total_satisfactory_reports,
  SUM(r.status = 'published' AND r.assessment_status = 'unsatisfactory') AS total_unsatisfactory_reports
FROM mec_regions mr
LEFT JOIN plans pl ON pl.mec_region_id = mr.id
LEFT JOIN plan_versions pv ON pv.plan_id = pl.id
LEFT JOIN plan_schemes ps ON ps.plan_version_id = pv.id
LEFT JOIN mec_reports r ON r.plan_scheme_id = ps.id AND r.status = 'published'
WHERE mr.status = 1
  AND (:year IS NULL OR pl.year = :year)
GROUP BY mr.id, mr.name
ORDER BY mr.name;
```

If the LEFT JOIN inflates rows, prefer a subquery of report aggregates grouped by `pl.mec_region_id`, then LEFT JOIN to `mec_regions`.

Unassigned:

```sql
SELECT COUNT(*) 
FROM mec_reports r
INNER JOIN plan_schemes ps ON ps.id = r.plan_scheme_id
INNER JOIN plan_versions pv ON pv.id = ps.plan_version_id
INNER JOIN plans pl ON pl.id = pv.plan_id
WHERE r.status = 'published'
  AND (pl.mec_region_id IS NULL OR pl.mec_region_id = 0);
```

### 20.5 ADP schemes by region

PHP algorithm (clearer than one cross-db SQL):

1. Load active regions.
2. Load `mec_region_scope` for those ids.
3. Split `locationIds` vs `divisionIds` (district ids).
4. Query core projects that pass ADP filters **and**:

```sql
AND EXISTS (
  SELECT 1
  FROM project_locations ploc
  INNER JOIN locations l ON l.location_id = ploc.location_id
  WHERE ploc.project_id = p.project_id
    AND (
      l.location_id IN (:locationIds)          -- empty IN must be skipped in PHP
      OR l.district_id IN (:divisionIds)
    )
)
OR p.location_id IN (:locationIds)
OR p.district_id IN (:divisionIds)
```

Mirror `SchemeListService` (`whereHas('locations', ...)` with `scope_type` location vs division).

5. Count **distinct** `project_id` per region independently (a scheme in two regions increments both).

### 20.6 Planned coordinates (distinct scheme)

```sql
SELECT
  ps.scheme_id,
  MAX(ps.id) AS plan_scheme_id,
  MAX(pl.mec_region_id) AS mec_region_id
FROM plan_schemes ps
INNER JOIN plan_versions pv ON pv.id = ps.plan_version_id
INNER JOIN plans pl ON pl.id = pv.plan_id
WHERE pl.status = 'approved'
  AND pl.is_active = 1
  AND pv.version_no = pl.current_version
  AND ps.rd_rejected_at IS NULL
  AND (:year IS NULL OR pl.year = :year)
  AND (:regionId IS NULL OR pl.mec_region_id = :regionId)
GROUP BY ps.scheme_id;
```

Then batch-load `projects` (names, adp_number, lat/lng) and `mec_regions` names for the page of scheme ids.

If a scheme appears on two regions’ current plans, `MAX(pl.mec_region_id)` is arbitrary. Better: pick the row with latest `pl.year`, then `pl.month`, then `ps.visit_date` using a window:

```sql
SELECT * FROM (
  SELECT
    ps.scheme_id,
    pl.mec_region_id,
    ps.visit_date,
    ROW_NUMBER() OVER (
      PARTITION BY ps.scheme_id
      ORDER BY pl.year DESC, pl.month DESC, ps.visit_date DESC, ps.id DESC
    ) AS rn
  FROM plan_schemes ps
  ...
) t WHERE t.rn = 1;
```

(If MySQL version lacks window functions, emulate in PHP.)

### 20.7 Visited coordinates (latest published assessed report)

```sql
SELECT * FROM (
  SELECT
    ps.scheme_id,
    r.assessment_status,
    r.approved_at,
    r.submitted_at,
    r.updated_at,
    ps.visit_date AS plan_visit_date,
    ps.id AS plan_scheme_id,
    pl.mec_region_id,
    ROW_NUMBER() OVER (
      PARTITION BY ps.scheme_id
      ORDER BY COALESCE(r.approved_at, r.updated_at) DESC, r.id DESC
    ) AS rn
  FROM mec_reports r
  INNER JOIN plan_schemes ps ON ps.id = r.plan_scheme_id
  INNER JOIN plan_versions pv ON pv.id = ps.plan_version_id
  INNER JOIN plans pl ON pl.id = pv.plan_id
  WHERE r.status = 'published'
    AND r.assessment_status IN ('satisfactory', 'unsatisfactory')
    AND (:year IS NULL OR pl.year = :year)
    AND (:regionId IS NULL OR pl.mec_region_id = :regionId)
) t
WHERE t.rn = 1
  AND (:status IS NULL OR t.assessment_status = :status);
```

Then attach `MAX(plan_scheme_dates.visit_date)` for those `plan_scheme_id`s and core project coords.

### 20.8 Coordinate expression (core)

```sql
COALESCE(
  NULLIF(p.latitude, 0),
  (
    SELECT NULLIF(pl2.latitude, 0)
    FROM project_locations pl2
    WHERE pl2.project_id = p.project_id
      AND pl2.latitude IS NOT NULL AND pl2.latitude != 0
      AND pl2.longitude IS NOT NULL AND pl2.longitude != 0
    ORDER BY pl2.id
    LIMIT 1
  )
) AS latitude
```

Same pattern for longitude. Copy from `AdpSchemesMapService` constants `LAT_EXPR` / `LNG_EXPR` if keeping full SQL in one query.

---

## 21. Caching, rate limiting, CORS, logging

### 21.1 Cache

| Endpoint | TTL | Key |
|----------|-----|-----|
| summary | 10 minutes | `public_mec:v1:summary:{year|all}` |
| regions | 10 minutes | `public_mec:v1:regions:{year|all}:{region_id|all}` |
| planned / visited | 5 minutes | include `page`, `per_page`, filters |

Use `Cache::remember`. Invalidate is not required for v1 (TTL is enough). If MEC publishes a report and needs instant GIS, drop TTL to 2–5 minutes or forget keys on report publish in `ReportsController` (optional later).

Dashboard already uses 300 seconds (`MecDashboardDataService::CACHE_TTL_SECONDS`). Align public stats to **600 seconds** unless product wants fresher GIS.

### 21.2 Rate limit

In `RouteServiceProvider::boot()`:

```php
RateLimiter::for('mec-public', function (Request $request) {
    return Limit::perMinute(120)->by($request->ip());
});
```

Return 429 JSON envelope. Do not require API keys after the limit.

The `api` group already applies `throttle:api` (60/min). Either:

- raise that for this prefix only via `withoutMiddleware` + `throttle:mec-public`, or
- keep 60/min if infrastructure is small.

### 21.3 CORS

`config/cors.php` already allows `api/*` from `*` with `allowed_methods: *`. For production MEC website:

```php
'allowed_origins' => [
    'https://mecsindh.gov.pk',
    'https://www.mecsindh.gov.pk',
],
'allowed_methods' => ['GET', 'OPTIONS'],
```

If unrestricted public embedding is required, keep `*`. `supports_credentials` must stay `false`.

### 21.4 HTTPS / headers

- Production: force HTTPS (`APP_URL`, load balancer).
- Optional response headers: `Cache-Control: public, max-age=60` (CDN) in addition to server cache.
- `X-Content-Type-Options: nosniff`.

### 21.5 Logging / monitoring

- Existing `RequestLoggingMiddleware` is global; ensure it does not write query strings that could later be published.
- Do not add public log endpoints.
- Metrics: count 2xx/4xx/5xx per route; alert on 5xx.

### 21.6 GET-only enforcement

Do not register other verbs. Middleware `Illuminate\Http\Middleware\HandleCors` will still answer OPTIONS for browsers.

---

## 22. OpenAPI / Swagger

Commit `docs/openapi/mec-public-v1.yaml` (OpenAPI 3.0) with:

- servers (staging + production)
- four paths
- query parameters
- schemas for envelope, summary, region row, planned point, visited point, error
- examples
- security: empty (`security: []`)

Serve internally for developers (not a requirement to expose Swagger UI on the public internet). If exposing UI, it must not sit behind login in a way that implies the **data** APIs need login.

---

## 23. Tests

Add Feature tests that hit HTTP (no actingAs).

Minimum cases:

| Test | Expect |
|------|--------|
| `GET summary` unauthenticated | 200, integer fields, no user/payload keys |
| `GET summary?year=1999` | 400 |
| `GET regions` | one object per active region, keys only as specified |
| Region ADP sum vs overall | document multi-region; assert overall equals §7.1 query |
| Report region sum + `unassigned_reports` = overall published | |
| Planned: unpublished plan excluded | factory/plan `status=draft` not listed |
| Planned: `rd_rejected_at` set excluded | |
| Visited: `processing` report excluded | |
| Visited: latest of two published reports wins | |
| Visited: `status=satisfactory` omits unsat | |
| Coords: lat=0 treated as null | |
| Response has no `payload`, `user_id`, `phone` | |
| `POST` same URL | 405 |
| Pagination `per_page=2` | `meta.total` / page size |

Use existing model factories where present; otherwise create minimal `plans` / `plan_versions` / `plan_schemes` / `mec_reports` rows in the test DB. Core `projects` may need a sqlite/mysql test connection — if core is unavailable in CI, mock `PublicMecAdpYearResolver` and coordinate loader behind an interface.

---

## 24. Acceptance criteria

- Unauthenticated clients can retrieve all ten outputs via the four GET endpoints (or the optional split endpoints).
- JSON UTF-8; numbers are JSON numbers.
- ADP totals match `MecDashboardPortfolioService::totalSchemesForAnnounceBudgetYear()` for the same FY (when no user is logged in / unrestricted).
- Published / SAT / UnSAT match `MecDashboardReportsAggregateService::globalStats()` published fields (unrestricted).
- Region report counts use `plans.mec_region_id`, same join family as `publishedByRegion()`.
- GIS planned set is the approved current-version plan-scheme set used by `MecDashboardVisitsAggregateService::loadVisitSchemeRows()`.
- GIS visited status uses latest published `assessment_status` only.
- Missing coordinates are `null`.
- No PII, workflow, payload, or unpublished rows.
- GET-only; HTTPS in production; errors are generic; rate limit does not require a key.
- OpenAPI file committed.
- Feature tests green in CI.

---

## 25. Implementation checklist

1. Add `mec-public` rate limiter.
2. Register GET routes under `v1/public/mec` with **no** auth middleware.
3. Implement Form Request validation.
4. Implement ADP year resolver (`announce_budgets`).
5. Implement statistics service (summary + regions).
6. Implement region-scope scheme counter (copy `SchemeListService` logic, no request user).
7. Implement coordinate resolver (copy map LAT/LNG exprs, one pin per scheme).
8. Implement planned + visited list services with pagination.
9. JSON resources + envelope trait.
10. Exception handler JSON for this prefix.
11. Cache keys + TTL.
12. Feature tests.
13. OpenAPI YAML.
14. Staging URL given to MEC website / GIS team.
15. Optional nginx aliases to `/api/v1/statistics/*` if the public site cannot use `/public/mec`.
16. Production CORS origins if not `*`.
17. Confirm with MEC whether completed ADP schemes stay in the public total (this spec: **yes**, if allocated and not Dropped).

---

## 26. Appendix A — Column reference

### `mec_regions`

`id`, `name`, `short_code`, `description`, `address`, `phone`, `status`, `created_by`, `updated_by`, `created_at`, `updated_at`

### `mec_region_scope`

`id`, `mec_region_id`, `scope_type`, `scope_id`, `created_at`, `updated_at`

### `plans` (subset)

`id`, `is_active`, `plan_type`, `month`, `year`, `mec_region_id`, `current_version`, `status`, `approved_visit_plan_uid`, …

### `plan_versions`

`id`, `plan_id`, `version_no`, …

### `plan_schemes`

`id`, `plan_version_id`, `scheme_id`, `monitoring_officer_id`, `visit_location`, `visit_date`, `physical_progress`, `financial_progress`, `rd_rejected_at`, `rd_rejected_by`, `rd_rejection_remarks`, …

### `plan_scheme_dates`

`id`, `plan_scheme_id`, `visit_date`

### `mec_reports`

`id`, `user_id`, `plan_scheme_id`, `report_type`, `mec_sector_id`, `mec_sub_sector_id`, `status`, `assessment_status`, `workflow_status`, `workflow_assignee_user_id`, `submitted_at`, `approved_at`, `declined_at`, `published_report_uid`, `publish_letter_number`, `publish_letter_reference_no`, `publish_letter_fiscal_year_label`, `payload`, timestamps

### `projects` (core, subset)

`project_id`, `name`, `code`, `adp_number`, `status`, `published`, `completion_status`, `project_type`, `latitude`, `longitude`, `location_id`, `district_id`, `department_id`, `mec_status`, `mec_sector`, …

### `project_locations` (core)

`id` (or equivalent PK), `project_id`, `location_id`, `latitude`, `longitude`, …

### `announce_budgets` (core)

`announce_budget_id`, `year`, `amount`, `status`, …

### `project_budget_allocation` (core)

`project_id`, `announce_budget_id`, capital/revenue/expenditure columns (not public)

---

## 27. Appendix B — Existing code to reuse (do not copy user-scope)

| Class | What to copy | What to strip |
|-------|----------------|---------------|
| `MecDashboardPortfolioService::totalSchemesForAnnounceBudgetYear()` | ADP + active/published + not Dropped | none (already unrestricted aside from those filters) |
| `MecDashboardReportsAggregateService::globalStats()` | published / SAT / UnSAT counts | `applySchemeScope()` |
| `MecDashboardReportsAggregateService::publishedByRegion()` | join `plans.mec_region_id` | `applySchemeScopeToJoin()` |
| `MecDashboardVisitsAggregateService::loadVisitSchemeRows()` | approved current-version plans | `allowedProjectIds()` |
| `AdpSchemesMapService` LAT_EXPR / LNG_EXPR | coordinate fallback | `applyMapSchemeListingScope` visibility; report payload GPS unless approved |
| `SchemeNameListingTones::latestPublishedReportToneByProjectId()` | latest published SAT/UnSAT | payload fallback only if `assessment_status` empty |
| `SchemeListService` mec_region_ids filter | `mec_region_scope` location vs division | HTTP request / user |
| `RespondsWithEaedJson` | envelope style | EAED error codes that imply auth |

PHP constants:

```php
MecReport::STATUS_PUBLISHED;            // 'published'
MecReport::ASSESSMENT_SATISFACTORY;     // 'satisfactory'
MecReport::ASSESSMENT_UNSATISFACTORY;   // 'unsatisfactory'
SchemeAccessHelper::COMPLETION_STATUS_DROP; // 'Drop'
```

---

## 28. Appendix C — Sample payloads

Full examples are in §14–§17. Minimal GIS GeoJSON is **not** required in v1; clients convert lat/lng to points. If a later GeoJSON endpoint is added, use `FeatureCollection` with the same properties and still no authentication.

---

## Formal request (for the implementing team)

Implement a public, read-only, versioned REST API for the Monitoring & Evaluation Cell that returns: (i) total ADP schemes, (ii) total published reports, (iii) total satisfactory reports, (iv) total unsatisfactory reports, (v–viii) the same counts by MEC region, (ix) coordinates of planned schemes, and (x) coordinates of visited schemes with satisfactory/unsatisfactory status. Use the counting and join rules in this document against the live SIMS tables. No authentication. GET only. JSON envelope. HTTPS. Cache and rate-limit without introducing API keys. Do not expose unpublished, personal, or workflow data.

---

*End of document.*