ALTER TABLE order_items
  ADD COLUMN vendor_active TINYINT(1) NOT NULL DEFAULT 0 AFTER note,
  ADD COLUMN vendor_customer_id BIGINT UNSIGNED NULL AFTER vendor_active,
  ADD COLUMN vendor_nominal DECIMAL(14,2) NOT NULL DEFAULT 0.00 AFTER vendor_customer_id,
  ADD COLUMN vendor_note VARCHAR(255) NULL AFTER vendor_nominal;

ALTER TABLE order_items
  ADD KEY idx_order_items_vendor_customer (vendor_customer_id);

CREATE TABLE IF NOT EXISTS vendor_expenses (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  source_type VARCHAR(20) NOT NULL,
  source_ref_id BIGINT UNSIGNED NULL,
  order_id BIGINT UNSIGNED NULL,
  order_item_id BIGINT UNSIGNED NULL,
  vendor_customer_id BIGINT UNSIGNED NULL,
  vendor_name_snapshot VARCHAR(190) NOT NULL,
  product_id BIGINT UNSIGNED NULL,
  product_name_snapshot VARCHAR(190) NOT NULL,
  tanggal DATE NOT NULL,
  nominal DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  catatan TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_by_name_snapshot VARCHAR(190) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_vendor_expenses_pos_item (source_type, order_item_id),
  KEY idx_vendor_expenses_location_date (location_id, tanggal),
  KEY idx_vendor_expenses_vendor_date (vendor_customer_id, tanggal),
  KEY idx_vendor_expenses_order (order_id),
  KEY idx_vendor_expenses_item (order_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;