-- Phase 8.2: durable background job queue and dead-letter queue
CREATE TABLE IF NOT EXISTS `jobs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `queue` VARCHAR(64) NOT NULL DEFAULT 'default',
    `type` VARCHAR(128) NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `status` ENUM('pending', 'reserved', 'completed', 'failed') NOT NULL DEFAULT 'pending',
    `attempts` INT UNSIGNED NOT NULL DEFAULT 0,
    `max_attempts` INT UNSIGNED NOT NULL DEFAULT 3,
    `idempotency_key` VARCHAR(128) NULL,
    `reserved_at` INT UNSIGNED NULL,
    `reserved_by` VARCHAR(128) NULL,
    `available_at` INT UNSIGNED NOT NULL,
    `created_at` INT UNSIGNED NOT NULL,
    `updated_at` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_queue_idempotency` (`queue`, `idempotency_key`),
    KEY `idx_queue_status_available` (`queue`, `status`, `available_at`),
    KEY `idx_reserved_at` (`reserved_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `dead_letter_jobs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `original_job_id` VARCHAR(64) NOT NULL,
    `queue` VARCHAR(64) NOT NULL DEFAULT 'default',
    `type` VARCHAR(128) NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `attempts` INT UNSIGNED NOT NULL,
    `error_message` TEXT NULL,
    `failed_at` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_dead_letter_orig` (`original_job_id`),
    KEY `idx_queue_failed_at` (`queue`, `failed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
