no-mistakes(review): Add pytest pythonpath config so tests resolve app imports

The CEO dashboard requested /api/tickets?page_size=500, but the API caps
page_size at 200 (le=200 in app/routers/tickets.py), so the request
returned 422 and every KPI/chart rendered zeros.

- ceo.html: fetch all tickets by looping pages of page_size=200 until
  total items are collected (with a safety bound), keeping KPIs accurate
  as volume grows past 200.
- tests: add test suite anchoring the pagination contract — page_size=500
  returns 422, page_size=200 returns items/total/page/page_size, and a
  page loop collects every ticket without duplicates.
- pyproject: enable pytest-asyncio auto mode and tests/ discovery.
- .gitignore: un-ignore committed tests/test_*.py.

.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
 

app/core/config.py

@@ -31,6 +31,12 @@ class Settings(BaseSettings):
     # ── CORS ─────────────────────────────────────────────────────────
     CORS_ORIGINS: str = "*"
 
+    # ── WhatsApp ─────────────────────────────────────────────────────
+    WHATSAPP_PHONE_NUMBER_ID: str = ""
+    WHATSAPP_ACCESS_TOKEN: str = ""
+    WHATSAPP_VERIFY_TOKEN: str = ""
+    META_GRAPH_BASE: str = "https://graph.facebook.com/v18.0"
+
     # ── Paths ────────────────────────────────────────────────────────
     BASE_DIR: Path = Path(__file__).resolve().parent.parent.parent
 

app/models/whatsapp_log.py

@@ -1,4 +1,4 @@
-"""WhatsApp log model for mock endpoint."""
+"""WhatsApp log model for inbound webhook messages."""
 
 from __future__ import annotations
 
@@ -14,10 +14,12 @@ class WhatsAppLog(Base):
     __tablename__ = "whatsapp_log"
 
     id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
-    command: Mapped[str] = mapped_column(Text, nullable=False)
+    from_number: Mapped[str] = mapped_column(String(50), nullable=False)
-    from_number: Mapped[str | None] = mapped_column(String(50), nullable=True)
+    message_text: Mapped[str] = mapped_column(Text, nullable=False)
+    wa_message_id: Mapped[str | None] = mapped_column(String(100), nullable=True)
     ticket_id: Mapped[int | None] = mapped_column(Integer, nullable=True)
+    ticket_number: Mapped[str | None] = mapped_column(String(30), nullable=True)
     received_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
 
     def __repr__(self) -> str:
-        return f"<WhatsAppLog {self.id}: {self.command[:50]}>"
+        return f"<WhatsAppLog {self.id}: from={self.from_number} ticket={self.ticket_number}>"

app/routers/whatsapp.py

@@ -1,44 +1,165 @@
-"""Mock WhatsApp endpoint for testing command parsing."""
+"""WhatsApp webhook handler — Meta Graph API integration."""
 
 from __future__ import annotations
 
+import logging
 from datetime import datetime, timezone
 from typing import Annotated
 
-from fastapi import APIRouter, Depends
+import httpx
+from fastapi import APIRouter, Depends, Query
 from sqlalchemy import select
 from sqlalchemy.ext.asyncio import AsyncSession
 
+from app.core.config import settings
 from app.core.database import get_db
 from app.models.whatsapp_log import WhatsAppLog
-from app.schemas.whatsapp import MockWhatsAppLogEntry, MockWhatsAppRequest, MockWhatsAppResponse
+from app.schemas.whatsapp import (
+    MetaWebhookRequest,
+    MockWhatsAppLogEntry,
+    WebhookVerificationResponse,
+    WhatsAppReplyResponse,
+)
+from app.services.ticket import create_ticket
+
+logger = logging.getLogger(__name__)
 
 router = APIRouter(prefix="/api/whatsapp", tags=["whatsapp"])
 
+REPLY_TEMPLATE = (
+    "Thank you for contacting Denya OneCare. "
+    "Your ticket number is {ticket_number}. "
+    "We will get back to you soon."
+)
 
-@router.post("/mock", response_model=MockWhatsAppResponse)
+
-async def mock_whatsapp(
+# ── Meta Graph API helpers ──────────────────────────────────────────
-    body: MockWhatsAppRequest,
+async def send_whatsapp_reply(
+    to_phone: str,
+    text: str,
+) -> WhatsAppReplyResponse:
+    """Send a text message via Meta Graph API."""
+    url = f"{settings.META_GRAPH_BASE}/{settings.WHATSAPP_PHONE_NUMBER_ID}/messages"
+    headers = {
+        "Authorization": f"Bearer {settings.WHATSAPP_ACCESS_TOKEN}",
+        "Content-Type": "application/x-www-form-urlencoded",
+    }
+    data = {
+        "messaging_product": "whatsapp",
+        "to": to_phone,
+        "type": "text",
+        "text": {"body": text},
+    }
+    # Encode nested dict as JSON string for form data (Meta requirement)
+    data["text"] = '{"body":' + f'"{text}"' + "}"
+    try:
+        async with httpx.AsyncClient() as client:
+            response = await client.post(url, headers=headers, data=data, timeout=15.0)
+            response.raise_for_status()
+        return WhatsAppReplyResponse(success=True, message="Reply sent")
+    except httpx.HTTPError as exc:
+        logger.error("Failed to send WhatsApp reply: %s", exc)
+        return WhatsAppReplyResponse(success=False, message=str(exc))
+
+
+# ── Webhook endpoint ────────────────────────────────────────────────
+@router.post("/webhook")
+async def whatsapp_webhook(
+    body: MetaWebhookRequest,
     db: Annotated[AsyncSession, Depends(get_db)],
-) -> MockWhatsAppResponse:
+    hub_verify_token: str | None = Query(None, alias="hub.verify_token"),
-    """Accept a mock WhatsApp command and log it."""
+    mode: str | None = Query(None),
+    hub_challenge: str | None = Query(None),
+) -> dict:
+    """Handle incoming WhatsApp webhook from Meta."""
+
+    # ── Verification GET request (Meta sends this on webhook setup) ──
+    if mode and hub_challenge:
+        if hub_verify_token != settings.WHATSAPP_VERIFY_TOKEN:
+            return {"error": "Verify token mismatch"}
+        return WebhookVerificationResponse(challenge=hub_challenge).model_dump()
+
+    # ── Process inbound messages ────────────────────────────────────
+    if not body.entry:
+        return {"status": "no entry"}
+
+    for entry in body.entry:
+        if not entry.changes:
+            continue
+
+        for change in entry.changes:
+            if not change.message or not change.message.text:
+                logger.info("Non-text message received, skipping")
+                continue
+
+            message_text = change.message.text.text
+            sender = change.message.from_field
+            wa_msg_id = change.message.id or change.id
+
+            # ── Create ticket ──────────────────────────────────────
+            try:
+                ticket = await create_ticket(
+                    db,
+                    data={
+                        "description": message_text,
+                        "reporter": sender,
+                        "reported_via": "WhatsApp",
+                    },
+                )
+            except Exception as exc:
+                logger.error("Failed to create ticket: %s", exc)
+                # Still log the message even if ticket creation fails
                 log = WhatsAppLog(
-        command=body.command,
+                    from_number=sender,
-        from_number=body.from_number,
+                    message_text=message_text,
-        ticket_id=body.ticket_id,
+                    wa_message_id=wa_msg_id,
+                    ticket_id=None,
+                    ticket_number=None,
                     received_at=datetime.now(timezone.utc),
                 )
                 db.add(log)
                 await db.flush()
-    return MockWhatsAppResponse()
+                return {"status": "ticket creation failed, message logged"}
+
+            # ── Store WhatsApp log ─────────────────────────────────
+            log = WhatsAppLog(
+                from_number=sender,
+                message_text=message_text,
+                wa_message_id=wa_msg_id,
+                ticket_id=ticket.id,
+                ticket_number=ticket.ticket_number,
+                received_at=datetime.now(timezone.utc),
+            )
+            db.add(log)
+            await db.flush()
+
+            # ── Send auto-reply ────────────────────────────────────
+            reply_text = REPLY_TEMPLATE.format(ticket_number=ticket.ticket_number)
+            reply_result = await send_whatsapp_reply(sender, reply_text)
+            if not reply_result.success:
+                logger.warning(
+                    "Auto-reply failed for ticket %s: %s",
+                    ticket.ticket_number,
+                    reply_result.message,
+                )
+
+            return {
+                "status": "processed",
+                "ticket_id": ticket.id,
+                "ticket_number": ticket.ticket_number,
+                "reply_sent": reply_result.success,
+            }
+
+    return {"status": "no messages"}
 
 
+# ── Legacy debug endpoint ──────────────────────────────────────────
 @router.get("/mock-log", response_model=list[MockWhatsAppLogEntry])
 async def mock_whatsapp_log(
     db: Annotated[AsyncSession, Depends(get_db)],
     limit: int = 50,
 ) -> list[MockWhatsAppLogEntry]:
-    """Return recent mock WhatsApp submissions."""
+    """Return recent WhatsApp webhook submissions for debugging."""
     result = await db.execute(
         select(WhatsAppLog).order_by(WhatsAppLog.received_at.desc()).limit(limit)
     )

app/schemas/whatsapp.py

@@ -1,10 +1,69 @@
-"""Pydantic schemas for mock WhatsApp endpoint."""
+"""Pydantic schemas for Meta WhatsApp webhook."""
 
 from __future__ import annotations
 
 from datetime import datetime
 
-from pydantic import BaseModel
+from pydantic import BaseModel, Field
+
+
+# ── Meta Webhook Verification ────────────────────────────────────────
+class WebhookVerificationResponse(BaseModel):
+    challenge: str
+
+
+# ── Inbound Message Parsing ─────────────────────────────────────────
+class MessageText(BaseModel):
+    text: str
+
+
+class Message(BaseModel):
+    from_field: str = Field(alias="from")
+    id: str | None = None
+    text: MessageText | None = None
+    type: str | None = None
+
+
+class Contact(BaseModel):
+    wa_id: str | None = None
+    profile: dict | None = None
+
+
+class EntryMessagesItem(BaseModel):
+    id: str | None = None
+    message: Message | None = None
+    contacts: list[Contact] | None = None
+    timestamp: str | None = None
+
+
+class Entry(BaseModel):
+    id: str | None = None
+    changes: list[EntryMessagesItem] | None = None
+    metadata: dict | None = None
+
+
+class MetaWebhookRequest(BaseModel):
+    object: str | None = None
+    entry: list[Entry] | None = None
+
+
+# ── Auto-reply ──────────────────────────────────────────────────────
+class WhatsAppReplyResponse(BaseModel):
+    success: bool
+    message: str
+
+
+# ── Legacy mock schemas (kept for /mock-log endpoint) ───────────────
+class MockWhatsAppLogEntry(BaseModel):
+    id: int
+    from_number: str
+    message_text: str
+    wa_message_id: str | None
+    ticket_id: int | None
+    ticket_number: str | None
+    received_at: datetime
+
+    model_config = {"from_attributes": True}
 
 
 class MockWhatsAppRequest(BaseModel):
@@ -16,13 +75,3 @@ class MockWhatsAppRequest(BaseModel):
 class MockWhatsAppResponse(BaseModel):
     status: str = "received"
     message: str = "Command logged successfully"
-
-
-class MockWhatsAppLogEntry(BaseModel):
-    id: int
-    command: str
-    from_number: str | None
-    ticket_id: int | None
-    received_at: datetime
-
-    model_config = {"from_attributes": True}

app/services/ticket.py

@@ -196,7 +196,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/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));

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
+from pathlib import Path
+
+_TMP_DIR = tempfile.mkdtemp(prefix="denya-test-")
+os.environ["DATABASE_URL"] = f"sqlite+aiosqlite:///{_TMP_DIR}/test.db"
+
+import pytest_asyncio
+from httpx import ASGITransport, AsyncClient
+
+from app.core.database import Base, async_session_factory, engine
+from app.main import app
+from app.models.ticket import Ticket
+from app.services.seed import seed_categories, seed_units, seed_users
+
+
+@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 (apartment_mapping.json is not committed)
+        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_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)