START TRANSACTION;

CREATE TABLE IF NOT EXISTS dispatch_note_items (
  id int unsigned NOT NULL AUTO_INCREMENT,
  dispatch_note_id int unsigned NOT NULL,
  packing_order_id int unsigned DEFAULT NULL,
  packing_order_code varchar(50) DEFAULT NULL,
  production_order_id int unsigned DEFAULT NULL,
  item_id int unsigned DEFAULT NULL,
  color varchar(100) DEFAULT NULL,
  size varchar(100) DEFAULT NULL,
  quantity int unsigned NOT NULL DEFAULT 0,
  cartons int unsigned NOT NULL DEFAULT 0,
  pallets int unsigned DEFAULT 0,
  weight_kg decimal(14,2) DEFAULT NULL,
  volume_cbm decimal(10,3) DEFAULT NULL,
  shipping_marks text DEFAULT NULL,
  carton_range_from int unsigned DEFAULT NULL,
  carton_range_to int unsigned DEFAULT NULL,
  seal_no varchar(50) DEFAULT NULL,
  notes text DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_dni_dispatch (dispatch_note_id),
  KEY idx_dni_pko (packing_order_id),
  KEY idx_dni_po (production_order_id),
  KEY idx_dni_item (item_id),
  CONSTRAINT fk_dni_dispatch FOREIGN KEY (dispatch_note_id) REFERENCES dispatch_notes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;
