-- Baltique OS Core 0.78.0 — Enterprise Integration Stabilization
-- Canonical release migration for schema compatibility, migration ledger and module registry.
-- This SQL file is safe to run through /upgrade_0_78_0.php. The PHP upgrade helper performs
-- column/index existence checks and treats duplicate-column/index errors as non-fatal.

CREATE TABLE IF NOT EXISTS system_migration_ledger (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  migration_key VARCHAR(160) NOT NULL,
  version VARCHAR(40) NOT NULL,
  file_path VARCHAR(255) NULL,
  checksum_sha256 VARCHAR(128) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'applied',
  applied_by_user_id BIGINT UNSIGNED NULL,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notes TEXT NULL,
  details_json JSON NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_system_migration_key (migration_key),
  KEY idx_system_migration_version (version, status, applied_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_modules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  module_key VARCHAR(120) NOT NULL,
  module_label VARCHAR(190) NOT NULL,
  domain_key VARCHAR(80) NOT NULL,
  owner_role VARCHAR(80) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  route_path VARCHAR(255) NULL,
  replacement_module_key VARCHAR(120) NULL,
  source_of_truth VARCHAR(80) NOT NULL DEFAULT 'database',
  clinical_purpose TEXT NULL,
  ai_mode VARCHAR(80) NOT NULL DEFAULT 'embedded_ready',
  version VARCHAR(40) NOT NULL DEFAULT '0.78.0',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_system_module_key (module_key),
  KEY idx_system_module_domain (domain_key, status, active),
  KEY idx_system_module_replacement (replacement_module_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_module_routes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  module_key VARCHAR(120) NOT NULL,
  route_path VARCHAR(255) NOT NULL,
  route_type VARCHAR(40) NOT NULL DEFAULT 'page',
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  requires_role VARCHAR(120) NULL,
  legacy_redirect_to VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_system_module_route (route_path),
  KEY idx_system_route_module (module_key, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_module_dependencies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  module_key VARCHAR(120) NOT NULL,
  depends_on_module_key VARCHAR(120) NOT NULL,
  dependency_type VARCHAR(40) NOT NULL DEFAULT 'runtime',
  required TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_system_module_dependency (module_key, depends_on_module_key, dependency_type),
  KEY idx_system_dependency_lookup (depends_on_module_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_feature_flags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  flag_key VARCHAR(160) NOT NULL,
  flag_value VARCHAR(255) NOT NULL DEFAULT '0',
  flag_scope VARCHAR(80) NOT NULL DEFAULT 'global',
  description TEXT NULL,
  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_system_feature_flag (flag_key, flag_scope)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_schema_compatibility_checks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  check_key VARCHAR(190) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'unknown',
  severity VARCHAR(40) NOT NULL DEFAULT 'info',
  message TEXT NULL,
  details_json JSON NULL,
  checked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_system_check_key (check_key),
  KEY idx_system_check_status (status, severity, checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Digital Twin 0.75/0.77/0.78 compatibility.
ALTER TABLE or_digital_twin_scenarios ADD COLUMN simulation_date DATE NULL;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN scenario_key VARCHAR(120) NULL;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN scenario_date DATE NULL;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN scenario_code VARCHAR(160) NULL;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN scenario_type VARCHAR(80) NOT NULL DEFAULT 'custom';
ALTER TABLE or_digital_twin_scenarios ADD COLUMN baseline_json JSON NULL;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN result_json JSON NULL;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN total_cases INT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN predicted_delay_minutes INT NOT NULL DEFAULT 0;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN predicted_overtime_minutes INT NOT NULL DEFAULT 0;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN predicted_pacu_conflicts INT NOT NULL DEFAULT 0;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN predicted_team_conflicts INT NOT NULL DEFAULT 0;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN predicted_pharmacy_conflicts INT NOT NULL DEFAULT 0;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN predicted_risk_score DECIMAL(10,2) NOT NULL DEFAULT 0.00;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN predicted_satisfaction_score DECIMAL(10,2) NOT NULL DEFAULT 0.00;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN predicted_cost_delta DECIMAL(12,2) NOT NULL DEFAULT 0.00;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN optimizer_score DECIMAL(12,2) NOT NULL DEFAULT 0.00;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN recommendation TEXT NULL;
ALTER TABLE or_digital_twin_scenarios ADD COLUMN status VARCHAR(40) NOT NULL DEFAULT 'generated';
ALTER TABLE or_digital_twin_scenarios ADD COLUMN updated_at DATETIME NULL;
UPDATE or_digital_twin_scenarios
   SET simulation_date=COALESCE(simulation_date, scenario_date, DATE(created_at)),
       scenario_date=COALESCE(scenario_date, simulation_date, DATE(created_at)),
       scenario_key=COALESCE(NULLIF(scenario_key,''), NULLIF(scenario_code,''), NULLIF(scenario_type,''), CONCAT('scenario_',id)),
       scenario_code=COALESCE(NULLIF(scenario_code,''), CONCAT(DATE_FORMAT(COALESCE(simulation_date,scenario_date,DATE(created_at)),'%Y%m%d'),'_',COALESCE(NULLIF(scenario_key,''),'scenario'),'_',id)),
       status=COALESCE(NULLIF(status,''),'generated'),
       updated_at=COALESCE(updated_at,NOW());
ALTER TABLE or_digital_twin_scenarios ADD KEY idx_or_dt078_simulation (simulation_date, scenario_key, optimizer_score);

ALTER TABLE or_digital_twin_case_predictions ADD COLUMN simulation_date DATE NULL;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN scenario_date DATE NULL;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN scenario_key VARCHAR(120) NULL;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN patient_id BIGINT UNSIGNED NULL;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN planned_start_at DATETIME NULL;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN predicted_start_at DATETIME NULL;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN predicted_end_at DATETIME NULL;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN predicted_pacu_start_at DATETIME NULL;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN predicted_pacu_end_at DATETIME NULL;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN delay_minutes INT NOT NULL DEFAULT 0;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN predicted_delay_minutes INT NOT NULL DEFAULT 0;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN risk_delta DECIMAL(10,2) NOT NULL DEFAULT 0.00;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN risk_score DECIMAL(10,2) NULL;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN satisfaction_delta DECIMAL(10,2) NOT NULL DEFAULT 0.00;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN satisfaction_score DECIMAL(10,2) NULL;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN cost_delta DECIMAL(12,2) NOT NULL DEFAULT 0.00;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN details_json JSON NULL;
ALTER TABLE or_digital_twin_case_predictions ADD COLUMN flags_json JSON NULL;
UPDATE or_digital_twin_case_predictions p
LEFT JOIN or_digital_twin_scenarios s ON s.id=p.scenario_id
   SET p.simulation_date=COALESCE(p.simulation_date,p.scenario_date,s.simulation_date,s.scenario_date,DATE(p.created_at)),
       p.scenario_date=COALESCE(p.scenario_date,p.simulation_date,s.scenario_date,s.simulation_date,DATE(p.created_at)),
       p.scenario_key=COALESCE(NULLIF(p.scenario_key,''),s.scenario_key,s.scenario_code,'scenario'),
       p.delay_minutes=COALESCE(NULLIF(p.delay_minutes,0),p.predicted_delay_minutes,0),
       p.predicted_delay_minutes=COALESCE(NULLIF(p.predicted_delay_minutes,0),p.delay_minutes,0);
ALTER TABLE or_digital_twin_case_predictions ADD KEY idx_or_dt078_case_date (simulation_date, scenario_key, operative_case_id);

ALTER TABLE or_digital_twin_recommendations ADD COLUMN simulation_date DATE NULL;
ALTER TABLE or_digital_twin_recommendations ADD COLUMN scenario_date DATE NULL;
ALTER TABLE or_digital_twin_recommendations ADD COLUMN baseline_value DECIMAL(12,2) NULL;
ALTER TABLE or_digital_twin_recommendations ADD COLUMN recommended_value DECIMAL(12,2) NULL;
ALTER TABLE or_digital_twin_recommendations ADD COLUMN impact_json JSON NULL;
ALTER TABLE or_digital_twin_recommendations ADD COLUMN expected_impact_json JSON NULL;
ALTER TABLE or_digital_twin_recommendations ADD COLUMN updated_at DATETIME NULL;
UPDATE or_digital_twin_recommendations r
LEFT JOIN or_digital_twin_scenarios s ON s.id=r.scenario_id
   SET r.simulation_date=COALESCE(r.simulation_date,r.scenario_date,s.simulation_date,s.scenario_date,DATE(r.created_at)),
       r.scenario_date=COALESCE(r.scenario_date,r.simulation_date,s.scenario_date,s.simulation_date,DATE(r.created_at)),
       r.impact_json=COALESCE(r.impact_json,r.expected_impact_json),
       r.expected_impact_json=COALESCE(r.expected_impact_json,r.impact_json),
       r.updated_at=COALESCE(r.updated_at,NOW());

-- OR Readiness idempotency: booking_id NULL no longer permits duplicate rows.
ALTER TABLE or_readiness_daily_status ADD COLUMN readiness_source_key VARCHAR(190) NULL;
UPDATE or_readiness_daily_status
   SET readiness_source_key = CASE
       WHEN booking_id IS NOT NULL THEN CONCAT('booking:', booking_id)
       WHEN operative_case_id IS NOT NULL THEN CONCAT('case:', operative_case_id)
       WHEN patient_id IS NOT NULL THEN CONCAT('patient:', patient_id)
       ELSE CONCAT('manual:', id)
   END
 WHERE readiness_source_key IS NULL OR readiness_source_key='';
DELETE d FROM or_readiness_daily_status d
JOIN or_readiness_daily_status k
  ON d.readiness_date=k.readiness_date
 AND d.readiness_source_key=k.readiness_source_key
 AND d.id<k.id;
ALTER TABLE or_readiness_daily_status MODIFY readiness_source_key VARCHAR(190) NOT NULL;
ALTER TABLE or_readiness_daily_status ADD UNIQUE KEY uq_or_ready_source_key (readiness_date, readiness_source_key);
ALTER TABLE or_readiness_daily_status ADD KEY idx_or_ready_patient_day (patient_id, readiness_date);

-- OR Mobile Push dual-schema compatibility.
ALTER TABLE or_mobile_push_subscriptions ADD COLUMN staff_email VARCHAR(190) NULL;
ALTER TABLE or_mobile_push_subscriptions ADD COLUMN staff_phone VARCHAR(80) NULL;
ALTER TABLE or_mobile_push_subscriptions ADD COLUMN endpoint_hash VARCHAR(128) NULL;
ALTER TABLE or_mobile_push_subscriptions ADD COLUMN subscription_json JSON NULL;
ALTER TABLE or_mobile_push_subscriptions ADD COLUMN permission_status VARCHAR(40) NOT NULL DEFAULT 'granted';
ALTER TABLE or_mobile_push_subscriptions ADD COLUMN endpoint TEXT NULL;
ALTER TABLE or_mobile_push_subscriptions ADD COLUMN p256dh VARCHAR(255) NULL;
ALTER TABLE or_mobile_push_subscriptions ADD COLUMN auth_secret VARCHAR(255) NULL;
ALTER TABLE or_mobile_push_subscriptions ADD COLUMN user_agent TEXT NULL;
UPDATE or_mobile_push_subscriptions
   SET endpoint = COALESCE(endpoint, JSON_UNQUOTE(JSON_EXTRACT(subscription_json,'$.endpoint'))),
       p256dh = COALESCE(p256dh, JSON_UNQUOTE(JSON_EXTRACT(subscription_json,'$.keys.p256dh'))),
       auth_secret = COALESCE(auth_secret, JSON_UNQUOTE(JSON_EXTRACT(subscription_json,'$.keys.auth')))
 WHERE subscription_json IS NOT NULL;
UPDATE or_mobile_push_subscriptions SET endpoint_hash=COALESCE(endpoint_hash,SHA2(endpoint,256)) WHERE endpoint IS NOT NULL AND endpoint<>'';
ALTER TABLE or_mobile_push_subscriptions ADD UNIQUE KEY uq_or_push_endpoint (endpoint_hash);

-- Immutable signed-document archive compatibility.
ALTER TABLE operative_signed_document_archives ADD COLUMN signed_by_user_id BIGINT UNSIGNED NULL;
ALTER TABLE operative_signed_document_archives ADD COLUMN created_by_user_id BIGINT UNSIGNED NULL;
ALTER TABLE operative_signed_document_archives ADD COLUMN document_title VARCHAR(255) NULL;
ALTER TABLE operative_signed_document_archives ADD COLUMN document_type VARCHAR(80) NULL;
ALTER TABLE operative_signed_document_archives ADD COLUMN snapshot_html MEDIUMTEXT NULL;
ALTER TABLE operative_signed_document_archives ADD COLUMN final_html MEDIUMTEXT NULL;
ALTER TABLE operative_signed_document_archives ADD COLUMN snapshot_sha256 VARCHAR(80) NULL;
ALTER TABLE operative_signed_document_archives ADD COLUMN content_sha256 VARCHAR(80) NULL;
ALTER TABLE operative_signed_document_archives ADD COLUMN content_block_manifest_json JSON NULL;
ALTER TABLE operative_signed_document_archives ADD COLUMN assembly_run_id BIGINT UNSIGNED NULL;
ALTER TABLE operative_signed_document_archives ADD COLUMN source_version VARCHAR(40) NULL;
ALTER TABLE operative_signed_document_archives ADD COLUMN immutable_record TINYINT(1) NOT NULL DEFAULT 1;
ALTER TABLE operative_signed_document_archives ADD COLUMN updated_at DATETIME NULL;
UPDATE operative_signed_document_archives
   SET snapshot_html=COALESCE(snapshot_html, final_html),
       final_html=COALESCE(final_html, snapshot_html),
       snapshot_sha256=COALESCE(snapshot_sha256, content_sha256),
       content_sha256=COALESCE(content_sha256, snapshot_sha256),
       signed_by_user_id=COALESCE(signed_by_user_id, created_by_user_id),
       created_by_user_id=COALESCE(created_by_user_id, signed_by_user_id),
       source_version=COALESCE(source_version,'0.78.0'),
       immutable_record=1,
       updated_at=COALESCE(updated_at,NOW());
ALTER TABLE operative_signed_document_archives ADD KEY idx_signed_doc_content_hash (content_sha256);

ALTER TABLE operative_case_documents ADD COLUMN signed_archive_id BIGINT UNSIGNED NULL;
ALTER TABLE operative_case_documents ADD COLUMN locked_at DATETIME NULL;
ALTER TABLE operative_case_documents ADD COLUMN locked_by_user_id BIGINT UNSIGNED NULL;
ALTER TABLE operative_case_documents ADD COLUMN document_sha256 VARCHAR(80) NULL;
ALTER TABLE operative_case_documents ADD COLUMN content_block_manifest_json JSON NULL;
ALTER TABLE operative_case_documents ADD COLUMN assembly_run_id BIGINT UNSIGNED NULL;
ALTER TABLE operative_case_documents ADD COLUMN immutable_status VARCHAR(40) NULL;

INSERT INTO app_settings (setting_key,setting_value,created_at,updated_at)
VALUES
('core_version','0.78.0',NOW(),NOW()),
('schema_version','0.78.0',NOW(),NOW()),
('enterprise_integration_0_78','1',NOW(),NOW()),
('migration_ledger_enabled','1',NOW(),NOW()),
('module_registry_enabled','1',NOW(),NOW()),
('legacy_modules_marked','1',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();
