Email Surveillance

def topic_status(topic, emails, today):
    """For one tracked topic, return latest send + response state."""
    topic_emails = filter_by_subject_pattern(emails, topic.patterns)
    if not topic_emails:
        return {"status": "Never sent", "last_sent": None, "follow_up_due": False}

    latest = max(topic_emails, key=lambda e: e["sent_date"])
    days_since = (today - latest["sent_date"]).days

    response = find_response_in_thread(latest["conversation_id"])
    follow_up_due = (response is None and days_since > topic.response_window_days)

    return {
        "status":          "Awaiting response" if not response else "Responded",
        "last_sent":       latest["sent_date"],
        "days_since":      days_since,
        "follow_up_due":   follow_up_due,
        "subject":         latest["subject"],
        "preview":         latest["body_preview"],
    }

-- Email metadata
CREATE TABLE emails (
    id              TEXT PRIMARY KEY,         -- Outlook conversation-message id
    conversation_id TEXT,                     -- Thread anchor
    sender          TEXT,
    recipients      TEXT,                     -- JSON array
    cc              TEXT,                     -- JSON array
    subject         TEXT,
    sent_date       TEXT,
    received_date   TEXT,
    body_html       TEXT,
    body_text       TEXT,
    folder          TEXT,
    labels          TEXT,                     -- JSON array
    has_attachments INTEGER
);

CREATE INDEX idx_conversation ON emails(conversation_id);
CREATE INDEX idx_sender       ON emails(sender);
CREATE INDEX idx_sent_date    ON emails(sent_date);
CREATE INDEX idx_subject      ON emails(subject);

-- Attachments stored on disk; this table indexes them
CREATE TABLE attachments (
    id        TEXT PRIMARY KEY,
    email_id  TEXT REFERENCES emails(id),
    filename  TEXT,
    size      INTEGER,
    path      TEXT,                            -- file system path
    mime_type TEXT
);

-- Sync bookkeeping
CREATE TABLE sync_state (
    folder       TEXT PRIMARY KEY,
    last_sync_at TEXT,
    delta_link   TEXT                          -- Graph API delta token
);