BEGIN IMMEDIATE;

CREATE TABLE audio_transcript_pieces (
    recording_id TEXT NOT NULL REFERENCES audio_recordings(id) ON DELETE CASCADE,
    attempt INTEGER NOT NULL CHECK(attempt > 0),
    piece_index INTEGER NOT NULL CHECK(piece_index >= 0),
    piece_count INTEGER NOT NULL CHECK(piece_count > 0 AND piece_index < piece_count),
    audio_start_ms INTEGER NOT NULL CHECK(audio_start_ms >= 0),
    audio_end_ms INTEGER NOT NULL CHECK(audio_end_ms > audio_start_ms),
    transcript_json TEXT NOT NULL,
    created_at TEXT NOT NULL,
    PRIMARY KEY(recording_id, attempt, piece_index)
);

CREATE INDEX audio_transcript_pieces_recording
ON audio_transcript_pieces(recording_id, attempt, piece_index);

PRAGMA user_version = 7;

COMMIT;
