no-mistakes(document): Sync AGENTS.md taxonomy, drop unused imports

Items 1-5, 9, 11 from denya-wahab-feedback-s2:
- Category.show_in_form (alert-only flag): Gas Leak hidden from issue picker
  but kept urgent for SLA/alert and reporting; emergency quick path on the
  new-issue form creates urgent tickets via include_hidden categories.
- Seed: Aluminum/Glass, Carpentry, Mould & Damp (Medium default) maintenance
  categories; Lost Property renamed Missing Item (+ sub).
- One alembic migration: add show_in_form (backfill True, Gas Leak False) +
  data rename Lost Property -> Missing Item.
- Property -> Building -> Apartment cascade with searchable apartment combobox
  on the new-issue form and ticket list filters; /api/tickets/units gains
  building filter + /units/grouped variant; /api/tickets gains additive
  building/unit_id filters. apartment_mapping.json committed (deterministic).
- Group-by-priority toggle on /tickets (four sections + unknown bucket,
  age-sortable, composes with filters, URL deep links), FM dashboard active
  tickets, and CS dashboard priority card click-through.
- Tests: 13 new (category visibility, seed idempotency/sync, unit grouping,
  ticket building/unit filters).

.gitignore

@@ -6,6 +6,7 @@ __pycache__/
 .venv/
 uploads/
 test_*.py
+!tests/test_*.py
 venv/
 *.egg-info/
 dist/

AGENTS.md

@@ -22,6 +22,7 @@ app/
 ├── services/      # Business logic (auth, seed, ticket, sla)
 └── routers/       # FastAPI route handlers
 alembic/           # Database migrations
+tests/             # pytest suite; conftest.py swaps DATABASE_URL to a temp SQLite
 uploads/           # Photo uploads (created at runtime)
 ```
 
@@ -29,6 +30,7 @@ uploads/           # Photo uploads (created at runtime)
 
 - `alembic upgrade head` — apply migrations
 - `alembic revision --autogenerate -m "msg"` — new migration
+- `pytest` — run the API test suite (tests/; pagination contract anchored in tests/test_tickets_pagination.py)
 
 ## Seed data
 
@@ -37,7 +39,7 @@ Users, units, and categories are auto-seeded on first startup via lifespan hook:
 - 120 apartment units (East/West, 10 floors × 6 apts per wing)
 - Default password for all seed users: `denya123`
 - Units load from `apartment_mapping.json` if present, else built-in fallback
-- Categories: 20 top-level (10 Maintenance, 6 CS, 4 Emergency) with sub-categories, seeded from `app/services/seed.py::SEED_CATEGORIES_DATA`
+- Categories: 23 top-level (13 Maintenance, 6 CS, 4 Emergency) with sub-categories, seeded from `app/services/seed.py::SEED_CATEGORIES_DATA`
 
 ## Key API endpoints
 
@@ -70,15 +72,15 @@ Frontend: Alpine.js (CDN) + Tailwind CSS (CDN). Auth state in localStorage. Role
 | Method | Path | Auth | Description |
 |--------|------|------|-------------|
 | POST | `/api/tickets` | Bearer | Create ticket (auto-number PAV-YYYY-NNNNN) |
-| GET | `/api/tickets` | No | List tickets (filter: status, priority, property, category_id, assigned_to, date_from, date_to) |
+| GET | `/api/tickets` | No | List tickets (filter: status, priority, property, building, unit_id, category_id, assigned_to, date_from, date_to) |
 | GET | `/api/tickets/{id}` | No | Get ticket detail with timeline, photos, SLA status |
 | PATCH | `/api/tickets/{id}` | Bearer | Update ticket (validates status transitions) |
 | POST | `/api/tickets/{id}/status` | Bearer | Change status with note |
 | GET | `/api/tickets/{id}/sla` | No | Check SLA breach status |
 | POST | `/api/tickets/{id}/photos` | Bearer | Upload photos (multipart, is_before param) |
 | GET | `/api/tickets/{id}/photos` | No | List photos |
-| GET | `/api/tickets/categories` | No | Category tree (optional `?type=` filter) |
+| GET | `/api/tickets/categories` | No | Category tree (filters: `type`; alert-only hidden unless `include_hidden=true`) |
-| GET | `/api/tickets/categories/flat` | No | Flat category list |
+| GET | `/api/tickets/categories/flat` | No | Flat category list (same `type`/`include_hidden` filters) |
 
 ## Auth
 
@@ -114,6 +116,23 @@ Stored under `uploads/` with UUID filenames. Static-files mounted at `/uploads/`
 SQLite via aiosqlite with async SQLAlchemy 2.0. Alembic for migrations.
 Tables: users, units, categories, tickets, ticket_timeline, ticket_photos, escalations, whatsapp_log
 
+## Categories & location (Sprint A)
+
+- `Category.show_in_form` (default True) marks alert-only categories: `Gas Leak` is hidden
+  from the issue picker but keeps `sla_urgency="urgent"` for SLA/alert/reporting. Pickers
+  (`/api/tickets/categories[/flat]`) exclude them unless `include_hidden=true` (used by the
+  emergency quick path on `tickets/new.html`). Seed taxonomy lives in `app/services/seed.py`
+  (`SEED_CATEGORIES_DATA`); the Lost Property → Missing Item rename is a data migration
+  (alembic `b2f4a6c8e0d2`), with a startup self-heal (`app/main.py::ensure_legacy_schema`)
+  applying the same column/rename fix to legacy create_all databases. Seeds are idempotent
+  and sync `show_in_form` on existing rows.
+- Location hierarchy is Property → Building → Apartment (uses `Unit.building`).
+  `GET /api/tickets/units/grouped` returns `{property: {building: [units]}}`;
+  `/api/tickets` accepts additive `building`/`unit_id` filters. Unit data is deterministic
+  from the committed `apartment_mapping.json` (built-in fallback in `seed.py`).
+- Priority grouping ("Group by priority") is client-side via `app().groupByPriority()` in
+  `app/templates/base.html`; used by `tickets/list.html` and `dashboard/fm.html`.
+
 ## Maintaining this file
 
 Keep this file for knowledge useful to almost every future agent session in this project.

alembic/versions/b2f4a6c8e0d2_category_show_in_form_and_missing_item_rename.py

@@ -0,0 +1,60 @@
+"""category show_in_form column + Lost Property → Missing Item rename
+
+Revision ID: b2f4a6c8e0d2
+Revises: 795c6b95f637
+Create Date: 2026-08-02 00:00:00.000000
+
+Sprint A (Wahab feedback batch): one schema change + one data rename.
+
+* Add ``categories.show_in_form`` (bool, default True) and backfill existing
+  rows to True; flip Gas Leak to False (alert-only: still urgent for SLA/alert
+  and reporting, but hidden from the new-issue category picker).
+* Rename the Customer Service category ``Lost Property`` → ``Missing Item``.
+  The seed is insert-if-missing, so without this data migration an existing
+  database would keep the old row and the seed would create a duplicate.
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'b2f4a6c8e0d2'
+down_revision: Union[str, Sequence[str], None] = '795c6b95f637'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    """Add show_in_form, backfill True, hide Gas Leak, rename Lost Property."""
+    op.add_column(
+        'categories',
+        sa.Column(
+            'show_in_form',
+            sa.Boolean(),
+            nullable=False,
+            server_default=sa.true(),
+            comment='Visible in the new-issue category picker (False = alert-only, e.g. Gas Leak)',
+        ),
+    )
+    # Backfill: Gas Leak stays in the SLA/alert table (urgent) but is hidden
+    # from the new-issue picker.
+    op.execute(
+        "UPDATE categories SET show_in_form = 0 "
+        "WHERE type = 'emergency' AND name = 'Gas Leak' AND parent_id IS NULL"
+    )
+    # Rename Lost Property → Missing Item (data migration; seed is insert-if-missing).
+    op.execute(
+        "UPDATE categories SET name = 'Missing Item' "
+        "WHERE type = 'cs' AND name = 'Lost Property'"
+    )
+
+
+def downgrade() -> None:
+    """Reverse the rename and drop the column."""
+    op.execute(
+        "UPDATE categories SET name = 'Lost Property' "
+        "WHERE type = 'cs' AND name = 'Missing Item'"
+    )
+    op.drop_column('categories', 'show_in_form')

apartment_mapping.json

@@ -0,0 +1,726 @@
+{
+  "version": 1,
+  "property": "Pavilion Accra",
+  "units": [
+    {
+      "apartment_code": "011E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 1
+    },
+    {
+      "apartment_code": "012E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 1
+    },
+    {
+      "apartment_code": "013E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 1
+    },
+    {
+      "apartment_code": "014E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 1
+    },
+    {
+      "apartment_code": "015E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 1
+    },
+    {
+      "apartment_code": "016E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 1
+    },
+    {
+      "apartment_code": "021E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 2
+    },
+    {
+      "apartment_code": "022E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 2
+    },
+    {
+      "apartment_code": "023E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 2
+    },
+    {
+      "apartment_code": "024E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 2
+    },
+    {
+      "apartment_code": "025E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 2
+    },
+    {
+      "apartment_code": "026E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 2
+    },
+    {
+      "apartment_code": "031E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 3
+    },
+    {
+      "apartment_code": "032E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 3
+    },
+    {
+      "apartment_code": "033E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 3
+    },
+    {
+      "apartment_code": "034E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 3
+    },
+    {
+      "apartment_code": "035E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 3
+    },
+    {
+      "apartment_code": "036E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 3
+    },
+    {
+      "apartment_code": "041E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 4
+    },
+    {
+      "apartment_code": "042E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 4
+    },
+    {
+      "apartment_code": "043E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 4
+    },
+    {
+      "apartment_code": "044E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 4
+    },
+    {
+      "apartment_code": "045E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 4
+    },
+    {
+      "apartment_code": "046E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 4
+    },
+    {
+      "apartment_code": "051E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 5
+    },
+    {
+      "apartment_code": "052E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 5
+    },
+    {
+      "apartment_code": "053E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 5
+    },
+    {
+      "apartment_code": "054E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 5
+    },
+    {
+      "apartment_code": "055E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 5
+    },
+    {
+      "apartment_code": "056E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 5
+    },
+    {
+      "apartment_code": "061E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 6
+    },
+    {
+      "apartment_code": "062E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 6
+    },
+    {
+      "apartment_code": "063E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 6
+    },
+    {
+      "apartment_code": "064E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 6
+    },
+    {
+      "apartment_code": "065E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 6
+    },
+    {
+      "apartment_code": "066E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 6
+    },
+    {
+      "apartment_code": "071E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 7
+    },
+    {
+      "apartment_code": "072E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 7
+    },
+    {
+      "apartment_code": "073E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 7
+    },
+    {
+      "apartment_code": "074E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 7
+    },
+    {
+      "apartment_code": "075E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 7
+    },
+    {
+      "apartment_code": "076E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 7
+    },
+    {
+      "apartment_code": "081E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 8
+    },
+    {
+      "apartment_code": "082E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 8
+    },
+    {
+      "apartment_code": "083E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 8
+    },
+    {
+      "apartment_code": "084E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 8
+    },
+    {
+      "apartment_code": "085E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 8
+    },
+    {
+      "apartment_code": "086E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 8
+    },
+    {
+      "apartment_code": "091E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 9
+    },
+    {
+      "apartment_code": "092E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 9
+    },
+    {
+      "apartment_code": "093E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 9
+    },
+    {
+      "apartment_code": "094E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 9
+    },
+    {
+      "apartment_code": "095E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 9
+    },
+    {
+      "apartment_code": "096E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 9
+    },
+    {
+      "apartment_code": "101E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 10
+    },
+    {
+      "apartment_code": "102E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 10
+    },
+    {
+      "apartment_code": "103E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 10
+    },
+    {
+      "apartment_code": "104E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 10
+    },
+    {
+      "apartment_code": "105E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 10
+    },
+    {
+      "apartment_code": "106E",
+      "property": "East",
+      "building": "Pavilion East",
+      "floor": 10
+    },
+    {
+      "apartment_code": "011W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 1
+    },
+    {
+      "apartment_code": "012W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 1
+    },
+    {
+      "apartment_code": "013W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 1
+    },
+    {
+      "apartment_code": "014W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 1
+    },
+    {
+      "apartment_code": "015W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 1
+    },
+    {
+      "apartment_code": "016W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 1
+    },
+    {
+      "apartment_code": "021W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 2
+    },
+    {
+      "apartment_code": "022W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 2
+    },
+    {
+      "apartment_code": "023W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 2
+    },
+    {
+      "apartment_code": "024W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 2
+    },
+    {
+      "apartment_code": "025W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 2
+    },
+    {
+      "apartment_code": "026W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 2
+    },
+    {
+      "apartment_code": "031W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 3
+    },
+    {
+      "apartment_code": "032W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 3
+    },
+    {
+      "apartment_code": "033W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 3
+    },
+    {
+      "apartment_code": "034W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 3
+    },
+    {
+      "apartment_code": "035W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 3
+    },
+    {
+      "apartment_code": "036W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 3
+    },
+    {
+      "apartment_code": "041W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 4
+    },
+    {
+      "apartment_code": "042W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 4
+    },
+    {
+      "apartment_code": "043W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 4
+    },
+    {
+      "apartment_code": "044W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 4
+    },
+    {
+      "apartment_code": "045W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 4
+    },
+    {
+      "apartment_code": "046W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 4
+    },
+    {
+      "apartment_code": "051W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 5
+    },
+    {
+      "apartment_code": "052W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 5
+    },
+    {
+      "apartment_code": "053W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 5
+    },
+    {
+      "apartment_code": "054W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 5
+    },
+    {
+      "apartment_code": "055W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 5
+    },
+    {
+      "apartment_code": "056W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 5
+    },
+    {
+      "apartment_code": "061W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 6
+    },
+    {
+      "apartment_code": "062W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 6
+    },
+    {
+      "apartment_code": "063W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 6
+    },
+    {
+      "apartment_code": "064W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 6
+    },
+    {
+      "apartment_code": "065W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 6
+    },
+    {
+      "apartment_code": "066W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 6
+    },
+    {
+      "apartment_code": "071W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 7
+    },
+    {
+      "apartment_code": "072W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 7
+    },
+    {
+      "apartment_code": "073W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 7
+    },
+    {
+      "apartment_code": "074W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 7
+    },
+    {
+      "apartment_code": "075W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 7
+    },
+    {
+      "apartment_code": "076W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 7
+    },
+    {
+      "apartment_code": "081W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 8
+    },
+    {
+      "apartment_code": "082W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 8
+    },
+    {
+      "apartment_code": "083W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 8
+    },
+    {
+      "apartment_code": "084W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 8
+    },
+    {
+      "apartment_code": "085W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 8
+    },
+    {
+      "apartment_code": "086W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 8
+    },
+    {
+      "apartment_code": "091W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 9
+    },
+    {
+      "apartment_code": "092W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 9
+    },
+    {
+      "apartment_code": "093W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 9
+    },
+    {
+      "apartment_code": "094W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 9
+    },
+    {
+      "apartment_code": "095W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 9
+    },
+    {
+      "apartment_code": "096W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 9
+    },
+    {
+      "apartment_code": "101W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 10
+    },
+    {
+      "apartment_code": "102W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 10
+    },
+    {
+      "apartment_code": "103W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 10
+    },
+    {
+      "apartment_code": "104W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 10
+    },
+    {
+      "apartment_code": "105W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 10
+    },
+    {
+      "apartment_code": "106W",
+      "property": "West",
+      "building": "Pavilion West",
+      "floor": 10
+    }
+  ]
+}

app/main.py

@@ -9,6 +9,7 @@ from pathlib import Path
 from fastapi import FastAPI
 from fastapi.middleware.cors import CORSMiddleware
 from fastapi.staticfiles import StaticFiles
+from sqlalchemy import text
 
 from app.core.config import settings
 from app.core.database import Base, async_session_factory, engine
@@ -18,12 +19,34 @@ from app.services.seed import seed_categories, seed_units, seed_users
 logger = logging.getLogger(__name__)
 
 
+async def ensure_legacy_schema(conn) -> None:
+    """Add columns/data changes from alembic migrations that legacy create_all databases lack."""
+    result = await conn.execute(text("PRAGMA table_info(categories)"))
+    columns = {row[1] for row in result}
+    if "show_in_form" not in columns:
+        await conn.execute(
+            text("ALTER TABLE categories ADD COLUMN show_in_form BOOLEAN NOT NULL DEFAULT 1")
+        )
+        logger.info("Added missing categories.show_in_form column (legacy database)")
+    result = await conn.execute(
+        text(
+            "UPDATE categories SET name = 'Missing Item' "
+            "WHERE type = 'cs' AND name = 'Lost Property' AND parent_id IS NULL "
+            "AND NOT EXISTS (SELECT 1 FROM categories c2 "
+            "WHERE c2.type = 'cs' AND c2.name = 'Missing Item' AND c2.parent_id IS NULL)"
+        )
+    )
+    if result.rowcount:
+        logger.info("Renamed legacy 'Lost Property' category to 'Missing Item'")
+
+
 @asynccontextmanager
 async def lifespan(app: FastAPI):
     """Initialise database and seed data on startup."""
     logger.info("Starting Denya OneCare …")
     async with engine.begin() as conn:
         await conn.run_sync(Base.metadata.create_all)
+        await ensure_legacy_schema(conn)
     async with async_session_factory() as session:
         await seed_users(session)
         await session.commit()

app/models/category.py

@@ -2,7 +2,7 @@
 
 from __future__ import annotations
 
-from sqlalchemy import ForeignKey, Integer, String
+from sqlalchemy import Boolean, ForeignKey, Integer, String, true
 from sqlalchemy.orm import Mapped, mapped_column, relationship
 
 from app.core.database import Base
@@ -28,6 +28,13 @@ class Category(Base):
         nullable=True,
         comment="urgent, high, medium, low",
     )
+    show_in_form: Mapped[bool] = mapped_column(
+        Boolean,
+        nullable=False,
+        default=True,
+        server_default=true(),
+        comment="Visible in the new-issue category picker (False = alert-only, e.g. Gas Leak)",
+    )
 
     # self-referencing relationship
     children: Mapped[list[Category]] = relationship("Category", back_populates="parent", cascade="all, delete-orphan")

app/routers/tickets.py

@@ -2,7 +2,6 @@
 
 from __future__ import annotations
 
-import os
 import uuid
 from datetime import datetime
 from pathlib import Path
@@ -45,33 +44,61 @@ UPLOADS_DIR.mkdir(parents=True, exist_ok=True)
 async def list_units(
     db: Annotated[AsyncSession, Depends(get_db)],
     property_filter: str | None = Query(None, alias="property"),
+    building: str | None = Query(None),
 ) -> list[Unit]:
-    """List all units, optionally filtered by property (East/West)."""
+    """List all units, optionally filtered by property (East/West) and building."""
     query = select(Unit).order_by(Unit.apartment_code)
     if property_filter:
         query = query.where(Unit.property == property_filter)
+    if building:
+        query = query.where(Unit.building == building)
     result = await db.execute(query)
     return list(result.scalars().all())
 
 
+@router.get("/units/grouped")
+async def list_units_grouped(
+    db: Annotated[AsyncSession, Depends(get_db)],
+    property_filter: str | None = Query(None, alias="property"),
+) -> dict[str, dict[str, list[dict]]]:
+    """Return units grouped as ``{property: {building: [units]}}``.
+
+    Additive convenience variant of ``GET /api/tickets/units`` for the
+    Property → Building → Apartment cascade. Units without a building are
+    grouped under an empty-string key.
+    """
+    query = select(Unit).order_by(Unit.property, Unit.building, Unit.apartment_code)
+    if property_filter:
+        query = query.where(Unit.property == property_filter)
+    result = await db.execute(query)
+    grouped: dict[str, dict[str, list[dict]]] = {}
+    for unit in result.scalars().all():
+        prop = unit.property or ""
+        building = unit.building or ""
+        grouped.setdefault(prop, {}).setdefault(building, []).append(
+            UnitOut.model_validate(unit).model_dump()
+        )
+    return grouped
+
+
 # ── Categories ──────────────────────────────────────────────────────
-async def _build_category_tree(db: AsyncSession, parent_id: int | None = None) -> list[CategoryTreeOut]:
+async def _build_category_tree(db: AsyncSession, parent_id: int | None = None, include_hidden: bool = False) -> list[CategoryTreeOut]:
     """Build a nested category tree."""
-    result = await db.execute(
+    query = select(Category).where(Category.parent_id == parent_id).order_by(Category.name)
-        select(Category)
+    if not include_hidden:
-        .where(Category.parent_id == parent_id)
+        query = query.where(Category.show_in_form.is_(True))
-        .order_by(Category.name)
+    result = await db.execute(query)
-    )
     categories = result.scalars().all()
     tree = []
     for cat in categories:
-        children = await _build_category_tree(db, cat.id)
+        children = await _build_category_tree(db, cat.id, include_hidden=include_hidden)
         tree.append(CategoryTreeOut(
             id=cat.id,
             type=cat.type,
             name=cat.name,
             parent_id=cat.parent_id,
             sla_urgency=cat.sla_urgency,
+            show_in_form=cat.show_in_form,
             children=children,
         ))
     return tree
@@ -81,23 +108,26 @@ async def _build_category_tree(db: AsyncSession, parent_id: int | None = None) -
 async def list_categories(
     db: Annotated[AsyncSession, Depends(get_db)],
     type_filter: str | None = Query(None, alias="type"),
+    include_hidden: bool = Query(False, description="Include alert-only categories (e.g. Gas Leak)"),
 ) -> list[CategoryTreeOut]:
-    """Return the full category tree, optionally filtered by type (maintenance, cs, emergency)."""
+    """Return the full category tree, optionally filtered by type (maintenance, cs, emergency).
+
+    Alert-only categories (``show_in_form=False``, e.g. Gas Leak) are excluded
+    unless ``include_hidden=true`` is passed (used by the emergency quick path).
+    """
     if type_filter:
         # Return flat list of top-level categories of the given type
-        result = await db.execute(
+        query = select(Category).where(Category.parent_id.is_(None), Category.type == type_filter).order_by(Category.name)
-            select(Category)
+        if not include_hidden:
-            .where(Category.parent_id.is_(None), Category.type == type_filter)
+            query = query.where(Category.show_in_form.is_(True))
-            .order_by(Category.name)
+        result = await db.execute(query)
-        )
         parents = result.scalars().all()
         tree = []
         for parent in parents:
-            children_result = await db.execute(
+            children_query = select(Category).where(Category.parent_id == parent.id).order_by(Category.name)
-                select(Category)
+            if not include_hidden:
-                .where(Category.parent_id == parent.id)
+                children_query = children_query.where(Category.show_in_form.is_(True))
-                .order_by(Category.name)
+            children_result = await db.execute(children_query)
-            )
             children = children_result.scalars().all()
             tree.append(CategoryTreeOut(
                 id=parent.id,
@@ -105,24 +135,43 @@ async def list_categories(
                 name=parent.name,
                 parent_id=parent.parent_id,
                 sla_urgency=parent.sla_urgency,
+                show_in_form=parent.show_in_form,
                 children=[CategoryOut.model_validate(c) for c in children],
             ))
         return tree
-    return await _build_category_tree(db)
+    return await _build_category_tree(db, include_hidden=include_hidden)
 
 
 @router.get("/categories/flat", response_model=list[CategoryOut])
 async def list_categories_flat(
     db: Annotated[AsyncSession, Depends(get_db)],
     type_filter: str | None = Query(None, alias="type"),
+    include_hidden: bool = Query(False, description="Include alert-only categories (e.g. Gas Leak)"),
 ) -> list[CategoryOut]:
-    """Return a flat list of all categories (no nesting), optionally filtered by type."""
+    """Return a flat list of all categories (no nesting), optionally filtered by type.
+
+    Alert-only categories are excluded by default; children of alert-only parents
+    are excluded too so no orphaned picker entries leak through.
+    """
     query = select(Category).order_by(Category.type, Category.name)
     if type_filter:
         query = query.where(Category.type == type_filter)
     result = await db.execute(query)
     categories = result.scalars().all()
-    return [CategoryOut.model_validate(c) for c in categories]
+    if include_hidden:
+        return [CategoryOut.model_validate(c) for c in categories]
+    # Exclude alert-only categories and any children whose parent is alert-only.
+    hidden_ids = {
+        c.id
+        for c in categories
+        if not c.show_in_form and c.parent_id is None
+    }
+    visible = [
+        c
+        for c in categories
+        if c.show_in_form and (c.parent_id is None or c.parent_id not in hidden_ids)
+    ]
+    return [CategoryOut.model_validate(c) for c in visible]
 
 
 # ── Ticket CRUD ──────────────────────────────────────────────────────
@@ -149,6 +198,8 @@ async def list_tickets(
     status: str | None = Query(None),
     priority: str | None = Query(None),
     property: str | None = Query(None),
+    building: str | None = Query(None),
+    unit_id: int | None = Query(None),
     category_id: int | None = Query(None),
     assigned_to: int | None = Query(None),
     date_from: datetime | None = Query(None),
@@ -160,6 +211,8 @@ async def list_tickets(
         status_filter=status,
         priority_filter=priority,
         property_filter=property,
+        building_filter=building,
+        unit_id=unit_id,
         category_id=category_id,
         assigned_to=assigned_to,
         date_from=date_from,

app/schemas/ticket.py

@@ -5,7 +5,7 @@ from __future__ import annotations
 from datetime import datetime
 from decimal import Decimal
 
-from pydantic import BaseModel, Field
+from pydantic import BaseModel
 
 
 # ── Unit ─────────────────────────────────────────────────────────────
@@ -26,6 +26,7 @@ class CategoryOut(BaseModel):
     name: str
     parent_id: int | None = None
     sla_urgency: str | None = None
+    show_in_form: bool = True
 
     model_config = {"from_attributes": True}
 

app/services/seed.py

@@ -135,23 +135,39 @@ SEED_CATEGORIES_DATA: list[dict] = [
     {"type": "maintenance", "name": "Security", "subs": ["Lock broken", "Door not closing", "Window latch", "CCTV issue"]},
     {"type": "maintenance", "name": "Internet", "subs": ["WiFi down", "Slow speed", "Router reset"]},
     {"type": "maintenance", "name": "Structural", "subs": ["Wall crack", "Ceiling leak", "Floor tile", "Paint touch-up"]},
+    {"type": "maintenance", "name": "Aluminum/Glass",
+     "subs": ["Window repair", "Window replacement", "Door repair (sliding)", "Door repair (fixed)",
+              "Glass replacement", "Aluminum frame repair", "Shower screen", "Mirror replacement"]},
+    {"type": "maintenance", "name": "Carpentry",
+     "subs": ["Door repair", "Door replacement", "Door frame", "Cabinet repair", "Wardrobe repair",
+              "Shelving", "Timber repair", "Loose hinge/lock plate"]},
+    {"type": "maintenance", "name": "Mould & Damp",
+     "subs": ["Wall mould", "Ceiling damp patch", "Bathroom mould", "Mould after leak",
+              "Damp proofing / remediation"],
+     "sla_urgency": "medium"},
     # ── Customer Service ─────────────────────────────────────────
     {"type": "cs", "name": "Check-in", "subs": ["Early check-in", "Key handover", "Welcome instructions"]},
     {"type": "cs", "name": "Check-out", "subs": ["Late checkout", "Key return", "Inspection"]},
     {"type": "cs", "name": "Housekeeping", "subs": ["Mid-stay cleaning", "Linen change", "Restocking"]},
-    {"type": "cs", "name": "Lost Property", "subs": ["Guest left items behind"]},
+    {"type": "cs", "name": "Missing Item", "subs": ["Guest left items behind", "Item search request"]},
     {"type": "cs", "name": "Billing", "subs": ["Invoice question", "Payment issue", "Deposit query"]},
     {"type": "cs", "name": "Staff Behaviour", "subs": ["Staff conduct feedback"]},
     # ── Emergency ────────────────────────────────────────────────
     {"type": "emergency", "name": "Fire", "subs": ["Smoke detected", "Fire alarm", "Sprinkler issue"], "sla_urgency": "urgent"},
     {"type": "emergency", "name": "Flood", "subs": ["Major water leak", "Burst pipe", "Overflowing"], "sla_urgency": "urgent"},
-    {"type": "emergency", "name": "Gas Leak", "subs": ["Gas smell", "Suspected leak"], "sla_urgency": "urgent"},
+    {"type": "emergency", "name": "Gas Leak", "subs": ["Gas smell", "Suspected leak"], "sla_urgency": "urgent", "show_in_form": False},
     {"type": "emergency", "name": "Electrical Hazard", "subs": ["Sparking", "Exposed wires", "Power outage multiple units"], "sla_urgency": "urgent"},
 ]
 
 
 async def seed_categories(db: AsyncSession) -> list[Category]:
-    """Seed the categories table with the full PRD §7 taxonomy."""
+    """Seed the categories table with the full PRD §7 taxonomy.
+
+    Idempotent: only inserts missing rows. ``show_in_form`` on existing
+    parents is synced when the seed data explicitly sets it (e.g. Gas Leak
+    is alert-only, so already-seeded databases get flipped to False on the
+    next startup).
+    """
     created: list[Category] = []
 
     for group in SEED_CATEGORIES_DATA:
@@ -170,10 +186,16 @@ async def seed_categories(db: AsyncSession) -> list[Category]:
                 name=group["name"],
                 parent_id=None,
                 sla_urgency=group.get("sla_urgency"),
+                show_in_form=group.get("show_in_form", True),
             )
             db.add(parent)
             await db.flush()
             created.append(parent)
+        elif "show_in_form" in group and parent.show_in_form != group["show_in_form"]:
+            # Sync visibility for existing rows (e.g. Gas Leak → alert-only)
+            parent.show_in_form = group["show_in_form"]
+            await db.flush()
+            created.append(parent)
 
         # Seed sub-categories
         for sub_name in group["subs"]:

app/services/ticket.py

@@ -156,6 +156,8 @@ async def list_tickets(
     status_filter: str | None = None,
     priority_filter: str | None = None,
     property_filter: str | None = None,
+    building_filter: str | None = None,
+    unit_id: int | None = None,
     category_id: int | None = None,
     assigned_to: int | None = None,
     date_from: datetime | None = None,
@@ -177,6 +179,12 @@ async def list_tickets(
         # Join unit to filter by property
         query = query.join(Ticket.unit).where(Unit.property == property_filter)
         count_query = count_query.join(Ticket.unit).where(Unit.property == property_filter)
+    if building_filter:
+        query = query.join(Ticket.unit).where(Unit.building == building_filter)
+        count_query = count_query.join(Ticket.unit).where(Unit.building == building_filter)
+    if unit_id:
+        query = query.where(Ticket.unit_id == unit_id)
+        count_query = count_query.where(Ticket.unit_id == unit_id)
     if category_id:
         query = query.where(Ticket.category_id == category_id)
         count_query = count_query.where(Ticket.category_id == category_id)
@@ -196,7 +204,7 @@ async def list_tickets(
 
     # Paginate
     offset = (page - 1) * page_size
-    query = query.order_by(Ticket.created_at.desc()).offset(offset).limit(page_size).options(selectinload(Ticket.assigned_technician))
+    query = query.order_by(Ticket.created_at.desc(), Ticket.id.desc()).offset(offset).limit(page_size).options(selectinload(Ticket.assigned_technician))
 
     result = await db.execute(query)
     tickets = list(result.scalars().all())

app/templates/base.html

@@ -12,17 +12,23 @@
                 extend: {
                     colors: {
                         denya: {
-                            50: '#eff6ff',
+                            50: '#e8f0ea',
-                            100: '#dbeafe',
+                            100: '#c5d9cb',
-                            200: '#bfdbfe',
+                            200: '#9ebfaa',
-                            300: '#93c5fd',
+                            300: '#74a589',
-                            400: '#60a5fa',
+                            400: '#4d8c69',
-                            500: '#3b82f6',
+                            500: '#2d734d',
-                            600: '#2563eb',
+                            600: '#1d5a3a',
-                            700: '#1d4ed8',
+                            700: '#0d2b18',
-                            800: '#1e40af',
+                            800: '#0a2012',
-                            900: '#1e3a8a',
+                            900: '#07150c',
-                        }
+                        },
+                        gold: {
+                            DEFAULT: '#c8a96e',
+                            light: '#e8d5a8',
+                            dark: '#a88a4e',
+                        },
+                        cream: '#faf8f5',
                     }
                 }
             }
@@ -49,49 +55,54 @@
         .priority-high { @apply bg-orange-100 text-orange-800 border-orange-300; }
         .priority-medium { @apply bg-yellow-100 text-yellow-800 border-yellow-300; }
         .priority-low { @apply bg-green-100 text-green-800 border-green-300; }
+        .brand-gradient { background: linear-gradient(135deg, #0d2b18 0%, #1a3d24 100%); }
     </style>
 </head>
-<body class="bg-gray-50 min-h-screen" x-data="app()" x-init="init()">
+<body class="bg-cream min-h-screen text-[#1a1a1a]" x-data="app()" x-init="init()">
     <!-- Nav Bar -->
-    <nav class="bg-white border-b border-gray-200 shadow-sm sticky top-0 z-50" x-show="isLoggedIn" x-cloak>
+    <nav class="bg-[#0d2b18] border-b border-denya-800 shadow-lg sticky top-0 z-50" x-show="isLoggedIn" x-cloak>
         <div class="max-w-7xl mx-auto px-4 sm:px-6 lg:px-8">
             <div class="flex items-center justify-between h-16">
                 <!-- Left side -->
                 <div class="flex items-center space-x-4">
-                    <a href="/dashboard/cs" class="flex items-center space-x-2 text-denya-700 font-bold text-lg">
+                    <a href="/dashboard/cs" class="flex items-center space-x-3">
-                        <svg class="w-8 h-8" fill="none" stroke="currentColor" viewBox="0 0 24 24">
+                        <!-- Denya Developers Logo Mark -->
-                            <path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M19 21V5a2 2 0 00-2-2H7a2 2 0 00-2 2v16m14 0h2m-2 0h-5m-9 0H3m2 0h5M9 7h1m-1 4h1m4-4h1m-1 4h1m-5 10v-5a1 1 0 011-1h2a1 1 0 011 1v5m-4 0h4"/>
+                        <div class="w-9 h-9 bg-gold rounded-lg flex items-center justify-center shadow-sm">
-                        </svg>
+                            <svg class="w-5 h-5 text-[#0d2b18]" fill="none" stroke="currentColor" stroke-width="2.5" viewBox="0 0 24 24">
-                        <span>Denya OneCare</span>
+                                <path stroke-linecap="round" stroke-linejoin="round" d="M3 12l2-2m0 0l7-7 7 7M5 10v10a1 1 0 001 1h3m10-11l2 2m-2-2v10a1 1 0 01-1 1h-3m-6 0a1 1 0 001-1v-4a1 1 0 011-1h2a1 1 0 011 1v4a1 1 0 001 1m-6 0h6"/>
+                            </svg>
+                        </div>
+                        <div class="flex flex-col">
+                            <span class="text-white font-bold text-base leading-tight">Denya Developers</span>
+                            <span class="text-gold text-xs leading-tight font-medium">OneCare</span>
+                        </div>
                     </a>
-                    <!-- CS Links -->
+                    <!-- Nav Links -->
                     <template x-if="isCS">
                         <div class="hidden md:flex space-x-1 ml-6">
-                            <a href="/dashboard/cs" class="px-3 py-2 rounded-md text-sm font-medium" :class="currentPath === '/dashboard/cs' ? 'bg-denya-50 text-denya-700' : 'text-gray-600 hover:text-gray-900 hover:bg-gray-50'">Dashboard</a>
+                            <a href="/dashboard/cs" class="px-3 py-2 rounded-md text-sm font-medium transition-colors" :class="currentPath === '/dashboard/cs' ? 'bg-denya-800 text-gold' : 'text-gray-300 hover:text-white hover:bg-denya-800/50'">Dashboard</a>
-                            <a href="/tickets" class="px-3 py-2 rounded-md text-sm font-medium" :class="currentPath.startsWith('/tickets') ? 'bg-denya-50 text-denya-700' : 'text-gray-600 hover:text-gray-900 hover:bg-gray-50'">All Issues</a>
+                            <a href="/tickets" class="px-3 py-2 rounded-md text-sm font-medium transition-colors" :class="currentPath.startsWith('/tickets') && !currentPath.endsWith('/new') ? 'bg-denya-800 text-gold' : 'text-gray-300 hover:text-white hover:bg-denya-800/50'">All Issues</a>
-                            <a href="/tickets/new" class="px-3 py-2 rounded-md text-sm font-medium text-gray-600 hover:text-gray-900 hover:bg-gray-50">Create Issue</a>
+                            <a href="/tickets/new" class="px-3 py-2 rounded-md text-sm font-medium text-gray-300 hover:text-white hover:bg-denya-800/50 transition-colors">Create Issue</a>
                         </div>
                     </template>
-                    <!-- FM Links -->
                     <template x-if="isFM">
                         <div class="hidden md:flex space-x-1 ml-6">
-                            <a href="/dashboard/fm" class="px-3 py-2 rounded-md text-sm font-medium" :class="currentPath === '/dashboard/fm' ? 'bg-denya-50 text-denya-700' : 'text-gray-600 hover:text-gray-900 hover:bg-gray-50'">Dashboard</a>
+                            <a href="/dashboard/fm" class="px-3 py-2 rounded-md text-sm font-medium transition-colors" :class="currentPath === '/dashboard/fm' ? 'bg-denya-800 text-gold' : 'text-gray-300 hover:text-white hover:bg-denya-800/50'">Dashboard</a>
-                            <a href="/tickets" class="px-3 py-2 rounded-md text-sm font-medium" :class="currentPath.startsWith('/tickets') ? 'bg-denya-50 text-denya-700' : 'text-gray-600 hover:text-gray-900 hover:bg-gray-50'">All Issues</a>
+                            <a href="/tickets" class="px-3 py-2 rounded-md text-sm font-medium transition-colors" :class="currentPath.startsWith('/tickets') && !currentPath.endsWith('/new') ? 'bg-denya-800 text-gold' : 'text-gray-300 hover:text-white hover:bg-denya-800/50'">All Issues</a>
-                            <a href="/tickets/new" class="px-3 py-2 rounded-md text-sm font-medium text-gray-600 hover:text-gray-900 hover:bg-gray-50">Create Issue</a>
+                            <a href="/tickets/new" class="px-3 py-2 rounded-md text-sm font-medium text-gray-300 hover:text-white hover:bg-denya-800/50 transition-colors">Create Issue</a>
                         </div>
                     </template>
-                    <!-- CEO/Director Links -->
                     <template x-if="isExecutive">
                         <div class="hidden md:flex space-x-1 ml-6">
-                            <a href="/dashboard/ceo" class="px-3 py-2 rounded-md text-sm font-medium" :class="currentPath === '/dashboard/ceo' ? 'bg-denya-50 text-denya-700' : 'text-gray-600 hover:text-gray-900 hover:bg-gray-50'">Dashboard</a>
+                            <a href="/dashboard/ceo" class="px-3 py-2 rounded-md text-sm font-medium transition-colors" :class="currentPath === '/dashboard/ceo' ? 'bg-denya-800 text-gold' : 'text-gray-300 hover:text-white hover:bg-denya-800/50'">Dashboard</a>
-                            <a href="/tickets" class="px-3 py-2 rounded-md text-sm font-medium" :class="currentPath.startsWith('/tickets') ? 'bg-denya-50 text-denya-700' : 'text-gray-600 hover:text-gray-900 hover:bg-gray-50'">Issues</a>
+                            <a href="/tickets" class="px-3 py-2 rounded-md text-sm font-medium transition-colors" :class="currentPath.startsWith('/tickets') ? 'bg-denya-800 text-gold' : 'text-gray-300 hover:text-white hover:bg-denya-800/50'">Issues</a>
                         </div>
                     </template>
                 </div>
                 <!-- Right side -->
                 <div class="flex items-center space-x-4">
-                    <span class="text-sm text-gray-600 hidden md:block" x-text="`${user.full_name} (${user.role})`"></span>
+                    <span class="text-sm text-gray-300 hidden md:block" x-text="`${user.full_name} (${user.role})`"></span>
-                    <button @click="logout()" class="px-3 py-1.5 text-sm text-red-600 hover:text-red-800 hover:bg-red-50 rounded-md transition-colors">
+                    <button @click="logout()" class="px-3 py-1.5 text-sm text-gold hover:text-gold-light hover:bg-denya-800/50 rounded-md transition-colors border border-denya-600">
                         Logout
                     </button>
                 </div>
@@ -99,19 +110,19 @@
         </div>
     </nav>
 
-    <!-- Mobile Nav (CS) -->
+    <!-- Mobile Nav -->
-    <div class="md:hidden border-b bg-white" x-show="isLoggedIn" x-cloak>
+    <div class="md:hidden border-b bg-[#0d2b18] border-denya-800" x-show="isLoggedIn" x-cloak>
         <template x-if="isCS || isFM">
             <div class="flex overflow-x-auto px-4 py-2 space-x-2">
-                <a href="/dashboard/cs" class="px-3 py-1.5 rounded text-sm font-medium whitespace-nowrap" :class="currentPath === '/dashboard/cs' ? 'bg-denya-50 text-denya-700' : 'text-gray-500'" x-text="isCS ? 'CS Dashboard' : 'FM Dashboard'"></a>
+                <a href="/dashboard/cs" class="px-3 py-1.5 rounded text-sm font-medium whitespace-nowrap" :class="currentPath === '/dashboard/cs' ? 'bg-denya-800 text-gold' : 'text-gray-300'">Dashboard</a>
-                <a href="/tickets" class="px-3 py-1.5 rounded text-sm font-medium whitespace-nowrap" :class="currentPath.startsWith('/tickets') && !currentPath.endsWith('/new') ? 'bg-denya-50 text-denya-700' : 'text-gray-500'">Issues</a>
+                <a href="/tickets" class="px-3 py-1.5 rounded text-sm font-medium whitespace-nowrap" :class="currentPath.startsWith('/tickets') && !currentPath.endsWith('/new') ? 'bg-denya-800 text-gold' : 'text-gray-300'">Issues</a>
-                <a href="/tickets/new" class="px-3 py-1.5 rounded text-sm font-medium whitespace-nowrap text-gray-500">New Issue</a>
+                <a href="/tickets/new" class="px-3 py-1.5 rounded text-sm font-medium whitespace-nowrap text-gray-300">New Issue</a>
             </div>
         </template>
         <template x-if="isExecutive">
             <div class="flex overflow-x-auto px-4 py-2 space-x-2">
-                <a href="/dashboard/ceo" class="px-3 py-1.5 rounded text-sm font-medium whitespace-nowrap" :class="currentPath === '/dashboard/ceo' ? 'bg-denya-50 text-denya-700' : 'text-gray-500'">CEO Dashboard</a>
+                <a href="/dashboard/ceo" class="px-3 py-1.5 rounded text-sm font-medium whitespace-nowrap" :class="currentPath === '/dashboard/ceo' ? 'bg-denya-800 text-gold' : 'text-gray-300'">CEO Dashboard</a>
-                <a href="/tickets" class="px-3 py-1.5 rounded text-sm font-medium whitespace-nowrap text-gray-500">Issues</a>
+                <a href="/tickets" class="px-3 py-1.5 rounded text-sm font-medium whitespace-nowrap text-gray-300">Issues</a>
             </div>
         </template>
     </div>
@@ -122,13 +133,13 @@
     </main>
 
     <!-- Loading Overlay -->
-    <div x-show="loading" class="fixed inset-0 bg-black bg-opacity-30 z-50 flex items-center justify-center" x-cloak>
+    <div x-show="loading" class="fixed inset-0 bg-[#0d2b18]/40 z-50 flex items-center justify-center" x-cloak>
-        <div class="bg-white rounded-lg p-6 flex items-center space-x-3 shadow-xl">
+        <div class="bg-white rounded-xl p-6 flex items-center space-x-3 shadow-2xl border border-denya-200">
-            <svg class="animate-spin h-6 w-6 text-denya-600" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24">
+            <svg class="animate-spin h-6 w-6 text-denya-700" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24">
                 <circle class="opacity-25" cx="12" cy="12" r="10" stroke="currentColor" stroke-width="4"></circle>
                 <path class="opacity-75" fill="currentColor" d="M4 12a8 8 0 018-8V0C5.373 0 0 5.373 0 12h4z"></path>
             </svg>
-            <span class="text-gray-700 font-medium" x-text="loadingMessage || 'Loading...'"></span>
+            <span class="text-denya-800 font-medium" x-text="loadingMessage || 'Loading...'"></span>
         </div>
     </div>
 
@@ -136,7 +147,7 @@
     <div class="fixed bottom-4 right-4 z-50 space-y-2">
         <template x-for="toast in toasts" :key="toast.id">
             <div class="px-4 py-3 rounded-lg shadow-lg text-white text-sm font-medium transition-all duration-300"
-                 :class="{'bg-green-600': toast.type === 'success', 'bg-red-600': toast.type === 'error', 'bg-blue-600': toast.type === 'info', 'bg-yellow-600': toast.type === 'warning'}"
+                 :class="{'bg-denya-600': toast.type === 'success', 'bg-red-600': toast.type === 'error', 'bg-denya-500': toast.type === 'info', 'bg-gold': toast.type === 'warning'}"
                  x-init="setTimeout(() => { toasts = toasts.filter(t => t.id !== toast.id) }, toast.duration || 4000)">
                 <span x-text="toast.message"></span>
             </div>
@@ -164,12 +175,10 @@
                 get isAdmin() { return ['Admin/Jerome', 'Admin/Wahab'].includes(this.user.role) },
 
                 init() {
-                    // Redirect to login if not logged in (skip for login page)
                     if (!this.isLoggedIn && this.currentPath !== '/login') {
                         window.location.href = '/login';
                         return;
                     }
-                    // Verify token on load
                     if (this.isLoggedIn) {
                         this.fetchMe();
                     }
@@ -209,7 +218,7 @@
                             opts.body = JSON.stringify(body);
                         } else if (body instanceof FormData) {
                             opts.body = body;
-                            delete opts.headers['Content-Type']; // Let browser set multipart boundary
+                            delete opts.headers['Content-Type'];
                             opts.headers = { 'Authorization': `Bearer ${this.token}` };
                         }
                         const res = await fetch(url, opts);
@@ -253,13 +262,11 @@
                         localStorage.setItem('refresh_token', data.refresh_token);
                         this.token = data.access_token;
 
-                        // Fetch user info
                         const me = await this.apiGet('/api/auth/me');
                         this.user = me;
                         localStorage.setItem('user', JSON.stringify(me));
                         this.isLoggedIn = true;
 
-                        // Role-based redirect
                         const role = me.role;
                         if (['CS Rep', 'CS Manager'].includes(role)) {
                             window.location.href = '/dashboard/cs';
@@ -348,6 +355,32 @@
                     if (diffHrs < 1) return '< 1h';
                     if (diffHrs < 24) return `${diffHrs}h`;
                     return `${diffDays}d`;
+                },
+
+                groupByPriority(tickets, ageDir = 'desc') {
+                    // Group tickets into Urgent/High/Medium/Low + an other bucket,
+                    // each sorted by age (desc = newest first, asc = oldest first).
+                    const order = ['urgent', 'high', 'medium', 'low'];
+                    const groups = { urgent: [], high: [], medium: [], low: [], other: [] };
+                    const age = (t) => new Date(t.created_at).getTime() || 0;
+                    (tickets || []).forEach(t => {
+                        const p = String(t.priority || '').toLowerCase();
+                        groups[order.includes(p) ? p : 'other'].push(t);
+                    });
+                    Object.keys(groups).forEach(k => {
+                        groups[k].sort((a, b) => (ageDir === 'asc' ? age(a) - age(b) : age(b) - age(a)));
+                    });
+                    return groups;
+                },
+
+                priorityGroupMeta() {
+                    return [
+                        { key: 'urgent', label: '🔴 Urgent', cls: 'border-red-200 bg-red-50' },
+                        { key: 'high', label: '🟠 High', cls: 'border-orange-200 bg-orange-50' },
+                        { key: 'medium', label: '🟡 Medium', cls: 'border-yellow-200 bg-yellow-50' },
+                        { key: 'low', label: '🟢 Low', cls: 'border-green-200 bg-green-50' },
+                        { key: 'other', label: '⚪ No priority / Unknown', cls: 'border-gray-200 bg-gray-50' },
+                    ];
                 }
             }
         }

app/templates/dashboard/ceo.html

@@ -171,10 +171,22 @@
 
             async loadData() {
                 try {
-                    const allData = await app().apiGet('/api/tickets?page_size=500');
+                    // Fetch ALL tickets via pagination. The API caps page_size at 200
-                    if (!allData?.items) return;
+                    // (app/routers/tickets.py), so a single page_size=500 request returns 422
-                    const all = allData.items;
+                    // and the dashboard renders empty KPIs. Loop pages until we have `total`
-                    const total = allData.total || all.length;
+                    // tickets so KPIs stay accurate as volume grows past 200.
+                    const all = [];
+                    const pageSize = 200;
+                    let page = 1;
+                    let total = Infinity;
+                    while (all.length < total && page <= 1000) {  // 1000-page safety bound
+                        const allData = await app().apiGet(`/api/tickets?page=${page}&page_size=${pageSize}`);
+                        if (!allData?.items || !allData.items.length) break;
+                        all.push(...allData.items);
+                        total = allData.total ?? all.length;
+                        page += 1;
+                    }
+                    if (!all.length) return;
 
                     // Basic KPIs
                     const open = all.filter(t => !['Closed', 'Completed'].includes(t.status));
@@ -237,8 +249,9 @@
                     this.charts.byProperty = { east: propData.east, west: propData.west, max: Math.max(propData.east, propData.west, 1) };
 
                     // By category — use categories endpoint
+                    // include_hidden=true so alert-only categories (Gas Leak) stay labeled in reporting
                     try {
-                        const cats = await app().apiGet('/api/tickets/categories/flat');
+                        const cats = await app().apiGet('/api/tickets/categories/flat?include_hidden=true');
                         const catCounts = {};
                         all.forEach(t => {
                             if (t.category_id) {

app/templates/dashboard/cs.html

@@ -70,23 +70,24 @@
         <!-- Open by Priority -->
         <div class="bg-white rounded-xl shadow-sm border border-gray-200 p-5">
             <h3 class="text-sm font-semibold text-gray-700 uppercase tracking-wide mb-4">Open by Priority</h3>
-            <div class="space-y-3">
+            <div class="space-y-2">
-                <div class="flex items-center justify-between">
+                <a href="/tickets?group=priority&priority=urgent" class="flex items-center justify-between rounded-lg px-2 py-1.5 hover:bg-red-50 transition">
                     <span class="text-red-600 font-medium">🔴 Urgent</span>
                     <span class="text-2xl font-bold" x-text="kpi.byPriority?.urgent || 0"></span>
-                </div>
+                </a>
-                <div class="flex items-center justify-between">
+                <a href="/tickets?group=priority&priority=high" class="flex items-center justify-between rounded-lg px-2 py-1.5 hover:bg-orange-50 transition">
                     <span class="text-orange-600 font-medium">🟠 High</span>
                     <span class="text-2xl font-bold" x-text="kpi.byPriority?.high || 0"></span>
-                </div>
+                </a>
-                <div class="flex items-center justify-between">
+                <a href="/tickets?group=priority&priority=medium" class="flex items-center justify-between rounded-lg px-2 py-1.5 hover:bg-yellow-50 transition">
                     <span class="text-yellow-600 font-medium">🟡 Medium</span>
                     <span class="text-2xl font-bold" x-text="kpi.byPriority?.medium || 0"></span>
-                </div>
+                </a>
-                <div class="flex items-center justify-between">
+                <a href="/tickets?group=priority&priority=low" class="flex items-center justify-between rounded-lg px-2 py-1.5 hover:bg-green-50 transition">
                     <span class="text-green-600 font-medium">🟢 Low</span>
                     <span class="text-2xl font-bold" x-text="kpi.byPriority?.low || 0"></span>
-                </div>
+                </a>
+                <p class="text-[11px] text-gray-400 pt-1">Click a row to open the priority-grouped queue.</p>
             </div>
         </div>
 

app/templates/dashboard/fm.html

@@ -127,11 +127,18 @@
 
     <!-- Active Tickets Table -->
     <div class="bg-white rounded-xl shadow-sm border border-gray-200">
-        <div class="px-5 py-4 border-b border-gray-200 flex items-center justify-between">
+        <div class="px-5 py-4 border-b border-gray-200 flex flex-wrap items-center justify-between gap-3">
             <h3 class="text-sm font-semibold text-gray-700 uppercase tracking-wide">Active Tickets</h3>
-            <a href="/tickets" class="text-sm text-denya-600 hover:text-denya-800">View All →</a>
+            <div class="flex items-center space-x-3">
+                <label class="flex items-center space-x-2 text-sm font-medium text-gray-700 cursor-pointer select-none">
+                    <input type="checkbox" x-model="groupByPriority" class="rounded border-gray-300 text-denya-600 focus:ring-denya-500">
+                    <span>Group by priority</span>
+                </label>
+                <button x-show="groupByPriority" @click="ageDir = ageDir === 'asc' ? 'desc' : 'asc'" class="px-2 py-1 text-xs border border-gray-300 rounded-lg hover:bg-gray-50 text-gray-600" x-text="ageDir === 'desc' ? 'Newest first ↓' : 'Oldest first ↑'"></button>
+                <a href="/tickets" class="text-sm text-denya-600 hover:text-denya-800">View All →</a>
+            </div>
         </div>
-        <div class="overflow-x-auto">
+        <div class="overflow-x-auto" x-show="!groupByPriority">
             <table class="w-full text-sm">
                 <thead class="bg-gray-50 text-gray-600 text-xs uppercase tracking-wider">
                     <tr>
@@ -160,6 +167,32 @@
                 </tbody>
             </table>
         </div>
+        <div class="space-y-3 p-3" x-show="groupByPriority">
+            <template x-for="meta in priorityGroupMeta()" :key="meta.key">
+                <div x-show="(groupedActiveTickets[meta.key] || []).length" class="rounded-xl border border-gray-200 overflow-hidden">
+                    <div class="px-4 py-2.5 flex items-center justify-between border-b border-gray-100" :class="meta.cls">
+                        <span class="text-sm font-semibold text-gray-800" x-text="`${meta.label} (${groupedActiveTickets[meta.key].length})`"></span>
+                    </div>
+                    <div class="overflow-x-auto">
+                        <table class="w-full text-sm">
+                            <tbody class="divide-y divide-gray-100">
+                                <template x-for="ticket in groupedActiveTickets[meta.key]" :key="ticket.id">
+                                    <tr class="hover:bg-gray-50 transition cursor-pointer" @click="window.location.href='/tickets/'+ticket.id">
+                                        <td class="px-5 py-3 font-medium text-denya-600" x-text="ticket.ticket_number"></td>
+                                        <td class="px-5 py-3"><span class="px-2 py-1 rounded-full text-xs font-medium" :class="statusClass(ticket.status)" x-text="ticket.status"></span></td>
+                                        <td class="px-5 py-3"><span class="px-2 py-1 rounded text-xs font-medium" :class="priorityClass(ticket.priority)" x-text="priorityBadge(ticket.priority)"></span></td>
+                                        <td class="px-5 py-3 text-gray-600" x-text="ticket.assigned_technician_name || 'Unassigned'"></td>
+                                        <td class="px-5 py-3 text-gray-600 max-w-xs truncate" x-text="ticket.description || ''"></td>
+                                        <td class="px-5 py-3 text-xs font-medium" :class="timeSince(ticket.created_at).includes('d') && parseInt(timeSince(ticket.created_at)) > 3 ? 'text-red-600' : 'text-gray-500'" x-text="timeSince(ticket.created_at)"></td>
+                                    </tr>
+                                </template>
+                            </tbody>
+                        </table>
+                    </div>
+                </div>
+            </template>
+            <div x-show="!activeTickets.length" class="px-5 py-12 text-center text-gray-400">No active tickets</div>
+        </div>
     </div>
 </div>
 
@@ -171,6 +204,12 @@
             aging: {},
             activeTickets: [],
             emergencyCount: 0,
+            groupByPriority: false,
+            ageDir: 'desc',
+
+            get groupedActiveTickets() {
+                return app().groupByPriority(this.activeTickets, this.ageDir);
+            },
 
             async init() {
                 await this.loadData();

app/templates/tickets/list.html

@@ -14,7 +14,7 @@
 
     <!-- Filters -->
     <div class="bg-white rounded-xl shadow-sm border border-gray-200 p-4 mb-6">
-        <div class="grid grid-cols-2 md:grid-cols-4 lg:grid-cols-6 gap-3">
+        <div class="grid grid-cols-2 md:grid-cols-4 xl:grid-cols-8 gap-3">
             <div>
                 <label class="block text-xs text-gray-500 font-medium mb-1">Search</label>
                 <input type="text" x-model="filters.search" @input.debounce="loadTickets()" placeholder="Ticket # or description..." class="w-full px-3 py-2 text-sm border border-gray-300 rounded-lg focus:ring-2 focus:ring-denya-500 focus:border-transparent outline-none">
@@ -52,12 +52,39 @@
             </div>
             <div>
                 <label class="block text-xs text-gray-500 font-medium mb-1">Property</label>
-                <select x-model="filters.property" @change="loadTickets()" class="w-full px-3 py-2 text-sm border border-gray-300 rounded-lg focus:ring-2 focus:ring-denya-500 outline-none">
+                <select x-model="filters.property" @change="onPropertyChange()" class="w-full px-3 py-2 text-sm border border-gray-300 rounded-lg focus:ring-2 focus:ring-denya-500 outline-none">
                     <option value="">All Properties</option>
                     <option value="East">Pavilion East</option>
                     <option value="West">Pavilion West</option>
                 </select>
             </div>
+            <div>
+                <label class="block text-xs text-gray-500 font-medium mb-1">Building</label>
+                <select x-model="filters.building" @change="onBuildingChange()" :disabled="!filters.property" class="w-full px-3 py-2 text-sm border border-gray-300 rounded-lg focus:ring-2 focus:ring-denya-500 outline-none disabled:bg-gray-50 disabled:text-gray-400">
+                    <option value="">All Buildings</option>
+                    <template x-for="b in buildings" :key="b">
+                        <option :value="b" x-text="b"></option>
+                    </template>
+                </select>
+            </div>
+            <div class="relative">
+                <label class="block text-xs text-gray-500 font-medium mb-1">Apartment</label>
+                <input type="text" x-model="searchApartment" @focus="apartmentOpen = true" @input="apartmentOpen = true" @keydown.escape="apartmentOpen = false"
+                       :disabled="!filters.building" @blur="closeApartment()"
+                       :placeholder="filterUnit ? filterUnit.apartment_code : (filters.building ? 'Search apartment...' : 'Select building')"
+                       class="w-full px-3 py-2 text-sm border border-gray-300 rounded-lg focus:ring-2 focus:ring-denya-500 outline-none disabled:bg-gray-50 disabled:text-gray-400">
+                <div x-show="apartmentOpen && filteredApartments.length" class="absolute z-20 mt-1 w-full bg-white border border-gray-200 rounded-lg shadow-lg max-h-40 overflow-y-auto">
+                    <template x-for="unit in filteredApartments" :key="unit.id">
+                        <button type="button" @mousedown.prevent="selectFilterUnit(unit)" class="block w-full text-left px-3 py-1.5 hover:bg-denya-50 text-xs">
+                            <span class="font-medium text-gray-800" x-text="unit.apartment_code"></span>
+                            <span class="text-gray-400" x-text="` · Floor ${unit.floor || '—'}`"></span>
+                        </button>
+                    </template>
+                </div>
+                <div x-show="apartmentOpen && !filteredApartments.length && filters.building" class="absolute z-20 mt-1 w-full bg-white border border-gray-200 rounded-lg shadow-lg px-3 py-2 text-xs text-gray-400">
+                    No apartments match
+                </div>
+            </div>
             <div>
                 <label class="block text-xs text-gray-500 font-medium mb-1">From</label>
                 <input type="date" x-model="filters.dateFrom" @change="loadTickets()" class="w-full px-3 py-2 text-sm border border-gray-300 rounded-lg focus:ring-2 focus:ring-denya-500 outline-none">
@@ -67,20 +94,27 @@
                 <input type="date" x-model="filters.dateTo" @change="loadTickets()" class="w-full px-3 py-2 text-sm border border-gray-300 rounded-lg focus:ring-2 focus:ring-denya-500 outline-none">
             </div>
         </div>
-        <div class="mt-3 flex items-center justify-between">
+        <div class="mt-3 flex flex-wrap items-center justify-between gap-3">
-            <div class="flex items-center space-x-2">
+            <div class="flex items-center space-x-3">
+                <label class="flex items-center space-x-2 text-sm font-medium text-gray-700 cursor-pointer select-none">
+                    <input type="checkbox" x-model="groupByPriority" @change="syncUrl()" class="rounded border-gray-300 text-denya-600 focus:ring-denya-500">
+                    <span>Group by priority</span>
+                </label>
+                <div x-show="groupByPriority" class="flex items-center space-x-2 text-xs">
+                    <button @click="ageDir = ageDir === 'asc' ? 'desc' : 'asc'" class="px-2 py-1 border border-gray-300 rounded-lg hover:bg-gray-50 text-gray-600" x-text="ageDir === 'desc' ? 'Newest first ↓' : 'Oldest first ↑'"></button>
+                </div>
                 <span class="text-xs text-gray-500">Page <span x-text="page"></span> of <span x-text="totalPages"></span></span>
             </div>
             <div class="flex items-center space-x-2">
                 <button @click="page > 1 && (page--, loadTickets())" :disabled="page <= 1" class="px-3 py-1 text-sm border rounded hover:bg-gray-50 disabled:opacity-50 disabled:cursor-not-allowed">Prev</button>
                 <button @click="page < totalPages && (page++, loadTickets())" :disabled="page >= totalPages" class="px-3 py-1 text-sm border rounded hover:bg-gray-50 disabled:opacity-50 disabled:cursor-not-allowed">Next</button>
-                <button @click="filters = {search:'',status:'',priority:'',property:'',dateFrom:'',dateTo:''}; page=1; loadTickets()" class="px-3 py-1 text-sm text-gray-500 border rounded hover:bg-gray-50">Clear Filters</button>
+                <button @click="clearFilters()" class="px-3 py-1 text-sm text-gray-500 border rounded hover:bg-gray-50">Clear Filters</button>
             </div>
         </div>
     </div>
 
-    <!-- Tickets Table -->
+    <!-- Tickets Table (flat) -->
-    <div class="bg-white rounded-xl shadow-sm border border-gray-200">
+    <div class="bg-white rounded-xl shadow-sm border border-gray-200" x-show="!groupByPriority">
         <div class="overflow-x-auto">
             <table class="w-full text-sm">
                 <thead class="bg-gray-50 text-gray-600 text-xs uppercase tracking-wider">
@@ -125,6 +159,53 @@
             </table>
         </div>
     </div>
+
+    <!-- Tickets grouped by priority -->
+    <div class="space-y-4" x-show="groupByPriority">
+        <template x-for="meta in priorityGroupMeta()" :key="meta.key">
+            <div x-show="(groupedTickets[meta.key] || []).length" class="bg-white rounded-xl shadow-sm border border-gray-200 overflow-hidden">
+                <div class="px-5 py-3 flex items-center justify-between border-b border-gray-100" :class="meta.cls">
+                    <span class="text-sm font-semibold text-gray-800" x-text="`${meta.label} (${groupedTickets[meta.key].length})`"></span>
+                </div>
+                <div class="overflow-x-auto">
+                    <table class="w-full text-sm">
+                        <thead class="bg-gray-50 text-gray-600 text-xs uppercase tracking-wider">
+                            <tr>
+                                <th class="px-5 py-3 text-left">Ticket</th>
+                                <th class="px-5 py-3 text-left">Status</th>
+                                <th class="px-5 py-3 text-left">Priority</th>
+                                <th class="px-5 py-3 text-left">Description</th>
+                                <th class="px-5 py-3 text-left">Assigned To</th>
+                                <th class="px-5 py-3 text-left">Created</th>
+                                <th class="px-5 py-3 text-left">SLA</th>
+                            </tr>
+                        </thead>
+                        <tbody class="divide-y divide-gray-100">
+                            <template x-for="ticket in groupedTickets[meta.key]" :key="ticket.id">
+                                <tr class="hover:bg-gray-50 transition cursor-pointer" @click="window.location.href='/tickets/'+ticket.id">
+                                    <td class="px-5 py-3 font-medium text-denya-600" x-text="ticket.ticket_number"></td>
+                                    <td class="px-5 py-3"><span class="px-2 py-1 rounded-full text-xs font-medium" :class="statusClass(ticket.status)" x-text="ticket.status"></span></td>
+                                    <td class="px-5 py-3"><span class="px-2 py-1 rounded text-xs font-medium" :class="priorityClass(ticket.priority)" x-text="priorityBadge(ticket.priority)"></span></td>
+                                    <td class="px-5 py-3 text-gray-600 max-w-xs truncate" x-text="ticket.description || ''"></td>
+                                    <td class="px-5 py-3 text-gray-500 text-xs" x-text="ticket.assigned_technician_name || '—'"></td>
+                                    <td class="px-5 py-3 text-gray-500 text-xs" x-text="formatDate(ticket.created_at)"></td>
+                                    <td class="px-5 py-3">
+                                        <span x-show="ticket.sla_deadline" class="text-xs" :class="new Date(ticket.sla_deadline) < new Date() && !['Closed','Completed'].includes(ticket.status) ? 'text-red-600 font-medium' : 'text-gray-400'">
+                                            <span x-text="formatDateShort(ticket.sla_deadline)"></span>
+                                        </span>
+                                        <span x-show="!ticket.sla_deadline" class="text-xs text-gray-300">—</span>
+                                    </td>
+                                </tr>
+                            </template>
+                        </tbody>
+                    </table>
+                </div>
+            </div>
+        </template>
+        <div x-show="!groupedTotal && !loading" class="bg-white rounded-xl shadow-sm border border-gray-200 px-5 py-16 text-center text-gray-400">
+            No tickets match your filters
+        </div>
+    </div>
 </div>
 
 <script>
@@ -137,25 +218,130 @@
             totalPages: 1,
             sortField: 'created_at',
             sortDir: 'desc',
+            groupByPriority: false,
+            ageDir: 'desc',
+            unitGroups: {},
+            buildings: [],
+            searchApartment: '',
+            apartmentOpen: false,
+            filterUnit: null,
             filters: {
                 search: '',
                 status: '',
                 priority: '',
                 property: '',
+                building: '',
+                unitId: '',
                 dateFrom: '',
                 dateTo: ''
             },
 
             async init() {
+                this.applyUrlParams();
+                await this.loadUnits();
                 await this.loadTickets();
             },
 
+            applyUrlParams() {
+                const params = new URLSearchParams(window.location.search);
+                if (params.get('group') === 'priority') this.groupByPriority = true;
+                if (params.get('priority')) this.filters.priority = params.get('priority');
+                if (params.get('property')) this.filters.property = params.get('property');
+                if (params.get('building')) this.filters.building = params.get('building');
+                if (params.get('unit_id')) this.filters.unitId = params.get('unit_id');
+            },
+
+            syncUrl() {
+                const params = new URLSearchParams();
+                if (this.groupByPriority) params.set('group', 'priority');
+                if (this.filters.priority) params.set('priority', this.filters.priority);
+                if (this.filters.property) params.set('property', this.filters.property);
+                if (this.filters.building) params.set('building', this.filters.building);
+                if (this.filters.unitId) params.set('unit_id', this.filters.unitId);
+                const qs = params.toString();
+                const url = qs ? `/tickets?${qs}` : '/tickets';
+                window.history.replaceState({}, '', url);
+            },
+
+            async loadUnits() {
+                try {
+                    const data = await app().apiGet('/api/tickets/units/grouped');
+                    this.unitGroups = data || {};
+                    if (this.filters.property) this.buildings = Object.keys(this.unitGroups[this.filters.property] || {});
+                    if (this.filters.unitId) {
+                        // Restore displayed apartment for a unit_id deep link
+                        Object.values(this.unitGroups).forEach(prop => Object.values(prop).forEach(units => {
+                            const u = (units || []).find(x => x.id == this.filters.unitId);
+                            if (u) this.filterUnit = u;
+                        }));
+                    }
+                } catch (e) { console.error('Units load error', e); }
+            },
+
+            onPropertyChange() {
+                this.filters.building = '';
+                this.filters.unitId = '';
+                this.filterUnit = null;
+                this.searchApartment = '';
+                this.buildings = this.filters.property ? Object.keys(this.unitGroups[this.filters.property] || {}) : [];
+                this.loadTickets();
+                this.syncUrl();
+            },
+
+            onBuildingChange() {
+                this.filters.unitId = '';
+                this.filterUnit = null;
+                this.searchApartment = '';
+                this.loadTickets();
+                this.syncUrl();
+            },
+
+            get buildingUnits() {
+                if (!this.filters.property || !this.filters.building) return [];
+                return this.unitGroups[this.filters.property]?.[this.filters.building] || [];
+            },
+
+            get filteredApartments() {
+                const q = (this.searchApartment || '').toLowerCase().trim();
+                let units = this.buildingUnits;
+                if (q) {
+                    units = units.filter(u =>
+                        (u.apartment_code || '').toLowerCase().includes(q) ||
+                        String(u.floor || '').includes(q)
+                    );
+                }
+                return units;
+            },
+
+            closeApartment() {
+                setTimeout(() => { this.apartmentOpen = false; }, 150);
+            },
+
+            selectFilterUnit(unit) {
+                this.filterUnit = unit;
+                this.filters.unitId = unit.id;
+                this.searchApartment = unit.apartment_code;
+                this.apartmentOpen = false;
+                this.loadTickets();
+                this.syncUrl();
+            },
+
+            get groupedTickets() {
+                return app().groupByPriority(this.tickets, this.ageDir);
+            },
+
+            get groupedTotal() {
+                return Object.values(this.groupedTickets).reduce((n, g) => n + g.length, 0);
+            },
+
             async loadTickets() {
                 try {
                     let url = `/api/tickets?page=${this.page}&page_size=${this.pageSize}`;
                     if (this.filters.status) url += `&status=${encodeURIComponent(this.filters.status)}`;
                     if (this.filters.priority) url += `&priority=${encodeURIComponent(this.filters.priority)}`;
                     if (this.filters.property) url += `&property=${encodeURIComponent(this.filters.property)}`;
+                    if (this.filters.building) url += `&building=${encodeURIComponent(this.filters.building)}`;
+                    if (this.filters.unitId) url += `&unit_id=${encodeURIComponent(this.filters.unitId)}`;
                     if (this.filters.dateFrom) url += `&date_from=${encodeURIComponent(this.filters.dateFrom)}`;
                     if (this.filters.dateTo) url += `&date_to=${encodeURIComponent(this.filters.dateTo)}`;
 
@@ -204,6 +390,16 @@
                     if (valA > valB) return this.sortDir === 'asc' ? 1 : -1;
                     return 0;
                 });
+            },
+
+            clearFilters() {
+                this.filters = { search: '', status: '', priority: '', property: '', building: '', unitId: '', dateFrom: '', dateTo: '' };
+                this.filterUnit = null;
+                this.searchApartment = '';
+                this.buildings = [];
+                this.page = 1;
+                this.loadTickets();
+                this.syncUrl();
             }
         }
     }

app/templates/tickets/new.html

@@ -6,6 +6,25 @@
         <p class="text-gray-500 mt-1">Report a maintenance or customer service issue</p>
     </div>
 
+    <!-- Report Mode: Standard / Emergency quick path -->
+    <div class="bg-white rounded-xl shadow-sm border border-gray-200 p-6 max-w-3xl mb-6">
+        <div class="grid grid-cols-1 md:grid-cols-2 gap-3">
+            <button type="button" @click="setMode('standard')" class="flex items-center justify-center space-x-2 px-4 py-3 rounded-xl border-2 text-sm font-medium transition"
+                    :class="mode === 'standard' ? 'border-denya-500 bg-denya-50 text-denya-700' : 'border-gray-200 text-gray-600 hover:bg-gray-50'">
+                <span>Standard Issue</span>
+            </button>
+            <button type="button" @click="setMode('emergency')" class="flex items-center justify-center space-x-2 px-4 py-3 rounded-xl border-2 text-sm font-medium transition"
+                    :class="mode === 'emergency' ? 'border-red-500 bg-red-50 text-red-700' : 'border-gray-200 text-gray-600 hover:bg-gray-50'">
+                <span>🚨 Emergency</span>
+            </button>
+        </div>
+        <p x-show="mode === 'emergency'" class="mt-3 text-xs text-red-600 leading-relaxed">
+            Emergency reports (gas leak, fire, flood, electrical hazard) are created with 🔴 Urgent priority and urgent SLA targets apply.
+            The Gas Leak category is available here only — it stays hidden from the standard issue list.
+        </p>
+        <p x-show="mode === 'standard'" class="mt-3 text-xs text-gray-400">Standard maintenance or customer service issues.</p>
+    </div>
+
     <div class="bg-white rounded-xl shadow-sm border border-gray-200 p-6 max-w-3xl">
         <form @submit.prevent="submitTicket" class="space-y-5">
             <!-- Row: Customer Name + Phone -->
@@ -20,36 +39,56 @@
                 </div>
             </div>
 
-            <!-- Row: Property + Apartment -->
+            <!-- Row: Property → Building → Apartment (3-level cascade + searchable combobox) -->
-            <div class="grid grid-cols-1 md:grid-cols-2 gap-4">
+            <div>
-                <div>
+                <label class="block text-sm font-medium text-gray-700 mb-1">Location <span class="text-red-500">*</span></label>
-                    <label class="block text-sm font-medium text-gray-700 mb-1">Property <span class="text-red-500">*</span></label>
+                <div class="grid grid-cols-1 md:grid-cols-3 gap-4">
-                    <select x-model="form.property" @change="loadUnits()" class="w-full px-4 py-2.5 rounded-lg border border-gray-300 focus:ring-2 focus:ring-denya-500 focus:border-transparent outline-none">
+                    <div>
-                        <option value="">Select Property</option>
+                        <select x-model="form.property" @change="onPropertyChange()" class="w-full px-4 py-2.5 rounded-lg border border-gray-300 focus:ring-2 focus:ring-denya-500 focus:border-transparent outline-none">
-                        <option value="East">Pavilion East</option>
+                            <option value="">Property</option>
-                        <option value="West">Pavilion West</option>
+                            <option value="East">Pavilion East</option>
-                    </select>
+                            <option value="West">Pavilion West</option>
-                </div>
+                        </select>
-                <div>
+                    </div>
-                    <label class="block text-sm font-medium text-gray-700 mb-1">Apartment <span class="text-red-500">*</span></label>
+                    <div>
-                    <select x-model="form.apartment_code" class="w-full px-4 py-2.5 rounded-lg border border-gray-300 focus:ring-2 focus:ring-denya-500 focus:border-transparent outline-none" :disabled="!form.property">
+                        <select x-model="form.building" @change="onBuildingChange()" class="w-full px-4 py-2.5 rounded-lg border border-gray-300 focus:ring-2 focus:ring-denya-500 focus:border-transparent outline-none" :disabled="!form.property">
-                        <option value="">Select Apartment</option>
+                            <option value="">Building</option>
-                        <template x-for="unit in units" :key="unit.id">
+                            <template x-for="b in buildings" :key="b">
-                            <option :value="unit.apartment_code" x-text="unit.apartment_code"></option>
+                                <option :value="b" x-text="b"></option>
-                        </template>
+                            </template>
-                    </select>
+                        </select>
+                    </div>
+                    <div class="relative">
+                        <input type="text" x-model="searchApartment" @focus="apartmentOpen = true" @input="apartmentOpen = true" @keydown.escape="apartmentOpen = false"
+                               :disabled="!form.building" @blur="closeApartment()"
+                               :placeholder="form.apartment_code ? form.apartment_code : (form.building ? 'Search apartment (e.g. 011E, 505, 10W)...' : 'Select building first')"
+                               class="w-full px-4 py-2.5 rounded-lg border border-gray-300 focus:ring-2 focus:ring-denya-500 focus:border-transparent outline-none disabled:bg-gray-50 disabled:text-gray-400">
+                        <div x-show="apartmentOpen && filteredApartments.length" class="absolute z-20 mt-1 w-full bg-white border border-gray-200 rounded-lg shadow-lg max-h-48 overflow-y-auto">
+                            <template x-for="unit in filteredApartments" :key="unit.id">
+                                <button type="button" @mousedown.prevent="selectApartment(unit)" class="block w-full text-left px-3 py-2 hover:bg-denya-50 text-sm">
+                                    <span class="font-medium text-gray-800" x-text="unit.apartment_code"></span>
+                                    <span class="text-xs text-gray-400" x-text="` · Floor ${unit.floor || '—'} · ${unit.building || ''}`"></span>
+                                </button>
+                            </template>
+                        </div>
+                        <div x-show="apartmentOpen && !filteredApartments.length && form.building" class="absolute z-20 mt-1 w-full bg-white border border-gray-200 rounded-lg shadow-lg px-3 py-2 text-xs text-gray-400">
+                            No apartments match “<span x-text="searchApartment"></span>”
+                        </div>
+                    </div>
                 </div>
+                <p class="mt-1 text-xs text-gray-400">Property → Building → Apartment. Search matches apartment code, floor, or building.</p>
             </div>
 
             <!-- Category -->
             <div>
                 <label class="block text-sm font-medium text-gray-700 mb-1">Category <span class="text-red-500">*</span></label>
-                <select x-model="form.category_main" @change="loadSubCategories()" class="w-full px-4 py-2.5 rounded-lg border border-gray-300 focus:ring-2 focus:ring-denya-500 focus:border-transparent outline-none">
+                <select x-model="form.category_main" @change="onCategoryChange()" class="w-full px-4 py-2.5 rounded-lg border border-gray-300 focus:ring-2 focus:ring-denya-500 focus:border-transparent outline-none">
                     <option value="">Select Category</option>
                     <template x-for="cat in categories" :key="cat.id">
                         <option :value="cat.id" x-text="cat.name"></option>
                     </template>
                 </select>
+                <div x-show="selectedCategoryHint" class="mt-2 text-xs text-gray-600 bg-amber-50 border border-amber-200 rounded-lg px-3 py-2" x-text="selectedCategoryHint"></div>
             </div>
             <div x-show="subCategories.length">
                 <label class="block text-sm font-medium text-gray-700 mb-1">Sub-Category</label>
@@ -62,23 +101,29 @@
             </div>
 
             <!-- Priority -->
-            <div>
+            <div x-show="mode === 'emergency'">
+                <label class="block text-sm font-medium text-gray-700 mb-1">Priority</label>
+                <div class="p-3 bg-red-50 border border-red-200 rounded-lg text-sm text-red-700 font-medium">
+                    🔴 Urgent — fixed for emergency reports (15 min response / 4 h resolution SLA)
+                </div>
+            </div>
+            <div x-show="mode === 'standard'">
                 <label class="block text-sm font-medium text-gray-700 mb-1">Priority</label>
                 <div class="grid grid-cols-2 md:grid-cols-4 gap-2">
                     <label class="flex items-center p-3 border rounded-lg cursor-pointer hover:bg-gray-50" :class="form.priority === 'urgent' ? 'border-red-500 bg-red-50' : 'border-gray-200'">
-                        <input type="radio" name="priority" value="urgent" x-model="form.priority" class="sr-only">
+                        <input type="radio" name="priority" value="urgent" x-model="form.priority" @change="priorityAuto = false" class="sr-only">
                         <span class="text-sm">🔴 Urgent</span>
                     </label>
                     <label class="flex items-center p-3 border rounded-lg cursor-pointer hover:bg-gray-50" :class="form.priority === 'high' ? 'border-orange-500 bg-orange-50' : 'border-gray-200'">
-                        <input type="radio" name="priority" value="high" x-model="form.priority" class="sr-only">
+                        <input type="radio" name="priority" value="high" x-model="form.priority" @change="priorityAuto = false" class="sr-only">
                         <span class="text-sm">🟠 High</span>
                     </label>
                     <label class="flex items-center p-3 border rounded-lg cursor-pointer hover:bg-gray-50" :class="form.priority === 'medium' ? 'border-yellow-500 bg-yellow-50' : 'border-gray-200'">
-                        <input type="radio" name="priority" value="medium" x-model="form.priority" class="sr-only">
+                        <input type="radio" name="priority" value="medium" x-model="form.priority" @change="priorityAuto = false" class="sr-only">
                         <span class="text-sm">🟡 Medium</span>
                     </label>
                     <label class="flex items-center p-3 border rounded-lg cursor-pointer hover:bg-gray-50" :class="form.priority === 'low' ? 'border-green-500 bg-green-50' : 'border-gray-200'">
-                        <input type="radio" name="priority" value="low" x-model="form.priority" class="sr-only">
+                        <input type="radio" name="priority" value="low" x-model="form.priority" @change="priorityAuto = false" class="sr-only">
                         <span class="text-sm">🟢 Low</span>
                     </label>
                 </div>
@@ -136,7 +181,7 @@
             <div class="flex items-center space-x-3 pt-2">
                 <button type="submit" :disabled="submitting" class="px-6 py-2.5 bg-denya-600 text-white rounded-lg hover:bg-denya-700 transition font-medium disabled:opacity-50 disabled:cursor-not-allowed flex items-center space-x-2">
                     <svg x-show="submitting" class="animate-spin h-4 w-4" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24"><circle class="opacity-25" cx="12" cy="12" r="10" stroke="currentColor" stroke-width="4"/><path class="opacity-75" fill="currentColor" d="M4 12a8 8 0 018-8V0C5.373 0 0 5.373 0 12h4z"/></svg>
-                    <span x-text="submitting ? 'Creating...' : 'Create Issue'"></span>
+                    <span x-text="submitting ? 'Creating...' : (mode === 'emergency' ? 'Create Emergency Issue' : 'Create Issue')"></span>
                 </button>
                 <a href="/tickets" class="px-6 py-2.5 border border-gray-300 rounded-lg text-gray-700 hover:bg-gray-50 transition font-medium">Cancel</a>
             </div>
@@ -147,11 +192,14 @@
 <script>
     function createTicket() {
         return {
+            mode: 'standard',
             form: {
                 customer_name: '',
                 phone: '',
                 property: '',
+                building: '',
                 apartment_code: '',
+                unit: null,
                 category_main: '',
                 category_id: null,
                 priority: '',
@@ -159,9 +207,14 @@
                 reporter: '',
                 reported_via: ''
             },
+            priorityAuto: false,
             categories: [],
             subCategories: [],
-            units: [],
+            unitGroups: {},
+            buildings: [],
+            searchApartment: '',
+            apartmentOpen: false,
+            selectedCategoryHint: '',
             photoFiles: [],
             photoPreviews: [],
             submitting: false,
@@ -169,38 +222,114 @@
 
             async init() {
                 await this.loadCategories();
+                await this.loadUnits();
+            },
+
+            // ── Report mode ──────────────────────────────────────────
+            setMode(mode) {
+                this.mode = mode;
+                // Reset category selection; priority follows mode
+                this.form.category_main = '';
+                this.form.category_id = null;
+                this.subCategories = [];
+                this.selectedCategoryHint = '';
+                if (mode === 'emergency') {
+                    this.form.priority = 'urgent';
+                } else {
+                    this.form.priority = '';
+                }
+                this.priorityAuto = false;
+                this.loadCategories();
             },
 
             async loadCategories() {
                 try {
-                    const data = await app().apiGet('/api/tickets/categories');
+                    const url = this.mode === 'emergency'
+                        ? '/api/tickets/categories?type=emergency&include_hidden=true'
+                        : '/api/tickets/categories';
+                    const data = await app().apiGet(url);
                     // Top-level categories only
                     this.categories = data?.filter(c => !c.parent_id) || [];
                 } catch (e) { console.error('Categories load error', e); }
             },
 
-            async loadSubCategories() {
+            async loadUnits() {
-                this.form.category_id = null;
-                this.subCategories = [];
-                if (!this.form.category_main) return;
                 try {
-                    const data = await app().apiGet('/api/tickets/categories');
+                    const data = await app().apiGet('/api/tickets/units/grouped');
-                    const parent = data?.find(c => c.id == this.form.category_main);
+                    this.unitGroups = data || {};
-                    if (parent?.children) {
+                } catch (e) { console.error('Units load error', e); }
-                        this.subCategories = parent.children;
-                    }
-                } catch (e) { console.error('Subcategories load error', e); }
             },
 
-            async loadUnits() {
+            // ── Location cascade ─────────────────────────────────────
+            onPropertyChange() {
+                this.form.building = '';
                 this.form.apartment_code = '';
-                this.units = [];
+                this.form.unit = null;
-                if (!this.form.property) return;
+                this.searchApartment = '';
-                try {
+                const prop = this.form.property;
-                    const data = await app().apiGet(`/api/tickets/units?property=${encodeURIComponent(this.form.property)}`);
+                this.buildings = prop ? Object.keys(this.unitGroups[prop] || {}) : [];
-                    this.units = data || [];
+            },
-                } catch (e) {
+
-                    console.error('Units load error', e);
+            onBuildingChange() {
+                this.form.apartment_code = '';
+                this.form.unit = null;
+                this.searchApartment = '';
+            },
+
+            get buildingUnits() {
+                if (!this.form.property || !this.form.building) return [];
+                return this.unitGroups[this.form.property]?.[this.form.building] || [];
+            },
+
+            get filteredApartments() {
+                const q = (this.searchApartment || '').toLowerCase().trim();
+                let units = this.buildingUnits;
+                if (q) {
+                    units = units.filter(u =>
+                        (u.apartment_code || '').toLowerCase().includes(q) ||
+                        (u.building || '').toLowerCase().includes(q) ||
+                        String(u.floor || '').includes(q)
+                    );
+                }
+                return units;
+            },
+
+            closeApartment() {
+                setTimeout(() => { this.apartmentOpen = false; }, 150);
+            },
+
+            selectApartment(unit) {
+                this.form.unit = unit;
+                this.form.apartment_code = unit.apartment_code;
+                this.searchApartment = unit.apartment_code;
+                this.apartmentOpen = false;
+            },
+
+            // ── Category helpers ─────────────────────────────────────
+            onCategoryChange() {
+                this.form.category_id = null;
+                this.subCategories = [];
+                this.selectedCategoryHint = '';
+                if (!this.form.category_main) return;
+                const parent = this.categories.find(c => c.id == this.form.category_main);
+                if (parent?.children) {
+                    this.subCategories = parent.children;
+                }
+                const hints = {
+                    'Carpentry': 'Triage tip: use Carpentry for structural timber, door and frame work; Furniture for movable pieces (beds, chairs, tables, wardrobes); Security for lock, latch and door-closing functionality.',
+                    'Mould & Damp': 'Damp / mould remediation. Priority defaults to 🟡 Medium unless you change it.',
+                    'Aluminum/Glass': 'Windows, doors, sliding/fixed panels, glass and mirror replacement.'
+                };
+                if (parent?.name && hints[parent.name]) this.selectedCategoryHint = hints[parent.name];
+                // Mould & Damp defaults to Medium priority; clear the auto-default
+                // when switching away so a non-Mould ticket doesn't keep it silently
+                if (parent?.name !== 'Mould & Damp' && this.priorityAuto) {
+                    this.form.priority = '';
+                    this.priorityAuto = false;
+                }
+                if (parent?.name === 'Mould & Damp' && !this.form.priority) {
+                    this.form.priority = 'medium';
+                    this.priorityAuto = true;
                 }
             },
 
@@ -226,32 +355,29 @@
             async submitTicket() {
                 this.error = '';
                 this.submitting = true;
-                
+
                 try {
                     // Validate required fields
-                    if (!this.form.property || !this.form.apartment_code || !this.form.description) {
+                    if (!this.form.property || !this.form.building || !this.form.unit || !this.form.description) {
-                        this.error = 'Please fill in Property, Apartment, and Description.';
+                        this.error = 'Please fill in Property, Building, Apartment, and Description.';
                         this.submitting = false;
                         return;
                     }
 
-                    // Resolve unit_id from selected apartment_code
-                    const selectedUnit = this.units.find(u => u.apartment_code === this.form.apartment_code);
-
                     // Create the ticket
                     const payload = {
                         description: this.form.description,
-                        priority: this.form.priority || null,
+                        priority: this.mode === 'emergency' ? 'urgent' : (this.form.priority || null),
                         reporter: this.form.reporter || this.form.customer_name || app().user.full_name,
                         reported_via: this.form.reported_via || 'walk-in',
                         category_id: this.form.category_id ? parseInt(this.form.category_id) : (this.form.category_main ? parseInt(this.form.category_main) : null),
-                        unit_id: selectedUnit ? selectedUnit.id : null,
+                        unit_id: this.form.unit.id,
                         customer_name: this.form.customer_name || null,
                         phone: this.form.phone || null,
                     };
 
                     const ticket = await app().apiPost('/api/tickets', payload);
-                    
+
                     // Upload photos if any
                     if (this.photoFiles.length > 0 && ticket?.id) {
                         const formData = new FormData();

pyproject.toml

@@ -22,8 +22,14 @@ build-backend = "setuptools.build_meta"
 [tool.setuptools.packages.find]
 include = ["app*"]
 
+[tool.pytest.ini_options]
+asyncio_mode = "auto"
+testpaths = ["tests"]
+pythonpath = ["."]
+
 [project.optional-dependencies]
 dev = [
     "pytest>=8.0",
+    "pytest-asyncio>=0.24",
     "httpx>=0.27.0",
 ]

tests/conftest.py

@@ -0,0 +1,66 @@
+"""Shared fixtures for the Denya OneCare test suite.
+
+Sets DATABASE_URL to an isolated temp SQLite file BEFORE importing any app
+module (the engine is created at import time), then provisions tables and
+seed data per test.
+"""
+
+from __future__ import annotations
+
+import os
+import tempfile
+
+_TMP_DIR = tempfile.mkdtemp(prefix="denya-test-")
+os.environ["DATABASE_URL"] = f"sqlite+aiosqlite:///{_TMP_DIR}/test.db"
+
+import pytest_asyncio  # noqa: E402  (DATABASE_URL must be set before app imports)
+from httpx import ASGITransport, AsyncClient  # noqa: E402
+
+from app.core.database import Base, async_session_factory, engine  # noqa: E402
+from app.main import app  # noqa: E402
+from app.models.ticket import Ticket  # noqa: E402
+from app.services.seed import seed_categories, seed_units, seed_users  # noqa: E402
+
+
+@pytest_asyncio.fixture
+async def client():
+    """Async test client with a fresh, seeded database per test."""
+    async with engine.begin() as conn:
+        await conn.run_sync(Base.metadata.create_all)
+    async with async_session_factory() as session:
+        await seed_users(session)
+        await session.commit()
+        # json_path=None → built-in fallback units (same data as the committed
+        # apartment_mapping.json, kept deterministic for tests)
+        await seed_units(session, json_path=None)
+        await session.commit()
+        await seed_categories(session)
+        await session.commit()
+
+    transport = ASGITransport(app=app)
+    async with AsyncClient(transport=transport, base_url="http://test") as c:
+        yield c
+
+    async with engine.begin() as conn:
+        await conn.run_sync(Base.metadata.drop_all)
+
+
+@pytest_asyncio.fixture
+async def seed_tickets():
+    """Insert `n` tickets directly into the DB; returns the count inserted."""
+
+    async def _seed(n: int) -> int:
+        async with async_session_factory() as session:
+            for i in range(n):
+                session.add(
+                    Ticket(
+                        ticket_number=f"PAV-TEST-{i:05d}",
+                        status="Logged",
+                        priority="medium",
+                        description=f"Test ticket {i}",
+                    )
+                )
+            await session.commit()
+        return n
+
+    return _seed

tests/test_categories_and_units.py

@@ -0,0 +1,248 @@
+"""Tests for the Sprint A category + property-hierarchy changes.
+
+Anchors:
+* Alert-only categories (Gas Leak) are hidden from the picker endpoints by
+  default but still present in the DB with urgent SLA urgency, and retrievable
+  via ``include_hidden=true`` (emergency quick path).
+* New seed categories (Aluminum/Glass, Carpentry, Mould & Damp) and the
+  Lost Property → Missing Item rename are idempotent.
+* ``GET /api/tickets/units`` gains a ``building`` filter and the grouped
+  variant ``GET /api/tickets/units/grouped`` returns ``{property: {building: [units]}}``.
+* ``GET /api/tickets`` supports additive ``building``/``unit_id`` filters.
+"""
+
+from __future__ import annotations
+
+import pytest
+
+pytestmark = pytest.mark.asyncio
+
+
+def _find(categories: list[dict], name: str) -> dict | None:
+    return next((c for c in categories if c["name"] == name), None)
+
+
+# ── Category picker visibility ────────────────────────────────────────
+async def test_gas_leak_hidden_from_category_tree_by_default(client):
+    """Gas Leak must not appear in the default category picker."""
+    resp = await client.get("/api/tickets/categories")
+    assert resp.status_code == 200
+    data = resp.json()
+    # Any nesting level: walk top-level only; Gas Leak is top-level emergency
+    assert _find(data, "Gas Leak") is None
+    names = [c["name"] for c in data]
+    assert "Fire" in names and "Flood" in names  # other emergencies still visible
+
+
+async def test_gas_leak_hidden_from_emergency_type_filter(client):
+    """The `?type=emergency` picker also excludes Gas Leak by default."""
+    resp = await client.get("/api/tickets/categories", params={"type": "emergency"})
+    assert resp.status_code == 200
+    data = resp.json()
+    assert _find(data, "Gas Leak") is None
+    assert _find(data, "Fire") is not None
+
+
+async def test_include_hidden_returns_gas_leak(client):
+    """include_hidden=true exposes alert-only categories (emergency quick path)."""
+    resp = await client.get("/api/tickets/categories", params={"include_hidden": "true"})
+    assert resp.status_code == 200
+    gas = _find(resp.json(), "Gas Leak")
+    assert gas is not None
+    assert gas["show_in_form"] is False
+    assert gas["sla_urgency"] == "urgent"
+
+
+async def test_flat_list_hides_gas_leak_and_its_children(client):
+    """Flat picker excludes Gas Leak and orphaned children of hidden parents."""
+    resp = await client.get("/api/tickets/categories/flat")
+    assert resp.status_code == 200
+    names = [c["name"] for c in resp.json()]
+    assert "Gas Leak" not in names
+    assert "Gas smell" not in names  # child of alert-only parent
+    assert "Suspected leak" not in names
+    assert "Fire" in names
+
+
+async def test_flat_list_include_hidden_keeps_gas_leak(client):
+    resp = await client.get("/api/tickets/categories/flat", params={"include_hidden": "true"})
+    assert resp.status_code == 200
+    names = [c["name"] for c in resp.json()]
+    assert "Gas Leak" in names
+    assert "Gas smell" in names
+
+
+# ── Seed taxonomy ─────────────────────────────────────────────────────
+async def test_seed_adds_new_categories(client):
+    """Aluminum/Glass, Carpentry, Mould & Damp land as maintenance categories."""
+    resp = await client.get("/api/tickets/categories", params={"type": "maintenance"})
+    assert resp.status_code == 200
+    data = resp.json()
+    for name in ("Aluminum/Glass", "Carpentry", "Mould & Damp"):
+        cat = _find(data, name)
+        assert cat is not None, f"expected {name} in maintenance categories"
+        assert cat["children"], f"{name} should have sub-categories"
+    mould = _find(data, "Mould & Damp")
+    assert mould["sla_urgency"] == "medium"
+
+
+async def test_seed_renames_lost_property_to_missing_item(client):
+    """Seed must create 'Missing Item' (not 'Lost Property')."""
+    resp = await client.get("/api/tickets/categories", params={"type": "cs"})
+    assert resp.status_code == 200
+    data = resp.json()
+    assert _find(data, "Missing Item") is not None
+    assert _find(data, "Lost Property") is None
+    missing = _find(data, "Missing Item")
+    sub_names = [s["name"] for s in missing["children"]]
+    assert "Guest left items behind" in sub_names
+    assert "Item search request" in sub_names
+
+
+async def test_seed_is_idempotent(client):
+    """Running the seed twice does not duplicate categories."""
+    from app.core.database import async_session_factory
+    from app.services.seed import seed_categories
+
+    async with async_session_factory() as session:
+        created = await seed_categories(session)
+        await session.commit()
+    assert created == []  # nothing new to insert/sync on second pass
+
+
+async def test_seed_syncs_show_in_form_on_existing_rows(client):
+    """Existing databases pick up Gas Leak's alert-only flag on next startup.
+
+    Simulates a pre-existing DB (Gas Leak seeded with show_in_form=True before
+    the flag existed) — re-running the seed must flip it back to False.
+    """
+    from sqlalchemy import select
+    from app.core.database import async_session_factory
+    from app.models.category import Category
+    from app.services.seed import seed_categories
+
+    async with async_session_factory() as session:
+        result = await session.execute(
+            select(Category).where(Category.type == "emergency", Category.name == "Gas Leak", Category.parent_id.is_(None))
+        )
+        gas = result.scalar_one()
+        gas.show_in_form = True  # simulate legacy DB before the flag existed
+        await session.commit()
+
+        created = await seed_categories(session)
+        await session.commit()
+        await session.refresh(gas)
+        assert gas.show_in_form is False
+        assert gas.sla_urgency == "urgent"  # SLA/alert mapping unchanged
+        # Sync counts as a change, but no duplicates are created
+        assert len(created) >= 1
+
+
+async def test_legacy_db_self_heals_show_in_form_column():
+    """A pre-Sprint-A categories table (no show_in_form) gets the column on startup."""
+    from sqlalchemy import text
+
+    from app.core.database import engine
+    from app.main import ensure_legacy_schema
+
+    async with engine.begin() as conn:
+        await conn.execute(text("DROP TABLE IF EXISTS tickets"))
+        await conn.execute(text("DROP TABLE IF EXISTS categories"))
+        await conn.execute(
+            text(
+                "CREATE TABLE categories ("
+                "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
+                "type VARCHAR(20) NOT NULL, "
+                "name VARCHAR(100) NOT NULL, "
+                "parent_id INTEGER, "
+                "sla_urgency VARCHAR(10))"
+            )
+        )
+        await ensure_legacy_schema(conn)
+        result = await conn.execute(text("PRAGMA table_info(categories)"))
+        assert "show_in_form" in {row[1] for row in result}
+
+    # Idempotent on the next startup
+    async with engine.begin() as conn:
+        await ensure_legacy_schema(conn)
+        result = await conn.execute(text("PRAGMA table_info(categories)"))
+        assert "show_in_form" in {row[1] for row in result}
+
+    async with engine.begin() as conn:
+        await conn.execute(text("DROP TABLE IF EXISTS categories"))
+        await conn.execute(text("DROP TABLE IF EXISTS tickets"))
+
+
+# ── Unit hierarchy ────────────────────────────────────────────────────
+async def test_units_building_filter(client):
+    """GET /api/tickets/units?building= filters to one building."""
+    resp = await client.get("/api/tickets/units", params={"building": "Pavilion East"})
+    assert resp.status_code == 200
+    units = resp.json()
+    assert len(units) > 0
+    assert all(u["building"] == "Pavilion East" for u in units)
+    assert all(u["property"] == "East" for u in units)
+
+
+async def test_units_grouped_shape(client):
+    """Grouped variant returns {property: {building: [units]}}."""
+    resp = await client.get("/api/tickets/units/grouped")
+    assert resp.status_code == 200
+    grouped = resp.json()
+    assert "East" in grouped and "West" in grouped
+    east = grouped["East"]
+    assert "Pavilion East" in east
+    assert len(east["Pavilion East"]) == 60
+    unit = east["Pavilion East"][0]
+    assert {"id", "property", "apartment_code", "building", "floor"} <= set(unit.keys())
+    # Distinct apartment codes
+    codes = [u["apartment_code"] for u in east["Pavilion East"]]
+    assert len(set(codes)) == len(codes)
+
+
+async def test_units_grouped_property_filter(client):
+    resp = await client.get("/api/tickets/units/grouped", params={"property": "West"})
+    assert resp.status_code == 200
+    grouped = resp.json()
+    assert set(grouped.keys()) == {"West"}
+
+
+# ── Ticket list filters ───────────────────────────────────────────────
+async def test_tickets_filter_by_building_and_unit(client):
+    """Additive building/unit_id filters compose with the list endpoint."""
+    # Grab two units from different buildings/properties
+    resp = await client.get("/api/tickets/units", params={"building": "Pavilion East"})
+    east_unit = resp.json()[0]
+    resp = await client.get("/api/tickets/units", params={"building": "Pavilion West"})
+    west_unit = resp.json()[0]
+
+    # Two tickets, one per building
+    payloads = [
+        {"description": "east ticket", "unit_id": east_unit["id"], "priority": "medium"},
+        {"description": "west ticket", "unit_id": west_unit["id"], "priority": "high"},
+    ]
+    token = await _login(client)
+    for p in payloads:
+        r = await client.post("/api/tickets", json=p, headers={"Authorization": f"Bearer {token}"})
+        assert r.status_code == 201, r.text
+
+    # building filter
+    r = await client.get("/api/tickets", params={"building": "Pavilion East"})
+    data = r.json()
+    assert data["total"] == 1
+    assert data["items"][0]["description"] == "east ticket"
+
+    # unit_id filter
+    r = await client.get("/api/tickets", params={"unit_id": west_unit["id"]})
+    data = r.json()
+    assert data["total"] == 1
+    assert data["items"][0]["description"] == "west ticket"
+
+
+async def _login(client) -> str:
+    resp = await client.post(
+        "/api/auth/login",
+        json={"email": "wahab@denya.com", "password": "denya123"},
+    )
+    assert resp.status_code == 200, resp.text
+    return resp.json()["access_token"]

tests/test_tickets_pagination.py

@@ -0,0 +1,56 @@
+"""Tests anchoring ticket list pagination behavior.
+
+The CEO dashboard (app/templates/dashboard/ceo.html) previously requested
+`page_size=500`; the API caps page_size at 200 (`le=200` in
+app/routers/tickets.py), so that request returned 422 and the dashboard
+rendered empty KPIs. These tests pin the API contract the frontend now
+relies on: page_size=200 + page loops that collect every ticket.
+"""
+
+from __future__ import annotations
+
+import pytest
+
+pytestmark = pytest.mark.asyncio
+
+
+async def test_page_size_over_cap_returns_422(client):
+    """Requests above the page_size cap must be rejected (the original bug)."""
+    resp = await client.get("/api/tickets", params={"page_size": 500})
+    assert resp.status_code == 422
+
+
+async def test_page_size_at_cap_returns_items_and_total(client, seed_tickets):
+    """page_size=200 is the max legal value and returns the full response shape."""
+    await seed_tickets(14)
+    resp = await client.get("/api/tickets", params={"page": 1, "page_size": 200})
+    assert resp.status_code == 200
+    data = resp.json()
+    assert data["total"] == 14
+    assert len(data["items"]) == 14
+    assert data["page"] == 1
+    assert data["page_size"] == 200
+
+
+async def test_paginated_loop_collects_all_tickets(client, seed_tickets):
+    """The frontend's page loop (page_size=200 until total reached) collects every ticket."""
+    total_seeded = await seed_tickets(450)  # 3 pages of 200
+
+    collected: list[dict] = []
+    total = float("inf")
+    page = 1
+    page_size = 200
+    while len(collected) < total and page <= 1000:
+        resp = await client.get("/api/tickets", params={"page": page, "page_size": page_size})
+        assert resp.status_code == 200
+        data = resp.json()
+        assert data["items"], "expected a non-empty page"
+        collected.extend(data["items"])
+        total = data["total"] or len(collected)
+        page += 1
+
+    assert total == total_seeded
+    assert len(collected) == total_seeded
+    # No duplicate tickets across pages
+    ids = [t["id"] for t in collected]
+    assert len(set(ids)) == len(ids)