-- Migration 011: Create upload_jobs table (DB-backed queue)
-- Requirements: 4.4, 4.6, 4.7, 4.8
-- Design: Data Models -> 11. UPLOAD JOBS

CREATE TABLE IF NOT EXISTS upload_jobs (
    id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id                 BIGINT UNSIGNED NOT NULL,
    marketplace_source      ENUM('SHOPEE','TOKOPEDIA','TIKTOK_SHOP') NOT NULL,
    file_path               VARCHAR(512) NOT NULL,
    original_filename       VARCHAR(255) NULL,
    file_size_bytes         BIGINT UNSIGNED NOT NULL,
    file_kind               ENUM('SALES','SETTLEMENT','RETURN','ADS','AFFILIATE')
                                NOT NULL DEFAULT 'SALES',
    status                  ENUM('QUEUED','PROCESSING','COMPLETED','FAILED','CANCELLED')
                                NOT NULL DEFAULT 'QUEUED',
    total_rows              INT          NOT NULL DEFAULT 0,
    processed_rows          INT          NOT NULL DEFAULT 0,
    last_processed_row      INT          NOT NULL DEFAULT 0,
    rows_accepted           INT          NOT NULL DEFAULT 0,
    rows_rejected           INT          NOT NULL DEFAULT 0,
    rows_unresolved         INT          NOT NULL DEFAULT 0,
    error_message           TEXT         NULL,
    error_log_path          VARCHAR(512) NULL,
    claimed_by              VARCHAR(64)  NULL,
    last_heartbeat          DATETIME(3)  NULL,
    started_at              DATETIME(3)  NULL,
    finished_at             DATETIME(3)  NULL,
    created_at              DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    KEY idx_uj_status_created (status, created_at),
    KEY idx_uj_heartbeat (status, last_heartbeat),
    KEY idx_uj_user (user_id, created_at),
    CONSTRAINT fk_uj_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;