"""Deletable private audio payloads, separate from append-only research events."""
SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS raw_audio_consents (
    audio_consent_id TEXT PRIMARY KEY,
    participant_id TEXT NOT NULL,
    consent_id TEXT NOT NULL REFERENCES consents(consent_id),
    app_id TEXT NOT NULL,
    policy_sha256 TEXT NOT NULL,
    policy_json TEXT NOT NULL,
    retention_days INTEGER NOT NULL,
    created_at TEXT NOT NULL,
    revoked_at TEXT
);
CREATE TABLE IF NOT EXISTS raw_audio_recordings (
    audio_id TEXT PRIMARY KEY,
    participant_id TEXT NOT NULL,
    audio_consent_id TEXT NOT NULL REFERENCES raw_audio_consents(audio_consent_id),
    game_id TEXT NOT NULL,
    media_type TEXT NOT NULL,
    nonce BLOB,
    ciphertext BLOB,
    created_at TEXT NOT NULL,
    expires_at TEXT NOT NULL,
    erased_at TEXT,
    erasure_reason TEXT
);
CREATE INDEX IF NOT EXISTS raw_audio_participant ON raw_audio_recordings(participant_id);
CREATE INDEX IF NOT EXISTS raw_audio_expiry ON raw_audio_recordings(expires_at);
CREATE TABLE IF NOT EXISTS raw_audio_sessions (
    audio_session_id TEXT PRIMARY KEY,
    participant_id TEXT NOT NULL,
    audio_consent_id TEXT NOT NULL REFERENCES raw_audio_consents(audio_consent_id),
    game_id TEXT NOT NULL,
    confirmation_json TEXT NOT NULL,
    created_at TEXT NOT NULL,
    expires_at TEXT NOT NULL,
    ended_at TEXT
);
CREATE INDEX IF NOT EXISTS raw_audio_session_owner ON raw_audio_sessions(participant_id);
CREATE TABLE IF NOT EXISTS raw_audio_recording_sessions (
    audio_id TEXT PRIMARY KEY REFERENCES raw_audio_recordings(audio_id),
    audio_session_id TEXT NOT NULL REFERENCES raw_audio_sessions(audio_session_id)
);
"""


def erase_participant_audio(connection, participant_id, when):
    # Same transaction as withdrawal: concurrent uploads either precede erasure
    # or observe the canonical participant tombstone and cannot commit.
    connection.execute(
        "UPDATE raw_audio_sessions SET ended_at = COALESCE(ended_at, ?) WHERE participant_id = ?",
        (when, participant_id),
    )
    connection.execute(
        "UPDATE raw_audio_consents SET revoked_at = COALESCE(revoked_at, ?) WHERE participant_id = ?",
        (when, participant_id),
    )
    connection.execute(
        "UPDATE raw_audio_recordings SET ciphertext = NULL, nonce = NULL, "
        "erased_at = ?, erasure_reason = 'withdrawal' WHERE participant_id = ? AND erased_at IS NULL",
        (when, participant_id),
    )