# Auto-Close Stale Tickets — Plan

## Goal
Automatically close tickets that have been open a long time and show no recent
activity (no recent sessions), so the open-ticket queue reflects work that is
actually live. The mechanism must be conservative, auditable, dry-runnable, and
safe to run unattended on a schedule.

## What "stale" means
A ticket is a candidate for auto-close when **all** of these hold:

- `is_closed = 0` — still open
- `is_deleted = 0` — not deleted
- `in_session = 0` — not currently in an active session (someone working it now)
- No **recent session**: the last completed session ended more than
  `SESSION_STALE_DAYS` ago (default **60**)
- The ticket itself is old: `created_at` older than `MIN_AGE_DAYS` (default
  **90**) — avoids closing brand-new tickets that simply haven't had a session yet

We already cache the signal we need on the ticket row, so this is a cheap query:

- `last_session_at` — populated by `ticket_update_session_cache()`
  (see [functions_tickets.inc.php:284](../code/functions_tickets.inc.php)).
  `'0000-00-00 00:00:00'` means the ticket never had a completed session.

### The "never had a session" case
Tickets with `last_session_at = '0000-00-00 00:00:00'` have had no session at
all. These are the most likely to be genuinely abandoned, but also the most
likely to be false positives (created and forgotten, or worked entirely over
chat). Treat them by falling back to `created_at`: close only if
`created_at` is older than `SESSION_STALE_DAYS`. Make this behavior gated by a
flag (`--include-no-session`) so the first production runs can exclude them
until we trust the results.

### Candidate query (concept)
```sql
SELECT pk_ticket, fk_system, created_at, last_session_at, message_brief
FROM tickets
WHERE is_closed = 0
  AND is_deleted = 0
  AND in_session = 0
  AND created_at < (NOW() - INTERVAL :min_age_days DAY)
  AND (
        (last_session_at > '2000-01-01' AND last_session_at < (NOW() - INTERVAL :stale_days DAY))
     OR (last_session_at <= '2000-01-01' AND :include_no_session = 1
         AND created_at < (NOW() - INTERVAL :stale_days DAY))
      )
```
Note: `last_session_at` is a cached column and is not in `docs/schema.sql` yet —
it is added/maintained by the session-cache functions. The script should treat a
missing/zero value defensively rather than assuming freshness.

## How closing works today
Manual close (see [ticketDoClose.php](../backend/ticketDoClose.php)) sets:

```php
$SITE['db']->set_item("tickets", [
  'fk_ticket_status' => $SITE['ts']['closed'],   // status id 4
  'is_closed'        => 1,
  'closed_at'        => strftime("%Y-%m-%d %H:%M:%S"),
], ['pk_ticket' => $pk_ticket]);
```

The auto-close script must produce the **same** end state so an auto-closed
ticket is indistinguishable from a manually closed one to the rest of the app.
`$SITE['ts']['closed']` is defined in
[site.inc.php:293](../code/site.inc.php).

## Script design
Model the script on the existing one-shot,
[scripts/backfill_ticket_session_cache.php](../scripts/backfill_ticket_session_cache.php):
same bootstrap, same CLI-from-project-root pattern.

**File:** `scripts/auto_close_stale_tickets.php`

### Bootstrap (copied from the backfill script)
```php
chdir(__DIR__ . "/..");
define("CLI_SCRIPT", true);
require('vendor/autoload.php');
include("code/site.inc.php");
include("code/cw_database.inc.php");
include("code/functions_tickets.inc.php");
$SITE['db'] = new CWDatabase($SITE['sqlhost'], $SITE['sqluser'], $SITE['sqlpass'], $SITE['sqldb'], true);
```

### CLI flags
- `--dry-run` (default ON) — report what *would* close, change nothing. Require
  an explicit `--commit` (or `--no-dry-run`) to actually write.
- `--stale-days=N` — session staleness threshold (default 60)
- `--min-age-days=N` — minimum ticket age (default 90)
- `--include-no-session` — also close tickets that never had a session
- `--limit=N` — cap number closed per run (safety valve, default e.g. 500)
- `--system=ID` — restrict to one `fk_system` (for staged rollout)

### Main loop
1. Select candidates using the criteria above.
2. For each candidate:
   - Re-check the live row inside the loop (guard against races — someone may
     have opened a session since the query).
   - **Dry-run:** print `pk_ticket`, system, age, `last_session_at`,
     `message_brief`; increment a counter.
   - **Commit:** apply the same `set_item` update as `ticketDoClose.php`, then
     record an audit message (below).
3. Print a summary: scanned, eligible, closed, skipped (with reasons).

### Audit trail
Do **not** close silently. For each auto-closed ticket, record a message so the
history shows why it closed, using the existing helper
[`ticket_record_message()`](../code/functions_tickets.inc.php):

```php
ticket_record_message(
  $pk_ticket,
  0,                                       // system/bot user id — see below
  $SITE['ticket_message_types']['action'], // type 4 = "action"
  "Auto-closed: no session activity in {$stale_days} days."
);
```
Decide on a dedicated system/bot `fk_user` for the actor. `0` works but a real
"System" user row is cleaner for reporting. Confirm before hardcoding an id.

### Idempotency & safety
- Dry-run is the default; committing requires an explicit flag.
- `--limit` caps blast radius per run.
- The `is_closed = 0` filter makes re-runs naturally idempotent (a closed ticket
  won't be re-selected).
- Log every closed `pk_ticket` to stdout (captured by cron) so a bad run can be
  reversed by id.

## Edge cases to decide before shipping
- **Close-requested tickets** (`is_close_requested = 1`): probably close these
  eagerly, or handle in a separate pass — confirm.
- **Tickets with a future `reminder_date`**: someone deliberately deferred them;
  exclude from auto-close.
- **Hold status** (`$SITE['ts']['hold']` = 9): a ticket parked on hold may be
  intentional. Recommend excluding held tickets, or treating them with a longer
  threshold.
- **Per-system status ids:** `ticket_statuses.is_closed` is per `fk_system`.
  Status id 4 is the global default closed status; verify every active system
  uses it, or resolve the closed status per system rather than hardcoding.
- **Reopen behavior:** what happens if a customer replies to an auto-closed
  ticket? Confirm the existing reopen path handles auto-closed tickets the same
  as manually closed ones (it should, since the end state is identical).

## Rollout
1. **Dry-run in dev** against a DB copy; eyeball the candidate list for
   surprises.
2. **Dry-run in prod** (read-only) and hand the list to whoever owns the queue
   for a sanity check.
3. **First commit run** narrowed with `--system` and a small `--limit`, no
   `--include-no-session`.
4. **Widen** thresholds/scope once the closed tickets look right.
5. **Schedule** via cron once trusted, e.g. nightly off-hours:
   ```
   0 3 * * *  cd /var/www/connect && php scripts/auto_close_stale_tickets.php --commit >> /var/log/connect/auto_close.log 2>&1
   ```

## Open questions for Charles
- Confirm thresholds: 60-day session staleness, 90-day minimum age — right for
  your queue?
- Should held / reminder-deferred tickets be exempt?
- Which `fk_user` should own the auto-close audit message (bot user vs. `0`)?
- Notify anyone (assigned tech / customer) on auto-close, or close silently with
  just the audit message?