-- Baltique OS Core 2.7.0
-- Enterprise Clinical Function Unification & canonical Sopot site consolidation.

CREATE TABLE IF NOT EXISTS enterprise_clinical_functions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  function_key VARCHAR(100) NOT NULL,
  label_pl VARCHAR(190) NOT NULL,
  label_en VARCHAR(190) NOT NULL,
  icon VARCHAR(20) NULL,
  group_pl VARCHAR(120) NOT NULL,
  group_en VARCHAR(120) NOT NULL,
  route_path VARCHAR(255) NOT NULL,
  purpose_pl TEXT NULL,
  purpose_en TEXT NULL,
  role_json JSON NOT NULL,
  sort_order INT NOT NULL DEFAULT 100,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_clinical_function_key(function_key),
  KEY idx_clinical_function_status(status,sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_clinical_function_tools (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  function_key VARCHAR(100) NOT NULL,
  module_key VARCHAR(120) NOT NULL,
  tool_label VARCHAR(190) NOT NULL,
  route_path VARCHAR(255) NOT NULL,
  icon VARCHAR(20) NULL,
  lifecycle VARCHAR(40) NOT NULL DEFAULT 'specialist',
  visible_in_center TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 100,
  metadata_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_clinical_function_tool(function_key,module_key),
  KEY idx_clinical_function_tool_route(route_path),
  KEY idx_clinical_function_tool_lifecycle(lifecycle,visible_in_center)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_route_aliases (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  legacy_route VARCHAR(255) NOT NULL,
  canonical_route VARCHAR(255) NOT NULL,
  function_key VARCHAR(100) NOT NULL,
  redirect_mode VARCHAR(40) NOT NULL DEFAULT 'navigation_only',
  active TINYINT(1) NOT NULL DEFAULT 1,
  reason VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_route_alias(legacy_route),
  KEY idx_enterprise_route_function(function_key,active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_module_inventory_270 (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  file_path VARCHAR(500) NOT NULL,
  route_path VARCHAR(500) NULL,
  file_type VARCHAR(40) NOT NULL,
  category VARCHAR(80) NOT NULL,
  function_key VARCHAR(100) NULL,
  lifecycle VARCHAR(40) NOT NULL DEFAULT 'specialist',
  decision VARCHAR(80) NOT NULL DEFAULT 'keep',
  title_detected VARCHAR(255) NULL,
  file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
  sha256 CHAR(64) NULL,
  notes TEXT NULL,
  scanned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_module_inventory_file(file_path),
  KEY idx_module_inventory_decision(category,lifecycle),
  KEY idx_module_inventory_function(function_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_module_unification_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_key VARCHAR(120) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'running',
  files_scanned INT NOT NULL DEFAULT 0,
  functions_seeded INT NOT NULL DEFAULT 0,
  tools_seeded INT NOT NULL DEFAULT 0,
  legacy_hidden INT NOT NULL DEFAULT 0,
  duplicates_mapped INT NOT NULL DEFAULT 0,
  site_consolidation_run_key VARCHAR(120) NULL,
  result_json JSON NULL,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_module_unification_run(run_key),
  KEY idx_module_unification_status(status,started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_site_aliases (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  alias_key VARCHAR(160) NOT NULL,
  canonical_site_key VARCHAR(120) NOT NULL,
  alias_type VARCHAR(40) NOT NULL DEFAULT 'site_key',
  source_table VARCHAR(120) NULL,
  source_id BIGINT UNSIGNED NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  metadata_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_site_alias(alias_key,alias_type),
  KEY idx_enterprise_site_alias_canonical(canonical_site_key,active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_site_consolidation_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_key VARCHAR(120) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'running',
  canonical_site_key VARCHAR(120) NOT NULL,
  canonical_location_id BIGINT UNSIGNED NULL,
  source_signature_before VARCHAR(64) NULL,
  source_signature_after VARCHAR(64) NULL,
  moved_references INT NOT NULL DEFAULT 0,
  merged_rows INT NOT NULL DEFAULT 0,
  deleted_duplicates INT NOT NULL DEFAULT 0,
  warnings_json JSON NULL,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_site_consolidation_run_key(run_key),
  KEY idx_site_consolidation_status(status,started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings(setting_key,setting_value,created_at,updated_at)
VALUES
 ('enterprise_clinical_unification_version','2.7.0',NOW(),NOW()),
 ('enterprise_navigation_model','one_tab_per_clinical_function',NOW(),NOW()),
 ('enterprise_site_canonical_sopot_key','sopot_current',NOW(),NOW()),
 ('enterprise_site_canonical_sopot_label','Baltique Sopot, Gabinet',NOW(),NOW()),
 ('enterprise_generic_release_scaffold_is_completion','0',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value),updated_at=NOW();
