# 12 — Master Data: Locations (Countries / States / Cities)

## Overview

CRUD for the geographic master data that drives **every dropdown on both VisoAdmin and the public apps**. Three resources: countries, states (FK country), cities (FK state).

**Status:** Live

---

## User Stories

| ID | As a | I want to | So that |
|----|------|-----------|---------|
| LOC-01 | Ops (perm 1) | List all countries / states / cities, paginated | Manage geography |
| LOC-02 | Ops | Add a new country, state, or city | Extend coverage |
| LOC-03 | Ops | Edit name / sort name / status of each | Fix typos |
| LOC-04 | Ops | Toggle `status` 0/1 | Hide locations from dropdowns without deleting |
| LOC-05 | RM (perm 8) | Get an active states list when editing an event | Form dropdowns work |
| LOC-06 | Form JS | Cascade — get cities for selected state | Cascading dropdowns |

---

## Screens & Flows

```
┌──────────────────────────┐   ┌──────────────────────────┐   ┌──────────────────────────┐
│ /location/countries      │   │ /location/states         │   │ /location/cities         │
│ countriesList.blade.php  │   │ statesList.blade.php     │   │ citiesList.blade.php     │
└────────────┬─────────────┘   └────────────┬─────────────┘   └────────────┬─────────────┘
             │ DataTable AJAX               │                              │
             ▼                              ▼                              ▼
   /location/ajaxCountriesList   /location/ajaxStatesList         /location/ajaxCitiesList

   Add country  ──▶ POST /location/add-country
   Edit country ──▶ GET  /location/country/{id} (modal data)
                    POST /location/country         (save)
   (same pattern for states & cities)

   Cascading dropdowns:
   GET /location/get-countries-for-dropdown
   GET /location/get-states-for-dropdown
   GET /location/get-cities-by-state/{stateId}
```

### Routes & Actions

| Route | Method | Handler | Description |
|-------|--------|---------|-------------|
| `/location/countries` | GET | `LocationController::countriesList()` | List page |
| `/location/ajaxCountriesList` | GET | `LocationController::ajaxCountriesList()` | DataTable JSON |
| `/location/country/{id}` | GET | `LocationController::getCountry()` | Modal data |
| `/location/add-country` | POST | `LocationController::addCountry()` | Validated insert |
| `/location/country` | POST | `LocationController::updateCountry()` | Update |
| `/location/get-countries-for-dropdown` | GET | `LocationController::getCountryForDropdown()` | `[{id, name}, ...]` (active only) |
| `/location/states` | GET | `LocationController::statesList()` | List page |
| `/location/ajaxStatesList` | GET | `LocationController::ajaxStatesList()` | DataTable JSON (with event_count per state) |
| `/location/state/{id}` | GET | `LocationController::getState()` | Modal data |
| `/location/add-state` | POST | `LocationController::addState()` | Validated insert |
| `/location/state` | POST | `LocationController::updateState()` | Update |
| `/location/get-states-for-dropdown` | GET | `LocationController::getStatesForDropdown()` | `[{id, name}, ...]` |
| `/location/cities` | GET | `LocationController::citiesList()` | List page |
| `/location/ajaxCitiesList` | GET | `LocationController::ajaxCitiesList()` | DataTable JSON (with state+country join) |
| `/location/city/{id}` | GET | `LocationController::getCity()` | Modal data |
| `/location/add-city` | POST | `LocationController::addCity()` | Validated insert |
| `/location/get-cities-by-state/{stateId}` | GET | `LocationController::getCitiesByState()` | Active cities for a state |

> Note: `LocationController::updateCity` is NOT defined — there's no city-update endpoint. **Partial.**

---

## Data Model

### `countries`

```php
// App\Models\Countries
protected $table = 'countries';
public $timestamps = false;

{
  id:       int,
  name:     string,    // unique, max 50
  sortname: string,    // unique, max 5 (ISO-2 country code)
  status:   0|1,
}
```

### `states`

```php
// App\Models\States
protected $table = 'states';
public $timestamps = false;

{
  id:         int,
  name:       string,
  country_id: int,     // FK countries.id
  status:     0|1,
}
```

### `cities`

```php
// App\Models\Cities
protected $table = 'cities';
public $timestamps = false;

{
  id:       int,
  name:     string,
  state_id: int,        // FK states.id
  status:   0|1,
}
```

The state list adds a `event_count` aggregated column via LEFT JOIN to `events`.

---

## Validations & Business Rules

| Rule | Detail |
|------|--------|
| Country name unique on add | `unique:countries,name`, `max:50` |
| Country sortname unique on add | `unique:countries,sortname`, `max:5` |
| State name unique on add | `unique:states,name`, `max:50` |
| City name unique on add | `unique:cities,name`, `max:50` |
| State requires `country_id` | `required \| numeric` |
| City requires `state_id` | `required \| numeric` |
| Status required | `required \| numeric` (0 or 1) |
| No `unique` check on EDIT | Editing to a duplicate name will succeed if the model has no DB-level unique constraint |
| Cities-by-state filters `status=1` | But list page shows ALL cities regardless of status |
| Dropdowns (`/get-*-for-dropdown`) filter `status=1` | So an active city under an inactive state appears in dropdowns |

---

## API Endpoints

(All require session auth. Most are AJAX-whitelisted via `permissionGroup`.)

| Method | Path | Request | Response | Consumer |
|--------|------|---------|----------|----------|
| GET | `/location/countries` | — | HTML | Browser |
| GET | `/location/ajaxCountriesList` | `draw, start, length` | DataTable JSON | Country list |
| GET | `/location/country/{id}` | path id | `{ id, name, sortname, status }` | Edit modal |
| POST | `/location/add-country` | `new_country_name, new_country_sortname, new_country_status` | redirect | Add form |
| POST | `/location/country` | `country_id, country_name, country_sortname, country_status` | redirect | Edit form |
| GET | `/location/get-countries-for-dropdown` | — | `[{id, name}, ...]` | Form JS |
| GET | `/location/states` | — | HTML | Browser |
| GET | `/location/ajaxStatesList` | `draw, start, length` | DataTable JSON | State list |
| GET | `/location/state/{id}` | path id | `{ id, name, country_id, status }` | Edit modal |
| POST | `/location/add-state` | `new_state_name, new_state_country_id, new_state_status` | redirect | Add form |
| POST | `/location/state` | `state_id, state_name, country_id, state_status` | redirect | Edit form |
| GET | `/location/get-states-for-dropdown` | — | `[{id, name}, ...]` | Form JS |
| GET | `/location/cities` | — | HTML | Browser |
| GET | `/location/ajaxCitiesList` | `draw, start, length, search.value?` | DataTable JSON (with state + country names) | City list |
| GET | `/location/city/{id}` | path id | `{ id, name, state_id, status }` | Edit modal |
| POST | `/location/add-city` | `new_city_name, new_state_id, new_city_status` | redirect | Add form |
| GET | `/location/get-cities-by-state/{stateId}` | path stateId | `[{id, name}, ...]` (active) | Cascading dropdown |

---

## Upstream Impact

- **None — locations are root master data.**

---

## Downstream Impact

- **Every dropdown** in:
  - Feature 05 Vendor Mgmt (cities multi-select)
  - Feature 06 Event Oversight (read-only display)
  - Feature 07 Coordinator Console (vendor candidate by city)
  - Feature 09 RM Create Event (country/state/city cascading)
  - Feature 10 Critical Events (state_name, location columns)
  - Feature 11 Analytics (distinct location count)
- **`vendor_services_locations`** — joined by `location_id = cities.id`.
- **`events.cities_id`, `events.state_id`, `events.country_id`** — point at these IDs.

---

## Impact of Changes

| If you change... | Risk to... | Level | Type |
|-----------------|------------|-------|------|
| Deleting a city referenced by a vendor or event | Orphans on the FK; UI shows blanks | High | Data |
| Toggling a country `status = 0` | Doesn't cascade to states or cities — they still appear in dropdowns through other endpoints | High | UI |
| Renaming a city | Vendor & event displays update (joins are by id), but any cached views of "Mumbai" → "Bombay" silently change for everyone | Medium | UI |
| Adding required columns to any of the three tables | `add*` controllers may fail | Medium | Data |
| Removing UAE from countries | Feature 09 RM Create Event aborts with flash error | High | Data |

---

## Known Issues

- **No update-city endpoint** — only add-city. To rename, ops must edit the row in MySQL directly. **Partial feature**.
- **Disabling a country does NOT cascade** — states under it remain active.
- **Validation of `status` only checks `numeric`** — `status = 5` is technically accepted and might break UI badges.
- **The state list adds `event_count`** via LEFT JOIN to events — slow as event volume grows.
- **Cities `unique:cities,name`** — Mumbai cannot exist in two countries. Globally-unique city names are unrealistic at scale.
- **City list has search but list page may not pass it** — `getCitiesList` reads `search.value`. States/Countries don't have search.