-- Migration: 0007_create_broadcast_jobs_table
-- Turns /broadcast from a single synchronous loop (risking an HTTP
-- timeout on shared hosting for large user bases) into a resumable
-- queue: one row per broadcast, processed in small batches by a
-- cron-triggered script (see installer/process-broadcast.php and
-- public/cron/process-broadcast.php).

CREATE TABLE IF NOT EXISTS broadcast_jobs (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    message           LONGTEXT NOT NULL,
    status            ENUM('pending', 'processing', 'completed') NOT NULL DEFAULT 'pending',
    total_recipients  INT UNSIGNED NOT NULL,
    next_offset       INT UNSIGNED NOT NULL DEFAULT 0,
    sent_count        INT UNSIGNED NOT NULL DEFAULT 0,
    failed_count      INT UNSIGNED NOT NULL DEFAULT 0,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at      DATETIME NULL,

    KEY idx_broadcast_jobs_status (status)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;