-- Baltique OS Core 0.37.0 — Interactive SVG Floorplan Board / Lab OCR

CREATE TABLE IF NOT EXISTS hospital_floorplan_polygons (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id BIGINT UNSIGNED NOT NULL,
  floor_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NULL,
  code VARCHAR(120) NOT NULL,
  label VARCHAR(190) NOT NULL,
  points TEXT NOT NULL,
  room_type VARCHAR(80) NOT NULL DEFAULT 'generic',
  capacity INT UNSIGNED NOT NULL DEFAULT 0,
  color VARCHAR(40) NULL,
  z_index INT NOT NULL DEFAULT 10,
  active TINYINT(1) NOT NULL DEFAULT 1,
  metadata_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_floorplan_polygon_code (floor_id, code),
  KEY idx_floorplan_poly_site (site_id, floor_id),
  KEY idx_floorplan_poly_room (room_id),
  KEY idx_floorplan_poly_type (room_type, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hospital_live_entities (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity_key VARCHAR(140) NOT NULL,
  entity_type VARCHAR(50) NOT NULL,
  label VARCHAR(190) NOT NULL,
  subtitle VARCHAR(190) NULL,
  site_id BIGINT UNSIGNED NULL,
  floor_id BIGINT UNSIGNED NULL,
  room_id BIGINT UNSIGNED NULL,
  x DECIMAL(10,2) NOT NULL DEFAULT 100.00,
  y DECIMAL(10,2) NOT NULL DEFAULT 100.00,
  priority INT NOT NULL DEFAULT 0,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  metadata_json JSON NULL,
  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_live_entity_key (entity_key),
  KEY idx_live_entity_floor (floor_id, active),
  KEY idx_live_entity_room (room_id),
  KEY idx_live_entity_type (entity_type, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hospital_entity_movements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity_id BIGINT UNSIGNED NOT NULL,
  from_room_id BIGINT UNSIGNED NULL,
  to_room_id BIGINT UNSIGNED NULL,
  x DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  y DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  moved_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_entity_mov_entity (entity_id, created_at),
  KEY idx_entity_mov_room (to_room_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hospital_board_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id BIGINT UNSIGNED NULL,
  floor_id BIGINT UNSIGNED NULL,
  snapshot_json JSON NOT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_board_snapshot_site (site_id, floor_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_lab_ocr_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  upload_id BIGINT UNSIGNED NOT NULL,
  lab_request_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  source_path TEXT NOT NULL,
  mime_type VARCHAR(120) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'queued',
  method VARCHAR(80) NULL,
  extracted_text LONGTEXT NULL,
  error_message TEXT NULL,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_ocr_jobs_upload (upload_id),
  KEY idx_ocr_jobs_case (operative_case_id, status),
  KEY idx_ocr_jobs_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_lab_ocr_pages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ocr_job_id BIGINT UNSIGNED NOT NULL,
  page_no INT UNSIGNED NOT NULL DEFAULT 1,
  image_path TEXT NULL,
  extracted_text LONGTEXT NULL,
  confidence DECIMAL(6,2) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'new',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ocr_pages_job (ocr_job_id, page_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_lab_ocr_values (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ocr_job_id BIGINT UNSIGNED NOT NULL,
  upload_id BIGINT UNSIGNED NOT NULL,
  lab_request_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  item_name VARCHAR(190) NOT NULL,
  result_value DECIMAL(12,4) NULL,
  unit VARCHAR(60) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'ok',
  reference_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ocr_values_job (ocr_job_id),
  KEY idx_ocr_values_case (operative_case_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
