-- Baltique OS Core 0.78.7 — Enterprise Communication Bridge
-- Safe approval outbox between Enterprise Work Queue and Communication Hub.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS enterprise_communication_policies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  policy_key VARCHAR(160) NOT NULL,
  policy_label VARCHAR(255) NOT NULL,
  role_key VARCHAR(80) NULL,
  source_gate_pattern VARCHAR(255) NULL,
  work_text_pattern VARCHAR(255) NULL,
  purpose_code VARCHAR(80) NOT NULL DEFAULT 'operational',
  channel VARCHAR(40) NOT NULL DEFAULT 'internal',
  patient_facing TINYINT(1) NOT NULL DEFAULT 0,
  min_priority VARCHAR(40) NOT NULL DEFAULT 'low',
  subject_template VARCHAR(255) NOT NULL,
  body_template MEDIUMTEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 100,
  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_enterprise_comm_policy (policy_key),
  KEY idx_enterprise_comm_policy_lookup (active, role_key, patient_facing, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_communication_outbox (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  outbox_uid VARCHAR(80) NOT NULL,
  source_dedupe_key VARCHAR(190) NOT NULL,
  policy_key VARCHAR(160) 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,
  work_item_id BIGINT UNSIGNED NULL,
  source_gate_key VARCHAR(160) NULL,
  role_key VARCHAR(80) NULL,
  purpose_code VARCHAR(80) NOT NULL DEFAULT 'operational',
  channel VARCHAR(40) NOT NULL DEFAULT 'internal',
  patient_facing TINYINT(1) NOT NULL DEFAULT 0,
  recipient_label VARCHAR(190) NULL,
  recipient_email VARCHAR(190) NULL,
  recipient_phone VARCHAR(80) NULL,
  subject VARCHAR(255) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  status VARCHAR(40) NOT NULL DEFAULT 'needs_review',
  consent_status VARCHAR(80) NOT NULL DEFAULT 'needs_review',
  consent_reason TEXT NULL,
  approved_by_user_id BIGINT UNSIGNED NULL,
  approved_at DATETIME NULL,
  materialized_by_user_id BIGINT UNSIGNED NULL,
  materialized_at DATETIME NULL,
  hub_thread_id BIGINT UNSIGNED NULL,
  hub_message_id BIGINT UNSIGNED NULL,
  queue_table VARCHAR(80) NULL,
  queue_id BIGINT UNSIGNED NULL,
  notes TEXT NULL,
  context_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_comm_outbox_uid (outbox_uid),
  UNIQUE KEY uq_enterprise_comm_outbox_source (source_dedupe_key),
  KEY idx_enterprise_comm_outbox_status (status, priority, created_at),
  KEY idx_enterprise_comm_outbox_case (case_spine_id, status),
  KEY idx_enterprise_comm_outbox_patient (patient_id, status),
  KEY idx_enterprise_comm_outbox_work (work_item_id),
  KEY idx_enterprise_comm_outbox_hub (hub_message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_communication_outbox_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  outbox_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_comm_event_outbox (outbox_id, created_at),
  KEY idx_enterprise_comm_event_type (event_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_communication_bridge_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_uuid VARCHAR(80) NOT NULL,
  run_type VARCHAR(80) NOT NULL DEFAULT 'manual_sync',
  scanned_work_items INT UNSIGNED NOT NULL DEFAULT 0,
  created_drafts INT UNSIGNED NOT NULL DEFAULT 0,
  updated_drafts INT UNSIGNED NOT NULL DEFAULT 0,
  skipped_items INT UNSIGNED NOT NULL DEFAULT 0,
  blocked_drafts INT UNSIGNED NOT NULL DEFAULT 0,
  materialized_messages 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_comm_bridge_run (run_uuid),
  KEY idx_enterprise_comm_bridge_run_type (run_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings (setting_key, setting_value, created_at, updated_at) VALUES
('core_version','0.78.7',NOW(),NOW()),
('schema_version','0.78.7',NOW(),NOW()),
('enterprise_current_release','0.78.7',NOW(),NOW()),
('core_version_0787','1',NOW(),NOW()),
('enterprise_communication_bridge_version','0.78.7',NOW(),NOW()),
('enterprise_communication_bridge_enabled','1',NOW(),NOW()),
('enterprise_communication_bridge_safe_mode','1',NOW(),NOW()),
('enterprise_communication_bridge_auto_send_enabled','0',NOW(),NOW()),
('enterprise_communication_bridge_cron_enabled','0',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();

INSERT INTO system_feature_flags (flag_key,flag_value,flag_scope,description,updated_by_user_id,created_at,updated_at)
VALUES
('core_version_0787','1','global','Compatibility marker: current core is at least 0.78.7.',NULL,NOW(),NOW()),
('enterprise_communication_bridge_enabled','1','global','Enterprise Communication Bridge is enabled. Work Queue can create governed Communication Hub drafts.',NULL,NOW(),NOW()),
('enterprise_communication_bridge_auto_send_enabled','0','global','Safety flag: Communication Bridge never sends or queues patient messages automatically.',NULL,NOW(),NOW())
ON DUPLICATE KEY UPDATE flag_value=VALUES(flag_value), description=VALUES(description), updated_at=NOW();

INSERT INTO system_modules
(module_key,module_label,domain_key,owner_role,status,route_path,replacement_module_key,source_of_truth,clinical_purpose,ai_mode,version,active,nav_label,nav_icon,nav_group,nav_order,nav_visible,nav_workspace,route_health,last_seen_in_code,created_at,updated_at)
VALUES
('enterprisecommunicationbridge','Enterprise Communication Bridge','communication_experience','coordinator','active','/system_communication_bridge.php',NULL,'database','Zatwierdzalny outbox łączący Work Queue z Communication Hub; bez automatycznej wysyłki do pacjenta.','embedded_ready','0.78.7',1,'Communication Bridge','✉','Komunikacja i doświadczenie',101,1,'global','unknown',1,NOW(),NOW())
ON DUPLICATE KEY UPDATE module_label=VALUES(module_label), status='active', route_path=VALUES(route_path), clinical_purpose=VALUES(clinical_purpose), version='0.78.7', nav_label=VALUES(nav_label), nav_icon=VALUES(nav_icon), nav_group=VALUES(nav_group), nav_order=VALUES(nav_order), nav_visible=1, active=1, updated_at=NOW();

INSERT INTO system_module_routes (module_key,route_path,route_type,status,requires_role,legacy_redirect_to,created_at,updated_at)
VALUES ('enterprisecommunicationbridge','/system_communication_bridge.php','page','active','coordinator',NULL,NOW(),NOW())
ON DUPLICATE KEY UPDATE module_key=VALUES(module_key), status='active', requires_role='coordinator', 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
('093_core_0_78_7_enterprise_communication_bridge','0.78.7','database/migrations/093_core_0_78_7_enterprise_communication_bridge.sql',NULL,'applied',NULL,NOW(),'Enterprise Communication Bridge applied.',JSON_OBJECT('work_queue_bridge', true, 'auto_send', false))
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);
