-- Baltique OS Core 2.1.0 — Enterprise Codebase Normalization Release
-- Reference migration. Guarded PHP upgrade performs compatibility-aware seeding.

CREATE TABLE IF NOT EXISTS enterprise_codebase_normalization_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_uid VARCHAR(64) NOT NULL,
  run_type VARCHAR(40) NOT NULL DEFAULT 'manual',
  status VARCHAR(40) NOT NULL DEFAULT 'running',
  scanned_files INT UNSIGNED NOT NULL DEFAULT 0,
  findings_total INT UNSIGNED NOT NULL DEFAULT 0,
  critical_findings INT UNSIGNED NOT NULL DEFAULT 0,
  warning_findings INT UNSIGNED NOT NULL DEFAULT 0,
  info_findings INT UNSIGNED NOT NULL DEFAULT 0,
  summary_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_normalization_run_uid (run_uid),
  KEY idx_normalization_run_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_codebase_normalization_findings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NOT NULL,
  finding_key VARCHAR(160) NOT NULL,
  domain_key VARCHAR(80) NOT NULL DEFAULT 'core',
  severity VARCHAR(40) NOT NULL DEFAULT 'info',
  finding_status VARCHAR(40) NOT NULL DEFAULT 'open',
  file_path VARCHAR(255) NULL,
  line_number INT UNSIGNED NULL,
  title VARCHAR(255) NOT NULL,
  message TEXT NULL,
  recommendation TEXT NULL,
  evidence_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_normalization_findings_run (run_id,severity,finding_status),
  KEY idx_normalization_findings_file (file_path,line_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_legacy_route_registry (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  route_path VARCHAR(255) NOT NULL,
  route_type VARCHAR(40) NOT NULL DEFAULT 'upgrade',
  legacy_version VARCHAR(40) NULL,
  replacement_route VARCHAR(255) NOT NULL DEFAULT '/system_codebase_normalization.php',
  execution_allowed TINYINT(1) NOT NULL DEFAULT 0,
  status VARCHAR(40) NOT NULL DEFAULT 'historical',
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_legacy_route_path (route_path),
  KEY idx_legacy_route_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_ui_string_registry (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  string_key VARCHAR(190) NOT NULL,
  locale VARCHAR(8) NOT NULL,
  string_value TEXT NOT NULL,
  context_key VARCHAR(120) NULL,
  official_flag TINYINT(1) NOT NULL DEFAULT 1,
  review_status VARCHAR(40) NOT NULL DEFAULT 'approved',
  updated_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ui_string_locale (string_key,locale)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings (setting_key,setting_value,created_at,updated_at) VALUES
('core_version','2.1.0',NOW(),NOW()),
('schema_version','2.1.0',NOW(),NOW()),
('enterprise_current_release','2.1.0',NOW(),NOW()),
('enterprise_codebase_normalization_version','2.1.0',NOW(),NOW()),
('enterprise_codebase_normalization_enabled','1',NOW(),NOW()),
('enterprise_dynamic_asset_versioning_enabled','1',NOW(),NOW()),
('enterprise_legacy_upgrade_guard_enabled','1',NOW(),NOW()),
('enterprise_allow_legacy_upgrade_execution','0',NOW(),NOW()),
('enterprise_runtime_error_guard_enabled','1',NOW(),NOW()),
('enterprise_unified_action_router_enabled','1',NOW(),NOW()),
('enterprise_request_metadata_cache_enabled','1',NOW(),NOW()),
('enterprise_official_ui_line','Enterprise OS',NOW(),NOW()),
('enterprise_refresh_label_policy','current_layer_only',NOW(),NOW()),
('enterprise_role_based_navigation_enabled','1',NOW(),NOW()),
('core_version_210','1',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value),updated_at=NOW();
