-- Addons feature — run AFTER etailorz_orders.sql
-- (needs shops and order_items tables to already exist)

-- ============================================
-- 1. ADDONS (shop-scoped master list)
-- ============================================
CREATE TABLE IF NOT EXISTS `addons` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `name_ta` VARCHAR(255) NULL DEFAULT NULL,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `addons_shop_id_foreign` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 2. ORDER_ITEM_ADDON (pivot with price snapshot)
-- ============================================
CREATE TABLE IF NOT EXISTS `order_item_addon` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_item_id` BIGINT UNSIGNED NOT NULL,
  `addon_id` BIGINT UNSIGNED NOT NULL,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `oia_order_item_id_foreign` FOREIGN KEY (`order_item_id`) REFERENCES `order_items` (`id`) ON DELETE CASCADE,
  CONSTRAINT `oia_addon_id_foreign` FOREIGN KEY (`addon_id`) REFERENCES `addons` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 3. SAMPLE ADDONS (edit or delete these to match your shop)
-- ============================================
INSERT INTO `addons` (`shop_id`, `name`, `name_ta`, `price`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Extra Buttons', 'கூடுதல் பட்டன்', 20.00, 1, NOW(), NOW()),
(1, 'Embroidery', 'எம்பிராய்டரி', 150.00, 1, NOW(), NOW()),
(1, 'Lining', 'லைனிங்', 80.00, 1, NOW(), NOW()),
(1, 'Piping', 'பைப்பிங்', 40.00, 1, NOW(), NOW());