-- Baltique OS Core 0.78.6 — Enterprise Work Queue
-- Reference migration. Runtime upgrade is implemented idempotently in /upgrade_0_78_6.php.

CREATE TABLE IF NOT EXISTS enterprise_work_sla_policies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  policy_key VARCHAR(160) NOT NULL,
  role_key VARCHAR(80) NOT NULL,
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  target_minutes INT UNSIGNED NOT NULL DEFAULT 1440,
  escalation_minutes INT UNSIGNED NOT NULL DEFAULT 2880,
  active TINYINT(1) NOT NULL DEFAULT 1,
  description TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_work_sla_policy (policy_key),
  KEY idx_enterprise_work_sla_lookup (role_key, priority, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_work_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  work_uid VARCHAR(80) NOT NULL,
  case_spine_id BIGINT UNSIGNED NULL,
  case_uid VARCHAR(80) NULL,
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  journey_action_id BIGINT UNSIGNED NULL,
  source_engine VARCHAR(80) NOT NULL DEFAULT 'journey_orchestrator',
  source_key VARCHAR(190) NOT NULL,
  source_gate_key VARCHAR(160) NULL,
  work_type VARCHAR(80) NOT NULL DEFAULT 'journey_action',
  work_label VARCHAR(255) NOT NULL,
  work_body TEXT NULL,
  role_key VARCHAR(80) NOT NULL DEFAULT 'coordinator',
  owner_user_id BIGINT UNSIGNED NULL,
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  sla_policy_key VARCHAR(160) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  due_at DATETIME NULL,
  breached_at DATETIME NULL,
  completed_at DATETIME NULL,
  completed_by_user_id BIGINT UNSIGNED NULL,
  dedupe_key VARCHAR(190) NOT NULL,
  context_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_work_uid (work_uid),
  UNIQUE KEY uq_enterprise_work_dedupe (dedupe_key),
  KEY idx_enterprise_work_inbox (status, role_key, priority, due_at),
  KEY idx_enterprise_work_case (case_spine_id, status),
  KEY idx_enterprise_work_patient (patient_id, status),
  KEY idx_enterprise_work_source (source_engine, source_key),
  KEY idx_enterprise_work_journey_action (journey_action_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_work_item_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  work_item_id BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  from_status VARCHAR(40) NULL,
  to_status VARCHAR(40) NULL,
  actor_user_id BIGINT UNSIGNED NULL,
  note TEXT NULL,
  details_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_enterprise_work_event_item (work_item_id, created_at),
  KEY idx_enterprise_work_event_type (event_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_work_queue_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_uuid VARCHAR(80) NOT NULL,
  run_type VARCHAR(80) NOT NULL DEFAULT 'manual_sync',
  scanned_actions INT UNSIGNED NOT NULL DEFAULT 0,
  created_items INT UNSIGNED NOT NULL DEFAULT 0,
  updated_items INT UNSIGNED NOT NULL DEFAULT 0,
  reopened_items INT UNSIGNED NOT NULL DEFAULT 0,
  closed_items INT UNSIGNED NOT NULL DEFAULT 0,
  open_items INT UNSIGNED NOT NULL DEFAULT 0,
  breached_items INT UNSIGNED NOT NULL DEFAULT 0,
  details_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_work_queue_run_uuid (run_uuid),
  KEY idx_enterprise_work_queue_run_type (run_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO enterprise_work_sla_policies (policy_key,role_key,priority,target_minutes,escalation_minutes,active,description,created_at,updated_at) VALUES
('coordinator_critical_default','coordinator','critical',60,120,1,'Koordynacja — krytyczne blokady pacjenta lub dokumentów.',NOW(),NOW()),
('coordinator_high_default','coordinator','high',240,480,1,'Koordynacja — ważne zadania przedoperacyjne.',NOW(),NOW()),
('coordinator_normal_default','coordinator','normal',1440,2880,1,'Koordynacja — standardowe zadania ścieżki.',NOW(),NOW()),
('doctor_critical_default','doctor','critical',120,240,1,'Lekarz — krytyczna decyzja kliniczna GO/HOLD/NO-GO.',NOW(),NOW()),
('doctor_high_default','doctor','high',360,720,1,'Lekarz — kwalifikacja lub przegląd medyczny.',NOW(),NOW()),
('doctor_normal_default','doctor','normal',1440,2880,1,'Lekarz — standardowe zadania kliniczne.',NOW(),NOW()),
('anesthesiologist_critical_default','anesthesiologist','critical',120,240,1,'Anestezjologia — krytyczny blocker przed zabiegiem.',NOW(),NOW()),
('anesthesiologist_high_default','anesthesiologist','high',360,720,1,'Anestezjologia — review wymagające decyzji.',NOW(),NOW()),
('anesthesiologist_normal_default','anesthesiologist','normal',1440,2880,1,'Anestezjologia — standardowy review.',NOW(),NOW()),
('or_manager_critical_default','or_manager','critical',60,120,1,'OR Manager — krytyczny blocker sali, personelu, PACU lub implantów.',NOW(),NOW()),
('or_manager_high_default','or_manager','high',180,360,1,'OR Manager — ważna gotowość dnia operacyjnego.',NOW(),NOW()),
('or_manager_normal_default','or_manager','normal',720,1440,1,'OR Manager — standardowa gotowość OR.',NOW(),NOW()),
('reception_critical_default','reception','critical',120,240,1,'Recepcja — krytyczna blokada płatności lub identyfikacji.',NOW(),NOW()),
('reception_high_default','reception','high',360,720,1,'Recepcja — ważne rozliczenie.',NOW(),NOW()),
('reception_normal_default','reception','normal',1440,2880,1,'Recepcja — standardowa obsługa płatności.',NOW(),NOW()),
('nurse_critical_default','nurse','critical',60,120,1,'Pielęgniarki — krytyczna akcja bezpieczeństwa pacjenta.',NOW(),NOW()),
('nurse_high_default','nurse','high',240,480,1,'Pielęgniarki — ważna akcja opieki.',NOW(),NOW()),
('nurse_normal_default','nurse','normal',1440,2880,1,'Pielęgniarki — standardowa opieka.',NOW(),NOW()),
('marketing_normal_default','marketing','normal',2880,5760,1,'Marketing — działania po zakończeniu opieki i zgodach.',NOW(),NOW())
ON DUPLICATE KEY UPDATE role_key=VALUES(role_key), priority=VALUES(priority), target_minutes=VALUES(target_minutes), escalation_minutes=VALUES(escalation_minutes), active=1, description=VALUES(description), updated_at=NOW();

INSERT INTO app_settings (setting_key,setting_value,created_at,updated_at) VALUES
('core_version','0.78.6',NOW(),NOW()),
('schema_version','0.78.6',NOW(),NOW()),
('enterprise_current_release','0.78.6',NOW(),NOW()),
('enterprise_work_queue_version','0.78.6',NOW(),NOW()),
('enterprise_runtime_preflight_version','0.78.6',NOW(),NOW()),
('core_version_0786','1',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();

-- 0.78.6.1 note: if tables already existed before this migration, PHP upgrade applies
-- ALTER-based compatibility repair before seed inserts. See app/enterprise_work_queue.php.
