# Book a Meeting Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Give the chatbot a real `book_meeting` capability (check availability, book, cancel) for a tenant whose active meeting provider is Google Calendar or Microsoft Teams, using the OAuth connections a separate service already writes into the shared `integrations` table.

**Architecture:** Two new provider modules (`google_calendar.py`, `teams.py`) under `app/services/integrations/`, each reading/refreshing a tenant's OAuth connection and calling the provider's calendar API live (no local bookings table — the provider's own calendar is the source of truth). Three new chat tools (`check_availability`, `book_meeting`, `cancel_meeting`) wired into the existing `TOOL_SCHEMAS`/`TOOL_EXECUTORS` pattern, gated by a new `book_meeting` feature switch and `providers.book_meeting` selection in `tools_settings.py`.

**Tech Stack:** Python, FastAPI, `httpx` for the Google Calendar and Microsoft Graph REST APIs (no SDK — matches this codebase's existing style in `mailchimp.py`), `psycopg2` for the shared per-tenant `integrations` table.

**Spec:** docs/superpowers/specs/2026-08-25-book-a-meeting-design.md

## Global Constraints

- Providers in scope: **Google Calendar and Microsoft Teams only.** Zoom and Google Meet are out of scope — do not add code paths for them.
- **No new database table.** Every provider call reads/writes the provider's own calendar live; nothing is cached or persisted locally.
- Meeting duration is fixed at 30 minutes for this phase — not tenant-configurable.
- Timezone comes from the connected calendar's own setting (`calendars.get` for Google, `mailboxSettings` for Teams), never from a new tenant setting or the visitor's browser.
- The shared `integrations` table lives in the `galaxiq_tenants` database, in each tenant's own schema (e.g. `"org_xxx".integrations`), with columns: `id`, `"user"`, `"organisationId"`, `"tenantId"`, `provider`, `"authType"`, `"accessToken"`, `"userAccessToken"`, `"pageAccessToken"`, `"refreshToken"`, `"expiresAt"`, `metadata`, `status`, `"createdAt"`, `"updatedAt"`. The `provider` column values already in use are the literal strings `'google-calendar'` and `'teams'` (hyphenated/lowercase, written by a different service) — every query in this plan must match those exact strings.
- Every new provider function must never raise for a delivery/connection failure — same contract as `send_via_mandrill` and `get_mailchimp_connection`: catch, log, and return `None`/an error dict instead.

## Prerequisite (blocks deployment, not implementation)

Refreshing an OAuth access token requires the **same `client_id`/`client_secret`** that originally issued it. These belong to whichever other service built the existing connect flow (the one that already wrote the `ACTIVE` rows found in staging) — this repo has never had them and they cannot be guessed or reverse-engineered from the stored tokens. Before this plan's code can be deployed against real tenants, someone needs to get from that service's owner:

1. The Google OAuth `client_id`/`client_secret` used for the Calendar connect flow (must be registered with Calendar API scopes, e.g. `https://www.googleapis.com/auth/calendar`).
2. The Microsoft Entra/Azure AD app registration `client_id`/`client_secret` used for the Teams connect flow (must have `Calendars.ReadWrite` and `MailboxSettings.Read` Graph permissions).
3. Confirmation of whether `accessToken`/`refreshToken` are stored encrypted or in plaintext in the `integrations` table, so the read path can decrypt if needed.

This plan's tasks are fully implementable and testable now (every provider call is mocked in tests, per the spec), but do not deploy Tasks 1 or 3's token-refresh code against production without those three answers — a wrong assumption here fails silently as "every token refresh fails, provider treated as disconnected."

Add placeholder settings now so the code has somewhere to read from once the real values arrive — `app/core/config.py`, alongside the existing `SHOPIFY_CLIENT_ID`/`SHOPIFY_CLIENT_SECRET` pattern:

```python
    GOOGLE_CALENDAR_CLIENT_ID: str = ""
    GOOGLE_CALENDAR_CLIENT_SECRET: str = ""
    MS_TEAMS_CLIENT_ID: str = ""
    MS_TEAMS_CLIENT_SECRET: str = ""
```

---

### Task 1: Google Calendar connection read + token refresh

**Files:**
- Create: `app/services/integrations/google_calendar.py`
- Modify: `app/core/config.py` (add `GOOGLE_CALENDAR_CLIENT_ID`/`GOOGLE_CALENDAR_CLIENT_SECRET`)
- Test: `tests/unit/test_google_calendar_connection.py`

**Interfaces:**
- Consumes: `app.services.infra.database.get_db_connection` (per-tenant DB connection, same helper `mailchimp.py` uses).
- Produces: `get_google_calendar_connection(tenant_id: str) -> dict | None`, returning `{"access_token": str, "calendar_email": str, "timezone": str}` on success. Tasks 2 and 6 both call this by name. Also produces `settings.GOOGLE_CALENDAR_CLIENT_ID`/`settings.GOOGLE_CALENDAR_CLIENT_SECRET` (empty-string defaults) for this task's own `_refresh` to read.

Before Step 1, add these two fields to the `Settings` class in `app/core/config.py`, alongside the existing `SHOPIFY_CLIENT_ID`/`SHOPIFY_CLIENT_SECRET` fields:

```python
    GOOGLE_CALENDAR_CLIENT_ID: str = ""
    GOOGLE_CALENDAR_CLIENT_SECRET: str = ""
```

These are placeholder defaults for this phase — real values come from whoever owns the existing OAuth connect flow (see the plan's Prerequisite section); do not deploy token-refresh against production before they're set in the real `.env`. Tests use `monkeypatch.setattr` on `settings.GOOGLE_CALENDAR_CLIENT_ID`/`_SECRET` directly, so the empty-string default never affects test behavior.

- [ ] **Step 1: Write the failing tests**

```python
"""get_google_calendar_connection reads a tenant's Google Calendar OAuth
connection from the shared integrations table, refreshing the access token
first if it has expired. Fully isolated from a real database or network
call -- get_db_connection and httpx.post/get are mocked in every test.
"""
from datetime import datetime, timedelta, timezone
from unittest.mock import MagicMock, patch

from app.services.integrations.google_calendar import get_google_calendar_connection


def _mock_row(monkeypatch, row):
    """A fake `SELECT "accessToken", "refreshToken", "expiresAt" FROM
    {tenant}.integrations WHERE provider = 'google-calendar'` result."""
    conn = MagicMock()
    cur = conn.cursor.return_value.__enter__.return_value
    cur.fetchone.return_value = row
    monkeypatch.setattr(
        "app.services.integrations.google_calendar.get_db_connection", lambda: conn)
    return conn, cur


def test_no_row_means_not_connected(monkeypatch):
    _mock_row(monkeypatch, None)
    assert get_google_calendar_connection("org_test") is None


def test_a_disconnected_integration_is_not_used(monkeypatch):
    # The row still exists after a tenant disconnects -- status flips
    # instead of the row being deleted (same pattern platform.py's
    # set_integration_status uses for the master-DB integrations table).
    future = datetime.now(timezone.utc) + timedelta(hours=1)
    _mock_row(monkeypatch, ("access-tok", "refresh-tok", future))
    conn = MagicMock()
    cur = conn.cursor.return_value.__enter__.return_value
    cur.fetchone.return_value = None  # status filter excludes it
    monkeypatch.setattr(
        "app.services.integrations.google_calendar.get_db_connection", lambda: conn)
    assert get_google_calendar_connection("org_test") is None


def test_a_still_valid_token_is_used_without_refreshing(monkeypatch):
    future = datetime.now(timezone.utc) + timedelta(hours=1)
    _mock_row(monkeypatch, ("access-tok", "refresh-tok", future))

    calendar_response = MagicMock()
    calendar_response.raise_for_status.return_value = None
    calendar_response.json.return_value = {
        "id": "shivraj@galaxiq.ai", "timeZone": "Australia/Sydney"}
    with patch("httpx.get", return_value=calendar_response) as get, \
         patch("httpx.post") as post:
        connection = get_google_calendar_connection("org_test")

    post.assert_not_called()  # no refresh call -- token was still valid
    get.assert_called_once()
    assert connection == {
        "access_token": "access-tok",
        "calendar_email": "shivraj@galaxiq.ai",
        "timezone": "Australia/Sydney",
    }


def test_an_expired_token_is_refreshed_before_use(monkeypatch):
    past = datetime.now(timezone.utc) - timedelta(minutes=5)
    conn, cur = _mock_row(monkeypatch, ("stale-tok", "refresh-tok", past))
    monkeypatch.setattr(
        "app.services.integrations.google_calendar.settings.GOOGLE_CALENDAR_CLIENT_ID",
        "client-id")
    monkeypatch.setattr(
        "app.services.integrations.google_calendar.settings.GOOGLE_CALENDAR_CLIENT_SECRET",
        "client-secret")

    refresh_response = MagicMock()
    refresh_response.raise_for_status.return_value = None
    refresh_response.json.return_value = {"access_token": "fresh-tok", "expires_in": 3600}

    calendar_response = MagicMock()
    calendar_response.raise_for_status.return_value = None
    calendar_response.json.return_value = {
        "id": "shivraj@galaxiq.ai", "timeZone": "Australia/Sydney"}

    with patch("httpx.post", return_value=refresh_response) as post, \
         patch("httpx.get", return_value=calendar_response):
        connection = get_google_calendar_connection("org_test")

    post.assert_called_once()
    assert post.call_args.kwargs["data"]["refresh_token"] == "refresh-tok"
    assert post.call_args.kwargs["data"]["client_id"] == "client-id"
    assert connection["access_token"] == "fresh-tok"
    # The new token and expiry are written back so the next call doesn't
    # refresh again.
    update_call = next(c for c in cur.execute.call_args_list if "UPDATE" in c.args[0])
    assert '"accessToken"' in update_call.args[0] and '"expiresAt"' in update_call.args[0]
    assert update_call.args[1][0] == "fresh-tok"


def test_a_refresh_failure_is_treated_as_not_connected(monkeypatch):
    past = datetime.now(timezone.utc) - timedelta(minutes=5)
    _mock_row(monkeypatch, ("stale-tok", "revoked-refresh-tok", past))

    refresh_response = MagicMock()
    refresh_response.raise_for_status.side_effect = Exception("invalid_grant")

    with patch("httpx.post", return_value=refresh_response):
        assert get_google_calendar_connection("org_test") is None


def test_a_db_error_is_treated_as_not_connected(monkeypatch):
    conn = MagicMock()
    conn.cursor.side_effect = Exception("connection refused")
    monkeypatch.setattr(
        "app.services.integrations.google_calendar.get_db_connection", lambda: conn)
    assert get_google_calendar_connection("org_test") is None
```

- [ ] **Step 2: Run tests to verify they fail**

Run: `pytest tests/unit/test_google_calendar_connection.py -v`
Expected: FAIL with `ModuleNotFoundError: No module named 'app.services.integrations.google_calendar'`

- [ ] **Step 3: Write the implementation**

```python
"""A tenant's connected Google Calendar account: reading it, refreshing its
access token when expired, and everything below this module needs to call
the Calendar API.

The connection is written by the dashboard's generic OAuth connect flow
into the tenant's own schema -- the same galaxiq_tenants database and
per-tenant `integrations` table mailchimp.py's connection lives beside (see
app/services/infra/database.py's get_setting). That connect flow lives in a
different service, not this one; this module only ever reads what it wrote,
and writes back a refreshed access token in place.
"""
import logging
from datetime import datetime, timedelta, timezone

import httpx

from app.core.config import settings
from app.services.infra.database import get_db_connection

logger = logging.getLogger(__name__)

TOKEN_URL = "https://oauth2.googleapis.com/token"
CALENDAR_URL = "https://www.googleapis.com/calendar/v3/calendars/primary"

PROVIDER = "google-calendar"


def get_google_calendar_connection(tenant_id: str):
    """This tenant's active Google Calendar connection, refreshed if its
    access token has expired.

    Returns {"access_token", "calendar_email", "timezone"} -- everything
    check_availability/create_event/cancel_event need. None covers every
    reason it cannot be used: no connection, a disconnected one, or a
    refresh failure (revoked consent).
    """
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                'SELECT "accessToken", "refreshToken", "expiresAt" FROM '
                f'"{tenant_id}".integrations WHERE provider = %s AND status = %s',
                (PROVIDER, "ACTIVE"))
            row = cur.fetchone()
    except Exception as ex:
        logger.error(f"Could not read the Google Calendar connection for {tenant_id}: {ex}")
        return None
    finally:
        conn.close()

    if not row:
        return None

    access_token, refresh_token, expires_at = row
    if expires_at is not None and expires_at <= datetime.now(timezone.utc):
        access_token = _refresh(tenant_id, refresh_token)
        if not access_token:
            return None

    try:
        response = httpx.get(
            CALENDAR_URL,
            headers={"Authorization": f"Bearer {access_token}"},
            timeout=20,
        )
        response.raise_for_status()
        calendar = response.json()
    except Exception as ex:
        logger.error(f"Could not read the Google Calendar for {tenant_id}: {ex}")
        return None

    return {
        "access_token": access_token,
        "calendar_email": calendar.get("id") or "",
        "timezone": calendar.get("timeZone") or "UTC",
    }


def _refresh(tenant_id: str, refresh_token: str):
    """Exchanges a refresh token for a new access token, and writes the new
    token + expiry back onto the same row so the next call doesn't refresh
    again. Returns the new access token, or None on failure (a revoked
    refresh token is the same as never having connected)."""
    try:
        response = httpx.post(
            TOKEN_URL,
            data={
                "client_id": settings.GOOGLE_CALENDAR_CLIENT_ID,
                "client_secret": settings.GOOGLE_CALENDAR_CLIENT_SECRET,
                "refresh_token": refresh_token,
                "grant_type": "refresh_token",
            },
            timeout=20,
        )
        response.raise_for_status()
        payload = response.json()
    except Exception as ex:
        logger.error(f"Could not refresh the Google Calendar token for {tenant_id}: {ex}")
        return None

    access_token = payload.get("access_token")
    if not access_token:
        return None

    expires_at = datetime.now(timezone.utc) + timedelta(
        seconds=payload.get("expires_in") or 3600)

    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                f'UPDATE "{tenant_id}".integrations SET "accessToken" = %s, '
                '"expiresAt" = %s WHERE provider = %s',
                (access_token, expires_at, PROVIDER))
        conn.commit()
    except Exception as ex:
        conn.rollback()
        logger.error(f"Could not persist the refreshed Google Calendar token "
                     f"for {tenant_id}: {ex}")
    finally:
        conn.close()

    return access_token
```

- [ ] **Step 4: Run tests to verify they pass**

Run: `pytest tests/unit/test_google_calendar_connection.py -v`
Expected: PASS (7 tests)

- [ ] **Step 5: Commit**

```bash
git add app/services/integrations/google_calendar.py tests/unit/test_google_calendar_connection.py
git commit -m "feat: read and refresh a tenant's Google Calendar connection"
```

---

### Task 2: Google Calendar availability, booking, and cancellation

**Files:**
- Modify: `app/services/integrations/google_calendar.py`
- Test: `tests/unit/test_google_calendar_booking.py`

**Interfaces:**
- Consumes: the `connection` dict shape from Task 1 (`{"access_token", "calendar_email", "timezone"}`).
- Produces:
  - `check_availability(connection: dict, start: datetime, end: datetime, duration_minutes: int = 30) -> list[dict]` — returns `[{"start": datetime, "end": datetime}, ...]`, each a `duration_minutes`-long open slot inside `[start, end]`.
  - `create_event(connection: dict, start: datetime, end: datetime, visitor_email: str, visitor_name: str) -> dict | None` — returns `{"event_id": str, "meeting_link": str | None}` on success, `None` on failure.
  - `cancel_event(connection: dict, visitor_email: str, near_time: datetime = None) -> bool` — `True` if exactly one matching event was found and deleted.
  Task 6 calls all three by name.

- [ ] **Step 1: Write the failing tests**

```python
"""check_availability/create_event/cancel_event call the Google Calendar
API directly -- freebusy.query for availability, events.insert for
booking (with the visitor as an attendee, which is what sends the calendar
invite), events.list + events.delete for cancellation. Fully isolated --
httpx is mocked in every test.
"""
from datetime import datetime, timedelta, timezone
from unittest.mock import MagicMock, patch

from app.services.integrations.google_calendar import (
    cancel_event, check_availability, create_event,
)

CONNECTION = {
    "access_token": "access-tok",
    "calendar_email": "shivraj@galaxiq.ai",
    "timezone": "Australia/Sydney",
}

START = datetime(2026, 9, 1, 9, 0, tzinfo=timezone.utc)
END = datetime(2026, 9, 1, 17, 0, tzinfo=timezone.utc)


def _json_response(payload, status_ok=True):
    response = MagicMock()
    if status_ok:
        response.raise_for_status.return_value = None
    else:
        response.raise_for_status.side_effect = Exception("http error")
    response.json.return_value = payload
    return response


def test_a_day_with_no_busy_blocks_is_fully_open(monkeypatch):
    response = _json_response({
        "calendars": {"shivraj@galaxiq.ai": {"busy": []}}})
    with patch("httpx.post", return_value=response):
        slots = check_availability(CONNECTION, START, END)

    assert len(slots) > 0
    assert all(s["end"] - s["start"] == timedelta(minutes=30) for s in slots)


def test_a_busy_block_removes_the_overlapping_slots(monkeypatch):
    busy_start = datetime(2026, 9, 1, 10, 0, tzinfo=timezone.utc)
    busy_end = datetime(2026, 9, 1, 11, 0, tzinfo=timezone.utc)
    response = _json_response({
        "calendars": {"shivraj@galaxiq.ai": {"busy": [
            {"start": busy_start.isoformat(), "end": busy_end.isoformat()}]}}})
    with patch("httpx.post", return_value=response):
        slots = check_availability(CONNECTION, START, END)

    assert not any(s["start"] < busy_end and s["end"] > busy_start for s in slots)


def test_an_api_failure_returns_no_slots_not_a_crash(monkeypatch):
    response = _json_response({}, status_ok=False)
    with patch("httpx.post", return_value=response):
        assert check_availability(CONNECTION, START, END) == []


def test_creating_an_event_returns_its_id_and_meet_link(monkeypatch):
    response = _json_response({
        "id": "evt123",
        "hangoutLink": "https://meet.google.com/abc-defg-hij",
    })
    with patch("httpx.post", return_value=response) as post:
        result = create_event(CONNECTION, START, START + timedelta(minutes=30),
                              "visitor@example.com", "Jo Visitor")

    assert result == {"event_id": "evt123",
                      "meeting_link": "https://meet.google.com/abc-defg-hij"}
    body = post.call_args.kwargs["json"]
    assert body["attendees"] == [{"email": "visitor@example.com"}]
    assert post.call_args.kwargs["params"]["sendUpdates"] == "all"
    assert post.call_args.kwargs["params"]["conferenceDataVersion"] == 1


def test_creating_an_event_with_no_meet_link_still_returns_the_event_id(monkeypatch):
    response = _json_response({"id": "evt123"})  # no hangoutLink
    with patch("httpx.post", return_value=response):
        result = create_event(CONNECTION, START, START + timedelta(minutes=30),
                              "visitor@example.com", "Jo Visitor")

    assert result == {"event_id": "evt123", "meeting_link": None}


def test_event_creation_failure_returns_none(monkeypatch):
    response = _json_response({}, status_ok=False)
    with patch("httpx.post", return_value=response):
        result = create_event(CONNECTION, START, START + timedelta(minutes=30),
                              "visitor@example.com", "Jo Visitor")

    assert result is None


def test_cancel_deletes_the_single_matching_event(monkeypatch):
    list_response = _json_response({"items": [{"id": "evt123"}]})
    delete_response = MagicMock()
    delete_response.raise_for_status.return_value = None
    with patch("httpx.get", return_value=list_response), \
         patch("httpx.delete", return_value=delete_response) as delete:
        assert cancel_event(CONNECTION, "visitor@example.com") is True

    assert "evt123" in delete.call_args[0][0]


def test_cancel_with_no_matching_event_returns_false(monkeypatch):
    list_response = _json_response({"items": []})
    with patch("httpx.get", return_value=list_response), \
         patch("httpx.delete") as delete:
        assert cancel_event(CONNECTION, "visitor@example.com") is False

    delete.assert_not_called()


def test_cancel_with_more_than_one_matching_event_is_refused_not_guessed(monkeypatch):
    list_response = _json_response({"items": [{"id": "evt123"}, {"id": "evt456"}]})
    with patch("httpx.get", return_value=list_response), \
         patch("httpx.delete") as delete:
        assert cancel_event(CONNECTION, "visitor@example.com") is False

    delete.assert_not_called()


def test_cancel_narrows_by_near_time_when_given(monkeypatch):
    list_response = _json_response({"items": [{"id": "evt123"}]})
    with patch("httpx.get", return_value=list_response) as get:
        cancel_event(CONNECTION, "visitor@example.com",
                     near_time=datetime(2026, 9, 1, 10, 0, tzinfo=timezone.utc))

    params = get.call_args.kwargs["params"]
    assert "timeMin" in params and "timeMax" in params
```

- [ ] **Step 2: Run tests to verify they fail**

Run: `pytest tests/unit/test_google_calendar_booking.py -v`
Expected: FAIL with `ImportError: cannot import name 'check_availability'`

- [ ] **Step 3: Write the implementation**

Append to `app/services/integrations/google_calendar.py`:

```python
FREEBUSY_URL = "https://www.googleapis.com/calendar/v3/freeBusy"
EVENTS_URL = "https://www.googleapis.com/calendar/v3/calendars/primary/events"

SLOT_MINUTES = 30


def check_availability(connection: dict, start, end, duration_minutes: int = SLOT_MINUTES) -> list:
    """Open duration_minutes-long slots inside [start, end], on the half
    hour. Returns [] on any API failure -- an empty list of slots reads to
    the model as "nothing available", which is the safe failure mode
    (never claims a slot is free when the check itself failed)."""
    try:
        response = httpx.post(
            FREEBUSY_URL,
            headers={"Authorization": f"Bearer {connection['access_token']}"},
            json={
                "timeMin": start.isoformat(),
                "timeMax": end.isoformat(),
                "items": [{"id": connection["calendar_email"]}],
            },
            timeout=20,
        )
        response.raise_for_status()
        busy_blocks = response.json()["calendars"][connection["calendar_email"]]["busy"]
    except Exception as ex:
        logger.error(f"Could not check Google Calendar availability: {ex}")
        return []

    busy = [(datetime.fromisoformat(b["start"]), datetime.fromisoformat(b["end"]))
            for b in busy_blocks]

    slots = []
    cursor = start
    step = timedelta(minutes=duration_minutes)
    while cursor + step <= end:
        slot_end = cursor + step
        if not any(cursor < b_end and slot_end > b_start for b_start, b_end in busy):
            slots.append({"start": cursor, "end": slot_end})
        cursor += step
    return slots


def create_event(connection: dict, start, end, visitor_email: str, visitor_name: str):
    """Books the slot and emails the visitor a calendar invite (sendUpdates
    'all' is what makes Calendar actually send it) with an auto-attached
    Meet link (conferenceDataVersion 1). Returns None on failure -- never
    raises, matching every other provider call in this module."""
    try:
        response = httpx.post(
            EVENTS_URL,
            headers={"Authorization": f"Bearer {connection['access_token']}"},
            params={"sendUpdates": "all", "conferenceDataVersion": 1},
            json={
                "summary": f"Meeting with {visitor_name}",
                "start": {"dateTime": start.isoformat()},
                "end": {"dateTime": end.isoformat()},
                "attendees": [{"email": visitor_email}],
                "conferenceData": {
                    "createRequest": {"requestId": f"{visitor_email}-{start.isoformat()}"}},
            },
            timeout=20,
        )
        response.raise_for_status()
        event = response.json()
    except Exception as ex:
        logger.error(f"Could not create the Google Calendar event: {ex}")
        return None

    return {"event_id": event["id"], "meeting_link": event.get("hangoutLink")}


def cancel_event(connection: dict, visitor_email: str, near_time=None) -> bool:
    """Finds the event by attendee email (narrowed to a window around
    near_time when given) and deletes it. Returns False -- does not guess
    -- when zero or more than one event matches."""
    params = {"q": visitor_email}
    if near_time is not None:
        params["timeMin"] = (near_time - timedelta(hours=2)).isoformat()
        params["timeMax"] = (near_time + timedelta(hours=2)).isoformat()

    try:
        response = httpx.get(
            EVENTS_URL,
            headers={"Authorization": f"Bearer {connection['access_token']}"},
            params=params,
            timeout=20,
        )
        response.raise_for_status()
        items = response.json().get("items") or []
    except Exception as ex:
        logger.error(f"Could not search the Google Calendar for cancellation: {ex}")
        return False

    if len(items) != 1:
        return False

    try:
        response = httpx.delete(
            f"{EVENTS_URL}/{items[0]['id']}",
            headers={"Authorization": f"Bearer {connection['access_token']}"},
            timeout=20,
        )
        response.raise_for_status()
        return True
    except Exception as ex:
        logger.error(f"Could not delete the Google Calendar event: {ex}")
        return False
```

- [ ] **Step 4: Run tests to verify they pass**

Run: `pytest tests/unit/test_google_calendar_booking.py -v`
Expected: PASS (10 tests)

- [ ] **Step 5: Commit**

```bash
git add app/services/integrations/google_calendar.py tests/unit/test_google_calendar_booking.py
git commit -m "feat: Google Calendar availability, booking, and cancellation"
```

---

### Task 3: Microsoft Teams connection read + token refresh

**Files:**
- Create: `app/services/integrations/teams.py`
- Modify: `app/core/config.py` (add `MS_TEAMS_CLIENT_ID`/`MS_TEAMS_CLIENT_SECRET`)
- Test: `tests/unit/test_teams_connection.py`

**Interfaces:**
- Consumes: `app.services.infra.database.get_db_connection`.
- Produces: `get_teams_connection(tenant_id: str) -> dict | None`, returning `{"access_token": str, "user_email": str, "timezone": str}`. Tasks 4 and 6 both call this by name. Also produces `settings.MS_TEAMS_CLIENT_ID`/`settings.MS_TEAMS_CLIENT_SECRET` (empty-string defaults) for this task's own `_refresh` to read.

Before Step 1, add these two fields to the `Settings` class in `app/core/config.py`, alongside the fields Task 1 already added:

```python
    MS_TEAMS_CLIENT_ID: str = ""
    MS_TEAMS_CLIENT_SECRET: str = ""
```

Same placeholder-default reasoning as Task 1's `GOOGLE_CALENDAR_CLIENT_ID`/`_SECRET` — see the plan's Prerequisite section.

- [ ] **Step 1: Write the failing tests**

Same structure as Task 1's tests, adapted for Microsoft Graph. Create `tests/unit/test_teams_connection.py`:

```python
"""get_teams_connection reads a tenant's Microsoft Teams OAuth connection
from the shared integrations table, refreshing the access token first if
expired. Fully isolated -- get_db_connection and httpx are mocked in every
test.
"""
from datetime import datetime, timedelta, timezone
from unittest.mock import MagicMock, patch

from app.services.integrations.teams import get_teams_connection


def _mock_row(monkeypatch, row):
    conn = MagicMock()
    cur = conn.cursor.return_value.__enter__.return_value
    cur.fetchone.return_value = row
    monkeypatch.setattr(
        "app.services.integrations.teams.get_db_connection", lambda: conn)
    return conn, cur


def test_no_row_means_not_connected(monkeypatch):
    _mock_row(monkeypatch, None)
    assert get_teams_connection("org_test") is None


def test_a_still_valid_token_is_used_without_refreshing(monkeypatch):
    future = datetime.now(timezone.utc) + timedelta(hours=1)
    _mock_row(monkeypatch, ("access-tok", "refresh-tok", future))

    settings_response = MagicMock()
    settings_response.raise_for_status.return_value = None
    settings_response.json.return_value = {"timeZone": "Pacific Standard Time"}
    me_response = MagicMock()
    me_response.raise_for_status.return_value = None
    me_response.json.return_value = {"mail": "shivraj@galaxiq.ai"}

    with patch("httpx.get", side_effect=[settings_response, me_response]) as get, \
         patch("httpx.post") as post:
        connection = get_teams_connection("org_test")

    post.assert_not_called()
    assert get.call_count == 2
    assert connection == {
        "access_token": "access-tok",
        "user_email": "shivraj@galaxiq.ai",
        "timezone": "Pacific Standard Time",
    }


def test_an_expired_token_is_refreshed_before_use(monkeypatch):
    past = datetime.now(timezone.utc) - timedelta(minutes=5)
    conn, cur = _mock_row(monkeypatch, ("stale-tok", "refresh-tok", past))
    monkeypatch.setattr(
        "app.services.integrations.teams.settings.MS_TEAMS_CLIENT_ID", "client-id")
    monkeypatch.setattr(
        "app.services.integrations.teams.settings.MS_TEAMS_CLIENT_SECRET", "client-secret")

    refresh_response = MagicMock()
    refresh_response.raise_for_status.return_value = None
    refresh_response.json.return_value = {"access_token": "fresh-tok", "expires_in": 3600}
    settings_response = MagicMock()
    settings_response.raise_for_status.return_value = None
    settings_response.json.return_value = {"timeZone": "Pacific Standard Time"}
    me_response = MagicMock()
    me_response.raise_for_status.return_value = None
    me_response.json.return_value = {"mail": "shivraj@galaxiq.ai"}

    with patch("httpx.post", return_value=refresh_response) as post, \
         patch("httpx.get", side_effect=[settings_response, me_response]):
        connection = get_teams_connection("org_test")

    post.assert_called_once()
    assert post.call_args.kwargs["data"]["refresh_token"] == "refresh-tok"
    assert connection["access_token"] == "fresh-tok"


def test_a_refresh_failure_is_treated_as_not_connected(monkeypatch):
    past = datetime.now(timezone.utc) - timedelta(minutes=5)
    _mock_row(monkeypatch, ("stale-tok", "revoked-refresh-tok", past))
    refresh_response = MagicMock()
    refresh_response.raise_for_status.side_effect = Exception("invalid_grant")
    with patch("httpx.post", return_value=refresh_response):
        assert get_teams_connection("org_test") is None


def test_a_db_error_is_treated_as_not_connected(monkeypatch):
    conn = MagicMock()
    conn.cursor.side_effect = Exception("connection refused")
    monkeypatch.setattr(
        "app.services.integrations.teams.get_db_connection", lambda: conn)
    assert get_teams_connection("org_test") is None
```

- [ ] **Step 2: Run tests to verify they fail**

Run: `pytest tests/unit/test_teams_connection.py -v`
Expected: FAIL with `ModuleNotFoundError: No module named 'app.services.integrations.teams'`

- [ ] **Step 3: Write the implementation**

```python
"""A tenant's connected Microsoft Teams account: reading it, refreshing its
access token when expired, and everything below this module needs to call
Microsoft Graph.

Same shape as google_calendar.py, and the same connect flow -- written by a
different service into the tenant's own galaxiq_tenants schema, provider
value 'teams'. This module only ever reads what it wrote.
"""
import logging
from datetime import datetime, timedelta, timezone

import httpx

from app.core.config import settings
from app.services.infra.database import get_db_connection

logger = logging.getLogger(__name__)

TOKEN_URL = "https://login.microsoftonline.com/common/oauth2/v2.0/token"
GRAPH_BASE = "https://graph.microsoft.com/v1.0"

PROVIDER = "teams"


def get_teams_connection(tenant_id: str):
    """This tenant's active Microsoft Teams connection, refreshed if its
    access token has expired.

    Returns {"access_token", "user_email", "timezone"} -- everything
    check_availability/create_event/cancel_event need. None covers every
    reason it cannot be used: no connection, a disconnected one, or a
    refresh failure (revoked consent).
    """
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                'SELECT "accessToken", "refreshToken", "expiresAt" FROM '
                f'"{tenant_id}".integrations WHERE provider = %s AND status = %s',
                (PROVIDER, "ACTIVE"))
            row = cur.fetchone()
    except Exception as ex:
        logger.error(f"Could not read the Teams connection for {tenant_id}: {ex}")
        return None
    finally:
        conn.close()

    if not row:
        return None

    access_token, refresh_token, expires_at = row
    if expires_at is not None and expires_at <= datetime.now(timezone.utc):
        access_token = _refresh(tenant_id, refresh_token)
        if not access_token:
            return None

    headers = {"Authorization": f"Bearer {access_token}"}
    try:
        settings_response = httpx.get(
            f"{GRAPH_BASE}/me/mailboxSettings", headers=headers, timeout=20)
        settings_response.raise_for_status()
        mailbox_settings = settings_response.json()

        me_response = httpx.get(f"{GRAPH_BASE}/me", headers=headers, timeout=20)
        me_response.raise_for_status()
        me = me_response.json()
    except Exception as ex:
        logger.error(f"Could not read Microsoft Graph profile for {tenant_id}: {ex}")
        return None

    return {
        "access_token": access_token,
        "user_email": me.get("mail") or me.get("userPrincipalName") or "",
        "timezone": mailbox_settings.get("timeZone") or "UTC",
    }


def _refresh(tenant_id: str, refresh_token: str):
    """Exchanges a refresh token for a new access token, and writes it back
    onto the same row. Returns None on failure."""
    try:
        response = httpx.post(
            TOKEN_URL,
            data={
                "client_id": settings.MS_TEAMS_CLIENT_ID,
                "client_secret": settings.MS_TEAMS_CLIENT_SECRET,
                "refresh_token": refresh_token,
                "grant_type": "refresh_token",
                "scope": "https://graph.microsoft.com/.default",
            },
            timeout=20,
        )
        response.raise_for_status()
        payload = response.json()
    except Exception as ex:
        logger.error(f"Could not refresh the Teams token for {tenant_id}: {ex}")
        return None

    access_token = payload.get("access_token")
    if not access_token:
        return None

    expires_at = datetime.now(timezone.utc) + timedelta(
        seconds=payload.get("expires_in") or 3600)

    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                f'UPDATE "{tenant_id}".integrations SET "accessToken" = %s, '
                '"expiresAt" = %s WHERE provider = %s',
                (access_token, expires_at, PROVIDER))
        conn.commit()
    except Exception as ex:
        conn.rollback()
        logger.error(f"Could not persist the refreshed Teams token for {tenant_id}: {ex}")
    finally:
        conn.close()

    return access_token
```

- [ ] **Step 4: Run tests to verify they pass**

Run: `pytest tests/unit/test_teams_connection.py -v`
Expected: PASS (5 tests)

- [ ] **Step 5: Commit**

```bash
git add app/services/integrations/teams.py tests/unit/test_teams_connection.py
git commit -m "feat: read and refresh a tenant's Microsoft Teams connection"
```

---

### Task 4: Microsoft Teams availability, booking, and cancellation

**Files:**
- Modify: `app/services/integrations/teams.py`
- Test: `tests/unit/test_teams_booking.py`

**Interfaces:**
- Consumes: the `connection` dict shape from Task 3 (`{"access_token", "user_email", "timezone"}`).
- Produces:
  - `check_availability(connection: dict, start, end, duration_minutes: int = 30) -> list[dict]` — same return shape as Task 2's.
  - `create_event(connection: dict, start, end, visitor_email: str, visitor_name: str) -> dict | None` — `{"event_id": str, "meeting_link": str | None}`.
  - `cancel_event(connection: dict, visitor_email: str, near_time: datetime = None) -> bool`.
  Task 6 calls all three by name (dispatching to this module or Task 2's depending on which provider is active).

- [ ] **Step 1: Write the failing tests**

```python
"""check_availability/create_event/cancel_event call Microsoft Graph
directly -- getSchedule for availability, POST /me/events with
isOnlineMeeting for booking (this one call both books the calendar slot
and creates the Teams meeting), GET+DELETE /me/events for cancellation.
Fully isolated -- httpx is mocked in every test.
"""
from datetime import datetime, timedelta, timezone
from unittest.mock import MagicMock, patch

from app.services.integrations.teams import cancel_event, check_availability, create_event

CONNECTION = {
    "access_token": "access-tok",
    "user_email": "shivraj@galaxiq.ai",
    "timezone": "Pacific Standard Time",
}

START = datetime(2026, 9, 1, 9, 0, tzinfo=timezone.utc)
END = datetime(2026, 9, 1, 17, 0, tzinfo=timezone.utc)


def _json_response(payload, status_ok=True):
    response = MagicMock()
    if status_ok:
        response.raise_for_status.return_value = None
    else:
        response.raise_for_status.side_effect = Exception("http error")
    response.json.return_value = payload
    return response


def test_a_day_with_no_busy_blocks_is_fully_open(monkeypatch):
    response = _json_response({
        "value": [{"scheduleItems": []}]})
    with patch("httpx.post", return_value=response):
        slots = check_availability(CONNECTION, START, END)
    assert len(slots) > 0


def test_a_busy_block_removes_the_overlapping_slots(monkeypatch):
    busy_start = datetime(2026, 9, 1, 10, 0, tzinfo=timezone.utc)
    busy_end = datetime(2026, 9, 1, 11, 0, tzinfo=timezone.utc)
    response = _json_response({
        "value": [{"scheduleItems": [{
            "status": "busy",
            "start": {"dateTime": busy_start.strftime("%Y-%m-%dT%H:%M:%S"), "timeZone": "UTC"},
            "end": {"dateTime": busy_end.strftime("%Y-%m-%dT%H:%M:%S"), "timeZone": "UTC"},
        }]}]})
    with patch("httpx.post", return_value=response):
        slots = check_availability(CONNECTION, START, END)
    assert not any(s["start"] < busy_end and s["end"] > busy_start for s in slots)


def test_an_api_failure_returns_no_slots_not_a_crash(monkeypatch):
    response = _json_response({}, status_ok=False)
    with patch("httpx.post", return_value=response):
        assert check_availability(CONNECTION, START, END) == []


def test_creating_an_event_returns_its_id_and_teams_link(monkeypatch):
    response = _json_response({
        "id": "AAMk...evt123",
        "onlineMeeting": {"joinUrl": "https://teams.microsoft.com/l/meetup-join/abc"},
    })
    with patch("httpx.post", return_value=response) as post:
        result = create_event(CONNECTION, START, START + timedelta(minutes=30),
                              "visitor@example.com", "Jo Visitor")

    assert result == {"event_id": "AAMk...evt123",
                      "meeting_link": "https://teams.microsoft.com/l/meetup-join/abc"}
    body = post.call_args.kwargs["json"]
    assert body["isOnlineMeeting"] is True
    assert body["attendees"][0]["emailAddress"]["address"] == "visitor@example.com"


def test_event_creation_failure_returns_none(monkeypatch):
    response = _json_response({}, status_ok=False)
    with patch("httpx.post", return_value=response):
        result = create_event(CONNECTION, START, START + timedelta(minutes=30),
                              "visitor@example.com", "Jo Visitor")
    assert result is None


def test_cancel_deletes_the_single_matching_event(monkeypatch):
    list_response = _json_response({"value": [{"id": "evt123"}]})
    delete_response = MagicMock()
    delete_response.raise_for_status.return_value = None
    with patch("httpx.get", return_value=list_response), \
         patch("httpx.delete", return_value=delete_response) as delete:
        assert cancel_event(CONNECTION, "visitor@example.com") is True
    assert "evt123" in delete.call_args[0][0]


def test_cancel_with_no_matching_event_returns_false(monkeypatch):
    list_response = _json_response({"value": []})
    with patch("httpx.get", return_value=list_response), \
         patch("httpx.delete") as delete:
        assert cancel_event(CONNECTION, "visitor@example.com") is False
    delete.assert_not_called()


def test_cancel_with_more_than_one_matching_event_is_refused_not_guessed(monkeypatch):
    list_response = _json_response({"value": [{"id": "evt123"}, {"id": "evt456"}]})
    with patch("httpx.get", return_value=list_response), \
         patch("httpx.delete") as delete:
        assert cancel_event(CONNECTION, "visitor@example.com") is False
    delete.assert_not_called()
```

- [ ] **Step 2: Run tests to verify they fail**

Run: `pytest tests/unit/test_teams_booking.py -v`
Expected: FAIL with `ImportError: cannot import name 'check_availability'`

- [ ] **Step 3: Write the implementation**

Append to `app/services/integrations/teams.py`:

```python
SLOT_MINUTES = 30


def check_availability(connection: dict, start, end, duration_minutes: int = SLOT_MINUTES) -> list:
    """Open duration_minutes-long slots inside [start, end]. Returns [] on
    any API failure, same safe-failure reasoning as the Google Calendar
    module."""
    try:
        response = httpx.post(
            f"{GRAPH_BASE}/me/calendar/getSchedule",
            headers={"Authorization": f"Bearer {connection['access_token']}"},
            json={
                "schedules": [connection["user_email"]],
                "startTime": {"dateTime": start.strftime("%Y-%m-%dT%H:%M:%S"), "timeZone": "UTC"},
                "endTime": {"dateTime": end.strftime("%Y-%m-%dT%H:%M:%S"), "timeZone": "UTC"},
            },
            timeout=20,
        )
        response.raise_for_status()
        items = response.json()["value"][0].get("scheduleItems") or []
    except Exception as ex:
        logger.error(f"Could not check Teams availability: {ex}")
        return []

    busy = []
    for item in items:
        if item.get("status") != "busy":
            continue
        busy.append((
            datetime.fromisoformat(item["start"]["dateTime"]).replace(tzinfo=timezone.utc),
            datetime.fromisoformat(item["end"]["dateTime"]).replace(tzinfo=timezone.utc),
        ))

    slots = []
    cursor = start
    step = timedelta(minutes=duration_minutes)
    while cursor + step <= end:
        slot_end = cursor + step
        if not any(cursor < b_end and slot_end > b_start for b_start, b_end in busy):
            slots.append({"start": cursor, "end": slot_end})
        cursor += step
    return slots


def create_event(connection: dict, start, end, visitor_email: str, visitor_name: str):
    """Books the slot and creates the Teams meeting in one call
    (isOnlineMeeting=true) -- Graph returns the join link on the created
    event. Returns None on failure."""
    try:
        response = httpx.post(
            f"{GRAPH_BASE}/me/events",
            headers={"Authorization": f"Bearer {connection['access_token']}"},
            json={
                "subject": f"Meeting with {visitor_name}",
                "start": {"dateTime": start.strftime("%Y-%m-%dT%H:%M:%S"), "timeZone": "UTC"},
                "end": {"dateTime": end.strftime("%Y-%m-%dT%H:%M:%S"), "timeZone": "UTC"},
                "attendees": [{
                    "emailAddress": {"address": visitor_email, "name": visitor_name},
                    "type": "required",
                }],
                "isOnlineMeeting": True,
                "onlineMeetingProvider": "teamsForBusiness",
            },
            timeout=20,
        )
        response.raise_for_status()
        event = response.json()
    except Exception as ex:
        logger.error(f"Could not create the Teams event: {ex}")
        return None

    online_meeting = event.get("onlineMeeting") or {}
    return {"event_id": event["id"], "meeting_link": online_meeting.get("joinUrl")}


def cancel_event(connection: dict, visitor_email: str, near_time=None) -> bool:
    """Finds the event by attendee email (narrowed to a window around
    near_time when given) and deletes it. Returns False when zero or more
    than one event matches -- does not guess."""
    params = {
        "$filter": f"attendees/any(a: a/emailAddress/address eq '{visitor_email}')"}
    if near_time is not None:
        window_start = (near_time - timedelta(hours=2)).strftime("%Y-%m-%dT%H:%M:%S")
        window_end = (near_time + timedelta(hours=2)).strftime("%Y-%m-%dT%H:%M:%S")
        params["startDateTime"] = window_start
        params["endDateTime"] = window_end

    try:
        response = httpx.get(
            f"{GRAPH_BASE}/me/events",
            headers={"Authorization": f"Bearer {connection['access_token']}"},
            params=params,
            timeout=20,
        )
        response.raise_for_status()
        items = response.json().get("value") or []
    except Exception as ex:
        logger.error(f"Could not search Teams events for cancellation: {ex}")
        return False

    if len(items) != 1:
        return False

    try:
        response = httpx.delete(
            f"{GRAPH_BASE}/me/events/{items[0]['id']}",
            headers={"Authorization": f"Bearer {connection['access_token']}"},
            timeout=20,
        )
        response.raise_for_status()
        return True
    except Exception as ex:
        logger.error(f"Could not delete the Teams event: {ex}")
        return False
```

- [ ] **Step 4: Run tests to verify they pass**

Run: `pytest tests/unit/test_teams_booking.py -v`
Expected: PASS (8 tests)

- [ ] **Step 5: Commit**

```bash
git add app/services/integrations/teams.py tests/unit/test_teams_booking.py
git commit -m "feat: Microsoft Teams availability, booking, and cancellation"
```

---

### Task 5: Wire book_meeting into the settings model

**Files:**
- Modify: `app/services/chat/tools_settings.py`
- Test: `tests/unit/test_provider_settings.py`

**Interfaces:**
- Consumes: `get_google_calendar_connection` (Task 1), `get_teams_connection` (Task 3).
- Produces: `"book_meeting"` becomes a valid key in `FEATURE_TOOLS`, `PROVIDER_OPTIONS`, and `PROVIDER_DEFAULTS`. Task 6's tool executors read the active provider via `get_tool_settings(tenant_id)["providers"]["book_meeting"]`.

- [ ] **Step 1: Write the failing tests**

Append to `tests/unit/test_provider_settings.py` (same file the existing Mailchimp-gate tests live in — read that file first to match its existing `save_tool_settings`/`get_tool_settings` import style and fixtures before adding these):

```python
def test_book_meeting_defaults_to_unset():
    # Like product_recommendation: nothing is pre-selected, since neither
    # Google Calendar nor Teams is inherently "the" default the way SMTP is
    # for send_email.
    settings = get_tool_settings("org_provider_test_bm")
    assert settings["providers"]["book_meeting"] is None


def test_book_meeting_is_now_wired_to_a_real_tool():
    from app.services.chat.tools_settings import FEATURE_TOOLS
    assert FEATURE_TOOLS["book_meeting"] == "book_meeting"


def test_selecting_google_calendar_without_a_connection_is_refused(monkeypatch):
    monkeypatch.setattr(
        "app.services.chat.tools_settings.get_google_calendar_connection",
        lambda t: None)
    with pytest.raises(ValueError, match="Connect Google Calendar under Integrations"):
        save_tool_settings("org_provider_test_bm", providers={"book_meeting": "google_calendar"})


def test_selecting_google_calendar_with_an_active_connection_is_allowed(monkeypatch):
    monkeypatch.setattr(
        "app.services.chat.tools_settings.get_google_calendar_connection",
        lambda t: {"access_token": "tok", "calendar_email": "a@b.com", "timezone": "UTC"})
    saved = save_tool_settings("org_provider_test_bm", providers={"book_meeting": "google_calendar"})
    assert saved["providers"]["book_meeting"] == "google_calendar"


def test_selecting_teams_without_a_connection_is_refused(monkeypatch):
    monkeypatch.setattr(
        "app.services.chat.tools_settings.get_teams_connection", lambda t: None)
    with pytest.raises(ValueError, match="Connect Microsoft Teams under Integrations"):
        save_tool_settings("org_provider_test_bm", providers={"book_meeting": "teams"})


def test_selecting_teams_with_an_active_connection_is_allowed(monkeypatch):
    monkeypatch.setattr(
        "app.services.chat.tools_settings.get_teams_connection",
        lambda t: {"access_token": "tok", "user_email": "a@b.com", "timezone": "UTC"})
    saved = save_tool_settings("org_provider_test_bm", providers={"book_meeting": "teams"})
    assert saved["providers"]["book_meeting"] == "teams"
```

- [ ] **Step 2: Run tests to verify they fail**

Run: `pytest tests/unit/test_provider_settings.py -v -k book_meeting`
Expected: FAIL — `KeyError: 'book_meeting'` on the `FEATURE_TOOLS`/`PROVIDER_OPTIONS` tests, `ImportError` once the gate tests try to monkeypatch a name that doesn't exist yet.

- [ ] **Step 3: Write the implementation**

In `app/services/chat/tools_settings.py`:

1. Add the import, alongside the existing Mailchimp one:

```python
from app.services.integrations.google_calendar import get_google_calendar_connection
from app.services.integrations.mailchimp import get_mailchimp_connection
from app.services.integrations.teams import get_teams_connection
```

2. Add `"book_meeting": "book_meeting"` to `FEATURE_TOOLS`:

```python
FEATURE_TOOLS = {
    "send_email": "send_email",
    "ticket_management": "create_or_update_ticket",
    "attach_documents": "send_document",
    "product_recommendation": "recommend_products",
    "book_meeting": "book_meeting",
}
```

3. Add `"book_meeting": ("google_calendar", "teams")` to `PROVIDER_OPTIONS`:

```python
PROVIDER_OPTIONS = {
    "chat_conversation": ("whatsapp", "slack", "galaxiq_chat"),
    "ticket_management": ("galaxiq_ticketmanagement",),
    "send_email": ("smtp", "mailchimp"),
    "product_recommendation": ("shopify",),
    "book_meeting": ("google_calendar", "teams"),
}
```

4. Add `"book_meeting": None` to `PROVIDER_DEFAULTS`:

```python
PROVIDER_DEFAULTS = {
    "chat_conversation": None,
    "ticket_management": "galaxiq_ticketmanagement",
    "send_email": "smtp",
    "product_recommendation": None,
    "book_meeting": None,
}
```

5. Add the connection gate in `save_tool_settings`, immediately after the existing Mailchimp gate (around line 286):

```python
    if (providers or {}).get("book_meeting") == "google_calendar":
        if not get_google_calendar_connection(tenant_id):
            raise ValueError(
                "Connect Google Calendar under Integrations before selecting it "
                "as your meeting provider.")

    if (providers or {}).get("book_meeting") == "teams":
        if not get_teams_connection(tenant_id):
            raise ValueError(
                "Connect Microsoft Teams under Integrations before selecting it "
                "as your meeting provider.")
```

- [ ] **Step 4: Run tests to verify they pass**

Run: `pytest tests/unit/test_provider_settings.py -v`
Expected: PASS (all tests in the file, including the pre-existing ones — nothing above should have changed their behavior)

- [ ] **Step 5: Commit**

```bash
git add app/services/chat/tools_settings.py tests/unit/test_provider_settings.py
git commit -m "feat: wire book_meeting into the settings model"
```

---

### Task 6: check_availability, book_meeting, and cancel_meeting chat tools

**Files:**
- Modify: `app/services/chat/tools.py`
- Test: `tests/unit/test_book_meeting_tools.py`

**Interfaces:**
- Consumes: `check_availability`/`create_event`/`cancel_event` from both `google_calendar.py` (Task 2) and `teams.py` (Task 4); `get_tool_settings` from `tools_settings.py` (Task 5) to read `providers.book_meeting` and the `book_meeting` feature switch.
- Produces: three new entries in `TOOL_SCHEMAS`/`TOOL_EXECUTORS` — `check_availability`, `book_meeting`, `cancel_meeting` — callable by `dispatch_tool`.

- [ ] **Step 1: Write the failing tests**

Create `tests/unit/test_book_meeting_tools.py`:

```python
"""check_availability/book_meeting/cancel_meeting resolve which provider
(Google Calendar or Teams) the tenant has active, then call that provider's
module. Every provider function is mocked -- these tests only exercise the
tool-level routing, feature/connection gating, and the immediate-booking
race-condition guard.
"""
from datetime import datetime, timezone
from unittest.mock import MagicMock, patch

import pytest

from app.services.chat.tools import (
    execute_book_meeting, execute_cancel_meeting, execute_check_availability,
)


def _ctx():
    return {"tenant_id": "org_test", "thread_id": "thread_1", "user_id": None}


def _settings(feature_on=True, provider="google_calendar"):
    return {"features": {"book_meeting": feature_on},
            "providers": {"book_meeting": provider}}


def _run(coro):
    import asyncio
    return asyncio.get_event_loop().run_until_complete(coro)


@patch("app.services.chat.tools.get_tool_settings")
def test_check_availability_is_refused_when_the_feature_is_off(mock_settings):
    mock_settings.return_value = _settings(feature_on=False)
    result = _run(execute_check_availability({"date_range": "tomorrow"}, _ctx()))
    assert result["status"] == "error"


@patch("app.services.chat.tools.get_tool_settings")
def test_check_availability_is_refused_with_no_provider_selected(mock_settings):
    mock_settings.return_value = _settings(provider=None)
    result = _run(execute_check_availability({"date_range": "tomorrow"}, _ctx()))
    assert result["status"] == "error"


@patch("app.services.chat.tools.get_google_calendar_connection")
@patch("app.services.chat.tools.gcal_check_availability")
@patch("app.services.chat.tools.get_tool_settings")
def test_check_availability_routes_to_google_calendar(mock_settings, mock_check, mock_conn):
    mock_settings.return_value = _settings(provider="google_calendar")
    mock_conn.return_value = {"access_token": "tok", "calendar_email": "a@b.com",
                              "timezone": "UTC"}
    mock_check.return_value = [{"start": datetime(2026, 9, 1, 9, tzinfo=timezone.utc),
                                "end": datetime(2026, 9, 1, 9, 30, tzinfo=timezone.utc)}]

    result = _run(execute_check_availability({"date_range": "tomorrow"}, _ctx()))

    mock_check.assert_called_once()
    assert result["status"] == "ok"
    assert len(result["slots"]) == 1


@patch("app.services.chat.tools.get_teams_connection")
@patch("app.services.chat.tools.teams_check_availability")
@patch("app.services.chat.tools.get_tool_settings")
def test_check_availability_routes_to_teams(mock_settings, mock_check, mock_conn):
    mock_settings.return_value = _settings(provider="teams")
    mock_conn.return_value = {"access_token": "tok", "user_email": "a@b.com",
                              "timezone": "UTC"}
    mock_check.return_value = []

    result = _run(execute_check_availability({"date_range": "tomorrow"}, _ctx()))

    mock_check.assert_called_once()
    assert result["status"] == "ok"


@patch("app.services.chat.tools.get_google_calendar_connection")
@patch("app.services.chat.tools.gcal_check_availability")
@patch("app.services.chat.tools.gcal_create_event")
@patch("app.services.chat.tools.get_tool_settings")
def test_book_meeting_books_a_still_free_slot(mock_settings, mock_create, mock_check, mock_conn):
    mock_settings.return_value = _settings(provider="google_calendar")
    mock_conn.return_value = {"access_token": "tok", "calendar_email": "a@b.com",
                              "timezone": "UTC"}
    start = datetime(2026, 9, 1, 9, tzinfo=timezone.utc)
    mock_check.return_value = [{"start": start,
                                "end": datetime(2026, 9, 1, 9, 30, tzinfo=timezone.utc)}]
    mock_create.return_value = {"event_id": "evt123", "meeting_link": "https://meet.google.com/x"}

    result = _run(execute_book_meeting(
        {"start_time": start.isoformat(), "visitor_email": "jo@example.com",
         "visitor_name": "Jo"}, _ctx()))

    mock_create.assert_called_once()
    assert result["status"] == "ok"
    assert result["meeting_link"] == "https://meet.google.com/x"


@patch("app.services.chat.tools.get_google_calendar_connection")
@patch("app.services.chat.tools.gcal_check_availability")
@patch("app.services.chat.tools.gcal_create_event")
@patch("app.services.chat.tools.get_tool_settings")
def test_book_meeting_refuses_a_slot_that_is_no_longer_free(
        mock_settings, mock_create, mock_check, mock_conn):
    # The re-check right before booking finds no matching open slot -- e.g.
    # another visitor took it between the suggestion and this pick.
    mock_settings.return_value = _settings(provider="google_calendar")
    mock_conn.return_value = {"access_token": "tok", "calendar_email": "a@b.com",
                              "timezone": "UTC"}
    mock_check.return_value = []  # nothing free any more

    start = datetime(2026, 9, 1, 9, tzinfo=timezone.utc)
    result = _run(execute_book_meeting(
        {"start_time": start.isoformat(), "visitor_email": "jo@example.com",
         "visitor_name": "Jo"}, _ctx()))

    mock_create.assert_not_called()
    assert result["status"] == "error"


@patch("app.services.chat.tools.get_google_calendar_connection")
@patch("app.services.chat.tools.gcal_cancel_event")
@patch("app.services.chat.tools.get_tool_settings")
def test_cancel_meeting_routes_to_google_calendar(mock_settings, mock_cancel, mock_conn):
    mock_settings.return_value = _settings(provider="google_calendar")
    mock_conn.return_value = {"access_token": "tok", "calendar_email": "a@b.com",
                              "timezone": "UTC"}
    mock_cancel.return_value = True

    result = _run(execute_cancel_meeting({"visitor_email": "jo@example.com"}, _ctx()))

    mock_cancel.assert_called_once()
    assert result["status"] == "ok"


@patch("app.services.chat.tools.get_google_calendar_connection")
@patch("app.services.chat.tools.gcal_cancel_event")
@patch("app.services.chat.tools.get_tool_settings")
def test_cancel_meeting_reports_an_ambiguous_match_as_an_error(
        mock_settings, mock_cancel, mock_conn):
    mock_settings.return_value = _settings(provider="google_calendar")
    mock_conn.return_value = {"access_token": "tok", "calendar_email": "a@b.com",
                              "timezone": "UTC"}
    mock_cancel.return_value = False  # zero or multiple matches

    result = _run(execute_cancel_meeting({"visitor_email": "jo@example.com"}, _ctx()))

    assert result["status"] == "error"


def test_book_meeting_tool_names_are_registered():
    from app.services.chat.tools import TOOL_EXECUTORS, TOOL_SCHEMAS
    names = {t["function"]["name"] for t in TOOL_SCHEMAS}
    assert {"check_availability", "book_meeting", "cancel_meeting"} <= names
    assert {"check_availability", "book_meeting", "cancel_meeting"} <= set(TOOL_EXECUTORS)
```

- [ ] **Step 2: Run tests to verify they fail**

Run: `pytest tests/unit/test_book_meeting_tools.py -v`
Expected: FAIL with `ImportError: cannot import name 'execute_book_meeting'`

- [ ] **Step 3: Write the implementation**

In `app/services/chat/tools.py`:

1. Add the imports, alongside the existing `email_configured`/`get_mailchimp_connection`-style imports at the top of the file:

```python
from app.services.integrations.google_calendar import (
    cancel_event as gcal_cancel_event,
    check_availability as gcal_check_availability,
    create_event as gcal_create_event,
    get_google_calendar_connection,
)
from app.services.integrations.teams import (
    cancel_event as teams_cancel_event,
    check_availability as teams_check_availability,
    create_event as teams_create_event,
    get_teams_connection,
)
```

2. Add three entries to `TOOL_SCHEMAS` (append near the end, before the closing `]`):

```python
    {
        "type": "function",
        "function": {
            "name": "check_availability",
            "description": (
                "Check what meeting times are open. Use when the user asks to book a "
                "call/meeting/demo and you need to offer them real times -- never invent "
                "times without calling this first. "
                "`date_range` is a natural description of when they want to meet (e.g. "
                "'tomorrow afternoon', 'next Tuesday', 'this week') -- pass it through "
                "roughly as they said it. "
                "Relay at most 2-3 of the returned slots in chat, in the business's own "
                "local time -- do not read out every slot returned. "
                "If this returns no slots or an error, tell the user you could not find an "
                "open time and offer a ticket/human contact instead of inventing one."
            ),
            "parameters": {
                "type": "object",
                "properties": {
                    "date_range": {
                        "type": "string",
                        "description": "The time window to search, as the user described it.",
                    },
                },
                "required": ["date_range"],
            },
        },
    },
    {
        "type": "function",
        "function": {
            "name": "book_meeting",
            "description": (
                "Book a meeting at a specific time. Only call this after check_availability "
                "has shown this exact time as open and the user has picked it -- never call "
                "this on a time you have not already confirmed is free. "
                "This books immediately; there is no separate confirmation step after this "
                "call succeeds, so only call it once the user has clearly picked a time, not "
                "while they are still considering options. "
                "You must have the visitor's email and name before calling -- ask for "
                "whichever is missing first. "
                "If this returns an error because the slot was taken in the meantime (someone "
                "else booked it first), tell the user and offer to check availability again -- "
                "never silently pick a different time yourself."
            ),
            "parameters": {
                "type": "object",
                "properties": {
                    "start_time": {
                        "type": "string",
                        "description": "ISO 8601 datetime for the meeting start, exactly as "
                                       "returned by check_availability.",
                    },
                    "visitor_email": {
                        "type": "string",
                        "description": "The visitor's email address, as they gave it.",
                    },
                    "visitor_name": {
                        "type": "string",
                        "description": "The visitor's name, as they gave it.",
                    },
                },
                "required": ["start_time", "visitor_email", "visitor_name"],
            },
        },
    },
    {
        "type": "function",
        "function": {
            "name": "cancel_meeting",
            "description": (
                "Cancel a previously booked meeting. Ask for the visitor's email, and if they "
                "don't recall the exact time, roughly when it was (e.g. 'around 2pm ' or "
                "'sometime tomorrow') -- the lookup searches by these, not by an exact ID, so "
                "the more specific they are the more reliably it finds the right one. "
                "If this reports it could not find exactly one matching meeting, tell the "
                "user and ask for more detail (a narrower time) rather than trying again with "
                "the same information."
            ),
            "parameters": {
                "type": "object",
                "properties": {
                    "visitor_email": {
                        "type": "string",
                        "description": "The email address the meeting was booked under.",
                    },
                    "approximate_time": {
                        "type": "string",
                        "description": "ISO 8601 datetime near when the meeting was, if the "
                                       "user recalls it. Omit if they don't know.",
                    },
                },
                "required": ["visitor_email"],
            },
        },
    },
```

3. Add a shared provider-resolution helper and the three executors (place near `execute_send_email`):

```python
def _book_meeting_connection(tenant_id: str):
    """The active meeting provider's connection, or (None, error_dict) if
    the feature is off, nothing is selected, or the selected provider has
    no active connection. Mirrors execute_send_email's guard shape."""
    settings = get_tool_settings(tenant_id)
    if not settings["features"].get("book_meeting"):
        return None, None, {"status": "error",
                            "detail": "Meeting booking is turned off for this business."}

    provider = settings["providers"].get("book_meeting")
    if provider not in ("google_calendar", "teams"):
        return None, None, {"status": "error",
                            "detail": "No meeting provider is connected for this business."}

    connection = (get_google_calendar_connection(tenant_id) if provider == "google_calendar"
                 else get_teams_connection(tenant_id))
    if not connection:
        return None, None, {"status": "error",
                            "detail": "The connected meeting provider is unavailable right now."}

    return provider, connection, None


async def execute_check_availability(args, ctx):
    """Reads free slots from whichever meeting provider this tenant has active."""
    provider, connection, error = _book_meeting_connection(ctx["tenant_id"])
    if error:
        return error

    now = datetime.now(timezone.utc)
    window_end = now + timedelta(days=7)  # a one-week search window
    check_fn = gcal_check_availability if provider == "google_calendar" else teams_check_availability
    slots = check_fn(connection, now, window_end)

    return {"status": "ok", "timezone": connection["timezone"],
           "slots": [{"start": s["start"].isoformat(), "end": s["end"].isoformat()}
                     for s in slots]}


async def execute_book_meeting(args, ctx):
    """Books a slot immediately, after re-confirming it is still free."""
    provider, connection, error = _book_meeting_connection(ctx["tenant_id"])
    if error:
        return error

    try:
        start = datetime.fromisoformat(args["start_time"])
    except (KeyError, ValueError):
        return {"status": "error", "detail": "start_time is missing or not a valid datetime."}
    end = start + timedelta(minutes=30)

    check_fn = gcal_check_availability if provider == "google_calendar" else teams_check_availability
    still_free = any(s["start"] == start for s in check_fn(connection, start, end + timedelta(minutes=1)))
    if not still_free:
        return {"status": "error",
               "detail": "That time is no longer available. Offer to check availability again."}

    create_fn = gcal_create_event if provider == "google_calendar" else teams_create_event
    booking = create_fn(connection, start, end, args.get("visitor_email", ""),
                        args.get("visitor_name", ""))
    if not booking:
        return {"status": "error", "detail": "Could not book that meeting right now."}

    return {"status": "ok", "event_id": booking["event_id"],
           "meeting_link": booking.get("meeting_link")}


async def execute_cancel_meeting(args, ctx):
    """Finds and cancels a previously booked meeting."""
    provider, connection, error = _book_meeting_connection(ctx["tenant_id"])
    if error:
        return error

    near_time = None
    if args.get("approximate_time"):
        try:
            near_time = datetime.fromisoformat(args["approximate_time"])
        except ValueError:
            near_time = None

    cancel_fn = gcal_cancel_event if provider == "google_calendar" else teams_cancel_event
    cancelled = cancel_fn(connection, args.get("visitor_email", ""), near_time=near_time)
    if not cancelled:
        return {"status": "error",
               "detail": "Could not find exactly one matching meeting to cancel."}

    return {"status": "ok"}
```

4. Register the three executors in `TOOL_EXECUTORS`:

```python
TOOL_EXECUTORS = {
    "search_knowledge_base": execute_search_knowledge_base,
    "create_or_update_ticket": execute_create_or_update_ticket,
    "submit_feedback": execute_submit_feedback,
    "request_human_takeover": execute_request_human_takeover,
    "send_email": execute_send_email,
    "attach_resources": execute_attach_resources,
    "send_document": execute_send_document,
    "recommend_products": execute_recommend_products,
    "check_availability": execute_check_availability,
    "book_meeting": execute_book_meeting,
    "cancel_meeting": execute_cancel_meeting,
}
```

5. Confirm `datetime`, `timedelta`, and `timezone` are already imported at the top of `tools.py` (check `from datetime import ...`); add them to the existing import line if not already present.

- [ ] **Step 4: Run tests to verify they pass**

Run: `pytest tests/unit/test_book_meeting_tools.py -v`
Expected: PASS (10 tests)

- [ ] **Step 5: Run the full existing chat-tools suite to confirm nothing else broke**

Run: `pytest tests/unit/test_chat_tools.py -v`
Expected: PASS (all pre-existing tests still green — the new imports/tools must not change any existing tool's behavior)

- [ ] **Step 6: Commit**

```bash
git add app/services/chat/tools.py tests/unit/test_book_meeting_tools.py
git commit -m "feat: check_availability, book_meeting, and cancel_meeting chat tools"
```

---

## Final integration check (after all 6 tasks)

- [ ] Run the full test suite: `pytest tests/ -q`. Expected: all pass, no regressions in `test_chat_tools.py`, `test_provider_settings.py`, or anywhere else.
- [ ] Grep the diff for the literal strings `'google-calendar'` and `'teams'` used as DB `provider` values — confirm every query uses these exact strings, matching what the other service already writes (a typo here means "never finds the connection" with no error, since a 0-row `SELECT` and "not connected" look identical).
- [ ] Confirm `GOOGLE_CALENDAR_CLIENT_ID`/`GOOGLE_CALENDAR_CLIENT_SECRET`/`MS_TEAMS_CLIENT_ID`/`MS_TEAMS_CLIENT_SECRET` exist in `app/core/config.py` (added in the Prerequisite section above) but are **not** set in any deployed `.env` until the real values are obtained — deploying with blank client credentials means token refresh always fails, which degrades to "provider treated as disconnected," not a crash.
- [ ] Do not deploy the settings-gate change (Task 5) to a server until Tasks 1-4 are deployed alongside it — the gate calls `get_google_calendar_connection`/`get_teams_connection`, which must exist or the import fails at startup.

## Live integration test (after all 6 tasks, and after real OAuth client credentials are set)

Everything above is unit-tested only, with every HTTP call to Google/Microsoft mocked — nothing has exercised a real Google Calendar or Microsoft Teams account yet. Before this feature is considered done, run one real end-to-end pass:

- [ ] Confirm `GOOGLE_CALENDAR_CLIENT_ID`/`GOOGLE_CALENDAR_CLIENT_SECRET` are set to real values in the target environment's `.env` (see the Prerequisite section — these come from whoever owns the existing OAuth connect flow).
- [ ] Use tenant `org_75de1ced-2eb1-40a7-8b22-37e794734a14` — it already has an `ACTIVE` Google Calendar connection in staging (connected by Krish Bhanderi, `krish@galaxiq.ai`, confirmed directly in the `galaxiq_tenants` database). Do not use a different tenant for this check without first confirming its connection is still `ACTIVE` — a revoked or expired-refresh-token row will just report "not connected" and looks identical to a real code bug from the outside.
- [ ] Call `get_google_calendar_connection("org_75de1ced-2eb1-40a7-8b22-37e794734a14")` directly (a throwaway script or a REPL is fine) and confirm it returns a real connection dict with a genuine `timezone` value (not the `"UTC"` fallback, unless that tenant's calendar is genuinely set to UTC) — this exercises the real token-refresh path for the first time.
- [ ] Call `check_availability` against that connection for a real near-future date range and confirm the returned slots look sane against what that Google Calendar actually shows as free/busy in the real Google Calendar UI.
- [ ] Call `book_meeting` (via the chat tool, or `create_event` directly) for one test slot with a real, checkable visitor email, and confirm: (a) a real event appears on the connected Google Calendar, (b) the visitor's email actually receives a calendar invite, (c) a Meet link is attached.
- [ ] Call `cancel_meeting` (or `cancel_event` directly) for that same booking and confirm the event is actually removed from the calendar.
- [ ] Repeat the same sequence for Microsoft Teams using tenant `org_ab0cf942-a4fc-4095-8f2c-bae6338c09c2` — it has both Google Calendar and Teams `ACTIVE` (login `sam41@yopmail.com`, Organisation name "Sam", confirmed directly in the `galaxiq_tenants`/`galaxiq_master` databases). Do not assume Teams works just because Google Calendar did; they are separate implementations against separate APIs, and the final whole-branch review found real Teams-specific bugs (timezone handling using `strftime()` instead of `isoformat()`, and `cancel_event`'s `near_time`/`$filter` construction likely not working against Microsoft Graph as written) that this live test is specifically what will confirm or refute.

### Partial live test already completed (2026-08-25, on `4.188.84.64`)

- [x] Confirmed the connection-check pipeline runs against the real Google OAuth endpoint end to end: selecting `google_calendar` as the provider for `org_ab0cf942-a4fc-4095-8f2c-bae6338c09c2` correctly failed with a genuine `400 Bad Request` from `oauth2.googleapis.com` (token refresh attempted with the still-empty `GOOGLE_CALENDAR_CLIENT_ID`/`_SECRET` placeholders) — the failure path itself is proven correct, just blocked on real client credentials.
- [x] Sent a real chat message to the deployed `/chat` endpoint ("can I book a meeting with your team tomorrow afternoon?") for that tenant. The AI correctly recognized the booking intent, called `check_availability` (confirmed via the response's `tools` list), and gracefully degraded to offering a human follow-up when the tool reported no provider connected — no crash, no hallucinated slots, no leaked internal error text.
- [ ] Still blocked: everything below this line, which needs real `GOOGLE_CALENDAR_CLIENT_ID`/`_SECRET` and `MS_TEAMS_CLIENT_ID`/`_SECRET` before a provider can actually be selected and real Calendar/Teams API responses observed.