-- Release 079: external provider run controls, reconciliation and alerts.
ALTER TABLE master_data_sync_runs
  ADD COLUMN source_watermark VARCHAR(191) NULL AFTER source_fingerprint,
  ADD COLUMN mapping_version VARCHAR(80) NULL AFTER source_watermark,
  ADD COLUMN record_counts_json JSON NULL AFTER summary_json,
  ADD COLUMN error_class VARCHAR(80) NULL AFTER error_code,
  ADD COLUMN operator_decision ENUM('pending','preview_only','approved_apply','rejected') NOT NULL DEFAULT 'pending' AFTER error_class,
  ADD COLUMN decided_by BIGINT UNSIGNED NULL AFTER operator_decision,
  ADD COLUMN decided_at DATETIME NULL AFTER decided_by,
  ADD KEY idx_master_data_runs_decision(provider_id,operator_decision,status),
  ADD CONSTRAINT fk_master_data_runs_decided_by FOREIGN KEY(decided_by) REFERENCES users(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS master_data_provider_policies (
  provider_id BIGINT UNSIGNED PRIMARY KEY,
  allow_scheduled_preview TINYINT(1) NOT NULL DEFAULT 1,
  allow_approved_apply TINYINT(1) NOT NULL DEFAULT 0,
  allow_membership_deactivation TINYINT(1) NOT NULL DEFAULT 0,
  stale_after_hours SMALLINT UNSIGNED NOT NULL DEFAULT 72,
  duplicate_alert_threshold SMALLINT UNSIGNED NOT NULL DEFAULT 20,
  updated_by BIGINT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_master_data_policy_provider FOREIGN KEY(provider_id) REFERENCES master_data_providers(id) ON DELETE CASCADE,
  CONSTRAINT fk_master_data_policy_user FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS master_data_provider_alerts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_id BIGINT UNSIGNED NOT NULL,
  run_id BIGINT UNSIGNED NULL,
  alert_key ENUM('stale_run','token_expiry','schema_change','elevated_duplicates','failed_run','rate_limited','malformed_data') NOT NULL,
  severity ENUM('warning','critical') NOT NULL DEFAULT 'warning',
  detail_code VARCHAR(80) NOT NULL,
  resolved_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_master_data_provider_alert(provider_id,run_id,alert_key,detail_code),
  KEY idx_master_data_provider_alert_open(provider_id,resolved_at,created_at),
  CONSTRAINT fk_master_data_alert_provider FOREIGN KEY(provider_id) REFERENCES master_data_providers(id) ON DELETE CASCADE,
  CONSTRAINT fk_master_data_alert_run FOREIGN KEY(run_id) REFERENCES master_data_sync_runs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO migration_history(version,name,checksum,execution_ms)
VALUES('202610020079','External provider preview controls and reconciliation alerts',SHA2('202610020079_provider_run_controls_v1',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);