-- Baltique OS Core 0.12.0 — Payment Wizard / Inventory Movements / Sync Conflicts
-- Reference migration. Prefer /upgrade_0_12_0.php because it conditionally adds columns.

CREATE TABLE IF NOT EXISTS inventory_lots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  inventory_item_id BIGINT UNSIGNED NOT NULL,
  lot_number VARCHAR(120) NULL,
  serial_number VARCHAR(120) NULL,
  expiry_date DATE NULL,
  manufacturer VARCHAR(190) NULL,
  qty_initial DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  qty_on_hand DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  notes 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_inventory_lots_item (inventory_item_id),
  KEY idx_inventory_lots_lot (lot_number),
  KEY idx_inventory_lots_serial (serial_number),
  KEY idx_inventory_lots_status (status, expiry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventory_movements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  inventory_item_id BIGINT UNSIGNED NOT NULL,
  inventory_lot_id BIGINT UNSIGNED NULL,
  location_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  surgery_case_id BIGINT UNSIGNED NULL,
  reservation_id BIGINT UNSIGNED NULL,
  movement_type VARCHAR(40) NOT NULL,
  quantity DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  unit VARCHAR(30) NOT NULL DEFAULT 'szt.',
  reason VARCHAR(190) NULL,
  document_ref VARCHAR(190) NULL,
  notes TEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_inventory_mov_item (inventory_item_id, created_at),
  KEY idx_inventory_mov_lot (inventory_lot_id),
  KEY idx_inventory_mov_patient (patient_id),
  KEY idx_inventory_mov_surgery (surgery_case_id),
  KEY idx_inventory_mov_type (movement_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_sync_conflicts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_id BIGINT UNSIGNED NULL,
  source_key VARCHAR(80) NOT NULL,
  catalog_item_id BIGINT UNSIGNED NULL,
  sku VARCHAR(120) NULL,
  existing_json JSON NULL,
  incoming_json JSON NULL,
  conflict_fields VARCHAR(255) NULL,
  source_url TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'pending',
  detected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME NULL,
  resolved_by_user_id BIGINT UNSIGNED NULL,
  decision_note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_catalog_conflicts_status (status, detected_at),
  KEY idx_catalog_conflicts_item (catalog_item_id),
  KEY idx_catalog_conflicts_source (source_key, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- catalog_sync_sources gets conflict_mode: review or auto.
