CREATE TABLE IF NOT EXISTS material_options (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  material_id INT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  description TEXT NULL,
  cash_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  installment_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  unit_label VARCHAR(60) NULL,
  image VARCHAR(255) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_material_options_material FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE
);

ALTER TABLE customer_requests
  ADD COLUMN IF NOT EXISTS service_item_id INT UNSIGNED NULL AFTER material_id,
  ADD COLUMN IF NOT EXISTS service_item_name VARCHAR(190) NULL AFTER service_item_id,
  ADD COLUMN IF NOT EXISTS cash_price DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER material_price,
  ADD COLUMN IF NOT EXISTS installment_price DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER cash_price;

INSERT INTO material_options (material_id, name, description, cash_price, installment_price, unit_label, image, status, sort_order, created_at, updated_at)
SELECT m.id, m.name, m.short_description, m.price, m.price, COALESCE(m.unit_label, 'كمية'), m.image, 'active', 1, NOW(), NOW()
FROM materials m
WHERE NOT EXISTS (
  SELECT 1 FROM material_options mo WHERE mo.material_id = m.id
);