-- Baltique OS Core 0.78.3 — Unified Enterprise Shell & 0.78.2 Upgrade Repair
-- Canonical migration reference. The deployment path is /upgrade_0_78_3.php, which performs
-- idempotent column/index checks before applying these schema changes.

-- 0.78.2 repair note:
-- app/system_runtime_preflight.php fixes the PHP seed for system_acceptance_scenarios:
-- column list = 14, VALUES list = 14. The prior 0.78.2 PHP code had one extra placeholder.

ALTER TABLE system_modules ADD COLUMN nav_label VARCHAR(190) NULL;
ALTER TABLE system_modules ADD COLUMN nav_icon VARCHAR(32) NULL;
ALTER TABLE system_modules ADD COLUMN nav_group VARCHAR(120) NULL;
ALTER TABLE system_modules ADD COLUMN nav_order INT NOT NULL DEFAULT 1000;
ALTER TABLE system_modules ADD COLUMN nav_visible TINYINT(1) NOT NULL DEFAULT 1;
ALTER TABLE system_modules ADD COLUMN route_health VARCHAR(40) NOT NULL DEFAULT 'unknown';
ALTER TABLE system_modules ADD COLUMN route_checked_at DATETIME NULL;
ALTER TABLE system_modules ADD COLUMN last_seen_in_code TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE system_modules ADD COLUMN legacy_reason TEXT NULL;
ALTER TABLE system_modules ADD COLUMN nav_workspace VARCHAR(80) NOT NULL DEFAULT 'global';
ALTER TABLE system_modules ADD KEY idx_system_module_nav (nav_visible, nav_group, nav_order);
ALTER TABLE system_modules ADD KEY idx_system_module_route_health (route_health, route_checked_at);

CREATE TABLE IF NOT EXISTS system_module_route_health (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  module_key VARCHAR(120) NOT NULL,
  route_path VARCHAR(255) NOT NULL,
  route_exists TINYINT(1) NOT NULL DEFAULT 0,
  file_path_checked VARCHAR(255) NULL,
  health_status VARCHAR(40) NOT NULL DEFAULT 'unknown',
  message TEXT NULL,
  checked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_system_route_health_module (module_key, route_path),
  KEY idx_system_route_health_status (health_status, checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_navigation_audit (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  action_key VARCHAR(120) NOT NULL,
  module_key VARCHAR(120) NULL,
  user_id BIGINT UNSIGNED NULL,
  details_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_system_nav_audit_action (action_key, created_at),
  KEY idx_system_nav_audit_module (module_key, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO system_feature_flags
(flag_key,flag_value,flag_scope,description,updated_by_user_id,created_at,updated_at)
VALUES
('enterprise_shell_runtime_nav_enabled','1','global','Use Unified Enterprise Shell backed by Module Registry with static fallback.',NULL,NOW(),NOW()),
('enterprise_shell_hide_legacy_in_nav','1','global','Hide legacy modules from default Enterprise Shell navigation.',NULL,NOW(),NOW()),
('enterprise_shell_admin_show_legacy','0','global','Legacy modules remain accessible from Navigation Registry, but are hidden from the sidebar by default.',NULL,NOW(),NOW())
ON DUPLICATE KEY UPDATE flag_value='1', description=VALUES(description), updated_at=NOW();

INSERT INTO app_settings (setting_key,setting_value,created_at,updated_at)
VALUES
('core_version','0.78.3',NOW(),NOW()),
('schema_version','0.78.3',NOW(),NOW()),
('enterprise_shell_0_78_3','1',NOW(),NOW()),
('enterprise_shell_runtime_nav','1',NOW(),NOW()),
('legacy_modules_hidden_from_default_nav','1',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();

INSERT INTO system_migration_ledger
(migration_key,version,file_path,checksum_sha256,status,applied_by_user_id,applied_at,notes,details_json)
VALUES
('088_core_0_78_3_unified_enterprise_shell','0.78.3','database/migrations/088_core_0_78_3_unified_enterprise_shell.sql',NULL,'applied',NULL,NOW(),'Unified Enterprise Shell applied; 0.78.2 upgrade seed repaired.',JSON_OBJECT('enterprise_shell', true, 'upgrade_0782_repair', true))
ON DUPLICATE KEY UPDATE version=VALUES(version), file_path=VALUES(file_path), status='applied', applied_at=NOW(), notes=VALUES(notes), details_json=VALUES(details_json);
