"""sentence completion: a per-user index of phrases from their sent mail

``completion_phrases`` maps the words before the cursor (normalized) to what
the user usually writes next, with how often; rebuilt from the sent mail at
most daily (app/completion.py). The primary key leads with (user_id, context),
which is the lookup.

Revision ID: 0029
Revises: 0028
Create Date: 2026-10-18
"""

from __future__ import annotations

from collections.abc import Sequence

from alembic import op

revision: str = "0029"
down_revision: str | None = "0028"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE completion_phrases (
          user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          context text NOT NULL,
          continuation text NOT NULL,
          count integer NOT NULL,
          PRIMARY KEY (user_id, context, continuation)
        )
        """
    )
    op.execute(
        """
        CREATE TABLE completion_index_state (
          user_id uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
          built_at timestamptz NOT NULL
        )
        """
    )


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS completion_index_state")
    op.execute("DROP TABLE IF EXISTS completion_phrases")