-- migrations/0xx_product_bundles.sql
-- =====================================
-- SPRINT 3 — PRODUCT BUNDLE (optional)
-- =====================================
-- Catatan:
-- - Produk & SKU scope per location_id
-- - bundle = hubungan parent_product -> component_product + qty

CREATE TABLE IF NOT EXISTS product_bundle_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  location_id BIGINT UNSIGNED NOT NULL,
  parent_product_id BIGINT UNSIGNED NOT NULL,
  component_product_id BIGINT UNSIGNED NOT NULL,
  qty DECIMAL(12,3) NOT NULL DEFAULT 1,

  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY uq_bundle_parent_component (parent_product_id, component_product_id),
  KEY idx_bundle_location_parent (location_id, parent_product_id),
  KEY idx_bundle_location_component (location_id, component_product_id)
);

-- Optional: FK (aktifkan jika tabel products sudah pakai InnoDB & sesuai)
-- ALTER TABLE product_bundle_items
--   ADD CONSTRAINT fk_bundle_parent FOREIGN KEY (parent_product_id) REFERENCES products(id) ON DELETE CASCADE,
--   ADD CONSTRAINT fk_bundle_component FOREIGN KEY (component_product_id) REFERENCES products(id) ON DELETE RESTRICT;