-- Baltique OS Core 2.6.0
-- Enterprise Hospital Command Center
-- Reference migration. The PHP upgrade performs schema-safe, idempotent application.

CREATE TABLE IF NOT EXISTS enterprise_command_center_role_presets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_key VARCHAR(80) NOT NULL,
  preset_key VARCHAR(120) NOT NULL,
  preset_label VARCHAR(190) NOT NULL,
  layers_json JSON NULL,
  priorities_json JSON NULL,
  object_permissions_json JSON NULL,
  default_flag TINYINT(1) NOT NULL DEFAULT 1,
  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_hcc_role_preset(role_key,preset_key),
  KEY idx_hcc_role_active(role_key,active,default_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_command_center_user_preferences (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  site_id BIGINT UNSIGNED NULL,
  floor_id BIGINT UNSIGNED NULL,
  preset_key VARCHAR(120) NULL,
  layers_json JSON NULL,
  density VARCHAR(40) NOT NULL DEFAULT 'comfortable',
  zoom_level DECIMAL(8,4) NOT NULL DEFAULT 1.0000,
  pan_x DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  pan_y DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  auto_refresh_seconds INT UNSIGNED NOT NULL DEFAULT 15,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_hcc_user_pref(user_id),
  KEY idx_hcc_pref_site(site_id,floor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_command_center_object_positions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id BIGINT UNSIGNED NOT NULL,
  floor_id BIGINT UNSIGNED NOT NULL,
  object_type VARCHAR(60) NOT NULL,
  object_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NULL,
  x DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  y DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  width DECIMAL(10,2) NOT NULL DEFAULT 150.00,
  height DECIMAL(10,2) NOT NULL DEFAULT 74.00,
  source_mode VARCHAR(40) NOT NULL DEFAULT 'derived',
  locked TINYINT(1) NOT NULL DEFAULT 0,
  metadata_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_hcc_object_position(site_id,floor_id,object_type,object_id),
  KEY idx_hcc_position_room(room_id,object_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_command_center_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  snapshot_key VARCHAR(128) NOT NULL,
  site_id BIGINT UNSIGNED NULL,
  floor_id BIGINT UNSIGNED NULL,
  role_key VARCHAR(80) NOT NULL,
  operational_date DATE NOT NULL,
  payload_hash VARCHAR(128) NOT NULL,
  payload_json LONGTEXT NOT NULL,
  generation_ms INT UNSIGNED NOT NULL DEFAULT 0,
  query_count INT UNSIGNED NOT NULL DEFAULT 0,
  generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_hcc_snapshot_key(snapshot_key),
  KEY idx_hcc_snapshot_expiry(expires_at),
  KEY idx_hcc_snapshot_scope(site_id,floor_id,role_key,operational_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_command_center_action_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  action_uid VARCHAR(80) NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  role_key VARCHAR(80) NOT NULL,
  site_id BIGINT UNSIGNED NULL,
  floor_id BIGINT UNSIGNED NULL,
  room_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  work_item_id BIGINT UNSIGNED NULL,
  action_key VARCHAR(120) NOT NULL,
  object_type VARCHAR(60) NULL,
  object_id BIGINT UNSIGNED NULL,
  previous_value TEXT NULL,
  new_value TEXT NULL,
  action_status VARCHAR(40) NOT NULL DEFAULT 'completed',
  reason TEXT NULL,
  payload_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_hcc_action_uid(action_uid),
  KEY idx_hcc_action_case(operative_case_id,created_at),
  KEY idx_hcc_action_user(user_id,created_at),
  KEY idx_hcc_action_work(work_item_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_command_center_handoffs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  handoff_uid VARCHAR(80) NOT NULL,
  shift_date DATE NOT NULL,
  site_id BIGINT UNSIGNED NULL,
  floor_id BIGINT UNSIGNED NULL,
  role_key VARCHAR(80) NOT NULL,
  title VARCHAR(255) NOT NULL,
  body TEXT NULL,
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  owner_user_id BIGINT UNSIGNED NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  acknowledged_by_user_id BIGINT UNSIGNED NULL,
  acknowledged_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_hcc_handoff_uid(handoff_uid),
  KEY idx_hcc_handoff_scope(site_id,floor_id,shift_date,status),
  KEY idx_hcc_handoff_role(role_key,status,priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings(setting_key,setting_value,created_at,updated_at) VALUES
('enterprise_hospital_command_center_version','2.6.0',NOW(),NOW()),
('enterprise_hospital_command_center_enabled','1',NOW(),NOW()),
('enterprise_hospital_command_center_cache_ttl','12',NOW(),NOW()),
('enterprise_hospital_command_center_auto_clinical_decision_enabled','0',NOW(),NOW()),
('enterprise_hospital_command_center_auto_patient_message_enabled','0',NOW(),NOW()),
('enterprise_hospital_command_center_auto_or_stage_move_enabled','0',NOW(),NOW()),
('enterprise_hospital_command_center_auto_go_live_enabled','0',NOW(),NOW()),
('enterprise_hospital_command_center_role_home_enabled','1',NOW(),NOW()),
('enterprise_hospital_command_center_incremental_refresh_enabled','1',NOW(),NOW()),
('core_version_260','1',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value),updated_at=NOW();
