-- DentalCare v0.5.9.8 inventory, procurement, and sterilization integrity.

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_suppliers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(190) NOT NULL,
  `contact_person` VARCHAR(150) NULL,
  `email` VARCHAR(190) NULL,
  `phone` VARCHAR(60) NULL,
  `address` TEXT NULL,
  `tax_id` VARCHAR(80) NULL,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inventory_supplier_name` (`name`),
  KEY `idx_inventory_supplier_status` (`status`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_branch_stocks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `current_stock` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `reorder_level` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `last_reconciled_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inventory_branch_stock` (`item_id`,`branch_id`),
  KEY `idx_inventory_branch_alert` (`branch_id`,`current_stock`,`reorder_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_documents` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `document_no` VARCHAR(40) NOT NULL,
  `document_type` ENUM('purchase_order','goods_receipt','issue','return','transfer','stock_count','adjustment','damage','expiry','disposal') NOT NULL,
  `source_document_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `source_branch_id` BIGINT UNSIGNED NULL,
  `destination_branch_id` BIGINT UNSIGNED NULL,
  `supplier_id` BIGINT UNSIGNED NULL,
  `requested_for_user_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `procedure_id` BIGINT UNSIGNED NULL,
  `external_reference` VARCHAR(120) NULL,
  `reason` VARCHAR(500) NOT NULL,
  `evidence_reference` VARCHAR(255) NULL,
  `status` ENUM('draft','submitted','approved','rejected','posted','dispatched','received','cancelled') NOT NULL DEFAULT 'draft',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `approved_by` BIGINT UNSIGNED NULL,
  `approved_at` DATETIME NULL,
  `review_note` TEXT NULL,
  `posted_by` BIGINT UNSIGNED NULL,
  `posted_at` DATETIME NULL,
  `received_by` BIGINT UNSIGNED NULL,
  `received_at` DATETIME NULL,
  `document_hash` CHAR(64) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inventory_document_no` (`document_no`),
  KEY `idx_inventory_document_work` (`document_type`,`status`,`branch_id`,`requested_at`),
  KEY `idx_inventory_document_transfer` (`source_branch_id`,`destination_branch_id`,`status`),
  KEY `idx_inventory_document_source` (`source_document_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_document_lines` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `document_id` BIGINT UNSIGNED NOT NULL,
  `source_line_id` BIGINT UNSIGNED NULL,
  `item_id` BIGINT UNSIGNED NOT NULL,
  `lot_id` BIGINT UNSIGNED NULL,
  `lot_number` VARCHAR(100) NULL,
  `expiry_date` DATE NULL,
  `quantity` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `unit_cost` DECIMAL(12,2) NULL,
  `recorded_quantity` DECIMAL(12,2) NULL,
  `counted_quantity` DECIMAL(12,2) NULL,
  `variance_quantity` DECIMAL(12,2) NULL,
  `notes` VARCHAR(500) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_inventory_document_line` (`document_id`,`item_id`),
  KEY `idx_inventory_source_line` (`source_line_id`),
  KEY `idx_inventory_document_lot` (`lot_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_document_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `document_id` BIGINT UNSIGNED NOT NULL,
  `previous_status` VARCHAR(30) NULL,
  `new_status` VARCHAR(30) NOT NULL,
  `notes` TEXT NULL,
  `changed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_inventory_document_history` (`document_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}sterilization_equipment` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `serial_number` VARCHAR(120) NULL,
  `manufacturer` VARCHAR(150) NULL,
  `model` VARCHAR(120) NULL,
  `last_service_date` DATE NULL,
  `next_service_date` DATE NULL,
  `status` ENUM('active','maintenance','out_of_service','retired') NOT NULL DEFAULT 'active',
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sterilization_equipment` (`branch_id`,`name`),
  KEY `idx_sterilization_equipment_status` (`branch_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}instrument_sets` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `set_code` VARCHAR(80) NOT NULL,
  `name` VARCHAR(190) NOT NULL,
  `contents` TEXT NOT NULL,
  `status` ENUM('dirty','prepared','in_cycle','sterile','in_use','quarantined','retired') NOT NULL DEFAULT 'dirty',
  `last_batch_id` BIGINT UNSIGNED NULL,
  `prepared_by` BIGINT UNSIGNED NULL,
  `prepared_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_instrument_set_code` (`branch_id`,`set_code`),
  KEY `idx_instrument_set_work` (`branch_id`,`status`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}sterilization_batch_sets` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `batch_id` BIGINT UNSIGNED NOT NULL,
  `instrument_set_id` BIGINT UNSIGNED NOT NULL,
  `loaded_by` BIGINT UNSIGNED NULL,
  `loaded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sterilization_batch_set` (`batch_id`,`instrument_set_id`),
  KEY `idx_sterilization_set_batch` (`instrument_set_id`,`batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}sterilization_batch_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `batch_id` BIGINT UNSIGNED NOT NULL,
  `previous_status` VARCHAR(30) NULL,
  `new_status` VARCHAR(30) NOT NULL,
  `event_data_json` JSON NULL,
  `notes` TEXT NULL,
  `changed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sterilization_batch_event` (`batch_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}instrument_usage` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `instrument_set_id` BIGINT UNSIGNED NOT NULL,
  `batch_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `procedure_id` BIGINT UNSIGNED NULL,
  `assigned_by` BIGINT UNSIGNED NOT NULL,
  `assigned_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `returned_by` BIGINT UNSIGNED NULL,
  `returned_at` DATETIME NULL,
  `notes` TEXT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_instrument_usage_patient` (`patient_id`,`assigned_at`),
  KEY `idx_instrument_usage_set` (`instrument_set_id`,`returned_at`),
  KEY `idx_instrument_usage_batch` (`batch_id`,`assigned_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Existing stock is preserved by creating a branch balance and a legacy lot.
INSERT INTO `{{prefix}}inventory_branch_stocks` (`item_id`,`branch_id`,`current_stock`,`reorder_level`,`last_reconciled_at`,`updated_at`)
SELECT i.id,COALESCE(i.branch_id,(SELECT b.id FROM `{{prefix}}branches` b WHERE b.is_active=1 ORDER BY b.id LIMIT 1)),i.current_stock,i.reorder_level,NOW(),NOW()
FROM `{{prefix}}inventory_items` i
WHERE COALESCE(i.branch_id,(SELECT b.id FROM `{{prefix}}branches` b WHERE b.is_active=1 ORDER BY b.id LIMIT 1)) IS NOT NULL
ON DUPLICATE KEY UPDATE reorder_level=VALUES(reorder_level),last_reconciled_at=NOW(),updated_at=NOW();

INSERT INTO `{{prefix}}inventory_lots` (`item_id`,`branch_id`,`lot_number`,`expiry_date`,`quantity_received`,`quantity_remaining`,`received_at`,`status`,`created_at`,`updated_at`)
SELECT bs.item_id,bs.branch_id,CONCAT('LEGACY-RECON-',bs.item_id,'-',bs.branch_id),NULL,
       GREATEST(bs.current_stock-COALESCE((SELECT SUM(l.quantity_remaining) FROM `{{prefix}}inventory_lots` l WHERE l.item_id=bs.item_id AND l.branch_id=bs.branch_id),0),0),
       GREATEST(bs.current_stock-COALESCE((SELECT SUM(l.quantity_remaining) FROM `{{prefix}}inventory_lots` l WHERE l.item_id=bs.item_id AND l.branch_id=bs.branch_id),0),0),
       CURDATE(),'active',NOW(),NOW()
FROM `{{prefix}}inventory_branch_stocks` bs
WHERE bs.current_stock>COALESCE((SELECT SUM(l.quantity_remaining) FROM `{{prefix}}inventory_lots` l WHERE l.item_id=bs.item_id AND l.branch_id=bs.branch_id),0)
  AND NOT EXISTS (SELECT 1 FROM `{{prefix}}inventory_lots` lx WHERE lx.item_id=bs.item_id AND lx.branch_id=bs.branch_id AND lx.lot_number=CONCAT('LEGACY-RECON-',bs.item_id,'-',bs.branch_id));

UPDATE `{{prefix}}inventory_branch_stocks` bs
SET bs.current_stock=COALESCE((SELECT SUM(l.quantity_remaining) FROM `{{prefix}}inventory_lots` l WHERE l.item_id=bs.item_id AND l.branch_id=bs.branch_id AND l.status='active'),0),
    bs.last_reconciled_at=NOW(),bs.updated_at=NOW();

UPDATE `{{prefix}}inventory_items` i
SET i.current_stock=COALESCE((SELECT SUM(bs.current_stock) FROM `{{prefix}}inventory_branch_stocks` bs WHERE bs.item_id=i.id),0),i.updated_at=NOW();

UPDATE `{{prefix}}sterilization_batches`
SET `cycle_status`=CASE
  WHEN `release_status`='released' THEN 'released'
  WHEN `release_status`='failed' THEN 'failed'
  WHEN `release_status`='quarantined' THEN 'quarantined'
  WHEN `cycle_completed_at` IS NOT NULL THEN 'completed'
  ELSE 'loaded'
END
WHERE `cycle_status`='loaded';
