CREATE TABLE IF NOT EXISTS catalogue_downloads (
    id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    remote_video_id      VARCHAR(100) NOT NULL,
    source_site          VARCHAR(50) NOT NULL DEFAULT '',
    title                VARCHAR(500) NOT NULL,
    description          TEXT DEFAULT NULL,
    duration             INT UNSIGNED NOT NULL DEFAULT 0,
    thumbnail_url        VARCHAR(1000) DEFAULT NULL,
    embed_url            VARCHAR(1000) DEFAULT NULL,
    download_url         VARCHAR(2000) DEFAULT NULL,
    download_url_expires DATETIME DEFAULT NULL,
    categories           TEXT DEFAULT NULL,
    tags                 TEXT DEFAULT NULL,
    performers           TEXT DEFAULT NULL,
    quality              VARCHAR(20) NOT NULL DEFAULT '',
    status               ENUM('pending','downloading','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
    attempt_count        TINYINT UNSIGNED NOT NULL DEFAULT 0,
    max_attempts         TINYINT UNSIGNED NOT NULL DEFAULT 3,
    error_message        VARCHAR(1000) DEFAULT NULL,
    video_id             INT UNSIGNED DEFAULT NULL,
    user_id              INT UNSIGNED NOT NULL,
    batch_id             VARCHAR(36) DEFAULT NULL,
    cost_credits         DECIMAL(10,4) NOT NULL DEFAULT 0,
    file_size            BIGINT UNSIGNED NOT NULL DEFAULT 0,
    bytes_downloaded     BIGINT UNSIGNED NOT NULL DEFAULT 0,
    created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    started_at           DATETIME DEFAULT NULL,
    completed_at         DATETIME DEFAULT NULL,
    INDEX idx_status (status),
    INDEX idx_batch (batch_id),
    INDEX idx_updated (updated_at),
    UNIQUE INDEX idx_remote_unique (remote_video_id),
    FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
