CREATE TABLE IF NOT EXISTS `{{prefix}}data_import_batches` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `file_name` VARCHAR(255) NOT NULL,
  `stored_name` VARCHAR(255) NOT NULL,
  `file_type` VARCHAR(20) NOT NULL,
  `status` ENUM('uploaded','previewed','importing','completed','partial','failed','cancelled') NOT NULL DEFAULT 'uploaded',
  `source_count_json` JSON NULL,
  `imported_count_json` JSON NULL,
  `skipped_count_json` JSON NULL,
  `report_json` LONGTEXT NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_import_batch_status` (`status`,`created_at`),
  CONSTRAINT `{{prefix}}fk_import_batch_user` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}demo_record_registry` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `batch_id` BIGINT UNSIGNED NULL,
  `entity_type` VARCHAR(80) NOT NULL,
  `entity_id` BIGINT UNSIGNED NOT NULL,
  `external_key` VARCHAR(190) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_demo_entity` (`entity_type`,`entity_id`),
  KEY `idx_demo_batch` (`batch_id`,`entity_type`),
  CONSTRAINT `{{prefix}}fk_demo_batch` FOREIGN KEY (`batch_id`) REFERENCES `{{prefix}}data_import_batches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}data_reset_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `reset_type` ENUM('demo_only','clinic_activity','factory_defaults') NOT NULL,
  `backup_file` VARCHAR(255) NOT NULL,
  `summary_json` JSON NULL,
  `performed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_reset_log_date` (`created_at`),
  CONSTRAINT `{{prefix}}fk_reset_user` FOREIGN KEY (`performed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
