-- Track bug report submissions for rate limiting and auditing.

CREATE TABLE IF NOT EXISTS bug_report_submissions (
    id SERIAL PRIMARY KEY,
    sag_id INTEGER NOT NULL REFERENCES sag_sager(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    screenshot_attached BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_bug_report_submissions_user_time
    ON bug_report_submissions(user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_bug_report_submissions_sag
    ON bug_report_submissions(sag_id);