app/db/base.py - master - artyuhovs/AIFeedBackTrainingBot

app/db/base.py
534 строки21 KB
Codex
Завершить архитектурное оздоровление и исправить итоги
15 июл 2026, 11:05
15 июл 2026, 11:05c65f9a6
SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS schema_migrations (
    name TEXT PRIMARY KEY,
    applied_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS audit_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_type TEXT NOT NULL,
    actor_user_id INTEGER,
    source_user_id INTEGER,
    target_user_id INTEGER,
    result TEXT NOT NULL,
    release TEXT NOT NULL,
    details_json TEXT NOT NULL DEFAULT '{}',
    created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_audit_events_type_created
ON audit_events(event_type, created_at);

CREATE TABLE IF NOT EXISTS runtime_heartbeats (
    component TEXT PRIMARY KEY,
    status TEXT NOT NULL CHECK(status IN ('running', 'ok', 'failed')),
    last_started_at TEXT NOT NULL,
    last_success_at TEXT,
    last_error TEXT,
    release TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS outbound_jobs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    kind TEXT NOT NULL,
    aggregate_id TEXT NOT NULL,
    recipient_id INTEGER NOT NULL,
    payload_json TEXT NOT NULL DEFAULT '{}',
    scheduled_at TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK(status IN ('pending', 'leased', 'retry', 'sent', 'dead', 'uncertain', 'cancelled')),
    attempts INTEGER NOT NULL DEFAULT 0,
    next_attempt_at TEXT,
    lease_until TEXT,
    leased_by TEXT,
    idempotency_key TEXT NOT NULL UNIQUE,
    last_error TEXT,
    sent_message_id INTEGER,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_outbound_jobs_due
ON outbound_jobs(status, next_attempt_at, scheduled_at);

CREATE TABLE IF NOT EXISTS processing_jobs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message_id INTEGER NOT NULL UNIQUE,
    status TEXT NOT NULL DEFAULT 'queued'
        CHECK(status IN ('queued', 'running', 'retry', 'dead', 'completed')),
    attempts INTEGER NOT NULL DEFAULT 0,
    next_attempt_at TEXT,
    lease_until TEXT,
    last_error TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY(message_id) REFERENCES messages(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_processing_jobs_due
ON processing_jobs(status, next_attempt_at, lease_until);

CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stream_id INTEGER,
    participant_assignment_id INTEGER,
    submission_kind TEXT NOT NULL DEFAULT 'original' CHECK(submission_kind IN ('original', 'correction')),
    telegram_user_id INTEGER NOT NULL,
    telegram_chat_id INTEGER NOT NULL,
    telegram_message_id INTEGER NOT NULL,
    telegram_received_at TEXT,
    input_intent_id INTEGER,
    telegram_file_id TEXT NOT NULL,
    telegram_file_unique_id TEXT,
    media_type TEXT NOT NULL,
    original_file_name TEXT,
    mime_type TEXT,
    file_size_bytes INTEGER,
    status TEXT NOT NULL DEFAULT 'received',
    error_code TEXT,
    error_message TEXT,
    original_file_path TEXT,
    audio_file_path TEXT,
    duration_sec REAL,
    audio_size_bytes INTEGER,
    transcript_text TEXT,
    transcript_sha256 TEXT,
    transcript_file_path TEXT,
    salutespeech_raw_response_path TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    downloaded_at TEXT,
    audio_prepared_at TEXT,
    transcribed_at TEXT,
    failed_at TEXT
);

CREATE TABLE IF NOT EXISTS bot_users (
    telegram_user_id INTEGER PRIMARY KEY,
    identity_kind TEXT NOT NULL DEFAULT 'telegram'
        CHECK(
            (identity_kind='telegram' AND telegram_user_id>0)
            OR (identity_kind IN ('placeholder', 'synthetic') AND telegram_user_id<0)
        ),
    username TEXT NOT NULL DEFAULT '',
    first_name TEXT NOT NULL DEFAULT '',
    last_name TEXT NOT NULL DEFAULT '',
    is_admin INTEGER NOT NULL DEFAULT 0,
    current_stream_id INTEGER,
    current_assignment_id INTEGER,
    current_assignment_kind TEXT NOT NULL DEFAULT 'original'
        CHECK(current_assignment_kind IN ('original', 'correction')),
    free_training_pending INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY(current_stream_id) REFERENCES streams(id) ON DELETE SET NULL,
    FOREIGN KEY(current_assignment_id) REFERENCES participant_assignments(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS input_intents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    telegram_user_id INTEGER NOT NULL,
    stream_id INTEGER,
    kind TEXT NOT NULL CHECK(kind IN (
        'practice', 'course_original', 'course_correction',
        'peer_review_comment', 'review_quality_comment'
    )),
    subject_id INTEGER,
    payload_json TEXT NOT NULL DEFAULT '{}',
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK(status IN ('pending', 'reserved', 'consumed', 'cancelled')),
    reserved_by_message_id INTEGER,
    reserved_by_telegram_chat_id INTEGER,
    reserved_by_telegram_message_id INTEGER,
    received_at TEXT,
    expires_at TEXT,
    version INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY(telegram_user_id) REFERENCES bot_users(telegram_user_id) ON DELETE CASCADE,
    FOREIGN KEY(stream_id) REFERENCES streams(id) ON DELETE CASCADE,
    FOREIGN KEY(reserved_by_message_id) REFERENCES messages(id) ON DELETE SET NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_input_intents_one_pending_per_user
ON input_intents(telegram_user_id) WHERE status='pending';

CREATE INDEX IF NOT EXISTS idx_input_intents_reserved_message
ON input_intents(reserved_by_message_id) WHERE status='reserved';

CREATE TABLE IF NOT EXISTS streams (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    description TEXT NOT NULL DEFAULT '',
    is_active INTEGER NOT NULL DEFAULT 1,
    course_version_id INTEGER,
    starts_at TEXT,
    timezone TEXT NOT NULL DEFAULT 'Europe/Moscow',
    ends_at TEXT,
    status TEXT NOT NULL DEFAULT 'draft' CHECK(status IN ('draft', 'active', 'paused', 'completed')),
    telegram_chat_id INTEGER,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY(course_version_id) REFERENCES course_versions(id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS stream_memberships (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stream_id INTEGER NOT NULL,
    telegram_user_id INTEGER NOT NULL,
    role TEXT NOT NULL DEFAULT 'learner' CHECK(role IN ('learner', 'curator')),
    status TEXT NOT NULL CHECK(status IN ('pending', 'approved', 'denied')),
    requested_at TEXT NOT NULL,
    decided_at TEXT,
    decided_by_user_id INTEGER,
    updated_at TEXT NOT NULL,
    UNIQUE(stream_id, telegram_user_id),
    FOREIGN KEY(stream_id) REFERENCES streams(id) ON DELETE CASCADE,
    FOREIGN KEY(telegram_user_id) REFERENCES bot_users(telegram_user_id) ON DELETE CASCADE,
    FOREIGN KEY(decided_by_user_id) REFERENCES bot_users(telegram_user_id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS stream_membership_roles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    membership_id INTEGER NOT NULL,
    role TEXT NOT NULL CHECK(role IN ('learner', 'curator')),
    created_at TEXT NOT NULL,
    UNIQUE(membership_id, role),
    FOREIGN KEY(membership_id) REFERENCES stream_memberships(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_messages_user_id ON messages(telegram_user_id);
CREATE INDEX IF NOT EXISTS idx_messages_chat_id ON messages(telegram_chat_id);
CREATE INDEX IF NOT EXISTS idx_messages_status ON messages(status);
CREATE INDEX IF NOT EXISTS idx_bot_users_username ON bot_users(username);
CREATE INDEX IF NOT EXISTS idx_bot_users_admin ON bot_users(is_admin);
CREATE INDEX IF NOT EXISTS idx_stream_memberships_stream_status ON stream_memberships(stream_id, status);
CREATE INDEX IF NOT EXISTS idx_stream_memberships_user_status ON stream_memberships(telegram_user_id, status);
CREATE INDEX IF NOT EXISTS idx_stream_membership_roles_role ON stream_membership_roles(role, membership_id);

CREATE TABLE IF NOT EXISTS courses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS course_versions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    course_id INTEGER NOT NULL,
    version_number INTEGER NOT NULL CHECK(version_number > 0),
    status TEXT NOT NULL CHECK(status IN ('draft', 'published')),
    reminder_offsets_minutes TEXT NOT NULL DEFAULT '[50, 80]',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    published_at TEXT,
    UNIQUE(course_id, version_number),
    FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS course_topics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    course_version_id INTEGER NOT NULL,
    position INTEGER NOT NULL CHECK(position >= 0),
    title TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    UNIQUE(course_version_id, position),
    FOREIGN KEY(course_version_id) REFERENCES course_versions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS course_publications (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    course_topic_id INTEGER NOT NULL,
    position INTEGER NOT NULL CHECK(position >= 0),
    publication_type TEXT NOT NULL,
    content_text TEXT NOT NULL,
    delivery_channel TEXT NOT NULL CHECK(delivery_channel IN ('private', 'group', 'both')),
    publish_offset_minutes INTEGER NOT NULL CHECK(publish_offset_minutes >= 0),
    deadline_offset_minutes INTEGER,
    response_format TEXT,
    reminder_offsets_minutes TEXT,
    crowd_training_enabled INTEGER NOT NULL DEFAULT 0,
    required_reviews INTEGER NOT NULL DEFAULT 3,
    assigned_reviews INTEGER NOT NULL DEFAULT 4,
    review_criteria_json TEXT,
    reference_answer TEXT,
    review_duration_minutes INTEGER NOT NULL DEFAULT 120 CHECK(review_duration_minutes > 0),
    review_reminder_offsets_minutes TEXT NOT NULL DEFAULT '[50, 80]',
    review_quality_duration_minutes INTEGER NOT NULL DEFAULT 120 CHECK(review_quality_duration_minutes > 0),
    review_quality_reminder_offsets_minutes TEXT NOT NULL DEFAULT '[50, 80]',
    correction_duration_minutes INTEGER NOT NULL DEFAULT 1440 CHECK(correction_duration_minutes > 0),
    correction_reminder_offsets_minutes TEXT NOT NULL DEFAULT '[50, 80]',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    UNIQUE(course_topic_id, position),
    CHECK(deadline_offset_minutes IS NULL OR deadline_offset_minutes > publish_offset_minutes),
    CHECK(deadline_offset_minutes IS NULL OR length(trim(response_format)) > 0),
    CHECK(assigned_reviews >= required_reviews),
    FOREIGN KEY(course_topic_id) REFERENCES course_topics(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_course_versions_course_status ON course_versions(course_id, status);
CREATE INDEX IF NOT EXISTS idx_course_topics_version_position ON course_topics(course_version_id, position);
CREATE INDEX IF NOT EXISTS idx_course_publications_topic_position ON course_publications(course_topic_id, position);
CREATE INDEX IF NOT EXISTS idx_course_publications_publish_offset ON course_publications(publish_offset_minutes);

CREATE TABLE IF NOT EXISTS stream_publications (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stream_id INTEGER NOT NULL,
    course_publication_id INTEGER NOT NULL,
    scheduled_at TEXT NOT NULL,
    deadline_at TEXT,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'sent', 'skipped', 'failed')),
    telegram_message_id INTEGER,
    sent_at TEXT,
    error_message TEXT,
    crowd_distributed_at TEXT,
    review_deadline_at TEXT,
    review_closed_at TEXT,
    review_quality_deadline_at TEXT,
    review_quality_closed_at TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    UNIQUE(stream_id, course_publication_id),
    FOREIGN KEY(stream_id) REFERENCES streams(id) ON DELETE CASCADE,
    FOREIGN KEY(course_publication_id) REFERENCES course_publications(id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS participant_assignments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stream_publication_id INTEGER NOT NULL,
    telegram_user_id INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'not_started'
        CHECK(status IN ('not_started', 'submitted', 'completed', 'late', 'excused')),
    submitted_at TEXT,
    completed_at TEXT,
    score REAL,
    max_score REAL,
    work_score REAL,
    review_score REAL,
    result_notification_status TEXT NOT NULL DEFAULT 'pending'
        CHECK(result_notification_status IN ('pending', 'sent', 'failed', 'skipped')),
    result_notification_attempts INTEGER NOT NULL DEFAULT 0,
    result_notified_at TEXT,
    result_notification_error TEXT,
    collection_closed_status TEXT NOT NULL DEFAULT 'pending'
        CHECK(collection_closed_status IN ('pending', 'sent', 'failed', 'skipped')),
    collection_closed_attempts INTEGER NOT NULL DEFAULT 0,
    collection_closed_at TEXT,
    collection_closed_error TEXT,
    self_assessment_status TEXT NOT NULL DEFAULT 'not_started'
        CHECK(self_assessment_status IN ('not_started', 'requested', 'completed', 'skipped')),
    self_assessment_score REAL
        CHECK(self_assessment_score IS NULL OR (self_assessment_score >= 1 AND self_assessment_score <= 10)),
    self_assessment_comment TEXT,
    self_assessment_criteria_json TEXT,
    self_assessment_requested_at TEXT,
    self_assessment_completed_at TEXT,
    correction_status TEXT NOT NULL DEFAULT 'not_started'
        CHECK(correction_status IN ('not_started', 'submitted', 'completed', 'excused')),
    correction_opened_at TEXT,
    correction_deadline_at TEXT,
    correction_submitted_at TEXT,
    reviewer_feedback_seen_at TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    UNIQUE(stream_publication_id, telegram_user_id),
    FOREIGN KEY(stream_publication_id) REFERENCES stream_publications(id) ON DELETE CASCADE,
    FOREIGN KEY(telegram_user_id) REFERENCES bot_users(telegram_user_id) ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS idx_stream_publications_schedule ON stream_publications(status, scheduled_at);
CREATE INDEX IF NOT EXISTS idx_participant_assignments_user_status ON participant_assignments(telegram_user_id, status);

CREATE TABLE IF NOT EXISTS publication_deliveries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stream_publication_id INTEGER NOT NULL,
    telegram_user_id INTEGER,
    recipient_chat_id INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'sent', 'failed', 'skipped')),
    attempts INTEGER NOT NULL DEFAULT 0,
    next_attempt_at TEXT,
    telegram_message_id INTEGER,
    sent_at TEXT,
    error_message TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    UNIQUE(stream_publication_id, recipient_chat_id),
    FOREIGN KEY(stream_publication_id) REFERENCES stream_publications(id) ON DELETE CASCADE,
    FOREIGN KEY(telegram_user_id) REFERENCES bot_users(telegram_user_id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS assignment_reminders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    participant_assignment_id INTEGER NOT NULL,
    offset_minutes INTEGER NOT NULL,
    scheduled_at TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'sent', 'skipped', 'failed')),
    sent_at TEXT,
    error_message TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    UNIQUE(participant_assignment_id, offset_minutes),
    FOREIGN KEY(participant_assignment_id) REFERENCES participant_assignments(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_publication_deliveries_due ON publication_deliveries(status, next_attempt_at);
CREATE INDEX IF NOT EXISTS idx_assignment_reminders_due ON assignment_reminders(status, scheduled_at);

CREATE TABLE IF NOT EXISTS peer_review_assignments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    target_assignment_id INTEGER NOT NULL,
    reviewer_user_id INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'assigned' CHECK(status IN ('assigned', 'completed', 'skipped')),
    assigned_at TEXT NOT NULL,
    completed_at TEXT,
    notification_status TEXT NOT NULL DEFAULT 'pending'
        CHECK(notification_status IN ('pending', 'sent', 'failed', 'skipped')),
    notification_attempts INTEGER NOT NULL DEFAULT 0,
    notified_at TEXT,
    notification_error TEXT,
    deadline_at TEXT,
    UNIQUE(target_assignment_id, reviewer_user_id),
    FOREIGN KEY(target_assignment_id) REFERENCES participant_assignments(id) ON DELETE CASCADE,
    FOREIGN KEY(reviewer_user_id) REFERENCES bot_users(telegram_user_id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS peer_reviews (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    peer_review_assignment_id INTEGER NOT NULL UNIQUE,
    score REAL NOT NULL CHECK(score >= 0),
    max_score REAL NOT NULL CHECK(max_score > 0),
    comment TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY(peer_review_assignment_id) REFERENCES peer_review_assignments(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS peer_review_drafts (
    reviewer_user_id INTEGER PRIMARY KEY,
    peer_review_assignment_id INTEGER NOT NULL,
    score REAL NOT NULL CHECK(score >= 1 AND score <= 10),
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY(reviewer_user_id) REFERENCES bot_users(telegram_user_id) ON DELETE CASCADE,
    FOREIGN KEY(peer_review_assignment_id) REFERENCES peer_review_assignments(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS peer_review_quality_ratings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    peer_review_id INTEGER NOT NULL UNIQUE,
    author_user_id INTEGER NOT NULL,
    score REAL NOT NULL CHECK(score >= 1 AND score <= 10),
    comment TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY(peer_review_id) REFERENCES peer_reviews(id) ON DELETE CASCADE,
    FOREIGN KEY(author_user_id) REFERENCES bot_users(telegram_user_id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS peer_review_quality_drafts (
    author_user_id INTEGER PRIMARY KEY,
    peer_review_id INTEGER NOT NULL,
    score REAL NOT NULL CHECK(score >= 1 AND score <= 10),
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY(peer_review_id) REFERENCES peer_reviews(id) ON DELETE CASCADE,
    FOREIGN KEY(author_user_id) REFERENCES bot_users(telegram_user_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS peer_review_reminders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    peer_review_assignment_id INTEGER NOT NULL,
    offset_minutes INTEGER NOT NULL,
    scheduled_at TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'sent', 'failed', 'skipped')),
    sent_at TEXT,
    error_message TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    UNIQUE(peer_review_assignment_id, offset_minutes),
    FOREIGN KEY(peer_review_assignment_id) REFERENCES peer_review_assignments(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_peer_review_assignments_reviewer_status
ON peer_review_assignments(reviewer_user_id, status);
CREATE INDEX IF NOT EXISTS idx_peer_review_quality_ratings_author
ON peer_review_quality_ratings(author_user_id);
CREATE INDEX IF NOT EXISTS idx_peer_review_reminders_due
ON peer_review_reminders(status, scheduled_at);

CREATE TABLE IF NOT EXISTS peer_review_quality_reminders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    participant_assignment_id INTEGER NOT NULL,
    offset_minutes INTEGER NOT NULL,
    scheduled_at TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'sent', 'failed', 'skipped')),
    attempts INTEGER NOT NULL DEFAULT 0,
    sent_at TEXT,
    error_message TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    UNIQUE(participant_assignment_id, offset_minutes),
    FOREIGN KEY(participant_assignment_id) REFERENCES participant_assignments(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_peer_review_quality_reminders_due
ON peer_review_quality_reminders(status, scheduled_at);

CREATE TABLE IF NOT EXISTS correction_reminders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    participant_assignment_id INTEGER NOT NULL,
    offset_minutes INTEGER NOT NULL,
    scheduled_at TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'sent', 'failed', 'skipped')),
    attempts INTEGER NOT NULL DEFAULT 0,
    sent_at TEXT,
    error_message TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    UNIQUE(participant_assignment_id, offset_minutes),
    FOREIGN KEY(participant_assignment_id) REFERENCES participant_assignments(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_correction_reminders_due
ON correction_reminders(status, scheduled_at);

CREATE TABLE IF NOT EXISTS peer_review_result_parts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    participant_assignment_id INTEGER NOT NULL,
    part_key TEXT NOT NULL,
    sent_at TEXT NOT NULL,
    created_at TEXT NOT NULL,
    UNIQUE(participant_assignment_id, part_key),
    FOREIGN KEY(participant_assignment_id) REFERENCES participant_assignments(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS analysis_runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message_id INTEGER NOT NULL,
    provider TEXT,
    model TEXT,
    framework TEXT,
    prompt_version TEXT,
    status TEXT NOT NULL,
    input_text TEXT,
    input_text_sha256 TEXT,
    output_text TEXT,
    raw_response_path TEXT,
    error_code TEXT,
    error_message TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    completed_at TEXT,
    FOREIGN KEY(message_id) REFERENCES messages(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_analysis_runs_message_id ON analysis_runs(message_id);
CREATE INDEX IF NOT EXISTS idx_analysis_runs_status ON analysis_runs(status);
"""