-- Release 077: fixture-only external-provider discovery governance.
-- This migration does not create a connector, schedule, import, or live write path.

CREATE TABLE IF NOT EXISTS provider_discovery_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_key VARCHAR(80) NOT NULL,
  display_name VARCHAR(160) NOT NULL,
  discovery_status ENUM('documentation_pending','mapping_draft','approved_read_only','approved_for_import','retired') NOT NULL DEFAULT 'documentation_pending',
  official_document_url VARCHAR(1000) NULL,
  data_controller_note TEXT NULL,
  support_contact VARCHAR(255) NULL,
  credential_cipher TEXT NULL,
  credential_updated_at DATETIME NULL,
  credential_updated_by BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_provider_discovery_profile(provider_key),
  CONSTRAINT fk_provider_discovery_credential_user FOREIGN KEY(credential_updated_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_provider_discovery_created_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_provider_discovery_updated_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 provider_mapping_decisions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_profile_id BIGINT UNSIGNED NOT NULL,
  version_no INT UNSIGNED NOT NULL,
  mapping_json JSON NOT NULL,
  mapping_sha256 CHAR(64) NOT NULL,
  decision_log TEXT NOT NULL,
  status ENUM('draft','signed_off','superseded','rejected') NOT NULL DEFAULT 'draft',
  signed_off_by BIGINT UNSIGNED NULL,
  signed_off_at DATETIME NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_provider_mapping_version(provider_profile_id,version_no),
  KEY idx_provider_mapping_signed_off(provider_profile_id,status,signed_off_at),
  CONSTRAINT fk_provider_mapping_profile FOREIGN KEY(provider_profile_id) REFERENCES provider_discovery_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_provider_mapping_signed_user FOREIGN KEY(signed_off_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_provider_mapping_created_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO provider_discovery_profiles(provider_key,display_name,discovery_status)
VALUES
 ('hermes_adelphi','Hermes Adelphi','documentation_pending'),
 ('keystone_online','Keystone Online','documentation_pending')
ON DUPLICATE KEY UPDATE display_name=VALUES(display_name);

INSERT INTO migration_history(version,name,checksum,execution_ms)
VALUES('202610020077','Fixture-only external provider discovery governance',SHA2('202610020077_provider_discovery_governance_v1',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);