-- DentalCare v0.5.9.8.46 inventory, sterilization, and clinical-supply completion.
-- Idempotent create-only tables. Column/index repairs are handled in setup_apply_v059846().

CREATE TABLE IF NOT EXISTS `{{prefix}}procedure_supply_templates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `template_name` VARCHAR(190) NOT NULL,
  `procedure_keyword` VARCHAR(190) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_procedure_supply_template` (`branch_id`,`template_name`),
  KEY `idx_procedure_supply_template_lookup` (`branch_id`,`is_active`,`procedure_keyword`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}procedure_supply_template_lines` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `template_id` BIGINT UNSIGNED NOT NULL,
  `item_id` BIGINT UNSIGNED NOT NULL,
  `default_quantity` DECIMAL(12,2) NOT NULL DEFAULT 1.00,
  `is_required` TINYINT(1) NOT NULL DEFAULT 1,
  `notes` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_procedure_supply_template_line` (`template_id`,`item_id`),
  KEY `idx_procedure_supply_template_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}clinical_supply_usage_audit` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `procedure_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `inventory_document_id` BIGINT UNSIGNED NULL,
  `inventory_document_line_id` BIGINT UNSIGNED NULL,
  `item_id` BIGINT UNSIGNED NOT NULL,
  `lot_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `quantity_used` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `usage_source` ENUM('manual_issue','procedure_template','chairside_adjustment','return_adjustment') NOT NULL DEFAULT 'manual_issue',
  `review_status` ENUM('pending','reviewed','variance','voided') NOT NULL DEFAULT 'pending',
  `notes` VARCHAR(500) NULL,
  `recorded_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_clinical_supply_usage_proc` (`procedure_id`,`created_at`),
  KEY `idx_clinical_supply_usage_encounter` (`encounter_id`,`created_at`),
  KEY `idx_clinical_supply_usage_lot` (`item_id`,`lot_id`,`branch_id`),
  KEY `idx_clinical_supply_usage_doc` (`inventory_document_id`,`inventory_document_line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_safety_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `event_type` VARCHAR(80) NOT NULL,
  `severity` ENUM('info','warning','critical') NOT NULL DEFAULT 'info',
  `branch_id` BIGINT UNSIGNED NULL,
  `item_id` BIGINT UNSIGNED NULL,
  `lot_id` BIGINT UNSIGNED NULL,
  `document_id` BIGINT UNSIGNED NULL,
  `procedure_id` BIGINT UNSIGNED NULL,
  `batch_id` BIGINT UNSIGNED NULL,
  `instrument_set_id` BIGINT UNSIGNED NULL,
  `message` VARCHAR(500) NOT NULL,
  `resolved_at` DATETIME NULL,
  `resolved_by` BIGINT UNSIGNED NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_inventory_safety_events_open` (`branch_id`,`severity`,`resolved_at`,`created_at`),
  KEY `idx_inventory_safety_events_type` (`event_type`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}sterilization_release_reviews` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `batch_id` BIGINT UNSIGNED NOT NULL,
  `review_status` ENUM('pending','released','quarantined','failed') NOT NULL DEFAULT 'pending',
  `chemical_indicator_result` VARCHAR(30) NULL,
  `biological_indicator_result` VARCHAR(30) NULL,
  `equipment_service_status` ENUM('current','due_soon','overdue','unknown') NOT NULL DEFAULT 'unknown',
  `released_set_count` INT NOT NULL DEFAULT 0,
  `review_note` TEXT NULL,
  `review_hash` CHAR(64) NULL,
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sterilization_release_review_batch` (`batch_id`),
  KEY `idx_sterilization_release_review_status` (`review_status`,`reviewed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
