-- Baltique OS Core 0.42.0 — TheatreFlow v2 / OS compatibility hotfixes / operative docs repair

CREATE TABLE IF NOT EXISTS theatre_v2_anesthesia_types (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(80) NOT NULL,
  name VARCHAR(190) NOT NULL,
  color VARCHAR(40) 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_tf2_an_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_room_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  theatre_room_id BIGINT UNSIGNED NOT NULL,
  day_of_week TINYINT UNSIGNED NOT NULL DEFAULT 1,
  starts_at TIME NOT NULL DEFAULT '07:00:00',
  ends_at TIME NOT NULL DEFAULT '19:00:00',
  allowed_anesthesia_json JSON NULL,
  default_team_id BIGINT UNSIGNED NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  note TEXT 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_tf2_room_rules_room (theatre_room_id, day_of_week, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_teams (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(190) NOT NULL,
  role_profile VARCHAR(120) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  note TEXT 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_tf2_teams_active (active, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_team_members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  display_name VARCHAR(190) NULL,
  clinical_role VARCHAR(120) NOT NULL DEFAULT 'staff',
  starts_at TIME NULL,
  ends_at TIME NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_tf2_team_members_team (team_id, active),
  KEY idx_tf2_team_members_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_beds (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  hospital_room_id BIGINT UNSIGNED NULL,
  theatre_room_id BIGINT UNSIGNED NULL,
  bed_code VARCHAR(120) NOT NULL,
  bed_type VARCHAR(80) NOT NULL DEFAULT 'ward',
  status VARCHAR(40) NOT NULL DEFAULT 'free',
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED 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_tf2_bed_code (bed_code),
  KEY idx_tf2_beds_status (status, active),
  KEY idx_tf2_beds_case (operative_case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_bookings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  theatre_room_id BIGINT UNSIGNED NOT NULL,
  team_id BIGINT UNSIGNED NULL,
  anesthesia_type VARCHAR(80) NULL,
  bed_id BIGINT UNSIGNED NULL,
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'planned',
  readiness_status VARCHAR(40) NOT NULL DEFAULT 'pending',
  implant_status VARCHAR(40) NOT NULL DEFAULT 'not_required',
  lab_status VARCHAR(40) NOT NULL DEFAULT 'unknown',
  map_entity_id BIGINT UNSIGNED NULL,
  note TEXT NULL,
  created_by_user_id BIGINT UNSIGNED 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_tf2_booking_case (operative_case_id),
  KEY idx_tf2_booking_room (theatre_room_id, starts_at),
  KEY idx_tf2_booking_patient (patient_id),
  KEY idx_tf2_booking_status (status, starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(80) NOT NULL,
  event_body TEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tf2_events_booking (booking_id, created_at),
  KEY idx_tf2_events_case (operative_case_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operative_signed_document_archives (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  document_id BIGINT UNSIGNED NOT NULL,
  operative_case_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  signature_id BIGINT UNSIGNED NULL,
  signer_role VARCHAR(60) NOT NULL DEFAULT 'patient',
  signed_at DATETIME NOT NULL,
  signed_by_user_id BIGINT UNSIGNED NULL,
  signer_ip VARCHAR(64) NULL,
  signer_user_agent VARCHAR(500) NULL,
  document_title VARCHAR(255) NOT NULL,
  document_type VARCHAR(80) NOT NULL,
  snapshot_html MEDIUMTEXT NOT NULL,
  signature_png MEDIUMTEXT NULL,
  snapshot_sha256 VARCHAR(80) NOT NULL,
  pdf_storage_path TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'locked',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_signed_doc_role (document_id, signer_role),
  KEY idx_signed_doc_case (operative_case_id, created_at),
  KEY idx_signed_doc_patient (patient_id, created_at),
  KEY idx_signed_doc_hash (snapshot_sha256)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bad old unique index uq_op_doc is dropped by upgrade_0_42_0.php through INFORMATION_SCHEMA.
