-- Baltique OS Core 0.78.30 — Master Data Clean Room
-- Reference migration. Runtime PHP upgrade performs idempotent schema checks.

CREATE TABLE IF NOT EXISTS enterprise_master_data_domains (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  domain_key VARCHAR(120) NOT NULL,
  domain_label VARCHAR(255) NOT NULL,
  owner_role VARCHAR(80) NOT NULL DEFAULT 'admin',
  source_tables_json JSON NULL,
  canonical_table VARCHAR(120) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  description TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_enterprise_master_data_domains_first (domain_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_master_data_candidates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  domain_key VARCHAR(120) NOT NULL,
  source_table VARCHAR(120) NULL,
  source_id BIGINT UNSIGNED NULL,
  source_label VARCHAR(255) NULL,
  canonical_key VARCHAR(160) NULL,
  dedupe_hash CHAR(64) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'candidate',
  evidence_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_enterprise_master_data_candidates_first (domain_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_master_data_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_uuid VARCHAR(80) NOT NULL,
  run_type VARCHAR(60) NOT NULL DEFAULT 'manual',
  status VARCHAR(40) NOT NULL DEFAULT 'completed',
  candidates_scanned INT NOT NULL DEFAULT 0,
  duplicates_detected INT NOT NULL DEFAULT 0,
  evidence_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_enterprise_master_data_runs_first (run_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
