-- Baltique OS 2.10.4 — Instrumentarium chirurgii
-- Hierarchy: official specialty -> governed set -> instrument.
-- Seed data is applied idempotently by upgrade_surgical_instrumentarium_2104.php
-- or automatically on the first opening of the canonical module.

CREATE TABLE IF NOT EXISTS enterprise_medical_specialties_2104 (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  specialty_key VARCHAR(96) NOT NULL,
  official_code CHAR(4) NOT NULL,
  profession_type VARCHAR(24) NOT NULL,
  name_pl VARCHAR(190) NOT NULL,
  instrument_profile VARCHAR(24) NOT NULL DEFAULT 'clinical',
  official_source_url VARCHAR(500) NOT NULL,
  official_status VARCHAR(32) NOT NULL DEFAULT 'official_current',
  sort_order INT NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_specialty_key (specialty_key),
  UNIQUE KEY uq_specialty_profession_code (profession_type,official_code),
  KEY idx_specialty_name (profession_type,name_pl),
  KEY idx_specialty_active (active,instrument_profile)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_surgical_instrument_sets_2104 (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  set_key VARCHAR(128) NOT NULL,
  set_code VARCHAR(64) NOT NULL,
  specialty_id BIGINT UNSIGNED NOT NULL,
  name_pl VARCHAR(255) NOT NULL,
  procedure_scope TEXT NULL,
  set_type VARCHAR(32) NOT NULL DEFAULT 'procedure',
  approval_status VARCHAR(32) NOT NULL DEFAULT 'reference_draft',
  count_sheet_required TINYINT(1) NOT NULL DEFAULT 1,
  reprocessing_notes TEXT NULL,
  source_kind VARCHAR(24) NOT NULL DEFAULT 'local',
  metadata_json LONGTEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  approved_by BIGINT UNSIGNED NULL,
  approved_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_instrument_set_key (set_key),
  UNIQUE KEY uq_instrument_set_code (set_code),
  KEY idx_instrument_set_specialty (specialty_id,active,approval_status),
  KEY idx_instrument_set_name (name_pl)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_surgical_instrument_set_items_2104 (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  set_id BIGINT UNSIGNED NOT NULL,
  variant_id BIGINT UNSIGNED NOT NULL,
  quantity_required DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  item_role VARCHAR(24) NOT NULL DEFAULT 'required',
  sequence_no INT NOT NULL DEFAULT 100,
  assignment_source VARCHAR(24) NOT NULL DEFAULT 'manual',
  notes TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_instrument_set_variant (set_id,variant_id),
  KEY idx_instrument_variant_sets (variant_id,active),
  KEY idx_instrument_set_sequence (set_id,active,sequence_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE system_modules
SET module_label='Instrumentarium chirurgii',
    nav_label='Instrumentarium',
    route_path='/system_surgical_instrumentarium_2104.php',
    clinical_purpose='Specjalizacja → zestaw zabiegowy → narzędzie; kontrolowane składy, liczenie i lokalne zatwierdzanie.',
    status='active',active=1,nav_visible=1,updated_at=NOW()
WHERE module_key='plasticinstrumentcatalog2103';

