-- ============================================================================
-- Copy Jobs Table
-- Stores locally-created copy jobs and their Fabric identifiers for tracking.
-- Run this manually if not using Laravel migrations.
-- ============================================================================

CREATE TABLE IF NOT EXISTS `copy_jobs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

    -- Relationships
    `workspace_id` BIGINT UNSIGNED NOT NULL,
    `data_source_connection_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,

    -- Fabric identifiers
    `fabric_copy_job_id` VARCHAR(255) NULL DEFAULT NULL,
    `fabric_workspace_id` VARCHAR(255) NULL DEFAULT NULL,

    -- Job details
    `job_name` VARCHAR(255) NOT NULL,
    `copy_mode` VARCHAR(20) NOT NULL DEFAULT 'CDC',
    `status` ENUM('created', 'running', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'created',

    -- Configuration (JSON)
    `table_mappings` JSON NULL DEFAULT NULL,
    `settings` JSON NULL DEFAULT NULL,
    `schedule_config` JSON NULL DEFAULT NULL,

    -- Run tracking
    `last_run_at` TIMESTAMP NULL DEFAULT NULL,
    `last_error` TEXT NULL DEFAULT NULL,

    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,

    PRIMARY KEY (`id`),
    INDEX `copy_jobs_workspace_id_user_id_index` (`workspace_id`, `user_id`),
    INDEX `copy_jobs_fabric_copy_job_id_index` (`fabric_copy_job_id`),
    INDEX `copy_jobs_status_index` (`status`),

    CONSTRAINT `copy_jobs_workspace_id_foreign` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `copy_jobs_data_source_connection_id_foreign` FOREIGN KEY (`data_source_connection_id`) REFERENCES `data_source_connections` (`id`) ON DELETE CASCADE,
    CONSTRAINT `copy_jobs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
