-- Add attached products functionality
-- This allows products to include other products (e.g., sponsor packages including delegate passes)

-- Create product_attachments table
CREATE TABLE IF NOT EXISTS `product_attachments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_product_id` int(11) NOT NULL COMMENT 'The main product (e.g., sponsor package)',
  `attached_product_id` int(11) NOT NULL COMMENT 'The included product (e.g., delegate pass)',
  `quantity` int(11) NOT NULL DEFAULT 1 COMMENT 'How many of the attached product are included',
  `is_active` tinyint(1) DEFAULT 1 COMMENT 'Whether this attachment is currently active',
  `display_order` int(11) DEFAULT 0 COMMENT 'Order for displaying attached products',
  `notes` text DEFAULT NULL COMMENT 'Optional notes about this attachment',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_attachment` (`parent_product_id`, `attached_product_id`),
  KEY `idx_parent_product` (`parent_product_id`),
  KEY `idx_attached_product` (`attached_product_id`),
  CONSTRAINT `fk_parent_product` FOREIGN KEY (`parent_product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_attached_product` FOREIGN KEY (`attached_product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Add indexes for better performance
CREATE INDEX `idx_product_attachments_parent_active` ON `product_attachments` (`parent_product_id`, `is_active`);
CREATE INDEX `idx_product_attachments_attached_active` ON `product_attachments` (`attached_product_id`, `is_active`);
CREATE INDEX `idx_product_attachments_display_order` ON `product_attachments` (`parent_product_id`, `display_order`);

-- Add sample data for testing (optional - can be removed in production)
-- Example: Platinum Sponsor includes 5 delegate passes
INSERT IGNORE INTO `product_attachments` (`parent_product_id`, `attached_product_id`, `quantity`, `display_order`, `notes`) 
SELECT 
    p1.id as parent_product_id,
    p2.id as attached_product_id,
    5 as quantity,
    1 as display_order,
    'Included delegate passes for Platinum sponsorship' as notes
FROM products p1, products p2 
WHERE p1.product_code = 'Platinum_Sponsor' 
  AND p2.product_code = 'dsdsd'
  AND p1.id != p2.id
LIMIT 1;
