CREATE TABLE IF NOT EXISTS report_delivery_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    saved_report_id INT NOT NULL,
    requested_by_user_id INT NOT NULL,
    delivery_channel VARCHAR(40) NOT NULL DEFAULT 'email',
    recipient VARCHAR(190),
    status VARCHAR(60) NOT NULL,
    scheduled_for DATETIME NOT NULL,
    delivered_at DATETIME NULL,
    provider_message TEXT,
    metadata JSON,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

CREATE INDEX idx_report_delivery_jobs_tenant_status_scheduled ON report_delivery_jobs (tenant_id, status, scheduled_for);
CREATE INDEX idx_report_delivery_jobs_saved_report ON report_delivery_jobs (tenant_id, saved_report_id, scheduled_for);
