-- Sync Center repair patch for existing MySQL installations.
-- Import this file in phpMyAdmin when /app/sync reports missing data_sync_clients.

CREATE TABLE IF NOT EXISTS `data_import_batches` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `import_type` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `original_filename` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `total_rows` int unsigned NOT NULL DEFAULT 0,
  `successful_rows` int unsigned NOT NULL DEFAULT 0,
  `failed_rows` int unsigned NOT NULL DEFAULT 0,
  `summary` json DEFAULT NULL,
  `errors` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `data_import_batches_tenant_id_created_at_index` (`tenant_id`, `created_at`),
  KEY `data_import_batches_user_id_foreign` (`user_id`),
  KEY `data_import_batches_import_type_index` (`import_type`),
  KEY `data_import_batches_status_index` (`status`),
  CONSTRAINT `data_import_batches_tenant_id_foreign` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `data_import_batches_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `data_sync_clients` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token_hash` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_synced_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `data_sync_clients_tenant_id_is_active_index` (`tenant_id`, `is_active`),
  KEY `data_sync_clients_is_active_index` (`is_active`),
  CONSTRAINT `data_sync_clients_tenant_id_foreign` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `data_sync_packages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `data_sync_client_id` bigint unsigned DEFAULT NULL,
  `direction` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `records_count` int unsigned NOT NULL DEFAULT 0,
  `summary` json DEFAULT NULL,
  `errors` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `data_sync_packages_tenant_id_created_at_index` (`tenant_id`, `created_at`),
  KEY `data_sync_packages_user_id_foreign` (`user_id`),
  KEY `data_sync_packages_data_sync_client_id_foreign` (`data_sync_client_id`),
  KEY `data_sync_packages_direction_index` (`direction`),
  KEY `data_sync_packages_status_index` (`status`),
  CONSTRAINT `data_sync_packages_data_sync_client_id_foreign` FOREIGN KEY (`data_sync_client_id`) REFERENCES `data_sync_clients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `data_sync_packages_tenant_id_foreign` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `data_sync_packages_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
